xiaoshu-app/scripts/sql/member-credit-ledger.sql

-- Additive migration. Does not rewrite balances or replay historical charges.
CREATE TABLE IF NOT EXISTS "CreditOperation" (
 "objectId" text PRIMARY KEY, company text NOT NULL, "studentId" numeric NOT NULL,
 "idempotencyKey" text NOT NULL, request jsonb NOT NULL, result jsonb NOT NULL,
 "createdAt" timestamptz NOT NULL DEFAULT now(), UNIQUE(company,"idempotencyKey")
);
CREATE TABLE IF NOT EXISTS "TrialEligibility" (
 company text NOT NULL, "studentId" numeric NOT NULL, status text NOT NULL CHECK(status IN ('pending','eligible','issued')),
 evidence jsonb NOT NULL DEFAULT '{}', "updatedAt" timestamptz NOT NULL DEFAULT now(), PRIMARY KEY(company,"studentId")
);
CREATE TABLE IF NOT EXISTS "LegacyFinanceReceipt" (
 company text NOT NULL, "studentId" numeric NOT NULL, account text NOT NULL,
 "runId" text NOT NULL, ids jsonb NOT NULL, checksum text NOT NULL, "createdAt" timestamptz DEFAULT now(),
 PRIMARY KEY(company,"studentId",account,"runId")
);
DO $$ DECLARE t text; BEGIN
 FOREACH t IN ARRAY ARRAY['UserExpDomP','UserSIcon','UserExpHis','UserUserPoint'] LOOP
 EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS "legacyFinanceId" text, ADD COLUMN IF NOT EXISTS "legacyFinanceRaw" jsonb, ADD COLUMN IF NOT EXISTS "legacyFinanceHash" text',t);
 EXECUTE format('CREATE UNIQUE INDEX IF NOT EXISTS %I ON %I(company,"legacyFinanceId") WHERE "legacyFinanceId" IS NOT NULL',lower(t)||'_finance_source',t);
 END LOOP;
END $$;
ALTER TABLE "CreditTransaction" ADD COLUMN IF NOT EXISTS "operationId" text;
INSERT INTO "TrialEligibility"(company,"studentId",status,evidence)
 SELECT u.company,u."legacyUserId",CASE WHEN COALESCE((u."legacyUserData"->>'UserExp')::numeric,0)>0
 OR EXISTS(SELECT 1 FROM "UserExpHis" h WHERE h.company=u.company AND h."userId"=u."legacyUserId" AND h.score<>0)
 OR EXISTS(SELECT 1 FROM "LessonRecord" l WHERE l.company=u.company AND l.xymz::text=u."legacyUserId"::text AND (l."courseCategoryKey"='trial' OR l.kclx::text='3'))
 THEN 'issued' ELSE 'pending' END,jsonb_build_object('source','migration-local-evidence') FROM "_User" u
 WHERE u.company IS NOT NULL AND u."legacyUserId" IS NOT NULL AND u."identityType"='member'
 ON CONFLICT(company,"studentId") DO UPDATE SET status=CASE WHEN EXCLUDED.status='issued' THEN 'issued' ELSE "TrialEligibility".status END;

-- All balance entry points, including older gateways, must obey the lifetime grant guard.
CREATE OR REPLACE FUNCTION xs_trial_grant_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE previous numeric=0; next_value numeric; qualification text;
BEGIN
 IF NEW."identityType" IS DISTINCT FROM 'member' THEN RETURN NEW;END IF;
 next_value=COALESCE((NEW."legacyUserData"->>'UserExp')::numeric,0);
 IF TG_OP='UPDATE' THEN previous=COALESCE((OLD."legacyUserData"->>'UserExp')::numeric,0);END IF;
 IF next_value<=previous THEN RETURN NEW;END IF;
 IF TG_OP='INSERT' AND COALESCE(to_jsonb(NEW)->>'sourceKey','') LIKE 'legacy%' AND NEW."legacyUserId" IS NOT NULL THEN
  INSERT INTO "TrialEligibility"(company,"studentId",status,evidence) VALUES(NEW.company,NEW."legacyUserId",'issued',jsonb_build_object('source','initial-legacy-balance')) ON CONFLICT(company,"studentId") DO UPDATE SET status='issued';RETURN NEW;
 END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||NEW.company));
 SELECT status INTO qualification FROM "TrialEligibility" WHERE company=NEW.company AND "studentId"=NEW."legacyUserId" FOR UPDATE;
 IF qualification IS DISTINCT FROM 'eligible' THEN RAISE EXCEPTION '体验资格已使用或待核验，每位会员仅可发放一次';END IF;
 IF next_value-previous<>1 OR previous<>0 THEN RAISE EXCEPTION '体验数量固定 1 次';END IF;
 UPDATE "TrialEligibility" SET status='issued',evidence=evidence||jsonb_build_object('issuedAt',now()),"updatedAt"=now() WHERE company=NEW.company AND "studentId"=NEW."legacyUserId";
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS xs_trial_grant_guard ON "_User";
CREATE TRIGGER xs_trial_grant_guard BEFORE INSERT OR UPDATE OF "legacyUserData" ON "_User" FOR EACH ROW EXECUTE FUNCTION xs_trial_grant_guard();

