"""plot_groups + plot_group_members (INDL-41 grave grouping)

Revision ID: 0046
Revises: 0045
Create Date: 2026-07-02

Owned by INDL-41 (Cemetery Map — Core View), editor-mode grave grouping (AC-20).
The PRD attributes these tables to migration 0041, but that number was already
taken in this codebase, so they are created here instead.

All DDL is idempotent (IF NOT EXISTS).
"""
from alembic import op
import sqlalchemy as sa

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


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

    conn.execute(sa.text(
        """
        CREATE TABLE IF NOT EXISTS plot_groups (
            id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            tenant_id   UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
            section_id  UUID NULL REFERENCES sections(id) ON DELETE SET NULL,
            group_type  VARCHAR(20)  NOT NULL,
            label       VARCHAR(255) NULL,
            capacity    INTEGER      NOT NULL DEFAULT 2,
            boundary    geography(POLYGON,4326) 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_plot_groups_tenant_id ON plot_groups (tenant_id)"
    ))
    conn.execute(sa.text(
        "CREATE INDEX IF NOT EXISTS ix_plot_groups_section_id ON plot_groups (section_id)"
    ))

    conn.execute(sa.text(
        """
        CREATE TABLE IF NOT EXISTS plot_group_members (
            plot_group_id UUID NOT NULL REFERENCES plot_groups(id) ON DELETE CASCADE,
            plot_id       UUID NOT NULL REFERENCES plots(id) ON DELETE CASCADE,
            PRIMARY KEY (plot_group_id, plot_id)
        )
        """
    ))
    conn.execute(sa.text(
        "CREATE INDEX IF NOT EXISTS ix_plot_group_members_plot_id "
        "ON plot_group_members (plot_id)"
    ))


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