"""Owner analytics: trends, customers, profit, and the inventory picture. Read-only.

Rows are fetched once and totalled in Python so the same code gives the same numbers on PostgreSQL and MySQL
(no date_trunc / hour() / weekday() differences). A shop this size has far fewer rows than that would matter for.
"""
from collections import defaultdict
from datetime import timedelta

from fastapi import APIRouter, Depends
from sqlalchemy import func, select
from sqlalchemy.orm import Session

from .. import forecast, payments, security
from ..db import get_db
from ..models import Category, Order, OrderEvent, OrderItem, Product, StockMovement, now
from .admin import day_start, money, pct

router = APIRouter(prefix="/api/admin", dependencies=[Depends(security.staff_user)])

WEEKDAYS = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]


def _period(days: int) -> int:
    return days if days in (7, 30, 90, 180, 365) else 30


def _line_rows(db: Session, start, end=None):
    """One row per sold line: (order created_at, order id, category, name, qty, price, cost)."""
    stmt = (select(Order.created_at, Order.id, OrderItem.category, OrderItem.name, OrderItem.quantity,
                   OrderItem.unit_price, OrderItem.unit_cost)
            .join(Order, Order.id == OrderItem.order_id)
            .where(Order.status != "cancelled", Order.created_at >= start))
    if end is not None:
        stmt = stmt.where(Order.created_at < end)
    return db.execute(stmt).all()


def _totals(db: Session, start, end):
    orders = db.execute(select(Order.total).where(Order.status != "cancelled", Order.created_at >= start,
                                                  Order.created_at < end)).all()
    lines = _line_rows(db, start, end)
    sales = sum(money(o[0]) for o in orders)
    profit = sum((money(l[5]) - money(l[6])) * l[4] for l in lines)
    return {"sales": round(sales, 2), "orders": len(orders), "profit": round(profit, 2),
            "basket": round(sales / len(orders), 2) if orders else 0}


