xiaoshu-app/scripts/sql/writing-without-enrollment.sql

-- Selected textbooks are linked atomically with trial/self-study scheduling.
CREATE OR REPLACE FUNCTION xs_schedule_textbook(co text,actor text,uid numeric,course text,category text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE bid text; ident bigint; content_count numeric; course_name text; category_name text;
BEGIN
 IF category NOT IN ('trial','word_self_study') THEN RAISE EXCEPTION '该课型需要提前开课';END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 content_count=xs_course_size(co,course);
 SELECT b."objectId" INTO bid FROM "CourseBinding" b JOIN "CommonModel" c ON c.company=b.company AND c."modelId"=58 AND c."itemId"=b.id AND c.status=99 WHERE b.company=co AND b.yhid::text=uid::text AND b.kcid::text=course AND b."categoryKey"=category AND b."enrollmentStatus"='active' ORDER BY b."createdAt",b."objectId" LIMIT 1;
 IF bid IS NOT NULL THEN RETURN bid;END IF;
 SELECT "nodeName" INTO course_name FROM "Node" WHERE company=co AND "nodeId"::text=course;
 category_name=CASE category WHEN 'trial' THEN '体验课' ELSE '单词自学课' END;
 bid=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','schedule-textbook:'||bid,'yhid',uid,'kcid',course::numeric,'yxx',0,'cksl',content_count,'syjd','','categoryKey',category,'categoryName',category_name,'enrollmentStatus','active'));
 PERFORM xs_insert('CommonModel',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'createTime',now(),'sourceKey','schedule-textbook:'||bid,'generalId',nextval('xiaoshu_business_id'),'itemId',ident,'modelId',58,'nodeId',28,'tableName','ZL_C_kcbd','status',99,'title',course_name,'inputer',actor));
 RETURN bid;
END $$;

DO $patch$
DECLARE definition text; original text;
BEGIN
 SELECT pg_get_functiondef(p.oid) INTO definition FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace WHERE n.nspname='public' AND p.proname='xs_validate_appointment';
 IF definition IS NULL THEN RAISE EXCEPTION 'xs_validate_appointment missing';END IF;
 IF position('Writing lessons use a category and credit rule' in definition)=0 THEN
  original=definition;
  definition=replace(definition,$old$ 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;
$old$,$new$ -- Writing lessons use a category and credit rule, not a vocabulary binding.
 IF NOT (COALESCE(a."courseCategoryKey",'') IN ('primary_writing','middle_writing','high_writing') AND COALESCE(a."kcid"::text,'')='0' AND COALESCE(a."bindingId",'')='') THEN
 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;
 END IF;
$new$);
  IF definition=original THEN RAISE EXCEPTION 'xs_validate_appointment baseline changed; review required';END IF;
  EXECUTE definition;
 END IF;
 SELECT pg_get_functiondef(p.oid) INTO definition FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace WHERE n.nspname='public' AND p.proname='xs_schedule_save';
 IF definition IS NULL THEN RAISE EXCEPTION 'xs_schedule_save missing';END IF;
 IF position('xs_schedule_textbook(co,actor,uid' in definition)=0 THEN
  original=definition;
  definition=replace(definition,$old$  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);
$old$,$new$  IF student."objectId" IS NULL OR COALESCE(student."isDisabled",false) OR COALESCE((to_jsonb(student)->>'isDeleted')::boolean,false) THEN RAISE EXCEPTION '学员不存在或已停用';END IF;
  IF addon_data->>'courseCategoryKey' IN ('trial','word_self_study') THEN
   IF (addon_data->>'durationMinutes')::numeric IS DISTINCT FROM 60 THEN RAISE EXCEPTION '体验课和单词自学课仅支持60分钟';END IF;
   addon_data=jsonb_set(addon_data,'{bindingId}',to_jsonb(xs_schedule_textbook(co,actor,uid,addon_data->>'kcid',addon_data->>'courseCategoryKey')));
  END IF;
  -- Writing lessons use a category and credit rule, not a vocabulary binding.
  IF NOT (COALESCE(addon_data->>'courseCategoryKey','') IN ('primary_writing','middle_writing','high_writing') AND COALESCE(addon_data->>'kcid','')='0' AND COALESCE(addon_data->>'bindingId','')='') THEN
  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);
  END IF;
$new$);
  IF definition=original THEN RAISE EXCEPTION 'xs_schedule_save baseline changed; review required';END IF;
  EXECUTE definition;
 END IF;
END $patch$;