iridium_core 0.1.12

use iridium_core::{parse_sql, Engine, QueryResult};

fn exec(engine: &mut Engine, sql: &str) {
    let stmt = parse_sql(sql).expect("parse failed");
    engine.execute(stmt).expect("execute failed");
}

fn query(engine: &mut Engine, sql: &str) -> QueryResult {
    let stmt = parse_sql(sql).expect("parse failed");
    engine
        .execute(stmt)
        .expect("execute failed")
        .expect("expected result")
}

fn val(r: &QueryResult, row: usize, col: usize) -> String {
    r.rows[row][col].to_string_value()
}

fn is_null(r: &QueryResult, row: usize, col: usize) -> bool {
    r.rows[row][col].is_null()
}

// ─── SCHEMATA ──────────────────────────────────────────────────────────

#[test]
fn test_schemata_default() {
    let mut e = Engine::new();
    let r = query(
        &mut e,
        "SELECT CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER FROM INFORMATION_SCHEMA.SCHEMATA",
    );
    assert!(!r.rows.is_empty());
    assert_eq!(val(&r, 0, 0), "iridium_sql");
    assert_eq!(val(&r, 0, 1), "dbo");
    assert_eq!(val(&r, 0, 2), "dbo");
}

#[test]
fn test_schemata_with_custom_schema() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE SCHEMA sales");
    let r = query(
        &mut e,
        "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'sales'",
    );
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "sales");
}

// ─── TABLES ────────────────────────────────────────────────────────────

#[test]
fn test_tables_has_catalog() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT)");
    let r = query(&mut e, "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "iridium_sql");
    assert_eq!(val(&r, 0, 1), "dbo");
    assert_eq!(val(&r, 0, 2), "t1");
    assert_eq!(val(&r, 0, 3), "BASE TABLE");
}

#[test]
fn test_tables_includes_views() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT)");
    exec(&mut e, "CREATE VIEW v1 AS SELECT id FROM t1");
    let r = query(
        &mut e,
        "SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME",
    );
    assert_eq!(r.rows.len(), 2);
    assert_eq!(val(&r, 1, 0), "v1");
    assert_eq!(val(&r, 1, 1), "VIEW");
}

// ─── COLUMNS ───────────────────────────────────────────────────────────

#[test]
fn test_columns_full_columns() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (id INT NOT NULL, name VARCHAR(50) NULL, amount DECIMAL(10,2))",
    );
    let r = query(&mut e, "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1' ORDER BY ORDINAL_POSITION");
    assert_eq!(r.rows.len(), 3);
    // id
    assert_eq!(val(&r, 0, 0), "iridium_sql");
    assert_eq!(val(&r, 0, 3), "id");
    assert_eq!(val(&r, 0, 4), "1");
    assert_eq!(val(&r, 0, 5), "NO");
    assert_eq!(val(&r, 0, 6), "int");
    // name
    assert_eq!(val(&r, 1, 3), "name");
    assert_eq!(val(&r, 1, 5), "YES");
    assert_eq!(val(&r, 1, 6), "varchar");
    // amount
    assert_eq!(val(&r, 2, 3), "amount");
    assert_eq!(val(&r, 2, 6), "decimal");
}

#[test]
fn test_columns_char_max_length() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (a VARCHAR(100), b NVARCHAR(50), c INT)",
    );
    let r = query(&mut e, "SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1' ORDER BY ORDINAL_POSITION");
    assert_eq!(val(&r, 0, 1), "100"); // VARCHAR(100) char max len
    assert_eq!(val(&r, 0, 2), "100"); // VARCHAR(100) octet len
    assert_eq!(val(&r, 1, 1), "50"); // NVARCHAR(50) char max len
    assert_eq!(val(&r, 1, 2), "100"); // NVARCHAR(50) octet len = 50*2
    assert!(is_null(&r, 2, 1)); // INT has no char max length
}

#[test]
fn test_columns_numeric_precision() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (a INT, b DECIMAL(8,3), c FLOAT, d VARCHAR(10))",
    );
    let r = query(&mut e, "SELECT COLUMN_NAME, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1' ORDER BY ORDINAL_POSITION");
    // INT
    assert_eq!(val(&r, 0, 1), "10"); // precision
    assert_eq!(val(&r, 0, 2), "10"); // radix
    assert_eq!(val(&r, 0, 3), "0"); // scale
                                    // DECIMAL(8,3)
    assert_eq!(val(&r, 1, 1), "8");
    assert_eq!(val(&r, 1, 2), "10");
    assert_eq!(val(&r, 1, 3), "3");
    // FLOAT
    assert_eq!(val(&r, 2, 1), "53");
    assert_eq!(val(&r, 2, 2), "2"); // binary radix
    assert!(is_null(&r, 2, 3)); // Float has no scale
                                // VARCHAR
    assert!(is_null(&r, 3, 1));
}

