from typing import Optional
import csv
import io
from datetime import date, datetime, time, timedelta, timezone
from decimal import Decimal

from fastapi import APIRouter, Depends, HTTPException
from fastapi.responses import StreamingResponse
from sqlalchemy import func, select
from sqlalchemy.orm import Session, selectinload

from .. import payments, schemas, security, serialize
from ..db import get_db
from ..models import AuditLog, Order, OrderItem, Product, PurchaseOrder, StockMovement, User, now
from ..services import LABEL, STATUSES, audit, change_status

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


def money(x):
    return float(x or 0)


def pct(a, b):
    return round((a - b) / b * 100, 1) if b else None


def day_start(d: date) -> datetime:
    return datetime.combine(d, time.min, tzinfo=timezone.utc)


# ----------------------------------------------------------------- dashboard

@router.get("/dashboard")
def dashboard(days: int = 30, db: Session = Depends(get_db)):
    payments.expire_unpaid(db)
    days = 7 if days < 7 else min(days, 90)
    today = now().date()
    live = Order.status != "cancelled"
    start = day_start(today - timedelta(days=days - 1))
    rows = db.execute(select(func.date(Order.created_at), func.sum(Order.total), func.count())
                      .where(live, Order.created_at >= start).group_by(func.date(Order.created_at))).all()
    by_day = {r[0]: (money(r[1]), r[2]) for r in rows}
    series = []
    for i in range(days):
        d = today - timedelta(days=days - 1 - i)
        s, c = by_day.get(d, (0.0, 0))
        series.append({"date": d.isoformat(), "sales": s, "orders": c})
    s_today, o_today = by_day.get(today, (0.0, 0))
    s_yest, o_yest = by_day.get(today - timedelta(days=1), (0.0, 0))
    period_sales = sum(x["sales"] for x in series)
    half = days // 2
    recent_half = sum(x["sales"] for x in series[-half:])
    prior_half = sum(x["sales"] for x in series[-2 * half:-half])
    period_orders = sum(x["orders"] for x in series)

    cost = db.scalar(select(func.sum(OrderItem.unit_cost * OrderItem.quantity)).join(Order)
                     .where(live, Order.created_at >= start)) or 0
    revenue_items = db.scalar(select(func.sum(OrderItem.unit_price * OrderItem.quantity)).join(Order)
                              .where(live, Order.created_at >= start)) or 0
    gross_profit = money(revenue_items) - money(cost)

    counts = dict(db.execute(select(Order.status, func.count()).group_by(Order.status)).all())
    status_counts = [{"status": s, "label": LABEL[s], "count": counts.get(s, 0)} for s in STATUSES]

    top = db.execute(select(OrderItem.name, func.sum(OrderItem.quantity), func.sum(OrderItem.unit_price * OrderItem.quantity))
                     .join(Order).where(live, Order.created_at >= start).group_by(OrderItem.name)
                     .order_by(func.sum(OrderItem.unit_price * OrderItem.quantity).desc()).limit(6)).all()
    cats = db.execute(select(OrderItem.category, func.sum(OrderItem.unit_price * OrderItem.quantity))
                      .join(Order).where(live, Order.created_at >= start).group_by(OrderItem.category)
                      .order_by(func.sum(OrderItem.unit_price * OrderItem.quantity).desc())).all()

    per_customer = db.execute(select(Order.phone, func.count()).where(live, Order.created_at >= start)
                              .group_by(Order.phone)).all()
    returning = sum(1 for _, c in per_customer if c > 1)
    unique = len(per_customer)

    active = Product.active
    low = db.scalars(select(Product).where(active, Product.stock <= Product.reorder_level)
                     .order_by(Product.stock, Product.name).limit(12)).all()
    low_count = db.scalar(select(func.count()).where(active, Product.stock <= Product.reorder_level, Product.stock > 0))
    out_count = db.scalar(select(func.count()).where(active, Product.stock == 0))
    stock_cost = db.scalar(select(func.sum(Product.cost * Product.stock)).where(active)) or 0
    stock_retail = db.scalar(select(func.sum(Product.price * Product.stock)).where(active)) or 0

    recent = db.scalars(select(Order).options(selectinload(Order.items), selectinload(Order.events))
                        .order_by(Order.id.desc()).limit(8)).all()
    action = db.scalar(select(func.count()).where(Order.status.in_(["pending", "confirmed", "packed"])))
    unpaid = db.scalar(select(func.count()).where(Order.payment_method == "momo", Order.payment_status == "unpaid",
                                                  Order.status != "cancelled"))
    return {
        "days": days,
        "kpis": {
            "sales_today": s_today, "sales_vs_yesterday": pct(s_today, s_yest),
            "orders_today": o_today, "orders_vs_yesterday": o_today - o_yest,
            "period_sales": period_sales, "period_trend": pct(recent_half, prior_half),
            "period_orders": period_orders,
            "average_order": round(period_sales / period_orders, 2) if period_orders else 0,
            "gross_profit": round(gross_profit, 2),
            "margin_pct": round(gross_profit / money(revenue_items) * 100, 1) if revenue_items else None,
            "returning_pct": round(returning / unique * 100) if unique else None,
            "orders_to_action": action, "momo_unpaid": unpaid,
            "refunds_due": db.scalar(select(func.count()).where(Order.payment_status == "refund_due")),
            "low_stock": low_count, "out_of_stock": out_count,
            "stock_cost": money(stock_cost), "stock_retail": money(stock_retail),
            "no_image": db.scalar(select(func.count()).where(active, Product.image_url == "")),
        },
        "series": series,
        "status_counts": status_counts,
        "top_products": [{"name": n, "units": int(u), "revenue": money(r)} for n, u, r in top],
        "category_sales": [{"name": n or "Other", "revenue": money(r)} for n, r in cats],
        "low_stock": [serialize.product(p, True) for p in low],
        "recent_orders": [serialize.order(o, True) for o in recent],
    }


