"""Explicit allocation, picking and partial shipment over the shared inventory ledger."""
from dataclasses import dataclass
from decimal import Decimal

from fastapi import HTTPException
from sqlalchemy import text


@dataclass(frozen=True)
class FulfillmentQty:
    ordered: Decimal
    reserved: Decimal
    picked: Decimal
    shipped: Decimal
    returned: Decimal

    def validate(self) -> None:
        values = [Decimal(str(value)) for value in
                  (self.ordered, self.reserved, self.picked, self.shipped, self.returned)]
        ordered, reserved, picked, shipped, returned = values
        if min(values) < 0 or reserved > ordered or picked > reserved or shipped > picked or returned > shipped:
            raise HTTPException(status_code=409, detail="履约数量不满足 ordered≥reserved≥picked≥shipped≥returned")

    @property
    def outstanding(self) -> Decimal:
        return Decimal(str(self.ordered)) - Decimal(str(self.shipped))


async def _order(db, tenant_id: int, order_id: int, *, lock: bool = False):
    sql = "SELECT id,status FROM orders WHERE id=:id AND tenant_id=:tid"
    if lock:
        sql += " FOR UPDATE"
    row = (await db.execute(text(sql), {"id": order_id, "tid": tenant_id})).fetchone()
    if row is None:
        raise HTTPException(status_code=404, detail="订单不存在")
    return row


