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)
	}

	if err := migrate(db); err != nil {
		return nil, fmt.Errorf("migrate: %w", err)
	}

	return db, 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`},
	}

	for _, m := range migrations {
		if m.version <= currentVersion {
			continue
		}
		if _, err := db.Exec(m.sql); err != nil {
			return fmt.Errorf("migration %d: %w", m.version, err)
		}
		if _, err := db.Exec(`INSERT INTO schema_migrations (version) VALUES (?)`, m.version); err != nil {
			return fmt.Errorf("record migration %d: %w", m.version, err)
		}
	}
	return nil
}