"""Savings across the shopper's airports.

The question the client's brief actually asked: flying JFK to Heathrow, is it
cheaper to buy at departure or on arrival? We answer a slightly broader version
of it, because a traveller knows which shops they will walk past but not which
leg they will be on when they decide to buy: given a *set* of airports, return
the products stocked at more than one of them, ordered by how much the choice
is worth.
"""

from sqlalchemy import Select, distinct, func, or_, select
from sqlalchemy.orm import Session

from app.models import Award, Listing, Shop, PriceObservation, ProductVariant, Retailer
from app.models.schemas import TripPrice, TripProduct, TripResult, TripStop
from app.services.catalog_queries import (
    _latest_observation_subquery,
    _like_pattern,
    blocked_shop_ids,
    publishable,
)


def _shared_pairs(db: Session, limit: int) -> Select:
    """Pairs of airport shops the site shows, ordered by how many products they share.

    The two shops are aliases of one table, so `publishable(db)` cannot be
    joined in as it is elsewhere; the same rule is applied to each alias by
    hand: visible, and not one of the shops verify has blocked. Without the
    second half a blocked shop's prices stayed on /trip and in the suggested
    routes while every other page had hidden them.
    """
    a = Shop.__table__.alias("a")
    b = Shop.__table__.alias("b")
    la = Listing.__table__.alias("la")
    lb = Listing.__table__.alias("lb")
    stmt = (
        select(a.c.code, b.c.code, func.count().label("shared"))
        .select_from(
            la.join(a, a.c.id == la.c.shop_id)
            .join(lb, lb.c.variant_id == la.c.variant_id)
            .join(b, (b.c.id == lb.c.shop_id) & (b.c.code > a.c.code))
        )
        .where(
            a.c.is_catalogue_only.is_(False),
            b.c.is_catalogue_only.is_(False),
            a.c.visible.is_(True),
            b.c.visible.is_(True),
        )
        .group_by(a.c.code, b.c.code)
        .order_by(func.count().desc())
        .limit(limit)
    )
    blocked = blocked_shop_ids(db)
    if blocked:
        stmt = stmt.where(a.c.id.not_in(blocked), b.c.id.not_in(blocked))
    return stmt


def best_route(db: Session) -> list[str]:
    """The pair of real airports that currently share the most products.

    Data-driven so the home page always shows a live, populated comparison
    without hardcoding airport codes that might have no overlap.
    """
    row = db.execute(_shared_pairs(db, 1)).first()
    return [row[0], row[1]] if row else []


def suggested_routes(db: Session, limit: int = 4) -> list[list[str]]:
    """Airport pairs that actually share products, best first.

    Without this a visitor has to guess which combinations return anything,
    and most do not — the shops that publish barcodes are the ones that match.
    """
    rows = db.execute(_shared_pairs(db, limit)).all()
    return [[row[0], row[1]] for row in rows]


def available_stops(db: Session) -> list[TripStop]:
    """Airports we can actually compare, for the airport picker.

    A configured store we hold nothing for -- one whose site we cannot read yet
    -- must not appear here. Offering it means a shopper picks it, gets nothing
    back, and concludes the site is broken rather than that the shop is closed
    to us.
    """
    rows = db.scalars(
        select(Shop)
        .where(
            Shop.is_catalogue_only.is_(False),
            publishable(db),
            Shop.id.in_(select(Listing.shop_id).distinct()),
        )
        .order_by(Shop.name)
    )
    return [
        TripStop(code=r.code, iata=r.iata, name=r.name, city=r.city, country=r.country)
        for r in rows
    ]


