"""Replenishment, landed-cost and explicit revaluation services."""
from decimal import Decimal, ROUND_CEILING
import hashlib

from fastapi import HTTPException
from sqlalchemy import text


def forecast(*, sellable: Decimal, incoming: Decimal, outgoing: Decimal) -> Decimal:
    return Decimal(str(sellable)) + Decimal(str(incoming)) - Decimal(str(outgoing))


def reorder_quantity(required: Decimal, *, minimum: Decimal, multiple: Decimal) -> Decimal:
    needed = max(Decimal(str(required)), Decimal(str(minimum)))
    step = Decimal(str(multiple))
    if step <= 0:
        raise ValueError("order multiple must be positive")
    return (needed / step).to_integral_value(rounding=ROUND_CEILING) * step


def _revaluation_fingerprint(body) -> str:
    raw = (f"{int(body.product_id)}|{int(body.variant_id or 0)}|"
           f"{Decimal(str(body.new_unit_cost))}|{body.reason}")
    return f"sha256:{hashlib.sha256(raw.encode('utf-8')).hexdigest()}"


async def upsert_rule(db, tenant_id: int, body) -> int:
    if body.target_qty < body.min_qty:
        raise HTTPException(status_code=400, detail="目标库存不能低于最低库存")
    product = (await db.execute(
        text("SELECT 1 FROM products WHERE id=:pid AND tenant_id=:tid"),
        {"pid": body.product_id, "tid": tenant_id},
    )).scalar()
    warehouse = (await db.execute(
        text("SELECT 1 FROM inventory_warehouses WHERE id=:wh AND tenant_id=:tid AND is_active=1"),
        {"wh": body.warehouse_id, "tid": tenant_id},
    )).scalar()
    if not product or not warehouse:
        raise HTTPException(status_code=400, detail="商品或仓库不存在")
    if body.supplier_id:
        supplier = (await db.execute(
            text("SELECT 1 FROM inventory_suppliers WHERE id=:id AND tenant_id=:tid AND is_active=1"),
            {"id": body.supplier_id, "tid": tenant_id},
        )).scalar()
        if not supplier:
            raise HTTPException(status_code=400, detail="供应商不存在")
    await db.execute(
        text("INSERT INTO inventory_reorder_rules (tenant_id,product_id,variant_id,warehouse_id,supplier_id,"
             "min_qty,target_qty,safety_qty,min_order_qty,order_multiple,is_enabled,created_at,updated_at) "
             "VALUES (:tid,:pid,:vid,:wh,:supplier,:min,:target,:safety,:moq,:multiple,:enabled,NOW(3),NOW(3)) "
             "ON DUPLICATE KEY UPDATE supplier_id=:supplier,min_qty=:min,target_qty=:target,safety_qty=:safety,"
             "min_order_qty=:moq,order_multiple=:multiple,is_enabled=:enabled,updated_at=NOW(3)"),
        {"tid": tenant_id, "pid": body.product_id, "vid": body.variant_id or 0,
         "wh": body.warehouse_id, "supplier": body.supplier_id, "min": body.min_qty,
         "target": body.target_qty, "safety": body.safety_qty, "moq": body.min_order_qty,
         "multiple": body.order_multiple, "enabled": int(body.is_enabled)},
    )
    return int((await db.execute(
        text("SELECT id FROM inventory_reorder_rules WHERE tenant_id=:tid AND product_id=:pid "
             "AND variant_id=:vid AND warehouse_id=:wh"),
        {"tid": tenant_id, "pid": body.product_id, "vid": body.variant_id or 0,
         "wh": body.warehouse_id},
    )).scalar())


