package main

import (
	"database/sql"
	"log"
	"os"
	"path/filepath"

	_ "modernc.org/sqlite"
)

var db *sql.DB

const schema = `
CREATE TABLE IF NOT EXISTS organizations (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
	plan TEXT NOT NULL DEFAULT 'standard'
);

CREATE TABLE IF NOT EXISTS api_keys (
	api_key TEXT PRIMARY KEY,
	org_id INTEGER NOT NULL,
	role TEXT NOT NULL DEFAULT 'service',
	label TEXT
);

CREATE TABLE IF NOT EXISTS accounts (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	org_id INTEGER NOT NULL,
	name TEXT NOT NULL,
	currency TEXT NOT NULL DEFAULT 'USD',
	balance_cents INTEGER NOT NULL DEFAULT 0,
	status TEXT NOT NULL DEFAULT 'active'
);

CREATE TABLE IF NOT EXISTS beneficiaries (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	org_id INTEGER NOT NULL,
	name TEXT NOT NULL,
	bank_name TEXT,
	iban TEXT,
	status TEXT NOT NULL DEFAULT 'active'
);

CREATE TABLE IF NOT EXISTS transfers (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	org_id INTEGER NOT NULL,
	account_id INTEGER NOT NULL,
	beneficiary_id INTEGER,
	amount_cents INTEGER NOT NULL,
	currency TEXT NOT NULL DEFAULT 'USD',
	status TEXT NOT NULL DEFAULT 'pending',
	memo TEXT,
	created_at TEXT NOT NULL DEFAULT (datetime('now')),
	settled_at TEXT
);

CREATE TABLE IF NOT EXISTS audit_log (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	org_id INTEGER,
	actor TEXT,
	action TEXT NOT NULL,
	detail TEXT,
	ip TEXT,
	created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
`

func initDB() {
	dataDir := "data"
	if err := os.MkdirAll(dataDir, 0o755); err != nil {
		log.Fatalf("creating data dir: %v", err)
	}

	dbPath := filepath.Join(dataDir, "payments.sqlite")
	var err error
	db, err = sql.Open("sqlite", dbPath)
	if err != nil {
		log.Fatalf("opening database: %v", err)
	}

	if _, err := db.Exec(schema); err != nil {
		log.Fatalf("applying schema: %v", err)
	}

	seed()
}

func seed() {
	var count int
	if err := db.QueryRow("SELECT COUNT(*) FROM organizations").Scan(&count); err != nil {
		log.Fatalf("counting organizations: %v", err)
	}
	if count > 0 {
		return
	}

	res, err := db.Exec("INSERT INTO organizations (name, plan) VALUES (?, ?)", "Acme Manufacturing", "enterprise")
	if err != nil {
		log.Fatalf("seeding organizations: %v", err)
	}
	acmeID, _ := res.LastInsertId()

	res, err = db.Exec("INSERT INTO organizations (name, plan) VALUES (?, ?)", "Globex Trading", "standard")
	if err != nil {
		log.Fatalf("seeding organizations: %v", err)
	}
	globexID, _ := res.LastInsertId()

	// API keys are secrets: read them from the environment (or a managed
	// secret store) at runtime instead of committing live key material. If a
	// key is not configured the seed skips it rather than falling back to a
	// hardcoded credential.
	if acmeKey := os.Getenv("ACME_API_KEY"); acmeKey != "" {
		db.Exec("INSERT INTO api_keys (api_key, org_id, role, label) VALUES (?, ?, ?, ?)",
			acmeKey, acmeID, "admin", "Acme treasury ops key")
	}
	if globexKey := os.Getenv("GLOBEX_API_KEY"); globexKey != "" {
		db.Exec("INSERT INTO api_keys (api_key, org_id, role, label) VALUES (?, ?, ?, ?)",
			globexKey, globexID, "admin", "Globex finance key")
	}

	accRes, err := db.Exec("INSERT INTO accounts (org_id, name, currency, balance_cents, status) VALUES (?, ?, ?, ?, ?)",
		acmeID, "Acme Operating Account", "USD", 500000000, "active")
	if err != nil {
		log.Fatalf("seeding accounts: %v", err)
	}
	acmeAccID, _ := accRes.LastInsertId()

	globexAccRes, err := db.Exec("INSERT INTO accounts (org_id, name, currency, balance_cents, status) VALUES (?, ?, ?, ?, ?)",
		globexID, "Globex Trading Account", "EUR", 250000000, "active")
	if err != nil {
		log.Fatalf("seeding accounts: %v", err)
	}
	globexAccID, _ := globexAccRes.LastInsertId()

	benRes, err := db.Exec("INSERT INTO beneficiaries (org_id, name, bank_name, iban, status) VALUES (?, ?, ?, ?, ?)",
		acmeID, "Northwind Supplies", "First Union Bank", "GB29NWBK60161331926819", "active")
	if err != nil {
		log.Fatalf("seeding beneficiaries: %v", err)
	}
	northwindID, _ := benRes.LastInsertId()

	db.Exec(`INSERT INTO transfers (org_id, account_id, beneficiary_id, amount_cents, currency, status, memo)
		VALUES (?, ?, ?, ?, ?, ?, ?)`,
		acmeID, acmeAccID, northwindID, 340000, "USD", "pending", "Q3 supplies invoice 4472")
	db.Exec(`INSERT INTO transfers (org_id, account_id, beneficiary_id, amount_cents, currency, status, memo)
		VALUES (?, ?, ?, ?, ?, ?, ?)`,
		globexID, globexAccID, nil, 1850000, "EUR", "pending", "Freight contract milestone 2")
}