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

-- Server-owned bindings: login accounts do not duplicate a store's business ID.
CREATE TABLE IF NOT EXISTS "StoreAdministrator" (
 "objectId" text PRIMARY KEY, company text NOT NULL, "userId" text NOT NULL UNIQUE,
 "storeObjectId" text NOT NULL, enabled boolean NOT NULL DEFAULT true,
 "createdBy" text NOT NULL, "createdAt" timestamptz NOT NULL DEFAULT now(),
 "updatedAt" timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE "StoreAdministrator" ALTER COLUMN enabled SET DEFAULT true;
ALTER TABLE "StoreAdministrator" ALTER COLUMN "createdAt" SET DEFAULT now();
ALTER TABLE "StoreAdministrator" ALTER COLUMN "updatedAt" SET DEFAULT now();
CREATE UNIQUE INDEX IF NOT EXISTS store_administrator_user_once ON "StoreAdministrator" ("userId");
CREATE OR REPLACE FUNCTION xs_store_actor(co text, actor text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE u "_User"; st "_User"; sid text;
BEGIN
 SELECT * INTO u FROM "_User" WHERE "objectId"=actor AND company=co FOR SHARE;
 IF NOT FOUND OR COALESCE(u."isDisabled",false) OR COALESCE(u."isDeleted",false) THEN RAISE EXCEPTION '门店账号无效或已停用'; END IF;
 IF u."identityType"='store-admin' THEN
  SELECT "storeObjectId" INTO sid FROM "StoreAdministrator" WHERE company=co AND "userId"=actor AND enabled FOR SHARE;
 ELSE sid:=actor; END IF;
 SELECT * INTO st FROM "_User" WHERE "objectId"=sid AND company=co FOR SHARE;
 IF NOT FOUND OR COALESCE(st."isDisabled",false) OR COALESCE(st."isDeleted",false)
 OR NOT (st."identityType"='store' OR st."identityType" IS NULL AND st."legacyGroupId"=2)
 OR COALESCE(st."legacyUserId",0)<=0 THEN RAISE EXCEPTION '门店账号无效或已停用'; END IF;
 RETURN sid;
END $$;

CREATE OR REPLACE FUNCTION xs_store_support_process(co text,rid text,actor_id text,actor_name text,next_status text,note text,expected text)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE sid text; st "_User"; owner "_User"; g "Guestbook";
BEGIN
 sid:=xs_store_actor(co,actor_id);
 SELECT * INTO st FROM "_User" WHERE "objectId"=sid;
 SELECT * INTO g FROM "Guestbook" WHERE company=co AND "objectId"=rid FOR UPDATE;
 SELECT * INTO owner FROM "_User" WHERE company=co AND "legacyUserId"::text=g.userid::text FOR SHARE;
 IF owner."objectId" IS NULL OR owner."identityType" NOT IN ('member','coach') OR COALESCE(owner."isDeleted",false) OR owner."legacyUserData"->>'ParentUserID' IS DISTINCT FROM st."legacyUserId"::text THEN RAISE EXCEPTION '申请不存在或不属于本店'; END IF;
 RETURN xs_support_request_process(co,rid,actor_id,actor_name,next_status,note,expected);
END $$;