package database

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

	"vidarchive/internal/config"

	_ "modernc.org/sqlite"
)

func New(cfg *config.Config) (*sql.DB, error) {
	if err := os.MkdirAll(filepath.Dir(cfg.DBPath), 0755); err != nil {
		return nil, fmt.Errorf("create db dir: %w", err)
	}

	db, err := sql.Open("sqlite", cfg.DBPath)
	if err != nil {
		return nil, fmt.Errorf("open db: %w", err)
	}

	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)

	if _, err := db.Exec("PRAGMA journal_mode=WAL"); err != nil {
		return nil, fmt.Errorf("enable wal mode: %w", err)
	}
	if _, err := db.Exec("PRAGMA busy_timeout=5000"); err != nil {
		return nil, fmt.Errorf("set busy timeout: %w", err)
	}
	// Migrations run with foreign keys off: the table rebuilds below drop and
	// rename tables, which enforcement would reject mid-flight.
	if err := migrate(db); err != nil {
		return nil, fmt.Errorf("migrate: %w", err)
	}

	// SQLite ignores FOREIGN KEY clauses unless this is enabled per connection,
	// so without it the declared constraints are decorative.
	if _, err := db.Exec("PRAGMA foreign_keys=ON"); err != nil {
		return nil, fmt.Errorf("enable foreign keys: %w", err)
	}

	return db, nil
}

// Checkpoint flushes the write-ahead log into the main database file. Call it
// before closing so a killed process doesn't leave a large uncheckpointed WAL.
func Checkpoint(db *sql.DB) error {
	if _, err := db.Exec("PRAGMA wal_checkpoint(TRUNCATE)"); err != nil {
		return fmt.Errorf("wal checkpoint: %w", err)
	}
	return nil
}

