gendesign/data/sql/140_domrf_documents_unique_nulls_not_distinct.sql

-- 140_domrf_documents_unique_nulls_not_distinct.sql
-- Issue #1206 (P2 data-integrity). Fix UNIQUE-key NULL-handling в domrf_kn_documents.
--
-- Bug:
--   М.112 объявила inline UNIQUE (obj_id, doc_type, doc_num, file_url) БЕЗ NULLS NOT
--   DISTINCT. doc_num — nullable, и scrapers/documents.py:135 реально передаёт
--   doc_num=None для документов без номера (DOM.РФ docNum=null — нередкий кейс).
--   В Postgres по умолчанию `NULL <> NULL` в уникальном индексе → ON CONFLICT
--   арбитр в scrapers/documents.py:59 НЕ матчит существующую строку с doc_num IS NULL
--   → каждый daily sweep (domrf_kn.py:1903) молча INSERT-ит дубль вместо UPDATE.
--   Таблица растёт ×N прогонов, дубли уходят в API analytics_queries.py:1068.
--
-- Fix (этот файл):
--   1. Дедуп существующих дублей с doc_num IS NULL — keep самый свежий по scraped_at
--      (с tiebreak ↑id, чтобы получить детерминированный winner).
--   2. DROP старого UNIQUE-constraint'а (autonamed Postgres'ом из inline UNIQUE
--      в м.112: `<table>_<columns>_key` → 55 chars, fits 63-char limit, без truncation).
--   3. ADD нового UNIQUE NULLS NOT DISTINCT с теми же столбцами — NULL==NULL
--      в матчинге, ON CONFLICT начинает попадать в существующую строку.
--
-- Why NULLS NOT DISTINCT, не doc_num NOT NULL DEFAULT '':
--   Issue #1206 предлагал две альтернативы. NULLS NOT DISTINCT — чище: ни строчки
--   data-coercion (пустая строка ≠ "не было номера" семантически), не трогаем
--   scrapers/documents.py (caller остаётся корректным как есть). Прецедент в репо:
--   м.110 (uq_infra_dedupe), м.125 (uq_supply_layers_logical) — те же грабли,
--   тот же fix.
--
-- Caller (НЕ меняется этим PR-ом):
--   backend/app/services/scrapers/documents.py:59 — ON CONFLICT (obj_id, doc_type,
--   doc_num, file_url) DO UPDATE … уже корректен по столбцам. После этой миграции
--   арбитр начнёт матчить строки с NULL doc_num — UPDATE вместо INSERT — дубли
--   перестают копиться. Никаких изменений в documents.py не требуется.
--
-- Dependencies:
--   • PostgreSQL 16 (postgis/postgis:16-3.4) — NULLS NOT DISTINCT доступен с PG15.
--   • Никакие views/MVs не ссылаются на domrf_kn_documents по имени constraint'а
--     (grep по data/sql/ — пусто). Caller ссылается ТОЛЬКО на column list в ON
--     CONFLICT, имя constraint'а не упомянуто нигде.
--
-- Deploy order:
--   1. Эта SQL-миграция применяется первой (auto-apply через deploy.yml +
--      _schema_migrations tracking по имени файла, ровно один раз).
--   2. Backend code НЕ меняется — re-deploy не обязателен под этот PR.
--
-- Idempotency:
--   • DELETE — конвергентный (после первого прогона дубли нет, повторный запуск
--     удаляет 0 строк).
--   • DROP CONSTRAINT IF EXISTS — re-apply safe.
--   • ADD CONSTRAINT в guarded DO-блок по pg_constraint.conname (constraint нельзя
--     добавить дважды; прецедент паттерна — м.125 uq_supply_layers_logical, м.40 FK).
--   • Re-apply дважды чисто, без ошибок.

BEGIN;

