-- ============================================================================
-- task-2967-rollback.sql
-- ⚠️⚠️⚠️  경고: 이 스크립트는 데이터를 영구 삭제합니다(DROP TABLE / DROP COLUMN) ⚠️⚠️⚠️
-- ============================================================================
--
-- 대상 마이그레이션(둘 다 down 스크립트 없음 — 이 파일이 그 대체):
--   1) 20260812130000_coverage_grouping_knowledge.sql
--      → coverage_product / coverage_clause / coverage_group_map / grouping_rule
--        4테이블 + grouping_rule_set_updated_at() 함수 + 트리거 신규 생성
--   2) 20260816120000_grouping_phase2_states_and_hitl.sql
--      → coverage_group_map.map_state 컬럼/제약/인덱스 추가
--      → grouping_hitl_question 테이블 신규 생성
--
-- ★★★ 실행 전 필수 확인 사항 ★★★
--   1. 대상 Supabase 프로젝트가 정확히 zayhfjuwviporbzokudr (InsuRo 프로덕션)인지
--      재확인할 것. 다른 프로젝트(스테이징/InsuWiki 등)에 잘못 실행하면 안 됨.
--   2. 실행 전 반드시 백업(pg_dump 또는 Supabase 대시보드 스냅샷)을 확보할 것.
--      이 스크립트가 지우는 데이터는 백업 없이는 복구 불가능하다.
--   3. 이 스크립트는 "배포 실패 시 즉시 롤백"용 사전 준비물이다 — 평시에는
--      실행하지 않는다. 실행 여부는 반드시 사람(회장/팀장)의 명시적 판단으로 결정.
--   4. 아래 COMMIT 은 의도적으로 주석 처리되어 있다. 각 PART 의 결과(특히
--      A-0/B-0 의 행 수 NOTICE, 그리고 스크립트 종료 후 SELECT 로 실제 스키마
--      상태)를 확인한 뒤, 의도한 대로면 마지막 줄의 주석을 풀고 COMMIT;을
--      수동으로 실행한다. 문제가 있으면 ROLLBACK; 을 실행해 아무 것도 반영되지
--      않게 한다.
--   5. 프로덕션 DB 접속/실행은 이 스크립트를 준비한 사람(헤임달/QA)이 아니라
--      명시적 승인을 받은 담당자가 별도로 수행한다.
--
-- 구성:
--   PART A — Phase 2(20260816120000)만 롤백. 4개 기반 테이블은 유지.
--            (map_state 컬럼/제약/인덱스 제거 + grouping_hitl_question 드롭)
--   PART B — 전체 롤백. PART A 다음에 실행되며, grouping_rule → coverage_group_map
--            → coverage_clause → coverage_product 역순으로 드롭한다.
--            (PART B는 PART A 가 이미 실행됐는지 여부와 무관하게 안전하게
--             동작하도록 B-1 에서 Phase2/HITL 객체를 다시 한번 멱등하게 정리한다.)
--
-- 멱등성: 모든 DROP/ALTER 구문에 IF EXISTS 를 사용했다. 이 파일 전체를 두 번
-- 연속 실행해도 에러 없이 통과한다(행 수 확인 NOTICE 는 DO 블록 안에서
-- to_regclass/information_schema 존재 체크 후 실행하므로 대상이 이미 없어도
-- 에러 대신 "이미 롤백됨" 메시지만 남긴다).
--
-- ============================================================================

BEGIN;

-- ============================================================================
-- PART A — Phase 2(20260816120000)만 롤백 (4개 기반 테이블은 유지)
-- ============================================================================

-- [A-0] 롤백 전 데이터량 확인 (삭제될 행 수를 미리 눈으로 확인하기 위함).
-- 일반 SELECT 대신 DO 블록 + 존재 체크를 쓰는 이유: 재실행(2회차) 시점에는
-- map_state 컬럼/grouping_hitl_question 테이블이 이미 없을 수 있어, 무조건
-- SELECT 하면 "column/relation does not exist" 에러로 멱등성이 깨진다.
DO $$
DECLARE
  v_count bigint;
BEGIN
  IF to_regclass('public.grouping_hitl_question') IS NOT NULL THEN
    EXECUTE 'SELECT count(*) FROM public.grouping_hitl_question' INTO v_count;
    RAISE NOTICE '[A-0] grouping_hitl_question 삭제 예정 행 수: %', v_count;
  ELSE
    RAISE NOTICE '[A-0] grouping_hitl_question 테이블 없음(이미 롤백되었거나 최초부터 미생성)';
  END IF;

  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = 'coverage_group_map'
      AND column_name = 'map_state'
  ) THEN
    EXECUTE 'SELECT count(*) FROM public.coverage_group_map WHERE map_state IS NOT NULL' INTO v_count;
    RAISE NOTICE '[A-0] coverage_group_map.map_state 값이 채워진 행 수(컬럼 삭제 예정): %', v_count;
  ELSE
    RAISE NOTICE '[A-0] coverage_group_map.map_state 컬럼 없음(이미 롤백됨)';
  END IF;
