xiaoshu-app/scripts/cloud/lesson-records.js

// Shared classroom history for member detail, scheduling and the records centre.
const LESSON_RECORDS_VERSION='2026-10-13-lesson-records-v10';
function lessonHistoryBase(){return `WITH facts AS (
 SELECT l.*,c."objectId" AS "targetId",c."generalId",c."createdAt" AS "commonCreatedAt",
 COALESCE(NULLIF(NULLIF(l.yyds,'0'),''),'lesson:'||l."objectId") AS "classKey",
 COALESCE(r."actualStartAt",l."lessonAt",xiaoshu_safe_shanghai_timestamp(l.plpjsj),c."createdAt",l."sourceCreatedAt",l."createdAt") AS "actualAt",
 COALESCE(NULLIF(s."realName",''),NULLIF(s.nickname,''),s."legacyUserData"->>'HoneyName',s.username,l."studentName",'学员 '||l.xymz) AS "studentNameResolved",
 COALESCE(NULLIF(p."realName",''),NULLIF(p.nickname,''),p."legacyUserData"->>'HoneyName',p.username,l."coachName",'历史陪练 '||l.jsmz) AS "coachNameResolved",
 COALESCE(NULLIF(l.kcmc,''),NULLIF(l."courseName",''),n."nodeName",'未记录课程') AS "courseNameResolved",
 COALESCE(NULLIF(l.szmdid,''),NULLIF(l."storeId"::text,'0'),s."legacyUserData"->>'ParentUserID') AS "storeIdResolved",
 COALESCE(NULLIF(st."realName",''),NULLIF(st.nickname,''),st."legacyUserData"->>'HoneyName',st.username,'未记录门店') AS "storeName",
 r."contentSummary" AS "feedbackSummary"
 FROM "LessonRecord" l
 LEFT JOIN LATERAL (SELECT * FROM "CommonModel" c WHERE c.company=l.company AND c."modelId"=59 AND c."itemId"=l.id ORDER BY c."objectId" LIMIT 1) c ON true
 LEFT JOIN LATERAL (SELECT * FROM "_User" u WHERE u.company=l.company AND u."legacyUserId"::text=l.xymz ORDER BY u."objectId" LIMIT 1) s ON true
 LEFT JOIN LATERAL (SELECT * FROM "_User" u WHERE u.company=l.company AND u."legacyUserId"::text=l.jsmz ORDER BY u."objectId" LIMIT 1) p ON true
 LEFT JOIN LATERAL (SELECT * FROM "_User" u WHERE u.company=l.company AND u."legacyUserId"::text=COALESCE(NULLIF(l.szmdid,''),NULLIF(l."storeId"::text,'0'),s."legacyUserData"->>'ParentUserID') ORDER BY u."objectId" LIMIT 1) st ON true
 LEFT JOIN LATERAL (SELECT "nodeName" FROM "Node" n WHERE n.company=l.company AND n."nodeId"::text=l.kcid LIMIT 1) n ON true
 LEFT JOIN LATERAL (SELECT "contentSummary","actualStartAt" FROM "LessonCompletionReport" r WHERE r.company=l.company AND (r."lessonId"=l."objectId" OR r."appointmentGeneralId"::text=l.yyds) ORDER BY r."submittedAt" DESC LIMIT 1) r ON true
 WHERE l.company=$1
 ), ranked AS (SELECT facts.*,row_number() OVER(PARTITION BY company,xymz,"classKey" ORDER BY "actualAt" DESC NULLS LAST,"objectId") rn,count(*) OVER(PARTITION BY company,xymz,"classKey") duplicates FROM facts), lessons AS (SELECT * FROM ranked WHERE rn=1)`;}
function lessonHistoryFilters(input,args){const f={...(input.filters||{}),...input},conditions=[];
 const add=(sql,value)=>{if(value!==undefined&&value!==null&&String(value).trim()){args.push(String(value).trim());conditions.push(sql.replaceAll('?',String(args.length)));}};
 add('xymz=$?::text',f.studentId);add('jsmz=$?::text',f.coachId);add('"storeIdResolved"=$?::text',f.storeId);add('kcid=$?::text',f.courseId);
 for(const [field,key] of [['"studentNameResolved"','studentName'],['"coachNameResolved"','coachName'],['"storeName"','storeName'],['"courseNameResolved"','courseName']])add('position(lower($?) in lower('+field+'))>0',f[key]);
 add('position(lower($?) in lower(concat_ws(\' \',xymz,"studentNameResolved")))>0',f.search);
 for(const key of ['dateFrom','dateTo'])if(f[key]&&!/^\d{4}-\d{2}-\d{2}$/.test(f[key]))fail(400,'日期格式应为 YYYY-MM-DD');
 add('"actualAt">=($?::date::timestamp AT TIME ZONE \'Asia/Shanghai\')',f.dateFrom);add('"actualAt"<(($?::date+1)::timestamp AT TIME ZONE \'Asia/Shanghai\')',f.dateTo);
 return conditions.length?' WHERE '+conditions.join(' AND '):'';}
