import { Injectable, Logger } from '@nestjs/common';
import {
  PlanScenario,
  DiagnosisTreatmentMap,
  APPT_COMPLAINT_TO_CATEGORY,
  lookupDxTreatment,
  diagnosisCodeNameZh,
  treatmentCategoryNameZh,
} from '@pac/types';
import { PrismaService } from '../../../../prisma/prisma.service';
import type {
  PlanScenarioPlugin,
  ScenarioHit,
  ScenarioScope,
} from '../scenario.interface';
import { calcPriority } from '../priority-scorer';

/**
 * 潜在治疗新链召回(treatment_initiation_recall)— v2.1 重写
 *
 * **v2.1 关键变化**(对齐 canonical-fact-layer.md §四 + potential-treatment-recall.md §八):
 *   - 读独立 fact_type:diagnosis_record / treatment_record / recommendation_record
 *     不再 LIKE 自由文本,直接 `content->>'code' = 'K08'` 索引友好
 *   - 算分用 6 因子 priority-scorer(临床基线 × 时间窗 + 价值/转化/紧迫 + 信号置信)
 *   - 排除条件改读 treatment_record(独立)而非嵌套 encounter.treatments[]
 *
 * 3 个主子场景(单价由高到低):
 *
 *   #A 缺失牙 → 种植/桥/义齿(黄金窗 30-180 天,基线 60)
 *      触发:diagnosis_record.code='K08' 或 recommendation_record.code='IMPLANT_RECOMMENDED'
 *      排除:诊断后启动的 treatment_record(actual).category IN ('implant','prosthodontic')
 *
 *   #B 龋齿 → 充填(黄金窗 14-90 天,基线 45)
 *      触发:diagnosis_record.code='K02' 或 recommendation_record.code='FILLING_RECOMMENDED'
 *      排除:诊断后启动的 treatment_record(actual).category='restorative'
 *
 *   #C 牙周诊断 → SRP/翻瓣(黄金窗 30-120 天,基线 50)
 *      触发:diagnosis_record.code='K05' 或 recommendation_record.code='SRP_RECOMMENDED'
 *      排除:诊断后启动的 treatment_record(actual).category='periodontic'
 *
 *   #D 牙髓/根尖周炎 → 根管(黄金窗 14-90 天,基线 52)
 *      触发:diagnosis_record.code='K04'
 *      排除:诊断后启动的 treatment_record(actual).category='endodontic'
 *      (K04 拖久 → 根尖脓肿/拔牙,故窗口比龋齿略紧、紧迫临界 45 天)
 *
 * ⭐ 入池窗口 vs 黄金窗(W3 末修正,别再混用):
 *   - goldenRange = 算分用(priority-scorer.computeTimeWindowFactor):窗内 1.0,过早 0.6→1.0,
 *     过晚从 end 到 2×end 衰减到 0.4。**只影响优先级,不当入池硬边界**。
 *   - 入池窗口 = [start, INTAKE_MAX_DAYS]:下界 start(刚诊断 <start 天还在考虑期,不急召);
 *     上界放宽到 INTAKE_MAX_DAYS(缺牙拖 1 年比拖 3 个月更该召!超 golden 上界仍入池,交 scorer 衰减)。
 *   - 旧 bug:入池上界 = golden end → 缺牙 >180 天直接踢出池,scorer 的"过晚"分支成死代码。
 *
 * ⭐ 触发时间 = COALESCE(occurred_at, planned_for):
 *   diagnosis 用 occurred_at;recommendation 是 planned kind,occurred_at 为 null、时间在 planned_for。
 *   旧 bug:SQL 写 `occurred_at IS NOT NULL` → recommendation 触发(recCodes)全部命不中,死代码。
 *
 * ⚠️ 牙位无法做"按牙排除"(数据源限制,非 bug):
 *   "已做治疗"的真凭据是结算表 fact_settlement_out(花钱才算做)→ 落 actual treatment,
 *   而结算按收费项记账,**结构上无牙位列**。故排除只能 patient 级(同 patient 做过同类治疗即排)。
 *   treat_plan 里 46% 有牙位,但落的是 planned(≠已做),不能用于排除。反馈 DW 在结算补牙位再升级。
 *
 * 通用过滤(每个子场景 SQL 自己写,共用片段后续抽):
 *   active + 非 DNC + 非 deceased(phone 缺失暂不强制,DW 不提供)
 */
