mod common;

use common::{TestEnv, ok};
use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions};
use sqlx::{Connection as _, Row, SqliteConnection};
use std::collections::HashSet;
use std::path::Path;
use std::time::Duration;

const READ_PATH_INDEXES: &[(&str, &str)] = &[
    (
        "idx_tasks_workspace_deleted_updated",
        "CREATE INDEX idx_tasks_workspace_deleted_updated ON tasks(workspace_id, deleted, updated_at DESC, created_at DESC)",
    ),
    (
        "idx_tasks_workspace_deleted_status_updated",
        "CREATE INDEX idx_tasks_workspace_deleted_status_updated ON tasks(workspace_id, deleted, status, updated_at DESC, created_at DESC)",
    ),
    (
        "idx_tasks_workspace_deleted_priority_updated",
        "CREATE INDEX idx_tasks_workspace_deleted_priority_updated ON tasks(workspace_id, deleted, priority, updated_at DESC, created_at DESC)",
    ),
    (
        "idx_tasks_workspace_project_deleted_updated",
        "CREATE INDEX idx_tasks_workspace_project_deleted_updated ON tasks(workspace_id, project_id, deleted, updated_at DESC, created_at DESC)",
    ),
    (
        "idx_tasks_workspace_project_deleted_status",
        "CREATE INDEX idx_tasks_workspace_project_deleted_status ON tasks(workspace_id, project_id, deleted, status)",
    ),
    (
        "idx_conflicts_workspace_resolved_created_task",
        "CREATE INDEX idx_conflicts_workspace_resolved_created_task ON conflicts(workspace_id, resolved, created_at, task_id)",
    ),
    (
        "idx_conflicts_workspace_resolved_task",
        "CREATE INDEX idx_conflicts_workspace_resolved_task ON conflicts(workspace_id, resolved, task_id)",
    ),
    (
        "idx_task_labels_workspace_label_task",
        "CREATE INDEX idx_task_labels_workspace_label_task ON task_labels(workspace_id, label, task_id)",
    ),
];

const READ_PATH_INDEX_MIGRATION: &str =
    include_str!("../migrations/20260621000000_read_path_indexes.sql");

#[test]
fn fresh_database_creates_read_path_indexes() {
    let env = TestEnv::new();
    let db = env.db("fresh.sqlite");

    ok(env.aven(&db, ["list"]));

    let indexes = read_index_names(&db);
    for (index, _) in READ_PATH_INDEXES {
        assert!(indexes.contains(*index), "missing index {index}");
    }
}

#[test]
fn fresh_database_index_ddl_matches_migration() {
    let env = TestEnv::new();
    let db = env.db("fresh-ddl.sqlite");

    ok(env.aven(&db, ["list"]));

    let ddl = read_index_ddl(&db);
    for (index, expected) in READ_PATH_INDEXES {
        let actual = ddl
            .get(*index)
            .unwrap_or_else(|| panic!("missing index ddl for {index}"));
        assert_eq!(
            normalize_sql(actual),
            normalize_sql(expected),
            "unexpected ddl for {index}"
        );
    }
}

#[test]
fn existing_database_migration_preserves_data() {
    let env = TestEnv::new();
    let db = env.db("existing.sqlite");

    ok(env.aven(&db, ["project", "create", "app"]));
    ok(env.aven(&db, ["label", "create", "bug"]));
    ok(env.aven(
        &db,
        [
            "add",
            "indexed task",
            "--project",
            "app",
            "--priority",
            "high",
            "--label",
            "bug",
        ],
    ));

    ok(env.aven(&db, ["list", "--label", "bug"]));

    let runtime = runtime();
    let (task_count, task_title, label_count) = runtime.block_on(async {
        let mut conn = open_db(&db).await;
        let task_count = sqlx::query_scalar::<_, i64>("SELECT count(*) FROM tasks")
            .fetch_one(&mut conn)
            .await
            .expect("count tasks");
        let task_title = sqlx::query_scalar::<_, String>("SELECT title FROM tasks")
            .fetch_one(&mut conn)
            .await
            .expect("read task title");
        let label_count = sqlx::query_scalar::<_, i64>("SELECT count(*) FROM task_labels")
            .fetch_one(&mut conn)
            .await
            .expect("count task labels");
        (task_count, task_title, label_count)
    });

    assert_eq!(task_count, 1);
    assert_eq!(task_title, "indexed task");
    assert_eq!(label_count, 1);

    let indexes = read_index_names(&db);
    for (index, _) in READ_PATH_INDEXES {
        assert!(indexes.contains(*index), "missing index {index}");
    }
}

