vignette/infra/db/init/22_case_profile_multi_case.sql

-- =============================================================================
-- Vignette · migration 22 — 동일 learner-persona의 새 사례 분리
-- =============================================================================
-- migration 21의 learner-persona 단일 활성 회기 제약은 그대로 유지한다.
-- 이 migration은 끝난 기존 사례를 보존한 채, 같은 내담자에 새 사례(case_profile)를
-- 만들 수 있도록 legacy pair unique만 제거한다. raw transcript·memory row는 수정하지 않는다.
--
-- CREATE INDEX CONCURRENTLY는 transaction block 안에서 실행할 수 없다.
-- release agent가 online migration으로 owner psql에 전달한다.

SET lock_timeout = '5s';
SET statement_timeout = '15min';

DO $$
DECLARE
  legacy_constraints text[];
  target_index oid := to_regclass('app.idx_case_profile_learner_persona_activity');
BEGIN
  SELECT array_agg(candidate.conname ORDER BY candidate.conname)
  INTO legacy_constraints
  FROM (
    SELECT constraint_meta.conname
    FROM pg_constraint AS constraint_meta
    WHERE constraint_meta.conrelid = 'app.case_profile'::regclass
      AND constraint_meta.contype = 'u'
      AND ARRAY(
        SELECT attribute_meta.attname::text
        FROM unnest(constraint_meta.conkey) WITH ORDINALITY AS key_column(attnum, ordinality)
        JOIN pg_attribute AS attribute_meta
          ON attribute_meta.attrelid = constraint_meta.conrelid
         AND attribute_meta.attnum = key_column.attnum
        ORDER BY key_column.ordinality
      ) = ARRAY['persona_id', 'learner_id']::text[]
  ) AS candidate;

  IF COALESCE(array_length(legacy_constraints, 1), 0) > 1 THEN
    RAISE EXCEPTION
      'migration 22 blocked: multiple legacy case_profile persona-learner unique constraints exist';
  END IF;

  IF target_index IS NOT NULL AND NOT EXISTS (
    SELECT 1
    FROM pg_index AS index_meta
    WHERE index_meta.indexrelid = target_index
      AND index_meta.indrelid = 'app.case_profile'::regclass
      AND index_meta.indisvalid
      AND index_meta.indisready
      AND NOT index_meta.indisunique
      AND index_meta.indnkeyatts = 4
      AND pg_get_indexdef(index_meta.indexrelid) LIKE
        '%(learner_id, persona_id, updated_at DESC, case_id)%'
  ) THEN
    RAISE EXCEPTION
      'migration 22 blocked: target index exists but is invalid or has a different definition';
  END IF;
END
$$;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_case_profile_learner_persona_activity
  ON app.case_profile (learner_id, persona_id, updated_at DESC, case_id);

DO $$
DECLARE
  legacy_constraints text[];
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_index AS index_meta
    WHERE index_meta.indexrelid = 'app.idx_case_profile_learner_persona_activity'::regclass
      AND index_meta.indrelid = 'app.case_profile'::regclass
      AND index_meta.indisvalid
      AND index_meta.indisready
      AND NOT index_meta.indisunique
      AND index_meta.indnkeyatts = 4
      AND pg_get_indexdef(index_meta.indexrelid) LIKE
        '%(learner_id, persona_id, updated_at DESC, case_id)%'
  ) THEN
    RAISE EXCEPTION
      'migration 22 failed: valid case activity index was not created';
  END IF;

  SELECT array_agg(candidate.conname ORDER BY candidate.conname)
  INTO legacy_constraints
  FROM (
    SELECT constraint_meta.conname
    FROM pg_constraint AS constraint_meta
    WHERE constraint_meta.conrelid = 'app.case_profile'::regclass
      AND constraint_meta.contype = 'u'
      AND ARRAY(
        SELECT attribute_meta.attname::text
        FROM unnest(constraint_meta.conkey) WITH ORDINALITY AS key_column(attnum, ordinality)
        JOIN pg_attribute AS attribute_meta
          ON attribute_meta.attrelid = constraint_meta.conrelid
         AND attribute_meta.attnum = key_column.attnum
        ORDER BY key_column.ordinality
      ) = ARRAY['persona_id', 'learner_id']::text[]
  ) AS candidate;

  IF COALESCE(array_length(legacy_constraints, 1), 0) > 1 THEN
    RAISE EXCEPTION
      'migration 22 blocked: multiple legacy case_profile persona-learner unique constraints exist';
  END IF;

  IF COALESCE(array_length(legacy_constraints, 1), 0) = 1 THEN
    EXECUTE format(
      'ALTER TABLE app.case_profile DROP CONSTRAINT %I',
      legacy_constraints[1]
    );
  END IF;

  IF EXISTS (
    SELECT 1
    FROM pg_constraint AS constraint_meta
    WHERE constraint_meta.conrelid = 'app.case_profile'::regclass
      AND constraint_meta.contype = 'u'
      AND ARRAY(
        SELECT attribute_meta.attname::text
        FROM unnest(constraint_meta.conkey) WITH ORDINALITY AS key_column(attnum, ordinality)
        JOIN pg_attribute AS attribute_meta
          ON attribute_meta.attrelid = constraint_meta.conrelid
         AND attribute_meta.attnum = key_column.attnum
        ORDER BY key_column.ordinality
      ) = ARRAY['persona_id', 'learner_id']::text[]
  ) THEN
    RAISE EXCEPTION
      'migration 22 failed: legacy case_profile persona-learner unique constraint remains';
  END IF;
END
$$;