xiaoshu-app/scripts/sql/review-rewards.sql

-- Applied as one transaction before publishing the app/operations gateways.
BEGIN;
LOCK TABLE "MemoryPracticeRecord" IN SHARE ROW EXCLUSIVE MODE;
ALTER TABLE "MemoryPracticeRecord" ADD COLUMN IF NOT EXISTS "rewardTaskKey" text;
ALTER TABLE "MemoryPracticeRecord" ADD COLUMN IF NOT EXISTS "rewardDuplicate" boolean;
ALTER TABLE "PayrollLine" ADD COLUMN IF NOT EXISTS "reviewRewardId" text;
ALTER TABLE "PayrollLine" ADD COLUMN IF NOT EXISTS "reviewTaskId" text;
ALTER TABLE "PayrollLine" ADD COLUMN IF NOT EXISTS "plannedDate" text;
ALTER TABLE "PayrollBatch" ADD COLUMN IF NOT EXISTS "reviewCount" double precision;
ALTER TABLE "PayrollBatch" ADD COLUMN IF NOT EXISTS "reviewAmount" double precision;
CREATE TABLE IF NOT EXISTS "ReviewReward" (
  "objectId" text PRIMARY KEY, "company" text NOT NULL, "taskId" text NOT NULL,
  "taskKey" text, "coachId" double precision NOT NULL DEFAULT 0, "coachName" text NOT NULL DEFAULT '',
  "studentId" double precision NOT NULL DEFAULT 0, "studentName" text NOT NULL DEFAULT '',
  "courseName" text NOT NULL DEFAULT '', "storeId" double precision NOT NULL DEFAULT 0, "storeName" text NOT NULL DEFAULT '',
  "plannedDate" text NOT NULL DEFAULT '', "completedAt" timestamptz, "incomeMonth" text NOT NULL DEFAULT '',
  "amount" numeric(12,2) NOT NULL DEFAULT 0, "status" text NOT NULL,
  "exception" text NOT NULL DEFAULT '', "createdAt" timestamptz NOT NULL DEFAULT now(),
  UNIQUE ("company","taskId"), CHECK ("status" IN ('earned','historical','exception','ineligible')),
  CHECK (("status"='earned' AND "amount"=1 AND "completedAt" IS NOT NULL AND "coachId">0) OR ("status"<>'earned' AND "amount"=0))
);
-- Upgrade the original auto-named constraint without rewriting immutable receipts.
ALTER TABLE "ReviewReward" ADD COLUMN IF NOT EXISTS "ineligibleReason" text NOT NULL DEFAULT '';
ALTER TABLE "ReviewReward" DROP CONSTRAINT IF EXISTS "ReviewReward_status_check";
ALTER TABLE "ReviewReward" ADD CONSTRAINT "ReviewReward_status_check" CHECK (status IN ('earned','historical','exception','ineligible'));
CREATE OR REPLACE FUNCTION xiaoshu_review_eligibility(planned text, finished timestamptz) RETURNS text
LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE day date; actual date := (finished AT TIME ZONE 'Asia/Shanghai')::date;
BEGIN
 IF planned IS NULL OR planned !~ '^\d{4}-\d{2}-\d{2}$' OR finished IS NULL THEN RETURN 'invalid_date'; END IF;
 BEGIN day := planned::date; EXCEPTION WHEN OTHERS THEN RETURN 'invalid_date'; END;
 RETURN CASE WHEN day=actual THEN 'eligible' WHEN day<actual THEN 'overdue' ELSE 'future' END;
