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

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

// ============================================================================
// TEST MIGRATION CLASSES
// ============================================================================

// --- Auth namespace migrations ---

/**
 * Auth V1: Create users table - base migration with no dependencies
 */
public class Test_Auth_V1 : Migration {
    public override string migration_namespace { get { return "auth"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "CreateUsers"; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("users", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<string>("email").not_null().unique();
            t.column<string>("password_hash").not_null();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("users");
    }
}

/**
 * Auth V2: Add roles table - depends on auth:1 (within-namespace)
 */
public class Test_Auth_V2 : Migration {
    public override string migration_namespace { get { return "auth"; } }
    public override uint64 serial { get { return 2; } }
    public override string name { get { return "AddRoles"; } }
    public override string[] dependencies { owned get { return {"auth:1"}; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("roles", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<string>("name").not_null().unique();
        });
        
        b.alter_table("users", t => {
            t.add_column<int64?>("role_id").references("roles", "id");
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.alter_table("users", t => {
            t.drop_column("role_id");
        });
        b.drop_table("roles");
    }
}

/**
 * Auth V3: Add sessions table - depends on auth:2
 */
public class Test_Auth_V3 : Migration {
    public override string migration_namespace { get { return "auth"; } }
    public override uint64 serial { get { return 3; } }
    public override string name { get { return "AddSessions"; } }
    public override string[] dependencies { owned get { return {"auth:2"}; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("sessions", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<int64?>("user_id").not_null().references("users", "id");
            t.column<string>("token").not_null();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("sessions");
    }
}

// --- App namespace migrations ---

/**
 * App V1: Create orders table - depends on "auth" namespace (any migration)
 */
public class Test_App_V1 : Migration {
    public override string migration_namespace { get { return "app"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "CreateOrders"; } }
    public override string[] dependencies { owned get { return {"auth"}; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("orders", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<int64?>("user_id").not_null().references("users", "id");
            t.column<double?>("total").not_null();
            t.column<string>("status").not_null();
            t.index("idx_orders_user_id").on_column("user_id");
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("orders");
    }
}

/**
 * App V2: Create order items - depends on auth:1 and app:1
 */
public class Test_App_V2 : Migration {
    public override string migration_namespace { get { return "app"; } }
    public override uint64 serial { get { return 2; } }
    public override string name { get { return "CreateOrderItems"; } }
    public override string[] dependencies { owned get { return {"auth:1", "app:1"}; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("order_items", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<int64?>("order_id").not_null().references("orders", "id");
            t.column<string>("product_name").not_null();
            t.column<int>("quantity").not_null();
            t.column<double?>("price").not_null();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("order_items");
    }
}

// --- Logging namespace migrations ---

/**
 * Logging V1: Create audit log - no dependencies
 */
public class Test_Logging_V1 : Migration {
    public override string migration_namespace { get { return "logging"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "CreateAuditLog"; } }
    // No dependencies
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("audit_log", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<string>("action").not_null();
            t.column<int64?>("user_id");
            t.column<string>("details");
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("audit_log");
    }
}

/**
 * Logging V2: Add timestamps - depends on logging:1
 */
public class Test_Logging_V2 : Migration {
    public override string migration_namespace { get { return "logging"; } }
    public override uint64 serial { get { return 2; } }
    public override string name { get { return "AddTimestamps"; } }
    public override string[] dependencies { owned get { return {"logging:1"}; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.alter_table("audit_log", t => {
            t.add_column<string>("created_at").not_null();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.alter_table("audit_log", t => {
            t.drop_column("created_at");
        });
    }
}

// --- Circular dependency test migrations ---

/**
 * Circular A: depends on Circular B
 */
public class Test_Circular_A : Migration {
    public override string migration_namespace { get { return "circular"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "CircularA"; } }
    public override string[] dependencies { owned get { return {"circular:2"}; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("circular_a", t => {
            t.column<int64?>("id").primary_key().auto_increment();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("circular_a");
    }
}

/**
 * Circular B: depends on Circular A - creates a cycle!
 */
public class Test_Circular_B : Migration {
    public override string migration_namespace { get { return "circular"; } }
    public override uint64 serial { get { return 2; } }
    public override string name { get { return "CircularB"; } }
    public override string[] dependencies { owned get { return {"circular:1"}; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("circular_b", t => {
            t.column<int64?>("id").primary_key().auto_increment();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("circular_b");
    }
}

/**
 * Three-way cycle: A -> B -> C -> A
 */
public class Test_Cycle_A : Migration {
    public override string migration_namespace { get { return "cycle"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "CycleA"; } }
    public override string[] dependencies { owned get { return {"cycle:3"}; } } // depends on C
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("cycle_a", t => {
            t.column<int64?>("id").primary_key().auto_increment();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("cycle_a");
    }
}

/**
 * Three-way cycle: B
 */
public class Test_Cycle_B : Migration {
    public override string migration_namespace { get { return "cycle"; } }
    public override uint64 serial { get { return 2; } }
    public override string name { get { return "CycleB"; } }
    public override string[] dependencies { owned get { return {"cycle:1"}; } } // depends on A
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("cycle_b", t => {
            t.column<int64?>("id").primary_key().auto_increment();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("cycle_b");
    }
}

/**
 * Three-way cycle: C
 */
public class Test_Cycle_C : Migration {
    public override string migration_namespace { get { return "cycle"; } }
    public override uint64 serial { get { return 3; } }
    public override string name { get { return "CycleC"; } }
    public override string[] dependencies { owned get { return {"cycle:2"}; } } // depends on B
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("cycle_c", t => {
            t.column<int64?>("id").primary_key().auto_increment();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("cycle_c");
    }
}

/**
 * Self-dependency: depends on itself
 */
public class Test_Self_Dependency : Migration {
    public override string migration_namespace { get { return "selfdep"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "SelfDependency"; } }
    public override string[] dependencies { owned get { return {"selfdep:1"}; } } // depends on itself!
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("self_dep", t => {
            t.column<int64?>("id").primary_key().auto_increment();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("self_dep");
    }
}

// --- Missing dependency test migrations ---

/**
 * Missing namespace dependency
 */
public class Test_Missing_Namespace : Migration {
    public override string migration_namespace { get { return "missing"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "MissingNamespace"; } }
    public override string[] dependencies { owned get { return {"nonexistent"}; } } // namespace doesn't exist
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("missing_test", t => {
            t.column<int64?>("id").primary_key().auto_increment();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("missing_test");
    }
}

/**
 * Missing serial dependency
 */
public class Test_Missing_Serial : Migration {
    public override string migration_namespace { get { return "missing_serial"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "MissingSerial"; } }
    public override string[] dependencies { owned get { return {"auth:999"}; } } // serial 999 doesn't exist
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("missing_serial_test", t => {
            t.column<int64?>("id").primary_key().auto_increment();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("missing_serial_test");
    }
}

// --- FK + indexed regression test migrations ---

/**
 * Chained FK + indexed: the exact shape from the bug report.
 * The index must actually be created on the FK column.
 */
public class Test_FkIndexed_V1 : Migration {
    public override string migration_namespace { get { return "fkidx"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "CreateRepos"; } }

    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("repos", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<string>("name").not_null();
        });
    }

    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("repos");
    }
}

public class Test_FkIndexed_V2 : Migration {
    public override string migration_namespace { get { return "fkidx"; } }
    public override uint64 serial { get { return 2; } }
    public override string name { get { return "CreateOrgRepos"; } }
    public override string[] dependencies { owned get { return {"fkidx:1"}; } }

    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("org_repos", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<int64?>("repo_id")
                .not_null()
                .references("repos", "id")
                    .on_delete_cascade()
                .indexed();
            t.column<string>("slug").not_null();
        });
    }

    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("org_repos");
    }
}

// ============================================================================
// TEST MAIN
// ============================================================================

public int main(string[] args) {
    Test.init(ref args);
    
    // ========================================
    // 1. Dependency Parsing Tests
    // ========================================
    Test.add_func("/migrations/dependency/parse_namespace", test_parse_namespace);
    Test.add_func("/migrations/dependency/parse_namespace_serial", test_parse_namespace_serial);
    Test.add_func("/migrations/dependency/parse_serial_zero", test_parse_serial_zero);
    Test.add_func("/migrations/dependency/parse_empty_namespace", test_parse_empty_namespace);
    Test.add_func("/migrations/dependency/parse_empty_serial", test_parse_empty_serial);
    Test.add_func("/migrations/dependency/parse_invalid_serial", test_parse_invalid_serial);
    Test.add_func("/migrations/dependency/parse_multiple_colons", test_parse_multiple_colons);
    Test.add_func("/migrations/dependency/to_string", test_dependency_to_string);
    Test.add_func("/migrations/dependency/equality", test_dependency_equality);
    
    // ========================================
    // 2. Single Namespace Migration Tests
    // ========================================
    Test.add_func("/migrations/single/register", test_single_namespace_register);
    Test.add_func("/migrations/single/migrate_to_latest", test_single_namespace_migrate_to_latest);
    Test.add_func("/migrations/single/get_applied", test_single_namespace_get_applied);
    Test.add_func("/migrations/single/get_pending", test_single_namespace_get_pending);
    Test.add_func("/migrations/single/rollback", test_single_namespace_rollback);
    Test.add_func("/migrations/single/get_current_serial", test_single_namespace_get_current_serial);
    
    // ========================================
    // 3. Multi-Namespace Migration Tests
    // ========================================
    Test.add_func("/migrations/multi/register_multiple_namespaces", test_multi_register_namespaces);
    Test.add_func("/migrations/multi/migrate_to_latest_all", test_multi_migrate_to_latest_all);
    Test.add_func("/migrations/multi/migrate_specific_namespace", test_multi_migrate_specific_namespace);
    Test.add_func("/migrations/multi/filter_applied_by_namespace", test_multi_filter_applied_by_namespace);
    Test.add_func("/migrations/multi/filter_pending_by_namespace", test_multi_filter_pending_by_namespace);
    
    // ========================================
    // 4. Cross-Namespace Dependency Tests
    // ========================================
    Test.add_func("/migrations/cross_namespace/namespace_dependency", test_cross_namespace_dependency);
    Test.add_func("/migrations/cross_namespace/specific_serial_dependency", test_cross_namespace_specific_serial);
    Test.add_func("/migrations/cross_namespace/multiple_dependencies", test_cross_namespace_multiple_deps);
    Test.add_func("/migrations/cross_namespace/dependency_order", test_cross_namespace_order);
    Test.add_func("/migrations/cross_namespace/missing_namespace_error", test_cross_namespace_missing_namespace);
    Test.add_func("/migrations/cross_namespace/missing_serial_error", test_cross_namespace_missing_serial);
    
    // ========================================
    // 5. Circular Dependency Detection Tests
    // ========================================
    Test.add_func("/migrations/circular/two_migration_cycle", test_circular_two_migration);
    Test.add_func("/migrations/circular/three_migration_cycle", test_circular_three_migration);
    Test.add_func("/migrations/circular/self_dependency", test_circular_self_dependency);
    Test.add_func("/migrations/circular/error_message_includes_path", test_circular_error_message);
    
    // ========================================
    // 6. Time-Based Rollback Tests
    // ========================================
    Test.add_func("/migrations/rollback/to_specific_migration", test_rollback_to_specific);
    Test.add_func("/migrations/rollback/last_n_migrations", test_rollback_last_n);
    Test.add_func("/migrations/rollback/all_migrations", test_rollback_all);
    Test.add_func("/migrations/rollback/across_namespaces", test_rollback_across_namespaces);
    
    // ========================================
    // 7. Error Message Tests
    // ========================================
    Test.add_func("/migrations/errors/circular_dependency_message", test_error_circular_message);
    Test.add_func("/migrations/errors/unsatisfied_dependency_message", test_error_unsatisfied_message);
    Test.add_func("/migrations/errors/invalid_dependency_syntax", test_error_invalid_syntax);
    
    // ========================================
    // 8. SQL Generation Tests (retained from original)
    // ========================================
    Test.add_func("/migrations/sql/create_table", test_create_table_sql);
    Test.add_func("/migrations/sql/drop_table", test_drop_table_sql);
    Test.add_func("/migrations/sql/create_index", test_create_index_sql);
    Test.add_func("/migrations/sql/add_column", test_add_column_sql);
    Test.add_func("/migrations/sql/drop_column", test_drop_column_sql);
    Test.add_func("/migrations/sql/rename_column", test_rename_column_sql);
    
    // ========================================
    // 9. Foreign Key Tests (retained from original)
    // ========================================
    Test.add_func("/migrations/fk/auto_name", test_fk_creation_with_auto_generated_name);
    Test.add_func("/migrations/fk/explicit_name", test_fk_creation_with_explicit_name);
    Test.add_func("/migrations/fk/on_delete_actions", test_fk_on_delete_actions);
    Test.add_func("/migrations/fk/on_update_actions", test_fk_on_update_actions);
    Test.add_func("/migrations/fk/alter_table", test_fk_in_alter_table_add_column);
    
    // ========================================
    // 10. Indexed Column Tests (retained from original)
    // ========================================
    Test.add_func("/migrations/indexed/auto_name", test_indexed_with_auto_generated_name);
    Test.add_func("/migrations/indexed/custom_name", test_indexed_with_custom_name);
    Test.add_func("/migrations/indexed/unique", test_unique_indexed_creates_unique_index);
    Test.add_func("/migrations/indexed/drop", test_drop_index_on);
    
    // ========================================
    // 11. FK + Indexed Regression Tests
    // ========================================
    Test.add_func("/migrations/fk_indexed/e2e_index_created", test_fk_indexed_creates_index_in_database);
    Test.add_func("/migrations/fk_indexed/operation_model", test_fk_indexed_emits_index_operation);
    Test.add_func("/migrations/fk_indexed/custom_name", test_fk_indexed_with_custom_name);
    Test.add_func("/migrations/fk_indexed/not_null_propagates", test_fk_chain_not_null_propagates);
    Test.add_func("/migrations/fk_indexed/unique_propagates", test_fk_chain_unique_propagates);
    Test.add_func("/migrations/fk_indexed/type_overrides_propagate", test_fk_chain_type_overrides_propagate);
    Test.add_func("/migrations/fk_indexed/alter_table_index_created", test_fk_indexed_in_alter_table_add_column);
    Test.add_func("/migrations/fk_indexed/double_fk_not_required", test_fk_chain_double_references_returns_fk_builder);
    
    return Test.run();
}

// ============================================================================
// 1. DEPENDENCY PARSING TESTS
// ============================================================================

void test_parse_namespace() {
    try {
        var dep = Dependency.parse("auth");
        assert(dep.namespace == "auth");
        assert(dep.serial == null);
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_parse_namespace_serial() {
    try {
        var dep = Dependency.parse("auth:2");
        assert(dep.namespace == "auth");
        assert(dep.serial != null);
        assert(dep.serial == 2);
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_parse_serial_zero() {
    try {
        var dep = Dependency.parse("auth:0");
        assert(dep.namespace == "auth");
        assert(dep.serial != null);
        assert(dep.serial == 0); // Serial 0 is valid, not null
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_parse_empty_namespace() {
    try {
        Dependency.parse(":1");
        assert_not_reached(); // Should have thrown
    } catch (SqlError e) {
        assert("namespace cannot be empty" in e.message || "Invalid dependency syntax" in e.message);
    }
}

void test_parse_empty_serial() {
    try {
        Dependency.parse("auth:");
        assert_not_reached(); // Should have thrown
    } catch (SqlError e) {
        assert("Invalid dependency syntax" in e.message);
    }
}

void test_parse_invalid_serial() {
    try {
        Dependency.parse("auth:abc");
        assert_not_reached(); // Should have thrown
    } catch (SqlError e) {
        assert("serial must be a number" in e.message || "Invalid dependency syntax" in e.message);
    }
}

void test_parse_multiple_colons() {
    try {
        Dependency.parse("auth:1:extra");
        assert_not_reached(); // Should have thrown
    } catch (SqlError e) {
        assert("Invalid dependency syntax" in e.message);
    }
}

void test_dependency_to_string() {
    try {
        var dep1 = Dependency.parse("auth");
        assert(dep1.to_string() == "auth");
        
        var dep2 = Dependency.parse("auth:2");
        assert(dep2.to_string() == "auth:2");
        
        var dep3 = Dependency.parse("logging:0");
        assert(dep3.to_string() == "logging:0");
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_dependency_equality() {
    try {
        var dep1 = Dependency.parse("auth");
        var dep2 = Dependency.parse("auth");
        assert(dep1.equals_dependency(dep2));
        
        var dep3 = Dependency.parse("auth:1");
        var dep4 = Dependency.parse("auth:1");
        assert(dep3.equals_dependency(dep4));
        
        var dep5 = Dependency.parse("auth:1");
        var dep6 = Dependency.parse("auth:2");
        assert(!dep5.equals_dependency(dep6));
        
        var dep7 = Dependency.parse("auth");
        var dep8 = Dependency.parse("auth:1");
        assert(!dep7.equals_dependency(dep8));
    } catch (SqlError e) {
        assert_not_reached();
    }
}

// ============================================================================
// 2. SINGLE NAMESPACE MIGRATION TESTS
// ============================================================================

void test_single_namespace_register() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        
        // Current serial should be 0 (nothing applied yet)
        var current = runner.get_current_serial("auth");
        assert(current == 0);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_single_namespace_migrate_to_latest() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        
        runner.migrate_to_latest();
        
        // Both migrations should be applied
        var current = runner.get_current_serial("auth");
        assert(current == 2);
        
        // Verify tables exist
        var cmd = conn.create_command("SELECT name FROM sqlite_master WHERE type='table' AND name='users'");
        assert(cmd.execute_query().any());
        
        cmd = conn.create_command("SELECT name FROM sqlite_master WHERE type='table' AND name='roles'");
        assert(cmd.execute_query().any());
        
        conn.close();
    } catch (SqlError e) {
        stderr.printf("ERROR: %s\n", e.message);
        assert_not_reached();
    }
}

void test_single_namespace_get_applied() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_Auth_V3());
        
        // Before migration: no applied
        var applied = runner.get_applied_migrations("auth");
        assert(applied.length == 0);
        
        runner.migrate_to_latest();
        
        // After migration: 3 applied in auth namespace
        applied = runner.get_applied_migrations("auth");
        assert(applied.length == 3);
        
        // Verify order (by application_order)
        assert(applied[0].serial == 1);
        assert(applied[1].serial == 2);
        assert(applied[2].serial == 3);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_single_namespace_get_pending() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_Auth_V3());
        
        // Before migration: all pending
        var pending = runner.get_pending_migrations("auth");
        assert(pending.length == 3);
        
        // Apply first migration only via migrate_to
        runner.migrate_to("auth", 1);
        
        // After: 2 pending (V2 and V3)
        pending = runner.get_pending_migrations("auth");
        assert(pending.length == 2);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_single_namespace_rollback() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_Auth_V3());
        
        runner.migrate_to_latest();
        assert(runner.get_current_serial("auth") == 3);
        
        // Rollback one step
        runner.rollback(1);
        assert(runner.get_current_serial("auth") == 2);
        
        // Rollback two more steps
        runner.rollback(2);
        assert(runner.get_current_serial("auth") == 0);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_single_namespace_get_current_serial() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        
        // No migrations applied
        assert(runner.get_current_serial("auth") == 0);
        
        // Apply first
        runner.migrate_to("auth", 1);
        assert(runner.get_current_serial("auth") == 1);
        
        // Apply second
        runner.migrate_to("auth", 2);
        assert(runner.get_current_serial("auth") == 2);
        
        // Unknown namespace returns 0
        assert(runner.get_current_serial("unknown") == 0);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

// ============================================================================
// 3. MULTI-NAMESPACE MIGRATION TESTS
// ============================================================================

void test_multi_register_namespaces() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // Register migrations from multiple namespaces
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_App_V1());
        runner.register_migration(new Test_App_V2());
        runner.register_migration(new Test_Logging_V1());
        
        // All should start at 0
        assert(runner.get_current_serial("auth") == 0);
        assert(runner.get_current_serial("app") == 0);
        assert(runner.get_current_serial("logging") == 0);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_multi_migrate_to_latest_all() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // Register all migrations
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_App_V1());
        runner.register_migration(new Test_App_V2());
        runner.register_migration(new Test_Logging_V1());
        runner.register_migration(new Test_Logging_V2());
        
        runner.migrate_to_latest();
        
        // All namespaces should be at their latest
        assert(runner.get_current_serial("auth") == 2);
        assert(runner.get_current_serial("app") == 2);
        assert(runner.get_current_serial("logging") == 2);
        
        // Verify all tables exist
        string[] expected_tables = {"users", "roles", "orders", "order_items", "audit_log"};
        foreach (var table in expected_tables) {
            var cmd = conn.create_command(
                "SELECT name FROM sqlite_master WHERE type='table' AND name='%s'".printf(table)
            );
            assert(cmd.execute_query().any());
        }
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_multi_migrate_specific_namespace() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_App_V1()); // depends on "auth"
        runner.register_migration(new Test_Logging_V1()); // no dependencies
        
        // Migrate only the auth namespace
        runner.migrate_to_latest_for_namespace("auth");
        
        // Auth should be migrated
        assert(runner.get_current_serial("auth") == 2);
        
        // App and logging should not be migrated (even though logging has no deps)
        assert(runner.get_current_serial("app") == 0);
        assert(runner.get_current_serial("logging") == 0);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_multi_filter_applied_by_namespace() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_App_V1());
        runner.register_migration(new Test_Logging_V1());
        
        runner.migrate_to_latest();
        
        // Get applied for auth only
        var auth_applied = runner.get_applied_migrations("auth");
        assert(auth_applied.length == 2);
        foreach (var record in auth_applied) {
            assert(record.migration_namespace == "auth");
        }
        
        // Get applied for app only
        var app_applied = runner.get_applied_migrations("app");
        assert(app_applied.length == 1);
        foreach (var record in app_applied) {
            assert(record.migration_namespace == "app");
        }
        
        // Get all applied
        var all_applied = runner.get_applied_migrations();
        assert(all_applied.length == 4);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_multi_filter_pending_by_namespace() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_Auth_V3());
        runner.register_migration(new Test_App_V1());
        runner.register_migration(new Test_Logging_V1());
        
        // Apply only auth:1 and auth:2
        runner.migrate_to("auth", 2);
        
        // Pending for auth should be just V3
        var auth_pending = runner.get_pending_migrations("auth");
        assert(auth_pending.length == 1);
        assert(auth_pending[0].serial == 3);
        
        // Pending for app should be V1
        var app_pending = runner.get_pending_migrations("app");
        assert(app_pending.length == 1);
        
        // Pending for logging should be V1
        var log_pending = runner.get_pending_migrations("logging");
        assert(log_pending.length == 1);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

// ============================================================================
// 4. CROSS-NAMESPACE DEPENDENCY TESTS
// ============================================================================

void test_cross_namespace_dependency() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // App V1 depends on "auth" namespace (any migration)
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_App_V1());
        
        runner.migrate_to_latest();
        
        // Both should be applied
        assert(runner.get_current_serial("auth") == 1);
        assert(runner.get_current_serial("app") == 1);
        
        // Verify auth was applied BEFORE app by checking application_order
        var all_applied = runner.get_applied_migrations();
        int64? auth_order = null;
        int64? app_order = null;
        
        foreach (var record in all_applied) {
            if (record.migration_namespace == "auth" && record.serial == 1) {
                auth_order = record.application_order;
            }
            if (record.migration_namespace == "app" && record.serial == 1) {
                app_order = record.application_order;
            }
        }
        
        assert(auth_order != null);
        assert(app_order != null);
        assert(auth_order < app_order); // Auth must be applied before app
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_cross_namespace_specific_serial() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // App V2 depends on auth:1 specifically
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_App_V2()); // depends on auth:1, app:1
        runner.register_migration(new Test_App_V1()); // depends on auth
        
        runner.migrate_to_latest();
        
        // All should be applied
        assert(runner.get_current_serial("auth") == 2);
        assert(runner.get_current_serial("app") == 2);
        
        conn.close();
    } catch (SqlError e) {
        stderr.printf("ERROR in specific_serial: %s\n", e.message);
        assert_not_reached();
    }
}

void test_cross_namespace_multiple_deps() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // App V2 has multiple dependencies: auth:1 and app:1
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_App_V1());
        runner.register_migration(new Test_App_V2());
        
        runner.migrate_to_latest();
        
        // Verify all dependencies were applied before App V2
        var all_applied = runner.get_applied_migrations();
        
        int64? auth1_order = null;
        int64? app1_order = null;
        int64? app2_order = null;
        
        foreach (var record in all_applied) {
            if (record.migration_namespace == "auth" && record.serial == 1) auth1_order = record.application_order;
            if (record.migration_namespace == "app" && record.serial == 1) app1_order = record.application_order;
            if (record.migration_namespace == "app" && record.serial == 2) app2_order = record.application_order;
        }
        
        assert(auth1_order != null);
        assert(app1_order != null);
        assert(app2_order != null);
        
        // App V2 must come after both auth:1 and app:1
        assert(app2_order > auth1_order);
        assert(app2_order > app1_order);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_cross_namespace_order() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // Register in random order
        runner.register_migration(new Test_App_V2()); // depends on auth:1, app:1
        runner.register_migration(new Test_Logging_V1()); // no deps
        runner.register_migration(new Test_Auth_V2()); // depends on auth:1
        runner.register_migration(new Test_App_V1()); // depends on auth
        runner.register_migration(new Test_Auth_V1()); // no deps
        
        runner.migrate_to_latest();
        
        // Verify correct order regardless of registration order
        var all_applied = runner.get_applied_migrations();
        
        // Build a map of application orders
        var orders = new Dictionary<string, int64?>();
        foreach (var record in all_applied) {
            string key = "%s:%s".printf(record.migration_namespace, record.serial.to_string());
            orders.set(key, record.application_order);
        }
        
        // Verify ordering constraints
        // auth:1 must come before auth:2
        assert(orders.get("auth:1") < orders.get("auth:2"));
        
        // auth:1 must come before app:1 (app:1 depends on "auth")
        assert(orders.get("auth:1") < orders.get("app:1"));
        
        // app:1 must come before app:2
        assert(orders.get("app:1") < orders.get("app:2"));
        
        // auth:1 must come before app:2 (app:2 depends on auth:1)
        assert(orders.get("auth:1") < orders.get("app:2"));
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_cross_namespace_missing_namespace() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // Register migration that depends on non-existent namespace
        runner.register_migration(new Test_Missing_Namespace());
        
        runner.validate_dependencies();
        assert_not_reached(); // Should have thrown
    } catch (SqlError e) {
        assert("requires namespace" in e.message || "Unsatisfied dependency" in e.message);
    }
}

void test_cross_namespace_missing_serial() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // Register auth:1 and migration that depends on auth:999
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Missing_Serial());
        
        runner.validate_dependencies();
        assert_not_reached(); // Should have thrown
    } catch (SqlError e) {
        assert("serial" in e.message || "Unsatisfied dependency" in e.message);
    }
}

// ============================================================================
// 5. CIRCULAR DEPENDENCY DETECTION TESTS
// ============================================================================

void test_circular_two_migration() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // Circular A depends on B, B depends on A
        runner.register_migration(new Test_Circular_A());
        runner.register_migration(new Test_Circular_B());
        
        runner.migrate_to_latest();
        assert_not_reached(); // Should have thrown
    } catch (SqlError e) {
        assert("Circular dependency" in e.message);
    }
}

void test_circular_three_migration() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // Three-way cycle: A -> B -> C -> A
        runner.register_migration(new Test_Cycle_A());
        runner.register_migration(new Test_Cycle_B());
        runner.register_migration(new Test_Cycle_C());
        
        runner.migrate_to_latest();
        assert_not_reached(); // Should have thrown
    } catch (SqlError e) {
        assert("Circular dependency" in e.message);
    }
}

void test_circular_self_dependency() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // Self-dependency
        runner.register_migration(new Test_Self_Dependency());
        
        runner.migrate_to_latest();
        assert_not_reached(); // Should have thrown
    } catch (SqlError e) {
        assert("Circular dependency" in e.message);
    }
}

void test_circular_error_message() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Circular_A());
        runner.register_migration(new Test_Circular_B());
        
        runner.migrate_to_latest();
        assert_not_reached();
    } catch (SqlError e) {
        // Error message should include the cycle path
        assert("Circular dependency" in e.message);
        // Should show the cycle with arrow notation
        assert("→" in e.message || "->" in e.message || "circular" in e.message.down());
    }
}