async def allocate_order(db, tenant_id: int, order_id: int, *, warehouse_id: int | None,
                         shortage_mode: str) -> dict:
    from app.plugins.inventory.services import _default_warehouse_id, load_policy
    from app.plugins.inventory.traceability import load_product_profile

    order = await _order(db, tenant_id, order_id, lock=True)
    policy = await load_policy(db, tenant_id)
    if order[1] not in policy.reserve_statuses:
        raise HTTPException(status_code=409, detail=f"订单状态 {order[1]} 不在租户配置的预留阶段")
    warehouse_id = warehouse_id or await _default_warehouse_id(db, tenant_id)
    items = (await db.execute(
        text("SELECT id,product_id,COALESCE(variant_id,0),quantity FROM order_items "
             "WHERE tenant_id=:tid AND order_id=:order ORDER BY id FOR UPDATE"),
        {"tid": tenant_id, "order": order_id},
    )).fetchall()
    shortages = []
    for item_id, product_id, variant_id, ordered_qty in items:
        if product_id is None:
            raise HTTPException(status_code=409, detail=f"订单行 {item_id} 的商品已删除，无法分配")
        already_reserved = (await db.execute(
            text("SELECT COALESCE(SUM(reserved_qty),0) FROM inventory_allocations "
                 "WHERE tenant_id=:tid AND order_id=:order AND order_item_id=:item"),
            {"tid": tenant_id, "order": order_id, "item": item_id},
        )).scalar()
        needed = Decimal(str(ordered_qty)) - Decimal(str(already_reserved or 0))
        if needed <= 0:
            continue
        profile = await load_product_profile(db, tenant_id, int(product_id), int(variant_id))
        if profile["tracking_type"] == "serial":
            serials = (await db.execute(
                text("SELECT s.id,s.location_id,COALESCE(s.batch_id,0) FROM inventory_serials s "
                     "WHERE s.tenant_id=:tid AND s.product_id=:pid "
                     "AND COALESCE(s.variant_id,0)=:vid AND s.warehouse_id=:wh AND s.stock_state='sellable' "
                     "AND NOT EXISTS (SELECT 1 FROM inventory_allocations a WHERE a.tenant_id=s.tenant_id "
                     "AND a.serial_id=s.id AND a.shipped_qty<a.reserved_qty) "
                     "ORDER BY s.id LIMIT 10000 FOR UPDATE"),
                {"tid": tenant_id, "pid": product_id, "vid": variant_id, "wh": warehouse_id},
            )).fetchall()
            take_count = min(int(needed), len(serials)) if needed == needed.to_integral_value() else 0
            for serial_id, location_id, batch_id in serials[:take_count]:
                await db.execute(
                    text("INSERT INTO inventory_allocations "
                         "(tenant_id,order_id,order_item_id,product_id,variant_id,warehouse_id,location_id,batch_id,"
                         "serial_id,reserved_qty,picked_qty,shipped_qty,returned_qty,created_at,updated_at) "
                         "VALUES (:tid,:order,:item,:pid,:vid,:wh,:location,:batch,:serial,1,0,0,0,NOW(3),NOW(3))"),
                    {"tid": tenant_id, "order": order_id, "item": item_id, "pid": product_id,
                     "vid": variant_id, "wh": warehouse_id, "location": location_id,
                     "batch": batch_id, "serial": serial_id},
                )
            needed -= Decimal(take_count)
        else:
            # Serializes the availability calculation across orders sharing the same stock rows.
            await db.execute(
                text("SELECT id FROM inventory_balances WHERE tenant_id=:tid AND product_id=:pid "
                     "AND variant_id=:vid AND warehouse_id=:wh AND stock_state='sellable' FOR UPDATE"),
                {"tid": tenant_id, "pid": product_id, "vid": variant_id, "wh": warehouse_id},
            )
            balances = (await db.execute(
                text("SELECT bal.location_id,bal.batch_id,"
                     "bal.qty-COALESCE(a.allocated,0) AS available "
                     "FROM inventory_balances bal LEFT JOIN inventory_batches b ON b.id=bal.batch_id "
                     "LEFT JOIN (SELECT tenant_id,product_id,variant_id,warehouse_id,location_id,batch_id,"
                     "SUM(reserved_qty-shipped_qty) allocated FROM inventory_allocations "
                     "GROUP BY tenant_id,product_id,variant_id,warehouse_id,location_id,batch_id) a "
                     "ON a.tenant_id=bal.tenant_id AND a.product_id=bal.product_id AND a.variant_id=bal.variant_id "
                     "AND a.warehouse_id=bal.warehouse_id AND a.location_id=bal.location_id AND a.batch_id=bal.batch_id "
                     "WHERE bal.tenant_id=:tid AND bal.product_id=:pid AND bal.variant_id=:vid "
                     "AND bal.warehouse_id=:wh AND bal.stock_state='sellable' AND bal.qty>COALESCE(a.allocated,0) "
                     "AND (bal.batch_id=0 OR (b.qc_state='passed' AND (b.expires_on IS NULL OR b.expires_on>=CURDATE()))) "
                     "ORDER BY b.expires_on IS NULL,b.expires_on,bal.batch_id,bal.location_id"),
                {"tid": tenant_id, "pid": product_id, "vid": variant_id, "wh": warehouse_id},
            )).fetchall()
            for location_id, batch_id, available in balances:
                if needed <= 0:
                    break
                take = min(needed, Decimal(str(available)))
                await db.execute(
                    text("INSERT INTO inventory_allocations "
                         "(tenant_id,order_id,order_item_id,product_id,variant_id,warehouse_id,location_id,batch_id,"
                         "serial_id,reserved_qty,picked_qty,shipped_qty,returned_qty,created_at,updated_at) "
                         "VALUES (:tid,:order,:item,:pid,:vid,:wh,:location,:batch,0,:qty,0,0,0,NOW(3),NOW(3))"),
                    {"tid": tenant_id, "order": order_id, "item": item_id, "pid": product_id,
                     "vid": variant_id, "wh": warehouse_id, "location": location_id,
                     "batch": batch_id, "qty": take},
                )
                needed -= take
        if needed > 0:
            shortages.append({"order_item_id": item_id, "shortage_qty": str(needed)})
    if shortages and shortage_mode == "reject":
        raise HTTPException(status_code=409, detail={"message": "库存不足", "shortages": shortages})
    return {**await fulfillment_status(db, tenant_id, order_id), "shortages": shortages}


async def pick_order(db, tenant_id: int, order_id: int, lines: list) -> dict:
    await _order(db, tenant_id, order_id, lock=True)
    requested = _requested_quantities(lines)
    allocations = (await db.execute(
        text("SELECT id,order_item_id,reserved_qty,picked_qty,shipped_qty,serial_id FROM inventory_allocations "
             "WHERE tenant_id=:tid AND order_id=:order ORDER BY id FOR UPDATE"),
        {"tid": tenant_id, "order": order_id},
    )).fetchall()
    if not allocations:
        raise HTTPException(status_code=409, detail="订单尚未分配库存")
    for allocation_id, item_id, reserved, picked, shipped, serial_id in allocations:
        available = Decimal(str(reserved)) - Decimal(str(picked))
        if available <= 0:
            continue
        qty = available if not lines else min(available, requested.get(int(item_id), Decimal("0")))
        if qty <= 0:
            continue
        if serial_id and qty != Decimal("1"):
            raise HTTPException(status_code=409, detail="序列号商品必须逐件拣货")
        await db.execute(text("UPDATE inventory_allocations SET picked_qty=picked_qty+:qty,updated_at=NOW(3) "
                              "WHERE id=:id AND tenant_id=:tid"),
                         {"qty": qty, "id": allocation_id, "tid": tenant_id})
        if lines:
            requested[int(item_id)] = requested.get(int(item_id), Decimal("0")) - qty
    if any(value > 0 for value in requested.values()):
        raise HTTPException(status_code=409, detail="拣货数量超过已分配数量")
    return await fulfillment_status(db, tenant_id, order_id)


