"""add disclosure_requests table

Revision ID: 005_disclosure
Revises: 5717043d0f9d
Create Date: 2026-02-26 14:00:00.000000

"""
from typing import Sequence, Union

from alembic import op


# revision identifiers, used by Alembic.
revision: str = "005_disclosure"
down_revision: Union[str, None] = "5717043d0f9d"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE disclosure_requests (
            id INT AUTO_INCREMENT PRIMARY KEY,
            case_id INT NOT NULL,
            requester_id INT NOT NULL,
            reason VARCHAR(500) NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            reviewed_by INT NULL,
            reviewed_at DATETIME NULL,
            expires_at DATETIME NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT fk_dr_case FOREIGN KEY (case_id) REFERENCES cases(id),
            CONSTRAINT fk_dr_requester FOREIGN KEY (requester_id) REFERENCES users(id),
            CONSTRAINT fk_dr_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id),
            CONSTRAINT chk_dr_status CHECK (status IN ('pending', 'approved', 'rejected')),
            INDEX idx_dr_case_status (case_id, status),
            INDEX idx_dr_requester (requester_id),
            INDEX idx_dr_status (status)
        )
        """
    )

    # Update Notification CHECK constraint to include disclosure types
    op.execute("ALTER TABLE notifications DROP CONSTRAINT chk_notif")
    op.execute(
        """
        ALTER TABLE notifications ADD CONSTRAINT chk_notif CHECK (
            notification_type IN (
                'new_cases_uploaded','icd_entered','icd_uploaded',
                'report_ready','coding_completed','disclosure_request','disclosure_resolved'
            )
        )
        """
    )


def downgrade() -> None:
    # Restore original Notification CHECK constraint
    op.execute("ALTER TABLE notifications DROP CONSTRAINT chk_notif")
    op.execute(
        """
        ALTER TABLE notifications ADD CONSTRAINT chk_notif CHECK (
            notification_type IN (
                'new_cases_uploaded','icd_entered','icd_uploaded',
                'report_ready','coding_completed'
            )
        )
        """
    )

    op.execute("DROP TABLE disclosure_requests")