package database

import (
	"database/sql"
	"fmt"
)

// Schema version constants
const (
	// SchemaVersion is the current schema version.
	// Increment when making schema changes and add migration logic.
	// Version history:
	//   v1: Initial schema (findings, scans tables)
	//   v2: Session management (sessions, session_progress tables)
	//   v3: Resume support (targets_file column in sessions)
	SchemaVersion = 3
)

// schemaV1 contains the initial schema matching existing main.go tables.
const schemaV1 = `
CREATE TABLE IF NOT EXISTS findings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    domain TEXT NOT NULL,
    path TEXT NOT NULL,
    status_code INTEGER,
    content_type TEXT,
    body_snippet TEXT,
    patterns TEXT,
    found_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(domain, path)
);
CREATE INDEX IF NOT EXISTS idx_findings_domain ON findings(domain);
CREATE INDEX IF NOT EXISTS idx_findings_path ON findings(path);

CREATE TABLE IF NOT EXISTS scans (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    domain TEXT NOT NULL,
    paths_checked INTEGER DEFAULT 0,
    findings_count INTEGER DEFAULT 0,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME
);
CREATE INDEX IF NOT EXISTS idx_scans_domain ON scans(domain);
`

// schemaV2 adds session management tables and links findings to sessions.
// Sessions track scan progress, enable checkpointing, and allow resume.
const schemaV2 = `
CREATE TABLE IF NOT EXISTS sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    status TEXT NOT NULL DEFAULT 'active',
    total_targets INTEGER NOT NULL DEFAULT 0,
    completed_targets INTEGER NOT NULL DEFAULT 0,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME
);
CREATE INDEX IF NOT EXISTS idx_sessions_status ON sessions(status);
CREATE INDEX IF NOT EXISTS idx_sessions_name ON sessions(name);

ALTER TABLE findings ADD COLUMN session_id INTEGER REFERENCES sessions(id);
CREATE INDEX IF NOT EXISTS idx_findings_session ON findings(session_id);

CREATE TABLE IF NOT EXISTS session_progress (
    session_id INTEGER NOT NULL REFERENCES sessions(id),
    domain TEXT NOT NULL,
    completed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (session_id, domain)
);
`

// EnsureSchema checks the current schema version and creates/migrates
// the database schema as needed. Uses BEGIN IMMEDIATE transaction for
// exclusive write access during schema changes.
//
// Version tracking via PRAGMA user_version enables future migrations:
//   - version 0: no schema (fresh database)
//   - version 1: initial schema with findings and scans tables
func EnsureSchema(db *sql.DB) error {
	// Check current version
	var version int
	err := db.QueryRow("PRAGMA user_version").Scan(&version)
	if err != nil {
		return fmt.Errorf("read user_version: %w", err)
	}

	if version >= SchemaVersion {
		// Already at or past current version
		return nil
	}

	// BEGIN IMMEDIATE for exclusive write access during schema changes
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("begin transaction: %w", err)
	}
	defer tx.Rollback()

	// Apply migrations based on current version
	if version < 1 {
		// Create initial schema (v1)
		_, err = tx.Exec(schemaV1)
		if err != nil {
			return fmt.Errorf("create schema v1: %w", err)
		}
	}

	// Migrate to v2: add sessions and session_progress tables
	if version < 2 {
		_, err = tx.Exec(schemaV2)
		if err != nil {
			return fmt.Errorf("create schema v2: %w", err)
		}
	}

	// Migrate to v3: add targets_file column for resume support
	if version < 3 {
		_, err = tx.Exec(`ALTER TABLE sessions ADD COLUMN targets_file TEXT DEFAULT ''`)
		if err != nil {
			return fmt.Errorf("migrate v2 to v3: %w", err)
		}
	}

	// Update version to current
	_, err = tx.Exec(fmt.Sprintf("PRAGMA user_version = %d", SchemaVersion))
	if err != nil {
		return fmt.Errorf("set user_version: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit schema: %w", err)
	}

	return nil
}

// CheckIntegrity runs SQLite's integrity_check PRAGMA to detect database
// corruption. Returns an error if the database fails integrity checks.
func CheckIntegrity(db *sql.DB) error {
	var result string
	err := db.QueryRow("PRAGMA integrity_check").Scan(&result)
	if err != nil {
		return fmt.Errorf("integrity_check query: %w", err)
	}

	if result != "ok" {
		return fmt.Errorf("integrity_check failed: %s", result)
	}

	return nil
}

// GetSchemaVersion returns the current user_version of the database.
// Useful for diagnostics and debugging.
func GetSchemaVersion(db *sql.DB) (int, error) {
	var version int
	err := db.QueryRow("PRAGMA user_version").Scan(&version)
	if err != nil {
		return 0, fmt.Errorf("read user_version: %w", err)
	}
	return version, nil
}