xiaoshu-app/scripts/sql/legacy-enrollment-reconciliation.sql

-- Historical authorizations already have an identity and learning progress.
-- Complete only an unambiguous, confirmed WORD classification; never recreate them.
CREATE OR REPLACE FUNCTION xs_reconcile_legacy_word_enrollments(co text,uid text DEFAULT NULL,course text DEFAULT NULL)
RETURNS integer LANGUAGE plpgsql AS $$
DECLARE candidate record; changed integer=0; affected integer;
BEGIN
 FOR candidate IN
  SELECT b."objectId" FROM "CourseBinding" b
  WHERE b.company=co AND (uid IS NULL OR b.yhid=uid) AND (course IS NULL OR b.kcid=course)
   AND COALESCE(b."enrollmentStatus",'pending')='pending'
   AND (b."categoryKey" IS NULL OR b."categoryKey"='word')
   AND (b."sourceKey" ~ '^legacy[:\-]' OR b."sourceKey" ~ '^\[\["(ID|GeneralID)",[0-9]+\]\]$')
  ORDER BY b."objectId"
 LOOP
  BEGIN
   UPDATE "CourseBinding" b SET "categoryKey"='word',"categoryName"='单词课',"enrollmentStatus"='active',"updatedAt"=now()
   WHERE b.company=co AND b."objectId"=candidate."objectId"
    AND COALESCE(b."enrollmentStatus",'pending')='pending'
    AND (b."categoryKey" IS NULL OR b."categoryKey"='word')
    AND (b."sourceKey" ~ '^legacy[:\-]' OR b."sourceKey" ~ '^\[\["(ID|GeneralID)",[0-9]+\]\]$')
    AND EXISTS(SELECT 1 FROM "CommonModel" c WHERE c.company=b.company AND c."modelId"=58 AND c."itemId"=b.id AND c.status=99
     AND (c."sourceKey" ~ '^legacy[:\-]' OR c."sourceKey" ~ '^\[\["(ID|GeneralID)",[0-9]+\]\]$'))
    AND EXISTS(SELECT 1 FROM "Node" n WHERE n.company=b.company AND n."nodeId"::text=b.kcid AND COALESCE(n.zstatus,99)=99)
    AND EXISTS(SELECT 1 FROM "_User" u WHERE u.company=b.company AND u."legacyUserId"::text=b.yhid
     AND u."identityType"='member' AND NOT COALESCE(u."isDisabled",false) AND NOT COALESCE(u."isDeleted",false))
    AND EXISTS(SELECT 1 FROM "CourseTeachingProfile" p WHERE p.company=b.company AND p."courseId"::text=b.kcid AND p.confirmed=true AND p."categoryKey"='word')
    AND NOT EXISTS(SELECT 1 FROM "CourseTeachingProfile" p WHERE p.company=b.company AND p."courseId"::text=b.kcid AND p.confirmed=true AND p."categoryKey" IS DISTINCT FROM 'word')
    AND NOT 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",'pending')<>'recycled'
     AND (other."categoryKey" IS NULL OR other."categoryKey"='word'));
   GET DIAGNOSTICS affected=ROW_COUNT;
   changed=changed+affected;
  EXCEPTION WHEN unique_violation THEN
   -- A concurrent explicit enrollment won the active-identity index. Keep its
   -- new-system authorization and this historical record/progress unchanged.
   NULL;
  END;
 END LOOP;
 RETURN changed;
END $$;

CREATE OR REPLACE FUNCTION xs_legacy_word_enrollment_changed() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE b record;
BEGIN
 IF TG_TABLE_NAME='CourseBinding' THEN
  IF COALESCE(NEW."enrollmentStatus",'pending')='pending' AND (NEW."categoryKey" IS NULL OR NEW."categoryKey"='word') THEN
   PERFORM xs_reconcile_legacy_word_enrollments(NEW.company,NEW.yhid,NEW.kcid);
  END IF;
 ELSIF TG_TABLE_NAME='CommonModel' THEN
  IF NEW."modelId"=58 AND NEW.status=99 THEN
   FOR b IN SELECT yhid,kcid FROM "CourseBinding" WHERE company=NEW.company AND id=NEW."itemId" LOOP
    PERFORM xs_reconcile_legacy_word_enrollments(NEW.company,b.yhid,b.kcid);
   END LOOP;
  END IF;
 ELSIF TG_TABLE_NAME='_User' THEN
  IF NEW."identityType"='member' AND NOT COALESCE(NEW."isDisabled",false) AND NOT COALESCE(NEW."isDeleted",false) THEN
   PERFORM xs_reconcile_legacy_word_enrollments(NEW.company,NEW."legacyUserId"::text,NULL);
  END IF;
 ELSIF TG_TABLE_NAME='CourseTeachingProfile' THEN
  IF NEW.confirmed=true AND NEW."categoryKey"='word' THEN
   PERFORM xs_reconcile_legacy_word_enrollments(NEW.company,NULL,NEW."courseId"::text);
  END IF;
 ELSIF TG_TABLE_NAME='Node' THEN
  IF COALESCE(NEW.zstatus,99)=99 THEN
   PERFORM xs_reconcile_legacy_word_enrollments(NEW.company,NULL,NEW."nodeId"::text);
  END IF;
 END IF;
 RETURN NEW;
END $$;

DROP TRIGGER IF EXISTS xs_legacy_word_binding_changed ON "CourseBinding";
CREATE TRIGGER xs_legacy_word_binding_changed AFTER INSERT OR UPDATE OF company,yhid,kcid,"categoryKey","enrollmentStatus","sourceKey" ON "CourseBinding"
 FOR EACH ROW EXECUTE FUNCTION xs_legacy_word_enrollment_changed();
DROP TRIGGER IF EXISTS xs_legacy_word_common_changed ON "CommonModel";
CREATE TRIGGER xs_legacy_word_common_changed AFTER INSERT OR UPDATE OF company,"modelId","itemId",status,"sourceKey" ON "CommonModel"
 FOR EACH ROW EXECUTE FUNCTION xs_legacy_word_enrollment_changed();
DROP TRIGGER IF EXISTS xs_legacy_word_member_changed ON "_User";
CREATE TRIGGER xs_legacy_word_member_changed AFTER INSERT OR UPDATE OF company,"legacyUserId","identityType","isDisabled","isDeleted" ON "_User"
 FOR EACH ROW EXECUTE FUNCTION xs_legacy_word_enrollment_changed();
DROP TRIGGER IF EXISTS xs_legacy_word_profile_changed ON "CourseTeachingProfile";
CREATE TRIGGER xs_legacy_word_profile_changed AFTER INSERT OR UPDATE OF company,"courseId","categoryKey",confirmed ON "CourseTeachingProfile"
 FOR EACH ROW EXECUTE FUNCTION xs_legacy_word_enrollment_changed();
DROP TRIGGER IF EXISTS xs_legacy_word_node_changed ON "Node";
CREATE TRIGGER xs_legacy_word_node_changed AFTER INSERT OR UPDATE OF company,"nodeId",zstatus ON "Node"
 FOR EACH ROW EXECUTE FUNCTION xs_legacy_word_enrollment_changed();

REVOKE ALL ON FUNCTION xs_reconcile_legacy_word_enrollments(text,text,text) FROM PUBLIC;
REVOKE ALL ON FUNCTION xs_legacy_word_enrollment_changed() FROM PUBLIC;

DO $$
DECLARE tenant record;
BEGIN
 FOR tenant IN SELECT DISTINCT company FROM "CourseBinding" LOOP
  PERFORM xs_reconcile_legacy_word_enrollments(tenant.company);
 END LOOP;
END $$;