"""新增场外识别尝试追踪表。"""

from alembic import op

revision = "20260910_recog_attempt"
down_revision = "20260910_offsite_worker_identity"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE IF NOT EXISTS offsite_recognition_attempt (
          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          task_id VARCHAR(24) NULL,
          mail_id VARCHAR(16) NULL,
          attachment_id VARCHAR(24) NULL,
          imap_uid VARCHAR(64) NULL,
          message_id VARCHAR(255) NOT NULL,
          filename VARCHAR(255) NOT NULL,
          file_hash VARCHAR(128) NOT NULL,
          original_file_path VARCHAR(500) NOT NULL,
          attempt_no INT UNSIGNED NOT NULL,
          source VARCHAR(16) NOT NULL,
          operator_id VARCHAR(64) NULL,
          document_type VARCHAR(24) NOT NULL,
          ocr_status VARCHAR(16) NOT NULL,
          llm_status VARCHAR(16) NOT NULL,
          extracted_fields JSON NOT NULL,
          field_confidence JSON NOT NULL,
          missing_fields JSON NOT NULL,
          low_confidence_fields JSON NOT NULL,
          page_evidence JSON NOT NULL,
          status VARCHAR(24) NOT NULL,
          error_message VARCHAR(500) NULL,
          started_at DATETIME(6) NOT NULL,
          finished_at DATETIME(6) NULL,
          created_at DATETIME(6) NOT NULL,
          PRIMARY KEY (id),
          KEY idx_offsite_recognition_attempt_source
            (message_id, file_hash, attempt_no),
          KEY idx_offsite_recognition_attempt_task
            (task_id, created_at),
          KEY idx_offsite_recognition_attempt_attachment
            (attachment_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
        """
    )


def downgrade() -> None:
    raise RuntimeError("场外识别尝试记录属于审计数据，禁止自动删除")