-- ============================================================
-- 召回正确性扫描器(treatment_initiation_recall · 只读回归)· 集合式(全量可跑)
--
-- 用途:独立于 scenario 代码,用 plan_reasons.signals 反查 patient_facts 交叉验证
--       "该召没召 / 不该召却召"。每次摄入/改算法后跑,看 FP/FN 体量。
-- 独立性:下方 vt_codes 重述一份 code→{resolver/cooldown/⑤d科目/全口/乳牙},
--         跟 scenario 代码各写一份 → 不一致即暴露(差分)。
-- 性能:把 治疗/召回/诊断/预约 牙位各 unnest 一次进【索引临时表】+ ANALYZE,
--       再用索引查找做 EXISTS/JOIN。13 万患者秒级(旧逐行相关子查询版会 O(n²) 卡死)。
-- 运行:
--   本地   docker exec -i pac-postgres   psql -U pac -d pac -f - < apps/pac-service/sql/verify-recall.sql
--   服务器 docker exec -i pac-postgres-1 psql -U pac -d pac -f - < apps/pac-service/sql/verify-recall.sql
-- 章节:§A 规模 / §B FP 硬闸(应0) / §C 牙位交叉表+FP / §C3-C4 FN 逐层解释 /
--       §D 全口码治疗后复发被压 / §E K08 正畸语境 FP
-- 注:§C/§D 的"残留"需再排 cooldown / 已指派锁 / ⑤d 才是真问题(见 docs 讨论)。
-- ============================================================
\pset pager off
\set ON_ERROR_STOP on
SET work_mem = '256MB';

-- 牙位级 code 配置(K05/K07 全口码不在牙位交叉表,单列 §D)
CREATE TEMP TABLE vt_codes(code text, sub text, cooldown int, resolver text[], complaints text[], drop_decid bool);
INSERT INTO vt_codes VALUES
 ('K08','missing_tooth',30,        ARRAY['implant','prosthodontic','restorative','endodontic','surgical','cosmetic','pediatric'], ARRAY['种植','修复'], true),
 ('K02','caries_no_filling',14,    ARRAY['implant','prosthodontic','restorative','endodontic','surgical','cosmetic','pediatric'], ARRAY[]::text[], false),
 ('K03','hard_tissue_damage',14,   ARRAY['implant','prosthodontic','restorative','endodontic','surgical','cosmetic','pediatric'], ARRAY['修复','拔牙'], false),
 ('K04','endo_no_rct',14,          ARRAY['implant','prosthodontic','restorative','endodontic','surgical','cosmetic','pediatric'], ARRAY[]::text[], false),
 ('K01','impacted_tooth',14,       ARRAY['implant','prosthodontic','restorative','endodontic','surgical','cosmetic','pediatric'], ARRAY['拔牙'], false),
 ('K09','jaw_cyst',14,             ARRAY['implant','prosthodontic','restorative','endodontic','surgical','cosmetic','pediatric'], ARRAY['拔牙'], false),
 ('K00','development_eruption',30, ARRAY['implant','prosthodontic','restorative','endodontic','surgical','cosmetic','pediatric','orthodontic'], ARRAY['拔牙','修复','种植','正畸','早矫'], false),
 ('K06','gum_alveolar_lesion',14,  ARRAY['periodontic','surgical'], ARRAY['牙周','拔牙'], false);

-- ── 牙位 unnest 一次 → 索引临时表 ──
CREATE TEMP TABLE vt_treat AS
SELECT t.patient_id, t.content->>'category' AS cat, tk AS tooth, t.occurred_at AS tx_at
FROM patient_facts t
CROSS JOIN LATERAL unnest(string_to_array(regexp_replace(coalesce(t.content->>'tooth_position',''),'[^0-9;]+',';','g'),';')) tk
WHERE t.type='treatment_record' AND t.kind='actual' AND t.status IN('active','fulfilled') AND tk ~ '^[0-9]{2}$';
CREATE INDEX ON vt_treat(patient_id, tooth); ANALYZE vt_treat;

CREATE TEMP TABLE vt_recall AS
SELECT fp.patient_id, pr.signals->>'subKey' AS sub, tk AS tooth
FROM plan_reasons pr JOIN followup_plans fp ON fp.id=pr.plan_id
CROSS JOIN LATERAL unnest(string_to_array(regexp_replace(coalesce(pr.signals->>'toothPosition',''),'[^0-9;]+',';','g'),';')) tk
WHERE fp.status IN('active','assigned') AND pr.scenario='treatment_initiation_recall' AND tk ~ '^[0-9]{2}$';
CREATE INDEX ON vt_recall(patient_id, sub, tooth); ANALYZE vt_recall;

-- 预约科目(⑤d):patient × 中文 complaint × 时间
CREATE TEMP TABLE vt_appt AS
SELECT a.patient_id, trim(c) AS compl, COALESCE(a.planned_for,a.occurred_at) AS appt_at
FROM patient_facts a
CROSS JOIN LATERAL unnest(string_to_array(coalesce(a.content->>'complaint_category',''),',')) c
WHERE a.type='appointment_record' AND a.status IN('active','fulfilled') AND trim(c)<>'';
CREATE INDEX ON vt_appt(patient_id, compl); ANALYZE vt_appt;