-- ── 1. Дедуп существующих строк с doc_num IS NULL ─────────────────────────────
-- Группируем по (obj_id, doc_type, doc_num, file_url). Оставляем самую СВЕЖУЮ
-- строку по scraped_at — это семантически правильный winner: scraped_at бампится
-- на каждом UPDATE в caller'е (documents.py:63 `scraped_at = NOW()`), значит
-- держим версию с самыми свежими метаданными (size_bytes, local_path,
-- downloaded_at). Tiebreak по id DESC — детерминированный winner при равных
-- scraped_at (массовый backfill в одной транзакции).
--
-- DELETE-by-id (не ctid) безопасен на партицируемых таблицах и при VACUUM FULL.
-- Только doc_num IS NULL — дубли с не-NULL doc_num невозможны (исходный UNIQUE
-- их корректно ловил, NULL != NULL — единственная дыра).
WITH ranked AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY obj_id, doc_type, doc_num, file_url
            ORDER BY scraped_at DESC, id DESC
        ) AS rn
    FROM domrf_kn_documents
    WHERE doc_num IS NULL
)
DELETE FROM domrf_kn_documents
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

-- ── 2. Сбросить старый UNIQUE (inline-объявление в м.112) ─────────────────────
-- Имя autonamed Postgres'ом по правилу `<table>_<col1>_<col2>_..._key`. Для
-- domrf_kn_documents(obj_id, doc_type, doc_num, file_url) это
-- `domrf_kn_documents_obj_id_doc_type_doc_num_file_url_key` — 55 chars, fits
-- 63-char identifier limit, без truncation. IF EXISTS — re-apply safe.
ALTER TABLE domrf_kn_documents
    DROP CONSTRAINT IF EXISTS domrf_kn_documents_obj_id_doc_type_doc_num_file_url_key;

-- ── 3. Поднять новый UNIQUE NULLS NOT DISTINCT ────────────────────────────────
-- Те же столбцы — column-list в caller'ах (documents.py:59 ON CONFLICT) НЕ
-- меняется. NULLS NOT DISTINCT (PG15+) — NULL'ы сравниваются как равные в
-- уникальном индексе, ON CONFLICT начинает матчить строки с doc_num IS NULL.
-- Constraint именованный (uq_domrf_kn_documents_logical) — стабильное имя для
-- diagnostics и идемпотентности (autoгенерированные имена ломаются при
-- перестановке столбцов). Guard по pg_constraint → re-apply не падает
-- "constraint already exists" (прецедент: м.125 uq_supply_layers_logical).
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'uq_domrf_kn_documents_logical'
    ) THEN
        ALTER TABLE domrf_kn_documents
            ADD CONSTRAINT uq_domrf_kn_documents_logical
            UNIQUE NULLS NOT DISTINCT (obj_id, doc_type, doc_num, file_url);
    END IF;
END
$$;

COMMENT ON CONSTRAINT uq_domrf_kn_documents_logical ON domrf_kn_documents IS
    'Логический dedup-ключ. NULLS NOT DISTINCT — NULL doc_num сравнивается как '
    'равное (PG15+). Цель ON CONFLICT для scrapers/documents.py:59. Issue #1206.';

COMMIT;

-- ── Smoke (псевдо-тест для будущего dev'а; НЕ выполняется автоматически) ──────
-- После применения этой миграции `\d+ domrf_kn_documents` в psql должно показать:
--   Indexes:
--     "domrf_kn_documents_pkey" PRIMARY KEY, btree (id)
--     "uq_domrf_kn_documents_logical" UNIQUE CONSTRAINT, btree (obj_id, doc_type,
--         doc_num, file_url) NULLS NOT DISTINCT
--     "domrf_kn_documents_obj_type_idx" btree (obj_id, doc_type)
--
-- Ручная sanity-проверка:
--   INSERT INTO domrf_kn_documents (obj_id, doc_type, doc_num, file_url)
--     VALUES (1, 'прочее', NULL, 'https://x.test/a.pdf');
--   INSERT INTO domrf_kn_documents (obj_id, doc_type, doc_num, file_url)
--     VALUES (1, 'прочее', NULL, 'https://x.test/a.pdf')
--     ON CONFLICT (obj_id, doc_type, doc_num, file_url) DO UPDATE
--       SET scraped_at = NOW();
--   -- → 1 строка в таблице (вместо 2 до фикса), scraped_at обновлён.