import datetime as dt

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

from flyway.api.deps import get_db
from flyway.api.views import slip_row, transfer_row
from flyway.api.routes_parcels import parcel_out
from flyway.domain.clock import utcnow
from flyway.models import PackingSlip, Product, ProductBatch, ShippingParcel, Transfer, TransferItem
from flyway.services.parcels import ATTENTION_DAYS, operator_names

router = APIRouter(prefix="/api/dashboard")


def _kind(t: Transfer) -> str:
    return "Transfer" if t.is_local else ("Load-out" if t.is_load_out else "Load-in")


@router.get("")
def dashboard(db: Session = Depends(get_db)):
    """首页区块（文档 5.1）：待处理包裹、最近单据、装箱单、产品库存、草稿数。"""
    now = utcnow()
    attention_q = select(ShippingParcel).where(ShippingParcel.shipped_utc.is_(None), ShippingParcel.deleted_utc.is_(None),
                                               ShippingParcel.created_utc <= now - dt.timedelta(days=ATTENTION_DAYS))
    attention = db.scalars(attention_q.order_by(ShippingParcel.created_utc).limit(10)).all()
    attention_count = db.scalar(select(func.count()).select_from(attention_q.subquery()))
    names = operator_names(db, attention)
    recent = db.scalars(select(Transfer).where(Transfer.transfer_from_id.is_(None))
                        .order_by(Transfer.date.desc()).limit(10)).all()
    slips = db.scalars(select(PackingSlip).order_by(PackingSlip.date.desc()).limit(10)).all()
    stock = (select(func.coalesce(func.sum(TransferItem.remaining_stock), 0)).select_from(TransferItem)
             .join(Transfer).join(ProductBatch).where(ProductBatch.product_id == Product.id,
                 Transfer.is_load_out.is_(False), Transfer.is_draft.is_(False)).correlate(Product).scalar_subquery())
    last_activity = (select(func.max(Transfer.date)).select_from(TransferItem).join(Transfer).join(ProductBatch)
                     .where(ProductBatch.product_id == Product.id, Transfer.is_draft.is_(False))
                     .correlate(Product).scalar_subquery())
    batches = select(func.count()).where(ProductBatch.product_id == Product.id).correlate(Product).scalar_subquery()
    products = db.execute(select(Product.id, Product.title, stock, batches)
                          .order_by(last_activity.desc().nulls_last()).limit(10)).all()

    def drafts(load_out: bool) -> int:
        return db.scalar(select(func.count()).where(Transfer.is_draft.is_(True), Transfer.is_local.is_(False),
                                                    Transfer.is_load_out.is_(load_out)))

    totals = db.execute(select(func.sum(TransferItem.remaining_stock), Product.default_unit)
                        .select_from(TransferItem).join(Transfer).join(ProductBatch).join(Product)
                        .group_by(Product.id, Product.default_unit)).all()
    return {"total_cases": sum(int(quantity / unit) for quantity, unit in totals), "recent": [{**transfer_row(t), "kind": _kind(t)} for t in recent],
            "packing_slips": [slip_row(s) for s in slips],
            "products": [{"id": r[0], "title": r[1], "stock": r[2], "batch_count": r[3]} for r in products],
            "attention": {"count": attention_count, "items": [parcel_out(p, names, now) for p in attention]},
            "drafts": {"load_in": drafts(False), "load_out": drafts(True)}}