#[test]
fn test_columns_datetime_precision() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (a DATE, b DATETIME, c DATETIME2, d TIME)",
    );
    let r = query(&mut e, "SELECT COLUMN_NAME, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1' ORDER BY ORDINAL_POSITION");
    assert_eq!(val(&r, 0, 1), "0"); // DATE
    assert_eq!(val(&r, 1, 1), "3"); // DATETIME
    assert_eq!(val(&r, 2, 1), "7"); // DATETIME2
    assert_eq!(val(&r, 3, 1), "7"); // TIME
}

#[test]
fn test_columns_collation() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (a VARCHAR(10), b INT)");
    let r = query(&mut e, "SELECT COLUMN_NAME, COLLATION_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1' ORDER BY ORDINAL_POSITION");
    assert_eq!(val(&r, 0, 1), "SQL_Latin1_General_CP1_CI_AS");
    assert_eq!(val(&r, 0, 2), "iso_1");
    assert!(is_null(&r, 1, 1)); // INT has no collation
    assert!(is_null(&r, 1, 2));
}

// ─── VIEWS ─────────────────────────────────────────────────────────────

#[test]
fn test_views() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT)");
    exec(&mut e, "CREATE VIEW v1 AS SELECT id FROM t1");
    let r = query(&mut e, "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CHECK_OPTION, IS_UPDATABLE FROM INFORMATION_SCHEMA.VIEWS");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "iridium_sql");
    assert_eq!(val(&r, 0, 2), "v1");
    assert_eq!(val(&r, 0, 3), "NONE");
    assert_eq!(val(&r, 0, 4), "NO");
}

// ─── ROUTINES ──────────────────────────────────────────────────────────

#[test]
fn test_routines_procedure() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE PROCEDURE dbo.sp_test AS SELECT 1");
    let r = query(&mut e, "SELECT SPECIFIC_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_BODY, IS_DETERMINISTIC, SQL_DATA_ACCESS, SCHEMA_LEVEL_ROUTINE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_test'");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "iridium_sql");
    assert_eq!(val(&r, 0, 1), "dbo");
    assert_eq!(val(&r, 0, 2), "sp_test");
    assert_eq!(val(&r, 0, 3), "PROCEDURE");
    assert_eq!(val(&r, 0, 4), "SQL");
    assert_eq!(val(&r, 0, 5), "NO");
    assert_eq!(val(&r, 0, 6), "MODIFIES");
    assert_eq!(val(&r, 0, 7), "YES");
}

#[test]
fn test_routines_function() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE FUNCTION dbo.fn_double(@x INT) RETURNS INT AS BEGIN RETURN @x * 2 END",
    );
    let r = query(&mut e, "SELECT ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, SQL_DATA_ACCESS FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'fn_double'");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 1), "FUNCTION");
    assert_eq!(val(&r, 0, 2), "int");
    assert_eq!(val(&r, 0, 3), "READS");
}

#[test]
fn test_routine_columns_inline_tvf() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (v INT)");
    exec(
        &mut e,
        "
        CREATE FUNCTION dbo.gt(@min INT) RETURNS TABLE AS
        RETURN (SELECT v FROM t1 WHERE v > @min)
    ",
    );
    let r = query(
        &mut e,
        "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS WHERE TABLE_NAME = 'gt' ORDER BY ORDINAL_POSITION",
    );
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "dbo");
    assert_eq!(val(&r, 0, 1), "gt");
    assert_eq!(val(&r, 0, 2), "v");
    assert_eq!(val(&r, 0, 3), "1");
    assert_eq!(val(&r, 0, 4), "int");
}

// ─── PARAMETERS ────────────────────────────────────────────────────────

