from typing import Optional
import csv
import io
import secrets
import zipfile
from decimal import Decimal, InvalidOperation

from fastapi import APIRouter, Depends, File, HTTPException, UploadFile
from fastapi.responses import Response
from sqlalchemy import func, select
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session

from .. import config, schemas, security, serialize
from ..db import get_db
from ..models import (Category, Product, PurchaseLine, PurchaseOrder, StockMovement,
                      Supplier, User, now)
from ..services import audit, locked_products, move_stock

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

MAX_IMPORT = 5 * 1024 * 1024
MAX_ROWS = 5000
MAX_IMAGE = 3 * 1024 * 1024


# ------------------------------------------------------------------ products

@router.get("/products")
def list_products(q: str = "", category: Optional[int] = None, filter: str = "", page: int = 1, per_page: int = 50,
                  db: Session = Depends(get_db)):
    per_page = max(1, min(per_page, 200))
    stmt = select(Product)
    if q.strip():
        like = f"%{q.strip()}%"
        stmt = stmt.where(Product.name.ilike(like) | Product.sku.ilike(like) | Product.brand.ilike(like))
    if category:
        stmt = stmt.where(Product.category_id == category)
    if filter == "low":
        stmt = stmt.where(Product.active, Product.stock <= Product.reorder_level, Product.stock > 0)
    elif filter == "out":
        stmt = stmt.where(Product.active, Product.stock == 0)
    elif filter == "hidden":
        stmt = stmt.where(~Product.active)
    total = db.scalar(select(func.count()).select_from(stmt.subquery()))
    rows = db.scalars(stmt.order_by(Product.name).offset((max(page, 1) - 1) * per_page).limit(per_page))
    return {"total": total, "page": page, "per_page": per_page, "items": [serialize.product(p, True) for p in rows]}


def _check_refs(db, body):
    if body.category_id and not db.get(Category, body.category_id):
        raise HTTPException(400, "Choose a department from the list.")
    if body.supplier_id and not db.get(Supplier, body.supplier_id):
        raise HTTPException(400, "Choose a supplier from the list.")


