use rusqlite::Connection;

use astmap_core::DbError;

pub fn create_tables(conn: &Connection) -> Result<(), DbError> {
    conn.execute_batch(
        "
        -- Scan tracking
        CREATE TABLE IF NOT EXISTS scan (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            started_at  TEXT NOT NULL,
            completed_at TEXT,
            status      TEXT NOT NULL DEFAULT 'running',
            file_count  INTEGER NOT NULL DEFAULT 0,
            symbol_count INTEGER NOT NULL DEFAULT 0,
            dep_count   INTEGER NOT NULL DEFAULT 0,
            config_hash TEXT
        );

        -- Source files
        CREATE TABLE IF NOT EXISTS file (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            scan_id       INTEGER NOT NULL REFERENCES scan(id),
            path          TEXT NOT NULL UNIQUE,
            language      TEXT NOT NULL,
            size_bytes    INTEGER NOT NULL,
            content_hash  TEXT NOT NULL,
            last_modified TEXT NOT NULL,
            parse_status  TEXT NOT NULL DEFAULT 'ok'
        );

        -- Symbols (language-agnostic code entities)
        CREATE TABLE IF NOT EXISTS symbol (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            file_id    INTEGER NOT NULL REFERENCES file(id) ON DELETE CASCADE,
            parent_id  INTEGER REFERENCES symbol(id) ON DELETE CASCADE,
            name       TEXT NOT NULL,
            kind       TEXT NOT NULL,
            signature  TEXT,
            summary    TEXT,
            start_line INTEGER NOT NULL,
            end_line   INTEGER NOT NULL,
            start_byte INTEGER NOT NULL,
            end_byte   INTEGER NOT NULL
        );

        -- Dependencies (directed relationships)
        CREATE TABLE IF NOT EXISTS dependency (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            source_file_id   INTEGER NOT NULL REFERENCES file(id) ON DELETE CASCADE,
            source_symbol_id INTEGER REFERENCES symbol(id) ON DELETE CASCADE,
            target_file_id   INTEGER REFERENCES file(id) ON DELETE CASCADE,
            target_symbol_id INTEGER REFERENCES symbol(id) ON DELETE CASCADE,
            dep_type         TEXT NOT NULL,
            level            TEXT NOT NULL,
            raw_import       TEXT
        );

        -- Configuration (key-value store)
        CREATE TABLE IF NOT EXISTS config (
            key        TEXT PRIMARY KEY,
            value      TEXT NOT NULL,
            updated_at TEXT NOT NULL
        );

        -- FTS5: full-text search on symbol names and summaries
        CREATE VIRTUAL TABLE IF NOT EXISTS symbol_fts USING fts5(
            name, summary,
            content=symbol, content_rowid=id
        );

        -- FTS5: full-text search on file paths
        CREATE VIRTUAL TABLE IF NOT EXISTS file_fts USING fts5(
            path,
            content=file, content_rowid=id
        );

        -- Indexes
        CREATE INDEX IF NOT EXISTS idx_file_language ON file(language);
        CREATE INDEX IF NOT EXISTS idx_file_content_hash ON file(content_hash);

        CREATE UNIQUE INDEX IF NOT EXISTS idx_symbol_identity
            ON symbol(file_id, COALESCE(parent_id, 0), name, kind);
        CREATE INDEX IF NOT EXISTS idx_symbol_file ON symbol(file_id);
        CREATE INDEX IF NOT EXISTS idx_symbol_parent ON symbol(parent_id);
        CREATE INDEX IF NOT EXISTS idx_symbol_kind ON symbol(kind);
        CREATE INDEX IF NOT EXISTS idx_symbol_name ON symbol(name);

        CREATE INDEX IF NOT EXISTS idx_dep_source_file ON dependency(source_file_id);
        CREATE INDEX IF NOT EXISTS idx_dep_source_symbol ON dependency(source_symbol_id);
        CREATE INDEX IF NOT EXISTS idx_dep_target_file ON dependency(target_file_id);
        CREATE INDEX IF NOT EXISTS idx_dep_target_symbol ON dependency(target_symbol_id);
        CREATE INDEX IF NOT EXISTS idx_dep_type ON dependency(dep_type);

        -- FTS5 sync triggers for symbol_fts
        CREATE TRIGGER IF NOT EXISTS symbol_fts_insert AFTER INSERT ON symbol BEGIN
            INSERT INTO symbol_fts(rowid, name, summary)
            VALUES (new.id, new.name, new.summary);
        END;

        CREATE TRIGGER IF NOT EXISTS symbol_fts_delete AFTER DELETE ON symbol BEGIN
            INSERT INTO symbol_fts(symbol_fts, rowid, name, summary)
            VALUES ('delete', old.id, old.name, old.summary);
        END;

        CREATE TRIGGER IF NOT EXISTS symbol_fts_update AFTER UPDATE ON symbol BEGIN
            INSERT INTO symbol_fts(symbol_fts, rowid, name, summary)
            VALUES ('delete', old.id, old.name, old.summary);
            INSERT INTO symbol_fts(rowid, name, summary)
            VALUES (new.id, new.name, new.summary);
        END;

        -- FTS5 sync triggers for file_fts
        CREATE TRIGGER IF NOT EXISTS file_fts_insert AFTER INSERT ON file BEGIN
            INSERT INTO file_fts(rowid, path) VALUES (new.id, new.path);
        END;

        CREATE TRIGGER IF NOT EXISTS file_fts_delete AFTER DELETE ON file BEGIN
            INSERT INTO file_fts(file_fts, rowid, path)
            VALUES ('delete', old.id, old.path);
        END;

        CREATE TRIGGER IF NOT EXISTS file_fts_update AFTER UPDATE ON file BEGIN
            INSERT INTO file_fts(file_fts, rowid, path)
            VALUES ('delete', old.id, old.path);
            INSERT INTO file_fts(rowid, path) VALUES (new.id, new.path);
        END;
        ",
    )
    .map_err(|e| DbError::Storage(e.to_string()))?;
    Ok(())
}

