gendesign/tradein-mvp/backend/data/sql/216_dead_code_sweep.sql

-- 216_dead_code_sweep.sql
-- Purpose (#2674, раздел «Мёртвый код»): развести три разных вещи, которые снаружи
-- выглядят одинаково — «написано и ни разу не сработало».
--
--   1. ОБОРВАННАЯ ПРОВОДКА — механизм рабочий, звать некому/нечем. Чиним подключением.
--   2. МЁРТВОЕ — механизм невыразим, дублирует существующее или потерял смысл. Удаляем.
--   3. ЗАДЕЛ — оставляем, но в схеме должно быть написано, чем он НЕ является сегодня.
--
-- Все числа — с прод-БД tradein 2026-08-06, ТОЧНЫМ count(*). Первая редакция несла
-- сюда reltuples-оценки планировщика (listings «142 569» против реальных 93 408 —
-- раздув мёртвыми кортежами на 53%); в постоянном комментарии к схеме оценке не место,
-- тем более в PR, тезис которого — «каждое утверждение несёт число с прода».
--
-- ── 1. ПОДКЛЮЧАЕМ ───────────────────────────────────────────────────────────
--
-- A. domrf_kapremont_load — расписание для загрузчика ДОМ.РФ.
--    Загрузчик (app/services/domrf_kapremont_loader.py) и CLI
--    (app/tasks/domrf_kapremont_load.py) написаны и покрыты тестами с #2013, но
--    Handler'а в product_handlers и строки в scrape_schedules не существовало —
--    вызвать его было НЕЧЕМ. Итог на проде: 29 978 строк staging, у ВСЕХ один и тот
--    же loaded_at = 2026-07-12 13:19:30 (ровно один ручной запуск), 24 дня без
--    обновления. Это не мёртвый код: houses.year_built/material_walls/total_floors и
--    дальше listings.year_built (когортный фильтр эстиматора) кормятся именно отсюда.
--
-- B. external_valuations.filters_hash — бэкфилл из сырых ответов.
--    Парсер читал estimation.sale.data.filtersHash, а Циан кладёт ключ на УРОВЕНЬ
--    ВЫШЕ — estimation.sale.filtersHash (ключи sale на проде: isError, filtersHash,
--    data, isFetching). Колонка была пуста 0/1658, при том что в сырых ответах хеш
--    есть у 139/139 строк cian_valuation и все 139 значений различны. Путь починен в
--    providers/cian/valuation.py; здесь достаём то, что уже лежит в raw_payload.
--
-- ── 2. УДАЛЯЕМ ──────────────────────────────────────────────────────────────
--
-- C. asking_to_sold_ratios_tiered + asking_to_sold_tier_bounds (мигр. 098, #928).
--    Ноль читателей и ноль писателей в коде — грепом не находится ни одного
--    упоминания вне самой 098 и манифеста. Флага tier_aware_ratio_enabled, под
--    который таблицы задумывались, в конфиге не существует. Посчитаны один раз при
--    накатке (21 + 5 строк, computed_at 2026-06-27) и с тех пор не двигались, тогда
--    как ЖИВАЯ asking_to_sold_ratios обновляется ежедневно (computed_at 2026-08-05).
--    То есть в БД лежат коэффициенты выкупа сорокадневной давности, которые выглядят
--    как рабочая сегментация — их достаточно один раз прочитать по ошибке, чтобы
--    получить оценку по устаревшему рынку. Методика не потеряна: derivation-CTE
--    целиком сохранён в 098, восстановить = переприменить файл.
--
-- D. listings.merged_into — 93 408 строк, NULL у всех, ноль упоминаний в коде.
--    Заведена в 028 «under dedup workflow», который так и не построили; 113 уже
--    писала прямым текстом «column is dead, no code writer». Дедуп объявлений живёт
--    в другом месте и по-другому (estimator._union_find_phys_dedup, во время оценки,
--    без записи в БД). Соседнюю listings.canonical НЕ трогаем — она вырождена (t у
--    всех 93 408), но её читает WHERE listings_search_mv (050/094), и снос колонки
--    потянул бы пересоздание matview с шестью индексами ради нулевого выигрыша.
--
-- E. house_sources.raw_payload + GIN-индекс по нему — 49 502 строки, NULL у всех.
--    Оба писателя house_sources (matching/houses.py:556, house_dedup_merge.py:493)
--    эту колонку в INSERT не включают; читателей нет, из публичного контракта
--    market.v_house_sources (154) она намеренно исключена. GIN-индекс по колонке,
--    которая всегда NULL, — чистая стоимость на каждой вставке.
--    Соседний house_sources.ext_url тоже пуст 49 502/49 502, но он ВХОДИТ в
--    market.v_house_sources — удаление сломало бы обещание стабильности контракта.
--    Оставляем и подписываем (см. п. 3).
--
-- F. v_data_quality.price_disagreements_count — показатель, который не может быть
--    ненулевым. Считает строки v_price_divergence («разброс цен между площадками у
--    одного объявления > 5%»), а на проде у 89 699 объявлений РОВНО ОДИН источник
--    каждое: distinct listing_id = 89 699 при 89 699 строках listing_sources.
--    v_price_divergence = 0 строк, v_cross_source_health = 0 строк. Причина не в
--    данных: боевой путь загрузки (scrapers/base.py::_link_listing_to_house) зовёт
--    upsert_listing_source('source_link') напрямую и НЕ зовёт match_or_create_listing —
--    см. NOTE на matching/listings.py:188. Пока связывание источников не подключено,
--    ноль здесь читается как «расхождений нет», хотя честно это «мы не сравниваем».
--    Тот же довод, по которому 214 убрала outliers_flagged.
--
-- ── 3. ОСТАВЛЯЕМ И ПОДПИСЫВАЕМ ──────────────────────────────────────────────
--
-- G. Сами v_price_divergence / v_cross_source_health не удаляем: они выразимы и
--    станут ненулевыми в тот день, когда связывание источников заработает. Но в
--    COMMENT должно быть написано, что они пусты СТРУКТУРНО, а не по счастью.
--    Туда же — house_sources.ext_url.
--
-- ⚠️ View-зависимость (тот же порядок, что 214): v_data_quality содержит
--    `WITH active_listings AS (SELECT * FROM listings)`, что фиксирует column-level
--    зависимость на ВСЕ колонки listings. Порядок: DROP VIEW → DROP COLUMN →
--    CREATE VIEW. Последний DDL v_data_quality — 214_drop_dead_run_metrics.sql.
--
-- Dependencies: 028_matching_tables.sql, 029_extend_matching_valuation_dynamics.sql,
--               046_views.sql, 052_scrape_schedules.sql, 098_asking_to_sold_ratios_tiered.sql,
--               176_domrf_kapremont.sql, 214_drop_dead_run_metrics.sql.
-- Deploy order: применять ПОСЛЕ деплоя backend-кода, регистрирующего
--   'domrf_kapremont_load' в product_handlers.build_product_handlers() — иначе
--   kit-scheduler не найдёт Handler на первом due-run. next_run_at = завтра, так что
--   даже при обратном порядке накатки окно не наступит раньше следующих суток.
-- Идемпотентно: ON CONFLICT DO NOTHING / DROP ... IF EXISTS / CREATE OR REPLACE VIEW /
--   бэкфилл под WHERE filters_hash IS NULL.

BEGIN;

-- ── A. Расписание загрузчика ДОМ.РФ (оборванная проводка) ────────────────────
-- enabled = true: внешний источник, но открытые данные без auth и без анти-бота
--   (тот же класс, что sber_index_pull / rosreestr_quarter_poll).
-- interval_days = 7: реестр капремонта не меняется ежедневно, а прогон качает два
--   zip и парсит ~30 тыс. строк. Недельный такт достаточен и не жжёт трафик впустую.
--   Ключ читает compute_next_run_at из default_params (см. 129).
-- Окно 01:00-02:00 UTC. Первая редакция ставила 03:00-04:00 — ровно туда, где сидит
--   refresh_search_matview (сверено с прод-таблицей scrape_schedules), то есть именно
--   то задание, которое и переносит year_built в поиск. Планировщик берёт случайный
--   момент внутри окна и гоняет источники ПАРАЛЛЕЛЬНО, порядок он не гарантирует
--   ничем — совпадение окон превращало «сначала загрузка, потом обновление поиска»
--   в подбрасывание монеты. Час до 02:00 разводит их при типовой длительности прогона
--   и остаётся раньше rosreestr_dkp_import (04:00-06:00) и
--   asking_to_sold_ratio_refresh (06:00-07:00).
--   ЧЕСТНАЯ ОГОВОРКА: гарантии всё равно нет — при аномально долгом прогоне (сеть
--   ДОМ.РФ, ретраи) свежий year_built доедет до поиска на цикл позже. Ни блокировок,
--   ни потери данных: следующее обновление matview его подхватит.
--   Соседи в 01:00-02:00 — listing_source_snapshot и avito_city_sweep_kamensk_uralskiy;
--   общих ресурсов нет (ДОМ.РФ ходит своим httpx, мимо прокси-пула).
INSERT INTO scrape_schedules (
    source,
    enabled,
    window_start_hour,
    window_end_hour,
    next_run_at,
    default_params
)
VALUES
(
    'domrf_kapremont_load',
    true,
    1,
    2,
    ((CURRENT_DATE + INTERVAL '1 day') + make_interval(hours => 1)) AT TIME ZONE 'UTC',
    '{"interval_days": 7}'::jsonb
)
ON CONFLICT (source) DO NOTHING;

-- ── B. Бэкфилл filters_hash из уже сохранённых сырых ответов ─────────────────
-- Путь тот же, что теперь читает парсер. Пустую строку не пишем (NULLIF) — «есть
-- ключ, но он пуст» и «ключа нет» должны остаться одинаково NULL, а не разойтись.
UPDATE external_valuations
SET filters_hash = NULLIF(raw_payload #>> '{estimation,sale,filtersHash}', '')
WHERE filters_hash IS NULL
  AND raw_payload #>> '{estimation,sale,filtersHash}' IS NOT NULL;

-- ── C. Тиерные коэффициенты выкупа: две таблицы без читателя и писателя ──────
DROP TABLE IF EXISTS asking_to_sold_ratios_tiered;
DROP TABLE IF EXISTS asking_to_sold_tier_bounds;

-- ── D+E+F. Колонки без писателя + показатель, который не может быть ненулевым ─
DROP VIEW IF EXISTS v_data_quality;

ALTER TABLE IF EXISTS listings DROP COLUMN IF EXISTS merged_into;

DROP INDEX IF EXISTS house_sources_raw_payload_gin_idx;
ALTER TABLE IF EXISTS house_sources DROP COLUMN IF EXISTS raw_payload;

-- DDL идентичен 214, минус строка price_disagreements_count (см. п. F шапки).
CREATE OR REPLACE VIEW v_data_quality AS
WITH active_listings AS (
    SELECT * FROM listings WHERE is_active = true
)
SELECT
    (SELECT count(*) FROM houses)                                                  AS houses_total,
    (SELECT count(*) FROM houses h
       WHERE EXISTS (SELECT 1 FROM house_sources hs WHERE hs.house_id = h.id))      AS houses_with_source,
    (SELECT count(*) FROM houses h
       WHERE EXISTS (SELECT 1 FROM house_sources hs
                      WHERE hs.house_id = h.id AND hs.ext_source = 'avito'))        AS houses_with_avito,
    (SELECT count(*) FROM houses h
       WHERE EXISTS (SELECT 1 FROM house_sources hs
                      WHERE hs.house_id = h.id AND hs.ext_source LIKE 'cian%'))     AS houses_with_cian,
    (SELECT count(*) FROM houses h
       WHERE EXISTS (SELECT 1 FROM house_sources hs
                      WHERE hs.house_id = h.id AND hs.ext_source = 'yandex'))       AS houses_with_yandex,
    (SELECT count(*) FROM (
        SELECT house_id FROM house_sources GROUP BY house_id HAVING count(*) >= 2
    ) sub)                                                                          AS houses_2plus_sources,
    (SELECT count(*) FROM (
        SELECT house_id FROM house_sources GROUP BY house_id HAVING count(*) >= 3
    ) sub)                                                                          AS houses_3plus_sources,
    (SELECT count(*) FROM active_listings)                                         AS listings_active,
    (SELECT count(*) FROM (
        SELECT listing_id FROM listing_sources
         WHERE listing_id IN (SELECT id FROM active_listings)
         GROUP BY listing_id HAVING count(*) >= 2
    ) sub)                                                                          AS listings_dedup_2sources,
    (SELECT count(*) FROM active_listings WHERE lat IS NOT NULL) * 100.0
        / NULLIF((SELECT count(*) FROM active_listings), 0)                         AS pct_geocoded,
    (SELECT count(*) FROM active_listings WHERE cadastral_number IS NOT NULL) * 100.0
        / NULLIF((SELECT count(*) FROM active_listings), 0)                         AS pct_cadastr,
    (SELECT count(*) FROM active_listings WHERE description IS NOT NULL) * 100.0
        / NULLIF((SELECT count(*) FROM active_listings), 0)                         AS pct_description,
    (SELECT count(*) FROM active_listings l
       JOIN houses h ON h.id = l.house_id_fk
       WHERE h.year_built IS NOT NULL) * 100.0
        / NULLIF((SELECT count(*) FROM active_listings), 0)                         AS pct_year_built,
    NOW() - (SELECT max(scraped_at) FROM listings WHERE source = 'avito')           AS avito_last_scrape_ago,
    NOW() - (SELECT max(scraped_at) FROM listings WHERE source = 'cian')            AS cian_last_scrape_ago,
    NOW() - (SELECT max(scraped_at) FROM listings WHERE source = 'yandex')          AS yandex_last_scrape_ago;

COMMENT ON VIEW v_data_quality IS
  'KPI-снимок для РУЧНЫХ psql-запросов. Читателей в коде нет (проверено #2674): '
  '/api/v1/admin/scraper/data-quality считает свои метрики сам и этот view не трогает. '
  '#2674: price_disagreements_count убран — у всех 89 699 объявлений ровно один '
  'источник, поэтому показатель структурно не мог быть ненулевым и ноль читался как '
  '«расхождений нет» вместо «мы не сравниваем». listings_dedup_2sources оставлен '
  'намеренно: он ту же пустоту называет своим именем («объявлений с 2+ источниками»), '
  'ноль в нём — честный ответ, а не мнимое благополучие.';

-- ── G. Подписи к тому, что оставлено как задел ───────────────────────────────
COMMENT ON VIEW v_price_divergence IS
  'Объявления с разбросом цен между источниками > 5%. #2674: СЕГОДНЯ ВСЕГДА ПУСТ '
  'и это структурно, а не случайно — боевой путь загрузки зовёт '
  'upsert_listing_source(''source_link'') напрямую (scrapers/base.py::_link_listing_to_house) '
  'и не зовёт match_or_create_listing, поэтому у каждого объявления ровно один '
  'источник (89 699 строк listing_sources = 89 699 разных listing_id). View оставлен '
  'как задел: станет осмысленным в тот день, когда связывание источников подключат '
  '(см. NOTE на matching/listings.py:188). Из v_data_quality исключён — там ноль '
  'выглядел как результат проверки.';

COMMENT ON VIEW v_cross_source_health IS
  'Пер-объявленческая агрегация цен по источникам. #2674: пуст по той же причине, '
  'что v_price_divergence — HAVING count(*) >= 2 недостижим, пока связывание '
  'источников не подключено. Читателей в коде нет.';

COMMENT ON COLUMN house_sources.ext_url IS
  '#2674: NULL у всех 49 502 строк — ни один из двух писателей house_sources '
  '(matching/houses.py, house_dedup_merge.py) эту колонку не заполняет. НЕ удалена '
  'только потому, что входит в публичный контракт market.v_house_sources (мигр. 154), '
  'где удаление колонки объявлено ломающим изменением. Соседний raw_payload из '
  'контракта исключён и удалён этой же миграцией.';

COMMENT ON COLUMN houses.has_panorama IS
  '#2674: заполняется из yandex_valuation (estimator._save_yandex_house_panorama). '
  'До этой правки колонка была пуста у всех 9 366 домов, хотя парсер флаг разбирал. '
  'Пишется ТОЛЬКО когда страница оценки подтверждённо отрисовалась (в мете есть год '
  'или этажность) — иначе «метки нет» неотличимо от «страница не открылась», и NULL '
  'честнее false.';

COMMENT ON COLUMN external_valuations.filters_hash IS
  'sha256 фильтров от самого Циана, estimation.sale.filtersHash (#2674: НЕ '
  'estimation.sale.data.filtersHash — из-за лишнего уровня колонка была пуста 0/1658). '
  'Отличается от cache_key: cache_key — наш хеш параметров ЗАПРОСА, filters_hash — '
  'хеш того, во что Циан их разрешил, поэтому он способен схлопнуть варианты записи '
  'одного адреса, которые cache_key разводит.';

COMMIT;