"""plot_inquiries: public plot availability inquiry capture (INDL-31)

Revision ID: 0044
Revises: 0043
Create Date: 2026-07-02
"""
from alembic import op
import sqlalchemy as sa

revision = "0044"
down_revision = "0043"
branch_labels = None
depends_on = None


def upgrade() -> None:
    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:
    op.execute("DROP TABLE IF EXISTS plot_inquiries")