-- 患者级标记:未来预约(⑤b)/ 近期到诊(⑤f)
CREATE TEMP TABLE vt_future AS SELECT DISTINCT patient_id FROM patient_facts
  WHERE type='appointment_record' AND status='active' AND COALESCE(planned_for,occurred_at)>now();
CREATE INDEX ON vt_future(patient_id); ANALYZE vt_future;
CREATE TEMP TABLE vt_recent AS SELECT DISTINCT patient_id FROM patient_facts
  WHERE type IN('encounter_record','emr_record') AND occurred_at>now()-interval '14 days';
CREATE INDEX ON vt_recent(patient_id); ANALYZE vt_recent;

-- 结构码 dx/rec 牙位(§C4 取代规则:同牙更晚不同结构码 → 取代)
CREATE TEMP TABLE vt_struct AS
SELECT ld.patient_id, ld.content->>'code' AS code, tk AS tooth, COALESCE(ld.occurred_at,ld.planned_for) AS at
FROM patient_facts ld
CROSS JOIN LATERAL unnest(string_to_array(regexp_replace(coalesce(ld.content->>'tooth_position',''),'[^0-9;]+',';','g'),';')) tk
WHERE ld.status='active' AND ld.type IN('diagnosis_record','recommendation_record') AND tk ~ '^[0-9]{2}$'
  AND ld.content->>'code' IN ('K00','K01','K02','K03','K04','K08','K09','IMPLANT_RECOMMENDED','CROWN_RECOMMENDED','FILLING_RECOMMENDED','EXTRACTION_RECOMMENDED','RCT_RECOMMENDED','HARD_TISSUE_REPAIR_RECOMMENDED','ERUPTION_INTERVENTION_RECOMMENDED','JAW_CYST_REMOVAL_RECOMMENDED');
CREATE INDEX ON vt_struct(patient_id, tooth); ANALYZE vt_struct;

-- ── 诊断牙(宇宙):合规 + 过 cooldown,取 LATEST 诊断 ──
CREATE TEMP TABLE vt_diag AS
SELECT d.patient_id, c.code, c.sub, c.resolver, c.complaints, c.drop_decid,
       tk AS tooth, max(d.occurred_at) AS dx_at, string_agg(DISTINCT d.content->>'name_zh','/') AS name_zh, min(c.cooldown) AS cooldown
FROM patient_facts d
JOIN vt_codes c ON c.code = d.content->>'code'
JOIN patients p ON p.id=d.patient_id JOIN patient_profiles pp ON pp.patient_id=d.patient_id
CROSS JOIN LATERAL unnest(string_to_array(regexp_replace(coalesce(d.content->>'tooth_position',''),'[^0-9;]+',';','g'),';')) tk
WHERE d.type='diagnosis_record' AND d.status='active' AND p.active AND NOT pp.do_not_contact AND NOT pp.deceased AND tk ~ '^[0-9]{2}$'
GROUP BY d.patient_id, c.code, c.sub, c.resolver, c.complaints, c.drop_decid, tk;
DELETE FROM vt_diag WHERE dx_at > now() - (cooldown||' days')::interval;
CREATE INDEX ON vt_diag(patient_id, tooth); ANALYZE vt_diag;

-- §E 正畸语境患者(K07 诊断 或 正畸治疗)— 给 x_orthoextract 用
CREATE TEMP TABLE vt_ortho_pt AS
  SELECT DISTINCT patient_id FROM patient_facts WHERE status='active' AND type='diagnosis_record' AND content->>'code'='K07'
  UNION SELECT DISTINCT patient_id FROM patient_facts WHERE type='treatment_record' AND content->>'category'='orthodontic';
CREATE INDEX ON vt_ortho_pt(patient_id); ANALYZE vt_ortho_pt;

-- ── 新排除解释表(对齐 gap 核心 2026-06 六类修复;拆线/ICD 已随 reparse 自动正确,无需表)──
-- (2) 固定桥跨度:同一牙弓 ≥2 颗 prosthodontic actual → 基牙跨度区间内全牙位计入已修复
CREATE TEMP TABLE vt_bridge AS
WITH bt AS (
  SELECT t.patient_id,
    CASE substr(tk,1,1) WHEN '1' THEN 9-substr(tk,2,1)::int WHEN '2' THEN 8+substr(tk,2,1)::int
                        WHEN '4' THEN 9-substr(tk,2,1)::int WHEN '3' THEN 8+substr(tk,2,1)::int END AS idx,
    CASE WHEN substr(tk,1,1) IN('1','2') THEN 'U' ELSE 'L' END AS arch
  FROM patient_facts t
  CROSS JOIN LATERAL unnest(string_to_array(regexp_replace(coalesce(t.content->>'tooth_position',''),'[^0-9;]+',';','g'),';')) tk
  WHERE t.type='treatment_record' AND t.kind='actual' AND t.status IN('active','fulfilled')
    AND t.content->>'category'='prosthodontic' AND tk ~ '^[1-4][1-8]$'
), span AS (SELECT patient_id, arch, min(idx) mi, max(idx) ma FROM bt GROUP BY patient_id, arch HAVING count(*)>=2)
SELECT s.patient_id,
  CASE WHEN s.arch='U' THEN CASE WHEN gi<=8 THEN '1'||(9-gi)::text ELSE '2'||(gi-8)::text END
       ELSE CASE WHEN gi<=8 THEN '4'||(9-gi)::text ELSE '3'||(gi-8)::text END END AS tooth
