// Copyright 2026 printing-press-golden and contributors. 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 (
	"bytes"
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"hash/fnv"
	"math"
	"os"
	"path/filepath"
	"regexp"
	"strconv"
	"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}$`)
var isoDatePattern = regexp.MustCompile(`^\d{4}-\d{2}-\d{2}(?:[T ][0-9:.+-Zz]+)?$`)
var ftsQueryTokenRE = regexp.MustCompile(`[\pL\pN_]+`)

var sqliteDriverInit struct {
	mu   sync.Mutex
	done bool
}

// 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. Non-learn CLIs advance to v4 for the
// resources_fts content extraction.
const StoreSchemaVersion = 4

// resourcesFTSContentSchemaVersion pins the schema bump that rewrote
// resources_fts content from raw JSON to searchable leaf values. Keep this
// separate from StoreSchemaVersion so future unrelated migrations do not
// trigger an expensive full FTS rebuild.
const resourcesFTSContentSchemaVersion = 4

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. Pragmas use the driver's _pragma=
// name(value) syntax — modernc.org/sqlite does NOT recognize the
// mattn/go-sqlite3 _journal_mode=WAL / _busy_timeout=5000 form and drops
// those keys silently, so the busy_timeout below is what keeps a read
// concurrent with a writer from failing immediately with SQLITE_BUSY.
//
// Deliberately no journal_mode pragma here: journal mode is a property of
// the database file, set by the read-write open, not the connection. Issuing
// PRAGMA journal_mode=WAL on a read-only handle to a DB still in the default
// delete mode (e.g. a pre-WAL database opened by an old binary before its
// first read-write open) errors with "attempt to write a readonly database".
//
// OpenReadOnly uses context.Background(); callers holding a context should use
// OpenReadOnlyContext so a cancelled command (SIGINT, deadline) interrupts the
// SQLITE_BUSY retry during driver init instead of waiting out the full timeout.
func OpenReadOnly(dbPath string) (*Store, error) {
	return OpenReadOnlyContext(context.Background(), dbPath)
}

