//! Database schema migrations and version management.
//!
//! This module handles:
//! - Schema version tracking
//! - Migration execution from older versions to current
//! - Detection of incompatible legacy schemas

use fyi_core::{StorageError, StringError};
use rusqlite::Connection;

use crate::sql::{DROP_OLD_INDEXES, MIGRATE_API_COLUMNS, MIGRATE_API_REQUESTS};

/// Check if the database has a v1 schema (uses 'ts' instead of 'timestamp').
/// V1 schemas are incompatible and require manual deletion.
pub(crate) fn is_v1_schema(conn: &Connection) -> Result<bool, StorageError> {
    // Check if the events table exists and has a 'ts' column but NOT a 'timestamp' column
    // This avoids false positives from unrelated databases
    let has_ts: Result<i64, _> = conn.query_row(
        "SELECT COUNT(*) FROM pragma_table_info('events') WHERE name = 'ts'",
        [],
        |row| row.get(0),
    );

    let has_timestamp: Result<i64, _> = conn.query_row(
        "SELECT COUNT(*) FROM pragma_table_info('events') WHERE name = 'timestamp'",
        [],
        |row| row.get(0),
    );

    match (has_ts, has_timestamp) {
        // Has 'ts' but no 'timestamp' - this is a v1 schema
        (Ok(ts_count), Ok(timestamp_count)) if ts_count > 0 && timestamp_count == 0 => Ok(true),
        // Any other case (including errors) - not v1
        _ => Ok(false),
    }
}

/// Check if a table exists in the database.
pub(crate) fn table_exists(conn: &Connection, table_name: &str) -> Result<bool, StorageError> {
    let count: i64 = conn
        .query_row(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?1",
            [table_name],
            |row| row.get(0),
        )
        .map_err(|e| StorageError::Query(Box::new(e)))?;
    Ok(count > 0)
}

/// Get the current schema version from the database.
/// Returns None if the schema_version table doesn't exist or is empty.
pub(crate) fn get_schema_version(conn: &Connection) -> Result<Option<u32>, StorageError> {
    // First check if the schema_version table exists
    if !table_exists(conn, "schema_version")? {
        return Ok(None);
    }

    // Get the highest version number
    let version: Result<Option<u32>, _> =
        conn.query_row("SELECT MAX(version) FROM schema_version", [], |row| {
            row.get(0)
        });

    match version {
        Ok(v) => Ok(v),
        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
        Err(e) => Err(StorageError::Query(Box::new(e))),
    }
}

/// Initialize the schema_version table.
pub(crate) fn init_version_table(conn: &Connection) -> Result<(), StorageError> {
    conn.execute_batch(
        r"
        CREATE TABLE IF NOT EXISTS schema_version (
            version INTEGER PRIMARY KEY,
            applied_at INTEGER NOT NULL
        );
        ",
    )
    .map_err(|e| StorageError::Query(Box::new(e)))?;
    Ok(())
}

/// Record a schema version as applied.
pub(crate) fn record_version(conn: &Connection, version: u32) -> Result<(), StorageError> {
    let now = std::time::SystemTime::now()
        .duration_since(std::time::UNIX_EPOCH)
        .map(|d| d.as_millis() as i64)
        .unwrap_or(0);

    conn.execute(
        "INSERT INTO schema_version (version, applied_at) VALUES (?1, ?2)",
        rusqlite::params![version, now],
    )
    .map_err(|e| StorageError::Query(Box::new(e)))?;

    Ok(())
}

/// Drop old indexes with inconsistent naming.
/// This is safe to run even if indexes don't exist (uses IF EXISTS).
pub(crate) fn drop_old_indexes(conn: &Connection) -> Result<(), StorageError> {
    conn.execute_batch(DROP_OLD_INDEXES)
        .map_err(|e| StorageError::Query(Box::new(e)))?;
    Ok(())
}

/// Run migrations from the current version to the target version.
pub(crate) fn run_migrations(conn: &Connection, from_version: u32) -> Result<(), StorageError> {
    let mut current_version = from_version;

    // v2 -> v3: Standardize index naming and ensure all v3 columns exist
    if current_version < 3 {
        migrate_v2_to_v3(conn)?;
        record_version(conn, 3)?;
        current_version = 3;
    }

    // v3 -> v4: Merge api_requests into events table
    if current_version < 4 {
        migrate_v3_to_v4(conn)?;
        record_version(conn, 4)?;
    }

    Ok(())
}

