# Phase 7: Foreign Key and Index Support for Migrations

## Overview

Add comprehensive foreign key and simplified index support to the migration/schema system with a fluent API that allows defining FK constraints and indexes inline with column definitions.

## Design Goals

1. **Fluent column-level API** - Define FKs and indexes directly on column builder
2. **Method order independence** - Chain methods in any order
3. **Automatic name generation** - Generate constraint/index names when not explicitly provided
4. **ALTER TABLE support** - Add FKs/indexes when adding columns, drop by name or column
5. **Cross-dialect support** - Handle SQLite limitations transparently
6. **Consistent API** - FKs and indexes follow the same patterns

## API Design

### Column-Level Foreign Key API

The primary API for defining foreign keys is inline with column definitions:

```vala
// Basic FK with auto-generated name
t.column<int64?>("user_id")
    .not_null()
    .references("users", "id");

// Full FK with all options
t.column<int64?>("user_id")
    .references("users", "id")
        .name("fk_orders_users")           // optional, auto-generated if omitted
        .on_delete_cascade()               // ON DELETE CASCADE
        .on_delete_set_null()              // ON DELETE SET NULL
        .on_delete_set_default()           // ON DELETE SET DEFAULT
        .on_delete_no_action()             // ON DELETE NO ACTION (default)
        .on_delete_restrict()              // ON DELETE RESTRICT
        .on_update_cascade()               // ON UPDATE CASCADE
        .on_update_set_null()              // etc.
        .on_update_set_default()
        .on_update_no_action()             // default
        .on_update_restrict()
    .not_null();                           // Can continue with column methods
```

### Column-Level Index API

For simple single-column indexes, use `.indexed()` directly on the column builder:

```vala
// Basic index with auto-generated name (idx_{table}_{column})
t.column<string>("email")
    .not_null()
    .indexed();

// Unique index - uniqueness inferred from column's unique() flag
t.column<string>("email")
    .not_null()
    .unique()
    .indexed();  // Creates UNIQUE index

// Index with custom name
t.column<string>("email")
    .indexed("idx_users_email_address");
```

**Key Design Points:**
- `indexed(string? name = null)` returns `MigrationColumnBuilder` (same type, allows chaining)
- Uniqueness is automatically inferred from the column's `.unique()` flag
- No separate builder class needed - keeps the API simple
- The existing `t.index("name").on_column("col")` API remains for:
  - Multi-column composite indexes
  - More complex index configurations
  - Explicit control over index definition order

### Key Design Principle: Inheritance Chain

The object returned by `.references()` must **inherit from MigrationColumnBuilder** so that:
- All column methods remain available after FK configuration
- Method order is flexible (`.not_null()` before or after `.references()`)
- The API feels natural and discoverable

For `.indexed()`, it simply returns `this` (MigrationColumnBuilder) since no additional configuration is needed.

### ALTER TABLE Support

```vala
// Add column with FK
b.alter_table("orders", t => {
    t.add_column<int64?>("product_id")
        .references("products", "id")
        .on_delete_restrict();
});

// Add column with index
b.alter_table("orders", t => {
    t.add_column<string>("sku")
        .unique()
        .indexed();  // Creates UNIQUE index (uniqueness from column)
});

// Drop FK by constraint name
b.alter_table("orders", t => {
    t.drop_foreign_key("fk_orders_users");
});

// Drop FK by column (useful for auto-generated names)
b.alter_table("orders", t => {
    t.drop_foreign_key_on("user_id");
});

// Drop index by column (useful for auto-generated names)
b.alter_table("orders", t => {
    t.drop_index_on("sku");
});
```

### Default Values

- **ON DELETE**: NO ACTION (SQL standard default)
- **ON UPDATE**: NO ACTION (SQL standard default)
- **Constraint name**: Auto-generated as `fk_{table}_{column}` if not specified

## Architecture

### Class Hierarchy

```
MigrationColumnBuilder
    └── ForeignKeyBuilder (extends MigrationColumnBuilder)
            - Stores FK configuration
            - All column methods pass through to parent
            - FK-specific methods return this

Note: indexed() does NOT need a separate builder - it returns this (MigrationColumnBuilder)
```

### New Classes/Files

1. **ForeignKeyBuilder** (`src/migrations/foreign-key-builder.vala`)
   - Extends MigrationColumnBuilder
   - Stores: referenced table, referenced column, constraint name, on_delete, on_update
   - Provides fluent FK methods

2. **DropForeignKeyOperation** (in `schema-operations.vala`)
   - Schema operation for dropping FK constraints
   - Properties: constraint_name OR column_name (for lookup)