FROM span s CROSS JOIN generate_series(s.mi, s.ma) gi;
CREATE INDEX ON vt_bridge(patient_id, tooth); ANALYZE vt_bridge;

-- (3) 检查文本无修复指征:emr exam_findings 牙位 message "缺牙+间隙关闭/无修复间隙/间隙不足"
CREATE TEMP TABLE vt_norestor AS
SELECT src.patient_id, tk AS tooth
FROM (SELECT patient_id, content->>'exam_findings' AS ef_text FROM patient_facts
      WHERE type='emr_record' AND status IN('active','fulfilled')
        AND content->>'exam_findings' ~ '^\[' AND content->>'exam_findings' ~ '缺[牙失]'
        AND content->>'exam_findings' ~ '间隙关闭|间隙已关闭|无修复间隙|间隙不足') src
CROSS JOIN LATERAL jsonb_array_elements(src.ef_text::jsonb) ef
CROSS JOIN LATERAL unnest(string_to_array(regexp_replace(coalesce(ef->>'toothPosition',''),'[^0-9;]+',';','g'),';')) tk
WHERE ef->>'message' ~ '缺[牙失]' AND ef->>'message' ~ '间隙关闭|间隙已关闭|无修复间隙|间隙不足' AND tk ~ '^[0-9]{2}$';
CREATE INDEX ON vt_norestor(patient_id, tooth); ANALYZE vt_norestor;

-- (4) 患者无意愿(按牙位):treatment subtype 含无意愿/不愿/拒绝(任意 kind)
CREATE TEMP TABLE vt_refusal AS
SELECT t.patient_id, t.content->>'category' AS cat, tk AS tooth, COALESCE(t.occurred_at,t.planned_for) AS at
FROM patient_facts t
CROSS JOIN LATERAL unnest(string_to_array(regexp_replace(coalesce(t.content->>'tooth_position',''),'[^0-9;]+',';','g'),';')) tk
WHERE t.type='treatment_record' AND t.status IN('active','fulfilled')
  AND t.content->>'subtype' ~ '无意愿|不愿|拒绝' AND tk ~ '^[0-9]{2}$';
CREATE INDEX ON vt_refusal(patient_id, tooth); ANALYZE vt_refusal;

-- (5) 外院已治疗(回访记录 result,患者级 + 治疗日)
CREATE TEMP TABLE vt_external AS
SELECT DISTINCT patient_id, task_date AS at FROM patient_return_visits
WHERE result ~ '(外院|他院|别院|别的医院|其他医院|换了医院)[^，；。]{0,3}(治疗|种植|矫正|矫治|修复|根管|补牙|做了|做过)|(治疗|种植|矫正|矫治|修复|根管|补牙|做了|做过)[^，；。]{0,3}(外院|他院|别院|别的医院|其他医院)'
  AND result !~ '(建议|打算|考虑|可能|拟|会去|要去|去咨询|去看|咨询了|不考虑)[^，；。]{0,8}(外院|他院|别院|别的?医院|其他医院)';
CREATE INDEX ON vt_external(patient_id); ANALYZE vt_external;

-- ── 分类(EXISTS 全打索引临时表,秒级)──
CREATE TEMP TABLE vt_final AS
SELECT d.patient_id, d.code, d.sub, d.tooth, d.dx_at, d.name_zh,
  EXISTS(SELECT 1 FROM vt_treat t WHERE t.patient_id=d.patient_id AND t.tooth=d.tooth AND t.cat=ANY(d.resolver) AND t.tx_at>=d.dx_at) AS resolved,
  EXISTS(SELECT 1 FROM vt_recall r WHERE r.patient_id=d.patient_id AND r.sub=d.sub AND r.tooth=d.tooth) AS recalled,
  (d.patient_id IN (SELECT patient_id FROM vt_future)) AS x_future,
  (d.patient_id IN (SELECT patient_id FROM vt_recent)) AS x_recent,
  (cardinality(d.complaints)>0 AND EXISTS(SELECT 1 FROM vt_appt a WHERE a.patient_id=d.patient_id AND a.compl=ANY(d.complaints) AND a.appt_at>=d.dx_at)) AS x_appt,
  (d.name_zh IN('废用牙','无功能牙')) AS x_inelig,
  (d.drop_decid AND d.tooth ~ '^[5-8][1-5]$') AS x_decid,
  EXISTS(SELECT 1 FROM vt_struct s WHERE s.patient_id=d.patient_id AND s.tooth=d.tooth AND s.code<>d.code AND s.at>=d.dx_at) AS x_superseded,
  -- §E 三条剔除(仅 K08,跟 scenario 代码 missing_tooth 对齐):智齿位 / 先天 / 正畸减数位
  (d.code='K08' AND d.tooth ~ '^[1-4]8$') AS x_thirdmolar,
  (d.code='K08' AND COALESCE(d.name_zh,'') LIKE '%先天%') AS x_congenital,
  (d.code='K08' AND d.patient_id IN (SELECT patient_id FROM vt_ortho_pt)
     AND EXISTS(SELECT 1 FROM vt_treat t WHERE t.patient_id=d.patient_id AND t.tooth=d.tooth AND t.cat='surgical')) AS x_orthoextract,
  -- 2026-06 六类修复对齐(固定桥/检查文本/无意愿/外院)
  EXISTS(SELECT 1 FROM vt_bridge b WHERE b.patient_id=d.patient_id AND b.tooth=d.tooth) AS x_bridge,
  EXISTS(SELECT 1 FROM vt_norestor n WHERE n.patient_id=d.patient_id AND n.tooth=d.tooth) AS x_norestor,
  EXISTS(SELECT 1 FROM vt_refusal rf WHERE rf.patient_id=d.patient_id AND rf.tooth=d.tooth AND rf.cat=ANY(d.resolver) AND rf.at>=d.dx_at) AS x_refusal,
  EXISTS(SELECT 1 FROM vt_external xe WHERE xe.patient_id=d.patient_id AND xe.at>=d.dx_at::date) AS x_external
