"""Promote tables from JSON blob to first-class DB entities (zone, floor_table, chair). Add nullable table_id and combination_id FK columns to reservation. Migrate existing settings.floor_plan.tables JSON into zone/floor_table/chair rows. Revision ID: 0005_table_entity_promotion Revises: 0004_service_blocks Create Date: 2026-02-24 """ from collections.abc import Sequence import sqlalchemy as sa import sqlmodel from alembic import op revision: str = "0005_table_entity_promotion" down_revision: str | None = "0004_service_blocks" branch_labels: str | Sequence[str] | None = None depends_on: str | Sequence[str] | None = None def upgrade() -> None: # ── (a) Create zone table ──────────────────────────────────────────────── op.create_table( "zone", sa.Column("restaurant_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=True), sa.Column("name", sqlmodel.sql.sqltypes.AutoString(), nullable=False), sa.Column("display_order", sa.Integer(), nullable=False), sa.Column("icon", sqlmodel.sql.sqltypes.AutoString(), nullable=True), sa.Column("id", sqlmodel.sql.sqltypes.AutoString(), nullable=False), sa.ForeignKeyConstraint(["restaurant_id"], ["restaurant.id"]), sa.PrimaryKeyConstraint("id"), ) op.create_index(op.f("ix_zone_restaurant_id"), "zone", ["restaurant_id"], unique=False) # ── (b) Create floor_table table ───────────────────────────────────────── op.create_table( "floor_table", sa.Column("restaurant_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=True), sa.Column("label", sqlmodel.sql.sqltypes.AutoString(), nullable=False), sa.Column("x", sa.Float(), nullable=False), sa.Column("y", sa.Float(), nullable=False), sa.Column("width", sa.Float(), nullable=False), sa.Column("height", sa.Float(), nullable=False), sa.Column("shape", sqlmodel.sql.sqltypes.AutoString(), nullable=False), sa.Column("rotation", sa.Float(), nullable=False), sa.Column("slot_count", sa.Integer(), nullable=False), sa.Column("zone_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False), sa.Column("id", sqlmodel.sql.sqltypes.AutoString(), nullable=False), sa.ForeignKeyConstraint(["restaurant_id"], ["restaurant.id"]), sa.ForeignKeyConstraint(["zone_id"], ["zone.id"]), sa.PrimaryKeyConstraint("id"), ) op.create_index( op.f("ix_floor_table_restaurant_id"), "floor_table", ["restaurant_id"], unique=False, ) op.create_index(op.f("ix_floor_table_zone_id"), "floor_table", ["zone_id"], unique=False) # ── (c) Create chair table ─────────────────────────────────────────────── op.create_table( "chair", sa.Column("slot_index", sa.Integer(), nullable=False), sa.Column("side", sqlmodel.sql.sqltypes.AutoString(), nullable=False), sa.Column("enabled", sa.Boolean(), nullable=False), sa.Column("table_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False), sa.Column("id", sqlmodel.sql.sqltypes.AutoString(), nullable=False), sa.ForeignKeyConstraint(["table_id"], ["floor_table.id"]), sa.PrimaryKeyConstraint("id"), ) op.create_index(op.f("ix_chair_table_id"), "chair", ["table_id"], unique=False) # ── (d) Add nullable FKs to reservation ────────────────────────────────── op.add_column( "reservation", sa.Column("table_id", sqlmodel.sql.sqltypes.AutoString(), nullable=True), ) op.add_column( "reservation", sa.Column("combination_id", sqlmodel.sql.sqltypes.AutoString(), nullable=True), ) op.create_index(op.f("ix_reservation_table_id"), "reservation", ["table_id"], unique=False) op.create_foreign_key( "fk_reservation_table_id", "reservation", "floor_table", ["table_id"], ["id"], ) # ── (e) Migrate JSON blob data ─────────────────────────────────────────── # For each restaurant that has settings.floor_plan.tables JSON: # 1. Create a "Main" zone # 2. Insert a floor_table row per JSON entry (seats → slot_count) # 3. Generate chair rows based on shape + slot_count op.execute( """ DO $$ DECLARE r RECORD; t RECORD; zone_uuid TEXT; table_uuid TEXT; chair_uuid TEXT; slot_idx INTEGER; side_val TEXT; sides TEXT[]; top_count INTEGER; half INTEGER; BEGIN FOR r IN SELECT id AS restaurant_id, settings->'floor_plan'->'tables' AS tables_json FROM restaurant WHERE settings->'floor_plan'->'tables' IS NOT NULL AND jsonb_array_length(settings->'floor_plan'->'tables') > 0 LOOP -- Create default "Main" zone zone_uuid := gen_random_uuid()::TEXT; INSERT INTO zone (id, restaurant_id, name, display_order, created_at) VALUES (zone_uuid, r.restaurant_id, 'Main', 0, NOW()); -- Insert each table FOR t IN SELECT value AS tbl FROM jsonb_array_elements(r.tables_json) LOOP table_uuid := gen_random_uuid()::TEXT; INSERT INTO floor_table ( id, restaurant_id, zone_id, label, x, y, width, height, shape, rotation, slot_count, created_at ) VALUES ( table_uuid, r.restaurant_id, zone_uuid, COALESCE(t.tbl->>'label', 'Table'), COALESCE((t.tbl->>'x')::FLOAT, 0), COALESCE((t.tbl->>'y')::FLOAT, 0), COALESCE((t.tbl->>'width')::FLOAT, 80), COALESCE((t.tbl->>'height')::FLOAT, 80), COALESCE(t.tbl->>'shape', 'round'), COALESCE((t.tbl->>'rotation')::FLOAT, 0), GREATEST(COALESCE((t.tbl->>'seats')::INTEGER, 4), 1), NOW() ); -- Generate chairs based on shape + slot_count IF COALESCE(t.tbl->>'shape', 'round') = 'round' THEN FOR slot_idx IN 0..GREATEST(COALESCE((t.tbl->>'seats')::INTEGER, 4), 1) - 1 LOOP chair_uuid := gen_random_uuid()::TEXT; INSERT INTO chair (id, table_id, slot_index, side, enabled) VALUES (chair_uuid, table_uuid, slot_idx, 'around', TRUE); END LOOP; ELSIF COALESCE(t.tbl->>'shape', 'round') = 'square' THEN sides := ARRAY['top', 'right', 'bottom', 'left']; FOR slot_idx IN 0..GREATEST(COALESCE((t.tbl->>'seats')::INTEGER, 4), 1) - 1 LOOP chair_uuid := gen_random_uuid()::TEXT; side_val := sides[1 + (slot_idx % 4)]; INSERT INTO chair (id, table_id, slot_index, side, enabled) VALUES (chair_uuid, table_uuid, slot_idx, side_val, TRUE); END LOOP; ELSIF COALESCE(t.tbl->>'shape', 'round') = 'rectangle' THEN half := GREATEST(COALESCE((t.tbl->>'seats')::INTEGER, 4), 1) / 2; top_count := half + (GREATEST(COALESCE((t.tbl->>'seats')::INTEGER, 4), 1) % 2); FOR slot_idx IN 0..top_count - 1 LOOP chair_uuid := gen_random_uuid()::TEXT; INSERT INTO chair (id, table_id, slot_index, side, enabled) VALUES (chair_uuid, table_uuid, slot_idx, 'top', TRUE); END LOOP; FOR slot_idx IN 0..half - 1 LOOP chair_uuid := gen_random_uuid()::TEXT; INSERT INTO chair (id, table_id, slot_index, side, enabled) VALUES (chair_uuid, table_uuid, top_count + slot_idx, 'bottom', TRUE); END LOOP; ELSE -- Unknown shape: treat as round FOR slot_idx IN 0..GREATEST(COALESCE((t.tbl->>'seats')::INTEGER, 4), 1) - 1 LOOP chair_uuid := gen_random_uuid()::TEXT; INSERT INTO chair (id, table_id, slot_index, side, enabled) VALUES (chair_uuid, table_uuid, slot_idx, 'around', TRUE); END LOOP; END IF; END LOOP; END LOOP; END $$; """ ) def downgrade() -> None: # Remove reservation FK columns op.drop_constraint("fk_reservation_table_id", "reservation", type_="foreignkey") op.drop_index(op.f("ix_reservation_table_id"), table_name="reservation") op.drop_column("reservation", "combination_id") op.drop_column("reservation", "table_id") # Drop tables in reverse dependency order op.drop_index(op.f("ix_chair_table_id"), table_name="chair") op.drop_table("chair") op.drop_index(op.f("ix_floor_table_zone_id"), table_name="floor_table") op.drop_index(op.f("ix_floor_table_restaurant_id"), table_name="floor_table") op.drop_table("floor_table") op.drop_index(op.f("ix_zone_restaurant_id"), table_name="zone") op.drop_table("zone")