// ============================================================================
// 6. TIME-BASED ROLLBACK TESTS
// ============================================================================

void test_rollback_to_specific() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_Auth_V3());
        
        runner.migrate_to_latest();
        assert(runner.get_current_serial("auth") == 3);
        
        // Rollback to auth:1
        runner.rollback_to("auth", 1);
        assert(runner.get_current_serial("auth") == 1);
        
        // V2 and V3 should be rolled back (tables dropped)
        // Only users table should exist
        var cmd = conn.create_command("SELECT name FROM sqlite_master WHERE type='table' AND name='roles'");
        assert(!cmd.execute_query().any());
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_rollback_last_n() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_Auth_V3());
        
        runner.migrate_to_latest();
        
        // Rollback last 2 migrations
        runner.rollback(2);
        assert(runner.get_current_serial("auth") == 1);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_rollback_all() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Auth_V2());
        runner.register_migration(new Test_App_V1());
        runner.register_migration(new Test_Logging_V1());
        
        runner.migrate_to_latest();
        
        // Rollback all
        runner.rollback_all();
        
        assert(runner.get_current_serial("auth") == 0);
        assert(runner.get_current_serial("app") == 0);
        assert(runner.get_current_serial("logging") == 0);
        
        // No migrations should be applied
        var applied = runner.get_applied_migrations();
        assert(applied.length == 0);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

