/**
 * 对账（SDD「积分清算」）：① 已关闭托管的发放明细之和 = 托管金额；② held 托管都对应未结束的任务，
 * 未结束的任务都有 held 托管；③ 每笔 voko 流水的 ref 都能找到业务记录。返回全部不满足项，带数据 id。
 */

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')
  const orphanTipRefs = await db('billing_ledger as l').leftJoin('vk_tips as p', 'p.id', 'l.ref_id').where({ 'l.source': 'voko', 'l.ref_type': 'vk_tip' }).whereNull('p.id').select('l.id', 'l.ref_id')
  for (const row of [...orphanTaskRefs, ...orphanAgentRefs, ...orphanTipRefs]) problems.push({ rule: 'ledger_ref_missing', id: row.id, detail: `ref_id=${row.ref_id}` })
  return problems
}