/**
 * 일별 총합계 검증 — TreatMonitorService.dailySummary 와 '완전히 동일한' 로직을 prod DB에 직접 돌려
 *  엑셀(삼성화재_진료모니터링_일별총합계_*.xlsx / 벤더 통합)과 숫자를 대조한다.
 *  - 분류(진짜이탈/유저실패/시스템실패)는 실서비스 VENDOR_MAP·USER_FAIL_CATS 를 그대로 import → 정의 100% 일치.
 *  - prod 읽기전용 집계 전용. 비번은 MYSQL_PWD env 로만(명령줄 노출 금지). ltr_created_at 은 KST 저장 → CONVERT_TZ 금지.
 *
 * 사용:
 *   cd ~/projects/work/insuniverse/server
 *   MYSQL_PWD='운영DB비번' TS_NODE_TRANSPILE_ONLY=1 TS_NODE_COMPILER_OPTIONS='{"module":"commonjs"}' \
 *     TDB_PORT=13307 TDB_USER=admin FROM=2026-07-06 TO=2026-07-20 \
 *     npx ts-node scripts/treat-daily-verify.ts
 *
 *  ※ 엑셀 '일별 총합계'는 대상=삼성화재 계정이지만, 본 스크립트는 전체 통합(dailySummary와 동일 범위)이라
 *    삼성화재가 전체와 다르면 숫자가 다를 수 있음. 코호트 로직/일자 정합 검증이 1차 목적.
 */
import mysql from 'mysql2/promise';
import { TreatErrorCategory as C } from '../apps/api/src/modules/treat/error/treat-error-category';
import { VENDOR_MAP } from '../apps/api/src/modules/treat/error/treat-error-vendor-map';

const HOST = process.env.TDB_HOST || '127.0.0.1';
const PORT = Number(process.env.TDB_PORT || 13307);
const USER = process.env.TDB_USER || 'admin';
const DB = process.env.TDB_NAME || 'insuniverse';

const ymd = (d: Date) =>
  `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}-${String(d.getDate()).padStart(2, '0')}`;
const _today = new Date();
const _defFrom = new Date(_today);
_defFrom.setDate(_defFrom.getDate() - 14);
const FROM = /^\d{4}-\d{2}-\d{2}$/.test(process.env.FROM || '') ? (process.env.FROM as string) : ymd(_defFrom);
const TO = /^\d{4}-\d{2}-\d{2}$/.test(process.env.TO || '') ? (process.env.TO as string) : ymd(_today);
//* 기업상품(PRD003) 코드 다중필터 — PRODUCTS='코드1,코드2'. 미지정이면 전체 통합.
const PRODUCTS = (process.env.PRODUCTS || '').split(',').map((s) => s.trim()).filter(Boolean);

//* 실서비스 USER_FAIL_CATS 와 동일
const USER_FAIL_CATS = new Set<C>([
  C.INPUT_INVALID,
  C.AUTH_CODE_WRONG,
  C.AUTH_APP_MISSING,
  C.AUTH_CERT,
  C.AGE_RESTRICTED,
]);
const vmap = VENDOR_MAP as Record<string, Record<string, C>>;
const isHumanError = (provider: string, code: string | null | undefined) => {
  if (!code) return false;
  const cat = vmap[provider]?.[code];
  return cat ? USER_FAIL_CATS.has(cat) : false;
};

const n = (v: any) => Number(v ?? 0);
const rate = (a: number, b: number) => (b > 0 ? a / b : null);
const pct = (r: number | null) => (r == null ? '  -  ' : (r * 100).toFixed(1).padStart(5));
const pad = (v: any, w: number) => String(v).padStart(w);

