#!/usr/bin/env python3
# Fajl: objekti_enrich_address.py | v1.0 | 05.05.2026
# Author: Damir Radulić
# Svrha: Reverse-geocode lat/lng → adresa za sportski_objekti
import os, time, json
import psycopg2, requests

DSN = f"host=10.10.0.2 port=6432 dbname=rinet_v3 user=rinet password={os.environ['DB_PASSWORD']}"
HEADERS = {"User-Agent": "Ri.NET PGŽ Sport (dradulic@outlook.com)"}

conn = psycopg2.connect(DSN); conn.autocommit = True

with conn.cursor() as cur:
    cur.execute("""
        SELECT id, naziv, lat, lng FROM pgz_sport.sportski_objekti 
        WHERE aktivan = true AND lat IS NOT NULL AND lng IS NOT NULL
          AND (adresa IS NULL OR adresa = '')
        LIMIT 60
    """)
    rows = cur.fetchall()

print(f"Total: {len(rows)} objekata bez adrese")

for i, (oid, naziv, lat, lng) in enumerate(rows):
    try:
        # Nominatim reverse geocoding
        r = requests.get(
            f"https://nominatim.openstreetmap.org/reverse",
            params={"lat": lat, "lon": lng, "format": "json", "accept-language": "hr"},
            headers=HEADERS, timeout=10
        )
        if r.status_code == 200:
            d = r.json()
            addr = d.get("display_name", "")
            # Krat: ulica + broj + grad
            a = d.get("address", {})
            short = []
            for k in ["road", "house_number", "suburb", "city", "town", "village"]:
                if a.get(k): short.append(a[k])
            addr_short = ", ".join(short[:4]) or addr[:100]
            
            with conn.cursor() as cur:
                cur.execute("UPDATE pgz_sport.sportski_objekti SET adresa = %s WHERE id = %s", (addr_short, oid))
            print(f"  [{i+1}/{len(rows)}] {naziv} → {addr_short}")
        time.sleep(1.1)  # Nominatim rate-limit 1 req/s
    except Exception as e:
        print(f"  [FAIL] {naziv}: {e}")

print("DONE")