#[test]
fn test_parameters() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE PROCEDURE dbo.sp_add @a INT, @b INT AS SELECT @a",
    );
    let r = query(&mut e, "SELECT SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'sp_add' ORDER BY ORDINAL_POSITION");
    assert_eq!(r.rows.len(), 2);
    assert_eq!(val(&r, 0, 0), "sp_add");
    assert_eq!(val(&r, 0, 1), "1");
    assert_eq!(val(&r, 0, 2), "IN");
    assert_eq!(val(&r, 0, 3), "@a");
    assert_eq!(val(&r, 0, 4), "int");
    assert_eq!(val(&r, 1, 3), "@b");
    assert_eq!(val(&r, 1, 4), "int");
}

// ─── TABLE_CONSTRAINTS ────────────────────────────────────────────────

#[test]
fn test_table_constraints_pk() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50))",
    );
    let r = query(&mut e, "SELECT CONSTRAINT_CATALOG, CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE, IS_DEFERRABLE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 't1' AND CONSTRAINT_TYPE = 'PRIMARY KEY'");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "iridium_sql");
    assert_eq!(val(&r, 0, 1), "PK_t1");
    assert_eq!(val(&r, 0, 3), "PRIMARY KEY");
    assert_eq!(val(&r, 0, 4), "NO");
}

#[test]
fn test_table_constraints_check() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (id INT, age INT, CONSTRAINT CK_age CHECK (age > 0))",
    );
    let r = query(&mut e, "SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 't1' AND CONSTRAINT_TYPE = 'CHECK'");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "CK_age");
}

#[test]
fn test_table_constraints_fk() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE parent (id INT PRIMARY KEY)");
    exec(&mut e, "CREATE TABLE child (id INT, parent_id INT, CONSTRAINT FK_child_parent FOREIGN KEY (parent_id) REFERENCES parent(id))");
    let r = query(&mut e, "SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'child' AND CONSTRAINT_TYPE = 'FOREIGN KEY'");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "FK_child_parent");
}

// ─── CHECK_CONSTRAINTS ────────────────────────────────────────────────

#[test]
fn test_check_constraints() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (id INT, age INT, CONSTRAINT CK_age CHECK (age > 0))",
    );
    let r = query(&mut e, "SELECT CONSTRAINT_CATALOG, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME = 'CK_age'");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "iridium_sql");
    assert_eq!(val(&r, 0, 1), "CK_age");
}

// ─── REFERENTIAL_CONSTRAINTS ──────────────────────────────────────────

#[test]
fn test_referential_constraints() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE parent (id INT PRIMARY KEY)");
    exec(&mut e, "CREATE TABLE child (id INT, pid INT, CONSTRAINT FK_cp FOREIGN KEY (pid) REFERENCES parent(id))");
    let r = query(&mut e, "SELECT CONSTRAINT_NAME, MATCH_OPTION, UPDATE_RULE, DELETE_RULE FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_cp'");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 1), "SIMPLE");
    assert_eq!(val(&r, 0, 2), "NO ACTION");
    assert_eq!(val(&r, 0, 3), "NO ACTION");
}

// ─── KEY_COLUMN_USAGE ─────────────────────────────────────────────────

#[test]
fn test_key_column_usage_pk() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50))",
    );
    let r = query(&mut e, "SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 't1'");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "PK_t1");
    assert_eq!(val(&r, 0, 2), "id");
    assert_eq!(val(&r, 0, 3), "1");
}

// ─── CONSTRAINT_TABLE_USAGE ───────────────────────────────────────────

#[test]
fn test_constraint_table_usage() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (id INT PRIMARY KEY, age INT, CONSTRAINT CK_age CHECK (age > 0))",
    );
    let r = query(&mut e, "SELECT TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE TABLE_NAME = 't1' ORDER BY CONSTRAINT_NAME");
    assert!(r.rows.len() >= 2); // PK + CHECK
}

// ─── CONSTRAINT_COLUMN_USAGE ──────────────────────────────────────────

#[test]
fn test_constraint_column_usage() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT PRIMARY KEY)");
    let r = query(&mut e, "SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 't1'");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 1), "id");
    assert_eq!(val(&r, 0, 2), "PK_t1");
}

#[test]
fn test_view_table_usage() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT, v INT)");
    exec(&mut e, "CREATE VIEW v1 AS SELECT id FROM t1 WHERE v > 0");
    let r = query(&mut e, "SELECT VIEW_SCHEMA, VIEW_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE WHERE VIEW_NAME = 'v1'");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "dbo");
    assert_eq!(val(&r, 0, 1), "v1");
    assert_eq!(val(&r, 0, 2), "t1");
}