// OpenReadOnlyContext is OpenReadOnly with a caller-supplied context honored by
// the driver-init SQLITE_BUSY retry.
func OpenReadOnlyContext(ctx context.Context, dbPath string) (*Store, error) {
	dsn := "file:" + dbPath + "?mode=ro&_pragma=busy_timeout(5000)&_pragma=foreign_keys(ON)&_pragma=temp_store(MEMORY)&_pragma=mmap_size(268435456)"
	if err := ensureSQLiteDriverInitialized(ctx, dsn); err != nil {
		return nil, err
	}

	db, err := sql.Open("sqlite", dsn)
	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), 0o700); err != nil {
		return nil, fmt.Errorf("creating db directory: %w", err)
	}

	// Pragma order is load-bearing: busy_timeout must engage BEFORE
	// journal_mode(WAL) so the delete→WAL conversion (an exclusive
	// operation on a fresh DB) runs with a busy handler active. With the
	// timeout listed after the conversion, concurrent first-run opens
	// race the WAL switch and fail SQLITE_BUSY instead of waiting. This
	// mirrors the OpenReadOnly DSN and works alongside the retryOnBusy
	// wrapper around Conn() acquisition below; both layers are needed
	// because modernc.org/sqlite's connect-time conversion is not fully
	// covered by the statement-level busy handler alone.
	dsn := dbPath + "?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=synchronous(NORMAL)&_pragma=foreign_keys(ON)&_pragma=temp_store(MEMORY)&_pragma=mmap_size(268435456)"
	if err := ensureSQLiteDriverInitialized(ctx, dsn); err != nil {
		return nil, err
	}

	db, err := sql.Open("sqlite", dsn)
	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 ensureSQLiteDriverInitialized(ctx context.Context, dsn string) error {
	sqliteDriverInit.mu.Lock()
	defer sqliteDriverInit.mu.Unlock()

	if sqliteDriverInit.done {
		return nil
	}

	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return fmt.Errorf("opening database for driver initialization: %w", err)
	}
	defer db.Close()

	// Acquiring the first physical connection runs the DSN _pragma directives,
	// including the journal_mode(WAL) conversion for a read-write DSN. On a
	// fresh DB opened concurrently — e.g. the scorecard live-check probing
	// sampled commands in parallel — that conversion can return SQLITE_BUSY
	// before the DSN's busy_timeout engages, so retry the acquisition against a
	// bounded deadline. SQLITE_BUSY here is always transient.
	deadline := time.Now().Add(migrationLockTimeout)
	var conn *sql.Conn
	if err := retryOnBusy(ctx, deadline, "initializing sqlite driver", func() error {
		c, err := db.Conn(ctx)
		if err != nil {
			return err
		}
		conn = c
		return nil
	}); err != nil {
		return err
	}
	if err := conn.Close(); err != nil {
		return fmt.Errorf("closing sqlite initialization connection: %w", err)
	}

	sqliteDriverInit.done = true
	return 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 {
	// Acquiring the migration connection establishes a physical SQLite
	// connection, which runs the DSN _pragma directives — including the
	// journal_mode(WAL) conversion. On a fresh DB opened by several
	// processes at once, that conversion briefly needs an exclusive lock
	// and can return SQLITE_BUSY before any statement-level busy handler
	// applies, so retry the acquisition against the shared deadline.
	deadline := time.Now().Add(migrationLockTimeout)
	var conn *sql.Conn
	if err := retryOnBusy(ctx, deadline, "acquiring migration connection", func() error {
		c, err := s.db.Conn(ctx)
		if err != nil {
			return err
		}
		conn = c
		return nil
	}); err != nil {
		return 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.
	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 current == 2 {
			if err := s.migrateResourcesFTSRowIDs(ctx, conn); err != nil {
				return fmt.Errorf("migrating resources FTS rowids: %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)
			}
		}
		if err := s.migrateExtras(ctx, conn); err != nil {
			return fmt.Errorf("running extra migrations: %w", err)
		}
		if current < resourcesFTSContentSchemaVersion {
			if err := s.migrateResourcesFTSContent(ctx, conn); err != nil {
				return fmt.Errorf("migrating resources FTS content: %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 (s *Store) migrateResourcesFTSRowIDs(ctx context.Context, conn *sql.Conn) error {
	exists, err := tableExists(ctx, conn, "resources")
	if err != nil {
		return err
	}
	if !exists {
		return nil
	}

	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 (s *Store) migrateResourcesFTSContent(ctx context.Context, conn *sql.Conn) error {
	exists, err := tableExists(ctx, conn, "resources")
	if err != nil {
		return err
	}
	if !exists {
		return nil
	}

	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, searchableResourceContent(json.RawMessage(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().UTC().Format(time.RFC3339), time.Now().UTC().Format(time.RFC3339),
	)
	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, searchableResourceContent(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
}

// List returns resources of the given type. A positive limit caps the result
// count; zero or negative means no limit.
func (s *Store) List(resourceType string, limit int) ([]json.RawMessage, error) {
	query := `SELECT data FROM resources WHERE resource_type = ? ORDER BY updated_at DESC`
	args := []any{resourceType}
	if limit > 0 {
		query += ` LIMIT ?`
		args = append(args, limit)
	}
	rows, err := s.db.Query(query, args...)
	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, resourceTypes ...string) ([]json.RawMessage, error) {
	if limit <= 0 {
		limit = 50
	}
	matchQuery := ftsMatchQuery(query)
	if matchQuery == "" {
		return nil, nil
	}
	resourceType := ""
	if len(resourceTypes) > 0 {
		resourceType = strings.TrimSpace(resourceTypes[0])
	}
	if resourceType != "" {
		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 ?
			 AND r.resource_type = ?
			 ORDER BY f.rank
			 LIMIT ?`,
			matchQuery, 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()
	}
	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 f.rank
		 LIMIT ?`,
		matchQuery, 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 searchableResourceContent(data json.RawMessage) string {
	dec := json.NewDecoder(bytes.NewReader(data))
	dec.UseNumber()
	var value any
	if err := dec.Decode(&value); err != nil {
		return ""
	}
	var parts []string
	collectSearchableStrings(&parts, "", value)
	return strings.Join(parts, " ")
}

func collectSearchableStrings(parts *[]string, key string, value any) {
	switch v := value.(type) {
	case map[string]any:
		for childKey, child := range v {
			collectSearchableStrings(parts, childKey, child)
		}
	case []any:
		for _, child := range v {
			collectSearchableStrings(parts, key, child)
		}
	case string:
		if shouldIndexSearchString(key, v) {
			*parts = append(*parts, strings.TrimSpace(v))
		}
	}
}

func shouldIndexSearchString(key, value string) bool {
	s := strings.TrimSpace(value)
	if len(s) < 2 {
		return false
	}
	if isIdentifierKey(key) {
		return false
	}
	lower := strings.ToLower(s)
	switch {
	case IsUUID(s):
		return false
	case isoDatePattern.MatchString(s):
		return false
	case strings.HasPrefix(lower, "http://") || strings.HasPrefix(lower, "https://"):
		return false
	}
	tokens := ftsQueryTokenRE.FindAllString(s, -1)
	return len(tokens) > 0
}

func isIdentifierKey(key string) bool {
	if key == "" {
		return false
	}
	lower := strings.ToLower(key)
	return lower == "id" ||
		lower == "uuid" ||
		strings.HasSuffix(lower, "_id") ||
		strings.HasSuffix(lower, "-id") ||
		strings.HasSuffix(key, "Id") ||
		strings.HasSuffix(key, "ID")
}

func ftsMatchQuery(query string) string {
	tokens := ftsQueryTokenRE.FindAllString(query, -1)
	if len(tokens) == 0 {
		return ""
	}
	quoted := make([]string, 0, len(tokens))
	for _, token := range tokens {
		quoted = append(quoted, `"`+token+`"`)
	}
	return strings.Join(quoted, " ")
}

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

// ftsRowID derives a deterministic rowid from a string ID for use with FTS5.
// Any change to this derivation requires a StoreSchemaVersion bump and a
// resources_fts rebuild migration for already-stamped databases.
// 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 {
	h := fnv.New64a()
	_, _ = h.Write([]byte(scope))
	_, _ = h.Write([]byte{0}) // separator so ("ab","c") != ("a","bc")
	_, _ = h.Write([]byte(id))
	return int64(h.Sum64() & 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 t := v.(type) {
	case nil, string, bool, int, int64, float64, []byte:
		return v
	case json.Number:
		return strings.TrimSpace(t.String())
	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)
}

// DecodeJSONObject decodes data into an object while preserving JSON numbers.
// Plain json.Unmarshal turns numbers into float64, and fmt on those values can
// render large integer IDs as scientific notation before they reach resources.id.
func DecodeJSONObject(data json.RawMessage) (map[string]any, error) {
	var obj map[string]any
	dec := json.NewDecoder(bytes.NewReader(data))
	dec.UseNumber()
	if err := dec.Decode(&obj); err != nil {
		return nil, err
	}
	return obj, nil
}

// ResourceIDString returns the stable text form used for resources.id.
func ResourceIDString(v any) string {
	switch t := v.(type) {
	case nil:
		return ""
	case json.Number:
		return strings.TrimSpace(t.String())
	case float64:
		if math.IsNaN(t) || math.IsInf(t, 0) {
			return ""
		}
		return strconv.FormatFloat(t, 'f', -1, 64)
	case float32:
		f := float64(t)
		if math.IsNaN(f) || math.IsInf(f, 0) {
			return ""
		}
		return strconv.FormatFloat(f, 'f', -1, 32)
	default:
		// fmt.Sprint on typed nil pointers returns "<nil>"; callers still guard
		// that sentinel so unresolved IDs do not become stored resource keys.
		return strings.TrimSpace(fmt.Sprint(t))
	}
}

// upsertLeaguesTx writes the per-resource domain-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
// domain 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().UTC().Format(time.RFC3339),
		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 {
	obj, err := DecodeJSONObject(data)
	if err != nil {
		return fmt.Errorf("unmarshaling leagues: %w", err)
	}

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

	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", storageID, data); err != nil {
		return err
	}
	if err := s.upsertLeaguesTx(tx, storageID, 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", "api_id", "name", "slug", "key", "code"}

// resourceParentKeyColumns identifies generated dependent resources whose
// local mirror rows need the parent context in the storage key. Without this,
// many-to-many sub-collections collapse every parent association onto the
// child's bare id and silently keep only the last synced parent.
var resourceParentKeyColumns = map[string]string{
	"leagues": "parent_id",
}

// ExtractResourceID resolves the bare resource id field that UpsertBatch
// extracts from a resource item. For dependent resource types, UpsertBatch
// derives the actual storage key by combining this id with the parent value;
// use resourceStorageID if you need the key as it appears in the database.
// Callers that need to gate best-effort writes can use this to avoid passing
// non-entity envelopes into the batch path.
func ExtractResourceID(resourceType string, obj map[string]any) string {
	if override, ok := resourceIDFieldOverrides[resourceType]; ok && override != "" {
		if v := lookupFieldValue(obj, override); v != nil {
			s := ResourceIDString(v)
			if s != "" && s != "<nil>" {
				return s
			}
		}
	}
	for _, key := range genericIDFieldFallbacks {
		if v := lookupFieldValue(obj, key); v != nil {
			s := ResourceIDString(v)
			if s != "" && s != "<nil>" {
				return s
			}
		}
	}
	if s := suffixIDFieldFallback(resourceType, obj); s != "" {
		return s
	}
	return ""
}

// suffixIDFieldFallback resolves an id-less resource that keys on its own
// "<name>_code" / "<name>_id" / "<name>_key" / "<name>_slug" field (e.g. the
// "currencies" resource keying on "currency_code" — see #2327). It is scoped to
// the resource's OWN name so a foreign key like account_id/parent_id is never
// promoted to the primary key, and it uses direct map lookups in a fixed suffix
// order so the chosen id is deterministic.
func suffixIDFieldFallback(resourceType string, obj map[string]any) string {
	for _, base := range resourceIDBaseNames(resourceType) {
		for _, suffix := range []string{"_id", "_code", "_key", "_slug"} {
			if v, ok := obj[base+suffix]; ok {
				if s := scalarIDString(v); s != "" && s != "<nil>" {
					return s
				}
			}
		}
		camelBase := lowerCamelResourceIDBase(base)
		for _, suffix := range []string{"Id", "Code", "Key", "Slug"} {
			if v, ok := obj[camelBase+suffix]; ok {
				if s := scalarIDString(v); s != "" && s != "<nil>" {
					return s
				}
			}
		}
	}
	return ""
}

// resourceIDBaseNames returns lowercase candidate singular/plural stems of a
// resource name to build "<base>_id"-style key probes from (e.g. "currencies"
// -> ["currencies","currency"]). OpenAPI-/path-derived names can carry a
// leading verb token ("get-currencies"), so the same probes are also attempted
// on the de-verbed stem. Minimal English depluralization; the raw name is
// always included so already-singular names work too.
func resourceIDBaseNames(resourceType string) []string {
	r := strings.ToLower(strings.TrimSpace(resourceType))
	if r == "" {
		return nil
	}
	stems := []string{r}
	if d := stripLeadingResourceVerb(r); d != "" && d != r {
		stems = append(stems, d)
	}
	var bases []string
	seen := map[string]bool{}
	add := func(s string) {
		if s != "" && !seen[s] {
			seen[s] = true
			bases = append(bases, s)
		}
	}
	for _, stem := range stems {
		add(stem)
		add(depluralizeResourceStem(stem))
	}
	return bases
}

func stripLeadingResourceVerb(r string) string {
	for _, verb := range []string{"get", "list", "fetch", "find", "retrieve", "read", "show", "all"} {
		for _, sep := range []string{"-", "_"} {
			prefix := verb + sep
			if strings.HasPrefix(r, prefix) && len(r) > len(prefix) {
				return r[len(prefix):]
			}
		}
	}
	return ""
}

func depluralizeResourceStem(r string) string {
	switch {
	case strings.HasSuffix(r, "ies") && len(r) > 3:
		return strings.TrimSuffix(r, "ies") + "y" // currencies -> currency
	// Plurals formed by adding "es" to a base ending in s/x/z/ch/sh. The
	// double-s "sses" guard (not bare "ses") keeps soft-e plurals — where the
	// singular already ends in a silent "e" (cases, databases, licenses,
	// purchases) — out of this branch; they fall through to the "-s" case below
	// (cases -> case, not cas). Trade-off: a genuine "-es" plural of an s-ending
	// singular (buses, statuses) depluralizes imperfectly, but those are rare as
	// resource names and this stem only feeds best-effort id-field probing.
	case strings.HasSuffix(r, "sses") || strings.HasSuffix(r, "xes") ||
		strings.HasSuffix(r, "zes") || strings.HasSuffix(r, "ches") ||
		strings.HasSuffix(r, "shes"):
		return strings.TrimSuffix(r, "es") // classes -> class, boxes -> box, dishes -> dish
	case strings.HasSuffix(r, "s") && !strings.HasSuffix(r, "ss") && len(r) > 1:
		return strings.TrimSuffix(r, "s") // languages -> language, cases -> case
	}
	return r
}

func lowerCamelResourceIDBase(base string) string {
	parts := strings.FieldsFunc(base, func(r rune) bool {
		return r == '_' || r == '-'
	})
	if len(parts) == 0 {
		return base
	}
	for i := range parts {
		if i == 0 {
			parts[i] = strings.ToLower(parts[i])
			continue
		}
		parts[i] = strings.ToUpper(parts[i][:1]) + strings.ToLower(parts[i][1:])
	}
	return strings.Join(parts, "")
}

func scalarIDString(value any) string {
	switch value.(type) {
	case string, bool, int, int8, int16, int32, int64,
		uint, uint8, uint16, uint32, uint64,
		float32, float64, json.Number, []byte:
		return ResourceIDString(value)
	default:
		return ""
	}
}

func resourceStorageID(resourceType, id string, obj map[string]any) string {
	parentKey := resourceParentKeyColumns[resourceType]
	if parentKey == "" {
		return id
	}
	parentValue := ResourceIDString(lookupFieldValue(obj, parentKey))
	if parentValue == "" || parentValue == "<nil>" {
		return id
	}
	return id + string([]byte{0}) + parentValue
}

// BareResourceID strips the NUL-delimited parent suffix that resourceStorageID
// appends to dependent resource types, returning the bare entity id. ListIDs
// returns composite keys for parent-keyed resources, so callers comparing those
// ids against bare API ids must run them through this first. For non-composite
// ids it returns the input unchanged, so it is safe to apply to every id.
func BareResourceID(storageID string) string {
	if i := strings.IndexByte(storageID, 0); i >= 0 {
		return storageID[:i]
	}
	return storageID
}

// childScopeColumnSources maps a typed child table's path-placeholder scope
// column (the FK the dependent sync injects per item, e.g. "projects_id") to
// the singular parent-reference field the API body carries natively (e.g.
// "project"). deriveScopeColumns consults this so write-through cache paths —
// which pass RAW API items to UpsertBatch and never carry the path-injected
// scope column — still satisfy the typed table's NOT NULL scope column instead
// of stranding the row in generic resources.
var childScopeColumnSources = map[string]string{
	"games_id": "game",
}

// deriveScopeColumns backfills a typed child table's scope column from the
// item's own parent reference when path injection is absent. A value already
// present (valid injection) is never overwritten.
func deriveScopeColumns(obj map[string]any) {
	for scopeKey, sourceKey := range childScopeColumnSources {
		if v := lookupFieldValue(obj, scopeKey); v != nil {
			if s, ok := v.(string); !ok || s != "" {
				continue // path injection already supplied a usable value
			}
		}
		src := lookupFieldValue(obj, sourceKey)
		if src == nil {
			continue
		}
		if s, ok := src.(string); ok && s == "" {
			continue
		}
		obj[scopeKey] = src
	}
}

// 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 {
		obj, err := DecodeJSONObject(item)
		if 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).
		id := ExtractResourceID(resourceType, obj)
		if id == "" {
			if unwrappedObj, unwrappedItem, ok := unwrapIDBearingEnvelopeItem(resourceType, item, obj); ok {
				obj = unwrappedObj
				item = unwrappedItem
				id = ExtractResourceID(resourceType, obj)
			}
		}
		if id == "" {
			skippedCount++
			extractFailures++
			continue
		}
		storageID := resourceStorageID(resourceType, id, obj)

		if err := s.upsertGenericResourceTx(tx, resourceType, storageID, 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, storageID, err)
		}
		stored++

		// Backfill the typed child table's NOT NULL scope column from the item's
		// own parent reference when the dependent-sync path injection is absent
		// (write-through cache feeds RAW API items here).
		deriveScopeColumns(obj)

		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, storageID, err)
		}

		var typedErr error
		switch resourceType {
		case "leagues":
			typedErr = s.upsertLeaguesTx(tx, storageID, 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, storageID, 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, storageID, relErr)
			}
			typedFailures++
			continue
		}
		if _, err := tx.Exec("RELEASE SAVEPOINT " + savepoint); err != nil {
			return stored, extractFailures, fmt.Errorf("release savepoint for %s/%s: %w", resourceType, storageID, err)
		}
	}

	// Warn when every decoded item in a batch lacks an extractable ID — this
	// likely means the API uses a primary key field we don't recognize yet.
	// Partial misses still surface through extractFailures so sync can emit
	// a structured primary_key_unresolved anomaly without spamming stderr for
	// write-through cache batches that did persist useful rows.
	if extractFailures > 0 && stored == 0 && len(items) > 0 {
		fmt.Fprintf(os.Stderr, "warning: %d/%d %s items returned but not cached locally (no extractable ID field; offline lookup against these rows will be incomplete; live queries unaffected)\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 unwrapIDBearingEnvelopeItem(resourceType string, item json.RawMessage, obj map[string]any) (map[string]any, json.RawMessage, bool) {
	var candidate map[string]any
	candidateKey := ""
	objectFields := 0
	for key, value := range obj {
		inner, ok := value.(map[string]any)
		if !ok {
			continue
		}
		objectFields++
		if ExtractResourceID(resourceType, inner) != "" {
			candidate = inner
			candidateKey = key
		}
	}
	if objectFields != 1 || candidate == nil || candidateKey == "" {
		return nil, nil, false
	}
	var raw map[string]json.RawMessage
	if err := json.Unmarshal(item, &raw); err != nil {
		return nil, nil, false
	}
	data, ok := raw[candidateKey]
	if !ok {
		return nil, nil, false
	}
	return candidate, data, true
}

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().UTC().Format(time.RFC3339), 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()
	now := time.Now().UTC().Format(time.RFC3339)
	_, err := s.db.Exec(
		`INSERT INTO sync_state (resource_type, last_cursor, last_synced_at, total_count)
		 VALUES (?, ?, ?, 0)
		 ON CONFLICT(resource_type) DO UPDATE SET last_cursor = ?, last_synced_at = ?`,
		resourceType, cursor, now, cursor, now,
	)
	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.
// For parent-keyed resource types these are composite storage keys; run them
// through BareResourceID before comparing against bare API ids.
//
// 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()
}

// ListIDsScoped is ListIDs with an optional tenant filter. scopeValue=="" =>
// unscoped (identical to ListIDs). When the typed table exists AND has
// scopeColumn (validated via validIdentifierRE + pragma_table_info), the IDs are
// filtered by that bound column. When the typed table exists but LACKS the
// column, it degrades to unscoped ListIDs (never silently returns zero parents).
// When no typed table exists, it filters the generic resources table via
// json_extract. scopeColumn is validated; scopeValue is always bound.
func (s *Store) ListIDsScoped(resourceType, scopeColumn, scopeValue string) ([]string, error) {
	if scopeValue == "" || scopeColumn == "" {
		return s.ListIDs(resourceType)
	}
	if !validIdentifierRE.MatchString(scopeColumn) {
		return nil, fmt.Errorf("ListIDsScoped: invalid scope column %q", scopeColumn)
	}
	var table string
	err := s.db.QueryRow(
		`SELECT name FROM sqlite_master WHERE type='table' AND name=?`,
		resourceType,
	).Scan(&table)
	if err == nil && table != "" {
		var colName string
		colErr := s.db.QueryRow(
			`SELECT name FROM pragma_table_info(?) WHERE name=?`,
			table, scopeColumn,
		).Scan(&colName)
		if colErr != nil || colName == "" {
			// Typed table exists but lacks the scope column: degrade to unscoped
			// rather than returning zero parents.
			return s.ListIDs(resourceType)
		}
		qTable := strings.ReplaceAll(table, `"`, `""`)
		qCol := strings.ReplaceAll(colName, `"`, `""`)
		rows, qerr := s.db.Query(
			fmt.Sprintf(`SELECT id FROM "%s" WHERE "%s" = ?`, qTable, qCol), scopeValue)
		if qerr != nil {
			return nil, qerr
		}
		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()
	}
	// No typed table: filter the generic resources table by body field.
	rows, qerr := s.db.Query(
		fmt.Sprintf(`SELECT id FROM resources WHERE resource_type = ? AND (CASE WHEN json_valid(data) THEN json_extract(data, '$.%s') END) = ?`, scopeColumn),
		resourceType, scopeValue,
	)
	if qerr != nil {
		return nil, qerr
	}
	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()
}

// ListFieldSets returns row-correlated values from the generic resources
// table. Dependent sync uses this for multi-placeholder paths where values
// such as owner/repo or server/webapp must stay paired per parent row.
func (s *Store) ListFieldSets(resourceType string, fields []string) ([]map[string]string, error) {
	if len(fields) == 0 {
		return nil, nil
	}
	for _, field := range fields {
		if !validIdentifierRE.MatchString(field) {
			return nil, fmt.Errorf("ListFieldSets: invalid field name %q (must match %s)", field, validIdentifierRE.String())
		}
	}

	rows, err := s.db.Query(`SELECT id, data FROM resources WHERE resource_type = ?`, resourceType)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []map[string]string
	seenRows := map[string]bool{}
	for rows.Next() {
		var id string
		var data []byte
		if err := rows.Scan(&id, &data); err != nil {
			return nil, err
		}
		var obj map[string]any
		if len(data) > 0 {
			var err error
			obj, err = DecodeJSONObject(data)
			if err != nil {
				return nil, fmt.Errorf("decode %s parent row %s: %w", resourceType, id, err)
			}
		}
		values := make(map[string]string, len(fields))
		complete := true
		for _, field := range fields {
			var value any
			if field == "id" {
				value = id
			} else {
				value = LookupFieldValue(obj, field)
			}
			valueString := ResourceIDString(value)
			if value == nil || valueString == "" {
				complete = false
				break
			}
			values[field] = valueString
		}
		if complete {
			keyParts := make([]string, 0, len(fields))
			for _, field := range fields {
				keyParts = append(keyParts, values[field])
			}
			key := strings.Join(keyParts, "\x00")
			if seenRows[key] {
				continue
			}
			seenRows[key] = true
			out = append(out, values)
		}
	}
	return out, 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()
}

// CascadeJunction names a junction table + the FK column referencing the
// reconciled resource's primary key, to be cleaned when a row is swept.
type CascadeJunction struct {
	Table    string
	FKColumn string
}

var (
	cascadeMu        sync.Mutex
	cascadeJunctions = map[string][]CascadeJunction{}
)

// RegisterCascadeJunction records a junction to clean when rows of resourceType
// are reconciled away. Used for runtime-created junctions (e.g. module_issues)
// that the generated schema does not declare.
//
// Registration is idempotent: re-registering the same (Table, FKColumn) for a
// resourceType is a no-op. The registry is a process-global with no removal path
// (registrations happen once at startup in the generated binary); dedupe keeps a
// repeated init() or a test that re-registers across sub-tests from accumulating
// duplicate cascades.
func RegisterCascadeJunction(resourceType string, j CascadeJunction) {
	cascadeMu.Lock()
	defer cascadeMu.Unlock()
	for _, existing := range cascadeJunctions[resourceType] {
		if existing == j {
			return
		}
	}
	cascadeJunctions[resourceType] = append(cascadeJunctions[resourceType], j)
}

// CascadeJunctionsFor returns the registered cascade junctions for resourceType.
func CascadeJunctionsFor(resourceType string) []CascadeJunction {
	cascadeMu.Lock()
	defer cascadeMu.Unlock()
	out := make([]CascadeJunction, len(cascadeJunctions[resourceType]))
	copy(out, cascadeJunctions[resourceType])
	return out
}

// ReconcilePartition hard-deletes local rows of resourceType in one partition
// (rows whose data JSON at genericScopeJSONPath equals scopeValue) whose primary
// key is NOT in seenIDs. It is the mark-and-sweep half of deletion mirroring;
// the caller must pass the COMPLETE, successfully-enumerated seen-ID set for the
// partition. Victims are computed from the generic resources table so that
// legacy rows lacking a typed projection are also cleaned. Cleans, per victim:
// the typed table row (firing its AFTER DELETE FTS triggers, if any), the
// generic resources_fts entry (manual, no triggers), the generic resources row,
// and each cascade junction. Returns the number of generic rows deleted.
func (s *Store) ReconcilePartition(resourceType, genericScopeJSONPath, scopeValue string, seenIDs []string, typedTable string, cascades []CascadeJunction) (int, error) {
	if genericScopeJSONPath == "" || scopeValue == "" {
		return 0, fmt.Errorf("reconcile %s: empty partition scope", resourceType)
	}
	s.writeMu.Lock()
	defer s.writeMu.Unlock()

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

	// Seen-set membership is tested in Go, not SQL. Parent-keyed dependent rows
	// carry a NUL-composite storage id ("<id>\x00<parent>", built by
	// resourceStorageID) while seenIDs holds the BARE API ids sync enumerated, so
	// each stored id must run through BareResourceID before the comparison. A SQL
	// seen-set is not viable here: SQLite string functions treat the embedded NUL
	// as a C-string terminator, so an instr/substr or `IN` test over a key
	// containing "\x00" silently truncates and mis-matches. BareResourceID is a
	// no-op for plain ids, so flat/non-composite partitions are unaffected.
	seen := make(map[string]struct{}, len(seenIDs))
	for _, id := range seenIDs {
		seen[id] = struct{}{}
	}

	// CASE guards against a malformed-JSON row aborting the victim scan:
	// a row we cannot parse is never a victim — it is skipped (never deleted).
	rows, err := tx.Query(
		`SELECT id FROM resources
		 WHERE resource_type = ?
		   AND (CASE WHEN json_valid(data) THEN json_extract(data, ?) END) = ?`,
		resourceType, genericScopeJSONPath, scopeValue,
	)
	if err != nil {
		return 0, fmt.Errorf("reconcile %s: select victims: %w", resourceType, err)
	}
	var victims []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			rows.Close()
			return 0, err
		}
		if _, ok := seen[BareResourceID(id)]; ok {
			continue // bare id was enumerated this run — keep the row
		}
		victims = append(victims, id)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return 0, err
	}

	// Safety: typedTable and cascade Table/FKColumn are TRUSTED generator/registration
	// metadata (schema-derived or RegisterCascadeJunction), not user input — Sprintf
	// interpolation here is intentional and safe.
	for _, id := range victims {
		if typedTable != "" {
			if _, err := tx.Exec(fmt.Sprintf(`DELETE FROM "%s" WHERE id = ?`, typedTable), id); err != nil {
				return 0, fmt.Errorf("reconcile %s: typed delete: %w", resourceType, err)
			}
		}
		if _, err := tx.Exec(`DELETE FROM resources_fts WHERE rowid = ?`, ftsRowID(resourceType, id)); err != nil {
			return 0, fmt.Errorf("reconcile %s: fts delete: %w", resourceType, err)
		}
		if _, err := tx.Exec(`DELETE FROM resources WHERE resource_type = ? AND id = ?`, resourceType, id); err != nil {
			return 0, fmt.Errorf("reconcile %s: generic delete: %w", resourceType, err)
		}
		// Cascade junction FKs hold the BARE entity id, never the NUL-composite
		// storage key, so strip the suffix before matching (no-op for plain ids).
		for _, c := range cascades {
			if _, err := tx.Exec(fmt.Sprintf(`DELETE FROM "%s" WHERE "%s" = ?`, c.Table, c.FKColumn), BareResourceID(id)); err != nil {
				return 0, fmt.Errorf("reconcile %s: cascade %s: %w", resourceType, c.Table, err)
			}
		}
	}

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

// 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 {
			return "", err
		}
		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)
				}
			}
		}
		if err := rows.Err(); err != nil {
			rows.Close()
			return "", err
		}
		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)
	}
}
