// Package db wraps the two databases the Command Center owns: SQLite for
// transactional state and DuckDB for columnar analytics. Each open function
// returns a *sql.DB that the caller closes at shutdown.
package db

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"

	_ "modernc.org/sqlite"
)

// OpenSQLite opens (or creates) the SQLite database at path with the pragmas
// recommended in PROJECT.md Appendix A: WAL mode, foreign keys on, normal
// synchronous mode, ~64 MB cache, and memory-resident temp storage.
//
// The parent directory is created with mode 0o755 if it does not exist.
func OpenSQLite(path string) (*sql.DB, error) {
	if dir := filepath.Dir(path); dir != "" {
		if err := os.MkdirAll(dir, 0o755); err != nil {
			return nil, fmt.Errorf("sqlite: mkdir %s: %w", dir, err)
		}
	}

	// modernc's connection string supports the same _pragma=... convention as
	// mattn's driver. Passing them here means every connection in the pool
	// gets the same settings.
	dsn := path +
		"?_pragma=journal_mode(WAL)" +
		"&_pragma=foreign_keys(ON)" +
		"&_pragma=synchronous(NORMAL)" +
		"&_pragma=cache_size(-64000)" +
		"&_pragma=temp_store(MEMORY)" +
		"&_pragma=busy_timeout(5000)"

	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("sqlite: open: %w", err)
	}
	// modernc/sqlite is safe with multiple connections, but a single writer
	// avoids spurious SQLITE_BUSY in WAL mode under contention.
	db.SetMaxOpenConns(1)
	if err := db.Ping(); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("sqlite: ping: %w", err)
	}
	return db, nil
}