// Copyright 2026 printing-press-golden. Licensed under Apache-2.0. See LICENSE.
// Generated by CLI Printing Press (https://github.com/mvanhorn/cli-printing-press). DO NOT EDIT.

// Package store provides local SQLite persistence for sync-walker-golden-pp-cli.
// Uses modernc.org/sqlite (pure Go, no CGO) for zero-dependency cross-compilation.
// FTS5 full-text search indexes are created for searchable content.
package store

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"os"
	"path/filepath"
	"regexp"
	"strings"
	"sync"
	"time"

	_ "modernc.org/sqlite"
)

var uuidPattern = regexp.MustCompile(`^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$`)

// validIdentifierRE pins ListField's `field` argument to a safe SQL
// identifier shape before any Sprintf interpolation. Matches what
// pragma_table_info implicitly enforces on the primary path, so the
// fallback path inherits the same defense without depending on whether
// the parent's typed domain table exists at the moment of the lookup.
var validIdentifierRE = regexp.MustCompile(`^[a-zA-Z_][a-zA-Z0-9_]*$`)

// IsUUID returns true if the input looks like a UUID.
func IsUUID(s string) bool {
	return uuidPattern.MatchString(s)
}

// StoreSchemaVersion is the on-disk schema version this binary understands.
// It is stamped into SQLite's PRAGMA user_version on fresh databases and
// checked on every open. Bump this whenever a migration changes table
// shape — adding columns, dropping indexes, changing FTS5 tokenizers —
// so an older binary refuses to open a newer database rather than silently
// producing wrong results against a schema it cannot read.
const StoreSchemaVersion = 2

const resourcesFTSCreateSQL = `CREATE VIRTUAL TABLE IF NOT EXISTS resources_fts USING fts5(
	id, resource_type, content, tokenize='porter unicode61'
)`

type Store struct {
	db *sql.DB
	// writeMu serializes all DB writes. Read paths bypass the lock and run
	// concurrently against WAL. Resource-level concurrency in sync.go.tmpl
	// is 1 (one goroutine per resource via len(resources)-sized work channel)
	// — read-then-write sequences (e.g., GetSyncCursor → SaveSyncState) are
	// race-free by construction within a resource.
	writeMu sync.Mutex
	path    string
}

// Open opens or creates the SQLite store at dbPath using the background
// context. Prefer OpenWithContext from a Cobra command so SIGINT during
// a slow migration interrupts the open instead of stranding the caller.
func Open(dbPath string) (*Store, error) {
	return OpenWithContext(context.Background(), dbPath)
}

// OpenReadOnly opens an existing SQLite store at dbPath in read-only mode.
// mode=ro rejects direct and CTE-wrapped writes (INSERT, UPDATE, DELETE,
// REPLACE, "WITH x AS (...) INSERT ...") at the driver level. Skips
// MkdirAll and migrate; the file is expected to exist.
//
// The file: URI prefix is load-bearing: modernc.org/sqlite only honors
// SQLite's URI query parameters (mode, cache, etc.) when the DSN starts
// with "file:". Without the prefix, "?mode=ro" is silently dropped and
// the connection opens read-write. Underscore-prefixed driver pragmas
// (_journal_mode, _busy_timeout, etc.) work either way; they're parsed
// out of the DSN by the driver before sqlite3_open_v2.
func OpenReadOnly(dbPath string) (*Store, error) {
	db, err := sql.Open("sqlite", "file:"+dbPath+"?mode=ro&_journal_mode=WAL&_busy_timeout=5000&_foreign_keys=ON&_temp_store=MEMORY&_mmap_size=268435456")
	if err != nil {
		return nil, fmt.Errorf("opening database (read-only): %w", err)
	}
	db.SetMaxOpenConns(2)
	return &Store{db: db, path: dbPath}, nil
}

// OpenWithContext opens or creates the SQLite store at dbPath. The
// context is honored by the migration path: cancellation interrupts the
// retry-on-SQLITE_BUSY loop and propagates ctx.Err() back to the caller
// instead of waiting out the full migrationLockTimeout.
func OpenWithContext(ctx context.Context, dbPath string) (*Store, error) {
	if err := os.MkdirAll(filepath.Dir(dbPath), 0o755); err != nil {
		return nil, fmt.Errorf("creating db directory: %w", err)
	}

	db, err := sql.Open("sqlite", dbPath+"?_journal_mode=WAL&_synchronous=NORMAL&_busy_timeout=5000&_foreign_keys=ON&_temp_store=MEMORY&_mmap_size=268435456")
	if err != nil {
		return nil, fmt.Errorf("opening database: %w", err)
	}

	// WAL mode + 2 connections allows one read cursor open while a second
	// query executes (e.g., analytics commands calling helpers during row
	// iteration). Writes are still serialized by SQLite's WAL lock.
	db.SetMaxOpenConns(2)

	s := &Store{db: db, path: dbPath}
	if err := s.migrate(ctx); err != nil {
		db.Close()
		return nil, fmt.Errorf("running migrations: %w", err)
	}

	return s, nil
}

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

// Path returns the on-disk path of the backing SQLite file.
func (s *Store) Path() string {
	return s.path
}

// DB exposes the underlying *sql.DB for callers that need to run ad-hoc
// queries (e.g., doctor's cache inspection, share snapshot import).
// Callers must not call Close on the returned handle.
func (s *Store) DB() *sql.DB {
	return s.db
}

// SchemaVersion reads PRAGMA user_version, which is stamped by migrate().
// A zero value means the database predates the schema-version gate — not
// a bug, but the caller may want to warn.
func (s *Store) SchemaVersion() (int, error) {
	var v int
	if err := s.db.QueryRow(`PRAGMA user_version`).Scan(&v); err != nil {
		return 0, fmt.Errorf("read user_version: %w", err)
	}
	return v, nil
}

