-- 99a_quarter_price_index.sql
-- Issue #760 — Materialized view mv_quarter_price_index: per-cadastral-quarter
-- price index normalized to EKB city median. Foundation for estimator #647-3.
--
-- Naming: deploy.yml applies files via `ls -1 data/sql/*.sql | sort` (lexicographic).
-- '99a_' sorts AFTER '99_nspd_entities_denorm.sql' because '_' (0x5F) < 'a' (0x61).
-- Files 100_–122_ sort before 99_ (since '1' < '9') — they are already applied.
-- This file applies last of all current migrations.
--
-- Dependencies:
--   - public.mv_quarter_price_per_m2  (created by 95_mv_quarter_price.sql)
--     Columns used: quarter_cad_number (varchar), deals_count (bigint), median (float8)
--
-- Methodology:
--   1. Source: mv_quarter_price_per_m2, filter scope = all 66:% quarters present.
--   2. City normalizer (city_median): percentile_cont(0.5) over EKB (66:41:%) quarters
--      with deals_count >= 5. Computed in SQL — NOT hardcoded.
--   3. District fallback: per district prefix substring(quarter_cad_number for 5)
--      (e.g. '66:41'), district_median = median of quarter medians (deals_count>=5).
--   4. price_index assignment:
--        deals_count >= 5          → q.median / city_median          basis='quarter'
--        deals_count < 5 + district exists → district_median / city_median  basis='district'
--        no data                   → 1.0                             basis='city_fallback'
--   5. Coverage: all 66:% quarters (Sverdlovsk oblast), normalizer = EKB city median.
--
-- REFRESH CONCURRENTLY: enabled via UNIQUE index on quarter_cad_number.
-- Re-apply: DROP MATERIALIZED VIEW IF EXISTS ... CASCADE makes it idempotent.
-- _schema_migrations tracking prevents re-apply in prod (deploy.yml guard).
--
-- Validation snapshot (2026-05-30, source MV current):
--   city_median ≈ 110,883 ₽/m²
--   total 66:% quarters covered: 1,972
--   basis breakdown: quarter=1,708 / district=262 / city_fallback=2
--   NULL price_index: 0
--   index range: 0.2771 – 3.9788
--   Near-1.0 quarter: 66:41:0313118 (index=1.0000, q_median=110,883)
--   Top quarter: 66:41:0401018 (index=3.9788, q_median=441,176 — центр ЕКБ)
--   Low quarter: 66:17:0806002 (index=0.2771, q_median=30,722 — периферия области)
--   City-fallback examples: 66:00:0000000 (null-district), 66:39:0302011 (district no data)

BEGIN;

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;