@router.post("/products")
def create_product(body: schemas.ProductIn, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    _check_refs(db, body)
    data = body.model_dump(exclude={"stock"})
    p = Product(**data, stock=0)
    db.add(p)
    try:
        db.flush()
    except IntegrityError:
        db.rollback()
        raise HTTPException(409, "Another product already uses that SKU.")
    if body.stock:
        move_stock(db, p, body.stock, "adjust", ref="opening stock", by=user.name)
    audit(db, user.email, "product_create", f"{p.sku} {p.name}")
    db.commit()
    return serialize.product(p, True)


@router.put("/products/{pid}")
def update_product(pid: int, body: schemas.ProductIn, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    p = locked_products(db, [pid]).get(pid)
    if not p:
        raise HTTPException(404, "Product not found.")
    _check_refs(db, body)
    before = (p.price, p.cost)
    for k, v in body.model_dump(exclude={"stock"}).items():
        setattr(p, k, v)
    if body.stock != p.stock:
        move_stock(db, p, body.stock - p.stock, "adjust", ref="edited on product page", by=user.name)
    if before != (p.price, p.cost):
        audit(db, user.email, "price_change", f"{p.sku}: price {before[0]}->{p.price}, cost {before[1]}->{p.cost}")
    audit(db, user.email, "product_update", f"{p.sku} {p.name}")
    try:
        db.commit()
    except IntegrityError:
        db.rollback()
        raise HTTPException(409, "Another product already uses that SKU.")
    return serialize.product(p, True)


@router.delete("/products/{pid}")
def hide_product(pid: int, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    """Products with sales history are hidden, never deleted, so old orders stay readable."""
    p = db.get(Product, pid)
    if not p:
        raise HTTPException(404, "Product not found.")
    p.active = False
    audit(db, user.email, "product_hide", f"{p.sku} {p.name}")
    db.commit()
    return {"ok": True}


@router.post("/products/{pid}/stock")
def adjust_stock(pid: int, body: schemas.StockAdjust, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    p = locked_products(db, [pid]).get(pid)
    if not p:
        raise HTTPException(404, "Product not found.")
    if body.change == 0:
        raise HTTPException(400, "Enter a number other than zero.")
    move_stock(db, p, body.change, "adjust", ref=body.reason, by=user.name)
    audit(db, user.email, "stock_adjust", f"{p.sku} {body.change:+d}: {body.reason}")
    db.commit()
    return serialize.product(p, True)


@router.get("/products/{pid}/movements")
def movements(pid: int, db: Session = Depends(get_db)):
    rows = db.scalars(select(StockMovement).where(StockMovement.product_id == pid).order_by(StockMovement.id.desc()).limit(50))
    return [{"at": m.at.isoformat(), "change": m.change, "after": m.after, "reason": m.reason, "ref": m.ref, "by": m.by}
            for m in rows]


# -------------------------------------------------------------------- images

def _image_kind(b: bytes) -> Optional[str]:
    if b[:3] == b"\xff\xd8\xff":
        return ".jpg"
    if b[:8] == b"\x89PNG\r\n\x1a\n":
        return ".png"
    if b[:4] == b"RIFF" and b[8:12] == b"WEBP":
        return ".webp"
    return None


@router.post("/products/{pid}/image")
async def upload_image(pid: int, file: UploadFile = File(...), db: Session = Depends(get_db),
                       user: User = Depends(security.staff_user)):
    p = db.get(Product, pid)
    if not p:
        raise HTTPException(404, "Product not found.")
    data = await file.read(MAX_IMAGE + 1)
    if len(data) > MAX_IMAGE:
        raise HTTPException(400, "That picture is bigger than 3 MB. Please use a smaller one.")
    ext = _image_kind(data)  # judged by the file's real bytes, not its name
    if not ext:
        raise HTTPException(400, "Upload a JPG, PNG or WebP picture.")
    config.UPLOAD_DIR.mkdir(parents=True, exist_ok=True)
    name = f"p{pid}-{secrets.token_hex(5)}{ext}"
    (config.UPLOAD_DIR / name).write_bytes(data)
    if p.image_url.startswith("/uploads/"):
        old = config.UPLOAD_DIR / p.image_url.removeprefix("/uploads/")
        if old.parent == config.UPLOAD_DIR and old.exists():
            old.unlink()
    p.image_url = f"/uploads/{name}"
    audit(db, user.email, "product_image", f"{p.sku}")
    db.commit()
    return serialize.product(p, True)


# -------------------------------------------------------------------- import

TEMPLATE = "sku,name,stock,price,cost\nGP-SPAG-500,Golden Penny Spaghetti,60,18.50,13.20\n"


@router.get("/import/template.csv")
def import_template():
    return Response("" + TEMPLATE, media_type="text/csv; charset=utf-8",
                    headers={"Content-Disposition": 'attachment; filename="stock-upload-template.csv"'})


def read_rows(content: bytes, filename: str) -> list[dict]:
    name = filename.lower()
    if name.endswith((".csv", ".tsv", ".txt")):
        try:
            text = content.decode("utf-8-sig")
        except UnicodeDecodeError:
            text = content.decode("cp1252")
        delim = "\t" if name.endswith(".tsv") else (";" if text.split("\n", 1)[0].count(";") > text.split("\n", 1)[0].count(",") else ",")
        rows = list(csv.reader(io.StringIO(text), delimiter=delim))
    elif name.endswith(".xlsx"):
        from openpyxl import load_workbook
        try:
            wb = load_workbook(io.BytesIO(content), read_only=True, data_only=True)
            rows = [["" if c is None else str(c) for c in r] for r in wb.worksheets[0].iter_rows(values_only=True)]
        except (zipfile.BadZipFile, KeyError, ValueError, OSError):
            raise HTTPException(400, "This Excel file could not be read. Save it as .xlsx (or CSV) and try again.")
    else:
        raise HTTPException(400, "Use a CSV, TSV or Excel (.xlsx) file.")
    rows = [r for r in rows if any(str(c).strip() for c in r)]
    if len(rows) < 2:
        raise HTTPException(400, "The file has no data rows. The first row must be the column names.")
    if len(rows) - 1 > MAX_ROWS:
        raise HTTPException(400, f"Please upload at most {MAX_ROWS} rows at a time.")
    head = [str(h).strip().lower() for h in rows[0]]
    return [{head[i]: str(v).strip() for i, v in enumerate(r) if i < len(head)} for r in rows[1:]]


def _num(v, kind):
    try:
        d = Decimal(v.replace(",", ""))
    except (InvalidOperation, AttributeError):
        raise ValueError
    if d < 0:
        raise ValueError
    if kind is int:
        if d != d.to_integral_value():
            raise ValueError
        return int(d)
    return d.quantize(Decimal("0.01"))


@router.post("/import")
async def import_stock(apply: bool = False, create_new: bool = False, file: UploadFile = File(...),
                       db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    """apply=false only checks the file and shows what would change."""
    content = await file.read(MAX_IMPORT + 1)
    if len(content) > MAX_IMPORT:
        raise HTTPException(400, "That file is bigger than 5 MB.")
    rows = read_rows(content, file.filename or "")
    if not any(k in rows[0] for k in ("sku", "id", "name")):
        raise HTTPException(400, "The first row needs a 'sku' (or 'id' or 'name') column, plus 'stock' and/or 'price'.")
    if not any(k in rows[0] for k in ("stock", "price", "cost")):
        raise HTTPException(400, "Add at least one of these columns: stock, price, cost.")
    by_sku = {p.sku.lower(): p for p in db.scalars(select(Product).with_for_update())}
    by_id = {str(p.id): p for p in by_sku.values()}
    by_name = {p.name.lower(): p for p in by_sku.values()}
    changes, problems, seen = [], [], set()
    for n, r in enumerate(rows, start=2):
        p = by_sku.get(r.get("sku", "").lower()) if r.get("sku") else None
        p = p or (by_id.get(r.get("id", "")) if r.get("id") else None) or (by_name.get(r.get("name", "").lower()) if r.get("name") else None)
        try:
            new = {}
            if r.get("stock", "") != "":
                new["stock"] = _num(r["stock"], int)
            if r.get("price", "") != "":
                new["price"] = _num(r["price"], Decimal)
            if r.get("cost", "") != "":
                new["cost"] = _num(r["cost"], Decimal)
        except ValueError:
            problems.append({"row": n, "what": r.get("sku") or r.get("name") or r.get("id") or "?", "problem": "A number is not valid (no negatives, whole numbers for stock)."})
            continue
        if not p:
            if create_new and r.get("sku") and r.get("name") and "price" in new:
                changes.append({"row": n, "new": True, "sku": r["sku"], "name": r["name"], **{k: float(v) for k, v in new.items()},
                                "_data": {"sku": r["sku"], "name": r["name"], **new}})
            else:
                problems.append({"row": n, "what": r.get("sku") or r.get("name") or r.get("id") or "?",
                                 "problem": "No matching product." + ("" if create_new else " Tick 'add new products' to create it.")})
            continue
        if p.id in seen:
            problems.append({"row": n, "what": p.sku, "problem": "This product appears twice in the file. Only the first row was used."})
            continue
        seen.add(p.id)
        changes.append({"row": n, "new": False, "sku": p.sku, "name": p.name,
                        "stock_from": p.stock, "price_from": float(p.price), "cost_from": float(p.cost),
                        **{k: float(v) for k, v in new.items()}, "_p": p, "_data": new})
    if apply:
        for c in changes:
            data = c["_data"]
            if c["new"]:
                p = Product(sku=data["sku"], name=data["name"], price=data["price"], cost=data.get("cost", 0), stock=0)
                db.add(p)
                db.flush()
                if data.get("stock"):
                    move_stock(db, p, data["stock"], "import", ref=file.filename or "", by=user.name)
            else:
                p = c["_p"]
                if "stock" in data and data["stock"] != p.stock:
                    move_stock(db, p, data["stock"] - p.stock, "import", ref=file.filename or "", by=user.name)
                if "price" in data:
                    p.price = data["price"]
                if "cost" in data:
                    p.cost = data["cost"]
        audit(db, user.email, "stock_import", f"{file.filename}: {len(changes)} applied, {len(problems)} skipped")
        db.commit()
    for c in changes:
        c.pop("_p", None)
        c.pop("_data", None)
    return {"applied": apply, "changes": changes, "problems": problems,
            "message": (f"Updated {len(changes)} product(s)." if apply else f"{len(changes)} product(s) will change.")
                       + (f" {len(problems)} row(s) skipped." if problems else "")}


# ---------------------------------------------------------------- categories

@router.get("/categories")
def admin_categories(db: Session = Depends(get_db)):
    rows = db.execute(select(Category, func.count(Product.id)).outerjoin(Product).group_by(Category.id)
                      .order_by(Category.sort, Category.name)).all()
    return [{"id": c.id, "name": c.name, "sort": c.sort, "products": n} for c, n in rows]


@router.post("/categories")
def add_category(body: schemas.CategoryIn, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    c = Category(name=body.name.strip(), sort=body.sort)
    db.add(c)
    try:
        db.flush()
    except IntegrityError:
        db.rollback()
        raise HTTPException(409, "That department already exists.")
    audit(db, user.email, "category_create", c.name)
    db.commit()
    return {"id": c.id, "name": c.name, "sort": c.sort, "products": 0}


@router.put("/categories/{cid}")
def edit_category(cid: int, body: schemas.CategoryIn, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    c = db.get(Category, cid)
    if not c:
        raise HTTPException(404, "Department not found.")
    c.name, c.sort = body.name.strip(), body.sort
    try:
        db.commit()
    except IntegrityError:
        db.rollback()
        raise HTTPException(409, "That department already exists.")
    return {"id": c.id, "name": c.name, "sort": c.sort}


@router.delete("/categories/{cid}")
def delete_category(cid: int, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    c = db.get(Category, cid)
    if not c:
        raise HTTPException(404, "Department not found.")
    if db.scalar(select(func.count()).where(Product.category_id == cid)):
        raise HTTPException(400, "Move this department's products elsewhere first.")
    audit(db, user.email, "category_delete", c.name)
    db.delete(c)
    db.commit()
    return {"ok": True}


# ----------------------------------------------------------------- suppliers

def _sup(s: Supplier):
    return {"id": s.id, "name": s.name, "contact": s.contact, "phone": s.phone, "email": s.email,
            "country": s.country, "lead_days": s.lead_days}


@router.get("/suppliers")
def suppliers(db: Session = Depends(get_db)):
    return [_sup(s) for s in db.scalars(select(Supplier).order_by(Supplier.name))]


@router.post("/suppliers")
def add_supplier(body: schemas.SupplierIn, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    s = Supplier(**body.model_dump())
    db.add(s)
    try:
        db.flush()
    except IntegrityError:
        db.rollback()
        raise HTTPException(409, "A supplier with that name already exists.")
    audit(db, user.email, "supplier_create", s.name)
    db.commit()
    return _sup(s)


@router.put("/suppliers/{sid}")
def edit_supplier(sid: int, body: schemas.SupplierIn, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    s = db.get(Supplier, sid)
    if not s:
        raise HTTPException(404, "Supplier not found.")
    for k, v in body.model_dump().items():
        setattr(s, k, v)
    try:
        db.commit()
    except IntegrityError:
        db.rollback()
        raise HTTPException(409, "A supplier with that name already exists.")
    return _sup(s)


# ----------------------------------------------------------- purchase orders

def _po(o: PurchaseOrder):
    return {"id": o.id, "number": o.number, "supplier": o.supplier.name, "supplier_id": o.supplier_id,
            "status": o.status, "note": o.note, "created_at": o.created_at.isoformat(),
            "received_at": o.received_at.isoformat() if o.received_at else None,
            "total": float(sum(l.unit_cost * l.quantity for l in o.lines)),
            "lines": [{"product_id": l.product_id, "name": l.product.name, "sku": l.product.sku,
                       "quantity": l.quantity, "unit_cost": float(l.unit_cost)} for l in o.lines]}


@router.get("/purchase-orders")
def purchase_orders(db: Session = Depends(get_db)):
    return [_po(o) for o in db.scalars(select(PurchaseOrder).order_by(PurchaseOrder.id.desc()).limit(100))]


@router.get("/reorder-suggestions")
def reorder_suggestions(db: Session = Depends(get_db)):
    """What to buy now: sized from how fast each product really sells and how long its supplier takes."""
    from .. import forecast
    demand = {d["id"]: d for d in forecast.product_demand(db)}
    rows = db.scalars(select(Product).where(Product.active).order_by(Product.stock, Product.name))
    out = []
    for p in rows:
        d = demand[p.id]
        if d["suggested"] <= 0 and not (p.stock <= p.reorder_level and not d["on_order"]):
            continue
        out.append({**serialize.product(p, True), "suggested": max(d["suggested"], 1),
                    "supplier": d["supplier"], "per_day": d["per_day"], "days_left": d["days_left"],
                    "on_order": d["on_order"], "risk": d["risk"], "lead_days": d["lead_days"], "reliable": d["reliable"]})
    return out


@router.post("/purchase-orders")
def create_po(body: schemas.POIn, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    if not db.get(Supplier, body.supplier_id):
        raise HTTPException(400, "Choose a supplier.")
    ids = [l.product_id for l in body.lines]
    if len(set(ids)) != len(ids):
        raise HTTPException(400, "Each product can appear only once on an order.")
    found = {p.id for p in db.scalars(select(Product).where(Product.id.in_(ids)))}
    if found != set(ids):
        raise HTTPException(400, "One of the products does not exist.")
    n = (db.scalar(select(func.max(PurchaseOrder.id))) or 0) + 1
    po = PurchaseOrder(number=f"PO-{now():%y%m}-{n:04d}", supplier_id=body.supplier_id, note=body.note.strip(),
                       lines=[PurchaseLine(**l.model_dump()) for l in body.lines])
    db.add(po)
    audit(db, user.email, "po_create", po.number)
    db.commit()
    return _po(po)


@router.post("/purchase-orders/{poid}/receive")
def receive_po(poid: int, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    po = db.scalar(select(PurchaseOrder).where(PurchaseOrder.id == poid).with_for_update())
    if not po:
        raise HTTPException(404, "Purchase order not found.")
    if po.status != "ordered":
        raise HTTPException(400, f"This order is already {po.status}.")
    products = locked_products(db, [l.product_id for l in po.lines])
    for l in po.lines:
        p = products[l.product_id]
        move_stock(db, p, l.quantity, "purchase", ref=po.number, by=user.name)
        p.cost = l.unit_cost
    po.status, po.received_at = "received", now()
    audit(db, user.email, "po_receive", po.number)
    db.commit()
    return _po(po)


@router.post("/purchase-orders/{poid}/cancel")
def cancel_po(poid: int, db: Session = Depends(get_db), user: User = Depends(security.staff_user)):
    po = db.get(PurchaseOrder, poid)
    if not po or po.status != "ordered":
        raise HTTPException(400, "Only an order that is still waiting can be cancelled.")
    po.status = "cancelled"
    audit(db, user.email, "po_cancel", po.number)
    db.commit()
    return _po(po)