package main

import (
	"crypto/rand"
	"crypto/sha256"
	"database/sql"
	"encoding/base64"
	"encoding/hex"
	"encoding/json"
	"errors"
	"strconv"
	"time"

	"github.com/go-webauthn/webauthn/webauthn"
	_ "modernc.org/sqlite"
)

// sqliteStore is vaultStore implementation #1 — the zero-config default. The
// home server is content-blind: the one blob per user is opaque,
// client-encrypted bytes; credentials are WebAuthn public keys (useless
// without the authenticator); tokens are stored hashed. There is deliberately
// no email, username, or display name anywhere.
//
// Versions are integer rowversions internally but rendered as opaque strings
// at the vaultStore boundary — the interface (and the wire) never learns
// they're integers.
type sqliteStore struct{ sql *sql.DB }

var _ vaultStore = (*sqliteStore)(nil)

func openSQLiteStore(path string) (*sqliteStore, error) {
	d, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}
	// One writer at a time keeps WAL happy and avoids "database is locked".
	d.SetMaxOpenConns(1)
	for _, p := range []string{
		"PRAGMA journal_mode=WAL",
		"PRAGMA busy_timeout=5000",
		"PRAGMA synchronous=NORMAL",
		"PRAGMA foreign_keys=ON",
	} {
		if _, err := d.Exec(p); err != nil {
			return nil, err
		}
	}
	db := &sqliteStore{sql: d}
	if err := db.migrate(); err != nil {
		return nil, err
	}
	return db, nil
}

func (db *sqliteStore) migrate() error {
	_, err := db.sql.Exec(`
CREATE TABLE IF NOT EXISTS users (
  id         TEXT PRIMARY KEY,        -- random; the only identity there is
  created_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS credentials (
  cred_id    TEXT PRIMARY KEY,        -- base64url WebAuthn credential id
  user_id    TEXT NOT NULL,
  credential TEXT NOT NULL,           -- webauthn.Credential JSON (public key, sign count, flags)
  created_at INTEGER NOT NULL
);
-- One logical blob per user, kept as its last few versions (overwrite
-- protection). The latest version is the blob; older rows are history.
CREATE TABLE IF NOT EXISTS blobs (
  user_id    TEXT NOT NULL,
  version    INTEGER NOT NULL,
  data       BLOB NOT NULL,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, version)
);
-- Device link tokens: bearer credentials minted after a verified ceremony.
-- Only the hash is stored (cf. device_tokens in the messenger) — a stolen DB
-- yields no live credential. id is a separate random handle so the devices
-- list can name a token without revealing anything derived from it.
CREATE TABLE IF NOT EXISTS link_tokens (
  id         TEXT PRIMARY KEY,
  token_hash TEXT NOT NULL UNIQUE,
  user_id    TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  last_seen  INTEGER NOT NULL
);
-- RETIRED: ceremony proofs became stateless HMAC tokens (stateless.go) —
-- nothing reads or writes this table any more. It stays declared because
-- migrations are additive-only; any leftover rows are expired and inert.
CREATE TABLE IF NOT EXISTS ceremony_proofs (
  proof_hash TEXT PRIMARY KEY,
  user_id    TEXT NOT NULL,
  created_at INTEGER NOT NULL
);
-- Sign in with Apple anchors (siwa.go): the HMAC(pepper, sub) hash is the
-- only trace of the Apple identity — nothing mailable, nothing reversible.
CREATE TABLE IF NOT EXISTS siwa_anchors (
  sub_hash   TEXT PRIMARY KEY,
  user_id    TEXT NOT NULL,
  created_at INTEGER NOT NULL
);
-- Email anchors (email.go): only the blind index — HMAC(pepper, address) —
-- is ever stored; no plaintext address exists anywhere at rest.
CREATE TABLE IF NOT EXISTS email_anchors (
  email_hash TEXT PRIMARY KEY,
  user_id    TEXT NOT NULL,
  created_at INTEGER NOT NULL
);
-- OIDC provider anchors (oidc.go): siwa_anchors generalized — one row per
-- (provider, HMAC(provider pepper, sub)). Apple deliberately STAYS in
-- siwa_anchors (deployed wire compat); these rows are the newer doors
-- (google, microsoft).
CREATE TABLE IF NOT EXISTS oidc_anchors (
  provider   TEXT NOT NULL,
  sub_hash   TEXT NOT NULL,
  user_id    TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (provider, sub_hash)
);`)
	return err
}

