#!/usr/bin/env node
/**
 * 对账（SDD「积分清算」）：① 已关闭托管的发放明细之和 = 托管金额；② held 托管都对应未结束的任务，
 * 未结束的任务都有 held 托管；③ 每笔 voko 流水的 ref 都能找到业务记录。
 * 任一不满足即失败并打印数据 id。可单独运行，也在每个场景脚本末尾运行。
 */
import { fileURLToPath } from 'node:url'
import { createDb } from '@drama/db'

import { databaseUrlFromArgs } from '../../../src/db/env-file.js'

const TERMINAL = ['settled', 'cancelled', 'refunded']

/**
 * @param {import('knex').Knex} db
 * @returns {Promise<Array<{ rule: string, id: string, detail: string }>>}
 */
export async function verifyConservation(db) {
  const problems = []
  const closed = await db('vk_escrows as e')
    .leftJoin('vk_escrow_payouts as p', 'p.escrow_id', 'e.id')
    .whereNot('e.status', 'held')
    .groupBy('e.id', 'e.amount')
    .havingRaw('COALESCE(SUM(p.amount), 0) <> e.amount')
    .select('e.id', 'e.amount', db.raw('COALESCE(SUM(p.amount), 0) as paid'))
  for (const row of closed) problems.push({ rule: 'payouts_sum', id: row.id, detail: `amount=${row.amount}, payouts=${row.paid}` })

  const heldOnEnded = await db('vk_escrows as e').join('vk_tasks as t', 't.id', 'e.task_id').where('e.status', 'held').whereIn('t.status', TERMINAL).select('e.id', 't.status')
  for (const row of heldOnEnded) problems.push({ rule: 'held_on_ended_task', id: row.id, detail: `task status=${row.status}` })
  const openWithoutHeld = await db('vk_tasks as t')
    .leftJoin('vk_escrows as e', (join) => join.on('e.task_id', 't.id').andOnVal('e.status', 'held'))
    .whereNotIn('t.status', TERMINAL)
    .whereNull('e.id')
    .select('t.id', 't.status')
  for (const row of openWithoutHeld) problems.push({ rule: 'active_task_without_held_escrow', id: row.id, detail: `task status=${row.status}` })

  const orphanTaskRefs = await db('billing_ledger as l').leftJoin('vk_tasks as t', 't.id', 'l.ref_id').where({ 'l.source': 'voko', 'l.ref_type': 'vk_task' }).whereNull('t.id').select('l.id', 'l.ref_id')
  const orphanAgentRefs = await db('billing_ledger as l').leftJoin('vk_agents as a', 'a.id', 'l.ref_id').where({ 'l.source': 'voko', 'l.ref_type': 'vk_agent' }).whereNull('a.id').select('l.id', 'l.ref_id')
  for (const row of [...orphanTaskRefs, ...orphanAgentRefs]) problems.push({ rule: 'ledger_ref_missing', id: row.id, detail: `ref_id=${row.ref_id}` })
  return problems
}

if (process.argv[1] === fileURLToPath(import.meta.url)) {
  const db = createDb({ url: databaseUrlFromArgs(process.argv.slice(2)), pool: { min: 1, max: 1 } })
  try {
    const problems = await verifyConservation(db)
    console.log(JSON.stringify({ status: problems.length === 0 ? 'PASS' : 'FAIL', problems }, null, 2))
    if (problems.length > 0) process.exitCode = 1
  } finally {
    await db.destroy()
  }
}