xiaoshu-app/scripts/sql/payroll-integrity.sql

-- Requires member-enrollment.sql and review-rewards.sql. One atomic installation.
BEGIN;
SELECT set_config('xiaoshu.payroll_write','yes',true);
LOCK TABLE "PayrollBatch", "PayrollLine" IN SHARE ROW EXCLUSIVE MODE;
CREATE INDEX IF NOT EXISTS payroll_common_item ON "CommonModel" ("company","modelId","itemId","objectId");
CREATE INDEX IF NOT EXISTS payroll_common_general_text ON "CommonModel" ("company","modelId",("generalId"::text));
CREATE INDEX IF NOT EXISTS payroll_appointment_item ON "CourseAppointment" ("company","id");
ALTER TABLE "PayrollLine" ADD COLUMN IF NOT EXISTS "standardLessonKey" text;
ALTER TABLE "PayrollLine" ADD COLUMN IF NOT EXISTS "coachObjectId" text;
ALTER TABLE "PayrollLine" ADD COLUMN IF NOT EXISTS "coachMobile" text;
ALTER TABLE "PayrollBatch" ADD COLUMN IF NOT EXISTS "version" integer NOT NULL DEFAULT 0;
ALTER TABLE "PayrollBatch" ALTER COLUMN "version" SET DEFAULT 0;
UPDATE "PayrollBatch" SET "version"=0 WHERE "version" IS NULL;
ALTER TABLE "PayrollBatch" ALTER COLUMN "version" SET NOT NULL;
ALTER TABLE "TeachingCompensationRule" ADD COLUMN IF NOT EXISTS "revision" integer NOT NULL DEFAULT 1;
ALTER TABLE "TeachingCompensationRule" ADD COLUMN IF NOT EXISTS "supersedesId" text;
ALTER TABLE "TeachingCompensationRule" ALTER COLUMN "revision" SET DEFAULT 1;
ALTER TABLE "CourseAppointment" ADD COLUMN IF NOT EXISTS "categoryEvidence" text;
CREATE TABLE IF NOT EXISTS "PayrollCommand" (
 "company" text NOT NULL,"requestId" text NOT NULL,"request" jsonb NOT NULL,"result" jsonb NOT NULL,
 "createdAt" timestamptz NOT NULL DEFAULT now(),PRIMARY KEY("company","requestId")
);
CREATE TABLE IF NOT EXISTS "PayrollReconciliation" (
 "company" text NOT NULL,"appointmentId" text NOT NULL,"amount" numeric(12,2) NOT NULL,
 "transactionId" text NOT NULL,"createdAt" timestamptz NOT NULL DEFAULT now(),PRIMARY KEY("company","appointmentId")
);
CREATE OR REPLACE FUNCTION xs_payroll_time(v text) RETURNS timestamptz LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
 IF NULLIF(trim(v),'') IS NULL THEN RETURN NULL; END IF;
 IF v ~ '(Z|[+-][0-9]{2}(:?[0-9]{2})?)$' AND v ~ '[ T]' THEN RETURN v::timestamptz; END IF;
 RETURN v::timestamp AT TIME ZONE 'Asia/Shanghai';
EXCEPTION WHEN OTHERS THEN RETURN NULL;
END $$;

