use mad_hatter::dao;
use mad_hatter::reconcile_schema;
use mad_hatter::db::Db;
use mad_hatter::{ConceptMapToken, SuppressibleToken, Critical};
use sqlx::FromRow;

dao!(TestInitDb, "test_init.db", init = "fixtures/test_init.sql");
dao!(TestPlainDb, "test_plain.db");
dao!(TestSchemaDb, "test_schema.db", schema = "fixtures/test_schema.json");
dao!(TestSchemaV2Db, "test_schema_v2.db", schema = "fixtures/test_schema_v2.json");
dao!(TestSchemaIdxDb, "test_schema_idx.db", schema = "fixtures/test_schema_indexes.json");

// dao! with custom config
dao!(TestCustomConfigDb, "test_custom.db", config {
    pool_size = 4,
    busy_timeout = 10000,
});

dao!(TestSchemaWithConfigDb, "test_schema_cfg.db", schema = "fixtures/test_schema.json", config {
    busy_timeout = 8000,
});

// ── Test helper: ConceptMapToken for unwrapping Critical in tests ──

#[derive(Copy, Clone, Debug)]
struct TestToken;
impl ConceptMapToken for TestToken {
    fn operation_name(&self) -> &'static str { "test" }
}
impl SuppressibleToken for TestToken {}

fn uc<T>(c: Critical<T>) -> T {
    c.suppress(TestToken, "test").unwrap()
}

// Helper structs for sqlx query results

#[derive(FromRow)]
struct TableName {
    name: String,
}

#[derive(FromRow)]
struct ItemRow {
    id: i32,
    name: String,
    value: Option<String>,
}

#[derive(FromRow)]
struct MetaRow {
    key: String,
    val: String,
}

#[derive(FromRow)]
struct ExtraRow {
    extra: String,
}

#[derive(FromRow)]
struct UserRow {
    id: i32,
    name: String,
}

#[derive(FromRow)]
struct PragmaJournalMode {
    journal_mode: String,
}

#[derive(FromRow)]
struct PragmaBusyTimeout {
    timeout: i32,
}

// ── Init SQL tests ──

#[tokio::test]
async fn test_init_creates_tables() {
    let dir = tempfile::tempdir().unwrap();
    let db = uc(TestInitDb::open(dir.path()).await);

    let tables: Vec<String> = db.db.fetch_scalars(
        "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name",
    ).await.unwrap();

    assert!(tables.contains(&"test_items".to_string()));
    assert!(tables.contains(&"test_meta".to_string()));
}

#[tokio::test]
async fn test_init_idempotent() {
    let dir = tempfile::tempdir().unwrap();
    // Open twice — should not error
    let _db1 = uc(TestInitDb::open(dir.path()).await);
    let _db2 = uc(TestInitDb::open(dir.path()).await);
}

#[tokio::test]
async fn test_init_insert_and_query() {
    let dir = tempfile::tempdir().unwrap();
    let db = uc(TestInitDb::open(dir.path()).await);

    db.db.execute_raw("INSERT INTO test_items (name, value) VALUES ('hello', 'world')")
        .await.unwrap();

    let items: Vec<ItemRow> = uc(
        db.db.fetch_all(sqlx::query_as("SELECT id, name, value FROM test_items")).await
    );

    assert_eq!(items.len(), 1);
    assert_eq!(items[0].name, "hello");
    assert_eq!(items[0].value, Some("world".to_string()));
}

#[tokio::test]
async fn test_plain_dao_still_works() {
    let dir = tempfile::tempdir().unwrap();
    let _db = uc(TestPlainDb::open(dir.path()).await);
    // Just verify open works without init
}

#[tokio::test]
async fn test_init_multi_table() {
    let dir = tempfile::tempdir().unwrap();
    let db = uc(TestInitDb::open(dir.path()).await);

    db.db.execute_raw("INSERT INTO test_meta (key, val) VALUES ('version', '1')")
        .await.unwrap();

    let meta: Vec<MetaRow> = uc(
        db.db.fetch_all(sqlx::query_as("SELECT key, val FROM test_meta")).await
    );

    assert_eq!(meta.len(), 1);
    assert_eq!(meta[0].key, "version");
    assert_eq!(meta[0].val, "1");
}

// ── Schema JSON tests ──

#[tokio::test]
async fn test_schema_creates_tables() {
    let dir = tempfile::tempdir().unwrap();
    let db = uc(TestSchemaDb::open(dir.path()).await);

    let tables: Vec<String> = db.db.fetch_scalars(
        "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name",
    ).await.unwrap();

    assert!(tables.contains(&"test_items".to_string()));
    assert!(tables.contains(&"test_meta".to_string()));
}

#[tokio::test]
async fn test_schema_idempotent() {
    let dir = tempfile::tempdir().unwrap();
    let _db1 = uc(TestSchemaDb::open(dir.path()).await);
    let _db2 = uc(TestSchemaDb::open(dir.path()).await);
}

#[tokio::test]
async fn test_schema_insert_and_query() {
    let dir = tempfile::tempdir().unwrap();
    let db = uc(TestSchemaDb::open(dir.path()).await);

    db.db.execute_raw("INSERT INTO test_items (name, value) VALUES ('hello', 'world')")
        .await.unwrap();

    let items: Vec<ItemRow> = uc(
        db.db.fetch_all(sqlx::query_as("SELECT id, name, value FROM test_items")).await
    );

    assert_eq!(items.len(), 1);
    assert_eq!(items[0].name, "hello");
    assert_eq!(items[0].value, Some("world".to_string()));
}