func (s *sqliteStore) siwaUser(subHash string) (string, bool) {
	var uid string
	err := s.sql.QueryRow(`SELECT user_id FROM siwa_anchors WHERE sub_hash = ?`, subHash).Scan(&uid)
	if err != nil {
		return "", false
	}
	return uid, true
}

func (s *sqliteStore) insertSiwaAnchor(uid, subHash string) (bool, error) {
	// ON CONFLICT DO NOTHING + re-read: the insert either won the row or
	// someone holds it — same-user is idempotent, different-user is the
	// conflict the handler turns into a 409.
	if _, err := s.sql.Exec(
		`INSERT INTO siwa_anchors (sub_hash, user_id, created_at) VALUES (?, ?, ?)
		 ON CONFLICT (sub_hash) DO NOTHING`,
		subHash, uid, time.Now().Unix()); err != nil {
		return false, err
	}
	owner, ok := s.siwaUser(subHash)
	if !ok {
		return false, errors.New("siwa anchor vanished")
	}
	return owner != uid, nil
}

func (s *sqliteStore) listSiwaAnchors(userID string) ([]Anchor, error) {
	return s.listAnchorRows("SELECT sub_hash, created_at FROM siwa_anchors WHERE user_id = ? ORDER BY created_at", userID)
}

func (s *sqliteStore) deleteSiwaAnchor(userID, subHash string) (deleted, last bool, err error) {
	return s.deleteAnchorRow("siwa_anchors", "sub_hash", userID, subHash)
}

func (s *sqliteStore) emailUser(index string) (string, bool) {
	var uid string
	err := s.sql.QueryRow(`SELECT user_id FROM email_anchors WHERE email_hash = ?`, index).Scan(&uid)
	if err != nil {
		return "", false
	}
	return uid, true
}

func (s *sqliteStore) insertEmailAnchor(uid, index string) (bool, error) {
	// Same shape as insertSiwaAnchor: win the row or read who holds it.
	if _, err := s.sql.Exec(
		`INSERT INTO email_anchors (email_hash, user_id, created_at) VALUES (?, ?, ?)
		 ON CONFLICT (email_hash) DO NOTHING`,
		index, uid, time.Now().Unix()); err != nil {
		return false, err
	}
	owner, ok := s.emailUser(index)
	if !ok {
		return false, errors.New("email anchor vanished")
	}
	return owner != uid, nil
}

func (s *sqliteStore) listEmailAnchors(userID string) ([]Anchor, error) {
	return s.listAnchorRows("SELECT email_hash, created_at FROM email_anchors WHERE user_id = ? ORDER BY created_at", userID)
}

func (s *sqliteStore) deleteEmailAnchor(userID, index string) (deleted, last bool, err error) {
	return s.deleteAnchorRow("email_anchors", "email_hash", userID, index)
}

// The OIDC anchor methods mirror the siwa set with the provider name beside
// the hash — the same row shape, one more key column.

func (s *sqliteStore) oidcUser(provider, subHash string) (string, bool) {
	var uid string
	err := s.sql.QueryRow(`SELECT user_id FROM oidc_anchors WHERE provider = ? AND sub_hash = ?`, provider, subHash).Scan(&uid)
	if err != nil {
		return "", false
	}
	return uid, true
}

func (s *sqliteStore) insertOidcAnchor(uid, provider, subHash string) (bool, error) {
	// Same shape as insertSiwaAnchor: win the row or read who holds it.
	if _, err := s.sql.Exec(
		`INSERT INTO oidc_anchors (provider, sub_hash, user_id, created_at) VALUES (?, ?, ?, ?)
		 ON CONFLICT (provider, sub_hash) DO NOTHING`,
		provider, subHash, uid, time.Now().Unix()); err != nil {
		return false, err
	}
	owner, ok := s.oidcUser(provider, subHash)
	if !ok {
		return false, errors.New("oidc anchor vanished")
	}
	return owner != uid, nil
}

