// Package db is the PostgreSQL persistence layer for spec.sr.ht. It maps the
// eight tables of schema.sql — space, document_id, proposal, agent_token,
// index_stamp, digest_mark, project and project_space — to core value types
// with plain database/sql and $n placeholders (no ORM).
//
// The layering rule from the design is what shapes this package: **git refs are
// the source of truth for whether a proposal exists and whether it merged;
// Postgres holds metadata that is reconstructable from git; the index and the
// render cache are pure caches.** So nothing here stores a document body, and
// every row is something the reconciler could rebuild from refs. The queries
// are correspondingly small: registry lookups, one state machine, and two
// key/value stamps.
//
// Design: a Store wraps a Querier — an interface satisfied by *sql.DB, *sql.Tx
// and *sql.Conn alike. This gives us two things at once:
//
//   - Context-first, middleware-compatible signatures. Production callers build
//     a Store from the connection that core-go's database middleware injects
//     into the request context (see FromContext, which reads the same *sql.DB
//     that database.Middleware installed). Every method takes ctx first and
//     threads it into the query for cancellation.
//
//   - Trivial test injection. Tests call NewStore(db) with a plain *sql.DB, no
//     HTTP context required.
//
// Because the wrapped value is an interface, a Store can be re-bound to an open
// transaction with WithTx(tx) or InTx(ctx, fn). That is what MergeProposal
// needs: recording the merge and re-pointing the affected document_id rows are
// one invariant, not two writes that may half-happen.
package db

import (
	"context"
	"database/sql"
	"errors"
	"fmt"

	"sourcecraft.dev/bigbes/sr-ht-core/database"
)

// Querier is the common subset of *sql.DB, *sql.Tx and *sql.Conn used by this
// package. Binding a Store to any of them keeps every method identical whether
// it runs standalone (autocommit) or inside a caller-managed transaction.
type Querier interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

// beginner is the subset of *sql.DB that can open a transaction. A Store bound
// to a *sql.Tx does not satisfy it, which is how InTx refuses to nest instead of
// silently running the body outside a transaction.
type beginner interface {
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
}

// Store is the entry point for all queries in this package.
type Store struct {
	q Querier
}

// NewStore builds a Store over a database handle (or any Querier). Tests pass a
// plain *sql.DB; production wiring passes the shared pool.
func NewStore(q Querier) *Store {
	return &Store{q: q}
}

// FromContext builds a Store over the *sql.DB that core-go's database.Middleware
// installed in ctx. It panics (via database.DBForContext) if no database is
// present in the context — a programming error, never a runtime condition to
// recover from. We wrap the pooled *sql.DB rather than checking out a *sql.Conn
// (database.ForContext) so the Store has no connection to leak; the pool manages
// connection lifetime and ctx still bounds each query.
func FromContext(ctx context.Context) *Store {
	return &Store{q: database.DBForContext(ctx)}
}

// WithTx returns a Store that runs every query on tx instead of the pool. Used
// where the caller already owns a transaction and wants these queries inside it.
func (s *Store) WithTx(tx *sql.Tx) *Store {
	return &Store{q: tx}
}

// InTx runs fn inside a transaction, on a Store bound to it. fn returning an
// error rolls back and the error is returned unwrapped, so callers can still
// match sentinels with errors.Is. A panic in fn rolls back and re-panics rather
// than leaving the transaction open.
//
// It requires the Store to wrap something that can begin a transaction (the
// pool). A Store already bound to a *sql.Tx returns ErrNoTransaction: nesting is
// a caller bug, and quietly running the body without transactional isolation
// would defeat the only reason this method exists.
func (s *Store) InTx(ctx context.Context, fn func(*Store) error) error {
	b, ok := s.q.(beginner)
	if !ok {
		return ErrNoTransaction
	}
	tx, err := b.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin transaction: %w", err)
	}
	committed := false
	defer func() {
		if !committed {
			tx.Rollback()
		}
	}()
	if err := fn(&Store{q: tx}); err != nil {
		return err
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit transaction: %w", err)
	}
	committed = true
	return nil
}

// Typed errors returned by this package. Callers match them with errors.Is.
var (
	// ErrNotFound is returned when a lookup, update or delete matched no row.
	ErrNotFound = errors.New("db: not found")

	// ErrSpaceExists is returned by CreateSpace when the owner already has a
	// space with that name (uq_space_owner_name violation).
	ErrSpaceExists = errors.New("db: space already exists")

	// ErrProjectExists is returned by CreateProject when the owner already has
	// a project with that name (uq_project_owner_name violation).
	ErrProjectExists = errors.New("db: project already exists")

	// ErrDocIDTaken is returned when a document ID is already registered.
	// Document IDs are globally unique, so this is the registry refusing a
	// collision — the invariant that lets [[SPEC-0007]] resolve the same way
	// everywhere and lets a later import not clash with what is already here.
	ErrDocIDTaken = errors.New("db: document id already registered")

	// ErrDocIDDuplicate is returned when one batch of documents carries the
	// same ID twice. Distinct from ErrDocIDTaken: the collision is inside the
	// push itself, not against the registry.
	ErrDocIDDuplicate = errors.New("db: document id appears twice in one batch")

	// ErrTokenExists is returned by CreateAgentToken when that exact token is
	// already registered (agent_token.token_hash UNIQUE).
	ErrTokenExists = errors.New("db: agent token already registered")

	// ErrTokenRevoked is returned by AuthenticateAgentToken for a token that
	// exists but has been revoked. Kept distinct from ErrNotFound so the audit
	// log can say which happened; both map to 401 at the API boundary.
	ErrTokenRevoked = errors.New("db: agent token revoked")

	// ErrProposalNotOpen is returned by DeleteOpenProposal for a proposal that
	// exists but has already been resolved. Kept distinct from ErrNotFound so
	// the reconciler can tell "the row is gone", which is the state it wanted,
	// from "the proposal merged under us", which means the repair it planned no
	// longer applies.
	ErrProposalNotOpen = errors.New("db: proposal is not open")

	// ErrNoTransaction is returned by InTx when the Store is not bound to
	// something that can begin one (i.e. it is already inside a transaction).
	ErrNoTransaction = errors.New("db: store cannot begin a transaction")
)

// rowScanner is satisfied by both *sql.Row and *sql.Rows.
type rowScanner interface {
	Scan(dest ...any) error
}

// requireOne turns a zero-rows-affected result into ErrNotFound so that missing
// targets surface loudly instead of passing silently.
func requireOne(res sql.Result, what string) error {
	n, err := res.RowsAffected()
	if err != nil {
		return fmt.Errorf("%s: rows affected: %w", what, err)
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

// nullable maps the empty string to a SQL NULL, for the columns the schema
// leaves nullable (proposal.rationale). Storing "" and NULL interchangeably
// would make round-tripping lossy.
func nullable(s string) any {
	if s == "" {
		return nil
	}
	return s
}