cryptomon/internal/store/store.go

// Package store wraps the SQLite database: the ledger of buys/sells/
// deposits/withdrawals, per-currency hide/show/favourite, cached prices/
// deltas/live balances, portfolio value snapshots for charting, and
// encrypted exchange credentials.
package store

import (
	"database/sql"
	"fmt"
	"strings"
	"time"

	_ "modernc.org/sqlite"
)

const schema = `
CREATE TABLE IF NOT EXISTS credentials (
	id INTEGER PRIMARY KEY CHECK (id = 1),
	exchange TEXT NOT NULL,
	api_key_enc TEXT NOT NULL,
	api_secret_enc TEXT NOT NULL,
	updated_at TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS purchases (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	source TEXT NOT NULL,
	external_id TEXT,
	pair TEXT NOT NULL,
	currency TEXT NOT NULL,
	amount REAL NOT NULL,
	price REAL NOT NULL,
	fee REAL NOT NULL DEFAULT 0,
	purchased_at TIMESTAMP NOT NULL,
	created_at TIMESTAMP NOT NULL,
	UNIQUE(source, external_id)
);

CREATE TABLE IF NOT EXISTS currency_settings (
	currency TEXT PRIMARY KEY,
	hidden INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS price_cache (
	pair TEXT PRIMARY KEY,
	price REAL NOT NULL,
	change_4h REAL,
	change_1d REAL,
	change_7d REAL,
	change_30d REAL,
	change_all REAL,
	updated_at TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS portfolio_snapshots (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	ts TIMESTAMP NOT NULL,
	total_value REAL NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_snapshots_ts ON portfolio_snapshots(ts);

CREATE TABLE IF NOT EXISTS balance_cache (
	currency TEXT PRIMARY KEY,
	amount REAL NOT NULL,
	updated_at TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS candle_cache (
	pair TEXT NOT NULL,
	interval_minutes INTEGER NOT NULL,
	ts TIMESTAMP NOT NULL,
	open REAL NOT NULL,
	high REAL NOT NULL,
	low REAL NOT NULL,
	close REAL NOT NULL,
	PRIMARY KEY (pair, interval_minutes, ts)
);

CREATE TABLE IF NOT EXISTS auth (
	id INTEGER PRIMARY KEY CHECK (id = 1),
	username TEXT NOT NULL,
	password_hash TEXT NOT NULL,
	must_change_password INTEGER NOT NULL DEFAULT 1,
	mfa_secret_enc TEXT,
	mfa_enabled INTEGER NOT NULL DEFAULT 0,
	session_version INTEGER NOT NULL DEFAULT 1
);
`

// migrations adds columns to tables that may already exist from an earlier
// version of the schema. ALTER TABLE ADD COLUMN has no "IF NOT EXISTS" in
// SQLite, so each statement's "duplicate column" error is swallowed.
var migrations = []string{
	`ALTER TABLE purchases ADD COLUMN quote TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE currency_settings ADD COLUMN favourite INTEGER NOT NULL DEFAULT 0`,
	`ALTER TABLE purchases ADD COLUMN entry_type TEXT NOT NULL DEFAULT 'buy'`,
	`ALTER TABLE balance_cache ADD COLUMN staked REAL NOT NULL DEFAULT 0`,
}

type Store struct {
	db *sql.DB
}

func Open(path string) (*Store, error) {
	db, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}
	if _, err := db.Exec(schema); err != nil {
		db.Close()
		return nil, fmt.Errorf("init schema: %w", err)
	}
	for _, stmt := range migrations {
		if _, err := db.Exec(stmt); err != nil && !strings.Contains(err.Error(), "duplicate column") {
			db.Close()
			return nil, fmt.Errorf("migrate: %s: %w", stmt, err)
		}
	}
	return &Store{db: db}, nil
}

func (s *Store) Close() error { return s.db.Close() }

// --- credentials ---

func (s *Store) SaveCredentials(exchange, apiKeyEnc, apiSecretEnc string) error {
	_, err := s.db.Exec(`
		INSERT INTO credentials (id, exchange, api_key_enc, api_secret_enc, updated_at)
		VALUES (1, ?, ?, ?, ?)
		ON CONFLICT(id) DO UPDATE SET exchange=excluded.exchange,
			api_key_enc=excluded.api_key_enc, api_secret_enc=excluded.api_secret_enc,
			updated_at=excluded.updated_at`,
		exchange, apiKeyEnc, apiSecretEnc, time.Now())
	return err
}

