xiaoshu-app/scripts/cloud/payroll-integrity.js

// Payroll entry points share canonical sources and atomic SQL commands.
function payrollBusinessDate(value) {
  const iso=payrollDateIso(value);return iso?new Date(iso).toLocaleDateString('sv-SE',{timeZone:'Asia/Shanghai'}):'';
}
function payrollDateIso(value) {
  if(value instanceof Date)return Number.isNaN(value.getTime())?'':value.toISOString();
  const raw=text(value?.iso||value);if(!/^\d{4}-\d{1,2}-\d{1,2}/.test(raw))return '';
  let normalized=raw.replace(' ','T');if(!normalized.includes('T'))normalized+='T00:00:00';
  if(!/(?:Z|[+-]\d{2}(?::?\d{2})?)$/i.test(normalized))normalized+='+08:00';
  const date=new Date(normalized);return Number.isNaN(date.getTime())?'':date.toISOString();
}
function payrollTenant(context,input){requireRole(context,['ops-manager','ops-staff','ops-auditor']);const co=companyIdOf(context,input);if(!co)fail(400,'工资结算必须指定帐套');return co;}
async function payrollSql(sql,args){try{return await Psql.one(sql,args);}catch(error){fail(/does not exist/.test(text(error.message))?503:409,text(error.message).replace(/^.*ERROR:\s*/,''));}}
function payrollIdentity(item){return text(item.standardLessonKey)||(number(item.legacyGeneralId)?'legacy:'+number(item.legacyGeneralId):'lesson:'+item.objectId);}
const PAYROLL_SYNC_DATASETS=['appointments','lessons','memory-records'];
async function payrollSettlementSyncStatus(context,input){
 const checkedAt=new Date().toISOString(),datasets=PAYROLL_SYNC_DATASETS.map(key=>({key,state:'never_synced',lastSuccessAt:'',lagSeconds:null,pendingFailures:0,conflicts:0,legacyCount:null,localCount:null})),config=syncConfig();
 const result={state:'not_configured',settlementReady:false,message:'旧系统持续同步尚未配置，工资统计可能缺少最新记录',checkedAt,lastSuccessAt:'',lagSeconds:null,datasets};
 if(!config.baseUrl||!config.keyId||!config.secret)return result;
 try{
  const co=payrollTenant(context,input),[checkpoints,issues,localCounts,manifest]=await Promise.all([
   Psql.query('SELECT "dataset","state","cursor","lastSuccessAt","lastWatermark" FROM "LegacySyncCheckpoint" WHERE "company"=$1 AND "dataset"=ANY($2::text[]) ORDER BY "updatedAt" DESC',[co,PAYROLL_SYNC_DATASETS]),
   Psql.query('SELECT "dataset","kind",COUNT(*)::int AS total FROM (SELECT "dataset",\'failure\' AS kind FROM "LegacySyncDeadLetter" WHERE "company"=$1 AND COALESCE("resolved",FALSE)=FALSE UNION ALL SELECT "dataset",\'conflict\' AS kind FROM "LegacySyncConflict" WHERE "company"=$1 AND COALESCE("resolved",FALSE)=FALSE) x WHERE "dataset"=ANY($2::text[]) GROUP BY "dataset","kind"',[co,PAYROLL_SYNC_DATASETS]),
   Psql.query('SELECT "modelId"::int AS model,ARRAY_AGG("generalId"::bigint ORDER BY "generalId"::bigint) AS keys FROM "CommonModel" WHERE "company"=$1 AND "modelId" IN (54,59,60) AND COALESCE("status",99)<>-2 AND ("sourceKey" LIKE \'legacy:model:%\' OR "sourceKey" LIKE \'legacy-sync:%\') GROUP BY "modelId"',[co]),
   syncBridgeRequest('/v1/manifest')
  ]);
  const byKey=new Map();for(const row of checkpoints)if(!byKey.has(text(row.dataset)))byKey.set(text(row.dataset),row);
  const manifestByKey=new Map((manifest.datasets||[]).map(row=>[text(row.key),row])),modelByKey={'appointments':54,'lessons':59,'memory-records':60},localByModel=new Map(localCounts.map(row=>[number(row.model),Array.isArray(row.keys)?row.keys.map(text):[]]));
  for(const item of datasets){const checkpoint=byKey.get(item.key),remote=manifestByKey.get(item.key),keys=localByModel.get(modelByKey[item.key])||[];item.state=text(checkpoint?.state)||'never_synced';item.lastSuccessAt=payrollDateIso(checkpoint?.lastSuccessAt);item.lagSeconds=item.lastSuccessAt?Math.max(0,Math.floor((Date.now()-new Date(item.lastSuccessAt).getTime())/1000)):null;item.legacyCount=remote?number(remote.count):null;item.localCount=keys.length;item.localChecksum=await sha256(keys.length?keys.join('\n')+'\n':'');item.pendingFailures=issues.filter(row=>row.dataset===item.key&&row.kind==='failure').reduce((sum,row)=>sum+number(row.total),0);item.conflicts=issues.filter(row=>row.dataset===item.key&&row.kind==='conflict').reduce((sum,row)=>sum+number(row.total),0);item.watermarkCurrent=Boolean(remote&&checkpoint&&number(checkpoint.lastWatermark)>=number(remote.watermark));item.complete=Boolean(checkpoint?.cursor&&item.state==='healthy'&&item.watermarkCurrent&&item.legacyCount!==null&&item.localCount===item.legacyCount&&item.localChecksum===text(remote?.checksum));}
  result.lastSuccessAt=datasets.map(row=>row.lastSuccessAt).filter(Boolean).sort()[0]||'';result.lagSeconds=result.lastSuccessAt?Math.max(0,Math.floor((Date.now()-new Date(result.lastSuccessAt).getTime())/1000)):null;
  result.settlementReady=manifest.consistent===true&&datasets.every(row=>row.complete&&row.lagSeconds!==null&&row.lagSeconds<=120&&!row.pendingFailures&&!row.conflicts);
  result.state=result.settlementReady?'synced':manifest.consistent===true?'delayed':'inconsistent';
  result.message=result.settlementReady?'旧系统预约、上课和抗遗忘记录已追平':manifest.consistent!==true?'旧系统数据在核对期间发生变化，请刷新重试':datasets.filter(row=>!row.complete||row.lagSeconds===null||row.lagSeconds>120||row.pendingFailures||row.conflicts).map(row=>row.key+'：'+(row.pendingFailures||row.conflicts?'有失败或冲突记录':row.state==='catching_up'||row.state==='running'?'仍在追赶':row.state!=='healthy'?'同步未完成':row.localCount!==row.legacyCount||row.localChecksum!==text(manifestByKey.get(row.key)?.checksum)?'旧库与新库记录主键不一致':!row.watermarkCurrent?'增量水位未追平':'最近同步超过两分钟')).join('；');
 }catch(error){result.state='error';result.message='旧系统同步状态无法核验：'+text(error?.message).slice(0,160);}
 return result;
}
async function payrollCanonicalSnapshot(context,input) {
  const co=payrollTenant(context,input);
  const [snapshot,sync]=await Promise.all([payrollSql('SELECT COALESCE(jsonb_agg(s),\'[]\'::jsonb) AS rows,xs_payroll_source_version($1) AS version FROM xs_payroll_sources($1) s',[co]),payrollSettlementSyncStatus(context,input)]);
  const users=await Psql.query('SELECT * FROM "_User" WHERE "company"=$1',[co]);
  const map=new Map(users.map(u=>[number(u.legacyUserId||legacyOf(u).UserID),u]));
  const items=(snapshot.rows||[]).map(row=>{
    const item=payrollSourceProjection(row,'cache'),coach=map.get(item.coachId),student=map.get(item.studentId),storeId=item.storeId||number(coach&&legacyOf(coach).ParentUserID),store=map.get(storeId);
    return {...item,objectId:text(row.lessonObjectId||row.objectId),standardLessonKey:row.standardLessonKey,source:row.source,commonStatus:number(row.commonStatus,99),
      coachName:coach?displayName(coach):item.coachName,coachObjectId:coach?coach.objectId:'',coachMobile:coach?mobileOf(coach):'',
      studentName:student?displayName(student):item.studentName,storeId,storeName:store?displayName(store):item.storeName||'未绑定门店',
      teacherPaySnapshot:row.teacherPaySnapshot==null?null:Number(row.teacherPaySnapshot)};
  });
  return {items,version:snapshot.version,sourceStatus:{available:true,mode:'parse',message:'新数据库有效完课记录（含历史补充）',total:items.length,refreshedAt:new Date().toISOString(),complete:sync.settlementReady,stale:!sync.settlementReady,cacheHit:false,sync}};
}
async function payrollSourceRows(context,input,month,requireLive=false){const data=await payrollCanonicalSnapshot(context,input);return {...data,items:data.items.filter(i=>payrollBusinessDate(i.lessonAt).slice(0,7)===month)};}
function payrollCalculatedLine(item,rates,settlementMonth,rules,mappings){
 const date=payrollBusinessDate(item.lessonAt),type=number(item.classType),mapping=mappings.get(item.courseId),categoryKey=text(item.courseCategoryKey||mapping?.categoryKey),categoryName=text(item.courseCategoryName||mapping?.categoryName),minutes=number(item.durationMinutes)||durationMinutes(type),mode=deliveryMode(item.deliveryMode),configured=date>='2026-09-01',rule=configured?teachingRuleFor(rules,categoryKey,minutes,mode,date):rateFor(rates,type,date),snapshot=item.teacherPaySnapshot!=null&&Number.isFinite(Number(item.teacherPaySnapshot)),amount=snapshot?Number(item.teacherPaySnapshot):configured?number(rule?.teacherPay):type===4?0:number(rule?.amount);
 const exception=item.commonStatus===-2?'上课记录已作废':!item.coachId?'缺少陪练身份':!date?'缺少有效上课日期':item.duplicate?'来源课次重复':configured&&!categoryKey?'课程未确认标准分类':!snapshot&&configured&&!rule?'课程分类、时长和授课方式缺少有效计价规则':!snapshot&&!configured&&type!==4&&!rule?'课型未配置有效单价':amount<0?'课酬金额无效':'';
 return {...item,kind:'lesson',standardLessonKey:payrollIdentity(item),lessonId:item.objectId,lessonAt:payrollDateIso(item.lessonAt),originalMonth:date.slice(0,7),settlementMonth,classType:type,classTypeLabel:categoryName||classType(type),courseCategoryKey:categoryKey,courseCategoryName:categoryName,deliveryMode:mode,deliveryLabel:deliveryLabel(mode),durationMinutes:minutes,durationHours:configured?minutes/60:type===4?0:number(rule?.durationHours,minutes/60),teachingRuleId:text(item.teachingRuleId||rule?.objectId),rateAmount:amount,amount:exception?0:amount,description:snapshot?'按已保存的完课／预约工资快照':'按上课日期匹配历史工资规则',exception};
}
async function payrollPreviewData(context,input,month,includeUsed=false,requireLive=false){
 if(!/^\d{4}-(0[1-9]|1[0-2])$/.test(month))fail(400,'工资月份无效');
 const co=payrollTenant(context,input),[source,rates,rules,mappings,used]=await Promise.all([payrollCanonicalSnapshot(context,input),payRateRows(context,input),teachingRuleRows(context,input),courseTeachingRows(context,input),Psql.query('SELECT "standardLessonKey","batchId","excluded" FROM "PayrollLine" WHERE "company"=$1 AND "kind"=\'lesson\' AND COALESCE("excluded",false)=false',[co])]);
 const other=new Set(used.filter(r=>r.batchId!==input.targetId).map(r=>r.standardLessonKey)),mappingMap=new Map(mappings.filter(r=>r.confirmed).map(r=>[r.courseId,r]));
 const previous=month.endsWith('-01')?(Number(month.slice(0,4))-1)+'-12':month.slice(0,5)+String(Number(month.slice(5))-1).padStart(2,'0');
 const allowLate=!includeUsed&&Boolean((await Psql.one('SELECT EXISTS(SELECT 1 FROM "PayrollBatch" WHERE "company"=$1 AND "month"=$2 AND "status" IN (\'reviewed\',\'paid\')) AS value',[co,previous])).value);
 const grouped=new Map();for(const item of source.items){const key=payrollIdentity(item);if(grouped.has(key))grouped.get(key).duplicate=true;else grouped.set(key,item);}
 const lines=Array.from(grouped.values()).filter(item=>{const m=payrollBusinessDate(item.lessonAt).slice(0,7);return (m===month||!m||allowLate&&m===previous)&& (includeUsed||!other.has(payrollIdentity(item)));}).map(item=>payrollCalculatedLine(item,rates,month,rules,mappingMap));
 const rewards=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 NOT COALESCE(l."excluded",false) AND l."batchId"<>$3)')+' ORDER BY r."objectId"',includeUsed?[co,month]:[co,month,text(input.targetId)]);
 lines.push(...rewards.map(r=>rewardPayrollLine(r,month)));
 if(!includeUsed&&input.targetId){const existing=await Psql.query('SELECT * FROM "PayrollLine" WHERE "company"=$1 AND "batchId"=$2 AND "kind"=\'lesson\' AND NOT COALESCE("excluded",false)',[co,input.targetId]),present=new Set(lines.map(payrollIdentity));for(const row of existing)if(!present.has(row.standardLessonKey))lines.push({...payrollLine(row),standardLessonKey:row.standardLessonKey,exception:'原计薪课次已缺失、跨月或被其他批次占用，请核对',amount:0});}
 return {...payrollResult(month,lines,source.sourceStatus,input),sourceVersion:source.version};
}
async function payrollPage(context,input){const co=payrollTenant(context,input),paging=pageInput(input),month=text(input.month),where='"company"=$1 AND ($2=\'\' OR "month"=$2)',rows=await Psql.query('SELECT * FROM "PayrollBatch" WHERE '+where+' ORDER BY "month" DESC,"objectId" LIMIT $3 OFFSET $4',[co,month,paging.pageSize,(paging.page-1)*paging.pageSize]),total=await Psql.one('SELECT count(*)::int AS total FROM "PayrollBatch" WHERE '+where,[co,month]);return{items:rows.map(r=>({...payrollSummary(r),version:r.version})),total:total.total,page:paging.page,pageSize:paging.pageSize};}
async function payrollDetailData(context,input,targetId){const co=payrollTenant(context,input),batch=await Psql.oneOrNone('SELECT * FROM "PayrollBatch" WHERE "company"=$1 AND "objectId"=$2',[co,targetId]);if(!batch)fail(404,'工资单不存在');const rows=await Psql.query('SELECT * FROM "PayrollLine" WHERE "company"=$1 AND "batchId"=$2 ORDER BY "lessonAt" NULLS LAST,"objectId"',[co,targetId]),lines=rows.map(row=>({...payrollLine(row),standardLessonKey:row.standardLessonKey,exclusionReason:row.exclusionReason,coachMobile:row.coachMobile}));return{...payrollSummary(batch),version:batch.version,lines,exceptions:lines.filter(r=>r.exception&&!r.excluded),coachItems:payrollCoachItems(lines,{})};}
async function payrollOverviewData(context,input){
 const co=payrollTenant(context,input),month=text(input.month)||businessDate().slice(0,7),income=await payrollPreviewData(context,{...input,storeId:0,search:''},month,true),batches=(await payrollPage(context,{...input,month,page:1,pageSize:100})).items;
 const batch=batches[0],settlement=batch?await payrollDetailData(context,{},batch.objectId):null;
 const visible=payrollResult(month,income.lines,income.sourceStatus,input),settlementTotals=settlement?payrollResult(month,settlement.lines,income.sourceStatus,input):null;
 const stores=await Psql.query('SELECT "legacyUserId" AS "storeId",COALESCE(NULLIF("realName",\'\'),NULLIF("nickname",\'\'),"username") AS "storeName" FROM "_User" WHERE "company"=$1 AND ("identityType"=\'store\' OR "legacyGroupId"=2) ORDER BY "legacyUserId"',[co]);
 return {...visible,batches,stores,incomeAmount:visible.payableAmount,settlementAmount:settlementTotals?.payableAmount??null,settlementAdjustmentAmount:settlementTotals?.adjustmentAmount??0,settlementCarryAmount:settlementTotals?settlementTotals.lines.filter(l=>l.kind!=='adjustment'&&l.originalMonth!==month&&!l.exception).reduce((a,l)=>a+l.amount,0):0};
}
async function payrollExportData(context,input){const data=input.targetId?await payrollDetailData(context,input,input.targetId):await payrollOverviewData(context,input),filtered=payrollVisibleLines(data.lines,input).filter(l=>!number(input.coachId)||l.coachId===number(input.coachId)),included=filtered.filter(l=>!l.excluded),excluded=filtered.filter(l=>l.excluded),totals=payrollResult(data.month,included,data.sourceStatus||{refreshedAt:new Date().toISOString()},{});return{fileName:'小树陪练工资_'+data.month+'.xlsx',summary:totals,coachItems:totals.coachItems,lines:included.filter(l=>!l.exception),exceptions:included.filter(l=>l.exception),excluded};}
async function confirmLegacyReviewRewards(context,input,payload){
 requireRole(context,['ops-manager']);const sync=await payrollSettlementSyncStatus(context,input);if(!sync.settlementReady)fail(503,'旧系统数据尚未追平，暂不能核对复习工资：'+sync.message);
 const ids=Array.isArray(payload.rewardIds)?[...new Set(payload.rewardIds.map(text).filter(Boolean))]:[];if(!ids.length||ids.length>100)fail(400,'请选择 1 至 100 条待核对凭据');
 const result=await payrollSql('SELECT xs_review_reward_confirm($1,$2,$3::jsonb,$4) AS result',[payrollTenant(context,input),context.current.id,JSON.stringify(ids),reasonOf(input)]);return result.result;
}
async function payrollExecute(context,input,op,payload={}){
 requireRole(context,['ops-manager']);const co=payrollTenant(context,input),reason=reasonOf(input),target=text(input.targetId),req={reason,version:input.version??payload.version,...payload};
 if(!text(input.idempotencyKey))fail(400,'缺少工资操作请求编号');
 if(['create','refresh','review'].includes(op)){const month=op==='create'?text(payload.month):(await payrollDetailData(context,input,target)).month;const preview=await payrollPreviewData(context,{...input,storeId:0,search:'',targetId:target},month,false);if(!preview.sourceStatus.sync?.settlementReady)fail(503,'工资来源尚未追平，暂不能生成或审核：'+text(preview.sourceStatus.sync?.message));req.month=month;req.lines=preview.lines.map(l=>({...l,lessonAt:l.lessonAt||null}));req.sourceVersion=preview.sourceVersion;}
 const result=await payrollSql('SELECT xs_payroll_command($1,$2,$3,$4,$5::jsonb,$6) AS result',[co,context.current.id,op,target,JSON.stringify(req),input.idempotencyKey]);
 return {...payrollSummary(result.result),version:result.result.version};
}
async function createPayroll(context,input,payload){return payrollExecute(context,input,'create',payload);}
async function refreshPayrollDraft(context,input){await payrollExecute(context,input,'refresh');return payrollDetailData(context,input,input.targetId);}
async function payrollWorkflow(context,input,operation,payload){if(operation==='reopen')fail(409,'工资单审核后永久锁定，差异请进入下期调整单');return payrollExecute(context,input,operation,payload);}
async function payrollAdjustment(context,input,payload){return payrollExecute(context,input,'adjustment',{coachId:payload.coachId,amount:payload.amount});}
async function payrollExclude(context,input){const co=payrollTenant(context,input),line=await Psql.oneOrNone('SELECT "batchId" FROM "PayrollLine" WHERE "company"=$1 AND "objectId"=$2',[co,input.targetId]);if(!line)fail(404,'明细不存在');return payrollExecute(context,{...input,targetId:line.batchId},'exclude',{lineId:input.targetId});}
async function payrollCoaches(context,input){const co=payrollTenant(context,input),rows=await Psql.query('SELECT * FROM "_User" WHERE "company"=$1 AND ("identityType"=\'coach\' OR "identityType" IS NULL AND "legacyGroupId"=3) AND NOT COALESCE("isDeleted",false) ORDER BY "legacyUserId"',[co]);return{items:rows.map(u=>({coachId:number(u.legacyUserId||legacyOf(u).UserID),coachName:displayName(u),coachObjectId:u.objectId}))};}
async function teachingRuleRows(context,input){
 const co=payrollTenant(context,input),stored=await Psql.query('SELECT * FROM "TeachingCompensationRule" WHERE "company"=$1 ORDER BY "revision" DESC,"createdAt" DESC,"objectId"',[co]),names=new Map(TEACHING_CATEGORY_DEFAULTS.map(c=>[c.key,c.name])),latest=new Set(),rows=[];
 for(const r of stored){const key=[r.categoryKey,r.durationMinutes,r.deliveryMode,r.effectiveFrom].join(':');const superseded=latest.has(key);latest.add(key);rows.push({...teachingRuleProjection(r,names),revision:r.revision,supersedesId:r.supersedesId,superseded,active:r.active!==false&&!superseded});}
 return [...defaultTeachingRules().filter(r=>!latest.has([r.categoryKey,r.durationMinutes,r.deliveryMode,r.effectiveFrom].join(':'))),...rows];
}
async function saveTeachingRule(context,input,payload){
 requireRole(context,['ops-manager']);const co=payrollTenant(context,input),categoryKey=text(payload.categoryKey),duration=number(payload.durationMinutes),mode=text(payload.deliveryMode),pay=Number(payload.teacherPay),effectiveFrom=text(payload.effectiveFrom),allowed={word:[30,60],trial:[60],primary_writing:[30,60],middle_writing:[30,60],high_writing:[30,60],word_self_study:[60]};
 if(!allowed[categoryKey]?.includes(duration)||!['online','offline'].includes(mode)||!Number.isFinite(pay)||pay<0||pay>1000000000||Math.abs(Math.round(pay*100)-pay*100)>0.000001||!/^\d{4}-\d{2}-\d{2}$/.test(effectiveFrom)||payrollBusinessDate(effectiveFrom)!==effectiveFrom)fail(400,'工资规则参数无效');
 const rules=await teachingRuleRows(context,input),previous=rules.find(r=>!r.isDefault&&!r.superseded&&r.categoryKey===categoryKey&&r.durationMinutes===duration&&r.deliveryMode===mode&&r.effectiveFrom===effectiveFrom),req={categoryKey,durationMinutes:duration,deliveryMode:mode,teacherPay:pay,effectiveFrom,creditCosts:teachingCredits(categoryKey,duration),active:payload.active!==false,supersedesId:previous?.objectId||'',reason:reasonOf(input)};
 return (await payrollSql('SELECT xs_payroll_rule($1,$2,$3::jsonb) AS result',[co,context.current.id,JSON.stringify(req)])).result;
}
async function deactivateTeachingRule(context,input){const co=payrollTenant(context,input),r=await Psql.oneOrNone('SELECT * FROM "TeachingCompensationRule" WHERE "company"=$1 AND "objectId"=$2',[co,input.targetId]);if(!r)fail(404,'规则不存在');const latest=(await teachingRuleRows(context,input)).find(v=>v.objectId===r.objectId);if(latest?.superseded)fail(409,'请选择当前规则版本');return saveTeachingRule(context,input,{...r,active:false});}
async function historicalReservationPreview(context,input){const co=payrollTenant(context,input),data=await historicalReservationPreviewLegacy(context,input),rows=await Psql.query('SELECT c."objectId",a."updatedAt"::text AS "updatedAt" FROM "CommonModel" c JOIN "CourseAppointment" a ON a."company"=c."company" AND a."id"::text=c."itemId"::text WHERE c."company"=$1 AND c."modelId"=54',[co]),versions=new Map(rows.map(r=>[r.objectId,r.updatedAt]));return{...data,items:data.items.map(i=>({...i,expectedUpdatedAt:versions.get(i.appointmentId)}))};}
async function commitHistoricalReservations(context,input,payload){requireRole(context,['ops-manager']);const co=payrollTenant(context,input),selected=new Set((payload.appointmentIds||[]).map(text));if(!selected.size)fail(400,'请选择预约');const before=await Psql.one('SELECT xs_payroll_source_version($1) AS version',[co]),preview=await historicalReservationPreview(context,input),items=preview.items.filter(i=>selected.has(i.appointmentId));if(!items.length)return{reserved:0,held:0};return(await payrollSql('SELECT xs_payroll_reservations($1,$2,$3::jsonb,$4,$5) AS result',[co,context.current.id,JSON.stringify(items),reasonOf(input),before.version])).result;}
async function creditReconciliationPreview(context,input){const rows=await Psql.query('SELECT s AS row FROM xs_payroll_refund_rows($1) s',[payrollTenant(context,input)]),items=rows.map(r=>r.row);return{items,summary:{completedReviewCount:items.length,differenceCount:items.filter(i=>i.adjustment>0).length,refundTotal:items.reduce((s,i)=>s+Number(i.adjustment),0)},refreshedAt:new Date().toISOString()};}
async function commitCreditReconciliation(context,input,payload){requireRole(context,['ops-manager']);if(!payload.appointmentIds?.length)fail(400,'请选择要核对的预约');return(await payrollSql('SELECT xs_payroll_refunds($1,$2,$3::jsonb,$4) AS result',[payrollTenant(context,input),context.current.id,JSON.stringify(payload.appointmentIds.map(text)),reasonOf(input)])).result;}
async function writingMappingPage(context,input){const co=payrollTenant(context,input),rows=await Psql.query(`SELECT c."objectId" AS "appointmentId",c."generalId" AS "appointmentGeneralId",c."title" AS "studentName",c."subtitle" AS "courseName",a."szyh" AS "studentId",a."yysj" AS "startsAt",a."updatedAt",a."courseCategoryKey" AS "categoryKey",a."categoryEvidence" AS evidence,COALESCE(to_jsonb(u)->>'grade',u."legacyUserData"->>'Position',u."legacyUserData"->'UserBase'->>'Position','') AS grade,u."nickname",u."username" FROM "CommonModel" c JOIN "CourseAppointment" a ON a."company"=c."company" AND a."id"::text=c."itemId"::text LEFT JOIN "_User" u ON u."company"=c."company" AND u."legacyUserId"::text=a."szyh"::text WHERE c."company"=$1 AND c."modelId"=54 AND c."status"<>-2 AND c."subtitle" ~ '阅读|写作|语法|作文|强化' AND a."dszt" IN ('0','10') AND NOT (COALESCE(a."courseCategoryKey",'') IN ('primary_writing','middle_writing','high_writing') AND length(trim(COALESCE(a."categoryEvidence",'')))>=2) ORDER BY a."yysj",c."objectId"`,[co]);
 return{items:rows.map(r=>{const stage=teachingStageFromDatabase([r.grade,r.nickname,r.username]);return{...r,suggestedCategoryKey:stage?stage+'_writing':'',evidence:r.evidence||r.grade||'',categoryKey:r.categoryKey||'',studentName:r.studentName||r.nickname};})};}