#[test]
fn old_schema_database_upgrade_creates_read_path_indexes() {
    let env = TestEnv::new();
    let db = env.db("old-upgrade.sqlite");

    let runtime = runtime();
    runtime.block_on(async {
        let pool = SqlitePoolOptions::new()
            .max_connections(1)
            .connect_with(
                SqliteConnectOptions::new()
                    .filename(&db)
                    .create_if_missing(true),
            )
            .await
            .expect("open old schema db");

        sqlx::raw_sql(include_str!("../migrations/20260618000000_initial.sql"))
            .execute(&pool)
            .await
            .expect("apply initial migration");
        sqlx::query(
            "INSERT INTO projects(key, name, prefix, created_at, updated_at)
             VALUES ('app', 'app', 'APP', 't', 't')",
        )
        .execute(&pool)
        .await
        .expect("insert project");
        sqlx::query(
            "INSERT INTO tasks(id, title, description, project_key, status, priority, created_at, updated_at)
             VALUES ('7KQ9A1X4MV2P8D6R', 'old task', '', 'app', 'inbox', 'none', 't', 't')",
        )
        .execute(&pool)
        .await
        .expect("insert task");
    });

    let shown = ok(env.aven(&db, ["show", "7KQ"]));
    assert!(
        shown.contains("old task"),
        "expected old task after upgrade\n{shown}"
    );

    let indexes = read_index_names(&db);
    for (index, _) in READ_PATH_INDEXES {
        assert!(
            indexes.contains(*index),
            "missing index {index} after upgrade"
        );
    }
}

#[test]
fn read_path_index_migration_sql_is_idempotent() {
    let env = TestEnv::new();
    let db = env.db("idempotent.sqlite");

    ok(env.aven(&db, ["list"]));

    let runtime = runtime();
    runtime.block_on(async {
        let mut conn = open_db(&db).await;
        sqlx::raw_sql(READ_PATH_INDEX_MIGRATION)
            .execute(&mut conn)
            .await
            .expect("first direct migration apply");
        sqlx::raw_sql(READ_PATH_INDEX_MIGRATION)
            .execute(&mut conn)
            .await
            .expect("second direct migration apply");
    });

    let indexes = read_index_names(&db);
    for (index, _) in READ_PATH_INDEXES {
        assert!(indexes.contains(*index), "missing index {index}");
    }
}

#[test]
fn common_read_filters_have_workspace_scoped_query_plans() {
    let env = TestEnv::new();
    let db = env.db("plans.sqlite");
    ok(env.aven(&db, ["project", "create", "app"]));

    let runtime = runtime();
    runtime.block_on(async {
        let mut conn = open_db(&db).await;
        seed_plan_rows(&mut conn).await;
        sqlx::query("ANALYZE").execute(&mut conn).await.expect("analyze");

        assert_plan_uses(
            &mut conn,
            "EXPLAIN QUERY PLAN
             SELECT t.id FROM tasks t
             WHERE t.workspace_id = ? AND t.deleted = 0
             ORDER BY t.updated_at DESC, t.created_at DESC",
            &["0000000000000000"],
            "idx_tasks_workspace_deleted_updated",
        )
        .await;

        assert_plan_uses(
            &mut conn,
            "EXPLAIN QUERY PLAN
             SELECT t.id FROM tasks t
             WHERE t.workspace_id = ? AND t.deleted = 0 AND t.status = ?
             ORDER BY t.updated_at DESC, t.created_at DESC",
            &["0000000000000000", "todo"],
            "idx_tasks_workspace_deleted_status_updated",
        )
        .await;

        assert_plan_uses(
            &mut conn,
            "EXPLAIN QUERY PLAN
             SELECT t.id FROM tasks t
             WHERE t.workspace_id = ? AND t.deleted = 0 AND t.priority = ?
             ORDER BY t.updated_at DESC, t.created_at DESC",
            &["0000000000000000", "high"],
            "idx_tasks_workspace_deleted_priority_updated",
        )
        .await;

        assert_plan_uses(
            &mut conn,
            "EXPLAIN QUERY PLAN
             SELECT t.id FROM tasks t
             WHERE t.workspace_id = ? AND t.deleted = 0 AND t.project_id = ?
             ORDER BY t.updated_at DESC, t.created_at DESC",
            &["0000000000000000", "APPPROJECT000001"],
            "idx_tasks_workspace_project_deleted_updated",
        )
        .await;

        assert_plan_uses(
            &mut conn,
            "EXPLAIN QUERY PLAN
             SELECT t.id FROM tasks t
             WHERE t.workspace_id = ? AND t.deleted = 0
             AND t.id IN (
                 SELECT tl.task_id FROM task_labels tl INDEXED BY idx_task_labels_workspace_label_task
                 WHERE tl.workspace_id = ? AND tl.label = ?
             )
             ORDER BY t.updated_at DESC, t.created_at DESC",
            &["0000000000000000", "0000000000000000", "bug"],
            "idx_task_labels_workspace_label_task",
        )
        .await;

        assert_plan_uses(
            &mut conn,
            "EXPLAIN QUERY PLAN
             SELECT c.task_id FROM conflicts c
             WHERE c.workspace_id = ? AND c.resolved = 0
             ORDER BY c.created_at",
            &["0000000000000000"],
            "idx_conflicts_workspace_resolved_created_task",
        )
        .await;

        assert_plan_uses(
            &mut conn,
            "EXPLAIN QUERY PLAN
             SELECT count(*) FROM tasks
             WHERE workspace_id = ? AND deleted = 0 AND status = ?",
            &["0000000000000000", "active"],
            "idx_tasks_workspace_deleted_status_updated",
        )
        .await;
    });
}

