"""The image sourcing list behind /images: which images would cover the most comparisons.

Sources of truth: the catalogue itself (products alive after merges, their listings, the
airports that carry them). Three groups the client sources imagery for: alcohol (the liquor
vertical), perfume (the beauty vertical's Perfume category), and cosmetics (the rest of
beauty). A product is *comparable* when two or more airports list it; everything here is
ranked by that count, because a photo on a product nobody can compare buys nothing.

Two units of imagery, because the client may be able to get either:

- **product**: one official photo per product, ranked by airports carried;
- **brand**: one brand image standing in for every product of that brand, ranked by the
  comparable products it would cover.

Reads only; computed on every request, so the list never disagrees with the catalogue.
"""

from __future__ import annotations

from sqlalchemy import case, func, or_, select
from sqlalchemy.orm import Session

from app.models import Listing, Location, Product, ProductLine

GROUPS: dict[str, str] = {"alcohol": "Alcohol", "perfume": "Perfume", "cosmetics": "Cosmetics"}
MODES = ("product", "brand")
RECOMMENDED = 40
MIN_AIRPORTS = 2
PAGE_MAX = 500

_GROUP = case(
    (Product.vertical == "liquor", "alcohol"),
    (Product.category == "Perfume", "perfume"),
    (Product.vertical == "beauty", "cosmetics"),
    else_=None,
).label("grp")
_HAS_IMAGE = (Product.image_url.is_not(None) & (Product.image_url != "")).label("has_image")


def _per_product():
    """One row per alive product with the airports that list it (the base of every read)."""
    return (
        select(
            Product.id.label("id"), _GROUP,
            func.count(func.distinct(Listing.location_id)).label("airports"),
            func.count(func.distinct(case((Location.visible.is_(True), Listing.location_id)))).label("visible"),
        )
        .join(Listing, Listing.product_id == Product.id)
        .join(Location, Location.id == Listing.location_id)
        .where(Product.merged_into_id.is_(None), _GROUP.is_not(None))
        .group_by(Product.id)
        .having(func.count(func.distinct(Listing.location_id)) >= MIN_AIRPORTS)
        .subquery("pa")
    )


def _codes(db: Session, ids: list[int]) -> dict[int, list[str]]:
    if not ids:
        return {}
    rows = db.execute(
        select(Listing.product_id, Location.iata, Location.code)
        .join(Location, Location.id == Listing.location_id)
        .where(Listing.product_id.in_(ids))
        .distinct()
    ).all()
    out: dict[int, set[str]] = {}
    for pid, iata, code in rows:
        out.setdefault(pid, set()).add(iata or code)
    return {pid: sorted(codes) for pid, codes in out.items()}


def _size(p: Product) -> str | None:
    if p.size_value and p.size_unit:
        return f"{p.size_value:g} {p.size_unit}"
    return f"{p.size_ml:g} ml" if p.size_ml else None


def _product_rows(db: Session, stmt, pa) -> list[dict]:
    rows = db.execute(stmt).all()
    codes = _codes(db, [r.Product.id for r in rows])
    out = []
    for r in rows:
        p: Product = r.Product
        out.append({
            "id": p.id, "group": r.grp, "brand": p.brand, "name": p.name, "category": p.category,
            "line": r.line, "size": _size(p), "airports": int(r.airports), "visible_airports": int(r.visible),
            "codes": codes.get(p.id, []),
            "has_image": bool(p.image_url), "image_source": p.image_source if p.image_url else None,
            "gtin": p.gtin,
        })
    return out


def _ranked_products(db: Session, group: str | None, *, limit: int | None, offset: int = 0,
                     brand: str | None = None, q: str | None = None, missing: bool = False) -> tuple[list[dict], int]:
    pa = _per_product()
    base = (
        select(Product, pa.c.grp, pa.c.airports, pa.c.visible, ProductLine.name.label("line"))
        .join(pa, pa.c.id == Product.id)
        .outerjoin(ProductLine, ProductLine.id == Product.line_id)
    )
    if group:
        base = base.where(pa.c.grp == group)
    if brand:
        base = base.where(Product.brand == brand)
    if missing:
        base = base.where(or_(Product.image_url.is_(None), Product.image_url == ""))
    if q:
        needle = f"%{q.strip().lower()}%"
        base = base.where(or_(func.lower(Product.name).like(needle), func.lower(Product.brand).like(needle)))
    total = db.scalar(select(func.count()).select_from(base.subquery())) or 0
    stmt = base.order_by(pa.c.airports.desc(), pa.c.visible.desc(), Product.brand, Product.name).offset(offset)
    if limit:
        stmt = stmt.limit(limit)
    return _product_rows(db, stmt, pa), int(total)


