"""Read-only management reports derived from inventory facts."""
from decimal import Decimal

from sqlalchemy import text


async def forecast_report(db, tenant_id: int) -> list[dict]:
    from app.plugins.inventory.planning import _forecast_dimension

    rules = (await db.execute(
        text("SELECT id,product_id,variant_id,warehouse_id,min_qty,target_qty,safety_qty "
             "FROM inventory_reorder_rules WHERE tenant_id=:tid AND is_enabled=1 ORDER BY id"),
        {"tid": tenant_id},
    )).fetchall()
    items = []
    for row in rules:
        sellable, incoming, outgoing, projected = await _forecast_dimension(
            db, tenant_id, int(row[1]), int(row[2]), int(row[3])
        )
        items.append({"rule_id": row[0], "product_id": row[1], "variant_id": row[2] or None,
                      "warehouse_id": row[3], "sellable_qty": str(sellable),
                      "incoming_qty": str(incoming), "outgoing_qty": str(outgoing),
                      "forecast_qty": str(projected), "min_qty": str(row[4]),
                      "target_qty": str(row[5]), "safety_qty": str(row[6])})
    return items


async def valuation_report(db, tenant_id: int) -> dict:
    rows = (await db.execute(
        text("SELECT b.product_id,b.variant_id,b.stock_state,b.qty,COALESCE(c.avg_cost,0) FROM "
             "(SELECT product_id,variant_id,stock_state,SUM(qty) qty FROM inventory_balances "
             "WHERE tenant_id=:tid AND stock_state<>'reserved' GROUP BY product_id,variant_id,stock_state) b "
             "LEFT JOIN (SELECT product_id,variant_id,"
             "SUM((qty_in-qty_consumed)*unit_cost+(additional_cost-additional_cost_consumed))/"
             "NULLIF(SUM(qty_in-qty_consumed),0) avg_cost "
             "FROM inventory_cost_layers WHERE tenant_id=:tid AND qty_in>qty_consumed "
             "GROUP BY product_id,variant_id) c ON c.product_id=b.product_id AND c.variant_id=b.variant_id "
             "ORDER BY b.product_id,b.variant_id,b.stock_state"),
        {"tid": tenant_id},
    )).fetchall()
    items = []
    total = Decimal("0")
    for row in rows:
        value = Decimal(str(row[3] or 0)) * Decimal(str(row[4] or 0))
        total += value
        items.append({"product_id": row[0], "variant_id": row[1] or None, "stock_state": row[2],
                      "qty": str(row[3]), "avg_cost": str(row[4]), "value": str(value)})
    return {"items": items, "total_value": str(total)}


async def aging_report(db, tenant_id: int) -> dict:
    rows = (await db.execute(
        text("SELECT CASE WHEN DATEDIFF(CURDATE(),created_at)<30 THEN '0-29' "
             "WHEN DATEDIFF(CURDATE(),created_at)<60 THEN '30-59' "
             "WHEN DATEDIFF(CURDATE(),created_at)<90 THEN '60-89' ELSE '90+' END bucket,"
             "SUM(qty_in-qty_consumed),SUM((qty_in-qty_consumed)*unit_cost+"
             "(additional_cost-additional_cost_consumed)) "
             "FROM inventory_cost_layers WHERE tenant_id=:tid AND qty_in>qty_consumed GROUP BY bucket "
             "ORDER BY MIN(DATEDIFF(CURDATE(),created_at))"),
        {"tid": tenant_id},
    )).fetchall()
    return {"buckets": [{"bucket": row[0], "qty": str(row[1] or 0),
                          "value": str(row[2] or 0)} for row in rows],
            "total_value": str(sum((Decimal(str(row[2] or 0)) for row in rows), Decimal("0")))}


async def anomaly_report(db, tenant_id: int) -> list[dict]:
    rows = (await db.execute(
        text("SELECT 'failed_operation' kind,id,error_message detail,created_at FROM inventory_operations "
             "WHERE tenant_id=:tid AND state='failed' UNION ALL "
             "SELECT 'unallocated',id,reason,created_at FROM inventory_transactions "
             "WHERE tenant_id=:tid AND unallocated=1 UNION ALL "
             "SELECT 'expired_batch',id,CONCAT('batch ',batch_no,' expired'),created_at "
             "FROM inventory_batches WHERE tenant_id=:tid AND expires_on<CURDATE() "
             "ORDER BY created_at DESC LIMIT 500"),
        {"tid": tenant_id},
    )).fetchall()
    return [{"kind": row[0], "id": row[1], "detail": row[2], "created_at": str(row[3])}
            for row in rows]


async def fulfillment_report(db, tenant_id: int) -> list[dict]:
    rows = (await db.execute(
        text("SELECT order_id,SUM(reserved_qty),SUM(picked_qty),SUM(shipped_qty),SUM(returned_qty) "
             "FROM inventory_allocations WHERE tenant_id=:tid GROUP BY order_id ORDER BY order_id DESC LIMIT 500"),
        {"tid": tenant_id},
    )).fetchall()
    return [{"order_id": row[0], "reserved_qty": str(row[1]), "picked_qty": str(row[2]),
             "shipped_qty": str(row[3]), "returned_qty": str(row[4])} for row in rows]


async def supplier_report(db, tenant_id: int) -> list[dict]:
    rows = (await db.execute(
        text("SELECT s.id,s.name,COUNT(DISTINCT p.id),"
             "COUNT(DISTINCT CASE WHEN p.status IN ('received','closed') THEN p.id END),"
             "AVG(CASE WHEN p.planned_arrival_date IS NOT NULL AND o.actual_at IS NOT NULL "
             "THEN DATEDIFF(DATE(o.actual_at),p.planned_arrival_date) END) "
             "FROM inventory_suppliers s LEFT JOIN inventory_purchase_orders p "
             "ON p.tenant_id=s.tenant_id AND p.supplier_id=s.id LEFT JOIN inventory_operations o "
             "ON o.tenant_id=p.tenant_id AND o.source_doc_type='purchase_order' AND o.source_doc_id=p.id "
             "AND o.operation_type='receipt' AND o.state='done' WHERE s.tenant_id=:tid "
             "GROUP BY s.id,s.name ORDER BY s.name"),
        {"tid": tenant_id},
    )).fetchall()
    return [{"supplier_id": row[0], "supplier_name": row[1], "po_count": int(row[2] or 0),
             "completed_count": int(row[3] or 0),
             "average_days_late": str(row[4]) if row[4] is not None else None} for row in rows]