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 lessonHistoryLabelColumns(){return ` 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"`;}
function lessonHistoryLabelJoins(){return ` 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
`;}
function lessonHistoryBase(lean=false,studentParameter=0){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",
${lean?'':lessonHistoryLabelColumns()+','}
 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
${lean?'':lessonHistoryLabelJoins()}
 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 ${studentParameter?`AND l.xymz=$${studentParameter}`:``} AND c.status IS DISTINCT FROM -2
 ), 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)));}};
 if(f._storeId)add('EXISTS(SELECT 1 FROM \"_User\" s WHERE s.company=lessons.company AND s.\"legacyUserId\"::text=lessons.xymz AND s.\"identityType\"=\'member\' AND s.\"legacyUserData\"->>\'ParentUserID\'=$? AND NOT COALESCE(s.\"isDeleted\",false))',f._storeId);
 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 '):'';}
// Resolve only the displayed page; preserve the original completed-lesson snapshot when present.
async function lessonHistoryTypeRows(rows,db=Psql) {
 const wanted=rows.map(row=>({company:row.company,appointmentId:text(row.yyds),studentId:text(row.xymz)})).filter(row=>row.company&&/^\d+$/.test(row.appointmentId)&&row.studentId);
 if(!wanted.length)return rows;
 const saved=await db.query(`WITH wanted AS (SELECT DISTINCT * FROM jsonb_to_recordset($1::jsonb) AS w(company text,"appointmentId" numeric,"studentId" text))
 SELECT w.company,w."appointmentId"::text AS "appointmentId",w."studentId",
 r."courseCategoryKey" AS "reportKey",r."courseCategoryName" AS "reportName",r."durationMinutes" AS "reportMinutes",
 a.kcid AS "appointmentCourseId",a."courseCategoryKey" AS "appointmentKey",a."courseCategoryName" AS "appointmentName",a."durationMinutes" AS "appointmentMinutes"
 FROM wanted w
 LEFT JOIN LATERAL (SELECT a.* FROM "CommonModel" c JOIN "CourseAppointment" a ON a.company=c.company AND a.id=c."itemId" AND a.szyh::text=w."studentId"
 WHERE c.company=w.company AND c."modelId"=54 AND c."generalId"=w."appointmentId" ORDER BY c."objectId" LIMIT 1) a ON true
 LEFT JOIN LATERAL (SELECT r.* FROM "LessonCompletionReport" r WHERE r.company=w.company AND r."appointmentGeneralId"=w."appointmentId" AND r."studentId"::text=w."studentId"
 ORDER BY r."submittedAt" DESC NULLS LAST,r."objectId" DESC LIMIT 1) r ON true`,[JSON.stringify(wanted)]);
 const key=row=>[row.company,row.appointmentId,row.studentId].join(':'),byAppointment=new Map(saved.map(row=>[key(row),row]));
 return rows.map(row=>{
  const source=byAppointment.get(key({company:row.company,appointmentId:text(row.yyds),studentId:text(row.xymz)}))||{};
  const own=text(row.courseCategoryKey)||text(row.courseCategoryName),report=text(source.reportKey)||text(source.reportName);
  return {...row,resolvedCourseId:text(row.kcid)||text(source.appointmentCourseId),courseCategoryKey:own?row.courseCategoryKey:report?source.reportKey:source.appointmentKey,
   courseCategoryName:own?row.courseCategoryName:report?source.reportName:source.appointmentName,
   durationMinutes:[source.reportMinutes,source.appointmentMinutes,row.durationMinutes].map(value=>number(value)).find(value=>value>0)};
 });
}
function lessonHistoryClassType(row) {
 const categoryKey=text(row.courseCategoryKey),categoryName=text(row.courseCategoryName),type=text(row.kclx),courseId=number(row.kcid)||number(row.resolvedCourseId);
 const minutes=number(row.durationMinutes)||({'1':30,'2':60,'3':60})[type];
 const isWord=categoryKey==='word'||categoryName==='单词课'||(!categoryKey&&!categoryName&&courseId>0&&courseId!==398&&['1','2'].includes(type));
 const name=categoryName||TEACHING_CATEGORY_LABELS.get(categoryKey)||(type==='3'?'体验课':isWord?'单词课':courseId===398?'语法写作课':'');
 return name?name+(minutes?' '+minutes+' 分钟':''):classType(row.kclx);
}
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:lessonHistoryClassType(row),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||'无待复习任务'};}
// Locks live for the actual database transaction, so cancellation/death releases capacity.
async function lessonHistoryRead(work){
 return Psql.tx(async db=>{
  await db.none("SET LOCAL statement_timeout='12s'");
  let admitted=false;
  for(const slot of [1,2]){const row=await db.one('SELECT pg_try_advisory_xact_lock(1736200,$1) acquired',[slot]);if(row.acquired){admitted=true;break;}}
  if(!admitted)fail(429,'查询较多，请稍后重试');
  return work(db);
 });
}
async function lessonHistoryPage(context,input){
 return lessonHistoryRead(db=>lessonHistoryPageData(context,input,db));
}
async function lessonHistoryPageData(context,input,db=Psql){
 requireRole(context,['ops-manager','ops-staff','ops-auditor']);const co=companyIdOf(context,input);if(!co)fail(400,'缺少帐套');
 const installed=await db.one("SELECT to_regprocedure('xs_read_version(text,text)') IS NOT NULL installed");
 const revision=installed.installed?(await db.one("SELECT xs_read_version($1,'lessons') revision",[co])).revision:null;
 const {page,pageSize,refresh,cursor,...filters}=input;
 const key=JSON.stringify([context.storeId||0,roleOf(context),filters]),actor=text(context.current.id);
 const cached=revision&&!refresh?(await db.query('SELECT value FROM xs_history_summary WHERE company=$1 AND actor=$2 AND key=md5($3) AND revision=$4 AND expires_at>now()',[co,actor,key,revision]))[0]?.value:null;
 const plan=lessonHistoryPlan(context,input,cached,revision),result=await db.one(plan.q,plan.args);
 const version=LESSON_RECORDS_VERSION+':'+result.historyVersion;
 if(input.cursor&&input.cursor.version!==version)fail(409,'记录发生变化，请重新查询');
 const last=(result.items||[]).at(-1),nextCursor=last?{at:last.actualAt||null,id:last.objectId,version}:null;
 if(revision&&!cached)await db.none("INSERT INTO xs_history_summary(company,actor,key,revision,value,expires_at) VALUES($1,$2,md5($3),$4,$5::jsonb,now()+interval '30 seconds') ON CONFLICT(company,actor,key) DO UPDATE SET revision=excluded.revision,value=excluded.value,expires_at=excluded.expires_at",[co,actor,key,revision,JSON.stringify({total:result.total,cumulativeTotal:result.cumulativeTotal})]);
 return {page:plan.paging.page,pageSize:plan.paging.pageSize,total:number(result.total),cumulativeTotal:number(result.cumulativeTotal),items:(await lessonHistoryTypeRows(result.items||[],db)).map(lessonHistoryRow),refreshedAt:new Date().toISOString(),version,nextCursor};
}
function lessonHistoryPlan(context,input,summary=null,revision=null,snapshot=false){
 const co=companyIdOf(context,input);
 const args=[co];if(context.storeId)input={...input,storeId:context.storeId,_storeId:context.storeId};const where=lessonHistoryFilters(input,args),cumulativeWhere=lessonHistoryFilters({studentId:input.studentId,studentName:input.studentName,search:input.search,_storeId:input._storeId},args),paging=pageInput(input);const studentParameter=text(input.studentId)?args.push(text(input.studentId)):0;const revisionParameter=revision!==null?args.push(revision):0;
 let cursorWhere='';
 if(input.cursor&&!snapshot){const cursor=input.cursor;
  if(typeof cursor!=='object'||!text(cursor.id)||text(cursor.id).length>200||(cursor.at!==null&&(typeof cursor.at!=='string'||cursor.at.length>80||!Number.isFinite(Date.parse(cursor.at)))))fail(400,'分页游标无效');
  const at=args.push(cursor.at),id=args.push(text(cursor.id));
  cursorWhere=` WHERE (($${at}::timestamptz IS NOT NULL AND ("actualAt"<$${at}::timestamptz OR "actualAt" IS NULL OR ("actualAt"=$${at}::timestamptz AND "objectId"<$${id}::text))) OR ($${at}::timestamptz IS NULL AND "actualAt" IS NULL AND "objectId"<$${id}::text))`;
 }
 args.push(snapshot?2147483647:paging.pageSize,snapshot||input.cursor?0:(paging.page-1)*paging.pageSize);
 const filters={...(input.filters||{}),...input},lean=!context.storeId&&!['search','studentName','coachName','storeName','courseName','storeId'].some(key=>text(filters[key]));
 const q=lessonHistoryBase(lean,studentParameter)+`,filtered AS MATERIALIZED (SELECT * FROM lessons${where}), page AS (SELECT * FROM filtered${cursorWhere} ORDER BY "actualAt" DESC NULLS LAST,"objectId" DESC LIMIT $${args.length-1} OFFSET $${args.length})${lean?`,display_page AS (SELECT l.*,${lessonHistoryLabelColumns()} FROM page l ${lessonHistoryLabelJoins()})`:``}${snapshot?`,review_pending AS MATERIALIZED (
 SELECT m.* FROM "MemoryPracticeRecord" m WHERE m.company=$1 AND COALESCE(m.fxzt,'0')<>'1' AND NOT COALESCE(m."rewardDuplicate",false)
 AND EXISTS(SELECT 1 FROM page p WHERE p.xymz=m.yhid::text)
),review_counts AS MATERIALIZED (
 SELECT link."studentId",link."appointmentId",m.plid::text coach,count(*)::int n FROM review_pending m
 JOIN xs_review_task_link link ON link.company=m.company AND link."taskId"=m."objectId"
 WHERE link."appointmentId" IS NOT NULL GROUP BY link."studentId",link."appointmentId",m.plid
),review_names AS MATERIALIZED (
 SELECT c.coach,COALESCE(NULLIF(u."realName",''),NULLIF(u.nickname,''),u."legacyUserData"->>'HoneyName',u.username,'陪练 '||c.coach) name
 FROM (SELECT DISTINCT coach FROM review_counts) c LEFT JOIN LATERAL(SELECT * FROM "_User" u WHERE u.company=$1 AND u."legacyUserId"::text=c.coach ORDER BY u."objectId" LIMIT 1) u ON true
),review_lookup AS (
 SELECT c."studentId",c."appointmentId",sum(c.n) n,jsonb_agg(jsonb_build_object('id',c.coach,'name',u.name,'count',c.n) ORDER BY u.name) coaches
 FROM review_counts c JOIN review_names u ON u.coach=c.coach GROUP BY c."studentId",c."appointmentId"
)`:``}
 SELECT ${revisionParameter?`md5($${revisionParameter}::text)`:`md5(COALESCE((SELECT string_agg("objectId"||COALESCE("updatedAt"::text,''),',' ORDER BY "objectId") FROM filtered),''))`} AS "historyVersion",${summary?number(summary.total):`(SELECT count(*)::int FROM filtered)`} total,${summary?number(summary.cumulativeTotal):`(SELECT count(*)::int FROM lessons${cumulativeWhere})`} "cumulativeTotal",
 COALESCE((SELECT jsonb_agg(to_jsonb(p)||jsonb_build_object('pendingReviews',COALESCE(rv.n,0),'reviewCoaches',rv.coaches) ORDER BY p."actualAt" DESC NULLS LAST,p."objectId" DESC) FROM ${lean?'display_page':'page'} p
 ${snapshot?` LEFT JOIN review_lookup rv ON rv."studentId"=p.xymz AND rv."appointmentId"=p.yyds`:` 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`;
 // Cached totals remove placeholders used only by the cumulative count. Compact them.
 const used=[...new Set([...q.matchAll(/\$(\d+)/g)].map(match=>Number(match[1])))].sort((a,b)=>a-b);
 return {q:q.replace(/\$(\d+)/g,(_,n)=>'$'+(used.indexOf(Number(n))+1)),args:used.map(n=>args[n-1]),paging};
}
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,'未找到实际上课记录');
 if(context.storeId)await storeAssertMember(context,input,row.xymz);
 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((await lessonHistoryTypeRows([row]))[0]),report:report||null,coachComment:text(report?.coachComment||row.pldp),studentReview:review||{score:number(row.pf),comment:text(row.pjnr)},tasks,audit:auditRows,canTransfer:!context.storeId&&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};}