//! Database schema definitions and initialization.
//!
//! This module provides the entry point for database schema setup.
//! Schema SQL definitions are in [`crate::sql`], and migration logic
//! is in [`crate::migrations`].

use fyi_core::StorageError;
use rusqlite::Connection;

use crate::migrations::{
    drop_old_indexes, get_schema_version, init_version_table, record_version, reject_newer_schema,
    reject_v1_schema, run_migrations, table_exists,
};
use crate::sql::{
    EVENTS_TABLE, SPAWNED_AGENT_INDEX, SUBAGENT_TYPE_INDEX, TOOL_NAME_INDEX, TOOL_USE_INDEX,
};

/// Current schema version.
///
/// Version history:
/// - v1: Original schema with 'ts' column (incompatible, must delete and recreate)
/// - v2: Schema with 'timestamp' column, proper types, and indexes
/// - v3: Standardized index naming (idx_<table>_<column> pattern)
/// - v4: Merged events and api_requests tables into unified events table
pub(crate) const CURRENT_SCHEMA_VERSION: u32 = 4;

/// Initialize the database schema.
///
/// This function handles:
/// - Fresh database creation
/// - Upgrading pre-versioned databases
/// - Running migrations from older versions
/// - Rejecting incompatible or too-new schemas
pub(crate) fn init_schema(conn: &Connection) -> Result<(), StorageError> {
    // Reject incompatible v1 schema
    reject_v1_schema(conn)?;

    // Check current schema version
    let current_version = get_schema_version(conn)?;

    match current_version {
        None => {
            // Fresh database or pre-versioning database
            // Check if this is a pre-versioning v2 database (has events table but no schema_version)
            let has_events_table = table_exists(conn, "events")?;

            if has_events_table {
                // Existing v2 database without version tracking - add version table
                // Also drop old indexes and create new ones with standardized naming
                drop_old_indexes(conn)?;
                create_base_schema(conn)?;
                init_version_table(conn)?;
                record_version(conn, CURRENT_SCHEMA_VERSION)?;
            } else {
                // Fresh database - create everything from scratch
                init_version_table(conn)?;
                create_base_schema(conn)?;
                record_version(conn, CURRENT_SCHEMA_VERSION)?;
            }
        }
        Some(version) if version < CURRENT_SCHEMA_VERSION => {
            // Need to run migrations
            run_migrations(conn, version)?;
        }
        Some(version) if version > CURRENT_SCHEMA_VERSION => {
            reject_newer_schema(version, CURRENT_SCHEMA_VERSION)?;
        }
        Some(_) => {
            // Schema is up to date, nothing to do
        }
    }

    Ok(())
}