/// Migrate from v2 to v3: standardize index naming and add missing columns.
fn migrate_v2_to_v3(conn: &Connection) -> Result<(), StorageError> {
    // Drop old indexes with inconsistent naming and create new ones
    drop_old_indexes(conn)?;

    // Add columns that may be missing from early v2 databases
    // (SQLite allows ADD COLUMN even if column already exists in some versions,
    // but we ignore errors to handle both cases)
    let v3_columns = [
        "ALTER TABLE events ADD COLUMN framework TEXT",
        "ALTER TABLE events ADD COLUMN tool_use_id TEXT",
        "ALTER TABLE events ADD COLUMN spawned_agent_id TEXT",
        "ALTER TABLE events ADD COLUMN tool_name TEXT",
        "ALTER TABLE events ADD COLUMN subagent_type TEXT",
        "ALTER TABLE events ADD COLUMN permission_mode TEXT",
        "ALTER TABLE events ADD COLUMN transcript_path TEXT",
        "ALTER TABLE events ADD COLUMN pid INTEGER",
        "ALTER TABLE events ADD COLUMN cwd TEXT",
        "ALTER TABLE events ADD COLUMN payload TEXT",
        "ALTER TABLE events ADD COLUMN metadata TEXT",
    ];
    for col in v3_columns {
        let _ = conn.execute(col, []);
    }

    // Recreate indexes with standardized naming
    conn.execute_batch(
        r"
        CREATE INDEX IF NOT EXISTS idx_events_session_id ON events(session_id);
        CREATE INDEX IF NOT EXISTS idx_events_timestamp ON events(timestamp);
        CREATE INDEX IF NOT EXISTS idx_events_event_type ON events(event_type);
        CREATE INDEX IF NOT EXISTS idx_events_machine_id ON events(machine_id);
        ",
    )
    .map_err(|e| StorageError::Query(Box::new(e)))?;

    Ok(())
}

/// Migrate from v3 to v4: merge api_requests table into events table.
fn migrate_v3_to_v4(conn: &Connection) -> Result<(), StorageError> {
    // Add new columns to events table for API request data
    // Each ALTER TABLE is executed separately as SQLite doesn't support multiple ADD COLUMN in one statement
    for line in MIGRATE_API_COLUMNS.lines() {
        let line = line.trim();
        if !line.is_empty() && line.starts_with("ALTER") {
            // Ignore errors if column already exists
            let _ = conn.execute(line, []);
        }
    }

    // Check if api_requests table exists before migrating
    if table_exists(conn, "api_requests")? {
        // Migrate data from api_requests to events
        conn.execute_batch(MIGRATE_API_REQUESTS)
            .map_err(|e| StorageError::Query(Box::new(e)))?;

        // Drop the old api_requests table and its indexes
        conn.execute_batch(
            r"
            DROP INDEX IF EXISTS idx_api_requests_session_id;
            DROP INDEX IF EXISTS idx_api_requests_timestamp;
            DROP INDEX IF EXISTS idx_api_requests_machine_id;
            DROP TABLE api_requests;
            ",
        )
        .map_err(|e| StorageError::Query(Box::new(e)))?;
    }

    Ok(())
}

/// Validates and rejects incompatible v1 schemas.
pub(crate) fn reject_v1_schema(conn: &Connection) -> Result<(), StorageError> {
    if is_v1_schema(conn)? {
        return Err(StorageError::Connection(Box::new(StringError(
            "Database uses schema v1 which is incompatible with this version. \
             Please delete the database file and re-initialize, or use a different database path. \
             Your existing data will be lost."
                .to_string(),
        ))));
    }
    Ok(())
}

/// Validates and rejects schemas newer than the current version.
pub(crate) fn reject_newer_schema(version: u32, current: u32) -> Result<(), StorageError> {
    if version > current {
        return Err(StorageError::Connection(Box::new(StringError(format!(
            "Database schema version {} is newer than supported version {}. \
             Please upgrade fyi to a newer version.",
            version, current
        )))));
    }
    Ok(())
}

#[cfg(test)]
#[expect(clippy::unwrap_used)]
mod tests {
    use super::*;
    use std::path::PathBuf;

