xiaoshu-app/scripts/sql/store-credit.sql

-- Additive only: existing balances and receipts are never rewritten.
CREATE TABLE IF NOT EXISTS "StoreCreditGrant" (
 company text NOT NULL, "userId" text NOT NULL, "grantedBy" text NOT NULL,
 "updatedAt" timestamptz NOT NULL DEFAULT now(), PRIMARY KEY(company,"userId")
);
CREATE TABLE IF NOT EXISTS "StoreCreditGrantAudit" (
 id bigserial PRIMARY KEY, company text NOT NULL, "userId" text NOT NULL,
 actor text NOT NULL, enabled boolean NOT NULL, reason text NOT NULL, at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS "StoreCreditOperation" (
 company text NOT NULL,"idempotencyKey" text NOT NULL, request jsonb NOT NULL,
 result jsonb NOT NULL,"createdAt" timestamptz NOT NULL DEFAULT now(),PRIMARY KEY(company,"idempotencyKey")
);
CREATE OR REPLACE FUNCTION xs_store_credit_role(actor text) RETURNS text LANGUAGE sql STABLE AS $$
 SELECT CASE WHEN COALESCE(u."isDisabled",false) OR COALESCE((to_jsonb(u)->>'isDeleted')::boolean,false) THEN ''
 ELSE COALESCE(NULLIF(to_jsonb(u)->>'adminRoleKey',''),
 (SELECT v FROM jsonb_array_elements_text(COALESCE(NULLIF(to_jsonb(u)->'roles','null'::jsonb),'[]'::jsonb)) v WHERE v IN ('super-admin','ops-manager','ops-staff','ops-auditor') ORDER BY CASE v WHEN 'super-admin' THEN 0 WHEN 'ops-manager' THEN 1 WHEN 'ops-staff' THEN 2 ELSE 3 END LIMIT 1),
 CASE WHEN to_jsonb(u)->>'isAdmin'='true' OR to_jsonb(u)->>'role'='admin' OR to_jsonb(u)->'roles' ? 'admin' THEN 'ops-manager' ELSE '' END) END
 FROM "_User" u WHERE u."objectId"=actor
$$;
CREATE OR REPLACE FUNCTION xs_store_credit_allowed(co text,actor text) RETURNS boolean LANGUAGE sql STABLE AS $$
 SELECT COALESCE(xs_store_credit_role(actor)='super-admin' OR
 (xs_store_credit_role(actor)='ops-manager' AND EXISTS(SELECT 1 FROM "_User" u JOIN "StoreCreditGrant" g ON g.company=u.company AND g."userId"=u."objectId" WHERE u.company=co AND u."objectId"=actor)),false)
$$;
CREATE OR REPLACE FUNCTION xs_store_credit_grant(co text,actor text,target text,enabled boolean,reason text) RETURNS jsonb LANGUAGE plpgsql AS $$
BEGIN
 IF xs_store_credit_role(actor) IS DISTINCT FROM 'super-admin' THEN RAISE EXCEPTION '只有超级管理员可以授权门店充值';END IF;
 IF length(trim(COALESCE(reason,'')))<2 OR length(reason)>500 OR enabled IS NULL THEN RAISE EXCEPTION '请填写授权原因';END IF;
 IF NOT EXISTS(SELECT 1 FROM "_User" WHERE company=co AND "objectId"=target) OR (enabled AND xs_store_credit_role(target) IS DISTINCT FROM 'ops-manager') THEN RAISE EXCEPTION '请选择本帐套有效的运营管理员';END IF;
 IF enabled THEN INSERT INTO "StoreCreditGrant" VALUES(co,target,actor,now()) ON CONFLICT(company,"userId") DO UPDATE SET "grantedBy"=actor,"updatedAt"=now();
 ELSE DELETE FROM "StoreCreditGrant" WHERE company=co AND "userId"=target;END IF;
 INSERT INTO "StoreCreditGrantAudit"(company,"userId",actor,enabled,reason) VALUES(co,target,actor,enabled,reason);
 RETURN jsonb_build_object('userId',target,'enabled',enabled);
END $$;
CREATE OR REPLACE FUNCTION xs_store_credit_plan(co text,actor text,target text,p jsonb) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE st "_User"; src "CreditTransaction"; a text; n numeric; before_value numeric; held numeric; remaining numeric;
 lines jsonb='[]'; item jsonb; direction text=COALESCE(p->>'direction','add'); version text;
BEGIN
 IF NOT xs_store_credit_allowed(co,actor) THEN RAISE EXCEPTION '当前账号未获门店额度充值权限';END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT * INTO st FROM "_User" WHERE company=co AND "objectId"=target FOR UPDATE;
 IF NOT FOUND OR (st."identityType" IS DISTINCT FROM 'store' AND COALESCE(st."legacyGroupId",0)<>2) OR st."legacyUserId" IS NULL OR COALESCE(st."isDisabled",false) OR COALESCE((to_jsonb(st)->>'isDeleted')::boolean,false) THEN RAISE EXCEPTION '门店不存在或已停用';END IF;
 IF direction NOT IN ('add','reverse') OR length(trim(COALESCE(p->>'payMethod','')))=0 THEN RAISE EXCEPTION '请选择业务类型和记账方式';END IF;
 IF jsonb_typeof(p->'lines') IS DISTINCT FROM 'array' OR jsonb_array_length(p->'lines') NOT BETWEEN 1 AND 4 THEN RAISE EXCEPTION '请填写 1 至 4 项充值额度';END IF;
 IF (SELECT count(DISTINCT v->>'account') FROM jsonb_array_elements(p->'lines') v)<>jsonb_array_length(p->'lines') THEN RAISE EXCEPTION '账户不能重复';END IF;
 IF direction='reverse' THEN
  IF jsonb_array_length(p->'lines')<>1 THEN RAISE EXCEPTION '每次纠正一条原入账凭据';END IF;
  SELECT * INTO src FROM "CreditTransaction" WHERE company=co AND "objectId"=p->>'sourceTransactionId' AND kind='fund' AND "storeId"=st."legacyUserId";
  IF NOT FOUND THEN RAISE EXCEPTION '原总部入账凭据不存在或不属于该门店';END IF;
  remaining=src.amount-COALESCE((SELECT sum(amount) FROM "CreditTransaction" WHERE company=co AND "sourceTransactionId"=src."objectId" AND kind='adjust' AND "studentId" IS NULL),0);
 END IF;
 FOR item IN SELECT value FROM jsonb_array_elements(p->'lines') ORDER BY value->>'account' LOOP
  a=item->>'account';n=(item->>'amount')::numeric;
  IF a IS NULL OR a NOT IN ('Purse','SilverCoin','UserExp','UserPoint') OR n IS NULL OR n<=0 OR n>1000000000 OR n::text IN ('NaN','Infinity','-Infinity') OR round(n,2)<>n OR (a<>'UserPoint' AND trunc(n)<>n) THEN RAISE EXCEPTION '课时和体验次数必须为正整数，陪练小时最多两位小数';END IF;
  before_value=COALESCE((st."legacyUserData"->>a)::numeric,0);held=xs_reserved(co,st."legacyUserId"::numeric,a);
  IF direction='reverse' THEN
   IF a<>src.account OR n>remaining THEN RAISE EXCEPTION '超过原入账凭据可纠正数量';END IF;
   IF before_value-held<n THEN RAISE EXCEPTION '门店可用额度不足，已划拨或预留部分不能扣回';END IF;
   n=-n;
  END IF;
  lines=lines||jsonb_build_array(jsonb_build_object('account',a,'delta',n,'before',before_value,'after',before_value+n,'availableBefore',before_value-held,'availableAfter',before_value-held+n));
 END LOOP;
 version=md5(jsonb_build_object('balances',st."legacyUserData",'updated',st."updatedAt",'lines',lines,'source',src."objectId",'remaining',remaining)::text);
 RETURN jsonb_build_object('storeId',st."legacyUserId",'storeObjectId',target,'version',version,'lines',lines,'sourceTransactionId',src."objectId");
END $$;
CREATE OR REPLACE FUNCTION xs_store_credit_post(co text,actor text,target text,p jsonb,reason text,idem text,expected text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE prior "StoreCreditOperation"; req jsonb; plan jsonb; line jsonb; receipt jsonb; receipts jsonb='[]'; oid text=xs_object_id(); tx text; delta numeric;
BEGIN
 IF NOT xs_store_credit_allowed(co,actor) THEN RAISE EXCEPTION '当前账号未获门店额度充值权限';END IF;
 IF length(COALESCE(idem,''))<8 OR length(trim(COALESCE(reason,'')))<2 OR length(reason)>500 THEN RAISE EXCEPTION '请填写操作原因和有效请求编号';END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 req=jsonb_build_object('actor',actor,'target',target,'payload',p,'reason',reason);
 SELECT * INTO prior FROM "StoreCreditOperation" WHERE company=co AND "idempotencyKey"=idem;
 IF FOUND THEN IF prior.request<>req THEN RAISE EXCEPTION '请求编号已用于其他充值';END IF;RETURN prior.result;END IF;
 plan=xs_store_credit_plan(co,actor,target,p);
 IF COALESCE(expected,'')='' OR expected<>plan->>'version' THEN RAISE EXCEPTION '余额已变化，请重新预览后提交';END IF;
 FOR line IN SELECT value FROM jsonb_array_elements(plan->'lines') LOOP
  delta=(line->>'delta')::numeric;
  IF delta>0 THEN
   receipt=xs_credit_post(co,actor,'fund',target,line->>'account',delta,'',reason,p->>'payMethod','store:'||idem||':'||(line->>'account'));
   UPDATE "CreditTransaction" SET "operationId"=oid WHERE "objectId"=receipt->>'objectId';
  ELSE
   tx=xs_object_id();PERFORM set_config('xiaoshu.credit_write','yes',true);
   UPDATE "_User" SET "legacyUserData"=jsonb_set(COALESCE("legacyUserData",'{}'),ARRAY[line->>'account'],to_jsonb((line->>'after')::numeric)),"updatedAt"=now() WHERE "objectId"=target AND company=co;
   PERFORM xs_ledger(co,(plan->>'storeId')::numeric,line->>'account',delta,(line->>'before')::numeric,tx,actor,reason,p->>'payMethod');
   receipt=jsonb_build_object('objectId',tx,'company',co,'createdAt',now(),'updatedAt',now(),'idempotencyKey','store:'||idem||':'||(line->>'account'),'request',req,'kind','adjust','account',line->>'account','amount',-delta,'storeId',plan->'storeId','sourceTransactionId',p->>'sourceTransactionId','operatorId',actor,'reason',reason,'payMethod',p->>'payMethod','operationId',oid,'balances',jsonb_build_object('storeBefore',line->'before','storeAfter',line->'after'));
   PERFORM xs_insert('CreditTransaction',receipt);
   PERFORM xs_insert('SysLog',jsonb_build_object('objectId',xs_object_id(),'company',co,'sourceKey','credit:'||tx,'createdAt',now(),'updatedAt',now(),'id',nextval('xiaoshu_business_id'),'cdate',now(),'cname','门店入账纠正','type1','operations-admin','type2','store-credit-reverse','type3','CreditTransaction','clevel',0,'detail',receipt::text,'content1',reason,'content2',actor));
  END IF;
  receipts=receipts||jsonb_build_array(receipt-'request');
 END LOOP;
 receipt=jsonb_build_object('operationId',oid,'storeId',plan->'storeId','lines',plan->'lines','transactions',receipts);
 INSERT INTO "StoreCreditOperation" VALUES(co,idem,req,receipt,now());RETURN receipt;
END $$;