package db

import (
	"context"
	"database/sql"
	"fmt"
	_ "modernc.org/sqlite"
	"os"
	"path/filepath"
	"time"
)

func Open(path string) (*sql.DB, error) {
	if err := os.MkdirAll(filepath.Dir(path), 0750); err != nil {
		return nil, err
	}
	dsn := "file:" + path + "?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=foreign_keys(ON)"
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, err
	}
	// WAL allows concurrent readers while SQLite still serializes writes. A small
	// pool keeps UI reads responsive while monitor checks are being persisted.
	db.SetMaxOpenConns(4)
	db.SetMaxIdleConns(4)
	db.SetConnMaxLifetime(30 * time.Minute)
	ctx, c := context.WithTimeout(context.Background(), 10*time.Second)
	defer c()
	if err := db.PingContext(ctx); err != nil {
		_ = db.Close()
		return nil, err
	}
	if err := migrate(ctx, db); err != nil {
		_ = db.Close()
		return nil, err
	}
	return db, nil
}
func migrate(ctx context.Context, db *sql.DB) error {
	stmts := []string{
		`CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY AUTOINCREMENT,oidc_sub TEXT NOT NULL UNIQUE,email TEXT NOT NULL DEFAULT '',name TEXT NOT NULL DEFAULT '',role TEXT NOT NULL DEFAULT 'viewer',last_login_at INTEGER NOT NULL,created_at INTEGER NOT NULL)`,
		`CREATE TABLE IF NOT EXISTS sessions(token_hash BLOB PRIMARY KEY,user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,expires_at INTEGER NOT NULL,created_at INTEGER NOT NULL)`,
		`CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at)`,
		`CREATE TABLE IF NOT EXISTS nodes(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL UNIQUE,base_url TEXT NOT NULL,token_enc BLOB NOT NULL,enabled INTEGER NOT NULL DEFAULT 1,created_at INTEGER NOT NULL,updated_at INTEGER NOT NULL)`,
		`CREATE TABLE IF NOT EXISTS monitor_services(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL UNIQUE,description TEXT NOT NULL DEFAULT '',created_at INTEGER NOT NULL,updated_at INTEGER NOT NULL)`,
		`CREATE TABLE IF NOT EXISTS monitors(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,type TEXT NOT NULL,target TEXT NOT NULL,node_id INTEGER NULL REFERENCES nodes(id) ON DELETE SET NULL,service_id INTEGER NULL REFERENCES monitor_services(id) ON DELETE SET NULL,interval_seconds INTEGER NOT NULL DEFAULT 60,timeout_ms INTEGER NOT NULL DEFAULT 5000,expected_min INTEGER NOT NULL DEFAULT 200,expected_max INTEGER NOT NULL DEFAULT 399,method TEXT NOT NULL DEFAULT 'GET',headers_json TEXT NOT NULL DEFAULT '{}',body TEXT NOT NULL DEFAULT '',keyword TEXT NOT NULL DEFAULT '',invert_keyword INTEGER NOT NULL DEFAULT 0,ignore_tls INTEGER NOT NULL DEFAULT 0,require_healthy INTEGER NOT NULL DEFAULT 0,enabled INTEGER NOT NULL DEFAULT 1,status TEXT NOT NULL DEFAULT 'pending',maintenance_until INTEGER NULL,maintenance_note TEXT NOT NULL DEFAULT '',last_checked_at INTEGER NULL,created_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,created_at INTEGER NOT NULL,updated_at INTEGER NOT NULL)`,
		`CREATE TABLE IF NOT EXISTS monitor_checks(id INTEGER PRIMARY KEY AUTOINCREMENT,monitor_id INTEGER NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,ok INTEGER NOT NULL,status_code INTEGER NOT NULL DEFAULT 0,latency_ms INTEGER NOT NULL DEFAULT 0,message TEXT NOT NULL DEFAULT '',checked_at INTEGER NOT NULL)`,
		`CREATE INDEX IF NOT EXISTS idx_monitor_checks_mon_time ON monitor_checks(monitor_id,checked_at DESC)`,
		`CREATE TABLE IF NOT EXISTS audit_log(id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,actor TEXT NOT NULL DEFAULT '',action TEXT NOT NULL,resource TEXT NOT NULL DEFAULT '',detail_json TEXT NOT NULL DEFAULT '{}',ip TEXT NOT NULL DEFAULT '',user_agent TEXT NOT NULL DEFAULT '',status INTEGER NOT NULL DEFAULT 0,created_at INTEGER NOT NULL)`,
		`CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at DESC)`,
		`CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_log(action,created_at DESC)`,
		`CREATE TABLE IF NOT EXISTS notification_channels(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL UNIQUE,type TEXT NOT NULL,config_json TEXT NOT NULL DEFAULT '{}',enabled INTEGER NOT NULL DEFAULT 1,created_at INTEGER NOT NULL,updated_at INTEGER NOT NULL)`,
		`CREATE INDEX IF NOT EXISTS idx_notifications_enabled ON notification_channels(enabled,type)`,
		`CREATE TABLE IF NOT EXISTS status_pages(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,slug TEXT NOT NULL UNIQUE,description TEXT NOT NULL DEFAULT '',enabled INTEGER NOT NULL DEFAULT 1,created_at INTEGER NOT NULL,updated_at INTEGER NOT NULL)`,
		`CREATE TABLE IF NOT EXISTS status_page_services(page_id INTEGER NOT NULL REFERENCES status_pages(id) ON DELETE CASCADE,service_id INTEGER NOT NULL REFERENCES monitor_services(id) ON DELETE CASCADE,sort_order INTEGER NOT NULL DEFAULT 0,PRIMARY KEY(page_id,service_id))`,
		`CREATE TABLE IF NOT EXISTS git_sources(id INTEGER PRIMARY KEY AUTOINCREMENT,stack_name TEXT NOT NULL UNIQUE,repo_url TEXT NOT NULL,branch TEXT NOT NULL DEFAULT 'main',workdir TEXT NOT NULL DEFAULT '.',compose_file TEXT NOT NULL DEFAULT 'compose.yaml',auto_deploy INTEGER NOT NULL DEFAULT 0,webhook_secret_enc BLOB NOT NULL,last_commit TEXT NOT NULL DEFAULT '',last_sync_at INTEGER NULL,last_error TEXT NOT NULL DEFAULT '',created_at INTEGER NOT NULL,updated_at INTEGER NOT NULL)`}
	for i, s := range stmts {
		if _, err := db.ExecContext(ctx, s); err != nil {
			return fmt.Errorf("migration %d: %w", i+1, err)
		}
	}
	// Additive migrations keep existing installations compatible.
	cols := map[string]string{
		"method":            "TEXT NOT NULL DEFAULT 'GET'",
		"headers_json":      "TEXT NOT NULL DEFAULT '{}'",
		"body":              "TEXT NOT NULL DEFAULT ''",
		"keyword":           "TEXT NOT NULL DEFAULT ''",
		"invert_keyword":    "INTEGER NOT NULL DEFAULT 0",
		"ignore_tls":        "INTEGER NOT NULL DEFAULT 0",
		"maintenance_until": "INTEGER NULL",
		"maintenance_note":  "TEXT NOT NULL DEFAULT ''",
		"service_id":        "INTEGER NULL REFERENCES monitor_services(id) ON DELETE SET NULL",
		"require_healthy":   "INTEGER NOT NULL DEFAULT 0",
	}
	for name, def := range cols {
		if err := ensureColumn(ctx, db, "monitors", name, def); err != nil {
			return err
		}
	}
	if err := ensureColumn(ctx, db, "git_sources", "node_id", "INTEGER NULL REFERENCES nodes(id) ON DELETE SET NULL"); err != nil {
		return err
	}
	indexes := []string{
		`CREATE INDEX IF NOT EXISTS idx_monitors_service ON monitors(service_id,name)`,
		`CREATE INDEX IF NOT EXISTS idx_monitors_schedule ON monitors(enabled,last_checked_at,interval_seconds)`,
		`CREATE INDEX IF NOT EXISTS idx_monitors_status ON monitors(status)`,
		`CREATE INDEX IF NOT EXISTS idx_status_page_services_page ON status_page_services(page_id,sort_order)`,
		`CREATE INDEX IF NOT EXISTS idx_git_sources_node ON git_sources(node_id)`,
	}
	for _, stmt := range indexes {
		if _, err := db.ExecContext(ctx, stmt); err != nil {
			return fmt.Errorf("create index: %w", err)
		}
	}
	_, _ = db.ExecContext(ctx, `PRAGMA optimize`)
	return nil
}

func ensureColumn(ctx context.Context, db *sql.DB, table, column, definition string) error {
	rows, err := db.QueryContext(ctx, "PRAGMA table_info("+table+")")
	if err != nil {
		return err
	}
	found := false
	for rows.Next() {
		var cid int
		var name, typ string
		var notnull, pk int
		var dflt any
		if err := rows.Scan(&cid, &name, &typ, &notnull, &dflt, &pk); err != nil {
			_ = rows.Close()
			return err
		}
		if name == column {
			found = true
		}
	}
	if err := rows.Err(); err != nil {
		_ = rows.Close()
		return err
	}
	if err := rows.Close(); err != nil {
		return err
	}
	if found {
		return nil
	}
	if _, err := db.ExecContext(ctx, "ALTER TABLE "+table+" ADD COLUMN "+column+" "+definition); err != nil {
		return fmt.Errorf("add %s.%s: %w", table, column, err)
	}
	return nil
}