import csv
import io
import json

from fastapi import APIRouter, Depends, Query, HTTPException, BackgroundTasks
from tz import local_strftime, to_local
from fastapi.responses import StreamingResponse
from pydantic import BaseModel
from sqlalchemy.orm import Session
from sqlalchemy import func
from datetime import date, datetime, timedelta, timezone
from typing import Optional, List

from database import get_db
from models.order import Order, OrderItem, OrderWaiter, PrintLog
from models.prep_zone import PrepZone
from models.user import User
from models.table import Table
from models.printer import Printer
from models.shift import WaiterShift
from models.business_day import BusinessDay
from schemas.order import OrderOut
from schemas.table import TableOut
from routers.deps import require_reports
from services.printer_service import (
    print_waiter_report, print_printer_report, print_order_receipt, load_divider_style,
    print_products_report, print_categories_report, print_tables_report,
    print_prep_zone_summary,
)

router = APIRouter()


def _item_val(item) -> float:
    """Effective line value: unit_price + any price adjustment, times quantity."""
    return ((item.unit_price or 0.0) + (item.price_adjustment or 0.0)) * item.quantity


def _dt(dt):
    if dt is None:
        return None
    return (dt.isoformat() + "Z") if dt.tzinfo is None else dt.isoformat()


@router.get("/shift")
def shift_summary(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    report_date: Optional[date] = Query(default=None, alias="date"),
    waiter_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    """Payments collected per waiter — based on paid_by on order items."""
    if from_dt and to_dt:
        start = datetime.fromisoformat(from_dt)
        end = datetime.fromisoformat(to_dt)
    else:
        target = report_date or date.today()
        start = datetime.combine(target, datetime.min.time())
        end = start + timedelta(days=1)

    q = db.query(OrderItem).filter(
        OrderItem.status == "paid",
        OrderItem.paid_at >= start,
        OrderItem.paid_at < end,
    )
    if waiter_id:
        q = q.filter(OrderItem.paid_by == waiter_id)
    items = q.all()

    waiters_db = {u.id: u for u in db.query(User).all()}
    tables_db = {t.id: (t.label or f"T{t.number}") for t in db.query(Table).all()}

    # Build per-waiter summary keyed by waiter_id
    summary: dict[int, dict] = {}
    for item in items:
        wid = item.paid_by
        if wid not in summary:
            w = waiters_db.get(wid)
            wname = (w.full_name or w.username) if w else f"#{wid}"
            summary[wid] = {
                "waiter_id": wid,
                "waiter_name": wname,
                "items": 0,
                "total": 0.0,
                "order_data": {},
            }
        summary[wid]["items"] += item.quantity
        val = _item_val(item)
        summary[wid]["total"] += val

        oid = item.order_id
        if oid not in summary[wid]["order_data"]:
            order = db.query(Order).filter(Order.id == oid).first()
            summary[wid]["order_data"][oid] = {
                "id": oid,
                "time_open": local_strftime(order.opened_at, "%H:%M") if order else "",
                "time_close": local_strftime(order.closed_at, "%H:%M") if order and order.closed_at else "",
                "table": tables_db.get(order.table_id, f"#{oid}") if order else f"#{oid}",
                "total": 0.0,
                "items": [],
            }
        summary[wid]["order_data"][oid]["total"] += val
        product_name = item.product.name if item.product else f"#{item.product_id}"
        summary[wid]["order_data"][oid]["items"].append(
            {"name": product_name, "quantity": item.quantity}
        )

    result = []
    for entry in summary.values():
        entry["orders"] = len(entry["order_data"])
        entry["order_data"] = list(entry["order_data"].values())
        result.append(entry)

    return {"from": start.isoformat(), "to": end.isoformat(), "waiters": result}


@router.get("/shift/orders")
def shift_orders_summary(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    report_date: Optional[date] = Query(default=None, alias="date"),
    waiter_id: Optional[int] = None,
    business_day_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    """Items sent (added) per waiter — regardless of payment status."""
    q = db.query(OrderItem).filter(OrderItem.status.in_(["active", "paid"]))
    if business_day_id:
        q = q.join(Order).filter(Order.business_day_id == business_day_id)
    else:
        if from_dt and to_dt:
            start = datetime.fromisoformat(from_dt)
            end = datetime.fromisoformat(to_dt)
        else:
            target = report_date or date.today()
            start = datetime.combine(target, datetime.min.time())
            end = start + timedelta(days=1)
        q = q.filter(OrderItem.added_at >= start, OrderItem.added_at < end)
    if waiter_id:
        q = q.filter(OrderItem.added_by == waiter_id)
    items = q.all()

    # Also fetch cancelled items for the same window/waiter
    cq = db.query(OrderItem).filter(OrderItem.status == "cancelled")
    if business_day_id:
        cq = cq.join(Order, OrderItem.order_id == Order.id).filter(Order.business_day_id == business_day_id)
    else:
        cq = cq.filter(OrderItem.added_at >= start, OrderItem.added_at < end)
    if waiter_id:
        cq = cq.filter(OrderItem.added_by == waiter_id)
    cancelled_items = cq.all()

    waiters_db = {u.id: u for u in db.query(User).all()}
    tables_db = {t.id: (t.label or f"T{t.number}") for t in db.query(Table).all()}

    # Track shifts for duration (hours worked)
    shift_q = db.query(WaiterShift)
    if business_day_id:
        shift_q = shift_q.filter(WaiterShift.business_day_id == business_day_id)
    else:
        shift_q = shift_q.filter(WaiterShift.started_at >= start, WaiterShift.started_at < end)
    if waiter_id:
        shift_q = shift_q.filter(WaiterShift.waiter_id == waiter_id)
    shifts_list = shift_q.all()
    # hours worked per waiter
    hours_by_waiter: dict[int, float] = {}
    for s in shifts_list:
        wid = s.waiter_id
        end_time = s.ended_at or datetime.now(timezone.utc).replace(tzinfo=None)
        delta = (end_time - s.started_at).total_seconds() / 3600.0
        hours_by_waiter[wid] = hours_by_waiter.get(wid, 0.0) + delta

    summary: dict[int, dict] = {}
    for item in items:
        wid = item.added_by
        if wid not in summary:
            w = waiters_db.get(wid)
            wname = (w.full_name or w.username) if w else f"#{wid}"
            summary[wid] = {
                "waiter_id": wid,
                "waiter_name": wname,
                "items": 0,
                "total": 0.0,
                "cancellations": 0,
                "order_data": {},
            }
        summary[wid]["items"] += item.quantity
        val = _item_val(item)
        summary[wid]["total"] += val

        oid = item.order_id
        if oid not in summary[wid]["order_data"]:
            order = db.query(Order).filter(Order.id == oid).first()
            summary[wid]["order_data"][oid] = {
                "id": oid,
                "time_open": local_strftime(order.opened_at, "%H:%M") if order else "",
                "time_close": local_strftime(order.closed_at, "%H:%M") if order and order.closed_at else "",
                "table": tables_db.get(order.table_id, f"#{oid}") if order else f"#{oid}",
                "total": 0.0,
                "items": [],
            }
        summary[wid]["order_data"][oid]["total"] += val
        product_name = item.product.name if item.product else f"#{item.product_id}"
        summary[wid]["order_data"][oid]["items"].append(
            {"name": product_name, "quantity": item.quantity}
        )

    for item in cancelled_items:
        wid = item.added_by
        if wid not in summary:
            w = waiters_db.get(wid)
            wname = (w.full_name or w.username) if w else f"#{wid}"
            summary[wid] = {
                "waiter_id": wid,
                "waiter_name": wname,
                "items": 0,
                "total": 0.0,
                "cancellations": 0,
                "order_data": {},
            }
        summary[wid]["cancellations"] = summary[wid].get("cancellations", 0) + item.quantity

    result = []
    for entry in summary.values():
        entry["orders"] = len(entry["order_data"])
        entry["order_data"] = list(entry["order_data"].values())
        entry["hours_worked"] = round(hours_by_waiter.get(entry["waiter_id"], 0.0), 2)
        result.append(entry)

    return {"from": start.isoformat(), "to": end.isoformat(), "waiters": result}


@router.get("/orders/history")
def order_history(
    from_date: Optional[str] = Query(default=None, alias="from"),
    to_date: Optional[str] = Query(default=None, alias="to"),
    waiter_id: Optional[int] = None,
    order_status: Optional[str] = Query(default=None, alias="status"),
    table_id: Optional[int] = None,
    business_day_id: Optional[int] = None,
    page: int = 1,
    page_size: int = 50,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    from sqlalchemy.orm import joinedload
    from models.table import Table as TableModel

    q = db.query(Order).options(
        joinedload(Order.items).joinedload(OrderItem.product),
        joinedload(Order.waiters),
        joinedload(Order.audit_logs),
    )
    if business_day_id:
        q = q.filter(Order.business_day_id == business_day_id)
    elif from_date or to_date:
        if from_date:
            q = q.filter(Order.opened_at >= datetime.fromisoformat(from_date))
        if to_date:
            q = q.filter(Order.opened_at <= datetime.fromisoformat(to_date))
    if waiter_id:
        q = q.join(OrderWaiter).filter(OrderWaiter.waiter_id == waiter_id)
    if order_status:
        q = q.filter(Order.status == order_status)
    if table_id:
        q = q.filter(Order.table_id == table_id)
    orders = q.order_by(Order.opened_at.desc()).offset((page - 1) * page_size).limit(page_size).all()

    # Collect all waiter IDs and table IDs to resolve in bulk
    waiter_ids: set[int] = set()
    table_ids: set[int] = set()
    item_waiter_ids: set[int] = set()
    for o in orders:
        if o.opened_by:
            waiter_ids.add(o.opened_by)
        if o.closed_by:
            waiter_ids.add(o.closed_by)
        if o.table_id:
            table_ids.add(o.table_id)
        for item in o.items:
            if item.added_by:
                item_waiter_ids.add(item.added_by)
            if item.paid_by:
                item_waiter_ids.add(item.paid_by)
        for log in o.audit_logs:
            if log.waiter_id:
                waiter_ids.add(log.waiter_id)

    all_waiter_ids = waiter_ids | item_waiter_ids
    waiters_map: dict[int, str] = {}
    if all_waiter_ids:
        for w in db.query(User).filter(User.id.in_(all_waiter_ids)).all():
            waiters_map[w.id] = w.full_name or w.username

    tables_map: dict[int, str] = {}
    if table_ids:
        for t in db.query(TableModel).filter(TableModel.id.in_(table_ids)).all():
            prefix = (t.group.prefix if t.group and t.group.prefix else "") if t.group else ""
            tables_map[t.id] = t.label if t.label else f"{prefix}{t.number}"

    def _wname(wid):
        if wid is None:
            return None
        return waiters_map.get(wid, f"#{wid}")

    def _dt_local(dt):
        if dt is None:
            return None
        return (dt.isoformat() + "Z") if dt.tzinfo is None else dt.isoformat()

    result = []
    for o in orders:
        items_out = []
        for item in o.items:
            items_out.append({
                "id": item.id,
                "order_id": item.order_id,
                "product_id": item.product_id,
                "product": {"id": item.product.id, "name": item.product.name} if item.product else None,
                "added_by": item.added_by,
                "added_by_name": _wname(item.added_by),
                "added_at": _dt_local(item.added_at),
                "quantity": item.quantity,
                "unit_price": float(item.unit_price),
                "price_adjustment": float(item.price_adjustment or 0.0),
                "status": item.status,
                "paid_by": item.paid_by,
                "paid_by_name": _wname(item.paid_by),
                "paid_at": _dt_local(item.paid_at),
                "payment_method": item.payment_method,
                "paid_in_shift_id": item.paid_in_shift_id,
                "notes": item.notes,
                "printed": item.printed,
                "selected_options": item.selected_options,
                "removed_ingredients": item.removed_ingredients,
            })
        audit_out = []
        for log in o.audit_logs:
            audit_out.append({
                "id": log.id,
                "order_id": log.order_id,
                "event_type": log.event_type,
                "waiter_id": log.waiter_id,
                "waiter_name": _wname(log.waiter_id),
                "item_ids": log.item_ids,
                "amount": log.amount,
                "payment_method": log.payment_method,
                "note": log.note,
                "created_at": _dt_local(log.created_at),
                "offline_at": log.offline_at,
                "is_duplicate": log.is_duplicate,
            })
        result.append({
            "id": o.id,
            "table_id": o.table_id,
            "table_name": tables_map.get(o.table_id) if o.table_id else None,
            "opened_by": o.opened_by,
            "opened_by_name": _wname(o.opened_by),
            "opened_at": _dt_local(o.opened_at),
            "closed_by": o.closed_by,
            "closed_by_name": _wname(o.closed_by),
            "closed_at": _dt_local(o.closed_at),
            "status": o.status,
            "notes": o.notes,
            "business_day_id": o.business_day_id,
            "items": items_out,
            "waiters": [{"waiter_id": w.waiter_id} for w in o.waiters],
            "audit_logs": audit_out,
        })
    return result


@router.get("/tables/summary")
def tables_summary(db: Session = Depends(get_db), user: User = Depends(require_reports)):
    tables = db.query(Table).filter(Table.is_active == True).all()
    result = []
    for table in tables:
        active_order = db.query(Order).filter(
            Order.table_id == table.id,
            Order.status.in_(["open", "partially_paid"]),
        ).first()
        result.append({
            "table": TableOut.model_validate(table),
            "status": active_order.status if active_order else "free",
            "order_id": active_order.id if active_order else None,
        })
    return result


@router.get("/printers")
def printer_totals(
    from_date: Optional[str] = Query(default=None, alias="from"),
    to_date: Optional[str] = Query(default=None, alias="to"),
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    """Returns totals per printer based on print_log entries in the date range."""
    q = db.query(PrintLog).filter(PrintLog.success == True)
    if from_date:
        q = q.filter(PrintLog.printed_at >= datetime.fromisoformat(from_date))
    if to_date:
        q = q.filter(PrintLog.printed_at <= datetime.fromisoformat(to_date))
    logs = q.all()

    printers_db = {p.id: p for p in db.query(Printer).all()}
    tables_db = {t.id: (t.label or f"T{t.number}") for t in db.query(Table).all()}

    # summary[pid] — aggregated totals
    summary: dict[int, dict] = {}
    # order_map[pid][order_id] — per-order detail with items
    order_map: dict[int, dict] = {}

    for log in logs:
        pid = log.printer_id
        if pid not in summary:
            printer = printers_db.get(pid)
            summary[pid] = {
                "printer_id": pid,
                "printer_name": printer.name if printer else f"Printer #{pid}",
                "print_jobs": 0,
                "orders": set(),
                "items": 0,
                "total": 0.0,
            }
            order_map[pid] = {}

        summary[pid]["print_jobs"] += 1
        summary[pid]["orders"].add(log.order_id)

        oid = log.order_id
        if oid not in order_map[pid]:
            order = db.query(Order).filter(Order.id == oid).first()
            order_map[pid][oid] = {
                "order_id": oid,
                "time": local_strftime(log.printed_at, "%H:%M"),
                "table": tables_db.get(order.table_id, f"#{oid}") if order else f"#{oid}",
                "total": 0.0,
                "items": [],
            }

        try:
            item_ids = json.loads(log.item_ids)
        except Exception:
            item_ids = []
        if item_ids is None:
            item_ids = []
        for item_id in item_ids:
            item = db.query(OrderItem).filter(OrderItem.id == item_id).first()
            if item and item.status in ("active", "paid"):
                summary[pid]["items"] += item.quantity
                val = _item_val(item)
                summary[pid]["total"] += val
                order_map[pid][oid]["total"] += val
                product_name = item.product.name if item.product else f"#{item.product_id}"
                order_map[pid][oid]["items"].append({"name": product_name, "quantity": item.quantity})

    result = []
    for pid, entry in summary.items():
        entry["orders"] = len(entry["orders"])
        entry["order_data"] = list(order_map.get(pid, {}).values())
        result.append(entry)
    return {"printers": result}


class PrintWaiterReportBody(BaseModel):
    waiter_name: str
    printer_id: int
    mode: str  # "simple" | "extensive"
    from_dt: str
    to_dt: str


class PrintPrinterReportBody(BaseModel):
    # printer_target_id: 0 = all printers, >0 = specific printer id
    printer_target_id: int
    printer_id: int
    # mode: "quick" | "orders" | "detailed"
    mode: str
    from_dt: Optional[str] = None
    to_dt: Optional[str] = None
    business_day_id: Optional[int] = None
    item_breakdown: bool = False


class PrintOrderBody(BaseModel):
    printer_id: int


def _build_printer_block(printer_id: int, printer_name: str, logs, tables, db, mode: str) -> dict:
    """
    Build a report block for a single printer from its logs.
    Returns dict with print_jobs, orders, items, total, order_data, item_breakdown.
    item_breakdown is always computed (used for product analysis section when requested).
    """
    order_map: dict = {}
    item_breakdown: dict = {}  # name → {"qty": int, "value": float}
    items_count = 0
    grand_total = 0.0

    for log in logs:
        oid = log.order_id
        if oid not in order_map:
            order = db.query(Order).filter(Order.id == oid).first()
            if order:
                order_map[oid] = {
                    "id": oid,
                    "time": local_strftime(log.printed_at, "%H:%M"),
                    "table": tables.get(order.table_id, f"#{order.table_id}"),
                    "total": 0.0,
                    "items": [],
                }
        try:
            item_ids = json.loads(log.item_ids)
        except Exception:
            item_ids = []
        for item_id in item_ids:
            item = db.query(OrderItem).filter(OrderItem.id == item_id).first()
            if item and item.status in ("active", "paid"):
                items_count += item.quantity
                val = _item_val(item)
                grand_total += val
                product_name = item.product.name if item.product else f"#{item.product_id}"
                if oid in order_map:
                    order_map[oid]["total"] += val
                    order_map[oid]["items"].append({
                        "name": product_name,
                        "quantity": item.quantity,
                        "unit_price": float((item.unit_price or 0.0) + (item.price_adjustment or 0.0)),
                        "total": round(val, 2),
                    })
                # Accumulate item breakdown (always, regardless of mode)
                if product_name not in item_breakdown:
                    item_breakdown[product_name] = {"qty": 0, "value": 0.0}
                item_breakdown[product_name]["qty"] += item.quantity
                item_breakdown[product_name]["value"] = round(item_breakdown[product_name]["value"] + val, 2)

    # Convert breakdown to sorted list for easy rendering
    breakdown_list = sorted(
        [{"name": k, "qty": v["qty"], "value": v["value"]} for k, v in item_breakdown.items()],
        key=lambda x: x["qty"],
        reverse=True,
    )

    return {
        "printer_id": printer_id,
        "printer_name": printer_name,
        "print_jobs": len(logs),
        "orders": len(order_map),
        "items": items_count,
        "total": round(grand_total, 2),
        "order_data": list(order_map.values()) if mode in ("orders", "detailed") else [],
        "item_breakdown": breakdown_list,
    }


@router.post("/print/waiter")
def print_waiter(
    body: PrintWaiterReportBody,
    background_tasks: BackgroundTasks,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    printer = db.query(Printer).filter(Printer.id == body.printer_id, Printer.is_active == True).first()
    if not printer:
        raise HTTPException(status_code=404, detail="Printer not found or inactive")

    from_dt = datetime.fromisoformat(body.from_dt)
    to_dt = datetime.fromisoformat(body.to_dt)

    # Gather orders for this waiter in time range
    waiter = db.query(User).filter(User.username == body.waiter_name).first()
    q = db.query(Order).filter(
        Order.opened_at >= from_dt,
        Order.opened_at <= to_dt,
    )
    if waiter:
        q = q.filter(Order.opened_by == waiter.id)
    else:
        q = q.filter(False)
    orders = q.all()

    # Enrich with table names
    tables = {t.id: (t.label or f"T{t.number}") for t in db.query(Table).all()}
    order_data = []
    for o in orders:
        active_items = [i for i in o.items if i.status in ("active", "paid")]
        total = sum(_item_val(i) for i in active_items)
        order_data.append({
            "id": o.id,
            "time_open": local_strftime(o.opened_at, "%H:%M"),
            "time_close": local_strftime(o.closed_at, "%H:%M") if o.closed_at else "",
            "table": tables.get(o.table_id, f"#{o.table_id}"),
            "total": total,
            "items": [
                {"name": (i.product.name if i.product else f"#{i.product_id}"), "quantity": i.quantity}
                for i in active_items
            ],
        })

    items_count = sum(
        i.quantity for o in orders for i in o.items if i.status in ("active", "paid")
    )
    grand_total = sum(d["total"] for d in order_data)

    report = {
        "waiter_name": body.waiter_name,
        "orders": len(orders),
        "items": items_count,
        "total": grand_total,
        "order_data": order_data if body.mode == "extensive" else [],
        "from_dt": local_strftime(from_dt, "%d/%m/%Y %H:%M"),
        "to_dt": local_strftime(to_dt, "%d/%m/%Y %H:%M"),
    }

    background_tasks.add_task(print_waiter_report, printer.ip_address, printer.port, report, body.mode, printer.codepage_n)
    return {"status": "printing"}


@router.post("/print/printer")
def print_printer_totals(
    body: PrintPrinterReportBody,
    background_tasks: BackgroundTasks,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    # The physical printer that will receive the paper
    printer = db.query(Printer).filter(Printer.id == body.printer_id, Printer.is_active == True).first()
    if not printer:
        raise HTTPException(status_code=404, detail="Printer not found or inactive")

    tables = {t.id: (t.label or f"T{t.number}") for t in db.query(Table).all()}

    # Build time filter for logs
    def _base_log_q():
        q = db.query(PrintLog).filter(PrintLog.success == True)
        if body.business_day_id:
            q = q.join(Order).filter(Order.business_day_id == body.business_day_id)
        elif body.from_dt and body.to_dt:
            from_dt = datetime.fromisoformat(body.from_dt)
            to_dt = datetime.fromisoformat(body.to_dt)
            q = q.filter(PrintLog.printed_at >= from_dt, PrintLog.printed_at <= to_dt)
        return q

    # Determine period label for the receipt header
    if body.business_day_id:
        bd = db.query(BusinessDay).filter(BusinessDay.id == body.business_day_id).first()
        period_label = local_strftime(bd.opened_at, "%d/%m/%Y") if bd else f"Workday #{body.business_day_id}"
        period_is_workday = True
    else:
        from_dt = datetime.fromisoformat(body.from_dt) if body.from_dt else None
        to_dt = datetime.fromisoformat(body.to_dt) if body.to_dt else None
        period_label = (
            f"{local_strftime(from_dt, '%d/%m/%Y')} - {local_strftime(to_dt, '%d/%m/%Y')}"
            if from_dt and to_dt else "—"
        )
        period_is_workday = False

    if body.printer_target_id == 0:
        # All printers — build one block per active printer that has logs
        all_printers = db.query(Printer).filter(Printer.is_active == True).all()
        printer_blocks = []
        for p in all_printers:
            logs = _base_log_q().filter(PrintLog.printer_id == p.id).all()
            if not logs:
                continue
            block = _build_printer_block(p.id, p.name, logs, tables, db, body.mode)
            printer_blocks.append(block)
    else:
        target_printer = db.query(Printer).filter(Printer.id == body.printer_target_id).first()
        target_name = target_printer.name if target_printer else f"Printer #{body.printer_target_id}"
        logs = _base_log_q().filter(PrintLog.printer_id == body.printer_target_id).all()
        printer_blocks = [_build_printer_block(body.printer_target_id, target_name, logs, tables, db, body.mode)]

    report = {
        "printer_blocks": printer_blocks,
        "period_label": period_label,
        "period_is_workday": period_is_workday,
        "mode": body.mode,
        "item_breakdown": body.item_breakdown,
        "line_width": printer.line_width,
        "div_style": load_divider_style(db),
    }

    background_tasks.add_task(print_printer_report, printer.ip_address, printer.port, report, body.mode, printer.codepage_n)
    return {"status": "printing"}


# ---------------------------------------------------------------------------
# Print: products / categories / tables
# ---------------------------------------------------------------------------

class PrintAnalyticsBody(BaseModel):
    printer_id: int
    mode: str  # "smart" | "full"
    from_dt: Optional[str] = None
    to_dt: Optional[str] = None
    business_day_id: Optional[int] = None


def _analytics_period(body: PrintAnalyticsBody, db) -> tuple[str, bool]:
    """Return (period_label, period_is_workday)."""
    if body.business_day_id:
        bd = db.query(BusinessDay).filter(BusinessDay.id == body.business_day_id).first()
        label = local_strftime(bd.opened_at, "%d/%m/%Y") if bd else f"Workday #{body.business_day_id}"
        return label, True
    from_dt = datetime.fromisoformat(body.from_dt) if body.from_dt else None
    to_dt = datetime.fromisoformat(body.to_dt) if body.to_dt else None
    label = (
        f"{local_strftime(from_dt, '%d/%m/%Y')} - {local_strftime(to_dt, '%d/%m/%Y')}"
        if from_dt and to_dt else "—"
    )
    return label, False


@router.post("/print/products")
def print_products(
    body: PrintAnalyticsBody,
    background_tasks: BackgroundTasks,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    from models.product import Product, Category

    printer = db.query(Printer).filter(Printer.id == body.printer_id, Printer.is_active == True).first()
    if not printer:
        raise HTTPException(status_code=404, detail="Printer not found or inactive")

    period_label, period_is_workday = _analytics_period(body, db)

    # Build sold-item summary
    q = db.query(OrderItem).filter(OrderItem.status.in_(["active", "paid"]))
    if body.business_day_id:
        q = q.join(Order).filter(Order.business_day_id == body.business_day_id)
    elif body.from_dt and body.to_dt:
        q = q.filter(
            OrderItem.added_at >= datetime.fromisoformat(body.from_dt),
            OrderItem.added_at <= datetime.fromisoformat(body.to_dt),
        )
    items = q.all()

    sold: dict = {}
    for item in items:
        pid = item.product_id
        if pid not in sold:
            sold[pid] = {"qty": 0, "revenue": 0.0}
        sold[pid]["qty"] += item.quantity
        sold[pid]["revenue"] += _item_val(item)

    if body.mode == "full":
        # All active products, 0-sold included
        all_products = db.query(Product).filter(Product.lifecycle_status == "active").order_by(Product.name).all()
        report_items = sorted(
            [{"name": p.name, "qty": sold.get(p.id, {}).get("qty", 0), "revenue": round(sold.get(p.id, {}).get("revenue", 0.0), 2)} for p in all_products],
            key=lambda x: x["qty"], reverse=True,
        )
    else:
        # Smart: only sold
        products_db = {p.id: p for p in db.query(Product).all()}
        report_items = sorted(
            [{"name": (products_db[pid].name if pid in products_db else f"#{pid}"), "qty": v["qty"], "revenue": round(v["revenue"], 2)} for pid, v in sold.items()],
            key=lambda x: x["qty"], reverse=True,
        )

    report = {
        "mode": body.mode,
        "period_label": period_label,
        "period_is_workday": period_is_workday,
        "items": report_items,
        "line_width": printer.line_width,
        "div_style": load_divider_style(db),
    }
    background_tasks.add_task(print_products_report, printer.ip_address, printer.port, report, printer.codepage_n)
    return {"status": "printing"}


@router.post("/print/categories")
def print_categories(
    body: PrintAnalyticsBody,
    background_tasks: BackgroundTasks,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    from models.product import Product, Category

    printer = db.query(Printer).filter(Printer.id == body.printer_id, Printer.is_active == True).first()
    if not printer:
        raise HTTPException(status_code=404, detail="Printer not found or inactive")

    period_label, period_is_workday = _analytics_period(body, db)

    q = db.query(OrderItem).filter(OrderItem.status.in_(["active", "paid"]))
    if body.business_day_id:
        q = q.join(Order).filter(Order.business_day_id == body.business_day_id)
    elif body.from_dt and body.to_dt:
        q = q.filter(
            OrderItem.added_at >= datetime.fromisoformat(body.from_dt),
            OrderItem.added_at <= datetime.fromisoformat(body.to_dt),
        )
    items = q.all()

    products_db = {p.id: p for p in db.query(Product).all()}
    categories_db = {c.id: c for c in db.query(Category).all()}
    # summary[cid] = {name, units_sold, revenue, products: {pid: {name, qty, revenue}}}
    summary: dict = {}
    for item in items:
        product = products_db.get(item.product_id)
        if not product:
            continue
        cid = product.category_id
        pid = item.product_id
        if cid not in summary:
            cat = categories_db.get(cid)
            summary[cid] = {"name": cat.name if cat else f"#{cid}", "units_sold": 0, "revenue": 0.0, "products": {}}
        summary[cid]["units_sold"] += item.quantity
        summary[cid]["revenue"] += _item_val(item)
        if pid not in summary[cid]["products"]:
            summary[cid]["products"][pid] = {"name": product.name, "qty": 0, "revenue": 0.0}
        summary[cid]["products"][pid]["qty"] += item.quantity
        summary[cid]["products"][pid]["revenue"] += _item_val(item)

    total_rev = sum(v["revenue"] for v in summary.values())
    total_qty = sum(v["units_sold"] for v in summary.values())
    cat_list = []
    for v in sorted(summary.values(), key=lambda x: x["revenue"], reverse=True):
        cat_total_qty = v["units_sold"]
        cat_total_rev = v["revenue"]
        products_list = sorted(
            [{"name": p["name"], "qty": p["qty"], "revenue": round(p["revenue"], 2),
              "pct_qty": round(p["qty"] / cat_total_qty * 100, 1) if cat_total_qty else 0,
              "pct_rev": round(p["revenue"] / cat_total_rev * 100, 1) if cat_total_rev else 0}
             for p in v["products"].values()],
            key=lambda x: x["qty"], reverse=True,
        )
        cat_list.append({
            "name": v["name"],
            "units_sold": v["units_sold"],
            "revenue": round(v["revenue"], 2),
            "pct_rev": round(v["revenue"] / total_rev * 100, 1) if total_rev else 0,
            "pct_qty": round(v["units_sold"] / total_qty * 100, 1) if total_qty else 0,
            "products": products_list,
        })

    report = {
        "mode": body.mode,
        "period_label": period_label,
        "period_is_workday": period_is_workday,
        "categories": cat_list,
        "line_width": printer.line_width,
        "div_style": load_divider_style(db),
    }
    background_tasks.add_task(print_categories_report, printer.ip_address, printer.port, report, printer.codepage_n)
    return {"status": "printing"}


@router.post("/print/tables")
def print_tables(
    body: PrintAnalyticsBody,
    background_tasks: BackgroundTasks,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    printer = db.query(Printer).filter(Printer.id == body.printer_id, Printer.is_active == True).first()
    if not printer:
        raise HTTPException(status_code=404, detail="Printer not found or inactive")

    period_label, period_is_workday = _analytics_period(body, db)

    q = db.query(Order).filter(Order.status.in_(["closed", "paid"]))
    if body.business_day_id:
        q = q.filter(Order.business_day_id == body.business_day_id)
    elif body.from_dt and body.to_dt:
        q = q.filter(
            Order.opened_at >= datetime.fromisoformat(body.from_dt),
            Order.opened_at <= datetime.fromisoformat(body.to_dt),
        )
    orders = q.all()

    tables_db = {t.id: t for t in db.query(Table).all()}
    summary: dict = {}
    for order in orders:
        tid = order.table_id
        if tid not in summary:
            t = tables_db.get(tid)
            summary[tid] = {
                "name": (t.label or f"T{t.number}") if t else f"#{tid}",
                "order_count": 0, "revenue": 0.0, "durations": [],
            }
        summary[tid]["order_count"] += 1
        summary[tid]["revenue"] += sum(_item_val(i) for i in order.items if i.status in ("active", "paid"))
        if order.closed_at and order.opened_at:
            summary[tid]["durations"].append((order.closed_at - order.opened_at).total_seconds() / 60)

    table_list = []
    for entry in summary.values():
        durations = entry.pop("durations")
        entry["avg_duration_minutes"] = round(sum(durations) / len(durations), 1) if durations else None
        entry["revenue"] = round(entry["revenue"], 2)
        table_list.append(entry)
    table_list.sort(key=lambda x: x["revenue"], reverse=True)

    report = {
        "mode": body.mode,
        "period_label": period_label,
        "period_is_workday": period_is_workday,
        "tables": table_list,
        "line_width": printer.line_width,
        "div_style": load_divider_style(db),
    }
    background_tasks.add_task(print_tables_report, printer.ip_address, printer.port, report, printer.codepage_n)
    return {"status": "printing"}


# ---------------------------------------------------------------------------
# Shift history report
# ---------------------------------------------------------------------------

@router.get("/shifts")
def shifts_report(
    waiter_id: Optional[int] = None,
    business_day_id: Optional[int] = None,
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    active_only: bool = False,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    from routers.shifts import _enrich_shift

    q = db.query(WaiterShift)
    if waiter_id:
        q = q.filter(WaiterShift.waiter_id == waiter_id)
    if business_day_id:
        q = q.filter(WaiterShift.business_day_id == business_day_id)
    if from_dt:
        q = q.filter(WaiterShift.started_at >= datetime.fromisoformat(from_dt))
    if to_dt:
        q = q.filter(WaiterShift.started_at <= datetime.fromisoformat(to_dt))
    if active_only:
        q = q.filter(WaiterShift.ended_at == None)

    shifts = q.order_by(WaiterShift.started_at.desc()).all()
    return {"shifts": [_enrich_shift(s, db) for s in shifts]}


# ---------------------------------------------------------------------------
# Product performance analytics
# ---------------------------------------------------------------------------

@router.get("/products/performance")
def product_performance(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    business_day_id: Optional[int] = None,
    category_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    from models.product import Product
    from models.waste import WasteLog

    q = db.query(OrderItem).filter(OrderItem.status.in_(["active", "paid"]))
    if from_dt:
        q = q.filter(OrderItem.added_at >= datetime.fromisoformat(from_dt))
    if to_dt:
        q = q.filter(OrderItem.added_at <= datetime.fromisoformat(to_dt))
    if business_day_id:
        q = q.join(Order).filter(Order.business_day_id == business_day_id)

    items = q.all()
    products_db = {p.id: p for p in db.query(Product).all()}

    # Phase 2H: load waste for the same period
    wq = db.query(WasteLog)
    if business_day_id:
        wq = wq.filter(WasteLog.business_day_id == business_day_id)
    elif from_dt and to_dt:
        wq = wq.filter(
            WasteLog.logged_at >= datetime.fromisoformat(from_dt),
            WasteLog.logged_at <= datetime.fromisoformat(to_dt),
        )
    waste_entries = wq.all()
    waste_by_product: dict = {}
    for w in waste_entries:
        pid = w.product_id
        if pid not in waste_by_product:
            waste_by_product[pid] = {"waste_qty": 0.0, "waste_cost": 0.0}
        waste_by_product[pid]["waste_qty"] += w.quantity
        waste_by_product[pid]["waste_cost"] += w.total_cost or 0.0

    summary: dict = {}
    for item in items:
        pid = item.product_id
        product = products_db.get(pid)
        if category_id and (not product or product.category_id != category_id):
            continue
        if pid not in summary:
            summary[pid] = {
                "product_id": pid,
                "product_name": product.name if product else f"#{pid}",
                "category_id": product.category_id if product else None,
                "qty_sold": 0,
                "revenue": 0.0,
                "total_cost": 0.0,
                "trackable_profit": 0.0,
                "uncosted_revenue": 0.0,
                "uncosted_items": 0,
                "order_ids": set(),
            }
        qty = item.quantity
        revenue = _item_val(item)
        summary[pid]["qty_sold"] += qty
        summary[pid]["revenue"] += revenue
        summary[pid]["order_ids"].add(item.order_id)
        if item.unit_cost is not None:
            cost = item.unit_cost * qty
            summary[pid]["total_cost"] += cost
            summary[pid]["trackable_profit"] += revenue - cost
        else:
            summary[pid]["uncosted_revenue"] += revenue
            summary[pid]["uncosted_items"] += qty

    # Also include products that only have waste (not sold) in the period
    for pid, wdata in waste_by_product.items():
        if pid not in summary:
            product = products_db.get(pid)
            if category_id and (not product or product.category_id != category_id):
                continue
            summary[pid] = {
                "product_id": pid,
                "product_name": product.name if product else f"#{pid}",
                "category_id": product.category_id if product else None,
                "qty_sold": 0, "revenue": 0.0, "total_cost": 0.0, "trackable_profit": 0.0,
                "uncosted_revenue": 0.0, "uncosted_items": 0, "order_ids": set(),
            }

    result = []
    for entry in summary.values():
        pid = entry["product_id"]
        wdata = waste_by_product.get(pid, {"waste_qty": 0.0, "waste_cost": 0.0})
        entry["order_count"] = len(entry.pop("order_ids"))
        entry["revenue"] = round(entry["revenue"], 2)
        entry["total_cost"] = round(entry["total_cost"], 2)
        entry["trackable_profit"] = round(entry["trackable_profit"], 2)
        entry["uncosted_revenue"] = round(entry["uncosted_revenue"], 2)
        entry["has_gap"] = entry["uncosted_items"] > 0
        entry["waste_qty"] = round(wdata["waste_qty"], 3)
        entry["waste_cost"] = round(wdata["waste_cost"], 2)
        result.append(entry)

    result.sort(key=lambda x: x["qty_sold"], reverse=True)
    return {"products": result}


# ---------------------------------------------------------------------------
# Table performance analytics
# ---------------------------------------------------------------------------

@router.get("/tables/performance")
def table_performance(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    business_day_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    q = db.query(Order).filter(Order.status.in_(["closed", "paid"]))
    if from_dt:
        q = q.filter(Order.opened_at >= datetime.fromisoformat(from_dt))
    if to_dt:
        q = q.filter(Order.opened_at <= datetime.fromisoformat(to_dt))
    if business_day_id:
        q = q.filter(Order.business_day_id == business_day_id)
    orders = q.all()

    tables_db = {t.id: t for t in db.query(Table).all()}

    summary: dict = {}
    for order in orders:
        tid = order.table_id
        if tid not in summary:
            t = tables_db.get(tid)
            summary[tid] = {
                "table_id": tid,
                "table_name": (t.label or f"T{t.number}") if t else f"#{tid}",
                "order_count": 0,
                "revenue": 0.0,
                "durations": [],
            }
        summary[tid]["order_count"] += 1
        summary[tid]["revenue"] += sum(
            _item_val(i) for i in order.items if i.status in ("active", "paid")
        )
        if order.closed_at and order.opened_at:
            summary[tid]["durations"].append(
                (order.closed_at - order.opened_at).total_seconds() / 60
            )

    result = []
    for entry in summary.values():
        durations = entry.pop("durations")
        entry["avg_duration_minutes"] = round(sum(durations) / len(durations), 1) if durations else None
        entry["revenue"] = round(entry["revenue"], 2)
        result.append(entry)

    result.sort(key=lambda x: x["revenue"], reverse=True)
    return {"tables": result}


# ---------------------------------------------------------------------------
# Traffic analysis (hour-of-day / day-of-week)
# ---------------------------------------------------------------------------

@router.get("/traffic")
def traffic_analysis(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    business_day_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    q = db.query(Order)
    if from_dt:
        q = q.filter(Order.opened_at >= datetime.fromisoformat(from_dt))
    if to_dt:
        q = q.filter(Order.opened_at <= datetime.fromisoformat(to_dt))
    if business_day_id:
        q = q.filter(Order.business_day_id == business_day_id)
    orders = q.all()

    by_hour = {h: {"hour": h, "orders": 0, "revenue": 0.0} for h in range(24)}
    day_labels = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
    by_weekday = {d: {"day": d, "label": day_labels[d], "orders": 0, "revenue": 0.0} for d in range(7)}

    for order in orders:
        revenue = sum(
            _item_val(i) for i in order.items if i.status in ("active", "paid")
        )
        h = order.opened_at.hour
        d = order.opened_at.weekday()
        by_hour[h]["orders"] += 1
        by_hour[h]["revenue"] += revenue
        by_weekday[d]["orders"] += 1
        by_weekday[d]["revenue"] += revenue

    for h in by_hour:
        by_hour[h]["revenue"] = round(by_hour[h]["revenue"], 2)
    for d in by_weekday:
        by_weekday[d]["revenue"] = round(by_weekday[d]["revenue"], 2)

    return {
        "by_hour": list(by_hour.values()),
        "by_weekday": list(by_weekday.values()),
    }


# ---------------------------------------------------------------------------
# Business days list
# ---------------------------------------------------------------------------

@router.get("/business-days")
def business_days_list(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    q = db.query(BusinessDay)
    if from_dt:
        q = q.filter(BusinessDay.opened_at >= datetime.fromisoformat(from_dt))
    if to_dt:
        q = q.filter(BusinessDay.opened_at <= datetime.fromisoformat(to_dt))
    days = q.order_by(BusinessDay.opened_at.desc()).all()

    waiters_db = {u.id: u for u in db.query(User).all()}
    result = []
    for d in days:
        orders = db.query(Order).filter(Order.business_day_id == d.id).all()
        closed_orders = [o for o in orders if o.status in ("closed", "paid")]
        cancelled_orders = [o for o in orders if o.status == "cancelled"]
        day_shifts = db.query(WaiterShift).filter(WaiterShift.business_day_id == d.id).all()
        waiter_ids = {s.waiter_id for s in day_shifts}
        revenue = 0.0
        total_cost = 0.0
        trackable_profit = 0.0
        has_gap = False
        for o in closed_orders:
            for i in o.items:
                if i.status not in ("active", "paid"):
                    continue
                rev = _item_val(i)
                revenue += rev
                if i.unit_cost is not None:
                    cost = i.unit_cost * i.quantity
                    total_cost += cost
                    trackable_profit += rev - cost
                else:
                    has_gap = True
        opener = waiters_db.get(d.opened_by_id)
        closer = waiters_db.get(d.closed_by_id) if d.closed_by_id else None
        result.append({
            "id": d.id,
            "status": d.status,
            "opened_at": _dt(d.opened_at),
            "closed_at": _dt(d.closed_at),
            "opened_by": (opener.full_name or opener.username) if opener else None,
            "closed_by": (closer.full_name or closer.username) if closer else None,
            "notes": d.notes,
            "order_count": len(orders),
            "closed_order_count": len(closed_orders),
            "cancellation_count": len(cancelled_orders),
            "waiter_count": len(waiter_ids),
            "shift_count": len(day_shifts),
            "revenue": round(revenue, 2),
            "total_cost": round(total_cost, 2),
            "trackable_profit": round(trackable_profit, 2),
            "has_gap": has_gap,
        })
    return {"business_days": result}


@router.get("/business-days/current")
def current_business_day(
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    day = db.query(BusinessDay).filter(BusinessDay.status == "open").order_by(BusinessDay.opened_at.desc()).first()
    if not day:
        return {"business_day": None}

    orders = db.query(Order).filter(Order.business_day_id == day.id).all()
    closed_orders = [o for o in orders if o.status in ("closed", "paid")]
    open_orders = [o for o in orders if o.status in ("open", "partially_paid")]
    cancelled_orders = [o for o in orders if o.status == "cancelled"]
    active_shifts = db.query(WaiterShift).filter(
        WaiterShift.business_day_id == day.id,
        WaiterShift.ended_at == None,
    ).all()
    revenue = 0.0
    total_cost = 0.0
    trackable_profit = 0.0
    has_gap = False
    for o in closed_orders:
        for i in o.items:
            if i.status not in ("active", "paid"):
                continue
            rev = _item_val(i)
            revenue += rev
            if i.unit_cost is not None:
                cost = i.unit_cost * i.quantity
                total_cost += cost
                trackable_profit += rev - cost
            else:
                has_gap = True

    item_counts: dict = {}
    for o in orders:
        if o.status == "cancelled":
            continue
        for item in o.items:
            if item.status in ("active", "paid"):
                pid = item.product_id
                item_counts[pid] = item_counts.get(pid, 0) + item.quantity
    top_product_id = max(item_counts, key=item_counts.get) if item_counts else None
    top_product = None
    if top_product_id:
        from models.product import Product
        p = db.query(Product).filter(Product.id == top_product_id).first()
        top_product = {"id": top_product_id, "name": p.name if p else f"#{top_product_id}", "qty": item_counts[top_product_id]}

    # Count cancelled items (individual items cancelled, across all orders in this day)
    all_order_ids = [o.id for o in orders]
    cancelled_items_qty = 0
    cancellation_events = 0
    if all_order_ids:
        from sqlalchemy import func as sqlfunc
        cancelled_items_qty = db.query(sqlfunc.sum(OrderItem.quantity)).filter(
            OrderItem.order_id.in_(all_order_ids),
            OrderItem.status == "cancelled",
        ).scalar() or 0
        cancellation_events = db.query(sqlfunc.count(OrderItem.id)).filter(
            OrderItem.order_id.in_(all_order_ids),
            OrderItem.status == "cancelled",
        ).scalar() or 0

    return {
        "business_day": {
            "id": day.id,
            "opened_at": _dt(day.opened_at),
            "revenue": round(revenue, 2),
            "total_cost": round(total_cost, 2),
            "trackable_profit": round(trackable_profit, 2),
            "has_gap": has_gap,
            "orders_closed": len(closed_orders),
            "orders_open": len(open_orders),
            "active_waiters": len(active_shifts),
            "cancellations": len(cancelled_orders),
            "cancelled_items": int(cancelled_items_qty),
            "cancellation_events": int(cancellation_events),
            "top_product": top_product,
        }
    }


# ---------------------------------------------------------------------------
# Revenue trends
# ---------------------------------------------------------------------------

@router.get("/revenue/trends")
def revenue_trends(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    granularity: str = Query(default="daily"),  # daily | weekly | monthly
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    q = db.query(Order).filter(Order.status.in_(["closed", "paid"]))
    if from_dt:
        q = q.filter(Order.opened_at >= datetime.fromisoformat(from_dt))
    if to_dt:
        q = q.filter(Order.opened_at <= datetime.fromisoformat(to_dt))
    orders = q.all()

    buckets: dict = {}
    for order in orders:
        dt = to_local(order.opened_at)
        if granularity == "monthly":
            key = dt.strftime("%Y-%m")
        elif granularity == "weekly":
            monday = dt - timedelta(days=dt.weekday())
            key = monday.strftime("%Y-%m-%d")
        else:
            key = dt.strftime("%Y-%m-%d")

        if key not in buckets:
            buckets[key] = {"date": key, "revenue": 0.0, "profit": 0.0, "has_gap": False, "orders": 0}
        for i in order.items:
            if i.status not in ("active", "paid"):
                continue
            rev = _item_val(i)
            buckets[key]["revenue"] += rev
            if i.unit_cost is not None:
                buckets[key]["profit"] += rev - i.unit_cost * i.quantity
            else:
                buckets[key]["has_gap"] = True
        buckets[key]["orders"] += 1

    arr = sorted(buckets.values(), key=lambda x: x["date"])
    for d in arr:
        d["revenue"] = round(d["revenue"], 2)
        d["profit"] = round(d["profit"], 2)

    if granularity == "daily":
        for i, d in enumerate(arr):
            window = arr[max(0, i - 6): i + 1]
            d["rolling7"] = round(sum(x["revenue"] for x in window) / len(window), 2)

    return {"trends": arr, "granularity": granularity}


# ---------------------------------------------------------------------------
# Category performance
# ---------------------------------------------------------------------------

@router.get("/categories/performance")
def category_performance(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    business_day_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    from models.product import Product, Category

    q = db.query(OrderItem).filter(OrderItem.status.in_(["active", "paid"]))
    if from_dt:
        q = q.filter(OrderItem.added_at >= datetime.fromisoformat(from_dt))
    if to_dt:
        q = q.filter(OrderItem.added_at <= datetime.fromisoformat(to_dt))
    if business_day_id:
        q = q.join(Order).filter(Order.business_day_id == business_day_id)
    items = q.all()

    products_db = {p.id: p for p in db.query(Product).all()}
    categories_db = {c.id: c for c in db.query(Category).all()}

    summary: dict = {}
    for item in items:
        product = products_db.get(item.product_id)
        if not product:
            continue
        cid = product.category_id
        if cid not in summary:
            cat = categories_db.get(cid)
            summary[cid] = {
                "category_id": cid,
                "category_name": cat.name if cat else f"#{cid}",
                "color": cat.color if cat and hasattr(cat, "color") else None,
                "units_sold": 0,
                "revenue": 0.0,
                "total_cost": 0.0,
                "trackable_profit": 0.0,
                "uncosted_revenue": 0.0,
                "has_gap": False,
                "product_ids": set(),
            }
        qty = item.quantity
        rev = _item_val(item)
        summary[cid]["units_sold"] += qty
        summary[cid]["revenue"] += rev
        summary[cid]["product_ids"].add(item.product_id)
        if item.unit_cost is not None:
            cost = item.unit_cost * qty
            summary[cid]["total_cost"] += cost
            summary[cid]["trackable_profit"] += rev - cost
        else:
            summary[cid]["uncosted_revenue"] += rev
            summary[cid]["has_gap"] = True

    total_rev = sum(v["revenue"] for v in summary.values())
    total_profit = sum(v["trackable_profit"] for v in summary.values())
    result = []
    for entry in summary.values():
        entry["product_count"] = len(entry.pop("product_ids"))
        entry["revenue"] = round(entry["revenue"], 2)
        entry["total_cost"] = round(entry["total_cost"], 2)
        entry["trackable_profit"] = round(entry["trackable_profit"], 2)
        entry["uncosted_revenue"] = round(entry["uncosted_revenue"], 2)
        entry["pct_rev"] = round((entry["revenue"] / total_rev * 100) if total_rev else 0, 1)
        entry["pct_profit"] = round((entry["trackable_profit"] / total_profit * 100) if total_profit > 0 else 0, 1)
        entry["pct"] = entry["pct_rev"]
        result.append(entry)

    result.sort(key=lambda x: x["revenue"], reverse=True)
    return {"categories": result}


# ---------------------------------------------------------------------------
# Cancellations log
# ---------------------------------------------------------------------------

@router.get("/cancellations")
def cancellations_log(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    business_day_id: Optional[int] = None,
    waiter_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    q = db.query(OrderItem).filter(OrderItem.status == "cancelled")

    # Date filter on cancelled_at if available, otherwise fall back to added_at
    if from_dt:
        dt_from = datetime.fromisoformat(from_dt)
        from sqlalchemy import or_
        q = q.filter(or_(
            OrderItem.cancelled_at >= dt_from,
            (OrderItem.cancelled_at == None) & (OrderItem.added_at >= dt_from),
        ))
    if to_dt:
        dt_to = datetime.fromisoformat(to_dt)
        from sqlalchemy import or_
        q = q.filter(or_(
            OrderItem.cancelled_at <= dt_to,
            (OrderItem.cancelled_at == None) & (OrderItem.added_at <= dt_to),
        ))
    if business_day_id:
        q = q.join(Order, Order.id == OrderItem.order_id).filter(Order.business_day_id == business_day_id)
    if waiter_id:
        # Show items cancelled BY this waiter OR items that were on this waiter's order
        from sqlalchemy import or_
        q = q.filter(or_(
            OrderItem.cancelled_by == waiter_id,
            OrderItem.added_by == waiter_id,
        ))

    items = q.order_by(OrderItem.added_at.desc()).all()
    waiters_db = {u.id: u for u in db.query(User).all()}
    tables_db = {t.id: (t.label or f"T{t.number}") for t in db.query(Table).all()}
    orders_cache = {}

    def _get_order(oid):
        if oid not in orders_cache:
            orders_cache[oid] = db.query(Order).filter(Order.id == oid).first()
        return orders_cache[oid]

    result = []
    for item in items:
        order = _get_order(item.order_id)
        table_name = tables_db.get(order.table_id, f"#{order.table_id}") if order else "—"
        waiter = waiters_db.get(item.added_by)
        waiter_name = (waiter.full_name or waiter.username) if waiter else f"#{item.added_by}"
        cancelled_by_user = waiters_db.get(item.cancelled_by) if item.cancelled_by else None
        cancelled_by_name = (cancelled_by_user.full_name or cancelled_by_user.username) if cancelled_by_user else None
        product_name = item.product.name if item.product else f"#{item.product_id}"
        cancelled_at = getattr(item, "cancelled_at", None)
        result.append({
            "id": item.id,
            "order_id": item.order_id,
            "table": table_name,
            "waiter_name": waiter_name,
            "product_name": product_name,
            "quantity": item.quantity,
            "unit_price": item.unit_price,
            "value": round(_item_val(item), 2),
            "cancelled_by": cancelled_by_name,
            "cancel_reason": getattr(item, "cancel_reason", None),
            "cancelled_at": _dt(cancelled_at) if cancelled_at else _dt(item.added_at),
        })

    total_value = sum(r["value"] for r in result)
    return {"cancellations": result, "total_value": round(total_value, 2)}


# ---------------------------------------------------------------------------
# Printer history (detailed log)
# ---------------------------------------------------------------------------

@router.get("/printers/history")
def printer_history(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    business_day_id: Optional[int] = None,
    printer_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    q = db.query(PrintLog)
    if from_dt:
        q = q.filter(PrintLog.printed_at >= datetime.fromisoformat(from_dt))
    if to_dt:
        q = q.filter(PrintLog.printed_at <= datetime.fromisoformat(to_dt))
    if printer_id:
        q = q.filter(PrintLog.printer_id == printer_id)
    if business_day_id:
        q = q.join(Order).filter(Order.business_day_id == business_day_id)

    logs = q.order_by(PrintLog.printed_at.desc()).all()
    printers_db = {p.id: p for p in db.query(Printer).all()}
    tables_db = {t.id: (t.label or f"T{t.number}") for t in db.query(Table).all()}

    # Cache orders to avoid repeated queries
    order_cache = {}
    def _get_order(oid):
        if oid not in order_cache:
            order_cache[oid] = db.query(Order).filter(Order.id == oid).first()
        return order_cache[oid]

    result = []
    seen_order_totals = {}  # order_id → total, so we don't double-count reprints

    for log in logs:
        order = _get_order(log.order_id)
        printer = printers_db.get(log.printer_id)
        try:
            item_ids = json.loads(log.item_ids)
        except Exception:
            item_ids = []
        if item_ids is None:
            item_ids = []
        items = []
        for iid in item_ids:
            oi = db.query(OrderItem).filter(OrderItem.id == iid).first()
            if oi:
                pname = oi.product.name if oi.product else f"#{oi.product_id}"
                items.append({"name": pname, "quantity": oi.quantity})

        # Compute full order total (all active+paid items, not just printed slice)
        order_total = None
        if order:
            order_total = sum(
                _item_val(i)
                for i in order.items
                if i.status in ("active", "paid")
            )
            if log.success and log.order_id not in seen_order_totals:
                seen_order_totals[log.order_id] = order_total

        result.append({
            "id": log.id,
            "printed_at": _dt(log.printed_at),
            "printer_name": printer.name if printer else f"#{log.printer_id}",
            "order_id": log.order_id,
            "table": tables_db.get(order.table_id, "—") if order else "—",
            "items": items,
            "success": log.success,
            "error_message": log.error_message,
            "order_total": round(order_total, 2) if order_total is not None else None,
        })

    total = len(result)
    failed = sum(1 for r in result if not r["success"])
    # Sum unique order totals (avoid inflating on reprints of the same order)
    total_amount = round(sum(seen_order_totals.values()), 2) if seen_order_totals else None
    return {"logs": result, "total": total, "failed": failed, "total_amount": total_amount}


# ---------------------------------------------------------------------------
# Waiters list (for filter dropdowns)
# ---------------------------------------------------------------------------

@router.get("/meta/waiters")
def meta_waiters(
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    waiters = db.query(User).filter(User.perm_access_waiter_app == True, User.is_active == True).all()
    return {"waiters": [{"id": w.id, "name": w.full_name or w.username} for w in waiters]}


@router.get("/meta/tables")
def meta_tables(
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    tables = db.query(Table).filter(Table.is_active == True).all()
    return {"tables": [{"id": t.id, "name": t.label or f"T{t.number}", "group": t.group.name if t.group else None} for t in tables]}


@router.get("/meta/printers")
def meta_printers(
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    printers = db.query(Printer).filter(Printer.is_active == True).all()
    return {"printers": [{"id": p.id, "name": p.name} for p in printers]}


@router.get("/meta/products")
def meta_products(
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    from models.product import Product, Category
    products = db.query(Product).filter(Product.lifecycle_status == "active").order_by(Product.name).all()
    categories_db = {c.id: c.name for c in db.query(Category).all()}
    return {
        "products": [
            {
                "id": p.id,
                "name": p.name,
                "category_id": p.category_id,
                "category_name": categories_db.get(p.category_id, "—"),
                "price": float(p.base_price),
            }
            for p in products
        ]
    }


# ---------------------------------------------------------------------------
# CSV export endpoints
# ---------------------------------------------------------------------------

def _csv_response(rows: list, filename: str) -> StreamingResponse:
    if not rows:
        rows = [{}]
    output = io.StringIO()
    writer = csv.DictWriter(output, fieldnames=list(rows[0].keys()))
    writer.writeheader()
    writer.writerows(rows)
    output.seek(0)
    return StreamingResponse(
        iter([output.getvalue()]),
        media_type="text/csv",
        headers={"Content-Disposition": f'attachment; filename="{filename}"'},
    )


@router.get("/shifts/export")
def shifts_export(
    waiter_id: Optional[int] = None,
    business_day_id: Optional[int] = None,
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    data = shifts_report(waiter_id=waiter_id, business_day_id=business_day_id, from_dt=from_dt, to_dt=to_dt, active_only=False, db=db, user=user)
    rows = []
    for s in data["shifts"]:
        rows.append({
            "id": s["id"],
            "waiter": s["waiter_name"],
            "started_at": s["started_at"],
            "ended_at": s["ended_at"] or "",
            "duration_hours": s.get("duration_hours", ""),
            "hourly_rate": s.get("hourly_rate_snapshot", ""),
            "shift_pay": s.get("shift_pay", ""),
            "counted_cash_end": s.get("counted_cash_end", ""),
            "cash_discrepancy": s.get("cash_discrepancy", ""),
            "starting_cash": s["starting_cash"],
            "total_collected": s["total_collected"],
            "net_to_deliver": s["net_to_deliver"],
            "status": "active" if s["is_active"] else "ended",
        })
    date_str = (from_dt or "")[:10]
    return _csv_response(rows, f"shifts-{date_str}.csv")


@router.get("/orders/export")
def orders_export(
    from_date: Optional[str] = Query(default=None, alias="from"),
    to_date: Optional[str] = Query(default=None, alias="to"),
    waiter_id: Optional[int] = None,
    order_status: Optional[str] = Query(default=None, alias="status"),
    table_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    orders = order_history(from_date=from_date, to_date=to_date, waiter_id=waiter_id, order_status=order_status, table_id=table_id, page=1, page_size=10000, db=db, user=user)
    tables_db = {t.id: (t.label or f"T{t.number}") for t in db.query(Table).all()}
    waiters_db = {u.id: u for u in db.query(User).all()}
    rows = []
    for o in orders:
        waiter = waiters_db.get(o.opened_by)
        rows.append({
            "id": o.id,
            "table": tables_db.get(o.table_id, ""),
            "waiter": (waiter.full_name or waiter.username) if waiter else "",
            "opened_at": _dt(o.opened_at),
            "closed_at": _dt(o.closed_at) if o.closed_at else "",
            "status": o.status,
            "total": round(sum(_item_val(i) for i in o.items if i.status in ("active", "paid")), 2),
        })
    date_str = (from_date or "")[:10]
    return _csv_response(rows, f"orders-{date_str}.csv")


@router.get("/products/export")
def products_export(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    business_day_id: Optional[int] = None,
    category_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    data = product_performance(from_dt=from_dt, to_dt=to_dt, business_day_id=business_day_id, category_id=category_id, db=db, user=user)
    rows = [{
        "product_id": p["product_id"],
        "product_name": p["product_name"],
        "category_id": p.get("category_id", ""),
        "qty_sold": p["qty_sold"],
        "revenue": p["revenue"],
        "order_count": p["order_count"],
    } for p in data["products"]]
    date_str = (from_dt or "")[:10]
    return _csv_response(rows, f"products-{date_str}.csv")


@router.get("/printers/export")
def printers_export(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    printer_id: Optional[int] = None,
    business_day_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    data = printer_history(from_dt=from_dt, to_dt=to_dt, business_day_id=business_day_id, printer_id=printer_id, db=db, user=user)
    rows = [{
        "id": r["id"],
        "printed_at": r["printed_at"],
        "printer": r["printer_name"],
        "order_id": r["order_id"],
        "table": r["table"],
        "success": r["success"],
        "error_message": r.get("error_message") or "",
    } for r in data["logs"]]
    date_str = (from_dt or "")[:10]
    return _csv_response(rows, f"printer-history-{date_str}.csv")


@router.get("/cancellations/export")
def cancellations_export(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    business_day_id: Optional[int] = None,
    waiter_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    data = cancellations_log(from_dt=from_dt, to_dt=to_dt, business_day_id=business_day_id, waiter_id=waiter_id, db=db, user=user)
    rows = [{
        "id": c["id"],
        "order_id": c["order_id"],
        "table": c["table"],
        "waiter": c["waiter_name"],
        "product": c["product_name"],
        "quantity": c["quantity"],
        "value": c["value"],
        "cancelled_by": c.get("cancelled_by") or "",
        "reason": c.get("cancel_reason") or "",
        "cancelled_at": c["cancelled_at"],
    } for c in data["cancellations"]]
    date_str = (from_dt or "")[:10]
    return _csv_response(rows, f"cancellations-{date_str}.csv")


# ── Prep Zones report ──────────────────────────────────────────────────────────

@router.get("/prep-zones")
def prep_zones_report(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    business_day_id: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    """
    Per-prep-zone stats: item totals, full product list, and full order list
    for the requested date range / business day.
    """
    zones = db.query(PrepZone).order_by(PrepZone.id).all()

    # Build product → zones mapping
    product_zone_map: dict[int, list[int]] = {}
    for zone in zones:
        for product in zone.products:
            if product.id not in product_zone_map:
                product_zone_map[product.id] = []
            product_zone_map[product.id].append(zone.id)

    # Query order items in the requested range
    q = db.query(OrderItem).join(Order)
    if from_dt:
        q = q.filter(Order.opened_at >= datetime.fromisoformat(from_dt))
    if to_dt:
        q = q.filter(Order.opened_at <= datetime.fromisoformat(to_dt))
    if business_day_id:
        q = q.filter(Order.business_day_id == business_day_id)
    q = q.filter(OrderItem.status.in_(["active", "paid"]))
    items = q.all()

    # Aggregate per zone
    zone_stats: dict[int, dict] = {
        zone.id: {
            "id": zone.id,
            "name": zone.name,
            "notification_name": zone.notification_name,
            "printers": [{"id": p.id, "name": p.name} for p in zone.printers],
            "item_count": 0,
            "total_value": 0.0,
            "product_counts": {},   # product_name → {count, value}
            "orders_map": {},       # order_id → {order_id, table, opened_at, items_count, value}
        }
        for zone in zones
    }

    for item in items:
        zids = product_zone_map.get(item.product_id, [])
        pname = item.product.name if item.product else f"#{item.product_id}"
        ivalue = round(_item_val(item), 2)
        order = item.order
        table_label = None
        if order and order.table:
            t = order.table
            table_label = t.label or str(t.number)
        for zid in zids:
            if zid not in zone_stats:
                continue
            zs = zone_stats[zid]
            zs["item_count"] += item.quantity
            zs["total_value"] += ivalue
            if pname not in zs["product_counts"]:
                zs["product_counts"][pname] = {"count": 0, "value": 0.0}
            zs["product_counts"][pname]["count"] += item.quantity
            zs["product_counts"][pname]["value"] += ivalue
            if order:
                oid = order.id
                if oid not in zs["orders_map"]:
                    zs["orders_map"][oid] = {
                        "order_id": oid,
                        "table": table_label or f"#{oid}",
                        "opened_at": order.opened_at.isoformat() if order.opened_at else None,
                        "items_count": 0,
                        "value": 0.0,
                    }
                zs["orders_map"][oid]["items_count"] += item.quantity
                zs["orders_map"][oid]["value"] += ivalue

    result = []
    for zs in zone_stats.values():
        all_products = sorted(
            [
                {"name": k, "count": v["count"], "value": round(v["value"], 2)}
                for k, v in zs["product_counts"].items()
            ],
            key=lambda x: -x["count"],
        )
        all_orders = sorted(
            [
                {**o, "value": round(o["value"], 2)}
                for o in zs["orders_map"].values()
            ],
            key=lambda x: x["opened_at"] or "",
        )
        result.append({
            "id": zs["id"],
            "name": zs["name"],
            "notification_name": zs["notification_name"],
            "printers": zs["printers"],
            "item_count": zs["item_count"],
            "total_value": round(zs["total_value"], 2),
            "all_products": all_products,
            "all_orders": all_orders,
        })

    return {"zones": result}


class PrintPrepZoneBody(BaseModel):
    printer_id: int
    zone_id: int
    from_dt: Optional[str] = None
    to_dt: Optional[str] = None
    business_day_id: Optional[int] = None


@router.post("/print/prep-zone")
def print_prep_zone(
    body: PrintPrepZoneBody,
    background_tasks: BackgroundTasks,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    printer = db.query(Printer).filter(Printer.id == body.printer_id, Printer.is_active == True).first()
    if not printer:
        raise HTTPException(status_code=404, detail="Printer not found or inactive")

    zone = db.query(PrepZone).filter(PrepZone.id == body.zone_id).first()
    if not zone:
        raise HTTPException(status_code=404, detail="Prep zone not found")

    # Resolve period label
    if body.business_day_id:
        from models.business_day import BusinessDay
        bd = db.query(BusinessDay).filter(BusinessDay.id == body.business_day_id).first()
        period_label = bd.opened_at.strftime("%d/%m/%Y") if bd and bd.opened_at else f"#{body.business_day_id}"
    elif body.from_dt and body.to_dt:
        d1 = datetime.fromisoformat(body.from_dt).strftime("%d/%m/%Y")
        d2 = datetime.fromisoformat(body.to_dt).strftime("%d/%m/%Y")
        period_label = f"{d1} - {d2}"
    else:
        period_label = "Όλες"

    # Build product→zone mapping for this zone
    product_ids = {product.id for product in zone.products}

    q = db.query(OrderItem).join(Order)
    if body.from_dt:
        q = q.filter(Order.opened_at >= datetime.fromisoformat(body.from_dt))
    if body.to_dt:
        q = q.filter(Order.opened_at <= datetime.fromisoformat(body.to_dt))
    if body.business_day_id:
        q = q.filter(Order.business_day_id == body.business_day_id)
    q = q.filter(OrderItem.status.in_(["active", "paid"]))
    items = q.all()

    product_counts: dict[str, dict] = {}
    for item in items:
        if item.product_id not in product_ids:
            continue
        pname = item.product.name if item.product else f"#{item.product_id}"
        if pname not in product_counts:
            product_counts[pname] = {"count": 0, "value": 0.0}
        product_counts[pname]["count"] += item.quantity
        product_counts[pname]["value"] += _item_val(item)

    report_items = sorted(
        [{"name": k, "count": v["count"], "value": round(v["value"], 2)} for k, v in product_counts.items()],
        key=lambda x: -x["count"],
    )

    report = {
        "zone_name": zone.name,
        "period_label": period_label,
        "items": report_items,
        "line_width": printer.line_width,
        "div_style": load_divider_style(db),
    }
    background_tasks.add_task(print_prep_zone_summary, printer.ip_address, printer.port, report, printer.codepage_n)
    return {"status": "printing"}


# ---------------------------------------------------------------------------
# Phase 2L — Discount audit report
# ---------------------------------------------------------------------------

@router.get("/discounts")
def discounts_report(
    from_dt: Optional[str] = Query(default=None, alias="from"),
    to_dt: Optional[str] = Query(default=None, alias="to"),
    business_day_id: Optional[int] = None,
    applied_by: Optional[int] = None,
    db: Session = Depends(get_db),
    user: User = Depends(require_reports),
):
    from models.order import OrderDiscount, OrderItem

    q = db.query(OrderDiscount)
    if business_day_id:
        q = q.join(Order, Order.id == OrderDiscount.order_id).filter(Order.business_day_id == business_day_id)
    elif from_dt and to_dt:
        q = q.filter(
            OrderDiscount.applied_at >= datetime.fromisoformat(from_dt),
            OrderDiscount.applied_at <= datetime.fromisoformat(to_dt),
        )
    if applied_by:
        q = q.filter(OrderDiscount.applied_by == applied_by)

    discounts = q.order_by(OrderDiscount.applied_at.desc()).all()

    users_map = {u.id: u for u in db.query(User).all()}
    tables_map = {t.id: (t.label or f"T{t.number}") for t in db.query(Table).all()}

    # Preload orders for table names
    order_ids = {d.order_id for d in discounts}
    orders_map = {}
    if order_ids:
        for o in db.query(Order).filter(Order.id.in_(order_ids)).all():
            orders_map[o.id] = o

    def _compute_discount_amount(d: OrderDiscount) -> float:
        """Compute the actual euro amount discounted."""
        order = orders_map.get(d.order_id)
        if d.discount_type == "fixed":
            return round(d.discount_value, 2)
        if d.discount_type == "percent":
            if d.item_id:
                item = db.query(OrderItem).filter(OrderItem.id == d.item_id).first()
                if item:
                    base = _item_val(item)
                    return round(base * d.discount_value / 100, 2)
            elif order:
                base = sum(_item_val(i) for i in order.items if i.status != "cancelled")
                return round(base * d.discount_value / 100, 2)
        return 0.0

    result = []
    total_discount_value = 0.0
    by_waiter: dict = {}

    for d in discounts:
        order = orders_map.get(d.order_id)
        applier = users_map.get(d.applied_by)
        applier_name = (applier.full_name or applier.username) if applier else f"#{d.applied_by}"
        table_name = tables_map.get(order.table_id) if order and order.table_id else None
        order_total = sum(_item_val(i) for i in order.items if i.status != "cancelled") if order else None
        discount_amount = _compute_discount_amount(d)
        total_discount_value += discount_amount

        if d.applied_by not in by_waiter:
            by_waiter[d.applied_by] = {"waiter_id": d.applied_by, "waiter_name": applier_name, "count": 0, "total_value": 0.0}
        by_waiter[d.applied_by]["count"] += 1
        by_waiter[d.applied_by]["total_value"] = round(by_waiter[d.applied_by]["total_value"] + discount_amount, 2)

        result.append({
            "id": d.id,
            "order_id": d.order_id,
            "table_name": table_name,
            "applied_by_id": d.applied_by,
            "applied_by_name": applier_name,
            "applied_at": _dt(d.applied_at),
            "discount_type": d.discount_type,
            "discount_value": d.discount_value,
            "discount_amount": discount_amount,
            "order_total_before": round(order_total, 2) if order_total is not None else None,
            "item_id": d.item_id,
            "reason": d.reason,
        })

    return {
        "discounts": result,
        "total_discount_value": round(total_discount_value, 2),
        "order_count": len({d["order_id"] for d in result}),
        "by_waiter": sorted(by_waiter.values(), key=lambda x: -x["total_value"]),
    }
