"""Customer returns, disposition, freeze and scrap over exact stock dimensions."""
from dataclasses import dataclass
from decimal import Decimal
import json
from types import SimpleNamespace

from fastapi import HTTPException
from sqlalchemy import text


@dataclass(frozen=True)
class ReturnableQty:
    shipped: Decimal
    returned: Decimal

    @property
    def remaining(self) -> Decimal:
        return max(Decimal("0"), Decimal(str(self.shipped)) - Decimal(str(self.returned)))

    def apply(self, qty: Decimal) -> Decimal:
        value = Decimal(str(qty))
        if value <= 0:
            raise HTTPException(status_code=400, detail="退货数量必须大于 0")
        if value > self.remaining:
            raise HTTPException(status_code=409, detail=f"退货数量超过可退数量 {self.remaining}")
        return self.remaining - value


def destination_state(disposition: str) -> str:
    states = {"resellable": "sellable", "inspect": "inspection", "scrap": "scrapped"}
    try:
        return states[disposition]
    except KeyError as exc:
        raise HTTPException(status_code=400, detail="不支持的退货处置方式") from exc


async def restore_allocated_by_sku(db, tenant_id: int, order_id: int, items, *,
                                   suffix: str, disposition: str = "resellable",
                                   operator_id: int = 0) -> dict:
    """Bridge existing refund/POS payloads into exact allocation-based returns."""
    order = (await db.execute(
        text("SELECT id FROM orders WHERE id=:id AND tenant_id=:tid FOR UPDATE"),
        {"id": order_id, "tid": tenant_id},
    )).scalar()
    if not order:
        raise HTTPException(status_code=404, detail="订单不存在")
    rows = (await db.execute(
        text("SELECT oi.id,oi.product_id,COALESCE(oi.variant_id,0),"
             "COALESCE(SUM(a.shipped_qty-a.returned_qty),0) FROM order_items oi "
             "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 ORDER BY oi.id"),
        {"tid": tenant_id, "order": order_id},
    )).fetchall()
    remaining_by_item = {int(row[0]): Decimal(str(row[3] or 0)) for row in rows}
    return_lines = []
    for item in items:
        pid = int(item["product_id"])
        vid = int(item.get("variant_id") or 0)
        requested = Decimal(str(item["qty"]))
        candidates = [row for row in rows if (vid and int(row[2]) == vid) or (not vid and int(row[1]) == pid)]
        for row in candidates:
            available = remaining_by_item[int(row[0])]
            take = min(requested, available)
            if take <= 0:
                continue
            return_lines.append(SimpleNamespace(
                order_item_id=int(row[0]), qty=take, disposition=disposition,
            ))
            remaining_by_item[int(row[0])] -= take
            requested -= take
            if requested <= 0:
                break
        if requested > 0:
            raise HTTPException(status_code=409, detail=f"商品 {pid} 的可退已发货数量不足")
    body = SimpleNamespace(
        lines=return_lines, reason="退款/退货完成",
        reference=suffix, idempotency_key=f"return:{order_id}:{suffix}"[:120],
    )
    return await create_customer_return(db, tenant_id, order_id, operator_id, body)


async def _shipment_cost_slices(db, tenant_id: int, allocation_id: int,
                                qty: Decimal) -> list[tuple[int, Decimal, Decimal]]:
    rows = (await db.execute(
        text("SELECT id,qty-returned_qty,unit_cost FROM inventory_allocation_shipments "
             "WHERE tenant_id=:tid AND allocation_id=:allocation AND qty>returned_qty "
             "ORDER BY id FOR UPDATE"),
        {"tid": tenant_id, "allocation": allocation_id},
    )).fetchall()
    remaining = Decimal(str(qty))
    slices = []
    for shipment_id, available, unit_cost in rows:
        take = min(remaining, Decimal(str(available)))
        if take <= 0:
            continue
        slices.append((int(shipment_id), take, Decimal(str(unit_cost))))
        remaining -= take
        if remaining <= 0:
            break
    if remaining > 0:
        raise HTTPException(status_code=409, detail="找不到完整的原始发货成本切片")
    return slices


