"""Katalogdaten einer Liste: Märkte, Warengruppen, Artikel.

Alle Schreibzugriffe erhöhen den Revisionszähler der Liste und schreiben
den neuen Wert in `row_rev` der geänderten Zeile.
"""

from datetime import timedelta

from fastapi import APIRouter, HTTPException, Query, status
from sqlalchemy import select
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session

from app.deps import DbSession
from app.models import (
    Article,
    ArticleAttribute,
    ArticleMarket,
    Category,
    ListItem,
    Market,
)
from app.permissions import EditableList, ReadableList, bump_rev
from app.schemas import MessageOut
from app.product_lookup import lookup
from app.schemas_shopping import (
    ArticleIn,
    ArticleSuggestion,
    ArticleOut,
    ArticleUpdateIn,
    AttributeOut,
    CategoryIn,
    CategoryOut,
    MarketIn,
    MarketOut,
    ProductLookupOut,
)
from app.security import utcnow

router = APIRouter(prefix="/api/lists/{list_id}", tags=["catalog"])


def _check_belongs(db: Session, model, obj_id: str | None, list_id: str, label: str):
    """Verhindert, dass eine Liste auf Objekte einer fremden Liste zeigt.
    Ohne diese Prüfung könnte ein Mitglied durch Angabe einer fremden ID
    Rückschlüsse auf andere Listen ziehen."""
    if obj_id is None:
        return None
    obj = db.get(model, obj_id)
    if obj is None or obj.list_id != list_id or obj.deleted_at is not None:
        raise HTTPException(status.HTTP_400_BAD_REQUEST, f"{label} nicht gefunden")
    return obj


# ==========================================================================
#  Märkte
# ==========================================================================

@router.get("/markets", response_model=list[MarketOut])
def get_markets(lst: ReadableList, db: DbSession):
    rows = db.scalars(
        select(Market)
        .where(Market.list_id == lst.id, Market.deleted_at.is_(None))
        .order_by(Market.sort_order, Market.name)
    ).all()
    return [MarketOut.model_validate(m) for m in rows]


@router.post("/markets", response_model=MarketOut, status_code=status.HTTP_201_CREATED)
def create_market(payload: MarketIn, lst: EditableList, db: DbSession):
    rev = bump_rev(db, lst.id)
    market = Market(
        list_id=lst.id, name=payload.name.strip(),
        sort_order=payload.sort_order, row_rev=rev,
    )
    db.add(market)
    try:
        db.commit()
    except IntegrityError:
        db.rollback()
        raise HTTPException(
            status.HTTP_409_CONFLICT, "Ein Markt mit diesem Namen existiert bereits."
        ) from None
    return MarketOut.model_validate(market)


@router.put("/markets/{market_id}", response_model=MarketOut)
def update_market(market_id: str, payload: MarketIn, lst: EditableList, db: DbSession):
    market = _check_belongs(db, Market, market_id, lst.id, "Markt")
    market.name = payload.name.strip()
    market.sort_order = payload.sort_order
    market.row_rev = bump_rev(db, lst.id)
    try:
        db.commit()
    except IntegrityError:
        db.rollback()
        raise HTTPException(
            status.HTTP_409_CONFLICT, "Ein Markt mit diesem Namen existiert bereits."
        ) from None
    return MarketOut.model_validate(market)


@router.delete("/markets/{market_id}", response_model=MessageOut)
def delete_market(market_id: str, lst: EditableList, db: DbSession):
    market = _check_belongs(db, Market, market_id, lst.id, "Markt")
    rev = bump_rev(db, lst.id)
    market.deleted_at = utcnow()
    market.row_rev = rev

    # Einträge nicht mitlöschen, nur den Marktbezug lösen - sie landen
    # dann in der Gruppe "Ohne Markt" und gehen nicht verloren.
    for item in db.scalars(
        select(ListItem).where(
            ListItem.list_id == lst.id,
            ListItem.market_id == market_id,
            ListItem.deleted_at.is_(None),
        )
    ).all():
        item.market_id = None
        item.row_rev = rev

    db.commit()
    return MessageOut(detail="Markt gelöscht. Betroffene Einträge sind ohne Markt.")


# ==========================================================================
#  Warengruppen
# ==========================================================================