def _ranked_brands(db: Session, group: str | None, *, limit: int | None = None) -> list[dict]:
    """Brands ranked by the comparable products one brand image would stand in for.

    Two bulk reads and a fold in Python: one row per comparable product, and one row per
    (brand, airport) pair; a query per brand was measured at over a minute on the real catalogue.
    """
    pa = _per_product()
    where = [Product.brand.is_not(None), Product.brand != ""]
    if group:
        where.append(pa.c.grp == group)
    rows = db.execute(
        select(pa.c.grp, Product.brand, Product.name, pa.c.airports, _HAS_IMAGE)
        .join(pa, pa.c.id == Product.id).where(*where)
        .order_by(pa.c.airports.desc(), Product.name)
    ).all()
    pairs = db.execute(
        select(pa.c.grp, Product.brand, Listing.location_id)
        .join(pa, pa.c.id == Product.id).join(Listing, Listing.product_id == Product.id).where(*where)
        .distinct()
    ).all()
    airports: dict[tuple[str, str], int] = {}
    for grp, brand, _ in pairs:
        airports[(grp, brand)] = airports.get((grp, brand), 0) + 1
    acc: dict[tuple[str, str], dict] = {}
    for grp, brand, name, n_airports, has_image in rows:
        b = acc.setdefault((grp, brand), {
            "group": grp, "brand": brand, "products": 0, "without_image": 0, "airports": airports.get((grp, brand), 0),
            "best_airports": 0, "listing_airports": 0, "examples": [],
        })
        b["products"] += 1
        b["without_image"] += 0 if has_image else 1
        b["best_airports"] = max(b["best_airports"], int(n_airports))
        b["listing_airports"] += int(n_airports)
        if len(b["examples"]) < 3:
            b["examples"].append(name)
    out = sorted(acc.values(), key=lambda b: (-b["products"], -b["listing_airports"], b["brand"]))
    for b in out:
        del b["listing_airports"]
    return out[:limit] if limit else out


def recommended(db: Session, mode: str = "product", limit: int = RECOMMENDED) -> dict:
    """The top `limit` images per group, in the unit the client can source."""
    groups = []
    for key, label in GROUPS.items():
        if mode == "brand":
            rows = _ranked_brands(db, key, limit=limit)
            covered = sum(r["products"] for r in rows)
            groups.append({"key": key, "label": label, "rows": rows, "covers_products": covered})
        else:
            rows, total = _ranked_products(db, key, limit=limit)
            groups.append({"key": key, "label": label, "rows": rows, "comparable": total,
                           "without_image": sum(1 for r in rows if not r["has_image"])})
    return {"mode": mode, "limit": limit, "groups": groups, "summary": summary(db)}


def summary(db: Session) -> dict:
    pa = _per_product()
    rows = db.execute(
        select(pa.c.grp, func.count(Product.id), func.sum(case((_HAS_IMAGE, 1), else_=0)))
        .join(Product, Product.id == pa.c.id).group_by(pa.c.grp)
    ).all()
    by = {grp: {"comparable": int(n), "with_image": int(w or 0)} for grp, n, w in rows}
    return {key: by.get(key, {"comparable": 0, "with_image": 0}) for key in GROUPS}


def products(db: Session, *, group: str | None, brand: str | None, q: str | None, missing: bool,
             page: int, per_page: int) -> dict:
    rows, total = _ranked_products(db, group, limit=per_page, offset=(page - 1) * per_page,
                                   brand=brand, q=q, missing=missing)
    for i, row in enumerate(rows, start=(page - 1) * per_page + 1):
        row["rank"] = i
    return {"rows": rows, "total": total, "page": page, "per_page": per_page}


def brands(db: Session, *, group: str | None) -> dict:
    rows = _ranked_brands(db, group)
    for i, row in enumerate(rows, start=1):
        row["rank"] = i
    return {"rows": rows, "total": len(rows)}