"""phase 4 aqmm task evidence and review notes

Revision ID: 20260626_phase_4_aqmm_task_evidence_review_notes
Revises: 20260625_phase_3_1_aqmm_task_quality_tags
Create Date: 2026-06-26
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "20260626_phase_4_aqmm_task_evidence_review_notes"
down_revision = "20260625_phase_3_1_aqmm_task_quality_tags"
branch_labels = None
depends_on = None

TASK_COLUMNS = [
    sa.Column("evidence_status", sa.String(length=30), nullable=False, server_default="not_required"),
    sa.Column("submitted_for_review_by_user_id", sa.Integer(), nullable=True),
    sa.Column("submitted_for_review_at_utc", sa.DateTime(timezone=True), nullable=True),
    sa.Column("manager_review_status", sa.String(length=30), nullable=False, server_default="not_required"),
    sa.Column("manager_review_note", sa.Text(), nullable=True),
    sa.Column("manager_reviewed_by_user_id", sa.Integer(), nullable=True),
    sa.Column("manager_reviewed_at_utc", sa.DateTime(timezone=True), nullable=True),
    sa.Column("partner_review_status", sa.String(length=30), nullable=False, server_default="not_required"),
    sa.Column("partner_review_note", sa.Text(), nullable=True),
    sa.Column("partner_reviewed_by_user_id", sa.Integer(), nullable=True),
    sa.Column("partner_reviewed_at_utc", sa.DateTime(timezone=True), nullable=True),
    sa.Column("review_partner_review_status", sa.String(length=30), nullable=False, server_default="not_required"),
    sa.Column("review_partner_review_note", sa.Text(), nullable=True),
    sa.Column("review_partner_reviewed_by_user_id", sa.Integer(), nullable=True),
    sa.Column("review_partner_reviewed_at_utc", sa.DateTime(timezone=True), nullable=True),
    sa.Column("rework_status", sa.String(length=30), nullable=False, server_default="none"),
    sa.Column("rework_reason", sa.Text(), nullable=True),
    sa.Column("rework_requested_by_user_id", sa.Integer(), nullable=True),
    sa.Column("rework_requested_at_utc", sa.DateTime(timezone=True), nullable=True),
    sa.Column("rework_resolved_at_utc", sa.DateTime(timezone=True), nullable=True),
]

DOCUMENT_COLUMNS = [
    sa.Column("is_aqmm_evidence", sa.Boolean(), nullable=False, server_default=sa.false()),
    sa.Column("evidence_type", sa.String(length=80), nullable=True),
    sa.Column("evidence_description", sa.Text(), nullable=True),
    sa.Column("evidence_status", sa.String(length=30), nullable=False, server_default="not_required"),
    sa.Column("evidence_review_note", sa.Text(), nullable=True),
    sa.Column("evidence_reviewed_by_user_id", sa.Integer(), nullable=True),
    sa.Column("evidence_reviewed_at_utc", sa.DateTime(timezone=True), nullable=True),
]


def _has_table(bind, table_name: str) -> bool:
    return sa.inspect(bind).has_table(table_name)


def _has_column(bind, table_name: str, column_name: str) -> bool:
    if not _has_table(bind, table_name):
        return False
    return column_name in {c["name"] for c in sa.inspect(bind).get_columns(table_name)}


def _add_column_once(bind, table: str, column: sa.Column) -> None:
    if not _has_column(bind, table, column.name):
        with op.batch_alter_table(table) as batch:
            batch.add_column(column)


def _drop_column_once(bind, table: str, column_name: str) -> None:
    if _has_column(bind, table, column_name):
        with op.batch_alter_table(table) as batch:
            batch.drop_column(column_name)


def upgrade() -> None:
    bind = op.get_bind()

    if _has_table(bind, "client_service_task_instances"):
        for column in TASK_COLUMNS:
            _add_column_once(bind, "client_service_task_instances", column.copy())
        op.execute("""
            UPDATE client_service_task_instances
            SET evidence_status = CASE
                    WHEN COALESCE(is_aqmm_task, FALSE) = FALSE THEN 'not_required'
                    WHEN COALESCE(aqmm_evidence_required, FALSE) = FALSE THEN 'not_required'
                    WHEN EXISTS (
                        SELECT 1 FROM engagement_documents ed
                        WHERE ed.task_instance_id = client_service_task_instances.id
                          AND COALESCE(ed.is_deleted, FALSE) = FALSE
                    ) THEN 'uploaded'
                    ELSE 'pending'
                END,
                manager_review_status = CASE WHEN COALESCE(aqmm_manager_review_required, FALSE) THEN 'pending' ELSE 'not_required' END,
                partner_review_status = CASE WHEN COALESCE(aqmm_partner_review_required, FALSE) THEN 'pending' ELSE 'not_required' END,
                review_partner_review_status = CASE WHEN COALESCE(aqmm_review_partner_required, FALSE) THEN 'pending' ELSE 'not_required' END
            WHERE COALESCE(is_aqmm_task, FALSE) = TRUE
        """)

    if _has_table(bind, "engagement_documents"):
        for column in DOCUMENT_COLUMNS:
            _add_column_once(bind, "engagement_documents", column.copy())
        op.execute("""
            UPDATE engagement_documents ed
            SET is_aqmm_evidence = TRUE,
                evidence_status = 'uploaded',
                evidence_type = COALESCE(ed.document_type, 'GENERAL')
            WHERE ed.task_instance_id IS NOT NULL
              AND EXISTS (
                  SELECT 1 FROM client_service_task_instances t
                  WHERE t.id = ed.task_instance_id
                    AND COALESCE(t.is_aqmm_task, FALSE) = TRUE
              )
        """)


def downgrade() -> None:
    bind = op.get_bind()
    if _has_table(bind, "engagement_documents"):
        for col in [
            "evidence_reviewed_at_utc",
            "evidence_reviewed_by_user_id",
            "evidence_review_note",
            "evidence_status",
            "evidence_description",
            "evidence_type",
            "is_aqmm_evidence",
        ]:
            _drop_column_once(bind, "engagement_documents", col)

    if _has_table(bind, "client_service_task_instances"):
        for col in [
            "rework_resolved_at_utc",
            "rework_requested_at_utc",
            "rework_requested_by_user_id",
            "rework_reason",
            "rework_status",
            "review_partner_reviewed_at_utc",
            "review_partner_reviewed_by_user_id",
            "review_partner_review_note",
            "review_partner_review_status",
            "partner_reviewed_at_utc",
            "partner_reviewed_by_user_id",
            "partner_review_note",
            "partner_review_status",
            "manager_reviewed_at_utc",
            "manager_reviewed_by_user_id",
            "manager_review_note",
            "manager_review_status",
            "submitted_for_review_at_utc",
            "submitted_for_review_by_user_id",
            "evidence_status",
        ]:
            _drop_column_once(bind, "client_service_task_instances", col)