    fn temp_db_path() -> PathBuf {
        std::env::temp_dir().join(format!(
            "fyi-sqlite-migrations-test-{}-{:?}-{}.db",
            std::process::id(),
            std::thread::current().id(),
            std::time::SystemTime::now()
                .duration_since(std::time::UNIX_EPOCH)
                .unwrap()
                .as_nanos()
        ))
    }

    #[test]
    fn test_v1_schema_detection() {
        let path = temp_db_path();

        // Create a v1-style database manually
        let conn = Connection::open(&path).unwrap();
        conn.execute_batch(
            "CREATE TABLE events (
                id INTEGER PRIMARY KEY,
                ts INTEGER NOT NULL,
                event_type TEXT NOT NULL,
                session_id TEXT NOT NULL
            )",
        )
        .unwrap();

        // Verify v1 schema is detected
        assert!(is_v1_schema(&conn).unwrap());

        // Clean up
        drop(conn);
        let _ = std::fs::remove_file(&path);
    }

    #[test]
    fn test_v2_schema_not_detected_as_v1() {
        let path = temp_db_path();

        let conn = Connection::open(&path).unwrap();
        conn.execute_batch(
            "CREATE TABLE events (
                id INTEGER PRIMARY KEY,
                timestamp INTEGER NOT NULL,
                event_type TEXT NOT NULL,
                session_id TEXT NOT NULL
            )",
        )
        .unwrap();

        // V2 schema should not be detected as v1
        assert!(!is_v1_schema(&conn).unwrap());

        // Clean up
        drop(conn);
        let _ = std::fs::remove_file(&path);
    }

    #[test]
    fn test_table_exists() {
        let path = temp_db_path();

        let conn = Connection::open(&path).unwrap();

        // No tables yet
        assert!(!table_exists(&conn, "events").unwrap());

        // Create table
        conn.execute_batch("CREATE TABLE events (id INTEGER PRIMARY KEY)")
            .unwrap();

        // Table should exist now
        assert!(table_exists(&conn, "events").unwrap());

        // Non-existent table should return false
        assert!(!table_exists(&conn, "nonexistent").unwrap());

        // Clean up
        drop(conn);
        let _ = std::fs::remove_file(&path);
    }

    #[test]
    fn test_version_tracking() {
        let path = temp_db_path();

        let conn = Connection::open(&path).unwrap();

        // No version table yet
        assert_eq!(get_schema_version(&conn).unwrap(), None);

        // Initialize version table
        init_version_table(&conn).unwrap();

        // Still no version recorded
        assert_eq!(get_schema_version(&conn).unwrap(), None);

        // Record version
        record_version(&conn, 3).unwrap();
        assert_eq!(get_schema_version(&conn).unwrap(), Some(3));

        // Record higher version
        record_version(&conn, 4).unwrap();
        assert_eq!(get_schema_version(&conn).unwrap(), Some(4));

        // Clean up
        drop(conn);
        let _ = std::fs::remove_file(&path);
    }

    #[test]
    fn test_schema_version_records_timestamp() {
        let path = temp_db_path();

        let before = std::time::SystemTime::now()
            .duration_since(std::time::UNIX_EPOCH)
            .unwrap()
            .as_millis() as i64;

        let conn = Connection::open(&path).unwrap();
        init_version_table(&conn).unwrap();
        record_version(&conn, 4).unwrap();

        let after = std::time::SystemTime::now()
            .duration_since(std::time::UNIX_EPOCH)
            .unwrap()
            .as_millis() as i64;

        // Check that applied_at is within the expected range
        let applied_at: i64 = conn
            .query_row(
                "SELECT applied_at FROM schema_version WHERE version = ?1",
                [4],
                |row| row.get(0),
            )
            .unwrap();

        assert!(
            applied_at >= before && applied_at <= after,
            "applied_at {} should be between {} and {}",
            applied_at,
            before,
            after
        );

        // Clean up
        drop(conn);
        let _ = std::fs::remove_file(&path);
    }

    #[test]
    fn test_reject_newer_schema() {
        let result = reject_newer_schema(99, 4);
        assert!(result.is_err());
        assert!(
            result
                .unwrap_err()
                .to_string()
                .contains("newer than supported")
        );
    }

    #[test]
    fn test_accept_current_or_older_schema() {
        assert!(reject_newer_schema(4, 4).is_ok());
        assert!(reject_newer_schema(3, 4).is_ok());
        assert!(reject_newer_schema(1, 4).is_ok());
    }
}
