xiaoshu-app/scripts/sql/member-enrollment.sql

-- New database is authoritative for member accounts, credit transfers and enrollments.
-- Run the read-only migration report and back up the affected tables before installation.
CREATE SEQUENCE IF NOT EXISTS xiaoshu_business_id START 8000000000000;
DO $$ BEGIN
 PERFORM setval('xiaoshu_business_id',GREATEST((SELECT last_value FROM xiaoshu_business_id),COALESCE((SELECT max("generalId") FROM "CommonModel"),0),COALESCE((SELECT max("id") FROM "CourseBinding"),0),COALESCE((SELECT max("id") FROM "CourseAppointment"),0),COALESCE((SELECT max("id") FROM "LessonRecord"),0))::bigint);
END $$;
CREATE TABLE IF NOT EXISTS "CreditTransaction" (
 "objectId" text PRIMARY KEY,"company" text NOT NULL,"createdAt" timestamptz,"updatedAt" timestamptz,
 "idempotencyKey" text NOT NULL,"request" jsonb NOT NULL,"kind" text NOT NULL,"account" text NOT NULL,
 "amount" numeric NOT NULL,"storeId" numeric,"studentId" numeric,"sourceTransactionId" text,
 "operatorId" text,"reason" text,"payMethod" text,"balances" jsonb
);
CREATE UNIQUE INDEX IF NOT EXISTS credit_transaction_request ON "CreditTransaction" ("company","idempotencyKey");
CREATE TABLE IF NOT EXISTS "EnrollmentCommand" (
 "objectId" text PRIMARY KEY,"company" text NOT NULL,"createdAt" timestamptz,"updatedAt" timestamptz,
 "idempotencyKey" text NOT NULL,"request" jsonb,"result" jsonb
);
CREATE UNIQUE INDEX IF NOT EXISTS enrollment_command_request ON "EnrollmentCommand" ("company","idempotencyKey");
ALTER TABLE "CourseBinding" ADD COLUMN IF NOT EXISTS "categoryKey" text;
ALTER TABLE "CourseBinding" ADD COLUMN IF NOT EXISTS "categoryName" text;
ALTER TABLE "CourseBinding" ADD COLUMN IF NOT EXISTS "enrollmentStatus" text;
ALTER TABLE "CourseAppointment" ADD COLUMN IF NOT EXISTS "bindingId" text;
ALTER TABLE "LessonRecord" ADD COLUMN IF NOT EXISTS "bindingId" text;
ALTER TABLE "LessonRecord" ADD COLUMN IF NOT EXISTS "courseCategoryKey" text;
ALTER TABLE "LessonRecord" ADD COLUMN IF NOT EXISTS "courseCategoryName" text;
ALTER TABLE "LessonRecord" ADD COLUMN IF NOT EXISTS "teacherPaySnapshot" numeric;
ALTER TABLE "LessonRecord" ADD COLUMN IF NOT EXISTS "creditCostsSnapshot" jsonb;
ALTER TABLE "DailyStudyRecord" ADD COLUMN IF NOT EXISTS "bindingId" text;
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 "transactionId" text',t);
 END LOOP;
END $$;

CREATE OR REPLACE FUNCTION xs_object_id() RETURNS text LANGUAGE sql VOLATILE AS $$
 SELECT substr(md5(random()::text || clock_timestamp()::text),1,20)
$$;
CREATE OR REPLACE FUNCTION xs_insert(t text, d jsonb) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
 IF NOT t=ANY(ARRAY['CreditTransaction','EnrollmentCommand','CourseBinding','CommonModel','LessonRecord','CourseAppointment','CourseCreditReservation','UserExpDomP','UserSIcon','UserExpHis','UserUserPoint','SysLog']) THEN RAISE EXCEPTION 'Invalid business table'; END IF;
 EXECUTE format('INSERT INTO %I SELECT * FROM jsonb_populate_record(NULL::%I,$1)',t,t) USING d;
END $$;
CREATE OR REPLACE FUNCTION xs_reserved(co text, uid numeric, account text, excluded text DEFAULT '') RETURNS numeric LANGUAGE sql STABLE AS $$
 SELECT COALESCE(sum((cost->>'amount')::numeric),0) FROM "CourseCreditReservation" r,
 LATERAL jsonb_array_elements(COALESCE(r."creditCosts"::jsonb,'[]')) cost
 WHERE r."company"=co AND r."studentId"=uid AND r."state"='reserved'
 AND COALESCE(r."appointmentId",'')<>excluded AND cost->>'account'=account
$$;
CREATE OR REPLACE FUNCTION xs_ledger(co text, uid numeric, account text, delta numeric, before_value numeric, tx text, actor text, reason text, method text DEFAULT '') RETURNS void LANGUAGE plpgsql AS $$
DECLARE t text; typ int;
BEGIN
 t=CASE account WHEN 'Purse' THEN 'UserExpDomP' WHEN 'SilverCoin' THEN 'UserSIcon' WHEN 'UserExp' THEN 'UserExpHis' WHEN 'UserPoint' THEN 'UserUserPoint' END;
 typ=array_position(ARRAY['Purse','SilverCoin','UserExp','UserPoint'],account);
 IF t IS NULL THEN RAISE EXCEPTION '课时类型无效'; END IF;
 PERFORM xs_insert(t,jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'sourceKey','credit:'||tx||':'||uid||':'||account,
 'transactionId',tx,'userId',uid,'score',delta,'scoreBefore',before_value,'hisTime',now(),'operator',CASE WHEN delta>0 THEN 1 ELSE 2 END,'scoreType',typ,'detail',reason,'remark',actor,'payMethod',method));
