-- Task 6 Step 5: 只读历史数据评估报告
-- 仅统计，不更新数据；未经人工确认不得批量重算或覆盖历史 risk_score/risk_level。

WITH real_inspections AS (
    SELECT
        id,
        risk_level,
        risk_score,
        detections
    FROM inspection_records
    WHERE ai_status = 'done'
      AND (is_mock IS NULL OR is_mock = false)
)
SELECT
    risk_level,
    count(*) AS records,
    count(*) FILTER (
        WHERE EXISTS (
            SELECT 1
            FROM jsonb_array_elements(detections) AS d
            WHERE d->>'class' = 'healthy'
              AND COALESCE((d->>'confidence')::numeric, 0) >= 0.80
        )
    ) AS healthy_high_conf_records
FROM real_inspections
GROUP BY risk_level
ORDER BY risk_level;