-- Canonical completion records are authoritative, including tombstones. Facts only
-- supplement records absent from the canonical table; a deletion cannot reappear.
CREATE OR REPLACE FUNCTION xs_payroll_sources(co text) RETURNS SETOF jsonb LANGUAGE sql STABLE AS $$
 WITH canonical AS (
 SELECT (to_jsonb(l)-'kzsj')||jsonb_build_object(
  'lessonObjectId',l."objectId",'legacyGeneralId',COALESCE(c."generalId",l."legacyGeneralId",0),
  'standardLessonKey',CASE WHEN COALESCE(c."generalId",l."legacyGeneralId",0)>0 THEN 'legacy:'||COALESCE(c."generalId",l."legacyGeneralId")::text ELSE 'lesson:'||l."objectId" END,
  'source','canonical','commonStatus',COALESCE(c."status",99),
  'lessonAt',COALESCE(l."lessonAt",xs_payroll_time(l."plpjsj"),xs_payroll_time(a."jssj"),l."sourceCreatedAt",c."createdAt"),
  'courseCategoryKey',COALESCE(NULLIF(l."courseCategoryKey",''),a."courseCategoryKey"),
  'courseCategoryName',COALESCE(NULLIF(l."courseCategoryName",''),a."courseCategoryName"),
  'teacherPaySnapshot',COALESCE(l."teacherPaySnapshot",a."teacherPaySnapshot"),
  'durationMinutes',a."durationMinutes",'deliveryMode',COALESCE(NULLIF(l."jffs",''),a."deliveryMode",a."jffs"),
  'teachingRuleId',a."teachingRuleId",'studentName',COALESCE(NULLIF(l."studentName",''),c."title"),
  'courseName',COALESCE(NULLIF(l."courseName",''),NULLIF(l."kcmc",''),c."subtitle")
 ) AS row_data
 FROM "LessonRecord" l
 LEFT JOIN LATERAL (SELECT * FROM "CommonModel" c WHERE c."company"=co AND c."modelId"=59 AND c."itemId"=l."id" ORDER BY c."objectId" LIMIT 1) c ON true
 LEFT JOIN LATERAL (SELECT a.* FROM "CourseAppointment" a JOIN "CommonModel" ac ON ac."company"=co AND ac."modelId"=54 AND ac."itemId"=a."id" WHERE a."company"=co AND ac."generalId"::text=l."yyds"::text ORDER BY ac."objectId" LIMIT 1) a ON true
 WHERE l."company"=co
 ), fallback AS (
 SELECT (to_jsonb(f)-'kzsj')||jsonb_build_object('lessonObjectId',f."objectId",'source','historical-fact','commonStatus',99,'courseCategoryKey',a."courseCategoryKey",'courseCategoryName',a."courseCategoryName",'teacherPaySnapshot',a."teacherPaySnapshot",'durationMinutes',a."durationMinutes",'deliveryMode',COALESCE(NULLIF(f."jffs",''),a."deliveryMode",a."jffs"),'teachingRuleId',a."teachingRuleId",
  'standardLessonKey',CASE WHEN COALESCE(f."legacyGeneralId",0)>0 THEN 'legacy:'||f."legacyGeneralId"::text ELSE 'fact:'||f."objectId" END) AS row_data
 FROM "LessonRecordFact" f LEFT JOIN LATERAL (SELECT a.* FROM "CourseAppointment" a JOIN "CommonModel" ac ON ac."company"=co AND ac."modelId"=54 AND ac."itemId"=a."id" WHERE a."company"=co AND ac."generalId"::text=f."yyds"::text ORDER BY ac."objectId" LIMIT 1) a ON true WHERE f."company"=co AND NOT EXISTS(SELECT 1 FROM canonical c WHERE c.row_data->>'legacyGeneralId'=f."legacyGeneralId"::text AND COALESCE(f."legacyGeneralId",0)>0)
 ) SELECT row_data FROM canonical UNION ALL SELECT row_data FROM fallback
$$;
CREATE OR REPLACE FUNCTION xs_payroll_source_version(co text) RETURNS text LANGUAGE sql STABLE AS $$
 SELECT md5(COALESCE((SELECT string_agg(s::text,'|' ORDER BY s::text) FROM xs_payroll_sources(co) s),'')||
 COALESCE((SELECT string_agg(to_jsonb(r)::text,'|' ORDER BY r."objectId") FROM "TeachingCompensationRule" r WHERE r."company"=co),'')||
 COALESCE((SELECT string_agg(to_jsonb(p)::text,'|' ORDER BY p."objectId") FROM "CourseTeachingProfile" p WHERE p."company"=co),'')||
 COALESCE((SELECT string_agg(to_jsonb(p)::text,'|' ORDER BY p."objectId") FROM "CoachPayRate" p WHERE p."company"=co),'')||
 COALESCE((SELECT string_agg(to_jsonb(r)::text,'|' ORDER BY r."objectId") FROM "ReviewReward" r WHERE r."company"=co AND r."status"='earned'),''))
$$;
CREATE OR REPLACE FUNCTION xs_payroll_audit(co text,actor text,action text,target text,reason text,before_data jsonb,after_data jsonb) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
 PERFORM xs_insert('SysLog',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),
 'sourceKey','payroll-audit:'||xs_object_id(),'id',nextval('xiaoshu_business_id'),'cdate',now(),'cname','工资结算',
 'type1','operations-admin','type2',action,'type3','PayrollBatch','clevel',0,'cadminId',(SELECT "legacyUserId" FROM "_User" WHERE "objectId"=actor),
 'detail',jsonb_build_object('action',action,'target',target,'actor',actor,'reason',reason,'before',before_data,'after',after_data)::text,'content1',reason,'content2',actor));
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;
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 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"=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 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;
  PERFORM set_config('xiaoshu.review_reward_confirm','yes',true);
  UPDATE "ReviewReward" SET "status"='earned',"amount"=1 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',1));
  confirmed:=confirmed+1;
 END LOOP;
 RETURN jsonb_build_object('confirmed',confirmed,'alreadyConfirmed',already);
