-- Migration 112: schema extension for issues #297 sub-tasks 22b + 22e + 22g + 22h
-- Adds 6 flat-level cols, 30 object-level cols (incl. metro_top3), 4 developer metrics.
--
-- Payload audit (obj=65136, 2026-05-17):
--   kn_api payload keys: objId, hobjId, objCommercNm, objAddr, shortAddr, rpdRegionCd,
--   developer{devId,devInn,orgForm,fullName,groupName,shortName,companyGroup},
--   rpdNum, objPublDt, objStatus, siteStatus, objFloorMax, objFloorMin, objPriceAVG,
--   problemFlag, rpdRegionCd, objSquareLiving, objElemLivingCnt, objGreenHouseFlg,
--   objGuarantyEscrowFlg, objReady100PercDt, hobjRenderPhotoUrl, aiDescription,
--   freeFlatsInfo{priceMin, numberFlats}, residentialBuildings,
--   metro{id, line, name, time, color, colors, isWalk}, latitude, longitude, pdId, buildType.
--
--   flat-level (portal/table): flatId, odsId, elemId, type, number, isStudio,
--   totalArea, livingArea, rooms, status, price, pricePerSquareMeter, numberFloors,
--   _objId(injected), _floor(injected), _entrance(injected as entranceNumber).
--
-- Fields that map NOW (from existing kn-API payload):
--   22b: section_no (from _entrance/entranceNumber injected by _flatten_table)
--   22e: section_count (residentialBuildings), project_declaration_num (rpdNum),
--        project_published_at (objPublDt), metro_nearest_name + metro_nearest_walk_minutes
--        (metro.name + metro.time), metro_top3 (metro wrapped as single-element array),
--        dev_group_name (developer.groupName), price_min_rub (freeFlatsInfo.priceMin)
--
-- Fields remaining NULL until catalog scraper 22d:
--   finishing_type, ceiling_height_m (flat), key_handover_dt, catalog_updated_at,
--   catalog_url_hash, first_floor_type, elevators_passenger_count, elevators_cargo_count,
--   parking_total_slots, guest_parking_inside_count, guest_parking_outside_count,
--   ceiling_height_m (obj), finishing_variants_count, has_free_planning,
--   playground_kids_count, playground_sport_count, has_bike_paths, trash_areas_count,
--   has_ramp, has_low_platforms, has_wheelchair_lift, flat_area_min, flat_area_max,
--   price_max_rub, price_per_m2_min, price_per_m2_max, parking_provision_pct,
--   avg_flat_area_m2, domrf_score_location, domrf_score_transport,
--   domrf_score_infrastructure, houses_building, houses_building_delayed,
--   houses_delivered, houses_delivered_delayed.

BEGIN;

-- ─────────────────────────────────────────────────────────────────────────────
-- 22b — domrf_kn_flats +6 columns
-- ─────────────────────────────────────────────────────────────────────────────
COMMENT ON TABLE domrf_kn_flats IS 'Per-flat catalogue from DOM.RF kn-API portal/table endpoint';

ALTER TABLE domrf_kn_flats
    ADD COLUMN IF NOT EXISTS section_no            int,
    ADD COLUMN IF NOT EXISTS finishing_type        text,
    ADD COLUMN IF NOT EXISTS ceiling_height_m      numeric(3,2),
    ADD COLUMN IF NOT EXISTS key_handover_dt       date,
    ADD COLUMN IF NOT EXISTS catalog_updated_at    date,
    ADD COLUMN IF NOT EXISTS catalog_url_hash      text;

COMMENT ON COLUMN domrf_kn_flats.section_no         IS '22b: подъезд из entrance.entranceNumber (portal/table)';
COMMENT ON COLUMN domrf_kn_flats.finishing_type      IS '22b/22d: тип отделки — заполнит catalog scraper (22d)';
COMMENT ON COLUMN domrf_kn_flats.ceiling_height_m    IS '22b/22d: высота потолков — заполнит catalog scraper (22d)';
COMMENT ON COLUMN domrf_kn_flats.key_handover_dt     IS '22b/22d: дата выдачи ключей — заполнит catalog scraper (22d)';
COMMENT ON COLUMN domrf_kn_flats.catalog_updated_at  IS '22b/22d: дата обновления инфо на catalog-странице (22d)';
COMMENT ON COLUMN domrf_kn_flats.catalog_url_hash    IS '22b/22d: hash-часть URL квартиры на каталоге dom.rf (22d)';

-- ─────────────────────────────────────────────────────────────────────────────
-- 22e + 22h — domrf_kn_objects +30 columns (incl. metro_top3)
-- ─────────────────────────────────────────────────────────────────────────────
COMMENT ON TABLE domrf_kn_objects IS 'Per-object (ЖК) catalogue from DOM.RF kn-API list endpoint';

-- Building specs (8)
ALTER TABLE domrf_kn_objects
    ADD COLUMN IF NOT EXISTS first_floor_type              text,
    ADD COLUMN IF NOT EXISTS section_count                 int,
    ADD COLUMN IF NOT EXISTS elevators_passenger_count     int,
    ADD COLUMN IF NOT EXISTS elevators_cargo_count         int,
    ADD COLUMN IF NOT EXISTS parking_total_slots           int,
    ADD COLUMN IF NOT EXISTS guest_parking_inside_count    int,
    ADD COLUMN IF NOT EXISTS guest_parking_outside_count   int,
    ADD COLUMN IF NOT EXISTS ceiling_height_m              numeric(3,2);