# -------------------------------------------------------------------- orders

@router.get("/orders")
def orders(status: str = "", q: str = "", payment: str = "", page: int = 1, per_page: int = 25,
           db: Session = Depends(get_db)):
    payments.expire_unpaid(db)
    per_page = max(1, min(per_page, 100))
    stmt = select(Order)
    if status:
        stmt = stmt.where(Order.status == status)
    if payment:
        stmt = stmt.where(Order.payment_status == payment)
    if q.strip():
        like = f"%{q.strip()}%"
        stmt = stmt.where(Order.number.ilike(like) | Order.customer_name.ilike(like) | Order.phone.ilike(like))
    total = db.scalar(select(func.count()).select_from(stmt.subquery()))
    rows = db.scalars(stmt.options(selectinload(Order.items), selectinload(Order.events))
                      .order_by(Order.id.desc()).offset((max(page, 1) - 1) * per_page).limit(per_page)).all()
    return {"total": total, "page": page, "per_page": per_page, "items": [serialize.order(o, True) for o in rows]}


def _order(db, number) -> Order:
    o = db.scalar(select(Order).where(Order.number == number).with_for_update())
    if not o:
        raise HTTPException(404, "Order not found.")
    return o


@router.get("/orders/{number}")
def order_detail(number: str, db: Session = Depends(get_db)):
    o = db.scalar(select(Order).where(Order.number == number))
    if not o:
        raise HTTPException(404, "Order not found.")
    return serialize.order(o, True)


@router.post("/orders/{number}/status")
def order_status(number: str, body: schemas.StatusChange, db: Session = Depends(get_db),
                 user: User = Depends(security.staff_user)):
    o = _order(db, number)
    change_status(db, o, body.status, user.name, body.note)
    audit(db, user.email, "order_status", f"{o.number} -> {body.status}")
    db.commit()
    return serialize.order(o, True)


@router.post("/orders/{number}/payment")
def order_payment(number: str, body: schemas.PaymentChange, db: Session = Depends(get_db),
                  user: User = Depends(security.staff_user)):
    if body.payment_status not in ("paid", "unpaid", "refunded"):
        raise HTTPException(400, "Payment must be paid, unpaid or refunded.")
    o = _order(db, number)
    if body.payment_status == "refunded":
        if o.payment_status != "refund_due":
            raise HTTPException(400, "Only an order marked 'refund due' can be marked refunded.")
    elif o.status == "cancelled" or o.payment_status == "refund_due":
        raise HTTPException(400, "This order is cancelled.")
    if o.payment_method == "online" and body.payment_status == "unpaid" and o.payment_status == "paid":
        raise HTTPException(400, "This was paid online. To give the money back, cancel the order and refund it in your Paystack dashboard.")
    o.payment_status = body.payment_status
    o.payment_ref = body.payment_ref.strip()
    audit(db, user.email, "order_payment", f"{o.number} {body.payment_status} {o.payment_ref}")
    db.commit()
    return serialize.order(o, True)


