// Package store opens the SQLite-backed persistence layer used by the broker
// for OAuth clients, authorization codes, access tokens, and refresh tokens.
//
// Migrations are embedded under migrations/NNNN_name.sql and applied in
// lexicographic order on Open. A schema_migrations table tracks which
// versions have been applied; re-opening an up-to-date database is a no-op.
//
// The current schema (phase 1) only contains a broker_meta table. The real
// OAuth tables ship with phase 2.
package store

import (
	"context"
	"database/sql"
	"fmt"
	"net/url"
	"path/filepath"

	_ "modernc.org/sqlite" // registers the "sqlite" database/sql driver
)

// Store wraps a *sql.DB opened against a SQLite file, with schema migrations
// already applied. It is safe for concurrent use.
type Store struct {
	db   *sql.DB
	path string
}

// Open opens (or creates) the SQLite database at path, enables WAL mode and
// foreign-key enforcement, then applies any pending schema migrations. The
// returned Store must be closed with Close to release file handles.
func Open(ctx context.Context, path string) (*Store, error) {
	abs, err := filepath.Abs(path)
	if err != nil {
		return nil, fmt.Errorf("store: resolve path %q: %w", path, err)
	}

	db, err := sql.Open("sqlite", buildDSN(abs))
	if err != nil {
		return nil, fmt.Errorf("store: open %q: %w", abs, err)
	}

	// Fail early if the path is unreachable (e.g. parent dir missing). sql.Open
	// is lazy and won't touch the file until first use.
	if err := db.PingContext(ctx); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("store: ping %q: %w", abs, err)
	}

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

// DB exposes the underlying *sql.DB for packages that need to issue queries.
func (s *Store) DB() *sql.DB { return s.db }

// Path returns the absolute path of the SQLite file.
func (s *Store) Path() string { return s.path }

// Ping verifies connectivity. Suitable for use in /healthz.
func (s *Store) Ping(ctx context.Context) error {
	return s.db.PingContext(ctx)
}

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

// buildDSN constructs a modernc.org/sqlite DSN with the pragmas we always
// want: WAL for better reader/writer concurrency, foreign-key enforcement
// (off by default in SQLite), and a 5-second busy timeout to absorb brief
// contention without surfacing SQLITE_BUSY to the app.
func buildDSN(path string) string {
	q := url.Values{}
	q.Add("_pragma", "journal_mode(WAL)")
	q.Add("_pragma", "foreign_keys(ON)")
	q.Add("_pragma", "busy_timeout(5000)")
	q.Add("_pragma", "synchronous(NORMAL)")
	return "file:" + path + "?" + q.Encode()
}