xiaoshu-app/scripts/sql/review-pay-policy.sql

-- Apply after review-rewards.sql, lesson-records.sql and payroll-integrity.sql.
-- Immutable receipts keep their original amount; historical rate corrections are separate evidence.
BEGIN;
SET LOCAL lock_timeout = '10s';
LOCK TABLE "MemoryPracticeRecord", "ReviewReward" IN SHARE ROW EXCLUSIVE MODE;
ALTER TABLE "ReviewReward" ADD COLUMN IF NOT EXISTS "completedBy" text;
ALTER TABLE "ReviewReward" ADD COLUMN IF NOT EXISTS "completionRole" text;
ALTER TABLE "ReviewReward" ADD COLUMN IF NOT EXISTS "durationMinutes" integer;
ALTER TABLE "ReviewReward" ADD COLUMN IF NOT EXISTS "rateVersion" text;
ALTER TABLE "ReviewReward" DROP CONSTRAINT IF EXISTS "ReviewReward_check";
ALTER TABLE "ReviewReward" ADD CONSTRAINT "ReviewReward_check" CHECK (
 (status='earned' AND amount IN (0.5,1) AND "completedAt" IS NOT NULL AND "coachId">0)
 OR (status<>'earned' AND amount=0));
ALTER TABLE "ReviewReward" DROP CONSTRAINT IF EXISTS review_reward_duration_rate_check;
ALTER TABLE "ReviewReward" ADD CONSTRAINT review_reward_duration_rate_check CHECK (
 "rateVersion" IS DISTINCT FROM 'duration-actor-v2' OR status<>'earned' OR
 ("completionRole" IS NOT NULL AND "completionRole" IN ('coach','historical_verified') AND NULLIF("completedBy",'') IS NOT NULL
 AND "durationMinutes" IS NOT NULL AND (("durationMinutes"=30 AND amount=0.5) OR ("durationMinutes"=60 AND amount=1))));
ALTER TABLE "ReviewRewardCorrection" DROP CONSTRAINT IF EXISTS "ReviewRewardCorrection_amount_check";
ALTER TABLE "ReviewRewardCorrection" ADD CONSTRAINT "ReviewRewardCorrection_amount_check" CHECK(amount IN (-0.5,-1));

-- Resolve the source lesson using the same student and unambiguous study-to-appointment link.
-- An explicit unsupported duration or a missing/conflicting source is never assumed to be 60 minutes.
CREATE OR REPLACE FUNCTION xs_review_duration(co text, task_id text) RETURNS integer LANGUAGE sql STABLE AS $$
 SELECT CASE WHEN count(*)=1 THEN min(CASE
  WHEN a."durationMinutes" IN (30,60) THEN a."durationMinutes"::integer
  WHEN COALESCE(a."durationMinutes",0)=0 THEN CASE a.dslx::text WHEN '1' THEN 30 WHEN '2' THEN 60 WHEN '3' THEN 60 END
 END) END
 FROM xs_review_task_link link
 JOIN "CommonModel" c ON c.company=link.company AND c."modelId"=54 AND c."generalId"::text=link."appointmentId"
 JOIN "CourseAppointment" a ON a.company=c.company AND a.id=c."itemId" AND a.szyh::text=link."studentId"
 WHERE link.company=co AND link."taskId"=task_id
$$;

