#!/usr/bin/env python3
"""Convert an Overpass JSON snapshot into HelvPlug's deterministic SQLite database."""

from __future__ import annotations

import argparse
import hashlib
import json
import math
import re
import sqlite3
import sys
from dataclasses import dataclass
from datetime import datetime, timezone
from pathlib import Path
from typing import Any, Iterable


SWISS_BOUNDS = (45.70, 47.90, 5.90, 10.60)
SOURCE_NAME = "OpenStreetMap"
ATTRIBUTION = "© OpenStreetMap contributors"
LICENSE = "Open Database License (ODbL) 1.0"
MAX_AC_POWER_KW = 100.0

CONNECTOR_TAGS = {
    "socket:type1": ("type1", "ac"),
    "socket:type2": ("type2", "ac"),
    "socket:type2_combo": ("ccs", "dc"),
    "socket:ccs": ("ccs", "dc"),
    "socket:chademo": ("chademo", "dc"),
    "socket:tesla_supercharger": ("tesla_supercharger", "dc"),
    "socket:tesla_supercharger_ccs": ("ccs", "dc"),
    "socket:schuko": ("schuko", "ac"),
}


@dataclass(frozen=True)
class NormalizedConnector:
    type: str
    current_type: str
    max_power_kw: float | None
    quantity: int | None


@dataclass(frozen=True)
class NormalizedStation:
    id: str
    name: str
    operator_name: str | None
    latitude: float
    longitude: float
    country_code: str
    access: str | None
    opening_hours: str | None
    connectors: tuple[NormalizedConnector, ...]
    max_power_kw: float | None
    source: str
    source_updated_at: str | None


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--input", required=True, type=Path, help="Overpass JSON snapshot")
    parser.add_argument("--output", required=True, type=Path, help="SQLite output path")
    parser.add_argument("--metadata-output", required=True, type=Path, help="License metadata JSON")
    parser.add_argument("--source-url", required=True, help="Documented snapshot source URL")
    return parser.parse_args()


def fail(message: str) -> None:
    print(f"error: {message}", file=sys.stderr)
    raise SystemExit(1)


def optional_text(value: Any) -> str | None:
    if not isinstance(value, str):
        return None
    stripped = value.strip()
    return stripped or None


def parse_quantity(value: Any) -> int | None:
    if isinstance(value, int) and value > 0:
        return value
    if not isinstance(value, str):
        return None
    stripped = value.strip()
    if stripped.isdigit():
        parsed = int(stripped)
        return parsed if parsed > 0 else None
    return None


def parse_power_kw(value: Any) -> float | None:
    if not isinstance(value, (str, int, float)):
        return None
    text = str(value).lower().replace(",", ".")
    candidates: list[float] = []
    for number, unit in re.findall(r"(?<![\w.])(\d+(?:\.\d+)?)\s*(kw|w)\b", text):
        parsed = float(number)
        if unit == "w":
            parsed /= 1_000
        if 0 < parsed <= 1_000:
            candidates.append(parsed)
    return max(candidates) if candidates else None


def validate_power_for_current(power: float | None, current_type: str) -> float | None:
    if power is None:
        return None
    if current_type == "ac" and power > MAX_AC_POWER_KW:
        return None
    return power


def coordinates(element: dict[str, Any]) -> tuple[float, float] | None:
    latitude = element.get("lat")
    longitude = element.get("lon")
    if latitude is None or longitude is None:
        center = element.get("center") or {}
        latitude = center.get("lat")
        longitude = center.get("lon")
    if not isinstance(latitude, (int, float)) or not isinstance(longitude, (int, float)):
        return None
    if not math.isfinite(latitude) or not math.isfinite(longitude):
        return None
    minimum_latitude, maximum_latitude, minimum_longitude, maximum_longitude = SWISS_BOUNDS
    if not (minimum_latitude <= latitude <= maximum_latitude):
        return None
    if not (minimum_longitude <= longitude <= maximum_longitude):
        return None
    return float(latitude), float(longitude)


