use citadel::{Argon2Profile, DatabaseBuilder};
use citadel_sql::{Connection, ExecutionResult, Value};

fn create_db(dir: &std::path::Path) -> citadel::Database {
    let db_path = dir.join("test.db");
    DatabaseBuilder::new(db_path)
        .passphrase(b"test-passphrase")
        .argon2_profile(Argon2Profile::Iot)
        .create()
        .unwrap()
}

fn open_db(dir: &std::path::Path) -> citadel::Database {
    let db_path = dir.join("test.db");
    DatabaseBuilder::new(db_path)
        .passphrase(b"test-passphrase")
        .argon2_profile(Argon2Profile::Iot)
        .open()
        .unwrap()
}

fn assert_rows_affected(result: ExecutionResult, expected: u64) {
    match result {
        ExecutionResult::RowsAffected(n) => assert_eq!(n, expected),
        other => panic!("expected RowsAffected({expected}), got {other:?}"),
    }
}

fn assert_ok(result: ExecutionResult) {
    match result {
        ExecutionResult::Ok => {}
        other => panic!("expected Ok, got {other:?}"),
    }
}

#[test]
fn stress_exists_500x500() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();

    assert_ok(
        conn.execute("CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, cat INTEGER)")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE t2 (id INTEGER NOT NULL PRIMARY KEY, cat INTEGER)")
            .unwrap(),
    );

    conn.execute("BEGIN").unwrap();
    for i in 0..500 {
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO t1 VALUES ({i}, {})", i % 50))
                .unwrap(),
            1,
        );
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO t2 VALUES ({i}, {})", i % 25))
                .unwrap(),
            1,
        );
    }
    conn.execute("COMMIT").unwrap();

    let qr = conn
        .query("SELECT COUNT(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.cat = t1.cat)")
        .unwrap();
    // cats 0..24 exist in both, cats 25..49 only in t1
    // t1 has 500 rows, 10 per cat. 25 cats match → 250 rows
    assert_eq!(qr.rows[0][0], Value::Integer(250));
}

#[test]
fn stress_not_exists_500x500() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();

    assert_ok(
        conn.execute("CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, cat INTEGER)")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE t2 (id INTEGER NOT NULL PRIMARY KEY, cat INTEGER)")
            .unwrap(),
    );

    conn.execute("BEGIN").unwrap();
    for i in 0..500 {
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO t1 VALUES ({i}, {})", i % 50))
                .unwrap(),
            1,
        );
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO t2 VALUES ({i}, {})", i % 25))
                .unwrap(),
            1,
        );
    }
    conn.execute("COMMIT").unwrap();

    let qr = conn
        .query("SELECT COUNT(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.cat = t1.cat)")
        .unwrap();
    assert_eq!(qr.rows[0][0], Value::Integer(250));
}

#[test]
fn stress_correlated_update() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();

    assert_ok(
        conn.execute("CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, val INTEGER)")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE t2 (id INTEGER NOT NULL PRIMARY KEY, ref_id INTEGER)")
            .unwrap(),
    );

    conn.execute("BEGIN").unwrap();
    for i in 0..200 {
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO t1 VALUES ({i}, 0)"))
                .unwrap(),
            1,
        );
    }
    for i in (0..200).step_by(2) {
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO t2 VALUES ({i}, {i})"))
                .unwrap(),
            1,
        );
    }
    conn.execute("COMMIT").unwrap();

    assert_rows_affected(
        conn.execute(
            "UPDATE t1 SET val = 1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.ref_id = t1.id)",
        )
        .unwrap(),
        100,
    );

    let qr = conn.query("SELECT COUNT(*) FROM t1 WHERE val = 1").unwrap();
    assert_eq!(qr.rows[0][0], Value::Integer(100));
}

#[test]
fn stress_correlated_delete() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();

    assert_ok(
        conn.execute("CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, val INTEGER)")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE t2 (id INTEGER NOT NULL PRIMARY KEY, ref_id INTEGER)")
            .unwrap(),
    );

    conn.execute("BEGIN").unwrap();
    for i in 0..200 {
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO t1 VALUES ({i}, {i})"))
                .unwrap(),
            1,
        );
    }
    for i in (0..200).step_by(3) {
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO t2 VALUES ({i}, {i})"))
                .unwrap(),
            1,
        );
    }
    conn.execute("COMMIT").unwrap();

    assert_rows_affected(
        conn.execute("DELETE FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.ref_id = t1.id)")
            .unwrap(),
        133,
    );

    let qr = conn.query("SELECT COUNT(*) FROM t1").unwrap();
    assert_eq!(qr.rows[0][0], Value::Integer(67));
}