@Injectable()
export class TreatmentInitiationRecallScenario implements PlanScenarioPlugin {
  readonly key = PlanScenario.TREATMENT_INITIATION_RECALL;
  private readonly logger = new Logger(TreatmentInitiationRecallScenario.name);

  /// W3 末决策:**移除入池时间上界硬过滤**(原 730 天)
  /// - 临床缺口不会自愈(K08 缺牙/K07 错颌/K00 先天缺失 拖 5 年仍是缺口,该召还是该召)
  /// - 算法层已有衰减:priority-scorer.computeTimeWindowFactor 对超 goldenRange.end 的诊断按 [end → 2×end] 衰减到 0.4
  /// - 真正"该止损"的判断让 scorer 给低分 + UI 排到尾部 + 客服自选,**不在 SQL 层硬切**
  /// - 副作用:召回池可能膨胀(老 K05 牙周诊断永远在),但 scorer 衰减 + priority 排序自然处理
  private static readonly INTAKE_MAX_DAYS = null;

  // ─ 子场景配置 ─
  //
  // 临床窗口 / 类别 / 紧迫临界 **统一从 canonical-codes.DiagnosisTreatmentMap 读**(单一真理源),
  // 本表只装跟"召回话术 / 业务话目标"相关的 UI 文案 + base 基线分。
  // 改窗口/类别去改字典,不要在这里硬编码,否则 scenario / chain-composer / scorer 会跟字典背离。
  // W3 末扩 9 子场景(K00-K08 全覆盖,跟 DiagnosisTreatmentMap 一对一)
  // base 分级:K08 缺牙 60(单价 1.5-3 万)/ K07 正畸 55(单价 3-5 万)/ K04 牙髓 52(防恶化)/
  //          K05 牙周 50 / K02 龋齿 45 / K03 牙体 35 / K06 牙龈 35 / K01 阻生 30 / K00 萌出 25
  // 不漏码原则:host 数据出现的诊断都进召回池,priority 自然衰减(低 base + 慢窗口 → 排到后面)
  private static readonly SUB_SCENARIOS = {
    missing_tooth: {
      base: 60,
      primaryCode: 'K08', // → DiagnosisTreatmentMap.K08(cooldownDays/windowDays/urgencyDayThreshold/categories)
      dxCodes: ['K08'],
      recCodes: ['IMPLANT_RECOMMENDED'],
      label: '缺失牙未启动修复',
      goal: '邀约启动缺失牙修复(种植/桥/义齿),避免邻牙倾斜 / 对颌伸长',
    },
    ortho_no_consult: {
      base: 55,
      primaryCode: 'K07',
      dxCodes: ['K07'],
      recCodes: ['ORTHO_CONSULT_RECOMMENDED'],
      label: '错颌畸形未启动正畸',
      goal: '邀约做正畸初诊评估,把握矫治窗口(尤其混合牙列期 8-12 岁 / 成人骨健全期)',
    },
    endo_no_rct: {
      base: 52,
      primaryCode: 'K04',
      dxCodes: ['K04'],
      recCodes: ['RCT_RECOMMENDED'],
      label: '牙髓炎/根尖周炎未做根管',
      goal: '邀约尽快做根管治疗,避免发展为根尖脓肿 / 拔牙',
    },
    perio_no_srp: {
      base: 50,
      primaryCode: 'K05',
      dxCodes: ['K05'],
      recCodes: ['SRP_RECOMMENDED'],
      label: '牙周炎未做基础治疗',
      goal: '邀约做牙周基础治疗(SRP / 翻瓣),控制炎症发展',
    },
    caries_no_filling: {
      base: 45,
      primaryCode: 'K02',
      dxCodes: ['K02'],
      recCodes: ['FILLING_RECOMMENDED'],
      label: '龋齿未做充填',
      goal: '邀约尽快做龋齿充填,避免发展为牙髓炎',
    },
    hard_tissue_damage: {
      base: 35,
      primaryCode: 'K03',
      dxCodes: ['K03'],
      recCodes: ['HARD_TISSUE_REPAIR_RECOMMENDED', 'CROWN_RECOMMENDED'],
      // K03 复合:残根残冠→拔(surgical)/ 楔缺→充填(restorative)/ 大缺损→冠桥(prosthodontic)
      // 三类 actual 任一存在即排除(见 DxMap K03.categories)— 数据噪音可控
      label: '牙体损伤未修复',
      goal: '邀约处理牙体硬组织损伤(楔状缺损/残根/残冠 → 充填/嵌体/拔除修复)',
    },
    gum_alveolar_lesion: {
      base: 35,
      primaryCode: 'K06',
      // K06 病种保留为 K06,不归 K05(不改诊断码原则)— 临床路径同 periodontic+surgical
      dxCodes: ['K06'],
      recCodes: ['GUM_TREATMENT_RECOMMENDED'],
      label: '牙龈/牙槽嵴疾患未处置',
      goal: '邀约处理牙龈/牙槽嵴问题(牙龈萎缩/增生/瘤/根分叉病变/系带异常 → 牙周治疗或手术)',
    },
    impacted_tooth: {
      base: 30,
      primaryCode: 'K01',
      // 智齿阻生临床不强制拔除(无症状可观察);base 低让分数沉底,真急的患者靠 urgencyBonus 升上来
      dxCodes: ['K01'],
      recCodes: ['EXTRACTION_RECOMMENDED'],
      label: '阻生牙未拔除',
      goal: '邀约评估阻生牙是否需拔除(智齿冠周炎反复 / 顶坏邻牙 / 正畸需要)',
    },
    jaw_cyst: {
      base: 50,
      primaryCode: 'K09',
      dxCodes: ['K09'],
      recCodes: ['JAW_CYST_REMOVAL_RECOMMENDED'],
      // 颌骨囊肿临床必处理(不摘除会继续扩大压迫邻牙/神经),base 中等偏高
      label: '颌骨囊肿未处理',
      goal: '邀约尽快做颌骨囊肿摘除术,避免囊肿扩大压迫邻牙 / 神经',
    },
    development_eruption: {
      base: 25,
      primaryCode: 'K00',
      // K00 真病种(乳牙滞留 / 多生牙 / 先天缺失 / 釉质发育不全 / 萌出障碍)— 不含"乳牙列""混合牙列"等正常态(yaml 不映射)
      // 儿童居多,base 最低;windowDays 365 给足观察期
      dxCodes: ['K00'],
      recCodes: ['ERUPTION_INTERVENTION_RECOMMENDED'],
      label: '牙发育/萌出异常未处置',
      goal: '邀约评估处置(乳牙滞留/多生牙拔除 · 先天缺失修复 · 釉质发育不全美容修复 · 萌出障碍助萌)',
    },
  } as const;

