#!/usr/bin/env bash
# FRIDAY SaaS 测试环境 → PAC cold-import 导出(等价"host 侧单表 dump")
#
# 纪律(同 jvs-dw SQL 朴素化):每条导出 = 列选 + WHERE,零 join/零变换;
#   (2026-07 对齐:一列都不改名 —— 宿主原生字段直出;与 PAC 租户概念的区分由 PAC 适配层消化。)
#   形态改造(拆 JSON 数组/字典 join/K 码截取)全部在 PAC transforms 层做。
#
# 凭据走 env(不入 git):
#   export FRIDAY_MYSQL_HOST=… FRIDAY_MYSQL_PORT=3309 FRIDAY_MYSQL_USER=… FRIDAY_MYSQL_PASSWORD=…
#   export FRIDAY_MONGO_URI='mongodb://user:pass@host:27017/emr_market'   # 密码含 & 等须 URL 编码
#
# 依赖:docker(mysql:8 / mongo:7 客户端镜像),产物落本目录(.gitignore 已挡,不入 git)。
set -euo pipefail
cd "$(dirname "$0")"

: "${FRIDAY_MYSQL_HOST:?need FRIDAY_MYSQL_HOST}"
: "${FRIDAY_MYSQL_PORT:=3309}"
: "${FRIDAY_MYSQL_USER:?need FRIDAY_MYSQL_USER}"
: "${FRIDAY_MYSQL_PASSWORD:?need FRIDAY_MYSQL_PASSWORD}"
: "${FRIDAY_MONGO_URI:?need FRIDAY_MONGO_URI}"