END $$;
CREATE OR REPLACE FUNCTION xs_credit_post(co text, actor text, kind text, target text, account text, amount numeric, original text, reason text, method text, idem text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE u "_User"; st "_User"; src "CreditTransaction"; existing "CreditTransaction"; req jsonb; tx text=xs_object_id(); delta numeric; sb numeric; ub numeric; refunded numeric; result jsonb;
BEGIN
 IF co IS NULL OR length(idem)<8 OR length(trim(reason))<2 OR amount IS NULL OR amount<=0 OR amount>1000000000 OR amount::text IN ('NaN','Infinity') THEN RAISE EXCEPTION '数量、操作原因或请求编号无效'; END IF;
 IF account IS NULL OR NOT account=ANY(ARRAY['Purse','SilverCoin','UserExp','UserPoint']) OR (account<>'UserPoint' AND trunc(amount)<>amount) OR round(amount,2)<>amount THEN RAISE EXCEPTION '课时必须为整数，陪练时长最多两位小数'; END IF;
 IF NOT kind=ANY(ARRAY['fund','transfer','refund']) OR length(method)=0 THEN RAISE EXCEPTION '业务类型或付款方式无效'; END IF;
 req=jsonb_build_object('actor',actor,'kind',kind,'target',target,'account',account,'amount',amount,'original',original,'reason',reason,'method',method);
 -- A company lock orders all fund/transfer/enrollment operations; account locks also serialize scheduling and completion.
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT * INTO existing FROM "CreditTransaction" WHERE "company"=co AND "idempotencyKey"=idem;
 IF FOUND THEN IF existing."request"<>req THEN RAISE EXCEPTION '请求编号已用于其他操作'; END IF; RETURN to_jsonb(existing); END IF;
 SELECT * INTO u FROM "_User" WHERE "company"=co AND "objectId"=target;
 IF NOT FOUND OR COALESCE(u."isDisabled",false) THEN RAISE EXCEPTION '账户不存在或已停用'; END IF;
 IF kind='fund' THEN
  IF COALESCE(u."identityType",'')<>'store' AND COALESCE(u."legacyGroupId",0)<>2 THEN RAISE EXCEPTION '目标必须是门店'; END IF; st=u;
 ELSE
  IF COALESCE(u."identityType",'')<>'member' AND COALESCE(u."legacyGroupId",0)<>1 THEN RAISE EXCEPTION '目标必须是学员'; END IF;
  IF kind='refund' THEN
   SELECT t.* INTO src FROM "CreditTransaction" t WHERE t."company"=co AND t."objectId"=original AND t."kind"='transfer';
   IF NOT FOUND OR src."studentId"<>u."legacyUserId" OR src."account"<>account THEN RAISE EXCEPTION '原划拨单不存在或不匹配；历史流水需先核对'; END IF;
   SELECT COALESCE(sum(t."amount"),0) INTO refunded FROM "CreditTransaction" t WHERE t."company"=co AND t."kind"='refund' AND t."sourceTransactionId"=original;
   IF amount+refunded>src."amount" THEN RAISE EXCEPTION '超过原划拨单可退数量'; END IF;
   SELECT * INTO st FROM "_User" WHERE "company"=co AND "legacyUserId"=src."storeId";
  ELSE SELECT * INTO st FROM "_User" WHERE "company"=co AND "legacyUserId"=COALESCE(NULLIF(u."legacyUserData"->>'ParentUserID',''),'0')::numeric; END IF;
  IF st."objectId" IS NULL OR COALESCE(st."isDisabled",false) OR (COALESCE(st."identityType",'')<>'store' AND COALESCE(st."legacyGroupId",0)<>2) THEN RAISE EXCEPTION '请先关联有效门店'; END IF;
 END IF;
 PERFORM 1 FROM "_User" WHERE "objectId" IN(u."objectId",st."objectId") ORDER BY "objectId" FOR UPDATE;
 SELECT * INTO u FROM "_User" WHERE "objectId"=u."objectId";
 SELECT * INTO st FROM "_User" WHERE "objectId"=st."objectId";
 IF COALESCE(u."isDisabled",false) OR COALESCE(st."isDisabled",false) THEN RAISE EXCEPTION '账户已停用';END IF;
 IF kind='transfer' AND COALESCE(NULLIF(u."legacyUserData"->>'ParentUserID',''),'0')::numeric<>st."legacyUserId" THEN RAISE EXCEPTION '所属门店已变更，请重新提交';END IF;
 PERFORM set_config('xiaoshu.credit_write','yes',true);
 sb=COALESCE((st."legacyUserData"->>account)::numeric,0);ub=COALESCE((u."legacyUserData"->>account)::numeric,0);
 IF kind='transfer' AND sb-xs_reserved(co,st."legacyUserId"::numeric,account)<amount THEN RAISE EXCEPTION '门店可用课时不足'; END IF;
 IF kind='refund' AND ub-xs_reserved(co,u."legacyUserId"::numeric,account)<amount THEN RAISE EXCEPTION '学员可退课时不足，已使用或已预留课时不能退回'; END IF;
 delta=CASE WHEN kind='transfer' THEN -amount ELSE amount END;
 UPDATE "_User" SET "legacyUserData"=jsonb_set(COALESCE("legacyUserData",'{}'),ARRAY[account],to_jsonb(sb+delta)),"updatedAt"=now() WHERE "objectId"=st."objectId";
 PERFORM xs_ledger(co,st."legacyUserId"::numeric,account,delta,sb,tx,actor,reason,method);
 IF kind<>'fund' THEN
  UPDATE "_User" SET "legacyUserData"=jsonb_set(COALESCE("legacyUserData",'{}'),ARRAY[account],to_jsonb(ub-delta)),"updatedAt"=now() WHERE "objectId"=u."objectId";
  PERFORM xs_ledger(co,u."legacyUserId"::numeric,account,-delta,ub,tx,actor,reason,method);
 END IF;
 result=jsonb_build_object('objectId',tx,'company',co,'createdAt',now(),'updatedAt',now(),'idempotencyKey',idem,'request',req,'kind',kind,'account',account,'amount',amount,'storeId',st."legacyUserId",'studentId',CASE WHEN kind='fund' THEN NULL ELSE u."legacyUserId" END,'sourceTransactionId',original,'operatorId',actor,'reason',reason,'payMethod',method,'balances',jsonb_build_object('storeBefore',sb,'storeAfter',sb+delta,'studentBefore',ub,'studentAfter',CASE WHEN kind='fund' THEN NULL ELSE ub-delta END));
 PERFORM xs_insert('CreditTransaction',result);
 PERFORM xs_insert('SysLog',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'sourceKey','credit:'||tx,'id',nextval('xiaoshu_business_id'),'cdate',now(),'cname','课时业务','type1','operations-admin','type2','credit-'||kind,'type3','CreditTransaction','clevel',0,'cadminId',(SELECT "legacyUserId" FROM "_User" WHERE "objectId"=actor),'detail',result::text,'content1',reason,'content2',actor));
 RETURN result;