FROM vt_diag d;
ANALYZE vt_final;

\echo '════════ §A 规模(诊断牙:合规+过cooldown)════════'
SELECT count(*) AS teeth, count(DISTINCT patient_id) AS patients,
  count(*) FILTER (WHERE recalled) AS recalled, count(*) FILTER (WHERE resolved) AS resolved FROM vt_final;

\echo '════════ §B FP 硬闸(应=0)════════'
SELECT
 (SELECT count(*) FROM plan_reasons pr JOIN followup_plans fp ON fp.id=pr.plan_id JOIN patients p ON p.id=fp.patient_id JOIN patient_profiles pp ON pp.patient_id=fp.patient_id
   WHERE fp.status IN('active','assigned') AND pr.scenario='treatment_initiation_recall' AND (p.active=false OR pp.do_not_contact OR pp.deceased)) AS fp_compliance,
 (SELECT count(*) FROM plan_reasons pr JOIN followup_plans fp ON fp.id=pr.plan_id
   WHERE fp.status IN('active','assigned') AND pr.scenario='treatment_initiation_recall'
     AND EXISTS(SELECT 1 FROM vt_future f WHERE f.patient_id=fp.patient_id)) AS fp_future_appt;

\echo '════════ §C 牙位交叉表(resolved × recalled)════════'
SELECT resolved, recalled, count(*) AS teeth,
  CASE WHEN NOT resolved AND recalled THEN '✓ 未治→召' WHEN resolved AND NOT recalled THEN '✓ 已治→不召'
       WHEN resolved AND recalled THEN '✗ FP 已治却召' ELSE '? FN 未治却不召' END AS verdict
FROM vt_final GROUP BY 1,2 ORDER BY 1,2;

\echo '════════ §C2 FP(已治却召)样例 ════════'
SELECT substr(patient_id::text,1,8) pid, code, tooth, dx_at::date, name_zh FROM vt_final WHERE resolved AND recalled LIMIT 15;

\echo '════════ §C3 FN 逐层解释 → 真·无法解释(按首个命中归桶)════════'
CREATE TEMP VIEW vt_fn AS
SELECT *,
  CASE
    WHEN x_future                          THEN '1_未来预约⑤b'
    WHEN x_recent                          THEN '2_近期到诊⑤f'
    -- ⑤d(预约科目)W7 已从生产移除 → scanner 同步不再用它解释 FN(保持 oracle 与生产一致)
    WHEN x_inelig OR x_decid               THEN '4_废用牙/乳牙'
    WHEN x_thirdmolar OR x_congenital OR x_orthoextract THEN '5_§E剔除(智齿/先天/正畸减数)'
    WHEN x_superseded                      THEN '6_同牙更晚诊断取代'
    WHEN x_bridge                          THEN '7a_固定桥跨度修复'
    WHEN x_norestor                        THEN '7b_检查文本无修复指征'
    WHEN x_refusal                         THEN '7c_患者无意愿(拒绝该类)'
    WHEN x_external                        THEN '7d_外院已治疗(回访)'
    ELSE                                        '9_真·无法解释'
  END AS bucket
FROM vt_final WHERE NOT resolved AND NOT recalled;
SELECT bucket, count(*) FROM vt_fn GROUP BY bucket ORDER BY bucket;
\echo '---- 真·无法解释 FN 样例 ----'
SELECT code, substr(patient_id::text,1,8) pid, tooth, dx_at::date, name_zh
FROM vt_fn WHERE bucket='9_真·无法解释' ORDER BY code LIMIT 30;

