# Phase 2: Migration System - Implementation Summary

## ⚠️ CRITICAL CONSTRAINT

**`GLib.List`, `GLib.HashSet`, `GLib.HashTable`, or ANY `Libgee` structures ARE STRICTLY FORBIDDEN.**

All collection types MUST use `Invercargill.DataStructures`:
- `Invercargill.DataStructures.Vector<T>` instead of GLib.List
- `Invercargill.DataStructures.Series<T>` for ordered collections
- `Invercargill.DataStructures.Dictionary<K,V>` instead of GLib.HashTable
- `Invercargill.DataStructures.HashSet<T>` for sets
- `Invercargill.DataStructures.Buffer<T>` for fixed-size indexed collections

---

## Overview

Implement a versioned explicit migration system with `up()` and `down()` methods using a fluent `MigrationBuilder` API. Migrations are tracked in a `__migrations` table.

## Files to Create

```
src/
├── migrations/
│   ├── migration.vala               # Abstract Migration class
│   ├── migration-runner.vala        # MigrationRunner class
│   ├── migration-builder.vala       # Fluent builder for schema operations
│   ├── table-builder.vala           # Builder for CREATE TABLE
│   ├── alter-table-builder.vala     # Builder for ALTER TABLE
│   ├── column-builder.vala          # Builder for column definitions (migration)
│   └── schema-operations.vala       # Schema operation classes
```

---

## Implementation Tasks

### Task 1: Schema Operations

Create `src/migrations/schema-operations.vala`:

```vala
using Invercargill.DataStructures;

namespace InvercargillSql.Migrations {
    
    public interface SchemaOperation : Object {
        public abstract string to_sql(SqlDialect dialect);
    }
    
    public class CreateTableOperation : Object, SchemaOperation {
        public string table_name { get; set; }
        public Vector<ColumnDefinition> columns { get; set; }
        public Vector<TableConstraint> constraints { get; set; }
        
        public CreateTableOperation() {
            columns = new Vector<ColumnDefinition>();
            constraints = new Vector<TableConstraint>();
        }
        
        public string to_sql(SqlDialect dialect) {
            return dialect.create_table_sql(this);
        }
    }
    
    public class DropTableOperation : Object, SchemaOperation {
        public string table_name { get; set; }
        
        public string to_sql(SqlDialect dialect) {
            return dialect.drop_table_sql(this);
        }
    }
    
    public class AddColumnOperation : Object, SchemaOperation {
        public string table_name { get; set; }
        public ColumnDefinition column { get; set; }
        
        public string to_sql(SqlDialect dialect) {
            return dialect.add_column_sql(this);
        }
    }
    
    public class DropColumnOperation : Object, SchemaOperation {
        public string table_name { get; set; }
        public string column_name { get; set; }
        
        public string to_sql(SqlDialect dialect) {
            return dialect.drop_column_sql(this);
        }
    }
    
    public class RenameColumnOperation : Object, SchemaOperation {
        public string table_name { get; set; }
        public string old_name { get; set; }
        public string new_name { get; set; }
        
        public string to_sql(SqlDialect dialect) {
            return dialect.rename_column_sql(this);
        }
    }
    
    public class CreateIndexOperation : Object, SchemaOperation {
        public string index_name { get; set; }
        public string table_name { get; set; }
        public Vector<string> columns { get; set; }
        public bool is_unique { get; set; }
        
        public CreateIndexOperation() {
            columns = new Vector<string>();
        }
        
        public string to_sql(SqlDialect dialect) {
            return dialect.create_index_sql(this);
        }
    }
    
    public class DropIndexOperation : Object, SchemaOperation {
        public string index_name { get; set; }
        public string table_name { get; set; }
        
        public string to_sql(SqlDialect dialect) {
            return dialect.drop_index_sql(this);
        }
    }
    
    public class RawSqlOperation : Object, SchemaOperation {
        public string sql { get; set; }
        
        public string to_sql(SqlDialect dialect) {
            return sql;
        }
    }
    
    public class TableConstraint : Object {
        public string name { get; set; }
        public string constraint_type { get; set; }  // PRIMARY KEY, UNIQUE, FOREIGN KEY, etc.
        public Vector<string> columns { get; set; }
        public string? reference_table { get; set; }
        public Vector<string> reference_columns { get; set; }
        
        public TableConstraint() {
            columns = new Vector<string>();
            reference_columns = new Vector<string>();
        }
    }
}
```

### Task 2: Abstract Migration Class

Create `src/migrations/migration.vala`:

```vala
namespace InvercargillSql.Migrations {
    
    public abstract class Migration : Object {
        public abstract int version { get; }
        public abstract string name { get; }
        public abstract void up(MigrationBuilder b) throws SqlError;
        public abstract void down(MigrationBuilder b) throws SqlError;
    }
}
```

### Task 3: ColumnBuilder for Migrations

Create `src/migrations/column-builder.vala`:

```vala
namespace InvercargillSql.Migrations {
    
    public class MigrationColumnBuilder : Object {
        private MigrationBuilder? _migration_parent;
        private TableBuilder? _table_parent;
        private AlterTableBuilder? _alter_parent;
        private ColumnDefinition _column;
        private string? _table_name;
        
        internal MigrationColumnBuilder(MigrationBuilder parent, string table_name, ColumnDefinition column) {
            _migration_parent = parent;
            _table_name = table_name;
            _column = column;
        }
        
        internal MigrationColumnBuilder.for_table(TableBuilder parent, ColumnDefinition column) {
            _table_parent = parent;
            _column = column;
        }
        
        internal MigrationColumnBuilder.for_alter(AlterTableBuilder parent, string table_name, ColumnDefinition column) {
            _alter_parent = parent;
            _table_name = table_name;
            _column = column;
        }
        
        public MigrationBuilder primary_key() {
            _column.is_primary_key = true;
            return return_to_migration();
        }
        
        public MigrationBuilder not_null() {
            _column.is_required = true;
            return return_to_migration();
        }
        
        public MigrationBuilder unique() {
            _column.is_unique = true;
            return return_to_migration();
        }
        
        public MigrationBuilder auto_increment() {
            _column.auto_increment = true;
            return return_to_migration();
        }
        
        public MigrationBuilder default_value<T>(T value) {
            _column.default_value = new Invercargill.NativeElement<T>(value);
            return return_to_migration();
        }
        
        public MigrationBuilder default_now() {
            _column.default_now = true;
            return return_to_migration();
        }
        
        public MigrationBuilder references(string table, string column) {
            // Add foreign key reference
            return return_to_migration();
        }
        
        private MigrationBuilder return_to_migration() {
            if (_migration_parent != null) return _migration_parent;
            if (_table_parent != null) return _table_parent.return_to_migration();
            if (_alter_parent != null) return _alter_parent.return_to_migration();
            assert_not_reached();
        }
    }
}
```

### Task 4: TableBuilder for CREATE TABLE

Create `src/migrations/table-builder.vala`:

```vala
using Invercargill.DataStructures;

namespace InvercargillSql.Migrations {
    
    public class TableBuilder : Object {
        private MigrationBuilder _parent;
        private string _table_name;
        private Vector<ColumnDefinition> _columns;
        private Vector<TableConstraint> _constraints;
        
        internal TableBuilder(MigrationBuilder parent, string table_name) {
            _parent = parent;
            _table_name = table_name;
            _columns = new Vector<ColumnDefinition>();
            _constraints = new Vector<TableConstraint>();
        }
        
        public MigrationColumnBuilder column<T>(string name) {
            var col = new ColumnDefinition() {
                name = name,
                column_type = ColumnType.from_gtype(typeof(T)) ?? ColumnType.TEXT
            };
            _columns.add(col);
            return new MigrationColumnBuilder.for_table(this, col);
        }
        
        public TableBuilder primary_key(string[] columns) {
            var constraint = new TableConstraint() {
                constraint_type = "PRIMARY KEY"
            };
            foreach (var col in columns) {
                constraint.columns.add(col);
            }
            _constraints.add(constraint);
            return this;
        }
        
        public TableBuilder unique(string name, string[] columns) {
            var constraint = new TableConstraint() {
                name = name,
                constraint_type = "UNIQUE"
            };
            foreach (var col in columns) {
                constraint.columns.add(col);
            }
            _constraints.add(constraint);
            return this;
        }
        
        public TableBuilder foreign_key(string name, string[] columns, 
                                        string ref_table, string[] ref_columns) {
            var constraint = new TableConstraint() {
                name = name,
                constraint_type = "FOREIGN KEY",
                reference_table = ref_table
            };
            foreach (var col in columns) {
                constraint.columns.add(col);
            }
            foreach (var col in ref_columns) {
                constraint.reference_columns.add(col);
            }
            _constraints.add(constraint);
            return this;
        }
        
        internal void add_column(ColumnDefinition col) {
            _columns.add(col);
        }
        
        internal MigrationBuilder return_to_migration() {
            return _parent;
        }
        
        internal CreateTableOperation build_operation() {
            var op = new CreateTableOperation() {
                table_name = _table_name
            };
            foreach (var col in _columns) {
                op.columns.add(col);
            }
            foreach (var constraint in _constraints) {
                op.constraints.add(constraint);
            }
            return op;
        }
    }
}
```

