"""库存内核（文档 10.2、12.6 第 5 条）：库存挂在入库类明细上。"""
from collections.abc import Iterable
from uuid import UUID

from sqlalchemy import func, select
from sqlalchemy.orm import Session, aliased

from flyway.domain.errors import BusinessError
from flyway.models import ProductBatch, Transfer, TransferItem


def lock_items(session: Session, ids: Iterable[UUID | None]) -> list[TransferItem]:
    """按 ID 升序逐行加锁（固定顺序避免死锁），并用数据库最新值刷新已加载对象。"""
    wanted = sorted({i for i in ids if i is not None})
    if not wanted:
        return []
    q = (
        select(TransferItem)
        .where(TransferItem.id.in_(wanted))
        .order_by(TransferItem.id)
        .with_for_update()
        .execution_options(populate_existing=True)
    )
    return list(session.scalars(q))


def compute_remaining(session: Session, item: TransferItem) -> int:
    """入库类明细剩余 = 自身数量 + 调拨转入数量 − 引用它的非草稿出库类明细数量。"""
    if item.transfer.is_load_out:
        return 0
    r, t = aliased(TransferItem), aliased(Transfer)
    rows = session.execute(
        select(t.is_load_out, t.is_draft, t.transfer_from_id, r.quantity * r.unit)
        .join(t, r.transfer_id == t.id)
        .where(r.related_transfer_item_id == item.id)
    ).all()
    transfer_in = sum(q for load_out, _, transfer_from, q in rows if not load_out and transfer_from is not None)
    loaded_out = sum(q for load_out, draft, _, q in rows if load_out and not draft)
    return item.quantity * item.unit + transfer_in - loaded_out


def refresh(session: Session, items: Iterable[TransferItem]) -> None:
    session.flush()
    for item in items:
        item.remaining_stock = compute_remaining(session, item)
        if item.remaining_stock < 0:
            raise BusinessError(f'Unable to save! "{item.batch.product.title}" insufficient inventory.')
    session.flush()


def stock(session: Session, *, client_id=None, product_id=None, batch_id=None) -> int:
    """汇总库存 = 非草稿入库类明细的剩余之和（文档 10.2-3）。"""
    q = (
        select(func.coalesce(func.sum(TransferItem.remaining_stock), 0))
        .join(Transfer, TransferItem.transfer_id == Transfer.id)
        .join(ProductBatch, TransferItem.batch_id == ProductBatch.id)
        .where(Transfer.is_load_out.is_(False), Transfer.is_draft.is_(False))
    )
    if client_id is not None:
        q = q.where(Transfer.client_id == client_id)
    if product_id is not None:
        q = q.where(ProductBatch.product_id == product_id)
    if batch_id is not None:
        q = q.where(TransferItem.batch_id == batch_id)
    return session.scalar(q)