# ── MySQL → CSV(TSV 中转,node 处理 mysql --batch 的 \t \n \\ 转义再按 RFC4180 引号)──
mysql_csv() {
  local sql="$1" out="$2"
  docker run --rm mysql:8 mysql \
    -h"$FRIDAY_MYSQL_HOST" -P"$FRIDAY_MYSQL_PORT" -u"$FRIDAY_MYSQL_USER" -p"$FRIDAY_MYSQL_PASSWORD" \
    --default-character-set=utf8mb4 --batch --raw=FALSE --connect-timeout=20 -e "$sql" \
    | node -e '
      const lines = require("fs").readFileSync(0, "utf-8").split("\n");
      const unesc = (s) => { let r = ""; for (let i = 0; i < s.length; i++) { const c = s[i];
        if (c === "\\" && i + 1 < s.length) { const n = s[++i];
          r += n === "n" ? "\n" : n === "t" ? "\t" : n === "0" ? "\0" : n === "N" ? "" : n; }
        else r += c; } return r; };
      const q = (s) => /[",\n\r]/.test(s) ? `"${s.replace(/"/g, `""`)}"` : s;
      const out = [];
      for (const line of lines) { if (line === "") continue;
        out.push(line.split("\t").map((f) => q(f === "NULL" ? "" : unesc(f))).join(",")); }
      process.stdout.write(out.join("\n") + "\n");
    ' > "$out.tmp"
  # ⚠️ 先写 .tmp,校验有数据才替换正式文件 —— 测试库抖动时裸重定向会把好文件清空(2026-07-20 踩)
  local n=$(($(wc -l < "$out.tmp") - 1))
  if [ "$n" -lt 1 ]; then
    rm -f "$out.tmp"
    echo "  ✗ $out: 导出为空(库不可达?),保留原文件不覆盖" >&2
    return 1
  fi
  mv "$out.tmp" "$out"
  echo "  $out: $n rows"
}

echo "── MySQL 导出 ──"
# 患者主档(全量;既有 450 行样本 → 扩全量,EMR 覆盖 ~1.65 万患者的前置)
mysql_csv "SELECT id,name,sex,birthday,file_number,tenant_id,organization_id,created_gmt_at,updated_gmt_at FROM customer.customer_basic_info" customer_basic_info.csv
# 联系方式 1:N(lookup 挑默认号 → patient.phone)
mysql_csv "SELECT id,customer_id,contacts_tel,is_default,contacts_type,tel_type,relationship FROM customer.customer_contacts" customer_contacts.csv
# 预约(全状态;transforms 丢草稿 10)
mysql_csv "SELECT id,patient_appointment_id,organization_id,tenant_id,appointment_date,appointment_start,appointment_status,doctor_user_id,appointment_time_length,in_time,created_gmt_at,updated_gmt_at FROM \`arrail-appointment-server\`.appointment_base" appointment_base.csv
# 影像档案(挂 emr_id 病历号;modality 经 std_check_class 字典翻)
mysql_csv "SELECT id,tenant_id,organization_id,organization_name,patient_id,emr_id,class_code,file_name,file_type,file_url,files_size,shooting_time,created_gmt_at,updated_gmt_at FROM emr.med_check" med_check.csv
# 影像类型字典(class_code UUID 全局唯一,lookup 不需 tenant 限定)
mysql_csv "SELECT class_code,class_name,tenant_id FROM emr.std_check_class" std_check_class.csv
# 品牌主档(apply 服务 tenant_info;brand_directory 派生 host.sourceUnitNames → 前端显示品牌中文名)
mysql_csv "SELECT tenant_id,name,name_ab FROM tenant_apply.tenant_info" tenant_info.csv
# 诊断字典(linkCode → std_code;K 码用,非 K 落自由文本)
mysql_csv "SELECT diag_code,diag_name,std_code FROM emr.std_diag" std_diag.csv
# 治疗计划(头:方案名/期望;行:牙位/术式/价格区间)→ treatment_planned
mysql_csv "SELECT id,tenant_id,organization_id,customer_id,plan_group_id,plan_name,desired_effect,emergency_urgency,expect_cost,created_gmt_at,updated_gmt_at FROM customer.customer_treat_plan" customer_treat_plan.csv
mysql_csv "SELECT id,tenant_id,customer_id,treat_plan_id,tooth_position,mode_name,charge_min,charge_max,created_gmt_at,updated_gmt_at FROM customer.customer_treat_plan_item" customer_treat_plan_item.csv
# 结算(消费/退费)。status 语义(2026-07-18 实测):1=已结算 3=含退费行的单 4=整单反向冲减(负额+ref 挂原单)。
# ⚠️ 2ac96f6d 品牌不用 status=4:整单冲减直接记 status=3 负金额(实测 200 行)→ 按金额正负切分:
#   消费 = status∈{1,3} 且金额≥0;整单退费 = status=4 或 status=3 负金额(退费第三表达)。
# 其余 status(0 草稿/2/5/6/7/8)语义未明,先不收 → WHERE 收窄(host 等价 dump 允许的过滤)
mysql_csv "SELECT uuid,tenant_id,organization_id,patient_id,doctor_id,status,receivable_this,net_receipts_this,billing_date,registration_id,ref_settlement_id,settlement_serial_num,reason,created_gmt_at,updated_gmt_at FROM \`arrail-settlement-server\`.patient_settlement WHERE status IN (1,3) AND receivable_this >= 0" patient_settlement.csv
mysql_csv "SELECT uuid,tenant_id,organization_id,patient_id,doctor_id,status,receivable_this,net_receipts_this,billing_date,registration_id,ref_settlement_id,settlement_serial_num,reason,created_gmt_at,updated_gmt_at FROM \`arrail-settlement-server\`.patient_settlement WHERE status=4 OR (status=3 AND receivable_this < 0)" patient_settlement_refund.csv
# 退费行级明细(部分退费轨道:is_refund=1 全部归属 status=3 头单)
mysql_csv "SELECT id,tenant_id,organization_id,patient_id,settlement_id,cure_name,service_project_name,receivable_this,net_receipts_this,is_refund,created_gmt_at,updated_gmt_at FROM \`arrail-settlement-server\`.patient_settlement_spec WHERE is_refund=1" patient_settlement_spec_refund.csv
# 支付通道长表(每单×通道一行;lookup 挑金额最大者为主导通道)
mysql_csv "SELECT id,tenant_id,settlement_id,modes_name,money,created_gmt_at,updated_gmt_at FROM \`arrail-settlement-server\`.settlement_modes" settlement_modes.csv
# 转介绍圈(患者-患者关系边,双向成对存;referee_relationship 为宿主字典 id,PAC 侧统计推断解码)
mysql_csv "SELECT id,tenant_id,organization_id,customer_id,referee_patient_id,referee_relationship,type,created_gmt_at,updated_gmt_at FROM customer.customer_referee_circle" customer_referee_circle.csv
# 咨询(意向 potential_treatment/未成交原因;测试库仅 45 行,结构真实)
mysql_csv "SELECT id,tenant_id,organization_id,customer_id,task_director_id,task_director,treatment_plan,preferential_way,unsettled_reason,unsettled_reason_description,other_conditions,potential_treatment,data_sources,created_gmt_at,updated_gmt_at FROM customer.customer_consult" customer_consult.csv

echo "── Mongo 导出(med_emr_info,status∈{3,4} 正式病历)──"
# 不用 mongoexport:Extended JSON 会把 Date/Long 包成 {$date}/{$numberLong} 对象,破坏平铺契约。
# mongosh 手动平铺:Date→naive 北京墙钟字符串,Long→字符串,数组(diag/treat/dispose/examine)原样保留
# (PAC split_json_array 原生支持已 parse 数组)。产出 JSON 数组文件(cold-import json 格式)。
#
# ⚠️ Date 时区实证(2026-07-20,clinicTime/createdGmtAt 小时分布双证):FRIDAY 写库时把
#   北京墙钟"伪装成 UTC"存进 Mongo Date(直读 UTC 字段=营业时间双峰 上午7-10/下午13-17;
#   按真 UTC 解释则一半病历落深夜——荒谬)。故导出**不能** toISOString 带 Z(会被 PAC 当真
#   UTC 再 +8,EMR 链时间全偏 8 小时,已踩):把 UTC 字段值直读为墙钟,输出 naive
#   "YYYY-MM-DD HH:mm:ss",交给 manifest timezone=Asia/Shanghai 解释 → 瞬间正确。
docker run --rm mongo:7 mongosh "$FRIDAY_MONGO_URI" --quiet --eval '
  const flat = (v) => v instanceof Date ? v.toISOString().slice(0, 19).replace("T", " ")
    : (v && v.constructor && v.constructor.name === "Long") ? String(v)
    : v === undefined ? null : v;
  // id 类字段字符串化:Mongo 存的是 number/Long 混型,canonical 契约要 string
  // (CSV 源天然全字符串;JSON 导出须显式对齐,否则 patientExternalId 等校验失败)
  const sid = (v) => (v === undefined || v === null || v === "") ? null : String(flat(v));
  let first = true;
  print("[");
  db.med_emr_info.find({ status: { $in: [3, 4] } }).forEach((d) => {
    const o = {
      emr_sub_id: sid(d.emrSubId), emr_id: sid(d.emrId),
      appointment_id: sid(d.appointmentId),
      tenant_id: sid(d.tenantId), organization_id: sid(d.organizationId),
      organization_name: flat(d.organizationName),   // 诊所名(clinic_directory 派生 host.clinicNames)
      patient_id: sid(d.patientId), user_id: sid(d.userId), user_name: flat(d.userName),
      clinic_time: flat(d.clinicTime), visit_indicator: flat(d.visitIndicator), status: flat(d.status),
      diag: d.diag ?? [], treat: d.treat ?? [], dispose: d.dispose ?? [], examine: d.examine ?? [],
      // emr_record 自由文本位要字符串(fact 层 zod nullableString);数组原样给 split_json_array 拆行
      dispose_text: (d.dispose ?? []).length ? JSON.stringify(d.dispose) : null,
      examine_text: (d.examine ?? []).length ? JSON.stringify(d.examine) : null,
      illness_desc: flat(d.illnessDesc), pre_illness: flat(d.preIllness), past_hist: flat(d.pastHist),
      gen_cond: flat(d.genCond), auxi_exam: flat(d.auxiExam), doc_order: flat(d.docOrder),
      created_gmt_at: flat(d.createdGmtAt), updated_gmt_at: flat(d.updatedGmtAt),
    };
    print((first ? "" : ",") + JSON.stringify(o)); first = false;
  });
  print("]");
' > med_emr_info.json.tmp
# 同款保护:解析成功且非空才替换(mongosh 连接失败会产出空/半截文件)
node -e "
  const fs=require('fs');
  let a; try { a = JSON.parse(fs.readFileSync('med_emr_info.json.tmp','utf-8')); } catch (e) {
    fs.unlinkSync('med_emr_info.json.tmp');
    console.error('  ✗ med_emr_info.json: 导出无效(库不可达?),保留原文件不覆盖'); process.exit(1);
  }
  if (!Array.isArray(a) || a.length === 0) {
    fs.unlinkSync('med_emr_info.json.tmp');
    console.error('  ✗ med_emr_info.json: 导出为空,保留原文件不覆盖'); process.exit(1);
  }
  fs.renameSync('med_emr_info.json.tmp','med_emr_info.json');
  console.log('  med_emr_info.json:', a.length, 'docs');
"

echo "── 导出完成 ──"
ls -la *.csv *.json | awk '{print "  " $NF, $5}'
