"""Fetch POIs from OSM Overpass for all sites in one bulk query.

POI taxonomy (matches the user's spec):
  EDUCATION
    kindergarten   amenity=kindergarten
    school         amenity=school
    university     amenity=university | college
  HEALTH
    pharmacy       amenity=pharmacy
    clinic         amenity=clinic | doctors
    hospital       amenity=hospital
  RETAIL
    shop_big       shop=mall | supermarket | department_store | hypermarket
    shop_med       shop=convenience | grocery | bakery
    shop_small     shop=kiosk | newsagent
  TRANSIT
    bus_stop       highway=bus_stop | public_transport=platform
    tram_stop      railway=tram_stop
    metro          railway=station + station=subway
  LEISURE
    park           leisure=park | garden
    playground     leisure=playground
    sports         leisure=sports_centre | fitness_centre | pitch
"""
import sqlite3, pathlib, requests, time, json, math, urllib3
urllib3.disable_warnings()

DB = pathlib.Path(__file__).parent / "analysis.db"
CACHE = pathlib.Path(__file__).parent / "cache" / "overpass_raw.json"

OVERPASS_ENDPOINTS = [
    "https://overpass-api.de/api/interpreter",
    "https://overpass.kumi.systems/api/interpreter",
    "https://overpass.openstreetmap.ru/api/interpreter",
]

# (category, overpass filter)
QUERIES = [
    ("kindergarten",  '["amenity"="kindergarten"]'),
    ("school",        '["amenity"="school"]'),
    ("university",    '["amenity"~"^(university|college)$"]'),
    ("pharmacy",      '["amenity"="pharmacy"]'),
    ("clinic",        '["amenity"~"^(clinic|doctors)$"]'),
    ("hospital",      '["amenity"="hospital"]'),
    ("shop_big",      '["shop"~"^(mall|supermarket|department_store|hypermarket)$"]'),
    ("shop_med",      '["shop"~"^(convenience|grocery|bakery)$"]'),
    ("shop_small",    '["shop"~"^(kiosk|newsagent)$"]'),
    ("bus_stop",      '["highway"="bus_stop"]'),
    ("tram_stop",     '["railway"="tram_stop"]'),
    ("metro",         '["station"="subway"]'),
    ("park",          '["leisure"~"^(park|garden)$"]'),
    ("playground",    '["leisure"="playground"]'),
    ("sports",        '["leisure"~"^(sports_centre|fitness_centre|pitch)$"]'),
]

def haversine_m(lat1, lon1, lat2, lon2):
    R = 6371000
    p1, p2 = math.radians(lat1), math.radians(lat2)
    dp = math.radians(lat2-lat1); dl = math.radians(lon2-lon1)
    a = math.sin(dp/2)**2 + math.cos(p1)*math.cos(p2)*math.sin(dl/2)**2
    return 2 * R * math.asin(math.sqrt(a))

def bbox(rows, pad_deg=0.05):
    lats=[r[0] for r in rows]; lons=[r[1] for r in rows]
    return (min(lats)-pad_deg, min(lons)-pad_deg, max(lats)+pad_deg, max(lons)+pad_deg)

def overpass_query(filt, b):
    q = f"""
[out:json][timeout:120];
(
  node{filt}({b[0]},{b[1]},{b[2]},{b[3]});
  way{filt}({b[0]},{b[1]},{b[2]},{b[3]});
);
out center tags;
"""
    last_err = None
    for ep in OVERPASS_ENDPOINTS:
        try:
            r = requests.post(ep, data={"data": q}, timeout=180, verify=False,
                              headers={"User-Agent":"gendesign-research/1.0"})
            if r.status_code == 200:
                return r.json()
            last_err = f"HTTP {r.status_code} from {ep}"
            time.sleep(2)
        except Exception as e:
            last_err = f"{ep}: {e}"
            time.sleep(2)
    raise RuntimeError(f"All Overpass endpoints failed: {last_err}")

def main():
    conn = sqlite3.connect(DB)
    sites = conn.execute("SELECT site_id, lat, lon FROM sites").fetchall()
    print(f"Sites: {len(sites)}")
    coords = [(s[1], s[2]) for s in sites]
    b = bbox(coords)
    print(f"BBox (S,W,N,E): {b}")

    cache_data = {}
    for cat, filt in QUERIES:
        print(f"  fetching {cat:<14} ...", end=" ", flush=True)
        d = overpass_query(filt, b)
        elements = d.get("elements", [])
        cache_data[cat] = elements
        print(f"{len(elements)} elements")
        time.sleep(2)  # be kind

    CACHE.parent.mkdir(parents=True, exist_ok=True)
    with open(CACHE, 'w') as f: json.dump(cache_data, f, ensure_ascii=False)
    print(f"\nCache: {CACHE} ({CACHE.stat().st_size/1024:.0f} KB)")

    # Compute nearest POI per site per category, plus all POIs within 1km
    conn.execute("DELETE FROM pois")
    n_pois = 0
    for cat, elems in cache_data.items():
        # extract (lat,lon,name,raw)
        poi_list = []
        for el in elems:
            if el["type"] == "node":
                lat, lon = el.get("lat"), el.get("lon")
            else:
                c = el.get("center") or {}
                lat, lon = c.get("lat"), c.get("lon")
            if lat is None or lon is None: continue
            tags = el.get("tags") or {}
            name = tags.get("name") or tags.get("operator") or ""
            poi_list.append((lat, lon, name, el["type"], el["id"], json.dumps(tags, ensure_ascii=False)))
        # for each site, find within 2 km (we'll bucket by distance later)
        for s in sites:
            site_id, slat, slon = s
            for plat, plon, pname, ptype, pid, ptags in poi_list:
                d = haversine_m(slat, slon, plat, plon)
                if d <= 2000:
                    conn.execute("""INSERT INTO pois(site_id,category,osm_type,osm_id,name,lat,lon,distance_m,raw_tags)
                        VALUES (?,?,?,?,?,?,?,?,?)""",
                        (site_id, cat, ptype, str(pid), pname, plat, plon, d, ptags))
                    n_pois += 1
    conn.commit()
    print(f"\nStored {n_pois} POI-site pairs (within 2 km)")
    print("Per-category:")
    for cat, n in conn.execute("SELECT category,count(*) FROM pois GROUP BY 1 ORDER BY 2 DESC").fetchall():
        print(f"  {cat:<14} {n}")
    conn.close()

if __name__ == "__main__":
    main()