async function writingMappingCommit(context,input,payload){requireRole(context,['ops-manager']);return(await payrollSql('SELECT xs_payroll_writing_mapping($1,$2,$3,$4,$5,$6::timestamptz,$7) AS result',[payrollTenant(context,input),context.current.id,input.targetId,text(payload.categoryKey),text(payload.evidence),dateIso(input.expectedUpdatedAt)||null,reasonOf(input)])).result;}
async function payRateRows(context,input){
 const rows=await Psql.query('SELECT * FROM "CoachPayRate" WHERE "company"=$1 ORDER BY "createdAt" DESC,"objectId" DESC',[payrollTenant(context,input)]),seen=new Set(),current=[];
 for(const row of rows){const key=row.classType+':'+row.effectiveFrom;if(seen.has(key))continue;seen.add(key);current.push({...row,classType:number(row.classType),classTypeLabel:classType(row.classType),amount:number(row.amount),durationHours:number(row.durationHours),active:row.active!==false,isDefault:false});}
 return [...current,...defaultPayRates().filter(r=>!seen.has(r.classType+':'+r.effectiveFrom))].sort((a,b)=>b.effectiveFrom.localeCompare(a.effectiveFrom));
}
async function savePayRateUpsert(context,input,payload){
 requireRole(context,['ops-manager']);const type=Number(payload.classType),amount=Number(payload.amount),hours=Number(payload.durationHours),effectiveFrom=text(payload.effectiveFrom);
 if(![1,2,3,4].includes(type)||!Number.isFinite(amount)||amount<0||amount>1000000000||Math.abs(Math.round(amount*100)-amount*100)>.000001||!Number.isFinite(hours)||hours<=0||hours>24||payrollBusinessDate(effectiveFrom)!==effectiveFrom)fail(400,'课型、单价、工时或生效日期无效');
 return(await payrollSql('SELECT xs_payroll_legacy_rate($1,$2,$3::jsonb) AS result',[payrollTenant(context,input),context.current.id,JSON.stringify({classType:type,amount,durationHours:hours,effectiveFrom,active:payload.active!==false,reason:reasonOf(input)})])).result;
}