def normalize_connectors(tags: dict[str, Any]) -> tuple[NormalizedConnector, ...]:
    merged: dict[tuple[str, str], NormalizedConnector] = {}
    for osm_key, (connector_type, current_type) in CONNECTOR_TAGS.items():
        if osm_key not in tags:
            continue
        quantity = parse_quantity(tags[osm_key])
        raw_value = str(tags[osm_key]).strip().lower()
        if raw_value in {"no", "0", "false"}:
            continue
        power = validate_power_for_current(
            parse_power_kw(tags.get(f"{osm_key}:output")),
            current_type,
        )
        key = (connector_type, current_type)
        existing = merged.get(key)
        if existing is None:
            merged[key] = NormalizedConnector(connector_type, current_type, power, quantity)
            continue
        powers = [candidate for candidate in (existing.max_power_kw, power) if candidate is not None]
        quantities = [candidate for candidate in (existing.quantity, quantity) if candidate is not None]
        merged[key] = NormalizedConnector(
            connector_type,
            current_type,
            max(powers) if powers else None,
            max(quantities) if quantities else None,
        )
    return tuple(sorted(merged.values(), key=lambda item: (item.type, item.current_type)))


def normalize_station(element: dict[str, Any]) -> NormalizedStation | None:
    tags = element.get("tags")
    if not isinstance(tags, dict) or tags.get("amenity") != "charging_station":
        return None
    coordinate = coordinates(element)
    if coordinate is None:
        return None
    element_type = element.get("type")
    element_id = element.get("id")
    if element_type not in {"node", "way", "relation"} or not isinstance(element_id, int):
        return None

    connectors = normalize_connectors(tags)
    explicit_station_power = (
        parse_power_kw(tags.get("max_power"))
        or parse_power_kw(tags.get("charging_station:output"))
    )
    if connectors and all(item.current_type == "ac" for item in connectors):
        explicit_station_power = validate_power_for_current(explicit_station_power, "ac")
    connector_powers = [item.max_power_kw for item in connectors if item.max_power_kw is not None]
    known_powers = connector_powers + ([explicit_station_power] if explicit_station_power else [])
    updated_at = optional_text(element.get("timestamp"))

    return NormalizedStation(
        id=f"osm:{element_type}:{element_id}",
        name=optional_text(tags.get("name")) or "",
        operator_name=optional_text(tags.get("operator")),
        latitude=coordinate[0],
        longitude=coordinate[1],
        country_code="CH",
        access=optional_text(tags.get("access")),
        opening_hours=optional_text(tags.get("opening_hours")),
        connectors=connectors,
        max_power_kw=max(known_powers) if known_powers else None,
        source=SOURCE_NAME,
        source_updated_at=updated_at,
    )


def deduplicate(stations: Iterable[NormalizedStation]) -> list[NormalizedStation]:
    by_id: dict[str, NormalizedStation] = {}
    by_signature: dict[tuple[float, float, str, str], NormalizedStation] = {}
    for station in sorted(stations, key=lambda item: item.id):
        if station.id in by_id:
            continue
        signature = (
            round(station.latitude, 6),
            round(station.longitude, 6),
            station.name.casefold().strip(),
            (station.operator_name or "").casefold().strip(),
        )
        if signature in by_signature:
            continue
        by_id[station.id] = station
        by_signature[signature] = station
    return sorted(by_id.values(), key=lambda item: item.id)


def source_timestamp(document: dict[str, Any], stations: list[NormalizedStation]) -> str | None:
    osm3s = document.get("osm3s")
    if isinstance(osm3s, dict):
        timestamp = optional_text(osm3s.get("timestamp_osm_base"))
        if timestamp:
            return timestamp
    timestamps = [item.source_updated_at for item in stations if item.source_updated_at]
    return max(timestamps) if timestamps else None