#[test]
fn test_view_column_usage() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE customers (CustomerId INT, FirstName VARCHAR(50), LastName VARCHAR(50))",
    );
    exec(
        &mut e,
        "CREATE TABLE orders (OrderId INT, CustomerId INT, TotalAmount DECIMAL(18,2))",
    );
    exec(
        &mut e,
        "
        CREATE VIEW v_customer_orders AS
        SELECT
            c.CustomerId,
            c.FirstName,
            c.LastName,
            COUNT(o.OrderId) AS TotalOrders,
            CAST(COALESCE(SUM(o.TotalAmount), 0) AS DECIMAL(18,2)) AS TotalSpent
        FROM customers c
        LEFT JOIN orders o ON c.CustomerId = o.CustomerId
        GROUP BY c.CustomerId, c.FirstName, c.LastName
    ",
    );
    let r = query(&mut e, "SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE VIEW_NAME = 'v_customer_orders' ORDER BY TABLE_NAME, COLUMN_NAME");
    let got: std::collections::HashSet<(String, String)> = r
        .rows
        .iter()
        .map(|row| (row[0].to_string_value(), row[1].to_string_value()))
        .collect();
    let expected = [
        ("customers", "CustomerId"),
        ("customers", "FirstName"),
        ("customers", "LastName"),
        ("orders", "CustomerId"),
        ("orders", "OrderId"),
        ("orders", "TotalAmount"),
    ];
    assert_eq!(got.len(), expected.len());
    for pair in expected {
        assert!(
            got.contains(&(pair.0.to_string(), pair.1.to_string())),
            "missing {:?}",
            pair
        );
    }
}

// ─── Empty views (should return 0 rows but not error) ─────────────────

#[test]
fn test_empty_views_queryable() {
    let mut e = Engine::new();
    for view in &["COLUMN_DOMAIN_USAGE", "DOMAINS", "DOMAIN_CONSTRAINTS"] {
        let sql = format!("SELECT * FROM INFORMATION_SCHEMA.{}", view);
        let r = query(&mut e, &sql);
        assert_eq!(r.rows.len(), 0, "{} should return 0 rows", view);
    }
}

// ─── sys.view_columns ───────────────────────────────────────────────

#[test]
fn test_sys_view_columns() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT, name VARCHAR(50))");
    exec(&mut e, "INSERT INTO t1 VALUES (1, 'test')");
    exec(
        &mut e,
        "CREATE VIEW v1 AS SELECT id, name FROM t1 WHERE id > 0",
    );

    let r = query(
        &mut e,
        "SELECT object_id, column_id, name FROM sys.view_columns WHERE object_id = OBJECT_ID('dbo.v1') ORDER BY column_id",
    );
    assert_eq!(r.rows.len(), 2, "v1 should have 2 columns");
    assert_eq!(val(&r, 0, 2), "id");
    assert_eq!(val(&r, 1, 2), "name");
}

#[test]
fn test_sys_view_columns_join_with_alias() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT, name VARCHAR(50))");
    exec(&mut e, "INSERT INTO t1 VALUES (1, 'test')");
    exec(
        &mut e,
        "CREATE VIEW v1 AS SELECT id, name FROM t1 WHERE id > 0",
    );

    // Test JOIN between sys.views and sys.view_columns with aliases
    let r = query(
        &mut e,
        "SELECT v.name AS view_name, c.name AS column_name FROM sys.views v INNER JOIN sys.view_columns c ON v.object_id = c.object_id WHERE v.name = 'v1' ORDER BY c.column_id",
    );
    assert_eq!(r.rows.len(), 2, "v1 should have 2 columns");
    assert_eq!(val(&r, 0, 1), "id");
    assert_eq!(val(&r, 1, 1), "name");
}

#[test]
fn test_sys_index_columns_with_alias() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50))",
    );

    // Test alias for sys.index_columns - using correct column names
    let r = query(
        &mut e,
        "SELECT ic.column_id, ic.object_id FROM sys.index_columns ic WHERE ic.object_id = OBJECT_ID('dbo.t1')",
    );
    assert!(r.rows.len() >= 1, "t1 should have at least 1 index column");
}

#[test]
fn test_sys_index_columns_no_alias() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50))",
    );

    // Test without alias - this should work
    let r = query(
        &mut e,
        "SELECT object_id FROM sys.index_columns WHERE object_id = OBJECT_ID('dbo.t1')",
    );
    assert!(r.rows.len() >= 1, "t1 should have at least 1 index column");
}

