-- 028_matching_tables.sql
-- Purpose: Cross-source matching link tables for deduplication and enrichment.
--          Implements 3-tier house + apartment matching per Cross_Source_Matching_Strategy.
--          house_sources   — one canonical house   ↔ many external (source, ext_id) pairs
--          listing_sources — one canonical listing ↔ many external (source, ext_id) pairs
--          house_address_aliases — manual overrides for corpus/address format mismatches
-- Dependencies:
--   - houses table (009_houses.sql)
--   - listings table (002_core_tables.sql)
-- Deploy order: Apply after 027.
--
-- Sources: Cross_Source_Matching_Strategy sec 2

BEGIN;

-- address_fingerprint column on houses (needed for Tier-1 matching algorithm)
ALTER TABLE houses
  ADD COLUMN IF NOT EXISTS address_fingerprint text;

CREATE INDEX IF NOT EXISTS houses_addr_fp_idx
  ON houses (address_fingerprint)
  WHERE address_fingerprint IS NOT NULL;

-- canonical / merged_into fields on listings (needed for dedup workflow)
ALTER TABLE listings
  ADD COLUMN IF NOT EXISTS canonical    boolean DEFAULT true,
  ADD COLUMN IF NOT EXISTS merged_into  bigint  REFERENCES listings(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS listings_merged_into_idx
  ON listings (merged_into)
  WHERE merged_into IS NOT NULL;

-- -----------------------------------------------------------------------
-- house_sources: link canonical house to external source records
-- -----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS house_sources (
    id              bigserial   PRIMARY KEY,
    house_id        bigint      NOT NULL REFERENCES houses(id) ON DELETE CASCADE,
    ext_source      text        NOT NULL,             -- 'avito' / 'cian' / 'yandex' / 'n1'
    ext_id          text        NOT NULL,             -- source-specific house/newbuilding ID
    confidence      real        NOT NULL,             -- 0.0–1.0
    matched_method  text        NOT NULL,             -- 'cadastr_exact'/'address_geo_year'/'geo_only'/'new'/'manual'
    matched_at      timestamptz NOT NULL DEFAULT NOW(),
    UNIQUE (ext_source, ext_id)
);

CREATE INDEX IF NOT EXISTS house_sources_house_idx
  ON house_sources (house_id);

-- Low-confidence matches for review queue
CREATE INDEX IF NOT EXISTS house_sources_low_conf_idx
  ON house_sources (confidence)
  WHERE confidence < 0.85;

-- -----------------------------------------------------------------------
-- listing_sources: link canonical listing to external source records
-- -----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS listing_sources (
    id              bigserial   PRIMARY KEY,
    listing_id      bigint      NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
    ext_source      text        NOT NULL,             -- 'avito' / 'cian'
    ext_id          text        NOT NULL,             -- Avito source_id / Cian cianId (as text)
    confidence      real        NOT NULL,             -- 0.0–1.0
    matched_method  text        NOT NULL,             -- 'cadastr_exact'/'minhash'/'composite'/'new'/'manual'
    matched_at      timestamptz NOT NULL DEFAULT NOW(),
    source_url      text,                             -- URL объявления на источнике
    last_scraped_at timestamptz,                      -- последний раз видели на источнике
    UNIQUE (ext_source, ext_id)
);

CREATE INDEX IF NOT EXISTS listing_sources_listing_idx
  ON listing_sources (listing_id);

-- Low-confidence listing matches for review
CREATE INDEX IF NOT EXISTS listing_sources_low_conf_idx
  ON listing_sources (confidence)
  WHERE confidence < 0.85;

-- Recency index for stale-data detection
CREATE INDEX IF NOT EXISTS listing_sources_scraped_idx
  ON listing_sources (ext_source, last_scraped_at DESC);

-- -----------------------------------------------------------------------
-- house_address_aliases: manual mapping for corpus/address format mismatches
-- e.g. 'ул. Постовского 17А' ↔ 'ул. Постовского 17 корп.1'
-- -----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS house_address_aliases (
    id                  bigserial   PRIMARY KEY,
    house_id            bigint      NOT NULL REFERENCES houses(id) ON DELETE CASCADE,
    normalized_address  text        NOT NULL,         -- normalized form (output of normalize_address())
    fingerprint         text,                         -- SHA1 of normalized_address
    source              text,                         -- which scraper/dataset provided this alias
    UNIQUE (normalized_address)
);

CREATE INDEX IF NOT EXISTS haa_house_idx
  ON house_address_aliases (house_id);

CREATE INDEX IF NOT EXISTS haa_fingerprint_idx
  ON house_address_aliases (fingerprint)
  WHERE fingerprint IS NOT NULL;

COMMIT;