### Task 5: AlterTableBuilder for ALTER TABLE

Create `src/migrations/alter-table-builder.vala`:

```vala
using Invercargill.DataStructures;

namespace InvercargillSql.Migrations {
    
    public class AlterTableBuilder : Object {
        private MigrationBuilder _parent;
        private string _table_name;
        private Vector<SchemaOperation> _operations;
        
        internal AlterTableBuilder(MigrationBuilder parent, string table_name) {
            _parent = parent;
            _table_name = table_name;
            _operations = new Vector<SchemaOperation>();
        }
        
        public MigrationColumnBuilder add_column<T>(string name) {
            var col = new ColumnDefinition() {
                name = name,
                column_type = ColumnType.from_gtype(typeof(T)) ?? ColumnType.TEXT
            };
            return new MigrationColumnBuilder.for_alter(this, _table_name, col);
        }
        
        internal void add_column_operation(ColumnDefinition col) {
            _operations.add(new AddColumnOperation() {
                table_name = _table_name,
                column = col
            });
        }
        
        public AlterTableBuilder drop_column(string name) {
            _operations.add(new DropColumnOperation() {
                table_name = _table_name,
                column_name = name
            });
            return this;
        }
        
        public AlterTableBuilder rename_column(string old_name, string new_name) {
            _operations.add(new RenameColumnOperation() {
                table_name = _table_name,
                old_name = old_name,
                new_name = new_name
            });
            return this;
        }
        
        internal MigrationBuilder return_to_migration() {
            return _parent;
        }
        
        internal Vector<SchemaOperation> get_operations() {
            return _operations;
        }
    }
}
```

### Task 6: MigrationBuilder

Create `src/migrations/migration-builder.vala`:

```vala
using Invercargill.DataStructures;

namespace InvercargillSql.Migrations {
    
    public delegate void TableConfig(TableBuilder t);
    public delegate void AlterConfig(AlterTableBuilder t);
    
    public class MigrationBuilder : Object {
        private SqlDialect _dialect;
        private Vector<SchemaOperation> _operations;
        
        public MigrationBuilder(SqlDialect dialect) {
            _dialect = dialect;
            _operations = new Vector<SchemaOperation>();
        }
        
        public MigrationBuilder create_table(string name, owned TableConfig config) {
            var builder = new TableBuilder(this, name);
            config(builder);
            _operations.add(builder.build_operation());
            return this;
        }
        
        public MigrationBuilder drop_table(string name) {
            _operations.add(new DropTableOperation() { table_name = name });
            return this;
        }
        
        public MigrationBuilder alter_table(string name, owned AlterConfig config) {
            var builder = new AlterTableBuilder(this, name);
            config(builder);
            foreach (var op in builder.get_operations()) {
                _operations.add(op);
            }
            return this;
        }
        
        public MigrationBuilder create_index(string name, string table, string[] columns, bool unique = false) {
            var op = new CreateIndexOperation() {
                index_name = name,
                table_name = table,
                is_unique = unique
            };
            foreach (var col in columns) {
                op.columns.add(col);
            }
            _operations.add(op);
            return this;
        }
        
        public MigrationBuilder drop_index(string name, string table = "") {
            _operations.add(new DropIndexOperation() {
                index_name = name,
                table_name = table
            });
            return this;
        }
        
        public MigrationBuilder execute_sql(string sql) {
            _operations.add(new RawSqlOperation() { sql = sql });
            return this;
        }
        
        internal void add_column_from_builder(string table_name, ColumnDefinition col) {
            _operations.add(new AddColumnOperation() {
                table_name = table_name,
                column = col
            });
        }
        
        public Vector<SchemaOperation> get_operations() {
            return _operations;
        }
        
        public void execute(Connection conn) throws SqlError {
            foreach (var op in _operations) {
                var sql = op.to_sql(_dialect);
                conn.execute(sql);
            }
        }
    }
}
```

### Task 7: MigrationRunner

Create `src/migrations/migration-runner.vala`:

