xiaoshu-app/scripts/sql/lesson-workflow.sql

-- Install after member-enrollment.sql and mobile-teaching.sql. No historical backfill.
CREATE TABLE IF NOT EXISTS "LessonWorkflowCommand" (
 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")
);
CREATE TABLE IF NOT EXISTS "LessonReview" (
 company text NOT NULL, "appointmentGeneralId" text NOT NULL, "studentId" numeric NOT NULL,
 score integer NOT NULL CHECK(score BETWEEN 1 AND 5), comment text NOT NULL CHECK(length(comment)<=200),
 "submittedAt" timestamptz NOT NULL DEFAULT now(), PRIMARY KEY(company,"appointmentGeneralId")
);
CREATE TABLE IF NOT EXISTS "LessonWorkflowIssue" (
 company text NOT NULL, "appointmentGeneralId" text NOT NULL, message text NOT NULL,
 "updatedAt" timestamptz NOT NULL DEFAULT now(), PRIMARY KEY(company,"appointmentGeneralId")
);
ALTER TABLE "CourseAppointment" ADD COLUMN IF NOT EXISTS "teachingManaged" boolean;
ALTER TABLE "CommonModel" ADD COLUMN IF NOT EXISTS "teachingManaged" boolean;
ALTER TABLE "LessonRecord" ADD COLUMN IF NOT EXISTS "teachingManaged" boolean;
ALTER TABLE "LessonCompletionReport" ADD COLUMN IF NOT EXISTS "coachComment" text;

CREATE OR REPLACE FUNCTION xs_lesson_complete(co text,gid text,actor text,actor_uid numeric,expected timestamptz,request_key text,feedback jsonb)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE a "CourseAppointment"; c "CommonModel"; l "LessonRecord"; report "LessonCompletionReport";
 previous "LessonWorkflowCommand"; result jsonb; lesson_count int; cost jsonb; ledger_table text; evidence int;
 study record; words jsonb='[]'; review_date text; review_key text; review_id numeric; review_object text;
 total_reviews int; completed_reviews int; report_id text; was_charged boolean=false;
