sql/schema.sql

-- Esquema Neon/Postgres para Idealista HF + Telegram v2
-- Incluye medición de desplazamiento como desaparición rápida del anuncio
-- después de haber sido observado en distritos efectivamente consultados.

CREATE TABLE IF NOT EXISTS idealista_runs (
    run_id BIGSERIAL PRIMARY KEY,
    started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at TIMESTAMPTZ,
    source_filename TEXT,
    entries_count INTEGER DEFAULT 0,
    scraped_count INTEGER DEFAULT 0,
    inserted_count INTEGER DEFAULT 0,
    updated_count INTEGER DEFAULT 0,
    unchanged_count INTEGER DEFAULT 0,
    reactivated_count INTEGER DEFAULT 0,
    missing_updated_count INTEGER DEFAULT 0,
    probably_rented_count INTEGER DEFAULT 0,
    snapshot_count INTEGER DEFAULT 0,
    status TEXT NOT NULL DEFAULT 'running',
    error_message TEXT,
    districts_queried JSONB DEFAULT '[]'::jsonb,
    metadata JSONB DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS idealista_listings (
    listing_key TEXT PRIMARY KEY,
    listing_id TEXT,
    district_slug TEXT,
    source_input TEXT,
    page_hint INTEGER,
    position_in_page INTEGER,
    global_position INTEGER,
    url TEXT,
    title TEXT,
    address_text TEXT,
    location_full TEXT,
    price_eur NUMERIC,
    price_text TEXT,
    price_period TEXT,
    tipologia INTEGER,
    tipologia_text TEXT,
    area_m2 NUMERIC,
    area_text TEXT,
    floor_info TEXT,
    listed_when TEXT,
    estimated_published_at DATE,
    tag TEXT,
    agency_name TEXT,
    agency_url TEXT,
    image_main_url TEXT,
    image_main_webp TEXT,
    image_count INTEGER,
    online_booking BOOLEAN,
    has_map_button BOOLEAN,
    first_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_run_id BIGINT REFERENCES idealista_runs(run_id) ON DELETE SET NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    status TEXT NOT NULL DEFAULT 'active',
    missing_runs INTEGER NOT NULL DEFAULT 0,
    visible_runs INTEGER NOT NULL DEFAULT 1,
    days_to_displacement INTEGER,
    displacement_detection_lag_days INTEGER,
    rental_velocity_category TEXT NOT NULL DEFAULT 'unknown',
    rental_velocity_basis TEXT NOT NULL DEFAULT 'visible_runs_3_day_cadence',
    rental_velocity_classified_at TIMESTAMPTZ,
    deactivated_at TIMESTAMPTZ,
    reactivated_at TIMESTAMPTZ,
    content_hash TEXT,
    payload JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_idealista_listings_listing_id ON idealista_listings(listing_id);
CREATE INDEX IF NOT EXISTS idx_idealista_listings_district ON idealista_listings(district_slug);
CREATE INDEX IF NOT EXISTS idx_idealista_listings_price ON idealista_listings(price_eur);
CREATE INDEX IF NOT EXISTS idx_idealista_listings_last_seen ON idealista_listings(last_seen_at);
CREATE INDEX IF NOT EXISTS idx_idealista_listings_status ON idealista_listings(status);
CREATE INDEX IF NOT EXISTS idx_idealista_listings_missing ON idealista_listings(missing_runs);
CREATE INDEX IF NOT EXISTS idx_idealista_listings_velocity ON idealista_listings(rental_velocity_category);
CREATE INDEX IF NOT EXISTS idx_idealista_listings_visible_runs ON idealista_listings(visible_runs);

CREATE TABLE IF NOT EXISTS idealista_listing_snapshots (
    snapshot_id BIGSERIAL PRIMARY KEY,
    run_id BIGINT REFERENCES idealista_runs(run_id) ON DELETE CASCADE,
    listing_key TEXT NOT NULL,
    listing_id TEXT,
    district_slug TEXT,
    price_eur NUMERIC,
    area_m2 NUMERIC,
    title TEXT,
    url TEXT,
    page_hint INTEGER,
    position_in_page INTEGER,
    global_position INTEGER,
    listed_when TEXT,
    estimated_published_at DATE,
    content_hash TEXT,
    observed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    payload JSONB DEFAULT '{}'::jsonb,
    UNIQUE(run_id, listing_key)
);

CREATE INDEX IF NOT EXISTS idx_idealista_snapshots_listing_key ON idealista_listing_snapshots(listing_key);
CREATE INDEX IF NOT EXISTS idx_idealista_snapshots_run ON idealista_listing_snapshots(run_id);
CREATE INDEX IF NOT EXISTS idx_idealista_snapshots_observed ON idealista_listing_snapshots(observed_at);

-- Consultas útiles:

-- Mayor desplazamiento: anuncios que desaparecieron más rápido.
-- Para corrida cada tercer día se clasifica principalmente por visible_runs:
-- 1 = very_fast, 2-3 = fast, 4-6 = normal, 7-10 = slow, 11+ = very_slow.
-- SELECT listing_key, district_slug, status, rental_velocity_category, visible_runs,
--        missing_runs, first_seen_at, last_seen_at, deactivated_at,
--        days_to_displacement, displacement_detection_lag_days, price_eur, area_m2, title, url
-- FROM idealista_listings
-- WHERE status = 'probably_rented'
-- ORDER BY visible_runs ASC, days_to_displacement ASC NULLS LAST, deactivated_at DESC NULLS LAST;

-- Mayor antigüedad en base:
-- SELECT * FROM idealista_listings ORDER BY first_seen_at ASC;

-- Menor antigüedad en base:
-- SELECT * FROM idealista_listings ORDER BY first_seen_at DESC;