-- Apartment summary (3)
ALTER TABLE domrf_kn_objects
    ADD COLUMN IF NOT EXISTS finishing_variants_count      int,
    ADD COLUMN IF NOT EXISTS has_free_planning             bool,
    ADD COLUMN IF NOT EXISTS avg_flat_area_m2              numeric(5,1);

-- Yard infrastructure (4)
ALTER TABLE domrf_kn_objects
    ADD COLUMN IF NOT EXISTS playground_kids_count         int,
    ADD COLUMN IF NOT EXISTS playground_sport_count        int,
    ADD COLUMN IF NOT EXISTS has_bike_paths                bool,
    ADD COLUMN IF NOT EXISTS trash_areas_count             int;

-- Accessibility / OVZ (3)
ALTER TABLE domrf_kn_objects
    ADD COLUMN IF NOT EXISTS has_ramp                      bool,
    ADD COLUMN IF NOT EXISTS has_low_platforms             bool,
    ADD COLUMN IF NOT EXISTS has_wheelchair_lift           bool;

-- Catalog / UI (9)
ALTER TABLE domrf_kn_objects
    ADD COLUMN IF NOT EXISTS flat_area_min                 numeric(8,2),
    ADD COLUMN IF NOT EXISTS flat_area_max                 numeric(8,2),
    ADD COLUMN IF NOT EXISTS price_min_rub                 bigint,
    ADD COLUMN IF NOT EXISTS price_max_rub                 bigint,
    ADD COLUMN IF NOT EXISTS price_per_m2_min              numeric(12,2),
    ADD COLUMN IF NOT EXISTS price_per_m2_max              numeric(12,2),
    ADD COLUMN IF NOT EXISTS parking_provision_pct         numeric(5,1),
    ADD COLUMN IF NOT EXISTS project_published_at          date,
    ADD COLUMN IF NOT EXISTS project_declaration_num       text;

-- Metro & scores (5) — 22h merged into 22e
ALTER TABLE domrf_kn_objects
    ADD COLUMN IF NOT EXISTS metro_nearest_name            text,
    ADD COLUMN IF NOT EXISTS metro_nearest_walk_minutes    int,
    ADD COLUMN IF NOT EXISTS metro_top3                    jsonb,
    ADD COLUMN IF NOT EXISTS domrf_score_location          int,
    ADD COLUMN IF NOT EXISTS domrf_score_transport         int,
    ADD COLUMN IF NOT EXISTS domrf_score_infrastructure    int;

-- Developer split (1)
ALTER TABLE domrf_kn_objects
    ADD COLUMN IF NOT EXISTS dev_group_name                text;

COMMENT ON COLUMN domrf_kn_objects.section_count
    IS '22e: подъезды (residentialBuildings из kn-API)';
COMMENT ON COLUMN domrf_kn_objects.project_published_at
    IS '22e: дата публикации проекта (objPublDt из kn-API)';
COMMENT ON COLUMN domrf_kn_objects.project_declaration_num
    IS '22e: номер проектной декларации (rpdNum из kn-API)';
COMMENT ON COLUMN domrf_kn_objects.price_min_rub
    IS '22e: минимальная цена (freeFlatsInfo.priceMin из kn-API)';
COMMENT ON COLUMN domrf_kn_objects.metro_nearest_name
    IS '22h: ближайшая станция метро (metro.name из kn-API)';
COMMENT ON COLUMN domrf_kn_objects.metro_nearest_walk_minutes
    IS '22h: время пешком до метро, мин (metro.time из kn-API, rounded)';
COMMENT ON COLUMN domrf_kn_objects.metro_top3
    IS '22h: топ-3 ближайших станций [{name,time,line,color,isWalk}], сейчас 1 элемент';
COMMENT ON COLUMN domrf_kn_objects.dev_group_name
    IS '22e: группа компаний (developer.groupName из kn-API)';
COMMENT ON COLUMN domrf_kn_objects.domrf_score_location
    IS '22e: оценка расположения от DOM.RF (catalog 22d)';
COMMENT ON COLUMN domrf_kn_objects.domrf_score_transport
    IS '22e: оценка транспорта от DOM.RF (catalog 22d)';
COMMENT ON COLUMN domrf_kn_objects.domrf_score_infrastructure
    IS '22e: оценка инфраструктуры от DOM.RF (catalog 22d)';

-- ─────────────────────────────────────────────────────────────────────────────
-- 22g — domrf_developers +4 metrics
-- ─────────────────────────────────────────────────────────────────────────────
COMMENT ON TABLE domrf_developers IS 'Застройщики DOM.RF (companyGroup-level)';

ALTER TABLE domrf_developers
    ADD COLUMN IF NOT EXISTS houses_building             int,
    ADD COLUMN IF NOT EXISTS houses_building_delayed     int,
    ADD COLUMN IF NOT EXISTS houses_delivered            int,
    ADD COLUMN IF NOT EXISTS houses_delivered_delayed    int;

COMMENT ON COLUMN domrf_developers.houses_building
    IS '22g: домов строится (из карточки застройщика, catalog 22d)';
COMMENT ON COLUMN domrf_developers.houses_building_delayed
    IS '22g: строится с задержкой';
COMMENT ON COLUMN domrf_developers.houses_delivered
    IS '22g: сдано домов';
COMMENT ON COLUMN domrf_developers.houses_delivered_delayed
    IS '22g: сдано с задержкой';

COMMIT;