-- ════════════════════════════════════════════════════════════════════════
-- §C4 可疑漏召(独立临床检查 — 不信任代码自身的排除规则)
--   差分测试盲区:scanner 与生产用同一套排除规则 → 规则本身太粗(如已废的 ⑤d 按科目误排)会被
--   "互相印证"成"已解释",静默放过(沈静芳 34;43 当初即此)。本层换个视角独立判:
--   凡"该牙有结构诊断 + 本牙从没做过对应治疗 + 没有任何【牙位级硬层】(废用/乳牙/§E/更晚诊断)挡",
--   却仅因【患者级代理】(⑤b 未来预约 / ⑤f 近期到诊,均不看牙位)而没召 → 列出来【人审】:
--   该次就诊/未来预约是否真会处理这颗牙?是→合理;否→真漏召(就像 34;43 当年被科目级 ⑤d 误排)。
-- ════════════════════════════════════════════════════════════════════════
\echo '════════ §C4 可疑漏召(本牙零治疗+无硬层排除,仅患者级代理豁免 → 需人审)════════'
SELECT
  count(*) FILTER (WHERE x_future) AS 仅未来预约豁免,
  count(*) FILTER (WHERE x_recent AND NOT x_future) AS 仅近期到诊豁免,
  count(*) AS 可疑漏召合计
FROM vt_final
WHERE NOT resolved AND NOT recalled
  AND (x_future OR x_recent)
  AND NOT (x_inelig OR x_decid OR x_thirdmolar OR x_congenital OR x_orthoextract OR x_superseded OR x_bridge OR x_norestor OR x_refusal OR x_external);
\echo '---- 可疑漏召样例(人审:这颗牙的诊断,该次到诊/未来预约是否真处理?)----'
SELECT code, sub, substr(patient_id::text,1,8) pid, tooth, dx_at::date, name_zh,
  CASE WHEN x_future THEN '未来预约⑤b' ELSE '近期到诊⑤f' END AS 豁免来源
FROM vt_final
WHERE NOT resolved AND NOT recalled
  AND (x_future OR x_recent)
  AND NOT (x_inelig OR x_decid OR x_thirdmolar OR x_congenital OR x_orthoextract OR x_superseded OR x_bridge OR x_norestor OR x_refusal OR x_external)
ORDER BY code, tooth LIMIT 30;

\echo '════════ §D 全口码 治疗后复发被压(excludeIfEverTreated)════════'
WITH tx AS (SELECT patient_id, content->>'category' cat, max(occurred_at) last_tx FROM patient_facts
   WHERE type='treatment_record' AND kind='actual' AND status IN('active','fulfilled') AND content->>'category' IN('periodontic','orthodontic') GROUP BY 1,2),
dx AS (SELECT patient_id, content->>'code' code, max(occurred_at) last_dx FROM patient_facts
   WHERE type='diagnosis_record' AND status='active' AND content->>'code' IN('K05','K07') GROUP BY 1,2),
rec AS (SELECT fp.patient_id, pr.signals->>'subKey' sub FROM plan_reasons pr JOIN followup_plans fp ON fp.id=pr.plan_id
   WHERE fp.status IN('active','assigned') AND pr.signals->>'subKey' IN('perio_no_srp','ortho_no_consult') GROUP BY 1,2)
SELECT d.code,
  count(*) FILTER (WHERE t.last_tx IS NOT NULL) AS treated_pts,
  count(*) FILTER (WHERE t.last_tx IS NOT NULL AND d.last_dx>t.last_tx) AS recurrence,
  count(*) FILTER (WHERE t.last_tx IS NOT NULL AND d.last_dx>t.last_tx AND r.patient_id IS NOT NULL) AS recurrence_recalled
FROM dx d JOIN patients p ON p.id=d.patient_id JOIN patient_profiles pp ON pp.patient_id=d.patient_id
LEFT JOIN tx t ON t.patient_id=d.patient_id AND t.cat=CASE d.code WHEN 'K05' THEN 'periodontic' ELSE 'orthodontic' END
LEFT JOIN rec r ON r.patient_id=d.patient_id AND r.sub=CASE d.code WHEN 'K05' THEN 'perio_no_srp' ELSE 'ortho_no_consult' END
WHERE p.active AND NOT pp.do_not_contact AND NOT pp.deceased
GROUP BY d.code;

\echo '════════ §E K08 正畸语境 FP 上界(召缺牙→种植 且 有K07诊断/正畸治疗)════════'
-- vt_ortho_pt 已在 vt_final 前建好(给 x_orthoextract 用),此处直接复用
SELECT count(DISTINCT f.patient_id) AS k08_ortho_ctx_patients
FROM vt_final f WHERE f.code='K08' AND f.recalled AND f.patient_id IN (SELECT patient_id FROM vt_ortho_pt);

-- ════════════════════════════════════════════════════════════════════════
-- §C5 诊断码 ↔ 诊断名 语义不符(怪码监测)
--   '召回了也可能不一致':召回基于诊断【码】,若宿主 ICD 怪码让【码】与【名】矛盾(如 K07.303
--   牙体缺损被编 K07=正畸 → 召正畸),召回就召错了科目。这层独立查 code vs name 是否同类:
--   命中 = 疑似怪码 → 该进 manifest recode map(如已加 K07.303→K03,这里应归 0)。
-- ════════════════════════════════════════════════════════════════════════
\echo '════════ §C5 诊断码↔诊断名 语义不符(怪码监测;recode 修过的应=0)════════'
SELECT content->>'code' AS code, content->>'name_zh' AS name_zh,
  count(*) AS n, count(DISTINCT patient_id) AS pts