### Modified Classes

1. **MigrationColumnBuilder**
   - Add `references(table, column)` method returning ForeignKeyBuilder
   - Add `indexed(string? name = null)` method returning `this`
   - Add FK constraint storage
   - Add index flag storage (index_name, should_create_index)

2. **TableBuilder**
   - Collect FK constraints from column builders
   - Collect index operations from column builders
   - Pass to CreateTableOperation

3. **AlterTableBuilder**
   - Add `drop_foreign_key(name)` method
   - Add `drop_foreign_key_on(column)` method
   - Add `drop_index_on(column)` method

4. **TableConstraint**
   - Add `on_delete_action` property
   - Add `on_update_action` property

5. **SqlDialect Interface**
   - Add `drop_foreign_key_sql(DropForeignKeyOperation op)` method

6. **SqliteDialect**
   - Update `build_constraint_sql()` to include ON DELETE/UPDATE
   - Implement `drop_foreign_key_sql()` with table recreation

## SQLite Table Recreation Strategy

SQLite does not support `ALTER TABLE DROP CONSTRAINT`. The dialect must handle this transparently:

```sql
-- Generated SQL for dropping FK on SQLite:
BEGIN TRANSACTION;

CREATE TABLE _orders_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    -- ... other columns without FK constraint
);

INSERT INTO _orders_new SELECT * FROM orders;

DROP TABLE orders;

ALTER TABLE _orders_new RENAME TO orders;

-- Recreate indexes
CREATE INDEX idx_orders_user_id ON orders (user_id);

COMMIT;
```

### Implementation Approach

1. `drop_foreign_key_sql()` returns a single string with multiple statements separated by semicolons and newlines
2. The method needs schema introspection to:
   - Get all columns and their types
   - Get remaining constraints (PK, UNIQUE, other FKs)
   - Get all indexes on the table
3. Connection.execute() already handles multi-statement SQL

## Referential Actions Enum

```vala
public enum ReferentialAction {
    NO_ACTION,    // default
    CASCADE,
    SET_NULL,
    SET_DEFAULT,
    RESTRICT;
    
    public string to_sql() {
        switch (this) {
            case CASCADE: return "CASCADE";
            case SET_NULL: return "SET NULL";
            case SET_DEFAULT: return "SET DEFAULT";
            case RESTRICT: return "RESTRICT";
            default: return "NO ACTION";
        }
    }
}
```

## Implementation Tasks

### Task 1: Create ReferentialAction Enum
- [ ] Add enum to `src/migrations/schema-operations.vala`
- [ ] Include `to_sql()` method for SQL generation

### Task 2: Update TableConstraint Class
- [ ] Add `on_delete_action` property (ReferentialAction, default NO_ACTION)
- [ ] Add `on_update_action` property (ReferentialAction, default NO_ACTION)

### Task 3: Create ForeignKeyBuilder Class
- [ ] Create `src/migrations/foreign-key-builder.vala`
- [ ] Extend MigrationColumnBuilder
- [ ] Store FK configuration (ref_table, ref_column, name, on_delete, on_update)
- [ ] Implement fluent methods: `name()`, `on_delete_*()`, `on_update_*()`
- [ ] Override/extend to pass column methods to parent