BEGIN
 IF request_key IS NULL OR length(request_key)<10 OR length(request_key)>160 THEN RAISE EXCEPTION '缺少有效请求编号';END IF;
 IF 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;
 IF NULLIF(trim(feedback->>'contentSummary'),'') IS NULL OR length(feedback->>'contentSummary')>1000
 OR COALESCE(feedback->>'reviewStatus','') NOT IN ('完成','部分完成','未复习','无复习任务')
 OR COALESCE(feedback->>'trainingStatus','') NOT IN ('正常进行','需要巩固','已完成','暂停')
 OR NULLIF(trim(feedback->>'trainingSummary'),'') IS NULL OR length(feedback->>'trainingSummary')>1000
 OR (NOT COALESCE((feedback->>'noHomework')::boolean,false) AND NULLIF(trim(feedback->>'homework'),'') IS NULL)
 THEN RAISE EXCEPTION '请完整填写学习内容、复习、训练进度和作业';END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT * INTO c FROM "CommonModel" WHERE company=co AND "modelId"=54 AND "generalId"::text=gid;
 SELECT * INTO a FROM "CourseAppointment" WHERE company=co AND id=c."itemId";
 IF a."objectId" IS NULL OR c.status=-2 THEN RAISE EXCEPTION '预约不存在或已取消';END IF;
 IF a.pl::text IS DISTINCT FROM actor_uid::text THEN RAISE EXCEPTION '仅预约老师可以结课';END IF;
 -- Same lock order as credit transfers and xs_complete_appointment.
 PERFORM 1 FROM "_User" WHERE company=co AND "legacyUserId"::text=a.szyh::text FOR UPDATE;
 SELECT * INTO a FROM "CourseAppointment" WHERE "objectId"=a."objectId" FOR UPDATE;
 SELECT * INTO previous FROM "LessonWorkflowCommand" WHERE company=co AND "LessonWorkflowCommand".actor=xs_lesson_complete.actor AND "requestKey"=request_key;
 IF FOUND THEN
  IF previous.fingerprint<>jsonb_build_object('id',gid,'feedback',feedback) THEN RAISE EXCEPTION '请求编号已用于其他内容';END IF;
  RETURN previous.result;
 END IF;
 IF (SELECT count(*) FROM "LessonCompletionReport" WHERE company=co AND "appointmentGeneralId"::text=gid)>1 THEN RAISE EXCEPTION '历史凭据存在重复报告，请由后台核对';END IF;
 SELECT * INTO report FROM "LessonCompletionReport" WHERE company=co AND "appointmentGeneralId"::text=gid ORDER BY "submittedAt" DESC LIMIT 1;
 IF a.dszt='30' AND report."objectId" IS NOT NULL THEN RETURN jsonb_build_object('GeneralID',gid,'status',30,'unchanged',true,'feedbackId',report."objectId");END IF;
 IF expected IS NULL OR a."updatedAt" IS DISTINCT FROM expected THEN RAISE EXCEPTION '预约已更新，请刷新详情后重试';END IF;
 IF a.dszt NOT IN ('10','11','20') THEN RAISE EXCEPTION '请先开始课程';END IF;
 SELECT count(*) INTO lesson_count FROM "LessonRecord" WHERE company=co AND yyds::text=gid;
 IF lesson_count>1 THEN RAISE EXCEPTION '历史凭据存在重复上课记录，请由后台核对';END IF;
 SELECT * INTO l FROM "LessonRecord" WHERE company=co AND yyds::text=gid LIMIT 1;
 IF l."objectId" IS NOT NULL THEN words=COALESCE(NULLIF(l.kzsj,'')::jsonb,'[]');END IF;
 IF l."objectId" IS NOT NULL OR a.dszt IN ('11','20') THEN
  IF l."objectId" IS NULL OR l.xymz::text IS DISTINCT FROM a.szyh::text OR l.jsmz::text IS DISTINCT FROM a.pl::text
   OR NOT EXISTS(SELECT 1 FROM "CommonModel" WHERE company=co AND "modelId"=59 AND "itemId"=l.id AND status=99)
   OR jsonb_array_length(COALESCE(a."creditCostsSnapshot"::jsonb,'[]'))=0
   OR (SELECT count(*) FROM "CourseCreditReservation" WHERE company=co AND "appointmentId"=c."objectId" AND state='consumed' AND "creditCosts"::jsonb=a."creditCostsSnapshot"::jsonb)<>1
  THEN RAISE EXCEPTION '历史凭据不完整，请由后台核对课时，不能自动补扣';END IF;
  FOR cost IN SELECT value FROM jsonb_array_elements(a."creditCostsSnapshot"::jsonb) LOOP
   ledger_table=CASE cost->>'account' WHEN 'Purse' THEN 'UserExpDomP' WHEN 'SilverCoin' THEN 'UserSIcon' WHEN 'UserExp' THEN 'UserExpHis' WHEN 'UserPoint' THEN 'UserUserPoint' END;
   IF ledger_table IS NULL THEN RAISE EXCEPTION '历史凭据的账户无效';END IF;
   EXECUTE format('SELECT count(*) FROM %I WHERE company=$1 AND "userId"::text=$2 AND "sourceKey"=$3 AND score=$4',ledger_table)
    INTO evidence USING co,a.szyh::text,'credit:complete:'||c."objectId"||':'||a.szyh||':'||(cost->>'account'),-(cost->>'amount')::numeric;
   IF evidence<>1 THEN RAISE EXCEPTION '历史凭据与课时扣减不一致，请由后台核对';END IF;
  END LOOP;
 ELSE
  -- A fresh completion must consume exactly the reservation created by scheduling.
  IF (SELECT count(*) FROM "CourseCreditReservation" WHERE company=co AND "appointmentId"=c."objectId" AND state='reserved' AND "creditCosts"::jsonb=a."creditCostsSnapshot"::jsonb)<>1
  THEN RAISE EXCEPTION '历史凭据缺少匹配的课时预留，请由后台核对';END IF;
  result=xs_complete_appointment(co,c."objectId",actor);
  was_charged=COALESCE((result->>'periodDeducted')::boolean,false);
  SELECT * INTO l FROM "LessonRecord" WHERE company=co AND "objectId"=result->>'lessonId';
 END IF;
 SELECT d.*,sc."generalId" AS study_general_id INTO study FROM "DailyStudyRecord" d
 JOIN "CommonModel" sc ON sc.company=d.company AND sc."modelId"=56 AND sc."itemId"=d.id
 WHERE d.company=co AND d."userId"::text=a.szyh::text AND d.dsid::text=gid ORDER BY d."updatedAt" DESC LIMIT 1;
 IF FOUND THEN
  words=COALESCE(NULLIF(study.xxqs,'')::jsonb,'[]');
  IF jsonb_typeof(words)<>'array' THEN RAISE EXCEPTION '课堂学习记录格式异常，请核对后重试';END IF;
  IF jsonb_array_length(words)>0 THEN
   FOR review_date IN SELECT DISTINCT value FROM unnest(string_to_array(COALESCE(study.fxrl,''),',')) value WHERE value ~ '^\d{8}$' LIMIT 15 LOOP
    review_key='cloud:e_order_update:'||gid||':review:'||review_date;
    SELECT id,"objectId" INTO review_id,review_object FROM "MemoryPracticeRecord" WHERE company=co AND "sourceKey"=review_key LIMIT 1;
    IF review_object IS NULL THEN
     review_id=nextval('xiaoshu_business_id');review_object=xs_object_id();
     INSERT INTO "MemoryPracticeRecord" SELECT * FROM jsonb_populate_record(NULL::"MemoryPracticeRecord",jsonb_build_object(
      'objectId',review_object,'company',co,'id',review_id,'createdAt',now(),'updatedAt',now(),'sourceKey',review_key,
      'yhid',a.szyh,'plid',COALESCE(NULLIF(a.fxpl,''),a.pl),'orderId',gid,'xxjlid',study.study_general_id,'kcid',a.kcid,
      'fxzt','0','kywrq',to_char(to_date(review_date,'YYYYMMDD'),'YYYY-MM-DD'),'kywsj',to_char(to_date(review_date,'YYYYMMDD'),'YYYY-MM-DD')||' '||COALESCE(a.sdsd,'00:00')));
    END IF;
    IF NOT EXISTS(SELECT 1 FROM "CommonModel" WHERE company=co AND "sourceKey"=review_key) THEN
     PERFORM xs_insert('CommonModel',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'sourceKey',review_key,'generalId',nextval('xiaoshu_business_id'),'itemId',review_id,'modelId',60,'nodeId',388,'tableName','ZL_C_gywjl','title',c.title||'@抗遗忘@'||review_date,'inputer',actor,'status',99));
    END IF;
   END LOOP;
  END IF;
 END IF;
 UPDATE "LessonRecord" SET kzsj=words::text,pldp=COALESCE(NULLIF(feedback->>'coachComment',''),NULLIF(feedback->>'notes',''),pldp),plpjsj=now()::text,"teachingManaged"=true,"updatedAt"=now() WHERE "objectId"=l."objectId";
 UPDATE "CourseAppointment" SET dszt='30',jssj=COALESCE(NULLIF(jssj,''),now()::text),"teachingManaged"=true,"updatedAt"=now() WHERE "objectId"=a."objectId";
 UPDATE "CommonModel" SET "teachingManaged"=true,"updatedAt"=now() WHERE company=co AND ("objectId"=c."objectId" OR ("modelId"=59 AND "itemId"=l.id));
 SELECT count(*),count(*) FILTER(WHERE fxzt='1') INTO total_reviews,completed_reviews FROM "MemoryPracticeRecord" WHERE company=co AND "orderId"::text=gid;
 report_id=COALESCE(report."objectId",xs_object_id());
 -- Existing partial reports are replaced in the same transaction, preserving their ID.
 DELETE FROM "LessonCompletionReport" WHERE "objectId"=report_id AND company=co;
 INSERT INTO "LessonCompletionReport" SELECT * FROM jsonb_populate_record(NULL::"LessonCompletionReport",feedback||jsonb_build_object(
  'objectId',report_id,'company',co,'createdAt',COALESCE(report."createdAt",now()),'updatedAt',now(),'sourceKey','cloud:lesson-feedback:'||co||':'||gid,
  'appointmentId',a."objectId",'appointmentGeneralId',gid,'lessonId',l."objectId",'studentId',a.szyh,'coachId',a.pl,
  'courseCategoryKey',a."courseCategoryKey",'courseCategoryName',a."courseCategoryName",'deliveryMode',a."deliveryMode",'durationMinutes',a."durationMinutes",
  'actualStartAt',NULLIF(a.kssj,'')::timestamptz,'actualEndAt',COALESCE(NULLIF(a.jssj,'')::timestamptz,now()),'creditCosts',a."creditCostsSnapshot",
  'vocabularyCount',jsonb_array_length(words),'antiForgetSummary',jsonb_build_object('total',total_reviews,'completed',completed_reviews,'pending',total_reviews-completed_reviews),
  'homework',CASE WHEN COALESCE((feedback->>'noHomework')::boolean,false) THEN '无作业' ELSE feedback->>'homework' END,'submittedBy',actor,'submittedAt',now()));
 DELETE FROM "LessonWorkflowIssue" WHERE company=co AND "appointmentGeneralId"=gid;
 result=jsonb_build_object('GeneralID',gid,'status',30,'lessonId',l."objectId",'feedbackId',report_id,'periodDeducted',was_charged);
 INSERT INTO "LessonWorkflowCommand" VALUES(co,actor,request_key,jsonb_build_object('id',gid,'feedback',feedback),result,now());
 RETURN result;
