-- 财务上线验收（只读）
--
-- 用途：部署 + 数据初始化完成后，一次跑完所有闸门，输出 PASS / FAIL 清单。
-- 不写任何业务表；结果只落在会话级临时表 _launch_checks。
--
-- 用法：
--   # 1) 通过编排脚本（推荐，自动带上 launch_at）
--   bash deploy/post-deploy.sh --only=verify --launch-at='2026-10-27 18:00:00+08'
--
--   # 2) 手工执行（变量必须传，否则用脚本内的兜底时间）
--   docker compose exec -T postgres sh -lc \
--     'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" \
--        -v ON_ERROR_STOP=1 -v launch_at="2026-10-27 18:00:00+08" -f -' \
--     < scripts/verify-finance-launch.sql
--
-- 退出码：0 = 无 FAIL（WARN 不阻断）；3 = 存在 FAIL（末尾 RAISE EXCEPTION）。
-- 前置条件：迁移 103–106 已应用；否则先跑 deploy/ubuntu-deploy.sh。

\set ON_ERROR_STOP on

\if :{?launch_at}
\else
  -- 与 post-deploy.sh 一致：默认取本次执行时间（容器 TZ=Asia/Shanghai）。
  \set launch_at `date '+%Y-%m-%d %H:%M:%S%z'`
\endif

\echo ''
\echo '==================== 财务上线验收 ===================='
\echo '上线时间（launch_at）: ' :launch_at
\echo ''

-- 建表与 15 条检查的 INSERT 不输出到终端（错误仍会进 stderr）。
\o /dev/null
SET client_min_messages = warning;
DROP TABLE IF EXISTS _launch_checks;

CREATE TEMP TABLE _launch_checks (
  seq      int,
  id       text,
  title    text,
  actual   text,
  expected text,
  status   text,
  hint     text
);

-- 01 迁移基线 --------------------------------------------------------------
INSERT INTO _launch_checks
SELECT 1, '01', '迁移基线 103–106 已应用',
       v::text, '4',
       CASE WHEN v = 4 THEN 'PASS' ELSE 'FAIL' END,
       '后端未完成迁移：docker compose logs backend | grep -i migration'
FROM (
  -- 按版本前缀逐个判断，避免后续 106_xxx_hotfix 之类增量迁移把计数顶成 5。
  SELECT (EXISTS (SELECT 1 FROM schema_migrations WHERE COALESCE(filename, name) ~ '^103_'))::int
       + (EXISTS (SELECT 1 FROM schema_migrations WHERE COALESCE(filename, name) ~ '^104_'))::int
       + (EXISTS (SELECT 1 FROM schema_migrations WHERE COALESCE(filename, name) ~ '^105_'))::int
       + (EXISTS (SELECT 1 FROM schema_migrations WHERE COALESCE(filename, name) ~ '^106_'))::int AS v
) s;

-- 02 存量订单项收入回填 ----------------------------------------------------
INSERT INTO _launch_checks
SELECT 2, '02', '存量订单项收入已回填（revenue_basis）',
       v::text, '0',
       CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
       '跑 npm run revenue:backfill（deploy/post-deploy.sh 会自动执行）；未回填时退款按钮置灰'
FROM (
  SELECT count(*) AS v FROM order_items WHERE revenue_basis = 'unknown'
) s;

-- 03 履约方式回填 ----------------------------------------------------------
INSERT INTO _launch_checks
SELECT 3, '03', '履约方式已回填（fulfillment_mode）',
       v::text, '0',
       CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
       '跑 npm run fulfillment:backfill；NULL 会让订单/成本页口径缺失'
FROM (
  SELECT count(*) AS v FROM order_items WHERE fulfillment_mode IS NULL
) s;

-- 04 履约方式取值合法 ------------------------------------------------------
INSERT INTO _launch_checks
SELECT 4, '04', '履约方式取值合法',
       v::text, '0',
       CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
       '存在未知 fulfillment_mode，检查回填脚本与历史脏数据'
FROM (
  SELECT count(*) AS v
  FROM order_items
  WHERE fulfillment_mode IS NOT NULL
    AND fulfillment_mode NOT IN ('work_order', 'manual', 'legacy_platform', 'affiliate_dash')
) s;

-- 05 商品目录 --------------------------------------------------------------
INSERT INTO _launch_checks
SELECT 5, '05', '商品目录已初始化（product_catalog）',
       v::text, '> 0',
       CASE WHEN v > 0 THEN 'PASS' ELSE 'FAIL' END,
       '商品与成本 → 标准商品 →「从接单模板初始化」/「按工单回填商品」/「归一人工商品」'
FROM (SELECT count(*) AS v FROM product_catalog) s;