  constructor(private readonly prisma: PrismaService) {}

  async selectHits(scope: ScenarioScope): Promise<ScenarioHit[]> {
    const hits: ScenarioHit[] = [];
    for (const [subKey, cfg] of Object.entries(
      TreatmentInitiationRecallScenario.SUB_SCENARIOS,
    )) {
      const subHits = await this.runSubScenario(scope, subKey, cfg);
      hits.push(...subHits);
    }
    this.logger.debug(
      `initiation total hits: ${hits.length}(missing_tooth/caries/perio 合计)`,
    );
    return hits;
  }

  // ─────────────────────────────────────────────────────────
  // 子场景跑 SQL + 算 6 因子分
  // ─────────────────────────────────────────────────────────

  private async runSubScenario(
    scope: ScenarioScope,
    subKey: string,
    cfg: (typeof TreatmentInitiationRecallScenario.SUB_SCENARIOS)[keyof typeof TreatmentInitiationRecallScenario.SUB_SCENARIOS],
  ): Promise<ScenarioHit[]> {
    // 临床窗口 / 类别 / 紧迫临界从 canonical-codes.DiagnosisTreatmentMap 单一真理源读
    const rule = lookupDxTreatment(cfg.primaryCode);
    if (!rule) {
      throw new Error(
        `SUB_SCENARIOS[${subKey}].primaryCode=${cfg.primaryCode} 在 DiagnosisTreatmentMap 中找不到 — ` +
          `检查 canonical-codes.ts(单一真理源)`,
      );
    }
    const start = rule.cooldownDays;
    const goldenRange: [number, number] = [rule.cooldownDays, rule.windowDays];
    const dxCodes = cfg.dxCodes as readonly string[];
    const recCodes = cfg.recCodes as readonly string[];
    const allCodes = [...dxCodes, ...recCodes];
    const excludeCats = rule.categories as readonly string[];

    // W4 末:按 excludeCats 算出对应的预约 complaint 文本(host appointment.complaint_category 字段值)
    //   APPT_COMPLAINT_TO_CATEGORY 反查:filter complaint where category ∈ excludeCats
    //   例 K07 excludeCats=['orthodontic'] → complaintTexts=['正畸','早矫']
    //       K08 excludeCats=['implant','prosthodontic'] → ['种植','修复']
    //   用于 ⑤d:sig 之后有 complaint 匹配的 appointment → 患者已 entered 治疗链,不召回
    const complaintTexts = Object.entries(APPT_COMPLAINT_TO_CATEGORY)
      .filter(([, cat]) => excludeCats.includes(cat))
      .map(([text]) => text);

    // ╔═════════════════════════════════════════════════════════════════════╗
    // ║ 召回 SQL 完整解读(initiation = 潜在治疗新链召回)                  ║
    // ║                                                                      ║
    // ║ 一句话:"找诊断了 / 医生推荐了,但**没启动**对应治疗的患者"          ║
    // ║                                                                      ║
    // ║ ┌─ 主查询(找触发信号 sig + 算 daysSince) ─────────────────────────┐ ║
    // ║ │ 从 patients 表起,JOIN patient_profiles + patient_facts(sig)     │ ║
    // ║ │ 每命中一条 (patient × signal_fact) 组合返回一行                   │ ║
    // ║ │ daysSince = 今天 - 触发时间(单位:天,给 scorer 用)              │ ║
    // ║ └────────────────────────────────────────────────────────────────────┘ ║
    // ║                                                                      ║
    // ║ ┌─ ① 隔离闸(强制基线) ──────────────────────────────────────────┐ ║
    // ║ │ p.host_id = scope.hostId    + p.tenant_id = scope.tenantId       │ ║
    // ║ │ → PAC 三层模型的 platform+tenant 硬隔离,跨租户不漏              │ ║
    // ║ └────────────────────────────────────────────────────────────────────┘ ║
    // ║                                                                      ║
    // ║ ┌─ ② 合规闸 ────────────────────────────────────────────────────┐ ║
    // ║ │ p.active = true              (患者主档活跃)                     │ ║
    // ║ │ pp.do_not_contact = false    (没勾"勿打扰")                    │ ║
    // ║ │ pp.deceased = false          (没标"已故")                       │ ║
    // ║ │ → 法务 / 投诉风险硬过滤,任一不满足直接踢                       │ ║
    // ║ └────────────────────────────────────────────────────────────────────┘ ║
    // ║                                                                      ║
    // ║ ┌─ ③ 触发信号(sig 这条 fact 必须是"该召回理由") ───────────────┐ ║
    // ║ │ sig.status = 'active'                  (当前版本,非历史)        │ ║
    // ║ │ sig.type IN ('diagnosis_record',       (诊断)                  │ ║
    // ║ │              'recommendation_record')  (医生 / LLM 推荐)         │ ║
    // ║ │ sig.content->>'code' = ANY(allCodes)   (K08/K07/.. 或 *_RECOMMENDED)│ ║
    // ║ └────────────────────────────────────────────────────────────────────┘ ║
    // ║                                                                      ║
    // ║ ┌─ ④ 触发时间下界(冷静期 cooldown) ────────────────────────────┐ ║
    // ║ │ COALESCE(sig.occurred_at, sig.planned_for) <= now - cooldownDays │ ║
    // ║ │ → 刚诊断 < cooldown 天(K08=30天/K04=14天/...)还在医生考虑期,    │ ║
    // ║ │   客服过早打扰不合适。从字典 DiagnosisTreatmentMap[code] 读     │ ║
    // ║ │ 注:旧版还有上界(730天),W3 末已废 — 缺口不会自愈,scorer 自然衰减│ ║
    // ║ └────────────────────────────────────────────────────────────────────┘ ║
    // ║                                                                      ║
    // ║ ┌─ ⑤a 排除闸:NOT EXISTS 同类 actual 治疗(牙位级 W4 末升级)─┐ ║
    // ║ ┌─ ⑤c 排除:同牙位拔除(W4 末加,临床:拔了就终结)────────────┐ ║
    // ║ ┌─ ⑤b 排除:患者已有未来预约 ────────────────────────────────┐ ║
    // ║ │ tx.type = 'treatment_record' AND tx.kind = 'actual'              │ ║
    // ║ │ tx.status IN ('active','fulfilled')   (完成的 actual 是 fulfilled)│ ║
    // ║ │ tx.content->>'category' = ANY(excludeCats)                       │ ║
    // ║ │     excludeCats 来自 DxMap.K08.categories = ['implant','prostho']│ ║
    // ║ │ tx.occurred_at >= sig.occurred_at  ⭐ 时间方向                  │ ║
    // ║ │     只算"诊断之后才启动"的治疗,历史旧治疗(可能是另一颗牙)不算  │ ║
    // ║ │                                                                  │ ║
    // ║ │ W4 末升级:**牙位级 overlap**(EMR.treat_plan 带 48.7% 牙位)    │ ║
    // ║ │   信号有牙位 + actual 有牙位 → tooth array overlap 判排除         │ ║
    // ║ │   信号无牙位(K05 全口诊断)→ 不算 tooth,patient/category 级       │ ║
    // ║ │   actual 无牙位(全口洁治)→ 视为"全口覆盖"→ 仍排除 category 全位 │ ║
    // ║ │   ⇒ 罗国标 K04 14;15 之前误排(actual endo 在 36),现在正确召回   │ ║
    // ║ └────────────────────────────────────────────────────────────────────┘ ║
    // ║                                                                      ║
    // ║ COALESCE(occurred_at, planned_for):                                  ║
    // ║   diagnosis_record 是 actual(已发生)→ 用 occurred_at                ║
    // ║   recommendation_record 是 planned(未发生)→ 用 planned_for          ║
    // ║                                                                      ║
    // ║ 输出:每个命中 (patient × sig) 一行,后段 byPatient Map 去重         ║
    // ║      只留 daysSince 最大那条(最早诊断 = 最有召回价值)              ║
    // ╚═════════════════════════════════════════════════════════════════════╝
    const rows: HitRow[] = await this.prisma.$queryRaw`
      SELECT
        p.id                        AS patient_id,
        p.external_id               AS patient_external_id,
        sig.id                      AS signal_fact_id,
        sig.type                    AS signal_type,
        sig.content->>'code'        AS signal_code,
        sig.content->>'tooth_position' AS tooth,
        sig.content->>'extracted_by' AS extracted_by,
        sig.content->>'confidence'  AS confidence,
        sig.clinic_id               AS clinic_id,
        COALESCE(sig.occurred_at, sig.planned_for) AS signal_occurred_at,
        EXTRACT(DAY FROM ${scope.now}::timestamptz - COALESCE(sig.occurred_at, sig.planned_for))::int AS days_since
      FROM patients p
      JOIN patient_profiles pp ON pp.patient_id = p.id
      JOIN patient_facts sig ON sig.patient_id = p.id
      WHERE p.host_id = ${scope.hostId}::uuid              -- ① 隔离闸
        AND p.tenant_id = ${scope.tenantId}                -- ① 隔离闸
        AND p.active = true                                -- ② 合规闸
        AND pp.do_not_contact = false                      -- ② 合规闸
        AND pp.deceased = false                            -- ② 合规闸
        AND sig.status = 'active'                          -- ③ 触发信号 active 版本
        AND sig.type IN ('diagnosis_record', 'recommendation_record')  -- ③ 信号类型
        AND sig.content->>'code' = ANY(${allCodes}::text[])            -- ③ 信号 code 命中
        AND COALESCE(sig.occurred_at, sig.planned_for) IS NOT NULL     -- ④ 时间不为空
        AND COALESCE(sig.occurred_at, sig.planned_for) <= ${this.daysAgo(scope.now, start)}::timestamptz  -- ④ 过 cooldown
        AND NOT EXISTS (                                   -- ⑤a 排除:同类 actual 治疗(已开始做)
          SELECT 1 FROM patient_facts tx
          WHERE tx.patient_id = p.id
            AND tx.type = 'treatment_record'
            AND tx.kind = 'actual'
            AND tx.status IN ('active', 'fulfilled')       -- actual 完成 status=fulfilled
            AND tx.content->>'category' = ANY(${excludeCats}::text[])
            AND tx.occurred_at >= COALESCE(sig.occurred_at, sig.planned_for)  -- ⭐ 时间方向:诊断之后
            -- W4 末升级:牙位级 overlap(详见上面 ⑤a 注释 box)
            AND (
              -- 信号无牙位(全口诊断如 K05)→ patient/category 级排除,跟现状一致
              COALESCE(NULLIF(trim(sig.content->>'tooth_position'), ''), '') = ''
              -- actual 无牙位(全口洁治/牙周治疗)→ 视为"全口覆盖"→ 排除该 category 所有信号
              OR COALESCE(NULLIF(trim(tx.content->>'tooth_position'), ''), '') = ''
              -- 双方都有牙位 → tooth array overlap
              -- 牙位字符串形如 "15;24;26" / "15 B;24 B"(B/L/M 等牙面后缀)
              -- 1. regexp_replace 把非数字非分号字符(空格/B/L/M 牙面)替换成 ; → "15;24;26;"
              -- 2. string_to_array(...,';') → 含空字符串元素 {"15","24","26",""}
              -- 3. array_remove(...,'') 去掉空元素 → {"15","24","26"}
              -- 4. && PG array overlap 操作符,任一元素相同即 true
              -- ⚠️ 必须 array_remove '' — 否则两个 array 都含空字符串时 '' = '' 误返 true
              OR array_remove(
                   string_to_array(regexp_replace(sig.content->>'tooth_position', '[^0-9;]+', ';', 'g'), ';'),
                   ''
                 ) && array_remove(
                   string_to_array(regexp_replace(tx.content->>'tooth_position', '[^0-9;]+', ';', 'g'), ';'),
                   ''
                 )
            )
        )
        AND NOT EXISTS (                                   -- ⑤c 排除:同牙位拔除 actual(W4 末)
          -- 临床原则:拔除是任何牙病的终结处理 — 拔了就不需要任何后续治疗(根管/充填/牙周等)
          -- 例:陈化冰 K05 17;18 同日拔了 17;18 → 不该召牙周;K04 46 拔了 → 不该召根管
          -- 不限时间方向(拔了就拔了,后期 host 写"牙列缺失" 类诊断只是记录现状,不该召)
          -- 仅对**有具体牙位的信号**生效(全口诊断 K05 不走这里,避免任何拔牙误排全口)
          SELECT 1 FROM patient_facts surg
          WHERE surg.patient_id = p.id
            AND surg.type = 'treatment_record'
            AND surg.kind = 'actual'
            AND surg.status IN ('active', 'fulfilled')
            AND surg.content->>'category' = 'surgical'
            AND COALESCE(NULLIF(trim(sig.content->>'tooth_position'), ''), '') != ''  -- 信号必须有牙位
            AND array_remove(
                  string_to_array(regexp_replace(sig.content->>'tooth_position', '[^0-9;]+', ';', 'g'), ';'),
                  ''
                ) && array_remove(
                  string_to_array(regexp_replace(surg.content->>'tooth_position', '[^0-9;]+', ';', 'g'), ';'),
                  ''
                )
        )
        AND NOT EXISTS (                                   -- ⑤b 排除:患者已有未来预约
          -- 召回目的 = 让客服建预约。患者已经有未来预约 → 客服不需要再 push,医生到诊现场处理即可
          -- 不按 complaint_category 匹配(陆伟根:K07 诊断 + 修复预约 → 反正会来诊)
          -- 只看 status='active' + future
          SELECT 1 FROM patient_facts appt
          WHERE appt.patient_id = p.id
            AND appt.type = 'appointment_record'
            AND appt.status = 'active'
            AND COALESCE(appt.planned_for, appt.occurred_at) > ${scope.now}::timestamptz
        )
        AND NOT EXISTS (                                   -- ⑤d 排除:sig 之后 complaint 匹配的预约(W4 末加)
          -- 跟 chain-composer.collectS2Facts 同口径:complaint_category 命中 expectedCategories → 患者已 entered
          -- 例 林菲菲:K07@2026-04-14 诊断 + appointment(complaint=正畸)@2026-04-26 → 已进入正畸链 → 排除
          -- "现在召回只做新链" 原则:entered/ongoing 患者不属于新链召回,等 W5+ 治疗链内召回 scenario
          -- active 未到诊 + fulfilled 已到诊都算(只要有 complaint 匹配)
          SELECT 1 FROM patient_facts appt
          WHERE appt.patient_id = p.id
            AND appt.type = 'appointment_record'
            AND appt.status IN ('active', 'fulfilled')
            AND COALESCE(appt.planned_for, appt.occurred_at) >= COALESCE(sig.occurred_at, sig.planned_for)
            AND EXISTS (
              SELECT 1
              FROM unnest(string_to_array(COALESCE(appt.content->>'complaint_category', ''), ',')) AS c
              WHERE trim(c) = ANY(${complaintTexts}::text[])
            )
        )
    `;

    // 同 patient 多个命中信号 → 取最早(daysSince 最大)作为主 hit
    const byPatient = new Map<string, HitRow>();
    for (const r of rows) {
      const existing = byPatient.get(r.patient_id);
      if (!existing || r.days_since > existing.days_since) {
        byPatient.set(r.patient_id, r);
      }
    }

    // 为算 6 因子,一次性查所有命中 patient 的 persona + recent execution
    const patientIds = [...byPatient.keys()];
    const personaCtx = await this.fetchPersonaContext(patientIds);
    const execCtx = await this.fetchRecentExecutions(patientIds, scope.now);

    const hits: ScenarioHit[] = [];
    for (const [patientId, r] of byPatient.entries()) {
      const persona = personaCtx.get(patientId);
      const confidence = r.confidence
        ? Number(r.confidence)
        : r.signal_type === 'recommendation_record'
          ? 0.8 // 默认 LLM/规则抽出的 0.8
          : 1.0; // 结构化诊断 1.0

      const { score, breakdown } = calcPriority({
        base: cfg.base,
        daysSince: r.days_since,
        goldenRange,
        valueScore: persona?.valueScore ?? null,
        riskScore: persona?.riskScore ?? null,
        recentExecutions: execCtx.get(patientId) ?? [],
        signalConfidences: [confidence],
        urgencyDayThreshold: rule.urgencyDayThreshold,
      });

      const toothStr = r.tooth ? ` · 牙位 ${r.tooth}` : '';
      const sourceStr =
        r.signal_type === 'recommendation_record'
          ? '(医生建议)'
          : '(诊断)';
      // 触发信号类型(原 enum,不语义化;前端用 triggerTypeLabelZh 翻译)
      const triggerType =
        r.signal_type === 'recommendation_record' ? 'recommendation' : 'diagnosis';
      hits.push({
        patientId,
        patientExternalId: r.patient_external_id,
        // reason 文本兜底(AI prompt / 调试用,前端不依赖此字段)
        reason: `${cfg.label}${toothStr} — ${diagnosisCodeNameZh(r.signal_code)}${sourceStr} ${r.days_since} 天前,未启动${excludeCats.map(treatmentCategoryNameZh).join(' / ')}`,
        priorityScore: score,
        goal: cfg.goal,
        recommendedRole: 'staff',
        recommendedChannel: 'phone',
        // recommendedAt 不赋值(留 null):v1 没有"最佳触达时间"算法,全填当天无意义,
        // 前端已移除推荐时间展示/排序。待有真实择时逻辑再启用。
        // 目标诊所 = 诊断出该未治疗需求的诊所(患者最可能回访的地方)
        targetClinicId: r.clinic_id ?? null,
        evidence: {
          // [0] = 触发 fact(对应 signals.triggers[0]);其余是相关参考 fact
          factIds: [r.signal_fact_id],
        },
        subKey,
        // 结构化召回信号(DB 存 raw enum / canonical code,前端字典翻译富文本)
        signals: {
          subKey,
          triggers: [{ type: triggerType, code: r.signal_code }],
          toothPosition: r.tooth ?? null,
          daysSince: r.days_since,
          expectedCategories: [...excludeCats],
        },
        priorityBreakdown: breakdown,
      });
    }
    return hits;
  }

