# -----------------------------------------------------------------------------
# Lou-Ka — Agrégateur de logements à louer (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# gaz.py : stations-service à proximité et prix de l'essence — gazquebec.ca
#
#   gazquebec.ca expose un GeoJSON public de ~2 450 stations du Québec avec
#   les prix courants par carburant (Régulier / Super / Diesel). On garde une
#   copie locale (data/gaz.db) rafraîchie automatiquement au plus toutes les
#   4 h ; `nearby(lat, lng)` retourne les stations les plus proches avec
#   leurs prix et la médiane du rayon pour situer chaque prix.
# -----------------------------------------------------------------------------
from __future__ import annotations

import json
import math
import sqlite3
import time
import urllib.request
from pathlib import Path
from statistics import median

DB_PATH = Path(__file__).resolve().parent.parent / "data" / "gaz.db"
API = "https://gazquebec.ca/api/stations"
UA = "LouKaBot/1.0 (+https://www.lou-ka.com; contact@spboucher.ai)"
TTL = 4 * 3600                      # fraîcheur maximale de la copie locale


def _connect() -> sqlite3.Connection:
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    con = sqlite3.connect(DB_PATH, timeout=15)
    con.row_factory = sqlite3.Row
    con.executescript("""
        CREATE TABLE IF NOT EXISTS gaz_stations (
            id INTEGER PRIMARY KEY, nom TEXT, banniere TEXT, adresse TEXT,
            region TEXT, lat REAL, lng REAL,
            prix_regulier REAL, prix_super REAL, prix_diesel REAL);
        CREATE INDEX IF NOT EXISTS idx_gaz_latlng ON gaz_stations (lat, lng);
        CREATE TABLE IF NOT EXISTS meta (k TEXT PRIMARY KEY, v);
    """)
    return con


def refresh(con: sqlite3.Connection | None = None) -> int:
    """Télécharge le GeoJSON des stations et remplace la copie locale."""
    own = con is None
    con = con or _connect()
    req = urllib.request.Request(API, headers={"User-Agent": UA})
    with urllib.request.urlopen(req, timeout=60) as r:
        data = json.load(r)
    rows = []
    for f in data.get("features", []):
        try:
            lng, lat = f["geometry"]["coordinates"][:2]
        except (KeyError, TypeError, ValueError):
            continue
        p = f.get("properties") or {}
        prix = {"Régulier": None, "Super": None, "Diesel": None}
        try:
            for e in json.loads(p.get("allPrices") or "[]"):
                if e.get("fuelType") in prix and e.get("priceCents"):
                    prix[e["fuelType"]] = e["priceCents"] / 10.0   # ¢/L
        except ValueError:
            pass
        rows.append((p.get("id"), p.get("name"), p.get("brand"),
                     p.get("address"), p.get("region"), lat, lng,
                     prix["Régulier"], prix["Super"], prix["Diesel"]))
    with con:
        con.execute("DELETE FROM gaz_stations")
        con.executemany(
            "INSERT OR REPLACE INTO gaz_stations VALUES (?,?,?,?,?,?,?,?,?,?)",
            rows)
        con.execute("INSERT OR REPLACE INTO meta VALUES ('maj', ?)",
                    (time.time(),))
    if own:
        con.close()
    return len(rows)


def _dist_m(lat1, lng1, lat2, lng2) -> float:
    dlat = math.radians(lat2 - lat1)
    dlng = math.radians(lng2 - lng1)
    a = (math.sin(dlat / 2) ** 2 + math.cos(math.radians(lat1))
         * math.cos(math.radians(lat2)) * math.sin(dlng / 2) ** 2)
    return 6371000 * 2 * math.asin(math.sqrt(a))


def nearby(lat: float, lng: float, radius_m: int = 5000,
           limit: int = 5) -> dict:
    """Stations les plus proches + médiane du prix Régulier dans le rayon."""
    con = _connect()
    row = con.execute("SELECT v FROM meta WHERE k='maj'").fetchone()
    if row is None or time.time() - float(row["v"]) > TTL:
        try:
            refresh(con)
        except Exception:
            pass                     # copie périmée mieux que rien
    d = radius_m / 111320.0
    dl = d / max(0.2, math.cos(math.radians(lat)))
    rows = con.execute(
        "SELECT * FROM gaz_stations WHERE lat BETWEEN ? AND ? "
        "AND lng BETWEEN ? AND ?",
        (lat - d, lat + d, lng - dl, lng + dl)).fetchall()
    maj = con.execute("SELECT v FROM meta WHERE k='maj'").fetchone()
    con.close()
    hits = []
    for r in rows:
        dist = _dist_m(lat, lng, r["lat"], r["lng"])
        if dist <= radius_m:
            hits.append((dist, r))
    hits.sort(key=lambda t: t[0])
    regs = [r["prix_regulier"] for _, r in hits if r["prix_regulier"]]
    med = round(median(regs), 1) if len(regs) >= 3 else None
    mini = round(min(regs), 1) if regs else None
    items = [{"nom": r["banniere"] or r["nom"], "adresse": r["adresse"],
              "dist_m": round(dist),
              # position (2026-09-04) : filtre « Essence » de la carte de la fiche
              "lat": r["lat"], "lng": r["lng"],
              "regulier": r["prix_regulier"], "super": r["prix_super"],
              "diesel": r["prix_diesel"],
              "moins_chere": bool(r["prix_regulier"] and mini
                                  and r["prix_regulier"] <= mini)}
             for dist, r in hits[:limit]]
    return {"n": len(hits), "rayon_m": radius_m, "mediane_regulier": med,
            "min_regulier": mini, "stations": items,
            "maj": time.strftime("%Y-%m-%d %H:%M",
                                 time.localtime(float(maj["v"])))
                   if maj else None}