def compare_trip(
    db: Session,
    codes: list[str],
    *,
    category: str | None = None,
    awarded_only: bool = False,
    exclusives_only: bool = False,
    q: str | None = None,
    min_saving: float = 0.0,
    sort: str = "saving",
    limit: int = 24,
) -> TripResult:
    # A blocked shop is not a stop: dropped here, it is absent from the stops
    # the result names and from every price below (build plan Decision 10).
    shops = list(
        db.scalars(select(Shop).where(Shop.code.in_(codes), publishable(db)))
    )
    stops = [
        TripStop(code=l.code, iata=l.iata, name=l.name, city=l.city, country=l.country)
        for l in shops
    ]
    if len(shops) < 2:
        return TripResult(stops=stops, compared=0, items=[])

    shop_ids = [l.id for l in shops]

    # Products stocked at more than one stop on this route, fetched once. As a
    # subquery inside the main statement the planner (misled by the latest-price
    # filter's row estimate) re-ran it once per price row, 978 times for DUB+LHR.
    candidates = list(
        db.scalars(
            select(Listing.variant_id)
            .where(Listing.shop_id.in_(shop_ids))
            .group_by(Listing.variant_id)
            .having(func.count(distinct(Listing.shop_id)) > 1)
        )
    )
    if not candidates:
        return TripResult(stops=stops, compared=0, items=[])
    # Only those products' observations at the route's shops are ranked: the
    # whole catalogue's cost a two-airport route 0.7 to 2.3 s on staging (11 Sep).
    latest = _latest_observation_subquery(variant_ids=candidates, shop_ids=shop_ids)

    stmt = (
        select(
            ProductVariant,
            PriceObservation,
            Shop,
            Retailer,
        )
        .join(Listing, Listing.variant_id == ProductVariant.id)
        .join(PriceObservation, PriceObservation.listing_id == Listing.id)
        .join(Shop, Listing.shop_id == Shop.id)
        .join(Retailer, Shop.retailer_id == Retailer.id)
        .where(
            ProductVariant.id.in_(candidates),
            Listing.shop_id.in_(shop_ids),
            PriceObservation.id.in_(select(latest.c.obs_id)),
            PriceObservation.price_usd.isnot(None),
        )
    )
    if category:
        stmt = stmt.where(ProductVariant.category == category)
    if awarded_only:
        stmt = stmt.where(ProductVariant.id.in_(select(Award.variant_id)))
    if exclusives_only:
        stmt = stmt.where(ProductVariant.is_exclusive.is_(True))
    if q:
        needle = func.unaccent(_like_pattern(q))
        stmt = stmt.where(
            or_(
                func.unaccent(ProductVariant.name).ilike(needle, escape="\\"),
                func.unaccent(func.coalesce(ProductVariant.brand, "")).ilike(needle, escape="\\"),
            )
        )

    grouped: dict[int, tuple[ProductVariant, list[TripPrice]]] = {}
    for product, obs, shop, retailer in db.execute(stmt).all():
        entry = grouped.setdefault(product.id, (product, []))
        entry[1].append(
            TripPrice(
                in_stock=obs.in_stock,
                shop_name=shop.name,
                shop_iata=shop.iata,
                retailer_name=retailer.name,
                price_usd=float(obs.price_usd),
                currency=obs.currency,
                price=float(obs.price),
            )
        )

    award_rows = db.execute(
        select(Award.variant_id, func.count(Award.id))
        .where(Award.variant_id.in_(grouped))
        .group_by(Award.variant_id)
    ).all()
    awards = dict(award_rows)

    items: list[TripProduct] = []
    for product, prices in grouped.values():
        if len(prices) < 2:
            continue
        # Best must be somewhere the bottle is actually on the shelf; a
        # sold-out shop can still be the "worst" reference price.
        # Equal prices break on the shop, so which one is "best" never depends
        # on the order the database returned the rows in (it changed with the
        # query's plan, 11 Sep).
        by_price = sorted(prices, key=lambda p: (p.price_usd, p.shop_name, p.retailer_name))
        buyable = [p for p in by_price if p.in_stock is not False]
        best, worst = (buyable or by_price)[0], by_price[-1]
        items.append(
            TripProduct(
                id=product.id,
                name=product.name,
                brand=product.brand,
                category=product.category,
                quantity_ml=product.quantity_ml,
                thumb_url=product.thumb_url,
                is_exclusive=bool(product.is_exclusive),
                award_count=awards.get(product.id, 0),
                best=best,
                worst=worst,
                saving_usd=round(worst.price_usd - best.price_usd, 2),
            )
        )

    if min_saving > 0:
        items = [i for i in items if i.saving_usd >= min_saving]

    # "saving" is the default because the size of the decision is the whole
    # point; the others exist for a shopper who already knows what they want.
    # Every order ends on the product id: two shops of one retailer price many
    # bottles alike, and the equal savings at the cut must fall the same way on
    # every request.
    if sort == "cheapest":
        items.sort(key=lambda i: (i.best.price_usd, i.id))
    elif sort == "dearest":
        items.sort(key=lambda i: (-i.best.price_usd, i.id))
    elif sort == "percent":
        items.sort(key=lambda i: (-((i.saving_usd / i.worst.price_usd) if i.worst.price_usd else 0), i.id))
    elif sort == "name":
        items.sort(key=lambda i: (i.name.lower(), i.id))
    else:
        items.sort(key=lambda i: (-i.saving_usd, i.id))
    return TripResult(stops=stops, compared=len(items), items=items[:limit])