@router.get("/categories", response_model=list[CategoryOut])
def get_categories(lst: ReadableList, db: DbSession):
    rows = db.scalars(
        select(Category)
        .where(Category.list_id == lst.id, Category.deleted_at.is_(None))
        .order_by(Category.sort_order, Category.name)
    ).all()
    return [CategoryOut.model_validate(c) for c in rows]


@router.post("/categories", response_model=CategoryOut, status_code=status.HTTP_201_CREATED)
def create_category(payload: CategoryIn, lst: EditableList, db: DbSession):
    rev = bump_rev(db, lst.id)
    cat = Category(
        list_id=lst.id, name=payload.name.strip(),
        sort_order=payload.sort_order, row_rev=rev,
    )
    db.add(cat)
    try:
        db.commit()
    except IntegrityError:
        db.rollback()
        raise HTTPException(
            status.HTTP_409_CONFLICT, "Diese Warengruppe existiert bereits."
        ) from None
    return CategoryOut.model_validate(cat)


@router.put("/categories/{category_id}", response_model=CategoryOut)
def update_category(
    category_id: str, payload: CategoryIn, lst: EditableList, db: DbSession
):
    cat = _check_belongs(db, Category, category_id, lst.id, "Warengruppe")
    cat.name = payload.name.strip()
    cat.sort_order = payload.sort_order
    cat.row_rev = bump_rev(db, lst.id)
    try:
        db.commit()
    except IntegrityError:
        db.rollback()
        raise HTTPException(
            status.HTTP_409_CONFLICT, "Diese Warengruppe existiert bereits."
        ) from None
    return CategoryOut.model_validate(cat)


@router.delete("/categories/{category_id}", response_model=MessageOut)
def delete_category(category_id: str, lst: EditableList, db: DbSession):
    cat = _check_belongs(db, Category, category_id, lst.id, "Warengruppe")
    rev = bump_rev(db, lst.id)
    cat.deleted_at = utcnow()
    cat.row_rev = rev

    for item in db.scalars(
        select(ListItem).where(
            ListItem.list_id == lst.id,
            ListItem.category_id == category_id,
            ListItem.deleted_at.is_(None),
        )
    ).all():
        item.category_id = None
        item.row_rev = rev

    db.commit()
    return MessageOut(detail="Warengruppe gelöscht.")


# ==========================================================================
#  Artikel
# ==========================================================================

def _article_out(a: Article) -> ArticleOut:
    return ArticleOut(
        id=a.id, name=a.name, barcode=a.barcode, note=a.note,
        default_market_id=a.default_market_id,
        default_category_id=a.default_category_id,
        attributes=[
            AttributeOut(name=x.attr_name, value=x.attr_value) for x in a.attributes
        ],
        available_market_ids=[x.market_id for x in a.availability],
        row_rev=a.row_rev,
    )


def _apply_attributes(db: Session, article: Article, attributes) -> None:
    """Ersetzt die Attributmenge vollständig."""
    for old in list(article.attributes):
        db.delete(old)
    article.attributes = []
    db.flush()
    seen = set()
    for attr in attributes:
        name = attr.name.strip()
        if not name or name in seen:
            continue
        seen.add(name)
        db.add(
            ArticleAttribute(
                article_id=article.id, attr_name=name, attr_value=attr.value.strip()
            )
        )


def _apply_availability(db: Session, article: Article, market_ids, list_id: str) -> None:
    for old in db.scalars(
        select(ArticleMarket).where(ArticleMarket.article_id == article.id)
    ).all():
        db.delete(old)
    db.flush()
    for mid in dict.fromkeys(market_ids):
        _check_belongs(db, Market, mid, list_id, "Markt")
        db.add(ArticleMarket(article_id=article.id, market_id=mid))


