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

-- Apply after review-pay-policy.sql. Explicit, audited historical determinations;
-- original receipts and completed-by fields are never rewritten by reconciliation.
BEGIN;
SET LOCAL lock_timeout='10s';
CREATE INDEX IF NOT EXISTS memory_review_source_day ON "MemoryPracticeRecord"(company,yhid,xxjlid,(left(kywrq::text,10)));
CREATE TABLE IF NOT EXISTS "ReviewRewardResolution" (
 "objectId" text PRIMARY KEY, company text NOT NULL, "rewardId" text NOT NULL REFERENCES "ReviewReward"("objectId"),
 "taskKey" text NOT NULL, status text NOT NULL CHECK(status IN ('earned','ineligible')),
 amount numeric(12,2) NOT NULL, "reasonCode" text NOT NULL, reason text NOT NULL CHECK(length(trim(reason))>=2),
 actor text NOT NULL, evidence jsonb NOT NULL, "createdAt" timestamptz NOT NULL DEFAULT now(),
 UNIQUE(company,"rewardId"), CHECK((status='earned' AND amount IN (0.5,1)) OR(status='ineligible' AND amount=0))
);
CREATE UNIQUE INDEX IF NOT EXISTS review_resolution_once ON "ReviewRewardResolution"(company,"taskKey") WHERE status='earned';
CREATE OR REPLACE FUNCTION xs_review_resolution_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_OP<>'INSERT' OR current_setting('xiaoshu.review_resolution',true) IS DISTINCT FROM 'yes' THEN RAISE EXCEPTION '复习核定凭据只能由核定事务新增'; END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS review_resolution_immutable ON "ReviewRewardResolution";
CREATE TRIGGER review_resolution_immutable BEFORE INSERT OR UPDATE OR DELETE ON "ReviewRewardResolution" FOR EACH ROW EXECUTE FUNCTION xs_review_resolution_guard();
REVOKE ALL ON "ReviewRewardResolution" FROM PUBLIC;
CREATE OR REPLACE VIEW "ReviewRewardIncome" AS
SELECT r."objectId",r.company,r."taskId",COALESCE(d."taskKey",r."taskKey") AS "taskKey",r."coachId",r."coachName",r."studentId",r."studentName",r."courseName",r."storeId",r."storeName",r."plannedDate",r."completedAt",r."incomeMonth",
 (COALESCE(d.amount,r.amount)+COALESCE(c.amount,0)+COALESCE(rc.amount,0))::numeric(12,2) AS amount,
 CASE WHEN c."objectId" IS NOT NULL THEN 'ineligible' ELSE COALESCE(d.status,r.status) END AS status,
 CASE WHEN d."objectId" IS NULL THEN r.exception ELSE '' END AS exception,r."createdAt",
 CASE WHEN c."objectId" IS NOT NULL THEN 'corrected_overdue' WHEN d.status='ineligible' THEN d."reasonCode" ELSE r."ineligibleReason" END AS "ineligibleReason",
 r.amount AS "originalAmount",COALESCE(c."objectId",rc."objectId") AS "correctionId",
 NULLIF(concat_ws('；',rc.reason,c.reason,d.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"
LEFT JOIN "ReviewRewardResolution" d ON d.company=r.company AND d."rewardId"=r."objectId";

CREATE OR REPLACE FUNCTION xs_review_resolve(co text, actor text, items jsonb, reason text, historical_assignment boolean DEFAULT false) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE item jsonb; r "ReviewReward"; m "MemoryPracticeRecord"; canonical record; minutes integer; k text; outcome text; code text; snapshot jsonb; source jsonb; n integer:=0; existing integer:=0; prior text:=COALESCE(current_setting('xiaoshu.review_resolution',true),'');
BEGIN
 PERFORM xs_review_manager(co,actor);
 IF length(trim(COALESCE(reason,'')))<2 OR jsonb_typeof(items) IS DISTINCT FROM 'array' OR jsonb_array_length(items) NOT BETWEEN 1 AND 100 THEN RAISE EXCEPTION '需要明确核定依据和1至100条凭据'; END IF;
 LOCK TABLE "MemoryPracticeRecord", "ReviewReward" IN SHARE ROW EXCLUSIVE MODE;
 PERFORM pg_advisory_xact_lock(hashtext('xs-payroll:'||co));
 FOR item IN SELECT value FROM jsonb_array_elements(items) LOOP
  SELECT * INTO r FROM "ReviewReward" WHERE company=co AND "objectId"=item->>'rewardId' FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION '凭据不存在'; END IF;
  IF EXISTS(SELECT 1 FROM "ReviewRewardResolution" WHERE company=co AND "rewardId"=r."objectId") THEN existing:=existing+1;CONTINUE;END IF;
  IF r.status NOT IN ('historical','exception') OR r.amount<>0 OR EXISTS(SELECT 1 FROM "PayrollLine" WHERE company=co AND "reviewRewardId"=r."objectId" AND NOT COALESCE(excluded,false)) THEN RAISE EXCEPTION '仅能核定未计薪、未入工资单的待核对凭据'; END IF;
  SELECT * INTO m FROM "MemoryPracticeRecord" WHERE company=co AND "objectId"=r."taskId" FOR UPDATE;
  k:=xiaoshu_review_key(m.yhid::text,m.xxjlid::text,left(m.kywrq::text,10));
  IF m."objectId" IS NULL OR m.fxzt::text<>'1' OR k IS NULL OR r."studentId"::text<>m.yhid::text OR r."coachId"::text<>m.plid::text OR r."plannedDate"<>left(m.kywrq::text,10) OR r."completedAt" IS DISTINCT FROM xiaoshu_safe_shanghai_timestamp(m.wcsj::text) THEN RAISE EXCEPTION '任务、身份或完成时间与原凭据不一致'; END IF;
  code:=item->>'reasonCode';outcome:='ineligible';minutes:=NULL;
  snapshot:=jsonb_build_object('receipt',to_jsonb(r),'task',to_jsonb(m),'policy','duration-actor-v2');
  IF code='overdue' THEN
   IF xiaoshu_review_eligibility(r."plannedDate",r."completedAt")<>'overdue' THEN RAISE EXCEPTION '不能认定为逾期'; END IF;
  ELSIF code='duplicate_review' THEN
   SELECT v.* INTO canonical FROM "ReviewRewardIncome" v JOIN "MemoryPracticeRecord" t ON t.company=v.company AND t."objectId"=v."taskId"
    WHERE v.company=co AND v."objectId"=item->>'canonicalRewardId' AND v.status IN ('earned','historical','exception') AND v."objectId"<>r."objectId" AND v."coachId"=r."coachId" AND t.yhid=m.yhid AND t.xxjlid=m.xxjlid AND left(t.kywrq::text,10)=left(m.kywrq::text,10);
   IF NOT FOUND THEN RAISE EXCEPTION '重复记录必须关联同一老师、同一任务的有效计薪凭据或保留凭据'; END IF;
   IF canonical."objectId" IS DISTINCT FROM (SELECT v."objectId" FROM "ReviewRewardIncome" v JOIN "MemoryPracticeRecord" t ON t.company=v.company AND t."objectId"=v."taskId" WHERE v.company=co AND v.status IN ('earned','historical','exception') AND t.yhid=m.yhid AND t.xxjlid=m.xxjlid AND left(t.kywrq::text,10)=left(m.kywrq::text,10) ORDER BY (v.status='earned') DESC,(v.status='historical') DESC,v."completedAt",v."objectId" LIMIT 1) THEN RAISE EXCEPTION '必须保留已有计薪凭据或唯一优先完成凭据'; END IF;
   snapshot:=snapshot||jsonb_build_object('canonicalRewardId',canonical."objectId");
  ELSIF code='legacy_assignment_policy' THEN
   IF NOT historical_assignment THEN RAISE EXCEPTION '历史绑定老师核算口径未确认'; END IF;
   IF COALESCE(r."completionRole",'') IN ('student','unknown','coach') OR NULLIF(r."completedBy",'') IS NOT NULL OR r."ineligibleReason"='self_study' THEN RAISE EXCEPTION '已有实际身份凭据不能按历史口径覆盖'; END IF;
   IF COALESCE(m."sourceKey",'') NOT LIKE 'legacy:model:60:%' OR r."incomeMonth"<'2026-09' OR r."completedAt">now() OR xiaoshu_review_eligibility(r."plannedDate",r."completedAt")<>'eligible' OR r.exception NOT IN ('','来源复习任务重复') THEN RAISE EXCEPTION '历史来源或计划当天完成条件不满足'; END IF;
   IF NOT EXISTS(SELECT 1 FROM "_User" u WHERE u.company=co AND u."legacyUserId"=r."coachId" AND COALESCE(to_jsonb(u)->>'identityType','coach')='coach' AND COALESCE(to_jsonb(u)->>'legacyGroupId',u."legacyUserData"->>'GroupID')='3') OR NOT EXISTS(SELECT 1 FROM "_User" u WHERE u.company=co AND u."legacyUserId"=r."studentId") OR r."coachId"=r."studentId" THEN RAISE EXCEPTION '历史师生身份无效'; END IF;
   -- Existing earned receipts win; no second payout even if a duplicate has a null taskKey.
   IF EXISTS(SELECT 1 FROM "ReviewRewardIncome" v JOIN "MemoryPracticeRecord" t ON t.company=v.company AND t."objectId"=v."taskId" WHERE v.company=co AND v.status='earned' AND t.yhid=m.yhid AND t.xxjlid=m.xxjlid AND left(t.kywrq::text,10)=left(m.kywrq::text,10)) THEN RAISE EXCEPTION '同一复习已经计薪'; END IF;
   IF EXISTS(SELECT 1 FROM "MemoryPracticeRecord" t WHERE t.company=co AND t.yhid=m.yhid AND t.xxjlid=m.xxjlid AND left(t.kywrq::text,10)=left(m.kywrq::text,10) AND t.plid<>m.plid) THEN RAISE EXCEPTION '重复任务的老师归属冲突'; END IF;
   minutes:=xs_review_duration(co,m."objectId");
   IF minutes IS NULL THEN
    -- A separately fetched old-system lesson may bridge an absent local appointment only.
    source:=item->'legacyLesson';
    IF source IS NULL OR source->>'modelId' IS DISTINCT FROM '54' OR source->>'generalId' IS DISTINCT FROM m."orderId"::text OR source->>'szyh' IS DISTINCT FROM m.yhid::text OR NOT EXISTS(SELECT 1 FROM xs_review_task_link l WHERE l.company=co AND l."taskId"=m."objectId" AND l."appointmentId"=source->>'generalId') OR EXISTS(SELECT 1 FROM "CommonModel" c WHERE c.company=co AND c."modelId"=54 AND c."generalId"::text=source->>'generalId') THEN RAISE EXCEPTION '来源课次时长仍不明确'; END IF;
    minutes:=CASE source->>'dslx' WHEN '1' THEN 30 WHEN '2' THEN 60 WHEN '3' THEN 60 END;
    snapshot:=snapshot||jsonb_build_object('legacyLesson',source);
   END IF;
   IF minutes IS NULL THEN RAISE EXCEPTION '不能推测课时时长'; END IF;
   outcome:='earned';snapshot:=snapshot||jsonb_build_object('durationMinutes',minutes,'completionIdentity','not_recorded','historicalAssignmentPolicy',true);
  ELSE RAISE EXCEPTION '不支持的核定原因'; END IF;
  PERFORM set_config('xiaoshu.review_resolution','yes',true);
  INSERT INTO "ReviewRewardResolution"("objectId",company,"rewardId","taskKey",status,amount,"reasonCode",reason,actor,evidence)
   VALUES(md5(co||':review-resolution:'||r."objectId"),co,r."objectId",k,outcome,CASE WHEN outcome='earned' THEN minutes::numeric/60 ELSE 0 END,code,reason,actor,snapshot);
  PERFORM xs_payroll_audit(co,actor,'resolve-review-reward',r."objectId",reason,to_jsonb(r),jsonb_build_object('status',outcome,'amount',CASE WHEN outcome='earned' THEN minutes::numeric/60 ELSE 0 END,'reasonCode',code));
  n:=n+1;
 END LOOP;
 PERFORM set_config('xiaoshu.review_resolution',prior,true);
 RETURN jsonb_build_object('resolved',n,'alreadyResolved',existing);
EXCEPTION WHEN OTHERS THEN PERFORM set_config('xiaoshu.review_resolution',prior,true);RAISE;
END $$;
REVOKE ALL ON FUNCTION xs_review_resolve(text,text,jsonb,text,boolean) FROM PUBLIC;
-- Prevent a later completion of the original duplicate task from paying again.
CREATE OR REPLACE FUNCTION xs_review_resolved_once() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE k text;
BEGIN
 IF NEW.status<>'earned' THEN RETURN NEW; END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-payroll:'||NEW.company));
 SELECT xiaoshu_review_key(t.yhid::text,t.xxjlid::text,left(t.kywrq::text,10)) INTO k FROM "MemoryPracticeRecord" t WHERE t.company=NEW.company AND t."objectId"=NEW."taskId";
 IF EXISTS(SELECT 1 FROM "ReviewRewardResolution" d WHERE d.company=NEW.company AND (d."rewardId"=NEW."objectId" OR(d."taskKey"=k AND d.status='earned'))) THEN
  IF TG_OP='UPDATE' THEN RAISE EXCEPTION '复习已有核定结果，不能再次确认计薪'; END IF;
  NEW.status:='ineligible';NEW.amount:=0;NEW."ineligibleReason":='duplicate_review';
 END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS a_review_resolved_once ON "ReviewReward";
CREATE TRIGGER a_review_resolved_once BEFORE INSERT OR UPDATE ON "ReviewReward" FOR EACH ROW EXECUTE FUNCTION xs_review_resolved_once();
COMMIT;