```vala
using Invercargill.DataStructures;

namespace InvercargillSql.Migrations {
    
    public class MigrationRunner : Object {
        private Connection _connection;
        private SqlDialect _dialect;
        private Vector<Migration> _migrations;
        
        public MigrationRunner(Connection connection, SqlDialect dialect) {
            _connection = connection;
            _dialect = dialect;
            _migrations = new Vector<Migration>();
        }
        
        public void register<T>() where T : Migration {
            var migration = Object.new(typeof(T)) as Migration;
            if (migration != null) {
                _migrations.add(migration);
            }
        }
        
        public void register_migration(Migration migration) {
            _migrations.add(migration);
        }
        
        private void ensure_migrations_table() throws SqlError {
            _connection.execute(@"
                CREATE TABLE IF NOT EXISTS __migrations (
                    version INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    applied_at INTEGER NOT NULL
                )
            ");
        }
        
        public Vector<int> get_applied_versions() throws SqlError {
            var versions = new Vector<int>();
            var results = _connection.create_command("SELECT version FROM __migrations ORDER BY version")
                .execute_query();
            
            foreach (var row in results) {
                var version = row.get("version")?.as_int_or_null();
                if (version != null) {
                    versions.add(version);
                }
            }
            return versions;
        }
        
        public int get_current_version() throws SqlError {
            ensure_migrations_table();
            var versions = get_applied_versions();
            return versions.is_empty ? 0 : versions.last();
        }
        
        public Vector<Migration> get_pending_migrations() throws SqlError {
            ensure_migrations_table();
            var applied = get_applied_versions();
            var pending = new Vector<Migration>();
            
            foreach (var migration in _migrations) {
                if (!applied.contains(migration.version)) {
                    pending.add(migration);
                }
            }
            
            // Sort by version
            pending.sort((a, b) => a.version - b.version);
            return pending;
        }
        
        public void migrate_to_latest() throws SqlError {
            ensure_migrations_table();
            var pending = get_pending_migrations();
            
            foreach (var migration in pending) {
                apply_migration(migration);
            }
        }
        
        public void migrate_to(int target_version) throws SqlError {
            ensure_migrations_table();
            var current = get_current_version();
            
            if (target_version > current) {
                // Apply migrations up to target
                foreach (var migration in _migrations) {
                    if (migration.version > current && migration.version <= target_version) {
                        apply_migration(migration);
                    }
                }
            } else if (target_version < current) {
                // Rollback migrations down to target
                var applied = get_applied_versions();
                for (int i = applied.size - 1; i >= 0 && applied[i] > target_version; i--) {
                    var migration = find_migration(applied[i]);
                    if (migration != null) {
                        revert_migration(migration);
                    }
                }
            }
        }
        
        public void rollback(int steps = 1) throws SqlError {
            ensure_migrations_table();
            var applied = get_applied_versions();
            
            int count = 0;
            for (int i = applied.size - 1; i >= 0 && count < steps; i--, count++) {
                var migration = find_migration(applied[i]);
                if (migration != null) {
                    revert_migration(migration);
                }
            }
        }
        
        public void rollback_all() throws SqlError {
            ensure_migrations_table();
            var applied = get_applied_versions();
            
            for (int i = applied.size - 1; i >= 0; i--) {
                var migration = find_migration(applied[i]);
                if (migration != null) {
                    revert_migration(migration);
                }
            }
        }
        
        private void apply_migration(Migration migration) throws SqlError {
            var builder = new MigrationBuilder(_dialect);
            migration.up(builder);
            
            var transaction = _connection.begin_transaction();
            try {
                builder.execute(_connection);
                
                // Record migration
                var now = new DateTime.now_utc().to_unix();
                _connection.create_command(
                    "INSERT INTO __migrations (version, name, applied_at) VALUES (:version, :name, :applied_at)"
                )
                .with_parameter("version", migration.version)
                .with_parameter("name", migration.name)
                .with_parameter("applied_at", now)
                .execute_non_query();
                
                transaction.commit();
            } catch (SqlError e) {
                transaction.rollback();
                throw e;
            }
        }
        
        private void revert_migration(Migration migration) throws SqlError {
            var builder = new MigrationBuilder(_dialect);
            migration.down(builder);
            
            var transaction = _connection.begin_transaction();
            try {
                builder.execute(_connection);
                
                // Remove migration record
                _connection.create_command("DELETE FROM __migrations WHERE version = :version")
                    .with_parameter("version", migration.version)
                    .execute_non_query();
                
                transaction.commit();
            } catch (SqlError e) {
                transaction.rollback();
                throw e;
            }
        }
        
        private Migration? find_migration(int version) {
            foreach (var migration in _migrations) {
                if (migration.version == version) {
                    return migration;
                }
            }
            return null;
        }
    }
}
```

### Task 8: Extend SqlDialect Interface

Add to `src/dialects/sql-dialect.vala`:

