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;

-- 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),
  '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;
 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";
 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 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;
 IF expected IS NULL OR report."updatedAt" IS DISTINCT FROM expected THEN RAISE EXCEPTION '报告已被更新，请关闭编辑并刷新详情后重试';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');
 UPDATE "LessonCompletionReport" SET
 "contentSummary"=feedback->>'contentSummary',"reviewStatus"=feedback->>'reviewStatus',"reviewSummary"=feedback->>'reviewSummary',
 "trainingStatus"=feedback->>'trainingStatus',"trainingSummary"=feedback->>'trainingSummary',
 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;