// Package db owns SQLite access for CableGUI: migrations runner + the
// query layer (store.go). The Store wraps a *sql.DB with helpers; tests
// and the HTTP layer take a *Store, never a raw *sql.DB.
package db

import (
	"database/sql"
	"fmt"

	_ "modernc.org/sqlite"
)

// Open opens (or creates) the SQLite file at path and returns a Store
// with WAL + foreign keys + busy_timeout configured.
func Open(path string) (*Store, error) {
	// `_pragma` query params are honoured by modernc.org/sqlite for
	// connection-time PRAGMA setup. journal_mode WAL is persistent
	// across opens; the others apply per-connection.
	dsn := fmt.Sprintf(
		"file:%s?_pragma=journal_mode(WAL)&_pragma=foreign_keys(ON)&_pragma=busy_timeout(5000)",
		path,
	)
	d, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("open sqlite: %w", err)
	}
	if err := d.Ping(); err != nil {
		_ = d.Close()
		return nil, fmt.Errorf("ping sqlite: %w", err)
	}
	// Single writer keeps things deterministic for a local-LAN tool;
	// reads scale fine in WAL.
	d.SetMaxOpenConns(1)
	return &Store{db: d}, nil
}

// Store is the application's handle on the SQLite database.
type Store struct {
	db *sql.DB
}

// DB returns the underlying *sql.DB. Used by Migrate and (sparingly) by
// callers that need a raw query escape hatch.
func (s *Store) DB() *sql.DB { return s.db }

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