// Package db handles the SQLite connection and migrations.
package db

import (
	"database/sql"
	"embed"
	"fmt"
	"os"
	"path/filepath"
	"sort"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
	_ "modernc.org/sqlite"
)

// MySQLConfig targets a shared MySQL database for multi-operator logging
// (multiple OpsLog instances on one logbook, à la Log4OM).
type MySQLConfig struct {
	Host     string
	Port     int
	User     string
	Password string
	Database string
}

func (c MySQLConfig) dsn() string {
	port := c.Port
	if port == 0 {
		port = 3306
	}
	// parseTime + UTC so DATETIME columns scan into time.Time; utf8mb4 for full
	// Unicode (names, comments…). timeout bounds the TCP dial so an unreachable
	// or wrong-port server fails fast with a real error instead of hanging
	// startup (which would surface only as "db not initialized"); read/write
	// timeouts cap a stuck statement (generous, so normal migrations/imports
	// never trip them).
	return fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?parseTime=true&loc=UTC&charset=utf8mb4&timeout=10s&readTimeout=120s&writeTimeout=120s",
		c.User, c.Password, c.Host, port, c.Database)
}

// validDBIdent guards a database name we splice into DDL (CREATE DATABASE can't
// use a placeholder). Only plain identifiers allowed.
func validDBIdent(s string) bool {
	if s == "" {
		return false
	}
	for _, r := range s {
		if r != '_' && !(r >= 'a' && r <= 'z') && !(r >= 'A' && r <= 'Z') && !(r >= '0' && r <= '9') {
			return false
		}
	}
	return true
}

// PingMySQL verifies a shared-database connection and creates the logbook
// database if it doesn't exist yet. It connects at server level first (no
// database selected), tries CREATE DATABASE IF NOT EXISTS, then confirms the
// database is actually usable. A restricted user (common on shared hosting)
// may lack the CREATE DATABASE privilege but still have full rights on a
// pre-created database — so a denied CREATE is not fatal as long as the
// database already exists and we can connect to it. Backs the "Test
// connection" button.
func PingMySQL(c MySQLConfig) error {
	if strings.TrimSpace(c.Host) == "" {
		return fmt.Errorf("host is required")
	}
	server := c
	server.Database = "" // connect to the server, not a specific DB
	conn, err := sql.Open("mysql", server.dsn())
	if err != nil {
		return fmt.Errorf("open mysql: %w", err)
	}
	defer conn.Close()
	conn.SetConnMaxLifetime(15 * time.Second)
	if err := conn.Ping(); err != nil {
		return fmt.Errorf("connect to %s:%d: %w", c.Host, c.Port, err)
	}
	name := strings.TrimSpace(c.Database)
	if name == "" {
		return nil
	}
	if !validDBIdent(name) {
		return fmt.Errorf("invalid database name %q (letters, digits, underscore only)", name)
	}
	createErr := error(nil)
	if _, err := conn.Exec("CREATE DATABASE IF NOT EXISTS `" + name + "` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"); err != nil {
		if !isAccessDenied(err) {
			return fmt.Errorf("create database %q: %w", name, err)
		}
		createErr = err // remember it in case the DB also turns out to be unreachable
	}
	// Confirm the database is usable (it may have been pre-created by an admin
	// even though this user can't CREATE one).
	dbConn, err := sql.Open("mysql", c.dsn())
	if err != nil {
		return fmt.Errorf("open mysql db: %w", err)
	}
	defer dbConn.Close()
	dbConn.SetConnMaxLifetime(15 * time.Second)
	if err := dbConn.Ping(); err != nil {
		if createErr != nil {
			return fmt.Errorf("database %q does not exist and user %q cannot create it — ask your MySQL admin to create the database and grant access (%v)", name, c.User, createErr)
		}
		return fmt.Errorf("connect to database %q: %w", name, err)
	}
	return nil
}

//go:embed migrations/*.sql
var migrationsFS embed.FS