END $$;
-- Append-only correction evidence. Receipts and locked payroll documents remain intact.
CREATE TABLE IF NOT EXISTS "ReviewRewardCorrection" (
 "objectId" text PRIMARY KEY, company text NOT NULL, "rewardId" text NOT NULL,
 "amount" numeric(12,2) NOT NULL CHECK(amount=-1), reason text NOT NULL CHECK(length(trim(reason))>=2),
 actor text NOT NULL, "createdAt" timestamptz NOT NULL DEFAULT now(),
 "sourceBatchId" text, "adjustmentBatchId" text, "adjustmentRequestId" text,
 UNIQUE(company,"rewardId"), FOREIGN KEY("rewardId") REFERENCES "ReviewReward"("objectId")
);
-- Preserve every column and correction rule while allowing tenant/month predicates
-- to reach the indexed receipt table before projecting corrected amounts.
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))::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",
  c."objectId" AS "correctionId",
  c.reason AS "correctionReason",
  c.amount AS "correctionAmount",
  c."adjustmentBatchId"
FROM "ReviewReward" r LEFT JOIN "ReviewRewardCorrection" c ON c.company=r.company AND c."rewardId"=r."objectId";
CREATE OR REPLACE FUNCTION xs_review_correction_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_OP<>'INSERT' OR current_setting('xiaoshu.review_correction',true) IS DISTINCT FROM 'yes' THEN RAISE EXCEPTION '工资纠正凭据只能由核对事务新增，不能改写或删除'; END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS review_correction_immutable ON "ReviewRewardCorrection";
CREATE TRIGGER review_correction_immutable BEFORE INSERT OR UPDATE OR DELETE ON "ReviewRewardCorrection" FOR EACH ROW EXECUTE FUNCTION xs_review_correction_guard();
REVOKE ALL ON "ReviewRewardCorrection" FROM PUBLIC;
CREATE UNIQUE INDEX IF NOT EXISTS review_reward_once_per_plan ON "ReviewReward" ("company","taskKey") WHERE "status"='earned';
CREATE INDEX IF NOT EXISTS review_reward_coach_month ON "ReviewReward" ("company","coachId","incomeMonth");
CREATE UNIQUE INDEX IF NOT EXISTS payroll_company_month_once ON "PayrollBatch" ("company","month");
CREATE UNIQUE INDEX IF NOT EXISTS payroll_review_reward_once ON "PayrollLine" ("company","reviewRewardId") WHERE "kind"='review_reward' AND COALESCE("excluded",false)=false;

CREATE OR REPLACE FUNCTION xiaoshu_review_key(student text, study text, day text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$
 SELECT CASE WHEN student ~ '^[1-9][0-9]*$' AND study ~ '^[1-9][0-9]*$' AND day ~ '^\d{4}-\d{2}-\d{2}$'
 THEN student||':'||study||':'||day ELSE NULL END
$$;
-- Preserve duplicate legacy rows, reserve their identity, and flag all copies for review.
WITH ranked AS (
 SELECT "objectId", xiaoshu_review_key("yhid"::text,"xxjlid"::text,left("kywrq"::text,10)) k,
 row_number() OVER (PARTITION BY "company","yhid","xxjlid",left("kywrq"::text,10) ORDER BY "objectId") rn,
 count(*) OVER (PARTITION BY "company","yhid","xxjlid",left("kywrq"::text,10)) copies
 FROM "MemoryPracticeRecord"
)
UPDATE "MemoryPracticeRecord" m SET "rewardTaskKey"=CASE WHEN r.rn=1 THEN r.k END,
 "rewardDuplicate"=(r.copies>1 AND r.k IS NOT NULL)
FROM ranked r WHERE m."objectId"=r."objectId" AND m."rewardDuplicate" IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS memory_review_plan_once ON "MemoryPracticeRecord" ("company","rewardTaskKey") WHERE "rewardTaskKey" IS NOT NULL;

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;
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;
 eligibility := xiaoshu_review_eligibility(left(task."kywrq"::text,10),finished);
 IF problem='' AND eligibility='invalid_date' THEN problem := '缺少有效计划日期'; END IF;
 reward_status := CASE 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")
 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 1 ELSE 0 END,reward_status,problem,CASE WHEN reward_status='ineligible' THEN eligibility ELSE '' END)
 ON CONFLICT ("company","taskId") DO NOTHING;