FROM patient_facts
WHERE type='diagnosis_record' AND status='active'
  -- 命中"异类关键词" 且 "本码自身关键词缺席"(后者排多病名拼接如"龋齿、阻生齿"→K01 仍合理)
  AND (
    (content->>'code'='K07' AND content->>'name_zh' ~ '缺损|龋|根管|残根|缺失|牙髓|牙周炎' AND content->>'name_zh' !~ '错|畸|正畸|拥挤|不齐|间隙|反合|覆合|覆盖|开合|锁合|移位|扭转|异位|位置异常|关节') OR
    (content->>'code'='K02' AND content->>'name_zh' ~ '缺失|正畸|错[合颌]|阻生' AND content->>'name_zh' !~ '龋|齲|蛀') OR
    (content->>'code'='K08' AND content->>'name_zh' ~ '龋|正畸|牙髓|阻生' AND content->>'name_zh' !~ '缺失|缺牙') OR
    (content->>'code'='K04' AND content->>'name_zh' ~ '正畸|缺失|阻生' AND content->>'name_zh' !~ '牙髓|根尖|髓') OR
    (content->>'code'='K01' AND content->>'name_zh' ~ '龋|缺失牙|正畸' AND content->>'name_zh' !~ '阻生|埋伏|智齿') OR
    (content->>'code'='K05' AND content->>'name_zh' ~ '龋|缺失|正畸|阻生' AND content->>'name_zh' !~ '牙周炎|牙周病') OR
    (content->>'code'='K03' AND content->>'name_zh' ~ '正畸|阻生' AND content->>'name_zh' !~ '缺损|楔|残根|残冠|隐裂|折|磨损') OR
    (content->>'code'='K06' AND content->>'name_zh' ~ '龋|正畸|阻生' AND content->>'name_zh' !~ '牙龈|牙槽|龈') OR
    (content->>'code'='K00' AND content->>'name_zh' ~ '龋|牙髓|牙周炎' AND content->>'name_zh' !~ '发育|萌出|多生|融合|先天')
  )
GROUP BY 1,2 ORDER BY n DESC LIMIT 30;

-- ════════════════════════════════════════════════════════════════════════
-- §C6 页面事实视角 — 牙位"应治未治"全集(无 cooldown/合规预过滤)vs 召回,逐颗归因
--   §C 的宇宙 vt_diag 预先 DELETE 了 cooldown、JOIN 了合规闸 → 那些牙根本不进交叉表;
--   但【页面照样展示】(tooth-timeline 零推断,只按牙位陈列诊断/治疗事实)。于是"页面看着
--   诊断了没治疗、却没召"的牙,§C/§C3 静默不列(= 用户担忧的盲区)。
--   本层从原始事实独立重建"应治未治"全集,口径 = 人眼读页面:
--     有结构诊断(召回码)+ 本牙之后无对应治疗(resolver 家族)+ 未被同牙更晚诊断取代。
--   不预过滤,逐颗标注【为何没召】。'9_真·无法解释' = 既非已召、又无任何可解释排除
--   → 召回算法与牙位事实的【真不一致】(疑似漏召 bug)。
-- ════════════════════════════════════════════════════════════════════════
CREATE TEMP TABLE vt_diag_raw AS
SELECT d.patient_id, c.code, c.sub, c.resolver, c.drop_decid, c.cooldown,
       tk AS tooth, max(d.occurred_at) AS dx_at,
       string_agg(DISTINCT d.content->>'name_zh','/') AS name_zh
FROM patient_facts d
JOIN vt_codes c ON c.code = d.content->>'code'
CROSS JOIN LATERAL unnest(string_to_array(regexp_replace(coalesce(d.content->>'tooth_position',''),'[^0-9;]+',';','g'),';')) tk
WHERE d.type='diagnosis_record' AND d.status='active' AND tk ~ '^[0-9]{2}$'
GROUP BY d.patient_id, c.code, c.sub, c.resolver, c.drop_decid, c.cooldown, tk;
CREATE INDEX ON vt_diag_raw(patient_id, tooth); ANALYZE vt_diag_raw;