func (s *sqliteStore) listOidcAnchors(userID, provider string) ([]Anchor, error) {
	return s.listAnchorRows("SELECT sub_hash, created_at FROM oidc_anchors WHERE user_id = ? AND provider = ? ORDER BY created_at", userID, provider)
}

// deleteOidcAnchor is deleteAnchorRow with the provider in the predicate —
// the same transaction-atomic last-method guard.
func (s *sqliteStore) deleteOidcAnchor(userID, provider, subHash string) (deleted, last bool, err error) {
	tx, err := s.sql.Begin()
	if err != nil {
		return false, false, err
	}
	defer tx.Rollback()
	var owned int
	e := tx.QueryRow("SELECT 1 FROM oidc_anchors WHERE user_id = ? AND provider = ? AND sub_hash = ?",
		userID, provider, subHash).Scan(&owned)
	if e == sql.ErrNoRows {
		return false, false, nil
	}
	if e != nil {
		return false, false, e
	}
	n, err := signInMethods(tx, userID)
	if err != nil {
		return false, false, err
	}
	if n <= 1 {
		return false, true, nil
	}
	if _, err := tx.Exec("DELETE FROM oidc_anchors WHERE user_id = ? AND provider = ? AND sub_hash = ?",
		userID, provider, subHash); err != nil {
		return false, false, err
	}
	return true, false, tx.Commit()
}

// listAnchorRows runs one of the fixed (id, created_at) listing queries above
// — the anchor listings differ only in table and key column(s).
func (s *sqliteStore) listAnchorRows(query string, args ...any) ([]Anchor, error) {
	rows, err := s.sql.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []Anchor{}
	for rows.Next() {
		var a Anchor
		if err := rows.Scan(&a.ID, &a.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, a)
	}
	return out, rows.Err()
}

// deleteAnchorRow removes one identity anchor (siwa or email — table and key
// column are in-code constants from the two callers above, never input) under
// the same transaction-atomic last-method guard as deleteCredential.
func (s *sqliteStore) deleteAnchorRow(table, keyCol, userID, hash string) (deleted, last bool, err error) {
	tx, err := s.sql.Begin()
	if err != nil {
		return false, false, err
	}
	defer tx.Rollback()
	var owned int
	e := tx.QueryRow("SELECT 1 FROM "+table+" WHERE user_id = ? AND "+keyCol+" = ?", userID, hash).Scan(&owned)
	if e == sql.ErrNoRows {
		return false, false, nil
	}
	if e != nil {
		return false, false, e
	}
	n, err := signInMethods(tx, userID)
	if err != nil {
		return false, false, err
	}
	if n <= 1 {
		return false, true, nil
	}
	if _, err := tx.Exec("DELETE FROM "+table+" WHERE user_id = ? AND "+keyCol+" = ?", userID, hash); err != nil {
		return false, false, err
	}
	return true, false, tx.Commit()
}