CREATE OR REPLACE FUNCTION xiaoshu_record_review_reward(task "MemoryPracticeRecord", historical boolean) RETURNS void
LANGUAGE plpgsql AS $$
DECLARE coach record; student record; store record; course text; problem text := ''; finished timestamptz; reward_status text; eligibility text; minutes integer; actor_id text; actor_data jsonb; actor_uid text; actor_role text := 'unknown';
BEGIN
 IF task."company" IS NULL THEN RAISE EXCEPTION '复习任务缺少帐套'; END IF;
 IF EXISTS (SELECT 1 FROM "ReviewReward" WHERE "company"=task."company" AND "taskId"=task."objectId") THEN RETURN; END IF;
 SELECT * INTO coach FROM "_User" WHERE "company"=task."company" AND COALESCE("legacyUserId"::text,"legacyUserData"->>'UserID')=task."plid"::text LIMIT 1;
 SELECT * INTO student FROM "_User" WHERE "company"=task."company" AND COALESCE("legacyUserId"::text,"legacyUserData"->>'UserID')=task."yhid"::text LIMIT 1;
 SELECT * INTO store FROM "_User" WHERE "company"=task."company" AND COALESCE("legacyUserId"::text,"legacyUserData"->>'UserID')=coach."legacyUserData"->>'ParentUserID' LIMIT 1;
 SELECT "nodeName" INTO course FROM "Node" WHERE "company"=task."company" AND "nodeId"::text=task."kcid"::text LIMIT 1;
 BEGIN
   -- The BEFORE trigger overwrites client time on every first live completion.
   finished := NULLIF(task."wcsj"::text,'')::timestamp AT TIME ZONE 'Asia/Shanghai';
 EXCEPTION WHEN OTHERS THEN finished := NULL; END;
 IF task."rewardDuplicate" THEN problem := '来源复习任务重复';
 ELSIF task."rewardTaskKey" IS NULL OR NOT EXISTS (SELECT 1 FROM "CommonModel" WHERE "company"=task."company" AND "modelId"::text='56' AND "generalId"::text=task."xxjlid"::text) THEN problem := '缺少有效来源学习记录或计划日期';
 ELSIF coach."objectId" IS NULL OR COALESCE(task."plid"::text,'0') !~ '^[1-9][0-9]*$' THEN problem := '缺少有效复习老师';
 ELSIF student."objectId" IS NULL THEN problem := '缺少有效学员';
 ELSIF finished IS NULL THEN problem := '缺少有效完成时间'; END IF;
 minutes := xs_review_duration(task.company,task."objectId");
 IF NOT historical AND current_setting('xiaoshu.review_completion',true)=task.company||':'||task."objectId" THEN
  actor_id := NULLIF(current_setting('xiaoshu.review_actor',true),'');
  SELECT to_jsonb(u) INTO actor_data FROM "_User" u WHERE u.company=task.company AND u."objectId"=actor_id;
  actor_uid := COALESCE(actor_data->>'legacyUserId',actor_data->'legacyUserData'->>'UserID');
  IF actor_data IS NOT NULL AND COALESCE(actor_data->>'isDisabled','false')<>'true' AND COALESCE(actor_data->>'isDeleted','false')<>'true' THEN
   IF actor_uid=task.yhid::text THEN actor_role:='student';
   ELSIF actor_uid=task.plid::text AND COALESCE(actor_data->>'identityType','coach')='coach'
    AND COALESCE(actor_data->>'legacyGroupId',actor_data->>'groupId',actor_data->'legacyUserData'->>'GroupID')='3' THEN actor_role:='coach'; END IF;
  END IF;
 END IF;
 IF NOT historical AND actor_role='unknown' THEN problem:='缺少有效实际完成者身份';
 ELSIF NOT historical AND actor_role='coach' AND minutes IS NULL THEN problem:='来源课次时长缺失或冲突，需核对'; END IF;
 eligibility := xiaoshu_review_eligibility(left(task."kywrq"::text,10),finished);
 IF problem='' AND eligibility='invalid_date' THEN problem := '缺少有效计划日期'; END IF;
 reward_status := CASE WHEN NOT historical AND actor_role='student' THEN 'ineligible' WHEN problem<>'' THEN 'exception' WHEN historical THEN 'historical' WHEN eligibility<>'eligible' THEN 'ineligible' ELSE 'earned' END;
 INSERT INTO "ReviewReward" ("objectId","company","taskId","taskKey","coachId","coachName","studentId","studentName","courseName","storeId","storeName","plannedDate","completedAt","incomeMonth","amount","status","exception","ineligibleReason","completedBy","completionRole","durationMinutes","rateVersion")
 VALUES (md5(task."company"||':review:'||task."objectId"),task."company",task."objectId",task."rewardTaskKey",
 CASE WHEN task."plid"::text ~ '^[1-9][0-9]*$' THEN task."plid"::double precision ELSE 0 END,
 COALESCE(NULLIF(coach."realName",''),NULLIF(coach."nickname",''),coach."legacyUserData"->>'HoneyName',coach."username",'未识别老师'),
 CASE WHEN task."yhid"::text ~ '^[1-9][0-9]*$' THEN task."yhid"::double precision ELSE 0 END,
 COALESCE(NULLIF(student."realName",''),NULLIF(student."nickname",''),student."legacyUserData"->>'HoneyName',student."username",'未识别学员'),COALESCE(course,''),
 CASE WHEN coach."legacyUserData"->>'ParentUserID' ~ '^[1-9][0-9]*$' THEN (coach."legacyUserData"->>'ParentUserID')::double precision ELSE 0 END,
 COALESCE(NULLIF(store."realName",''),NULLIF(store."nickname",''),store."legacyUserData"->>'HoneyName',store."username",'未绑定门店'),
 COALESCE(left(task."kywrq"::text,10),''),finished,COALESCE(to_char(finished AT TIME ZONE 'Asia/Shanghai','YYYY-MM'),''),CASE WHEN reward_status='earned' THEN minutes::numeric/60 ELSE 0 END,reward_status,CASE WHEN actor_role='student' THEN '' ELSE problem END,CASE WHEN actor_role='student' THEN 'self_study' WHEN reward_status='ineligible' THEN eligibility ELSE '' END,actor_id,CASE WHEN historical THEN 'historical_unknown' ELSE actor_role END,minutes,'duration-actor-v2')
 ON CONFLICT ("company","taskId") DO NOTHING;
