gendesign/data/sql/76_complex_connectivity.sql

-- Соединение всех источников с canonical complexes (Phase A.2 продолжение).
-- Применено в проде через mcp postgres 2026-05-11.
-- Идемпотентно через ADD COLUMN IF NOT EXISTS / FK с REFERENCES.

-- ── 1. complexes.cad_quarter / cad_building_num: связь с rosreestr/NSPD ─────
ALTER TABLE complexes
  ADD COLUMN IF NOT EXISTS cad_quarter TEXT,
  ADD COLUMN IF NOT EXISTS cad_building_num TEXT;
CREATE INDEX IF NOT EXISTS complexes_cad_quarter_idx
    ON complexes(cad_quarter) WHERE cad_quarter IS NOT NULL;
CREATE INDEX IF NOT EXISTS complexes_cad_building_idx
    ON complexes(cad_building_num) WHERE cad_building_num IS NOT NULL;

-- Backfill: any non-null snapshot
WITH best_kn AS (
    SELECT obj_id,
           (array_agg(cad_quarter ORDER BY snapshot_date DESC NULLS LAST)
              FILTER (WHERE cad_quarter IS NOT NULL))[1] AS cad_quarter,
           (array_agg(cad_building_num ORDER BY snapshot_date DESC NULLS LAST)
              FILTER (WHERE cad_building_num IS NOT NULL))[1] AS cad_building_num
      FROM domrf_kn_objects GROUP BY obj_id
)
UPDATE complexes c
   SET cad_quarter = COALESCE(c.cad_quarter, bk.cad_quarter),
       cad_building_num = COALESCE(c.cad_building_num, bk.cad_building_num),
       updated_at = NOW()
  FROM complex_sources cs
  JOIN best_kn bk ON bk.obj_id::text = cs.source_id
 WHERE cs.complex_id = c.id AND cs.source = 'domrf_kn';
-- Эффект: cad_quarter заполнено у 551 из 1740 ЖК (32%).
-- cad_building_num = 0 (бaг kn-API: не парсит здание-уровень).

-- ── 2. objective_lots.complex_id (FK на complexes) ──────────────────────────
ALTER TABLE objective_lots
  ADD COLUMN IF NOT EXISTS complex_id BIGINT REFERENCES complexes(id) ON DELETE SET NULL;
ALTER TABLE objective_corpus_room_month
  ADD COLUMN IF NOT EXISTS complex_id BIGINT REFERENCES complexes(id) ON DELETE SET NULL;
