"""A user's favourite places for "Pogoda" (AV-062), on migration 012's
place_favorites table in the Journey database -- per owner (the local user
today; per account after AV-054). Plain mutable rows: add, list, remove."""

from __future__ import annotations

from typing import Any

from mountain_twin.journey.persistence import JourneySqliteDatabase, _now, _transaction

KEY_PRECISION = 4  # ~11 m: the same peak from two searches is one favourite
_FIELDS = (
    "name",
    "kind",
    "place_group",
    "latitude",
    "longitude",
    "elevation_m",
    "country",
    "region",
)


def place_key(latitude: float, longitude: float) -> str:
    return f"{round(latitude, KEY_PRECISION):.{KEY_PRECISION}f},{round(longitude, KEY_PRECISION):.{KEY_PRECISION}f}"


class PlaceFavoriteRepository:
    def __init__(self, database: JourneySqliteDatabase):
        self._connection = database.connect()

    def close(self) -> None:
        self._connection.close()

    def list(self, owner_id: str) -> list[dict[str, Any]]:
        rows = self._connection.execute(
            "SELECT * FROM place_favorites WHERE owner_id = ? ORDER BY created_at",
            (owner_id,),
        ).fetchall()
        return [_document(row) for row in rows]

    def add(self, owner_id: str, place: dict[str, Any]) -> dict[str, Any]:
        name = str(place.get("name") or "").strip()
        if not name:
            raise ValueError("name must be non-empty")
        kind = place.get("kind")
        if kind not in ("PEAK", "PLACE"):
            raise ValueError("kind must be PEAK or PLACE")
        latitude, longitude = float(place["latitude"]), float(place["longitude"])
        if not (-90 <= latitude <= 90 and -180 <= longitude <= 180):
            raise ValueError("latitude/longitude out of range")
        elevation = place.get("elevation_m")
        key = place_key(latitude, longitude)
        with _transaction(self._connection):
            self._connection.execute(
                "INSERT INTO place_favorites (owner_id, place_key, name, kind, place_group,"
                " latitude, longitude, elevation_m, country, region, created_at)"
                " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
                " ON CONFLICT(owner_id, place_key) DO UPDATE SET name = excluded.name,"
                " kind = excluded.kind, place_group = excluded.place_group,"
                " elevation_m = excluded.elevation_m, country = excluded.country,"
                " region = excluded.region",
                (
                    owner_id,
                    key,
                    name[:200],
                    kind,
                    str(place.get("group") or "OTHER")[:20],
                    latitude,
                    longitude,
                    None if elevation is None else float(elevation),
                    place.get("country"),
                    place.get("region"),
                    _now(),
                ),
            )
        row = self._connection.execute(
            "SELECT * FROM place_favorites WHERE owner_id = ? AND place_key = ?", (owner_id, key)
        ).fetchone()
        return _document(row)

    def remove(self, owner_id: str, key: str) -> bool:
        with _transaction(self._connection):
            cursor = self._connection.execute(
                "DELETE FROM place_favorites WHERE owner_id = ? AND place_key = ?", (owner_id, key)
            )
        return cursor.rowcount > 0


def _document(row) -> dict[str, Any]:
    document = {field: row[field] for field in _FIELDS}
    document["group"] = document.pop("place_group")
    return {"place_key": row["place_key"], **document, "created_at": row["created_at"]}