END $$;
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; previous_actor text:=COALESCE(current_setting('xiaoshu.review_actor',true),'');
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;
 IF uid<>task.yhid::text AND (COALESCE(u->>'identityType','coach')<>'coach' OR COALESCE(u->>'legacyGroupId',u->>'groupId',u->'legacyUserData'->>'GroupID','')<>'3') THEN RAISE EXCEPTION '当前账号不是有效陪练'; END IF;
 PERFORM set_config('xiaoshu.review_actor',actor,true);
 PERFORM xiaoshu_complete_review(co,task_id);
 PERFORM set_config('xiaoshu.review_actor',previous_actor,true);
EXCEPTION WHEN OTHERS THEN
 PERFORM set_config('xiaoshu.review_actor',previous_actor,true);
 RAISE;
END $$;
CREATE OR REPLACE FUNCTION xiaoshu_reward_immutable() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_OP='UPDATE' AND current_setting('xiaoshu.review_reward_confirm',true)='yes' AND OLD."status"='historical' AND NEW."status"='earned' AND NEW."amount"=NEW."durationMinutes"::numeric/60 AND NEW."durationMinutes" IN (30,60) AND NEW."rateVersion"='duration-actor-v2' AND NEW."completionRole"='historical_verified' AND NULLIF(NEW."completedBy",'') IS NOT NULL AND (to_jsonb(NEW)-'status'-'amount'-'updatedAt'-'durationMinutes'-'rateVersion'-'completionRole'-'completedBy')=(to_jsonb(OLD)-'status'-'amount'-'updatedAt'-'durationMinutes'-'rateVersion'-'completionRole'-'completedBy') THEN RETURN NEW; END IF;
 RAISE EXCEPTION '抗遗忘工资凭据不可改写或删除，请使用工资调整';
