using InvercargillSql;
using InvercargillSql.Migrations;

/**
 * Migration to create the orders table with foreign keys and indexes.
 * 
 * Demonstrates the fluent FK and index API:
 * - .references() for foreign key constraints
 * - .indexed() for automatic index creation
 * - Chaining FK options like .name() and .on_delete_cascade()
 */
public class V003_CreateOrders : Migration {
    public override string migration_namespace { get { return "app"; } }
    public override uint64 serial { 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();
            
            // FK to users with explicit name and cascade delete
            // indexed() auto-generates idx_orders_user_id
            t.column<int64?>("user_id")
                .not_null()
                .references("users", "id")
                    .name("fk_orders_users")
                    .on_delete_cascade()
                .indexed();
            
            // FK to products with default name and restrict delete
            // indexed() auto-generates idx_orders_product_id
            t.column<int64?>("product_id")
                .not_null()
                .references("products", "id")
                    .on_delete_restrict()
                .indexed();
            
            t.column<int64?>("quantity");
            t.column<double?>("total");
            
            // Non-FK column with index for filtering
            // indexed() auto-generates idx_orders_status
            t.column<string>("status")
                .indexed();
            
            t.column<int64?>("created_at");
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("orders");
    }
}