gendesign/data/sql/179_mv_quarter_price_cadastral_floor.sql

-- 179_mv_quarter_price_cadastral_floor.sql
-- Denoise the deals price index by a cadastral-value floor.
--
-- Chain (unchanged consumers):
--   rosreestr_deals
--     → mv_quarter_price_per_m2   (95_mv_quarter_price.sql)      ← MODIFIED here
--     → mv_quarter_price_index    (99a_quarter_price_index.sql)  ← recreated as-is
--     → tradein FDW quarter_price_index (reads mv_quarter_price_index by name)
--
-- What changes: mv_quarter_price_per_m2 gains a cadastral floor. Registered deals
-- priced below 0.7 × the cadastral median ₽/m² for their cad-quarter are dropped
-- as noise (data-entry junk / non-arm's-length deals drag medians down).
-- Floor source: rosreestr_cadastral_value (schema 178, loaded MANUALLY by
-- 178b_load_cadastral_value.py — region-66-scoped, apartment code 002001003000).
--
-- Why this file recreates BOTH MVs:
--   DROP MATERIALIZED VIEW mv_quarter_price_per_m2 CASCADE also drops the dependent
--   mv_quarter_price_index. We must recreate the index MV (verbatim from 99a) to
--   restore the FDW contract that CASCADE removed.
--
-- *** CRITICAL SAFETY — no-op when rosreestr_cadastral_value is empty ***
--   On prod this migration auto-applies (deploy.yml / _schema_migrations) BEFORE
--   any cadastral data is loaded — the table exists (178) but is EMPTY. With zero
--   rows, the cad_floor CTE produces zero rows → the LEFT JOIN yields
--   floor_ppm2 IS NULL for EVERY quarter → the predicate
--   `cf.floor_ppm2 IS NULL OR price_m2 >= cf.floor_ppm2` keeps ALL deals. The
--   resulting mv_quarter_price_per_m2 is byte-identical to the pre-floor (95)
--   definition. The floor only takes effect after an operator loads cadastral
--   data and REFRESHes the MVs. Verified by a rolled-back dry-run (EXCEPT both
--   directions = 0 rows when the cad source is empty).
--
-- Idempotent: DROP ... IF EXISTS ... CASCADE + recreate. Re-apply guarded by
--   _schema_migrations in prod. First REFRESH is non-concurrent (empty MV).
-- Dependencies: rosreestr_deals, rosreestr_cadastral_value (178).
-- Output columns / unique index names / REFRESH / COMMENT preserved exactly
--   (FDW + 99a depend on the column contract).

BEGIN;

-- ---------------------------------------------------------------------------
-- 1. Drop per_m2 MV (CASCADE also drops mv_quarter_price_index — recreated below).
-- ---------------------------------------------------------------------------
DROP MATERIALIZED VIEW IF EXISTS mv_quarter_price_per_m2 CASCADE;

-- ---------------------------------------------------------------------------
-- 2. Recreate mv_quarter_price_per_m2 = body from 95_mv_quarter_price.sql,
--    plus the cadastral floor (cad_floor CTE + LEFT JOIN in `filtered`).
-- ---------------------------------------------------------------------------
CREATE MATERIALIZED VIEW mv_quarter_price_per_m2 AS
WITH cad_floor AS (
    -- Cadastral median ₽/m² per quarter × 0.7 → noise floor.
    -- Zero rows when rosreestr_cadastral_value is empty → floor disabled (no-op).
    SELECT quarter_cad_number,
           0.7 * percentile_cont(0.5) WITHIN GROUP (ORDER BY cost_per_area) AS floor_ppm2
    FROM rosreestr_cadastral_value
    WHERE realestate_type_code = '002001003000'
      AND cost_per_area IS NOT NULL AND cost_per_area > 0
    GROUP BY quarter_cad_number
),
dataset AS (
    -- Resolve price_m2: use pre-computed field, fall back to deal_price / area
    SELECT
        quarter_cad_number,
        CASE
            WHEN price_per_sqm IS NOT NULL           THEN price_per_sqm
            WHEN deal_price IS NOT NULL AND area > 0 THEN deal_price / area
            ELSE NULL
        END                 AS price_m2,
        period_start_date   AS deal_date
    FROM rosreestr_deals
    WHERE realestate_type_code = '002001003000'
      AND period_start_date >= NOW() - INTERVAL '24 months'
),
filtered AS (
    -- Apply outlier filter once; reused by both aggregate CTEs.
    -- Cadastral-floor denoise: drop deals below 0.7 × cadastral median ₽/m².
    -- floor_ppm2 IS NULL (no cadastral row for the quarter) → keep the deal.
    SELECT quarter_cad_number, price_m2, deal_date
    FROM dataset
    LEFT JOIN cad_floor cf USING (quarter_cad_number)
    WHERE price_m2 BETWEEN 30000 AND 800000
      AND (cf.floor_ppm2 IS NULL OR price_m2 >= cf.floor_ppm2)
),
agg_main AS (
    -- Primary aggregation: full 24-month window
    SELECT
        quarter_cad_number,
        COUNT(*)                                                AS deals_count,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY price_m2) AS p25,
        PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY price_m2) AS median,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY price_m2) AS p75,
        AVG(price_m2)::numeric(12, 2)                          AS mean,
        MAX(deal_date)                                         AS last_deal_date
    FROM filtered
    GROUP BY quarter_cad_number
    HAVING COUNT(*) >= 3
),
agg_windows AS (
    -- Sub-window medians per quarter.
    -- PERCENTILE_CONT does not accept a FILTER clause in PG16, so we pre-filter
    -- per window inside separate conditional aggregations using a CASE that
    -- returns NULL for out-of-window rows (NULLs are excluded from PERCENTILE_CONT).
    SELECT
        quarter_cad_number,
        PERCENTILE_CONT(0.50) WITHIN GROUP (
            ORDER BY CASE WHEN deal_date > NOW() - INTERVAL '6 months'
                          THEN price_m2 ELSE NULL END
        )                                                       AS median_6m,
        PERCENTILE_CONT(0.50) WITHIN GROUP (
            ORDER BY CASE WHEN deal_date > NOW() - INTERVAL '12 months'
                          THEN price_m2 ELSE NULL END
        )                                                       AS median_12m
    FROM filtered
    GROUP BY quarter_cad_number
)
SELECT
    m.quarter_cad_number,
    m.deals_count,
    m.p25,
    m.median,
    m.p75,
    m.mean,
    m.last_deal_date,
    w.median_6m,
    w.median_12m,
    -- median_24m == median (window is already 24mo); kept for API symmetry
    m.median                                                    AS median_24m