def create_database(path: Path, stations: list[NormalizedStation], metadata: dict[str, str]) -> None:
    path.parent.mkdir(parents=True, exist_ok=True)
    if path.exists():
        path.unlink()
    connection = sqlite3.connect(path)
    try:
        connection.executescript(
            """
            PRAGMA journal_mode = DELETE;
            PRAGMA synchronous = FULL;
            PRAGMA foreign_keys = ON;
            CREATE TABLE stations (
                row_id INTEGER PRIMARY KEY,
                id TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL,
                operator_name TEXT,
                latitude REAL NOT NULL,
                longitude REAL NOT NULL,
                country_code TEXT NOT NULL,
                access TEXT,
                opening_hours TEXT,
                max_power_kw REAL,
                source TEXT NOT NULL,
                source_updated_at TEXT
            );
            CREATE VIRTUAL TABLE station_spatial USING rtree(
                row_id,
                minimum_latitude, maximum_latitude,
                minimum_longitude, maximum_longitude
            );
            CREATE TABLE connectors (
                station_row_id INTEGER NOT NULL REFERENCES stations(row_id) ON DELETE CASCADE,
                type TEXT NOT NULL,
                current_type TEXT NOT NULL,
                max_power_kw REAL,
                quantity INTEGER,
                PRIMARY KEY (station_row_id, type, current_type)
            ) WITHOUT ROWID;
            CREATE TABLE metadata (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL
            ) WITHOUT ROWID;
            CREATE INDEX stations_source_id ON stations(id);
            """
        )
        for row_id, station in enumerate(stations, start=1):
            connection.execute(
                """
                INSERT INTO stations VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    row_id,
                    station.id,
                    station.name,
                    station.operator_name,
                    station.latitude,
                    station.longitude,
                    station.country_code,
                    station.access,
                    station.opening_hours,
                    station.max_power_kw,
                    station.source,
                    station.source_updated_at,
                ),
            )
            connection.execute(
                "INSERT INTO station_spatial VALUES (?, ?, ?, ?, ?)",
                (row_id, station.latitude, station.latitude, station.longitude, station.longitude),
            )
            for connector in station.connectors:
                connection.execute(
                    "INSERT INTO connectors VALUES (?, ?, ?, ?, ?)",
                    (
                        row_id,
                        connector.type,
                        connector.current_type,
                        connector.max_power_kw,
                        connector.quantity,
                    ),
                )
        connection.executemany(
            "INSERT INTO metadata(key, value) VALUES (?, ?)",
            sorted(metadata.items()),
        )
        connection.commit()
        integrity = connection.execute("PRAGMA integrity_check").fetchone()
        if not integrity or integrity[0] != "ok":
            fail(f"SQLite integrity check failed: {integrity}")
        connection.execute("VACUUM")
    finally:
        connection.close()


def main() -> None:
    args = parse_args()
    if not args.input.is_file():
        fail(f"input snapshot does not exist: {args.input}")
    raw_bytes = args.input.read_bytes()
    try:
        document = json.loads(raw_bytes)
    except (json.JSONDecodeError, UnicodeDecodeError) as error:
        fail(f"invalid Overpass JSON: {error}")
    elements = document.get("elements")
    if not isinstance(elements, list):
        fail("Overpass JSON has no elements array")

    normalized = [station for item in elements if (station := normalize_station(item)) is not None]
    stations = deduplicate(normalized)
    if not stations:
        fail("snapshot contains no valid Swiss charging stations")

    timestamp = source_timestamp(document, stations)
    snapshot_hash = hashlib.sha256(raw_bytes).hexdigest()
    generated_at = timestamp or datetime.fromtimestamp(0, tz=timezone.utc).isoformat()
    metadata = {
        "attribution": ATTRIBUTION,
        "license": LICENSE,
        "record_count": str(len(stations)),
        "source": SOURCE_NAME,
        "source_sha256": snapshot_hash,
        "source_timestamp": timestamp or "unknown",
        "source_url": args.source_url,
        "generated_at": generated_at,
    }
    create_database(args.output, stations, metadata)

    args.metadata_output.parent.mkdir(parents=True, exist_ok=True)
    args.metadata_output.write_text(
        json.dumps(metadata, indent=2, ensure_ascii=False, sort_keys=True) + "\n",
        encoding="utf-8",
    )
    print(f"Imported {len(stations)} stations into {args.output}")
    print(f"Snapshot SHA-256: {snapshot_hash}")


if __name__ == "__main__":
    main()