// ensureColumn adds a column to an existing table if it isn't already
// present. It is the upgrade-path safety valve for schema additions:
// CREATE TABLE IF NOT EXISTS is a no-op when the table already exists, so
// columns added by newer binaries (e.g. parent_id from the dependent-
// resources work) never land on databases created by older binaries —
// which then trip "no such column" when a follow-on CREATE INDEX runs.
//
// Skips silently if the table doesn't yet exist (fresh install — the
// CREATE TABLE migration will create it with the column already declared)
// or if the column already exists. Runs on the pinned migration
// connection so it sees the writes performed by the in-flight BEGIN
// IMMEDIATE transaction; using s.db here would route through the pool
// and BUSY against the holding writer under concurrent migrators.
func (s *Store) ensureColumn(ctx context.Context, conn *sql.Conn, table, column, decl string) error {
	var name string
	err := conn.QueryRowContext(ctx,
		`SELECT name FROM sqlite_master WHERE type='table' AND name=?`, table,
	).Scan(&name)
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return fmt.Errorf("checking table %s: %w", table, err)
	}

	rows, err := conn.QueryContext(ctx, fmt.Sprintf(`PRAGMA table_info("%s")`, table))
	if err != nil {
		return fmt.Errorf("table_info %s: %w", table, err)
	}
	defer rows.Close()
	for rows.Next() {
		var cid int
		var n, typ string
		var notnull, pk int
		var dflt sql.NullString
		if err := rows.Scan(&cid, &n, &typ, &notnull, &dflt, &pk); err != nil {
			return fmt.Errorf("scan table_info %s: %w", table, err)
		}
		if n == column {
			return nil
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterating table_info %s: %w", table, err)
	}

	if _, err := conn.ExecContext(ctx, fmt.Sprintf(`ALTER TABLE "%s" ADD COLUMN "%s" %s`, table, column, decl)); err != nil {
		// A concurrent Open() may have added the column between our
		// PRAGMA check and this ALTER. SQLite returns SQLITE_ERROR with
		// "duplicate column name", which busy_timeout does not retry.
		// The DB is now in the desired state regardless of who won.
		if strings.Contains(err.Error(), "duplicate column name") {
			return nil
		}
		return fmt.Errorf("add column %s.%s: %w", table, column, err)
	}
	return nil
}

// backfillColumns adds columns that newer binaries declare but that
// pre-existing databases (created before those columns were added) lack.
// Must run before the migrations slice so that subsequent CREATE INDEX
// statements referencing the column can succeed against the upgraded
// table. Idempotent: safe to call on fresh DBs (table-not-found short-
// circuit) and on already-current DBs (column-exists short-circuit).
//
// Table names are emitted bare (no safeName) — ensureColumn double-quotes
// them at SQL emit time and uses parameter binding for the sqlite_master
// lookup, so the values flow as Go string literals first and SQL
// identifiers second. Wrapping with safeName here would embed literal
// double-quote characters into the Go string and break compilation for
// any spec whose dependent-resource snake_cased name is a SQL reserved
// word.
func (s *Store) backfillColumns(ctx context.Context, conn *sql.Conn) error {
	for _, c := range []struct{ table, column, decl string }{
		{table: "leagues", column: "parent_id", decl: "TEXT"},
		{table: "sync_state", column: "last_cursor", decl: "TEXT"},
		{table: "sync_state", column: "last_synced_at", decl: "DATETIME"},
		{table: "sync_state", column: "total_count", decl: "INTEGER DEFAULT 0"},
	} {
		if err := s.ensureColumn(ctx, conn, c.table, c.column, c.decl); err != nil {
			return err
		}
	}
	return nil
}

