Invercargill-Sql/src/tests/orm-test.vala

using Invercargill.DataStructures;
using Invercargill.Expressions;
using InvercargillSql;
using InvercargillSql.Orm;
using InvercargillSql.Dialects;
using InvercargillSql.Expressions;

/**
 * Test entity for ORM tests.
 */
public class TestUser : Object {
    public int64 id { get; set; }
    public string name { get; set; }
    public string email { get; set; }
    public int64 age { get; set; }  // Use int64 for SQLite compatibility
    public double? salary { get; set; }  // Nullable for database compatibility
    public bool? is_active { get; set; }  // Nullable for database compatibility
    public DateTime? created_at { get; set; }  // Nullable for database compatibility
    
    public TestUser() {
        name = "";
        email = "";
    }
}

/**
 * Test entity with binary data.
 */
public class TestDocument : Object {
    public int64 id { get; set; }
    public string filename { get; set; }
    public Invercargill.BinaryData? content { get; set; }
    
    public TestDocument() {
        filename = "";
    }
}

/**
 * Test entity for Product ORM tests.
 */
public class TestProduct : Object {
    public int64 id { get; set; }
    public string name { get; set; }
    public string category { get; set; }
    public double price { get; set; }
    public int64 stock { get; set; }
    
    public TestProduct() {
        name = "";
        category = "";
    }
}

/**
 * Test entity for Order ORM tests.
 */
public class TestOrder : Object {
    public int64 id { get; set; }
    public int64 user_id { get; set; }
    public int64 product_id { get; set; }
    public int64 quantity { get; set; }
    public double total { get; set; }
    public string status { get; set; }
    public DateTime? created_at { get; set; }
    
    public TestOrder() {
        status = "";
    }
}

/**
 * Comprehensive tests for Invercargill-Sql ORM.
 */
public int main(string[] args) {
    print("=== Invercargill-Sql ORM Tests ===\n\n");
    
    try {
        // ColumnType tests
        print("--- ColumnType Tests ---\n");
        test_column_type_from_gtype_int();
        test_column_type_from_gtype_int64();
        test_column_type_from_gtype_string();
        test_column_type_from_gtype_bool();
        test_column_type_from_gtype_double();
        test_column_type_from_gtype_float();
        test_column_type_from_gtype_datetime();
        test_column_type_from_gtype_binary();
        test_column_type_from_gtype_unsupported();
        
        // ColumnDefinition tests
        print("\n--- ColumnDefinition Tests ---\n");
        test_column_definition_defaults();
        test_column_definition_properties();
        
        // IndexDefinition tests
        print("\n--- IndexDefinition Tests ---\n");
        test_index_definition_defaults();
        test_index_definition_add_columns();
        
        // EntityMapperBuilder tests
        print("\n--- EntityMapperBuilder Tests ---\n");
        test_entity_mapper_builder_table_name();
        test_entity_mapper_builder_simple_column();
        test_entity_mapper_builder_chained_columns();
        
        // EntityMapper tests
        print("\n--- EntityMapper Tests ---\n");
        test_entity_mapper_build_for();
        test_entity_mapper_materialise();
        test_entity_mapper_map_from();
        
        // SqliteDialect tests
        print("\n--- SqliteDialect Tests ---\n");
        test_sqlite_dialect_translate_type_int32();
        test_sqlite_dialect_translate_type_int64();
        test_sqlite_dialect_translate_type_text();
        test_sqlite_dialect_translate_type_boolean();
        test_sqlite_dialect_translate_type_decimal();
        test_sqlite_dialect_translate_type_datetime();
        test_sqlite_dialect_translate_type_binary();
        test_sqlite_dialect_translate_type_uuid();
        test_sqlite_dialect_build_select_all();
        test_sqlite_dialect_build_select_by_id();
        test_sqlite_dialect_build_insert_sql();
        test_sqlite_dialect_build_update_sql();
        test_sqlite_dialect_build_delete_sql();
        
        // ExpressionToSqlVisitor tests
        print("\n--- ExpressionToSqlVisitor Tests ---\n");
        test_expression_visitor_binary_equal();
        test_expression_visitor_binary_not_equal();
        test_expression_visitor_binary_greater_than();
        test_expression_visitor_binary_greater_equal();
        test_expression_visitor_binary_less_than();
        test_expression_visitor_binary_less_equal();
        test_expression_visitor_binary_and();
        test_expression_visitor_binary_or();
        test_expression_visitor_unary_not();
        test_expression_visitor_unary_negate();
        test_expression_visitor_literal();
        test_expression_visitor_property();
        test_expression_visitor_complex_nested();
        test_expression_visitor_arithmetic();
        
        // OrmSession integration tests
        print("\n--- OrmSession Integration Tests ---\n");
        test_orm_session_register();
        test_orm_session_register_with_schema();
        test_orm_session_create_query();
        test_orm_session_insert();
        test_orm_session_query_with_where();
        test_orm_session_update();
        test_orm_session_delete();
        test_orm_session_order_by();
        test_orm_session_limit_offset();
        test_orm_session_first();
        
        // Schema introspection tests
        print("\n--- Schema Introspection Tests ---\n");
        test_schema_introspection_basic();
        test_schema_introspection_with_register();
        
        // Primary key back-population tests
        print("\n--- Primary Key Back-Population Tests ---\n");
        test_insert_back_populates_primary_key();
        test_insert_back_populates_different_ids();
        test_insert_back_populates_product();
        test_insert_back_populates_order();
        
        print("\n=== All ORM tests passed! ===\n");
        return 0;
    } catch (Error e) {
        printerr("\n=== Test failed: %s ===\n", e.message);
        return 1;
    }
}