async def ship_order(db, tenant_id: int, order_id: int, operator_id: int, lines: list) -> dict:
    from app.plugins.inventory.operations import _insert_operation, _write_operation_leg
    from app.plugins.inventory.services import (
        _consume_batch_layer, _consume_cost_pool, _cost_method,
        _default_warehouse_id, _reserved_leg,
    )
    from app.plugins.inventory.traceability import move_serials

    await _order(db, tenant_id, order_id, lock=True)
    requested = _requested_quantities(lines)
    allocations = (await db.execute(
        text("SELECT id,order_item_id,product_id,variant_id,warehouse_id,location_id,batch_id,serial_id,"
             "reserved_qty,picked_qty,shipped_qty FROM inventory_allocations "
             "WHERE tenant_id=:tid AND order_id=:order AND picked_qty>shipped_qty ORDER BY id FOR UPDATE"),
        {"tid": tenant_id, "order": order_id},
    )).fetchall()
    if not allocations:
        raise HTTPException(status_code=409, detail="订单没有待发货的已拣数量")
    operation_id = await _insert_operation(
        db, tenant_id, operation_type="shipment", source_doc_type="order", source_doc_id=order_id,
        warehouse_id=int(allocations[0][4]),
    )
    method = await _cost_method(db, tenant_id)
    shipped_by_key = {}
    for allocation in allocations:
        available = Decimal(str(allocation[9])) - Decimal(str(allocation[10]))
        qty = available if not lines else min(available, requested.get(int(allocation[1]), Decimal("0")))
        if qty <= 0:
            continue
        if allocation[7] and qty != Decimal("1"):
            raise HTTPException(status_code=409, detail="序列号分配必须逐件发货")
        if method == "batch_actual" and allocation[6]:
            unit_cost = await _consume_batch_layer(
                db, tenant_id, int(allocation[2]), int(allocation[3]), int(allocation[6]), qty
            )
        else:
            total_cost = await _consume_cost_pool(
                db, tenant_id, int(allocation[2]), int(allocation[3]), qty, method
            )
            unit_cost = (total_cost / qty).quantize(Decimal("0.0001"))
        await db.execute(
            text("INSERT INTO inventory_moves "
                 "(tenant_id,operation_id,product_id,variant_id,source_warehouse_id,destination_warehouse_id,"
                 "source_location_id,destination_location_id,planned_qty,done_qty,batch_id,source_state,"
                 "destination_state,unit_cost,created_at,updated_at) VALUES "
                 "(:tid,:op,:pid,:vid,:wh,0,:location,0,:qty,:qty,:batch,'sellable','customer',:cost,NOW(3),NOW(3))"),
            {"tid": tenant_id, "op": operation_id, "pid": allocation[2], "vid": allocation[3],
             "wh": allocation[4], "location": allocation[5], "qty": qty,
             "batch": allocation[6], "cost": unit_cost},
        )
        move_id = int((await db.execute(text("SELECT LAST_INSERT_ID()"))).scalar())
        await db.execute(
            text("INSERT INTO inventory_allocation_shipments "
                 "(tenant_id,allocation_id,shipment_move_id,qty,returned_qty,unit_cost,created_at,updated_at) "
                 "VALUES (:tid,:allocation,:move,:qty,0,:cost,NOW(3),NOW(3))"),
            {"tid": tenant_id, "allocation": allocation[0], "move": move_id,
             "qty": qty, "cost": unit_cost},
        )
        await _write_operation_leg(
            db, tenant_id, operation_id=operation_id, move_id=move_id, product_id=int(allocation[2]),
            variant_id=int(allocation[3]), warehouse_id=int(allocation[4]), location_id=int(allocation[5]),
            batch_id=int(allocation[6]), qty_delta=-qty, unit_cost=unit_cost, operator_id=operator_id,
            reason="销售发货", leg_suffix="shipment",
        )
        if allocation[7]:
            serial_no = (await db.execute(text("SELECT serial_no FROM inventory_serials "
                                               "WHERE id=:id AND tenant_id=:tid"),
                                          {"id": allocation[7], "tid": tenant_id})).scalar()
            await move_serials(
                db, tenant_id, serial_numbers=[serial_no], product_id=int(allocation[2]),
                variant_id=int(allocation[3]), operation_id=operation_id, event_type="shipment",
                from_warehouse_id=int(allocation[4]), to_warehouse_id=0,
                from_location_id=int(allocation[5]), to_location_id=0,
                from_state="sellable", to_state="shipped", batch_id=int(allocation[6]),
            )
        await db.execute(text("UPDATE inventory_allocations SET shipped_qty=shipped_qty+:qty,updated_at=NOW(3) "
                              "WHERE id=:id AND tenant_id=:tid"),
                         {"qty": qty, "id": allocation[0], "tid": tenant_id})
        key = (int(allocation[2]), int(allocation[3]), int(allocation[4]))
        shipped_by_key[key] = shipped_by_key.get(key, Decimal("0")) + qty
        if lines:
            requested[int(allocation[1])] -= qty
    if any(value > 0 for value in requested.values()):
        raise HTTPException(status_code=409, detail="发货数量超过已拣数量")
    reservation_warehouse_id = await _default_warehouse_id(db, tenant_id)
    reserved_release: dict[tuple[int, int], Decimal] = {}
    for (product_id, variant_id, _physical_warehouse_id), qty in shipped_by_key.items():
        key = (product_id, variant_id)
        reserved_release[key] = reserved_release.get(key, Decimal("0")) + qty
    for (product_id, variant_id), qty in reserved_release.items():
        await _reserved_leg(db, tenant_id, product_id, variant_id, reservation_warehouse_id, -qty,
                            "explicit_shipment_res", operation_id, str(operation_id))
    await db.execute(text("UPDATE inventory_operations SET state='done',actual_at=NOW(3),updated_at=NOW(3) "
                          "WHERE id=:id AND tenant_id=:tid"),
                     {"id": operation_id, "tid": tenant_id})
    return {**await fulfillment_status(db, tenant_id, order_id), "shipment_operation_id": operation_id}