func (s *Store) migrate(ctx context.Context) error {
	conn, err := s.db.Conn(ctx)
	if err != nil {
		return fmt.Errorf("acquiring migration connection: %w", err)
	}
	defer conn.Close()

	// Read user_version before the migration lock so an old binary
	// opening a newer-schema DB rejects immediately. WAL readers don't
	// normally block on writers, but the fresh-DB WAL-init race can BUSY
	// a SELECT — share the lock's deadline so total budget stays bounded.
	deadline := time.Now().Add(migrationLockTimeout)
	var current int
	if err := retryOnBusy(ctx, deadline, "reading schema version", func() error {
		return conn.QueryRowContext(ctx, `PRAGMA user_version`).Scan(&current)
	}); err != nil {
		return err
	}
	if current > StoreSchemaVersion {
		return fmt.Errorf("database schema version %d is newer than supported version %d; upgrade the CLI binary or open an older database", current, StoreSchemaVersion)
	}

	migrations := []string{
		`CREATE TABLE IF NOT EXISTS resources (
			id TEXT NOT NULL,
			resource_type TEXT NOT NULL,
			data JSON NOT NULL,
			synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (resource_type, id)
		)`,
		`CREATE INDEX IF NOT EXISTS idx_resources_type ON resources(resource_type)`,
		`CREATE INDEX IF NOT EXISTS idx_resources_synced ON resources(synced_at)`,
		`CREATE TABLE IF NOT EXISTS sync_state (
			resource_type TEXT PRIMARY KEY,
			last_cursor TEXT,
			last_synced_at DATETIME,
			total_count INTEGER DEFAULT 0
		)`,
		resourcesFTSCreateSQL,
		`CREATE TABLE IF NOT EXISTS "leagues" (
			"id" TEXT PRIMARY KEY,
			"data" JSON NOT NULL,
			"synced_at" DATETIME DEFAULT CURRENT_TIMESTAMP,
			"parent_id" TEXT
		)`,
		`CREATE INDEX IF NOT EXISTS "idx_leagues_parent_id" ON "leagues"("parent_id")`,
	}

	// Run every migration — including the column backfill and the
	// schema-version stamp — inside a single BEGIN IMMEDIATE transaction
	// pinned to one connection. IMMEDIATE acquires SQLite's RESERVED lock
	// at BEGIN time so concurrent migrators serialize on it instead of
	// racing per-statement and tripping SQLITE_BUSY despite busy_timeout.
	// modernc.org/sqlite's busy_timeout does not always cover write-write
	// contention at BEGIN/COMMIT time, so we retry both explicitly on
	// SQLITE_BUSY for up to migrationLockTimeout.
	return withMigrationLock(ctx, conn, deadline, func() error {
		// Re-read user_version inside the lock. This is load-bearing,
		// not paranoid: between the pre-lock read above and our
		// successful BEGIN IMMEDIATE, a newer-binary peer may have
		// committed a higher version stamp. Without this re-read, an
		// older binary (smaller StoreSchemaVersion) would proceed to
		// stamp its own lower version at the end of the closure,
		// silently downgrading user_version on a schema that's already
		// at the newer level. Future maintainers: leave this read in.
		var current int
		if err := conn.QueryRowContext(ctx, `PRAGMA user_version`).Scan(&current); err != nil {
			return fmt.Errorf("reading schema version: %w", err)
		}
		if current > StoreSchemaVersion {
			return fmt.Errorf("database schema version %d is newer than supported version %d; upgrade the CLI binary or open an older database", current, StoreSchemaVersion)
		}

		if current < 2 {
			if err := s.migrateResourcesCompositeKey(ctx, conn); err != nil {
				return fmt.Errorf("migrating resources composite key: %w", err)
			}
		}

		if err := s.backfillColumns(ctx, conn); err != nil {
			return fmt.Errorf("backfilling columns: %w", err)
		}
		for _, m := range migrations {
			if _, err := conn.ExecContext(ctx, m); err != nil {
				return fmt.Errorf("migration failed: %w", err)
			}
		}
		// Stamp the schema version. On a fresh DB this writes the current
		// StoreSchemaVersion; on an already-stamped DB this is a no-op
		// write of the same value.
		// An older DB with user_version = 0 and pre-existing tables gets
		// stamped here after any version-gated rewrites and idempotent
		// CREATE TABLE IF NOT EXISTS statements have completed.
		if _, err := conn.ExecContext(ctx, fmt.Sprintf(`PRAGMA user_version = %d`, StoreSchemaVersion)); err != nil {
			return fmt.Errorf("stamp user_version: %w", err)
		}
		return nil
	})
}

func (s *Store) migrateResourcesCompositeKey(ctx context.Context, conn *sql.Conn) error {
	exists, err := tableExists(ctx, conn, "resources")
	if err != nil {
		return err
	}
	if !exists {
		return nil
	}

	composite, err := resourcesTableHasCompositeKey(ctx, conn)
	if err != nil {
		return err
	}
	if !composite {
		if _, err := conn.ExecContext(ctx, `CREATE TABLE resources_v2 (
			id TEXT NOT NULL,
			resource_type TEXT NOT NULL,
			data JSON NOT NULL,
			synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (resource_type, id)
		)`); err != nil {
			return fmt.Errorf("creating resources_v2: %w", err)
		}
		if _, err := conn.ExecContext(ctx, `INSERT INTO resources_v2 (id, resource_type, data, synced_at, updated_at)
			SELECT id, resource_type, data, synced_at, updated_at FROM resources`); err != nil {
			return fmt.Errorf("copying resources rows: %w", err)
		}
		if _, err := conn.ExecContext(ctx, `DROP TABLE resources`); err != nil {
			return fmt.Errorf("dropping old resources table: %w", err)
		}
		if _, err := conn.ExecContext(ctx, `ALTER TABLE resources_v2 RENAME TO resources`); err != nil {
			return fmt.Errorf("renaming resources_v2: %w", err)
		}
	}

	// Always rebuild FTS during the v2 transition. The resources table may
	// already have the composite key, but v1 FTS rowids were scoped by id
	// alone and must be replaced with resource_type + id rowids.
	if _, err := conn.ExecContext(ctx, `DROP TABLE IF EXISTS resources_fts`); err != nil {
		return fmt.Errorf("dropping resources_fts: %w", err)
	}
	if _, err := conn.ExecContext(ctx, resourcesFTSCreateSQL); err != nil {
		return fmt.Errorf("creating resources_fts: %w", err)
	}
	if err := rebuildResourcesFTS(ctx, conn); err != nil {
		return fmt.Errorf("rebuilding resources_fts: %w", err)
	}
	return nil
}

func tableExists(ctx context.Context, conn *sql.Conn, name string) (bool, error) {
	var count int
	if err := conn.QueryRowContext(ctx, `SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = ?`, name).Scan(&count); err != nil {
		return false, fmt.Errorf("checking table %s: %w", name, err)
	}
	return count > 0, nil
}

func resourcesTableHasCompositeKey(ctx context.Context, conn *sql.Conn) (bool, error) {
	rows, err := conn.QueryContext(ctx, `PRAGMA table_info(resources)`)
	if err != nil {
		return false, fmt.Errorf("reading resources table info: %w", err)
	}
	defer rows.Close()

	pk := map[string]int{}
	for rows.Next() {
		var cid int
		var name, typ string
		var notnull, pkOrder int
		var dflt sql.NullString
		if err := rows.Scan(&cid, &name, &typ, &notnull, &dflt, &pkOrder); err != nil {
			return false, fmt.Errorf("scanning resources table info: %w", err)
		}
		pk[name] = pkOrder
	}
	if err := rows.Err(); err != nil {
		return false, fmt.Errorf("reading resources table info rows: %w", err)
	}
	return pk["resource_type"] == 1 && pk["id"] == 2, nil
}

