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;
ALTER TABLE "LessonCompletionReport" ADD COLUMN IF NOT EXISTS attachments jsonb NOT NULL DEFAULT '[]'::jsonb;
CREATE TABLE IF NOT EXISTS "LessonFeedbackAttachment" (
 "objectId" text PRIMARY KEY, company text NOT NULL, "appointmentGeneralId" text NOT NULL,
 "coachId" numeric NOT NULL, "fileName" text NOT NULL, "publicUrl" text NOT NULL,
 "contentType" text NOT NULL, size numeric NOT NULL, "createdAt" timestamptz DEFAULT now(), "updatedAt" timestamptz
);
CREATE INDEX IF NOT EXISTS lesson_feedback_attachment_owner ON "LessonFeedbackAttachment" (company,"appointmentGeneralId","coachId");

-- Existing reports were submitted; new feedback-free completions explicitly use pending.
ALTER TABLE "LessonCompletionReport" ADD COLUMN IF NOT EXISTS "feedbackStatus" text NOT NULL DEFAULT 'submitted';
UPDATE "LessonCompletionReport" SET "feedbackStatus"='submitted' WHERE "feedbackStatus" IS NULL;
ALTER TABLE "LessonCompletionReport" ALTER COLUMN "feedbackStatus" SET DEFAULT 'submitted';
ALTER TABLE "LessonCompletionReport" ALTER COLUMN "feedbackStatus" SET NOT NULL;
ALTER TABLE "LessonCompletionReport" DROP CONSTRAINT IF EXISTS lesson_feedback_status;
ALTER TABLE "LessonCompletionReport" ADD CONSTRAINT lesson_feedback_status CHECK ("feedbackStatus" IN ('pending','submitted'));
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; study_words jsonb; study_course text; found_study boolean=false; 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 feedback IS NOT NULL AND (NULLIF(trim(feedback->>'contentSummary'),'') IS NULL OR length(feedback->>'contentSummary')>1000
 OR length(COALESCE(feedback->>'reviewStatus',''))>200
 OR length(COALESCE(feedback->>'trainingStatus',''))>200
 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",'feedbackStatus',report."feedbackStatus",'teacherPay',(SELECT COALESCE(lr."teacherPaySnapshot",a."teacherPaySnapshot") FROM "LessonRecord" lr WHERE lr.company=co AND lr."objectId"=report."lessonId"),'payStatus',CASE WHEN (SELECT COALESCE(lr."teacherPaySnapshot",a."teacherPaySnapshot") FROM "LessonRecord" lr WHERE lr.company=co AND lr."objectId"=report."lessonId") IS NULL THEN 'pending' ELSE 'earned' END);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;
 -- Versioned batches and completion serialize on the same xs-credit lock.
 IF to_regclass('"LessonStudyBatch"') IS NOT NULL THEN
  IF EXISTS(SELECT 1 FROM "LessonStudyBatch" WHERE company=co AND "orderId"=gid AND status='draft') THEN RAISE EXCEPTION '还有未确认的学习批次，请先确认掌握状态或放弃该批次'; END IF;
  IF EXISTS(SELECT 1 FROM "LessonStudyBatch" WHERE company=co AND "orderId"=gid AND status='confirmed') OR EXISTS(SELECT 1 FROM "DailyStudyRecord" WHERE company=co AND dsid::text=gid AND "userId"::text=a.szyh::text) THEN
   PERFORM xs_lesson_study_rebuild(co,gid);
   IF EXISTS(SELECT 1 FROM "LessonStudyBatch" WHERE company=co AND "orderId"=gid AND status='draft') THEN RAISE EXCEPTION '历史学习批次尚未确认，请先确认掌握状态'; END IF;
   UPDATE "DailyStudyRecord" SET fxrl=CASE WHEN COALESCE(learned,0)>0 THEN (SELECT string_agg(to_char((now() AT TIME ZONE 'Asia/Shanghai')::date+offset_days,'YYYYMMDD'),',' ORDER BY offset_days) FROM unnest(ARRAY[1,2,3,5,7,9,11,14,17,21]) offset_days) ELSE '' END WHERE company=co AND dsid::text=gid AND "recordScope"='lesson';
  END IF;
 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;
 FOR study IN SELECT d.*,sc."generalId" AS study_general_id,b.kcid AS study_course_id FROM "DailyStudyRecord" d
 JOIN "CommonModel" sc ON sc.company=d.company AND sc."modelId"=56 AND sc."itemId"=d.id AND sc.status=99
 LEFT JOIN "CourseBinding" b ON b.company=d.company AND b."objectId"=to_jsonb(d)->>'bindingId' AND b.yhid::text=a.szyh::text
 WHERE d.company=co AND d."userId"::text=a.szyh::text AND d.dsid::text=gid AND COALESCE(to_jsonb(d)->>'recordScope','')<>'lesson-alias' ORDER BY d."updatedAt" DESC,d.id DESC LOOP
  IF NOT found_study THEN words='[]';found_study=true;END IF;
  study_words=COALESCE(NULLIF(study.xxqs,'')::jsonb,'[]');
  IF jsonb_typeof(study_words)<>'array' THEN RAISE EXCEPTION '课堂学习记录格式异常，请核对后重试';END IF;
  words=words||study_words;study_course=COALESCE(study.study_course_id,a.kcid);
  IF jsonb_array_length(study_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||CASE WHEN NULLIF(to_jsonb(study)->>'bindingId','') IS NOT NULL AND (to_jsonb(study)->>'bindingId') IS DISTINCT FROM a."bindingId" THEN ':study:'||study.study_general_id ELSE '' END||':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',study_course,
      '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 LOOP;
 -- Combine all selected books for the lesson report without counting a word twice.
 SELECT COALESCE(jsonb_agg(value ORDER BY ord),'[]'::jsonb) INTO words FROM (
  SELECT DISTINCT ON (COALESCE(value->>'GeneralID',value::text)) value,ord
  FROM jsonb_array_elements(words) WITH ORDINALITY w(value,ord)
  ORDER BY COALESCE(value->>'GeneralID',value::text),ord
 ) unique_words;
 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",COALESCE(feedback,'{}'::jsonb)||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),'attachments',COALESCE(feedback->'attachments','[]'::jsonb),
  'homework',CASE WHEN COALESCE((feedback->>'noHomework')::boolean,false) THEN '无作业' ELSE feedback->>'homework' END,'feedbackStatus',CASE WHEN feedback IS NULL THEN 'pending' ELSE 'submitted' END,'submittedBy',CASE WHEN feedback IS NULL THEN NULL ELSE actor END,'submittedAt',CASE WHEN feedback IS NULL THEN NULL ELSE now() END));
 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,'feedbackStatus',CASE WHEN feedback IS NULL THEN 'pending' ELSE 'submitted' END,'teacherPay',COALESCE(l."teacherPaySnapshot",a."teacherPaySnapshot"),'payStatus',CASE WHEN COALESCE(l."teacherPaySnapshot",a."teacherPaySnapshot") IS NULL THEN 'pending' ELSE 'earned' END);
 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;
 -- Serialize student evaluation with teacher amendments using the same lock order.
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 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::text,'') ~ '^[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,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;