void test_rollback_across_namespaces() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // Register migrations that will apply in a specific order
        runner.register_migration(new Test_Auth_V1());    // 1st
        runner.register_migration(new Test_Auth_V2());    // 2nd (depends on auth:1)
        runner.register_migration(new Test_App_V1());     // 3rd (depends on auth)
        runner.register_migration(new Test_Logging_V1()); // 4th (no deps)
        
        runner.migrate_to_latest();
        
        // Verify all are applied
        var applied = runner.get_applied_migrations();
        assert(applied.length == 4);
        
        // Rollback to auth:1 should roll back logging:1, app:1, auth:2 in that order
        runner.rollback_to("auth", 1);
        
        // Only auth:1 should remain
        applied = runner.get_applied_migrations();
        assert(applied.length == 1);
        assert(applied[0].migration_namespace == "auth");
        assert(applied[0].serial == 1);
        
        conn.close();
    } catch (SqlError e) {
        assert_not_reached();
    }
}

// ============================================================================
// 7. ERROR MESSAGE TESTS
// ============================================================================

void test_error_circular_message() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Circular_A());
        runner.register_migration(new Test_Circular_B());
        
        runner.validate_dependencies();
        assert_not_reached();
    } catch (SqlError e) {
        // Verify error message format
        assert("Circular dependency detected" in e.message);
    }
}

