"""Backfill tenant_page_content / plot_inquiries (self-heal for envs stuck pre-0043)

0043 and 0044 were inserted into the migration chain after some environments'
alembic_version had already advanced past their revision IDs (via an
out-of-band DB provisioning/stamp step), so `alembic upgrade head` treated
those environments as already current and never ran their CREATE TABLE
statements. This migration re-asserts both tables with IF NOT EXISTS so any
environment missing them (dev, test, or otherwise) self-heals on next
`alembic upgrade head`. No-op on environments where they already exist.

Revision ID: 0069
Revises: 0068
Create Date: 2026-07-06
"""
from alembic import op

revision = "0069"
down_revision = "0068"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE IF NOT EXISTS tenant_page_content (
            id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
            tenant_id   UUID        NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
            page_slug   VARCHAR(20) NOT NULL
                        CHECK (page_slug IN ('global', 'find', 'availability', 'map', 'contact')),
            content     JSONB       NOT NULL DEFAULT '{}',
            created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
            updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
            updated_by  UUID        NULL REFERENCES users(id) ON DELETE SET NULL,

            CONSTRAINT uq_tenant_page UNIQUE (tenant_id, page_slug)
        )
        """
    )
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_tenant_page_content_tenant ON tenant_page_content (tenant_id)"
    )

    op.execute(
        """
        CREATE TABLE IF NOT EXISTS plot_inquiries (
            id                  UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
            tenant_id           UUID        NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
            plot_id             UUID        NULL REFERENCES plots(id) ON DELETE SET NULL,
            plot_ref            VARCHAR(20) NOT NULL,

            sender_name         VARCHAR(255) NOT NULL,
            sender_email        VARCHAR(255) NULL,
            sender_phone        VARCHAR(30)  NULL,
            relationship        VARCHAR(100) NOT NULL,
            preferred_contact_time VARCHAR(100) NULL,
            message             TEXT         NULL,

            status              VARCHAR(20)  NOT NULL DEFAULT 'new',
            reference_id        VARCHAR(30)  NOT NULL UNIQUE,
            converted_opportunity_id UUID    NULL REFERENCES opportunities(id) ON DELETE SET NULL,

            ip_address          INET         NULL,
            user_agent          TEXT         NULL,

            created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
            updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
        )
        """
    )
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_plot_inquiries_tenant_status ON plot_inquiries (tenant_id, status)"
    )
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_plot_inquiries_tenant_created ON plot_inquiries (tenant_id, created_at DESC)"
    )


def downgrade() -> None:
    # No-op: this migration only backfills tables owned by 0043/0044.
    # Dropping them here would be destructive on environments where they
    # were already correctly created by 0043/0044 rather than by this guard.
    pass