-- backfill_003_listing_sources.sql
-- Purpose: Populate listing_sources from legacy listings.(source, source_id) pairs.
-- Idempotent -- ON CONFLICT DO NOTHING.
--
-- Column notes (verified against 002_core_tables.sql + 011_listings_alter.sql
--               + 029_extend_matching_valuation_dynamics.sql):
--   listings.area_m2      -- numeric(8,2), defined in 002_core_tables.sql (NOT total_area)
--   listings.price_rub    -- bigint NOT NULL (002_core_tables.sql)
--   listings.floor        -- int (002_core_tables.sql)
--   listings.rooms        -- int (002_core_tables.sql, NOT rooms_count)
--   listings.scraped_at   -- timestamptz NOT NULL DEFAULT NOW() (002_core_tables.sql)
--   listing_sources columns (029): price_rub, area_m2, floor, rooms_count, raw_payload, last_seen_at
--
-- Usage:
--   psql "$DATABASE_URL" -f tradein-mvp/scripts/backfill_003_listing_sources.sql

BEGIN;

INSERT INTO listing_sources (
    listing_id,
    ext_source,
    ext_id,
    confidence,
    matched_method,
    matched_at,
    last_scraped_at,
    price_rub,
    area_m2,
    floor,
    rooms_count
)
SELECT
    l.id,
    l.source,
    l.source_id,
    1.0                      AS confidence,
    'backfill'               AS matched_method,
    NOW()                    AS matched_at,
    l.scraped_at             AS last_scraped_at,
    l.price_rub              AS price_rub,
    l.area_m2                AS area_m2,
    l.floor                  AS floor,
    l.rooms                  AS rooms_count
FROM listings l
WHERE l.source_id IS NOT NULL
ON CONFLICT (ext_source, ext_id) DO NOTHING;

\echo 'listing_sources backfill complete'
SELECT count(*) AS total_listing_sources FROM listing_sources;
SELECT ext_source, count(*) AS rows FROM listing_sources GROUP BY ext_source ORDER BY ext_source;

COMMIT;