// mergeAccounts (#1098) folds the absorbed account into the survivor in ONE
// transaction: every credential, link token, and anchor row repoints its
// user_id to the survivor, then the absorbed user row goes. Repointing can't
// collide — each table's key column (cred_id, token_hash, the anchor hashes)
// is unique across ALL accounts, and the absorbed account by definition holds
// keys the survivor doesn't. The blob check inside the transaction is the
// store-side belt for the handler's precondition: the client empties the
// absorbed vault first, so merge itself destroys nothing but the empty shell.
func (db *sqliteStore) mergeAccounts(survivor, absorbed string) (mergeMoved, error) {
	var moved mergeMoved
	if survivor == absorbed {
		// Belt: the handler 400s this, but a same-account "merge" reaching the
		// store would delete the account's own user row below.
		return moved, errors.New("merge: survivor and absorbed are the same account")
	}
	tx, err := db.sql.Begin()
	if err != nil {
		return moved, err
	}
	defer tx.Rollback()
	var blobs int
	if err := tx.QueryRow("SELECT COUNT(*) FROM blobs WHERE user_id = ?", absorbed).Scan(&blobs); err != nil {
		return moved, err
	}
	if blobs > 0 {
		return moved, errAbsorbedHasBlob
	}
	// created_at rides along untouched everywhere — a merged anchor keeps its
	// original enrolment time.
	move := func(table string) (int, error) {
		res, err := tx.Exec("UPDATE "+table+" SET user_id = ? WHERE user_id = ?", survivor, absorbed)
		if err != nil {
			return 0, err
		}
		n, err := res.RowsAffected()
		return int(n), err
	}
	for _, table := range []string{"siwa_anchors", "email_anchors", "oidc_anchors"} {
		n, err := move(table)
		if err != nil {
			return mergeMoved{}, err
		}
		moved.Anchors += n
	}
	if moved.Passkeys, err = move("credentials"); err != nil {
		return mergeMoved{}, err
	}
	if moved.Devices, err = move("link_tokens"); err != nil {
		return mergeMoved{}, err
	}
	if _, err := tx.Exec("DELETE FROM users WHERE id = ?", absorbed); err != nil {
		return mergeMoved{}, err
	}
	return moved, tx.Commit()
}

// signInMethods counts every way into the account — passkeys plus SiwA,
// email, and OIDC-provider anchors — inside the caller's transaction, so the
// last-method guard is atomic with the delete it protects.
func signInMethods(tx *sql.Tx, userID string) (int, error) {
	var n int
	err := tx.QueryRow(`SELECT
		  (SELECT COUNT(*) FROM credentials    WHERE user_id = ?)
		+ (SELECT COUNT(*) FROM siwa_anchors   WHERE user_id = ?)
		+ (SELECT COUNT(*) FROM email_anchors  WHERE user_id = ?)
		+ (SELECT COUNT(*) FROM oidc_anchors   WHERE user_id = ?)`,
		userID, userID, userID, userID).Scan(&n)
	return n, err
}

func hashToken(t string) string {
	h := sha256.Sum256([]byte(t))
	return hex.EncodeToString(h[:])
}

func randToken() (string, error) {
	b := make([]byte, 32)
	if _, err := rand.Read(b); err != nil {
		return "", err
	}
	return base64.RawURLEncoding.EncodeToString(b), nil
}

// ---- users + credentials --------------------------------------------------

func (db *sqliteStore) createUser(id string) error {
	_, err := db.sql.Exec("INSERT INTO users(id, created_at) VALUES(?, ?)", id, time.Now().Unix())
	return err
}

func (db *sqliteStore) userExists(id string) bool {
	var x int
	return db.sql.QueryRow("SELECT 1 FROM users WHERE id = ?", id).Scan(&x) == nil
}

// insertCredential stores a verified WebAuthn credential as the library's own
// JSON — public key, sign count, and flags travel together, so upgrades that
// add fields stay additive.
func (db *sqliteStore) insertCredential(userID string, cred *webauthn.Credential) error {
	blob, err := json.Marshal(cred)
	if err != nil {
		return err
	}
	_, err = db.sql.Exec(
		"INSERT INTO credentials(cred_id, user_id, credential, created_at) VALUES(?, ?, ?, ?)",
		base64.RawURLEncoding.EncodeToString(cred.ID), userID, string(blob), time.Now().Unix(),
	)
	return err
}

// updateCredential persists post-ceremony state (sign count, backup flags).
func (db *sqliteStore) updateCredential(cred *webauthn.Credential) error {
	blob, err := json.Marshal(cred)
	if err != nil {
		return err
	}
	_, err = db.sql.Exec(
		"UPDATE credentials SET credential = ? WHERE cred_id = ?",
		string(blob), base64.RawURLEncoding.EncodeToString(cred.ID),
	)
	return err
}