void test_error_unsatisfied_message() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        runner.register_migration(new Test_Auth_V1());
        runner.register_migration(new Test_Missing_Serial()); // depends on auth:999
        
        runner.validate_dependencies();
        assert_not_reached();
    } catch (SqlError e) {
        // Verify error message contains useful information
        assert("Unsatisfied dependency" in e.message);
        assert("999" in e.message); // The missing serial
        assert("auth" in e.message); // The namespace
    }
}

void test_error_invalid_syntax() {
    try {
        Dependency.parse("auth:abc:extra");
        assert_not_reached();
    } catch (SqlError e) {
        assert("Invalid dependency syntax" in e.message);
    }
}

// ============================================================================
// 8. SQL GENERATION TESTS (retained from original)
// ============================================================================

void test_create_table_sql() {
    var dialect = new SqliteDialect();
    var op = new CreateTableOperation() { table_name = "test" };
    op.columns.add(new ColumnDefinition() {
        name = "id",
        column_type = ColumnType.INT_64,
        is_primary_key = true,
        auto_increment = true
    });
    op.columns.add(new ColumnDefinition() {
        name = "name",
        column_type = ColumnType.TEXT,
        is_required = true
    });
    
    var sql = dialect.create_table_sql(op);
    assert("CREATE TABLE test" in sql);
    assert("id" in sql);
    assert("INTEGER" in sql);
    assert("PRIMARY KEY" in sql);
    assert("name" in sql);
    assert("TEXT" in sql);
    assert("NOT NULL" in sql);
}