  // ─────────────────────────────────────────────────────────
  // 算分需要的辅助数据查询
  // ─────────────────────────────────────────────────────────

  /// 拉每个 patient 当前 active persona 的 value / recall_risk 两个 score
  private async fetchPersonaContext(
    patientIds: string[],
  ): Promise<Map<string, { valueScore: number | null; riskScore: number | null }>> {
    if (patientIds.length === 0) return new Map();
    const rows = await this.prisma.$queryRaw<
      Array<{ patient_id: string; key: string; score: number | null }>
    >`
      SELECT p.patient_id, pf.key, pf.score
      FROM personas p
      JOIN persona_features pf ON pf.persona_id = p.id
      WHERE p.patient_id = ANY(${patientIds}::uuid[])
        AND p.superseded_at IS NULL
        AND pf.key IN ('value', 'recall_risk')
    `;
    const ctx = new Map<string, { valueScore: number | null; riskScore: number | null }>();
    for (const r of rows) {
      const cur = ctx.get(r.patient_id) ?? { valueScore: null, riskScore: null };
      if (r.key === 'value') cur.valueScore = r.score;
      else if (r.key === 'recall_risk') cur.riskScore = r.score;
      ctx.set(r.patient_id, cur);
    }
    return ctx;
  }

  /// 拉每个 patient 最近 30 天的 plan_executions outcome(用于 likelihoodBonus)
  private async fetchRecentExecutions(
    patientIds: string[],
    now: Date,
  ): Promise<Map<string, Array<{ outcome: string }>>> {
    if (patientIds.length === 0) return new Map();
    const cutoff = new Date(now.getTime() - 30 * 86400_000);
    const rows = await this.prisma.$queryRaw<
      Array<{ patient_id: string; outcome: string }>
    >`
      SELECT fp.patient_id, pe.outcome
      FROM plan_executions pe
      JOIN followup_plans fp ON fp.id = pe.plan_id
      WHERE fp.patient_id = ANY(${patientIds}::uuid[])
        AND pe.created_at >= ${cutoff}::timestamptz
    `;
    const ctx = new Map<string, Array<{ outcome: string }>>();
    for (const r of rows) {
      const arr = ctx.get(r.patient_id) ?? [];
      arr.push({ outcome: r.outcome });
      ctx.set(r.patient_id, arr);
    }
    return ctx;
  }

  private daysAgo(now: Date, n: number): Date {
    return new Date(now.getTime() - n * 86400_000);
  }
}

interface HitRow {
  patient_id: string;
  patient_external_id: string;
  signal_fact_id: string;
  signal_type: string; // 'diagnosis_record' | 'recommendation_record'
  signal_code: string;
  tooth: string | null;
  extracted_by: string | null;
  confidence: string | null; // pg 转 string,JS Number 转换
  clinic_id: string | null; // 触发诊断 fact 的诊所 → plan.targetClinicId
  signal_occurred_at: Date;
  days_since: number;
}
