package storage

import (
	"database/sql"
	_ "embed"
	"fmt"

	_ "modernc.org/sqlite"
)

//go:embed schema.sql
var schemaSQLBytes []byte

// DB wraps the sql.DB connection with KeyHunter-specific behavior.
type DB struct {
	sql *sql.DB
}

// Open opens or creates a SQLite database at path, runs embedded schema migrations,
// and enables WAL mode for better concurrent read performance.
// Use ":memory:" for tests.
func Open(path string) (*DB, error) {
	sqlDB, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, fmt.Errorf("opening database: %w", err)
	}

	// Enable WAL mode for concurrent reads
	if _, err := sqlDB.Exec("PRAGMA journal_mode=WAL"); err != nil {
		sqlDB.Close()
		return nil, fmt.Errorf("enabling WAL mode: %w", err)
	}

	// Enable foreign keys
	if _, err := sqlDB.Exec("PRAGMA foreign_keys=ON"); err != nil {
		sqlDB.Close()
		return nil, fmt.Errorf("enabling foreign keys: %w", err)
	}

	// Run schema migrations
	if _, err := sqlDB.Exec(string(schemaSQLBytes)); err != nil {
		sqlDB.Close()
		return nil, fmt.Errorf("running schema migrations: %w", err)
	}

	// Idempotent in-place migration for pre-Phase-5 databases that created
	// the findings table before the verify_* columns existed.
	if err := migrateFindingsVerifyColumns(sqlDB); err != nil {
		sqlDB.Close()
		return nil, fmt.Errorf("migrating findings verify columns: %w", err)
	}

	return &DB{sql: sqlDB}, nil
}

// migrateFindingsVerifyColumns adds the Phase 5 verify_* columns to an
// existing findings table when they are missing. Uses PRAGMA table_info to
// detect the current column set (works on SQLite versions that lack
// ADD COLUMN IF NOT EXISTS).
func migrateFindingsVerifyColumns(sqlDB *sql.DB) error {
	rows, err := sqlDB.Query("PRAGMA table_info(findings)")
	if err != nil {
		return fmt.Errorf("reading findings schema: %w", err)
	}
	existing := map[string]bool{}
	for rows.Next() {
		var cid int
		var name, ctype string
		var notnull, pk int
		var dflt sql.NullString
		if err := rows.Scan(&cid, &name, &ctype, &notnull, &dflt, &pk); err != nil {
			rows.Close()
			return fmt.Errorf("scanning findings schema row: %w", err)
		}
		existing[name] = true
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return err
	}
	rows.Close()

	type colDef struct {
		name string
		ddl  string
	}
	wanted := []colDef{
		{"verified", "ALTER TABLE findings ADD COLUMN verified INTEGER NOT NULL DEFAULT 0"},
		{"verify_status", "ALTER TABLE findings ADD COLUMN verify_status TEXT NOT NULL DEFAULT ''"},
		{"verify_http_code", "ALTER TABLE findings ADD COLUMN verify_http_code INTEGER NOT NULL DEFAULT 0"},
		{"verify_metadata_json", "ALTER TABLE findings ADD COLUMN verify_metadata_json TEXT"},
	}
	for _, c := range wanted {
		if existing[c.name] {
			continue
		}
		if _, err := sqlDB.Exec(c.ddl); err != nil {
			return fmt.Errorf("adding column %s: %w", c.name, err)
		}
	}
	return nil
}

// Close closes the underlying database connection.
func (db *DB) Close() error {
	return db.sql.Close()
}

// SQL returns the underlying sql.DB for advanced use cases.
func (db *DB) SQL() *sql.DB {
	return db.sql
}