END $$;
-- Backfill creates zero-value, non-payable evidence; rerunning migration cannot award it.
-- Use one set-based statement so the full history is scanned once instead of issuing
-- several lookup queries for every completed task.
CREATE OR REPLACE FUNCTION xiaoshu_safe_shanghai_timestamp(value text) RETURNS timestamptz
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
 RETURN NULLIF(value,'')::timestamp AT TIME ZONE 'Asia/Shanghai';
EXCEPTION WHEN OTHERS THEN RETURN NULL;
END $$;
WITH users AS MATERIALIZED (
 SELECT DISTINCT ON ("company",COALESCE("legacyUserId"::text,"legacyUserData"->>'UserID'))
  "company",COALESCE("legacyUserId"::text,"legacyUserData"->>'UserID') AS legacy_id,
  "objectId","realName","nickname","username","legacyUserData"
 FROM "_User"
 WHERE COALESCE("legacyUserId"::text,"legacyUserData"->>'UserID') IS NOT NULL
 ORDER BY "company",COALESCE("legacyUserId"::text,"legacyUserData"->>'UserID'),"objectId"
), studies AS MATERIALIZED (
 SELECT DISTINCT "company","generalId"::text AS general_id
 FROM "CommonModel" WHERE "modelId"::text='56'
), courses AS MATERIALIZED (
 SELECT DISTINCT ON ("company","nodeId"::text) "company","nodeId"::text AS node_id,"nodeName"
 FROM "Node" ORDER BY "company","nodeId"::text,"nodeName"
), facts AS (
 SELECT task.*,xiaoshu_safe_shanghai_timestamp(task."wcsj"::text) AS finished,
  coach."objectId" AS coach_object_id,coach."realName" AS coach_real_name,coach."nickname" AS coach_nickname,
  coach."username" AS coach_username,coach."legacyUserData" AS coach_legacy,
  student."objectId" AS student_object_id,student."realName" AS student_real_name,student."nickname" AS student_nickname,
  student."username" AS student_username,student."legacyUserData" AS student_legacy,
  store."realName" AS store_real_name,store."nickname" AS store_nickname,store."username" AS store_username,
  store."legacyUserData" AS store_legacy,study.general_id AS study_general_id,course."nodeName" AS course_name
 FROM "MemoryPracticeRecord" task
 LEFT JOIN users coach ON coach."company"=task."company" AND coach.legacy_id=task."plid"::text
 LEFT JOIN users student ON student."company"=task."company" AND student.legacy_id=task."yhid"::text
 LEFT JOIN users store ON store."company"=task."company" AND store.legacy_id=coach."legacyUserData"->>'ParentUserID'
 LEFT JOIN studies study ON study."company"=task."company" AND study.general_id=task."xxjlid"::text
 LEFT JOIN courses course ON course."company"=task."company" AND course.node_id=task."kcid"::text
 WHERE task."fxzt"::text='1' AND task."company" IS NOT NULL
), prepared AS (
 SELECT facts.*,
  CASE WHEN "rewardDuplicate" THEN '来源复习任务重复'
   WHEN "rewardTaskKey" IS NULL OR study_general_id IS NULL THEN '缺少有效来源学习记录或计划日期'
   WHEN coach_object_id IS NULL OR COALESCE("plid"::text,'0') !~ '^[1-9][0-9]*$' THEN '缺少有效复习老师'
   WHEN student_object_id IS NULL THEN '缺少有效学员'
   WHEN finished IS NULL THEN '缺少有效完成时间' ELSE '' END AS problem
 FROM facts
)
INSERT INTO "ReviewReward" ("objectId","company","taskId","taskKey","coachId","coachName","studentId","studentName","courseName","storeId","storeName","plannedDate","completedAt","incomeMonth","amount","status","exception")
SELECT md5("company"||':review:'||"objectId"),"company","objectId","rewardTaskKey",
 CASE WHEN "plid"::text ~ '^[1-9][0-9]*$' THEN "plid"::double precision ELSE 0 END,
 COALESCE(NULLIF(coach_real_name,''),NULLIF(coach_nickname,''),coach_legacy->>'HoneyName',coach_username,'未识别老师'),
 CASE WHEN "yhid"::text ~ '^[1-9][0-9]*$' THEN "yhid"::double precision ELSE 0 END,
 COALESCE(NULLIF(student_real_name,''),NULLIF(student_nickname,''),student_legacy->>'HoneyName',student_username,'未识别学员'),COALESCE(course_name,''),
 CASE WHEN coach_legacy->>'ParentUserID' ~ '^[1-9][0-9]*$' THEN (coach_legacy->>'ParentUserID')::double precision ELSE 0 END,
 COALESCE(NULLIF(store_real_name,''),NULLIF(store_nickname,''),store_legacy->>'HoneyName',store_username,'未绑定门店'),
 COALESCE(left("kywrq"::text,10),''),finished,COALESCE(to_char(finished AT TIME ZONE 'Asia/Shanghai','YYYY-MM'),''),0,
 CASE WHEN problem='' THEN 'historical' ELSE 'exception' END,problem