// schemaMigrationsDDL tracks which migrations have run. Authored in SQLite
// dialect; run through the dialect translator for MySQL.
const schemaMigrationsDDL = `CREATE TABLE IF NOT EXISTS schema_migrations (
        name TEXT PRIMARY KEY,
        applied_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
    )`

// Dialect names the backend opened at startup. Repos consult it for the few
// places where SQLite and MySQL SQL genuinely differ (upserts, JSON extraction).
// Timestamps are generated in Go (NowISO) for both, so most queries are shared.
var Dialect = "sqlite"

// IsMySQL reports whether the active LOGBOOK backend is the shared MySQL
// server. Config tables always live in local SQLite, so this only governs
// qso-table SQL (see columnExpr).
func IsMySQL() bool { return Dialect == "mysql" }

// SetDialect pins the logbook dialect ("mysql" | "sqlite"). Called once at
// startup after the logbook backend is chosen, so it doesn't depend on the
// order in which the local and logbook connections were opened.
func SetDialect(d string) {
	if d == "mysql" {
		Dialect = "mysql"
	} else {
		Dialect = "sqlite"
	}
}

// NowISO returns the current UTC time as the ISO-8601 string OpsLog stores in
// *_at / date columns — millisecond precision, matching the old SQLite
// strftime('%Y-%m-%dT%H:%M:%fZ','now') default. Bind this as a parameter so the
// same INSERT/UPDATE works on both backends.
func NowISO() string { return time.Now().UTC().Format("2006-01-02T15:04:05.000Z") }

// Open opens (and creates if needed) the SQLite database at the given path,
// enables performance PRAGMAs, and applies embedded migrations.
//
// This is the SETTINGS database, which gets the full schema — see roles.go for
// why. Use OpenLogbook for a database that holds contacts only.
func Open(path string) (*sql.DB, error) { return open(path, RoleAll) }

// OpenLogbook opens a SQLite logbook: the qso table and nothing from the
// settings side, and any unused settings tables an earlier version left in it
// are dropped if they are empty.
func OpenLogbook(path string) (*sql.DB, error) { return open(path, RoleLogbook) }

func open(path string, role Role) (*sql.DB, error) {
	// Escape only the two characters a path could contain that the DSN would
	// otherwise read as its query/fragment delimiters. Windows separators
	// (\\ and the drive ':') are left intact — url.PathEscape would mangle them.
	safePath := strings.NewReplacer("?", "%3F", "#", "%23").Replace(path)
	dsn := fmt.Sprintf("file:%s?_pragma=journal_mode(WAL)&_pragma=foreign_keys(on)&_pragma=synchronous(normal)&_pragma=busy_timeout(5000)", safePath)
	conn, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("open sqlite: %w", err)
	}
	if err := conn.Ping(); err != nil {
		_ = conn.Close()
		return nil, fmt.Errorf("ping sqlite: %w", err)
	}
	Dialect = "sqlite"
	label := filepath.Base(path)
	if err := migrate(conn, nil, path, label, role); err != nil {
		_ = conn.Close()
		return nil, err
	}
	pruneForeignTables(conn, role, label)
	return conn, nil
}

// LogSink receives this package's diagnostic lines. The app points it at
// applog.Printf at startup (same pattern as cat / audio / extsvc); left nil in
// tests and in the CLI tools under cmd/, where it is simply discarded.
var LogSink func(format string, args ...any)

// logMigration records a migration that has just been applied, and how long it
// took — the only trace an operator has that a data-rewriting migration ran.
func logMigration(label, name string, start time.Time) {
	logf("db[%s]: migration %s applied in %s", label, name, time.Since(start).Round(time.Millisecond))
}

func logf(format string, args ...any) {
	if LogSink != nil {
		LogSink(format, args...)
	}
}

// dataRewriteMarker flags a migration that rewrites existing user rows rather
// than only altering the schema. Put it on its own line in the .sql file, and a
// safety copy of the logbook is taken before it runs.
const dataRewriteMarker = "-- opslog:rewrites-data"

