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

Sources of truth: the catalogue itself (product variants alive after merges, their listings, the
airports that carry them, and the pictures at three levels). 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 variant is *comparable* when two or more airports list
it; everything here is ranked by that count, because a photo on a product variant nobody can
compare buys nothing.

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

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

A product variant *has an image* when a bottle shows for it: its own picture or its product
line's (the same rule structured data follows, `imagery.STRUCTURED_DATA_LEVELS`). A brand mark
shown in the bottle's place is a fallback, so the row still counts as without an image; what the
card shows, at which level and with what credit, rides on the row beside the count. `coverage()`
is the per-level count behind the stat cards and `app.cli images coverage`; `wanted()` is the
short list per level with no picture anywhere in its cascade, the ones to chase first.

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

from __future__ import annotations

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

from app.models import Brand, Listing, Shop, ProductVariant, ProductLine
from app.services import imagery

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

_GROUP = case(
    (ProductVariant.vertical == "liquor", "alcohol"),
    (ProductVariant.category == "Perfume", "perfume"),
    (ProductVariant.vertical == "beauty", "cosmetics"),
    else_=None,
).label("grp")


def _own(model):
    """The row holds a picture of its own."""
    return model.image_url.is_not(None) & (model.image_url != "")


def _admin(model):
    return _own(model) & model.image_source.like(imagery.ADMIN_PREFIX + "%")


def _public(model):
    return _own(model) & model.image_source.like(imagery.PUBLIC_PREFIX + "%")


def _flag(condition):
    """1 when the condition holds, else 0: a boolean an aggregate can carry through a GROUP BY on
    both Postgres and SQLite."""
    return func.max(case((condition, 1), else_=0))