END $$;

DROP TRIGGER IF EXISTS payroll_reward_guard ON "PayrollLine";
UPDATE "PayrollLine" SET "standardLessonKey"=CASE WHEN COALESCE("legacyGeneralId",0)>0 THEN 'legacy:'||"legacyGeneralId"::text ELSE 'lesson:'||"lessonId" END
 WHERE "kind"='lesson' AND "standardLessonKey" IS NULL;
-- Deliberately fails on conflicting history. Never erase or silently exclude paid lines.
CREATE UNIQUE INDEX IF NOT EXISTS payroll_lesson_once ON "PayrollLine" ("company","standardLessonKey") WHERE "kind"='lesson' AND COALESCE("excluded",false)=false;
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='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 * INTO r FROM "ReviewReward" WHERE "company"=b."company" AND "objectId"=NEW."reviewRewardId";
  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 $$;
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 xs_payroll_batch_write_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF current_setting('xiaoshu.payroll_write',true) IS DISTINCT FROM 'yes' THEN RAISE EXCEPTION '工资批次只能通过工资业务事务修改'; END IF;
 IF TG_OP='INSERT' AND NEW."status" IS DISTINCT FROM 'draft' THEN RAISE EXCEPTION '工资批次必须从草稿开始'; END IF;
 IF TG_OP='DELETE' THEN RETURN OLD;END IF;RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS payroll_batch_write_guard ON "PayrollBatch";
CREATE TRIGGER payroll_batch_write_guard BEFORE INSERT OR UPDATE OR DELETE ON "PayrollBatch" FOR EACH ROW EXECUTE FUNCTION xs_payroll_batch_write_guard();
CREATE OR REPLACE FUNCTION xiaoshu_payroll_totals_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF current_setting('xiaoshu.payroll_write',true) IS DISTINCT FROM 'yes' THEN RAISE EXCEPTION '工资批次只能通过工资业务事务修改'; END IF;
 IF NEW."company" IS DISTINCT FROM OLD."company" OR NEW."month" IS DISTINCT FROM OLD."month" THEN RAISE EXCEPTION '不能修改工资批次归属'; END IF;
 IF OLD."status" IN ('reviewed','paid') THEN
  IF NOT (NEW."status"=OLD."status" OR OLD."status"='reviewed' AND NEW."status"='paid') THEN RAISE EXCEPTION '已审核工资不可重新打开'; END IF;
  IF ROW(NEW."baseAmount",NEW."reviewAmount",NEW."adjustmentAmount",NEW."payableAmount",NEW."lessonCount",NEW."reviewCount",NEW."coachCount",NEW."exceptionCount",NEW."totalHours") IS DISTINCT FROM ROW(OLD."baseAmount",OLD."reviewAmount",OLD."adjustmentAmount",OLD."payableAmount",OLD."lessonCount",OLD."reviewCount",OLD."coachCount",OLD."exceptionCount",OLD."totalHours") THEN RAISE EXCEPTION '已审核工资金额不可修改'; END IF;
 ELSE
  IF NEW."status" NOT IN ('draft','reviewed') THEN RAISE EXCEPTION '工资状态转换无效'; END IF;
  SELECT COALESCE(sum("amount"::numeric) FILTER(WHERE "kind"='lesson'),0),COALESCE(sum("amount"::numeric) FILTER(WHERE "kind"='review_reward'),0),COALESCE(sum("amount"::numeric) FILTER(WHERE "kind"='adjustment'),0),count(*) FILTER(WHERE "kind"='lesson'),count(*) FILTER(WHERE "kind"='review_reward'),count(DISTINCT "coachId"),COALESCE(sum("durationHours") FILTER(WHERE "kind"='lesson'),0)
  INTO NEW."baseAmount",NEW."reviewAmount",NEW."adjustmentAmount",NEW."lessonCount",NEW."reviewCount",NEW."coachCount",NEW."totalHours"
  FROM "PayrollLine" WHERE "company"=NEW."company" AND "batchId"=NEW."objectId" AND NOT COALESCE("excluded",false) AND COALESCE("exception",'')='';
  SELECT count(*) INTO NEW."exceptionCount" FROM "PayrollLine" WHERE "company"=NEW."company" AND "batchId"=NEW."objectId" AND NOT COALESCE("excluded",false) AND COALESCE("exception",'')<>'';
  NEW."payableAmount":=NEW."baseAmount"+NEW."reviewAmount"+NEW."adjustmentAmount";
  IF NEW."status"='reviewed' AND NEW."exceptionCount">0 THEN RAISE EXCEPTION '仍有异常明细，不能审核'; END IF;
 END IF;
 IF NEW."status"='paid' AND (NULLIF(trim(NEW."paymentReference"),'') IS NULL OR NULLIF(trim(NEW."paymentMethod"),'') IS NULL) THEN RAISE EXCEPTION '发放方式和凭证编号必填'; END IF;
 NEW."version":=COALESCE(OLD."version",0)+1;NEW."updatedAt":=clock_timestamp();
 RETURN NEW;