CREATE TEMP TABLE vt_final_raw AS
SELECT d.patient_id, d.code, d.sub, d.tooth, d.dx_at, d.name_zh,
  EXISTS(SELECT 1 FROM vt_treat t  WHERE t.patient_id=d.patient_id AND t.tooth=d.tooth AND t.cat=ANY(d.resolver) AND t.tx_at>=d.dx_at) AS resolved,
  EXISTS(SELECT 1 FROM vt_recall r WHERE r.patient_id=d.patient_id AND r.sub=d.sub AND r.tooth=d.tooth) AS recalled,
  EXISTS(SELECT 1 FROM vt_struct s WHERE s.patient_id=d.patient_id AND s.tooth=d.tooth AND s.code<>d.code AND s.at>=d.dx_at) AS x_superseded,
  (d.dx_at > now() - (d.cooldown||' days')::interval) AS x_cooldown,
  EXISTS(SELECT 1 FROM patients p JOIN patient_profiles pp ON pp.patient_id=p.id
          WHERE p.id=d.patient_id AND (NOT p.active OR pp.do_not_contact OR pp.deceased)) AS x_compliance,
  (d.patient_id IN (SELECT patient_id FROM vt_future)) AS x_future,
  (d.patient_id IN (SELECT patient_id FROM vt_recent)) AS x_recent,
  (d.name_zh IN('废用牙','无功能牙')) AS x_inelig,
  (d.drop_decid AND d.tooth ~ '^[5-8][1-5]$') AS x_decid,
  (d.code='K08' AND d.tooth ~ '^[1-4]8$') AS x_thirdmolar,
  (d.code='K08' AND COALESCE(d.name_zh,'') LIKE '%先天%') AS x_congenital,
  (d.code='K08' AND d.patient_id IN (SELECT patient_id FROM vt_ortho_pt)
     AND EXISTS(SELECT 1 FROM vt_treat t WHERE t.patient_id=d.patient_id AND t.tooth=d.tooth AND t.cat='surgical')) AS x_orthoextract,
  EXISTS(SELECT 1 FROM vt_bridge b WHERE b.patient_id=d.patient_id AND b.tooth=d.tooth) AS x_bridge,
  EXISTS(SELECT 1 FROM vt_norestor n WHERE n.patient_id=d.patient_id AND n.tooth=d.tooth) AS x_norestor,
  EXISTS(SELECT 1 FROM vt_refusal rf WHERE rf.patient_id=d.patient_id AND rf.tooth=d.tooth AND rf.cat=ANY(d.resolver) AND rf.at>=d.dx_at) AS x_refusal,
  EXISTS(SELECT 1 FROM vt_external xe WHERE xe.patient_id=d.patient_id AND xe.at>=d.dx_at::date) AS x_external
FROM vt_diag_raw d;
ANALYZE vt_final_raw;

\echo '════════ §C6 页面事实视角:牙位应治未治全集(无预过滤)vs 召回 ════════'
\echo '---- 全集规模(口径=页面人眼:有诊断 + 本牙后续无对应治疗 + 未被更晚诊断取代)----'
CREATE TEMP VIEW vt_open AS SELECT * FROM vt_final_raw WHERE NOT resolved AND NOT x_superseded;
SELECT count(*) AS 应治未治牙, count(DISTINCT patient_id) AS 患者,
  count(*) FILTER (WHERE recalled) AS 已召, count(*) FILTER (WHERE NOT recalled) AS 未召 FROM vt_open;

\echo '---- 未召逐层归因(硬→软;9=无法解释=疑似漏召 bug)----'
CREATE TEMP VIEW vt_open_fn AS
SELECT *,
  CASE
    WHEN x_compliance                                   THEN '1_合规闸(已故/勿扰/停用)'
    WHEN x_inelig OR x_decid                             THEN '2_废用牙/乳牙'
    WHEN x_thirdmolar OR x_congenital OR x_orthoextract  THEN '3_§E剔除(智齿/先天/正畸减数)'
    WHEN x_bridge OR x_norestor OR x_refusal OR x_external THEN '3b_六类修复(桥跨度/无修复指征/无意愿/外院)'
    WHEN x_cooldown                                      THEN '4_冷静期内(页面可见,未到召回时机)'
    WHEN x_future                                        THEN '5_⚠软豁免·未来预约⑤b(患者级blanket,不证明本牙被处理)'
    WHEN x_recent                                        THEN '6_⚠软豁免·近期到诊⑤f(患者级,同上)'
    ELSE                                                     '9_真·无法解释(疑似漏召bug)'
  END AS bucket
FROM vt_open WHERE NOT recalled;
SELECT bucket, count(*) AS 牙, count(DISTINCT patient_id) AS 患者 FROM vt_open_fn GROUP BY bucket ORDER BY bucket;
\echo '  判读:1-4=硬解释(真没问题) | 5-6=软豁免(患者级代理,不证明本牙会被处理 → 需人审,可能含真漏召,如沈静芳被科目级⑤d误挡) | 9=bug'
\echo '  ⚠ 5-6 不能算"干净"。⑤b 是纯患者级 blanket(有任何未来预约→压全部gap),与已删的⑤d 同类毛病,高价值病种(K08种植/K03修复)尤需人审。'

\echo '---- 9_真·无法解释 明细(召回算法 vs 牙位事实的真不一致,逐颗)----'
SELECT pt.name AS 患者, f.code, f.sub AS 场景, f.tooth AS 牙位, f.dx_at::date AS 诊断日, f.name_zh AS 诊断名
FROM vt_open_fn f JOIN patients pt ON pt.id=f.patient_id
WHERE f.bucket='9_真·无法解释' ORDER BY f.code, pt.name LIMIT 50;

\echo '---- 4_冷静期内 样例(页面看着应治未治,实为还没到召回时机)----'
SELECT pt.name AS 患者, f.code, f.tooth AS 牙位, f.dx_at::date AS 诊断日,
  (now()::date - f.dx_at::date) AS 诊断天数, f.name_zh AS 诊断名
FROM vt_open_fn f JOIN patients pt ON pt.id=f.patient_id
WHERE f.bucket LIKE '4_%' ORDER BY f.dx_at DESC LIMIT 12;

