// Package savedsearch implements the saved-search CRUD API: a per-owner list of
// named queries that can be stored, renamed, updated, and deleted. The package
// layers as Repository (raw SQL) and Handler (HTTP boundary). Both are steward
// services.
//
// Owner-scope invariant (IV2): the owner is always derived from the
// authenticated identity; no ?owner= query parameter is accepted on any
// saved-search route.
//
// Uniqueness invariant (IV3): (owner, name) is a composite primary key;
// duplicate names per owner produce a CONFLICT-coded error, never a silent
// overwrite.
package savedsearch

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

	"go.bigb.es/auxilia/culpa"
	"modernc.org/sqlite"
	sqlite3 "modernc.org/sqlite/lib"

	"sourcecraft.dev/bigbes/lethe/internal/platform/database"
)

// SavedSearch is the row shape for the saved_searches table. The Owner field
// carries json:"-" so it is never included in wire responses (IV2); all other
// fields use snake_case JSON tags.
type SavedSearch struct {
	Owner     string `db:"owner"      json:"-"`
	Name      string `db:"name"       json:"name"`
	Query     string `db:"query"      json:"query"`
	CreatedAt int64  `db:"created_at" json:"created_at"`
	UpdatedAt int64  `db:"updated_at" json:"updated_at"`
}

// Repository is the SQL steward for the saved_searches table. It is stateless
// beyond its injected dependencies; Init is empty.
type Repository struct {
	Database *database.Database `inject:""`
}

// Init satisfies the steward Initer contract. Nothing to set up — the
// underlying *sqlx.DB is owned by the Database steward.
func (r *Repository) Init(_ context.Context) error { return nil }

// List returns all saved searches for the given owner ordered by updated_at
// DESC. Returns a non-nil zero-length slice when no rows are present so
// JSON-encoding produces [] rather than null.
func (r *Repository) List(ctx context.Context, owner string) ([]SavedSearch, error) {
	const q = `SELECT owner, name, query, created_at, updated_at
	            FROM saved_searches
	            WHERE owner = ?
	            ORDER BY updated_at DESC`
	out := make([]SavedSearch, 0)
	if err := r.Database.DB.SelectContext(ctx, &out, q, owner); err != nil {
		return nil, culpa.WithCode(culpa.Wrap(err, "list saved searches"), "DB_QUERY")
	}
	return out, nil
}

// Create inserts a new saved search row. On primary-key collision (same owner
// + name already exists) it returns a CONFLICT-coded error with a public
// message suitable for surfacing to clients (IV3).
func (r *Repository) Create(ctx context.Context, s SavedSearch) error {
	const q = `INSERT INTO saved_searches (owner, name, query, created_at, updated_at)
	            VALUES (?, ?, ?, ?, ?)`
	_, err := r.Database.DB.ExecContext(ctx, q, s.Owner, s.Name, s.Query, s.CreatedAt, s.UpdatedAt)
	if err != nil {
		if isSQLiteConstraint(err) {
			return culpa.WithCode(
				culpa.WithPublic(culpa.Wrap(err, "create saved search"), "saved search with that name already exists"),
				"CONFLICT",
			)
		}
		return culpa.WithCode(culpa.Wrap(err, "create saved search"), "DB_QUERY")
	}
	return nil
}