async def _forecast_dimension(db, tenant_id: int, product_id: int, variant_id: int,
                              warehouse_id: int) -> tuple[Decimal, Decimal, Decimal, Decimal]:
    balance = (await db.execute(
        text("SELECT COALESCE(SUM(CASE WHEN stock_state='sellable' THEN qty ELSE 0 END),0),"
             "COALESCE(SUM(CASE WHEN stock_state='reserved' THEN qty ELSE 0 END),0) "
             "FROM inventory_balances WHERE tenant_id=:tid AND product_id=:pid AND variant_id=:vid "
             "AND warehouse_id=:wh"),
        {"tid": tenant_id, "pid": product_id, "vid": variant_id, "wh": warehouse_id},
    )).fetchone()
    incoming = (await db.execute(
        text("SELECT COALESCE(SUM((l.qty-l.received_qty)*l.uom_factor),0) FROM inventory_purchase_order_lines l "
             "JOIN inventory_purchase_orders p ON p.id=l.po_id AND p.tenant_id=l.tenant_id "
             "WHERE l.tenant_id=:tid AND l.product_id=:pid AND l.variant_id=:vid AND p.warehouse_id=:wh "
             "AND p.status IN ('draft','pending_approval','confirmed','partially_received') "
             "AND l.qty>l.received_qty"),
        {"tid": tenant_id, "pid": product_id, "vid": variant_id, "wh": warehouse_id},
    )).scalar()
    allocations = (await db.execute(
        text("SELECT COALESCE(SUM(CASE WHEN warehouse_id=:wh THEN reserved_qty-shipped_qty ELSE 0 END),0),"
             "COALESCE(SUM(reserved_qty-shipped_qty),0) FROM inventory_allocations "
             "WHERE tenant_id=:tid AND product_id=:pid AND variant_id=:vid AND reserved_qty>shipped_qty"),
        {"tid": tenant_id, "pid": product_id, "vid": variant_id, "wh": warehouse_id},
    )).fetchone()
    sellable = Decimal(str(balance[0] or 0))
    aggregate_reserved = Decimal(str(balance[1] or 0))
    allocated_here = Decimal(str(allocations[0] or 0))
    allocated_total = Decimal(str(allocations[1] or 0))
    # Allocations assign the physical warehouse; any still-unassigned aggregate reservation
    # remains demand in the warehouse that owns the aggregate reserved balance.
    outgoing = allocated_here + max(Decimal("0"), aggregate_reserved - allocated_total)
    incoming_qty = Decimal(str(incoming or 0))
    return sellable, incoming_qty, outgoing, forecast(
        sellable=sellable, incoming=incoming_qty, outgoing=outgoing
    )


async def generate_suggestions(db, tenant_id: int) -> list[dict]:
    await db.execute(
        text("UPDATE inventory_replenishment_suggestions SET state='stale',updated_at=NOW(3) "
             "WHERE tenant_id=:tid AND state='draft' AND generated_on<CURDATE()"),
        {"tid": tenant_id},
    )
    rules = (await db.execute(
        text("SELECT id,product_id,variant_id,warehouse_id,supplier_id,min_qty,target_qty,safety_qty,"
             "min_order_qty,order_multiple FROM inventory_reorder_rules "
             "WHERE tenant_id=:tid AND is_enabled=1 ORDER BY id FOR UPDATE"),
        {"tid": tenant_id},
    )).fetchall()
    result = []
    for rule in rules:
        sellable, incoming, outgoing, projected = await _forecast_dimension(
            db, tenant_id, int(rule[1]), int(rule[2]), int(rule[3])
        )
        trigger = Decimal(str(rule[5])) + Decimal(str(rule[7]))
        if projected >= trigger:
            await db.execute(
                text("UPDATE inventory_replenishment_suggestions SET state='stale',updated_at=NOW(3) "
                     "WHERE tenant_id=:tid AND rule_id=:rule AND state='draft'"),
                {"tid": tenant_id, "rule": rule[0]},
            )
            continue
        required = Decimal(str(rule[6])) + Decimal(str(rule[7])) - projected
        suggested = reorder_quantity(
            required, minimum=Decimal(str(rule[8])), multiple=Decimal(str(rule[9]))
        )
        params = {"tid": tenant_id, "rule": rule[0], "pid": rule[1], "vid": rule[2],
                  "wh": rule[3], "supplier": rule[4], "forecast": projected,
                  "suggested": suggested}
        current_draft = (await db.execute(
            text("SELECT id FROM inventory_replenishment_suggestions WHERE tenant_id=:tid "
                 "AND rule_id=:rule AND generated_on=CURDATE() AND state='draft' LIMIT 1 FOR UPDATE"),
            params,
        )).scalar()
        if current_draft:
            await db.execute(
                text("UPDATE inventory_replenishment_suggestions SET forecast_qty=:forecast,"
                     "suggested_qty=:suggested,supplier_id=:supplier,updated_at=NOW(3) "
                     "WHERE id=:id AND tenant_id=:tid AND state='draft'"),
                {**params, "id": current_draft},
            )
        else:
            generation_no = (await db.execute(
                text("SELECT COALESCE(MAX(generation_no),0)+1 FROM inventory_replenishment_suggestions "
                     "WHERE tenant_id=:tid AND rule_id=:rule AND generated_on=CURDATE()"),
                params,
            )).scalar()
            await db.execute(
                text("INSERT INTO inventory_replenishment_suggestions "
                     "(tenant_id,rule_id,product_id,variant_id,warehouse_id,supplier_id,forecast_qty,"
                     "suggested_qty,generated_on,generation_no,state,created_at,updated_at) VALUES "
                     "(:tid,:rule,:pid,:vid,:wh,:supplier,:forecast,:suggested,CURDATE(),:generation,"
                     "'draft',NOW(3),NOW(3))"),
                {**params, "generation": generation_no},
            )
        result.append({"rule_id": rule[0], "product_id": rule[1], "variant_id": rule[2] or None,
                       "warehouse_id": rule[3], "supplier_id": rule[4],
                       "sellable_qty": str(sellable), "incoming_qty": str(incoming),
                       "outgoing_qty": str(outgoing), "forecast_qty": str(projected),
                       "suggested_qty": str(suggested)})
    return result