void test_drop_table_sql() {
    var dialect = new SqliteDialect();
    var op = new DropTableOperation() { table_name = "test" };
    var sql = dialect.drop_table_sql(op);
    assert(sql == "DROP TABLE IF EXISTS test");
}

void test_create_index_sql() {
    var dialect = new SqliteDialect();
    var op = new CreateIndexOperation() {
        index_name = "idx_test",
        table_name = "test",
        is_unique = false
    };
    op.columns.add("name");
    var sql = dialect.create_index_sql(op);
    assert("CREATE INDEX idx_test ON test (name)" == sql);
}

void test_add_column_sql() {
    var dialect = new SqliteDialect();
    var op = new AddColumnOperation() {
        table_name = "users",
        column = new ColumnDefinition() {
            name = "age",
            column_type = ColumnType.INT_32
        }
    };
    var sql = dialect.add_column_sql(op);
    assert("ALTER TABLE users ADD COLUMN age" in sql);
    assert("INTEGER" in sql);
}

void test_drop_column_sql() {
    var dialect = new SqliteDialect();
    var op = new DropColumnOperation() {
        table_name = "users",
        column_name = "age"
    };
    var sql = dialect.drop_column_sql(op);
    assert("ALTER TABLE users DROP COLUMN age" == sql);
}

