-- 009_houses.sql
-- CREATE TABLE houses — multi-source house cache (Avito / CIAN / etc.)
--
-- Dependencies: 002_core_tables.sql (listings_set_geom function must exist)
-- Apply after: 008_crm_fields.sql
-- Apply before: 010_houses_alter.sql, 011_listings_alter.sql
--
-- Source: decisions/Schema_Avito_Houses_Listings_History.md sec 4.2
--
-- Идемпотентно: безопасно запускать повторно.

BEGIN;

CREATE TABLE IF NOT EXISTS houses (
    id              bigserial PRIMARY KEY,
    source          text NOT NULL,                       -- 'avito' / 'cian' / ...
    ext_house_id    text NOT NULL,                       -- Avito's house ID e.g. '3171365'

    -- Identity
    url             text NOT NULL,                       -- /catalog/houses/<slug>/<id>
    slug            text,                                -- ul_akademika_postovskogo_17a

    -- Geo (точные coords из developmentData.coords)
    address         text,
    lat             double precision,
    lon             double precision,
    geom            geometry(Point, 4326),               -- PostGIS для ST_DWithin

    -- Метаданные дома
    year_built      int,
    house_type      text,                                -- panel/brick/monolith/monolith_brick/other
    house_class     text,                                -- comfort/business/premium/economy
    total_floors    int,
    total_units     int,                                 -- кол-во квартир/апартаментов

    -- Лифты + удобства (из detail page / houses catalog developmentData)
    passenger_elevators int,
    cargo_elevators     int,
    has_concierge       boolean,
    closed_yard         boolean,

    -- Рейтинг
    rating          numeric(2,1),                        -- 4.8 (1 decimal)
    reviews_count   int,                                 -- кол-во отзывов

    -- Pin'ы соседних домов (для map preview) — JSONB
    map_pins        jsonb DEFAULT '[]'::jsonb,           -- [{lat, lng, type}, ...]

    -- Lifecycle
    raw_payload     jsonb,
    first_seen_at   timestamptz NOT NULL DEFAULT NOW(),
    last_scraped_at timestamptz NOT NULL DEFAULT NOW(),

    UNIQUE (source, ext_house_id)
);

-- Spatial index (обязателен для ST_Intersects/ST_DWithin)
CREATE INDEX IF NOT EXISTS houses_geom_idx        ON houses USING GIST (geom);
CREATE INDEX IF NOT EXISTS houses_source_ext_idx  ON houses (source, ext_house_id);
CREATE INDEX IF NOT EXISTS houses_last_scrape_idx ON houses (last_scraped_at DESC);

-- Trigger: auto-set geom из lat/lon (reuse listings_set_geom из 002_core_tables.sql)
DROP TRIGGER IF EXISTS houses_set_geom_trg ON houses;
CREATE TRIGGER houses_set_geom_trg
    BEFORE INSERT OR UPDATE OF lat, lon ON houses
    FOR EACH ROW EXECUTE FUNCTION listings_set_geom();

COMMENT ON TABLE houses IS
    'Кэш домов из Houses Catalog endpoint. 1 запись на (source, ext_house_id). '
    'Re-scrape если last_scraped_at > 30 дней. Geo через listings_set_geom trigger.';

COMMIT;