"""support telemetry platform

Revision ID: 000000000015
Revises: 000000000014
Create Date: 2026-03-01

Creates support_cases and support_case_events tables for centralized
cross-application support telemetry ingestion and triage lifecycle APIs.
"""

from alembic import op

revision = "000000000015"
down_revision = "000000000014"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS support_cases (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
            external_case_ref VARCHAR(255),
            title VARCHAR(500),
            status VARCHAR(20) NOT NULL DEFAULT 'open',
            priority VARCHAR(20) NOT NULL DEFAULT 'normal',
            highest_severity VARCHAR(20) NOT NULL DEFAULT 'info',
            assigned_to_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
            first_event_at TIMESTAMPTZ NOT NULL,
            last_event_at TIMESTAMPTZ NOT NULL,
            resolved_at TIMESTAMPTZ,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            CONSTRAINT ck_support_cases_status CHECK (
                status IN ('open', 'in_progress', 'resolved', 'ignored')
            ),
            CONSTRAINT ck_support_cases_priority CHECK (
                priority IN ('low', 'normal', 'high', 'urgent')
            ),
            CONSTRAINT ck_support_cases_highest_severity CHECK (
                highest_severity IN ('info', 'warning', 'error', 'critical')
            )
        );
    """)

    op.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS uq_support_cases_external_ref
        ON support_cases (application_id, external_case_ref)
        WHERE external_case_ref IS NOT NULL;
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_support_cases_app_status_priority
        ON support_cases (application_id, status, priority);
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_support_cases_assigned_user
        ON support_cases (assigned_to_user_id);
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_support_cases_last_event_at
        ON support_cases (last_event_at);
    """)

    op.execute("""
        CREATE TABLE IF NOT EXISTS support_case_events (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            case_id UUID NOT NULL REFERENCES support_cases(id) ON DELETE CASCADE,
            application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
            event_key VARCHAR(128) NOT NULL,
            source_channel VARCHAR(32) NOT NULL,
            event_type VARCHAR(100) NOT NULL,
            severity VARCHAR(20) NOT NULL,
            actor_type VARCHAR(32) NOT NULL,
            actor_external_id VARCHAR(255),
            correlation_id VARCHAR(255),
            payload JSONB NOT NULL DEFAULT '{}'::jsonb,
            redacted_keys JSONB NOT NULL DEFAULT '[]'::jsonb,
            occurred_at TIMESTAMPTZ NOT NULL,
            received_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            ingested_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
            CONSTRAINT ck_support_case_events_source_channel CHECK (
                source_channel IN ('issue_log', 'support_thread', 'email', 'frontend', 'api', 'webhook')
            ),
            CONSTRAINT ck_support_case_events_severity CHECK (
                severity IN ('info', 'warning', 'error', 'critical')
            ),
            CONSTRAINT ck_support_case_events_actor_type CHECK (
                actor_type IN ('user', 'practitioner', 'anonymous', 'service', 'support_operator')
            )
        );
    """)

    op.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS uq_support_case_events_event_key
        ON support_case_events (application_id, event_key);
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_support_case_events_case_occurred
        ON support_case_events (case_id, occurred_at);
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_support_case_events_app_correlation
        ON support_case_events (application_id, correlation_id)
        WHERE correlation_id IS NOT NULL;
    """)


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS support_case_events CASCADE;")
    op.execute("DROP TABLE IF EXISTS support_cases CASCADE;")