void test_rename_column_sql() {
    var dialect = new SqliteDialect();
    var op = new RenameColumnOperation() {
        table_name = "users",
        old_name = "old_name",
        new_name = "new_name"
    };
    var sql = dialect.rename_column_sql(op);
    assert("ALTER TABLE users RENAME COLUMN old_name TO new_name" == sql);
}

// ============================================================================
// 9. FOREIGN KEY TESTS (retained from original)
// ============================================================================

void test_fk_creation_with_auto_generated_name() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);
    
    builder.create_table("orders", t => {
        t.column<int64?>("id").primary_key().auto_increment();
        t.column<int64?>("user_id")
            .not_null()
            .references("users", "id");
    });
    
    var ops = builder.get_operations();
    assert(ops.length == 1);
    
    var table_op = ops[0] as CreateTableOperation;
    assert(table_op != null);
    assert(table_op.constraints.length == 1);
    
    var constraint = table_op.constraints.get(0);
    assert(constraint.constraint_type == "FOREIGN KEY");
    assert(constraint.name == "fk_orders_user_id");
    assert(constraint.reference_table == "users");
    assert(constraint.reference_columns.get(0) == "id");
    assert(constraint.columns.get(0) == "user_id");
    
    var sql = dialect.create_table_sql(table_op);
    assert("FOREIGN KEY" in sql);
    assert("REFERENCES users (id)" in sql);
    assert("fk_orders_user_id" in sql);
}

void test_fk_creation_with_explicit_name() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);
    
    builder.create_table("orders", t => {
        t.column<int64?>("id").primary_key().auto_increment();
        t.column<int64?>("user_id")
            .not_null()
            .references("users", "id")
                .name("custom_fk_orders_users");
    });
    
    var ops = builder.get_operations();
    var table_op = ops[0] as CreateTableOperation;
    assert(table_op != null);
    
    var constraint = table_op.constraints.get(0);
    assert(constraint.name == "custom_fk_orders_users");
    
    var sql = dialect.create_table_sql(table_op);
    assert("custom_fk_orders_users" in sql);
}

void test_fk_on_delete_actions() {
    var dialect = new SqliteDialect();
    
    // Test CASCADE
    var builder = new MigrationBuilder(dialect);
    builder.create_table("orders", t => {
        t.column<int64?>("user_id")
            .references("users", "id")
                .on_delete_cascade();
    });
    var ops = builder.get_operations();
    var table_op = ops[0] as CreateTableOperation;
    var constraint = table_op.constraints.get(0);
    assert(constraint.on_delete_action == ReferentialAction.CASCADE);
    var sql = dialect.create_table_sql(table_op);
    assert("ON DELETE CASCADE" in sql);
    
    // Test SET NULL
    builder = new MigrationBuilder(dialect);
    builder.create_table("orders", t => {
        t.column<int64?>("user_id")
            .references("users", "id")
                .on_delete_set_null();
    });
    ops = builder.get_operations();
    table_op = ops[0] as CreateTableOperation;
    constraint = table_op.constraints.get(0);
    assert(constraint.on_delete_action == ReferentialAction.SET_NULL);
    sql = dialect.create_table_sql(table_op);
    assert("ON DELETE SET NULL" in sql);
    
    // Test RESTRICT
    builder = new MigrationBuilder(dialect);
    builder.create_table("orders", t => {
        t.column<int64?>("user_id")
            .references("users", "id")
                .on_delete_restrict();
    });
    ops = builder.get_operations();
    table_op = ops[0] as CreateTableOperation;
    constraint = table_op.constraints.get(0);
    assert(constraint.on_delete_action == ReferentialAction.RESTRICT);
    sql = dialect.create_table_sql(table_op);
    assert("ON DELETE RESTRICT" in sql);
}