function lessonHistoryRow(row){return {targetId:text(row.targetId||row.objectId),lessonId:row.objectId,generalId:number(row.generalId||row.id),appointmentId:text(row.yyds),studentId:number(row.xymz),studentName:row.studentNameResolved,coachId:number(row.jsmz),coachName:row.coachNameResolved,storeId:number(row.storeIdResolved),storeName:row.storeName,courseId:number(row.kcid),courseName:row.courseNameResolved,classTypeLabel:row.courseCategoryName||classType(row.kclx),lessonAt:dateIso(row.actualAt),score:number(row.pf),comment:text(row.pldp),coachComment:text(row.pldp),studentComment:text(row.pjnr),feedbackSummary:text(row.feedbackSummary||row.pldp),duplicateCount:number(row.duplicates),pendingReviews:number(row.pendingReviews),reviewCoaches:row.reviewCoaches||[],reviewCoachLabel:(row.reviewCoaches||[]).length>1?'多位陪练':row.reviewCoaches?.[0]?.name||'无待复习任务'};}
async function lessonHistoryPage(context,input){
 requireRole(context,['ops-manager','ops-staff','ops-auditor']);const co=companyIdOf(context,input);if(!co)fail(400,'缺少帐套');
 const args=[co],where=lessonHistoryFilters(input,args),cumulativeWhere=lessonHistoryFilters({studentId:input.studentId,studentName:input.studentName,search:input.search},args),paging=pageInput(input);args.push(paging.pageSize,(paging.page-1)*paging.pageSize);
 const q=lessonHistoryBase()+`,filtered AS MATERIALIZED (SELECT * FROM lessons${where}), page AS (SELECT * FROM filtered ORDER BY "actualAt" DESC NULLS LAST,"objectId" DESC LIMIT $${args.length-1} OFFSET $${args.length})
 SELECT md5(COALESCE((SELECT string_agg("objectId"||COALESCE("updatedAt"::text,''),',' ORDER BY "objectId") FROM filtered),'')) AS "historyVersion",(SELECT count(*)::int FROM filtered) total,(SELECT count(*)::int FROM lessons${cumulativeWhere}) "cumulativeTotal",
 COALESCE((SELECT jsonb_agg(to_jsonb(p)||jsonb_build_object('pendingReviews',rv.n,'reviewCoaches',rv.coaches) ORDER BY p."actualAt" DESC NULLS LAST,p."objectId" DESC) FROM page p
 LEFT JOIN LATERAL (SELECT COALESCE(sum(n),0) n,jsonb_agg(jsonb_build_object('id',coach,'name',name,'count',n) ORDER BY name) coaches FROM (
 SELECT m.plid::text coach,COALESCE(NULLIF(u."realName",''),NULLIF(u.nickname,''),u."legacyUserData"->>'HoneyName',u.username,'陪练 '||m.plid::text) name,count(*)::int n
 FROM xs_review_task_link link JOIN "MemoryPracticeRecord" m ON m.company=link.company AND m."objectId"=link."taskId"
 LEFT JOIN LATERAL(SELECT * FROM "_User" u WHERE u.company=m.company AND u."legacyUserId"::text=m.plid::text ORDER BY u."objectId" LIMIT 1) u ON true
 WHERE link.company=p.company AND link."studentId"=p.xymz AND link."appointmentId"=p.yyds AND COALESCE(m.fxzt,'0')<>'1' AND NOT COALESCE(m."rewardDuplicate",false) GROUP BY m.plid,u."realName",u.nickname,u."legacyUserData",u.username) grouped) rv ON true),'[]'::jsonb) items`;
 const result=await Psql.one(q,args);return {page:paging.page,pageSize:paging.pageSize,total:number(result.total),cumulativeTotal:number(result.cumulativeTotal),items:(result.items||[]).map(lessonHistoryRow),refreshedAt:new Date().toISOString(),version:LESSON_RECORDS_VERSION+':'+result.historyVersion};
}
async function lessonHistoryDetail(context,input){
 requireRole(context,['ops-manager','ops-staff','ops-auditor']);const co=companyIdOf(context,input),id=text(input.lessonId||input.targetId),appointment=text(input.appointmentId);
 if(!co||(!id&&!appointment))fail(400,'缺少课次编号');
 const row=await Psql.oneOrNone(lessonHistoryBase()+` SELECT * FROM ranked WHERE ($2<>'' AND ("targetId"=$2 OR "objectId"=$2 OR "generalId"::text=$2)) OR ($3<>'' AND yyds=$3) LIMIT 1`,[co,id,appointment]);
 if(!row)fail(404,'未找到实际上课记录');
 const [report,review,tasks,auditRows]=await Promise.all([
 Psql.oneOrNone('SELECT * FROM "LessonCompletionReport" WHERE company=$1 AND ("lessonId"=$2 OR "appointmentGeneralId"::text=$3) ORDER BY "submittedAt" DESC LIMIT 1',[co,row.objectId,row.yyds]),
 Psql.oneOrNone('SELECT score,comment,"submittedAt" FROM "LessonReview" WHERE company=$1 AND "appointmentGeneralId"::text=$2 LIMIT 1',[co,row.yyds]),
 Psql.query('SELECT m."objectId",m.plid AS "coachId",m.kywrq AS "plannedDate",m.fxzt AS status,m.wcsj AS "completedAt",COALESCE(NULLIF(u."realName",\'\'),NULLIF(u.nickname,\'\'),u."legacyUserData"->>\'HoneyName\',u.username,\'陪练 \'||m.plid::text) AS "coachName" FROM xs_review_task_link l JOIN "MemoryPracticeRecord" m ON m.company=l.company AND m."objectId"=l."taskId" LEFT JOIN LATERAL(SELECT * FROM "_User" u WHERE u.company=m.company AND u."legacyUserId"::text=m.plid::text ORDER BY u."objectId" LIMIT 1) u ON true WHERE l.company=$1 AND l."studentId"=$2 AND l."appointmentId"=$3 ORDER BY m.kywrq,m."objectId"',[co,row.xymz,row.yyds]),
 Psql.query('SELECT a.*,c.reason,c."actorId" FROM "ReviewTransferAudit" a JOIN "ReviewTransferCommand" c ON c."objectId"=a."commandId" WHERE a.company=$1 AND a."studentId"=$2 AND a."appointmentId"=$3 ORDER BY a."createdAt" DESC',[co,row.xymz,row.yyds])]);
 return {...lessonHistoryRow(row),report:report||null,coachComment:text(report?.coachComment||row.pldp),studentReview:review||{score:number(row.pf),comment:text(row.pjnr)},tasks,audit:auditRows,canTransfer:roleOf(context)==='ops-manager',hasReport:Boolean(report)};
}
async function lessonTransferPreview(context,input){
 requireRole(context,['ops-manager']);const co=companyIdOf(context,input),student=text(input.studentId),appointment=input.scope==='student'?'':text(input.appointmentId),coach=text(input.coachId);
 if(!co||!/^\d+$/.test(student)||!/^\d+$/.test(coach)||!['lesson','student'].includes(input.scope)||input.scope==='lesson'&&!appointment)fail(400,'请选择有效的学员、课次和目标陪练');
 await Psql.one('SELECT xs_review_manager($1,$2),xs_review_target($1,$3)',[co,context.current.id,coach]);
 const snap=await Psql.one('SELECT s.tasks,md5(s.tasks::text) version FROM (SELECT xs_review_transfer_snapshot($1,$2,$3,$4) tasks) s',[co,student,appointment,coach]);
 const ids=[...new Set(snap.tasks.map(t=>t.coachId))],coaches=ids.length?await Psql.query('SELECT "legacyUserId"::text id,COALESCE(NULLIF("realName",\'\'),NULLIF(nickname,\'\'),"legacyUserData"->>\'HoneyName\',username) name FROM "_User" WHERE company=$1 AND "legacyUserId"::text=ANY($2::text[])',[co,ids]):[];
 const tasks=snap.tasks.map(t=>({...t,coachName:coaches.find(c=>c.id===t.coachId)?.name||'陪练 '+t.coachId}));
 return {version:snap.version,count:tasks.length,lessonCount:new Set(tasks.map(t=>t.appointmentId).filter(Boolean)).size,unlinkedCount:tasks.filter(t=>!t.appointmentId).length,dateFrom:tasks.map(t=>t.plannedDate).sort()[0]||'',dateTo:tasks.map(t=>t.plannedDate).sort().at(-1)||'',tasks};
}
async function lessonTransferCommit(context,input){
 requireRole(context,['ops-manager']);if(!['student','lesson'].includes(input.scope)||input.scope==='lesson'&&!text(input.appointmentId))fail(400,'缺少转交范围');
 try{return (await Psql.one('SELECT xs_review_transfer($1,$2,$3,$4,$5,$6,$7,$8) result',[companyIdOf(context,input),context.current.id,text(input.studentId),input.scope==='student'?'':text(input.appointmentId),text(input.coachId),text(input.version),text(input.reason),text(input.idempotencyKey)])).result;}catch(error){fail(409,text(error.message)||'转交失败，请刷新预览');}
}

async function lessonTransferCoaches(context,input){requireRole(context,['ops-manager']);const items=await Psql.query(`SELECT "legacyUserId" AS "userId",COALESCE(NULLIF("realName",''),NULLIF(nickname,''),"legacyUserData"->>'HoneyName',username) AS "displayName" FROM "_User" u WHERE company=$1 AND COALESCE(to_jsonb(u)->>'identityType','coach')='coach' AND COALESCE(to_jsonb(u)->>'legacyGroupId',to_jsonb(u)->>'groupId',u."legacyUserData"->>'GroupID')='3' AND NOT COALESCE("isDisabled",false) AND NOT COALESCE("isDeleted",false) ORDER BY "displayName","userId"`,[companyIdOf(context,input)]);return{items};}