-- §C6b 全口码(K05 牙周 / K07 正畸)应治未治 vs 召回(无对应全口治疗晚于最新诊断)
\echo '════════ §C6b 全口码(K05牙周/K07正畸)应治未治 vs 召回 ════════'
WITH wm_dx AS (
  SELECT d.patient_id, d.content->>'code' code, max(d.occurred_at) last_dx
  FROM patient_facts d WHERE d.type='diagnosis_record' AND d.status='active' AND d.content->>'code' IN('K05','K07') GROUP BY 1,2),
wm_tx AS (
  SELECT patient_id, content->>'category' cat, max(occurred_at) last_tx FROM patient_facts
  WHERE type='treatment_record' AND kind='actual' AND status IN('active','fulfilled') AND content->>'category' IN('periodontic','orthodontic') GROUP BY 1,2),
wm_rec AS (
  SELECT fp.patient_id, pr.signals->>'subKey' sub FROM plan_reasons pr JOIN followup_plans fp ON fp.id=pr.plan_id
  WHERE fp.status IN('active','assigned') AND pr.signals->>'subKey' IN('perio_no_srp','ortho_no_consult') GROUP BY 1,2)
SELECT dx.code,
  count(*) AS 应治未治患者,
  count(*) FILTER (WHERE rec.patient_id IS NOT NULL) AS 已召,
  -- K05/K07 cooldownDays 均=30:诊断 < 30 天仍在冷静期,页面可见但未到召回时机(非漏召)
  count(*) FILTER (WHERE rec.patient_id IS NULL AND dx.last_dx > now() - interval '30 days') AS 冷静期内,
  count(*) FILTER (WHERE rec.patient_id IS NULL AND dx.last_dx <= now() - interval '30 days'
                    AND NOT(NOT p.active OR pp.do_not_contact OR pp.deceased)
                    AND dx.patient_id NOT IN(SELECT patient_id FROM vt_future)
                    AND dx.patient_id NOT IN(SELECT patient_id FROM vt_recent)) AS 未召_无明显理由
FROM wm_dx dx
JOIN patients p ON p.id=dx.patient_id JOIN patient_profiles pp ON pp.patient_id=dx.patient_id
LEFT JOIN wm_tx tx ON tx.patient_id=dx.patient_id AND tx.cat=CASE dx.code WHEN 'K05' THEN 'periodontic' ELSE 'orthodontic' END AND tx.last_tx>=dx.last_dx
LEFT JOIN wm_rec rec ON rec.patient_id=dx.patient_id AND rec.sub=CASE dx.code WHEN 'K05' THEN 'perio_no_srp' ELSE 'ortho_no_consult' END
WHERE tx.patient_id IS NULL
GROUP BY dx.code;

-- ════════════════════════════════════════════════════════════════════════
-- §F 误召检查:召了"应治未治",但事实上已对症治疗(= 用户口径"召回说没治、事实治了")
--   口径与生产一致:治疗 occurred_at >= 最新【诊断 ∪ 建议】(latestDxOfCode)才算已治。
--   ⚠ 规则:治疗【之后】又出现诊断/建议(哪怕同日、不同医生)= 新的未治需求 → 应召回,非误召。
--      (王思涵:正畸治疗08:05 < 同日正畸建议08:58 → 治疗早于最新发现 → 正确召回,不计误召)
--   牙位级误召见 §C2(resolved×recalled 的 t/t 格,应0);本层补【全口码 K05/K07】。
-- ════════════════════════════════════════════════════════════════════════
\echo '════════ §F 全口码误召(召了但 最新诊断/建议后 已对症治疗;应=空)════════'
WITH cfg(code, sub, cat, allcodes) AS (VALUES
   ('K05','perio_no_srp','periodontic', ARRAY['K05','SRP_RECOMMENDED']),
   ('K07','ortho_no_consult','orthodontic', ARRAY['K07','ORTHO_CONSULT_RECOMMENDED'])),
rec AS (SELECT fp.patient_id, pr.signals->>'subKey' sub FROM plan_reasons pr JOIN followup_plans fp ON fp.id=pr.plan_id
   WHERE fp.status IN('active','assigned') AND pr.signals->>'subKey' IN('perio_no_srp','ortho_no_consult') GROUP BY 1,2),
latest AS (SELECT d.patient_id, c.code, c.sub, c.cat, max(COALESCE(d.occurred_at,d.planned_for)) last_finding
   FROM patient_facts d JOIN cfg c ON d.content->>'code' = ANY(c.allcodes)
   WHERE d.status='active' AND d.type IN('diagnosis_record','recommendation_record') GROUP BY 1,2,3,4)
SELECT l.code, count(*) AS 误召_全口
FROM latest l JOIN rec r ON r.patient_id=l.patient_id AND r.sub=l.sub
WHERE EXISTS(SELECT 1 FROM patient_facts t WHERE t.patient_id=l.patient_id AND t.type='treatment_record'
   AND t.kind='actual' AND t.status IN('active','fulfilled') AND t.content->>'category'=l.cat
   AND t.occurred_at >= l.last_finding)
GROUP BY 1;