### Task 4: Update MigrationColumnBuilder for Index Support
- [ ] Add index flag storage (index_name, should_create_index)
- [ ] Implement `indexed(string? name = null)` returning `this`
- [ ] Ensure index operation is created when column is built (use column's is_unique for index uniqueness)

### Task 5: Update MigrationColumnBuilder for FK Support
- [ ] Add FK constraint storage
- [ ] Implement `references(table, column)` returning ForeignKeyBuilder
- [ ] Ensure FK constraint is passed to parent builder on build

### Task 6: Update TableBuilder
- [ ] Collect FK constraints from column definitions
- [ ] Collect index operations from column definitions
- [ ] Add to CreateTableOperation constraints list
- [ ] Add index operations to migration

### Task 7: Update AlterTableBuilder
- [ ] Implement `drop_foreign_key(string name)` method
- [ ] Implement `drop_foreign_key_on(string column)` method
- [ ] Implement `drop_index_on(string column)` method
- [ ] Create DropForeignKeyOperation for FK drops

### Task 8: Create DropForeignKeyOperation
- [ ] Add to `src/migrations/schema-operations.vala`
- [ ] Properties: table_name, constraint_name (optional), column_name (optional)

### Task 9: Update SqlDialect Interface
- [ ] Add `drop_foreign_key_sql(DropForeignKeyOperation op)` abstract method

### Task 10: Update SqliteDialect
- [ ] Update `build_constraint_sql()` to emit ON DELETE/UPDATE clauses
- [ ] Implement `drop_foreign_key_sql()` with table recreation logic
- [ ] Implement helper to introspect current table schema for recreation

### Task 11: Update Example Migrations
- [ ] Update `v003-create-orders.vala` to use new FK API and `.indexed()`
- [ ] Add example of ALTER TABLE with FK

### Task 12: Add Tests
- [ ] Test FK creation with auto-generated name
- [ ] Test FK creation with explicit name
- [ ] Test all referential actions
- [ ] Test FK in ALTER TABLE add column
- [ ] Test drop FK by name
- [ ] Test drop FK by column
- [ ] Test SQLite table recreation for drop FK
- [ ] Test `.indexed()` with auto-generated name
- [ ] Test `.indexed("custom_name")` with custom name
- [ ] Test that `.unique().indexed()` creates UNIQUE index
- [ ] Test `drop_index_on()`

## Example Usage

### Complete Migration Example

```vala
public class V003_CreateOrders : Migration {
    public override int version { get { return 3; } }
    public override string name { get { return "CreateOrders"; } }
    
    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")
                    .name("fk_orders_users")
                    .on_delete_cascade()
                .indexed();  // Auto-generates idx_orders_user_id
            
            t.column<int64?>("product_id")
                .not_null()
                .references("products", "id")
                    .on_delete_restrict()
                .indexed();  // Auto-generates idx_orders_product_id
            
            t.column<int64?>("quantity");
            t.column<double?>("total");
            t.column<string>("status")
                .indexed();  // Auto-generates idx_orders_status
            t.column<int64?>("created_at");
            
            // Multi-column indexes still use the existing API:
            // t.index("idx_composite").on_columns("col1", "col2");
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("orders");
    }
}
```

### ALTER TABLE Example

```vala
public class V004_AddCategoryToProducts : Migration {
    public override int version { get { return 4; } }
    public override string name { get { return "AddCategoryToProducts"; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        // First create the categories table
        b.create_table("categories", t => {
            t.column<int64?>("id")
                .primary_key()
                .auto_increment();
            t.column<string>("name")
                .not_null();
        });
        
        // Then add the FK column to products
        b.alter_table("products", t => {
            t.add_column<int64?>("category_id")
                .references("categories", "id")
                .on_delete_set_null();  // Keep products when category deleted
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.alter_table("products", t => {
            t.drop_foreign_key_on("category_id");
            t.drop_column("category_id");
        });
        b.drop_table("categories");
    }
}
```

## Generated SQL Examples

### CREATE TABLE with FK

```sql
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER,
    total REAL,
    status TEXT,
    created_at INTEGER,
    CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_orders_products FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT
)
```

### ALTER TABLE ADD COLUMN with FK

```sql
ALTER TABLE products ADD COLUMN category_id INTEGER REFERENCES categories (id) ON DELETE SET NULL
```

### DROP FK on PostgreSQL/MySQL

```sql
ALTER TABLE orders DROP CONSTRAINT fk_orders_users
```

### DROP FK on SQLite

```sql
BEGIN TRANSACTION;
CREATE TABLE _orders_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER,
    total REAL,
    status TEXT,
    created_at INTEGER,
    CONSTRAINT fk_orders_products FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT
);
INSERT INTO _orders_new SELECT id, user_id, product_id, quantity, total, status, created_at FROM orders;
DROP TABLE orders;
ALTER TABLE _orders_new RENAME TO orders;
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_product_id ON orders (product_id);
CREATE INDEX idx_orders_status ON orders (status);
COMMIT;
```

## Questions Resolved

1. **API Style**: Column-level fluent API (EF Core inspired)
2. **Method Order**: Flexible - any order works due to inheritance
3. **Naming**: Optional `.name()` for FKs, optional name parameter for `.indexed(name)`; auto-generate as `fk_{table}_{column}` or `idx_{table}_{column}`
4. **Composite FKs**: Not in this phase (future enhancement)
5. **ALTER TABLE**: Full support for add/drop FKs and indexes
6. **Drop FK**: Both by name and by column
7. **Drop Index**: By column via `drop_index_on()`
8. **SQLite Handling**: Transparent table recreation in dialect layer
9. **Default Actions**: NO ACTION for both ON DELETE and ON UPDATE
10. **Index API**: Simple `.indexed(string? name = null)` returning same builder; uniqueness inferred from column's `.unique()` flag