END $$;

-- [A-1] grouping_hitl_question 전체 드롭 (20260816120000 신규 테이블).
-- 인덱스를 먼저 명시적으로 드롭하는 이유: 감사(audit) 목적상 어떤 인덱스가
-- 제거되는지 명시하기 위함. 실제로는 DROP TABLE ... CASCADE 만으로도 인덱스/
-- CHECK 제약/PK/UNIQUE 제약/RLS 정책이 모두 함께 제거된다.
DROP INDEX IF EXISTS public.idx_grouping_hitl_question_state_pending; -- PENDING 부분 인덱스
DROP INDEX IF EXISTS public.idx_grouping_hitl_question_clause_id;     -- clause_id 조회 인덱스
-- ↑ CASCADE 로 함께 제거되는 것: PK(id), UNIQUE(question_id), CHECK(state),
--   위 2개 인덱스(테이블과 함께 자동 제거되므로 위 명시적 DROP INDEX는 사실
--   선행 제거이며 중복이어도 안전함), RLS 정책
--   "Authenticated can read grouping_hitl_question", 테이블/컬럼 COMMENT 전체.

-- [A-2] coverage_group_map.map_state 컬럼/제약/인덱스만 제거 (테이블 자체는 유지).
-- 순서: 인덱스 → 제약(2개, 뒤에 추가된 것부터) → 컬럼. 컬럼을 먼저 지우면
-- 제약/인덱스는 자동으로 같이 사라지지만, 감사 추적을 위해 개별 문으로 명시한다.
DROP INDEX IF EXISTS public.idx_coverage_group_map_map_state; -- map_state 조회 인덱스

ALTER TABLE IF EXISTS public.coverage_group_map
  DROP CONSTRAINT IF EXISTS coverage_group_map_state_status_consistency_check;
  -- map_state='CONFIRMED' ⟹ status='confirmed' 단방향 일관성 제약 (Phase2 Round2 추가분)

ALTER TABLE IF EXISTS public.coverage_group_map
  DROP CONSTRAINT IF EXISTS coverage_group_map_map_state_check;
  -- map_state 5상태(CONFIRMED/LEARNED_RULE/AUTO_MAP/UNKNOWN/NOT_COMPARABLE) CHECK

ALTER TABLE IF EXISTS public.coverage_group_map
  DROP COLUMN IF EXISTS map_state;
  -- 컬럼 자체 제거. COMMENT ON COLUMN ... map_state 도 컬럼과 함께 자동 제거됨.

-- ★ 부분 롤백(=Phase 2 사고, 4테이블은 정상)만 원한다면 여기서 실행을 멈추고
-- 아래 "COMMIT;" 주석을 풀어 커밋하면 된다. PART B 는 실행하지 않는다.
-- (파일을 끝까지 실행하면 PART B 까지 이어져 4테이블 전체가 삭제되므로 주의.)

-- ============================================================================
-- PART B — 전체 롤백 (PART A 다음 실행 — 4개 기반 테이블까지 완전 제거)
-- 역순 드롭: grouping_rule → coverage_group_map → coverage_clause → coverage_product
-- (FK 의존성: coverage_clause→coverage_product, coverage_group_map→coverage_clause,
--  grouping_rule→coverage_clause 이므로, 참조하는 테이블을 먼저 지워야 참조당하는
--  테이블을 지울 때 FK 충돌이 없다.)
-- ============================================================================

-- [B-0] 롤백 전 데이터량 확인 (4개 기반 테이블).
DO $$
DECLARE
  v_count bigint;
  v_tbl text;
BEGIN
  FOREACH v_tbl IN ARRAY ARRAY['grouping_rule', 'coverage_group_map', 'coverage_clause', 'coverage_product']
  LOOP
    IF to_regclass('public.' || v_tbl) IS NOT NULL THEN
      EXECUTE format('SELECT count(*) FROM public.%I', v_tbl) INTO v_count;
      RAISE NOTICE '[B-0] % 삭제 예정 행 수: %', v_tbl, v_count;
    ELSE
      RAISE NOTICE '[B-0] % 테이블 없음(이미 롤백됨)', v_tbl;
    END IF;
  END LOOP;
END $$;