// ========================================
// ColumnType Tests
// ========================================

void test_column_type_from_gtype_int() throws Error {
    print("Test: ColumnType.from_gtype(int)... ");
    var result = ColumnType.from_gtype(typeof(int));
    assert(result == ColumnType.INT_32);
    print("PASSED\n");
}

void test_column_type_from_gtype_int64() throws Error {
    print("Test: ColumnType.from_gtype(int64)... ");
    var result = ColumnType.from_gtype(typeof(int64));
    assert(result == ColumnType.INT_64);
    print("PASSED\n");
}

void test_column_type_from_gtype_string() throws Error {
    print("Test: ColumnType.from_gtype(string)... ");
    var result = ColumnType.from_gtype(typeof(string));
    assert(result == ColumnType.TEXT);
    print("PASSED\n");
}

void test_column_type_from_gtype_bool() throws Error {
    print("Test: ColumnType.from_gtype(bool)... ");
    var result = ColumnType.from_gtype(typeof(bool));
    assert(result == ColumnType.BOOLEAN);
    print("PASSED\n");
}

void test_column_type_from_gtype_double() throws Error {
    print("Test: ColumnType.from_gtype(double)... ");
    var result = ColumnType.from_gtype(typeof(double));
    assert(result == ColumnType.DECIMAL);
    print("PASSED\n");
}

void test_column_type_from_gtype_float() throws Error {
    print("Test: ColumnType.from_gtype(float)... ");
    var result = ColumnType.from_gtype(typeof(float));
    assert(result == ColumnType.DECIMAL);
    print("PASSED\n");
}

void test_column_type_from_gtype_datetime() throws Error {
    print("Test: ColumnType.from_gtype(DateTime)... ");
    var result = ColumnType.from_gtype(typeof(DateTime));
    assert(result == ColumnType.DATETIME);
    print("PASSED\n");
}

void test_column_type_from_gtype_binary() throws Error {
    print("Test: ColumnType.from_gtype(BinaryData)... ");
    var result = ColumnType.from_gtype(typeof(Invercargill.BinaryData));
    assert(result == ColumnType.BINARY);
    print("PASSED\n");
}

void test_column_type_from_gtype_unsupported() throws Error {
    print("Test: ColumnType.from_gtype(unsupported)... ");
    var result = ColumnType.from_gtype(typeof(Object));
    assert(result == null);
    print("PASSED\n");
}

// ========================================
// ColumnDefinition Tests
// ========================================

void test_column_definition_defaults() throws Error {
    print("Test: ColumnDefinition defaults... ");
    var col = new ColumnDefinition();
    col.name = "test_col";
    col.column_type = ColumnType.TEXT;
    
    assert(col.name == "test_col");
    assert(col.column_type == ColumnType.TEXT);
    print("PASSED\n");
}

void test_column_definition_properties() throws Error {
    print("Test: ColumnDefinition properties... ");
    var col = new ColumnDefinition();
    col.name = "id";
    col.column_type = ColumnType.INT_64;
    
    assert(col.name == "id");
    assert(col.column_type == ColumnType.INT_64);
    print("PASSED\n");
}

// ========================================
// IndexDefinition Tests
// ========================================

void test_index_definition_defaults() throws Error {
    print("Test: IndexDefinition defaults... ");
    var idx = new IndexDefinition();
    idx.name = "idx_test";
    
    assert(idx.name == "idx_test");
    assert(idx.is_unique == false);
    assert(idx.columns != null);
    assert(idx.columns.count() == 0);
    print("PASSED\n");
}

void test_index_definition_add_columns() throws Error {
    print("Test: IndexDefinition add columns... ");
    var idx = new IndexDefinition();
    idx.name = "idx_multi";
    idx.is_unique = true;
    idx.columns.add("col1");
    idx.columns.add("col2");
    idx.columns.add("col3");
    
    assert(idx.name == "idx_multi");
    assert(idx.is_unique == true);
    assert(idx.columns.count() == 3);
    
    var arr = idx.columns.to_array();
    assert(arr[0] == "col1");
    assert(arr[1] == "col2");
    assert(arr[2] == "col3");
    print("PASSED\n");
}

// ========================================
// EntityMapperBuilder Tests
// ========================================

void test_entity_mapper_builder_table_name() throws Error {
    print("Test: EntityMapperBuilder table name... ");
    var mapper = EntityMapper.build_for<TestUser>(b => {
        b.table("users");
    });
    
    assert(mapper.table_name == "users");
    print("PASSED\n");
}