def _requested_quantities(lines: list) -> dict[int, Decimal]:
    requested: dict[int, Decimal] = {}
    for line in lines:
        item_id = int(line.order_item_id)
        requested[item_id] = requested.get(item_id, Decimal("0")) + Decimal(str(line.qty))
    return requested


async def fulfillment_status(db, tenant_id: int, order_id: int) -> dict:
    rows = (await db.execute(
        text("SELECT oi.id,oi.product_id,COALESCE(oi.variant_id,0),oi.quantity,"
             "COALESCE(SUM(a.reserved_qty),0),COALESCE(SUM(a.picked_qty),0),"
             "COALESCE(SUM(a.shipped_qty),0),COALESCE(SUM(a.returned_qty),0) "
             "FROM order_items oi LEFT JOIN inventory_allocations a "
             "ON a.tenant_id=oi.tenant_id AND a.order_id=oi.order_id AND a.order_item_id=oi.id "
             "WHERE oi.tenant_id=:tid AND oi.order_id=:order "
             "GROUP BY oi.id,oi.product_id,oi.variant_id,oi.quantity ORDER BY oi.id"),
        {"tid": tenant_id, "order": order_id},
    )).fetchall()
    return {"order_id": order_id, "lines": [
        {"order_item_id": row[0], "product_id": row[1], "variant_id": row[2] or None,
         "ordered_qty": str(row[3]), "reserved_qty": str(row[4]), "picked_qty": str(row[5]),
         "shipped_qty": str(row[6]), "returned_qty": str(row[7]),
         "backorder_qty": str(max(Decimal("0"), Decimal(str(row[3])) - Decimal(str(row[4])))),
         "outstanding_qty": str(max(Decimal("0"), Decimal(str(row[3])) - Decimal(str(row[6]))))}
        for row in rows
    ]}