"""cemetery map geometry: PostGIS + boundary/geometry columns on accounts, sections, plots

Revision ID: 0043
Revises: 0042a
Create Date: 2026-07-02

Owned by INDL-49 (Settings — Cemetery Map Boundary, Sections & Plot Grid Setup).

Adds the shared geospatial foundation that INDL-41 (Cemetery Map core view) and
INDL-42 (Add/Edit Plot) will later consume. INDL-41's PRD attributes these columns
to its migrations 0038-0041, but those numbers were already taken in this codebase
by unrelated work, so the geometry foundation INDL-49 depends on is created here
instead. All DDL is idempotent (IF NOT EXISTS) so INDL-41/42 migrations can be
authored defensively without colliding.

Changes:
  - CREATE EXTENSION IF NOT EXISTS postgis
  - accounts: boundary GEOGRAPHY(POLYGON,4326), centroid GEOGRAPHY(POINT,4326),
              map_mode VARCHAR(20) NOT NULL DEFAULT 'image', total_area_m2 NUMERIC(12,2)
  - sections: boundary GEOGRAPHY(POLYGON,4326), display_color VARCHAR(7),
              plot_number_prefix VARCHAR(10), next_plot_seq INTEGER NOT NULL DEFAULT 1,
              area_m2 NUMERIC(12,2)
  - plots:    geometry GEOGRAPHY(POLYGON,4326), centroid GEOGRAPHY(POINT,4326),
              rotation_deg NUMERIC(5,2)
  - GIST indexes on plots.geometry, sections.boundary, accounts.boundary
"""
from alembic import op
import sqlalchemy as sa

revision = "0047"
down_revision = "0046"
branch_labels = None
depends_on = None


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

    conn.execute(sa.text("CREATE EXTENSION IF NOT EXISTS postgis"))

    # ── accounts (the cemetery itself) ──────────────────────────────────────────
    conn.execute(sa.text(
        "ALTER TABLE accounts ADD COLUMN IF NOT EXISTS boundary geography(POLYGON,4326) NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE accounts ADD COLUMN IF NOT EXISTS centroid geography(POINT,4326) NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE accounts ADD COLUMN IF NOT EXISTS map_mode VARCHAR(20) NOT NULL DEFAULT 'image'"
    ))
    conn.execute(sa.text(
        "ALTER TABLE accounts ADD COLUMN IF NOT EXISTS total_area_m2 NUMERIC(12,2) NULL"
    ))

    # ── sections ────────────────────────────────────────────────────────────────
    conn.execute(sa.text(
        "ALTER TABLE sections ADD COLUMN IF NOT EXISTS boundary geography(POLYGON,4326) NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE sections ADD COLUMN IF NOT EXISTS display_color VARCHAR(7) NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE sections ADD COLUMN IF NOT EXISTS plot_number_prefix VARCHAR(10) NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE sections ADD COLUMN IF NOT EXISTS next_plot_seq INTEGER NOT NULL DEFAULT 1"
    ))
    conn.execute(sa.text(
        "ALTER TABLE sections ADD COLUMN IF NOT EXISTS area_m2 NUMERIC(12,2) NULL"
    ))

    # ── plots ─────────────────────────────────────────────────────────────────--
    conn.execute(sa.text(
        "ALTER TABLE plots ADD COLUMN IF NOT EXISTS geometry geography(POLYGON,4326) NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE plots ADD COLUMN IF NOT EXISTS centroid geography(POINT,4326) NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE plots ADD COLUMN IF NOT EXISTS rotation_deg NUMERIC(5,2) NULL"
    ))

    # ── spatial + composite indexes ─────────────────────────────────────────────
    conn.execute(sa.text(
        "CREATE INDEX IF NOT EXISTS ix_plots_geometry ON plots USING GIST (geometry)"
    ))
    conn.execute(sa.text(
        "CREATE INDEX IF NOT EXISTS ix_sections_boundary ON sections USING GIST (boundary)"
    ))
    conn.execute(sa.text(
        "CREATE INDEX IF NOT EXISTS ix_accounts_boundary ON accounts USING GIST (boundary)"
    ))


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

    conn.execute(sa.text("DROP INDEX IF EXISTS ix_accounts_boundary"))
    conn.execute(sa.text("DROP INDEX IF EXISTS ix_sections_boundary"))
    conn.execute(sa.text("DROP INDEX IF EXISTS ix_plots_geometry"))

    for col in ("rotation_deg", "centroid", "geometry"):
        conn.execute(sa.text(f"ALTER TABLE plots DROP COLUMN IF EXISTS {col}"))
    for col in ("area_m2", "next_plot_seq", "plot_number_prefix", "display_color", "boundary"):
        conn.execute(sa.text(f"ALTER TABLE sections DROP COLUMN IF EXISTS {col}"))
    for col in ("total_area_m2", "map_mode", "centroid", "boundary"):
        conn.execute(sa.text(f"ALTER TABLE accounts DROP COLUMN IF EXISTS {col}"))

    # postgis extension intentionally left installed on downgrade