# FRIDAY SaaS Cold Import Manifest(首版 — 仅 patient 试跑)
#
# 数据源:FRIDAY SaaS MySQL(customer 库)。cold-import 不支持 MySQL 直连,
#   走 CSV 文件模式:host 端**纯导原始表**(SELECT * FROM tb),不做任何 join。
#   电话在 1:N 的 customer_contacts 里 → 由 transforms.lookup 按 customer_id join、
#   挑默认号(is_default→本人→最早)填成 patient.phone 标量。宿主零特殊处理。
#
# ── 方案 B:合成"市场"集团 tenant;品牌降为 source_unit 命名空间 ──
#   FRIDAY 是多品牌 SaaS(30+ 独立品牌 tenant_id,各含 1-8 诊所 organization_id)。
#   PAC tenant = 合成 friday-market(整个市场一个租户,便于 FRIDAY 作为运营方跨品牌召回);
#   品牌(源 tenant_id 列,宿主原生名)→ patients.source_unit(集团内给 external_id 消歧:同号不同品牌=不同人);
#   诊所 organization_id → 后续 orgScope 隔离维度。
#   ⚠️ 隔离靠 orgScope/source_unit,非 tenant 边界 → 生产必须「禁止空 orgScope」。
#      若要品牌级硬隔离,改为 tenant_field: tenant_id + tenant_map(每品牌一个 PAC tenant)。
#
# 跑法:
#   pnpm sync -- --dir=./data/friday --dry-run     # 看翻译效果,不写库
#   pnpm sync -- --dir=./data/friday --no-recompute

host_name:    friday
display_name: FRIDAY SaaS(多品牌市场)
auto_sync:    false

tenant_id:                friday-market   # PAC 租户 = 合成市场根(静态)
identity_namespace_field: tenant_id       # patients.source_unit ← 源品牌列(宿主原生列名;与 PAC 租户概念的区分在 PAC 侧,宿主不改名)

amount_unit: yuan
timezone:    Asia/Shanghai

# 诊所名字典来源:病历表自带 organization_name(宿主原生列)。
# `pnpm refresh-clinic-names -- --dir=./data/friday` 据此 DISTINCT 出 id→名,写 host.clinicNames;
# /auth/session 合并进 dictionary.clinics 下发 → 工作台显示诊所中文名而非 GUID。
clinic_directory:
  table:      med_emr_info
  id_field:   organization_id
  name_field: organization_name


# ── 增量水位声明(2026-07 与 jvs-dw 统一;file 源放 manifest 顶层)──
# file 装载不消费水位(每轮全量装载,幂等去重兜底);每轮跑完与 jvs-dw 走同一段 finally
# 记账 cursor_after = run_start(sync_logs)。用途:delta 导出 WHERE 模板 / push 回放起点。
# 只列有 updated_gmt_at 的事件表;字典表(std_*)与 customer_contacts(无更新列)恒全量。
incremental:
  per_query:
    customer_basic_info:            { cursor_column: updated_gmt_at }
    appointment_base:               { cursor_column: updated_gmt_at }
    med_emr_info:                   { cursor_column: updated_gmt_at }
    med_check:                      { cursor_column: updated_gmt_at }
    patient_settlement:             { cursor_column: updated_gmt_at }
    patient_settlement_refund:      { cursor_column: updated_gmt_at }
    patient_settlement_spec_refund: { cursor_column: updated_gmt_at }
    settlement_modes:               { cursor_column: updated_gmt_at }
    customer_treat_plan:            { cursor_column: updated_gmt_at }
    customer_treat_plan_item:       { cursor_column: updated_gmt_at }
    customer_referee_circle:        { cursor_column: updated_gmt_at }
    customer_consult:               { cursor_column: updated_gmt_at }