async def convert_suggestions(db, tenant_id: int, suggestion_ids: list[int],
                              requester_id: int) -> list[int]:
    from types import SimpleNamespace
    from app.plugins.inventory.purchasing import create_po

    ids = sorted(set(int(value) for value in suggestion_ids))
    placeholders = ",".join(str(value) for value in ids)
    rows = (await db.execute(
        text("SELECT s.id,s.product_id,s.variant_id,s.warehouse_id,s.supplier_id,s.suggested_qty,"
             "sp.unit_price,sp.currency,sp.purchase_uom,sp.uom_factor "
             "FROM inventory_replenishment_suggestions s "
             "JOIN inventory_supplier_products sp ON sp.tenant_id=s.tenant_id "
             "AND sp.supplier_id=s.supplier_id AND sp.product_id=s.product_id AND sp.variant_id=s.variant_id "
             "AND sp.is_active=1 AND (sp.valid_from IS NULL OR sp.valid_from<=CURDATE()) "
             "AND (sp.valid_to IS NULL OR sp.valid_to>=CURDATE()) "
             f"WHERE s.tenant_id=:tid AND s.id IN ({placeholders}) AND s.state='draft' "
             "ORDER BY s.supplier_id,s.warehouse_id,s.id FOR UPDATE"),
        {"tid": tenant_id},
    )).fetchall()
    if len(rows) != len(ids):
        raise HTTPException(status_code=409, detail="补货建议不可用或缺少有效的供应商商品目录")
    if any(row[4] is None for row in rows):
        raise HTTPException(status_code=409, detail="补货建议尚未指定供应商")
    groups: dict[tuple[int, int, str], list] = {}
    for row in rows:
        groups.setdefault((int(row[4]), int(row[3]), str(row[7])), []).append(row)
    purchase_order_ids = []
    for (supplier_id, warehouse_id, currency), group in groups.items():
        currency_row = (await db.execute(
            text("SELECT exchange_rate FROM currencies WHERE tenant_id=:tid AND code=:code AND is_active=1"),
            {"tid": tenant_id, "code": currency},
        )).scalar()
        if currency_row is None or Decimal(str(currency_row)) <= 0:
            raise HTTPException(status_code=409, detail=f"币种 {currency} 没有有效汇率")
        fx_rate = Decimal("1") / Decimal(str(currency_row))
        lines = [SimpleNamespace(
            product_id=int(row[1]), variant_id=int(row[2]) or None,
            qty=Decimal(str(row[5])), unit_cost=Decimal(str(row[6])), expected_date=None,
            purchase_uom=str(row[8]), uom_factor=Decimal(str(row[9])),
            batch_no=None, expires_on=None,
        ) for row in group]
        po_id = await create_po(
            db, tenant_id, supplier_id=supplier_id, warehouse_id=warehouse_id,
            currency=currency, fx_rate=fx_rate, lines=lines, requester_id=requester_id,
        )
        purchase_order_ids.append(po_id)
        group_ids = ",".join(str(int(row[0])) for row in group)
        await db.execute(
            text(f"UPDATE inventory_replenishment_suggestions SET state='converted',purchase_order_id=:po,"
                 f"updated_at=NOW(3) WHERE tenant_id=:tid AND id IN ({group_ids})"),
            {"po": po_id, "tid": tenant_id},
        )
    return purchase_order_ids