END $$;

-- Safely repair only unambiguous historical mappings. Preserve conflicting progress for review.
UPDATE "CourseBinding" b SET "categoryKey"=p."categoryKey","categoryName"=CASE p."categoryKey" WHEN 'word' THEN '单词课' WHEN 'trial' THEN '体验课' WHEN 'primary_writing' THEN '小学语法写作课' WHEN 'middle_writing' THEN '中学语法写作课' WHEN 'high_writing' THEN '高中语法写作课' WHEN 'word_self_study' THEN '单词自学课' ELSE p."categoryKey" END,"enrollmentStatus"=CASE WHEN EXISTS(SELECT 1 FROM "CourseBinding" other WHERE other."company"=b."company" AND other."yhid"=b."yhid" AND other."kcid"=b."kcid" AND other."objectId"<>b."objectId" AND COALESCE(other."enrollmentStatus",'')<>'recycled' AND (other."categoryKey" IS NULL OR other."categoryKey"=p."categoryKey")) THEN 'pending' ELSE 'active' END
FROM (SELECT "company","courseId",min("categoryKey") AS "categoryKey",true AS "confirmed" FROM "CourseTeachingProfile" WHERE "confirmed"=true GROUP BY "company","courseId" HAVING count(DISTINCT "categoryKey")=1) p WHERE b."company"=p."company" AND b."kcid"::text=p."courseId"::text AND p."confirmed"=true
AND p."categoryKey" IN ('word','trial','primary_writing','middle_writing','high_writing','word_self_study') AND b."categoryKey" IS NULL;
UPDATE "CourseBinding" SET "enrollmentStatus"=CASE WHEN "categoryKey" IN ('word','trial','primary_writing','middle_writing','high_writing','word_self_study') THEN 'active' ELSE 'pending' END WHERE "enrollmentStatus" IS NULL;
UPDATE "CourseBinding" b SET "enrollmentStatus"='recycled' FROM "CommonModel" c WHERE c."company"=b."company" AND c."modelId"=58 AND c."itemId"=b."id" AND c."status"=-2;
UPDATE "CourseBinding" b SET "enrollmentStatus"='pending' WHERE (b."company",b."yhid",b."kcid",b."categoryKey") IN
(SELECT "company","yhid","kcid","categoryKey" FROM "CourseBinding" WHERE "enrollmentStatus"='active' GROUP BY 1,2,3,4 HAVING count(*)>1);
UPDATE "CommonModel" c SET "nodeId"=28,"tableName"='ZL_C_kcbd' FROM "CourseBinding" b WHERE c."company"=b."company" AND c."modelId"=58 AND c."itemId"=b."id" AND (c."nodeId"<>28 OR c."tableName"<>'ZL_C_kcbd');
UPDATE "CourseBinding" b SET "enrollmentStatus"='pending' WHERE b."enrollmentStatus"='active' AND (
 b."categoryKey" NOT IN ('word','trial','primary_writing','middle_writing','high_writing','word_self_study')
 OR NOT EXISTS(SELECT 1 FROM "CommonModel" c WHERE c."company"=b."company" AND c."modelId"=58 AND c."itemId"=b."id")
 OR NOT EXISTS(SELECT 1 FROM "Node" n WHERE n."company"=b."company" AND n."nodeId"::text=b."kcid"::text)
 OR NOT EXISTS(SELECT 1 FROM "_User" u WHERE u."company"=b."company" AND u."legacyUserId"::text=b."yhid"::text));
CREATE UNIQUE INDEX IF NOT EXISTS enrollment_active_identity ON "CourseBinding" ("company","yhid","categoryKey","kcid") WHERE "enrollmentStatus"='active';

CREATE OR REPLACE FUNCTION xs_course_size(co text,course text) RETURNS numeric LANGUAGE plpgsql AS $$
DECLARE total numeric;
BEGIN
 PERFORM 1 FROM "Node" WHERE "company"=co AND "nodeId"::text=course AND COALESCE("zstatus",99)=99 FOR SHARE;
 IF NOT FOUND THEN RAISE EXCEPTION '课程内容不存在或已停用';END IF;
 WITH RECURSIVE nodes AS (
  SELECT n."nodeId",ARRAY[n."nodeId"] visited FROM "Node" n WHERE n."company"=co AND n."nodeId"::text=course
  UNION ALL SELECT n."nodeId",p.visited||n."nodeId" FROM nodes p JOIN "Node" n ON n."company"=co AND n."parentId"=p."nodeId" WHERE NOT n."nodeId"=ANY(p.visited) AND COALESCE(n."zstatus",99)=99
 ) SELECT count(DISTINCT c."objectId") INTO total FROM nodes n JOIN "CommonModel" c ON c."company"=co AND c."nodeId"=n."nodeId" AND c."modelId"=52 AND COALESCE(c."status",99)=99;
 IF total=0 THEN RAISE EXCEPTION '课程内容为空';END IF;
 RETURN total;