END $$;
REVOKE ALL ON FUNCTION xs_lesson_complete(text,text,text,numeric,timestamptz,text,jsonb) FROM PUBLIC;

CREATE OR REPLACE FUNCTION xs_lesson_review(co text,gid text,actor text,actor_uid numeric,rating integer,review_comment text)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE a record; l record; existing "LessonReview";
BEGIN
 IF rating IS NULL OR rating NOT BETWEEN 1 AND 5 OR review_comment IS NULL OR length(review_comment)>200 THEN RAISE EXCEPTION '评分应为1至5分，评价最多200字';END IF;
 IF NOT EXISTS(SELECT 1 FROM "_User" WHERE "objectId"=actor AND company=co AND "legacyUserId"=actor_uid AND NOT COALESCE("isDisabled",false)) THEN RAISE EXCEPTION '用户身份已失效';END IF;
 SELECT ca.*,c.status AS common_status INTO a FROM "CourseAppointment" ca JOIN "CommonModel" c ON c.company=ca.company AND c."modelId"=54 AND c."itemId"=ca.id WHERE c.company=co AND c."generalId"::text=gid FOR UPDATE OF ca;
 IF NOT FOUND OR a.common_status=-2 THEN RAISE EXCEPTION '预约不存在或已取消';END IF;
 IF a.szyh::text IS DISTINCT FROM actor_uid::text THEN RAISE EXCEPTION '仅预约学生可以评价';END IF;
 IF a.dszt NOT IN ('30','40') THEN RAISE EXCEPTION '课程尚未结课';END IF;
 SELECT * INTO existing FROM "LessonReview" WHERE company=co AND "appointmentGeneralId"=gid;
 IF FOUND THEN RETURN to_jsonb(existing)||jsonb_build_object('unchanged',true,'status',a.dszt::int);END IF;
 IF (SELECT count(*) FROM "LessonRecord" WHERE company=co AND yyds::text=gid)<>1 THEN RAISE EXCEPTION '上课记录缺失或重复，请由后台核对';END IF;
 SELECT * INTO l FROM "LessonRecord" WHERE company=co AND yyds::text=gid FOR UPDATE;
 IF COALESCE(l.pf,'') ~ '^[1-5](\.0+)?$' THEN RETURN jsonb_build_object('score',l.pf,'comment',l.pjnr,'unchanged',true,'status',a.dszt::int);END IF;
 INSERT INTO "LessonReview" VALUES(co,gid,actor_uid,rating,review_comment,now()) RETURNING * INTO existing;
 UPDATE "LessonRecord" SET pf=rating::text,pjnr=review_comment,"teachingManaged"=true,"updatedAt"=now() WHERE "objectId"=l."objectId";
 UPDATE "CourseAppointment" SET "teachingManaged"=true,"updatedAt"=now() WHERE "objectId"=a."objectId";
 UPDATE "CommonModel" SET "teachingManaged"=true,"updatedAt"=now() WHERE company=co AND (("modelId"=54 AND "itemId"=a.id) OR ("modelId"=59 AND "itemId"=l.id));
 RETURN to_jsonb(existing)||jsonb_build_object('status',a.dszt::int);
