xiaoshu-app/scripts/cloud/member-enrollment.js

// Appended to the operations gateway; all writes remain behind its authentication.
async function enrollmentSql(sql, args, scope=null) {
  try {
    if(scope?.context.storeId)return await Psql.tx(async tx=>{
      await tx.one('SELECT xs_store_scope_guard($1,$2,$3,$4,$5)',[pointerId(scope.context.company),scope.context.current.id,scope.studentId,scope.coachId||0,scope.targetId||'']);
      return tx.one(sql,args);
    });
    return await Psql.one(sql,args);
  }
  catch(error) { if(/does not exist/i.test(text(error.message))) fail(503,'用户开课数据迁移尚未完成'); fail(409,text(error.message).replace(/^.*ERROR:\s*/,'')); }
}
async function creditAccounts(context,input,store=false) {
  if(store)return storeCreditAccounts(context,input);
  const target=store?await scopedStoreObject(context,{...input,targetId:input.objectId||input.targetId}):await memberTarget(context,input,text(input.objectId||input.targetId));
  const uid=number(target.get('legacyUserId')),co=pointerId(target.get('company')),legacy=target.get('legacyUserData')||{},reserved=await reservedCreditsFor(context,input,uid);
  const accounts=Object.entries(TEACHING_CREDIT_LABELS).map(([account,label])=>({account,label,balance:number(legacy[account]),reserved:number(reserved[account]),available:number(legacy[account])-number(reserved[account])}));
  const transactions=await Psql.query('SELECT t."objectId",t."kind",t."account",t."amount",t."storeId",t."studentId",t."sourceTransactionId",t."operatorId",t."reason",t."payMethod",t."balances",t."createdAt",t."operationId",t."amount"-COALESCE((SELECT SUM(r."amount") FROM "CreditTransaction" r WHERE r."company"=t."company" AND r."sourceTransactionId"=t."objectId" AND r."kind"=\'refund\'),0) AS "unreturned" FROM "CreditTransaction" t WHERE t."company"=$1 AND '+(store?'t."storeId"':'t."studentId"')+'=$2 ORDER BY t."createdAt" DESC',[co,uid]);
  const parent=store?null:await userByLegacyId(context,input,number(legacy.ParentUserID),2);
  const trial=store?null:await Psql.oneOrNone('SELECT status,evidence FROM "TrialEligibility" WHERE company=$1 AND "studentId"=$2',[co,uid]);
  return{trialStatus:trial?.status||'pending',accounts,transactions:transactions.filter(t=>(!context.storeId||number(t.storeId)===context.storeId)&&(store||!t.operationId||t.account!=='UserPoint'||!transactions.some(o=>o.operationId===t.operationId&&o.account!=='UserPoint'))),storeId:store?uid:number(legacy.ParentUserID),storeName:store?displayName(target.toJSON()):parent?displayName(parent):'',canPost:store?context.isSuperAdmin:roleOf(context)==='ops-manager'};
}
async function postCredits(context,input,payload,store=false) {
  if(store)return storeCreditPost(context,input,payload);
  requireRole(context,['ops-manager']);
  const target=store?await scopedStoreObject(context,input):await memberTarget(context,input,text(input.targetId));
  const account=text(payload.account)||({1:'Purse',2:'SilverCoin',3:'UserExp',4:'UserPoint'})[number(payload.type)],kind=store?'fund':payload.direction==='deduct'?'refund':'transfer';
  if(!store){const result=await enrollmentSql('SELECT xs_credit_operation($1,$2,$3,$4::jsonb,$5,$6,$7) AS result',[pointerId(target.get('company')),context.current.id,target.id,JSON.stringify({...payload,account}),reasonOf(input),text(input.idempotencyKey),text(input.expectedUpdatedAt)],{context,studentId:number(target.get('legacyUserId'))});return result.result;}
  const result=await enrollmentSql('SELECT xs_credit_post($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) AS result',[pointerId(target.get('company')),context.current.id,kind,target.id,account,number(payload.amount),text(payload.sourceTransactionId),reasonOf(input),text(payload.payMethod),text(input.idempotencyKey)]);
  const clean={...result.result};delete clean.request;return clean;
}
async function enrollmentCatalog(context,input) {
  const co=companyIdOf(context,input);
  // Course roots are direct children of the legacy academic-stage nodes, plus explicitly mapped content roots.
  const rows=await Psql.query(`WITH RECURSIVE roots AS (
    SELECT n.* FROM "Node" n WHERE n."company"=$1 AND (n."parentId" IN (8,9,10,11,12) OR EXISTS(SELECT 1 FROM "CourseTeachingProfile" p WHERE p."company"=n."company" AND p."courseId"=n."nodeId"))) ,
    descendants AS (SELECT "nodeId" AS root,"nodeId",ARRAY["nodeId"] AS visited FROM roots UNION ALL SELECT d.root,n."nodeId",d.visited||n."nodeId" FROM descendants d JOIN "Node" n ON n."company"=$1 AND n."parentId"=d."nodeId" WHERE NOT n."nodeId"=ANY(d.visited) AND COALESCE(n."zstatus",99)=99),
    counts AS (SELECT d.root,COUNT(DISTINCT c."objectId") AS total FROM descendants d JOIN "CommonModel" c ON c."company"=$1 AND c."nodeId"=d."nodeId" AND c."modelId"=52 AND COALESCE(c."status",99)=99 GROUP BY d.root)
    SELECT n."nodeId",n."nodeName",n."parentId",n."zstatus",p."nodeName" AS parent,c.total FROM roots n LEFT JOIN "Node" p ON p."company"=$1 AND p."nodeId"=n."parentId" LEFT JOIN counts c ON c.root=n."nodeId" ORDER BY CASE WHEN n."sourceKey" LIKE 'cloud:%' THEN n."createdAt" END DESC NULLS LAST,n."nodeId" DESC`,[co]);
  return rows.map(row=>({courseId:number(row.nodeId),courseName:text(row.nodeName),stageKey:courseStageFromDatabase([row.nodeName,row.parent]).key,stageName:courseStageFromDatabase([row.nodeName,row.parent]).name,wordCount:number(row.total),active:number(row.zstatus,99)===99}));
}
async function enrollmentOptions(context,input) {
  const categories=await teachingCategoryRows(context,input),categoryKey=text(input.categoryKey||input.payload?.categoryKey||categories[0]?.key),category=categories.find(row=>row.key===categoryKey);if(!category)fail(400,'课程分类无效');
  const uid=number(input.studentId||input.payload?.studentId),student=await userByLegacyId(context,input,uid,1);if(!student)fail(404,'学员不存在');
  const reserved=await reservedCreditsFor(context,input,uid),rules=await teachingRuleRows(context,input),date=businessDate();
  const effective=rules.filter(row=>row.categoryKey===categoryKey&&teachingRuleFor(rules,categoryKey,row.durationMinutes,row.deliveryMode,date)?.objectId===row.objectId);
  const options=effective.map(row=>({...row,affordable:row.creditCosts.length>0&&row.creditCosts.every(cost=>number(legacyOf(student)[cost.account])-number(reserved[cost.account])>=cost.amount)}));
  const eligible=!student.isDisabled&&options.some(row=>row.affordable),reason=student.isDisabled?'学员已停用':!effective.length?'该分类尚无有效课时规则':!eligible?'对应课时或陪练时长不足，请先充值':'';
  const bindings=await enrollmentBindings(context,input,uid),catalog=await enrollmentCatalog(context,input);
  return{categories,categoryKey,eligible,reason,rules:options,items:catalog.filter(row=>categoryMatchesStage(categoryKey,row.stageKey)).map(row=>({...row,alreadyOpened:bindings.some(b=>b.courseId===row.courseId&&b.categoryKey===categoryKey),eligible:eligible&&row.active&&row.wordCount>0,reason:!row.active?'内容已停用':!row.wordCount?'课程内容为空':reason}))};
}
async function saveEnrollment(context,input,payload) {
  requireRole(context,['ops-manager','ops-staff']);const reason=reasonOf(input),uid=number(payload.studentId),category=text(payload.categoryKey),ids=[...new Set((Array.isArray(payload.courseIds)?payload.courseIds:[payload.courseId]).map(Number))].sort((a,b)=>a-b);
  if(!ids.length||ids.length>100||ids.some(id=>!Number.isInteger(id)||id<=0))fail(400,'请选择 1 至 100 项课程内容');
  const req={uid,category,courseIds:ids,actor:context.current.id,reason},co=companyIdOf(context,input);
  const prior=await Psql.oneOrNone('SELECT "request","result" FROM "EnrollmentCommand" WHERE "company"=$1 AND "idempotencyKey"=$2',[co,text(input.idempotencyKey)]);
  if(prior){const same=await Psql.one('SELECT $1::jsonb=$2::jsonb AS same',[JSON.stringify(prior.request),JSON.stringify(req)]);if(!same.same)fail(409,'请求编号已用于其他开课操作');return prior.result;}
  const options=await enrollmentOptions(context,{...input,studentId:uid,categoryKey:category});
  if(!options.eligible)fail(409,options.reason);
  const selected=ids.map(id=>options.items.find(row=>row.courseId===id));if(selected.some(row=>!row||!row.eligible))fail(409,'课程内容为空、已停用或与分类学阶不符');
  const categoryName=options.categories.find(row=>row.key===category).name;
  return(await enrollmentSql('SELECT xs_enroll($1,$2,$3,$4,$5::jsonb,$6::jsonb,$7,$8) AS result',[companyIdOf(context,input),context.current.id,uid,category,JSON.stringify(selected.map(row=>({...row,categoryName}))),JSON.stringify(options.rules.map(rule=>rule.creditCosts)),reason,text(input.idempotencyKey)],{context,studentId:uid})).result;
}
async function enrollmentBindings(context,input,uid=0) {
  const rows=await Psql.query('SELECT b.*,c."objectId" AS "targetId",c."status",n."nodeName" AS "courseName" FROM "CourseBinding" b JOIN "CommonModel" c ON c."company"=b."company" AND c."modelId"=58 AND c."itemId"=b."id" JOIN "Node" n ON n."company"=b."company" AND n."nodeId"::text=b."kcid"::text WHERE b."company"=$1 AND ($2::numeric=0 OR b."yhid"::text=$2::text) AND c."status"=99 AND b."enrollmentStatus"=\'active\' AND COALESCE(n."zstatus",99)=99 AND ($3::text=\'\' OR EXISTS(SELECT 1 FROM \"_User\" s WHERE s.company=b.company AND s.\"legacyUserId\"::text=b.yhid::text AND s.\"identityType\"=\'member\' AND s.\"legacyUserData\"->>\'ParentUserID\'=$3 AND COALESCE(s.\"isDeleted\",false)=false))',[companyIdOf(context,input),uid,context.storeId?String(context.storeId):'']);
  return rows.map(row=>({bindingId:text(row.objectId),targetId:text(row.targetId),studentId:number(row.yhid),courseId:number(row.kcid),courseName:text(row.courseName),categoryKey:text(row.categoryKey),categoryName:TEACHING_CATEGORY_LABELS.get(text(row.categoryKey))||text(row.categoryName),learnedCount:number(row.yxx),wordCount:number(row.cksl)}));
}
async function enrollmentTeachingContext(context,input,payload,date) {
  const rows=(await enrollmentBindings(context,input,number(payload.studentId))).filter(row=>row.courseId===number(payload.courseId)&&(!payload.bindingId||row.bindingId===text(payload.bindingId)));
  if(rows.length!==1)fail(409,rows.length?'该内容有多个开课分类，请选择具体开课记录':'该课程尚未有效开课，请先开课或确认历史分类');
  const binding=rows[0],rules=await teachingRuleRows(context,input),rule=teachingRuleFor(rules,binding.categoryKey,appointmentDuration(payload),deliveryMode(payload.deliveryMode||payload.deliveryMethod),date);
  if(!rule)fail(409,'该开课分类没有对应时长和授课方式的有效规则');
  return{bindingId:binding.bindingId,mapping:{categoryKey:binding.categoryKey,categoryName:binding.categoryName},rule};
}
async function enrollmentSchedule(context,input,data) {
  const [bindings,rules]=await Promise.all([enrollmentBindings(context,input),teachingRuleRows(context,input)]),date=text(input.dateFrom)||businessDate();
  const courses=bindings.map(b=>{const active=rules.filter(r=>r.categoryKey===b.categoryKey&&teachingRuleFor(rules,b.categoryKey,r.durationMinutes,r.deliveryMode,date)?.objectId===r.objectId);return{...b,courseCategoryKey:b.categoryKey,courseCategoryName:b.categoryName,memberIds:[b.studentId],availableToAll:false,mappingConfirmed:active.length>0,supportedDurations:[...new Set(active.map(r=>r.durationMinutes))],supportedDeliveryModes:[...new Set(active.map(r=>r.deliveryMode))],categoryName:b.categoryName};});
  return{...data,courses};
}
async function toggleEnrollment(context,input,restore) {
  requireRole(context,['ops-manager']);const reason=reasonOf(input),co=companyIdOf(context,input),target=await scopedObject('CommonModel',text(input.targetId),context,input);assertVersion(target,input.expectedUpdatedAt);
  if(number(target.get('modelId'))!==58)fail(400,'目标不是开课记录');
  const b=await findAddon('CourseBinding',target.get('company'),target.get('itemId'));if(!b)fail(404,'开课记录不存在');
  let costs=[];if(restore){const opt=await enrollmentOptions(context,{...input,studentId:b.get('yhid'),categoryKey:b.get('categoryKey')});if(!opt.items.some(row=>row.courseId===number(b.get('kcid'))&&row.eligible))fail(409,opt.reason||'课程内容不可用');costs=opt.rules.map(rule=>rule.creditCosts);}
  return(await enrollmentSql('SELECT xs_enrollment_status($1,$2,$3,$4,$5,$6::jsonb) AS result',[co,b.id,restore,context.current.id,reason,JSON.stringify(costs)],{context,studentId:number(b.get('yhid')),targetId:target.id})).result;
}
async function saveEnrollmentSchedule(context,input,payload,editing=false) {
 requireRole(context,['ops-manager','ops-staff']);const reason=reasonOf(input),co=companyIdOf(context,input),idem=text(input.idempotencyKey),editId=editing?text(input.targetId):'',req={actor:context.current.id,operation:editing?'update':'create',targetId:editId,payload,reason};
 if(idem.length<8)fail(400,'缺少排课请求编号');
 const prior=await Psql.oneOrNone('SELECT "request","result" FROM "EnrollmentCommand" WHERE "company"=$1 AND "idempotencyKey"=$2',[co,idem]);
 if(prior){const same=await Psql.one('SELECT $1::jsonb=$2::jsonb AS same',[JSON.stringify(prior.request),JSON.stringify(req)]);if(!same.same)fail(409,'请求编号已用于其他排课操作');return prior.result;}
 const check=await preflightAppointments(context,input,payload,editId),skip=payload.skipConflicts===true&&!editing,conflicts=new Set(check.conflictIndexes),selected=check.candidates.filter(row=>!skip||!conflicts.has(row.index));
 if(check.conflictCount&&!skip)fail(409,'排课存在时间冲突');if(!selected.length)fail(409,'没有可保存的排课');
 const coach=number(payload.coachId)?await userByLegacyId(context,input,payload.coachId,3):null,student=await userByLegacyId(context,input,payload.studentId,1);
 const entries=selected.map(item=>{const start=new Date(item.startsAt),date=businessDate(start),time=start.toLocaleTimeString('zh-CN',{timeZone:'Asia/Shanghai',hour12:false,hour:'2-digit',minute:'2-digit'});return{common:{title:displayName(student)+'（'+check.courseCategoryName+'）',subtitle:check.courseName},addon:{szyh:String(payload.studentId),pl:coach?String(payload.coachId):'',fxpl:coach?String(payload.coachId):'',plxm:coach?displayName(coach):'',kcid:String(payload.courseId),yykcid:String(payload.courseId),dslx:String(legacyClassTypeFor(check.courseCategoryKey,check.durationMinutes)),jffs:check.deliveryLabel,bindingId:check.bindingId,courseCategoryKey:check.courseCategoryKey,courseCategoryName:check.courseCategoryName,durationMinutes:check.durationMinutes,deliveryMode:check.deliveryMode,teachingRuleId:item.teachingRule.objectId,teacherPaySnapshot:item.teachingRule.teacherPay,creditCostsSnapshot:item.teachingRule.creditCosts,bxrq:date,sdsd:time,yysj:date+' '+time,dszt:'0'}};});
 const result=(await enrollmentSql('SELECT xs_schedule_save($1,$2,$3::jsonb,$4::jsonb,$5,$6,$7) AS result',[co,context.current.id,JSON.stringify(req),JSON.stringify(entries),idem,editId,text(input.expectedUpdatedAt)],{context,studentId:number(payload.studentId),coachId:number(payload.coachId),targetId:editId})).result;return{...result,skipped:check.candidates.length-selected.length,warnings:check.warnings};
}
async function cancelEnrollmentSchedule(context,input,restore){
 requireRole(context,['ops-manager','ops-staff']);const reason=reasonOf(input),pair=await appointmentPair(context,input,text(input.targetId));assertVersion(pair.common,input.expectedUpdatedAt);

 return(await enrollmentSql('SELECT xs_schedule_cancel($1,$2,$3,$4,$5,$6) AS result',[pointerId(pair.common.get('company')),pair.common.id,restore,context.current.id,reason,text(input.expectedUpdatedAt)],{context,studentId:number(pair.addon.get('szyh')),targetId:pair.common.id})).result;
}