import base64
import binascii
import datetime as dt
import uuid
from urllib.parse import quote

from fastapi import APIRouter, Depends, HTTPException, Response
from pydantic import BaseModel
from sqlalchemy import func, select
from sqlalchemy.orm import Session

from flyway.api.deps import current_actor, get_db
from flyway.api.lists import IdsIn, Page, like, nz_range, page_params, paginate
from flyway.domain.actor import Actor
from flyway.domain.clock import NZ, utcnow
from flyway.domain.errors import BusinessError
from flyway.models import ShippingParcel
from flyway.services.parcels import (
    ATTENTION_DAYS, ParcelData, attentions_xlsx, delete_parcels, import_parcels, needs_attention, operator_names,
    restore_parcels, save_parcel, ship,
)

router = APIRouter(prefix="/api/parcels")
MAX_IMPORT_BYTES = 10 * 1024 * 1024


class ParcelIn(BaseModel):
    barcode: str
    is_shipped: bool = False
    shipped_utc: dt.datetime | None = None
    comment: str | None = None
    bg_color: str | None = None
    font_color: str | None = None
    operator_id: uuid.UUID | None = None


class DeleteIn(IdsIn):
    physical: bool = False


class ImportIn(BaseModel):
    filename: str
    content_base64: str
    auto_merge: bool = False
    operator_id: uuid.UUID | None = None


def parcel_out(p: ShippingParcel, names: dict, now: dt.datetime) -> dict:
    return {"id": p.id, "barcode": p.barcode, "comment": p.comment, "bg_color": p.bg_color,
            "font_color": p.font_color, "created_utc": p.created_utc, "shipped_utc": p.shipped_utc,
            "deleted_utc": p.deleted_utc, "operator_id": p.operator_id, "operator_name": names.get(p.operator_id),
            "is_shipped": p.shipped_utc is not None, "is_deleted": p.deleted_utc is not None,
            "needs_attention": needs_attention(p, now)}


def _out(db: Session, p: ShippingParcel) -> dict:
    return parcel_out(p, operator_names(db, [p]), utcnow())


@router.get("")
def list_parcels(status: str = "active", keyword: str | None = None, operator_id: uuid.UUID | None = None,
                 created_from: dt.date | None = None, created_to: dt.date | None = None,
                 shipped_from: dt.date | None = None, shipped_to: dt.date | None = None,
                 deleted_from: dt.date | None = None, deleted_to: dt.date | None = None,
                 attention: bool = False, page: Page = Depends(page_params), db: Session = Depends(get_db)):
    q = select(ShippingParcel)
    shipped, deleted = ShippingParcel.shipped_utc, ShippingParcel.deleted_utc
    if status == "active":
        q = q.where(shipped.is_(None), deleted.is_(None))
    elif status == "shipped":
        q = q.where(shipped.is_not(None), deleted.is_(None))
    elif status == "deleted":
        q = q.where(deleted.is_not(None))
    elif status != "all":
        raise HTTPException(status_code=422, detail="Unknown status.")
    if attention:
        q = q.where(ShippingParcel.created_utc <= utcnow() - dt.timedelta(days=ATTENTION_DAYS))
    if keyword:
        q = q.where(like(ShippingParcel.barcode, keyword) | like(ShippingParcel.comment, keyword))
    if operator_id:
        q = q.where(ShippingParcel.operator_id == operator_id)
    for column, start_d, end_d in ((ShippingParcel.created_utc, created_from, created_to),
                                   (shipped, shipped_from, shipped_to), (deleted, deleted_from, deleted_to)):
        start, end = nz_range(start_d, end_d)
        if start:
            q = q.where(column >= start)
        if end:
            q = q.where(column < end)
    sorts = {"barcode": ShippingParcel.barcode, "created": ShippingParcel.created_utc, "shipped": shipped,
             "deleted": deleted}
    rows, total = paginate(db, q, page, sorts, "-created")
    names, now = operator_names(db, rows), utcnow()
    return {"items": [parcel_out(p, names, now) for p in rows], "total": total}


@router.get("/attention-count")
def attention_count(db: Session = Depends(get_db)):
    return {"count": db.scalar(select(func.count()).where(
        ShippingParcel.shipped_utc.is_(None), ShippingParcel.deleted_utc.is_(None),
        ShippingParcel.created_utc <= utcnow() - dt.timedelta(days=ATTENTION_DAYS)))}


@router.get("/attentions.xlsx")
def export_attentions(db: Session = Depends(get_db)):
    name = f"Shipping Parcel Attentions {dt.datetime.now(NZ):%Y-%m-%d %I-%M %p}.xlsx"
    return Response(attentions_xlsx(db), media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    headers={"Content-Disposition": f"attachment; filename*=UTF-8''{quote(name)}"})


@router.get("/locate")
def locate(barcode: str, db: Session = Depends(get_db)):
    """扫码页：按条码（不区分大小写、去首尾空格）查找；找不到返回 found=false。"""
    p = db.scalar(select(ShippingParcel).where(func.lower(func.trim(ShippingParcel.barcode)) == barcode.strip().lower())
                  .limit(1))
    return {"found": False} if p is None else {"found": True, "parcel": _out(db, p)}


@router.get("/{parcel_id}")
def get_parcel(parcel_id: uuid.UUID, db: Session = Depends(get_db)):
    p = db.get(ShippingParcel, parcel_id)
    if p is None:
        raise HTTPException(status_code=404)
    return _out(db, p)


@router.post("")
def create(body: ParcelIn, db: Session = Depends(get_db), actor: Actor = Depends(current_actor)):
    p = save_parcel(db, actor, ParcelData(**body.model_dump()))
    db.commit()
    return _out(db, p)


@router.put("/{parcel_id}")
def update(parcel_id: uuid.UUID, body: ParcelIn, db: Session = Depends(get_db), actor: Actor = Depends(current_actor)):
    p = save_parcel(db, actor, ParcelData(**body.model_dump()), parcel_id)
    db.commit()
    return _out(db, p)


@router.post("/{parcel_id}/ship")
def ship_parcel(parcel_id: uuid.UUID, db: Session = Depends(get_db)):
    p = ship(db, parcel_id)
    db.commit()
    return _out(db, p)


@router.post("/delete", status_code=204)
def remove(body: DeleteIn, db: Session = Depends(get_db)):
    delete_parcels(db, body.ids, body.physical)
    db.commit()


@router.post("/restore", status_code=204)
def restore(body: IdsIn, db: Session = Depends(get_db)):
    restore_parcels(db, body.ids)
    db.commit()


@router.post("/import")
def import_file(body: ImportIn, db: Session = Depends(get_db), actor: Actor = Depends(current_actor)):
    try:
        content = base64.b64decode(body.content_base64, validate=True)
    except (binascii.Error, ValueError) as e:
        raise BusinessError("Invalid file content.") from e
    if len(content) > MAX_IMPORT_BYTES:
        raise BusinessError("The file is too large (max 10 MB).")
    result = import_parcels(db, actor, body.filename, content, body.auto_merge, body.operator_id)
    db.commit()
    rows = result.imported + result.merged
    names, now = operator_names(db, rows), utcnow()
    return {"total_count": result.total_count, "imported": [parcel_out(p, names, now) for p in result.imported],
            "merged": [parcel_out(p, names, now) for p in result.merged]}