#[test]
fn test_sys_indexes_with_alias_where() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50))",
    );

    let r = query(
        &mut e,
        "SELECT i.name, i.object_id FROM sys.indexes AS i WHERE i.object_id = OBJECT_ID('dbo.t1')",
    );
    assert!(r.rows.len() >= 1, "t1 should have at least 1 index");
}

// ─── sys.all_objects ──────────────────────────────────────────────────────

#[test]
fn test_sys_all_objects_returns_user_tables() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE foo (id INT PRIMARY KEY)");
    let r = query(
        &mut e,
        "SELECT name, type, type_desc FROM sys.all_objects WHERE type = 'U ' ORDER BY name",
    );
    assert!(!r.rows.is_empty());
    assert_eq!(val(&r, 0, 0), "foo");
    assert_eq!(val(&r, 0, 1), "U ");
    assert_eq!(val(&r, 0, 2), "USER_TABLE");
}

// ─── sys.identity_columns ──────────────────────────────────────────────

#[test]
fn test_sys_identity_columns() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE ident_test (id INT IDENTITY(1,1) PRIMARY KEY, name VARCHAR(50))",
    );
    let r = query(
        &mut e,
        "SELECT name, seed_value, increment_value FROM sys.identity_columns",
    );
    assert!(!r.rows.is_empty(), "identity_columns should have rows");
    assert_eq!(val(&r, 0, 0), "id");
    assert_eq!(val(&r, 0, 1), "1");
    assert_eq!(val(&r, 0, 2), "1");
}

#[test]
fn test_sys_identity_columns_no_identity() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE no_ident (id INT, name VARCHAR(50))");
    let r = query(&mut e, "SELECT name FROM sys.identity_columns");
    assert!(r.rows.is_empty(), "no identity columns expected");
}

// ─── sys.computed_columns ──────────────────────────────────────────────

#[test]
fn test_sys_computed_columns_empty() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE comp_test (id INT PRIMARY KEY, val INT)",
    );
    let r = query(&mut e, "SELECT name FROM sys.computed_columns");
    assert!(r.rows.is_empty(), "no computed columns expected");
}

// ─── sys.sql_expression_dependencies ──────────────────────────────────

#[test]
fn test_sys_sql_expression_dependencies_empty() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE dep_test (id INT PRIMARY KEY)");
    let r = query(
        &mut e,
        "SELECT referencing_id, referenced_id FROM sys.sql_expression_dependencies",
    );
    assert!(r.rows.is_empty(), "no dependencies expected yet");
}

// ─── sys.partition_functions column shape ──────────────────────────────

#[test]
fn test_sys_partition_functions_has_type_desc() {
    let mut e = Engine::new();
    let r = query(
        &mut e,
        "SELECT name, function_id, type, type_desc, fanout, boundary_value_on_right, create_date FROM sys.partition_functions",
    );
    assert!(r.rows.is_empty(), "no partition functions expected");
    assert!(r
        .columns
        .iter()
        .any(|c| c.eq_ignore_ascii_case("type_desc")));
    assert!(r
        .columns
        .iter()
        .any(|c| c.eq_ignore_ascii_case("create_date")));
}

// ─── sys.partition_schemes column shape ────────────────────────────────

#[test]
fn test_sys_partition_schemes_has_type_desc() {
    let mut e = Engine::new();
    let r = query(
        &mut e,
        "SELECT name, data_space_id, function_id, type, type_desc FROM sys.partition_schemes",
    );
    assert!(r.rows.is_empty(), "no partition schemes expected");
    assert!(r
        .columns
        .iter()
        .any(|c| c.eq_ignore_ascii_case("type_desc")));
}

// ─── sys.sql_modules ──────────────────────────────────────────────────

#[test]
fn test_sys_sql_modules_routine() {
    let mut e = Engine::new();
    let sql = "CREATE PROCEDURE dbo.sp_test AS SELECT 1";
    exec(&mut e, sql);
    let r = query(
        &mut e,
        "SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.sp_test')",
    );
    assert_eq!(r.rows.len(), 1);
    assert!(val(&r, 0, 0).to_lowercase().contains("sp_test"));
}

#[test]
fn test_sys_sql_modules_view() {
    let mut e = Engine::new();
    let sql = "CREATE VIEW dbo.v_test AS SELECT 1 AS x";
    exec(&mut e, sql);
    let r = query(
        &mut e,
        "SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.v_test')",
    );
    assert_eq!(r.rows.len(), 1);
    assert!(val(&r, 0, 0).to_lowercase().contains("v_test"));
}