func rebuildResourcesFTS(ctx context.Context, conn *sql.Conn) error {
	rows, err := conn.QueryContext(ctx, `SELECT id, resource_type, data FROM resources`)
	if err != nil {
		return fmt.Errorf("querying resources: %w", err)
	}

	type resourceRow struct {
		id           string
		resourceType string
		data         string
	}
	var resources []resourceRow
	for rows.Next() {
		var r resourceRow
		if err := rows.Scan(&r.id, &r.resourceType, &r.data); err != nil {
			rows.Close()
			return fmt.Errorf("scanning resource: %w", err)
		}
		resources = append(resources, r)
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return fmt.Errorf("reading resource rows: %w", err)
	}
	if err := rows.Close(); err != nil {
		return fmt.Errorf("closing resource rows: %w", err)
	}

	for _, r := range resources {
		if _, err := conn.ExecContext(ctx,
			`INSERT INTO resources_fts (rowid, id, resource_type, content) VALUES (?, ?, ?, ?)`,
			ftsRowID(r.resourceType, r.id), r.id, r.resourceType, r.data,
		); err != nil {
			return fmt.Errorf("indexing resource %s/%s: %w", r.resourceType, r.id, err)
		}
	}
	return nil
}

const (
	migrationLockTimeout    = 30 * time.Second
	migrationLockBackoffMin = 5 * time.Millisecond
	migrationLockBackoffMax = 100 * time.Millisecond
)

// withMigrationLock runs fn inside a BEGIN IMMEDIATE / COMMIT pair on
// conn, retrying both BEGIN and COMMIT on SQLITE_BUSY against the
// caller-provided deadline. Sharing the deadline with the pre-lock
// version read keeps total Open() latency bounded by a single budget.
// The real upper bound is deadline + one trailing backoff interval
// (≤100ms) + the driver's busy_timeout for the in-flight Exec, since
// the deadline is checked after each failed attempt rather than as a
// hard wall-clock cutoff. fn must use conn (not s.db) so its writes
// participate in the held transaction.
func withMigrationLock(ctx context.Context, conn *sql.Conn, deadline time.Time, fn func() error) error {
	if err := execWithBusyRetry(ctx, conn, "BEGIN IMMEDIATE", "begin migration transaction", deadline); err != nil {
		return err
	}
	committed := false
	defer func() {
		if committed {
			return
		}
		// ROLLBACK uses context.Background() so caller-context cancellation
		// can't strand the connection in an open transaction. A failed
		// rollback is rare on local SQLite (broken file handle, fatal
		// driver error) but worth surfacing — silent swallow leaves a
		// pinned connection returned to the pool with state that will
		// confuse later queries.
		if _, rerr := conn.ExecContext(context.Background(), "ROLLBACK"); rerr != nil {
			fmt.Fprintf(os.Stderr, "warning: store migration rollback failed: %v\n", rerr)
		}
	}()

	if err := fn(); err != nil {
		return err
	}

	if err := execWithBusyRetry(ctx, conn, "COMMIT", "commit migration transaction", deadline); err != nil {
		return err
	}
	committed = true
	return nil
}

// execWithBusyRetry runs stmt on conn and retries on SQLITE_BUSY until
// deadline. It covers BEGIN IMMEDIATE and COMMIT contention;
// modernc.org/sqlite's busy_timeout does not reliably cover either when
// multiple connections race for the WAL write lock.
func execWithBusyRetry(ctx context.Context, conn *sql.Conn, stmt, label string, deadline time.Time) error {
	return retryOnBusy(ctx, deadline, label, func() error {
		_, err := conn.ExecContext(ctx, stmt)
		return err
	})
}

// retryOnBusy runs op and retries it on SQLITE_BUSY/LOCKED until
// deadline. The same retry shape covers Exec, Query, and any other
// SQLite call that can race the WAL writer lock — including the
// pre-lock user_version read, where the WAL initialization race on a
// fresh DB can BUSY a SELECT that should otherwise succeed under WAL
// reader/writer concurrency.
func retryOnBusy(ctx context.Context, deadline time.Time, label string, op func() error) error {
	backoff := migrationLockBackoffMin
	for {
		err := op()
		if err == nil {
			return nil
		}
		if !isSQLiteBusy(err) {
			return fmt.Errorf("%s: %w", label, err)
		}
		if time.Now().After(deadline) {
			// The label carries the operation context (e.g. "begin
			// migration transaction", "reading schema version") — we
			// don't hardcode "waiting for write lock" because pre-lock
			// reads also flow through this helper.
			return fmt.Errorf("%s: timed out after %s under SQLite contention: %w", label, migrationLockTimeout, err)
		}
		select {
		case <-ctx.Done():
			return fmt.Errorf("%s: %w", label, ctx.Err())
		case <-time.After(backoff):
		}
		backoff = min(backoff*2, migrationLockBackoffMax)
	}
}

// isSQLiteBusy reports whether err is a retryable SQLite lock condition.
// Covers both the file-level WAL writer race (SQLITE_BUSY / "database is
// locked") and the table-level shared-cache contention (SQLITE_LOCKED /
// "database table is locked"). The match is on the error string because
// modernc.org/sqlite does not export an error type the generated code
// can switch on without dragging the driver package into every store
// consumer.
func isSQLiteBusy(err error) bool {
	if err == nil {
		return false
	}
	msg := err.Error()
	return strings.Contains(msg, "SQLITE_BUSY") ||
		strings.Contains(msg, "SQLITE_LOCKED") ||
		strings.Contains(msg, "database is locked") ||
		strings.Contains(msg, "database table is locked")
}