/// Run schema migrations for existing databases.
pub fn migrate(_conn: &Connection) -> Result<(), DbError> {
    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;
    use rusqlite::Connection;

    #[test]
    fn test_create_tables() {
        let conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        create_tables(&conn).unwrap();

        // Verify all tables exist
        let tables: Vec<String> = conn
            .prepare("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
            .unwrap()
            .query_map([], |row| row.get(0))
            .unwrap()
            .filter_map(|r| r.ok())
            .collect();

        assert!(tables.contains(&"scan".to_string()));
        assert!(tables.contains(&"file".to_string()));
        assert!(tables.contains(&"symbol".to_string()));
        assert!(tables.contains(&"dependency".to_string()));
        assert!(tables.contains(&"config".to_string()));
    }

    #[test]
    fn test_create_tables_idempotent() {
        let conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        create_tables(&conn).unwrap();
        // Second call should not fail
        create_tables(&conn).unwrap();
    }

    #[test]
    fn test_fts5_trigger_fires() {
        let conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        create_tables(&conn).unwrap();

        // Insert a scan and file first
        conn.execute(
            "INSERT INTO scan (started_at, status) VALUES ('2026-01-01T00:00:00Z', 'completed')",
            [],
        )
        .unwrap();
        conn.execute(
            "INSERT INTO file (scan_id, path, language, size_bytes, content_hash, last_modified)
             VALUES (1, 'src/main.rs', 'rust', 100, 'abc123', '2026-01-01T00:00:00Z')",
            [],
        )
        .unwrap();

        // Insert a symbol — FTS trigger should fire
        conn.execute(
            "INSERT INTO symbol (file_id, name, kind, start_line, end_line, start_byte, end_byte)
             VALUES (1, 'MyStruct', 'struct', 1, 10, 0, 100)",
            [],
        )
        .unwrap();

        // Search FTS5
        let count: i64 = conn
            .query_row(
                "SELECT count(*) FROM symbol_fts WHERE symbol_fts MATCH 'MyStruct'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(count, 1);
    }

    /// Helper: set up a DB with a scan, file, and symbol for FTS trigger tests.
    fn setup_fts_test_db() -> Connection {
        let conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        create_tables(&conn).unwrap();
        conn.execute(
            "INSERT INTO scan (started_at, status) VALUES ('2026-01-01T00:00:00Z', 'completed')",
            [],
        )
        .unwrap();
        conn.execute(
            "INSERT INTO file (scan_id, path, language, size_bytes, content_hash, last_modified)
             VALUES (1, 'src/main.rs', 'rust', 100, 'abc123', '2026-01-01T00:00:00Z')",
            [],
        )
        .unwrap();
        conn
    }

    #[test]
    fn test_fts5_trigger_delete() {
        let conn = setup_fts_test_db();
        conn.execute(
            "INSERT INTO symbol (file_id, name, kind, start_line, end_line, start_byte, end_byte)
             VALUES (1, 'DeleteMe', 'struct', 1, 10, 0, 100)",
            [],
        )
        .unwrap();

        // Verify it's in FTS
        let count: i64 = conn
            .query_row(
                "SELECT count(*) FROM symbol_fts WHERE symbol_fts MATCH 'DeleteMe'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(count, 1);

        // Delete the symbol
        conn.execute("DELETE FROM symbol WHERE name = 'DeleteMe'", [])
            .unwrap();

        // FTS should be cleaned up
        let count: i64 = conn
            .query_row(
                "SELECT count(*) FROM symbol_fts WHERE symbol_fts MATCH 'DeleteMe'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(count, 0);
    }

    #[test]
    fn test_fts5_trigger_update() {
        let conn = setup_fts_test_db();
        conn.execute(
            "INSERT INTO symbol (file_id, name, kind, start_line, end_line, start_byte, end_byte)
             VALUES (1, 'OldName', 'struct', 1, 10, 0, 100)",
            [],
        )
        .unwrap();

        // Update the symbol name
        conn.execute(
            "UPDATE symbol SET name = 'NewName' WHERE name = 'OldName'",
            [],
        )
        .unwrap();

        // Old name should be gone from FTS
        let count: i64 = conn
            .query_row(
                "SELECT count(*) FROM symbol_fts WHERE symbol_fts MATCH 'OldName'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(count, 0);

        // New name should be in FTS
        let count: i64 = conn
            .query_row(
                "SELECT count(*) FROM symbol_fts WHERE symbol_fts MATCH 'NewName'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(count, 1);
    }

    #[test]
    fn test_file_fts_trigger_insert() {
        let conn = setup_fts_test_db();
        // The file inserted in setup should be in file_fts
        let count: i64 = conn
            .query_row(
                "SELECT count(*) FROM file_fts WHERE file_fts MATCH 'main'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(count, 1);
    }

    #[test]
    fn test_file_fts_trigger_delete() {
        let conn = setup_fts_test_db();
        conn.execute("DELETE FROM file WHERE id = 1", []).unwrap();

        let count: i64 = conn
            .query_row(
                "SELECT count(*) FROM file_fts WHERE file_fts MATCH 'main'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(count, 0);
    }

    #[test]
    fn test_migrate_is_idempotent() {
        let conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        create_tables(&conn).unwrap();
        migrate(&conn).unwrap();
        migrate(&conn).unwrap();
    }
}
