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 directPayrollSyncStatus(context,input,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){
 // Confirmation validates the selected immutable receipts in SQL; bridge configuration
 // is unrelated to whether these particular legacy tasks were completed and unpaid.
 requireRole(context,['ops-manager']);
 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;}
function writingSchoolYear(value){
 const match=text(value).match(/^(\d{4})-(\d{2})-(\d{2})(?:[ T]|$)/);if(!match)return null;
 const year=Number(match[1]),month=Number(match[2]),day=Number(match[3]),date=new Date(Date.UTC(year,month-1,day));
 if(date.getUTCFullYear()!==year||date.getUTCMonth()+1!==month||date.getUTCDate()!==day)return null;
 return month>=9?year:year-1;
}
function writingStagesInText(value){
 const raw=text(value),stages=[];if(!raw||/待确认|不确定|未知|可能|疑似|不详|暂定|[?？]|待核/.test(raw))return stages;
 if(/高中|高[一二三123]|(?:^|[^一二三四五六七八九十\d])(?:十[一二]?|1[012])年级/.test(raw))stages.push('high');
 if(/初中|初[一二三123]|(?:^|[^一二三四五六七八九十\d])[七八九789]年级/.test(raw))stages.push('middle');
 if(/小学|(?:^|[^一二三四五六七八九十\d])[一二三四五六123456]年级/.test(raw))stages.push('primary');
 return stages;
}
function writingStageAssessment(row,confirmed,asOf=businessDate()){
 const schoolYear=writingSchoolYear(row.startsAt),currentYear=writingSchoolYear(asOf),sources=[];
 const labels={primary:'小学',middle:'初中',high:'高中'};
 if(schoolYear==null)return{canAutoConfirm:false,suggestedCategoryKey:'',evidence:'',assessmentReason:'预约日期无效，需人工核对'};
 for(const prior of confirmed||[]){if(text(prior.studentId)!==text(row.studentId)||writingSchoolYear(prior.startsAt)!==schoolYear||text(prior.evidence).length<2)continue;
  const stage={primary_writing:'primary',middle_writing:'middle',high_writing:'high'}[prior.category];
  if(stage)sources.push({stage,evidence:'本学年已确认预约 '+text(prior.appointmentGeneralId)+' 的'+labels[stage]+'分类'});
 }
 // Current profile labels cannot establish a student's stage in another school year.
 if(schoolYear===currentYear){
  const fields=[['档案年级',row.grade],['旧档案学段',row.position],['旧扩展档案学段',row.basePosition],['学员姓名',row.realName],['学员昵称',row.nickname],['学员账号',row.username],['旧学员昵称',row.oldName],['旧学员账号',row.oldUsername],['预约学员名称',row.studentName]];
  for(const [label,value]of fields)for(const stage of writingStagesInText(value))sources.push({stage,evidence:label+'“'+text(value)+'”明确标注'+labels[stage]});
 }
 const stages=[...new Set(sources.map(s=>s.stage))];
 if(stages.length!==1)return{canAutoConfirm:false,suggestedCategoryKey:'',evidence:'',assessmentReason:stages.length?'学段证据冲突：'+stages.map(s=>labels[s]).join('／'):schoolYear!==currentYear?'跨学年且无该学年确认记录，需人工核对':'档案和本学年记录均无明确学段，需人工核对'};
 const stage=stages[0],evidence=[...new Set(sources.map(s=>s.evidence))].slice(0,3).join('；');
 return{canAutoConfirm:true,suggestedCategoryKey:stage+'_writing',evidence,assessmentReason:'系统证据一致：'+labels[stage]};
}
async function writingMappingPage(context,input){const co=payrollTenant(context,input),month=text(input.month);if(month&&!/^\d{4}-(0[1-9]|1[0-2])$/.test(month))fail(400,'工资月份无效');const 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,to_jsonb(u)->>'grade' AS grade,u."legacyUserData"->>'Position' AS position,u."legacyUserData"->'UserBase'->>'Position' AS "basePosition",u."legacyUserData"->>'HoneyName' AS "oldName",u."legacyUserData"->>'UserName' AS "oldUsername",u."realName",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') OR ($2<>'' AND c."generalId"::text IN (SELECT source->>'yyds' FROM xs_payroll_sources($1) source WHERE to_char(xs_payroll_time(source->>'lessonAt') AT TIME ZONE 'Asia/Shanghai','YYYY-MM')=$2))) 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,month]);
 const ids=[...new Set(rows.map(r=>text(r.studentId)))],confirmed=ids.length?await Psql.query(`SELECT c."generalId" AS "appointmentGeneralId",a."szyh" AS "studentId",a."yysj" AS "startsAt",a."courseCategoryKey" AS category,a."categoryEvidence" AS evidence 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 AND c."status"<>-2 AND a."szyh"::text=ANY($2::text[]) AND a."courseCategoryKey" IN ('primary_writing','middle_writing','high_writing') AND length(trim(COALESCE(a."categoryEvidence",'')))>=2`,[co,ids]):[];
 return{items:rows.map(r=>{const decision=writingStageAssessment(r,confirmed);return{...r,...decision,grade:r.grade||r.position||r.basePosition||'',evidence:r.evidence||decision.evidence,categoryKey:r.categoryKey||decision.suggestedCategoryKey,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;
}

// A verified manual API import is useful evidence even when no continuous bridge exists.
// It never substitutes for a payroll cutoff snapshot or enables settlement.
async function directSyncReceipt(context,input){
 const co=companyIdOf(context,input);if(!co)return null;
 const rows=await Psql.query('SELECT "detail" FROM "SysLog" WHERE "company"=$1 AND "type2"=\'legacy-direct-sync-completed\' ORDER BY "createdAt" DESC LIMIT 1',[co]);
 if(!rows.length)return null;let event;try{event=typeof rows[0].detail==='string'?JSON.parse(rows[0].detail):rows[0].detail;}catch{return null;}
 const receipt=event?.after;if(receipt?.source!=='old-api/WXAPP'||!['completed','completed-with-preserved-conflicts'].includes(receipt.status)||!Array.isArray(receipt.datasets)||!receipt.datasets.length||!receipt.datasets.every(r=>r.reconciled===true&&number(r.failed)===0)||!payrollDateIso(receipt.completedAt))return null;
 return receipt;
}
async function directPayrollSyncStatus(context,input,fallback){
 try{const receipt=await directSyncReceipt(context,input);if(!receipt)return fallback;const lastSuccessAt=payrollDateIso(receipt.completedAt),lagSeconds=Math.max(0,Math.floor((Date.now()-new Date(lastSuccessAt).getTime())/1000));
 return {...fallback,state:'batch_verified',lastSuccessAt,lagSeconds,settlementReady:false,message:'旧接口手动同步批次已完成并核对业务编号；保留差异仍待复核，结算前须核验工资截止范围。每日自动同步尚未验收。',datasets:fallback.datasets.map(r=>{const model={'appointments':54,'lessons':59,'memory-records':60}[r.key],d=receipt.datasets.find(d=>number(d.modelId)===model);return d?{...r,state:'batch_verified',lastSuccessAt,lagSeconds,legacyCount:number(d.source),localCount:number(d.source),conflicts:number(d.conflicts),pendingFailures:number(d.failed),complete:false}:r;})};
 }catch{return fallback;}
}
async function operationalSyncIndicator(context,input){
 const config=syncConfig();if(!config.baseUrl||!config.keyId||!config.secret){const receipt=await directSyncReceipt(context,input).catch(()=>null);if(receipt){const lastSuccessAt=payrollDateIso(receipt.completedAt);return{state:'delayed',label:'旧接口批次已同步，保留差异待核对',lastSuccessAt,lagSeconds:Math.max(0,Math.floor((Date.now()-new Date(lastSuccessAt).getTime())/1000))};}return{state:'not_configured',label:'尚未启用持续同步',lastSuccessAt:'',lagSeconds:null};}
 const rows=await newClassObjects('LegacySyncCheckpoint',context,input,1000),critical=rows.filter(row=>PAYROLL_SYNC_DATASETS.includes(text(row.get('dataset')))),lastSuccessAt=critical.map(row=>dateIso(row.get('lastSuccessAt'))).filter(Boolean).sort()[0]||'',lagSeconds=lastSuccessAt?Math.max(0,Math.floor((Date.now()-new Date(lastSuccessAt).getTime())/1000)):null,healthy=critical.length===3&&critical.every(row=>text(row.get('state'))==='healthy')&&lagSeconds!==null&&lagSeconds<=120;return{state:healthy?'synced':'delayed',label:healthy?'工资相关数据刚刚同步':'数据同步延迟，正在追平',lastSuccessAt,lagSeconds};
}