END $$;
CREATE OR REPLACE FUNCTION xs_enroll(co text, actor text, uid numeric, category text, items jsonb, costs jsonb, reason text, idem text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE u "_User"; req jsonb; old "EnrollmentCommand"; item jsonb; option_costs jsonb; cost jsonb; affordable boolean=false; valid boolean; ids jsonb='[]'; bid text; cid text; ident bigint; existing text; content_count numeric;
BEGIN
 IF NOT category=ANY(ARRAY['word','trial','primary_writing','middle_writing','high_writing','word_self_study']) OR jsonb_array_length(items)<1 OR length(idem)<8 THEN RAISE EXCEPTION '请选择分类和课程内容'; END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 req=jsonb_build_object('uid',uid,'category',category,'courseIds',(SELECT jsonb_agg((value->>'courseId')::numeric ORDER BY (value->>'courseId')::numeric) FROM jsonb_array_elements(items)),'actor',actor,'reason',reason);
 SELECT * INTO old FROM "EnrollmentCommand" WHERE "company"=co AND "idempotencyKey"=idem;
 IF FOUND THEN IF old."request"<>req THEN RAISE EXCEPTION '请求编号已用于其他开课操作'; END IF;RETURN old."result";END IF;
 SELECT * INTO u FROM "_User" WHERE "company"=co AND "legacyUserId"=uid FOR UPDATE;
 IF NOT FOUND OR COALESCE(u."isDisabled",false) OR COALESCE(u."identityType",'member')<>'member' THEN RAISE EXCEPTION '学员不存在或已停用'; END IF;
 FOR option_costs IN SELECT value FROM jsonb_array_elements(costs) LOOP
  valid=jsonb_array_length(option_costs)>0;
  FOR cost IN SELECT value FROM jsonb_array_elements(option_costs) LOOP
   IF COALESCE((u."legacyUserData"->>(cost->>'account'))::numeric,0)-xs_reserved(co,uid,cost->>'account')<(cost->>'amount')::numeric THEN valid=false;END IF;
  END LOOP;
  affordable=affordable OR valid;
 END LOOP;
 IF NOT affordable THEN RAISE EXCEPTION '对应可用课时不足，请先充值（含规则要求的陪练时长）'; END IF;
 FOR item IN SELECT value FROM jsonb_array_elements(items) LOOP
  content_count=xs_course_size(co,item->>'courseId');
  SELECT b."objectId" INTO existing FROM "CourseBinding" b WHERE b."company"=co AND b."yhid"::text=uid::text AND b."kcid"::text=item->>'courseId' AND b."categoryKey"=category AND b."enrollmentStatus"='active';
  IF existing IS NOT NULL THEN RAISE EXCEPTION '该内容在所选分类已开课，请刷新列表';END IF;
  bid=xs_object_id();cid=xs_object_id();ident=nextval('xiaoshu_business_id');
  PERFORM xs_insert('CourseBinding',jsonb_build_object('objectId',bid,'company',co,'id',ident,'createdAt',now(),'updatedAt',now(),'sourceKey','enroll:'||bid,'yhid',uid,'kcid',(item->>'courseId')::numeric,'yxx',0,'cksl',content_count,'syjd','','categoryKey',category,'categoryName',item->>'categoryName','enrollmentStatus','active'));
  PERFORM xs_insert('CommonModel',jsonb_build_object('objectId',cid,'company',co,'createdAt',now(),'updatedAt',now(),'createTime',now(),'sourceKey','enroll:'||bid,'generalId',nextval('xiaoshu_business_id'),'itemId',ident,'modelId',58,'nodeId',28,'tableName','ZL_C_kcbd','status',99,'title',item->>'courseName','inputer',actor));
  ids=ids||jsonb_build_array(bid);
 END LOOP;
 req=jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'idempotencyKey',idem,'request',req,'result',jsonb_build_object('bindingIds',ids,'count',jsonb_array_length(ids)));
 PERFORM xs_insert('EnrollmentCommand',req);RETURN req->'result';
END $$;

-- Shared account lock protects EVERY reservation against parallel transfers/refunds/completion.
CREATE OR REPLACE FUNCTION xs_reservation_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE u "_User"; cost jsonb;
BEGIN
 SELECT * INTO u FROM "_User" WHERE "company"=NEW."company" AND "legacyUserId"=NEW."studentId" FOR UPDATE;
 IF NEW."state"='reserved' THEN
  IF COALESCE(jsonb_array_length(NEW."creditCosts"::jsonb),0)=0 THEN RAISE EXCEPTION '预约缺少有效课时';END IF;
  IF u."objectId" IS NULL OR COALESCE(u."isDisabled",false) THEN RAISE EXCEPTION '学员不存在或已停用'; END IF;
  FOR cost IN SELECT value FROM jsonb_array_elements(NEW."creditCosts"::jsonb) LOOP
   IF NOT (cost->>'account')=ANY(ARRAY['Purse','SilverCoin','UserExp','UserPoint']) OR (cost->>'amount')::numeric<=0 OR COALESCE((u."legacyUserData"->>(cost->>'account'))::numeric,0)-xs_reserved(NEW."company",NEW."studentId"::numeric,cost->>'account',COALESCE(NEW."appointmentId",''))<(cost->>'amount')::numeric THEN RAISE EXCEPTION '可用课时不足，不能重复预留';END IF;
  END LOOP;
 END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS xs_reservation_guard ON "CourseCreditReservation";