void test_entity_mapper_builder_simple_column() throws Error {
    print("Test: EntityMapperBuilder simple column... ");
    var mapper = EntityMapper.build_for<TestUser>(b => {
        b.table("users");
        b.column<int64?>("id", u => u.id, (u, v) => u.id = v);
        b.column<string>("name", u => u.name, (u, v) => u.name = v);
    });
    
    assert(mapper.columns.count() == 2);
    
    var cols = mapper.columns.to_array();
    assert(cols[0].name == "id");
    assert(cols[0].column_type == ColumnType.INT_64);
    assert(cols[1].name == "name");
    assert(cols[1].column_type == ColumnType.TEXT);
    print("PASSED\n");
}

void test_entity_mapper_builder_chained_columns() throws Error {
    print("Test: EntityMapperBuilder chained columns... ");
    // The new API allows natural chaining since column<T>() returns EntityMapperBuilder<T>
    var mapper = EntityMapper.build_for<TestUser>(b => {
        b.table("users")
            .column<int64?>("id", u => u.id, (u, v) => u.id = v)
            .column<string>("name", u => u.name, (u, v) => u.name = v)
            .column<string>("email", u => u.email, (u, v) => u.email = v);
    });
    
    assert(mapper.table_name == "users");
    assert(mapper.columns.count() == 3);
    
    var cols = mapper.columns.to_array();
    assert(cols[0].name == "id");
    assert(cols[1].name == "name");
    assert(cols[2].name == "email");
    print("PASSED\n");
}

// ========================================
// EntityMapper Tests
// ========================================

void test_entity_mapper_build_for() throws Error {
    print("Test: EntityMapper.build_for... ");
    var mapper = EntityMapper.build_for<TestUser>(b => {
        b.table("users");
        b.column<int64?>("id", u => u.id, (u, v) => u.id = v);
        b.column<string>("name", u => u.name, (u, v) => u.name = v);
    });
    
    assert(mapper != null);
    assert(mapper.table_name == "users");
    assert(mapper.columns.count() == 2);
    assert(mapper.property_mapper != null);
    print("PASSED\n");
}

void test_entity_mapper_materialise() throws Error {
    print("Test: EntityMapper.materialise... ");
    var mapper = EntityMapper.build_for<TestUser>(b => {
        b.table("users");
        b.column<int64?>("id", u => u.id, (u, v) => u.id = v);
        b.column<string>("name", u => u.name, (u, v) => u.name = v);
        b.column<string>("email", u => u.email, (u, v) => u.email = v);
    });
    
    var props = new PropertyDictionary();
    props.set_native<int64?>("id", 42);
    props.set_native<string>("name", "Alice");
    props.set_native<string>("email", "alice@example.com");
    
    var user = mapper.materialise(props);
    
    assert(user.id == 42);
    assert(user.name == "Alice");
    assert(user.email == "alice@example.com");
    print("PASSED\n");
}

void test_entity_mapper_map_from() throws Error {
    print("Test: EntityMapper.map_from... ");
    var mapper = EntityMapper.build_for<TestUser>(b => {
        b.table("users");
        b.column<int64?>("id", u => u.id, (u, v) => u.id = v);
        b.column<string>("name", u => u.name, (u, v) => u.name = v);
        b.column<string>("email", u => u.email, (u, v) => u.email = v);
    });
    
    var user = new TestUser();
    user.id = 123;
    user.name = "Bob";
    user.email = "bob@example.com";
    
    var props = mapper.map_from(user);
    
    assert(props != null);
    var id_elem = props.get("id");
    var name_elem = props.get("name");
    var email_elem = props.get("email");
    
    assert(id_elem != null);
    assert(name_elem != null);
    assert(email_elem != null);
    print("PASSED\n");
}

// ========================================
// SqliteDialect Tests
// ========================================

void test_sqlite_dialect_translate_type_int32() throws Error {
    print("Test: SqliteDialect.translate_type(INT_32)... ");
    var dialect = new SqliteDialect();
    var result = dialect.translate_type(ColumnType.INT_32);
    assert(result == "INTEGER");
    print("PASSED\n");
}

void test_sqlite_dialect_translate_type_int64() throws Error {
    print("Test: SqliteDialect.translate_type(INT_64)... ");
    var dialect = new SqliteDialect();
    var result = dialect.translate_type(ColumnType.INT_64);
    assert(result == "INTEGER");
    print("PASSED\n");
}

void test_sqlite_dialect_translate_type_text() throws Error {
    print("Test: SqliteDialect.translate_type(TEXT)... ");
    var dialect = new SqliteDialect();
    var result = dialect.translate_type(ColumnType.TEXT);
    assert(result == "TEXT");
    print("PASSED\n");
}

void test_sqlite_dialect_translate_type_boolean() throws Error {
    print("Test: SqliteDialect.translate_type(BOOLEAN)... ");
    var dialect = new SqliteDialect();
    var result = dialect.translate_type(ColumnType.BOOLEAN);
    assert(result == "INTEGER");
    print("PASSED\n");
}

void test_sqlite_dialect_translate_type_decimal() throws Error {
    print("Test: SqliteDialect.translate_type(DECIMAL)... ");
    var dialect = new SqliteDialect();
    var result = dialect.translate_type(ColumnType.DECIMAL);
    assert(result == "REAL");
    print("PASSED\n");
}

void test_sqlite_dialect_translate_type_datetime() throws Error {
    print("Test: SqliteDialect.translate_type(DATETIME)... ");
    var dialect = new SqliteDialect();
    var result = dialect.translate_type(ColumnType.DATETIME);
    assert(result == "INTEGER");
    print("PASSED\n");
}

