"""Add deleted_at to services; create service_staff_assignments table

Revision ID: 0012
Revises: 0011
Create Date: 2026-06-25 00:00:00.000000

Changes:
  1. ALTER services — add deleted_at column (soft delete support).
  2. CREATE service_staff_assignments — crew/staff linked to a service event.
  3. Add indexes on service_id and tenant_id for service_staff_assignments.
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision: str = "0012"
down_revision: Union[str, None] = "0011"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


# ---------------------------------------------------------------------------
# upgrade
# ---------------------------------------------------------------------------


def upgrade() -> None:

    # ── 1. Add deleted_at to services ────────────────────────────────────────

    op.add_column(
        "services",
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
    )

    # ── 2. Create service_staff_assignments table ─────────────────────────────

    op.create_table(
        "service_staff_assignments",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
            nullable=False,
        ),
        sa.Column(
            "tenant_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("accounts.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "service_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("services.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "user_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("users.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("role", sa.String(50), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
    )

    # ── 3. Indexes ────────────────────────────────────────────────────────────

    op.create_index(
        "ix_service_staff_service_id",
        "service_staff_assignments",
        ["service_id"],
    )
    op.create_index(
        "ix_service_staff_tenant_id",
        "service_staff_assignments",
        ["tenant_id"],
    )


# ---------------------------------------------------------------------------
# downgrade
# ---------------------------------------------------------------------------


def downgrade() -> None:

    # ── Drop indexes ──────────────────────────────────────────────────────────

    op.drop_index("ix_service_staff_tenant_id", table_name="service_staff_assignments")
    op.drop_index("ix_service_staff_service_id", table_name="service_staff_assignments")

    # ── Drop table ────────────────────────────────────────────────────────────

    op.drop_table("service_staff_assignments")

    # ── Drop column ───────────────────────────────────────────────────────────

    op.drop_column("services", "deleted_at")