func migrate(db *sql.DB) error {
	if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY)`); err != nil {
		return fmt.Errorf("create migrations table: %w", err)
	}

	var currentVersion int
	if err := db.QueryRow(`SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 1`).Scan(&currentVersion); err != nil && err != sql.ErrNoRows {
		return fmt.Errorf("get current version: %w", err)
	}

	migrations := []struct {
		version int
		sql     string
	}{
		{1, `CREATE TABLE IF NOT EXISTS presets (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL UNIQUE,
			description TEXT,
			is_default INTEGER DEFAULT 0,
			format TEXT DEFAULT 'best',
			quality TEXT,
			extract_audio INTEGER DEFAULT 0,
			audio_format TEXT DEFAULT 'mp3',
			embed_subs INTEGER DEFAULT 0,
			sub_langs TEXT DEFAULT 'en',
			embed_thumbnail INTEGER DEFAULT 1,
			embed_metadata INTEGER DEFAULT 1,
			write_info_json INTEGER DEFAULT 1,
			output_template TEXT DEFAULT '%(title)s.%(ext)s',
			custom_flags TEXT,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)`},
		{3, `CREATE TABLE IF NOT EXISTS downloads (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			url TEXT NOT NULL,
			status TEXT DEFAULT 'queued',
			logs TEXT,
			error_message TEXT,
			preset_id INTEGER,
			format_override TEXT,
			custom_flags TEXT,
			started_at DATETIME,
			completed_at DATETIME,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (preset_id) REFERENCES presets(id)
		)`},
		{4, `CREATE TABLE IF NOT EXISTS settings (
			key TEXT PRIMARY KEY,
			value TEXT
		)`},
		{5, `INSERT OR IGNORE INTO settings (key, value) VALUES ('refresh_interval', '5')`},
		{6, `INSERT OR IGNORE INTO settings (key, value) VALUES ('auto_refresh_library', '0')`},
		{7, `INSERT OR IGNORE INTO settings (key, value) VALUES ('auto_refresh_downloads', '1')`},
		{8, `ALTER TABLE presets ADD COLUMN format_mode TEXT DEFAULT 'preset'`},
		{9, `ALTER TABLE presets ADD COLUMN custom_format TEXT`},
		{10, `ALTER TABLE downloads ADD COLUMN output_dir TEXT`},
		{11, `DROP TABLE IF EXISTS media`},
		{12, `ALTER TABLE presets DROP COLUMN output_template`},
		{13, `CREATE TABLE IF NOT EXISTS subscriptions (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL,
			url TEXT NOT NULL,
			enabled INTEGER DEFAULT 1,
			refresh_mode TEXT DEFAULT 'overwrite',
			schedule_kind TEXT DEFAULT 'daily',
			cron_expr TEXT DEFAULT '0 3 * * *',
			preset_id INTEGER,
			format_override TEXT,
			custom_flags TEXT,
			output_dir TEXT NOT NULL,
			prune_removed INTEGER DEFAULT 0,
			last_run_at DATETIME,
			next_run_at DATETIME,
			last_status TEXT,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (preset_id) REFERENCES presets(id)
		)`},
		{14, `ALTER TABLE downloads ADD COLUMN subscription_id INTEGER`},
		// Rebuild downloads so its foreign keys survive enforcement: deleting a
		// preset or subscription now nulls the reference instead of failing, which
		// matches how ExecuteDownload already degrades to the default preset.
		// subscription_id gains the foreign key it never had.
		{15, `CREATE TABLE downloads_new (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			url TEXT NOT NULL,
			status TEXT DEFAULT 'queued',
			logs TEXT,
			error_message TEXT,
			preset_id INTEGER,
			format_override TEXT,
			custom_flags TEXT,
			output_dir TEXT,
			subscription_id INTEGER,
			started_at DATETIME,
			completed_at DATETIME,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (preset_id) REFERENCES presets(id) ON DELETE SET NULL,
			FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL
		);
		INSERT INTO downloads_new (id, url, status, logs, error_message, preset_id,
			format_override, custom_flags, output_dir, subscription_id, started_at,
			completed_at, created_at)
			SELECT id, url, status, logs, error_message, preset_id, format_override,
				custom_flags, output_dir, subscription_id, started_at, completed_at,
				created_at FROM downloads;
		DROP TABLE downloads;
		ALTER TABLE downloads_new RENAME TO downloads;`},
		{16, `CREATE TABLE subscriptions_new (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL,
			url TEXT NOT NULL,
			enabled INTEGER DEFAULT 1,
			refresh_mode TEXT DEFAULT 'overwrite',
			schedule_kind TEXT DEFAULT 'daily',
			cron_expr TEXT DEFAULT '0 3 * * *',
			preset_id INTEGER,
			format_override TEXT,
			custom_flags TEXT,
			output_dir TEXT NOT NULL,
			prune_removed INTEGER DEFAULT 0,
			last_run_at DATETIME,
			next_run_at DATETIME,
			last_status TEXT,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (preset_id) REFERENCES presets(id) ON DELETE SET NULL
		);
		INSERT INTO subscriptions_new SELECT id, name, url, enabled, refresh_mode,
			schedule_kind, cron_expr, preset_id, format_override, custom_flags,
			output_dir, prune_removed, last_run_at, next_run_at, last_status,
			created_at FROM subscriptions;
		DROP TABLE subscriptions;
		ALTER TABLE subscriptions_new RENAME TO subscriptions;`},
		// Indexes for the hot predicates: the queue poll and claim filter on
		// status, the scheduler's dedup check filters on subscription_id, and
		// GetDue filters on enabled + next_run_at.
		{17, `CREATE INDEX IF NOT EXISTS idx_downloads_status ON downloads(status);
		CREATE INDEX IF NOT EXISTS idx_downloads_subscription_id ON downloads(subscription_id);
		CREATE INDEX IF NOT EXISTS idx_downloads_created_at ON downloads(created_at);
		CREATE INDEX IF NOT EXISTS idx_subscriptions_due ON subscriptions(enabled, next_run_at);`},
	}

	for _, m := range migrations {
		if m.version <= currentVersion {
			continue
		}
		// Each migration runs in its own transaction so a multi-statement failure
		// can't leave the schema half-applied with no version recorded.
		if err := applyMigration(db, m.version, m.sql); err != nil {
			return err
		}
	}
	return nil
}

func applyMigration(db *sql.DB, version int, stmt string) error {
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("migration %d: begin: %w", version, err)
	}
	defer tx.Rollback()

	if _, err := tx.Exec(stmt); err != nil {
		return fmt.Errorf("migration %d: %w", version, err)
	}
	if _, err := tx.Exec(`INSERT INTO schema_migrations (version) VALUES (?)`, version); err != nil {
		return fmt.Errorf("record migration %d: %w", version, err)
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("migration %d: commit: %w", version, err)
	}
	return nil
}