"""Add document approval pre-extraction gate columns.

Adds 5 columns to the documents table for approval workflow:
- approval_status (VARCHAR 20, default 'pending', indexed)
- reviewed_by (UUID, nullable)
- reviewed_at (TIMESTAMPTZ, nullable)
- rejection_reason (TEXT, nullable)
- reviewed_note (TEXT, nullable)

Backfills all existing documents to approval_status='approved'.

Revision ID: 3c4d5e6f7a8b
Revises: 2b3c4d5e6f7a
Create Date: 2026-04-13 10:00:00.000000

"""
from typing import Sequence, Union

from alembic import op

revision: str = "3c4d5e6f7a8b"
down_revision: Union[str, None] = "2b3c4d5e6f7a"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.execute("""
        DO $$
        BEGIN
            -- 1. Add approval_status column with default 'pending'
            IF NOT EXISTS (
                SELECT 1 FROM information_schema.columns
                WHERE table_name = 'documents' AND column_name = 'approval_status'
            ) THEN
                ALTER TABLE documents
                    ADD COLUMN approval_status VARCHAR(20) NOT NULL DEFAULT 'pending';
            END IF;

            -- 2. Add reviewed_by column (UUID, nullable)
            IF NOT EXISTS (
                SELECT 1 FROM information_schema.columns
                WHERE table_name = 'documents' AND column_name = 'reviewed_by'
            ) THEN
                ALTER TABLE documents ADD COLUMN reviewed_by UUID;
            END IF;

            -- 3. Add reviewed_at column (TIMESTAMPTZ, nullable)
            IF NOT EXISTS (
                SELECT 1 FROM information_schema.columns
                WHERE table_name = 'documents' AND column_name = 'reviewed_at'
            ) THEN
                ALTER TABLE documents ADD COLUMN reviewed_at TIMESTAMPTZ;
            END IF;

            -- 4. Add rejection_reason column (TEXT, nullable)
            IF NOT EXISTS (
                SELECT 1 FROM information_schema.columns
                WHERE table_name = 'documents' AND column_name = 'rejection_reason'
            ) THEN
                ALTER TABLE documents ADD COLUMN rejection_reason TEXT;
            END IF;

            -- 5. Add reviewed_note column (TEXT, nullable)
            IF NOT EXISTS (
                SELECT 1 FROM information_schema.columns
                WHERE table_name = 'documents' AND column_name = 'reviewed_note'
            ) THEN
                ALTER TABLE documents ADD COLUMN reviewed_note TEXT;
            END IF;

            -- 6. Backfill: grandfather all existing documents that predate
            --    the approval gate. These ran without approval, so mark them
            --    approved. Only skip rows that already have a reviewer
            --    (reviewed via the new endpoints).
            UPDATE documents SET approval_status = 'approved'
            WHERE approval_status = 'pending'
              AND reviewed_by IS NULL;

            -- 7. Index on approval_status for filtering
            CREATE INDEX IF NOT EXISTS idx_documents_approval_status
                ON documents (approval_status);
        END $$;
    """)


def downgrade() -> None:
    op.execute("""
        DO $$
        BEGIN
            DROP INDEX IF EXISTS idx_documents_approval_status;
            ALTER TABLE documents DROP COLUMN IF EXISTS reviewed_note;
            ALTER TABLE documents DROP COLUMN IF EXISTS rejection_reason;
            ALTER TABLE documents DROP COLUMN IF EXISTS reviewed_at;
            ALTER TABLE documents DROP COLUMN IF EXISTS reviewed_by;
            ALTER TABLE documents DROP COLUMN IF EXISTS approval_status;
        END $$;
    """)