xiaoshu-app/scripts/tests/lesson-study-batches.test.mjs

import {test,before,after} from 'node:test';
import assert from 'node:assert/strict';
import {readFileSync} from 'node:fs';
import {PGlite} from '@electric-sql/pglite';
const db=new PGlite();
const enrollmentSql=readFileSync(new URL('../sql/member-enrollment.sql',import.meta.url),'utf8');
const workflowSql=readFileSync(new URL('../sql/lesson-workflow.sql',import.meta.url),'utf8');
const one=async(sql,args=[])=>(await db.query(sql,args)).rows[0];
const feedback={contentSummary:'学习 Unit 1',reviewStatus:'完成',reviewSummary:'复习两个单词',trainingStatus:'正常进行',trainingSummary:'完成课堂练习',noHomework:true,homework:'',notes:'',coachComment:'继续保持'};
const complete=async(gid,version,key='request-complete-001',actor='teacher',uid=30,f=feedback)=>(await one('SELECT xs_lesson_complete($1,$2,$3,$4,$5,$6,$7::jsonb) result',['co',gid,actor,uid,version,key,JSON.stringify(f)])).result;
const review=async(gid,rating=5,comment='很好',actor='student',uid=20)=>(await one('SELECT xs_lesson_review($1,$2,$3,$4,$5,$6) result',['co',gid,actor,uid,rating,comment])).result;
let binding;
before(async()=>{

 await db.exec(`CREATE TABLE "_User" ("objectId" text PRIMARY KEY,"company" text,"legacyUserId" double precision,"legacyGroupId" double precision,"identityType" text,"isDisabled" boolean,"legacyUserData" jsonb,"updatedAt" timestamptz);
 CREATE TABLE "CourseBinding" ("objectId" text PRIMARY KEY,"company" text,"id" double precision,"createdAt" timestamptz,"updatedAt" timestamptz,"sourceKey" text,"yhid" text,"kcid" text,"yxx" double precision,"cksl" double precision,"syjd" text);
 CREATE TABLE "CommonModel" ("objectId" text PRIMARY KEY,"company" text,"createdAt" timestamptz,"updatedAt" timestamptz,"createTime" timestamptz,"sourceKey" text,"generalId" double precision,"itemId" double precision,"modelId" double precision,"nodeId" double precision,"tableName" text,"title" text,"subtitle" text,"status" double precision,"inputer" text);
 CREATE TABLE "CourseAppointment" ("objectId" text PRIMARY KEY,"company" text,"id" double precision,"szyh" text,"kcid" text,"pl" text,"fxpl" text,"dszt" text,"dslx" text,"jffs" text,"jssj" text,"updatedAt" timestamptz,"creditCostsSnapshot" jsonb,"creditHoldStatus" text,"courseCategoryKey" text,"courseCategoryName" text,"teacherPaySnapshot" double precision);
 CREATE TABLE "LessonRecord" ("objectId" text PRIMARY KEY,"company" text,"id" double precision,"createdAt" timestamptz,"updatedAt" timestamptz,"sourceKey" text,"xymz" text,"jsmz" text,"kcid" text,"kclx" text,"kcmc" text,"yyds" text,"jffs" text,"kzsj" text,"lessonAt" timestamptz);
 CREATE TABLE "DailyStudyRecord" ("objectId" text);
 CREATE TABLE "CourseTeachingProfile" ("company" text,"courseId" double precision,"categoryKey" text,"confirmed" boolean);
 CREATE TABLE "CourseCreditReservation" ("objectId" text PRIMARY KEY,"company" text,"studentId" double precision,"appointmentId" text,"creditCosts" jsonb,"state" text,"consumedAt" timestamptz,"updatedAt" timestamptz);`);
 for(const table of ['UserExpDomP','UserSIcon','UserExpHis','UserUserPoint'])await db.exec(`CREATE TABLE "${table}" ("objectId" text PRIMARY KEY,"company" text,"sourceKey" text UNIQUE,"createdAt" timestamptz,"updatedAt" timestamptz,"userId" double precision,"score" double precision,"scoreBefore" double precision,"hisTime" timestamptz,"operator" double precision,"scoreType" double precision,"detail" text,"remark" text,"payMethod" text)`);
 await db.exec(`INSERT INTO "_User" VALUES ('store','co',10,2,'store',false,'{}',now()),('student','co',20,1,'member',false,'{"ParentUserID":10}',now()),('empty','co',21,1,'member',false,'{}',now()),('foreign','other',22,1,'member',false,'{}',now());`);
 await db.exec(`ALTER TABLE "CourseAppointment" ADD COLUMN "creditHoldReason" text,ADD COLUMN "kssj" text,ADD COLUMN "plxm" text,ADD COLUMN "yykcid" text,ADD COLUMN "durationMinutes" numeric,ADD COLUMN "deliveryMode" text,ADD COLUMN "teachingRuleId" text,ADD COLUMN "bxrq" text,ADD COLUMN "sdsd" text,ADD COLUMN "yysj" text,ADD COLUMN "sourceKey" text,ADD COLUMN "createdAt" timestamptz;
 ALTER TABLE "CourseCreditReservation" ADD COLUMN "sourceKey" text,ADD COLUMN "createdAt" timestamptz,ADD COLUMN "appointmentGeneralId" numeric,ADD COLUMN "ruleId" text,ADD COLUMN "reservedAt" timestamptz;`);
 await db.exec(`CREATE TABLE "Node" ("company" text,"nodeId" numeric,"parentId" numeric,"nodeName" text,"zstatus" numeric);
 CREATE TABLE "SysLog" ("objectId" text PRIMARY KEY,"company" text,"createdAt" timestamptz,"updatedAt" timestamptz,"sourceKey" text NOT NULL UNIQUE,"id" double precision,"cdate" timestamptz,"cname" text,"type1" text,"type2" text,"type3" text,"clevel" double precision,"cadminId" double precision,"detail" text,"content1" text,"content2" text);
 INSERT INTO "Node" VALUES ('co',100,8,'词表',99),('co',101,8,'空词表',99);
 INSERT INTO "CommonModel" ("objectId","company","nodeId","modelId","status") SELECT 'word-'||i,'co',100,52,99 FROM generate_series(1,200) i;`);
 await db.exec(enrollmentSql);
 await db.exec(`ALTER TABLE "LessonRecord" ADD pf numeric,ADD pjnr text,ADD pldp text,ADD plpjsj text;
 ALTER TABLE "DailyStudyRecord" ADD company text,ADD id numeric,ADD "userId" numeric,ADD dsid text,ADD xxqs text,ADD fxrl text,ADD "updatedAt" timestamptz;
 CREATE TABLE "MemoryPracticeRecord" ("objectId" text primary key,company text,id numeric,"sourceKey" text,"createdAt" timestamptz,"updatedAt" timestamptz,yhid numeric,plid numeric,"orderId" numeric,xxjlid numeric,kcid numeric,fxzt text,kywrq text,kywsj text);
 CREATE TABLE "LessonCompletionReport" ("objectId" text primary key,company text,"createdAt" timestamptz,"updatedAt" timestamptz,"sourceKey" text,"appointmentId" text,"appointmentGeneralId" numeric,"lessonId" text,"studentId" numeric,"coachId" numeric,"courseCategoryKey" text,"courseCategoryName" text,"deliveryMode" text,"durationMinutes" numeric,"actualStartAt" timestamptz,"actualEndAt" timestamptz,"creditCosts" jsonb,"contentSummary" text,"vocabularyCount" numeric,"reviewStatus" text,"reviewSummary" text,"trainingStatus" text,"trainingSummary" text,"antiForgetSummary" jsonb,homework text,"noHomework" boolean,notes text,"submittedBy" text,"submittedAt" timestamptz);
 INSERT INTO "_User" VALUES('teacher','co',30,3,'coach',false,'{}',now()),('other-teacher','co',31,3,'coach',false,'{}',now());`);
 await db.exec(workflowSql);
 await db.exec(`ALTER TABLE "DailyStudyRecord" ADD "createdAt" timestamptz,ADD "sourceKey" text,ADD COLUMN IF NOT EXISTS "bindingId" text,ADD pl text,ADD learned numeric,ADD ygg numeric,ADD djq numeric,ADD dqrq text,ADD con text;
 ALTER TABLE "CommonModel" ADD hits numeric;
 UPDATE "CommonModel" SET "generalId"=replace("objectId",'word-','')::numeric,title='word'||replace("objectId",'word-','') WHERE "modelId"=52;`);
 await db.exec(`INSERT INTO "Node" VALUES ('co',102,100,'七年级',99),('co',103,100,'八年级',99),('co',104,100,'九年级',99);
 UPDATE "CommonModel" SET "nodeId"=CASE WHEN "generalId"<=9 THEN 102 WHEN "generalId"<=14 THEN 103 ELSE 104 END WHERE "modelId"=52 AND "generalId" BETWEEN 1 AND 17;`);
 await db.exec(readFileSync(new URL('../sql/lesson-study-batches.sql',import.meta.url),'utf8'));
 await db.query("SELECT xs_credit_post('co','admin','fund','store','Purse',30,'','初始课时','转账','fund-workflow')");
 await db.query("SELECT xs_credit_post('co','admin','transfer','student','Purse',30,'','学员课时','转账','transfer-workflow')");
 binding=(await one(`SELECT xs_enroll('co','admin',20,'word','[{"courseId":100,"courseName":"词表","wordCount":200,"categoryName":"单词课"}]','[[{"account":"Purse","amount":1}]]','测试开课','enroll-workflow') result`)).result.bindingIds[0];
});
after(()=>db.close());
let serial=0;
async function appointment(start=true){
 const date=(await one("SELECT to_char(now() AT TIME ZONE 'Asia/Shanghai','YYYY-MM-DD') AS day")).day;
 const time=String(5+(serial++%16)).padStart(2,'0')+':00';
 const entries=[{common:{title:'学员',subtitle:'词表'},addon:{szyh:'20',pl:'30',fxpl:'30',plxm:'老师',kcid:'100',yykcid:'100',dslx:'1',jffs:'线上',bindingId:binding,courseCategoryKey:'word',durationMinutes:30,deliveryMode:'online',creditCostsSnapshot:[{account:'Purse',amount:1}],teacherPaySnapshot:20,bxrq:date,sdsd:time,yysj:date+' '+time,dszt:'0'}}];
 const result=(await one("SELECT xs_schedule_save('co','admin','{}',$1::jsonb,$2) result",[JSON.stringify(entries),'schedule-'+serial])).result;
 const common=result.targetIds[0];
 if(start) await db.query("SELECT xs_start_appointment('co',$1)",[common]);
 const row=await one('SELECT c."generalId"::text gid,a."objectId",a."updatedAt" FROM "CommonModel" c JOIN "CourseAppointment" a ON a.company=c.company AND a.id=c."itemId" WHERE c."objectId"=$1',[common]);
 return {...row,common};
}
const balance=async()=>Number((await one(`SELECT "legacyUserData"->>'Purse' balance FROM "_User" WHERE "objectId"='student'`)).balance);

