63 lines
4.1 KiB
PL/PgSQL
63 lines
4.1 KiB
PL/PgSQL
-- Fix missing relations + housekeeping after 27.04.2026 loads.
|
||
-- Audit findings:
|
||
-- 1. domrf_realization, domrf_xlsx_files, domrf_stat_series_observations
|
||
-- have snapshot_date but NO FK to domrf_snapshots.
|
||
-- 2. domrf_realization.region_code uses sentinel -1 for RF-level rows; the
|
||
-- RF placeholder already exists in domrf_regions as region_id=0. Migrate
|
||
-- sentinel and add FK to keep data consistent.
|
||
-- 3. Two duplicate indexes (covered by PK):
|
||
-- - idx_domrf_developer_aggregates_dev
|
||
-- - rr_agg_settl_search (on rr_agg_settlement)
|
||
-- 4. domrf_stat_series_observations.territory is TEXT and uses naming that
|
||
-- does NOT line up with domrf_regions.region_name (e.g. 'г. Москва' vs
|
||
-- 'Город Москва', 'Республика Адыгея (Адыгея)' vs 'Республика Адыгея',
|
||
-- plus 8 federal districts and a few synthetic rows). FK is not added;
|
||
-- lookup-by-alias work tracked as a separate TODO.
|
||
-- 5. ~8 700 stat_series rows have territory='-' or 'г. Москва1' (parser
|
||
-- noise). NOT deleted here — keep raw for future cleaning pass.
|
||
|
||
BEGIN;
|
||
|
||
-- 1. Migrate RF sentinel -1 → 0 (matches existing RF placeholder in regions)
|
||
UPDATE domrf_realization SET region_code = 0 WHERE region_code = -1;
|
||
ALTER TABLE domrf_realization ALTER COLUMN region_code SET DEFAULT 0;
|
||
|
||
-- 2. Add missing FKs
|
||
ALTER TABLE domrf_realization
|
||
ADD CONSTRAINT fk_snap_realization
|
||
FOREIGN KEY (snapshot_date) REFERENCES domrf_snapshots(snapshot_date) ON UPDATE CASCADE,
|
||
ADD CONSTRAINT fk_realization_region
|
||
FOREIGN KEY (region_code) REFERENCES domrf_regions(region_id) ON UPDATE CASCADE;
|
||
|
||
ALTER TABLE domrf_xlsx_files
|
||
ADD CONSTRAINT fk_snap_xlsx_files
|
||
FOREIGN KEY (snapshot_date) REFERENCES domrf_snapshots(snapshot_date) ON UPDATE CASCADE;
|
||
|
||
ALTER TABLE domrf_stat_series_observations
|
||
ADD CONSTRAINT fk_snap_stat_series_observations
|
||
FOREIGN KEY (snapshot_date) REFERENCES domrf_snapshots(snapshot_date) ON UPDATE CASCADE;
|
||
|
||
-- 3. Drop duplicate indexes (functionally covered by PK)
|
||
DROP INDEX IF EXISTS idx_domrf_developer_aggregates_dev;
|
||
DROP INDEX IF EXISTS rr_agg_settl_search;
|
||
|
||
-- 4. Document tables with COMMENT — helps future maintainers (and AI agents)
|
||
-- distinguish overlapping data sources (PDF snapshot vs API daily etc).
|
||
COMMENT ON TABLE domrf_realization IS
|
||
'RPP API per-region realization (sold/unsold/price). 4 endpoint_types: total, housing, ready_year, developer. region_code=0 = RF total. SUPERSET of the dropped sold_out_by_year / sold_out_by_progress (kept domrf_sold_out for unique attracted_funds_mln_rub).';
|
||
COMMENT ON TABLE domrf_sold_out IS
|
||
'PDF-snapshot of RF + 8 top regions (manual export 26.04). Kept ONLY for unique attracted_funds_mln_rub column. For full 89-region coverage use domrf_realization endpoint_type=total.';
|
||
COMMENT ON TABLE domrf_stat_series_observations IS
|
||
'Long-form panel of monthly Rosstat-form stats from 7 XLSX files (parsed 27.04). Indicators: см. domrf_stat_series_indicators. Coverage: 2020-2026, ~70 territories incl. 8 federal districts. NB: territory naming differs from domrf_regions.region_name (FK not enforced).';
|
||
COMMENT ON TABLE domrf_stat_series_indicators IS
|
||
'Lookup of XLSX sheet codes to human indicator names. PK (file_name, sheet_code) — codes like 01_01_01 repeat across files with different semantics.';
|
||
COMMENT ON TABLE domrf_xlsx_files IS
|
||
'BYTEA archive of raw stat_series XLSX. Acts as backup for re-parsing (domrf_stat_series_observations). 2 of 9 files (01_05_*) bespoke format — not yet normalized.';
|
||
COMMENT ON TABLE domrf_raw_endpoints IS
|
||
'Universal JSONB store: every captured /api/* response from DOM.RF analytics scrape. Source-of-truth backup for future normalization.';
|
||
COMMENT ON TABLE domrf_developers IS
|
||
'Developer dictionary from JSON dashboard (with developer_id). FK target for domrf_developer_aggregates and domrf_realization (when endpoint_type=developer; not enforced).';
|
||
COMMENT ON TABLE domrf_developers_full IS
|
||
'XLSX export of full developer registry (manual 26.04). Has metrics (area, permits, flats, market_share) but NO developer_id — name-only join to domrf_developers possible.';
|
||
|
||
COMMIT;
|