END $$;
CREATE OR REPLACE FUNCTION xs_review_reward_confirm(co text,actor text,ids jsonb,reason text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE reward "ReviewReward"; task "MemoryPracticeRecord"; item text; finished timestamptz; confirmed integer:=0; already integer:=0; minutes integer;
BEGIN
 IF co IS NULL OR actor IS NULL OR length(trim(COALESCE(reason,'')))<2 OR jsonb_typeof(ids)<>'array' OR jsonb_array_length(ids) NOT BETWEEN 1 AND 100 THEN RAISE EXCEPTION '缺少帐套、核对原因或有效凭据'; END IF;
 PERFORM xs_review_manager(co,actor);
 PERFORM pg_advisory_xact_lock(hashtext('xs-payroll:'||co));
 FOR item IN SELECT DISTINCT value FROM jsonb_array_elements_text(ids) LOOP
  SELECT * INTO reward FROM "ReviewReward" WHERE "company"=co AND "objectId"=item FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION '抗遗忘凭据不存在：%',item; END IF;
  IF reward."status"='earned' AND reward."amount" IN (0.5,1) THEN already:=already+1;CONTINUE;END IF;
  IF reward."status"<>'historical' OR reward."amount"<>0 OR COALESCE(reward."exception",'')<>'' THEN RAISE EXCEPTION '凭据不是可核对的历史完成记录：%',item; END IF;
  SELECT * INTO task FROM "MemoryPracticeRecord" WHERE "company"=co AND "objectId"=reward."taskId" FOR UPDATE;
  finished:=xiaoshu_safe_shanghai_timestamp(task."wcsj"::text);
  IF task."objectId" IS NULL OR task."fxzt"::text<>'1' OR (COALESCE(task."sourceKey",'') NOT LIKE 'legacy:model:60:%' AND COALESCE(task."sourceKey",'') NOT LIKE 'legacy-sync:memory-records:addon:%') OR task."rewardDuplicate" IS TRUE OR reward."taskKey" IS NULL OR reward."taskKey"<>xiaoshu_review_key(task."yhid"::text,task."xxjlid"::text,left(task."kywrq"::text,10)) OR finished IS NULL OR xiaoshu_review_eligibility(reward."plannedDate",finished)<>'eligible' OR finished AT TIME ZONE 'Asia/Shanghai'<'2026-09-01'::timestamp OR reward."completedAt" IS DISTINCT FROM finished OR reward."incomeMonth"<>to_char(finished AT TIME ZONE 'Asia/Shanghai','YYYY-MM') OR reward."coachId"::text<>task."plid"::text OR reward."studentId"::text<>task."yhid"::text THEN RAISE EXCEPTION '旧系统复习记录未通过来源、身份或完成时间核对：%',item; END IF;
  IF NOT EXISTS(SELECT 1 FROM "CommonModel" WHERE "company"=co AND "modelId"=56 AND "generalId"::text=task."xxjlid"::text) OR NOT EXISTS(SELECT 1 FROM "_User" WHERE "company"=co AND "legacyUserId"::text=task."plid"::text) OR NOT EXISTS(SELECT 1 FROM "_User" WHERE "company"=co AND "legacyUserId"::text=task."yhid"::text) OR EXISTS(SELECT 1 FROM "ReviewReward" WHERE "company"=co AND "taskKey"=reward."taskKey" AND "status"='earned') THEN RAISE EXCEPTION '旧系统复习记录缺少有效关联或存在重复计薪：%',item; END IF;
  minutes:=xs_review_duration(co,task."objectId");
  IF minutes IS NULL THEN RAISE EXCEPTION '来源课次时长缺失或冲突，不能确认计薪'; END IF;
  PERFORM set_config('xiaoshu.review_reward_confirm','yes',true);
  UPDATE "ReviewReward" SET "status"='earned',"amount"=minutes::numeric/60,"durationMinutes"=minutes,"rateVersion"='duration-actor-v2',"completionRole"='historical_verified',"completedBy"=actor WHERE "company"=co AND "objectId"=item;
  PERFORM set_config('xiaoshu.review_reward_confirm','no',true);
  PERFORM xs_payroll_audit(co,actor,'confirm-legacy-review-reward',item,reason,to_jsonb(reward),to_jsonb(reward)||jsonb_build_object('status','earned','amount',minutes::numeric/60,'durationMinutes',minutes,'rateVersion','duration-actor-v2','completionRole','historical_verified','completedBy',actor));
  confirmed:=confirmed+1;
 END LOOP;
 RETURN jsonb_build_object('confirmed',confirmed,'alreadyConfirmed',already);
END $$;

CREATE TABLE IF NOT EXISTS "ReviewRewardRateCorrection" (
 "objectId" text PRIMARY KEY, company text NOT NULL, "rewardId" text NOT NULL REFERENCES "ReviewReward"("objectId"),
 amount numeric(12,2) NOT NULL CHECK(amount=-0.5), "durationMinutes" integer NOT NULL CHECK("durationMinutes"=30),
 reason text NOT NULL CHECK(length(trim(reason))>=2), actor text NOT NULL, "createdAt" timestamptz NOT NULL DEFAULT now(),
 UNIQUE(company,"rewardId")
);
CREATE OR REPLACE FUNCTION xs_review_rate_correction_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_OP<>'INSERT' OR current_setting('xiaoshu.review_rate_correction',true) IS DISTINCT FROM 'yes' THEN RAISE EXCEPTION '时长纠正凭据只能由核对事务新增'; END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS review_rate_correction_immutable ON "ReviewRewardRateCorrection";
CREATE TRIGGER review_rate_correction_immutable BEFORE INSERT OR UPDATE OR DELETE ON "ReviewRewardRateCorrection" FOR EACH ROW EXECUTE FUNCTION xs_review_rate_correction_guard();
REVOKE ALL ON "ReviewRewardRateCorrection" FROM PUBLIC;
CREATE OR REPLACE VIEW "ReviewRewardIncome" AS
SELECT r."objectId",r.company,r."taskId",r."taskKey",r."coachId",r."coachName",r."studentId",r."studentName",r."courseName",r."storeId",r."storeName",r."plannedDate",r."completedAt",r."incomeMonth",
 (r.amount+COALESCE(c.amount,0)+COALESCE(rc.amount,0))::numeric(12,2) AS amount,
 CASE WHEN c."objectId" IS NULL THEN r.status ELSE 'ineligible' END AS status,
 r.exception,r."createdAt",CASE WHEN c."objectId" IS NULL THEN r."ineligibleReason" ELSE 'corrected_overdue' END AS "ineligibleReason",
 r.amount AS "originalAmount",COALESCE(c."objectId",rc."objectId") AS "correctionId",
 NULLIF(concat_ws('；',rc.reason,c.reason),'') AS "correctionReason",
 CASE WHEN c."objectId" IS NOT NULL OR rc."objectId" IS NOT NULL THEN COALESCE(c.amount,0)+COALESCE(rc.amount,0) END::numeric(12,2) AS "correctionAmount",c."adjustmentBatchId"
FROM "ReviewReward" r
LEFT JOIN "ReviewRewardCorrection" c ON c.company=r.company AND c."rewardId"=r."objectId"
LEFT JOIN "ReviewRewardRateCorrection" rc ON rc.company=r.company AND rc."rewardId"=r."objectId";

-- Deliberately bounded to unbatched receipts: paid or drafted wages need their own settlement adjustment.
CREATE OR REPLACE FUNCTION xs_review_correct_unbatched_rates(co text, actor text, ids jsonb, reason text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE item text; r "ReviewReward"; corrected integer:=0; already integer:=0; previous text:=COALESCE(current_setting('xiaoshu.review_rate_correction',true),'');
BEGIN
 PERFORM xs_review_manager(co,actor);
 IF length(trim(COALESCE(reason,'')))<2 OR jsonb_typeof(ids) IS DISTINCT FROM 'array' OR jsonb_array_length(ids) NOT BETWEEN 1 AND 500 THEN RAISE EXCEPTION '缺少有效核对清单或原因'; END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-payroll:'||co));
 FOR item IN SELECT DISTINCT value FROM jsonb_array_elements_text(ids) ORDER BY value LOOP
  SELECT * INTO r FROM "ReviewReward" WHERE company=co AND "objectId"=item FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION '工资凭据不存在'; END IF;
  IF EXISTS(SELECT 1 FROM "ReviewRewardRateCorrection" WHERE company=co AND "rewardId"=item) THEN already:=already+1;CONTINUE; END IF;
  IF r.status<>'earned' OR r.amount<>1 OR xs_review_duration(co,r."taskId") IS DISTINCT FROM 30 OR EXISTS(SELECT 1 FROM "ReviewRewardCorrection" WHERE company=co AND "rewardId"=item) THEN RAISE EXCEPTION '凭据不符合30分钟时长纠正条件'; END IF;
  IF EXISTS(SELECT 1 FROM "PayrollLine" WHERE company=co AND "reviewRewardId"=item AND NOT COALESCE(excluded,false)) THEN RAISE EXCEPTION '凭据已进入工资单，请单独核对结算调整'; END IF;
  PERFORM set_config('xiaoshu.review_rate_correction','yes',true);
  INSERT INTO "ReviewRewardRateCorrection"("objectId",company,"rewardId",amount,"durationMinutes",reason,actor)
  VALUES(md5(co||':review-rate-v2:'||item),co,item,-0.5,30,reason,actor);
  PERFORM xs_payroll_audit(co,actor,'correct-review-duration',item,reason,to_jsonb(r),jsonb_build_object('amount',0.5,'adjustment',-0.5,'durationMinutes',30));
  corrected:=corrected+1;
 END LOOP;
 PERFORM set_config('xiaoshu.review_rate_correction',previous,true);
 RETURN jsonb_build_object('corrected',corrected,'alreadyCorrected',already,'adjustment',-0.5*corrected);
EXCEPTION WHEN OTHERS THEN
 PERFORM set_config('xiaoshu.review_rate_correction',previous,true);RAISE;
END $$;
REVOKE ALL ON FUNCTION xs_review_correct_unbatched_rates(text,text,jsonb,text) FROM PUBLIC;

CREATE OR REPLACE FUNCTION xs_review_reward_correct(co text,actor text,reward_id text,reason text,target_batch text,expected integer) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE r "ReviewReward"; correction "ReviewRewardCorrection"; source_line "PayrollLine"; source_batch "PayrollBatch"; target_doc "PayrollBatch"; result jsonb; correction_id text:=xs_object_id(); request_id text;
BEGIN
 IF length(trim(COALESCE(reason,'')))<2 THEN RAISE EXCEPTION '请填写工资纠正依据'; END IF;
 IF NOT EXISTS(SELECT 1 FROM "_User" u WHERE u.company=co AND u."objectId"=actor AND NOT COALESCE((to_jsonb(u)->>'isDisabled')::boolean,false) AND NOT COALESCE((to_jsonb(u)->>'isDeleted')::boolean,false) AND (to_jsonb(u)->>'adminRoleKey' IN ('super-admin','ops-manager') OR to_jsonb(u)->'roles' ? 'ops-manager')) THEN RAISE EXCEPTION '仅管理人员可以纠正工资'; END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-payroll:'||co));
 SELECT * INTO correction FROM "ReviewRewardCorrection" WHERE company=co AND "rewardId"=reward_id;
 IF FOUND THEN
  IF correction.reason IS DISTINCT FROM reason OR COALESCE(correction."adjustmentBatchId",'') IS DISTINCT FROM COALESCE(target_batch,'') THEN RAISE EXCEPTION '该工资已纠正，请刷新核对原调整'; END IF;
  RETURN to_jsonb(correction)||jsonb_build_object('unchanged',true);
 END IF;
 SELECT * INTO r FROM "ReviewReward" WHERE company=co AND "objectId"=reward_id FOR UPDATE;
 IF NOT FOUND OR r.status<>'earned' OR r.amount NOT IN (0.5,1) OR xiaoshu_review_eligibility(r."plannedDate",r."completedAt")<>'overdue' THEN RAISE EXCEPTION '仅已核实的逾期错计凭据可纠正'; END IF;
 SELECT amount INTO r.amount FROM "ReviewRewardIncome" WHERE company=co AND "objectId"=reward_id;
 IF r.amount NOT IN (0.5,1) THEN RAISE EXCEPTION '该凭据已无可纠正收入'; END IF;
 SELECT * INTO source_line FROM "PayrollLine" WHERE company=co AND "reviewRewardId"=reward_id AND kind='review_reward' AND NOT COALESCE(excluded,false) FOR UPDATE;
 IF FOUND THEN SELECT * INTO source_batch FROM "PayrollBatch" WHERE company=co AND "objectId"=source_line."batchId" FOR UPDATE; END IF;
 IF source_batch.status IN ('reviewed','paid') THEN
  SELECT * INTO target_doc FROM "PayrollBatch" WHERE company=co AND "objectId"=target_batch FOR UPDATE;
  IF NOT FOUND OR target_doc.status<>'draft' OR target_doc.version IS DISTINCT FROM expected THEN RAISE EXCEPTION '请选择有效草稿工资单并刷新版本'; END IF;
  request_id:='review-correction:'||reward_id;
  result:=xs_payroll_command(co,actor,'adjustment',target_batch,jsonb_build_object('version',expected,'coachId',r."coachId",'amount',-r.amount,'reason','复习工资纠正 ['||reward_id||']：'||reason),request_id);
 ELSE
  IF NULLIF(target_batch,'') IS NOT NULL THEN RAISE EXCEPTION '尚未审核的工资只冲销来源，不应重复扣减结算'; END IF;
  IF source_batch.status='draft' THEN
   PERFORM set_config('xiaoshu.payroll_write','yes',true);
   DELETE FROM "PayrollLine" WHERE company=co AND "objectId"=source_line."objectId";
   UPDATE "PayrollBatch" SET "updatedAt"=now() WHERE company=co AND "objectId"=source_batch."objectId";
  ELSIF source_line."objectId" IS NOT NULL THEN RAISE EXCEPTION '原工资单状态异常，请先核对'; END IF;
 END IF;
 PERFORM set_config('xiaoshu.review_correction','yes',true);
 INSERT INTO "ReviewRewardCorrection"("objectId",company,"rewardId",amount,reason,actor,"sourceBatchId","adjustmentBatchId","adjustmentRequestId")
 VALUES(correction_id,co,reward_id,-r.amount,reason,actor,source_batch."objectId",NULLIF(target_batch,''),request_id) RETURNING * INTO correction;
 PERFORM set_config('xiaoshu.review_correction','no',true);
 PERFORM xs_payroll_audit(co,actor,'correct-review-reward',reward_id,reason,to_jsonb(r),to_jsonb(correction));
 RETURN to_jsonb(correction);
END $$;
CREATE OR REPLACE FUNCTION xiaoshu_payroll_reward_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE b "PayrollBatch"; r "ReviewReward";
BEGIN
 IF current_setting('xiaoshu.payroll_write',true) IS DISTINCT FROM 'yes' THEN RAISE EXCEPTION '工资明细只能通过工资业务事务修改'; END IF;
 IF TG_OP='DELETE' THEN SELECT * INTO b FROM "PayrollBatch" WHERE "company"=OLD."company" AND "objectId"=OLD."batchId" FOR UPDATE;
 ELSE SELECT * INTO b FROM "PayrollBatch" WHERE "company"=NEW."company" AND "objectId"=NEW."batchId" FOR UPDATE; END IF;
 IF b."objectId" IS NULL OR b."status"<>'draft' THEN RAISE EXCEPTION '工资单已锁定或不存在'; END IF;
 IF TG_OP IN ('UPDATE','DELETE') AND EXISTS(SELECT 1 FROM "ReviewRewardCorrection" c WHERE c.company=OLD.company AND OLD."sourceKey"='adjustment:'||c."adjustmentRequestId") THEN RAISE EXCEPTION '工资纠正的调整行不可改写或删除'; END IF;
 IF TG_OP='DELETE' THEN RETURN OLD; END IF;
 IF TG_OP='UPDATE' AND ROW(OLD."company",OLD."batchId",OLD."kind",OLD."reviewRewardId",OLD."standardLessonKey") IS DISTINCT FROM ROW(NEW."company",NEW."batchId",NEW."kind",NEW."reviewRewardId",NEW."standardLessonKey") THEN RAISE EXCEPTION '不能修改工资明细归属'; END IF;
 IF NEW."kind" IS NULL OR NEW."kind" NOT IN ('lesson','review_reward','adjustment') OR NEW."amount" IS NULL OR NEW."amount"::text IN ('NaN','Infinity','-Infinity') OR round(NEW."amount"::numeric,2)<>NEW."amount"::numeric THEN RAISE EXCEPTION '工资类型或金额无效'; END IF;
 IF NEW."kind"='lesson' AND NULLIF(NEW."standardLessonKey",'') IS NULL THEN RAISE EXCEPTION '课次缺少标准标识'; END IF;
 NEW."settlementMonth":=b."month";
 IF NEW."kind"='review_reward' THEN
  SELECT (jsonb_populate_record(NULL::"ReviewReward",to_jsonb(income))).* INTO r FROM "ReviewRewardIncome" income WHERE "company"=b."company" AND "objectId"=NEW."reviewRewardId";
  IF EXISTS(SELECT 1 FROM "ReviewRewardCorrection" WHERE company=b.company AND "rewardId"=r."objectId") THEN RAISE EXCEPTION '复习工资已纠正，不可重新计薪'; END IF;
  IF r."status" IS DISTINCT FROM 'earned' OR r."incomeMonth">b."month" OR NEW."excluded" THEN RAISE EXCEPTION '抗遗忘工资不可结算或排除'; END IF;
  NEW."coachId":=r."coachId";NEW."coachName":=r."coachName";NEW."studentName":=r."studentName";NEW."courseName":=r."courseName";
  NEW."storeId":=r."storeId";NEW."storeName":=r."storeName";NEW."reviewTaskId":=r."taskId";NEW."plannedDate":=r."plannedDate";
  NEW."amount":=r."amount";NEW."rateAmount":=r."amount";NEW."durationHours":=0;NEW."originalMonth":=r."incomeMonth";
  NEW."lessonAt":=r."completedAt";NEW."exception":='';NEW."classType":=0;NEW."courseCategoryName":='抗遗忘工资';
 END IF;
 RETURN NEW;
END $$;
COMMIT;