#[test]
fn stress_chained_exists_and_not_exists() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();

    assert_ok(
        conn.execute("CREATE TABLE users (id INTEGER NOT NULL PRIMARY KEY, dept TEXT)")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE orders (id INTEGER NOT NULL PRIMARY KEY, user_id INTEGER)")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE reviews (id INTEGER NOT NULL PRIMARY KEY, user_id INTEGER)")
            .unwrap(),
    );

    conn.execute("BEGIN").unwrap();
    for i in 0..100 {
        assert_rows_affected(
            conn.execute(&format!(
                "INSERT INTO users VALUES ({i}, 'dept{}' )",
                i % 10
            ))
            .unwrap(),
            1,
        );
    }
    for i in (0..100).step_by(2) {
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO orders VALUES ({i}, {i})"))
                .unwrap(),
            1,
        );
    }
    for i in (0..100).step_by(3) {
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO reviews VALUES ({i}, {i})"))
                .unwrap(),
            1,
        );
    }
    conn.execute("COMMIT").unwrap();

    // Users with orders but no reviews
    let qr = conn.query(
        "SELECT COUNT(*) FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id) AND NOT EXISTS (SELECT 1 FROM reviews WHERE reviews.user_id = users.id)"
    ).unwrap();
    // Even ids with orders = 50; even AND multiple-of-6 (reviewed) = 17; 50 - 17 = 33.
    assert_eq!(qr.rows[0][0], Value::Integer(33));
}

#[test]
fn stress_scalar_select_500_rows() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();

    assert_ok(
        conn.execute("CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, cat INTEGER)")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE t2 (id INTEGER NOT NULL PRIMARY KEY, cat INTEGER, val INTEGER)")
            .unwrap(),
    );

    conn.execute("BEGIN").unwrap();
    for i in 0..500 {
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO t1 VALUES ({i}, {})", i % 50))
                .unwrap(),
            1,
        );
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO t2 VALUES ({i}, {}, {})", i % 50, i))
                .unwrap(),
            1,
        );
    }
    conn.execute("COMMIT").unwrap();

    let qr = conn.query(
        "SELECT id, (SELECT MAX(val) FROM t2 WHERE t2.cat = t1.cat) AS max_val FROM t1 WHERE id < 5 ORDER BY id"
    ).unwrap();
    assert_eq!(qr.rows.len(), 5);
    // cat 0: max val among ids 0,50,100,...450 → 450
    assert_eq!(qr.rows[0][1], Value::Integer(450));
}

#[test]
fn null_both_sides() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();

    assert_ok(
        conn.execute("CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, val INTEGER)")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE t2 (id INTEGER NOT NULL PRIMARY KEY, val INTEGER)")
            .unwrap(),
    );
    assert_rows_affected(conn.execute("INSERT INTO t1 VALUES (1, NULL)").unwrap(), 1);
    assert_rows_affected(conn.execute("INSERT INTO t2 VALUES (1, NULL)").unwrap(), 1);

    // NULL = NULL is false in SQL → EXISTS returns false
    let qr = conn
        .query("SELECT id FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.val = t1.val)")
        .unwrap();
    assert_eq!(qr.rows.len(), 0);

    // NOT EXISTS with NULL → no match → passes
    let qr = conn
        .query("SELECT id FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.val = t1.val)")
        .unwrap();
    assert_eq!(qr.rows.len(), 1);
}

#[test]
fn null_scalar_returns_null() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();

    assert_ok(
        conn.execute("CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, cat INTEGER)")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE t2 (id INTEGER NOT NULL PRIMARY KEY, cat INTEGER, val INTEGER)")
            .unwrap(),
    );
    assert_rows_affected(conn.execute("INSERT INTO t1 VALUES (1, 99)").unwrap(), 1);
    assert_rows_affected(
        conn.execute("INSERT INTO t2 VALUES (1, 1, 100)").unwrap(),
        1,
    );

    // cat=99 has no match in t2 → scalar returns NULL
    let qr = conn
        .query("SELECT id, (SELECT MAX(val) FROM t2 WHERE t2.cat = t1.cat) AS mx FROM t1")
        .unwrap();
    assert!(qr.rows[0][1].is_null());
}

