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

BEGIN;
ALTER TABLE "MemoryPracticeRecord" ADD COLUMN IF NOT EXISTS "assignmentVersion" double precision DEFAULT 0;
CREATE TABLE IF NOT EXISTS "ReviewTransferCommand" (
 "objectId" text PRIMARY KEY,"company" text NOT NULL,"actorId" text NOT NULL,"key" text NOT NULL,
 "fingerprint" text NOT NULL,"reason" text NOT NULL,"result" jsonb NOT NULL,"createdAt" timestamptz NOT NULL DEFAULT now(),
 UNIQUE ("company","key")
);
CREATE TABLE IF NOT EXISTS "ReviewTransferAudit" (
 "objectId" text PRIMARY KEY,"company" text NOT NULL,"commandId" text NOT NULL,"taskId" text NOT NULL,
 "studentId" text NOT NULL,"appointmentId" text,"oldCoachId" text,"newCoachId" text NOT NULL,
 "oldVersion" double precision,"newVersion" double precision,"createdAt" timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS "ReviewAssignmentConflict" (
 "objectId" text PRIMARY KEY,"company" text NOT NULL,"taskId" text NOT NULL,"incoming" jsonb NOT NULL,
 "preserved" jsonb NOT NULL,"createdAt" timestamptz NOT NULL DEFAULT now()
);
-- A source learning record is authoritative; conflicting appointment links are never guessed.
CREATE OR REPLACE VIEW xs_review_task_link AS
SELECT m."company",m."objectId" AS "taskId",m."yhid"::text AS "studentId",
 CASE WHEN source.n=1 AND (COALESCE(NULLIF(m."orderId"::text,'0'),'')='' OR m."orderId"::text=source.appointment) THEN source.appointment END AS "appointmentId"
FROM "MemoryPracticeRecord" m
LEFT JOIN LATERAL (
 SELECT count(DISTINCT NULLIF(NULLIF(d.dsid,'0'),'')) n,min(NULLIF(NULLIF(d.dsid,'0'),'')) appointment
 FROM "CommonModel" c JOIN "DailyStudyRecord" d ON d.company=c.company AND d.id=c."itemId" AND d."userId"::text=m.yhid::text
 WHERE c.company=m.company AND c."modelId"=56 AND c."generalId"::text=m.xxjlid::text
) source ON true;

CREATE OR REPLACE FUNCTION xs_review_manager(co text, actor text) RETURNS void LANGUAGE plpgsql AS $$
DECLARE u jsonb; role_key text;
BEGIN
 SELECT to_jsonb(t) INTO u FROM "_User" t WHERE t."company"=co AND t."objectId"=actor;
 role_key:=COALESCE(NULLIF(u->>'adminRoleKey',''),CASE WHEN u->'roles' ? 'super-admin' THEN 'super-admin' WHEN u->'roles' ? 'ops-manager' THEN 'ops-manager' WHEN u->'roles' ? 'ops-staff' THEN 'ops-staff' WHEN u->'roles' ? 'ops-auditor' THEN 'ops-auditor' WHEN u->>'isAdmin'='true' OR u->>'role'='admin' OR u->'roles' ? 'admin' THEN 'ops-manager' END);
 IF u IS NULL OR u->>'isDisabled'='true' OR u->>'isDeleted'='true' OR COALESCE(role_key,'') NOT IN ('super-admin','ops-manager') THEN RAISE EXCEPTION '仅管理角色可以转交复习'; END IF;
END $$;
CREATE OR REPLACE FUNCTION xs_review_target(co text, coach text) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
 PERFORM 1 FROM "_User" u WHERE u."company"=co AND COALESCE(u."legacyUserId"::text,u."legacyUserData"->>'UserID')=coach
 AND COALESCE(to_jsonb(u)->>'identityType','coach')='coach'
 AND COALESCE(to_jsonb(u)->>'legacyGroupId',to_jsonb(u)->>'groupId',u."legacyUserData"->>'GroupID')='3'
 AND COALESCE(to_jsonb(u)->>'isDisabled','false')<>'true' AND COALESCE(to_jsonb(u)->>'isDeleted','false')<>'true' FOR SHARE;
 IF NOT FOUND THEN RAISE EXCEPTION '目标陪练不存在、已停用或身份异常'; END IF;
END $$;
CREATE OR REPLACE FUNCTION xs_review_transfer_snapshot(co text, student text, appointment text, coach text) RETURNS jsonb LANGUAGE sql STABLE AS $$
 SELECT COALESCE(jsonb_agg(jsonb_build_object('taskId',m."objectId",'coachId',m."plid"::text,'version',COALESCE(m."assignmentVersion",0),'updatedAt',m."updatedAt",'plannedDate',m."kywrq",'appointmentId',l."appointmentId") ORDER BY m."objectId"),'[]'::jsonb)
 FROM "MemoryPracticeRecord" m JOIN xs_review_task_link l ON l."company"=m."company" AND l."taskId"=m."objectId"
 WHERE m."company"=co AND m."yhid"::text=student AND COALESCE(m."fxzt"::text,'0')<>'1' AND COALESCE(m."rewardDuplicate",false)=false
 AND (appointment='' OR l."appointmentId"=appointment) AND COALESCE(m."plid"::text,'')<>coach
 AND NOT EXISTS(SELECT 1 FROM "ReviewReward" r WHERE r."company"=co AND r."taskId"=m."objectId");
$$;
CREATE OR REPLACE FUNCTION xs_review_transfer(co text, actor text, student text, appointment text, coach text, expected text, reason text, idem text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE snapshot jsonb; receipt record; fingerprint text; command_id text; task record; result jsonb;
BEGIN
 PERFORM xs_review_manager(co,actor);
 IF student !~ '^[1-9][0-9]*$' OR coach !~ '^[1-9][0-9]*$' OR length(trim(reason))<2 OR length(reason)>1000 OR length(idem)<8 OR length(idem)>120 THEN RAISE EXCEPTION '缺少有效学员、陪练、原因或请求编号'; END IF;
 PERFORM pg_advisory_xact_lock(hashtext('review-transfer:'||co||':'||student));
 fingerprint:=md5(jsonb_build_array(actor,student,appointment,coach,expected,reason)::text);
 SELECT * INTO receipt FROM "ReviewTransferCommand" WHERE "company"=co AND "key"=idem;
 IF FOUND THEN IF receipt."fingerprint"<>fingerprint THEN RAISE EXCEPTION '请求编号已用于其他转交'; END IF; RETURN receipt."result"; END IF;
 PERFORM xs_review_target(co,coach);
 -- Match completion's row lock; re-read versions after a concurrent completion settles.
 PERFORM 1 FROM "MemoryPracticeRecord" m WHERE m."company"=co AND m."yhid"::text=student ORDER BY m."objectId" FOR UPDATE;
 snapshot:=xs_review_transfer_snapshot(co,student,appointment,coach);
 IF md5(snapshot::text)<>expected THEN RAISE EXCEPTION '复习任务已变化，请刷新预览后重试'; END IF;
 IF jsonb_array_length(snapshot)=0 THEN RAISE EXCEPTION '没有可转交的未完成复习'; END IF;
 command_id:=md5(co||':'||idem);
 PERFORM set_config('xiaoshu.review_transfer',command_id,true);
 FOR task IN SELECT value AS data FROM jsonb_array_elements(snapshot) LOOP
  UPDATE "MemoryPracticeRecord" SET "plid"=coach::numeric,"assignmentVersion"=COALESCE("assignmentVersion",0)+1,"updatedAt"=clock_timestamp() WHERE "company"=co AND "objectId"=task.data->>'taskId';
  INSERT INTO "ReviewTransferAudit" ("objectId","company","commandId","taskId","studentId","appointmentId","oldCoachId","newCoachId","oldVersion","newVersion") VALUES
  (md5(command_id||':'||(task.data->>'taskId')),co,command_id,task.data->>'taskId',student,task.data->>'appointmentId',task.data->>'coachId',coach,(task.data->>'version')::numeric,(task.data->>'version')::numeric+1);
 END LOOP;
 PERFORM set_config('xiaoshu.review_transfer','',true);
 result:=jsonb_build_object('commandId',command_id,'count',jsonb_array_length(snapshot),'studentId',student,'coachId',coach);
 INSERT INTO "ReviewTransferCommand" ("objectId","company","actorId","key","fingerprint","reason","result") VALUES(command_id,co,actor,idem,fingerprint,reason,result);
 RETURN result;
END $$;
-- Protect assignments even when a page-triggered legacy refresh raced the transfer.
CREATE OR REPLACE FUNCTION xs_review_assignment_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF COALESCE(current_setting('xiaoshu.review_transfer',true),'')<>'' THEN RETURN NEW; END IF;
 NEW."assignmentVersion":=OLD."assignmentVersion";
 IF COALESCE(OLD."assignmentVersion",0)>0 AND
 (NEW."plid" IS DISTINCT FROM OLD."plid" OR (NEW."fxzt" IS DISTINCT FROM OLD."fxzt" AND COALESCE(current_setting('xiaoshu.review_completion',true),'')<>OLD."company"||':'||OLD."objectId")) THEN
  INSERT INTO "ReviewAssignmentConflict" ("objectId","company","taskId","incoming","preserved") VALUES(md5(OLD."company"||OLD."objectId"||to_jsonb(NEW)::text),OLD."company",OLD."objectId",jsonb_build_object('coachId',NEW."plid",'status',NEW."fxzt"),jsonb_build_object('coachId',OLD."plid",'status',OLD."fxzt")) ON CONFLICT DO NOTHING;
  RETURN OLD;
 END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS a_review_assignment_guard ON "MemoryPracticeRecord";
CREATE TRIGGER a_review_assignment_guard BEFORE UPDATE ON "MemoryPracticeRecord" FOR EACH ROW EXECUTE FUNCTION xs_review_assignment_guard();
-- Check the authenticated actor after taking the same row lock used by transfer.
CREATE OR REPLACE FUNCTION xs_complete_assigned_review(co text, task_id text, actor text) RETURNS void LANGUAGE plpgsql AS $$
DECLARE task record; u jsonb; uid text;
BEGIN
 SELECT to_jsonb(t) INTO u FROM "_User" t WHERE t."company"=co AND t."objectId"=actor;
 uid:=COALESCE(u->>'legacyUserId',u->'legacyUserData'->>'UserID');
 IF u IS NULL OR u->>'isDisabled'='true' OR u->>'isDeleted'='true' THEN RAISE EXCEPTION '账号不可用'; END IF;
 SELECT * INTO task FROM "MemoryPracticeRecord" WHERE "company"=co AND "objectId"=task_id FOR UPDATE;
 IF NOT FOUND THEN RAISE EXCEPTION '复习任务不存在'; END IF;
 IF uid IS NULL OR (uid<>task."plid"::text AND uid<>task."yhid"::text) THEN RAISE EXCEPTION '复习已转交或不属于当前账号，请刷新列表'; END IF;
 PERFORM xiaoshu_complete_review(co,task_id);
END $$;
CREATE INDEX IF NOT EXISTS xs_review_student_pending ON "MemoryPracticeRecord" ("company","yhid","fxzt");
CREATE INDEX IF NOT EXISTS xs_review_transfer_task ON "ReviewTransferAudit" ("company","taskId");
COMMIT;