"""plot_statuses catalog + plot detail-panel columns (INDL-41)

Revision ID: 0045
Revises: 0044a
Create Date: 2026-07-02

Owned by INDL-41 (Cemetery Map — Core View). The PRD attributes the status
catalog to migration 0040, but that number was already taken in this codebase,
so it is created here instead.

Changes:
  - CREATE TABLE plot_statuses (tenant-configurable status catalog; AC-06/AC-17)
  - Seed the 4 default statuses (Available/Reserved/Interred/On hold) for every
    existing tenant, keyed to the canonical plots.status enum via status_key
  - plots: add reserved_by VARCHAR(255), reserved_at TIMESTAMPTZ, label_manual VARCHAR(50)
  - Composite index ix_plots_section_status on plots(section_id, status)

All DDL is idempotent (IF NOT EXISTS) so it is safe to re-run.
"""
from alembic import op
import sqlalchemy as sa

revision = "0050"
down_revision = "0049"
branch_labels = None
depends_on = None



# (name, color_hex, status_key, sort_order) — mirrors the frontend legend order.
_DEFAULT_STATUSES = [
    ("Available", "#16a34a", "vacant", 0),
    ("Reserved", "#d97706", "reserved", 1),
    ("Interred", "#4f46e5", "occupied", 2),
    ("On hold", "#64748b", "hold", 3),
]


def upgrade() -> None:
    conn = op.get_bind()

    conn.execute(sa.text(
        """
        CREATE TABLE IF NOT EXISTS plot_statuses (
            id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            tenant_id   UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
            name        VARCHAR(50) NOT NULL,
            color_hex   VARCHAR(7)  NOT NULL,
            status_key  VARCHAR(20) NULL,
            is_default  BOOLEAN     NOT NULL DEFAULT false,
            sort_order  SMALLINT    NOT NULL DEFAULT 0,
            created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
            CONSTRAINT uq_plot_statuses_tenant_name UNIQUE (tenant_id, name)
        )
        """
    ))
    conn.execute(sa.text(
        "CREATE INDEX IF NOT EXISTS ix_plot_statuses_tenant_id ON plot_statuses (tenant_id)"
    ))

    # ── plots detail-panel columns ───────────────────────────────────────────────
    conn.execute(sa.text(
        "ALTER TABLE plots ADD COLUMN IF NOT EXISTS reserved_by VARCHAR(255) NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE plots ADD COLUMN IF NOT EXISTS reserved_at TIMESTAMPTZ NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE plots ADD COLUMN IF NOT EXISTS label_manual VARCHAR(50) NULL"
    ))
    conn.execute(sa.text(
        "CREATE INDEX IF NOT EXISTS ix_plots_section_status ON plots (section_id, status)"
    ))

    # ── seed 4 defaults per existing tenant (idempotent via ON CONFLICT) ──────────
    for name, color_hex, status_key, sort_order in _DEFAULT_STATUSES:
        conn.execute(
            sa.text(
                """
                INSERT INTO plot_statuses
                    (tenant_id, name, color_hex, status_key, is_default, sort_order)
                SELECT id, :name, :color, :key, true, :sort
                FROM accounts
                ON CONFLICT (tenant_id, name) DO NOTHING
                """
            ),
            {"name": name, "color": color_hex, "key": status_key, "sort": sort_order},
        )


def downgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("DROP INDEX IF EXISTS ix_plots_section_status"))
    for col in ("label_manual", "reserved_at", "reserved_by"):
        conn.execute(sa.text(f"ALTER TABLE plots DROP COLUMN IF EXISTS {col}"))
    conn.execute(sa.text("DROP TABLE IF EXISTS plot_statuses"))