// Package store owns harness.db: connection, migrations, low-level SQL helpers.
// No business logic lives here. Other modules access SQL through this package.
//
// Concurrency: SQLite is opened in WAL mode with busy_timeout. Most writes use
// Tx (default DEFERRED transaction), fine because WAL allows readers concurrent
// with a single writer. Queue dequeue and other ops that read-then-write inside
// the same transaction use TxImmediate, which acquires the writer lock up-front
// to avoid the "I read, then someone else got the writer lock" deadlock.
package store

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

	_ "modernc.org/sqlite"
)

// Store wraps a sql.DB pointing at harness.db.
type Store struct {
	db   *sql.DB
	path string
	mu   sync.Mutex // serializes BEGIN IMMEDIATE transactions
}

// Open opens (or creates) the harness database at path and runs migrations.
func Open(path string) (*Store, error) {
	if path == "" {
		return nil, errors.New("store: empty path")
	}
	dsn := path + "?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=synchronous(NORMAL)&_pragma=foreign_keys(ON)"
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("store: open: %w", err)
	}
	db.SetMaxOpenConns(8)
	db.SetMaxIdleConns(4)
	if err := db.Ping(); err != nil {
		db.Close()
		return nil, fmt.Errorf("store: ping: %w", err)
	}
	s := &Store{db: db, path: path}
	if err := s.migrate(context.Background()); err != nil {
		db.Close()
		return nil, fmt.Errorf("store: migrate: %w", err)
	}
	return s, nil
}

// DB returns the underlying *sql.DB for ad-hoc reads.
func (s *Store) DB() *sql.DB { return s.db }

// Path returns the file path the store was opened with.
func (s *Store) Path() string { return s.path }

// Close closes the underlying database.
func (s *Store) Close() error { return s.db.Close() }

// Querier is the shared shape exposed inside transactions. Both *sql.Tx and
// our pinned-conn adapter implement it, so helpers in other packages can take
// Querier and work in either Tx mode.
type Querier interface {
	Exec(query string, args ...any) (sql.Result, error)
	Query(query string, args ...any) (*sql.Rows, error)
	QueryRow(query string, args ...any) *sql.Row
}

// Tx runs fn inside a transaction. SQLite WAL is single-writer, so we
// serialize all write transactions through s.mu to avoid SQLITE_BUSY
// stalls under high concurrency. Readers (db.QueryRow, db.Query) are not
// blocked because WAL allows concurrent readers with one writer.
func (s *Store) Tx(ctx context.Context, fn func(Querier) error) error {
	s.mu.Lock()
	defer s.mu.Unlock()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	if err := fn(txQuerier{tx, ctx}); err != nil {
		_ = tx.Rollback()
		return err
	}
	return tx.Commit()
}

// TxImmediate runs fn inside a BEGIN IMMEDIATE transaction. Use for the queue
// dequeue path and any other read-then-write inside one transaction. Serialized
// via s.mu — we never have two writers contending at the database/sql layer.
func (s *Store) TxImmediate(ctx context.Context, fn func(Querier) error) error {
	s.mu.Lock()
	defer s.mu.Unlock()
	conn, err := s.db.Conn(ctx)
	if err != nil {
		return err
	}
	defer conn.Close()
	if _, err := conn.ExecContext(ctx, "BEGIN IMMEDIATE"); err != nil {
		return fmt.Errorf("store: BEGIN IMMEDIATE: %w", err)
	}
	q := connQuerier{conn: conn, ctx: ctx}
	if err := fn(q); err != nil {
		_, _ = conn.ExecContext(ctx, "ROLLBACK")
		return err
	}
	if _, err := conn.ExecContext(ctx, "COMMIT"); err != nil {
		_, _ = conn.ExecContext(ctx, "ROLLBACK")
		return err
	}
	return nil
}

// Now returns the canonical wall-clock time used by store rows.
func Now() time.Time { return time.Now().UTC() }

// FmtTime formats a time.Time in the canonical store text format. All TEXT
// timestamp columns use this format (ISO-8601, UTC, ms precision).
func FmtTime(t time.Time) string { return t.UTC().Format(timeFmt) }

type connQuerier struct {
	conn *sql.Conn
	ctx  context.Context
}

func (c connQuerier) Exec(q string, args ...any) (sql.Result, error) {
	return c.conn.ExecContext(c.ctx, q, args...)
}
func (c connQuerier) Query(q string, args ...any) (*sql.Rows, error) {
	return c.conn.QueryContext(c.ctx, q, args...)
}
func (c connQuerier) QueryRow(q string, args ...any) *sql.Row {
	return c.conn.QueryRowContext(c.ctx, q, args...)
}

type txQuerier struct {
	tx  *sql.Tx
	ctx context.Context
}

func (t txQuerier) Exec(q string, args ...any) (sql.Result, error) {
	return t.tx.ExecContext(t.ctx, q, args...)
}
func (t txQuerier) Query(q string, args ...any) (*sql.Rows, error) {
	return t.tx.QueryContext(t.ctx, q, args...)
}
func (t txQuerier) QueryRow(q string, args ...any) *sql.Row {
	return t.tx.QueryRowContext(t.ctx, q, args...)
}