void test_sqlite_dialect_translate_type_binary() throws Error {
    print("Test: SqliteDialect.translate_type(BINARY)... ");
    var dialect = new SqliteDialect();
    var result = dialect.translate_type(ColumnType.BINARY);
    assert(result == "BLOB");
    print("PASSED\n");
}

void test_sqlite_dialect_translate_type_uuid() throws Error {
    print("Test: SqliteDialect.translate_type(UUID)... ");
    var dialect = new SqliteDialect();
    var result = dialect.translate_type(ColumnType.UUID);
    assert(result == "TEXT");
    print("PASSED\n");
}

void test_sqlite_dialect_build_select_all() throws Error {
    print("Test: SqliteDialect.build_select_all... ");
    var dialect = new SqliteDialect();
    var sql = dialect.build_select_all("users");
    assert(sql == "SELECT * FROM users");
    print("PASSED\n");
}

void test_sqlite_dialect_build_select_by_id() throws Error {
    print("Test: SqliteDialect.build_select_by_id... ");
    var dialect = new SqliteDialect();
    var sql = dialect.build_select_by_id("users", "id");
    assert(sql == "SELECT * FROM users WHERE id = :id");
    print("PASSED\n");
}

void test_sqlite_dialect_build_insert_sql() throws Error {
    print("Test: SqliteDialect.build_insert_sql... ");
    var dialect = new SqliteDialect();
    var columns = new Vector<string>();
    columns.add("name");
    columns.add("email");
    
    var sql = dialect.build_insert_sql("users", columns);
    assert(sql == "INSERT INTO users (name, email) VALUES (:name, :email)");
    print("PASSED\n");
}

void test_sqlite_dialect_build_update_sql() throws Error {
    print("Test: SqliteDialect.build_update_sql... ");
    var dialect = new SqliteDialect();
    var columns = new Vector<string>();
    columns.add("id");
    columns.add("name");
    columns.add("email");
    
    var sql = dialect.build_update_sql("users", columns, "id");
    // id should be excluded from SET clause
    assert("UPDATE users SET" in sql);
    assert("name = :name" in sql);
    assert("email = :email" in sql);
    assert("WHERE id = :id" in sql);
    print("PASSED\n");
}

void test_sqlite_dialect_build_delete_sql() throws Error {
    print("Test: SqliteDialect.build_delete_sql... ");
    var dialect = new SqliteDialect();
    var sql = dialect.build_delete_sql("users", "id");
    assert(sql == "DELETE FROM users WHERE id = :id");
    print("PASSED\n");
}

// ========================================
// ExpressionToSqlVisitor Tests
// ========================================

EntityMapper get_test_mapper() {
    return EntityMapper.build_for<TestUser>(b => {
        b.table("users");
        b.column<int64?>("id", u => u.id, (u, v) => u.id = v);
        b.column<string>("name", u => u.name, (u, v) => u.name = v);
        b.column<int64?>("age", u => u.age, (u, v) => u.age = v);
    });
}