@router.get("/articles", response_model=list[ArticleOut])
def get_articles(
    lst: ReadableList,
    db: DbSession,
    q: str | None = None,
    with_barcode: bool | None = None,
):
    stmt = select(Article).where(
        Article.list_id == lst.id, Article.deleted_at.is_(None)
    )
    if q:
        # Nur der LIKE-Operator, kein zusammengebautes SQL - der Suchtext
        # geht als gebundener Parameter in die Abfrage. Sonderzeichen von
        # LIKE werden maskiert, damit "50%" nicht alles findet.
        needle = q.strip().replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
        stmt = stmt.where(Article.name.like(f"%{needle}%", escape="\\"))
    if with_barcode is True:
        stmt = stmt.where(Article.barcode.is_not(None))
    elif with_barcode is False:
        stmt = stmt.where(Article.barcode.is_(None))

    rows = db.scalars(stmt.order_by(Article.name).limit(500)).all()
    return [_article_out(a) for a in rows]


SUGGEST_MONTHS = 24
SUGGEST_LIMIT = 8
SUGGEST_MIN_CHARS = 3


@router.get("/articles/suggest", response_model=list[ArticleSuggestion])
def suggest_articles(
    lst: ReadableList,
    db: DbSession,
    q: str = Query(min_length=SUGGEST_MIN_CHARS, max_length=200),
):
    """Vorschlaege aus dem, was auf dieser Liste schon eingetragen wurde.

    Quelle sind die Listeneintraege der letzten 24 Monate - auch die
    bereits geloeschten, denn gerade die abgehakten und aufgeraeumten
    sind die interessanten. Was jemand vor drei Jahren einmal gekauft
    hat, hilft dagegen nicht mehr und wuerde die Liste nur verlaengern.

    Zurueckgegeben werden die zuletzt verwendeten Angaben: Menge,
    Gebinde, Eigenschaft, Markt und Warengruppe. Wer den Vorschlag
    antippt, hat die Felder gefuellt und kann sie noch aendern.
    """
    needle = q.strip()
    if len(needle) < SUGGEST_MIN_CHARS:
        return []

    # LIKE-Sonderzeichen maskieren, sonst faende die Eingabe "50%" alles.
    escaped = (
        needle.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
    )
    cutoff = utcnow() - timedelta(days=SUGGEST_MONTHS * 30)

    rows = db.execute(
        select(ListItem, Article)
        .join(Article, Article.id == ListItem.article_id)
        .where(
            ListItem.list_id == lst.id,
            ListItem.created_at >= cutoff,
            Article.deleted_at.is_(None),
            Article.name.like(f"%{escaped}%", escape="\\"),
        )
        .order_by(ListItem.created_at.desc())
        .limit(600)
    ).all()

    # Je Artikel den juengsten Eintrag behalten und mitzaehlen, wie oft
    # er vorkam - haeufig Verwendetes soll oben stehen.
    best: dict[str, dict] = {}
    for item, article in rows:
        entry = best.get(article.id)
        if entry is None:
            best[article.id] = {"item": item, "article": article, "count": 1}
        else:
            entry["count"] += 1

    def rank(entry: dict) -> tuple[int, float]:
        # Erst Haeufigkeit, dann Aktualitaet.
        return (-entry["count"], -entry["item"].created_at.timestamp())

    out = []
    for entry in sorted(best.values(), key=rank)[:SUGGEST_LIMIT]:
        item, article = entry["item"], entry["article"]
        out.append(
            ArticleSuggestion(
                article_id=article.id,
                name=article.name,
                count=item.count,
                pack_size=item.pack_size,
                pack_unit=item.pack_unit,
                variant=item.variant,
                market_id=item.market_id or article.default_market_id,
                category_id=item.category_id or article.default_category_id,
                times_used=entry["count"],
                last_used_at=item.created_at,
            )
        )
    return out


@router.get("/barcode/{barcode}", response_model=ProductLookupOut)
def resolve_barcode(barcode: str, lst: ReadableList, db: DbSession):
    """Strichcode auflösen - erst im eigenen Bestand, dann außerhalb.

    Die Reihenfolge ist wichtig: Was hier schon gepflegt wurde, ist
    verlässlicher als eine Fremdquelle, und ein eigener Name soll nicht
    von einer Datenbank überschrieben werden.
    """
    article = db.scalar(
        select(Article).where(
            Article.list_id == lst.id,
            Article.barcode == barcode,
            Article.deleted_at.is_(None),
        )
    )
    if article is not None:
        return ProductLookupOut(
            barcode=barcode, found=True, source="catalog",
            article_id=article.id, name=article.name,
        )

    hit = lookup(db, barcode)
    if hit is None or not hit.found:
        return ProductLookupOut(barcode=barcode, found=False, source="none")

    return ProductLookupOut(
        barcode=barcode,
        found=True,
        source="openfoodfacts",
        name=hit.name,
        brand=hit.brand,
        package=hit.package,
        count=hit.count,
        pack_size=hit.pack_size,
        pack_unit=hit.pack_unit,
    )