tables:
  - { table: customer_basic_info, file: customer_basic_info.csv }   # 患者主档(原始)
  - { table: customer_contacts,   file: customer_contacts.csv }     # 联系方式 1:N(原始)
  - { table: appointment_base,    file: appointment_base.csv }      # 预约(原始,全状态)
  # ── EMR 病历链(W FRIDAY-EMR:Mongo med_emr_info status∈{3,4} 正式病历;export.sh 平铺导出)──
  # 数组字段(diag/treat/dispose/examine)导出保留原生数组(split_json_array 原生支持);
  # dispose_text/examine_text 是同数据的 JSON 字符串形态(emr_record 自由文本位要 string)。
  - { table: med_emr_info,            file: med_emr_info.json }     # 病历正文(Mongo,含 diag/treat 数组)
  - { table: med_check,               file: med_check.csv }         # 影像档案(挂 emr_id 病历号)
  - { table: std_check_class,         file: std_check_class.csv }   # 影像类型字典(class_code→class_name)
  - { table: std_diag,                file: std_diag.csv }          # 诊断字典(linkCode→std_code;K 码用)
  - { table: customer_treat_plan,     file: customer_treat_plan.csv }      # 治疗计划头(方案名/期望)
  - { table: customer_treat_plan_item, file: customer_treat_plan_item.csv } # 治疗计划行(牙位/术式/价格)
  - { table: patient_settlement,        file: patient_settlement.csv }        # 消费头单(status 1/3 且金额≥0)
  - { table: patient_settlement_refund, file: patient_settlement_refund.csv } # 整单退费(status=4 或 3 负额)
  - { table: patient_settlement_spec_refund, file: patient_settlement_spec_refund.csv } # 行级退费明细(is_refund=1)
  - { table: settlement_modes,        file: settlement_modes.csv }          # 支付通道长表
  - { table: customer_referee_circle, file: customer_referee_circle.csv }    # 转介绍圈(患者-患者边)
  - { table: customer_consult,        file: customer_consult.csv }           # 咨询(意向/未成交原因)