def _per_product():
    """One row per alive product variant with the airports that list it (the base of every read),
    and whether a bottle shows for it (`has_image`: its own picture or its product line's) or
    anything at all does (`any_image`: those, or its brand's mark)."""
    return (
        select(
            ProductVariant.id.label("id"), _GROUP,
            func.count(func.distinct(Listing.shop_id)).label("airports"),
            func.count(func.distinct(case((Shop.visible.is_(True), Listing.shop_id)))).label("visible"),
            _flag(_own(ProductVariant) | _own(ProductLine)).label("has_image"),
            _flag(_own(ProductVariant) | _own(ProductLine) | _own(Brand)).label("any_image"),
        )
        .join(Listing, Listing.variant_id == ProductVariant.id)
        .join(Shop, Shop.id == Listing.shop_id)
        .outerjoin(ProductLine, ProductLine.id == ProductVariant.product_line_id)
        .outerjoin(Brand, Brand.id == func.coalesce(ProductVariant.brand_id, ProductLine.brand_id))
        .where(ProductVariant.merged_into_id.is_(None), _GROUP.is_not(None))
        .group_by(ProductVariant.id)
        .having(func.count(func.distinct(Listing.shop_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.variant_id, Shop.iata, Shop.code)
        .join(Shop, Shop.id == Listing.shop_id)
        .where(Listing.variant_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: ProductVariant) -> str | None:
    if p.quantity_stated_value and p.quantity_stated_unit:
        return f"{p.quantity_stated_value:g} {p.quantity_stated_unit}"
    return f"{p.quantity_ml:g} ml" if p.quantity_ml else None


def _product_rows(db: Session, stmt, pa) -> list[dict]:
    from app.services import catalog_queries  # lazy: the catalogue reads import widely

    rows = db.execute(stmt).all()
    codes = _codes(db, [r.ProductVariant.id for r in rows])
    pictures = catalog_queries.pictures_of(db, [r.ProductVariant for r in rows])
    out = []
    for r in rows:
        p: ProductVariant = r.ProductVariant
        picture = pictures.get(p.id)
        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(r.has_image),
            "image_source": picture.source if picture else None,
            "image_level": picture.level if picture else None,
            "image_credit": picture.credit if picture else None,
            "gtin": p.gtin,
        })
    return out


def _ranked_product_variants(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(ProductVariant, pa.c.grp, pa.c.airports, pa.c.visible, pa.c.has_image, ProductLine.name.label("line"))
        .join(pa, pa.c.id == ProductVariant.id)
        .outerjoin(ProductLine, ProductLine.id == ProductVariant.product_line_id)
    )
    if group:
        base = base.where(pa.c.grp == group)
    if brand:
        base = base.where(ProductVariant.brand == brand)
    if missing:
        base = base.where(pa.c.has_image == 0)
    if q:
        needle = f"%{q.strip().lower()}%"
        base = base.where(or_(func.lower(ProductVariant.name).like(needle), func.lower(ProductVariant.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(), ProductVariant.brand, ProductVariant.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 product variants one brand image would stand in for.

    Two bulk reads and a fold in Python: one row per comparable product variant, and one row per
    (brand, airport) pair; a query per brand was measured at over a minute on the real catalogue.
    A third read, by id set, says whether the brand row holds a mark already and from where.
    """
    from app.services import catalog_queries  # lazy: the catalogue reads import widely

    pa = _per_product()
    where = [ProductVariant.brand.is_not(None), ProductVariant.brand != ""]
    if group:
        where.append(pa.c.grp == group)
    rows = db.execute(
        select(pa.c.grp, ProductVariant.brand, ProductVariant.name, pa.c.airports, pa.c.has_image, ProductVariant.brand_id)
        .join(pa, pa.c.id == ProductVariant.id).where(*where)
        .order_by(pa.c.airports.desc(), ProductVariant.name)
    ).all()
    pairs = db.execute(
        select(pa.c.grp, ProductVariant.brand, Listing.shop_id)
        .join(pa, pa.c.id == ProductVariant.id).join(Listing, Listing.variant_id == ProductVariant.id).where(*where)
        .distinct()
    ).all()
    brand_rows = catalog_queries._fold_rows(db, Brand, {r.brand_id for r in rows})
    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, brand_id in rows:
        b = acc.setdefault((grp, brand), {
            "group": grp, "brand": brand, "product_variants": 0, "without_image": 0, "airports": airports.get((grp, brand), 0),
            "best_airports": 0, "listing_airports": 0, "examples": [],
            "has_image": False, "image_source": None,
        })
        b["product_variants"] += 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)
        mark = brand_rows.get(brand_id)
        if not b["has_image"] and mark is not None and mark.image_url:
            b["has_image"], b["image_source"] = True, mark.image_source
    out = sorted(acc.values(), key=lambda b: (-b["product_variants"], -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["product_variants"] for r in rows)
            groups.append({"key": key, "label": label, "rows": rows, "covers_product_variants": covered})
        else:
            rows, total = _ranked_product_variants(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:
    """Per group, the comparable product variants and how many show a bottle; and under
    `levels`, the per-level coverage of the whole catalogue (`coverage`)."""
    pa = _per_product()
    rows = db.execute(
        select(pa.c.grp, func.count(ProductVariant.id), func.sum(pa.c.has_image))
        .join(ProductVariant, ProductVariant.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}
    out: dict = {key: by.get(key, {"comparable": 0, "with_image": 0}) for key in GROUPS}
    out["levels"] = coverage(db)
    return out


# --- coverage: the per-level count -------------------------------------------------------------

def coverage(db: Session) -> dict[str, dict[str, int]]:
    """Per picture level, the canonical rows (a brand or product line that is no alias, a product
    variant not merged away, hidden ones included: coverage is of the catalogue, not the site):
    `total`, with a supplied picture (`admin`), with a fetched one (`public`), `without` one of
    their own; then what the cascade shows for the rows without: a product variant's `from_line`
    and `from_brand`, a product line's `from_brand`, and `none` at any level. The one
    implementation behind the stat cards and `app.cli images coverage`. An alias is not followed
    here (a fold re-points its rows), so the count is a straight join."""
    def counts(stmt) -> list[int]:
        return [int(v or 0) for v in db.execute(stmt).one()]

    total, admin, public = counts(
        select(func.count(Brand.id), func.sum(case((_admin(Brand), 1), else_=0)), func.sum(case((_public(Brand), 1), else_=0)))
        .where(Brand.alias_of_id.is_(None))
    )
    out = {"brand": {"total": total, "admin": admin, "public": public, "without": total - admin - public}}
    out["brand"]["none"] = out["brand"]["without"]

    total, admin, public, from_brand = counts(
        select(
            func.count(ProductLine.id),
            func.sum(case((_admin(ProductLine), 1), else_=0)),
            func.sum(case((_public(ProductLine), 1), else_=0)),
            func.sum(case((and_(~_own(ProductLine), _own(Brand)), 1), else_=0)),
        )
        .outerjoin(Brand, Brand.id == ProductLine.brand_id)
        .where(ProductLine.alias_of_id.is_(None))
    )
    without = total - admin - public
    out["line"] = {"total": total, "admin": admin, "public": public, "without": without,
                   "from_brand": from_brand, "none": without - from_brand}

    total, admin, public, from_line, from_brand = counts(
        select(
            func.count(ProductVariant.id),
            func.sum(case((_admin(ProductVariant), 1), else_=0)),
            func.sum(case((_public(ProductVariant), 1), else_=0)),
            func.sum(case((and_(~_own(ProductVariant), _own(ProductLine)), 1), else_=0)),
            func.sum(case((and_(~_own(ProductVariant), ~_own(ProductLine), _own(Brand)), 1), else_=0)),
        )
        .outerjoin(ProductLine, ProductLine.id == ProductVariant.product_line_id)
        .outerjoin(Brand, Brand.id == func.coalesce(ProductVariant.brand_id, ProductLine.brand_id))
        .where(ProductVariant.merged_into_id.is_(None))
    )
    without = total - admin - public
    out["variant"] = {"total": total, "admin": admin, "public": public, "without": without,
                      "from_line": from_line, "from_brand": from_brand, "none": without - from_line - from_brand}
    return out


# --- wanted: the short list per level with nothing to show ---------------------------------------

def wanted(db: Session, *, limit: int = WANTED) -> dict:
    """Per picture level, the rows a picture would do the most for and that show nothing today:
    a brand with no mark; a product line with no picture on it, its brand or any of its product
    variants; a comparable product variant with none on it, its line or its brand. Ranked by the
    airports that carry them, hidden pages left out (nothing shows there). These are the asks."""
    limit = max(1, min(int(limit), WANTED_MAX))
    live = ProductVariant.merged_into_id.is_(None)

    brand_airports = func.count(func.distinct(Listing.shop_id))
    brand_rows = db.execute(
        select(Brand.slug, Brand.name, brand_airports, func.count(func.distinct(ProductVariant.id)))
        .join(ProductVariant, and_(ProductVariant.brand_id == Brand.id, live))
        .join(Listing, Listing.variant_id == ProductVariant.id)
        .where(Brand.alias_of_id.is_(None), Brand.hidden.is_(False), ~_own(Brand))
        .group_by(Brand.id, Brand.slug, Brand.name)
        .order_by(brand_airports.desc(), func.count(func.distinct(ProductVariant.id)).desc(), Brand.name)
        .limit(limit)
    ).all()

    line_airports = func.count(func.distinct(Listing.shop_id))
    line_rows = db.execute(
        select(ProductLine.slug, ProductLine.name, Brand.name, line_airports, func.count(func.distinct(ProductVariant.id)))
        .join(Brand, Brand.id == ProductLine.brand_id)
        .join(ProductVariant, and_(ProductVariant.product_line_id == ProductLine.id, live))
        .outerjoin(Listing, Listing.variant_id == ProductVariant.id)  # every live variant counts, listed or not
        .where(ProductLine.alias_of_id.is_(None), ProductLine.hidden.is_(False), ~_own(ProductLine), ~_own(Brand))
        .group_by(ProductLine.id, ProductLine.slug, ProductLine.name, Brand.name)
        .having(_flag(_own(ProductVariant)) == 0, line_airports >= 1)
        .order_by(line_airports.desc(), func.count(func.distinct(ProductVariant.id)).desc(), ProductLine.name)
        .limit(limit)
    ).all()

    pa = _per_product()
    variant_rows = db.execute(
        select(ProductVariant, pa.c.airports, ProductLine.name.label("line"))
        .join(pa, pa.c.id == ProductVariant.id)
        .outerjoin(ProductLine, ProductLine.id == ProductVariant.product_line_id)
        .where(pa.c.any_image == 0)
        .order_by(pa.c.airports.desc(), ProductVariant.brand, ProductVariant.name)
        .limit(limit)
    ).all()

    return {
        "limit": limit,
        "brand": [{"slug": slug, "name": name, "airports": int(a), "product_variants": int(n)}
                  for slug, name, a, n in brand_rows],
        "line": [{"slug": slug, "name": name, "brand": brand, "airports": int(a), "product_variants": int(n)}
                 for slug, name, brand, a, n in line_rows],
        "variant": [{"id": r.ProductVariant.id, "brand": r.ProductVariant.brand, "name": r.ProductVariant.name,
                     "line": r.line, "size": _size(r.ProductVariant), "airports": int(r.airports),
                     "gtin": r.ProductVariant.gtin}
                    for r in variant_rows],
    }


def product_variants(db: Session, *, group: str | None, brand: str | None, q: str | None, missing: bool,
             page: int, per_page: int) -> dict:
    rows, total = _ranked_product_variants(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)}