ALTER TABLE objective_lots_history
  ADD COLUMN IF NOT EXISTS complex_id BIGINT REFERENCES complexes(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS objective_lots_complex_idx
  ON objective_lots(complex_id) WHERE complex_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS objective_crm_complex_idx
  ON objective_corpus_room_month(complex_id) WHERE complex_id IS NOT NULL;

UPDATE objective_lots ol
   SET complex_id = cs.complex_id
  FROM complex_sources cs
 WHERE cs.source = 'objective' AND cs.source_id = ol.project_name
   AND ol.complex_id IS NULL;

UPDATE objective_corpus_room_month ocrm
   SET complex_id = cs.complex_id
  FROM complex_sources cs
 WHERE cs.source = 'objective' AND cs.source_id = ocrm.project_name
   AND ocrm.complex_id IS NULL;
-- Эффект: 303 677 lots / 19 738 crm — 100% покрытие (все Objective lots
-- теперь напрямую JOIN'ятся с complex без двойного хопа через source_id).

-- ── 3. cad_buildings.complex_id (spatial join 50м) ──────────────────────────
ALTER TABLE cad_buildings
  ADD COLUMN IF NOT EXISTS complex_id BIGINT REFERENCES complexes(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS cad_buildings_complex_idx
  ON cad_buildings(complex_id) WHERE complex_id IS NOT NULL;

WITH bld_complex AS (
    SELECT cb.cad_num, c.complex_id_match
      FROM cad_buildings cb
      CROSS JOIN LATERAL (
        SELECT c2.id AS complex_id_match
          FROM complexes c2
         WHERE c2.geom IS NOT NULL
           AND ST_DWithin(c2.geom::geography, cb.geom::geography, 50)
         ORDER BY c2.geom <-> cb.geom LIMIT 1
      ) c
     WHERE cb.geom IS NOT NULL AND cb.complex_id IS NULL
)
UPDATE cad_buildings cb
   SET complex_id = bc.complex_id_match
  FROM bld_complex bc WHERE cb.cad_num = bc.cad_num;
-- Эффект: 716 buildings / 10590 (~7%) связаны с 436 уникальными ЖК.
-- Низкое покрытие т.к. большинство cad_buildings — частные дома в кадастровых
-- кварталах, не относящиеся к ЖК. Радиус 50м консервативный.

-- ── 4. Auto-create stub complex для 283 orphan obj_id из kn_flats ───────────
WITH orphan_obj_ids AS (
    SELECT DISTINCT obj_id FROM domrf_kn_flats f
     WHERE NOT EXISTS (
        SELECT 1 FROM complex_sources cs
         WHERE cs.source='domrf_kn' AND cs.source_id = f.obj_id::text
     )
),
inserted AS (
    INSERT INTO complexes (canonical_name, region_id, primary_source)
    SELECT 'orphan-kn-' || obj_id, 66, 'domrf_kn_flat_orphan' FROM orphan_obj_ids
    RETURNING id, canonical_name
)
INSERT INTO complex_sources (
    complex_id, source, source_id, source_external_id,
    match_method, match_score, is_reviewed, note
)
SELECT i.id, 'domrf_kn', oo.obj_id::text, oo.obj_id, 'orphan_flats_only', 0.0, FALSE,
       'auto-created stub: flats existed but kn-objects entry deleted/missing'
  FROM orphan_obj_ids oo
  JOIN inserted i ON i.canonical_name = 'orphan-kn-' || oo.obj_id;
-- Эффект: complexes 1740 → 2023 (+283 stub).

-- ── 5. v_complex_full v2: правильные counts с многокорпусными ЖК ────────────
DROP VIEW IF EXISTS v_complex_full;
CREATE VIEW v_complex_full AS
SELECT
    c.id AS complex_id,
    c.canonical_name, c.developer_name, c.region_id, c.district_name,
    c.obj_class, c.address, c.latitude, c.longitude,
    c.geom IS NOT NULL AS has_geom,
    c.flat_count, c.square_living, c.site_status,
    c.cad_quarter, c.cad_building_num,
    (SELECT array_agg(DISTINCT source ORDER BY source)
       FROM complex_sources WHERE complex_id = c.id) AS sources,
    (SELECT COUNT(*) FROM complex_sources WHERE complex_id = c.id) AS source_links_n,
    (SELECT COUNT(DISTINCT source) FROM complex_sources WHERE complex_id = c.id) AS source_kinds_n,
    (SELECT array_agg(source_external_id ORDER BY source_external_id)
       FROM complex_sources WHERE complex_id = c.id AND source = 'domrf_kn') AS domrf_obj_ids,
    (SELECT source_id FROM complex_sources
       WHERE complex_id = c.id AND source = 'objective' LIMIT 1) AS objective_project_name,
    (SELECT COUNT(*) FROM domrf_kn_flats f
        WHERE f.obj_id IN (SELECT source_external_id FROM complex_sources
                            WHERE complex_id = c.id AND source = 'domrf_kn'
                              AND source_external_id IS NOT NULL)) AS kn_flats_n,
    (SELECT COUNT(*) FROM objective_lots WHERE complex_id = c.id) AS objective_lots_n,
    (SELECT COUNT(*) FROM cad_buildings WHERE complex_id = c.id) AS cad_buildings_n,
    c.primary_source, c.created_at, c.updated_at
  FROM complexes c;

COMMENT ON VIEW v_complex_full IS
    'Карточка ЖК с агрегатами по всем источникам. multi-corpus ЖК (один project '
    'в Objective = N obj_id в kn) корректно объединены: kn_flats_n считает все '
    'корпуса. source_links_n включает дубликаты по корпусам, source_kinds_n — '
    'unique источники.';

-- ── 6. ANALYZE для актуализации статистики ──────────────────────────────────
ANALYZE complexes;
ANALYZE complex_sources;
ANALYZE objective_lots;
ANALYZE objective_corpus_room_month;
ANALYZE cad_buildings;
ANALYZE domrf_kn_objects;
ANALYZE domrf_kn_flats;