# import pandas as pd

# def compute_kpis(orders: pd.DataFrame, order_status: pd.DataFrame, inventory: pd.DataFrame, skus: pd.DataFrame):
#     merged = orders.merge(order_status, on="order_id", how="left")
#     merged["on_time"] = (pd.to_datetime(merged["actual_deliver_ts"], errors="coerce")
#                          <= pd.to_datetime(merged["promised_deliver_ts"], errors="coerce"))
#     delivered = merged[merged["status"]=="delivered"]
#     on_time_pct = (delivered["on_time"].mean() if len(delivered) else 0.0) * 100.0

#     inv = inventory.merge(skus[["sku_id","unit_cost"]], on="sku_id", how="left")
#     inv["value"] = inv["on_hand"] * inv["unit_cost"]
#     inventory_value = float(inv["value"].sum())

#     backorders = int((inventory["reserved"] > inventory["on_hand"]).sum())

#     return {
#         "on_time_pct": round(on_time_pct,1),
#         "inventory_value": round(inventory_value,2),
#         "backorders": backorders,
#         "return_rate": 0.0,
#     }
import pandas as pd

def compute_kpis(orders: pd.DataFrame, inventory: pd.DataFrame, skus: pd.DataFrame):

    today = pd.Timestamp.today()

    # =========================
    # ON-TIME DELIVERY %
    # =========================
    orders = orders.copy()

    # Ensure estimated_delivery exists
    if "estimated_delivery" not in orders.columns:
        orders["estimated_delivery"] = None

    orders["estimated_delivery"] = pd.to_datetime(orders["estimated_delivery"], errors="coerce")
    orders["order_date"] = pd.to_datetime(orders["order_date"], errors="coerce")

    # Define: delivered on time = delivered AND expected delivery was today or earlier
    delivered = orders[orders["status"].str.lower() == "delivered"]

    if not delivered.empty:
        delivered["on_time"] = delivered["estimated_delivery"] <= today
        on_time_pct = delivered["on_time"].mean() * 100
    else:
        on_time_pct = 0.0

    # =========================
    # INVENTORY VALUE
    # =========================
    inv = inventory.copy()

    # normalize on_hand
    if "on_hand" not in inv.columns:
        for alt in ("q_on_hand", "qty_on_hand", "stock"):
            if alt in inv.columns:
                inv = inv.rename(columns={alt: "on_hand"})
                break
        else:
            inv["on_hand"] = 0

    # normalize sku
    if "sku" not in inv.columns:
        if "product_id" in inv.columns:
            inv = inv.rename(columns={"product_id": "sku"})

    # normalize cost
    if "unit_cost" not in skus.columns:
        for alt in ("cost", "price", "unit_price"):
            if alt in skus.columns:
                skus = skus.rename(columns={alt: "unit_cost"})
                break
        else:
            skus["unit_cost"] = 0

    # merge for value calculation
    inv = inv.merge(skus[["sku", "unit_cost"]], on="sku", how="left")
    inv["value"] = inv["on_hand"] * inv["unit_cost"]
    inventory_value = float(inv["value"].sum())

    # =========================
    # BACKORDERS
    # =========================
    if "reserved" not in inventory.columns:
        inventory["reserved"] = 0

    if "on_hand" not in inventory.columns:
        inventory["on_hand"] = 0

    backorders = int((inventory["reserved"] > inventory["on_hand"]).sum())

    return {
        "on_time_pct": round(on_time_pct, 1),
        "inventory_value": round(inventory_value, 2),
        "backorders": backorders,
        "return_rate": 0.0,  # placeholder for future
    }
