-- v_zk_rosreestr_velocity refresh: + cad_buildings cost_value для cross-check.
--
-- Что добавилось vs 60_v_zk_rosreestr_velocity.sql:
--   1. quarter_cadastre_avg_th_per_m2 — медианная кадастровая стоимость м²
--      строений (≥3 этажей) в том же кадастровом квартале.
--   2. cadastre_vs_market_pct — премиум/дисконт рынка относительно кадастра
--      (положительные значения = рынок дороже кадастра, что норма).
--      Аномалии (>+50% или <-30%) пригодны для warning-badge на UI.
--
-- Логика: cad_buildings.cost_value хранится в ₽ на здание; делим на area
-- чтобы получить ₽/м². Берём только residential (purpose ILIKE
-- '%многокварт%' OR floors >= 3) — низкоэтажки и склады искажают.
-- Нет фильтра по году ввода: даже старый фонд даёт ориентир для зонирования.
--
-- Источник истины cost_value: НСПД thematicSearchId=1 → properties.options.cost_value.

CREATE OR REPLACE VIEW v_zk_rosreestr_velocity AS
WITH zk_pool AS (
    SELECT DISTINCT ON (o.obj_id)
           o.obj_id,
           o.comm_name,
           o.dev_name,
           o.district_name,
           o.flat_count,
           o.cad_quarter,
           o.obj_class
      FROM domrf_kn_objects o
     WHERE o.region_cd = 66
       AND o.cad_quarter IS NOT NULL
       AND o.flat_count IS NOT NULL
       AND o.flat_count > 0
       AND o.site_status = 'Строящиеся'
     ORDER BY o.obj_id, o.snapshot_date DESC
),
quarter_totals AS (
    SELECT cad_quarter, SUM(flat_count)::numeric AS total_flats
      FROM zk_pool
     GROUP BY cad_quarter
),
quarter_deals AS (
    SELECT quarter_cad_number AS cad_quarter,
           COUNT(*)                                            AS deals_12mo,
           AVG(price_per_sqm)                                  AS avg_price_pm,
           PERCENTILE_CONT(0.5) WITHIN GROUP
               (ORDER BY price_per_sqm)                        AS median_price_pm,
           AVG(area)                                           AS avg_area_sqm
      FROM rosreestr_deals
     WHERE region_code = 66
       AND doc_type = 'ДДУ'
       AND realestate_type_code = '002001003000'
       AND area > 10 AND area <= 200
       AND price_per_sqm BETWEEN 30000 AND 1000000
       AND period_start_date >= NOW() - INTERVAL '12 months'
     GROUP BY quarter_cad_number
),
quarter_cadastre AS (
    -- Медианная кадастровая стоимость ₽/м² по жилым строениям квартала.
    -- Фильтры:
    --   * cost_value > 0 и area >= 100 м² (исключаем подсобки)
    --   * floors >= 3 ИЛИ purpose ILIKE '%многокв%' (отсеиваем ИЖС/гаражи)
    --   * ₽/м² 5К..500К (уберает мусор и грубые ошибки росреестра)
    SELECT
        quarter_cad_num                                         AS cad_quarter,
        COUNT(*)                                                AS buildings_n,
        PERCENTILE_CONT(0.5) WITHIN GROUP
            (ORDER BY cost_value / NULLIF(area, 0))             AS median_cost_per_m2
      FROM cad_buildings
     WHERE quarter_cad_num IS NOT NULL
       AND cost_value IS NOT NULL
       AND area IS NOT NULL
       AND area >= 100
       -- floors хранится как TEXT (встречаются '1-2', '2-3') —
       -- считаем только чистые числа ≥3, либо purpose-fallback.
       AND ((floors ~ '^[0-9]+$' AND floors::int >= 3)
            OR purpose ILIKE '%многокв%')
       AND (cost_value / NULLIF(area, 0)) BETWEEN 5000 AND 500000
     GROUP BY quarter_cad_num
)
SELECT
    z.obj_id,
    z.comm_name,
    z.dev_name,
    z.district_name,
    z.obj_class,
    z.flat_count,
    z.cad_quarter,
    qt.total_flats                                              AS quarter_total_flats,
    qd.deals_12mo                                               AS quarter_deals_12mo,
    ROUND(qd.deals_12mo * z.flat_count::numeric / qt.total_flats, 1)
                                                                AS estimated_deals_12mo,
    ROUND(qd.deals_12mo * z.flat_count::numeric / qt.total_flats / 12.0, 2)
                                                                AS estimated_velocity_pm,
    ROUND(qd.median_price_pm / 1000.0, 1)                       AS median_price_th_per_m2,
    ROUND(qd.avg_area_sqm, 1)                                   AS avg_deal_area_sqm,
    -- Кадастровая стоимость и cross-check.
    qc.buildings_n                                              AS cadastre_buildings_n,
    ROUND(qc.median_cost_per_m2 / 1000.0, 1)                    AS cadastre_th_per_m2,
    CASE
        WHEN qc.median_cost_per_m2 IS NOT NULL
             AND qd.median_price_pm IS NOT NULL
             AND qc.median_cost_per_m2 > 0
        THEN ROUND(
            (qd.median_price_pm - qc.median_cost_per_m2)
                / qc.median_cost_per_m2 * 100.0,
            1
        )
        ELSE NULL
    END                                                         AS cadastre_vs_market_pct
  FROM zk_pool z
  JOIN quarter_totals qt ON qt.cad_quarter = z.cad_quarter
  JOIN quarter_deals qd ON qd.cad_quarter = z.cad_quarter
  LEFT JOIN quarter_cadastre qc ON qc.cad_quarter = z.cad_quarter
;

COMMENT ON VIEW v_zk_rosreestr_velocity IS
    'v2: per-ЖК velocity/цена через rosreestr-сделки квартала + кадастровая '
    'стоимость м² из cad_buildings (NSPD). cadastre_vs_market_pct = премиум '
    'рынка над кадастром, аномалии (>+50% / <-30%) — outliers для warning UI.';