# -----------------------------------------------------------------------------
# Auto-Ka — Agrégateur de voitures usagées à vendre (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# vindecode.py : fiche technique constructeur via le VIN — API vPIC de la NHTSA
#                (gratuite, sans clé), décodage paresseux + cache SQLite.
# -----------------------------------------------------------------------------
from __future__ import annotations

import json
import sqlite3
import time

import requests

VPIC_URL = "https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVINValuesBatch/"
TIMEOUT = 12

# Champs vPIC retenus → libellé français affiché sur la fiche.
# L'ordre du dict = l'ordre d'affichage.
_FIELDS: dict[str, str] = {
    "Trim": "Version (usine)",
    "Series": "Série",
    "BodyClass": "Carrosserie (usine)",
    "DriveType": "Motricité (usine)",
    "EngineCylinders": "Cylindres",
    "DisplacementL": "Cylindrée (L)",
    "EngineHP": "Puissance (ch)",
    "EngineConfiguration": "Configuration moteur",
    "FuelTypePrimary": "Carburant (usine)",
    "FuelTypeSecondary": "Carburant secondaire",
    "ElectrificationLevel": "Électrification",
    "BatteryKWh": "Batterie (kWh)",
    "TransmissionStyle": "Transmission (usine)",
    "TransmissionSpeeds": "Rapports",
    "Turbo": "Turbo",
    "Doors": "Portes (usine)",
    "Seats": "Places (usine)",
    "GVWR": "PNBV",
    "PlantCountry": "Pays d'assemblage",
    "PlantCity": "Ville d'assemblage",
    "ABS": "Freins ABS",
    "AirBagLocFront": "Coussins avant",
    "AirBagLocSide": "Coussins latéraux",
    "AirBagLocCurtain": "Coussins rideaux",
    "TPMS": "Surveillance pneus (TPMS)",
    "AdaptiveCruiseControl": "Régulateur adaptatif",
    "ForwardCollisionWarning": "Alerte de collision avant",
    "LaneDepartureWarning": "Alerte de sortie de voie",
    "BlindSpotMon": "Surveillance angles morts",
    "RearVisibilitySystem": "Caméra de recul",
}

# Traductions des valeurs vPIC les plus fréquentes (anglais → français).
_VALUES_FR: dict[str, str] = {
    "Gasoline": "Essence", "Diesel": "Diesel", "Electric": "Électrique",
    "Sedan/Saloon": "Berline", "Hatchback/Liftback/Notchback": "Hayon",
    "Sport Utility Vehicle (SUV)/Multi-Purpose Vehicle (MPV)": "VUS",
    "Sport Utility Vehicle [SUV]/Multipurpose Vehicle [MPV]": "VUS",
    "Crossover Utility Vehicle (CUV)": "VUS multisegment",
    "Pickup": "Camionnette", "Minivan": "Minifourgonnette",
    "Coupe": "Coupé", "Convertible/Cabriolet": "Cabriolet", "Wagon": "Familiale",
    "4WD/4-Wheel Drive/4x4": "4x4", "AWD/All-Wheel Drive": "Intégrale (AWD)",
    "FWD/Front-Wheel Drive": "Traction (FWD)",
    "RWD/Rear-Wheel Drive": "Propulsion (RWD)",
    "Automatic": "Automatique", "Manual/Standard": "Manuelle",
    "Continuously Variable Transmission (CVT)": "CVT",
    "Automated Manual Transmission (AMT)": "Manuelle automatisée",
    "Dual-Clutch Transmission (DCT)": "Double embrayage (DCT)",
    "Strong HEV (Hybrid Electric Vehicle)": "Hybride",
    "Mild HEV (Hybrid Electric Vehicle)": "Hybride légère",
    "PHEV (Plug-in Hybrid Electric Vehicle)": "Hybride rechargeable",
    "BEV (Battery Electric Vehicle)": "100 % électrique",
    "Standard": "De série", "Optional": "En option", "Yes": "Oui", "No": "Non",
    "Direct": "Directe (TPMS direct)", "Indirect": "Indirecte",
    "1st Row (Driver and Passenger)": "1re rangée (conducteur et passager)",
    "1st and 2nd Rows": "1re et 2e rangées",
    "1st, 2nd and 3rd Rows": "1re, 2e et 3e rangées",
    "All Rows": "Toutes les rangées",
    "UNITED STATES (USA)": "États-Unis", "CANADA": "Canada", "MEXICO": "Mexique",
    "JAPAN": "Japon", "GERMANY": "Allemagne", "SOUTH KOREA": "Corée du Sud",
    "V-Shaped": "En V", "In-Line": "En ligne", "Horizontally opposed (boxer)": "Boxer",
}

_SCHEMA = """
CREATE TABLE IF NOT EXISTS vin_decode (
    vin        TEXT PRIMARY KEY,
    data       TEXT,      -- JSON {libellé fr: valeur} (vide = décodage sans résultat)
    fetched_at REAL
);
"""


def _ensure(con: sqlite3.Connection) -> None:
    con.executescript(_SCHEMA)


def _fr(value: str) -> str:
    return _VALUES_FR.get(value, value)


def _extract(raw: dict) -> dict[str, str]:
    out: dict[str, str] = {}
    for key, label in _FIELDS.items():
        val = (raw.get(key) or "").strip()
        if not val or val.lower() in ("not applicable", "n/a", "0"):
            continue
        out[label] = _fr(val)
    return out


def decode(con: sqlite3.Connection, vin: str) -> dict[str, str] | None:
    """Fiche constructeur d'un VIN (cache DB, appel réseau au premier accès).

    Retourne un dict {libellé fr: valeur} ou None si indisponible.
    """
    vin = (vin or "").strip().upper()
    if len(vin) != 17:
        return None
    _ensure(con)
    row = con.execute("SELECT data FROM vin_decode WHERE vin=?", (vin,)).fetchone()
    if row is not None:
        try:
            data = json.loads(row["data"] or "{}")
        except ValueError:
            data = {}
        return data or None
    try:
        resp = requests.post(VPIC_URL, data={"format": "json", "data": vin},
                             timeout=TIMEOUT)
        resp.raise_for_status()
        results = resp.json().get("Results") or []
        data = _extract(results[0]) if results else {}
    except Exception:
        # échec réseau : ne pas mettre en cache, on retentera au prochain accès
        return None
    con.execute(
        "INSERT OR REPLACE INTO vin_decode (vin, data, fetched_at) VALUES (?,?,?)",
        (vin, json.dumps(data, ensure_ascii=False), time.time()))
    con.commit()
    return data or None