using FluentMigrator; namespace WmsLite.Api.Database.Migrations; [Migration(15, "Outbound allocation pick tasks")] public class OutboundAllocationPickTasks0015 : Migration { public override void Up() { if (!Schema.Table("outbound_pick_task").Exists()) { Create.Table("outbound_pick_task") .WithBaseColumns() .WithColumn("order_id").AsInt64().NotNullable() .WithColumn("order_line_id").AsInt64().NotNullable() .WithColumn("task_no").AsString(128).NotNullable() .WithColumn("inventory_id").AsInt64().NotNullable() .WithColumn("inventory_kind").AsInt32().NotNullable() .WithColumn("material_code").AsString(64).Nullable() .WithColumn("container_code").AsString(64).Nullable() .WithColumn("warehouse_code").AsString(64).NotNullable() .WithColumn("area_code").AsString(64).NotNullable() .WithColumn("zone_code").AsString(64).NotNullable() .WithColumn("location_code").AsString(64).NotNullable() .WithColumn("qty").AsDecimal().NotNullable() .WithColumn("picked_qty").AsDecimal().NotNullable().WithDefaultValue(0) .WithColumn("unit").AsString(32).NotNullable() .WithColumn("task_status").AsInt32().NotNullable() .WithColumn("attrs").AsCustom("jsonb").Nullable(); } Execute.Sql(""" ALTER TABLE outbound_order_line ALTER COLUMN location_code DROP NOT NULL; CREATE INDEX IF NOT EXISTS idx_outbound_pick_task_order ON outbound_pick_task (tenant_id, owner_code, order_id, task_no); CREATE INDEX IF NOT EXISTS idx_outbound_pick_task_line ON outbound_pick_task (tenant_id, owner_code, order_line_id); CREATE INDEX IF NOT EXISTS idx_outbound_pick_task_inventory ON outbound_pick_task (tenant_id, owner_code, inventory_id); CREATE UNIQUE INDEX IF NOT EXISTS uq_outbound_pick_task_no ON outbound_pick_task (tenant_id, owner_code, task_no) WHERE NOT is_deleted; """); } public override void Down() { Execute.Sql(""" DROP INDEX IF EXISTS uq_outbound_pick_task_no; DROP INDEX IF EXISTS idx_outbound_pick_task_inventory; DROP INDEX IF EXISTS idx_outbound_pick_task_line; DROP INDEX IF EXISTS idx_outbound_pick_task_order; """); if (Schema.Table("outbound_pick_task").Exists()) { Delete.Table("outbound_pick_task"); } Execute.Sql(""" UPDATE outbound_order_line SET location_code = 'DEFAULT' WHERE location_code IS NULL; ALTER TABLE outbound_order_line ALTER COLUMN location_code SET NOT NULL; """); } }