func (db *sqliteStore) credentialsFor(userID string) ([]webauthn.Credential, error) {
	rows, err := db.sql.Query("SELECT credential FROM credentials WHERE user_id = ? ORDER BY created_at", userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []webauthn.Credential{}
	for rows.Next() {
		var blob string
		if err := rows.Scan(&blob); err != nil {
			return nil, err
		}
		var c webauthn.Credential
		if err := json.Unmarshal([]byte(blob), &c); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// credentialUser resolves a credential id to its user — the creds/<credId>
// pointer read. In SQLite the pointer and the credential are one row, so
// resolution and the "still listed" check coincide.
func (db *sqliteStore) credentialUser(credID string) (string, bool) {
	var uid string
	err := db.sql.QueryRow("SELECT user_id FROM credentials WHERE cred_id = ?", credID).Scan(&uid)
	return uid, err == nil
}

// deleteCredential removes one of a user's passkeys (a rogue or lost one).
// It refuses to remove the account's LAST sign-in method — passkeys plus
// siwa/email anchors (#1089 evolved the guard from last-passkey to
// last-method): an account keeping an anchor may shed its final passkey, but
// no removal may leave zero ways in while the blob stays live on the server —
// the explicit path for "I'm done" is the ceremony-gated blob delete.
// deleted=false with last=false means the id is unknown (or someone else's).
func (db *sqliteStore) deleteCredential(userID, credID string) (deleted, last bool, err error) {
	tx, err := db.sql.Begin()
	if err != nil {
		return false, false, err
	}
	defer tx.Rollback()
	var owned int
	e := tx.QueryRow("SELECT 1 FROM credentials WHERE user_id = ? AND cred_id = ?", userID, credID).Scan(&owned)
	if e == sql.ErrNoRows {
		return false, false, nil
	}
	if e != nil {
		return false, false, e
	}
	n, err := signInMethods(tx, userID)
	if err != nil {
		return false, false, err
	}
	if n <= 1 {
		return false, true, nil
	}
	if _, err := tx.Exec("DELETE FROM credentials WHERE user_id = ? AND cred_id = ?", userID, credID); err != nil {
		return false, false, err
	}
	return true, false, tx.Commit()
}

// listCredentialMeta returns passkey listing metadata (base64url id +
// enrolment time), enrolment order — the anchors list needs no public keys.
func (db *sqliteStore) listCredentialMeta(userID string) ([]Anchor, error) {
	return db.listAnchorRows("SELECT cred_id, created_at FROM credentials WHERE user_id = ? ORDER BY created_at", userID)
}

// ---- the blob ---------------------------------------------------------------

// getBlob returns the latest version of a user's blob.
func (db *sqliteStore) getBlob(userID string) (data []byte, version string, found bool, err error) {
	var v int64
	err = db.sql.QueryRow(
		"SELECT data, version FROM blobs WHERE user_id = ? ORDER BY version DESC LIMIT 1", userID,
	).Scan(&data, &v)
	if err == sql.ErrNoRows {
		return nil, "", false, nil
	}
	if err != nil {
		return nil, "", false, err
	}
	return data, strconv.FormatInt(v, 10), true, nil
}

// putBlob writes the next version if ifMatch equals the current one ("0" = no
// blob yet). On mismatch it reports conflict=true with the current version so
// the caller can 409 + ETag; the client merges and retries. Old versions past
// blobKeepVersions are pruned. A version string that was never one of ours
// (non-numeric, negative) is errBadVersion, not a conflict.
func (db *sqliteStore) putBlob(userID string, data []byte, ifMatch string) (version string, conflict bool, err error) {
	want, err := strconv.ParseInt(ifMatch, 10, 64)
	if err != nil || want < 0 {
		return "", false, errBadVersion
	}
	tx, err := db.sql.Begin()
	if err != nil {
		return "", false, err
	}
	defer tx.Rollback()
	var cur int64
	if err := tx.QueryRow("SELECT COALESCE(MAX(version), 0) FROM blobs WHERE user_id = ?", userID).Scan(&cur); err != nil {
		return "", false, err
	}
	if cur != want {
		return strconv.FormatInt(cur, 10), true, nil
	}
	next := cur + 1
	if _, err := tx.Exec(
		"INSERT INTO blobs(user_id, version, data, updated_at) VALUES(?, ?, ?, ?)",
		userID, next, data, time.Now().Unix(),
	); err != nil {
		return "", false, err
	}
	if _, err := tx.Exec(
		"DELETE FROM blobs WHERE user_id = ? AND version <= ?", userID, next-blobKeepVersions,
	); err != nil {
		return "", false, err
	}
	return strconv.FormatInt(next, 10), false, tx.Commit()
}

// listBlobVersions returns the retained versions, newest first.
func (db *sqliteStore) listBlobVersions(userID string) ([]string, error) {
	rows, err := db.sql.Query("SELECT version FROM blobs WHERE user_id = ? ORDER BY version DESC", userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []string{}
	for rows.Next() {
		var v int64
		if err := rows.Scan(&v); err != nil {
			return nil, err
		}
		out = append(out, strconv.FormatInt(v, 10))
	}
	return out, rows.Err()
}

// deleteBlob removes every version — history included, that's the point of the
// ceremony gate in front of it.
func (db *sqliteStore) deleteBlob(userID string) error {
	_, err := db.sql.Exec("DELETE FROM blobs WHERE user_id = ?", userID)
	return err
}

// ---- device link tokens -----------------------------------------------------

// createLinkToken mints a bearer token for a user and returns the plaintext
// (shown/stored client-side once; only the hash persists here).
func (db *sqliteStore) createLinkToken(userID string) (string, error) {
	tok, err := randToken()
	if err != nil {
		return "", err
	}
	id, err := randToken()
	if err != nil {
		return "", err
	}
	now := time.Now().Unix()
	_, err = db.sql.Exec(
		"INSERT INTO link_tokens(id, token_hash, user_id, created_at, last_seen) VALUES(?, ?, ?, ?, ?)",
		id, hashToken(tok), userID, now, now,
	)
	return tok, err
}

// linkTokenUser resolves a token hash to its user and bumps last_seen — but
// only when the recorded value is over a day stale. last_seen exists solely
// for the devices list, where day granularity is plenty, and on a CAS'd
// object store a write-per-read would turn every authenticated GET into a
// PUT (#550 Phase 0).
func (db *sqliteStore) linkTokenUser(tokenHash string) (string, bool) {
	var uid string
	if db.sql.QueryRow("SELECT user_id FROM link_tokens WHERE token_hash = ?", tokenHash).Scan(&uid) != nil {
		return "", false
	}
	now := time.Now().Unix()
	db.sql.Exec("UPDATE link_tokens SET last_seen = ? WHERE token_hash = ? AND last_seen < ?",
		now, tokenHash, now-lastSeenGranularity)
	return uid, true
}

func (db *sqliteStore) listDevices(userID string) ([]Device, error) {
	rows, err := db.sql.Query(
		"SELECT id, created_at, last_seen FROM link_tokens WHERE user_id = ? ORDER BY created_at", userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []Device{}
	for rows.Next() {
		var d Device
		if err := rows.Scan(&d.ID, &d.CreatedAt, &d.LastSeen); err != nil {
			return nil, err
		}
		out = append(out, d)
	}
	return out, rows.Err()
}

// linkTokenID returns the device id behind a token hash, so the devices list
// can mark "this device".
func (db *sqliteStore) linkTokenID(tokenHash string) string {
	var id string
	db.sql.QueryRow("SELECT id FROM link_tokens WHERE token_hash = ?", tokenHash).Scan(&id)
	return id
}

// deleteDevice revokes one of the user's link tokens. ok=false when the id
// doesn't exist or belongs to someone else.
func (db *sqliteStore) deleteDevice(userID, id string) (bool, error) {
	res, err := db.sql.Exec("DELETE FROM link_tokens WHERE user_id = ? AND id = ?", userID, id)
	if err != nil {
		return false, err
	}
	n, _ := res.RowsAffected()
	return n > 0, nil
}