// backupBeforeRewrite takes a one-off copy of the logbook before a migration
// that rewrites user rows.
//
// It exists because the auto-updater gives the operator no say: the new build
// relaunches and migrates before the changelog explaining it is ever shown, so
// "back up first" is advice nobody can act on. The app takes the copy instead.
//
// Skipped when there is nothing to protect — a shared MySQL (no file to copy;
// that server is the admin's to back up), the settings database, and a
// freshly-created empty logbook all have no QSOs at stake.
func backupBeforeRewrite(conn *sql.DB, dbPath, migration string) {
	if dbPath == "" {
		return
	}
	var n int
	if err := conn.QueryRow(`SELECT COUNT(*) FROM qso`).Scan(&n); err != nil || n == 0 {
		return
	}
	dest := dbPath + ".pre-" + strings.TrimSuffix(migration, ".sql") + ".bak"
	if _, err := os.Stat(dest); err == nil {
		return // a copy from an earlier attempt is already there — never overwrite it
	}
	// VACUUM INTO rather than copying the file: it writes a consistent,
	// self-contained snapshot even with WAL pages still outstanding, which a
	// plain file copy would silently miss. It cannot run inside a transaction,
	// so it happens here, before the migration opens one. The destination is
	// spliced (VACUUM INTO takes no bound parameter), with quotes doubled.
	start := time.Now()
	if _, err := conn.Exec(`VACUUM INTO '` + strings.ReplaceAll(dest, "'", "''") + `'`); err != nil {
		// Not fatal: the migration itself is a single atomic transaction, so
		// failing to take a belt-and-braces copy is no reason to block the update.
		logf("db: could not back up before %s: %v — continuing (the migration is atomic)", migration, err)
		return
	}
	logf("db: backed up %d QSO(s) to %s in %s before %s", n, dest, time.Since(start).Round(time.Millisecond), migration)
}

// isIgnorableSQLiteDDLError reports a benign DDL failure: the change is
// already there, or the statement shapes a table this database does not hold.
// Scoped to shaping statements only — a CREATE TABLE or data statement that
// fails must still fail the migration.
func isIgnorableSQLiteDDLError(err error, stmt string) bool {
	msg := strings.ToLower(err.Error())
	if strings.Contains(msg, "duplicate column name") || strings.Contains(msg, "already exists") {
		return true
	}
	if strings.Contains(msg, "no such table") {
		head := strings.ToLower(strings.TrimSpace(stmt))
		return strings.HasPrefix(head, "alter table") ||
			strings.HasPrefix(head, "create index") ||
			strings.HasPrefix(head, "create unique index") ||
			strings.HasPrefix(head, "drop ")
	}
	return false
}