# ------------------------------------------------------------------- reports

def _safe(v):
    """Stop spreadsheet formulas: a cell that starts with = + - @ is shown as text."""
    if isinstance(v, str) and v[:1] in ("=", "+", "-", "@", "\t", "\r"):
        return "'" + v
    return v


def _plain(v):
    if isinstance(v, Decimal):
        return float(v)
    if isinstance(v, datetime):
        return v.strftime("%Y-%m-%d %H:%M")
    if isinstance(v, date):
        return v.isoformat()
    return v


def _file(name: str, ext: str, data, media: str) -> StreamingResponse:
    return StreamingResponse(iter([data]), media_type=media,
                             headers={"Content-Disposition": f'attachment; filename="{name}-{now().date()}.{ext}"'})


def _csv(name: str, header: list[str], rows) -> StreamingResponse:
    out = io.StringIO()
    w = csv.writer(out)
    w.writerow(header)
    for r in rows:
        w.writerow([_safe(_plain(c)) for c in r])
    return _file(name, "csv", "" + out.getvalue(), "text/csv; charset=utf-8")  # BOM so Excel reads UTF-8


def _xlsx(name: str, header: list[str], rows) -> StreamingResponse:
    from openpyxl import Workbook
    from openpyxl.styles import Font
    wb = Workbook()
    ws = wb.active
    ws.title = name[:30]
    ws.append(header)
    for c in ws[1]:
        c.font = Font(bold=True)
    for r in rows:
        ws.append([_safe(_plain(c)) for c in r])
    for i, h in enumerate(header, 1):
        width = max([len(str(h))] + [len(str(ws.cell(row=n, column=i).value or "")) for n in range(2, min(ws.max_row, 200) + 1)])
        ws.column_dimensions[ws.cell(row=1, column=i).column_letter].width = min(width + 3, 50)
    ws.freeze_panes = "A2"
    buf = io.BytesIO()
    wb.save(buf)
    return _file(name, "xlsx", buf.getvalue(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")


# key: (label, group, what it tells you, uses the date range?)
REPORTS = {
    "sales-by-day": ("Sales by day", "Sales", "Orders, goods, delivery and total for each day.", True),
    "orders": ("All orders", "Sales", "Every order with customer, area, status and payment.", True),
    "top-products": ("Best-selling products", "Sales", "Units, revenue and profit for each product sold.", True),
    "profit-by-department": ("Profit by department", "Sales", "Revenue, cost, profit and margin for each department.", True),
    "delivery-areas": ("Delivery areas", "Sales", "Orders and money for each delivery area.", True),
    "customers": ("Customers", "Customers & money", "Who bought, how often and how much. Best customers first.", True),
    "payments": ("Payments", "Customers & money", "How each order was paid and whether the money is in.", True),
    "inventory": ("Inventory (every product)", "Stock", "Every product with stock, cost, price and shelf value.", False),
    "low-stock": ("Low and out-of-stock", "Stock", "What to reorder, with a suggested amount.", False),
    "slow-movers": ("Slow movers (not selling)", "Stock", "Products on the shelf with no sales in the dates chosen. Money tied up.", True),
    "demand-forecast": ("Demand forecast and what to order", "Stock", "Expected sales for the next 14 days, when each product runs out, and how much to order.", False),
    "stock-movements": ("Stock movements (every change)", "Stock", "Every stock change with who did it and why.", True),
    "purchase-orders": ("Supplier orders", "Stock", "Orders placed with suppliers and what they cost.", True),
}


@router.get("/reports")
def report_list():
    return [{"key": k, "label": v[0], "group": v[1], "about": v[2], "dates": v[3]} for k, v in REPORTS.items()]


def _build(key: str, db: Session, lo: datetime, hi: datetime):
    live = Order.status != "cancelled"
    in_range = (Order.created_at >= lo, Order.created_at < hi)
    if key == "inventory":
        rows = db.scalars(select(Product).order_by(Product.name)).all()
        return (["SKU", "Name", "Brand", "Size", "Category", "Price", "Cost", "Stock", "Reorder level",
                 "Stock value (cost)", "Stock value (retail)", "Active"],
                [[p.sku, p.name, p.brand, p.size, p.category.name if p.category else "", p.price, p.cost, p.stock,
                  p.reorder_level, p.cost * p.stock, p.price * p.stock, "yes" if p.active else "no"] for p in rows])
    if key == "low-stock":
        rows = db.scalars(select(Product).where(Product.active, Product.stock <= Product.reorder_level)
                          .order_by(Product.stock)).all()
        return (["SKU", "Name", "Stock", "Reorder level", "Suggested order"],
                [[p.sku, p.name, p.stock, p.reorder_level, max(p.reorder_level * 3 - p.stock, 0)] for p in rows])
    if key == "sales-by-day":
        rows = db.execute(select(func.date(Order.created_at), func.count(), func.sum(Order.subtotal),
                                 func.sum(Order.delivery_fee), func.sum(Order.total))
                          .where(live, *in_range).group_by(func.date(Order.created_at))
                          .order_by(func.date(Order.created_at))).all()
        return ["Date", "Orders", "Goods", "Delivery", "Total"], [list(r) for r in rows]
    if key == "orders":
        rows = db.scalars(select(Order).where(*in_range).order_by(Order.id)).all()
        return (["Order", "Date", "Customer", "Phone", "Area", "Status", "Payment", "Paid?", "Goods", "Delivery", "Total"],
                [[o.number, o.created_at.date(), o.customer_name, o.phone, o.zone_name, LABEL[o.status],
                  o.payment_method, o.payment_status, o.subtotal, o.delivery_fee, o.total] for o in rows])
    if key == "top-products":
        rows = db.execute(select(OrderItem.name, func.sum(OrderItem.quantity), func.sum(OrderItem.unit_price * OrderItem.quantity),
                                 func.sum((OrderItem.unit_price - OrderItem.unit_cost) * OrderItem.quantity))
                          .join(Order).where(live, *in_range)
                          .group_by(OrderItem.name).order_by(func.sum(OrderItem.unit_price * OrderItem.quantity).desc())).all()
        return ["Product", "Units", "Revenue", "Gross profit"], [list(r) for r in rows]
    if key == "profit-by-department":
        rows = db.execute(select(OrderItem.category, func.sum(OrderItem.quantity), func.sum(OrderItem.unit_price * OrderItem.quantity),
                                 func.sum(OrderItem.unit_cost * OrderItem.quantity))
                          .join(Order).where(live, *in_range).group_by(OrderItem.category)
                          .order_by(func.sum(OrderItem.unit_price * OrderItem.quantity).desc())).all()
        return (["Department", "Units", "Revenue", "Cost", "Profit", "Margin %"],
                [[c or "Other", u, r, k, money(r) - money(k), round((money(r) - money(k)) / money(r) * 100, 1) if money(r) else ""]
                 for c, u, r, k in rows])
    if key == "delivery-areas":
        rows = db.execute(select(Order.zone_name, func.count(), func.sum(Order.subtotal), func.sum(Order.delivery_fee), func.sum(Order.total))
                          .where(live, *in_range).group_by(Order.zone_name).order_by(func.sum(Order.total).desc())).all()
        return ["Area", "Orders", "Goods", "Delivery fees", "Total"], [[r[0] or "Unknown", *r[1:]] for r in rows]
    if key == "customers":
        rows = db.execute(select(Order.phone, func.max(Order.customer_name), func.count(), func.sum(Order.total),
                                 func.min(Order.created_at), func.max(Order.created_at))
                          .where(live, *in_range).group_by(Order.phone).order_by(func.sum(Order.total).desc())).all()
        return (["Phone", "Name", "Orders", "Spent", "Average order", "First order", "Last order"],
                [[p, n, c, t, round(money(t) / c, 2), a.date(), b.date()] for p, n, c, t, a, b in rows])
    if key == "payments":
        rows = db.scalars(select(Order).where(live, *in_range).order_by(Order.id)).all()
        return (["Order", "Date", "Customer", "Method", "Payment status", "Reference", "Total"],
                [[o.number, o.created_at.date(), o.customer_name, o.payment_method, o.payment_status, o.payment_ref, o.total]
                 for o in rows])
    if key == "slow-movers":
        sold = {pid for (pid,) in db.execute(select(OrderItem.product_id).join(Order).where(live, *in_range).distinct()).all()}
        rows = db.scalars(select(Product).where(Product.active, Product.stock > 0).order_by(Product.name)).all()
        rows = [p for p in rows if p.id not in sold]
        rows.sort(key=lambda p: -(p.cost * p.stock))
        return (["SKU", "Name", "Category", "Stock", "Cost", "Money tied up (cost)", "Shelf value (retail)"],
                [[p.sku, p.name, p.category.name if p.category else "", p.stock, p.cost, p.cost * p.stock, p.price * p.stock]
                 for p in rows])
    if key == "demand-forecast":
        from .. import forecast
        risk = {"out": "Sold out", "urgent": "Runs out before an order can arrive", "soon": "Order soon", "ok": "Fine", "no_sales": "Not selling"}
        rows = sorted(forecast.product_demand(db), key=lambda r: (list(risk).index(r["risk"]), r["days_left"] or 9999))
        return (["SKU", "Name", "Stock", "Sells per day", "Expected next 14 days", "Days left", "Runs out on", "Supplier",
                 "Delivery days", "Already ordered", "Order now", "Status"],
                [[r["sku"], r["name"], r["stock"], r["per_day"], r["expected"], r["days_left"] if r["days_left"] is not None else "",
                  r["runs_out_on"] or "", r["supplier"], r["lead_days"], r["on_order"], r["suggested"], risk[r["risk"]]] for r in rows])
    if key == "stock-movements":
        rows = db.scalars(select(StockMovement).where(StockMovement.at >= lo, StockMovement.at < hi)
                          .order_by(StockMovement.id)).all()
        return (["When", "SKU", "Product", "Change", "Stock after", "Reason", "Reference", "By"],
                [[m.at.strftime("%Y-%m-%d %H:%M"), m.product.sku, m.product.name, m.change, m.after, m.reason, m.ref, m.by]
                 for m in rows])
    if key == "purchase-orders":
        rows = db.scalars(select(PurchaseOrder).where(PurchaseOrder.created_at >= lo, PurchaseOrder.created_at < hi)
                          .order_by(PurchaseOrder.id)).all()
        return (["Number", "Date", "Supplier", "Status", "Lines", "Value", "Received"],
                [[po.number, po.created_at.date(), po.supplier.name, po.status, len(po.lines),
                  sum(money(l.unit_cost) * l.quantity for l in po.lines), po.received_at.date() if po.received_at else ""]
                 for po in rows])
    raise HTTPException(404, "No such report.")


def _range(start, end):
    end = end or now().date()
    start = start or end - timedelta(days=29)
    if start > end or (end - start).days > 366:
        raise HTTPException(400, "Choose a date range of up to one year.")
    return start, end, day_start(start), day_start(end + timedelta(days=1))


@router.get("/reports/{key}/preview")
def report_preview(key: str, start: Optional[date] = None, end: Optional[date] = None, db: Session = Depends(get_db)):
    _, _, lo, hi = _range(start, end)
    header, rows = _build(key, db, lo, hi)
    return {"label": REPORTS[key][0], "header": header, "total": len(rows),
            "rows": [[_plain(c) for c in r] for r in rows[:100]]}


@router.get("/reports/{key}.{fmt}")
def report(key: str, fmt: str, start: Optional[date] = None, end: Optional[date] = None, db: Session = Depends(get_db),
           user: User = Depends(security.staff_user)):
    if fmt not in ("csv", "xlsx"):
        raise HTTPException(404, "No such report.")
    start, end, lo, hi = _range(start, end)
    header, rows = _build(key, db, lo, hi)
    audit(db, user.email, "report", f"{key}.{fmt} {start}..{end}")
    db.commit()
    return (_csv if fmt == "csv" else _xlsx)(key, header, rows)


# --------------------------------------------------------------------- audit

@router.get("/audit")
def audit_log(page: int = 1, db: Session = Depends(get_db), user: User = Depends(security.owner_user)):
    rows = db.scalars(select(AuditLog).order_by(AuditLog.id.desc()).offset((max(page, 1) - 1) * 50).limit(50))
    return [{"at": a.at.isoformat(), "actor": a.actor, "action": a.action, "detail": a.detail} for a in rows]