"""issue reporting platform

Revision ID: 000000000016
Revises: 000000000015
Create Date: 2026-03-29

Creates the issue_reports table for shared end-user issue reporting linked to
the support_telemetry_platform support-case substrate.
"""

from alembic import op

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


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS issue_reports (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
            reporter_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            reporter_role_hint VARCHAR(64),
            component_key VARCHAR(255) NOT NULL,
            component_label VARCHAR(255) NOT NULL,
            page_url VARCHAR(500) NOT NULL,
            surface_ref VARCHAR(255),
            target_metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
            note TEXT NOT NULL,
            parent_issue_report_id UUID REFERENCES issue_reports(id) ON DELETE SET NULL,
            support_case_id UUID NOT NULL REFERENCES support_cases(id) ON DELETE RESTRICT,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
        );
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_issue_reports_app_reporter_created
        ON issue_reports (application_id, reporter_user_id, created_at);
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_issue_reports_support_case
        ON issue_reports (support_case_id);
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_issue_reports_parent
        ON issue_reports (parent_issue_report_id);
    """)


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