"""support: create support_tickets + support_ticket_messages tables (INDL-57)

Revision ID: 0074
Revises: 0073
Create Date: 2026-08-17

Adds the two tables backing the Support Tickets module:

  - support_tickets — one row per ticket, tenant-scoped. Uses the
    TenantModel shape (UUID pk, tenant_id, created_at, updated_at) with
    NO deleted_at — tickets are never deleted, only status-transitioned
    (open | in_progress | resolved | closed), matching the "no delete"
    pattern already used by website_enquiries / plot_inquiries.
  - support_ticket_messages — thread entries for a ticket. The initial
    ticket description is stored as message #1 (created in the same
    transaction as the ticket by the service layer). tenant_id is
    denormalized here (not derived via a join through ticket_id) so
    tenant-isolation queries and RLS-style filters can be applied
    directly on the messages table without joining back to
    support_tickets first.

  - support_ticket_seq — a platform-wide (not per-tenant) sequence used
    by the service layer to generate human-readable ticket numbers via
    nextval('support_ticket_seq'), formatted as SUP-{:06d} (e.g.
    SUP-000001). Created before the table so ticket_number could be
    server-defaulted from it, but the default is intentionally left to
    the application/service layer (SVC-09 _next_ticket_number) rather
    than a column server_default, since the formatted "SUP-xxxxxx"
    string is app-level presentation, not something Postgres should
    format.

No soft delete on either table (see PRD "Database Design" section —
SupportTicket/SupportTicketMessage use TenantModel, not
TenantSoftDeleteModel).

Chains off 0073 (plot_type_gap_drop_capacity).
"""
from alembic import op
import sqlalchemy as sa

revision = "0074"
down_revision = "0073"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # Platform-wide sequence for human-readable ticket numbers (SUP-000001, ...).
    # Not tied to any table column server_default — the service layer reads
    # nextval() and formats the "SUP-" prefix itself.
    op.execute("CREATE SEQUENCE IF NOT EXISTS support_ticket_seq START WITH 1 INCREMENT BY 1")

    op.execute(
        """
        CREATE TABLE IF NOT EXISTS support_tickets (
            id                  UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
            tenant_id           UUID        NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
            ticket_number       VARCHAR(20) NOT NULL,
            subject             VARCHAR(200) NOT NULL,
            status              VARCHAR(20) NOT NULL DEFAULT 'open',
            created_by_user_id  UUID        NOT NULL REFERENCES users(id),
            last_message_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),

            created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
            updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

            CONSTRAINT uq_support_tickets_ticket_number UNIQUE (ticket_number)
        )
        """
    )

    op.execute(
        """
        CREATE TABLE IF NOT EXISTS support_ticket_messages (
            id                      UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
            ticket_id               UUID        NOT NULL REFERENCES support_tickets(id) ON DELETE CASCADE,
            tenant_id               UUID        NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
            author_user_id          UUID        NOT NULL REFERENCES users(id),
            author_role_snapshot    VARCHAR(20) NOT NULL,
            body                    TEXT        NOT NULL,

            created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
            updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
        )
        """
    )

    # Portal list: WHERE tenant_id = :tenant_id ORDER BY created_at DESC
    op.execute(
        "CREATE INDEX IF NOT EXISTS idx_support_tickets_tenant_created "
        "ON support_tickets (tenant_id, created_at DESC)"
    )
    # Site-admin queue: WHERE status = :status ORDER BY created_at DESC (and unfiltered "all" queue, also date-sorted)
    op.execute(
        "CREATE INDEX IF NOT EXISTS idx_support_tickets_status_created "
        "ON support_tickets (status, created_at DESC)"
    )
    # Thread fetch: WHERE ticket_id = :ticket_id ORDER BY created_at ASC
    op.execute(
        "CREATE INDEX IF NOT EXISTS idx_support_messages_ticket_created "
        "ON support_ticket_messages (ticket_id, created_at ASC)"
    )


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS idx_support_messages_ticket_created")
    op.execute("DROP INDEX IF EXISTS idx_support_tickets_status_created")
    op.execute("DROP INDEX IF EXISTS idx_support_tickets_tenant_created")

    op.execute("DROP TABLE IF EXISTS support_ticket_messages")
    op.execute("DROP TABLE IF EXISTS support_tickets")

    op.execute("DROP SEQUENCE IF EXISTS support_ticket_seq")