func (s *Store) upsertGenericResourceTx(tx *sql.Tx, resourceType, id string, data json.RawMessage) error {
	_, err := tx.Exec(
		`INSERT INTO resources (id, resource_type, data, synced_at, updated_at)
		 VALUES (?, ?, ?, ?, ?)
		 ON CONFLICT(resource_type, id) DO UPDATE SET data = excluded.data, synced_at = excluded.synced_at, updated_at = excluded.updated_at`,
		id, resourceType, string(data), time.Now(), time.Now(),
	)
	if err != nil {
		return err
	}

	ftsRowid := ftsRowID(resourceType, id)
	// Use explicit rowid for FTS5 compatibility with modernc.org/sqlite.
	// Standard DELETE WHERE column=? may not work on FTS5 virtual tables.
	if _, err = tx.Exec(`DELETE FROM resources_fts WHERE rowid = ?`, ftsRowid); err != nil {
		fmt.Fprintf(os.Stderr, "warning: FTS index cleanup failed: %v\n", err)
	}

	if _, err = tx.Exec(
		`INSERT INTO resources_fts (rowid, id, resource_type, content)
		 VALUES (?, ?, ?, ?)`,
		ftsRowid, id, resourceType, string(data),
	); err != nil {
		// FTS insert failure is non-fatal
		fmt.Fprintf(os.Stderr, "warning: FTS index update failed: %v\n", err)
	}

	return nil
}

func (s *Store) Upsert(resourceType, id string, data json.RawMessage) error {
	s.writeMu.Lock()
	defer s.writeMu.Unlock()
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	if err := s.upsertGenericResourceTx(tx, resourceType, id, data); err != nil {
		return err
	}

	return tx.Commit()
}

// Propagates sql.ErrNoRows on a miss so callers can distinguish absence from
// other scan errors via errors.Is.
func (s *Store) Get(resourceType, id string) (json.RawMessage, error) {
	var data string
	err := s.db.QueryRow(
		`SELECT data FROM resources WHERE resource_type = ? AND id = ?`,
		resourceType, id,
	).Scan(&data)
	if err != nil {
		return nil, err
	}
	return json.RawMessage(data), nil
}

