xiaoshu-app/scripts/sql/repair-confirmed-lesson-839154386019743.sql

-- User-confirmed recovery only: teacher 69, student 110, exactly 9+5+3 words.
-- Caller must back up the affected rows and run this in a transaction.
DO $$
DECLARE co text='7pIbDBJmKx';gid text='839154386019743';a "CourseAppointment";l "LessonRecord";d "DailyStudyRecord";r jsonb;w jsonb;group_node numeric;review_day text;key text;rid numeric;cid numeric;
 ids numeric[]=ARRAY[25163,25164,25165,25166,25167,25168,25169,25170,25171,26120,26121,26122,26123,26124,27143,27144,27145];
BEGIN
 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 WHERE c.company=co AND c."generalId"::text=gid FOR UPDATE OF a0;
 IF a.pl<>'69' OR a.szyh<>'110' OR a.dszt NOT IN ('30','40') OR a."objectId" IS NULL THEN RAISE EXCEPTION 'Recovery ownership/state mismatch';END IF;
 SELECT * INTO l FROM "LessonRecord" WHERE company=co AND yyds::text=gid;
 IF l."objectId" IS NULL THEN RAISE EXCEPTION 'Completed lesson missing';END IF;
 IF EXISTS(SELECT 1 FROM "LessonStudyBatch" WHERE company=co AND "orderId"=gid AND provenance='user-confirmed-recovery') THEN
  IF (SELECT learned FROM "DailyStudyRecord" WHERE company=co AND dsid::text=gid AND "recordScope"='lesson')<>17 OR (SELECT count(*) FROM "MemoryPracticeRecord" WHERE company=co AND "orderId"::text=gid)<>10 THEN RAISE EXCEPTION 'Existing repair needs manual review';END IF;
  RETURN;
 END IF;
 IF (SELECT count(*) FROM "DailyStudyRecord" WHERE company=co AND dsid::text=gid)<>1 OR jsonb_array_length(COALESCE(NULLIF(l.kzsj,'')::jsonb,'[]'))<>3 OR EXISTS(SELECT 1 FROM jsonb_array_elements(l.kzsj::jsonb) v WHERE (v->>'GeneralID')::numeric<>ALL(ARRAY[27143,27144,27145])) THEN RAISE EXCEPTION 'Source lesson changed since audit';END IF;
 IF EXISTS(SELECT 1 FROM "MemoryPracticeRecord" WHERE company=co AND "orderId"::text=gid) THEN RAISE EXCEPTION 'Existing reviews must be reconciled first';END IF;
 IF (SELECT count(*) FROM "CommonModel" WHERE company=co AND "modelId"=52 AND "generalId"=ANY(ids) AND "nodeId"=ANY(ARRAY[172,173,174]))<>17 THEN RAISE EXCEPTION 'Recovery words mismatch';END IF;
 -- First adopt the original three-word record, then replace its imported batch with
 -- the specifically authorized recovery batches, preserving the original record ID.
 PERFORM xs_lesson_study_rebuild(co,gid);
 UPDATE "LessonStudyBatch" SET status='discarded' WHERE company=co AND "orderId"=gid;
 FOREACH group_node IN ARRAY ARRAY[172,173,174] LOOP
  SELECT jsonb_agg(jsonb_build_object('GeneralID',c."generalId",'Title',c.title,'nodeId',c."nodeId",'bindingId','nq2u6S3Lc5','check',CASE WHEN group_node=174 THEN 1 ELSE 0 END) ORDER BY c."generalId") INTO w FROM "CommonModel" c WHERE c.company=co AND c."modelId"=52 AND c."generalId"=ANY(ids) AND c."nodeId"=group_node;
  INSERT INTO "LessonStudyBatch"(company,"orderId","batchId","studentId","coachId","bindingId",words,status,provenance) VALUES(co,gid,'recovery:'||group_node,110,69,'nq2u6S3Lc5',w,'confirmed','user-confirmed-recovery');
 END LOOP;
 r=xs_lesson_study_rebuild(co,gid);cid=(r->>'GeneralID')::numeric;
 SELECT * INTO d FROM "DailyStudyRecord" WHERE "objectId"=r->>'recordObjectId';
 UPDATE "DailyStudyRecord" SET fxrl=(SELECT string_agg(to_char((COALESCE(NULLIF(a.jssj,'')::timestamptz,l."createdAt") 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) WHERE "objectId"=d."objectId" RETURNING * INTO d;
 FOR review_day IN SELECT unnest(string_to_array(d.fxrl,',')) LOOP
  key='cloud:e_order_update:'||gid||':review:'||review_day;rid=nextval('xiaoshu_business_id');
  INSERT INTO "MemoryPracticeRecord" SELECT * FROM jsonb_populate_record(NULL::"MemoryPracticeRecord",jsonb_build_object('objectId',xs_object_id(),'company',co,'id',rid,'createdAt',now(),'updatedAt',now(),'sourceKey',key,'yhid',110,'plid',COALESCE(NULLIF(a.fxpl,''),a.pl),'orderId',gid,'xxjlid',cid,'kcid',161,'fxzt','0','kywrq',to_char(to_date(review_day,'YYYYMMDD'),'YYYY-MM-DD'),'kywsj',to_char(to_date(review_day,'YYYYMMDD'),'YYYY-MM-DD')||' '||COALESCE(a.sdsd,'00:00')));
  PERFORM xs_insert('CommonModel',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'sourceKey',key,'generalId',nextval('xiaoshu_business_id'),'itemId',rid,'modelId',60,'nodeId',388,'tableName','ZL_C_gywjl','title','课堂抗遗忘@'||review_day,'status',99,'teachingManaged',true));
 END LOOP;
 UPDATE "LessonRecord" SET kzsj=(r->'words')::text,"updatedAt"=now() WHERE "objectId"=l."objectId";
 UPDATE "LessonCompletionReport" SET "vocabularyCount"=17,"antiForgetSummary"=jsonb_build_object('total',10,'completed',0,'pending',10),"updatedAt"=now() WHERE company=co AND "appointmentGeneralId"::text=gid;
 INSERT INTO "LessonStudyCommand" VALUES(co,'authorized-recovery','recovery-20261013-'||gid,jsonb_build_object('reason','用户确认9+5+3个已学词；前14个掌握状态待老师补标，排除how及quite a few','wordIds',to_jsonb(ids)),r,now());
END $$;