FROM agg_main m
JOIN agg_windows w USING (quarter_cad_number)
WITH NO DATA;

-- UNIQUE index required for REFRESH MATERIALIZED VIEW CONCURRENTLY.
-- Created on empty MV → instant (no data lock).
CREATE UNIQUE INDEX mv_quarter_price_pk
    ON mv_quarter_price_per_m2 (quarter_cad_number);

-- Initial populate (non-concurrent — CONCURRENTLY not allowed on empty MV).
REFRESH MATERIALIZED VIEW mv_quarter_price_per_m2;

COMMENT ON MATERIALIZED VIEW mv_quarter_price_per_m2 IS
    'Per-cad_quarter (quarter_cad_number) price aggregation из rosreestr_deals. '
    'Filter: realestate_type_code=002001003000 (новостройки/ДДУ), rolling 24 months, '
    'outlier filter 30K–800K руб/м², HAVING >= 3 deals. '
    'All-Russia scope (~52K rows expected). '
    'Refresh: weekly via celery beat (TBD, Issue #33 PR C).';

-- ---------------------------------------------------------------------------
-- 3. Recreate mv_quarter_price_index = body from 99a_quarter_price_index.sql,
--    UNCHANGED (restores what the CASCADE above dropped).
-- ---------------------------------------------------------------------------
DROP MATERIALIZED VIEW IF EXISTS mv_quarter_price_index CASCADE;

CREATE MATERIALIZED VIEW mv_quarter_price_index AS
WITH
-- All 66:% quarters from source MV (Sverdlovsk oblast scope)
all_66_quarters AS (
    SELECT
        quarter_cad_number,
        deals_count,
        median,
        substring(quarter_cad_number FOR 5) AS district_prefix
    FROM mv_quarter_price_per_m2
    WHERE quarter_cad_number LIKE '66:%'
),

-- City normalizer: equal-weight median-of-quarter-medians for EKB (66:41:%)
-- Uses only quarters with deals_count >= 5 to suppress noise.
city_median_cte AS (
    SELECT
        percentile_cont(0.5) WITHIN GROUP (ORDER BY median) AS city_median
    FROM all_66_quarters
    WHERE quarter_cad_number LIKE '66:41:%'
      AND deals_count >= 5
),

-- District fallback: median of quarter medians per district prefix,
-- also restricted to deals_count >= 5 quarters.
district_medians AS (
    SELECT
        district_prefix,
        percentile_cont(0.5) WITHIN GROUP (ORDER BY median) AS district_median
    FROM all_66_quarters
    WHERE deals_count >= 5
    GROUP BY district_prefix
)

SELECT
    q.quarter_cad_number,
    CASE
        WHEN q.deals_count >= 5                    THEN q.median / c.city_median
        WHEN dm.district_median IS NOT NULL        THEN dm.district_median / c.city_median
        ELSE 1.0
    END::double precision                                       AS price_index,
    q.deals_count                                               AS n_deals,
    CASE
        WHEN q.deals_count >= 5                    THEN 'quarter'
        WHEN dm.district_median IS NOT NULL        THEN 'district'
        ELSE 'city_fallback'
    END::text                                                   AS basis,
    NOW()                                                       AS computed_at
FROM all_66_quarters q
CROSS JOIN city_median_cte c
LEFT JOIN district_medians dm ON dm.district_prefix = q.district_prefix
WITH NO DATA;

-- UNIQUE index required for REFRESH MATERIALIZED VIEW CONCURRENTLY.
-- Also serves as O(1) PK lookup for the estimator (#647-3).
CREATE UNIQUE INDEX IF NOT EXISTS mv_quarter_price_index_uq
    ON mv_quarter_price_index (quarter_cad_number);

-- Initial populate (non-concurrent — MV is empty, CONCURRENTLY not allowed on empty MV).
REFRESH MATERIALIZED VIEW mv_quarter_price_index;

COMMENT ON MATERIALIZED VIEW mv_quarter_price_index IS
    'Per-cadastral-quarter price index normalized to EKB city median (=1.0). '
    'Source: mv_quarter_price_per_m2. Scope: all 66:% (Sverdlovsk oblast) quarters. '
    'city_median = percentile_cont(0.5) over 66:41:% quarters with deals_count>=5. '
    'basis: quarter (>=5 deals) | district (sparse, district fallback) | city_fallback. '
    'Refresh: CONCURRENTLY safe (UNIQUE index on quarter_cad_number). '
    'Consumer: estimator service (#647-3) reads O(1) by quarter_cad_number. '
    'Issue: #760.';

COMMIT;