/// Create the base schema (for fresh databases).
fn create_base_schema(conn: &Connection) -> Result<(), StorageError> {
    conn.execute_batch(EVENTS_TABLE)
        .map_err(|e| StorageError::Query(Box::new(e)))?;

    // Create partial indexes for nullable fields
    // These are performance optimizations, not critical for functionality.
    // Failures are logged but don't prevent database initialization.
    if let Err(e) = conn.execute(TOOL_USE_INDEX, []) {
        eprintln!("fyi: warning: failed to create tool_use index: {}", e);
    }
    if let Err(e) = conn.execute(SPAWNED_AGENT_INDEX, []) {
        eprintln!("fyi: warning: failed to create spawned_agent index: {}", e);
    }
    if let Err(e) = conn.execute(TOOL_NAME_INDEX, []) {
        eprintln!("fyi: warning: failed to create tool_name index: {}", e);
    }
    if let Err(e) = conn.execute(SUBAGENT_TYPE_INDEX, []) {
        eprintln!("fyi: warning: failed to create subagent_type index: {}", e);
    }

    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::migrations::is_v1_schema;
    use std::path::PathBuf;

    fn temp_db_path() -> Result<PathBuf, String> {
        let nanos = std::time::SystemTime::now()
            .duration_since(std::time::UNIX_EPOCH)
            .map_err(|e| e.to_string())?
            .as_nanos();
        Ok(std::env::temp_dir().join(format!(
            "fyi-sqlite-schema-test-{}-{:?}-{}.db",
            std::process::id(),
            std::thread::current().id(),
            nanos
        )))
    }

    #[test]
    fn test_v1_schema_detection() -> Result<(), String> {
        // Create a database with v1 schema (has 'ts' column)
        let path = temp_db_path()?;

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

            // Verify v1 schema is detected
            assert!(is_v1_schema(&conn).map_err(|e| e.to_string())?);
        }

        // Trying to init schema should fail
        {
            let conn = Connection::open(&path).map_err(|e| e.to_string())?;
            let err = init_schema(&conn)
                .err()
                .ok_or("expected schema v1 error but got Ok")?;
            assert!(err.to_string().contains("schema v1"));
        }

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

    #[test]
    fn test_v2_schema_not_detected_as_v1() -> Result<(), String> {
        let path = temp_db_path()?;

        let conn = Connection::open(&path).map_err(|e| e.to_string())?;
        init_schema(&conn).map_err(|e| e.to_string())?;

        // V2+ schema should not be detected as v1
        assert!(!is_v1_schema(&conn).map_err(|e| e.to_string())?);

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

    #[test]
    fn test_fresh_database_gets_schema_version() -> Result<(), String> {
        let path = temp_db_path()?;

        let conn = Connection::open(&path).map_err(|e| e.to_string())?;
        init_schema(&conn).map_err(|e| e.to_string())?;

        // Check that schema_version table exists and has correct version
        let version = get_schema_version(&conn).map_err(|e| e.to_string())?;
        assert_eq!(version, Some(CURRENT_SCHEMA_VERSION));

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

    #[test]
    fn test_pre_versioning_database_gets_upgraded() -> Result<(), String> {
        let path = temp_db_path()?;

        // Create a pre-versioning v2 database (has events table but no schema_version)
        {
            let conn = Connection::open(&path).map_err(|e| e.to_string())?;
            conn.execute_batch(EVENTS_TABLE)
                .map_err(|e| e.to_string())?;

            // Verify no schema_version table yet
            assert!(!table_exists(&conn, "schema_version").map_err(|e| e.to_string())?);
        }

        // Open with init_schema - should add version tracking
        {
            let conn = Connection::open(&path).map_err(|e| e.to_string())?;
            init_schema(&conn).map_err(|e| e.to_string())?;

            // Now schema_version should exist with current version
            assert!(table_exists(&conn, "schema_version").map_err(|e| e.to_string())?);
            assert_eq!(
                get_schema_version(&conn).map_err(|e| e.to_string())?,
                Some(CURRENT_SCHEMA_VERSION)
            );
        }

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

    #[test]
    fn test_idempotent_init() -> Result<(), String> {
        let path = temp_db_path()?;

        let conn = Connection::open(&path).map_err(|e| e.to_string())?;

        // Initialize multiple times
        init_schema(&conn).map_err(|e| e.to_string())?;
        init_schema(&conn).map_err(|e| e.to_string())?;
        init_schema(&conn).map_err(|e| e.to_string())?;

        // Should still have correct version
        assert_eq!(
            get_schema_version(&conn).map_err(|e| e.to_string())?,
            Some(CURRENT_SCHEMA_VERSION)
        );

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

    #[test]
    fn test_schema_version_records_timestamp() -> Result<(), String> {
        let path = temp_db_path()?;

        let before = std::time::SystemTime::now()
            .duration_since(std::time::UNIX_EPOCH)
            .map_err(|e| e.to_string())?
            .as_millis() as i64;

        let conn = Connection::open(&path).map_err(|e| e.to_string())?;
        init_schema(&conn).map_err(|e| e.to_string())?;

        let after = std::time::SystemTime::now()
            .duration_since(std::time::UNIX_EPOCH)
            .map_err(|e| e.to_string())?
            .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",
                [CURRENT_SCHEMA_VERSION],
                |row| row.get(0),
            )
            .map_err(|e| e.to_string())?;

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

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

    #[test]
    fn test_table_exists() -> Result<(), String> {
        let path = temp_db_path()?;

        let conn = Connection::open(&path).map_err(|e| e.to_string())?;

        // No tables yet
        assert!(!table_exists(&conn, "events").map_err(|e| e.to_string())?);
        assert!(!table_exists(&conn, "schema_version").map_err(|e| e.to_string())?);

        // Create schema
        init_schema(&conn).map_err(|e| e.to_string())?;

        // Tables should exist now (api_requests no longer exists in v4)
        assert!(table_exists(&conn, "events").map_err(|e| e.to_string())?);
        assert!(!table_exists(&conn, "api_requests").map_err(|e| e.to_string())?);
        assert!(table_exists(&conn, "schema_version").map_err(|e| e.to_string())?);

        // Non-existent table should return false
        assert!(!table_exists(&conn, "nonexistent").map_err(|e| e.to_string())?);

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

    #[test]
    fn test_newer_schema_version_rejected() -> Result<(), String> {
        let path = temp_db_path()?;
        let conn = Connection::open(&path).map_err(|e| e.to_string())?;

        // Manually create a schema_version table with a future version
        conn.execute_batch(
            "CREATE TABLE schema_version (version INTEGER PRIMARY KEY, applied_at INTEGER NOT NULL)",
        )
        .map_err(|e| e.to_string())?;
        conn.execute("INSERT INTO schema_version VALUES (99, 0)", [])
            .map_err(|e| e.to_string())?;

        // Trying to init should fail with "newer than supported" error
        let err = init_schema(&conn)
            .err()
            .ok_or("expected newer than supported error but got Ok")?;
        assert!(
            err.to_string().contains("newer than supported"),
            "Expected error message to contain 'newer than supported', got: {}",
            err
        );

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

    #[test]
    fn test_events_table_structure() -> Result<(), String> {
        let path = temp_db_path()?;

        let conn = Connection::open(&path).map_err(|e| e.to_string())?;
        init_schema(&conn).map_err(|e| e.to_string())?;

        // Verify events table exists
        assert!(table_exists(&conn, "events").map_err(|e| e.to_string())?);

        // Verify key columns exist
        let columns: Vec<String> = conn
            .prepare("SELECT name FROM pragma_table_info('events')")
            .map_err(|e| e.to_string())?
            .query_map([], |row| row.get(0))
            .map_err(|e| e.to_string())?
            .collect::<Result<Vec<_>, _>>()
            .map_err(|e| e.to_string())?;

        // Check essential columns
        assert!(columns.contains(&"id".to_string()));
        assert!(columns.contains(&"machine_id".to_string()));
        assert!(columns.contains(&"timestamp".to_string()));
        assert!(columns.contains(&"event_type".to_string()));
        assert!(columns.contains(&"session_id".to_string()));
        assert!(columns.contains(&"model".to_string())); // API request column
        assert!(columns.contains(&"cost_usd".to_string())); // API request column

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

    #[test]
    fn test_v2_to_v4_migration() -> Result<(), String> {
        let path = temp_db_path()?;

        // Create a v2-style database with old index names and api_requests table
        {
            let conn = Connection::open(&path).map_err(|e| e.to_string())?;

            // Create schema version table and mark as v2
            conn.execute_batch(
                "CREATE TABLE schema_version (version INTEGER PRIMARY KEY, applied_at INTEGER NOT NULL)",
            )
            .map_err(|e| e.to_string())?;
            conn.execute("INSERT INTO schema_version VALUES (2, 0)", [])
                .map_err(|e| e.to_string())?;

            // Create events table with old index names
            conn.execute_batch(
                r"
                CREATE TABLE events (
                    id INTEGER PRIMARY KEY,
                    machine_id TEXT NOT NULL,
                    timestamp INTEGER NOT NULL,
                    event_type TEXT NOT NULL,
                    session_id TEXT NOT NULL
                );
                CREATE INDEX idx_session ON events(session_id);
                CREATE INDEX idx_timestamp ON events(timestamp);
                CREATE INDEX idx_event_type ON events(event_type);
                CREATE INDEX idx_machine_id ON events(machine_id);

                CREATE TABLE api_requests (
                    id INTEGER PRIMARY KEY,
                    machine_id TEXT NOT NULL,
                    timestamp INTEGER NOT NULL,
                    session_id TEXT NOT NULL,
                    framework TEXT,
                    model TEXT,
                    tokens_input INTEGER NOT NULL,
                    tokens_output INTEGER NOT NULL,
                    tokens_cache_read INTEGER,
                    tokens_cache_creation INTEGER,
                    cost_usd REAL,
                    duration_ms INTEGER,
                    metadata TEXT
                );
                CREATE INDEX idx_api_session ON api_requests(session_id);
                CREATE INDEX idx_api_timestamp ON api_requests(timestamp);
                CREATE INDEX idx_api_machine_id ON api_requests(machine_id);
                ",
            )
            .map_err(|e| e.to_string())?;

            // Insert some api_requests data to test migration
            conn.execute(
                "INSERT INTO api_requests (machine_id, timestamp, session_id, tokens_input, tokens_output, model) VALUES ('m1', 1000, 's1', 100, 50, 'claude-3')",
                [],
            )
            .map_err(|e| e.to_string())?;

            // Verify old indexes and api_requests table exist
            assert!(table_exists(&conn, "api_requests").map_err(|e| e.to_string())?);
        }

        // Run init_schema which should migrate v2 -> v3 -> v4
        {
            let conn = Connection::open(&path).map_err(|e| e.to_string())?;
            init_schema(&conn).map_err(|e| e.to_string())?;

            // Verify version is now 4
            assert_eq!(
                get_schema_version(&conn).map_err(|e| e.to_string())?,
                Some(4)
            );

            // Verify old indexes are gone
            let old_idx_session: i64 = conn
                .query_row(
                    "SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name='idx_session'",
                    [],
                    |row| row.get(0),
                )
                .map_err(|e| e.to_string())?;
            assert_eq!(old_idx_session, 0, "Old idx_session should be dropped");

            // Verify new indexes exist
            let new_idx_events_session: i64 = conn
                .query_row(
                    "SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name='idx_events_session_id'",
                    [],
                    |row| row.get(0),
                )
                .map_err(|e| e.to_string())?;
            assert_eq!(
                new_idx_events_session, 1,
                "New idx_events_session_id should exist"
            );

            // Verify api_requests table is GONE (merged into events)
            assert!(
                !table_exists(&conn, "api_requests").map_err(|e| e.to_string())?,
                "api_requests table should be dropped after v4 migration"
            );

            // Verify api_requests data was migrated to events table
            let migrated_count: i64 = conn
                .query_row(
                    "SELECT COUNT(*) FROM events WHERE event_type = 'ApiRequest'",
                    [],
                    |row| row.get(0),
                )
                .map_err(|e| e.to_string())?;
            assert_eq!(
                migrated_count, 1,
                "api_requests data should be migrated to events"
            );

            // Verify the migrated data has correct values
            let (model, tokens_in, tokens_out): (Option<String>, Option<i64>, Option<i64>) = conn
                .query_row(
                    "SELECT model, tokens_input, tokens_output FROM events WHERE event_type = 'ApiRequest'",
                    [],
                    |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)),
                )
                .map_err(|e| e.to_string())?;
            assert_eq!(model, Some("claude-3".to_string()));
            assert_eq!(tokens_in, Some(100));
            assert_eq!(tokens_out, Some(50));
        }

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

    #[test]
    fn test_v3_to_v4_migration() -> Result<(), String> {
        let path = temp_db_path()?;

        // Create a v3-style database with api_requests table
        {
            let conn = Connection::open(&path).map_err(|e| e.to_string())?;

            // Create schema version table and mark as v3
            conn.execute_batch(
                "CREATE TABLE schema_version (version INTEGER PRIMARY KEY, applied_at INTEGER NOT NULL)",
            )
            .map_err(|e| e.to_string())?;
            conn.execute("INSERT INTO schema_version VALUES (3, 0)", [])
                .map_err(|e| e.to_string())?;

            // Create v3-style tables (proper index names, but separate api_requests table)
            conn.execute_batch(
                r"
                CREATE TABLE events (
                    id INTEGER PRIMARY KEY,
                    machine_id TEXT NOT NULL,
                    timestamp INTEGER NOT NULL,
                    event_type TEXT NOT NULL,
                    session_id TEXT NOT NULL,
                    framework TEXT,
                    tool_use_id TEXT,
                    spawned_agent_id TEXT,
                    tool_name TEXT,
                    subagent_type TEXT,
                    permission_mode TEXT,
                    transcript_path TEXT,
                    pid INTEGER,
                    cwd TEXT,
                    payload TEXT,
                    metadata TEXT
                );
                CREATE INDEX idx_events_session_id ON events(session_id);
                CREATE INDEX idx_events_timestamp ON events(timestamp);
                CREATE INDEX idx_events_event_type ON events(event_type);
                CREATE INDEX idx_events_machine_id ON events(machine_id);

                CREATE TABLE api_requests (
                    id INTEGER PRIMARY KEY,
                    machine_id TEXT NOT NULL,
                    timestamp INTEGER NOT NULL,
                    session_id TEXT NOT NULL,
                    framework TEXT,
                    model TEXT,
                    tokens_input INTEGER NOT NULL,
                    tokens_output INTEGER NOT NULL,
                    tokens_cache_read INTEGER,
                    tokens_cache_creation INTEGER,
                    cost_usd REAL,
                    duration_ms INTEGER,
                    metadata TEXT
                );
                CREATE INDEX idx_api_requests_session_id ON api_requests(session_id);
                CREATE INDEX idx_api_requests_timestamp ON api_requests(timestamp);
                CREATE INDEX idx_api_requests_machine_id ON api_requests(machine_id);
                ",
            )
            .map_err(|e| e.to_string())?;

            // Insert test data
            conn.execute(
                "INSERT INTO events (machine_id, timestamp, event_type, session_id, framework) VALUES ('m1', 1000, 'SessionStart', 's1', 'claude')",
                [],
            )
            .map_err(|e| e.to_string())?;
            conn.execute(
                "INSERT INTO api_requests (machine_id, timestamp, session_id, framework, model, tokens_input, tokens_output, cost_usd) VALUES ('m1', 2000, 's1', 'claude', 'claude-3-opus', 1000, 500, 0.05)",
                [],
            )
            .map_err(|e| e.to_string())?;

            assert!(table_exists(&conn, "api_requests").map_err(|e| e.to_string())?);
        }

        // Run init_schema which should migrate v3 -> v4
        {
            let conn = Connection::open(&path).map_err(|e| e.to_string())?;
            init_schema(&conn).map_err(|e| e.to_string())?;

            // Verify version is now 4
            assert_eq!(
                get_schema_version(&conn).map_err(|e| e.to_string())?,
                Some(4)
            );

            // Verify api_requests table is gone
            assert!(!table_exists(&conn, "api_requests").map_err(|e| e.to_string())?);

            // Verify events table has the new columns
            let has_model_col: i64 = conn
                .query_row(
                    "SELECT COUNT(*) FROM pragma_table_info('events') WHERE name = 'model'",
                    [],
                    |row| row.get(0),
                )
                .map_err(|e| e.to_string())?;
            assert_eq!(has_model_col, 1, "events table should have model column");

            // Verify original events still exist
            let event_count: i64 = conn
                .query_row(
                    "SELECT COUNT(*) FROM events WHERE event_type = 'SessionStart'",
                    [],
                    |row| row.get(0),
                )
                .map_err(|e| e.to_string())?;
            assert_eq!(event_count, 1, "Original events should still exist");

            // Verify api_requests data was migrated
            let api_count: i64 = conn
                .query_row(
                    "SELECT COUNT(*) FROM events WHERE event_type = 'ApiRequest'",
                    [],
                    |row| row.get(0),
                )
                .map_err(|e| e.to_string())?;
            assert_eq!(api_count, 1, "API requests should be migrated to events");

            // Verify migrated data has correct values
            let cost: Option<f64> = conn
                .query_row(
                    "SELECT cost_usd FROM events WHERE event_type = 'ApiRequest'",
                    [],
                    |row| row.get(0),
                )
                .map_err(|e| e.to_string())?;
            assert_eq!(cost, Some(0.05));
        }

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