// GetCredentials returns ok=false if none have been saved yet.
func (s *Store) GetCredentials() (apiKeyEnc, apiSecretEnc string, ok bool, err error) {
	row := s.db.QueryRow(`SELECT api_key_enc, api_secret_enc FROM credentials WHERE id = 1`)
	err = row.Scan(&apiKeyEnc, &apiSecretEnc)
	if err == sql.ErrNoRows {
		return "", "", false, nil
	}
	if err != nil {
		return "", "", false, err
	}
	return apiKeyEnc, apiSecretEnc, true, nil
}

// --- auth (single-user login: username/password + optional MFA) ---

type AuthRecord struct {
	Username           string
	PasswordHash       string
	MustChangePassword bool
	MFASecretEnc       string // "" if MFA never set up
	MFAEnabled         bool
	SessionVersion     int // bumped on credential change/reset to invalidate old session tokens
}

// EnsureAuth seeds the row only if none exists yet — used to create the
// default admin/admin login on first run.
func (s *Store) EnsureAuth(username, passwordHash string) error {
	_, err := s.db.Exec(`
		INSERT INTO auth (id, username, password_hash, must_change_password, session_version)
		VALUES (1, ?, ?, 1, 1)
		ON CONFLICT(id) DO NOTHING`, username, passwordHash)
	return err
}

// ResetAuth unconditionally restores the default login (used by -userreset)
// and bumps session_version so any existing signed-in cookie is invalidated.
func (s *Store) ResetAuth(username, passwordHash string) error {
	_, err := s.db.Exec(`
		INSERT INTO auth (id, username, password_hash, must_change_password, mfa_secret_enc, mfa_enabled, session_version)
		VALUES (1, ?, ?, 1, NULL, 0, 1)
		ON CONFLICT(id) DO UPDATE SET username=excluded.username, password_hash=excluded.password_hash,
			must_change_password=1, mfa_secret_enc=NULL, mfa_enabled=0, session_version=auth.session_version+1`,
		username, passwordHash)
	return err
}

func (s *Store) GetAuth() (AuthRecord, error) {
	var r AuthRecord
	var mfaSecret sql.NullString
	row := s.db.QueryRow(`SELECT username, password_hash, must_change_password, mfa_secret_enc, mfa_enabled, session_version FROM auth WHERE id = 1`)
	if err := row.Scan(&r.Username, &r.PasswordHash, &r.MustChangePassword, &mfaSecret, &r.MFAEnabled, &r.SessionVersion); err != nil {
		return AuthRecord{}, err
	}
	r.MFASecretEnc = mfaSecret.String
	return r, nil
}

// SetAuthCredentials updates username/password, clears must-change, and
// bumps session_version so every other signed-in session is invalidated.
func (s *Store) SetAuthCredentials(username, passwordHash string) error {
	_, err := s.db.Exec(`
		UPDATE auth SET username = ?, password_hash = ?, must_change_password = 0, session_version = session_version + 1
		WHERE id = 1`, username, passwordHash)
	return err
}

// SetMFAPending stores a freshly generated secret, awaiting confirmation.
func (s *Store) SetMFAPending(secretEnc string) error {
	_, err := s.db.Exec(`UPDATE auth SET mfa_secret_enc = ?, mfa_enabled = 0 WHERE id = 1`, secretEnc)
	return err
}

func (s *Store) ConfirmMFA() error {
	_, err := s.db.Exec(`UPDATE auth SET mfa_enabled = 1 WHERE id = 1`)
	return err
}

func (s *Store) DisableMFA() error {
	_, err := s.db.Exec(`UPDATE auth SET mfa_secret_enc = NULL, mfa_enabled = 0 WHERE id = 1`)
	return err
}

// --- ledger: buys, sells, deposits, withdrawals ---