FROM prepared
ON CONFLICT ("company","taskId") DO NOTHING;

CREATE OR REPLACE FUNCTION xiaoshu_review_before_write() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_OP='UPDATE' THEN
   IF OLD."company" IS DISTINCT FROM NEW."company" OR OLD."objectId" IS DISTINCT FROM NEW."objectId" THEN RAISE EXCEPTION '不能修改复习任务身份'; END IF;
   IF OLD."rewardTaskKey" IS NOT NULL OR OLD."rewardDuplicate" OR OLD."fxzt"::text='1' THEN
     IF ROW(OLD."yhid",OLD."xxjlid",OLD."kywrq") IS DISTINCT FROM ROW(NEW."yhid",NEW."xxjlid",NEW."kywrq") THEN RAISE EXCEPTION '不能修改复习任务来源'; END IF;
     NEW."rewardTaskKey" := OLD."rewardTaskKey"; NEW."rewardDuplicate" := OLD."rewardDuplicate";
   ELSE
     NEW."rewardTaskKey" := xiaoshu_review_key(NEW."yhid"::text,NEW."xxjlid"::text,left(NEW."kywrq"::text,10)); NEW."rewardDuplicate" := false;
   END IF;
   IF OLD."fxzt"::text='1' THEN
     NEW."fxzt" := OLD."fxzt"; NEW."wcsj" := OLD."wcsj"; NEW."plid" := OLD."plid";
     RETURN NEW;
   END IF;
   IF NEW."fxzt"::text='1' AND (current_setting('xiaoshu.review_completion',true)=NEW."company"||':'||NEW."objectId" OR NOT ((COALESCE(NEW."sourceKey",'') LIKE 'legacy:model:60:%' OR COALESCE(NEW."sourceKey",'') LIKE 'legacy-sync:memory-records:addon:%') AND NULLIF(NEW."wcsj"::text,'') IS NOT NULL)) THEN
     NEW."wcsj" := to_char(clock_timestamp() AT TIME ZONE 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS');
   END IF;
 ELSE
   NEW."rewardTaskKey" := xiaoshu_review_key(NEW."yhid"::text,NEW."xxjlid"::text,left(NEW."kywrq"::text,10)); NEW."rewardDuplicate" := false;
 END IF;
 RETURN NEW;
END $$;
CREATE OR REPLACE FUNCTION xiaoshu_review_after_write() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF NEW."fxzt"::text='1' THEN
   IF TG_OP='INSERT' THEN PERFORM xiaoshu_record_review_reward(NEW,true);
   ELSE PERFORM xiaoshu_record_review_reward(NEW,OLD."fxzt"::text='1' OR (COALESCE(current_setting('xiaoshu.review_completion',true),'')<>NEW."company"||':'||NEW."objectId" AND (COALESCE(NEW."sourceKey",'') LIKE 'legacy:model:60:%' OR COALESCE(NEW."sourceKey",'') LIKE 'legacy-sync:memory-records:addon:%'))); END IF;
 END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS review_reward_before_write ON "MemoryPracticeRecord";
CREATE TRIGGER review_reward_before_write BEFORE INSERT OR UPDATE ON "MemoryPracticeRecord" FOR EACH ROW EXECUTE FUNCTION xiaoshu_review_before_write();
DROP TRIGGER IF EXISTS review_reward_after_write ON "MemoryPracticeRecord";
CREATE TRIGGER review_reward_after_write AFTER INSERT OR UPDATE ON "MemoryPracticeRecord" FOR EACH ROW EXECUTE FUNCTION xiaoshu_review_after_write();

-- The compatibility endpoint calls this, so an unapplied migration fails closed.
CREATE OR REPLACE FUNCTION xiaoshu_complete_review(tenant text, task_id text) RETURNS void LANGUAGE plpgsql AS $$
DECLARE previous text:=COALESCE(current_setting('xiaoshu.review_completion',true),''); task record; eligibility text;
BEGIN
 SELECT * INTO task FROM "MemoryPracticeRecord" WHERE company=tenant AND "objectId"=task_id FOR UPDATE;
 IF NOT FOUND THEN RAISE EXCEPTION '复习任务不存在'; END IF;
 IF task.fxzt::text='1' THEN RETURN; END IF;
 eligibility := xiaoshu_review_eligibility(left(task.kywrq::text,10),clock_timestamp());
 IF eligibility='future' THEN RAISE EXCEPTION '复习任务未到期，不能提前完成'; END IF;
 -- Only this authenticated completion path earns a reward for an imported pending task.
 PERFORM set_config('xiaoshu.review_completion',tenant||':'||task_id,true);
 UPDATE "MemoryPracticeRecord" SET "fxzt"='1',"updatedAt"=clock_timestamp() WHERE "company"=tenant AND "objectId"=task_id;
 IF NOT FOUND THEN RAISE EXCEPTION '复习任务不存在'; END IF;
 PERFORM set_config('xiaoshu.review_completion',previous,true);
EXCEPTION WHEN OTHERS THEN
 PERFORM set_config('xiaoshu.review_completion',previous,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"=1 AND (to_jsonb(NEW)-'status'-'amount'-'updatedAt')=(to_jsonb(OLD)-'status'-'amount'-'updatedAt') THEN RETURN NEW; END IF;
 RAISE EXCEPTION '抗遗忘工资凭据不可改写或删除，请使用工资调整';
END $$;
DROP TRIGGER IF EXISTS review_reward_immutable ON "ReviewReward";
CREATE TRIGGER review_reward_immutable BEFORE UPDATE OR DELETE ON "ReviewReward" FOR EACH ROW EXECUTE FUNCTION xiaoshu_reward_immutable();

CREATE OR REPLACE FUNCTION xiaoshu_payroll_reward_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE batch record; reward record; tenant text; batch_id text;
BEGIN
 tenant := CASE WHEN TG_OP='DELETE' THEN OLD."company" ELSE NEW."company" END;
 batch_id := CASE WHEN TG_OP='DELETE' THEN OLD."batchId" ELSE NEW."batchId" END;
 IF TG_OP='UPDATE' AND ROW(OLD."company",OLD."batchId",OLD."kind",OLD."reviewRewardId") IS DISTINCT FROM ROW(NEW."company",NEW."batchId",NEW."kind",NEW."reviewRewardId") THEN RAISE EXCEPTION '不能修改工资明细归属'; END IF;
 SELECT * INTO batch FROM "PayrollBatch" WHERE "company"=tenant AND "objectId"=batch_id FOR UPDATE;
 IF batch."objectId" IS NULL OR batch."status"<>'draft' THEN RAISE EXCEPTION '工资单已锁定或不存在'; END IF;
 IF TG_OP='DELETE' THEN RETURN OLD; END IF;
 IF NEW."kind"='review_reward' THEN
   SELECT * INTO reward FROM "ReviewReward" WHERE "company"=tenant AND "objectId"=NEW."reviewRewardId";
   IF reward."status" IS DISTINCT FROM 'earned' OR reward."incomeMonth">batch."month" THEN RAISE EXCEPTION '抗遗忘工资不可结算'; END IF;
   IF NEW."excluded" THEN RAISE EXCEPTION '有效抗遗忘工资不能排除，请使用工资调整'; END IF;
   NEW."coachId" := reward."coachId"; NEW."coachName" := reward."coachName";
   NEW."studentName" := reward."studentName"; NEW."courseName" := reward."courseName";
   NEW."storeId" := reward."storeId"; NEW."storeName" := reward."storeName";
   NEW."reviewTaskId" := reward."taskId"; NEW."plannedDate" := reward."plannedDate";
   NEW."amount" := reward."amount"; NEW."rateAmount" := reward."amount"; NEW."durationHours" := 0;
   NEW."originalMonth" := reward."incomeMonth"; NEW."settlementMonth" := batch."month"; NEW."lessonAt" := reward."completedAt";
   NEW."exception" := ''; NEW."classType" := 0; NEW."courseCategoryName" := '抗遗忘工资';
 END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS payroll_reward_guard ON "PayrollLine";
CREATE TRIGGER payroll_reward_guard BEFORE INSERT OR UPDATE OR DELETE ON "PayrollLine" FOR EACH ROW EXECUTE FUNCTION xiaoshu_payroll_reward_guard();
CREATE OR REPLACE FUNCTION xiaoshu_payroll_totals_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF OLD."status" IN ('reviewed','paid') THEN
   IF NEW."status" IS DISTINCT FROM OLD."status" AND NOT (OLD."status"='reviewed' AND NEW."status"='paid') THEN RAISE EXCEPTION '已审核工资不可重新打开'; END IF;
   IF ROW(NEW."baseAmount",NEW."reviewAmount",NEW."adjustmentAmount",NEW."payableAmount",NEW."month") IS DISTINCT FROM ROW(OLD."baseAmount",OLD."reviewAmount",OLD."adjustmentAmount",OLD."payableAmount",OLD."month") THEN RAISE EXCEPTION '已审核工资金额不可修改'; END IF;
   RETURN NEW;
 END IF;
 SELECT COALESCE(sum("amount") FILTER (WHERE "kind"='lesson'),0),COALESCE(sum("amount") FILTER (WHERE "kind"='review_reward'),0),COALESCE(sum("amount") FILTER (WHERE "kind"='adjustment'),0),count(*) FILTER (WHERE "kind"='review_reward'),count(*) FILTER (WHERE "kind"='lesson'),COALESCE(sum("durationHours") FILTER (WHERE "kind"='lesson'),0)
 INTO NEW."baseAmount",NEW."reviewAmount",NEW."adjustmentAmount",NEW."reviewCount",NEW."lessonCount",NEW."totalHours"
 FROM "PayrollLine" WHERE "company"=NEW."company" AND "batchId"=NEW."objectId" AND COALESCE("excluded",false)=false AND COALESCE("exception",'')='';
 NEW."payableAmount" := NEW."baseAmount"+NEW."reviewAmount"+NEW."adjustmentAmount";
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS payroll_totals_guard ON "PayrollBatch";
CREATE TRIGGER payroll_totals_guard BEFORE UPDATE ON "PayrollBatch" FOR EACH ROW EXECUTE FUNCTION xiaoshu_payroll_totals_guard();
CREATE OR REPLACE FUNCTION xiaoshu_payroll_delete_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF OLD."status" IN ('reviewed','paid') OR EXISTS (SELECT 1 FROM "PayrollLine" WHERE "company"=OLD."company" AND "batchId"=OLD."objectId") THEN RAISE EXCEPTION '工资批次已锁定或仍有明细，不能删除'; END IF;
 RETURN OLD;
END $$;
DROP TRIGGER IF EXISTS payroll_delete_guard ON "PayrollBatch";
CREATE TRIGGER payroll_delete_guard BEFORE DELETE ON "PayrollBatch" FOR EACH ROW EXECUTE FUNCTION xiaoshu_payroll_delete_guard();
COMMIT;