@router.get("/insights")
def insights(days: int = 30, db: Session = Depends(get_db)):
    payments.expire_unpaid(db)
    days = _period(days)
    today = now().date()
    start = day_start(today - timedelta(days=days - 1))
    end = day_start(today + timedelta(days=1))
    prev_start = day_start(today - timedelta(days=2 * days - 1))

    cur, prev = _totals(db, start, end), _totals(db, prev_start, start)
    growth = {k: pct(cur[k], prev[k]) for k in ("sales", "orders", "profit", "basket")}

    orders = db.scalars(select(Order).where(Order.created_at >= start, Order.created_at < end)).all()
    live = [o for o in orders if o.status != "cancelled"]
    lines = _line_rows(db, start, end)

    # weekday + hour rhythm
    wd = [{"label": w, "sales": 0.0, "orders": 0} for w in WEEKDAYS]
    hr = [{"label": f"{h:02d}", "sales": 0.0, "orders": 0} for h in range(24)]
    for o in live:
        w, h = wd[o.created_at.weekday()], hr[o.created_at.hour]
        w["sales"] += money(o.total)
        w["orders"] += 1
        h["sales"] += money(o.total)
        h["orders"] += 1
    for r in wd + hr:
        r["sales"] = round(r["sales"], 2)

    # profit by department and by product
    cat: dict = defaultdict(lambda: [0.0, 0.0, 0])
    prod: dict = defaultdict(lambda: [0.0, 0.0, 0])
    for _, _, c, name, q, price, cost in lines:
        rev, prof = money(price) * q, (money(price) - money(cost)) * q
        for d, key in ((cat, c or "Other"), (prod, name)):
            d[key][0] += rev
            d[key][1] += prof
            d[key][2] += q
    by_cat = sorted(({"name": k, "revenue": round(v[0], 2), "profit": round(v[1], 2), "units": v[2],
                      "margin": round(v[1] / v[0] * 100, 1) if v[0] else None} for k, v in cat.items()),
                    key=lambda r: -r["revenue"])
    prod_rows = [{"name": k, "revenue": round(v[0], 2), "profit": round(v[1], 2), "units": v[2],
                  "margin": round(v[1] / v[0] * 100, 1) if v[0] else None} for k, v in prod.items()]
    most_profit = sorted(prod_rows, key=lambda r: -r["profit"])[:8]
    thin_margin = sorted((r for r in prod_rows if r["units"] >= 3 and r["margin"] is not None),
                         key=lambda r: r["margin"])[:6]

    # payments
    labels = {"momo": "Mobile Money (customer sends)", "online": "Online (card / MoMo)", "cod": "Pay on delivery"}
    mix: dict = defaultdict(lambda: [0, 0.0])
    for o in live:
        mix[o.payment_method][0] += 1
        mix[o.payment_method][1] += money(o.total)
    pay_mix = [{"name": labels.get(k, k), "orders": v[0], "value": round(v[1], 2)} for k, v in mix.items()]
    collected = sum(money(o.total) for o in live if o.payment_status == "paid")
    owed = sum(money(o.total) for o in live if o.payment_status != "paid")
    refunds = [o for o in orders if o.payment_status == "refund_due"]

    # delivery areas
    zones: dict = defaultdict(lambda: [0, 0.0])
    for o in live:
        zones[o.zone_name or "Unknown"][0] += 1
        zones[o.zone_name or "Unknown"][1] += money(o.total)
    by_zone = sorted(({"name": k, "orders": v[0], "value": round(v[1], 2)} for k, v in zones.items()),
                     key=lambda r: -r["value"])

    # customers
    seen_before = {r[0] for r in db.execute(select(Order.phone).where(Order.status != "cancelled",
                                                                       Order.created_at < start)).all()}
    spend: dict = defaultdict(lambda: [0, 0.0, ""])
    for o in live:
        spend[o.phone][0] += 1
        spend[o.phone][1] += money(o.total)
        spend[o.phone][2] = o.customer_name
    new_c = sum(1 for p in spend if p not in seen_before)
    top_customers = sorted(({"name": v[2], "phone": p, "orders": v[0], "spent": round(v[1], 2)}
                            for p, v in spend.items()), key=lambda r: -r["spent"])[:8]

    # fulfilment
    cancelled = sum(1 for o in orders if o.status == "cancelled")
    ids = [o.id for o in orders if o.status == "delivered"]
    hours = []
    if ids:
        made = {o.id: o.created_at for o in orders}
        for oid, at in db.execute(select(OrderEvent.order_id, OrderEvent.at)
                                  .where(OrderEvent.order_id.in_(ids), OrderEvent.status == "delivered")).all():
            hours.append((at - made[oid]).total_seconds() / 3600)

    # month by month (12 months) from the same year-long pull
    m_start = day_start((today.replace(day=1) - timedelta(days=330)).replace(day=1))
    m_orders = db.execute(select(Order.created_at, Order.total).where(Order.status != "cancelled",
                                                                     Order.created_at >= m_start)).all()
    m_lines = _line_rows(db, m_start)
    months: dict = {}
    y, m = m_start.year, m_start.month
    while (y, m) <= (today.year, today.month):
        months[f"{y}-{m:02d}"] = {"label": f"{y}-{m:02d}", "sales": 0.0, "orders": 0, "profit": 0.0}
        y, m = (y + 1, 1) if m == 12 else (y, m + 1)
    for at, total in m_orders:
        r = months.get(f"{at.year}-{at.month:02d}")
        if r:
            r["sales"] += money(total)
            r["orders"] += 1
    for at, _, _, _, q, price, cost in m_lines:
        r = months.get(f"{at.year}-{at.month:02d}")
        if r:
            r["profit"] += (money(price) - money(cost)) * q
    month_rows = [{**r, "sales": round(r["sales"], 2), "profit": round(r["profit"], 2)} for r in months.values()]

    return {
        "days": days, "current": cur, "previous": prev, "growth": growth,
        "months": month_rows, "weekdays": wd, "hours": hr,
        "category_profit": by_cat, "most_profit": most_profit, "thin_margin": thin_margin,
        "payment_mix": pay_mix, "collected": round(collected, 2), "owed": round(owed, 2), "refunds_due": len(refunds),
        "zones": by_zone,
        "customers": {"unique": len(spend), "new": new_c, "returning": len(spend) - new_c,
                      "repeat": sum(1 for v in spend.values() if v[0] > 1), "top": top_customers},
        "fulfilment": {"cancelled": cancelled, "cancel_rate": round(cancelled / len(orders) * 100, 1) if orders else None,
                       "delivered": len(ids), "avg_hours": round(sum(hours) / len(hours), 1) if hours else None},
    }