// EntryType values. Buy/sell come from Kraken trades (or manual entry);
// deposit/withdrawal come from Kraken's ledger (or manual entry) and have
// no price — they only move the balance.
const (
	EntryBuy        = "buy"
	EntrySell       = "sell"
	EntryDeposit    = "deposit"
	EntryWithdrawal = "withdrawal"
)

type LedgerEntry struct {
	ID         int64
	Source     string // "kraken" | "manual"
	ExternalID string
	EntryType  string
	Pair       string // Kraken pair, e.g. XXBTZUSD; empty for deposit/withdrawal
	Quote      string // quote currency altname of Pair, e.g. "USD"; empty for deposit/withdrawal
	Currency   string
	Amount     float64 // always positive; EntryType implies the sign
	Price      float64 // per unit, in Quote; 0 for deposit/withdrawal
	Fee        float64
	OccurredAt time.Time
}

// UpsertKrakenEntry inserts a ledger entry imported from Kraken, ignoring it
// if external_id was already imported (dedup on re-sync).
func (s *Store) UpsertKrakenEntry(e LedgerEntry) error {
	_, err := s.db.Exec(`
		INSERT INTO purchases (source, external_id, entry_type, pair, quote, currency, amount, price, fee, purchased_at, created_at)
		VALUES ('kraken', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(source, external_id) DO NOTHING`,
		e.ExternalID, e.EntryType, e.Pair, e.Quote, e.Currency, e.Amount, e.Price, e.Fee, e.OccurredAt, time.Now())
	return err
}

