xiaoshu-app/scripts/sql/lesson-study-batches.sql

-- Install after lesson-workflow.sql. Additive: no historical lessons are rewritten.
ALTER TABLE "DailyStudyRecord" ADD COLUMN IF NOT EXISTS "recordScope" text;
ALTER TABLE "DailyStudyRecord" ADD COLUMN IF NOT EXISTS "studyVersion" integer DEFAULT 0;
ALTER TABLE "DailyStudyRecord" ADD COLUMN IF NOT EXISTS "canonicalStudyId" text;
CREATE UNIQUE INDEX IF NOT EXISTS xs_lesson_study_unique ON "DailyStudyRecord"(company,dsid,"userId") WHERE "recordScope"='lesson';
CREATE TABLE IF NOT EXISTS "LessonStudyBatch" (
 company text NOT NULL, "orderId" text NOT NULL, "batchId" text NOT NULL,
 "studentId" numeric NOT NULL, "coachId" numeric NOT NULL, "bindingId" text,
 words jsonb NOT NULL DEFAULT '[]', status text NOT NULL CHECK(status IN ('draft','confirmed','discarded')),
 version integer NOT NULL DEFAULT 1, "updatedAt" timestamptz NOT NULL DEFAULT now(),
 provenance text, PRIMARY KEY(company,"orderId","batchId")
);
CREATE TABLE IF NOT EXISTS "LessonStudyCommand" (
 company text NOT NULL, actor text NOT NULL, "requestKey" text NOT NULL,
 fingerprint jsonb NOT NULL, result jsonb NOT NULL, "createdAt" timestamptz DEFAULT now(),
 PRIMARY KEY(company,actor,"requestKey")
);
REVOKE ALL ON "LessonStudyBatch","LessonStudyCommand" FROM PUBLIC;