// migrate applies all embedded *.sql migrations in alphabetical order,
// skipping those already applied. Intentionally minimal in-house system
// (no external dependency). translate, when non-nil, rewrites each statement
// for a non-SQLite backend (see mysqlDDL); nil means run the SQLite DDL as-is.
// label names the database being migrated, for the log. Without it three
// interleaved migration runs in one log were indistinguishable — an operator
// reported "migrations are very slow" and the lines gave no way to tell one
// database migrated three times from three databases migrated once.
func migrate(conn *sql.DB, translate func(string) string, dbPath, label string, role Role) error {
	// A non-nil translator means this is the MySQL connection (use the
	// per-statement, FK-aware path); nil means a SQLite connection. This is
	// determined by the caller's argument, NOT the global Dialect, so the
	// in-memory SQLite used to build the MySQL baseline still migrates as SQLite.
	mysqlPath := translate != nil
	if translate == nil {
		translate = func(s string) string { return s }
	}
	if _, err := conn.Exec(translate(schemaMigrationsDDL)); err != nil {
		return fmt.Errorf("create schema_migrations: %w", err)
	}

	entries, err := migrationsFS.ReadDir("migrations")
	if err != nil {
		return fmt.Errorf("read migrations dir: %w", err)
	}
	names := make([]string, 0, len(entries))
	for _, e := range entries {
		if e.IsDir() || !strings.HasSuffix(e.Name(), ".sql") {
			continue
		}
		names = append(names, e.Name())
	}
	sort.Strings(names)

	// Fetch every applied migration name in ONE query rather than one round-trip
	// per migration. On a high-latency remote MySQL those 20+ SELECTs dominated the
	// connect time (each RTT × migration count) — the whole logbook connect could
	// take tens of seconds. One SELECT + an in-memory set is effectively instant.
	applied := map[string]bool{}
	if rows, err := conn.Query(`SELECT name FROM schema_migrations`); err == nil {
		for rows.Next() {
			var n string
			if rows.Scan(&n) == nil {
				applied[n] = true
			}
		}
		rows.Close()
	} else {
		return fmt.Errorf("read applied migrations: %w", err)
	}

	pending := 0
	for _, name := range names {
		if !applied[name] {
			pending++
		}
	}
	if pending > 0 {
		logf("db[%s]: %d migration(s) to apply", label, pending)
	}

	for _, name := range names {
		if applied[name] {
			continue // already applied
		}
		// Timed, and logged only once it has actually succeeded (below). Most
		// migrations are instant DDL, but some rewrite user rows — 0024 upper-cases
		// every callsign — and on a large logbook that is exactly what an operator
		// wants confirmed afterwards: that it ran, once, and what it cost.
		start := time.Now()
		content, err := migrationsFS.ReadFile("migrations/" + name)
		if err != nil {
			return fmt.Errorf("read migration %s: %w", name, err)
		}
		sqlText := translate(string(content))

		// A migration that rewrites user rows gets a safety copy taken first.
		if strings.Contains(string(content), dataRewriteMarker) {
			backupBeforeRewrite(conn, dbPath, name)
		}

		// MySQL implicitly commits each DDL statement, so a wrapping transaction
		// gives no atomicity — a mid-file failure would leave columns/tables
		// behind, unrecorded, and every restart would re-run and choke on
		// "duplicate column". Instead apply statement-by-statement and tolerate
		// "already exists" errors, making migrations idempotent (and self-healing
		// for a previously half-applied database).
		if mysqlPath {
			if err := applyMySQLMigration(conn, sqlText); err != nil {
				return fmt.Errorf("apply migration %s: %w", name, err)
			}
			if _, err := conn.Exec(`INSERT INTO schema_migrations(name) VALUES(?)`, name); err != nil {
				return fmt.Errorf("record migration %s: %w", name, err)
			}
			logMigration(label, name, start)
			continue
		}

		// SQLite supports DDL inside a transaction, so keep it atomic there.
		tx, err := conn.Begin()
		if err != nil {
			return fmt.Errorf("begin tx for %s: %w", name, err)
		}
		// Statement by statement rather than one Exec of the whole file: the role
		// filter works per statement, and a settings-table statement must not
		// reach a logbook database (see roles.go). Still one transaction, so the
		// file remains atomic.
		for _, stmt := range splitStatements(sqlText) {
			if !keepForRole(stmt, role) {
				continue
			}
			if _, err := tx.Exec(stmt); err != nil {
				// Same self-healing as the MySQL path, plus one case it never
				// meets: a table-shaping statement aimed at a table this
				// database legitimately does not have. A split settings
				// database dropped its qso table when the QSOs moved to the
				// logbook, but its role is still RoleAll — so a later
				// "ALTER TABLE qso ADD COLUMN" must be a no-op there, not a
				// failure that silently kills the whole startup (v0.27.4+).
				if isIgnorableSQLiteDDLError(err, stmt) {
					continue
				}
				_ = tx.Rollback()
				return fmt.Errorf("apply migration %s: %w", name, err)
			}
		}
		if _, err := tx.Exec(`INSERT INTO schema_migrations(name) VALUES(?)`, name); err != nil {
			_ = tx.Rollback()
			return fmt.Errorf("record migration %s: %w", name, err)
		}
		if err := tx.Commit(); err != nil {
			return fmt.Errorf("commit migration %s: %w", name, err)
		}
		logMigration(label, name, start)
	}
	return nil
}