@router.get("/articles/by-barcode/{barcode}", response_model=ArticleOut)
def article_by_barcode(barcode: str, lst: ReadableList, db: DbSession):
    article = db.scalar(
        select(Article).where(
            Article.list_id == lst.id,
            Article.barcode == barcode,
            Article.deleted_at.is_(None),
        )
    )
    if article is None:
        raise HTTPException(
            status.HTTP_404_NOT_FOUND, "Kein Artikel mit diesem Barcode"
        )
    return _article_out(article)


@router.post("/articles", response_model=ArticleOut, status_code=status.HTTP_201_CREATED)
def create_article(payload: ArticleIn, lst: EditableList, db: DbSession):
    _check_belongs(db, Market, payload.default_market_id, lst.id, "Markt")
    _check_belongs(db, Category, payload.default_category_id, lst.id, "Warengruppe")

    rev = bump_rev(db, lst.id)
    article = Article(
        list_id=lst.id, name=payload.name.strip(),
        barcode=(payload.barcode or None), note=(payload.note or None),
        default_market_id=payload.default_market_id,
        default_category_id=payload.default_category_id,
        row_rev=rev,
    )
    db.add(article)
    db.flush()
    _apply_attributes(db, article, payload.attributes)
    _apply_availability(db, article, payload.available_market_ids, lst.id)

    try:
        db.commit()
    except IntegrityError:
        db.rollback()
        raise HTTPException(
            status.HTTP_409_CONFLICT, "Ein Artikel mit diesem Namen existiert bereits."
        ) from None

    db.refresh(article)
    return _article_out(article)


@router.patch("/articles/{article_id}", response_model=ArticleOut)
def update_article(
    article_id: str, payload: ArticleUpdateIn, lst: EditableList, db: DbSession
):
    article = _check_belongs(db, Article, article_id, lst.id, "Artikel")
    data = payload.model_dump(exclude_unset=True)

    if "default_market_id" in data:
        _check_belongs(db, Market, data["default_market_id"], lst.id, "Markt")
        article.default_market_id = data["default_market_id"]
    if "default_category_id" in data:
        _check_belongs(db, Category, data["default_category_id"], lst.id, "Warengruppe")
        article.default_category_id = data["default_category_id"]
    if "name" in data and data["name"]:
        article.name = data["name"].strip()
    if "barcode" in data:
        article.barcode = data["barcode"] or None
    if "note" in data:
        article.note = data["note"] or None

    if payload.attributes is not None:
        _apply_attributes(db, article, payload.attributes)
    if payload.available_market_ids is not None:
        _apply_availability(db, article, payload.available_market_ids, lst.id)

    article.row_rev = bump_rev(db, lst.id)

    try:
        db.commit()
    except IntegrityError:
        db.rollback()
        raise HTTPException(
            status.HTTP_409_CONFLICT, "Ein Artikel mit diesem Namen existiert bereits."
        ) from None

    db.refresh(article)
    return _article_out(article)


@router.delete("/articles/{article_id}", response_model=MessageOut)
def delete_article(article_id: str, lst: EditableList, db: DbSession):
    article = _check_belongs(db, Article, article_id, lst.id, "Artikel")
    rev = bump_rev(db, lst.id)
    article.deleted_at = utcnow()
    article.row_rev = rev

    # Offene Einträge dieses Artikels verschwinden mit. Alles andere
    # hinterließe Einträge, die auf einen gelöschten Artikel zeigen.
    removed = 0
    for item in db.scalars(
        select(ListItem).where(
            ListItem.list_id == lst.id,
            ListItem.article_id == article_id,
            ListItem.deleted_at.is_(None),
        )
    ).all():
        item.deleted_at = utcnow()
        item.row_rev = rev
        removed += 1

    db.commit()
    return MessageOut(
        detail=f"Artikel gelöscht, dazu {removed} Eintrag/Einträge auf der Liste."
    )