#[test]
fn test_sys_sql_modules_trigger() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT)");
    let sql = "CREATE TRIGGER tr_test ON t1 FOR INSERT AS PRINT 'inserted'";
    exec(&mut e, sql);
    let r = query(
        &mut e,
        "SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('tr_test')",
    );
    assert_eq!(r.rows.len(), 1);
    assert!(val(&r, 0, 0).to_lowercase().contains("tr_test"));
}

// ─── sys.foreign_key_columns ──────────────────────────────────────────

#[test]
fn test_sys_foreign_key_columns() {
    let mut e = Engine::new();
    exec(
        &mut e,
        "CREATE TABLE p (id1 INT, id2 INT, PRIMARY KEY (id1, id2))",
    );
    exec(&mut e, "CREATE TABLE c (c1 INT, c2 INT, CONSTRAINT fk_c_p FOREIGN KEY (c1, c2) REFERENCES p (id1, id2))");
    let r = query(
        &mut e,
        "SELECT constraint_object_id, constraint_column_id, parent_object_id, referenced_object_id
         FROM sys.foreign_key_columns
         WHERE constraint_object_id = (SELECT object_id FROM sys.foreign_keys WHERE name = 'fk_c_p')
         ORDER BY constraint_column_id",
    );
    assert_eq!(r.rows.len(), 2);
    assert_eq!(val(&r, 0, 1), "1");
    assert_eq!(val(&r, 1, 1), "2");

    let pid = query(&mut e, "SELECT OBJECT_ID('dbo.c')").rows[0][0].to_string_value();
    let rid = query(&mut e, "SELECT OBJECT_ID('dbo.p')").rows[0][0].to_string_value();

    assert_eq!(val(&r, 0, 2), pid);
    assert_eq!(val(&r, 0, 3), rid);
}

// ─── sys.trigger_events ───────────────────────────────────────────────

#[test]
fn test_sys_trigger_events() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT)");
    exec(
        &mut e,
        "CREATE TRIGGER tr_multi ON t1 FOR INSERT, UPDATE AS PRINT 'hi'",
    );
    let r = query(
        &mut e,
        "SELECT type, type_desc FROM sys.trigger_events WHERE object_id = OBJECT_ID('tr_multi') ORDER BY type",
    );
    assert_eq!(r.rows.len(), 2);
    assert_eq!(val(&r, 0, 0), "1");
    assert_eq!(val(&r, 0, 1), "INSERT");
    assert_eq!(val(&r, 1, 0), "2");
    assert_eq!(val(&r, 1, 1), "UPDATE");
}

// ─── sys.partitions ───────────────────────────────────────────────────

#[test]
fn test_sys_partitions() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT PRIMARY KEY)");
    let r = query(
        &mut e,
        "SELECT object_id, index_id, partition_number FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.t1')",
    );
    assert!(r.rows.len() >= 1);
    assert_eq!(val(&r, 0, 2), "1");
}

// ─── sys.allocation_units ───────────────────────────────────────────────

#[test]
fn test_sys_allocation_units() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT PRIMARY KEY)");
    let r = query(
        &mut e,
        "SELECT type_desc, container_id FROM sys.allocation_units
         WHERE container_id IN (SELECT partition_id FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.t1'))",
    );
    assert!(r.rows.len() >= 1);
    assert_eq!(val(&r, 0, 0), "IN_ROW_DATA");
}

// ─── sys.stats_columns ────────────────────────────────────────────────

#[test]
fn test_sys_stats_columns() {
    let mut e = Engine::new();
    exec(&mut e, "CREATE TABLE t1 (id INT PRIMARY KEY)");
    let r = query(
        &mut e,
        "SELECT object_id, stats_id, stats_column_id, column_id FROM sys.stats_columns
         WHERE stats_id = (SELECT stats_id FROM sys.stats WHERE object_id = OBJECT_ID('dbo.t1'))",
    );
    assert!(r.rows.len() >= 1);
    assert_eq!(val(&r, 0, 2), "1");
}

// ─── sys.database_files ───────────────────────────────────────────────

#[test]
fn test_sys_database_files() {
    let mut e = Engine::new();
    let r = query(&mut e, "SELECT name, type_desc FROM sys.database_files");
    assert_eq!(r.rows.len(), 1);
    assert_eq!(val(&r, 0, 0), "iridium_sql");
    assert_eq!(val(&r, 0, 1), "ROWS");
}