func (s *Store) AddManualEntry(e LedgerEntry) error {
	_, err := s.db.Exec(`
		INSERT INTO purchases (source, external_id, entry_type, pair, quote, currency, amount, price, fee, purchased_at, created_at)
		VALUES ('manual', NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		e.EntryType, e.Pair, e.Quote, e.Currency, e.Amount, e.Price, e.Fee, e.OccurredAt, time.Now())
	return err
}

func (s *Store) ListEntries(currency string) ([]LedgerEntry, error) {
	rows, err := s.db.Query(`
		SELECT id, source, COALESCE(external_id, ''), entry_type, pair, quote, currency, amount, price, fee, purchased_at
		FROM purchases WHERE currency = ? ORDER BY purchased_at DESC`, currency)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanEntries(rows)
}

func (s *Store) ListAllEntries() ([]LedgerEntry, error) {
	rows, err := s.db.Query(`
		SELECT id, source, COALESCE(external_id, ''), entry_type, pair, quote, currency, amount, price, fee, purchased_at
		FROM purchases ORDER BY purchased_at DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanEntries(rows)
}

// EntriesMissingQuote returns buy/sell entries saved before the quote
// column existed, so it can be backfilled without a live Kraken lookup per
// request.
func (s *Store) EntriesMissingQuote() ([]LedgerEntry, error) {
	rows, err := s.db.Query(`
		SELECT id, source, COALESCE(external_id, ''), entry_type, pair, quote, currency, amount, price, fee, purchased_at
		FROM purchases WHERE quote = '' AND pair != ''`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanEntries(rows)
}

func (s *Store) UpdateEntryQuote(id int64, quote string) error {
	_, err := s.db.Exec(`UPDATE purchases SET quote = ? WHERE id = ?`, quote, id)
	return err
}

func scanEntries(rows *sql.Rows) ([]LedgerEntry, error) {
	var out []LedgerEntry
	for rows.Next() {
		var e LedgerEntry
		if err := rows.Scan(&e.ID, &e.Source, &e.ExternalID, &e.EntryType, &e.Pair, &e.Quote, &e.Currency, &e.Amount, &e.Price, &e.Fee, &e.OccurredAt); err != nil {
			return nil, err
		}
		out = append(out, e)
	}
	return out, rows.Err()
}

// ListCurrencies returns every distinct currency with at least one ledger entry.
func (s *Store) ListCurrencies() ([]string, error) {
	rows, err := s.db.Query(`SELECT DISTINCT currency FROM purchases ORDER BY currency`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var c string
		if err := rows.Scan(&c); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// RenameCurrency merges every ledger entry under `from` into `to` — used to
// retroactively fix rows saved under a raw staked-asset code (e.g.
// "ETH2.S") by an older build, before Balance()/Ledgers() normalized them.
// currency_settings/balance_cache rows for `from` are dropped rather than
// merged (currency is their primary key); they get repopulated on the next
// sync/interaction anyway.
func (s *Store) RenameCurrency(from, to string) error {
	if _, err := s.db.Exec(`UPDATE purchases SET currency = ? WHERE currency = ?`, to, from); err != nil {
		return err
	}
	if _, err := s.db.Exec(`DELETE FROM currency_settings WHERE currency = ?`, from); err != nil {
		return err
	}
	if _, err := s.db.Exec(`DELETE FROM balance_cache WHERE currency = ?`, from); err != nil {
		return err
	}
	return nil
}

// --- currency visibility / favourites ---

type CurrencyFlags struct {
	Hidden    bool
	Favourite bool
}

func (s *Store) SetHidden(currency string, hidden bool) error {
	_, err := s.db.Exec(`
		INSERT INTO currency_settings (currency, hidden, favourite) VALUES (?, ?, 0)
		ON CONFLICT(currency) DO UPDATE SET hidden=excluded.hidden`, currency, hidden)
	return err
}

func (s *Store) SetFavourite(currency string, favourite bool) error {
	_, err := s.db.Exec(`
		INSERT INTO currency_settings (currency, hidden, favourite) VALUES (?, 0, ?)
		ON CONFLICT(currency) DO UPDATE SET favourite=excluded.favourite`, currency, favourite)
	return err
}

func (s *Store) CurrencyFlags() (map[string]CurrencyFlags, error) {
	rows, err := s.db.Query(`SELECT currency, hidden, favourite FROM currency_settings`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[string]CurrencyFlags{}
	for rows.Next() {
		var c string
		var f CurrencyFlags
		if err := rows.Scan(&c, &f.Hidden, &f.Favourite); err != nil {
			return nil, err
		}
		out[c] = f
	}
	return out, rows.Err()
}

// --- price cache (also holds synthetic "FX:<CODE>" rows for FX rates) ---

type PriceCache struct {
	Pair      string
	Price     float64
	Change4h  sql.NullFloat64
	Change1d  sql.NullFloat64
	Change7d  sql.NullFloat64
	Change30d sql.NullFloat64
	ChangeAll sql.NullFloat64
	UpdatedAt time.Time
}

func (s *Store) UpsertPriceCache(pc PriceCache) error {
	_, err := s.db.Exec(`
		INSERT INTO price_cache (pair, price, change_4h, change_1d, change_7d, change_30d, change_all, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(pair) DO UPDATE SET price=excluded.price, change_4h=excluded.change_4h,
			change_1d=excluded.change_1d, change_7d=excluded.change_7d, change_30d=excluded.change_30d,
			change_all=excluded.change_all, updated_at=excluded.updated_at`,
		pc.Pair, pc.Price, pc.Change4h, pc.Change1d, pc.Change7d, pc.Change30d, pc.ChangeAll, pc.UpdatedAt)
	return err
}

func (s *Store) GetAllPriceCache() (map[string]PriceCache, error) {
	rows, err := s.db.Query(`SELECT pair, price, change_4h, change_1d, change_7d, change_30d, change_all, updated_at FROM price_cache`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[string]PriceCache{}
	for rows.Next() {
		var pc PriceCache
		if err := rows.Scan(&pc.Pair, &pc.Price, &pc.Change4h, &pc.Change1d, &pc.Change7d, &pc.Change30d, &pc.ChangeAll, &pc.UpdatedAt); err != nil {
			return nil, err
		}
		out[pc.Pair] = pc
	}
	return out, rows.Err()
}

// --- balance cache (live Kraken balance, authoritative when connected) ---

type BalanceRow struct {
	Amount float64 // liquid + staked combined
	Staked float64
}

func (s *Store) UpsertBalance(currency string, amount, staked float64, updatedAt time.Time) error {
	_, err := s.db.Exec(`
		INSERT INTO balance_cache (currency, amount, staked, updated_at) VALUES (?, ?, ?, ?)
		ON CONFLICT(currency) DO UPDATE SET amount=excluded.amount, staked=excluded.staked, updated_at=excluded.updated_at`,
		currency, amount, staked, updatedAt)
	return err
}

func (s *Store) GetAllBalances() (map[string]BalanceRow, error) {
	rows, err := s.db.Query(`SELECT currency, amount, staked FROM balance_cache`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[string]BalanceRow{}
	for rows.Next() {
		var c string
		var b BalanceRow
		if err := rows.Scan(&c, &b.Amount, &b.Staked); err != nil {
			return nil, err
		}
		out[c] = b
	}
	return out, rows.Err()
}

// --- candle cache (OHLC history, so repeated chart/timeframe requests
// don't re-hit Kraken and risk its rate limit) ---

type Candle struct {
	Time                   time.Time
	Open, High, Low, Close float64
}

// UpsertCandles stores/replaces a batch of candles for one pair+interval.
func (s *Store) UpsertCandles(pair string, intervalMinutes int, candles []Candle) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	stmt, err := tx.Prepare(`
		INSERT INTO candle_cache (pair, interval_minutes, ts, open, high, low, close)
		VALUES (?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(pair, interval_minutes, ts) DO UPDATE SET
			open=excluded.open, high=excluded.high, low=excluded.low, close=excluded.close`)
	if err != nil {
		tx.Rollback()
		return err
	}
	defer stmt.Close()
	for _, c := range candles {
		if _, err := stmt.Exec(pair, intervalMinutes, c.Time, c.Open, c.High, c.Low, c.Close); err != nil {
			tx.Rollback()
			return err
		}
	}
	return tx.Commit()
}

func (s *Store) GetCandles(pair string, intervalMinutes int, since time.Time) ([]Candle, error) {
	rows, err := s.db.Query(`
		SELECT ts, open, high, low, close FROM candle_cache
		WHERE pair = ? AND interval_minutes = ? AND ts >= ?
		ORDER BY ts ASC`, pair, intervalMinutes, since)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Candle
	for rows.Next() {
		var c Candle
		if err := rows.Scan(&c.Time, &c.Open, &c.High, &c.Low, &c.Close); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// LatestCandleTime and EarliestCandleTime report the cached range for a
// pair+interval, so the caller can tell whether it needs to top up recent
// candles, backfill older ones, or can serve entirely from cache.
func (s *Store) LatestCandleTime(pair string, intervalMinutes int) (time.Time, bool, error) {
	return candleBound(s, "DESC", pair, intervalMinutes)
}

func (s *Store) EarliestCandleTime(pair string, intervalMinutes int) (time.Time, bool, error) {
	return candleBound(s, "ASC", pair, intervalMinutes)
}

// candleBound reads the newest/oldest cached ts via ORDER BY + LIMIT 1
// rather than MAX(ts)/MIN(ts) — modernc.org/sqlite doesn't give an
// aggregate result the same time.Time scan treatment as a plain column
// select, so MAX(ts) into a *time.Time fails to scan.
func candleBound(s *Store, order, pair string, intervalMinutes int) (time.Time, bool, error) {
	var t time.Time
	err := s.db.QueryRow(
		fmt.Sprintf(`SELECT ts FROM candle_cache WHERE pair = ? AND interval_minutes = ? ORDER BY ts %s LIMIT 1`, order),
		pair, intervalMinutes).Scan(&t)
	if err == sql.ErrNoRows {
		return time.Time{}, false, nil
	}
	if err != nil {
		return time.Time{}, false, err
	}
	return t, true, nil
}

// --- portfolio value snapshots (for the "value over time" chart) ---

type Snapshot struct {
	Time  time.Time
	Value float64
}

func (s *Store) InsertSnapshot(ts time.Time, totalValue float64) error {
	_, err := s.db.Exec(`INSERT INTO portfolio_snapshots (ts, total_value) VALUES (?, ?)`, ts, totalValue)
	return err
}

func (s *Store) SnapshotsSince(since time.Time) ([]Snapshot, error) {
	rows, err := s.db.Query(`SELECT ts, total_value FROM portfolio_snapshots WHERE ts >= ? ORDER BY ts ASC`, since)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Snapshot
	for rows.Next() {
		var sn Snapshot
		if err := rows.Scan(&sn.Time, &sn.Value); err != nil {
			return nil, err
		}
		out = append(out, sn)
	}
	return out, rows.Err()
}