CREATE TRIGGER xs_reservation_guard BEFORE INSERT OR UPDATE ON "CourseCreditReservation" FOR EACH ROW EXECUTE FUNCTION xs_reservation_guard();
CREATE OR REPLACE FUNCTION xs_enrollment_status(co text,bid text,restore boolean,actor text,reason text,costs jsonb DEFAULT '[]') RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE b "CourseBinding"; u "_User"; option_costs jsonb; cost jsonb; valid boolean; affordable boolean=false;
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT * INTO b FROM "CourseBinding" WHERE "company"=co AND "objectId"=bid;
 IF NOT FOUND THEN RAISE EXCEPTION '开课记录不存在';END IF;
 SELECT * INTO u FROM "_User" WHERE "company"=co AND "legacyUserId"::text=b."yhid"::text FOR UPDATE;
 IF EXISTS(SELECT 1 FROM "CourseAppointment" a JOIN "CommonModel" c ON c."company"=a."company" AND c."modelId"=54 AND c."itemId"=a."id" WHERE a."company"=co AND a."szyh"::text=b."yhid"::text AND c."status"<>-2 AND a."dszt"::text IN ('0','10') AND (a."bindingId"=bid OR (a."bindingId" IS NULL AND a."kcid"::text=b."kcid"::text))) THEN RAISE EXCEPTION '请先处理该课程未结束的预约';END IF;
 IF restore AND (b."categoryKey" IS NULL OR b."enrollmentStatus"='pending') THEN RAISE EXCEPTION '历史开课分类待确认';END IF;
 IF restore THEN
  PERFORM xs_course_size(co,b."kcid"::text);
  IF COALESCE(u."isDisabled",false) THEN RAISE EXCEPTION '学员已停用';END IF;
  FOR option_costs IN SELECT value FROM jsonb_array_elements(costs) LOOP
   valid=jsonb_array_length(option_costs)>0;
   FOR cost IN SELECT value FROM jsonb_array_elements(option_costs) LOOP
    IF COALESCE((u."legacyUserData"->>(cost->>'account'))::numeric,0)-xs_reserved(co,b."yhid"::numeric,cost->>'account')<(cost->>'amount')::numeric THEN valid=false;END IF;
   END LOOP;
   affordable=affordable OR valid;
  END LOOP;
  IF NOT affordable THEN RAISE EXCEPTION '对应可用课时不足，请先充值';END IF;
 END IF;
 UPDATE "CourseBinding" SET "enrollmentStatus"=CASE WHEN restore THEN 'active' ELSE 'recycled' END,"updatedAt"=now() WHERE "objectId"=bid;
 UPDATE "CommonModel" SET "status"=CASE WHEN restore THEN 99 ELSE -2 END,"updatedAt"=now() WHERE "company"=co AND "modelId"=58 AND "itemId"=b."id";
 PERFORM xs_insert('EnrollmentCommand',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'idempotencyKey',xs_object_id(),'request',jsonb_build_object('actor',actor,'reason',reason,'bindingId',bid,'restore',restore),'result','{}'::jsonb));
 RETURN jsonb_build_object('bindingId',bid,'restored',restore);