CREATE OR REPLACE FUNCTION xs_credit_plan(co text,target text,p jsonb) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE u "_User"; st "_User"; source "CreditTransaction"; companion "CreditTransaction"; a text=p->>'account'; n numeric=(p->>'amount')::numeric;
 kind text=COALESCE(p->>'direction','add'); lines jsonb='[]'; item jsonb; signed numeric; paired boolean=true; before_value numeric; held numeric; store_value numeric; store_held numeric; delta numeric; qualification text; token text;
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT * INTO u FROM "_User" WHERE company=co AND "objectId"=target;
 IF NOT FOUND OR u."identityType"<>'member' OR COALESCE(u."isDisabled",false) OR COALESCE((to_jsonb(u)->>'isDeleted')::boolean,false) THEN RAISE EXCEPTION '学员不存在或已停用';END IF;
 IF a IS NULL OR NOT a=ANY(ARRAY['Purse','SilverCoin','UserExp','UserPoint']) OR n IS NULL OR n<=0 OR n>1000000000 OR n::text IN ('NaN','Infinity') OR round(n,2)<>n OR (a<>'UserPoint' AND trunc(n)<>n) THEN RAISE EXCEPTION '课时数量无效';END IF;
 IF kind NOT IN ('add','deduct','adjust') OR length(trim(COALESCE(p->>'payMethod','')))<1 THEN RAISE EXCEPTION '请选择记账方式和业务类型';END IF;
 IF kind='adjust' AND COALESCE(p->>'adjustMode','paired') NOT IN ('paired','single') THEN RAISE EXCEPTION '调整范围无效';END IF;
 IF kind='adjust' AND (length(trim(COALESCE(p->>'evidence','')))<2 OR COALESCE(p->>'adjustSign','') NOT IN ('add','deduct')) THEN RAISE EXCEPTION '差错调整需要依据和增减方向';END IF;
 SELECT status INTO qualification FROM "TrialEligibility" WHERE company=co AND "studentId"=u."legacyUserId";
 IF a='UserExp' AND (kind='add' OR (kind='adjust' AND p->>'adjustSign'='add')) AND (qualification IS DISTINCT FROM 'eligible' OR n<>1) THEN RAISE EXCEPTION '体验资格已使用或待核验，数量固定 1 次';END IF;
 IF kind='deduct' THEN
  SELECT c.* INTO source FROM "CreditTransaction" c WHERE c.company=co AND c."objectId"=p->>'sourceTransactionId' AND c."studentId"=u."legacyUserId" AND c.account=a AND c.kind='transfer';
  IF NOT FOUND THEN RAISE EXCEPTION '请选择有效原单；无原单请使用差错调整';END IF;
  IF a='UserPoint' AND source."operationId" IS NOT NULL AND EXISTS(SELECT 1 FROM "CreditTransaction" c WHERE c.company=co AND c."operationId"=source."operationId" AND c.account IN ('Purse','SilverCoin')) THEN RAISE EXCEPTION '成套充值请从原课时主单按比例退回';END IF;
  IF n>(source.amount-COALESCE((SELECT sum(t.amount) FROM "CreditTransaction" t WHERE t.company=co AND t."sourceTransactionId"=source."objectId" AND t.kind='refund'),0)) THEN RAISE EXCEPTION '超过原单剩余可退数量';END IF;
  SELECT * INTO st FROM "_User" WHERE company=co AND "legacyUserId"=source."storeId";
  IF source."operationId" IS NOT NULL AND a IN ('Purse','SilverCoin') THEN
   SELECT c.* INTO companion FROM "CreditTransaction" c WHERE c.company=co AND c."operationId"=source."operationId" AND c.account='UserPoint' AND c.kind='transfer';
   paired=FOUND;
  ELSE paired=false;END IF;
 ELSIF kind='add' THEN
  SELECT * INTO st FROM "_User" WHERE company=co AND "legacyUserId"=NULLIF(u."legacyUserData"->>'ParentUserID','')::numeric;
 ELSE paired=COALESCE(p->>'adjustMode','paired')='paired';END IF;
 IF kind<>'adjust' AND (st."objectId" IS NULL OR st."identityType"<>'store' OR COALESCE(st."isDisabled",false) OR COALESCE((to_jsonb(st)->>'isDeleted')::boolean,false)) THEN RAISE EXCEPTION '请先关联有效门店';END IF;
 signed=CASE WHEN kind='deduct' OR (kind='adjust' AND p->>'adjustSign'='deduct') THEN -1 ELSE 1 END;
 lines=jsonb_build_array(jsonb_build_object('account',a,'amount',n,'sourceTransactionId',source."objectId"));
 IF paired AND a IN ('Purse','SilverCoin') THEN lines=lines||jsonb_build_array(jsonb_build_object('account','UserPoint','amount',CASE WHEN kind='deduct' THEN n*companion.amount/source.amount WHEN a='Purse' THEN n*0.5 ELSE n END,'sourceTransactionId',companion."objectId"));END IF;
 token=md5(jsonb_build_object('student',u."legacyUserData",'store',st."legacyUserData",'trial',qualification,'lines',lines,'source',source."objectId",'refunds',(SELECT jsonb_agg(to_jsonb(t) ORDER BY t."objectId") FROM "CreditTransaction" t WHERE t.company=co AND t.kind='refund' AND t."studentId"=u."legacyUserId"),'reserved',(SELECT jsonb_agg(jsonb_build_array(x,xs_reserved(co,u."legacyUserId"::numeric,x),xs_reserved(co,st."legacyUserId"::numeric,x))) FROM unnest(ARRAY['Purse','SilverCoin','UserExp','UserPoint']) x))::text);
 FOR item IN SELECT value FROM jsonb_array_elements(lines) LOOP
  a=item->>'account';delta=signed*(item->>'amount')::numeric;before_value=COALESCE((u."legacyUserData"->>a)::numeric,0);held=xs_reserved(co,u."legacyUserId"::numeric,a);
  IF delta<0 AND before_value-held+delta<0 THEN RAISE EXCEPTION '% 可扣回额度不足，不能扣回已使用或已预留额度',a;END IF;
  store_value=COALESCE((st."legacyUserData"->>a)::numeric,0);store_held=xs_reserved(co,st."legacyUserId"::numeric,a);
  IF kind='add' AND store_value-store_held<delta THEN RAISE EXCEPTION '门店 % 库存不足，缺少 %；请在门店账户办理总部入账',a,delta-(store_value-store_held);END IF;
  lines=(SELECT jsonb_agg(CASE WHEN v->>'account'=a THEN v||jsonb_build_object('delta',delta,'before',before_value,'after',before_value+delta,'reserved',held,'availableBefore',before_value-held,'availableAfter',before_value-held+delta,'storeBefore',CASE WHEN kind='adjust' THEN NULL ELSE store_value END,'storeAfter',CASE WHEN kind='adjust' THEN NULL ELSE store_value-delta END,'storeAvailableBefore',CASE WHEN kind='adjust' THEN NULL ELSE store_value-store_held END,'storeAvailableAfter',CASE WHEN kind='adjust' THEN NULL ELSE store_value-store_held-delta END) ELSE v END) FROM jsonb_array_elements(lines) v);
 END LOOP;
 RETURN jsonb_build_object('version',token,'studentId',u."legacyUserId",'storeId',st."legacyUserId",'storeObjectId',st."objectId",'lines',lines,'trialStatus',COALESCE(qualification,'pending'));