func (s *Store) List(resourceType string, limit int) ([]json.RawMessage, error) {
	if limit <= 0 {
		limit = 200
	}
	rows, err := s.db.Query(
		`SELECT data FROM resources WHERE resource_type = ? ORDER BY updated_at DESC LIMIT ?`,
		resourceType, limit,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var results []json.RawMessage
	for rows.Next() {
		var data string
		if err := rows.Scan(&data); err != nil {
			return nil, err
		}
		results = append(results, json.RawMessage(data))
	}
	return results, rows.Err()
}

func (s *Store) Search(query string, limit int) ([]json.RawMessage, error) {
	if limit <= 0 {
		limit = 50
	}
	rows, err := s.db.Query(
		`SELECT r.data FROM resources r
		 JOIN resources_fts f ON r.id = f.id AND r.resource_type = f.resource_type
		 WHERE resources_fts MATCH ?
		 ORDER BY rank
		 LIMIT ?`,
		query, limit,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var results []json.RawMessage
	for rows.Next() {
		var data string
		if err := rows.Scan(&data); err != nil {
			return nil, err
		}
		results = append(results, json.RawMessage(data))
	}
	return results, rows.Err()
}

func extractObjectID(obj map[string]any) string {
	for _, key := range []string{"id", "Id", "ID", "uuid", "slug", "name"} {
		if v, ok := obj[key]; ok {
			return fmt.Sprintf("%v", v)
		}
	}
	return ""
}

// ftsRowID derives a deterministic rowid from a string ID for use with FTS5.
// modernc.org/sqlite's FTS5 implementation may not support DELETE WHERE column=?
// on virtual tables, so we use explicit rowids and DELETE WHERE rowid=? instead.
func ftsRowID(scope, id string) int64 {
	var h uint64
	for _, c := range scope {
		h = h*31 + uint64(c)
	}
	h *= 31
	for _, c := range id {
		h = h*31 + uint64(c)
	}
	return int64(h & 0x7FFFFFFFFFFFFFFF) // ensure positive
}

// LookupFieldValue resolves a field value from a JSON object map, trying the
// snake_case key first, then the camelCase rendering, then the PascalCase
// rendering. Exported so the sync command's extractID and the upsert path
// resolve fields the same way — a divergence here produces silent drops on
// heterogeneous payloads. The PascalCase pass handles .NET-shaped responses
// (`Id`, `Name`, `OrderId`) without forcing each spec to declare casing.
func LookupFieldValue(obj map[string]any, snakeKey string) any {
	if v, ok := obj[snakeKey]; ok {
		return sqliteFieldValue(v)
	}
	parts := strings.Split(snakeKey, "_")
	for i := 1; i < len(parts); i++ {
		if parts[i] == "" {
			continue
		}
		parts[i] = strings.ToUpper(parts[i][:1]) + parts[i][1:]
	}
	camel := strings.Join(parts, "")
	if v, ok := obj[camel]; ok {
		return sqliteFieldValue(v)
	}
	if parts[0] != "" {
		pascal := strings.ToUpper(parts[0][:1]) + parts[0][1:] + strings.Join(parts[1:], "")
		if v, ok := obj[pascal]; ok {
			return sqliteFieldValue(v)
		}
	}
	return nil
}

func sqliteFieldValue(v any) any {
	switch v.(type) {
	case nil, string, bool, int, int64, float64, []byte:
		return v
	default:
		data, err := json.Marshal(v)
		if err != nil {
			return fmt.Sprint(v)
		}
		return string(data)
	}
}

// lookupFieldValue is kept as an unexported alias for in-package callers so
// the existing UpsertBatch code reads naturally without prefixing every call
// with the package name.
func lookupFieldValue(obj map[string]any, snakeKey string) any {
	return LookupFieldValue(obj, snakeKey)
}

// upsertLeaguesTx writes the typed-table portion of a leagues upsert
// inside an existing transaction. The caller is responsible for the generic
// resources insert (via upsertGenericResourceTx) and for committing the tx.
// Splitting this out lets UpsertBatch dispatch typed inserts per item without
// opening a per-item transaction.
func (s *Store) upsertLeaguesTx(tx *sql.Tx, id string, obj map[string]any, data json.RawMessage) error {
	if _, err := tx.Exec(
		`INSERT INTO "leagues" ("id", "data", "synced_at", "parent_id")
		 VALUES (?, ?, ?, ?)
		 ON CONFLICT("id") DO UPDATE SET "data" = excluded."data", "synced_at" = excluded."synced_at", "parent_id" = excluded."parent_id"`,
		id,
		string(data),
		time.Now(),
		lookupFieldValue(obj, "parent_id"),
	); err != nil {
		return fmt.Errorf("insert into leagues: %w", err)
	}

	return nil
}

// UpsertLeagues inserts or updates a leagues record with domain-specific columns.
func (s *Store) UpsertLeagues(data json.RawMessage) error {
	var obj map[string]any
	if err := json.Unmarshal(data, &obj); err != nil {
		return fmt.Errorf("unmarshaling leagues: %w", err)
	}

	id := extractObjectID(obj)
	if id == "" {
		return fmt.Errorf("missing id for leagues")
	}

	s.writeMu.Lock()
	defer s.writeMu.Unlock()
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	if err := s.upsertGenericResourceTx(tx, "leagues", id, data); err != nil {
		return err
	}
	if err := s.upsertLeaguesTx(tx, id, obj, data); err != nil {
		return err
	}

	return tx.Commit()
}

// resourceIDFieldOverrides projects per-resource IDField (set by the profiler
// from x-resource-id or response-schema fallback) into a runtime lookup map.
// UpsertBatch consults this first so the templated path wins over the
// generic fallback list. Empty when no resource declared an override; the
// runtime fallback list still applies.
//
// Includes both flat resources and dependent (parent-child) resources so a
// child path-item annotated with x-resource-id resolves the same as a flat
// path-item.
var resourceIDFieldOverrides = map[string]string{
	"games": "game_key",
}

// genericIDFieldFallbacks is the runtime safety net for resources that did
// NOT receive a templated IDField. API-specific names belong in spec
// annotations (x-resource-id), not this list. Order matters: vendor
// identifier names (gid, sid, uid, uuid, guid) take precedence over `name`
// so APIs like Asana (gid) and Twilio (sid) don't fall through to a display
// field and upsert on names — see #1394.
var genericIDFieldFallbacks = []string{"id", "ID", "gid", "sid", "uid", "uuid", "guid", "name", "slug", "key", "code"}

// UpsertBatch inserts or replaces multiple records in a single transaction
// and returns (stored, extractFailures, err). stored counts rows landed in
// the generic resources table; extractFailures counts items that survived
// JSON unmarshal but had no extractable primary key (templated IDField AND
// generic fallback both missed). callers (sync.go.tmpl) compare these
// against len(items) to emit the per-item primary_key_unresolved warning
// and the F4b stored_count_zero_after_extraction probe.
//
// For resource types that have a domain-specific typed table, the per-item
// generic insert is followed by a dispatch to the matching upsert<Pascal>Tx
// inside the same transaction. Without that dispatch, paginated syncs would
// only populate the generic resources table — typed tables (and indexed
// columns like parent_id added by dependent-resource sync) would stay empty.
//
// Each typed-table dispatch runs inside a per-item SAVEPOINT so a constraint
// failure in the typed insert (e.g. NOT NULL parent FK when the generator
// didn't populate the parent path placeholder) rolls back only that typed
// upsert. The generic resources row inserted just above it survives the
// rollback, so successful API fetches never strand in memory because one
// downstream typed table is misconfigured. Failures are surfaced via a
// trailing stderr warning rather than aborting the batch.
func (s *Store) UpsertBatch(resourceType string, items []json.RawMessage) (int, int, error) {
	s.writeMu.Lock()
	defer s.writeMu.Unlock()
	tx, err := s.db.Begin()
	if err != nil {
		return 0, 0, fmt.Errorf("starting batch transaction: %w", err)
	}
	defer tx.Rollback()

	var stored, skippedCount, extractFailures, typedFailures int
	for i, item := range items {
		var obj map[string]any
		if err := json.Unmarshal(item, &obj); err != nil {
			skippedCount++
			continue
		}
		// Templated IDField wins; generic fallback list runs second when
		// the override is empty OR the override field is absent on this
		// particular item (response shape mismatches happen even when the
		// spec declares x-resource-id).
		var id string
		if override, ok := resourceIDFieldOverrides[resourceType]; ok && override != "" {
			if v := lookupFieldValue(obj, override); v != nil {
				s := fmt.Sprintf("%v", v)
				if s != "" && s != "<nil>" {
					id = s
				}
			}
		}
		if id == "" {
			for _, key := range genericIDFieldFallbacks {
				if v := lookupFieldValue(obj, key); v != nil {
					s := fmt.Sprintf("%v", v)
					if s != "" && s != "<nil>" {
						id = s
						break
					}
				}
			}
		}
		if id == "" {
			skippedCount++
			extractFailures++
			continue
		}

		if err := s.upsertGenericResourceTx(tx, resourceType, id, item); err != nil {
			// Return the running stored count rather than zero so callers
			// inspecting partial progress on failure see what already
			// landed in earlier loop iterations.
			return stored, extractFailures, fmt.Errorf("upserting %s/%s: %w", resourceType, id, err)
		}
		stored++

		savepoint := fmt.Sprintf("pp_typed_%d", i)
		if _, err := tx.Exec("SAVEPOINT " + savepoint); err != nil {
			return stored, extractFailures, fmt.Errorf("savepoint begin for %s/%s: %w", resourceType, id, err)
		}

		var typedErr error
		switch resourceType {
		case "leagues":
			typedErr = s.upsertLeaguesTx(tx, id, obj, item)
		}

		if typedErr != nil {
			if _, rbErr := tx.Exec("ROLLBACK TO SAVEPOINT " + savepoint); rbErr != nil {
				return stored, extractFailures, fmt.Errorf("rollback to savepoint for %s/%s (typed err: %v): %w", resourceType, id, typedErr, rbErr)
			}
			if _, relErr := tx.Exec("RELEASE SAVEPOINT " + savepoint); relErr != nil {
				return stored, extractFailures, fmt.Errorf("release savepoint after rollback for %s/%s: %w", resourceType, id, relErr)
			}
			typedFailures++
			continue
		}
		if _, err := tx.Exec("RELEASE SAVEPOINT " + savepoint); err != nil {
			return stored, extractFailures, fmt.Errorf("release savepoint for %s/%s: %w", resourceType, id, err)
		}
	}

	// Warn when most items in a batch lack an extractable ID — this likely
	// means the API uses a primary key field we don't recognize yet.
	if skippedCount > 0 && len(items) > 0 && skippedCount*2 > len(items) {
		fmt.Fprintf(os.Stderr, "warning: %d/%d %s items skipped (no extractable ID field found)\n", skippedCount, len(items), resourceType)
	}
	// Surface typed-table failures without aborting the batch. Generic rows
	// already committed; only the typed projection failed.
	if typedFailures > 0 {
		fmt.Fprintf(os.Stderr, "warning: %d/%d %s items: typed-table upsert failed; generic resources rows preserved\n", typedFailures, len(items), resourceType)
	}

	if err := tx.Commit(); err != nil {
		return 0, extractFailures, err
	}
	return stored, extractFailures, nil
}

func (s *Store) SaveSyncState(resourceType, cursor string, count int) error {
	s.writeMu.Lock()
	defer s.writeMu.Unlock()
	_, err := s.db.Exec(
		`INSERT INTO sync_state (resource_type, last_cursor, last_synced_at, total_count)
		 VALUES (?, ?, ?, ?)
		 ON CONFLICT(resource_type) DO UPDATE SET last_cursor = excluded.last_cursor,
		 last_synced_at = excluded.last_synced_at, total_count = excluded.total_count`,
		resourceType, cursor, time.Now(), count,
	)
	return err
}

func (s *Store) GetSyncState(resourceType string) (cursor string, lastSynced time.Time, count int, err error) {
	err = s.db.QueryRow(
		`SELECT last_cursor, last_synced_at, total_count FROM sync_state WHERE resource_type = ?`,
		resourceType,
	).Scan(&cursor, &lastSynced, &count)
	if err == sql.ErrNoRows {
		return "", time.Time{}, 0, nil
	}
	return
}

// SaveSyncCursor stores the pagination cursor for a resource type.
func (s *Store) SaveSyncCursor(resourceType, cursor string) error {
	s.writeMu.Lock()
	defer s.writeMu.Unlock()
	_, err := s.db.Exec(
		`INSERT INTO sync_state (resource_type, last_cursor, last_synced_at, total_count)
		 VALUES (?, ?, CURRENT_TIMESTAMP, 0)
		 ON CONFLICT(resource_type) DO UPDATE SET last_cursor = ?, last_synced_at = CURRENT_TIMESTAMP`,
		resourceType, cursor, cursor,
	)
	return err
}

// GetSyncCursor returns the last pagination cursor for a resource type.
func (s *Store) GetSyncCursor(resourceType string) string {
	var cursor sql.NullString
	s.db.QueryRow("SELECT last_cursor FROM sync_state WHERE resource_type = ?", resourceType).Scan(&cursor)
	if cursor.Valid {
		return cursor.String
	}
	return ""
}

// ListIDs returns all IDs from a resource's domain table, or from the generic
// resources table if no domain table exists. Used by dependent sync to iterate parents.
//
// resourceType is never interpolated into SQL directly. We resolve it to a real
// table name via a parameterized sqlite_master lookup; only that trusted name is
// substituted (double-quoted) into the SELECT. Callers may pass any string.
func (s *Store) ListIDs(resourceType string) ([]string, error) {
	var table string
	err := s.db.QueryRow(
		`SELECT name FROM sqlite_master WHERE type='table' AND name=?`,
		resourceType,
	).Scan(&table)
	var rows *sql.Rows
	if err == nil && table != "" {
		rows, err = s.db.Query(fmt.Sprintf(`SELECT id FROM "%s"`, strings.ReplaceAll(table, `"`, `""`)))
	}
	if err != nil || table == "" {
		// Fall back to generic resources table
		rows, err = s.db.Query("SELECT id FROM resources WHERE resource_type = ?", resourceType)
		if err != nil {
			return nil, err
		}
	}
	defer rows.Close()

	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			continue
		}
		ids = append(ids, id)
	}
	return ids, rows.Err()
}