-- Feedback-only amendment: never re-run completion, credit or learning projections.
CREATE OR REPLACE FUNCTION xs_lesson_feedback_update(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"; report "LessonCompletionReport"; lesson "LessonRecord";
 previous "LessonWorkflowCommand"; result jsonb; old_feedback jsonb;
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 NULLIF(trim(feedback->>'trainingSummary'),'') IS NULL
 OR (NOT COALESCE((feedback->>'noHomework')::boolean,false) AND NULLIF(trim(feedback->>'homework'),'') IS NULL)
 OR EXISTS(SELECT 1 FROM jsonb_each_text(feedback) f WHERE f.key<>'attachments' AND length(f.value)>CASE WHEN f.key IN ('reviewStatus','trainingStatus') THEN 200 ELSE 1000 END)
 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 FOR UPDATE;
 SELECT * INTO a FROM "CourseAppointment" WHERE company=co AND id=c."itemId" FOR UPDATE;
 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;
 IF a.dszt NOT IN ('30','40') THEN RAISE EXCEPTION '请先完成课程再修改报告';END IF;
 SELECT * INTO previous FROM "LessonWorkflowCommand" WHERE company=co AND "LessonWorkflowCommand".actor=xs_lesson_feedback_update.actor AND "requestKey"=request_key;
 IF FOUND THEN
  IF previous.fingerprint<>jsonb_build_object('action','feedback_update','id',gid,'feedback',feedback) THEN RAISE EXCEPTION '请求编号已用于其他内容';END IF;
  RETURN previous.result - 'previousFeedback';
 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 FOR UPDATE;
 -- Lock the legacy lesson too, so old-client ratings cannot race the decision.
 PERFORM 1 FROM "LessonRecord" WHERE company=co AND yyds::text=gid FOR UPDATE;
 IF report."objectId" IS NOT NULL AND report."feedbackStatus" IS DISTINCT FROM 'pending' AND (
  EXISTS(SELECT 1 FROM "LessonReview" WHERE company=co AND "appointmentGeneralId"=gid)
  OR EXISTS(SELECT 1 FROM "LessonRecord" WHERE company=co AND yyds::text=gid AND COALESCE(pf::text,'') ~ '^[1-5](\.0+)?$')
 ) THEN RAISE EXCEPTION '学生已评价，课堂反馈已锁定，不能再修改';END IF;
 -- Old completed lessons may have no report. First submission uses the appointment
 -- revision from the verified detail; later edits use the report revision.
 IF report."objectId" IS NULL THEN
  IF expected IS NULL OR a."updatedAt" IS DISTINCT FROM expected THEN RAISE EXCEPTION '预约已更新，请刷新详情后重试';END IF;
  IF (SELECT count(*) FROM "LessonRecord" WHERE company=co AND yyds::text=gid)<>1 THEN RAISE EXCEPTION '历史上课记录缺失或不唯一，请联系管理员核对';END IF;
  SELECT * INTO lesson FROM "LessonRecord" WHERE company=co AND yyds::text=gid FOR UPDATE;
  IF lesson.xymz::text IS DISTINCT FROM a.szyh::text OR lesson.jsmz::text IS DISTINCT FROM a.pl::text THEN RAISE EXCEPTION '历史上课记录归属不一致，请联系管理员核对';END IF;
  INSERT INTO "LessonCompletionReport" SELECT * FROM jsonb_populate_record(NULL::"LessonCompletionReport",jsonb_build_object(
   'objectId',xs_object_id(),'company',co,'createdAt',clock_timestamp(),'updatedAt',clock_timestamp(),
   'sourceKey','cloud:lesson-feedback:'||co||':'||gid,'appointmentId',a."objectId",'appointmentGeneralId',gid,
   'lessonId',lesson."objectId",'studentId',a.szyh,'coachId',a.pl,'courseCategoryKey',a."courseCategoryKey",
   'courseCategoryName',a."courseCategoryName",'deliveryMode',a."deliveryMode",'durationMinutes',a."durationMinutes",
   'creditCosts',a."creditCostsSnapshot",'feedbackStatus','pending','attachments','[]'::jsonb)) RETURNING * INTO report;
 ELSE
  IF expected IS NULL OR report."updatedAt" IS DISTINCT FROM expected THEN RAISE EXCEPTION '报告已被更新，请关闭编辑并刷新详情后重试';END IF;
 END IF;
 SELECT jsonb_object_agg(key,value) INTO old_feedback FROM jsonb_each(to_jsonb(report)) WHERE key IN ('contentSummary','reviewStatus','reviewSummary','trainingStatus','trainingSummary','homework','noHomework','coachComment','notes','attachments');
 UPDATE "LessonCompletionReport" SET
 "contentSummary"=feedback->>'contentSummary',"reviewStatus"=feedback->>'reviewStatus',"reviewSummary"=feedback->>'reviewSummary',
 "trainingStatus"=feedback->>'trainingStatus',"trainingSummary"=feedback->>'trainingSummary',
 attachments=CASE WHEN feedback?'attachments' THEN feedback->'attachments' ELSE COALESCE(report.attachments,'[]'::jsonb) END,
 homework=CASE WHEN COALESCE((feedback->>'noHomework')::boolean,false) THEN '无作业' ELSE feedback->>'homework' END,
 "feedbackStatus"='submitted',"submittedBy"=actor,"submittedAt"=COALESCE(report."submittedAt",clock_timestamp()),"noHomework"=COALESCE((feedback->>'noHomework')::boolean,false),"coachComment"=feedback->>'coachComment',notes=feedback->>'notes',"updatedAt"=clock_timestamp()
 WHERE "objectId"=report."objectId";
 UPDATE "LessonRecord" SET pldp=COALESCE(feedback->>'coachComment',''),plpjsj=now()::text,"updatedAt"=now()
 WHERE company=co AND "objectId"=report."lessonId" AND yyds::text=gid;
 UPDATE "CourseAppointment" SET "updatedAt"=now() WHERE "objectId"=a."objectId";
 UPDATE "CommonModel" SET "updatedAt"=now() WHERE "objectId"=c."objectId";
 result=jsonb_build_object('GeneralID',gid,'feedbackId',report."objectId",'saved',true,'previousFeedback',old_feedback);
 INSERT INTO "LessonWorkflowCommand" VALUES(co,actor,request_key,jsonb_build_object('action','feedback_update','id',gid,'feedback',feedback),result,now());
 RETURN result - 'previousFeedback';
END $$;
REVOKE ALL ON FUNCTION xs_lesson_feedback_update(text,text,text,numeric,timestamptz,text,jsonb) FROM PUBLIC;