-- 06 有效成本 --------------------------------------------------------------
INSERT INTO _launch_checks
SELECT 6, '06', '有效成本已生成（版本定价或正金额快照）',
       '版本 ' || priced_versions::text || ' 个 / 正金额快照 ' || positive_snapshots::text || ' 条',
       '至少一项 > 0',
       CASE WHEN priced_versions > 0 OR positive_snapshots > 0 THEN 'PASS' ELSE 'FAIL' END,
       '迁移只建 0 元占位版本；工单奖励/实际成本会写入快照。两项都为 0 时：先配置成本版本，或确认工单奖励已回写'
FROM (
  SELECT
    (SELECT count(*) FROM cost_versions
      WHERE status = 'active' AND (amount_fen > 0 OR rate_bps > 0)) AS priced_versions,
    (SELECT count(*) FROM cost_snapshots
      WHERE superseded_at IS NULL AND total_amount_fen > 0) AS positive_snapshots
) s;

-- 07 客服备用金账户 --------------------------------------------------------
INSERT INTO _launch_checks
SELECT 7, '07', '客服备用金账户已补建',
       v::text, '0',
       CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
       '钱包管理 → 客服账户 →「补建客服账户」（POST /admin/fund/accounts/ensure-agents）'
FROM (
  SELECT count(*) AS v
  FROM admin_users au
  LEFT JOIN fund_accounts fa
    ON fa.owner_admin_user_id = au.id
   AND fa.account_type = 'agent'
  WHERE au.role = 'support'
    AND fa.id IS NULL
) s;

-- 08 上线后人工项必须有履约配置 -------------------------------------------
INSERT INTO _launch_checks
SELECT 8, '08', '上线后人工项已匹配履约配置',
       v::text, '0',
       CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
       '商品与成本 → 人工 SKU 配置 →「扫描」并逐项配置；保存后会自动回填近 30 天'
FROM (
  SELECT count(*) AS v
  FROM order_items oi
  WHERE oi.fulfillment_mode = 'manual'
    AND oi.created_at >= :'launch_at'::timestamptz
    AND oi.fulfillment_config_id IS NULL
) s;

-- 09 上线后人工项必须有成本快照（关账门槛） -------------------------------
INSERT INTO _launch_checks
SELECT 9, '09', '上线后人工项均已确认履约（关账门槛）',
       v::text, '0',
       CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
       '待办量：有未确认人工单就会 FAIL，不宜当日常健康检查；订单列表/详情 →「确认履约」后写 manual_case 快照并扣备用金/积分'
FROM (
  SELECT count(*) AS v
  FROM order_items oi
  WHERE oi.fulfillment_mode = 'manual'
    AND oi.created_at >= :'launch_at'::timestamptz
    AND NOT EXISTS (
      SELECT 1 FROM cost_snapshots cs
      WHERE cs.subject_type = 'manual_case'
        AND cs.order_item_id = oi.id
        AND cs.superseded_at IS NULL
    )
) s;

-- 10 人工快照唯一性 --------------------------------------------------------
INSERT INTO _launch_checks
SELECT 10, '10', '一个订单项最多一条有效人工快照',
       v::text, '0',
       CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
       '出现重复快照说明并发确认未被幂等拦截，需排查 manual_case 写入路径'
FROM (
  SELECT count(*) AS v
  FROM (
    SELECT order_item_id
    FROM cost_snapshots
    WHERE subject_type = 'manual_case' AND superseded_at IS NULL
    GROUP BY 1
    HAVING count(*) > 1
  ) dup
) s;

-- 11 快照金额 = 备用金支出（现金路径） ------------------------------------
INSERT INTO _launch_checks
SELECT 11, '11', '人工快照金额与备用金支出一致',
       v::text, '0',
       CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
       '金额不一致通常是确认事务部分失败；查 cost_snapshots / manual_expenses / fund_entries 三处'
FROM (
  SELECT count(*) AS v
  FROM cost_snapshots cs
  -- 只核对现金路径：metadata.fundExpenseId 精确定位备用金支出，
  -- 避免同一订单项有多条 manual_expenses（如另外登记的人工支出）时行放大误报。
  JOIN manual_expenses me
    ON me.id = NULLIF(cs.metadata_json->>'fundExpenseId', '')::bigint
  WHERE cs.subject_type = 'manual_case'
    AND cs.superseded_at IS NULL
    AND cs.created_at >= :'launch_at'::timestamptz
    AND cs.total_amount_fen <> me.amount_fen
) s;

