xiaoshu-app/scripts/sql/legacy-appointment-preparation.sql

-- Restore imported appointments only from an existing, unambiguous enrollment.
-- No balances, teaching status, lesson records or existing pay snapshots are changed.
CREATE OR REPLACE FUNCTION xs_prepare_legacy_appointment(co text,aid text,actor text,seed_rules jsonb) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE c "CommonModel";a "CourseAppointment";b "CourseBinding";u "_User";op "_User";
 category text;category_name text;course text;day date;minutes numeric;mode text;rule jsonb;costs jsonb;cost jsonb;
 matches integer;notes_legacy boolean=false;legacy_trial boolean=false;existing "CourseCreditReservation";before_data jsonb;changed boolean=false;
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('xs-credit:'||co));
 SELECT * INTO op FROM "_User" WHERE company=co AND "objectId"=actor;
 SELECT * INTO c FROM "CommonModel" WHERE company=co AND "modelId"=54 AND "objectId"=aid FOR UPDATE;
 SELECT * INTO a FROM "CourseAppointment" WHERE company=co AND id=c."itemId";
 IF op."objectId" IS NULL OR COALESCE(op."isDisabled",false) OR COALESCE(to_jsonb(op)->>'isDeleted','false')='true'
 OR NOT (COALESCE(op."legacyUserId"::text IN (a.pl::text,a.szyh::text),false) OR COALESCE(to_jsonb(op)->>'adminRoleKey','') IN ('super-admin','ops-manager')) THEN RAISE EXCEPTION '无权核对该预约';END IF;
 IF a."objectId" IS NULL OR c.status=-2 OR a.dszt IS DISTINCT FROM '0' THEN RAISE EXCEPTION '仅能补齐未开始且未取消的预约';END IF;
 IF COALESCE(c."sourceKey",'') !~ '^legacy[:\-]' OR COALESCE(a."sourceKey",'') !~ '^legacy[:\-]' OR COALESCE(a."teachingManaged",false) THEN RAISE EXCEPTION '仅能核对导入的旧预约';END IF;
 day=xs_appointment_day(a.bxrq,a.yysj);
 IF day IS NULL OR day<(now() AT TIME ZONE 'Asia/Shanghai')::date 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) OR COALESCE(to_jsonb(u)->>'isDeleted','false')='true' THEN RAISE EXCEPTION '学员不存在或已停用';END IF;
 SELECT * INTO a FROM "CourseAppointment" WHERE "objectId"=a."objectId" FOR UPDATE;
 before_data=to_jsonb(a);
 -- A conflicting legacy course pair can also reflect a newer manual edit.
 -- Do not overwrite either identifier while filling missing credentials.
 course=a.kcid::text;
 IF NULLIF(a.yykcid::text,'') IS NOT NULL AND a.yykcid::text<>course THEN RAISE EXCEPTION '预约课程编号与排课课程不一致，请管理员核实';END IF;
 category=NULLIF(a."courseCategoryKey",'');
 -- The legacy lesson type identifies a trial; the textbook profile describes its words, not its price.
 legacy_trial=a.dslx::text='3' AND (category IS NULL OR category='trial');
 IF legacy_trial THEN category='trial'; END IF;
 -- The legacy writing course has no vocabulary binding; only a per-appointment human confirmation is sufficient.
 notes_legacy=COALESCE(course='398' AND category IN ('primary_writing','middle_writing','high_writing') AND length(trim(COALESCE(to_jsonb(a)->>'categoryEvidence','')))>=2 AND NULLIF(a."bindingId",'') IS NULL,false);
 IF notes_legacy THEN
  category_name=COALESCE(NULLIF(a."courseCategoryName",''),CASE category WHEN 'primary_writing' THEN '小学语法写作课' WHEN 'middle_writing' THEN '中学语法写作课' WHEN 'high_writing' THEN '高中语法写作课' END);
 ELSE
 PERFORM xs_course_size(co,course);
 SELECT count(*) INTO matches FROM "CourseBinding" cb JOIN "CommonModel" bc ON bc.company=cb.company AND bc."modelId"=58 AND bc."itemId"=cb.id
 WHERE cb.company=co AND cb.yhid::text=a.szyh::text AND cb.kcid::text=course AND bc.status=99
 AND COALESCE(cb."enrollmentStatus",'pending') IN ('active','pending') AND (category IS NULL OR cb."categoryKey"=category OR cb."categoryKey" IS NULL OR (legacy_trial AND cb."categoryKey"='word' AND cb."enrollmentStatus"='active'))
 AND (NULLIF(a."bindingId",'') IS NULL OR cb."objectId"=a."bindingId");
 IF matches<>1 THEN RAISE EXCEPTION '预约缺少唯一开课记录，请由管理员确认分类';END IF;
 SELECT cb.* INTO b FROM "CourseBinding" cb JOIN "CommonModel" bc ON bc.company=cb.company AND bc."modelId"=58 AND bc."itemId"=cb.id
 WHERE cb.company=co AND cb.yhid::text=a.szyh::text AND cb.kcid::text=course AND bc.status=99
 AND COALESCE(cb."enrollmentStatus",'pending') IN ('active','pending') AND (category IS NULL OR cb."categoryKey"=category OR cb."categoryKey" IS NULL OR (legacy_trial AND cb."categoryKey"='word' AND cb."enrollmentStatus"='active'))
 AND (NULLIF(a."bindingId",'') IS NULL OR cb."objectId"=a."bindingId") FOR UPDATE OF cb;
 category=COALESCE(category,NULLIF(b."categoryKey",''));category_name=CASE WHEN legacy_trial THEN '体验课' ELSE b."categoryName" END;
 IF category IS NULL THEN
  SELECT min(p."categoryKey") INTO category FROM "CourseTeachingProfile" p WHERE p.company=co AND p."courseId"::text=course AND p.confirmed=true HAVING count(DISTINCT p."categoryKey")=1;
  IF category NOT IN ('word','trial','word_self_study') THEN RAISE EXCEPTION '语法写作开课需按学员确认';END IF;
 END IF;
 category_name=COALESCE(NULLIF(category_name,''),CASE category WHEN 'word' THEN '单词课' WHEN 'trial' THEN '体验课' WHEN 'word_self_study' THEN '单词自学课' WHEN 'primary_writing' THEN '小学语法写作课' WHEN 'middle_writing' THEN '中学语法写作课' WHEN 'high_writing' THEN '高中语法写作课' END);
 IF category IS NULL OR category NOT IN ('word','trial','word_self_study','primary_writing','middle_writing','high_writing') THEN RAISE EXCEPTION '开课分类尚未确认';END IF;
 IF COALESCE(b."enrollmentStatus",'pending')='pending' AND (COALESCE(b."sourceKey",'') !~ '^legacy[:\-]' OR NOT EXISTS(
 SELECT 1 FROM "CourseTeachingProfile" p WHERE p.company=co AND p."courseId"::text=course AND p.confirmed=true AND (p."categoryKey"=category OR (legacy_trial AND b."categoryKey" IS NULL AND p."categoryKey"='word')))
 OR EXISTS(SELECT 1 FROM "CourseTeachingProfile" p WHERE p.company=co AND p."courseId"::text=course AND p.confirmed=true AND p."categoryKey"<>category AND NOT (legacy_trial AND b."categoryKey" IS NULL AND p."categoryKey"='word'))) THEN RAISE EXCEPTION '历史开课分类需管理员核实';END IF;
 END IF;
 IF EXISTS(SELECT 1 FROM "TeachingCourseCategory" tc WHERE tc.company=co AND tc.key=category AND tc.active=false) THEN RAISE EXCEPTION '该课程分类已停用';END IF;
 minutes=COALESCE(a."durationMinutes",CASE a.dslx WHEN '1' THEN 30 ELSE 60 END);
 mode=CASE WHEN COALESCE(NULLIF(a."deliveryMode",''),a.jffs,'') ~ 'offline|线下|到店|面授' THEN 'offline' ELSE 'online' END;
 -- Stored versions suppress the corresponding defaults even when disabled.
 WITH candidates AS (
 SELECT to_jsonb(r)||jsonb_build_object('isDefault',false) AS r FROM "TeachingCompensationRule" r WHERE r.company=co
 UNION ALL SELECT s||jsonb_build_object('isDefault',true) FROM jsonb_array_elements(seed_rules) s WHERE NOT EXISTS(
 SELECT 1 FROM "TeachingCompensationRule" r WHERE r.company=co AND r."categoryKey"=s->>'categoryKey' AND r."durationMinutes"=(s->>'durationMinutes')::numeric AND r."deliveryMode"=s->>'deliveryMode' AND r."effectiveFrom"=s->>'effectiveFrom'))
 SELECT r INTO rule FROM candidates WHERE COALESCE((r->>'active')::boolean,true) AND r->>'categoryKey'=category AND (r->>'durationMinutes')::numeric=minutes AND r->>'deliveryMode'=mode AND r->>'effectiveFrom'<=day::text ORDER BY r->>'effectiveFrom' DESC,(r->>'isDefault')::boolean,(r->>'revision')::numeric DESC NULLS LAST LIMIT 1;
 costs=COALESCE(a."creditCostsSnapshot"::jsonb,'[]');
 IF jsonb_array_length(costs)=0 THEN costs=rule->'creditCosts';END IF;
 IF rule IS NULL OR costs IS NULL OR jsonb_array_length(costs)=0 THEN RAISE EXCEPTION '缺少适用的工资与课时规则';END IF;
 IF (SELECT count(*)<>count(DISTINCT value->>'account') FROM jsonb_array_elements(costs)) THEN RAISE EXCEPTION '课时凭据含重复账户，请管理员核实';END IF;
 IF NULLIF(a."creditHoldStatus",'') IS NOT NULL AND a."creditHoldStatus" NOT IN ('ready','insufficient') THEN RAISE EXCEPTION '历史预约已标记待处理：%',a."creditHoldStatus";END IF;
 FOR cost IN SELECT value FROM jsonb_array_elements(costs) LOOP
  IF COALESCE(cost->>'account','') NOT IN ('Purse','SilverCoin','UserExp','UserPoint') OR COALESCE((cost->>'amount')::numeric,0)<=0 OR (cost->>'amount')::numeric::text IN ('NaN','Infinity','-Infinity') THEN RAISE EXCEPTION '课时凭据无效';END IF;
  IF (u."legacyUserData"->>(cost->>'account'))::numeric::text IN ('NaN','Infinity','-Infinity') THEN RAISE EXCEPTION '课时余额无效，请管理员核实';END IF;
  IF COALESCE((u."legacyUserData"->>(cost->>'account'))::numeric,0)-xs_reserved(co,u."legacyUserId"::numeric,cost->>'account',aid)<(cost->>'amount')::numeric THEN RAISE EXCEPTION '可用课时不足：%',cost->>'account';END IF;
 END LOOP;
 SELECT * INTO existing FROM "CourseCreditReservation" WHERE company=co AND "appointmentId"=aid AND state IN ('reserved','consumed') FOR UPDATE;
 IF FOUND AND (existing.state IS DISTINCT FROM 'reserved' OR existing."studentId"::text IS DISTINCT FROM a.szyh::text OR existing."creditCosts"::jsonb IS DISTINCT FROM costs) THEN RAISE EXCEPTION '现有课时凭据冲突，请管理员核实';END IF;
 IF NOT notes_legacy AND (COALESCE(b."enrollmentStatus",'pending')='pending' OR b."categoryKey" IS NULL) THEN
  -- A legacy trial may use a shared word textbook; preserve its word classification for later ordinary lessons.
  UPDATE "CourseBinding" SET "enrollmentStatus"='active',
   "categoryKey"=CASE WHEN legacy_trial AND b."categoryKey" IS NULL AND EXISTS(SELECT 1 FROM "CourseTeachingProfile" p WHERE p.company=co AND p."courseId"::text=course AND p.confirmed=true AND p."categoryKey"='word') THEN 'word' ELSE category END,
   "categoryName"=CASE WHEN legacy_trial AND b."categoryKey" IS NULL AND EXISTS(SELECT 1 FROM "CourseTeachingProfile" p WHERE p.company=co AND p."courseId"::text=course AND p.confirmed=true AND p."categoryKey"='word') THEN '单词课' ELSE category_name END,
   "updatedAt"=now() WHERE "objectId"=b."objectId";changed=true;
 END IF;
 IF a."bindingId" IS DISTINCT FROM b."objectId" OR a.kcid::text IS DISTINCT FROM course OR a."courseCategoryKey" IS NULL OR a."teacherPaySnapshot" IS NULL OR COALESCE(jsonb_array_length(a."creditCostsSnapshot"::jsonb),0)=0 OR a."creditHoldStatus" IS DISTINCT FROM 'ready' THEN
  UPDATE "CourseAppointment" SET "bindingId"=b."objectId",kcid=course,"courseCategoryKey"=category,"courseCategoryName"=COALESCE(NULLIF("courseCategoryName",''),category_name),
   "durationMinutes"=minutes,"deliveryMode"=mode,"teachingRuleId"=COALESCE(NULLIF("teachingRuleId",''),rule->>'objectId'),"teacherPaySnapshot"=COALESCE("teacherPaySnapshot",(rule->>'teacherPay')::numeric),
   "creditCostsSnapshot"=costs,"creditHoldStatus"='ready',"creditHoldReason"='',"updatedAt"=now() WHERE "objectId"=a."objectId";changed=true;
 END IF;
 IF existing."objectId" IS NULL THEN
  PERFORM xs_insert('CourseCreditReservation',jsonb_build_object('objectId',xs_object_id(),'company',co,'createdAt',now(),'updatedAt',now(),'sourceKey','legacy-prepare:'||aid,'appointmentId',aid,'appointmentGeneralId',c."generalId",'studentId',u."legacyUserId",'ruleId',COALESCE(NULLIF(a."teachingRuleId",''),rule->>'objectId'),'creditCosts',costs,'state','reserved','reservedAt',now()));changed=true;
 END IF;
 IF changed THEN
  PERFORM xs_payroll_audit(co,actor,'prepare-legacy-appointment',aid,'核对旧预约的有效开课与课时凭据',jsonb_build_object('appointment',before_data,'binding',to_jsonb(b)),jsonb_build_object('bindingId',b."objectId",'creditCosts',costs,'courseId',course));
 END IF;
 RETURN jsonb_build_object('changed',changed,'bindingId',b."objectId",'creditHoldStatus','ready');
END $$;
REVOKE ALL ON FUNCTION xs_prepare_legacy_appointment(text,text,text,jsonb) FROM PUBLIC;