END $$;
CREATE OR REPLACE FUNCTION xs_validate_appointment(co text,aid text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE a "CourseAppointment"; c "CommonModel"; u "_User"; matches int; cost jsonb;
BEGIN
 SELECT * INTO c FROM "CommonModel" WHERE "company"=co AND "modelId"=54 AND "objectId"=aid;
 SELECT * INTO a FROM "CourseAppointment" WHERE "company"=co AND "id"=c."itemId";
 IF a."objectId" IS NULL OR c."status"=-2 THEN RAISE EXCEPTION '预约不存在或已取消';END IF;
 SELECT * INTO u FROM "_User" WHERE "company"=co AND "legacyUserId"::text=a."szyh"::text FOR UPDATE;
 IF u."objectId" IS NULL OR COALESCE(u."isDisabled",false) THEN RAISE EXCEPTION '学员不存在或已停用'; END IF;
 SELECT count(*) INTO matches FROM "CourseBinding" b JOIN "CommonModel" bc ON bc."company"=b."company" AND bc."modelId"=58 AND bc."itemId"=b."id" WHERE b."company"=co AND b."yhid"::text=a."szyh"::text AND b."kcid"::text=a."kcid"::text AND b."enrollmentStatus"='active' AND bc."status"=99 AND (NULLIF(a."bindingId",'') IS NULL OR b."objectId"=a."bindingId");
 PERFORM xs_course_size(co,a."kcid"::text);
 IF matches<>1 THEN RAISE EXCEPTION '预约必须关联唯一有效开课记录，请先确认分类';END IF;
 IF COALESCE(a."creditHoldStatus",'ready')<>'ready' THEN RAISE EXCEPTION '历史预约课时待处理';END IF;
 IF jsonb_array_length(COALESCE(a."creditCostsSnapshot"::jsonb,'[]'))=0 THEN RAISE EXCEPTION '预约缺少课时快照';END IF;
 FOR cost IN SELECT value FROM jsonb_array_elements(a."creditCostsSnapshot"::jsonb) LOOP
  IF COALESCE((u."legacyUserData"->>(cost->>'account'))::numeric,0)-xs_reserved(co,u."legacyUserId"::numeric,cost->>'account',aid)<(cost->>'amount')::numeric THEN RAISE EXCEPTION '可用课时不足'; END IF;
 END LOOP;
 RETURN u."objectId";
END $$;
CREATE OR REPLACE FUNCTION xs_complete_appointment(co text,aid text,actor text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE a "CourseAppointment"; c "CommonModel"; u "_User"; existing text; lid text=xs_object_id(); lc text=xs_object_id(); ident bigint; cost jsonb; before_value numeric; tx text; charged boolean=false;
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 -- Acquire user before appointment/reservation locks, matching transfer and reservation lock ordering.
 SELECT * INTO c FROM "CommonModel" WHERE "company"=co AND "objectId"=aid AND "modelId"=54;
 SELECT * INTO a FROM "CourseAppointment" WHERE "company"=co AND "id"=c."itemId";
 SELECT * INTO u FROM "_User" WHERE "company"=co AND "legacyUserId"::text=a."szyh"::text FOR UPDATE;
 IF u."objectId" IS NULL THEN RAISE EXCEPTION '预约学员不存在';END IF;
 SELECT "objectId" INTO existing FROM "LessonRecord" WHERE "company"=co AND "yyds"::text=c."generalId"::text LIMIT 1;
 IF existing IS NOT NULL THEN RETURN jsonb_build_object('lessonId',existing,'idempotent',true,'periodDeducted',false);END IF;
 SELECT * INTO a FROM "CourseAppointment" WHERE "objectId"=a."objectId" FOR UPDATE;
 IF a."dszt"::text NOT IN ('10','11') OR COALESCE(NULLIF(a."pl"::text,''),a."fxpl"::text,'0')='0' THEN RAISE EXCEPTION '预约尚未上课或未分配教师';END IF;
 PERFORM xs_validate_appointment(co,aid);
 SELECT EXISTS(SELECT 1 FROM "CourseCreditReservation" WHERE "company"=co AND "appointmentId"=aid AND "state"='consumed') INTO charged;
 tx='complete:'||aid;
 IF NOT charged THEN
  PERFORM set_config('xiaoshu.credit_write','yes',true);
  UPDATE "CourseCreditReservation" SET "state"='consumed',"consumedAt"=now(),"updatedAt"=now() WHERE "company"=co AND "appointmentId"=aid;
  FOR cost IN SELECT value FROM jsonb_array_elements(a."creditCostsSnapshot"::jsonb) LOOP
   before_value=COALESCE((u."legacyUserData"->>(cost->>'account'))::numeric,0);
   u."legacyUserData"=jsonb_set(u."legacyUserData",ARRAY[cost->>'account'],to_jsonb(before_value-(cost->>'amount')::numeric));
   PERFORM xs_ledger(co,u."legacyUserId"::numeric,cost->>'account',-(cost->>'amount')::numeric,before_value,tx,actor,'完课扣除课时');
  END LOOP;
  UPDATE "_User" SET "legacyUserData"=u."legacyUserData","updatedAt"=now() WHERE "objectId"=u."objectId";
 END IF;
 ident=nextval('xiaoshu_business_id');
 PERFORM xs_insert('LessonRecord',jsonb_build_object('objectId',lid,'company',co,'id',ident,'sourceKey',tx,'createdAt',now(),'updatedAt',now(),'xymz',a."szyh",'jsmz',COALESCE(NULLIF(a."pl"::text,''),a."fxpl"::text),'kcid',a."kcid",'kclx',a."dslx",'kcmc',c."subtitle",'yyds',c."generalId"::text,'jffs',a."jffs",'kzsj','[]','lessonAt',now(),'bindingId',a."bindingId",'courseCategoryKey',a."courseCategoryKey",'courseCategoryName',a."courseCategoryName",'teacherPaySnapshot',a."teacherPaySnapshot",'creditCostsSnapshot',a."creditCostsSnapshot"));
 PERFORM xs_insert('CommonModel',jsonb_build_object('objectId',lc,'company',co,'createdAt',now(),'updatedAt',now(),'sourceKey',tx,'generalId',nextval('xiaoshu_business_id'),'itemId',ident,'modelId',59,'nodeId',296,'tableName','ZL_C_skjl','title',c."title",'inputer',actor,'status',99));
 UPDATE "CourseAppointment" SET "dszt"='11',"jssj"=COALESCE(NULLIF("jssj",''),now()::text),"updatedAt"=now() WHERE "objectId"=a."objectId";
 RETURN jsonb_build_object('lessonId',lid,'idempotent',false,'periodDeducted',NOT charged);
END $$;
CREATE OR REPLACE FUNCTION xs_schedule_save(co text,actor text,req jsonb,entries jsonb,idem text,edit_id text DEFAULT '',expected text DEFAULT '') RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE old "EnrollmentCommand"; entry jsonb; uid numeric; student "_User"; b "CourseBinding"; cid text; aid text; ident bigint; common_data jsonb; addon_data jsonb; ids jsonb='[]'; costs jsonb; old_c "CommonModel"; old_a "CourseAppointment"; requested_start timestamptz; requested_end timestamptz;
BEGIN
 IF length(idem)<8 OR jsonb_array_length(entries)=0 THEN RAISE EXCEPTION '排课请求无效';END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT * INTO old FROM "EnrollmentCommand" WHERE "company"=co AND "idempotencyKey"=idem;
 IF FOUND THEN IF old."request"<>req THEN RAISE EXCEPTION '请求编号已用于其他排课操作';END IF;RETURN old."result";END IF;
 IF edit_id<>'' THEN
  SELECT * INTO old_c FROM "CommonModel" WHERE "company"=co AND "objectId"=edit_id AND "modelId"=54;
  SELECT * INTO old_a FROM "CourseAppointment" WHERE "company"=co AND "id"=old_c."itemId";
  IF old_a."objectId" IS NULL OR old_a."dszt"::text<>'0' OR old_c."status"=-2 THEN RAISE EXCEPTION '只有未开始的预约可以修改';END IF;
  IF expected<>'' AND old_c."updatedAt"<>expected::timestamptz THEN RAISE EXCEPTION '预约已修改，请刷新';END IF;
 END IF;
 -- Account locks precede reservation locks. Stable order handles changing an appointment's student.
 PERFORM 1 FROM "_User" WHERE "company"=co AND ("legacyUserId"::text=old_a."szyh"::text OR "legacyUserId"::text IN(SELECT value->'addon'->>'szyh' FROM jsonb_array_elements(entries))) ORDER BY "objectId" FOR UPDATE;
 FOR entry IN SELECT value FROM jsonb_array_elements(entries) LOOP
  addon_data=entry->'addon';uid=(addon_data->>'szyh')::numeric;costs=addon_data->'creditCostsSnapshot';
  SELECT * INTO student FROM "_User" WHERE "company"=co AND "legacyUserId"=uid;
  SELECT * INTO b FROM "CourseBinding" WHERE "company"=co AND "objectId"=addon_data->>'bindingId' AND "yhid"::text=uid::text AND "enrollmentStatus"='active';
  IF b."objectId" IS NULL OR b."kcid"::text<>addon_data->>'kcid' OR b."categoryKey"<>addon_data->>'courseCategoryKey' OR COALESCE(student."isDisabled",false) THEN RAISE EXCEPTION '学员或开课记录已失效';END IF;
  PERFORM xs_course_size(co,b."kcid"::text);
  requested_start=(addon_data->>'yysj')::timestamp AT TIME ZONE 'Asia/Shanghai';requested_end=requested_start+make_interval(mins=>(addon_data->>'durationMinutes')::int);
  IF EXISTS(SELECT 1 FROM "CourseAppointment" a JOIN "CommonModel" c ON c."company"=a."company" AND c."modelId"=54 AND c."itemId"=a."id" WHERE a."company"=co AND c."status"<>-2 AND c."objectId"<>edit_id AND a."dszt"::text IN ('0','10','11','20') AND (a."szyh"::text=uid::text OR (NULLIF(addon_data->>'pl','') IS NOT NULL AND a."pl"::text=addon_data->>'pl')) AND COALESCE(a."yysj",'') ~ '^\d{4}-\d{2}-\d{2} ' AND (a."yysj"::timestamp AT TIME ZONE 'Asia/Shanghai')<requested_end AND (a."yysj"::timestamp AT TIME ZONE 'Asia/Shanghai')+make_interval(mins=>COALESCE(a."durationMinutes",30)::int)>requested_start) THEN RAISE EXCEPTION '会员或老师时间冲突，请重新检查排课';END IF;
  IF edit_id='' THEN
   cid=xs_object_id();aid=xs_object_id();ident=nextval('xiaoshu_business_id');
   addon_data=addon_data||jsonb_build_object('objectId',aid,'id',ident,'company',co,'createdAt',now(),'updatedAt',now(),'sourceKey','schedule:'||idem||':'||jsonb_array_length(ids));
   common_data=(entry->'common')||jsonb_build_object('objectId',cid,'company',co,'createdAt',now(),'updatedAt',now(),'createTime',now(),'sourceKey','schedule:'||idem||':'||jsonb_array_length(ids),'generalId',nextval('xiaoshu_business_id'),'itemId',ident,'modelId',54,'nodeId',29,'tableName','ZL_C_order','status',99,'inputer',actor);
   PERFORM xs_insert('CourseAppointment',addon_data);PERFORM xs_insert('CommonModel',common_data);
  ELSE
   cid=old_c."objectId";aid=old_a."objectId";
   -- Preserve all unrelated lesson/feedback fields; replace only scheduling fields supplied by the gateway.
   addon_data=to_jsonb(old_a)||addon_data||jsonb_build_object('updatedAt',now());
   UPDATE "CourseAppointment" a SET ("szyh","pl","fxpl","plxm","kcid","yykcid","dslx","jffs","bindingId","courseCategoryKey","courseCategoryName","durationMinutes","deliveryMode","teachingRuleId","teacherPaySnapshot","creditCostsSnapshot","bxrq","sdsd","yysj","updatedAt")=(SELECT r."szyh",r."pl",r."fxpl",r."plxm",r."kcid",r."yykcid",r."dslx",r."jffs",r."bindingId",r."courseCategoryKey",r."courseCategoryName",r."durationMinutes",r."deliveryMode",r."teachingRuleId",r."teacherPaySnapshot",r."creditCostsSnapshot",r."bxrq",r."sdsd",r."yysj",r."updatedAt" FROM jsonb_populate_record(NULL::"CourseAppointment",addon_data) r) WHERE a."objectId"=aid;
   UPDATE "CommonModel" SET "subtitle"=entry->'common'->>'subtitle',"updatedAt"=now() WHERE "objectId"=cid;
   UPDATE "CourseCreditReservation" SET "state"='released',"updatedAt"=now() WHERE "company"=co AND "appointmentId"=cid;
   DELETE FROM "CourseCreditReservation" WHERE "company"=co AND "appointmentId"=cid AND "state"='released';
   common_data=to_jsonb(old_c);
  END IF;
  PERFORM xs_insert('CourseCreditReservation',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'sourceKey','cloud:appointment-reservation:'||cid,'appointmentId',cid,'appointmentGeneralId',common_data->'generalId','studentId',uid,'ruleId',addon_data->>'teachingRuleId','creditCosts',costs,'state','reserved','reservedAt',now()));
  ids=ids||jsonb_build_array(cid);
 END LOOP;
 common_data=jsonb_build_object('created',jsonb_array_length(ids),'targetIds',ids);
 PERFORM xs_insert('EnrollmentCommand',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'idempotencyKey',idem,'request',req,'result',common_data));
 RETURN common_data;