-- [B-1] 안전망: PART A 를 거치지 않고 PART B 만 단독 실행되는 경우를 대비해
-- Phase2/HITL 객체를 한 번 더 멱등하게 정리한다(A-1/A-2와 동일 문장 — 이미
-- PART A 가 실행되었다면 전부 IF EXISTS 로 조용히 스킵된다).
DROP INDEX IF EXISTS public.idx_grouping_hitl_question_state_pending;
DROP INDEX IF EXISTS public.idx_grouping_hitl_question_clause_id;

DROP INDEX IF EXISTS public.idx_coverage_group_map_map_state;
ALTER TABLE IF EXISTS public.coverage_group_map
  DROP CONSTRAINT IF EXISTS coverage_group_map_state_status_consistency_check;
ALTER TABLE IF EXISTS public.coverage_group_map
  DROP CONSTRAINT IF EXISTS coverage_group_map_map_state_check;
ALTER TABLE IF EXISTS public.coverage_group_map
  DROP COLUMN IF EXISTS map_state;

-- [B-2] grouping_rule — 트리거는 CASCADE로 함께 제거되지만 함수는 남으므로
-- 별도 DROP FUNCTION 이 반드시 필요하다(요구사항 명시 사항).
-- ↑ CASCADE 로 함께 제거: PK(id), UNIQUE(axis_signature)(=자동 유니크 인덱스),
--   FK(source_clause_id→coverage_clause), 트리거 trg_grouping_rule_updated_at,
--   RLS 정책 "Authenticated can read grouping_rule", 테이블/컬럼 COMMENT.
DROP FUNCTION IF EXISTS public.grouping_rule_set_updated_at();
-- ↑ 함수는 테이블 소유 객체가 아니라 스키마 소유 객체이므로 테이블 DROP에
--   딸려오지 않는다 — 반드시 별도로 제거해야 잔존 오브젝트가 남지 않는다.

-- [B-3] coverage_group_map — grouping_rule 이 먼저 지워졌으므로 이제 FK 충돌 없음.
-- ↑ CASCADE 로 함께 제거: PK(id), FK(clause_id→coverage_clause),
--   CHECK(confidence 0~1), CHECK(status IN proposed/confirmed),
--   인덱스 idx_coverage_group_map_clause_id / _status / _clause_confirmed,
--   RLS 정책 "Authenticated can read coverage_group_map", 테이블/컬럼 COMMENT.
--   (map_state 관련 잔여물이 있었다면 B-1에서 이미 제거됨 — 여기서도 중복 안전.)

-- [B-4] coverage_clause — coverage_group_map 이 먼저 지워졌으므로 FK 충돌 없음.
-- ↑ CASCADE 로 함께 제거: PK(id), FK(product_id→coverage_product),
--   UNIQUE(product_id, code), 인덱스 idx_coverage_clause_product_id,
--   RLS 정책 "Authenticated can read coverage_clause", 컬럼 COMMENT(name/excerpt).

-- [B-5] coverage_product — 더 이상 아무도 참조하지 않으므로 마지막에 제거.
-- ↑ CASCADE 로 함께 제거: PK(id), UNIQUE(terms_sha256),
--   CHECK(char_length(terms_sha256)=64), RLS 정책
--   "Authenticated can read coverage_product", 테이블/컬럼 COMMENT.

-- [B-6] 최종 확인 — 관련 테이블/함수가 모두 사라졌는지 조회.
-- (아래 SELECT 는 존재하지 않는 릴레이션을 직접 조회하지 않으므로 재실행해도 안전.)
SELECT
  to_regclass('public.coverage_product')      AS coverage_product,
  to_regclass('public.coverage_clause')       AS coverage_clause,
  to_regclass('public.coverage_group_map')    AS coverage_group_map,
  to_regclass('public.grouping_rule')         AS grouping_rule,
  to_regclass('public.grouping_hitl_question') AS grouping_hitl_question,
  to_regproc('public.grouping_rule_set_updated_at') AS grouping_rule_fn;
-- 위 6개 컬럼이 전부 NULL 이면 전체 롤백(PART A + PART B) 완료.

-- ============================================================================
-- ★★★ 아래 COMMIT 은 의도적으로 주석 처리되어 있습니다. ★★★
-- 위 NOTICE/SELECT 결과를 확인한 뒤, 의도한 롤백 범위(PART A만 / PART A+B)가
-- 맞다면 아래 줄의 주석을 풀어 COMMIT; 을 수동 실행하세요.
-- 문제가 있거나 실수로 실행한 경우 COMMIT 대신 ROLLBACK; 을 실행하세요.
-- ============================================================================
-- COMMIT;