END $$;

CREATE OR REPLACE FUNCTION xs_payroll_command(co text,actor text,op text,target text,req jsonb,idem text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE b "PayrollBatch"; previous jsonb; saved "PayrollCommand"; line jsonb; candidate "PayrollLine"; key text; result jsonb; u "_User"; st "_User";
BEGIN
 IF co IS NULL OR length(COALESCE(idem,''))<8 OR length(trim(COALESCE(req->>'reason','')))<2 THEN RAISE EXCEPTION '缺少帐套、操作原因或请求编号'; END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-payroll:'||co));
 PERFORM set_config('xiaoshu.payroll_write','yes',true);
 req:=req||jsonb_build_object('actor',actor,'operation',op,'target',target);
 SELECT * INTO saved FROM "PayrollCommand" WHERE "company"=co AND "requestId"=idem;
 IF FOUND THEN IF saved."request" IS DISTINCT FROM (req-'lines'-'sourceVersion') THEN RAISE EXCEPTION '请求编号已用于不同操作'; END IF;RETURN saved."result";END IF;
 IF op='create' THEN
  IF COALESCE(req->>'month','') !~ '^\d{4}-(0[1-9]|1[0-2])$' THEN RAISE EXCEPTION '工资月份无效'; END IF;
  INSERT INTO "PayrollBatch" ("objectId","company","sourceKey","month","status","createdAt","updatedAt","createdBy") VALUES(xs_object_id(),co,'payroll:'||co||':'||(req->>'month'),req->>'month','draft',now(),now(),actor) RETURNING * INTO b;
 ELSE
  SELECT * INTO b FROM "PayrollBatch" WHERE "company"=co AND "objectId"=target FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION '工资单不存在'; END IF;
  IF req->>'version' IS NULL OR (req->>'version')::int<>b."version" THEN RAISE EXCEPTION '工资单已更新，请刷新后重试'; END IF;
 END IF;
 previous:=to_jsonb(b);
 IF op IN ('create','refresh','review') THEN
  IF b."status"<>'draft' THEN RAISE EXCEPTION '工资单已锁定'; END IF;
  IF req->>'sourceVersion' IS DISTINCT FROM xs_payroll_source_version(co) THEN RAISE EXCEPTION '计薪来源已更新，请重新核对'; END IF;
  -- Only automated lines are rebuilt; explicit exclusions and adjustments survive.
  DELETE FROM "PayrollLine" WHERE "company"=co AND "batchId"=b."objectId" AND "kind" IN ('lesson','review_reward') AND NOT COALESCE("excluded",false);
  FOR line IN SELECT value FROM jsonb_array_elements(COALESCE(req->'lines','[]')) LOOP
   IF line->>'kind' NOT IN ('lesson','review_reward') THEN RAISE EXCEPTION '自动明细类型无效'; END IF;
   IF line->>'kind'='lesson' AND EXISTS(SELECT 1 FROM "PayrollLine" WHERE "company"=co AND "batchId"=b."objectId" AND "standardLessonKey"=line->>'standardLessonKey' AND COALESCE("excluded",false)) THEN CONTINUE; END IF;
   line:=line||jsonb_build_object('objectId',xs_object_id(),'company',co,'batchId',b."objectId",'settlementMonth',b."month",'createdAt',now(),'updatedAt',now(),'excluded',false,'sourceKey','payroll-line:'||xs_object_id());
   candidate:=jsonb_populate_record(NULL::"PayrollLine",line); INSERT INTO "PayrollLine" SELECT candidate.*;
  END LOOP;
  IF op='review' THEN b."status":='reviewed'; END IF;
 ELSIF op='adjustment' THEN
  IF b."status"<>'draft' THEN RAISE EXCEPTION '只有草稿可以调整'; END IF;
  IF (req->>'amount')::numeric=0 OR abs((req->>'amount')::numeric)>1000000000 OR round((req->>'amount')::numeric,2)<>(req->>'amount')::numeric THEN RAISE EXCEPTION '调整金额无效，最多两位小数'; END IF;
  SELECT * INTO u FROM "_User" WHERE "company"=co AND COALESCE("legacyUserId"::text,"legacyUserData"->>'UserID')=req->>'coachId' AND ("identityType"='coach' OR "identityType" IS NULL AND COALESCE("legacyGroupId"::text,"legacyUserData"->>'GroupID')='3');
  IF NOT FOUND THEN RAISE EXCEPTION '陪练不存在'; END IF;
  SELECT * INTO st FROM "_User" WHERE "company"=co AND COALESCE("legacyUserId"::text,"legacyUserData"->>'UserID')=u."legacyUserData"->>'ParentUserID' LIMIT 1;
  INSERT INTO "PayrollLine" ("objectId","company","batchId","sourceKey","kind","coachId","coachObjectId","coachName","storeId","storeName","amount","rateAmount","durationHours","originalMonth","settlementMonth","description","exception","createdAt","updatedAt")
  VALUES(xs_object_id(),co,b."objectId",'adjustment:'||idem,'adjustment',(req->>'coachId')::numeric,u."objectId",COALESCE(NULLIF(u."realName",''),NULLIF(u."nickname",''),u."username"),COALESCE(st."legacyUserId",0),COALESCE(NULLIF(st."realName",''),NULLIF(st."nickname",''),st."username",'未绑定门店'),(req->>'amount')::numeric,0,0,b."month",b."month",req->>'reason','',now(),now());
 ELSIF op='exclude' THEN
  UPDATE "PayrollLine" SET "excluded"=true,"exclusionReason"=req->>'reason',"updatedAt"=now() WHERE "company"=co AND "batchId"=b."objectId" AND "objectId"=req->>'lineId' AND "kind"='lesson' AND COALESCE("exception",'')<>'';
  IF NOT FOUND THEN RAISE EXCEPTION '只能排除异常课次'; END IF;
 ELSIF op='mark-paid' THEN
  IF b."status"<>'reviewed' THEN RAISE EXCEPTION '只有已审核工资单可登记发放'; END IF;b."status":='paid';
 ELSE RAISE EXCEPTION '工资操作无效'; END IF;
 UPDATE "PayrollBatch" SET "status"=b."status","sourceRefreshedAt"=CASE WHEN op IN ('create','refresh','review') THEN now() ELSE "sourceRefreshedAt" END,
 "reviewedAt"=CASE WHEN op='review' THEN now() ELSE "reviewedAt" END,"reviewedBy"=CASE WHEN op='review' THEN actor ELSE "reviewedBy" END,
 "paidAt"=CASE WHEN op='mark-paid' THEN now() ELSE "paidAt" END,"paidBy"=CASE WHEN op='mark-paid' THEN actor ELSE "paidBy" END,
 "paymentMethod"=CASE WHEN op='mark-paid' THEN req->>'paymentMethod' ELSE "paymentMethod" END,"paymentReference"=CASE WHEN op='mark-paid' THEN req->>'paymentReference' ELSE "paymentReference" END
 WHERE "company"=co AND "objectId"=b."objectId" RETURNING to_jsonb("PayrollBatch".*) INTO result;
 PERFORM xs_payroll_audit(co,actor,op,b."objectId",req->>'reason',previous,result);
 INSERT INTO "PayrollCommand" ("company","requestId","request","result") VALUES(co,idem,req-'lines'-'sourceVersion',result);
 RETURN result;
END $$;

CREATE OR REPLACE FUNCTION xs_payroll_rule(co text,actor text,req jsonb) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE r "TeachingCompensationRule"; old "TeachingCompensationRule"; rev integer; result jsonb;
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-payroll:'||co));
 SELECT * INTO old FROM "TeachingCompensationRule" WHERE "company"=co AND "categoryKey"=req->>'categoryKey' AND "durationMinutes"=(req->>'durationMinutes')::numeric AND "deliveryMode"=req->>'deliveryMode' AND "effectiveFrom"=req->>'effectiveFrom' ORDER BY "revision" DESC,"createdAt" DESC LIMIT 1;
 IF COALESCE(old."objectId",'')<>COALESCE(req->>'supersedesId','') THEN RAISE EXCEPTION '工资规则已有新版本，请刷新'; END IF;
 rev:=COALESCE(old."revision",0)+1;
 r:=jsonb_populate_record(NULL::"TeachingCompensationRule",req||jsonb_build_object('objectId',xs_object_id(),'company',co,'sourceKey','payroll-rule:'||xs_object_id(),'createdAt',now(),'updatedAt',now(),'revision',rev,'supersedesId',old."objectId"));
 INSERT INTO "TeachingCompensationRule" SELECT r.*;
 result:=to_jsonb(r);PERFORM xs_payroll_audit(co,actor,'save-rule',r."objectId",req->>'reason',to_jsonb(old),result);RETURN result;
END $$;
CREATE OR REPLACE FUNCTION xs_payroll_rule_immutable() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN RAISE EXCEPTION '工资规则版本不可改写，请保存新版本';END $$;
DROP TRIGGER IF EXISTS payroll_rule_immutable ON "TeachingCompensationRule";
CREATE TRIGGER payroll_rule_immutable BEFORE UPDATE OR DELETE ON "TeachingCompensationRule" FOR EACH ROW EXECUTE FUNCTION xs_payroll_rule_immutable();

-- Keep the legacy class-type endpoint compatible without allowing old revisions
-- to be overwritten or a successful write to escape the transaction audit.
CREATE OR REPLACE FUNCTION xs_payroll_legacy_rate(co text,actor text,req jsonb) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE r "CoachPayRate"; old "CoachPayRate";
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-payroll:'||co));
 SELECT * INTO old FROM "CoachPayRate" WHERE "company"=co AND "classType"=(req->>'classType')::numeric AND "effectiveFrom"=req->>'effectiveFrom' ORDER BY "createdAt" DESC,"objectId" DESC LIMIT 1;
 r:=jsonb_populate_record(NULL::"CoachPayRate",req||jsonb_build_object('objectId',xs_object_id(),'company',co,'sourceKey','payroll-legacy-rate:'||xs_object_id(),'createdAt',clock_timestamp(),'updatedAt',clock_timestamp()));
 INSERT INTO "CoachPayRate" SELECT r.*;
 PERFORM xs_payroll_audit(co,actor,'save-legacy-rate',r."objectId",req->>'reason',to_jsonb(old),to_jsonb(r));RETURN to_jsonb(r);
END $$;
DROP TRIGGER IF EXISTS payroll_legacy_rate_immutable ON "CoachPayRate";
CREATE TRIGGER payroll_legacy_rate_immutable BEFORE UPDATE OR DELETE ON "CoachPayRate" FOR EACH ROW EXECUTE FUNCTION xs_payroll_rule_immutable();

CREATE OR REPLACE FUNCTION xs_payroll_reservations(co text,actor text,items jsonb,reason text,source_version text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE item jsonb;c "CommonModel";a "CourseAppointment";u "_User";cost jsonb;reserved integer=0;held integer=0;status text;why text;costs jsonb;existing "CourseCreditReservation";
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 IF source_version IS DISTINCT FROM xs_payroll_source_version(co) THEN RAISE EXCEPTION '工资或课程规则已更新，请重新核对'; END IF;
 IF jsonb_array_length(items)=0 THEN RAISE EXCEPTION '请选择预约';END IF;
 FOR item IN SELECT value FROM jsonb_array_elements(items) LOOP
  SELECT * INTO c FROM "CommonModel" WHERE "company"=co AND "objectId"=item->>'appointmentId' AND "modelId"=54 FOR UPDATE;
  IF NOT FOUND OR c."status"=-2 THEN RAISE EXCEPTION '预约已取消或不存在，请重新核对';END IF;
  SELECT * INTO a FROM "CourseAppointment" WHERE "company"=co AND "id"::text=c."itemId"::text FOR UPDATE;
  IF a."updatedAt" IS DISTINCT FROM (item->>'expectedUpdatedAt')::timestamptz OR a."dszt" NOT IN ('0','10') THEN RAISE EXCEPTION '预约已更新，请重新核对';END IF;
  IF a."dszt"='0' AND xs_payroll_time(a."yysj")<now() THEN RAISE EXCEPTION '预约已经到期，请重新核对';END IF;
  SELECT * INTO existing FROM "CourseCreditReservation" WHERE "company"=co AND "appointmentId"=c."objectId" AND "state" IN ('reserved','consumed') LIMIT 1;
  IF FOUND THEN CONTINUE;END IF;
  SELECT * INTO u FROM "_User" WHERE "company"=co AND "legacyUserId"::text=a."szyh"::text FOR UPDATE;
  costs:=COALESCE(item->'creditCosts','[]');status:=item->>'status';why:=item->>'reason';
  IF status IN ('ready','insufficient') THEN
   status:='ready';why:='';
   IF u."objectId" IS NULL THEN status:='missing_student';why:='学员不存在'; END IF;
   FOR cost IN SELECT value FROM jsonb_array_elements(costs) LOOP
    IF COALESCE((u."legacyUserData"->>(cost->>'account'))::numeric,0)-xs_reserved(co,u."legacyUserId"::numeric,cost->>'account')<(cost->>'amount')::numeric THEN status:='insufficient';why:='可用课时不足，请补充课时后重新核对';END IF;
   END LOOP;
  END IF;
  UPDATE "CourseAppointment" SET "creditHoldStatus"=status,"creditHoldReason"=why,
   "courseCategoryKey"=COALESCE(NULLIF(item->>'categoryKey',''),"courseCategoryKey"),"courseCategoryName"=COALESCE(NULLIF(item->>'categoryName',''),"courseCategoryName"),
   "teachingRuleId"=COALESCE(NULLIF(item->>'teachingRuleId',''),"teachingRuleId"),"teacherPaySnapshot"=COALESCE("teacherPaySnapshot",(item->>'teacherPay')::numeric),
   "creditCostsSnapshot"=CASE WHEN jsonb_array_length(costs)>0 THEN costs ELSE "creditCostsSnapshot" END,"durationMinutes"=(item->>'durationMinutes')::numeric,"deliveryMode"=item->>'deliveryMode',"updatedAt"=now() WHERE "objectId"=a."objectId";
  IF status='ready' THEN
   PERFORM xs_insert('CourseCreditReservation',jsonb_build_object('objectId',xs_object_id(),'company',co,'sourceKey','payroll-reservation:'||xs_object_id(),'createdAt',now(),'updatedAt',now(),'appointmentId',c."objectId",'appointmentGeneralId',c."generalId",'studentId',u."legacyUserId",'ruleId',item->>'teachingRuleId','creditCosts',costs,'state','reserved','reservedAt',now()));reserved:=reserved+1;
  ELSE held:=held+1;END IF;
  PERFORM xs_payroll_audit(co,actor,'reserve-history',c."objectId",reason,to_jsonb(a),jsonb_build_object('status',status,'costs',costs));
 END LOOP;
 RETURN jsonb_build_object('reserved',reserved,'held',held);
END $$;
CREATE OR REPLACE FUNCTION xs_payroll_refund_rows(co text) RETURNS SETOF jsonb LANGUAGE sql STABLE AS $$
 WITH rows AS (
 SELECT c."objectId" AS aid,c."generalId",a."szyh",a."yysj",COALESCE(a."durationMinutes",60) AS duration,
 COALESCE(NULLIF((SELECT sum((v->>'amount')::numeric) FROM jsonb_array_elements(COALESCE(a."creditCostsSnapshot"::jsonb,'[]')) v WHERE v->>'account'='UserPoint'),0),CASE WHEN a."durationMinutes"=30 THEN .5 ELSE 1 END) AS expected,
 COALESCE((SELECT sum(-l."score") FROM "UserUserPoint" l WHERE l."company"=co AND l."score"<0 AND (l."sourceKey" IN ('cloud:e_order_update:'||c."generalId"::text||':period','cloud:e_order_update:'||c."generalId"::text||':configured:UserPoint','cloud:ops-complete:'||c."objectId"||':point') OR EXISTS(SELECT 1 FROM "LessonRecord" lr WHERE lr."company"=co AND lr."yyds"::text=c."generalId"::text AND lr."sourceKey"=l."transactionId"))),0) AS charged,
 COALESCE((SELECT sum(l."score") FROM "UserUserPoint" l WHERE l."company"=co AND l."score">0 AND l."sourceKey"='cloud:credit-reconciliation:'||co||':'||c."generalId"::text),0)+COALESCE((SELECT sum(r."amount") FROM "PayrollReconciliation" r WHERE r."company"=co AND r."appointmentId"=c."objectId"),0) AS refunded
 FROM "CommonModel" c JOIN "CourseAppointment" a ON a."company"=co AND a."id"::text=c."itemId"::text
 WHERE c."company"=co AND c."modelId"=54 AND c."status"<>-2 AND a."courseCategoryKey"='review' AND left(COALESCE(a."yysj",a."bxrq"),10)>='2026-09-01' AND a."dszt" IN ('11','20','30','40'))
 SELECT jsonb_build_object('appointmentId',aid,'appointmentGeneralId',"generalId",'studentId',"szyh",'lessonAt',"yysj",'durationMinutes',duration,'expected',expected,'charged',charged,'refunded',refunded,'adjustment',GREATEST(0,charged-expected-refunded),'status',CASE WHEN refunded>0 THEN '已处理' WHEN charged>expected THEN '待退回' ELSE '无差异' END) FROM rows
$$;
CREATE OR REPLACE FUNCTION xs_payroll_refunds(co text,actor text,ids jsonb,reason text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE item jsonb;u "_User";delta numeric;before_value numeric;tx text;count integer=0;total numeric=0;
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));PERFORM set_config('xiaoshu.credit_write','yes',true);
 FOR item IN SELECT s FROM xs_payroll_refund_rows(co) s WHERE ids ? (s->>'appointmentId') LOOP
  delta:=(item->>'adjustment')::numeric;
  IF delta<=0 OR EXISTS(SELECT 1 FROM "PayrollReconciliation" WHERE "company"=co AND "appointmentId"=item->>'appointmentId') THEN CONTINUE; END IF;
  SELECT * INTO u FROM "_User" WHERE "company"=co AND "legacyUserId"::text=item->>'studentId' FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION '学员不存在';END IF;
  before_value:=COALESCE((u."legacyUserData"->>'UserPoint')::numeric,0);tx:=xs_object_id();
  PERFORM xs_ledger(co,u."legacyUserId"::numeric,'UserPoint',delta,before_value,tx,actor,reason);
  UPDATE "_User" SET "legacyUserData"=jsonb_set(COALESCE("legacyUserData",'{}'),'{UserPoint}',to_jsonb(before_value+delta)),"updatedAt"=now() WHERE "objectId"=u."objectId";
  INSERT INTO "CreditTransaction" ("objectId","company","createdAt","updatedAt","idempotencyKey","request","kind","account","amount","studentId","operatorId","reason","balances") VALUES(tx,co,now(),now(),'reconcile:'||(item->>'appointmentId'),item,'reconciliation','UserPoint',delta,u."legacyUserId",actor,reason,jsonb_build_object('before',before_value,'after',before_value+delta));
  INSERT INTO "PayrollReconciliation" ("company","appointmentId","amount","transactionId") VALUES(co,item->>'appointmentId',delta,tx);
  PERFORM xs_payroll_audit(co,actor,'credit-reconciliation',item->>'appointmentId',reason,item,jsonb_build_object('refund',delta,'transactionId',tx));count:=count+1;total:=total+delta;
 END LOOP;
 RETURN jsonb_build_object('committed',count,'refundTotal',total);
END $$;
CREATE OR REPLACE FUNCTION xs_payroll_writing_mapping(co text,actor text,aid text,category text,evidence text,expected timestamptz,reason text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE c "CommonModel";a "CourseAppointment";label text;
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT * INTO c FROM "CommonModel" WHERE "company"=co AND "objectId"=aid AND "modelId"=54 FOR UPDATE;
 SELECT * INTO a FROM "CourseAppointment" WHERE "company"=co AND "id"::text=c."itemId"::text FOR UPDATE;
 label:=CASE category WHEN 'primary_writing' THEN '小学语法写作课' WHEN 'middle_writing' THEN '中学语法写作课' WHEN 'high_writing' THEN '高中语法写作课' END;
 IF c."objectId" IS NULL OR a."objectId" IS NULL OR c."status"=-2 OR label IS NULL OR c."subtitle" !~ '阅读|写作|语法|作文|强化' OR length(trim(evidence))<2 THEN RAISE EXCEPTION '预约、语法写作分类或学段证据无效';END IF;
 IF COALESCE(a."courseCategoryKey",'') IN ('primary_writing','middle_writing','high_writing') AND length(trim(COALESCE(a."categoryEvidence",'')))>=2 THEN
  IF a."courseCategoryKey"=category AND trim(a."categoryEvidence")=trim(evidence) THEN RETURN jsonb_build_object('updated',false,'alreadyConfirmed',true);END IF;
  RAISE EXCEPTION '该预约已由其他人确认，请刷新后核对';
 END IF;
 IF COALESCE(a."courseCategoryKey",'')<>'' AND a."courseCategoryKey"<>category AND (a."teacherPaySnapshot" IS NOT NULL OR a."dszt" NOT IN ('0','10')) THEN RAISE EXCEPTION '已保存计薪快照或已完课，请通过差异调整处理';END IF;
 UPDATE "CourseAppointment" SET "courseCategoryKey"=category,"courseCategoryName"=label,"categoryEvidence"=evidence,"updatedAt"=now() WHERE "objectId"=a."objectId";
 PERFORM xs_payroll_audit(co,actor,'student-writing-mapping',aid,reason,to_jsonb(a),jsonb_build_object('categoryKey',category,'evidence',evidence));RETURN jsonb_build_object('updated',true);
END $$;
COMMIT;