END $$;

CREATE OR REPLACE FUNCTION xs_credit_operation(co text,actor text,target text,p jsonb,reason text,idem text,expected text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE req jsonb=jsonb_build_object('actor',actor,'target',target,'payload',p,'reason',reason); previous "CreditOperation"; plan jsonb; item jsonb; r jsonb; results jsonb='[]'; oid text=xs_object_id(); tx text; kind text=p->>'direction'; uid numeric;
BEGIN
 IF length(trim(reason))<2 OR length(idem)<8 THEN RAISE EXCEPTION '缺少原因或幂等编号';END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT * INTO previous FROM "CreditOperation" WHERE company=co AND "idempotencyKey"=idem;
 IF FOUND THEN IF previous.request<>req THEN RAISE EXCEPTION '请求编号已用于其他操作';END IF;RETURN previous.result;END IF;
 plan=xs_credit_plan(co,target,p);uid=(plan->>'studentId')::numeric;
 IF expected IS NULL OR expected IS DISTINCT FROM plan->>'version' THEN RAISE EXCEPTION '账户或预留已变化，请刷新预览后提交';END IF;
 FOR item IN SELECT value FROM jsonb_array_elements(plan->'lines') LOOP
  IF kind='adjust' THEN
   tx=xs_object_id();PERFORM set_config('xiaoshu.credit_write','yes',true);
   UPDATE "_User" SET "legacyUserData"=jsonb_set(COALESCE("legacyUserData",'{}'),ARRAY[item->>'account'],item->'after'),"updatedAt"=now() WHERE company=co AND "objectId"=target;
   PERFORM xs_ledger(co,uid,item->>'account',(item->>'delta')::numeric,(item->>'before')::numeric,tx,actor,reason||'；依据：'||(p->>'evidence'),p->>'payMethod');
   r=jsonb_build_object('objectId',tx,'company',co,'createdAt',now(),'updatedAt',now(),'idempotencyKey',idem||':'||(item->>'account'),'request',req,'kind','adjust','account',item->>'account','amount',(item->>'amount')::numeric,'studentId',uid,'operatorId',actor,'reason',reason,'payMethod',p->>'payMethod','balances',jsonb_build_object('studentBefore',item->'before','studentAfter',item->'after'),'operationId',oid);
   PERFORM xs_insert('CreditTransaction',r);
  ELSE
   r=xs_credit_post(co,actor,CASE kind WHEN 'add' THEN 'transfer' ELSE 'refund' END,target,item->>'account',(item->>'amount')::numeric,COALESCE(item->>'sourceTransactionId',''),reason,p->>'payMethod',idem||':'||(item->>'account'));
   UPDATE "CreditTransaction" SET "operationId"=oid WHERE "objectId"=r->>'objectId';
  END IF;
  results=results||jsonb_build_array(r-'request');
 END LOOP;
 r=plan||jsonb_build_object('objectId',oid,'transactions',results);
 INSERT INTO "CreditOperation" VALUES(oid,co,uid,idem,req,r,now());
 PERFORM xs_insert('SysLog',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'sourceKey','credit-operation:'||oid,'id',nextval('xiaoshu_business_id'),'cdate',now(),'cname','会员额度业务','type1','operations-admin','type2',kind,'type3','CreditOperation','clevel',0,'content1',reason,'content2',actor,'detail',jsonb_build_object('request',req,'result',r)::text));
 RETURN r;
