-- ============================================================
-- 召回正确性扫描器(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;

-- ── 分类(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
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_同牙更晚诊断取代'
    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;

\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);
