-- 132_ird_overlays.sql
-- #1067 (Site Finder v2 / слой ИРД для analyze-таблицы, «GG-форсайт») — durable overlay store.
--
-- Единое долговременное хранилище ГЕОМЕТРИИ ограничений/обременений участка (ИРД), снятых из
-- НСПД WMS GetFeatureInfo по кварталам ЕКБ. Один движок harvest (#1067 B5) grid-walk'ит готовые
-- НСПД-слои → upsert сюда; analyze читает overlap участка через ST_Intersects.
--
-- Что НСПД реально отдаёт (разведка 2026-06-06, vault research/NSPD_IRD_Layers_Automation_Recon_Jun06):
--   ТОЛЬКО геометрия (Polygon/MultiPolygon/LineString, EPSG:3857) + reg_numb_border + category +
--   subcategory + geom_data_id. Читаемого типа/имени/параметров НЕТ — резолвятся ОТДЕЛЬНО
--   (ЕКБ-геопортал ИСОГД / ЕГРН) и дописываются в content-колонки (nullable, заполняются позже,
--   #1067 C8/D9). Поэтому content_* — NULLABLE: геометрия живёт сама по себе, содержание — опц.
--
-- Покрываемые ИРД-пункты (готовые НСПД-слои, см. LAYERS в nspd_client.py):
--   п.8 ООПТ/леса/вода — 875845/875866/37580/875832/875835
--   п.10 ОКН          — 37577 (ЗОУИТ ОКН) + 875840 (территории ОКН)
--   п.7 сервитут      — 37581 (иные ЗОУИТ; точный «вид зоны» дорезолвится из геопортала)
--   п.2 ПЗЗ-границы   — 875838 (геометрия зоны; ВРИ+предельные параметры — из геопортала, C8)
--   (+ прочие ЗОУИТ 37578/37579 как контекст.)
--
-- Зерно (grain): одна строка = одна НСПД-фича в одном слое. Дедуп-ключ — (source_layer_id,
--   geom_data_id): geom_data_id — стабильный внутренний id геометрии НСПД, присутствует во всех
--   ответах GetFeatureInfo; reg_numb_border (реестровый номер границы, «66:41-6.4125») — для
--   человекочитаемых lookup'ов, но НЕ ключ (теоретически может отсутствовать у части фич).
--
-- Почему TABLE: harvest периодически ПЕРЕсчитывает (Celery beat, #1067 B5) и UPSERT-ит фичи
--   по (source_layer_id, geom_data_id) — latest wins, история не нужна (геометрия зон стабильна;
--   обновление = свежий fetched_at). content_* заполняются отдельным проходом и переживают
--   re-harvest геометрии (ON CONFLICT обновляет геометрию/raw_props, content_* трогает только
--   content-резолвер — см. D9).
--
-- geom типизирован как geometry(Geometry, 4326) (не MultiPolygon): overlay смешивает площадные
--   зоны (ЗОУИТ/ПЗЗ/ОКН/ООПТ — Polygon/MultiPolygon) и ЛИНЕЙНЫЕ слои (875835 береговые линии).
--   Generic Geometry + GIST покрывает все; harvest хранит ST_Transform(geom_3857, 4326) как есть.
--
-- Deploy: auto-applied deploy.yml через _schema_migrations (ровно один раз по NN). Idempotent
--   (ре-apply дважды чисто): CREATE TABLE/INDEX IF NOT EXISTS + guarded DO-блок для UNIQUE
--   (constraint нельзя добавить дважды → guard по pg_constraint, прецедент м.40/110/125).
--   НИКАКИХ строк здесь не вставляется (данные приходят из harvest-воркера #1067 B5).

BEGIN;

-- ── 1. Таблица ird_overlays ────────────────────────────────────────────────────
-- PK + inline CHECK: CREATE TABLE IF NOT EXISTS на существующей таблице пропускает statement
-- целиком, поэтому inline-CHECK не передобавляется (re-apply safe). UNIQUE — в DO-блоке ниже.
CREATE TABLE IF NOT EXISTS ird_overlays (
    id                BIGSERIAL PRIMARY KEY,
    source_layer_id   INTEGER NOT NULL,          -- numeric НСПД layer id (37577, 875838, ...)
    layer_kind        TEXT NOT NULL,             -- наш читаемый бакет (ключ LAYERS): 'zouit_okn'|'territorial_zone'|'okn_territory'|'protected_area'|'forestry'|'coast_line'|...
    geom_data_id      BIGINT NOT NULL,           -- стабильный id геометрии НСПД (дедуп-ключ внутри слоя)
    reg_numb_border   TEXT,                      -- реестровый номер границы ('66:41-6.4125'); префикс -6.=ЗОУИТ/-7.=тер.зона/-8.=ОКН
    category          INTEGER,                   -- НСПД category code (36940 ЗОУИТ / 472819 тер.зона / 472820 ОКН)
    subcategory       INTEGER,                   -- НСПД subcategory code (числовой тип внутри слоя)
    geom              geometry(Geometry, 4326) NOT NULL,  -- ST_Transform(3857→4326); Polygon/MultiPolygon/LineString
    raw_props         JSONB,                     -- полный properties фичи (forward-compat / debug)
    -- ── content (резолвится ОТДЕЛЬНО из ЕКБ-геопортала/ЕГРН; NULLABLE до резолва, #1067 C8/D9) ──
    zone_index        TEXT,                      -- индекс зоны ПЗЗ ('Ц-1', 'Ж-2') — из геопортала
    zone_type_name    TEXT,                      -- читаемый тип/имя (вид зоны сервитута, имя памятника ОКН, ...)
    content           JSONB,                     -- структурное содержание (ВРИ-списки 540н, предельные параметры) для ПЗЗ
    content_source    TEXT,                      -- 'ekb_geoportal'|'egrn'|... (NULL пока content не заполнен)
    content_fetched_at timestamptz,              -- когда резолвили content (NULL = ещё не резолвили)
    fetched_at        timestamptz NOT NULL DEFAULT now(),  -- когда harvest снял геометрию (freshness)
    -- geom_data_id осмыслен только положительным; ловим мусор harvest'а громко на write.
    CONSTRAINT ird_overlays_geom_data_id_chk CHECK (geom_data_id > 0)
);

COMMENT ON TABLE ird_overlays IS
    'Durable overlay-store ИРД-ограничений участка (#1067, GG-форсайт). 1 строка = 1 НСПД-фича '
    'в слое. ТОЛЬКО геометрия+reg_numb_border из НСПД WMS; content_* (zone_index/тип/регламент) '
    'дорезолвятся из ЕКБ-геопортала ИСОГД / ЕГРН и заполняются отдельно (C8/D9). UPSERT '
    'harvest-воркером (#1067 B5) по uq_ird_overlays_feature (source_layer_id, geom_data_id). '
    'analyze читает overlap участка через ST_Intersects(geom). Пустая таблица = 0 overlap.';

COMMENT ON COLUMN ird_overlays.layer_kind IS 'Читаемый бакет-тип = ключ LAYERS (nspd_client.py); НСПД текстового типа не отдаёт, держим у себя per-layer';
COMMENT ON COLUMN ird_overlays.geom_data_id IS 'Стабильный внутренний id геометрии НСПД; дедуп-ключ внутри слоя (reg_numb_border не гарантирован)';
COMMENT ON COLUMN ird_overlays.reg_numb_border IS 'Реестровый номер границы ЕГРН; префикс после "-": 6=ЗОУИТ, 7=территориальная зона, 8=территория ОКН';
COMMENT ON COLUMN ird_overlays.content IS 'Структурное содержание (NULLABLE): для ПЗЗ — ВРИ-списки (коды 540н) + предельные параметры из ЕКБ-геопортала urbanCard (#1067 C8)';
COMMENT ON COLUMN ird_overlays.content_source IS 'Источник content (NULL пока не резолвлен): ekb_geoportal | egrn | ...';

-- ── 2. Дедуп/UPSERT-ключ (source_layer_id, geom_data_id) ───────────────────────
-- Цель ON CONFLICT для harvest-воркера (#1067 B5): re-harvest обновляет геометрию/raw_props/
-- fetched_at той же фичи, не плодя дубли. Guard по pg_constraint → idempotent re-apply
-- (constraint нельзя добавить дважды). Прецедент: м.125 (uq_supply_layers_logical), м.40.
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'uq_ird_overlays_feature'
    ) THEN
        ALTER TABLE ird_overlays
            ADD CONSTRAINT uq_ird_overlays_feature
            UNIQUE (source_layer_id, geom_data_id);
    END IF;
END
$$;

-- ── 3. Индексы ─────────────────────────────────────────────────────────────────
-- ГЛАВНЫЙ read-path: spatial overlap участка (analyze: ST_Intersects(parcel.geom, ird.geom)).
CREATE INDEX IF NOT EXISTS idx_ird_overlays_geom
    ON ird_overlays USING GIST (geom);
-- Фильтр по типу слоя (выборка «все ОКН» / «все ЗОУИТ-природные» и т.п.).
CREATE INDEX IF NOT EXISTS idx_ird_overlays_layer_kind
    ON ird_overlays (layer_kind);
-- Lookup по реестровому номеру (cross-ref с ЕГРН/документами при резолве content).
CREATE INDEX IF NOT EXISTS idx_ird_overlays_reg_numb
    ON ird_overlays (reg_numb_border) WHERE reg_numb_border IS NOT NULL;
-- Join к регламент-кэшу геопортала по индексу зоны (#1067 C8/D9); только заполненные.
CREATE INDEX IF NOT EXISTS idx_ird_overlays_zone_index
    ON ird_overlays (zone_index) WHERE zone_index IS NOT NULL;
-- Очередь резолва content: «фичи без content» (content_fetched_at IS NULL) — для воркера C8.
CREATE INDEX IF NOT EXISTS idx_ird_overlays_content_pending
    ON ird_overlays (source_layer_id) WHERE content_fetched_at IS NULL;

COMMIT;
