"""The airport_hours rows: writing a reading, and which row an airport shows.

Hand beats collected: a human value is never overwritten by a machine, so while a hand row exists
a later collected row is stored (the history stays complete) and not shown. Within a kind the
newest `observed_at` wins. Nothing here deletes.
"""

from datetime import datetime

from sqlalchemy import select
from sqlalchemy.orm import Session

from app.models.catalog import Account, Location
from app.models.hours import SOURCE_KINDS, AirportHours


def location_ids_for(db: Session, iata: str) -> list[int]:
    """Every shop row at the airport, by id; the first is where a reading is written."""
    return list(db.scalars(select(Location.id).where(Location.iata == iata.upper()).order_by(Location.id)))


def record(
    db: Session,
    *,
    location_id: int,
    source_kind: str,
    text: str,
    observed_at: datetime,
    source_url: str | None = None,
    entered_by_id: int | None = None,
    detail: dict | None = None,
) -> AirportHours:
    """Append one reading. Never updates a row: the previous reading stays as history."""
    if source_kind not in SOURCE_KINDS:
        raise ValueError(f"source_kind must be one of {SOURCE_KINDS}, not {source_kind!r}")
    if not text.strip():
        raise ValueError("hours text is empty; empty beats guessed, so nothing is written")
    row = AirportHours(
        location_id=location_id,
        source_kind=source_kind,
        text=text.strip(),
        observed_at=observed_at,
        source_url=source_url,
        entered_by_id=entered_by_id,
        detail=detail,
    )
    db.add(row)
    db.flush()
    return row


def newest(db: Session, location_ids: list[int], source_kind: str) -> AirportHours | None:
    if not location_ids:
        return None
    return db.scalar(
        select(AirportHours)
        .where(AirportHours.location_id.in_(location_ids), AirportHours.source_kind == source_kind)
        .order_by(AirportHours.observed_at.desc(), AirportHours.id.desc())
        .limit(1)
    )


def current(db: Session, location_ids: list[int]) -> AirportHours | None:
    """The row the page shows: the newest hand row, else the newest collected row, else None."""
    return newest(db, location_ids, "hand") or newest(db, location_ids, "collected")


def current_for_airport(db: Session, iata: str) -> AirportHours | None:
    return current(db, location_ids_for(db, iata))


def account_by_name(db: Session, name: str | None) -> Account | None:
    """The account a hand row is written against: by username first, else by display name."""
    if not name or not name.strip():
        return None
    wanted = name.strip()
    row = db.scalar(select(Account).where(Account.username == wanted.lower()))
    if row is None:
        row = db.scalar(select(Account).where(Account.display_name == wanted).order_by(Account.id).limit(1))
    return row


def entered_by_username(db: Session, row: AirportHours) -> str | None:
    """The account behind a hand row, by username (the mention handle), else its display name."""
    if row.entered_by_id is None:
        return None
    account = db.get(Account, row.entered_by_id)
    if account is None:
        return None
    return account.username or account.display_name