CREATE OR REPLACE FUNCTION xs_lesson_study_rebuild(co text,gid text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE a "CourseAppointment"; d "DailyStudyRecord"; old record; w jsonb; cid numeric; rid numeric; oid text;
BEGIN
 SELECT a0.* INTO a FROM "CourseAppointment" a0 JOIN "CommonModel" c ON c.company=a0.company AND c."modelId"=54 AND c."itemId"=a0.id WHERE c.company=co AND c."generalId"::text=gid;
 IF a."objectId" IS NULL THEN RAISE EXCEPTION '预约不存在'; END IF;
 -- Adopt old per-book records once, retaining their IDs as read aliases.
 FOR old IN SELECT d0.* FROM "DailyStudyRecord" d0 WHERE d0.company=co AND d0.dsid::text=gid AND d0."userId"::text=a.szyh::text AND d0."recordScope" IS NULL LOOP
  INSERT INTO "LessonStudyBatch"(company,"orderId","batchId","studentId","coachId","bindingId",words,status,provenance)
  VALUES(co,gid,'import:'||old."objectId",a.szyh::numeric,a.pl::numeric,old."bindingId",COALESCE(NULLIF(old.xxqs,'')::jsonb,'[]'),CASE WHEN EXISTS(SELECT 1 FROM jsonb_array_elements(COALESCE(NULLIF(old.xxqs,'')::jsonb,'[]')) v WHERE COALESCE(v->>'check','0') NOT IN ('1','2')) THEN 'draft' ELSE 'confirmed' END,'existing-record') ON CONFLICT DO NOTHING;
 END LOOP;
 SELECT * INTO d FROM "DailyStudyRecord" WHERE company=co AND dsid::text=gid AND "userId"::text=a.szyh::text AND "recordScope"='lesson';
 IF d."objectId" IS NULL THEN
  SELECT * INTO d FROM "DailyStudyRecord" WHERE company=co AND dsid::text=gid AND "userId"::text=a.szyh::text AND "recordScope" IS NULL ORDER BY "createdAt",id LIMIT 1;
  IF d."objectId" IS NULL THEN
   rid=nextval('xiaoshu_business_id');oid=xs_object_id();cid=nextval('xiaoshu_business_id');
   INSERT INTO "DailyStudyRecord" SELECT * FROM jsonb_populate_record(NULL::"DailyStudyRecord",jsonb_build_object('objectId',oid,'company',co,'id',rid,'createdAt',now(),'updatedAt',now(),'sourceKey','cloud:lesson-study:'||gid,'userId',a.szyh::numeric,'pl',a.pl,'dsid',gid,'recordScope','lesson','studyVersion',0,'dqrq',to_char(now() AT TIME ZONE 'Asia/Shanghai','YYYYMMDD')));
   PERFORM xs_insert('CommonModel',jsonb_build_object('objectId',xs_object_id(),'company',co,'generalId',cid,'itemId',rid,'modelId',56,'nodeId',291,'tableName','ZL_C_ss','title','课堂学习记录','status',99,'createdAt',now(),'updatedAt',now(),'sourceKey','cloud:lesson-study:'||gid,'teachingManaged',true));
   SELECT * INTO d FROM "DailyStudyRecord" WHERE "objectId"=oid;
  ELSE
   UPDATE "DailyStudyRecord" SET "recordScope"='lesson',"bindingId"=NULL WHERE "objectId"=d."objectId";
  END IF;
 END IF;
 SELECT "generalId" INTO cid FROM "CommonModel" WHERE company=co AND "modelId"=56 AND "itemId"=d.id LIMIT 1;
 UPDATE "DailyStudyRecord" SET "recordScope"='lesson-alias',"canonicalStudyId"=cid::text WHERE company=co AND dsid::text=gid AND "userId"::text=a.szyh::text AND "objectId"<>d."objectId";
 SELECT COALESCE(jsonb_agg(value ORDER BY value->>'GeneralID'),'[]') INTO w FROM (
  SELECT DISTINCT ON (value->>'GeneralID') value FROM "LessonStudyBatch" b CROSS JOIN LATERAL jsonb_array_elements(b.words) value
  WHERE b.company=co AND b."orderId"=gid AND b.status='confirmed'
  ORDER BY value->>'GeneralID',b."updatedAt" DESC,b."batchId" DESC
 ) merged;
 UPDATE "DailyStudyRecord" SET xxqs=w::text,con='本节累计学习'||jsonb_array_length(w)||'个单词',learned=jsonb_array_length(w),ygg=(SELECT count(*) FROM jsonb_array_elements(w) v WHERE v->>'check'='1'),djq=(SELECT count(*) FROM jsonb_array_elements(w) v WHERE v->>'check'='2'),"studyVersion"=COALESCE("studyVersion",0)+1,"updatedAt"=now() WHERE "objectId"=d."objectId" RETURNING * INTO d;
 UPDATE "CommonModel" SET hits=jsonb_array_length(w),"updatedAt"=now(),"teachingManaged"=true WHERE company=co AND "modelId"=56 AND "itemId"=d.id;
 RETURN jsonb_build_object('GeneralID',cid,'recordObjectId',d."objectId",'version',d."studyVersion",'words',w,'learned',jsonb_array_length(w));
END $$;

CREATE OR REPLACE FUNCTION xs_lesson_study_save(co text,gid text,actor text,actor_uid numeric,request_key text,p jsonb) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE a "CourseAppointment"; b "LessonStudyBatch"; prior "LessonStudyCommand"; result jsonb; bid text=p->>'batchId'; mode text=p->>'mode'; w jsonb=p->'words'; fingerprint jsonb; valid_count integer;
BEGIN
 IF length(COALESCE(request_key,'')) NOT BETWEEN 10 AND 160 OR length(COALESCE(bid,'')) NOT BETWEEN 1 AND 160 OR mode IS NULL OR mode NOT IN ('draft','confirm','discard','legacy','mastery') OR jsonb_typeof(w) IS DISTINCT FROM 'array' OR jsonb_array_length(w)>500 THEN RAISE EXCEPTION '学习批次参数无效'; END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT a0.* INTO a FROM "CourseAppointment" a0 JOIN "CommonModel" c ON c.company=a0.company AND c."modelId"=54 AND c."itemId"=a0.id AND c.status=99 WHERE c.company=co AND c."generalId"::text=gid FOR UPDATE OF a0;
 IF a."objectId" IS NULL OR a.pl::text<>actor_uid::text OR NOT EXISTS(SELECT 1 FROM "_User" WHERE "objectId"=actor AND company=co AND "legacyUserId"=actor_uid AND "legacyGroupId"=3 AND NOT COALESCE("isDisabled",false)) THEN RAISE EXCEPTION '仅预约老师可以保存课堂学习'; END IF;
 fingerprint=jsonb_build_object('orderId',gid,'payload',p);
 SELECT * INTO prior FROM "LessonStudyCommand" WHERE company=co AND "LessonStudyCommand".actor=xs_lesson_study_save.actor AND "requestKey"=request_key;
 IF FOUND THEN IF prior.fingerprint<>fingerprint THEN RAISE EXCEPTION '请求编号已用于其他内容'; END IF; RETURN prior.result; END IF;
 IF a.dszt<>'10' AND NOT (mode='mastery' AND a.dszt IN ('30','40')) THEN RAISE EXCEPTION '请在课程进行中保存学习记录'; END IF;
 IF NOT EXISTS(SELECT 1 FROM "CourseBinding" b0 JOIN "CommonModel" c ON c.company=b0.company AND c."modelId"=58 AND c."itemId"=b0.id AND c.status=99 WHERE b0.company=co AND b0."objectId"=p->>'bindingId' AND b0.yhid::text=a.szyh::text AND b0."enrollmentStatus"='active') THEN RAISE EXCEPTION '所选课程尚未开通或已停用'; END IF;
 WITH RECURSIVE nodes AS (SELECT n."nodeId" FROM "Node" n JOIN "CourseBinding" cb ON cb.company=n.company AND cb.kcid=n."nodeId"::text WHERE cb.company=co AND cb."objectId"=p->>'bindingId' AND COALESCE(n.zstatus,99)=99 UNION SELECT n."nodeId" FROM "Node" n JOIN nodes parent ON n."parentId"=parent."nodeId" WHERE n.company=co AND COALESCE(n.zstatus,99)=99)
 SELECT count(DISTINCT c."generalId") INTO valid_count FROM "CommonModel" c JOIN nodes n ON n."nodeId"=c."nodeId" WHERE c.company=co AND c."modelId"=52 AND c.status=99 AND c."generalId"::text IN (SELECT value->>'GeneralID' FROM jsonb_array_elements(w));
 IF valid_count<>(SELECT count(DISTINCT value->>'GeneralID') FROM jsonb_array_elements(w)) OR EXISTS(SELECT 1 FROM jsonb_array_elements(w) v WHERE NULLIF(v->>'GeneralID','') IS NULL) THEN RAISE EXCEPTION '学习单词与所选课程不匹配'; END IF;
 SELECT * INTO b FROM "LessonStudyBatch" WHERE company=co AND "orderId"=gid AND "batchId"=bid;
 IF b."batchId" IS NOT NULL AND (b."bindingId" IS DISTINCT FROM p->>'bindingId' OR (mode<>'legacy' AND b.version IS DISTINCT FROM (p->>'expectedVersion')::int)) THEN RAISE EXCEPTION '学习批次已更新，请刷新后重试'; END IF;
 IF b."batchId" IS NULL AND COALESCE((p->>'expectedVersion')::int,0)<>0 THEN RAISE EXCEPTION '学习批次版本无效'; END IF;
 -- A stale round cannot overwrite a newer confirmation from another round.
 IF mode='confirm' AND b."batchId" IS NOT NULL AND EXISTS(
  SELECT 1 FROM "LessonStudyBatch" newer CROSS JOIN LATERAL jsonb_array_elements(newer.words) other
  JOIN jsonb_array_elements(w) incoming ON other->>'GeneralID'=incoming->>'GeneralID'
  WHERE newer.company=co AND newer."orderId"=gid AND newer."batchId"<>bid AND newer.status='confirmed'
   AND newer."updatedAt">b."updatedAt" AND other->>'check' IS DISTINCT FROM incoming->>'check'
 ) THEN RAISE EXCEPTION '其他轮次已更新掌握情况，请刷新后重新学习确认'; END IF;
 IF mode='mastery' THEN
  IF b.provenance IS DISTINCT FROM 'user-confirmed-recovery' OR b.status IS DISTINCT FROM 'confirmed' THEN RAISE EXCEPTION '只能补充已核实恢复记录的掌握情况'; END IF;
  IF (SELECT array_agg(value->>'GeneralID' ORDER BY value->>'GeneralID') FROM jsonb_array_elements(w)) IS DISTINCT FROM (SELECT array_agg(value->>'GeneralID' ORDER BY value->>'GeneralID') FROM jsonb_array_elements(b.words)) THEN RAISE EXCEPTION '补标不能更改课堂词表'; END IF;
  IF EXISTS(SELECT 1 FROM jsonb_array_elements(b.words) old JOIN jsonb_array_elements(w) incoming ON old->>'GeneralID'=incoming->>'GeneralID' WHERE old->>'check' IN ('1','2') AND old->>'check' IS DISTINCT FROM incoming->>'check') THEN RAISE EXCEPTION '补标不能覆盖已确认掌握状态'; END IF;
 END IF;
 IF b.status='confirmed' AND mode IN ('draft','discard') THEN RAISE EXCEPTION '已确认批次不能退回草稿或丢弃'; END IF;
 IF mode IN ('confirm','mastery') AND (jsonb_array_length(w)=0 OR EXISTS(SELECT 1 FROM jsonb_array_elements(w) v WHERE COALESCE(v->>'check','0') NOT IN ('1','2'))) THEN RAISE EXCEPTION '请为每个单词选择掌握情况'; END IF;
 -- Legacy clients send snapshots without revisions. Keep existing words and refuse a
 -- conflicting mastery overwrite; new clients use versioned batches for corrections.
 IF mode='legacy' AND EXISTS(SELECT 1 FROM jsonb_array_elements(w) v WHERE v->>'check' IN ('1','2')) THEN
  IF EXISTS(SELECT 1 FROM jsonb_array_elements(COALESCE(b.words,'[]')) old JOIN jsonb_array_elements(w) incoming ON old->>'GeneralID'=incoming->>'GeneralID' WHERE old->>'check' IN ('1','2') AND incoming->>'check' IN ('1','2') AND old->>'check'<>incoming->>'check') THEN RAISE EXCEPTION '请刷新新版页面后修改掌握状态'; END IF;
  SELECT COALESCE(jsonb_agg(value),'[]') INTO w FROM (SELECT DISTINCT ON(value->>'GeneralID') value FROM jsonb_array_elements(COALESCE(b.words,'[]')||w) value WHERE value->>'check' IN ('1','2') ORDER BY value->>'GeneralID',(value->>'check') DESC) merged;
 ELSIF mode='legacy' THEN
  IF b.status='confirmed' THEN RAISE EXCEPTION '已保存成果不能由未确认词表覆盖，请刷新新版页面'; END IF;
  mode='draft';
 END IF;
 -- Enrich provenance from authoritative vocabulary rows, not client labels.
 SELECT COALESCE(jsonb_agg(v||jsonb_build_object('nodeId',c."nodeId",'Title',c.title,'bindingId',p->>'bindingId')),'[]') INTO w FROM jsonb_array_elements(w) v JOIN "CommonModel" c ON c.company=co AND c."modelId"=52 AND c."generalId"::text=v->>'GeneralID';
 INSERT INTO "LessonStudyBatch"(company,"orderId","batchId","studentId","coachId","bindingId",words,status,version,"updatedAt")
 VALUES(co,gid,bid,a.szyh::numeric,actor_uid,p->>'bindingId',w,CASE WHEN mode IN ('confirm','legacy','mastery') THEN 'confirmed' WHEN mode='discard' THEN 'discarded' ELSE 'draft' END,COALESCE(b.version,0)+1,clock_timestamp())
 ON CONFLICT(company,"orderId","batchId") DO UPDATE SET words=EXCLUDED.words,status=EXCLUDED.status,version=EXCLUDED.version,"updatedAt"=EXCLUDED."updatedAt";
 result=xs_lesson_study_rebuild(co,gid)||jsonb_build_object('batchId',bid,'batchVersion',COALESCE(b.version,0)+1);
 IF mode='mastery' THEN
  UPDATE "LessonRecord" SET kzsj=(result->'words')::text,"updatedAt"=now() WHERE company=co AND yyds::text=gid;
 END IF;
 INSERT INTO "LessonStudyCommand" VALUES(co,actor,request_key,fingerprint,result,now());
 RETURN result;
END $$;
REVOKE ALL ON FUNCTION xs_lesson_study_rebuild(text,text),xs_lesson_study_save(text,text,text,numeric,text,jsonb) FROM PUBLIC;