void test_fk_on_update_actions() {
    var dialect = new SqliteDialect();
    
    // Test CASCADE
    var builder = new MigrationBuilder(dialect);
    builder.create_table("orders", t => {
        t.column<int64?>("user_id")
            .references("users", "id")
                .on_update_cascade();
    });
    var ops = builder.get_operations();
    var table_op = ops[0] as CreateTableOperation;
    var constraint = table_op.constraints.get(0);
    assert(constraint.on_update_action == ReferentialAction.CASCADE);
    var sql = dialect.create_table_sql(table_op);
    assert("ON UPDATE CASCADE" in sql);
    
    // Test SET NULL
    builder = new MigrationBuilder(dialect);
    builder.create_table("orders", t => {
        t.column<int64?>("user_id")
            .references("users", "id")
                .on_update_set_null();
    });
    ops = builder.get_operations();
    table_op = ops[0] as CreateTableOperation;
    constraint = table_op.constraints.get(0);
    assert(constraint.on_update_action == ReferentialAction.SET_NULL);
    sql = dialect.create_table_sql(table_op);
    assert("ON UPDATE SET NULL" in sql);
}

void test_fk_in_alter_table_add_column() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);
    
    builder.alter_table("orders", t => {
        t.add_column<int64?>("product_id")
            .not_null()
            .references("products", "id")
                .on_delete_restrict();
    });
    
    var ops = builder.get_operations();
    assert(ops.length == 1);
    
    var add_col_op = ops[0] as AddColumnOperation;
    assert(add_col_op != null);
    assert(add_col_op.foreign_key_constraint != null);
    
    var fk = add_col_op.foreign_key_constraint;
    assert(fk.constraint_type == "FOREIGN KEY");
    assert(fk.reference_table == "products");
    assert(fk.reference_columns.get(0) == "id");
    assert(fk.on_delete_action == ReferentialAction.RESTRICT);
    
    var sql = dialect.add_column_sql(add_col_op);
    assert("REFERENCES products (id)" in sql);
    assert("ON DELETE RESTRICT" in sql);
}

// ============================================================================
// 10. INDEXED COLUMN TESTS (retained from original)
// ============================================================================

void test_indexed_with_auto_generated_name() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);
    
    builder.create_table("users", t => {
        t.column<int64?>("id").primary_key().auto_increment();
        t.column<string>("email")
            .not_null()
            .indexed();
    });
    
    var ops = builder.get_operations();
    assert(ops.length == 2);
    
    var table_op = ops[0] as CreateTableOperation;
    assert(table_op != null);
    
    var idx_op = ops[1] as CreateIndexOperation;
    assert(idx_op != null);
    assert(idx_op.index_name == "idx_users_email");
    assert(idx_op.table_name == "users");
    assert(idx_op.columns.length == 1);
    assert(idx_op.columns.get(0) == "email");
    assert(idx_op.is_unique == false);
}

void test_indexed_with_custom_name() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);
    
    builder.create_table("users", t => {
        t.column<int64?>("id").primary_key().auto_increment();
        t.column<string>("email")
            .not_null()
            .indexed("idx_users_email_address");
    });
    
    var ops = builder.get_operations();
    assert(ops.length == 2);
    
    var idx_op = ops[1] as CreateIndexOperation;
    assert(idx_op != null);
    assert(idx_op.index_name == "idx_users_email_address");
}

void test_unique_indexed_creates_unique_index() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);
    
    builder.create_table("users", t => {
        t.column<int64?>("id").primary_key().auto_increment();
        t.column<string>("email")
            .not_null()
            .unique()
            .indexed();
    });
    
    var ops = builder.get_operations();
    assert(ops.length == 2);
    
    var idx_op = ops[1] as CreateIndexOperation;
    assert(idx_op != null);
    assert(idx_op.is_unique == true);
    
    var sql = dialect.create_index_sql(idx_op);
    assert("CREATE UNIQUE INDEX" in sql);
}

void test_drop_index_on() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);
    
    builder.alter_table("users", t => {
        t.drop_index_on("email");
    });
    
    var ops = builder.get_operations();
    assert(ops.length == 1);
    
    var drop_op = ops[0] as DropIndexOperation;
    assert(drop_op != null);
    assert(drop_op.index_name == "idx_users_email");
    assert(drop_op.table_name == "users");
}

// ============================================================================
// 11. FK + INDEXED REGRESSION TESTS
//
// Regression guard for the dropped-flag bug class: ForeignKeyBuilder wraps a
// MigrationColumnBuilder, and any column method called after .references()
// (indexed(), not_null(), unique(), type_*(), ...) used to mutate the
// wrapper's own throwaway state instead of the wrapped column builder that
// TableBuilder/AlterTableBuilder read at build time.
// ============================================================================

/**
 * End-to-end: the chained FK+indexed form must produce a real index in an
 * actual SQLite database file, verified via sqlite_master (what
 * `sqlite3 db .schema` reads).
 */
void test_fk_indexed_creates_index_in_database() {
    string db_path = GLib.Path.build_filename(
        GLib.Environment.get_tmp_dir(),
        "invercargill-sql-fk-indexed-test.db"
    );
    GLib.FileUtils.remove(db_path);  // ignore missing

    try {
        var conn = ConnectionFactory.create_and_open(@"sqlite://$db_path");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);

        runner.register_migration(new Test_FkIndexed_V1());
        runner.register_migration(new Test_FkIndexed_V2());
        runner.migrate_to_latest();

        assert(runner.get_current_serial("fkidx") == 2);

        // The FK constraint itself must still be there
        var fk_cmd = conn.create_command(
            "SELECT sql FROM sqlite_master WHERE type='table' AND name='org_repos'"
        );
        var create_sql = fk_cmd.execute_scalar();
        assert(create_sql != null);
        string table_sql = create_sql.as_string_or_null() ?? "";
        assert("FOREIGN KEY" in table_sql);
        assert("REFERENCES repos (id)" in table_sql);
        assert("ON DELETE CASCADE" in table_sql);

        // THE regression: a CREATE INDEX for the FK column must exist
        var idx_cmd = conn.create_command(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND tbl_name='org_repos' AND name='idx_org_repos_repo_id'"
        );
        var count_elem = idx_cmd.execute_scalar();
        assert(count_elem != null);
        assert(count_elem.as_int64_or_null() == 1);

        conn.close();
    } catch (SqlError e) {
        stderr.printf("ERROR: %s\n", e.message);
        assert_not_reached();
    } finally {
        GLib.FileUtils.remove(db_path);
    }
}

