#!/usr/bin/env python3 import concurrent.futures as cf import json import sqlite3 import time from pathlib import Path import requests BASE_FEATURE = "https://svc-location.aircash.eu/api/Locations/GetLocationsFeature" BASE_DETAIL = "https://svc-location.aircash.eu/api/Locations/GetOneLocation" OUT_DIR = Path("/home/louicyp/clawd-clean/data") OUT_DIR.mkdir(parents=True, exist_ok=True) DB_PATH = OUT_DIR / "aircash_locations.sqlite" JSON_PATH = OUT_DIR / "aircash_locations.json" BBOX = { "Lat": 47, "Lng": 15, "TopRightPointLat": 72, "TopRightPointLng": 45, "BottomLeftPointLat": 30, "BottomLeftPointLng": -25, } CATEGORY_QUERIES = [ ("payment", {"PaymentLocation": "true", "PayoutLocation": "", "AtmLocation": "", "AircashCardLocation": "", "AbonLocation": ""}), ("payout", {"PaymentLocation": "", "PayoutLocation": "true", "AtmLocation": "", "AircashCardLocation": "", "AbonLocation": ""}), ("atm", {"PaymentLocation": "", "PayoutLocation": "", "AtmLocation": "true", "AircashCardLocation": "", "AbonLocation": ""}), ("aircash_card", {"PaymentLocation": "", "PayoutLocation": "", "AtmLocation": "", "AircashCardLocation": "true", "AbonLocation": ""}), ("abon", {"PaymentLocation": "", "PayoutLocation": "", "AtmLocation": "", "AircashCardLocation": "", "AbonLocation": "true"}), ] def init_db(conn: sqlite3.Connection): conn.execute( """ CREATE TABLE IF NOT EXISTS locations ( id INTEGER PRIMARY KEY, lat REAL, lng REAL, last_update_utc TEXT, image_url TEXT, active INTEGER, payment_location INTEGER, payout_location INTEGER, atm_location INTEGER, aircash_card_location INTEGER, abon_location INTEGER, partner_name TEXT, address_line TEXT, post_code_and_city TEXT, working_hours TEXT, first_seen_category TEXT, detail_status TEXT DEFAULT 'pending', detail_error TEXT ) """ ) conn.commit() def bool_to_int(v): return 1 if bool(v) else 0 def fetch_features(session: requests.Session, category: str, flags: dict): params = {**BBOX, **flags} r = session.get(BASE_FEATURE, params=params, timeout=180) r.raise_for_status() data = r.json() return category, data.get("features", []) def upsert_feature(conn: sqlite3.Connection, feature: dict, category: str): p = feature.get("properties", {}) fid = int(feature["id"]) conn.execute( """ INSERT INTO locations ( id, lat, lng, last_update_utc, image_url, active, payment_location, payout_location, atm_location, aircash_card_location, abon_location, first_seen_category ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET lat=excluded.lat, lng=excluded.lng, last_update_utc=excluded.last_update_utc, image_url=excluded.image_url, active=excluded.active, payment_location=MAX(payment_location, excluded.payment_location), payout_location=MAX(payout_location, excluded.payout_location), atm_location=MAX(atm_location, excluded.atm_location), aircash_card_location=MAX(aircash_card_location, excluded.aircash_card_location), abon_location=MAX(abon_location, excluded.abon_location) """, ( fid, p.get("lat"), p.get("lng"), p.get("lastUpdateDateUTC"), p.get("imageUrl"), bool_to_int(p.get("active")), bool_to_int(p.get("paymentLocation")), bool_to_int(p.get("payoutLocation")), bool_to_int(p.get("atmLocation")), bool_to_int(p.get("aircashCardLocation")), bool_to_int(p.get("abonLocation")), category, ), ) def fetch_detail(session: requests.Session, loc_id: int): try: r = session.get(BASE_DETAIL, params={"Id": loc_id}, timeout=40) if r.status_code == 200: j = r.json() return loc_id, "ok", j, None return loc_id, "error", None, f"HTTP {r.status_code}: {r.text[:200]}" except Exception as e: return loc_id, "error", None, str(e) def main(): t0 = time.time() conn = sqlite3.connect(DB_PATH) init_db(conn) session = requests.Session() print("Fetching feature sets...") total_features = 0 for category, flags in CATEGORY_QUERIES: cat, feats = fetch_features(session, category, flags) print(f" {cat}: {len(feats)}") total_features += len(feats) for f in feats: upsert_feature(conn, f, cat) conn.commit() n_unique = conn.execute("SELECT COUNT(*) FROM locations").fetchone()[0] print(f"Unique locations discovered: {n_unique} (from {total_features} feature records)") ids = [row[0] for row in conn.execute("SELECT id FROM locations")] print("Fetching location detail records (parallel)...") done = 0 batch = [] def flush_batch(rows): if not rows: return conn.executemany( """ UPDATE locations SET partner_name=?, address_line=?, post_code_and_city=?, working_hours=?, detail_status=?, detail_error=? WHERE id=? """, rows, ) conn.commit() with cf.ThreadPoolExecutor(max_workers=64) as ex: local = requests.Session() futures = [ex.submit(fetch_detail, local, lid) for lid in ids] for fut in cf.as_completed(futures): lid, status, detail, err = fut.result() if status == "ok": batch.append(( detail.get("partnerName"), detail.get("addressLine"), detail.get("postCodeAndCity"), detail.get("workingHours"), "ok", None, lid, )) else: batch.append((None, None, None, None, "error", err, lid)) done += 1 if len(batch) >= 1000: flush_batch(batch) batch.clear() if done % 5000 == 0: print(f" detailed: {done}/{len(ids)}") flush_batch(batch) print("Exporting JSON...") cur = conn.execute( """ SELECT id, lat, lng, partner_name, address_line, post_code_and_city, working_hours, payment_location, payout_location, atm_location, aircash_card_location, abon_location, active, last_update_utc, detail_status FROM locations ORDER BY id """ ) cols = [c[0] for c in cur.description] rows = [dict(zip(cols, r)) for r in cur.fetchall()] JSON_PATH.write_text(json.dumps(rows, ensure_ascii=False, indent=2), encoding="utf-8") ok_count = conn.execute("SELECT COUNT(*) FROM locations WHERE detail_status='ok'").fetchone()[0] err_count = conn.execute("SELECT COUNT(*) FROM locations WHERE detail_status='error'").fetchone()[0] print(f"Done. DB: {DB_PATH}") print(f"Done. JSON: {JSON_PATH}") print(f"Detail ok={ok_count}, errors={err_count}, total={n_unique}") print(f"Elapsed: {time.time()-t0:.1f}s") if __name__ == "__main__": main()