// ListField returns values of a named field from a resource's domain table,
// or from the generic resources table via json_extract when no typed column
// exists. Used by dependent sync to iterate parents when a spec-declared
// walker extracts a non-PK field (Endpoint.Walker.KeyField in the upstream
// printing-press repo) for the child path's placeholder.
//
// Defense in depth: field is validated against validIdentifierRE at entry
// — the regex pins it to SQL-safe identifier shape covering both the
// typed-column primary path AND the json_extract fallback (where
// pragma_table_info validation would never run if the parent's domain
// table doesn't exist yet). resourceType is never interpolated into SQL
// directly; we resolve it to a real table name via a parameterized
// sqlite_master lookup. Only validated names are substituted
// (double-quoted) into the SELECT. Mirrors ListIDs's defense pattern so
// callers may pass any string.
func (s *Store) ListField(resourceType, field string) ([]string, error) {
	if !validIdentifierRE.MatchString(field) {
		return nil, fmt.Errorf("ListField: invalid field name %q (must match %s)", field, validIdentifierRE.String())
	}
	var table string
	err := s.db.QueryRow(
		`SELECT name FROM sqlite_master WHERE type='table' AND name=?`,
		resourceType,
	).Scan(&table)
	var rows *sql.Rows
	if err == nil && table != "" {
		// Validate the column exists on the resolved table before splicing
		// it into the SELECT. pragma_table_info is parameterizable.
		var colName string
		colErr := s.db.QueryRow(
			`SELECT name FROM pragma_table_info(?) WHERE name=?`,
			table, field,
		).Scan(&colName)
		if colErr == nil && colName != "" {
			qTable := strings.ReplaceAll(table, `"`, `""`)
			qCol := strings.ReplaceAll(colName, `"`, `""`)
			// DISTINCT: callers iterate the returned values as parent keys
			// for child-resource fan-out. Multiple parent rows sharing a
			// key_field value (legal for non-PK fields) would otherwise
			// cause the child endpoint to be fetched once per duplicate row.
			rows, err = s.db.Query(fmt.Sprintf(
				`SELECT DISTINCT "%s" FROM "%s" WHERE "%s" IS NOT NULL AND "%s" != ''`,
				qCol, qTable, qCol, qCol,
			))
		} else {
			err = colErr
		}
	}
	if err != nil || rows == nil {
		// Fall back to generic resources table via json_extract. Path is
		// Sprintf'd into the SQL string (matches ResolveByName below).
		// DISTINCT for the same reason as the typed-column path above.
		fallback := fmt.Sprintf(
			`SELECT DISTINCT json_extract(data, '$.%s') FROM resources WHERE resource_type = ? AND json_extract(data, '$.%s') IS NOT NULL`,
			field, field,
		)
		rows, err = s.db.Query(fallback, resourceType)
		if err != nil {
			return nil, err
		}
	}
	defer rows.Close()

	var values []string
	for rows.Next() {
		var v sql.NullString
		if err := rows.Scan(&v); err == nil && v.Valid && v.String != "" {
			values = append(values, v.String)
		}
	}
	return values, rows.Err()
}

