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.
-- 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 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(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' OR 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 $$
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
 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;