-- 0004 · 공용 콘텐츠를 한 테이블로 (0002·0003 정정)
--
-- 0002·0003 에서 spots·region_stories 를 새로 팠다. 풀 문제는 "place_contents 가 place_id
-- 키라 복제된다" 하나였고 실체/관계만 가르면 됐는데, local_contents 에 이미 축제 60·
-- 관광지 12·맛집 12건이 있는 걸 못 보고 옆에 하나 더 만들었다.
-- 공용 콘텐츠는 local_contents 한 벌, 관계만 place_contents 에 남긴다.

-- 좌표: 거리 계산이 읽는다. body 안에 두면 행마다 JSON 을 편다.
ALTER TABLE local.local_contents ADD COLUMN IF NOT EXISTS latitude  NUMERIC(10,7) NULL;
ALTER TABLE local.local_contents ADD COLUMN IF NOT EXISTS longitude NUMERIC(10,7) NULL;
-- kind: 지역 이야기(songs·people·…). 장소류는 NULL.
ALTER TABLE local.local_contents ADD COLUMN IF NOT EXISTS kind VARCHAR(50) NULL;

-- 축제는 전국 공용이다 — 지역이 유일성의 근거가 되면 한 벌이 아니다.
ALTER TABLE local.local_contents ALTER COLUMN region_code DROP NOT NULL;

-- 옛 유일성: (region_code, content_type, external_id) — 같은 축제를 지역 수만큼 허용한다.
DROP INDEX IF EXISTS local.uq_local_contents_keyed;

-- 옛 인덱스가 허용해 온 중복을 먼저 접는다 — 축제 60행이 지역 2곳에 걸쳐 있었다.
UPDATE local.local_contents lc
   SET deleted = TRUE, updated_at = now()
 WHERE lc.deleted = FALSE
   AND lc.external_id IS NOT NULL
   AND lc.local_content_id NOT IN (
       SELECT DISTINCT ON (source, external_id) local_content_id
       FROM local.local_contents
       WHERE deleted = FALSE AND external_id IS NOT NULL
       ORDER BY source, external_id, collected_at DESC, created_at DESC
   );
-- 새 유일성: 출처가 준 id 하나면 한 행. 지역과 무관하다.
CREATE UNIQUE INDEX IF NOT EXISTS uq_local_contents_external
    ON local.local_contents (source, external_id)
    WHERE deleted = FALSE AND external_id IS NOT NULL;
-- 지역 이야기: 한 지역에 종류당 한 벌.
CREATE UNIQUE INDEX IF NOT EXISTS uq_local_contents_kind
    ON local.local_contents (region_code, kind)
    WHERE deleted = FALSE AND kind IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_local_contents_type_region
    ON local.local_contents (content_type, region_code) WHERE deleted = FALSE;

INSERT INTO local.local_contents (region_code, content_type, source, external_id, title, body,
                                  latitude, longitude, display_end_at, collected_at, status)
SELECT s.region_code, s.content_type, s.source, s.external_id, s.title, s.body,
       s.latitude, s.longitude, s.display_end_at, s.collected_at,
       2                                    -- 공공데이터는 검수 없이 그대로 싣는다(2026-09-03 결정)
FROM local.spots s
WHERE s.deleted = FALSE
  AND NOT EXISTS (
      SELECT 1 FROM local.local_contents lc
      WHERE lc.deleted = FALSE AND lc.external_id = s.external_id AND lc.source = s.source
  );

-- 옛 구조는 이름을 비켜 둔다.
ALTER TABLE IF EXISTS local.place_contents RENAME TO place_contents_legacy;
ALTER INDEX IF EXISTS local.uq_place_contents_keyed RENAME TO uq_place_contents_legacy_keyed;
ALTER INDEX IF EXISTS local.idx_place_contents_place RENAME TO idx_place_contents_legacy_place;

CREATE TABLE IF NOT EXISTS local.place_contents (
    place_id         uuid        NOT NULL,                    -- 사업장(place.places.place_id)
    local_content_id uuid        NOT NULL,                    -- 공용 콘텐츠(local.local_contents)
    distance_m       INTEGER     NULL,                        -- 업장 좌표 기준 거리. 정렬·도보 시간의 원값
    hidden           BOOLEAN     NOT NULL DEFAULT FALSE,      -- 운영자 숨김(재수집이 덮어쓰지 않음)
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted          BOOLEAN     NOT NULL DEFAULT FALSE,
    PRIMARY KEY (place_id, local_content_id)
);
CREATE INDEX IF NOT EXISTS idx_place_contents_place
    ON local.place_contents (place_id) WHERE deleted = FALSE;

-- place_spots(0002) 의 관계를 옮긴다. spot → 같은 external_id 의 공용 콘텐츠로 잇는다.
INSERT INTO local.place_contents (place_id, local_content_id, distance_m, hidden)
SELECT ps.place_id, lc.local_content_id, ps.distance_m, ps.hidden
FROM local.place_spots ps
JOIN local.spots s  ON s.spot_id = ps.spot_id AND s.deleted = FALSE
JOIN local.local_contents lc
     ON lc.source = s.source AND lc.external_id = s.external_id AND lc.deleted = FALSE
WHERE ps.deleted = FALSE
ON CONFLICT (place_id, local_content_id) DO NOTHING;

DROP TABLE IF EXISTS local.place_spots;
DROP TABLE IF EXISTS local.spots;
DROP TABLE IF EXISTS local.region_stories;

-- legacy 는 읽는 코드가 옮겨간 뒤 별도 번호로 뗀다.