"""crew_members table (INDL-33 Crew Members)

Tenant-scoped roster of cemetery field staff with soft-delete support.
A partial unique index on (tenant_id, email) WHERE deleted_at IS NULL keeps
one active email per tenant while allowing email reuse after a soft-delete.

Chains off 0055 (the develop head after the email-templates migrations
0054-0055 were merged in). Idempotent (IF NOT EXISTS) so it is safe to re-run.
"""
from alembic import op
import sqlalchemy as sa

revision = "0056"
down_revision = "0055"
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text(
        """
        CREATE TABLE IF NOT EXISTS crew_members (
            id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            tenant_id     UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
            name          VARCHAR(255) NOT NULL,
            email         VARCHAR(255) NOT NULL,
            phone         VARCHAR(50) NOT NULL,
            address       TEXT NOT NULL,
            is_available  BOOLEAN NOT NULL DEFAULT TRUE,
            deleted_at    TIMESTAMPTZ NULL,
            created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
        )
        """
    ))
    conn.execute(sa.text(
        "CREATE INDEX IF NOT EXISTS ix_crew_members_tenant_id "
        "ON crew_members (tenant_id)"
    ))
    conn.execute(sa.text(
        "CREATE INDEX IF NOT EXISTS ix_crew_members_tenant_deleted "
        "ON crew_members (tenant_id, deleted_at)"
    ))
    conn.execute(sa.text(
        "CREATE UNIQUE INDEX IF NOT EXISTS uq_crew_members_tenant_email "
        "ON crew_members (tenant_id, email) WHERE deleted_at IS NULL"
    ))


def downgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("DROP TABLE IF EXISTS crew_members"))