const save=async(a,batchId,words,mode='confirm',version=0,key='save-'+crypto.randomUUID(),bind=binding)=>(await one('SELECT xs_lesson_study_save($1,$2,$3,$4,$5,$6::jsonb) result',['co',a.gid,'teacher',30,key,JSON.stringify({batchId,bindingId:bind,mode,expectedVersion:version,words})])).result;
const words=(start,count,check=1)=>Array.from({length:count},(_,i)=>({GeneralID:start+i,Title:'word'+(start+i),check}));
const studies=async a=>(await db.query('SELECT * FROM "DailyStudyRecord" WHERE company=\'co\' AND dsid=$1 AND "recordScope"=\'lesson\'',[a.gid])).rows;
test('same enrollment three rounds retain 9+5+3 words and produce one record with ten complete reviews',async()=>{
 const a=await appointment();
 assert.equal((await save(a,'round1',words(1,9))).learned,9);
 assert.equal((await save(a,'round2',words(10,5))).learned,14);
 const r=await save(a,'round3',words(15,3));assert.equal(r.learned,17);
 const ds=await studies(a);assert.equal(ds.length,1);assert.equal(JSON.parse(ds[0].xxqs).length,17);
 const before=await balance();await complete(a.gid,a.updatedAt,'complete-multi-'+serial);
 assert.equal(await balance(),before-1);
 const reviews=(await db.query('SELECT * FROM "MemoryPracticeRecord" WHERE "orderId"=$1',[a.gid])).rows;
 assert.equal(reviews.length,10);assert(reviews.every(v=>String(v.xxjlid)===String(r.GeneralID)));
 assert.equal((await one('SELECT "vocabularyCount" FROM "LessonCompletionReport" WHERE "appointmentGeneralId"=$1',[a.gid])).vocabularyCount,'17');
 const updated=await studies(a);assert.equal(updated[0].fxrl.split(',').length,10);
 await complete(a.gid,a.updatedAt,'complete-multi-'+serial);assert.equal(await balance(),before-1);
});
test('draft does not count, blocks completion, and can be confirmed without losing earlier words',async()=>{
 const a=await appointment();await save(a,'first',words(1,9));const r=await save(a,'pending',words(10,5,0),'draft');assert.equal(r.learned,9);
 await assert.rejects(complete(a.gid,a.updatedAt,'pending-complete'),/未确认/);
 await assert.rejects(save(a,'pending',words(10,5,0),'confirm',1),/掌握情况/);
 assert.equal((await save(a,'pending',words(10,5),'confirm',1)).learned,14);
});
test('duplicate words deduplicate and stale versions cannot overwrite mastery; retry is idempotent',async()=>{
 const a=await appointment();const key='retry-batch-key';const first=await save(a,'first',words(1,9),'confirm',0,key);assert.deepEqual(await save(a,'first',words(1,9),'confirm',0,key),first);
 await save(a,'second',words(5,5,2));assert.equal((await studies(a))[0].learned,'9');
 await save(a,'first',words(1,9,2),'confirm',1);
 await assert.rejects(save(a,'first',words(1,9),'confirm',1),/已更新/);
 await assert.rejects(save(a,'first',words(1,9),'draft',2),/不能退回/);
});
test('legacy snapshots merge rather than replace and cannot reset known mastery',async()=>{
 const a=await appointment();await save(a,'legacy',words(1,9),'legacy');assert.equal((await save(a,'legacy',words(10,5),'legacy')).learned,14);
 await assert.rejects(save(a,'legacy',words(1,9,2),'legacy'),/新版页面/);
});
test('discarding an unconfirmed round removes the completion blocker without counting words',async()=>{
 const a=await appointment();await save(a,'discard',words(1,5,0),'draft');await save(a,'discard',[],'discard',1);
 assert.equal((await studies(a))[0].learned,'0');await complete(a.gid,a.updatedAt,'discard-complete');
});
test('other teachers, unrelated course words and writes after completion are rejected',async()=>{
 const a=await appointment();await assert.rejects(one('SELECT xs_lesson_study_save($1,$2,$3,$4,$5,$6::jsonb) result',['co',a.gid,'other-teacher',31,'foreign-request',JSON.stringify({batchId:'bad',bindingId:binding,mode:'confirm',words:words(1,1)})]),/仅预约老师/);
 await assert.rejects(save(a,'bad',words(9999,1)),/不匹配/);
 await save(a,'ok',words(1,1));await complete(a.gid,a.updatedAt,'closed-save');await assert.rejects(save(a,'late',words(2,1)),/进行中/);
});
test('confirmed historical recovery restores exactly 17 words, unknown mastery, ten reviews and is financially idempotent',async()=>{
 const a=await appointment();
 await db.exec(`INSERT INTO "Node" VALUES('co',172,100,'七年级',99),('co',173,100,'八年级',99),('co',174,100,'九年级',99);UPDATE "CommonModel" SET "nodeId"=CASE WHEN "generalId"<=9 THEN 172 WHEN "generalId"<=14 THEN 173 ELSE 174 END WHERE "modelId"=52 AND "generalId" BETWEEN 1 AND 17;`);
 await save(a,'old-last',words(15,3));await complete(a.gid,a.updatedAt,'recovery-complete');
 await db.query('DELETE FROM "CommonModel" WHERE "modelId"=60 AND "itemId" IN (SELECT id FROM "MemoryPracticeRecord" WHERE "orderId"=$1)',[a.gid]);await db.query('DELETE FROM "MemoryPracticeRecord" WHERE "orderId"=$1',[a.gid]);
 const before=await balance(),ledgerBefore=(await db.query('SELECT * FROM "UserExpDomP" ORDER BY "objectId"')).rows;
 let sql=readFileSync(new URL('../sql/repair-confirmed-lesson-839154386019743.sql',import.meta.url),'utf8').replaceAll('7pIbDBJmKx','co').replaceAll('839154386019743',a.gid).replaceAll("'69'","'30'").replaceAll("'110'","'20'").replaceAll(',110,69,',',20,30,').replaceAll("'yhid',110","'yhid',20").replaceAll('nq2u6S3Lc5',binding).replace('ARRAY[25163,25164,25165,25166,25167,25168,25169,25170,25171,26120,26121,26122,26123,26124,27143,27144,27145]','ARRAY[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]').replaceAll('ARRAY[27143,27144,27145]','ARRAY[15,16,17]');
 await db.transaction(tx=>tx.exec(sql));const snapshot=await studies(a);assert.equal(snapshot[0].learned,'17');const restored=JSON.parse(snapshot[0].xxqs);assert.equal(restored.filter(w=>w.check===0).length,14);assert.equal(restored.filter(w=>w.check===1).length,3);
 await db.transaction(tx=>tx.exec(sql));assert.deepEqual(await studies(a),snapshot);assert.equal(await balance(),before);assert.deepEqual((await db.query('SELECT * FROM "UserExpDomP" ORDER BY "objectId"')).rows,ledgerBefore);
 assert.equal((await one('SELECT count(*)::int n FROM "MemoryPracticeRecord" WHERE "orderId"=$1',[a.gid])).n,10);
 await save(a,'recovery:172',words(1,9),'mastery',1);assert.equal((await studies(a))[0].ygg,'12');assert.equal(await balance(),before);
 await assert.rejects(save(a,'recovery:173',words(10,4),'mastery',1),/不能更改/);
});