#[tokio::test]
async fn test_schema_auto_migration() {
    let dir = tempfile::tempdir().unwrap();
    let db_path = dir.path().join("migration_test.db");

    // Open a Db directly for manual reconcile testing
    let db = Db::open(db_path.to_str().unwrap(), 1, 5000, true).await.unwrap();

    // First reconcile with v1 schema
    let v1 = include_str!("fixtures/test_schema.json");
    reconcile_schema(&db, v1).await.unwrap();

    db.execute_raw("INSERT INTO test_items (name, value) VALUES ('before', 'migration')")
        .await.unwrap();

    // Reconcile with v2 schema (adds extra and description columns)
    let v2 = include_str!("fixtures/test_schema_v2.json");
    reconcile_schema(&db, v2).await.unwrap();

    // Verify old data still exists
    let items: Vec<ItemRow> = uc(
        db.fetch_all(sqlx::query_as("SELECT id, name, value FROM test_items")).await
    );
    assert_eq!(items.len(), 1);
    assert_eq!(items[0].name, "before");

    // Verify new column exists and works
    db.execute_raw(
        "INSERT INTO test_items (name, value, extra) VALUES ('after', 'migration', 'new_col')",
    ).await.unwrap();

    let extras: Vec<ExtraRow> = uc(
        db.fetch_all(sqlx::query_as("SELECT extra FROM test_items WHERE name = 'after'")).await
    );
    assert_eq!(extras[0].extra, "new_col");

    // Verify description column on test_meta
    db.execute_raw(
        "INSERT INTO test_meta (key, val, description) VALUES ('k', 'v', 'desc')",
    ).await.unwrap();
}

#[tokio::test]
async fn test_schema_indexes() {
    let dir = tempfile::tempdir().unwrap();
    let db = uc(TestSchemaIdxDb::open(dir.path()).await);

    let indexes: Vec<String> = db.db.fetch_scalars(
        "SELECT name FROM sqlite_master WHERE type='index' AND name='idx_test_items_name'",
    ).await.unwrap();
    assert_eq!(indexes.len(), 1);
}

#[tokio::test]
async fn test_reconcile_schema_direct() {
    let dir = tempfile::tempdir().unwrap();
    let db_path = dir.path().join("direct_test.db");

    let db = Db::open(db_path.to_str().unwrap(), 1, 5000, true).await.unwrap();

    let schema_v1 = r#"{
        "users": {
            "id": "INTEGER PRIMARY KEY AUTOINCREMENT",
            "name": "TEXT NOT NULL"
        }
    }"#;

    reconcile_schema(&db, schema_v1).await.unwrap();

    // Insert data
    db.execute_raw("INSERT INTO users (name) VALUES ('alice')").await.unwrap();

    // Reconcile v2: add email column
    let schema_v2 = r#"{
        "users": {
            "id": "INTEGER PRIMARY KEY AUTOINCREMENT",
            "name": "TEXT NOT NULL",
            "email": "TEXT NOT NULL DEFAULT ''"
        }
    }"#;

    reconcile_schema(&db, schema_v2).await.unwrap();

    // Verify old data + new column
    db.execute_raw("INSERT INTO users (name, email) VALUES ('bob', 'bob@example.com')")
        .await.unwrap();

    let users: Vec<UserRow> = uc(
        db.fetch_all(sqlx::query_as("SELECT id, name FROM users ORDER BY id")).await
    );
    assert_eq!(users.len(), 2);
    assert_eq!(users[0].name, "alice");
    assert_eq!(users[1].name, "bob");
}

// ── PRAGMA verification tests ──

#[tokio::test]
async fn test_pragma_wal_mode_set() {
    let dir = tempfile::tempdir().unwrap();
    let db = uc(TestPlainDb::open(dir.path()).await);

    let modes: Vec<String> = db.db.fetch_scalars("PRAGMA journal_mode")
        .await.unwrap();
    assert_eq!(modes[0].to_lowercase(), "wal");
}

#[tokio::test]
async fn test_pragma_busy_timeout_set() {
    let dir = tempfile::tempdir().unwrap();
    let db = uc(TestPlainDb::open(dir.path()).await);

    let timeouts: Vec<String> = db.db.fetch_scalars("PRAGMA busy_timeout")
        .await.unwrap();
    assert_eq!(timeouts[0], "5000");
}

#[tokio::test]
async fn test_pragma_custom_busy_timeout() {
    let dir = tempfile::tempdir().unwrap();
    let db = uc(TestCustomConfigDb::open(dir.path()).await);

    let timeouts: Vec<String> = db.db.fetch_scalars("PRAGMA busy_timeout")
        .await.unwrap();
    assert_eq!(timeouts[0], "10000");
}

#[tokio::test]
async fn test_pragma_schema_with_config() {
    let dir = tempfile::tempdir().unwrap();
    let db = uc(TestSchemaWithConfigDb::open(dir.path()).await);

    let timeouts: Vec<String> = db.db.fetch_scalars("PRAGMA busy_timeout")
        .await.unwrap();
    assert_eq!(timeouts[0], "8000");

    let modes: Vec<String> = db.db.fetch_scalars("PRAGMA journal_mode")
        .await.unwrap();
    assert_eq!(modes[0].to_lowercase(), "wal");
}

#[tokio::test]
async fn test_custom_config_pool_works() {
    let dir = tempfile::tempdir().unwrap();
    let _db = uc(TestCustomConfigDb::open(dir.path()).await);
    // pool_size=4, just verify open works with custom config
}