ewoooc/migrations/030_create_ppt_audit_results.sql

-- =============================================================================
-- Migration 030: ppt_audit_results — PPT 視覺審核歷史持久化
-- Operation Ollama-First v5.0 — Phase 38
-- 日期: 2026-05-04 台北
-- 對應頁面: /observability/ppt_audit_history
-- =============================================================================
-- 說明:
--   原本 ppt_audit_history 頁面只 os.listdir(reports/) 列檔，
--   PPT_VISION minicpm-v 跑出的審核結論（issues_found, confidence）完全遺失。
--   本 migration 加表，audit 完一律寫入，方便：
--     1. 觀測頁面顯示「audit 結果」而不只「檔案存在」
--     2. 趨勢分析（過去 30 天 PPT 通過率？常見 issue 類型？）
--     3. Telegram 推播去重（同檔案同問題 7 天內不重推）
--
-- 寫入點：
--   1. services/ppt_vision_service.py::check_ppt_file 跑完 minicpm-v 後寫
--   2. scheduler.py daily 22:00 cron 跑完所有當日 PPT 後 batch 寫
--
-- 索引設計：
--   - (audited_at DESC) 最新 audit
--   - (pptx_filename) 同檔多次審核
--   - (audit_status) 篩選 failed only
-- =============================================================================

CREATE TABLE IF NOT EXISTS ppt_audit_results (
    id              BIGSERIAL PRIMARY KEY,
    audited_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    pptx_filename   VARCHAR(256) NOT NULL,
    pptx_size_kb    INTEGER,
    pptx_mtime      TIMESTAMPTZ,                -- 檔案本身 mtime（區分同名重生）
    vision_enabled  BOOLEAN NOT NULL,           -- audit 當時 PPT_VISION_ENABLED 狀態
    audit_status    VARCHAR(32) NOT NULL,       -- 'passed' / 'failed' / 'skipped' / 'error'
    issues_count    INTEGER DEFAULT 0,
    issues_found    JSONB,                      -- ppt_vision_service 回傳的 issues 陣列
    confidence      NUMERIC(4,3),               -- 0-1 minicpm-v 信心度
    duration_ms     INTEGER,                    -- audit 耗時
    error_msg       TEXT,                       -- 失敗時的 exception
    reviewer_notes  TEXT,                       -- 人工補註（admin 介面後續可加）

    CONSTRAINT chk_audit_status_030
        CHECK (audit_status IN ('passed', 'failed', 'skipped', 'error', 'pending'))
);

CREATE INDEX IF NOT EXISTS idx_ppt_audit_at
    ON ppt_audit_results (audited_at DESC);

CREATE INDEX IF NOT EXISTS idx_ppt_audit_filename
    ON ppt_audit_results (pptx_filename);

CREATE INDEX IF NOT EXISTS idx_ppt_audit_failed
    ON ppt_audit_results (audited_at DESC)
    WHERE audit_status = 'failed';

COMMENT ON TABLE ppt_audit_results IS
    'PPT 視覺審核結果歷史；by services/ppt_vision_service.py minicpm-v 推論';
COMMENT ON COLUMN ppt_audit_results.issues_found IS
    'JSONB 陣列：[{type, severity, description, slide_index}]';