(async () => {
  if (!process.env.MYSQL_PWD) {
    console.error('거부: MYSQL_PWD 미설정 (비번은 env 로만 주입).');
    process.exit(1);
  }
  const conn = await mysql.createConnection({
    host: HOST, port: PORT, user: USER, password: process.env.MYSQL_PWD, database: DB,
  });
  try {
    //* 발견 모드 — 기업상품(PRD003) 코드/명 목록만 찍고 종료. 삼성화재 상품코드 찾을 때: LIST_PRODUCTS=1
    if (process.env.LIST_PRODUCTS) {
      const [plist] = (await conn.query(
        `SELECT o.ord_product_code AS code, MAX(o.ord_product_name) AS name, COUNT(*) AS orders
         FROM tn_order o WHERE o.ord_product_type = 'PRD003'
         GROUP BY o.ord_product_code ORDER BY name`,
      )) as any[];
      console.log('\n기업상품(PRD003) 목록 — code | name | orders');
      for (const r of plist) console.log(`${String(r.code).padEnd(14)} | ${String(r.name).padEnd(24)} | ${n(r.orders)}`);
      console.log(`\n총 ${plist.length}개. 삼성화재 code 찾아서 PRODUCTS='그코드' 로 재실행하면 엑셀과 대조.`);
      return;
    }
    const prodJoin = PRODUCTS.length
      ? `INNER JOIN tn_order o ON o.ord_id = d.ord_id AND o.ord_product_type = 'PRD003'`
      : '';
    const prodWhere = PRODUCTS.length
      ? `AND o.ord_product_code IN (${PRODUCTS.map(() => '?').join(', ')})`
      : '';
    const [rows] = (await conn.query(
      `SELECT
         d.usr_id AS usr,
         DATE_FORMAT(d.odd_created_at, '%Y-%m-%d') AS day,
         DATE_FORMAT(d.odd_created_at, '%Y-%m-%d %H:%i:%s') AS createdAt,
         (COALESCE(d.odd_final_state, d.odd_state) = 'ODD006') AS success,
         EXISTS(SELECT 1 FROM log_treat l WHERE l.odd_transaction_id = d.odd_transaction_id
           AND l.codef_request_stage = 'CRS002' AND l.codef_request_type <> 'CAT002') AS has2,
         d.odd_provider AS provider,
         ll.rc AS lastCode
       FROM tn_order_detail d
       ${prodJoin}
       LEFT JOIN LATERAL (
         SELECT l.codef_result_code AS rc FROM log_treat l
         WHERE l.odd_transaction_id = d.odd_transaction_id AND l.codef_request_stage = 'CRS002'
           AND l.codef_result_status = 'CRT002' AND l.codef_request_type <> 'CAT002'
         ORDER BY l.ltr_created_at DESC LIMIT 1
       ) ll ON TRUE
       WHERE d.odd_created_at >= ?
         AND d.odd_created_at < DATE_ADD(?, INTERVAL 1 DAY)
         ${prodWhere}
         AND (
           COALESCE(d.odd_final_state, d.odd_state) = 'ODD006'
           OR d.odd_state IN ('ODD004','ODD007','ODD008')
           OR d.odd_final_state IN ('ODD004','ODD007')
         )`,
      [FROM, TO, ...PRODUCTS],
    )) as any[];

    const classify = (r: any): 'ok' | 'drop' | 'user' | 'sys' => {
      if (n(r.success) === 1) return 'ok';
      if (n(r.has2) !== 1) return 'drop';
      return isHumanError(r.provider, r.lastCode) ? 'user' : 'sys';
    };

    const dayMap = new Map<string, { requested: number; ok: number; drop: number; user: number; sys: number }>();
    const byUser = new Map<string, any[]>();
    for (const r of rows) {
      const o = dayMap.get(r.day) ?? { requested: 0, ok: 0, drop: 0, user: 0, sys: 0 };
      o.requested++;
      (o as any)[classify(r)]++;
      dayMap.set(r.day, o);
      const uk = String(r.usr);
      const arr = byUser.get(uk);
      if (arr) arr.push(r);
      else byUser.set(uk, [r]);
    }

    const cohortMap = new Map<string, { customers: number; firstSuccess: number; firstFail: number; recovered: number }>();
    for (const hist of byUser.values()) {
      hist.sort((a, b) => (a.createdAt < b.createdAt ? -1 : a.createdAt > b.createdAt ? 1 : 0));
      const first = hist[0];
      const c = cohortMap.get(first.day) ?? { customers: 0, firstSuccess: 0, firstFail: 0, recovered: 0 };
      c.customers++;
      if (n(first.success) === 1) c.firstSuccess++;
      else {
        c.firstFail++;
        if (hist.slice(1).some((r) => n(r.success) === 1)) c.recovered++;
      }
      cohortMap.set(first.day, c);
    }

    const days: string[] = [];
    for (let d = new Date(`${FROM}T00:00:00`), e = new Date(`${TO}T00:00:00`); d <= e; d.setDate(d.getDate() + 1)) {
      days.push(ymd(d));
    }

    const scope = PRODUCTS.length ? `기업상품[${PRODUCTS.join(',')}]` : '전체 통합';
    console.log(`\n일별 총합계 (${scope}) ${FROM} ~ ${TO}  |  rows=${rows.length}, 고객=${byUser.size}\n`);
    const head =
      '일자        | 요청  성공  성공%  이탈  이탈%  유저  유저%  시스  시스% ||' +
      ' 고객  최초  최초%  최실  회복  미회  회복%  최종  최종%';
    console.log(head);
    console.log('-'.repeat(head.length));

    let T = { req: 0, ok: 0, drop: 0, user: 0, sys: 0, cust: 0, fs: 0, ff: 0, rec: 0 };
    for (const day of days) {
      const a = dayMap.get(day) ?? { requested: 0, ok: 0, drop: 0, user: 0, sys: 0 };
      const c = cohortMap.get(day) ?? { customers: 0, firstSuccess: 0, firstFail: 0, recovered: 0 };
      const finalS = c.firstSuccess + c.recovered;
      T = {
        req: T.req + a.requested, ok: T.ok + a.ok, drop: T.drop + a.drop, user: T.user + a.user, sys: T.sys + a.sys,
        cust: T.cust + c.customers, fs: T.fs + c.firstSuccess, ff: T.ff + c.firstFail, rec: T.rec + c.recovered,
      };
      console.log(
        `${day} |${pad(a.requested, 5)}${pad(a.ok, 6)} ${pct(rate(a.ok, a.requested))}${pad(a.drop, 6)} ${pct(rate(a.drop, a.requested))}` +
          `${pad(a.user, 6)} ${pct(rate(a.user, a.requested))}${pad(a.sys, 6)} ${pct(rate(a.sys, a.requested))} ||` +
          `${pad(c.customers, 5)}${pad(c.firstSuccess, 6)} ${pct(rate(c.firstSuccess, c.customers))}${pad(c.firstFail, 6)}` +
          `${pad(c.recovered, 6)}${pad(c.firstFail - c.recovered, 6)} ${pct(rate(c.recovered, c.firstFail))}${pad(finalS, 6)} ${pct(rate(finalS, c.customers))}`,
      );
    }
    console.log('-'.repeat(head.length));
    const tFinal = T.fs + T.rec;
    console.log(
      `합계        |${pad(T.req, 5)}${pad(T.ok, 6)} ${pct(rate(T.ok, T.req))}${pad(T.drop, 6)} ${pct(rate(T.drop, T.req))}` +
        `${pad(T.user, 6)} ${pct(rate(T.user, T.req))}${pad(T.sys, 6)} ${pct(rate(T.sys, T.req))} ||` +
        `${pad(T.cust, 5)}${pad(T.fs, 6)} ${pct(rate(T.fs, T.cust))}${pad(T.ff, 6)}` +
        `${pad(T.rec, 6)}${pad(T.ff - T.rec, 6)} ${pct(rate(T.rec, T.ff))}${pad(tFinal, 6)} ${pct(rate(tFinal, T.cust))}`,
    );
    console.log(
      `\n정합 체크: 요청=성공+이탈+유저+시스 → ${T.req} = ${T.ok + T.drop + T.user + T.sys}` +
        `  |  최종성공=최초성공+회복 → ${tFinal} = ${T.fs + T.rec}` +
        `  |  고객=최초성공+최초실패 → ${T.cust} = ${T.fs + T.ff}`,
    );
  } finally {
    await conn.end();
  }
})().catch((e) => {
  console.error('ERR', e.message);
  process.exit(1);
});