#[test]
fn correlated_after_reopen() {
    let dir = tempfile::tempdir().unwrap();
    {
        let db = create_db(dir.path());
        let conn = Connection::open(&db).unwrap();
        assert_ok(
            conn.execute("CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, ref_id INTEGER)")
                .unwrap(),
        );
        assert_ok(
            conn.execute("CREATE TABLE t2 (id INTEGER NOT NULL PRIMARY KEY)")
                .unwrap(),
        );
        assert_rows_affected(conn.execute("INSERT INTO t1 VALUES (1, 10)").unwrap(), 1);
        assert_rows_affected(conn.execute("INSERT INTO t1 VALUES (2, 20)").unwrap(), 1);
        assert_rows_affected(conn.execute("INSERT INTO t2 VALUES (10)").unwrap(), 1);
    }
    {
        let db = open_db(dir.path());
        let conn = Connection::open(&db).unwrap();
        let qr = conn.query(
            "SELECT id FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.ref_id) ORDER BY id"
        ).unwrap();
        assert_eq!(qr.rows.len(), 1);
        assert_eq!(qr.rows[0][0], Value::Integer(1));
    }
}

#[test]
fn correlated_with_window_function() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();

    assert_ok(
        conn.execute(
            "CREATE TABLE emp (id INTEGER NOT NULL PRIMARY KEY, dept TEXT, salary INTEGER)",
        )
        .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE active_depts (id INTEGER NOT NULL PRIMARY KEY, dept TEXT)")
            .unwrap(),
    );
    for &(id, dept, sal) in &[
        (1, "eng", 100),
        (2, "eng", 80),
        (3, "sales", 90),
        (4, "hr", 70),
    ] {
        assert_rows_affected(
            conn.execute(&format!("INSERT INTO emp VALUES ({id}, '{dept}', {sal})"))
                .unwrap(),
            1,
        );
    }
    assert_rows_affected(
        conn.execute("INSERT INTO active_depts VALUES (1, 'eng')")
            .unwrap(),
        1,
    );
    assert_rows_affected(
        conn.execute("INSERT INTO active_depts VALUES (2, 'sales')")
            .unwrap(),
        1,
    );

    let qr = conn.query(
        "SELECT name_col, rn FROM (SELECT id, dept, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM emp WHERE EXISTS (SELECT 1 FROM active_depts WHERE active_depts.dept = emp.dept)) sub ORDER BY rn"
    );
    // This might not work due to subquery-in-FROM limitation, which is fine
    // Just verify it doesn't crash
    let _ = qr;
}

#[test]
fn correlated_insert_select_dedup() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();

    assert_ok(
        conn.execute("CREATE TABLE src (id INTEGER NOT NULL PRIMARY KEY, val TEXT)")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE dst (id INTEGER NOT NULL PRIMARY KEY, val TEXT)")
            .unwrap(),
    );

    assert_rows_affected(conn.execute("INSERT INTO src VALUES (1, 'a')").unwrap(), 1);
    assert_rows_affected(conn.execute("INSERT INTO src VALUES (2, 'b')").unwrap(), 1);
    assert_rows_affected(conn.execute("INSERT INTO src VALUES (3, 'c')").unwrap(), 1);
    assert_rows_affected(
        conn.execute("INSERT INTO dst VALUES (2, 'existing')")
            .unwrap(),
        1,
    );

    // Insert only rows not already in dst
    assert_rows_affected(conn.execute(
        "INSERT INTO dst SELECT id, val FROM src WHERE NOT EXISTS (SELECT 1 FROM dst WHERE dst.id = src.id)"
    ).unwrap(), 2);

    let qr = conn.query("SELECT COUNT(*) FROM dst").unwrap();
    assert_eq!(qr.rows[0][0], Value::Integer(3));
}

#[test]
fn correlated_with_view_outer_and_inner() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();

    assert_ok(
        conn.execute("CREATE TABLE users (id INTEGER NOT NULL PRIMARY KEY, name TEXT)")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE TABLE orders (id INTEGER NOT NULL PRIMARY KEY, user_id INTEGER)")
            .unwrap(),
    );
    assert_rows_affected(
        conn.execute("INSERT INTO users VALUES (1, 'Alice')")
            .unwrap(),
        1,
    );
    assert_rows_affected(
        conn.execute("INSERT INTO users VALUES (2, 'Bob')").unwrap(),
        1,
    );
    assert_rows_affected(conn.execute("INSERT INTO orders VALUES (1, 1)").unwrap(), 1);

    assert_ok(
        conn.execute("CREATE VIEW v_users AS SELECT * FROM users WHERE id > 0")
            .unwrap(),
    );
    assert_ok(
        conn.execute("CREATE VIEW v_orders AS SELECT * FROM orders WHERE id > 0")
            .unwrap(),
    );

    // View as outer, view as inner
    let qr = conn.query(
        "SELECT name FROM v_users WHERE EXISTS (SELECT 1 FROM v_orders WHERE v_orders.user_id = v_users.id) ORDER BY name"
    ).unwrap();
    assert_eq!(qr.rows.len(), 1);
    assert_eq!(qr.rows[0][0], Value::Text("Alice".into()));
}
