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

	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 [%(id)s].%(ext)s',
			custom_flags TEXT,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)`},
		{2, `CREATE TABLE IF NOT EXISTS media (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			url TEXT NOT NULL,
			title TEXT,
			description TEXT,
			duration INTEGER,
			filepath TEXT NOT NULL,
			relative_path TEXT NOT NULL,
			is_audio INTEGER DEFAULT 0,
			has_embedded_thumbnail INTEGER DEFAULT 0,
			info_json_path TEXT,
			playlist_id TEXT,
			playlist_index INTEGER,
			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',
			progress_percent INTEGER DEFAULT 0,
			current_speed TEXT,
			eta TEXT,
			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`},
	}

	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
}