transforms:
  # ── 患者:把 1:N 联系方式归约成 patient.phone 标量(无需源侧 join)──
  - kind: lookup
    input: customer_basic_info
    output: customer_basic_info        # in-place 附加 phone 列
    from: customer_contacts
    left_key: id                       # 患者主键
    right_key: customer_id             # 联系方式指向患者的外键
    where:
      contacts_tel: { not_empty: true } # 空号候选剔除
    order_by:                          # 挑一条:默认号 → 本人(type=1) → 最早 id
      - { field: is_default,    dir: desc }
      - { field: contacts_type, dir: asc }
      - { field: id,            dir: asc }
    select:
      phone: contacts_tel              # patient.phone ← 选中候选的 contacts_tel
      # 该号与患者的关系码(host sys_dict 编码;实测 1=本人 78.7%(成人),儿童过半非本人号)。
      # 先落原码(preferences.contactPhone,isSelf=码=='1');完整标签字典待 sys_dict 可读后补。
      phone_relationship: relationship

  # ── 预约:丢草稿 → 全部预约(拼 scheduledAt)+ 已到诊拆成接诊事件(→ encounter)──
  # 与 jvs-dw 同款设计:一个预约源喂两张 canonical(appointment / encounter)。
  # status 编码两家一致(1正常/2改约/3取消/4到诊/5接诊/6结算/8walkin/9爽约);10=草稿丢弃。
  - kind: filter
    input: appointment_base
    output: _appt_valid
    where:
      appointment_status: { in: ['1', '2', '3', '4', '5', '6', '8', '9'] }   # 丢草稿 10
  # 全部预约:拼精确预约时刻(appointment_datetime_str 是区间且 14% 空 → 用 date + start)
  - kind: derive
    input: _appt_valid
    output: appointment_all_rows
    fields:
      scheduled_at_ts:
        op: concat
        parts: ['${appointment_date}', ' ', '${appointment_start}']   # "2026-07-13 15:30:00"
  # 已到诊(4/5/6)且有到诊时刻 → 接诊事件(startedAt = in_time)
  - kind: filter
    input: _appt_valid
    output: appointment_encounter_rows
    where:
      appointment_status: { in: ['4', '5', '6'] }
      in_time: { not_empty: true }

  # ═══════════ EMR 病历链(同 jvs-dw 分化模式:一张病历源喂多 canonical)═══════════
  # med_emr_info → emr_record(自由文本) + diagnosis(diag[] 拆行) + treatment_actual(treat[] 拆行)
  # 锚点:临床事实 sourceEncounterExternalId=emr_sub_id(同诊聚合);emr 自身挂 appointment_id(56.5%)。

  # ── E.1 病历自由文本行(emr.yaml 直接消费 med_emr_info,无需 project:
  #        assembler field_mapping 天然只取所需列,数组列不映射即不入 canonical)──

  # ── E.2 diag[] 拆行 → 诊断候选 ──
  - kind: split_json_array
    input: med_emr_info
    output: _diag_raw
    array_field: diag
    parent_keys:
      emr_sub_id: emr_sub_id
      emr_id: emr_id
      tenant_id: tenant_id
      organization_id: organization_id
      patient_id: patient_id
      user_id: user_id
      user_name: user_name
      clinic_time: clinic_time
      created_gmt_at: created_gmt_at
      updated_gmt_at: updated_gmt_at
    element_fields:
      value: value
      tooth_position: toothPosition
      link_code: linkCode
    where:
      value: { not_empty: true }    # 只要求有诊断名;linkCode 可空(仅 0.3% 有)

  # E.2.1 linkCode → std_diag 字典(diag_code UUID 全局唯一,LEFT JOIN 未命中 → std_code=null)
  - kind: lookup
    input: _diag_raw
    output: _diag_raw            # in-place 附加 std_code
    from: std_diag
    left_key: link_code
    right_key: diag_code
    select:
      std_code: std_code

  # E.2.2 归一 + K 码截取 + coalesce
  # 规则(用户定):std_code K 开头 → 截 K 大类;非 K(DA0E.50 等 ICD-11)/join 不上/linkCode 空
  #   → 自由文本处理:substring 产物("DA0"等)不在共享 K 码字典 → 落 _default → code=null,name 保留。
  #   linkCode 空(99.7%)→ coalesce 落到归一中文名 → 共享 diagnosis-kcode 字典救码。
  - kind: derive
    input: _diag_raw
    output: _diag_norm
    fields:
      value_norm:
        op: normalize
        from: value
      std_code_k:
        op: substring
        from: std_code
        start: 0
        end: 3
  - kind: derive
    input: _diag_norm
    output: diagnosis_rows
    fields:
      diag_code_src:
        op: coalesce
        from: ['std_code_k', 'value_norm']
      diag_external_id:
        op: concat
        parts: ['${emr_sub_id}', '|', '${std_code_k}', '|', '${value_norm}', '|', '${tooth_position}']

  # ── E.3 treat[] 拆行 → 本次治疗(actual;用户确认 treat=本次治疗,病历无计划字段)──
  # 注:treat 填充仅 25%(dispose 98% 是处置叙述,整段进 emr_record.disposal 不丢);
  #    若后续漏召,可加 dispose keyword 路由补 actual(Phase 2 评估)。
  - kind: split_json_array
    input: med_emr_info
    output: _treat_raw
    array_field: treat
    parent_keys:
      emr_sub_id: emr_sub_id
      emr_id: emr_id
      tenant_id: tenant_id
      organization_id: organization_id
      patient_id: patient_id
      user_id: user_id
      user_name: user_name
      clinic_time: clinic_time
      created_gmt_at: created_gmt_at
      updated_gmt_at: updated_gmt_at
    element_fields:
      value: value
      tooth_position: toothPosition
    where:
      value: { not_empty: true }
  # E.3.1 归一(新列 value_norm;external_id 仍用原始 value,保持与已摄入数据的幂等键连续)
  - kind: derive
    input: _treat_raw
    output: _treat_raw
    fields:
      value_norm:
        op: normalize
        from: value

  # E.3.2 三路分流(同 jvs-dw C.3 语义):建议→recommendation / 流程复查→review / 其余→actual
  # 实测(2026-07-18 全量):建议/推荐开头 153 行、复查类 60 行(其中拆线 50——不入 review,
  # 按 jvs-dw 2026-06-11 一线判例 拆线=术后护理=外科治疗动作,走 default→actual→enum 拆线→surgical)
  - kind: route_by_pattern
    input: _treat_raw
    field: value_norm
    routes:
      - output: _rec_raw
        when:
          starts_with: ['建议', '推荐']
      # 流程/复查/行政词表(取自 jvs-dw review_terms 核心,normalize 后 ASCII 形态)
      - output: _review_raw
        when:
          equals:
            - 常规复查
            - 复查
            - 定期复查
            - 检查
            - 初诊检查
            - 正畸复查
            - 正畸检查
            - 正畸复诊
            - 种植复查
            - 牙周复查
            - 保持器复诊
            - 暂观
            - 观察
            - 观察随访
            - 随诊观察
            - 随访观察
            - 无治疗
            - 重复
            - 取资料
            - 转诊
            - 正畸咨询
            - 正畸会诊
            - 方案沟通
            - 沟通治疗方案
            - 听方案
            - 缴费
            - 未拍片
      - output: _actual_raw
        when:
          default: true

  # E.3.3 真治疗 → actual
  - kind: derive
    input: _actual_raw
    output: treatment_actual_rows
    fields:
      category_raw:
        op: coalesce
        from: ['value_norm']
      treat_external_id:
        op: concat
        parts: ['${emr_sub_id}', '|treat|', '${value}', '|', '${tooth_position}']

  # E.3.4 流程复查 → review(category 固定哨兵,assembler 翻成 review)
  - kind: derive
    input: _review_raw
    output: treatment_review_rows
    fields:
      category_raw:
        op: concat
        parts: ['_review_sentinel']
      treat_external_id:
        op: concat
        parts: ['${emr_sub_id}', '|treat|', '${value}', '|', '${tooth_position}']

  # E.3.5 建议/推荐 → recommendation(共享 recommendation-code 字典翻召回码)
  - kind: derive
    input: _rec_raw
    output: recommendation_rows
    fields:
      rec_external_id:
        op: concat
        parts: ['${emr_sub_id}', '|rec|', '${value}', '|', '${tooth_position}']

  # ── E.4 影像:med_check + 类型字典 → class_name(modality 经共享字典翻)──
  - kind: lookup
    input: med_check
    output: image_rows
    from: std_check_class
    left_key: class_code
    right_key: class_code
    select:
      class_name: class_name

  # ── E.5 治疗计划:行表 join 头表拿 诊所/方案名 → planned 候选 ──
  - kind: lookup
    input: customer_treat_plan_item
    output: _plan_item_joined
    from: customer_treat_plan
    left_key: treat_plan_id
    right_key: id
    select:
      organization_id: organization_id
      plan_name: plan_name
  # 头表 lookup 未命中 / 头表无诊所 → 计划行无法归属诊所,显式丢弃(canonical clinicId 必填;
  # 留着只会变 failed 噪音。测试库实测 75/187 行如此)
  - kind: filter
    input: _plan_item_joined
    output: _plan_item_with_org
    where:
      organization_id: { not_empty: true }
  - kind: derive
    input: _plan_item_with_org
    output: treatment_planned_rows
    fields:
      mode_norm:
        op: normalize
        from: mode_name

  # ═══════════ 结算链(消费/退费;对齐 jvs-dw payment/refund 契约)═══════════
  # status 语义(实测):1=已结算 3=含退费行的单(头单仍是原始消费) 4=整单反向冲减。
  # 消费 = status∈{1,3};退费双轨 = ①status=4 整单(ref_settlement_id 挂原单)②spec is_refund=1 行级。
  # LTV = Σpayment − Σrefund 自然对账;不从 settlement 反推治疗(财务≠临床,jvs-dw 已验证的教训)。

  # ── S.1 消费:status 1/3 → 主导支付通道 lookup(金额最大者)──
  - kind: filter
    input: patient_settlement
    output: _pay_hdr
    where:
      status: { in: ['1', '3'] }
  - kind: lookup
    input: _pay_hdr
    output: payment_rows
    from: settlement_modes
    left_key: uuid
    right_key: settlement_id
    order_by:
      - { field: money, dir: desc }
    select:
      method: modes_name             # 主导支付通道(现金/微信/刷卡/医保…干净中文名,原样入 fact)

  # ── S.2 退费①:整单冲减(status=4 或 status=3 负金额;导出侧 WHERE 已切分,assembler 直读
  #    patient_settlement_refund 表;金额负值由 refund parser Math.abs 归一)──

  # ═══════════ 患者关系链(customer_referee_circle → patient_relations 独立表)═══════════
  # 宿主 referee_relationship 是字典 id(DB 无字典表,疑似代码硬编码)。解码 = 统计推断
  # (2026-07-19,互逆对+年龄差+性别三重证据,详见 patient_relation.yaml)——需要对方性别
  # 参与消歧(码 3=父母,father/mother 按对方性别拆)→ lookup 对方 sex 后拼 "码|性别" 键。
  - kind: lookup
    input: customer_referee_circle
    output: _referee_with_sex
    from: customer_basic_info
    left_key: referee_patient_id
    right_key: id
    select:
      referee_sex: sex
  - kind: derive
    input: _referee_with_sex
    output: patient_relation_rows
    fields:
      rel_sex_key:
        op: concat
        parts: ['${referee_relationship}', '|', '${referee_sex}']

assemblers:
  - { file: assemblers/patient.yaml }
  - { file: assemblers/appointment.yaml }
  - { file: assemblers/encounter.yaml }
  - { file: assemblers/emr.yaml }
  - { file: assemblers/diagnosis.yaml }
  - { file: assemblers/treatment_actual.yaml }
  - { file: assemblers/treatment_planned.yaml }
  - { file: assemblers/image.yaml }
  - { file: assemblers/treatment_review.yaml }
  - { file: assemblers/recommendation.yaml }
  - { file: assemblers/payment.yaml }
  - { file: assemblers/refund_full.yaml }
  - { file: assemblers/refund_item.yaml }
  - { file: assemblers/patient_relation.yaml }
  - { file: assemblers/consult.yaml }
