"""Проверка: есть ли в DB Avito листинги из других городов (region leak)."""
from __future__ import annotations

import os
import psycopg

dsn = os.environ["DATABASE_URL"].replace("postgresql+psycopg://", "postgresql://")
conn = psycopg.connect(dsn)
cur = conn.cursor()

cur.execute("SELECT count(*) FROM listings WHERE source='avito'")
total = cur.fetchone()[0]
print(f"avito total: {total}")

# Все с lat/lon — проверим bounding box ЕКБ (56.7..57.0, 60.4..60.8)
cur.execute(
    """
    SELECT
        count(*) FILTER (WHERE lat BETWEEN 56.5 AND 57.0 AND lon BETWEEN 60.4 AND 61.0) as in_ekb,
        count(*) FILTER (WHERE lat IS NOT NULL AND (lat < 56.5 OR lat > 57.0 OR lon < 60.4 OR lon > 61.0)) as outside_ekb,
        count(*) FILTER (WHERE lat IS NULL) as no_coords
    FROM listings WHERE source='avito'
    """
)
in_ekb, outside, no_coords = cur.fetchone()
print(f"  in EKB bbox:        {in_ekb}")
print(f"  outside EKB bbox:   {outside}  ← out-of-region leak!")
print(f"  no coords yet:      {no_coords}")

# Sample outside
cur.execute(
    """
    SELECT source_id, lat, lon, address, price_rub, rooms
    FROM listings
    WHERE source='avito' AND lat IS NOT NULL
      AND (lat < 56.5 OR lat > 57.0 OR lon < 60.4 OR lon > 61.0)
    ORDER BY scraped_at DESC NULLS LAST
    LIMIT 10
    """
)
print("\nsample non-EKB listings:")
for r in cur.fetchall():
    sid, lat, lon, addr, price, rooms = r
    print(f"  {sid} ({lat:.3f},{lon:.3f}) rooms={rooms} {price:,}₽ — {(addr or '')[:80]!r}")

# Address-based leak (без lat/lon) — поиск явных маркеров других городов
cur.execute(
    """
    SELECT count(*) FROM listings
    WHERE source='avito'
      AND lat IS NULL
      AND address IS NOT NULL
      AND (address ILIKE '%москв%' OR address ILIKE '%омск%' OR address ILIKE '%чебок%'
        OR address ILIKE '%петерб%' OR address ILIKE '%казан%' OR address ILIKE '%новосиб%')
    """
)
print(f"\naddress-based leak (no coords, other-city keyword): {cur.fetchone()[0]}")

# По комнатам — где leak вероятнее всего
cur.execute(
    """
    SELECT rooms,
        count(*) AS total,
        count(*) FILTER (WHERE lat IS NOT NULL AND (lat < 56.5 OR lat > 57.0 OR lon < 60.4 OR lon > 61.0)) AS leak
    FROM listings WHERE source='avito'
    GROUP BY rooms
    ORDER BY rooms NULLS LAST
    """
)
print("\nleak by rooms:")
print(f"  {'rooms':<8} {'total':>8} {'leak':>8}")
for r in cur.fetchall():
    rooms, total, leak = r
    pct = (leak * 100.0 / total) if total else 0
    print(f"  {str(rooms):<8} {total:>8} {leak:>8}  ({pct:.0f}%)")