async def apply_landed_cost(db, tenant_id: int, body) -> dict:
    from app.plugins.inventory.costing import allocate_landed_cost

    settings = (await db.execute(
        text("SELECT id FROM inventory_settings WHERE tenant_id=:tid FOR UPDATE"), {"tid": tenant_id}
    )).scalar()
    if not settings:
        raise HTTPException(status_code=409, detail="进销存尚未启用")
    receipt = (await db.execute(
        text("SELECT id,state FROM inventory_operations WHERE id=:id AND tenant_id=:tid "
             "AND operation_type='receipt' FOR UPDATE"),
        {"id": body.receipt_operation_id, "tid": tenant_id},
    )).fetchone()
    if not receipt or receipt[1] != "done":
        raise HTTPException(status_code=409, detail="仅已完成收货单可分摊附加成本")
    existing = (await db.execute(
        text("SELECT id,receipt_operation_id,allocation_method,total_cost,reference "
             "FROM inventory_landed_costs WHERE tenant_id=:tid AND idempotency_key=:key"),
        {"tid": tenant_id, "key": body.idempotency_key},
    )).fetchone()
    if existing:
        same_request = (
            int(existing[1]) == int(body.receipt_operation_id)
            and existing[2] == body.allocation_method
            and Decimal(str(existing[3])) == Decimal(str(body.total_cost))
            and (existing[4] or None) == (body.reference or None)
        )
        if not same_request:
            raise HTTPException(status_code=409, detail="幂等键已被不同的附加成本请求使用")
        return {"id": int(existing[0]), "duplicate": True}
    cost_layers = (await db.execute(
        text("SELECT id,qty_consumed,source_move_id FROM inventory_cost_layers WHERE tenant_id=:tid "
             "AND source_operation_id=:op FOR UPDATE"),
        {"tid": tenant_id, "op": body.receipt_operation_id},
    )).fetchall()
    if any(Decimal(str(row[1] or 0)) > 0 for row in cost_layers):
        raise HTTPException(status_code=409, detail="该收货批次已发生出库，不能再补录附加成本")
    moves = (await db.execute(
        text("SELECT m.id,m.product_id,m.variant_id,m.done_qty,m.unit_cost,"
             "COALESCE(v.weight,p.weight,0) FROM inventory_moves m "
             "JOIN products p ON p.id=m.product_id AND p.tenant_id=m.tenant_id "
             "LEFT JOIN product_variants v ON v.id=m.variant_id AND v.tenant_id=m.tenant_id "
             "WHERE m.tenant_id=:tid AND m.operation_id=:op ORDER BY m.id FOR UPDATE"),
        {"tid": tenant_id, "op": body.receipt_operation_id},
    )).fetchall()
    if not moves:
        raise HTTPException(status_code=400, detail="收货单没有明细")
    if {int(row[2]) for row in cost_layers if row[2] is not None} != {int(row[0]) for row in moves}:
        raise HTTPException(status_code=409, detail="收货单成本来源不完整，不能分摊附加成本")
    if body.allocation_method == "quantity":
        bases = [Decimal(str(row[3])) for row in moves]
    elif body.allocation_method == "weight":
        bases = [Decimal(str(row[3])) * Decimal(str(row[5] or 0)) for row in moves]
    else:
        bases = [Decimal(str(row[3])) * Decimal(str(row[4] or 0)) for row in moves]
    try:
        shares = allocate_landed_cost(body.total_cost, bases)
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    await db.execute(
        text("INSERT INTO inventory_landed_costs (tenant_id,receipt_operation_id,allocation_method,total_cost,"
             "state,reference,idempotency_key,created_at,updated_at) VALUES "
             "(:tid,:op,:method,:total,'posted',:reference,:key,NOW(3),NOW(3))"),
        {"tid": tenant_id, "op": body.receipt_operation_id, "method": body.allocation_method,
         "total": body.total_cost, "reference": body.reference, "key": body.idempotency_key},
    )
    landed_id = int((await db.execute(text("SELECT LAST_INSERT_ID()"))).scalar())
    lines = []
    for move, basis, share in zip(moves, bases, shares):
        increment = (share / Decimal(str(move[3]))).quantize(Decimal("0.0001"))
        await db.execute(
            text("INSERT INTO inventory_landed_cost_lines (tenant_id,landed_cost_id,receipt_move_id,basis_value,"
                 "allocated_amount,unit_cost_increment,created_at,updated_at) VALUES "
                 "(:tid,:landed,:move,:basis,:amount,:increment,NOW(3),NOW(3))"),
            {"tid": tenant_id, "landed": landed_id, "move": move[0], "basis": basis,
             "amount": share, "increment": increment},
        )
        await db.execute(
            text("UPDATE inventory_cost_layers SET additional_cost=additional_cost+:amount,updated_at=NOW(3) "
                 "WHERE tenant_id=:tid AND source_move_id=:move"),
            {"amount": share, "tid": tenant_id, "move": move[0]},
        )
        await db.execute(
            text("UPDATE inventory_moves SET unit_cost=unit_cost+:increment,updated_at=NOW(3) "
                 "WHERE id=:id AND tenant_id=:tid"),
            {"increment": increment, "id": move[0], "tid": tenant_id},
        )
        lines.append({"receipt_move_id": move[0], "allocated_amount": str(share),
                      "unit_cost_increment": str(increment)})
    return {"id": landed_id, "duplicate": False, "lines": lines}


