xiaoshu-app/scripts/cloud/review-rewards.js

// Included verbatim in both gateway bundles. Read-only queries share the immutable ledger.
async function reviewIncomeTotal(companyId, coachId) {
  const row = await Psql.one('SELECT COALESCE(SUM("amount"),0) AS total FROM "ReviewReward" WHERE "company"=$1 AND "coachId"=$2 AND "status"=\'earned\'', [companyId, Number(coachId)]);
  return Number(row.total);
}
function rewardPayrollLine(row, month) {
  const iso = row.completedAt ? new Date(row.completedAt).toISOString() : '';
  return { objectId: 'review:' + row.objectId, kind: 'review_reward', reviewRewardId: row.objectId,
    reviewTaskId: row.taskId, plannedDate: row.plannedDate, coachId: Number(row.coachId), coachName: row.coachName,
    storeId: Number(row.storeId), storeName: row.storeName, studentName: row.studentName, courseName: row.courseName,
    legacyGeneralId: 0, lessonId: '', lessonAt: iso, originalMonth: row.incomeMonth, settlementMonth: month,
    classType: 0, classTypeLabel: '抗遗忘工资', courseCategoryName: '抗遗忘工资', courseCategoryKey: '',
    durationMinutes: 0, durationHours: 0, deliveryMode: '', deliveryLabel: '', rateAmount: Number(row.amount),
    amount: Number(row.amount), score: 0, exception: '', description: '计划复习首次完成 · ' + row.plannedDate };
}
async function reviewRewardPayrollLines(companyId, month, includeUsed) {
  if (!companyId) throw new Error('抗遗忘工资必须指定帐套');
  if (!/^\d{4}-(0[1-9]|1[0-2])$/.test(month)) throw new Error('月份格式无效');
  const rows = await Psql.query('SELECT r.* FROM "ReviewReward" r WHERE r."company"=$1 AND r."status"=\'earned\' AND ' +
    (includeUsed ? 'r."incomeMonth"=$2' : 'r."incomeMonth"<=$2 AND NOT EXISTS (SELECT 1 FROM "PayrollLine" l WHERE l."company"=r."company" AND l."reviewRewardId"=r."objectId" AND l."kind"=\'review_reward\' AND COALESCE(l."excluded",false)=false)') +
    ' ORDER BY r."completedAt",r."objectId"', [companyId, month]);
  return rows.map((row) => rewardPayrollLine(row, month));
}
async function reviewRewardPage(companyId, input) {
  if (!companyId) throw new Error('抗遗忘工资必须指定帐套');
  const page = Math.max(1, Math.floor(Number(input.page) || 1)), pageSize = Math.min(100, Math.max(1, Math.floor(Number(input.pageSize) || 20)));
  const values = [companyId], clauses = ['r."company"=$1'];
  const add = (condition, value) => { values.push(value); clauses.push(condition.replaceAll('?', '$' + values.length)); };
  if (input.month) { if (!/^\d{4}-(0[1-9]|1[0-2])$/.test(input.month)) throw new Error('月份格式无效'); add('(r."incomeMonth"=? OR r."incomeMonth"=\'\')', input.month); }
  if (Number(input.storeId)) add('r."storeId"=?', Number(input.storeId));
  if (Number(input.coachId)) add('r."coachId"=?', Number(input.coachId));
  if (input.search) add('(r."coachName" ILIKE ? OR r."coachId"::text ILIKE ?)', '%' + String(input.search).trim() + '%');
  if (['earned','historical','exception'].includes(input.status)) add('r."status"=?', input.status);
  const snapshot=input.exportMode?(input.snapshot||new Date().toISOString()):'';
  if(snapshot){if(Number.isNaN(new Date(snapshot).getTime()))throw new Error('导出快照无效');add('r."createdAt"<=?::timestamptz',snapshot);}
  const summaryWhere=clauses.join(' AND '),summaryValues=[...values];
  if(input.exportMode&&input.afterId)add('r."objectId">?',String(input.afterId));
  const where = clauses.join(' AND ');
  const summary = await Psql.one('SELECT COUNT(*)::int total, COUNT(*) FILTER (WHERE r."status"=\'earned\')::int "reviewCount", COALESCE(SUM(r."amount"),0) "reviewAmount",COUNT(*) FILTER (WHERE r."status"=\'historical\')::int "historicalCount",COUNT(*) FILTER (WHERE r."status"=\'exception\')::int "exceptionCount" FROM "ReviewReward" r WHERE '+summaryWhere, summaryValues);
  const rows = await Psql.query('SELECT r.*,m."sourceKey" AS "taskSourceKey",m."fxzt" AS "taskStatus",m."rewardDuplicate" AS "taskDuplicate",l."batchId",b."month" AS "settlementMonth",b."status" AS "batchStatus" FROM "ReviewReward" r LEFT JOIN "MemoryPracticeRecord" m ON m."company"=r."company" AND m."objectId"=r."taskId" LEFT JOIN "PayrollLine" l ON l."company"=r."company" AND l."reviewRewardId"=r."objectId" AND l."kind"=\'review_reward\' AND COALESCE(l."excluded",false)=false LEFT JOIN "PayrollBatch" b ON b."company"=l."company" AND b."objectId"=l."batchId" WHERE '+where+(input.exportMode?' ORDER BY r."objectId"':' ORDER BY r."completedAt" DESC NULLS FIRST,r."objectId"')+' LIMIT $'+(values.length+1)+' OFFSET $'+(values.length+2), [...values,pageSize,input.exportMode?0:(page-1)*pageSize]);
  return { snapshot,nextCursor:input.exportMode&&rows.length===pageSize?rows[rows.length-1].objectId:'',items: rows.map((row)=>{const completedAt=row.completedAt?new Date(row.completedAt).toISOString():'',businessDay=completedAt?new Date(completedAt).toLocaleDateString('sv-SE',{timeZone:'Asia/Shanghai'}):'',canConfirm=row.status==='historical'&&!row.exception&&businessDay>='2026-09-01'&&row.taskStatus==='1'&&row.taskDuplicate!==true&&(/^(legacy:model:60:|legacy-sync:memory-records:addon:)/.test(row.taskSourceKey||''));const {taskSourceKey,taskStatus,taskDuplicate,...visible}=row;return {...visible,amount:Number(row.amount),completedAt,canConfirm,statusLabel:row.status==='historical'?'历史待核对':row.status==='exception'?'异常待核对':({draft:'已入草稿',reviewed:'已审核',paid:'已发放'}[row.batchStatus]||'待结算')};}), total:Number(summary.total), page, pageSize, summary:{...summary,reviewAmount:Number(summary.reviewAmount)} };
}