from typing import Optional

from fastapi import FastAPI, Query

from .db import get_connection

app = FastAPI(title="Excursions Avi API", version="0.1.0")


@app.get("/health")
def health() -> dict:
    return {"status": "ok"}


@app.get("/excursions")
def list_excursions(
    q: Optional[str] = Query(default=None, description="Search by title"),
    difficulty: Optional[str] = Query(default=None),
    min_distance_km: Optional[float] = Query(default=None),
    max_distance_km: Optional[float] = Query(default=None),
) -> list[dict]:
    where = []
    params = []

    if q:
        where.append("title LIKE %s")
        params.append(f"%{q}%")

    if difficulty:
        where.append("difficulty = %s")
        params.append(difficulty)

    if min_distance_km is not None:
        where.append("distance_km >= %s")
        params.append(min_distance_km)

    if max_distance_km is not None:
        where.append("distance_km <= %s")
        params.append(max_distance_km)

    sql = """
    SELECT id, title, excursion_date, zone, distance_km, elevation_gain_m, duration_minutes, difficulty, summary
    FROM excursions
    """

    if where:
        sql += " WHERE " + " AND ".join(where)

    sql += " ORDER BY excursion_date DESC, id DESC LIMIT 200"

    with get_connection() as conn:
        with conn.cursor(dictionary=True) as cursor:
            cursor.execute(sql, params)
            rows = cursor.fetchall()

    return rows