END $$;
CREATE OR REPLACE FUNCTION xs_schedule_cancel(co text,aid text,restore boolean,actor text,reason text,expected text DEFAULT '') RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE c "CommonModel"; a "CourseAppointment"; u "_User"; requested_start timestamptz; requested_end timestamptz; restore_coach text;
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT * INTO c FROM "CommonModel" WHERE "company"=co AND "objectId"=aid AND "modelId"=54;
 IF expected<>'' AND c."updatedAt"<>expected::timestamptz THEN RAISE EXCEPTION '预约已变更，请刷新';END IF;
 SELECT * INTO a FROM "CourseAppointment" WHERE "company"=co AND "id"=c."itemId";
 SELECT * INTO u FROM "_User" WHERE "company"=co AND "legacyUserId"::text=a."szyh"::text FOR UPDATE;
 SELECT * INTO a FROM "CourseAppointment" WHERE "objectId"=a."objectId" FOR UPDATE;
 IF a."objectId" IS NULL OR a."dszt"::text NOT IN ('0','10') THEN RAISE EXCEPTION '预约已经上课或不存在';END IF;
 IF restore THEN
  requested_start=a."yysj"::timestamp AT TIME ZONE 'Asia/Shanghai';requested_end=requested_start+make_interval(mins=>COALESCE(a."durationMinutes",30)::int);restore_coach=a."pl"::text;
  IF EXISTS(SELECT 1 FROM "CourseAppointment" ca JOIN "CommonModel" cc ON cc."company"=ca."company" AND cc."modelId"=54 AND cc."itemId"=ca."id" WHERE ca."company"=co AND cc."status"<>-2 AND cc."objectId"<>aid AND ca."dszt"::text IN ('0','10','11','20') AND (ca."szyh"::text=u."legacyUserId"::text OR (NULLIF(restore_coach,'') IS NOT NULL AND ca."pl"::text=restore_coach)) AND COALESCE(ca."yysj",'') ~ '^\d{4}-\d{2}-\d{2} ' AND (ca."yysj"::timestamp AT TIME ZONE 'Asia/Shanghai')<requested_end AND (ca."yysj"::timestamp AT TIME ZONE 'Asia/Shanghai')+make_interval(mins=>COALESCE(ca."durationMinutes",30)::int)>requested_start) THEN RAISE EXCEPTION '会员或老师时间冲突，请重新检查排课';END IF;
  UPDATE "CommonModel" SET "status"=99,"updatedAt"=now() WHERE "objectId"=aid;
  PERFORM xs_validate_appointment(co,aid);
  UPDATE "CourseCreditReservation" SET "state"='reserved',"updatedAt"=now() WHERE "company"=co AND "appointmentId"=aid;
  IF NOT FOUND THEN PERFORM xs_insert('CourseCreditReservation',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'sourceKey','cloud:appointment-reservation:'||aid,'appointmentId',aid,'studentId',u."legacyUserId",'creditCosts',a."creditCostsSnapshot",'state','reserved'));END IF;
 ELSE
  UPDATE "CourseCreditReservation" SET "state"='released',"updatedAt"=now() WHERE "company"=co AND "appointmentId"=aid;
  UPDATE "CommonModel" SET "status"=-2,"updatedAt"=now() WHERE "objectId"=aid;
 END IF;
 PERFORM xs_insert('EnrollmentCommand',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'idempotencyKey',xs_object_id(),'request',jsonb_build_object('appointmentId',aid,'restore',restore,'actor',actor,'reason',reason),'result','{}'::jsonb));
 RETURN jsonb_build_object('targetId',aid,'restored',restore);