// Update modifies an existing saved search identified by (owner, oldName).
// newName and newQuery are optional; at least one must be non-nil (the handler
// enforces this precondition). updated_at is always set to now.
//
// The rename + read are performed inside a single transaction (BeginTxx) so
// the returned SavedSearch reflects the post-update state atomically.
//
// RowsAffected == 0 → NOT_FOUND; primary-key collision on rename → CONFLICT.
func (r *Repository) Update(ctx context.Context, owner, oldName string, newName *string, newQuery *string, now int64) (SavedSearch, error) {
	tx, err := r.Database.DB.BeginTxx(ctx, nil)
	if err != nil {
		return SavedSearch{}, culpa.WithCode(culpa.Wrap(err, "begin update transaction"), "DB_QUERY")
	}
	defer func() { _ = tx.Rollback() }()

	// Build a dynamic SET clause: name and/or query, plus updated_at.
	var sets []string
	var args []any
	if newName != nil {
		sets = append(sets, "name = ?")
		args = append(args, *newName)
	}
	if newQuery != nil {
		sets = append(sets, "query = ?")
		args = append(args, *newQuery)
	}
	sets = append(sets, "updated_at = ?")
	args = append(args, now)
	// WHERE clause args.
	args = append(args, owner, oldName)

	updateQ := "UPDATE saved_searches SET " + strings.Join(sets, ", ") + " WHERE owner = ? AND name = ?"
	res, err := tx.ExecContext(ctx, updateQ, args...)
	if err != nil {
		if isSQLiteConstraint(err) {
			return SavedSearch{}, culpa.WithCode(
				culpa.WithPublic(culpa.Wrap(err, "update saved search"), "saved search with that name already exists"),
				"CONFLICT",
			)
		}
		return SavedSearch{}, culpa.WithCode(culpa.Wrap(err, "update saved search"), "DB_QUERY")
	}

	n, err := res.RowsAffected()
	if err != nil {
		return SavedSearch{}, culpa.WithCode(culpa.Wrap(err, "rows affected"), "DB_QUERY")
	}
	if n == 0 {
		return SavedSearch{}, culpa.WithCode(
			culpa.WithPublic(culpa.New("saved search not found"), "saved search not found"),
			"NOT_FOUND",
		)
	}

	// Determine the effective name (may have changed on rename).
	effectiveName := oldName
	if newName != nil {
		effectiveName = *newName
	}

	const selectQ = `SELECT owner, name, query, created_at, updated_at
	                 FROM saved_searches WHERE owner = ? AND name = ?`
	var updated SavedSearch
	if err := tx.GetContext(ctx, &updated, selectQ, owner, effectiveName); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return SavedSearch{}, culpa.WithCode(culpa.New("saved search not found after update"), "NOT_FOUND")
		}
		return SavedSearch{}, culpa.WithCode(culpa.Wrap(err, "read updated saved search"), "DB_QUERY")
	}

	if err := tx.Commit(); err != nil {
		return SavedSearch{}, culpa.WithCode(culpa.Wrap(err, "commit update transaction"), "DB_QUERY")
	}
	return updated, nil
}

// Delete removes the saved search identified by (owner, name). Returns
// NOT_FOUND when no row is deleted.
func (r *Repository) Delete(ctx context.Context, owner, name string) error {
	const q = `DELETE FROM saved_searches WHERE owner = ? AND name = ?`
	res, err := r.Database.DB.ExecContext(ctx, q, owner, name)
	if err != nil {
		return culpa.WithCode(culpa.Wrap(err, "delete saved search"), "DB_QUERY")
	}
	n, err := res.RowsAffected()
	if err != nil {
		return culpa.WithCode(culpa.Wrap(err, "rows affected"), "DB_QUERY")
	}
	if n == 0 {
		return culpa.WithCode(
			culpa.WithPublic(culpa.New("saved search not found"), "saved search not found"),
			"NOT_FOUND",
		)
	}
	return nil
}

// isSQLiteConstraint returns true when err is a SQLite constraint violation
// (PRIMARY KEY, UNIQUE, NOT NULL, or generic CONSTRAINT). This mirrors the
// pattern used in internal/domain/ingest/repository.go.
func isSQLiteConstraint(err error) bool {
	var se *sqlite.Error
	if !errors.As(err, &se) {
		return false
	}
	switch se.Code() {
	case sqlite3.SQLITE_CONSTRAINT,
		sqlite3.SQLITE_CONSTRAINT_CHECK,
		sqlite3.SQLITE_CONSTRAINT_NOTNULL,
		sqlite3.SQLITE_CONSTRAINT_PRIMARYKEY,
		sqlite3.SQLITE_CONSTRAINT_UNIQUE:
		return true
	}
	return false
}