"""Refetch OSM POIs adding social/lifestyle categories.

New categories:
  cafe          amenity=cafe
  restaurant    amenity=restaurant | fast_food
  fuel          amenity=fuel
  atm           amenity=atm
  post          amenity=post_office
  worship       amenity=place_of_worship
  trolley_stop  highway=bus_stop + bus=yes; OR amenity=bus_station
  police        amenity=police
  fire          amenity=fire_station
  library       amenity=library

Existing 15 stay; only fetch new and append.
Recompute distances + features.
"""
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"

NEW_QUERIES = [
    ("cafe",        '["amenity"="cafe"]'),
    ("restaurant",  '["amenity"~"^(restaurant|fast_food)$"]'),
    ("fuel",        '["amenity"="fuel"]'),
    ("atm",         '["amenity"="atm"]'),
    ("post",        '["amenity"="post_office"]'),
    ("worship",     '["amenity"="place_of_worship"]'),
    ("library",     '["amenity"="library"]'),
    ("police",      '["amenity"="police"]'),
    ("fire",        '["amenity"="fire_station"]'),
    ("bank",        '["amenity"="bank"]'),
]
ENDPOINTS = [
    "https://overpass-api.de/api/interpreter",
    "https://overpass.kumi.systems/api/interpreter",
    "https://overpass.openstreetmap.ru/api/interpreter",
]

def hav(la1,lo1,la2,lo2):
    R=6371000;p1,p2=math.radians(la1),math.radians(la2)
    dp=math.radians(la2-la1);dl=math.radians(lo2-lo1)
    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 overpass(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 = None
    for ep in ENDPOINTS:
        try:
            r = requests.post(ep, data={"data": q}, timeout=180, verify=False,
                              headers={"User-Agent":"gendesign/0.5"})
            if r.ok: return r.json().get("elements", [])
            last = f"{ep} {r.status_code}"
            time.sleep(2)
        except Exception as e:
            last = f"{ep} {e}"; time.sleep(2)
    raise RuntimeError(last)

def main():
    cache = json.loads(CACHE.read_text()) if CACHE.exists() else {}
    conn = sqlite3.connect(DB)
    sites = conn.execute("SELECT site_id, lat, lon FROM sites").fetchall()
    lats=[s[1] for s in sites]; lons=[s[2] for s in sites]
    b = (min(lats)-0.05, min(lons)-0.05, max(lats)+0.05, max(lons)+0.05)
    print(f"BBox: {b}")

    for cat, filt in NEW_QUERIES:
        if cat in cache:
            print(f"  {cat:<12} cached: {len(cache[cat])}")
            continue
        print(f"  {cat:<12} fetching ...", end=" ", flush=True)
        try:
            elems = overpass(filt, b)
            cache[cat] = elems
            print(f"{len(elems)}")
        except Exception as e:
            print(f"FAIL {e}")
            cache[cat] = []
        time.sleep(2)

    CACHE.write_text(json.dumps(cache, ensure_ascii=False))
    print(f"\nCache saved: {CACHE.stat().st_size/1024:.0f} KB ({len(cache)} categories)")

    # Recompute pois table from full cache
    conn.execute("DELETE FROM pois")
    n=0
    for cat, elems in cache.items():
        for el in elems:
            if el["type"]=="node":
                la,lo=el.get("lat"),el.get("lon")
            else:
                c=el.get("center") or {}; la,lo=c.get("lat"),c.get("lon")
            if la is None: continue
            tags=el.get("tags") or {}
            name = tags.get("name") or tags.get("operator") or ""
            for sid,sla,slo in sites:
                d = hav(sla,slo,la,lo)
                if d <= 2000:
                    conn.execute("""INSERT INTO pois(site_id,category,osm_type,osm_id,name,lat,lon,distance_m,raw_tags)
                                    VALUES (?,?,?,?,?,?,?,?,?)""",
                                 (sid,cat,el["type"],str(el["id"]),name,la,lo,d,json.dumps(tags,ensure_ascii=False)))
                    n+=1
    conn.commit()
    print(f"\nrecomputed {n} site-poi pairs")
    print("\nPOI counts per category:")
    for cat,c in conn.execute("SELECT category,count(*) FROM pois GROUP BY 1 ORDER BY 2 DESC").fetchall():
        print(f"  {cat:<14} {c}")
    conn.close()

if __name__ == "__main__":
    main()