void test_expression_visitor_binary_equal() throws Error {
    print("Test: ExpressionToSqlVisitor binary equal... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var left = new PropertyExpression(new VariableExpression("u"), "age");
    var right = new LiteralExpression(new Invercargill.NativeElement<int?>(25));
    var expr = new BinaryExpression(left, right, BinaryOperator.EQUAL);
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("(age = :p1)" == sql);
    assert(visitor.get_parameters().count() == 1);
    print("PASSED\n");
}

void test_expression_visitor_binary_not_equal() throws Error {
    print("Test: ExpressionToSqlVisitor binary not equal... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var left = new PropertyExpression(new VariableExpression("u"), "age");
    var right = new LiteralExpression(new Invercargill.NativeElement<int?>(25));
    var expr = new BinaryExpression(left, right, BinaryOperator.NOT_EQUAL);
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("(age <> :p1)" == sql);
    print("PASSED\n");
}

void test_expression_visitor_binary_greater_than() throws Error {
    print("Test: ExpressionToSqlVisitor binary greater than... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var left = new PropertyExpression(new VariableExpression("u"), "age");
    var right = new LiteralExpression(new Invercargill.NativeElement<int?>(18));
    var expr = new BinaryExpression(left, right, BinaryOperator.GREATER_THAN);
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("(age > :p1)" == sql);
    print("PASSED\n");
}

void test_expression_visitor_binary_greater_equal() throws Error {
    print("Test: ExpressionToSqlVisitor binary greater equal... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var left = new PropertyExpression(new VariableExpression("u"), "age");
    var right = new LiteralExpression(new Invercargill.NativeElement<int?>(18));
    var expr = new BinaryExpression(left, right, BinaryOperator.GREATER_EQUAL);
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("(age >= :p1)" == sql);
    print("PASSED\n");
}

void test_expression_visitor_binary_less_than() throws Error {
    print("Test: ExpressionToSqlVisitor binary less than... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var left = new PropertyExpression(new VariableExpression("u"), "age");
    var right = new LiteralExpression(new Invercargill.NativeElement<int?>(65));
    var expr = new BinaryExpression(left, right, BinaryOperator.LESS_THAN);
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("(age < :p1)" == sql);
    print("PASSED\n");
}

void test_expression_visitor_binary_less_equal() throws Error {
    print("Test: ExpressionToSqlVisitor binary less equal... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var left = new PropertyExpression(new VariableExpression("u"), "age");
    var right = new LiteralExpression(new Invercargill.NativeElement<int?>(65));
    var expr = new BinaryExpression(left, right, BinaryOperator.LESS_EQUAL);
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("(age <= :p1)" == sql);
    print("PASSED\n");
}

void test_expression_visitor_binary_and() throws Error {
    print("Test: ExpressionToSqlVisitor binary AND... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var left = new BinaryExpression(
        new PropertyExpression(new VariableExpression("u"), "age"),
        new LiteralExpression(new Invercargill.NativeElement<int?>(18)),
        BinaryOperator.GREATER_THAN
    );
    var right = new BinaryExpression(
        new PropertyExpression(new VariableExpression("u"), "age"),
        new LiteralExpression(new Invercargill.NativeElement<int?>(65)),
        BinaryOperator.LESS_THAN
    );
    var expr = new BinaryExpression(left, right, BinaryOperator.AND);
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("((age > :p1) AND (age < :p2))" == sql);
    assert(visitor.get_parameters().count() == 2);
    print("PASSED\n");
}

void test_expression_visitor_binary_or() throws Error {
    print("Test: ExpressionToSqlVisitor binary OR... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var left = new BinaryExpression(
        new PropertyExpression(new VariableExpression("u"), "name"),
        new LiteralExpression(new Invercargill.NativeElement<string>("Alice")),
        BinaryOperator.EQUAL
    );
    var right = new BinaryExpression(
        new PropertyExpression(new VariableExpression("u"), "name"),
        new LiteralExpression(new Invercargill.NativeElement<string>("Bob")),
        BinaryOperator.EQUAL
    );
    var expr = new BinaryExpression(left, right, BinaryOperator.OR);
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("((name = :p1) OR (name = :p2))" == sql);
    assert(visitor.get_parameters().count() == 2);
    print("PASSED\n");
}

void test_expression_visitor_unary_not() throws Error {
    print("Test: ExpressionToSqlVisitor unary NOT... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var operand = new BinaryExpression(
        new PropertyExpression(new VariableExpression("u"), "age"),
        new LiteralExpression(new Invercargill.NativeElement<int?>(25)),
        BinaryOperator.EQUAL
    );
    var expr = new UnaryExpression(UnaryOperator.NOT, operand);
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("NOT (age = :p1)" == sql);
    print("PASSED\n");
}

void test_expression_visitor_unary_negate() throws Error {
    print("Test: ExpressionToSqlVisitor unary NEGATE... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var operand = new PropertyExpression(new VariableExpression("u"), "age");
    var expr = new UnaryExpression(UnaryOperator.NEGATE, operand);
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("-age" == sql);
    print("PASSED\n");
}

void test_expression_visitor_literal() throws Error {
    print("Test: ExpressionToSqlVisitor literal... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var expr = new LiteralExpression(new Invercargill.NativeElement<string>("test"));
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert(":p1" == sql);
    assert(visitor.get_parameters().count() == 1);
    print("PASSED\n");
}

void test_expression_visitor_property() throws Error {
    print("Test: ExpressionToSqlVisitor property... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    var expr = new PropertyExpression(new VariableExpression("u"), "name");
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("name" == sql);
    print("PASSED\n");
}

void test_expression_visitor_complex_nested() throws Error {
    print("Test: ExpressionToSqlVisitor complex nested... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    // (age > 18 AND age < 65) OR (name = 'Admin')
    var age_range = new BinaryExpression(
        new BinaryExpression(
            new PropertyExpression(new VariableExpression("u"), "age"),
            new LiteralExpression(new Invercargill.NativeElement<int?>(18)),
            BinaryOperator.GREATER_THAN
        ),
        new BinaryExpression(
            new PropertyExpression(new VariableExpression("u"), "age"),
            new LiteralExpression(new Invercargill.NativeElement<int?>(65)),
            BinaryOperator.LESS_THAN
        ),
        BinaryOperator.AND
    );
    var is_admin = new BinaryExpression(
        new PropertyExpression(new VariableExpression("u"), "name"),
        new LiteralExpression(new Invercargill.NativeElement<string>("Admin")),
        BinaryOperator.EQUAL
    );
    var expr = new BinaryExpression(age_range, is_admin, BinaryOperator.OR);
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("(((age > :p1) AND (age < :p2)) OR (name = :p3))" == sql);
    assert(visitor.get_parameters().count() == 3);
    print("PASSED\n");
}

void test_expression_visitor_arithmetic() throws Error {
    print("Test: ExpressionToSqlVisitor arithmetic... ");
    var dialect = new SqliteDialect();
    var mapper = get_test_mapper();
    var visitor = new ExpressionToSqlVisitor(dialect, mapper);
    
    // age + 10
    var expr = new BinaryExpression(
        new PropertyExpression(new VariableExpression("u"), "age"),
        new LiteralExpression(new Invercargill.NativeElement<int?>(10)),
        BinaryOperator.ADD
    );
    
    expr.accept(visitor);
    
    var sql = visitor.get_sql();
    assert("(age + :p1)" == sql);
    print("PASSED\n");
}

// ========================================
// OrmSession Integration Tests
// ========================================

OrmSession setup_test_session() throws SqlError {
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    var session = new OrmSession(conn, new SqliteDialect());
    
    // Create table first (schema is managed by migrations)
    conn.execute("""
        CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT,
            age INTEGER,
            salary REAL,
            is_active INTEGER,
            created_at INTEGER
        )
    """);
    
    // Register TestUser mapper with schema introspection
    session.register_with_schema<TestUser>("users", b => {
        b.column<int64?>("id", u => u.id, (u, v) => u.id = v);
        b.column<string>("name", u => u.name, (u, v) => u.name = v);
        b.column<string>("email", u => u.email, (u, v) => u.email = v);
        b.column<int64?>("age", u => u.age, (u, v) => u.age = v);
        b.column<double?>("salary", u => u.salary, (u, v) => u.salary = v);
        b.column<bool?>("is_active", u => u.is_active, (u, v) => u.is_active = v);
        b.column<DateTime?>("created_at", u => u.created_at, (u, v) => u.created_at = v);
    });
    
    return session;
}

void test_orm_session_register() throws Error {
    print("Test: OrmSession register... ");
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    var session = new OrmSession(conn, new SqliteDialect());
    
    // Create table first
    conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
    
    // Register using the simplified API (without schema methods)
    session.register<TestUser>(b => {
        b.table("users");
        b.column<int64?>("id", u => u.id, (u, v) => u.id = v);
        b.column<string>("name", u => u.name, (u, v) => u.name = v);
    });
    
    // If we got here without exception, registration worked
    print("PASSED\n");
    conn.close();
}

void test_orm_session_register_with_schema() throws Error {
    print("Test: OrmSession register_with_schema... ");
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    var session = new OrmSession(conn, new SqliteDialect());
    
    // Create table with auto-increment primary key
    conn.execute("""
        CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT
        )
    """);
    
    // Register with schema introspection - PK and auto-increment discovered automatically
    session.register_with_schema<TestUser>("users", b => {
        b.column<int64?>("id", u => u.id, (u, v) => u.id = v);
        b.column<string>("name", u => u.name, (u, v) => u.name = v);
        b.column<string>("email", u => u.email, (u, v) => u.email = v);
    });
    
    // Verify the mapper has the schema information
    var mapper = session.get_mapper<TestUser>();
    assert(mapper != null);
    assert(mapper.table_name == "users");
    assert(mapper.get_effective_primary_key() == "id");
    assert(mapper.is_auto_increment("id") == true);
    
    print("PASSED\n");
    conn.close();
}

void test_orm_session_create_query() throws Error {
    print("Test: OrmSession create query... ");
    var session = setup_test_session();
    
    var query = session.query<TestUser>();
    assert(query != null);
    
    print("PASSED\n");
}

void test_orm_session_insert() throws Error {
    print("Test: OrmSession insert... ");
    var session = setup_test_session();
    
    var user = new TestUser();
    user.name = "Alice";
    user.email = "alice@example.com";
    user.age = 30;
    user.salary = 50000.0;
    user.is_active = true;
    user.created_at = new DateTime.now_utc();
    
    session.insert(user);
    
    // Verify insert worked by querying
    var results = session.query<TestUser>().materialise();
    var arr = results.to_array();
    assert(arr.length == 1);
    
    var inserted = arr[0];
    assert(inserted != null);
    assert(inserted.name == "Alice");
    assert(inserted.email == "alice@example.com");
    assert(inserted.age == 30);
    
    print("PASSED\n");
}

void test_orm_session_query_with_where() throws Error {
    print("Test: OrmSession query with where... ");
    var session = setup_test_session();
    
    // Insert test data
    var alice = new TestUser();
    alice.name = "Alice";
    alice.age = 30;
    session.insert(alice);
    
    var bob = new TestUser();
    bob.name = "Bob";
    bob.age = 25;
    session.insert(bob);
    
    var charlie = new TestUser();
    charlie.name = "Charlie";
    charlie.age = 35;
    session.insert(charlie);
    
    // Query with where clause
    var results = session.query<TestUser>()
        .where("age > 28")
        .materialise();
    
    var arr = results.to_array();
    assert(arr.length == 2);
    
    print("PASSED\n");
}

void test_orm_session_update() throws Error {
    print("Test: OrmSession update... ");
    var session = setup_test_session();
    
    // Insert test data
    var user = new TestUser();
    user.name = "Alice";
    user.email = "alice@example.com";
    user.age = 30;
    session.insert(user);
    
    // Get the inserted user with ID - use age for lookup since string literals
    // in expressions aren't supported by the expression parser
    var inserted_arr = session.query<TestUser>()
        .where("age == 30")
        .first();
    assert(inserted_arr != null);
    
    // Update the user
    inserted_arr.name = "Alice Updated";
    inserted_arr.age = 31;
    session.update(inserted_arr);
    
    // Verify update - use the ID we now know
    var updated = session.query<TestUser>()
        .where("id == " + inserted_arr.id.to_string())
        .first();
    assert(updated != null);
    assert(updated.name == "Alice Updated");
    assert(updated.age == 31);
    
    print("PASSED\n");
}

void test_orm_session_delete() throws Error {
    print("Test: OrmSession delete... ");
    var session = setup_test_session();
    
    // Insert test data
    var user = new TestUser();
    user.name = "ToDelete";
    user.age = 99;
    session.insert(user);
    
    // Get the inserted user with ID - use age for lookup
    var inserted = session.query<TestUser>()
        .where("age == 99")
        .first();
    assert(inserted != null);
    
    // Delete the user
    session.delete(inserted);
    
    // Verify deletion - use the ID
    var results = session.query<TestUser>()
        .where("id == " + inserted.id.to_string())
        .materialise();
    var arr = results.to_array();
    assert(arr.length == 0);
    
    print("PASSED\n");
}

void test_orm_session_order_by() throws Error {
    print("Test: OrmSession order by... ");
    var session = setup_test_session();
    
    // Insert test data
    var alice = new TestUser();
    alice.name = "Alice";
    alice.age = 30;
    session.insert(alice);
    
    var bob = new TestUser();
    bob.name = "Bob";
    bob.age = 25;
    session.insert(bob);
    
    var charlie = new TestUser();
    charlie.name = "Charlie";
    charlie.age = 35;
    session.insert(charlie);
    
    // Query with order by ascending
    var results = session.query<TestUser>()
        .order_by("age")
        .materialise();
    
    var arr = results.to_array();
    assert(arr.length == 3);
    assert(arr[0].age == 25);  // Bob
    assert(arr[1].age == 30);  // Alice
    assert(arr[2].age == 35);  // Charlie
    
    // Query with order by descending
    var desc_results = session.query<TestUser>()
        .order_by_desc("age")
        .materialise();
    
    var desc_arr = desc_results.to_array();
    assert(desc_arr.length == 3);
    assert(desc_arr[0].age == 35);  // Charlie
    assert(desc_arr[1].age == 30);  // Alice
    assert(desc_arr[2].age == 25);  // Bob
    
    print("PASSED\n");
}

void test_orm_session_limit_offset() throws Error {
    print("Test: OrmSession limit/offset... ");
    var session = setup_test_session();
    
    // Insert test data
    for (int i = 0; i < 10; i++) {
        var user = new TestUser();
        user.name = "User%d".printf(i);
        user.age = 20 + i;
        session.insert(user);
    }
    
    // Query with limit
    var limited = session.query<TestUser>()
        .order_by("age")
        .limit(3)
        .materialise();
    
    var limited_arr = limited.to_array();
    assert(limited_arr.length == 3);
    
    // Query with limit and offset
    var paged = session.query<TestUser>()
        .order_by("age")
        .limit(3)
        .offset(3)
        .materialise();
    
    var paged_arr = paged.to_array();
    assert(paged_arr.length == 3);
    assert(paged_arr[0].age == 23);  // Skipped 20, 21, 22
    
    print("PASSED\n");
}

void test_orm_session_first() throws Error {
    print("Test: OrmSession first... ");
    var session = setup_test_session();
    
    // Insert test data
    var alice = new TestUser();
    alice.name = "Alice";
    alice.age = 30;
    session.insert(alice);
    
    var bob = new TestUser();
    bob.name = "Bob";
    bob.age = 25;
    session.insert(bob);
    
    // Get first with order
    var first = session.query<TestUser>()
        .order_by("age")
        .first();
    
    assert(first != null);
    assert(first.name == "Bob");  // Youngest
    assert(first.age == 25);
    
    // Query empty result
    var empty = session.query<TestUser>()
        .where("age > 100")
        .first();
    
    assert(empty == null);
    
    print("PASSED\n");
}

// ========================================
// Schema Introspection Tests
// ========================================

void test_schema_introspection_basic() throws Error {
    print("Test: Schema introspection basic... ");
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    var dialect = new SqliteDialect();
    
    // Create a table with various column types
    conn.execute("""
        CREATE TABLE test_table (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT,
            age INTEGER,
            salary REAL,
            created_at INTEGER
        )
    """);
    
    // Introspect the schema
    var schema = dialect.introspect_schema(conn, "test_table");
    
    assert(schema != null);
    assert(schema.table_name == "test_table");
    assert(schema.primary_key_column == "id");
    assert(schema.columns.count() == 6);
    
    // Find the id column and verify it's marked as PK and auto-increment
    var id_col = schema.get_column("id");
    assert(id_col != null);
    assert(id_col.is_primary_key == true);
    assert(id_col.auto_increment == true);
    
    // Find the name column and verify it's required
    var name_col = schema.get_column("name");
    assert(name_col != null);
    assert(name_col.is_required == true);
    
    print("PASSED\n");
    conn.close();
}

void test_schema_introspection_with_register() throws Error {
    print("Test: Schema introspection with register... ");
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    var session = new OrmSession(conn, new SqliteDialect());
    
    // Create table with composite structure
    conn.execute("""
        CREATE TABLE products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            price REAL NOT NULL,
            description TEXT,
            created_at INTEGER
        )
    """);
    
    // Define a simple product class inline using TestUser as proxy
    // Register with schema introspection
    session.register_with_schema<TestUser>("products", b => {
        b.column<int64?>("id", u => u.id, (u, v) => u.id = v);
        b.column<string>("name", u => u.name, (u, v) => u.name = v);
    });
    
    // Verify schema was introspected and applied
    var mapper = session.get_mapper<TestUser>();
    assert(mapper != null);
    assert(mapper.table_name == "products");
    assert(mapper.get_effective_primary_key() == "id");
    
    print("PASSED\n");
    conn.close();
}

// ========================================
// Primary Key Back-Population Tests
// ========================================

/**
 * Helper to set up a session with products table for testing.
 */
OrmSession setup_product_session() throws SqlError {
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    var session = new OrmSession(conn, new SqliteDialect());
    
    conn.execute("""
        CREATE TABLE products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            category TEXT,
            price REAL,
            stock INTEGER
        )
    """);
    
    session.register_with_schema<TestProduct>("products", b => {
        b.column<int64?>("id", p => p.id, (p, v) => p.id = v);
        b.column<string>("name", p => p.name, (p, v) => p.name = v);
        b.column<string>("category", p => p.category, (p, v) => p.category = v);
        b.column<double?>("price", p => p.price, (p, v) => p.price = v);
        b.column<int64?>("stock", p => p.stock, (p, v) => p.stock = v);
    });
    
    return session;
}

/**
 * Helper to set up a session with orders table for testing.
 */
OrmSession setup_order_session() throws SqlError {
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    var session = new OrmSession(conn, new SqliteDialect());
    
    conn.execute("""
        CREATE TABLE orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            product_id INTEGER,
            quantity INTEGER,
            total REAL,
            status TEXT,
            created_at INTEGER
        )
    """);
    
    session.register_with_schema<TestOrder>("orders", b => {
        b.column<int64?>("id", o => o.id, (o, v) => o.id = v);
        b.column<int64?>("user_id", o => o.user_id, (o, v) => o.user_id = v);
        b.column<int64?>("product_id", o => o.product_id, (o, v) => o.product_id = v);
        b.column<int64?>("quantity", o => o.quantity, (o, v) => o.quantity = v);
        b.column<double?>("total", o => o.total, (o, v) => o.total = v);
        b.column<string>("status", o => o.status, (o, v) => o.status = v);
        b.column<DateTime?>("created_at", o => o.created_at, (o, v) => o.created_at = v);
    });
    
    return session;
}

/**
 * Test: Basic insert back-populates primary key on User entity.
 *
 * Verifies that after inserting a new User entity with id = 0,
 * the id property is updated with the auto-generated database ID.
 */
void test_insert_back_populates_primary_key() throws Error {
    print("Test: insert back-populates primary key on User... ");
    var session = setup_test_session();
    
    // Create a new user with id = 0 (default for new entities)
    var user = new TestUser();
    user.name = "TestUser";
    user.email = "test@example.com";
    user.age = 25;
    
    // Verify initial state - id should be 0
    assert(user.id == 0);
    
    // Insert the user
    session.insert(user);
    
    // After insert, the id should be back-populated with the generated ID
    assert(user.id > 0);
    
    print("PASSED\n");
}

/**
 * Test: Multiple inserts get different auto-generated IDs.
 *
 * Verifies that inserting multiple entities results in each
 * getting a unique, incrementing primary key.
 */
void test_insert_back_populates_different_ids() throws Error {
    print("Test: multiple inserts get different IDs... ");
    var session = setup_test_session();
    
    // Create and insert first user
    var user1 = new TestUser();
    user1.name = "User1";
    user1.age = 20;
    session.insert(user1);
    
    // Create and insert second user
    var user2 = new TestUser();
    user2.name = "User2";
    user2.age = 25;
    session.insert(user2);
    
    // Both should have IDs > 0
    assert(user1.id > 0);
    assert(user2.id > 0);
    
    // IDs should be different
    assert(user1.id != user2.id);
    
    print("PASSED\n");
}

/**
 * Test: Insert back-populates primary key on Product entity.
 *
 * Verifies that the back-population works correctly for different
 * entity types, not just User.
 */
void test_insert_back_populates_product() throws Error {
    print("Test: insert back-populates primary key on Product... ");
    var session = setup_product_session();
    
    // Create a new product with id = 0
    var product = new TestProduct();
    product.name = "Test Product";
    product.category = "Electronics";
    product.price = 99.99;
    product.stock = 100;
    
    // Verify initial state
    assert(product.id == 0);
    
    // Insert the product
    session.insert(product);
    
    // After insert, the id should be back-populated
    assert(product.id > 0);
    
    print("PASSED\n");
}

/**
 * Test: Insert back-populates primary key on Order entity.
 *
 * Verifies that the back-population works correctly for Order entities
 * which have different column types including DateTime.
 */
void test_insert_back_populates_order() throws Error {
    print("Test: insert back-populates primary key on Order... ");
    var session = setup_order_session();
    
    // Create a new order with id = 0
    var order = new TestOrder();
    order.user_id = 1;
    order.product_id = 1;
    order.quantity = 2;
    order.total = 199.98;
    order.status = "pending";
    order.created_at = new DateTime.now_utc();
    
    // Verify initial state
    assert(order.id == 0);
    
    // Insert the order
    session.insert(order);
    
    // After insert, the id should be back-populated
    assert(order.id > 0);
    
    print("PASSED\n");
}