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')),
  CHECK (("status"='earned' AND "amount"=1 AND "completedAt" IS NOT NULL AND "coachId">0) OR ("status"<>'earned' AND "amount"=0))
);
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;
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
   finished := CASE WHEN historical THEN NULLIF(task."wcsj"::text,'')::timestamp AT TIME ZONE 'Asia/Shanghai' ELSE statement_timestamp() END;
 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;
 reward_status := CASE WHEN problem<>'' THEN 'exception' WHEN historical THEN 'historical' ELSE 'earned' END;
 INSERT INTO "ReviewReward" ("objectId","company","taskId","taskKey","coachId","coachName","studentId","studentName","courseName","storeId","storeName","plannedDate","completedAt","incomeMonth","amount","status","exception")
 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)
 ON CONFLICT ("company","taskId") DO NOTHING;
END $$;
-- Backfill creates zero-value, non-payable evidence; rerunning migration cannot award it.
DO $$ DECLARE task "MemoryPracticeRecord"; BEGIN
 FOR task IN SELECT * FROM "MemoryPracticeRecord" WHERE "fxzt"::text='1' AND "company" IS NOT NULL LOOP
   PERFORM xiaoshu_record_review_reward(task,true);
 END LOOP;
END $$;

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' THEN
     NEW."wcsj" := to_char(statement_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'); 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 $$
BEGIN
 UPDATE "MemoryPracticeRecord" SET "fxzt"='1',"updatedAt"=statement_timestamp() WHERE "company"=tenant AND "objectId"=task_id;
 IF NOT FOUND THEN RAISE EXCEPTION '复习任务不存在'; END IF;
END $$;
CREATE OR REPLACE FUNCTION xiaoshu_reward_immutable() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN 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;