-- 100_22j_dedup_infrastructure.sql
-- Context: domrf_kn_infrastructure has ~496k rows but only ~229k unique by
--          (obj_id, poi_category, poi_name, poi_address) — ~267k duplicates.
--          Root cause: old scraper re-inserts same POIs on every run with new
--          snapshot_date, and the old UNIQUE key includes snapshot_date so
--          dedup was per-day only.
-- Fix:
--   1. One-time cleanup: keep lowest ctid per dedup key, delete the rest.
--   2. Drop old UNIQUE constraint (includes snapshot_date — wrong granularity).
--   3. Add new UNIQUE (obj_id, poi_category, poi_name, poi_address)
--      with NULLS NOT DISTINCT (PG16) so NULL values are treated as equal.
-- Dependencies: none (no views reference this table by constraint name)
-- Deploy order: apply this SQL first, then deploy backend with ON CONFLICT fix.
-- Idempotent: yes — constraint uses IF NOT EXISTS equivalent via DO $$ block;
--             DELETE is safe to re-run (no rows left to delete after first run).
-- Issue: #297 / task 22j
-- Applied: 2026-05-17

BEGIN;

-- Step 1: one-time dedup — keep min(ctid) per logical key
DELETE FROM domrf_kn_infrastructure a
USING domrf_kn_infrastructure b
WHERE a.ctid > b.ctid
  AND a.obj_id = b.obj_id
  AND COALESCE(a.poi_category, '') = COALESCE(b.poi_category, '')
  AND COALESCE(a.poi_name, '') = COALESCE(b.poi_name, '')
  AND COALESCE(a.poi_address, '') = COALESCE(b.poi_address, '');

-- Step 2: drop old unique constraint (wrong key — includes snapshot_date)
ALTER TABLE domrf_kn_infrastructure
  DROP CONSTRAINT IF EXISTS domrf_kn_infrastructure_obj_id_poi_name_poi_lat_poi_lon_sna_key;

-- Step 3: add new unique constraint with NULLS NOT DISTINCT (PG15+)
--         so NULL poi_category / poi_address are treated as equal (not distinct)
-- PostgreSQL has no ADD CONSTRAINT ... IF NOT EXISTS form, so guard via DO $$
-- block (idempotency for DR-restore / re-apply under ON_ERROR_STOP).
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'uq_infra_dedupe'
      AND conrelid = 'domrf_kn_infrastructure'::regclass
  ) THEN
    ALTER TABLE domrf_kn_infrastructure
      ADD CONSTRAINT uq_infra_dedupe
      UNIQUE NULLS NOT DISTINCT (obj_id, poi_category, poi_name, poi_address);
  END IF;
END $$;

COMMIT;