END $$;
REVOKE ALL ON FUNCTION xs_lesson_review(text,text,text,numeric,integer,text) FROM PUBLIC;

-- Recheck assignment under the same lock as scheduling; the preceding HTTP read is not authority.
CREATE OR REPLACE FUNCTION xs_start_appointment_for_actor(co text,aid text,actor text,actor_uid numeric)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE assigned text;
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT a.pl INTO assigned FROM "CourseAppointment" a JOIN "CommonModel" c ON c.company=a.company AND c."modelId"=54 AND c."itemId"=a.id WHERE c.company=co AND c."objectId"=aid;
 IF assigned IS DISTINCT FROM actor_uid::text OR NOT EXISTS(SELECT 1 FROM "_User" WHERE company=co AND "objectId"=actor AND "legacyUserId"=actor_uid AND "legacyGroupId"=3 AND NOT COALESCE("isDisabled",false)) THEN RAISE EXCEPTION '仅预约老师可以开始课程';END IF;
 PERFORM xs_start_appointment(co,aid);
END $$;
REVOKE ALL ON FUNCTION xs_start_appointment_for_actor(text,text,text,numeric) FROM PUBLIC;

-- Protect completed lessons even if a legacy sync read races with the teacher command.
CREATE OR REPLACE FUNCTION xs_lesson_no_regression() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF OLD."teachingManaged" IS TRUE AND (
   (OLD.dszt IN ('30','40') AND NEW.dszt IS DISTINCT FROM OLD.dszt AND NOT (OLD.dszt='30' AND NEW.dszt='40'))
   OR (OLD.dszt='10' AND NEW.dszt NOT IN ('10','11','20','30','40'))
   OR NEW."teachingManaged" IS DISTINCT FROM true)
 THEN RAISE EXCEPTION '新系统课次不可由旧流程回退';END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS lesson_no_regression ON "CourseAppointment";
CREATE TRIGGER lesson_no_regression BEFORE UPDATE ON "CourseAppointment" FOR EACH ROW EXECUTE FUNCTION xs_lesson_no_regression();

-- Administrators retain the existing correction operation with a required, transactional audit reason.
CREATE OR REPLACE FUNCTION xs_admin_lesson_correction(co text,aid text,actor text,reason text)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE result jsonb;
BEGIN
 IF NULLIF(trim(reason),'') IS NULL THEN RAISE EXCEPTION '请填写纠错原因';END IF;
 result=xs_complete_appointment(co,aid,actor);
 PERFORM xs_insert('EnrollmentCommand',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'idempotencyKey',xs_object_id(),'request',jsonb_build_object('actor',actor,'reason',reason,'appointmentId',aid,'operation','admin.lesson-correction'),'result',result));
 RETURN result;
END $$;
REVOKE ALL ON FUNCTION xs_admin_lesson_correction(text,text,text,text) FROM PUBLIC;