async fn seed_plan_rows(conn: &mut SqliteConnection) {
    sqlx::query(
        "INSERT INTO projects(id, workspace_id, key, name, prefix, created_at, updated_at)
         VALUES ('APPPROJECT000001', '0000000000000000', 'app2', 'app2', 'AP2', 't', 't');
         INSERT INTO tasks(id, workspace_id, title, description, project_id, status, priority, created_at, updated_at)
         VALUES
         ('0000000000001001', '0000000000000000', 'todo bug', '', 'APPPROJECT000001', 'todo', 'high', '001', '003'),
         ('0000000000001002', '0000000000000000', 'active', '', 'APPPROJECT000001', 'active', 'low', '002', '004')",
    )
    .execute(&mut *conn)
    .await
    .expect("insert tasks");

    sqlx::query(
        "INSERT INTO task_labels(workspace_id, task_id, label)
         VALUES ('0000000000000000', '0000000000001001', 'bug')",
    )
    .execute(&mut *conn)
    .await
    .expect("insert task label");

    sqlx::query(
        "INSERT INTO conflicts(workspace_id, task_id, field, base_version, local_value, remote_value,
         local_change_id, remote_change_id, variant_a, variant_b, created_at, resolved)
         VALUES ('0000000000000000', '0000000000001001', 'title', NULL, 'local', 'remote', NULL,
         'remote-change', 'a', 'b', '005', 0)",
    )
    .execute(&mut *conn)
    .await
    .expect("insert conflict");
}

async fn assert_plan_uses(
    conn: &mut SqliteConnection,
    sql: &'static str,
    binds: &[&str],
    index_name: &str,
) {
    let mut query = sqlx::query(sql);
    for bind in binds {
        query = query.bind(*bind);
    }
    let rows = query
        .fetch_all(&mut *conn)
        .await
        .expect("explain query plan");
    let plan = rows
        .iter()
        .map(|row| row.get::<String, _>("detail"))
        .collect::<Vec<_>>()
        .join("\n");
    assert!(
        plan.contains(index_name),
        "expected plan to use {index_name}\n{plan}"
    );
}

fn normalize_sql(sql: &str) -> String {
    sql.split_whitespace().collect::<Vec<_>>().join(" ")
}

fn read_index_names(db: &Path) -> HashSet<String> {
    runtime().block_on(async {
        let mut conn = open_db(db).await;
        sqlx::query_scalar::<_, String>(
            "SELECT name FROM sqlite_master WHERE type = 'index' AND name LIKE 'idx_%'",
        )
        .fetch_all(&mut conn)
        .await
        .expect("read indexes")
        .into_iter()
        .collect()
    })
}

fn read_index_ddl(db: &Path) -> std::collections::HashMap<String, String> {
    runtime().block_on(async {
        let mut conn = open_db(db).await;
        let rows = sqlx::query(
            "SELECT name, sql FROM sqlite_master WHERE type = 'index' AND name LIKE 'idx_%'",
        )
        .fetch_all(&mut conn)
        .await
        .expect("read index ddl");
        rows.into_iter()
            .map(|row| (row.get("name"), row.get("sql")))
            .collect()
    })
}

async fn open_db(db: &Path) -> SqliteConnection {
    let options = SqliteConnectOptions::new()
        .filename(db)
        .create_if_missing(false)
        .foreign_keys(true)
        .journal_mode(SqliteJournalMode::Wal)
        .busy_timeout(Duration::from_secs(5));
    SqliteConnection::connect_with(&options)
        .await
        .expect("open sqlite db")
}

fn runtime() -> tokio::runtime::Runtime {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .expect("create tokio runtime")
}