test('three bindings accumulate and stale confirmation cannot replace a newer round mastery',async()=>{
 const a=await appointment();
 const extra=[];
 for(const [course,start,count] of [[105,201,5],[106,206,3]]){
  await db.query('INSERT INTO "Node" VALUES ($1,$2,8,$3,99)',['co',course,'词本'+course]);
  for(let id=start;id<start+count;id++)await db.query(`INSERT INTO "CommonModel" ("objectId",company,"nodeId","modelId",status,"generalId",title) VALUES($1,'co',$2,52,99,$3,$4)`,['word-'+id,course,id,'word'+id]);
  const r=(await one(`SELECT xs_enroll('co','admin',20,'word',$1::jsonb,'[[{"account":"Purse","amount":1}]]','测试开课',$2) result`,[JSON.stringify([{courseId:course,courseName:'词本'+course,wordCount:count,categoryName:'单词课'}]),'enroll-'+course])).result;
  extra.push(r.bindingIds[0]);
 }
 await save(a,'book1',words(1,9));await save(a,'book2',words(201,5),'confirm',0,undefined,extra[0]);
 assert.equal((await save(a,'book3',words(206,3),'confirm',0,undefined,extra[1])).learned,17);
 const draft=await save(a,'old-draft',words(1,1,0),'draft');
 await save(a,'newer',words(1,1,2));
 await assert.rejects(()=>save(a,'old-draft',words(1,1,1),'confirm',draft.batchVersion),/其他轮次已更新/);
 assert.equal(JSON.parse((await studies(a))[0].xxqs).find(w=>w.GeneralID===1).check,2);
});