END $$;
CREATE TABLE IF NOT EXISTS "LegacyFinanceSourcePage" (
 company text NOT NULL,"studentId" numeric NOT NULL,account text NOT NULL,"runId" text NOT NULL,page integer NOT NULL,
 rows jsonb NOT NULL,"createdAt" timestamptz DEFAULT now(),PRIMARY KEY(company,"studentId",account,"runId",page)
);
CREATE OR REPLACE VIEW xs_member_ledger AS
WITH raw AS (
 SELECT 'Purse'::text account,to_jsonb(l) j FROM "UserExpDomP" l UNION ALL
 SELECT 'SilverCoin',to_jsonb(l) FROM "UserSIcon" l UNION ALL
 SELECT 'UserExp',to_jsonb(l) FROM "UserExpHis" l UNION ALL
 SELECT 'UserPoint',to_jsonb(l) FROM "UserUserPoint" l
)
SELECT r.account,r.j->>'objectId' id,r.j->>'company' company,(r.j->>'userId')::numeric "studentId",
 COALESCE(t."operationId",r.account||':'||(r.j->>'objectId')) "groupId",
 COALESCE((r.j->>'hisTime')::timestamptz,(r.j->>'createdAt')::timestamptz) "happenedAt",
 (r.j->>'score')::numeric delta,(r.j->>'scoreBefore')::numeric "before",
 CASE WHEN r.j->>'legacyFinanceId' IS NULL THEN (r.j->>'scoreBefore')::numeric+(r.j->>'score')::numeric ELSE NULL END "after",
 COALESCE(t.kind,CASE WHEN r.j->>'transactionId' LIKE 'complete:%' OR r.j->>'detail' ~ '上课|结课|订单|陪练扣|课时扣' THEN 'lesson' WHEN (r.j->>'score')::numeric>0 THEN 'recharge' ELSE 'deduction' END) kind,
 COALESCE(t.reason,r.j->>'detail',r.j->>'remark') reason,
 CASE WHEN r.j->>'legacyFinanceId' IS NOT NULL OR r.j->>'sourceKey' NOT LIKE 'credit:%' THEN 'legacy' ELSE 'new' END source,
 COALESCE(actor."realName",actor.nickname,t."operatorId",r.j->'legacyFinanceRaw'->>'Operator',r.j->>'remark') operator,
 COALESCE(t."objectId",r.j->>'legacyFinanceId',r.j->>'transactionId',r.j->>'sourceKey') reference,
 t."storeId",t.balances,r.j->'legacyFinanceRaw' "legacyRaw",
 (SELECT l."objectId" FROM "LessonRecord" l WHERE l.company=r.j->>'company' AND l.xymz::text=r.j->>'userId' AND
 (r.j->>'transactionId'='complete:'||(SELECT c."objectId" FROM "CommonModel" c WHERE c.company=l.company AND c."modelId"=54 AND c."generalId"::text=l.yyds::text LIMIT 1) OR (r.j->>'detail') ~ ('订单[：: ]*'||l.yyds::text||'([^0-9]|$)')) LIMIT 1) "lessonId"