```vala
public interface SqlDialect : Object {
    // ... existing methods ...
    
    // Migration DDL methods
    public abstract string create_table_sql(CreateTableOperation op);
    public abstract string drop_table_sql(DropTableOperation op);
    public abstract string add_column_sql(AddColumnOperation op);
    public abstract string drop_column_sql(DropColumnOperation op);
    public abstract string rename_column_sql(RenameColumnOperation op);
    public abstract string create_index_sql(CreateIndexOperation op);
    public abstract string drop_index_sql(DropIndexOperation op);
}
```

### Task 9: Implement SqliteDialect DDL Methods

Add to `src/dialects/sqlite-dialect.vala`:

```vala
public class SqliteDialect : Object, SqlDialect {
    // ... existing methods ...
    
    public string create_table_sql(CreateTableOperation op) {
        var sql = new StringBuilder();
        sql.append("CREATE TABLE ");
        sql.append(op.table_name);
        sql.append(" (\n");
        
        var parts = new Vector<string>();
        
        foreach (var col in op.columns) {
            parts.add(build_column_sql(col));
        }
        
        foreach (var constraint in op.constraints) {
            parts.add(build_constraint_sql(constraint));
        }
        
        for (int i = 0; i < parts.size; i++) {
            sql.append("    ");
            sql.append(parts[i]);
            if (i < parts.size - 1) {
                sql.append(",");
            }
            sql.append("\n");
        }
        
        sql.append(")");
        return sql.str;
    }
    
    private string build_column_sql(ColumnDefinition col) {
        var parts = new Vector<string>();
        parts.add(col.name);
        parts.add(translate_type(col.column_type));
        
        if (col.is_primary_key) parts.add("PRIMARY KEY");
        if (col.auto_increment) parts.add("AUTOINCREMENT");
        if (col.is_required) parts.add("NOT NULL");
        if (col.is_unique) parts.add("UNIQUE");
        if (col.default_value != null) {
            parts.add("DEFAULT " + element_to_sql(col.default_value));
        }
        if (col.default_now) {
            parts.add("DEFAULT (strftime('%s', 'now'))");
        }
        
        return string.joinv(" ", parts.to_array());
    }
    
    public string drop_table_sql(DropTableOperation op) {
        return "DROP TABLE IF EXISTS " + op.table_name;
    }
    
    public string add_column_sql(AddColumnOperation op) {
        return "ALTER TABLE " + op.table_name + " ADD COLUMN " + build_column_sql(op.column);
    }
    
    public string drop_column_sql(DropColumnOperation op) {
        // SQLite 3.35.0+ supports DROP COLUMN
        return "ALTER TABLE " + op.table_name + " DROP COLUMN " + op.column_name;
    }
    
    public string rename_column_sql(RenameColumnOperation op) {
        return "ALTER TABLE " + op.table_name + " RENAME COLUMN " + op.old_name + " TO " + op.new_name;
    }
    
    public string create_index_sql(CreateIndexOperation op) {
        var sql = new StringBuilder();
        sql.append(op.is_unique ? "CREATE UNIQUE INDEX " : "CREATE INDEX ");
        sql.append(op.index_name);
        sql.append(" ON ");
        sql.append(op.table_name);
        sql.append(" (");
        sql.append(string.joinv(", ", op.columns.to_array()));
        sql.append(")");
        return sql.str;
    }
    
    public string drop_index_sql(DropIndexOperation op) {
        return "DROP INDEX IF EXISTS " + op.index_name;
    }
}
```

---

## Dependencies

- `Invercargill.DataStructures` - `Vector<T>`, `Dictionary<K,V>`
- `Invercargill` - `Element`, `NativeElement<T>`
- Phase 1 ORM components - `SqlDialect`, `ColumnType`, `ColumnDefinition`

---

## Example Usage (Target API)

```vala
// Define migration
public class V001_CreateUsers : Migration {
    public override int version { get { return 1; } }
    public override string name { get { return "CreateUsers"; } }
    
    public override void up(MigrationBuilder b) {
        b.create_table("users", t => {
            t.column<int64>("id")
                .primary_key()
                .auto_increment();
            t.column<string>("name")
                .not_null();
            t.column<string>("email")
                .unique();
            t.column<DateTime>("created_at")
                .default_now();
        });
        
        b.create_index("idx_users_email", "users", {"email"});
    }
    
    public override void down(MigrationBuilder b) {
        b.drop_index("idx_users_email");
        b.drop_table("users");
    }
}

// Run migrations
var runner = new MigrationRunner(conn, new SqliteDialect());
runner.register<V001_CreateUsers>();
runner.register<V002_AddStatus>();
runner.migrate_to_latest();
```