import uuid

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

from flyway.api.deps import get_db
from flyway.api.lists import like
from flyway.api.views import product_out, supplier_out
from flyway.models import AppUser, Client, PackingSlip, Product, ProductBatch, Supplier, Transfer, TransferItem

router = APIRouter(prefix="/api/lookup")
LIMIT = 100


@router.get("/clients")
def clients(keyword: str = "", db: Session = Depends(get_db)):
    q = select(Client).where(or_(like(Client.name, keyword), like(Client.email, keyword), like(Client.mobile_phone, keyword))).order_by(Client.name).limit(LIMIT)
    return [{"id": c.id, "name": f"{c.name} (E:{c.email or ''},M:{c.mobile_phone or ''})"} for c in db.scalars(q)]


@router.get("/products")
def products(keyword: str = "", db: Session = Depends(get_db)):
    q = select(Product).where(like(Product.title, keyword)).order_by(Product.title).limit(LIMIT)
    return [product_out(p) for p in db.scalars(q)]


@router.get("/suppliers")
def suppliers(keyword: str = "", db: Session = Depends(get_db)):
    q = (select(Supplier).where(or_(like(Supplier.name, keyword), like(Supplier.rmp_number, keyword)))
         .order_by(Supplier.rmp_number).limit(LIMIT))
    return [supplier_out(s) for s in db.scalars(q)]


@router.get("/batches")
def batches(product_id: uuid.UUID, keyword: str = "", db: Session = Depends(get_db)):
    q = (select(ProductBatch).where(ProductBatch.product_id == product_id, like(ProductBatch.number, keyword))
         .order_by(ProductBatch.date.desc()).limit(LIMIT))
    return [{"id": b.id, "number": b.number, "used_by_date": b.used_by_date} for b in db.scalars(q)]


@router.get("/load-ins")
def load_ins(client_id: uuid.UUID | None = None, keyword: str = "", db: Session = Depends(get_db)):
    """已完成且仍有剩余的入库单（出库 / 调拨的“关联入库单”与“一键带出剩余”）。"""
    has_stock = select(TransferItem.transfer_id).where(TransferItem.remaining_stock > 0)
    q = select(Transfer).where(Transfer.is_load_out.is_(False), Transfer.is_local.is_(False),
                               Transfer.is_draft.is_(False), Transfer.id.in_(has_stock), like(Transfer.code, keyword))
    if client_id:
        q = q.where(Transfer.client_id == client_id)
    return [{"id": t.id, "code": t.code, "date": t.date} for t in db.scalars(q.order_by(Transfer.date.desc()).limit(LIMIT))]


@router.get("/packing-slips")
def packing_slips(keyword: str = "", db: Session = Depends(get_db)):
    q = (select(PackingSlip).where(like(PackingSlip.reference, keyword), PackingSlip.completed.is_(None))
         .order_by(PackingSlip.date.desc()).limit(LIMIT))
    return [{"id": s.id, "reference": s.reference, "to_rmp": s.to_rmp} for s in db.scalars(q)]


@router.get("/users")
def users(keyword: str = "", db: Session = Depends(get_db)):
    q = select(AppUser).where(like(func.concat(AppUser.first_name, " ", AppUser.last_name, " ", AppUser.username),
                                   keyword)).order_by(AppUser.username).limit(LIMIT)
    return [{"id": u.id, "full_name": u.full_name, "username": u.username} for u in db.scalars(q)]