use rusqlite::Connection;

const SCHEMA_V0: &str = r#"
CREATE TABLE IF NOT EXISTS agents (
    id TEXT PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS experiences (
    id TEXT PRIMARY KEY,
    agent_id TEXT NOT NULL REFERENCES agents(id),
    input_summary TEXT,
    lesson TEXT NOT NULL,
    created_at INTEGER NOT NULL
);

CREATE VIRTUAL TABLE IF NOT EXISTS experiences_fts USING fts5(
    input_summary,
    lesson,
    content=experiences,
    content_rowid=rowid
);

-- FTS triggers for auto-sync
CREATE TRIGGER IF NOT EXISTS experiences_ai AFTER INSERT ON experiences BEGIN
    INSERT INTO experiences_fts(rowid, input_summary, lesson)
    VALUES (new.rowid, new.input_summary, new.lesson);
END;

CREATE TRIGGER IF NOT EXISTS experiences_ad AFTER DELETE ON experiences BEGIN
    INSERT INTO experiences_fts(experiences_fts, rowid, input_summary, lesson)
    VALUES ('delete', old.rowid, old.input_summary, old.lesson);
END;

CREATE TRIGGER IF NOT EXISTS experiences_au AFTER UPDATE ON experiences BEGIN
    INSERT INTO experiences_fts(experiences_fts, rowid, input_summary, lesson)
    VALUES ('delete', old.rowid, old.input_summary, old.lesson);
    INSERT INTO experiences_fts(rowid, input_summary, lesson)
    VALUES (new.rowid, new.input_summary, new.lesson);
END;

CREATE TABLE IF NOT EXISTS usage_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action TEXT NOT NULL,
    agent_name TEXT,
    created_at INTEGER NOT NULL
);
"#;

/// v0 → v1: Add projects table, agent hierarchy, source linking, notes, tags, access log
fn migrate_v0_to_v1(conn: &Connection) -> rusqlite::Result<()> {
    conn.execute_batch(
        r#"
        -- Project hierarchy
        CREATE TABLE IF NOT EXISTS projects (
            id TEXT PRIMARY KEY,
            name TEXT UNIQUE NOT NULL,
            description TEXT,
            created_at INTEGER NOT NULL
        );
        ALTER TABLE agents ADD COLUMN project_id TEXT REFERENCES projects(id);

        -- Source linking
        ALTER TABLE experiences ADD COLUMN source_session TEXT;
        ALTER TABLE experiences ADD COLUMN source_file TEXT;

        -- Refined lesson
        ALTER TABLE experiences ADD COLUMN refined_lesson TEXT;

        -- User notes
        CREATE TABLE IF NOT EXISTS notes (
            id TEXT PRIMARY KEY,
            experience_id TEXT NOT NULL REFERENCES experiences(id) ON DELETE CASCADE,
            content TEXT NOT NULL,
            created_at INTEGER NOT NULL
        );

        -- Tags
        CREATE TABLE IF NOT EXISTS tags (
            id TEXT PRIMARY KEY,
            name TEXT UNIQUE NOT NULL,
            color TEXT
        );
        CREATE TABLE IF NOT EXISTS experience_tags (
            experience_id TEXT NOT NULL REFERENCES experiences(id) ON DELETE CASCADE,
            tag_id TEXT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
            PRIMARY KEY (experience_id, tag_id)
        );

        -- AI access tracking
        CREATE TABLE IF NOT EXISTS access_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            experience_id TEXT NOT NULL REFERENCES experiences(id) ON DELETE CASCADE,
            action TEXT NOT NULL,
            session_id TEXT,
            created_at INTEGER NOT NULL
        );

        -- Rebuild FTS to include refined_lesson
        DROP TRIGGER IF EXISTS experiences_ai;
        DROP TRIGGER IF EXISTS experiences_ad;
        DROP TRIGGER IF EXISTS experiences_au;
        DROP TABLE IF EXISTS experiences_fts;

        CREATE VIRTUAL TABLE experiences_fts USING fts5(
            input_summary,
            lesson,
            refined_lesson,
            content=experiences,
            content_rowid=rowid
        );

        CREATE TRIGGER experiences_ai AFTER INSERT ON experiences BEGIN
            INSERT INTO experiences_fts(rowid, input_summary, lesson, refined_lesson)
            VALUES (new.rowid, new.input_summary, new.lesson, new.refined_lesson);
        END;

        CREATE TRIGGER experiences_ad AFTER DELETE ON experiences BEGIN
            INSERT INTO experiences_fts(experiences_fts, rowid, input_summary, lesson, refined_lesson)
            VALUES ('delete', old.rowid, old.input_summary, old.lesson, old.refined_lesson);
        END;

        CREATE TRIGGER experiences_au AFTER UPDATE ON experiences BEGIN
            INSERT INTO experiences_fts(experiences_fts, rowid, input_summary, lesson, refined_lesson)
            VALUES ('delete', old.rowid, old.input_summary, old.lesson, old.refined_lesson);
            INSERT INTO experiences_fts(rowid, input_summary, lesson, refined_lesson)
            VALUES (new.rowid, new.input_summary, new.lesson, new.refined_lesson);
        END;
        "#,
    )?;
    // Rebuild FTS index for existing data
    conn.execute_batch("INSERT INTO experiences_fts(experiences_fts) VALUES('rebuild');")?;
    Ok(())
}

fn get_version(conn: &Connection) -> rusqlite::Result<i32> {
    conn.pragma_query_value(None, "user_version", |row| row.get(0))
}

fn set_version(conn: &Connection, version: i32) -> rusqlite::Result<()> {
    conn.pragma_update(None, "user_version", version)
}

pub fn init_db(db_path: &str) -> rusqlite::Result<Connection> {
    let conn = Connection::open(db_path)?;
    conn.pragma_update(None, "journal_mode", "WAL")?;
    conn.pragma_update(None, "foreign_keys", "ON")?;

    // Base schema (idempotent for new DBs)
    conn.execute_batch(SCHEMA_V0)?;

    // Run pending migrations
    let version = get_version(&conn)?;
    if version < 1 {
        migrate_v0_to_v1(&conn)?;
        set_version(&conn, 1)?;
    }

    Ok(conn)
}