async def revalue_inventory(db, tenant_id: int, operator_id: int, body) -> dict:
    from app.plugins.inventory.operations import _insert_operation
    from app.plugins.inventory.services import assert_period_open

    await assert_period_open(db, tenant_id)
    settings = (await db.execute(
        text("SELECT id FROM inventory_settings WHERE tenant_id=:tid FOR UPDATE"), {"tid": tenant_id}
    )).scalar()
    existing = (await db.execute(
        text("SELECT id,source_doc_id,reference FROM inventory_operations "
             "WHERE tenant_id=:tid AND idempotency_key=:key"),
        {"tid": tenant_id, "key": body.idempotency_key},
    )).fetchone()
    if existing:
        expected_reference = _revaluation_fingerprint(body)
        if int(existing[1] or 0) != int(body.product_id) or existing[2] != expected_reference:
            raise HTTPException(status_code=409, detail="幂等键已被不同的库存重估请求使用")
        return {"operation_id": int(existing[0]), "duplicate": True}
    if not settings:
        raise HTTPException(status_code=409, detail="进销存尚未启用")
    layers = (await db.execute(
        text("SELECT id,qty_in-qty_consumed,unit_cost FROM inventory_cost_layers "
             "WHERE tenant_id=:tid AND product_id=:pid AND variant_id=:vid "
             "AND qty_in>qty_consumed FOR UPDATE"),
        {"tid": tenant_id, "pid": body.product_id, "vid": body.variant_id or 0},
    )).fetchall()
    if not layers:
        raise HTTPException(status_code=409, detail="没有可重估的现存成本层")
    operation_id = await _insert_operation(
        db, tenant_id, operation_type="revaluation", source_doc_type="product",
        source_doc_id=body.product_id, warehouse_id=0,
        reference=_revaluation_fingerprint(body), reason=body.reason,
        idempotency_key=body.idempotency_key,
    )
    for layer_id, remaining_qty, old_unit_cost in layers:
        remaining_qty = Decimal(str(remaining_qty))
        old_unit_cost = Decimal(str(old_unit_cost))
        value_delta = (remaining_qty * (body.new_unit_cost - old_unit_cost)).quantize(Decimal("0.0001"))
        await db.execute(
            text("INSERT INTO inventory_revaluation_lines (tenant_id,operation_id,cost_layer_id,operator_id,"
                 "remaining_qty,old_unit_cost,new_unit_cost,value_delta,created_at,updated_at) VALUES "
                 "(:tid,:op,:layer,:operator,:qty,:old,:new,:delta,NOW(3),NOW(3))"),
            {"tid": tenant_id, "op": operation_id, "layer": layer_id, "operator": operator_id,
             "qty": remaining_qty, "old": old_unit_cost, "new": body.new_unit_cost,
             "delta": value_delta},
        )
    await db.execute(
        text("UPDATE inventory_cost_layers SET unit_cost=:cost,updated_at=NOW(3) WHERE tenant_id=:tid "
             "AND product_id=:pid AND variant_id=:vid AND qty_in>qty_consumed"),
        {"cost": body.new_unit_cost, "tid": tenant_id, "pid": body.product_id,
         "vid": body.variant_id or 0},
    )
    if not body.variant_id:
        await db.execute(
            text("UPDATE products SET cost_price=:cost WHERE id=:pid AND tenant_id=:tid"),
            {"cost": body.new_unit_cost, "pid": body.product_id, "tid": tenant_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 {"operation_id": operation_id, "layers_updated": len(layers), "duplicate": False}