FROM raw r LEFT JOIN "CreditTransaction" t ON t.company=r.j->>'company' AND t."objectId"=r.j->>'transactionId'
LEFT JOIN "_User" actor ON actor."objectId"=t."operatorId" AND actor.company=t.company;
CREATE OR REPLACE FUNCTION xs_trial_reservation_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE cost jsonb;
BEGIN
 IF NEW.state NOT IN ('reserved','consumed') THEN RETURN NEW;END IF;
 SELECT value INTO cost FROM jsonb_array_elements(COALESCE(NEW."creditCosts"::jsonb,'[]')) WHERE value->>'account'='UserExp';
 IF cost IS NULL THEN RETURN NEW;END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||NEW.company));
 IF (cost->>'amount')::numeric<>1 OR jsonb_array_length(NEW."creditCosts"::jsonb)<>1 THEN RAISE EXCEPTION '体验课只扣一次体验，不扣普通陪练';END IF;
 IF TG_OP='UPDATE' AND OLD.state='consumed' THEN RETURN NEW;END IF;
 IF xs_reserved(NEW.company,NEW."studentId"::numeric,'UserExp',NEW."appointmentId")>0 OR EXISTS(
 SELECT 1 FROM "LessonRecord" l WHERE l.company=NEW.company AND l.xymz::text=NEW."studentId"::text AND (l."courseCategoryKey"='trial' OR l.kclx::text='3')
 ) THEN RAISE EXCEPTION '体验已使用或已有体验预约，不能重复预约';END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS xs_trial_reservation_guard ON "CourseCreditReservation";
CREATE TRIGGER xs_trial_reservation_guard BEFORE INSERT OR UPDATE ON "CourseCreditReservation" FOR EACH ROW EXECUTE FUNCTION xs_trial_reservation_guard();