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

-- Recheck scope under the same transaction/locks as the existing credit and
-- scheduling write; a concurrent headquarters ownership change cannot slip in.
CREATE OR REPLACE FUNCTION xs_store_scope_guard(co text,actor text,uid numeric,coach numeric DEFAULT 0,target text DEFAULT '') RETURNS void LANGUAGE plpgsql AS $$
DECLARE st "_User"; student "_User"; owner_uid numeric;
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT * INTO st FROM "_User" WHERE "objectId"=actor AND company=co FOR SHARE;
 IF NOT FOUND OR COALESCE(st."isDisabled",false) OR COALESCE((to_jsonb(st)->>'isDeleted')::boolean,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;
 SELECT * INTO student FROM "_User" WHERE company=co AND "legacyUserId"=uid FOR UPDATE;
 IF NOT FOUND OR student."identityType" IS DISTINCT FROM 'member' OR COALESCE((to_jsonb(student)->>'isDeleted')::boolean,false) OR (student."legacyUserData"->>'ParentUserID') IS DISTINCT FROM st."legacyUserId"::text THEN RAISE EXCEPTION '学生不存在或不属于本店';END IF;
 IF coach>0 AND NOT EXISTS(SELECT 1 FROM "_User" u WHERE u.company=co AND u."legacyUserId"=coach AND u."identityType"='coach' AND u."legacyUserData"->>'ParentUserID'=st."legacyUserId"::text AND COALESCE(u."isDisabled",false)=false AND COALESCE((to_jsonb(u)->>'isDeleted')::boolean,false)=false) THEN RAISE EXCEPTION '陪练老师不属于本店或已停用';END IF;
 IF COALESCE(target,'')<>'' THEN
  SELECT CASE c."modelId"::text WHEN '54' THEN a.szyh::numeric WHEN '58' THEN b.yhid::numeric END INTO owner_uid FROM "CommonModel" c LEFT JOIN "CourseAppointment" a ON a.company=c.company AND c."modelId"::text='54' AND a.id::text=c."itemId"::text LEFT JOIN "CourseBinding" b ON b.company=c.company AND c."modelId"::text='58' AND b.id::text=c."itemId"::text WHERE c.company=co AND c."objectId"=target;
  IF owner_uid IS DISTINCT FROM uid THEN RAISE EXCEPTION '原记录不属于此学生';END IF;
 END IF;
END $$;