END $$;

CREATE OR REPLACE FUNCTION xs_start_appointment(co text,aid text) RETURNS void LANGUAGE plpgsql AS $$
DECLARE a "CourseAppointment";
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 PERFORM xs_validate_appointment(co,aid);
 SELECT a0.* INTO a FROM "CourseAppointment" a0 JOIN "CommonModel" c ON c."company"=a0."company" AND c."modelId"=54 AND c."itemId"=a0."id" WHERE c."company"=co AND c."objectId"=aid FOR UPDATE OF a0;
 IF a."dszt"::text NOT IN ('0','10') THEN RAISE EXCEPTION '预约状态已变更';END IF;
 IF COALESCE(NULLIF(a."pl"::text,''),'0')='0' THEN RAISE EXCEPTION '请先分配教师';END IF;
 UPDATE "CourseAppointment" SET "dszt"='10',"kssj"=COALESCE(NULLIF("kssj",''),now()::text),"updatedAt"=now() WHERE "objectId"=a."objectId";
END $$;
-- Reject stale profile writes that would silently overwrite balances changed by a transaction.
CREATE OR REPLACE FUNCTION xs_user_credit_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE account text;
BEGIN
 IF current_setting('xiaoshu.credit_write',true) IS DISTINCT FROM 'yes' THEN
  FOREACH account IN ARRAY ARRAY['Purse','SilverCoin','UserExp','UserPoint'] LOOP
   IF COALESCE((OLD."legacyUserData"->>account)::numeric,0)<>COALESCE((NEW."legacyUserData"->>account)::numeric,0) THEN RAISE EXCEPTION '课时账户已变更，请刷新；余额仅能通过课时业务单修改';END IF;
  END LOOP;
 END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS xs_user_credit_guard ON "_User";
CREATE TRIGGER xs_user_credit_guard BEFORE UPDATE OF "legacyUserData" ON "_User" FOR EACH ROW EXECUTE FUNCTION xs_user_credit_guard();

CREATE UNIQUE INDEX IF NOT EXISTS enrollment_reservation_identity ON "CourseCreditReservation" ("company","appointmentId") WHERE "state"='reserved';