// GetLastSyncedAt returns the last sync timestamp for a resource type.
func (s *Store) GetLastSyncedAt(resourceType string) string {
	var ts sql.NullString
	s.db.QueryRow("SELECT last_synced_at FROM sync_state WHERE resource_type = ?", resourceType).Scan(&ts)
	if ts.Valid {
		return ts.String
	}
	return ""
}

// ClearSyncCursors resets all sync state for a full resync.
func (s *Store) ClearSyncCursors() error {
	s.writeMu.Lock()
	defer s.writeMu.Unlock()
	_, err := s.db.Exec("DELETE FROM sync_state")
	return err
}

// Query executes a raw SQL query and returns the rows.
// Used by workflow commands that need custom queries against the local store.
func (s *Store) Query(query string, args ...any) (*sql.Rows, error) {
	return s.db.Query(query, args...)
}

func (s *Store) Count(resourceType string) (int, error) {
	var count int
	err := s.db.QueryRow(
		`SELECT COUNT(*) FROM resources WHERE resource_type = ?`,
		resourceType,
	).Scan(&count)
	return count, err
}

func (s *Store) Status() (map[string]int, error) {
	rows, err := s.db.Query(
		`SELECT resource_type, COUNT(*) FROM resources GROUP BY resource_type ORDER BY resource_type`,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	status := make(map[string]int)
	for rows.Next() {
		var rt string
		var count int
		if err := rows.Scan(&rt, &count); err != nil {
			return nil, err
		}
		status[rt] = count
	}
	return status, rows.Err()
}

// ResolveByName resolves a human-readable name to a UUID from synced data.
// If the input is already a UUID, it is returned as-is.
// matchFields are JSON field names to search against (e.g., "name", "key", "email").
//
// json_extract path components cannot be bound as SQL parameters, so each
// field is validated against validIdentifierRE before being spliced into
// the query.
func (s *Store) ResolveByName(resourceType string, input string, matchFields ...string) (string, error) {
	if IsUUID(input) {
		return input, nil
	}

	var matches []string
	for _, field := range matchFields {
		if !validIdentifierRE.MatchString(field) {
			continue
		}
		query := fmt.Sprintf(
			`SELECT id FROM resources WHERE resource_type = ? AND LOWER(json_extract(data, '$.%s')) = LOWER(?)`,
			field,
		)
		rows, err := s.db.Query(query, resourceType, input)
		if err != nil {
			continue
		}
		for rows.Next() {
			var id string
			if rows.Scan(&id) == nil {
				// Deduplicate
				found := false
				for _, m := range matches {
					if m == id {
						found = true
						break
					}
				}
				if !found {
					matches = append(matches, id)
				}
			}
		}
		rows.Close()
	}

	switch len(matches) {
	case 0:
		return "", fmt.Errorf("%s %q not found in local store. Run 'sync' first, or use the UUID directly", resourceType, input)
	case 1:
		return matches[0], nil
	default:
		hint := matches[0]
		if len(matches) > 5 {
			hint = strings.Join(matches[:5], ", ") + "..."
		} else {
			hint = strings.Join(matches, ", ")
		}
		return "", fmt.Errorf("ambiguous: %q matches %d %s entries (%s). Use the exact UUID instead", input, len(matches), resourceType, hint)
	}
}