/**
 * Unit level: the chained form must emit a CreateIndexOperation alongside
 * the CreateTableOperation, with the auto-generated idx_{table}_{column} name.
 */
void test_fk_indexed_emits_index_operation() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);

    builder.create_table("org_repos", t => {
        t.column<int64?>("id").primary_key().auto_increment();
        t.column<int64?>("repo_id")
            .not_null()
            .references("repos", "id")
                .on_delete_cascade()
            .indexed();
    });

    var ops = builder.get_operations();
    assert(ops.length == 2);

    var table_op = ops[0] as CreateTableOperation;
    assert(table_op != null);

    // FK constraint still collected from the inner builder chain
    assert(table_op.constraints.length == 1);
    var constraint = table_op.constraints.get(0);
    assert(constraint.constraint_type == "FOREIGN KEY");
    assert(constraint.name == "fk_org_repos_repo_id");
    assert(constraint.reference_table == "repos");
    assert(constraint.on_delete_action == ReferentialAction.CASCADE);

    // The index operation must ALSO be emitted
    var idx_op = ops[1] as CreateIndexOperation;
    assert(idx_op != null);
    assert(idx_op.index_name == "idx_org_repos_repo_id");
    assert(idx_op.table_name == "org_repos");
    assert(idx_op.columns.length == 1);
    assert(idx_op.columns.get(0) == "repo_id");
    assert(idx_op.is_unique == false);

    var sql = dialect.create_index_sql(idx_op);
    assert("CREATE INDEX idx_org_repos_repo_id ON org_repos (repo_id)" == sql);
}

/**
 * Custom index names given after .references() must survive the wrapper.
 */
void test_fk_indexed_with_custom_name() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);

    builder.create_table("org_repos", t => {
        t.column<int64?>("repo_id")
            .references("repos", "id")
                .indexed("idx_org_repos_repo_fk");
    });

    var ops = builder.get_operations();
    assert(ops.length == 2);

    var idx_op = ops[1] as CreateIndexOperation;
    assert(idx_op != null);
    assert(idx_op.index_name == "idx_org_repos_repo_fk");
}

/**
 * not_null() called after .references() must reach the real column.
 */
void test_fk_chain_not_null_propagates() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);

    builder.create_table("org_repos", t => {
        t.column<int64?>("repo_id")
            .references("repos", "id")
                .not_null();
    });

    var ops = builder.get_operations();
    var table_op = ops[0] as CreateTableOperation;
    assert(table_op != null);
    assert(table_op.columns.length == 1);
    assert(table_op.columns.get(0).is_required == true);

    var sql = dialect.create_table_sql(table_op);
    assert("NOT NULL" in sql);
}

/**
 * unique() called after .references() must reach the real column.
 */
void test_fk_chain_unique_propagates() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);

    builder.create_table("org_repos", t => {
        t.column<int64?>("repo_id")
            .references("repos", "id")
                .unique()
                .indexed();
    });

    var ops = builder.get_operations();
    assert(ops.length == 2);

    var table_op = ops[0] as CreateTableOperation;
    assert(table_op.columns.get(0).is_unique == true);

    var idx_op = ops[1] as CreateIndexOperation;
    assert(idx_op != null);
    assert(idx_op.is_unique == true);
    var sql = dialect.create_index_sql(idx_op);
    assert("CREATE UNIQUE INDEX" in sql);
}

/**
 * Column type overrides called after .references() must reach the real column
 * (type_text() overriding the generic int64 inference).
 */
void test_fk_chain_type_overrides_propagate() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);

    builder.create_table("org_repos", t => {
        t.column<int64?>("repo_id")
            .references("repos", "id")
                .type_text();
    });

    var ops = builder.get_operations();
    var table_op = ops[0] as CreateTableOperation;
    assert(table_op.columns.get(0).column_type == ColumnType.TEXT);

    var sql = dialect.create_table_sql(table_op);
    assert("TEXT" in sql);
}

/**
 * ALTER TABLE form: add_column with the chained FK+indexed must emit the
 * index operation too.
 */
void test_fk_indexed_in_alter_table_add_column() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);

    builder.alter_table("org_repos", t => {
        t.add_column<int64?>("repo_id")
            .references("repos", "id")
                .indexed();
    });

    var ops = builder.get_operations();
    assert(ops.length == 2);

    var add_col_op = ops[0] as AddColumnOperation;
    assert(add_col_op != null);
    assert(add_col_op.foreign_key_constraint != null);
    assert(add_col_op.foreign_key_constraint.reference_table == "repos");

    var idx_op = ops[1] as CreateIndexOperation;
    assert(idx_op != null);
    assert(idx_op.index_name == "idx_org_repos_repo_id");
    assert(idx_op.columns.get(0) == "repo_id");
}

/**
 * Chaining a second .references() after FK configuration must still return a
 * ForeignKeyBuilder (documented behaviour) and register on the inner builder.
 */
void test_fk_chain_double_references_returns_fk_builder() {
    var dialect = new SqliteDialect();
    var builder = new MigrationBuilder(dialect);

    builder.create_table("org_repos", t => {
        var fk = t.column<int64?>("repo_id")
            .references("repos", "id")
                .on_delete_cascade()
            .references("other_table", "id");
        assert(fk is ForeignKeyBuilder);
    });

    // The inner column builder must expose the LAST fk configured
    var ops = builder.get_operations();
    var table_op = ops[0] as CreateTableOperation;
    assert(table_op != null);
    assert(table_op.constraints.length == 1);
    assert(table_op.constraints.get(0).reference_table == "other_table");
}