async def create_customer_return(db, tenant_id: int, order_id: int, operator_id: int, body) -> dict:
    from app.plugins.inventory.operations import _existing_operation, _insert_operation, _write_operation_leg
    from app.plugins.inventory.services import assert_period_open
    from app.plugins.inventory.traceability import move_serials

    await assert_period_open(db, tenant_id)
    order = (await db.execute(
        text("SELECT id FROM orders WHERE id=:id AND tenant_id=:tid FOR UPDATE"),
        {"id": order_id, "tid": tenant_id},
    )).scalar()
    if not order:
        raise HTTPException(status_code=404, detail="订单不存在")
    existing = await _existing_operation(db, tenant_id, body.idempotency_key)
    if existing:
        return {"ok": True, "operation_id": int(existing), "duplicate": True}

    requests: dict[tuple[int, str], Decimal] = {}
    for line in body.lines:
        key = (int(line.order_item_id), line.disposition)
        requests[key] = requests.get(key, Decimal("0")) + Decimal(str(line.qty))
    item_ids = sorted({key[0] for key in requests})
    placeholders = ",".join(str(value) for value in item_ids)
    allocations = (await db.execute(
        text("SELECT id,order_item_id,product_id,variant_id,warehouse_id,location_id,batch_id,serial_id,"
             "shipped_qty,returned_qty FROM inventory_allocations WHERE tenant_id=:tid AND order_id=:order "
             f"AND order_item_id IN ({placeholders}) AND shipped_qty>returned_qty ORDER BY id FOR UPDATE"),
        {"tid": tenant_id, "order": order_id},
    )).fetchall()
    known_items = {int(row[1]) for row in allocations}
    if any(item_id not in known_items for item_id in item_ids):
        raise HTTPException(status_code=409, detail="退货行没有可退的已发货数量")

    operation_id = await _insert_operation(
        db, tenant_id, operation_type="customer_return", source_doc_type="order",
        source_doc_id=order_id, warehouse_id=int(allocations[0][4]), reference=body.reference,
        reason=body.reason, idempotency_key=body.idempotency_key,
    )
    move_count = 0
    consumed_by_allocation: dict[int, Decimal] = {}
    for (item_id, disposition), requested in requests.items():
        remaining = requested
        for allocation in (row for row in allocations if int(row[1]) == item_id):
            available = ReturnableQty(
                Decimal(str(allocation[8])),
                Decimal(str(allocation[9])) + consumed_by_allocation.get(int(allocation[0]), Decimal("0")),
            ).remaining
            if available <= 0 or remaining <= 0:
                continue
            qty = min(available, remaining)
            if allocation[7] and qty != Decimal("1"):
                raise HTTPException(status_code=409, detail="序列号商品必须逐件退货")
            state = destination_state(disposition)
            cost_slices = await _shipment_cost_slices(db, tenant_id, int(allocation[0]), qty)
            serial_no = None
            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()
            for shipment_slice_id, slice_qty, unit_cost in cost_slices:
                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,serial_no,source_state,destination_state,unit_cost,"
                         "created_at,updated_at) VALUES (:tid,:op,:pid,:vid,0,:wh,0,:location,:qty,:qty,:batch,"
                         ":serial,'customer',:state,:cost,NOW(3),NOW(3))"),
                    {"tid": tenant_id, "op": operation_id, "pid": allocation[2], "vid": allocation[3],
                     "wh": allocation[4], "location": allocation[5], "qty": slice_qty,
                     "batch": allocation[6], "serial": serial_no, "state": state, "cost": unit_cost},
                )
                move_id = int((await db.execute(text("SELECT LAST_INSERT_ID()"))).scalar())
                if state != "scrapped":
                    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=slice_qty, unit_cost=unit_cost,
                        operator_id=operator_id, reason=body.reason, stock_state=state,
                        leg_suffix=f"return:{state}",
                    )
                    await db.execute(
                        text("INSERT INTO inventory_cost_layers (tenant_id,product_id,variant_id,batch_id,"
                             "source_operation_id,source_move_id,qty_in,qty_consumed,unit_cost,created_at,updated_at) "
                             "VALUES (:tid,:pid,:vid,:batch,:op,:move,:qty,0,:cost,NOW(3),NOW(3))"),
                        {"tid": tenant_id, "pid": allocation[2], "vid": allocation[3],
                         "batch": allocation[6], "op": operation_id, "move": move_id,
                         "qty": slice_qty, "cost": unit_cost},
                    )
                await db.execute(
                    text("UPDATE inventory_allocation_shipments SET returned_qty=returned_qty+:qty,"
                         "updated_at=NOW(3) WHERE id=:id AND tenant_id=:tid"),
                    {"qty": slice_qty, "id": shipment_slice_id, "tid": tenant_id},
                )
                move_count += 1
            if allocation[7]:
                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="customer_return", from_warehouse_id=0,
                    to_warehouse_id=int(allocation[4]) if state != "scrapped" else 0,
                    from_location_id=0, to_location_id=int(allocation[5]) if state != "scrapped" else 0,
                    from_state="shipped", to_state=state, batch_id=int(allocation[6]),
                )
            await db.execute(
                text("UPDATE inventory_allocations SET returned_qty=returned_qty+:qty,updated_at=NOW(3) "
                     "WHERE id=:id AND tenant_id=:tid"),
                {"qty": qty, "id": allocation[0], "tid": tenant_id},
            )
            remaining -= qty
            consumed_by_allocation[int(allocation[0])] = (
                consumed_by_allocation.get(int(allocation[0]), Decimal("0")) + qty
            )
        if remaining > 0:
            raise HTTPException(status_code=409, detail=f"订单行 {item_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 {"ok": True, "operation_id": operation_id, "moves": move_count, "duplicate": False}


async def apply_stock_state_action(db, tenant_id: int, operator_id: int, body) -> dict:
    from app.plugins.inventory.operations import _existing_operation, _insert_operation, _write_operation_leg
    from app.plugins.inventory.services import (
        _consume_batch_layer, _consume_cost_pool, _cost_method, assert_period_open,
    )
    from app.plugins.inventory.traceability import move_serials, validate_move_tracking, load_product_profile

    await assert_period_open(db, tenant_id)
    if body.from_state == body.to_state:
        raise HTTPException(status_code=400, detail="来源状态与目标状态不能相同")
    tenant_inventory = (await db.execute(
        text("SELECT id FROM inventory_settings WHERE tenant_id=:tid FOR UPDATE"),
        {"tid": tenant_id},
    )).scalar()
    if not tenant_inventory:
        raise HTTPException(status_code=409, detail="进销存尚未启用")
    existing = await _existing_operation(db, tenant_id, body.idempotency_key)
    if existing:
        return {"ok": True, "operation_id": int(existing), "duplicate": True}
    profile = await load_product_profile(db, tenant_id, body.product_id, body.variant_id or 0)
    validate_move_tracking(profile["tracking_type"], body.qty,
                           serial_numbers=body.serial_numbers,
                           batch_no="existing" if body.batch_id else None)
    operation_type = "scrap" if body.to_state == "scrapped" else "state_change"
    unit_cost = None
    if body.to_state == "scrapped":
        method = await _cost_method(db, tenant_id)
        if method == "batch_actual" and body.batch_id:
            unit_cost = await _consume_batch_layer(
                db, tenant_id, body.product_id, body.variant_id or 0, body.batch_id, body.qty
            )
        else:
            total_cost = await _consume_cost_pool(
                db, tenant_id, body.product_id, body.variant_id or 0, body.qty, method
            )
            unit_cost = (total_cost / body.qty).quantize(Decimal("0.0001"))
    operation_id = await _insert_operation(
        db, tenant_id, operation_type=operation_type, source_doc_type=None, source_doc_id=None,
        warehouse_id=body.warehouse_id, reference=body.reference, reason=body.reason,
        idempotency_key=body.idempotency_key,
    )
    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,"
             "serial_numbers,source_state,destination_state,created_at,updated_at) VALUES "
             "(:tid,:op,:pid,:vid,:wh,:to_wh,:location,:to_location,:qty,:qty,:batch,:serials,:source,:dest,"
             "NOW(3),NOW(3))"),
        {"tid": tenant_id, "op": operation_id, "pid": body.product_id, "vid": body.variant_id or 0,
         "wh": body.warehouse_id, "to_wh": 0 if body.to_state == "scrapped" else body.warehouse_id,
         "location": body.location_id, "to_location": 0 if body.to_state == "scrapped" else body.location_id,
         "qty": body.qty, "batch": body.batch_id or 0, "serials": json.dumps(body.serial_numbers),
         "source": body.from_state, "dest": body.to_state},
    )
    move_id = int((await db.execute(text("SELECT LAST_INSERT_ID()"))).scalar())
    await _write_operation_leg(
        db, tenant_id, operation_id=operation_id, move_id=move_id, product_id=body.product_id,
        variant_id=body.variant_id or 0, warehouse_id=body.warehouse_id,
        location_id=body.location_id, batch_id=body.batch_id or 0, qty_delta=-body.qty,
        unit_cost=unit_cost, operator_id=operator_id, reason=body.reason,
        stock_state=body.from_state, leg_suffix="state-out",
    )
    if body.to_state != "scrapped":
        await _write_operation_leg(
            db, tenant_id, operation_id=operation_id, move_id=move_id, product_id=body.product_id,
            variant_id=body.variant_id or 0, warehouse_id=body.warehouse_id,
            location_id=body.location_id, batch_id=body.batch_id or 0, qty_delta=body.qty,
            unit_cost=None, operator_id=operator_id, reason=body.reason,
            stock_state=body.to_state, leg_suffix="state-in",
        )
    if body.serial_numbers:
        await move_serials(
            db, tenant_id, serial_numbers=body.serial_numbers, product_id=body.product_id,
            variant_id=body.variant_id or 0, operation_id=operation_id, event_type=operation_type,
            from_warehouse_id=body.warehouse_id,
            to_warehouse_id=0 if body.to_state == "scrapped" else body.warehouse_id,
            from_location_id=body.location_id,
            to_location_id=0 if body.to_state == "scrapped" else body.location_id,
            from_state=body.from_state, to_state=body.to_state, batch_id=body.batch_id or 0,
        )
    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 {"ok": True, "operation_id": operation_id, "duplicate": False}