"""Replace time_slot with service_block, add service_block_override, add reservation.end_time

Revision ID: 0004_service_blocks
Revises: 0003_high_priority_integrity
Create Date: 2026-02-24
"""

from collections.abc import Sequence

import sqlalchemy as sa
import sqlmodel
from sqlalchemy.dialects import postgresql

from alembic import op

revision: str = "0004_service_blocks"
down_revision: str | None = "0003_high_priority_integrity"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # --- (a) Create service_block table ---
    op.create_table(
        "service_block",
        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("day_of_week", sa.Integer(), nullable=False),
        sa.Column("name", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("block_type", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("start_time", sa.Time(), nullable=False),
        sa.Column("end_time", sa.Time(), nullable=False),
        sa.Column("max_covers", sa.Integer(), nullable=True),
        sa.Column("default_duration_minutes", sa.Integer(), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False),
        sa.Column("display_order", sa.Integer(), nullable=False),
        sa.Column("id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.ForeignKeyConstraint(["restaurant_id"], ["restaurant.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_service_block_restaurant_id"),
        "service_block",
        ["restaurant_id"],
        unique=False,
    )

    # --- Create service_block_override table ---
    op.create_table(
        "service_block_override",
        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("start_date", sa.Date(), nullable=False),
        sa.Column("end_date", sa.Date(), nullable=False),
        sa.Column("name", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("blocks", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False),
        sa.Column("id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.ForeignKeyConstraint(["restaurant_id"], ["restaurant.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_service_block_override_restaurant_id"),
        "service_block_override",
        ["restaurant_id"],
        unique=False,
    )

    # --- (b) Migrate existing time_slot rows to service_block rows ---
    op.execute(
        """
        INSERT INTO service_block (
            id, restaurant_id, created_at, updated_at,
            day_of_week, name, block_type, start_time, end_time,
            max_covers, default_duration_minutes, is_active, display_order
        )
        SELECT
            id, restaurant_id, created_at, updated_at,
            day_of_week,
            CASE
                WHEN start_time < '12:00' THEN 'Morning'
                WHEN start_time < '15:00' THEN 'Lunch'
                WHEN start_time < '18:00' THEN 'Afternoon'
                ELSE 'Dinner'
            END AS name,
            'open' AS block_type,
            start_time, end_time,
            max_covers,
            slot_duration_minutes AS default_duration_minutes,
            is_active,
            0 AS display_order
        FROM time_slot
        """
    )

    # --- (c) Add nullable end_time to reservation and backfill ---
    op.add_column("reservation", sa.Column("end_time", sa.DateTime(), nullable=True))
    op.execute(
        """
        UPDATE reservation
        SET end_time = reserved_at + INTERVAL '90 minutes'
        WHERE end_time IS NULL
        """
    )

    # --- (d) Drop time_slot table ---
    op.drop_index(op.f("ix_time_slot_restaurant_id"), table_name="time_slot")
    op.drop_table("time_slot")


def downgrade() -> None:
    # --- Recreate time_slot table ---
    op.create_table(
        "time_slot",
        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("day_of_week", sa.Integer(), nullable=False),
        sa.Column("start_time", sa.Time(), nullable=False),
        sa.Column("end_time", sa.Time(), nullable=False),
        sa.Column("max_covers", sa.Integer(), nullable=False),
        sa.Column("slot_duration_minutes", sa.Integer(), nullable=False),
        sa.Column("is_active", sa.Boolean(), nullable=False),
        sa.Column("id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.ForeignKeyConstraint(["restaurant_id"], ["restaurant.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_time_slot_restaurant_id"), "time_slot", ["restaurant_id"], unique=False
    )

    # --- Migrate service_block rows back to time_slot ---
    op.execute(
        """
        INSERT INTO time_slot (
            id, restaurant_id, created_at, updated_at,
            day_of_week, start_time, end_time,
            max_covers, slot_duration_minutes, is_active
        )
        SELECT
            id, restaurant_id, created_at, updated_at,
            day_of_week, start_time, end_time,
            COALESCE(max_covers, 0),
            COALESCE(default_duration_minutes, 90),
            is_active
        FROM service_block
        WHERE block_type = 'open'
        """
    )

    # --- Remove end_time from reservation ---
    op.drop_column("reservation", "end_time")

    # --- Drop new tables ---
    op.drop_index(
        op.f("ix_service_block_override_restaurant_id"),
        table_name="service_block_override",
    )
    op.drop_table("service_block_override")
    op.drop_index(op.f("ix_service_block_restaurant_id"), table_name="service_block")
    op.drop_table("service_block")