-- 12 总账借贷平衡 ----------------------------------------------------------
INSERT INTO _launch_checks
SELECT 12, '12', '总账借贷平衡（全部已过账凭证）',
       v::text || '（已过账凭证 ' || posted::text || ' 张）', '借贷为 0 且至少有 1 张凭证',
       CASE WHEN v = 0 AND (posted > 0 OR facts = 0) THEN 'PASS' ELSE 'FAIL' END,
       '财务 → 总账与对账 →「同步销售事实到总账」；有销售事实但 0 张凭证同样视为未完成（空表求和恒为 0，不能直接判 PASS）'
FROM (
  SELECT
    COALESCE(abs(sum(l.debit_fen) - sum(l.credit_fen)), 0)::bigint AS v,
    count(DISTINCT e.id) AS posted,
    (SELECT count(*) FROM sales_daily_facts) AS facts
  FROM finance_journal_lines l
  JOIN finance_journal_entries e ON e.id = l.entry_id AND e.status = 'posted'
) s;

-- 13 已关账期间不应再产生新凭证 -------------------------------------------
INSERT INTO _launch_checks
SELECT 13, '13', '已关账期间无新增凭证',
       v::text, '0',
       CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
       '检查关账后是否有人重跑同步；以 closed_at 为基准，正常关账前生成的凭证不算违规'
FROM (
  SELECT count(*) AS v
  FROM finance_journal_entries e
  JOIN finance_periods p
    ON p.period_key = e.period_key
   AND p.status = 'closed'
  WHERE p.closed_at IS NOT NULL
    AND e.created_at > p.closed_at
) s;

-- 14 销售事实已生成 --------------------------------------------------------
INSERT INTO _launch_checks
SELECT 14, '14', '销售事实已生成（sales_daily_facts）',
       v::text, '> 0',
       CASE WHEN v > 0 THEN 'PASS' ELSE 'FAIL' END,
       '跑 finance:sync --mode=facts（post-deploy.sh 会按窗口执行）；定时任务只覆盖最近 7 天'
FROM (SELECT count(*) AS v FROM sales_daily_facts) s;

-- 15 近 7 天成本覆盖（仅提示，不阻断上线） ---------------------------------
-- 说明：历史人工成本按既定口径不补录，这里只看非人工；自动发货/接单平台
-- 的成本缺口会直接影响毛利，必须在上线前处理，但不用它单独卡住验收。
INSERT INTO _launch_checks
SELECT 15, '15', '近 7 天非人工销售事实成本覆盖（仅提示）',
       v::text, '0',
       CASE WHEN v = 0 THEN 'PASS' ELSE 'WARN' END,
       '存在成本缺失的非人工销售事实：先归一商品并配置成本版本；历史人工成本按既定口径不补录，故不计入'
FROM (
  SELECT count(*) AS v
  FROM sales_daily_facts
  WHERE stat_date >= current_date - 6
    AND fulfillment_mode <> 'manual'
    AND cost_missing_count > 0
) s;

\o
\echo '【闸门清单】'
SELECT seq AS "#",
       status,
       id,
       title AS "检查项",
       actual AS "实际",
       expected AS "期望"
FROM _launch_checks
ORDER BY seq;

\echo ''
\echo '【未通过 / 提示项的处理建议】'
SELECT id,
       status AS "状态",
       title AS "检查项",
       hint AS "处理建议"
FROM _launch_checks
WHERE status <> 'PASS'
ORDER BY seq;

\echo ''
\echo '【汇总】'
SELECT count(*) FILTER (WHERE status = 'PASS') AS "通过",
       count(*) FILTER (WHERE status = 'FAIL') AS "未通过",
       count(*) FILTER (WHERE status = 'WARN') AS "提示",
       count(*) AS "合计"
FROM _launch_checks;

\echo ''
\echo '【数据规模参考】'
SELECT (SELECT count(*) FROM orders) AS "订单",
       (SELECT count(*) FROM order_items) AS "订单项",
       (SELECT count(*) FROM product_catalog) AS "商品",
       (SELECT count(*) FROM cost_snapshots WHERE superseded_at IS NULL) AS "有效成本快照",
       (SELECT count(*) FROM sales_daily_facts) AS "销售事实行",
       (SELECT count(*) FROM finance_journal_entries) AS "总账凭证",
       (SELECT count(*) FROM cost_exceptions WHERE status = 'open') AS "未处理成本异常",
       (SELECT count(*) FROM order_items WHERE revenue_basis = 'unknown') AS "待回填收入项";

DO $$
DECLARE
  failed_count int;
  failed_list  text;
BEGIN
  SELECT count(*), string_agg(id || ' ' || title, '；' ORDER BY seq)
    INTO failed_count, failed_list
  FROM _launch_checks
  WHERE status = 'FAIL';

  IF failed_count > 0 THEN
    RAISE EXCEPTION '财务上线验收未通过（% 项）：%', failed_count, failed_list;
  END IF;

  RAISE NOTICE '财务上线验收全部通过';
END $$;