using InvercargillSql;
using InvercargillSql.Migrations;

/**
 * Migration to add category support to products.
 * 
 * Demonstrates ALTER TABLE with foreign keys:
 * - Creating a new reference table (categories)
 * - Adding an FK column to an existing table
 * - Using .on_delete_set_null() to preserve products when category is deleted
 * - Using drop_foreign_key_on() in down() for auto-generated FK names
 */
public class V004_AddCategory : Migration {
    public override int version { get { return 4; } }
    public override string name { get { return "AddCategory"; } }
    
    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
        // on_delete_set_null keeps products when their category is deleted
        b.alter_table("products", t => {
            t.add_column<int64?>("category_id")
                .references("categories", "id")
                .on_delete_set_null();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        // Drop the FK first using drop_foreign_key_on (handles auto-generated name)
        b.alter_table("products", t => {
            t.drop_foreign_key_on("category_id");
            t.drop_column("category_id");
        });
        b.drop_table("categories");
    }
}