"""流水、盘点、批次报表、客户库存汇总（文档 7.1–7.3，F31、F34、F37–F40）。只读。"""
import calendar
import datetime as dt
import uuid
from dataclasses import dataclass, field

from sqlalchemy import select
from sqlalchemy.orm import Session

from flyway.domain.clock import NZ
from flyway.models import Client, Product, ProductBatch, Transfer, TransferItem


def signed(i: TransferItem) -> int:
    return (-1 if i.transfer.is_load_out else 1) * i.quantity * i.unit


def doc_path(t: Transfer) -> str:
    """前端详情页地址；调拨转入单跳到对应的调拨（转出单）页面。"""
    if t.is_local:
        return f"/transfers/{t.transfer_from_id if not t.is_load_out else t.id}"
    return f"/{'load-outs' if t.is_load_out else 'load-ins'}/{t.id}"


def kind(t: Transfer) -> str:
    return "Transfer" if t.is_local else ("Load-Out" if t.is_load_out else "Load-In")


def running_balances(db: Session, product_id: uuid.UUID, client_id: uuid.UUID | None) -> dict[uuid.UUID, int]:
    """旧 Balance（按客户）/ GlobalBalance（全部客户）：非草稿明细按 (日期, 单号) 累计，
    草稿不在旧 SiblingItems 中，显示结余 0。
    只取四列在内存里累加（全量数据上单产品 6 万条明细约 0.2 秒）；再大时改成窗口函数。"""
    q = (select(TransferItem.id, TransferItem.quantity * TransferItem.unit, Transfer.is_load_out, Transfer.is_draft)
         .join(Transfer).join(ProductBatch)
         .where(ProductBatch.product_id == product_id)
         .order_by(Transfer.date, Transfer.code, TransferItem.id))
    if client_id:
        q = q.where(Transfer.client_id == client_id)
    out, total = {}, 0
    for item_id, qty, is_out, is_draft in db.execute(q):
        if not is_draft:
            total += -qty if is_out else qty
        out[item_id] = 0 if is_draft else total
    return out


def nz_window(from_date: dt.date | None, to_date: dt.date | None, today: dt.date) -> tuple[dt.date, dt.date]:
    """盘点默认区间：最近一个月（旧 TransferController）。"""
    to_date = to_date or today
    if from_date is None:
        y, m = (to_date.year, to_date.month - 1) if to_date.month > 1 else (to_date.year - 1, 12)
        from_date = to_date.replace(year=y, month=m, day=min(to_date.day, calendar.monthrange(y, m)[1]))
    return from_date, to_date


@dataclass
class CountRow:
    transfer: Transfer
    source: Transfer | None
    place: str
    place_client: Client | None
    quantities: dict[uuid.UUID, float]
    total: float


@dataclass
class CountDay:
    day: dt.date
    rows: list[CountRow] = field(default_factory=list)
    balances: dict[uuid.UUID, float] = field(default_factory=dict)


@dataclass
class InventoryCount:
    client: Client
    from_date: dt.date
    to_date: dt.date
    products: list[Product]
    days: list[CountDay]


def _place(t: Transfer) -> tuple[str, Client | None]:
    if t.is_local:
        if t.is_load_out:
            return "TRANSFER TO {}", t.transfer_tos[0].client if t.transfer_tos else None
        return "TRANSFER FROM {}", t.transfer_from.client if t.transfer_from else None
    if t.is_load_out:
        return f"TO {t.to_location or ''} ({t.to_rmp or ''})", None
    return f"FROM {t.from_location or ''}", None


def inventory_count(db: Session, client: Client, from_date: dt.date, to_date: dt.date) -> InventoryCount:
    """Transfer/ListBlocks/InventoryCount.aspx：按天列出已完成单据，按产品（以默认单位计箱数）记进出与结余。"""
    start = dt.datetime.combine(from_date, dt.time(), NZ)
    end = dt.datetime.combine(to_date + dt.timedelta(days=1), dt.time(), NZ)
    transfers = db.scalars(select(Transfer).where(Transfer.client_id == client.id, Transfer.is_draft.is_(False),
                                                  Transfer.date >= start, Transfer.date < end)
                           .order_by(Transfer.date, Transfer.code)).all()
    products = sorted({i.batch.product for t in transfers for i in t.items}, key=lambda p: p.title)
    balance: dict[uuid.UUID, float] = {}
    for p in products:
        before = db.scalars(select(TransferItem).join(Transfer).join(ProductBatch).where(
            Transfer.client_id == client.id, Transfer.is_draft.is_(False), Transfer.date < start,
            ProductBatch.product_id == p.id)).all()
        balance[p.id] = sum(signed(i) for i in before) / p.default_unit
    days: list[CountDay] = []
    for t in transfers:
        day = t.date.astimezone(NZ).date()
        if not days or days[-1].day != day:
            days.append(CountDay(day))
        sources = sorted({i.main_item.transfer for i in t.items if i.main_item}, key=lambda s: s.date) \
            if t.is_load_out else [None]
        text, other = _place(t)
        for src in sources or [None]:
            items = [i for i in t.items if not t.is_load_out or (i.main_item and i.main_item.transfer is src)]
            row = CountRow(t, src, text, other, {}, 0)
            for p in products:
                mine = [i for i in items if i.batch.product_id == p.id]
                if mine:
                    qty = sum(signed(i) for i in mine) / p.default_unit
                    row.quantities[p.id] = qty
                    row.total += qty
                    balance[p.id] += qty
            days[-1].rows.append(row)
        days[-1].balances = dict(balance)
    return InventoryCount(client, from_date, to_date, products, days)


def client_stock_by_product(db: Session, client_id: uuid.UUID) -> list[dict]:
    """客户库存汇总（ClientProduct/ListBlocks/Print）：按产品，包含零库存与历史批号。"""
    items = db.scalars(select(TransferItem).join(Transfer).where(
        Transfer.client_id == client_id, Transfer.is_load_out.is_(False), Transfer.is_draft.is_(False),
        )).all()
    by_product: dict[uuid.UUID, dict] = {}
    for i in items:
        p = i.batch.product
        row = by_product.setdefault(p.id, {"product": p, "stock": 0, "batches": {}})
        row["stock"] += i.remaining_stock
        row["batches"][i.batch.number] = row["batches"].get(i.batch.number, 0) + i.remaining_stock
    return sorted(by_product.values(), key=lambda r: r["stock"])