package main

import (
	"database/sql"

	_ "modernc.org/sqlite"
)

var db *sql.DB

func openDB(path string) error {
	d, err := sql.Open("sqlite", path+"?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)")
	if err != nil {
		return err
	}
	d.SetMaxOpenConns(1)
	if err := d.Ping(); err != nil {
		return err
	}
	db = d
	return nil
}

func migrate() error {
	stmts := []string{
		`CREATE TABLE IF NOT EXISTS users (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			username TEXT NOT NULL UNIQUE,
			password_hash TEXT NOT NULL,
			display_name TEXT NOT NULL DEFAULT '',
			language TEXT NOT NULL DEFAULT 'en',
			is_system_admin INTEGER NOT NULL DEFAULT 0,
			created_at TEXT NOT NULL DEFAULT (datetime('now'))
		)`,
		`CREATE TABLE IF NOT EXISTS sessions (
			token TEXT PRIMARY KEY,
			user_id INTEGER NOT NULL,
			expires_at TEXT NOT NULL,
			FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
		)`,
		`CREATE TABLE IF NOT EXISTS competitions (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL,
			allow_any_scorer_edit INTEGER NOT NULL DEFAULT 0,
			created_at TEXT NOT NULL DEFAULT (datetime('now'))
		)`,
		`CREATE TABLE IF NOT EXISTS competition_users (
			competition_id INTEGER NOT NULL,
			user_id INTEGER NOT NULL,
			role TEXT NOT NULL,
			PRIMARY KEY (competition_id, user_id),
			FOREIGN KEY(competition_id) REFERENCES competitions(id) ON DELETE CASCADE,
			FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
		)`,
		`CREATE TABLE IF NOT EXISTS pilots (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			competition_id INTEGER NOT NULL,
			number TEXT NOT NULL,
			last_name TEXT NOT NULL,
			first_name TEXT NOT NULL,
			country TEXT NOT NULL DEFAULT '',
			balloon_id TEXT NOT NULL DEFAULT '',
			UNIQUE(competition_id, number),
			FOREIGN KEY(competition_id) REFERENCES competitions(id) ON DELETE CASCADE
		)`,
		`CREATE TABLE IF NOT EXISTS penalties (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			competition_id INTEGER NOT NULL,
			flight TEXT NOT NULL DEFAULT '',
			date TEXT NOT NULL DEFAULT '',
			pilot_number TEXT NOT NULL DEFAULT '',
			rule_number TEXT NOT NULL DEFAULT '',
			task TEXT NOT NULL DEFAULT '',
			penalties_text TEXT NOT NULL DEFAULT '',
			description TEXT NOT NULL DEFAULT '',
			created_by INTEGER NOT NULL,
			transferred INTEGER NOT NULL DEFAULT 0,
			created_at TEXT NOT NULL DEFAULT (datetime('now')),
			updated_at TEXT NOT NULL DEFAULT (datetime('now')),
			FOREIGN KEY(competition_id) REFERENCES competitions(id) ON DELETE CASCADE,
			FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE RESTRICT
		)`,
		`CREATE INDEX IF NOT EXISTS idx_penalties_competition ON penalties(competition_id)`,
		`CREATE INDEX IF NOT EXISTS idx_pilots_competition ON pilots(competition_id)`,
	}
	for _, s := range stmts {
		if _, err := db.Exec(s); err != nil {
			return err
		}
	}
	// Idempotent column additions for older databases.
	addColumns := []string{
		`ALTER TABLE users ADD COLUMN must_change_password INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE competitions ADD COLUMN rules_language TEXT NOT NULL DEFAULT 'en'`,
		`ALTER TABLE competitions ADD COLUMN closed INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE competitions ADD COLUMN closed_at TEXT NOT NULL DEFAULT ''`,
	}
	for _, s := range addColumns {
		// Ignore "duplicate column" errors so the migration is idempotent.
		_, _ = db.Exec(s)
	}
	return nil
}