@router.get("/forecast")
def forecast_sales(horizon: int = 14, db: Session = Depends(get_db)):
    return forecast.sales_forecast(db, horizon)


@router.get("/forecast/products")
def forecast_products(horizon: int = 14, db: Session = Depends(get_db)):
    rows = forecast.product_demand(db, horizon)
    order = {"out": 0, "urgent": 1, "soon": 2, "ok": 3, "no_sales": 4}
    rows.sort(key=lambda r: (order[r["risk"]], r["days_left"] if r["days_left"] is not None else 9999, r["name"]))
    return {"horizon": max(7, min(horizon, 56)), "items": rows,
            "summary": {k: sum(1 for r in rows if r["risk"] == k) for k in order},
            "to_order": sum(1 for r in rows if r["suggested"] > 0)}


@router.get("/inventory/overview")
def inventory_overview(db: Session = Depends(get_db)):
    today = now().date()
    start = day_start(today - timedelta(days=29))
    products = db.scalars(select(Product).where(Product.active)).all()
    sold: dict = defaultdict(int)
    for pid, q in db.execute(select(OrderItem.product_id, func.sum(OrderItem.quantity)).join(Order, Order.id == OrderItem.order_id)
                             .where(Order.status != "cancelled", Order.created_at >= start)
                             .group_by(OrderItem.product_id)).all():
        sold[pid] = int(q or 0)

    cats = {c.id: c.name for c in db.scalars(select(Category))}
    cost_v = sum(money(p.cost) * p.stock for p in products)
    retail_v = sum(money(p.price) * p.stock for p in products)
    out = [p for p in products if p.stock == 0]
    low = [p for p in products if 0 < p.stock <= p.reorder_level]

    def row(p, extra=None):
        rate = sold[p.id] / 30
        base = {"id": p.id, "sku": p.sku, "name": p.name, "brand": p.brand, "image_url": p.image_url, "size": p.size,
                "stock": p.stock, "reorder_level": p.reorder_level, "sold_30": sold[p.id],
                "cover_days": round(p.stock / rate) if rate else None,
                "cost_value": round(money(p.cost) * p.stock, 2), "retail_value": round(money(p.price) * p.stock, 2)}
        return {**base, **(extra or {})}

    by_cat: dict = defaultdict(lambda: [0, 0, 0.0, 0.0])
    for p in products:
        r = by_cat[cats.get(p.category_id, "Other")]
        r[0] += 1
        r[1] += p.stock
        r[2] += money(p.cost) * p.stock
        r[3] += money(p.price) * p.stock

    running_out = sorted((row(p) for p in products if sold[p.id] and 0 < p.stock and p.stock / (sold[p.id] / 30) <= 14),
                         key=lambda r: r["cover_days"])[:10]
    dead = sorted((row(p) for p in products if p.stock > 0 and sold[p.id] == 0), key=lambda r: -r["cost_value"])[:10]
    movers = sorted((row(p) for p in products if sold[p.id]), key=lambda r: -r["sold_30"])[:10]
    biggest = sorted((row(p) for p in products), key=lambda r: -r["cost_value"])[:10]
    moves = db.scalars(select(StockMovement).order_by(StockMovement.id.desc()).limit(15)).all()
    return {
        "kpis": {"products": len(products), "units": sum(p.stock for p in products),
                 "cost_value": round(cost_v, 2), "retail_value": round(retail_v, 2),
                 "potential_profit": round(retail_v - cost_v, 2),
                 "out_of_stock": len(out), "low_stock": len(low),
                 "dead_stock_value": round(sum(r["cost_value"] for r in
                                               (row(p) for p in products if p.stock > 0 and sold[p.id] == 0)), 2)},
        "by_category": sorted(({"name": k, "products": v[0], "units": v[1], "cost_value": round(v[2], 2),
                                "retail_value": round(v[3], 2)} for k, v in by_cat.items()), key=lambda r: -r["cost_value"]),
        "running_out": running_out, "dead_stock": dead, "fast_movers": movers, "biggest_value": biggest,
        "out_of_stock": [row(p) for p in sorted(out, key=lambda p: p.name)[:12]],
        "movements": [{"at": m.at.isoformat(), "sku": m.product.sku, "name": m.product.name, "change": m.change,
                       "after": m.after, "reason": m.reason, "ref": m.ref, "by": m.by} for m in moves],
    }