from __future__ import annotations
import pandas as pd

REQUIRED_COLS = {"order_date", "sku", "quantity"}

def load_orders(csv_path: str) -> pd.DataFrame:
    # Robust read: auto-detect delimiter, keep quotes, skip bad lines
    df = pd.read_csv(
        csv_path,
        sep=None,            # sniff delimiter (comma/semicolon/tab)
        engine="python",
        quotechar='"',
        on_bad_lines="skip",
    )

    # 1) Normalize headers
    df.columns = [str(c).strip().lower() for c in df.columns]
    cols = set(df.columns)

    # 2) ---- Map/derive required columns ------------------------------------
    # order_date
    if "order_date" in cols:
        df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
    elif "order_ts" in cols:
        # your real file has order_ts → normalize to date
        df["order_date"] = pd.to_datetime(df["order_ts"], errors="coerce").dt.normalize()
    elif "date" in cols:
        df["order_date"] = pd.to_datetime(df["date"], errors="coerce")
    elif "order date" in cols:
        df["order_date"] = pd.to_datetime(df["order date"], errors="coerce")
    elif "orderdate" in cols:
        df["order_date"] = pd.to_datetime(df["orderdate"], errors="coerce")
    else:
        raise KeyError("No order_date/order_ts/date column found")

    # sku (prefer true SKU/product id; last-resort fallback to order_id so pipeline can run)
    if "sku" in cols:
        df["sku"] = df["sku"]
    elif "product_id" in cols:
        df["sku"] = df["product_id"]
    elif "item_id" in cols:
        df["sku"] = df["item_id"]
    elif "customer_id" in cols or "customer id" in cols:
        df["sku"] = df.get("customer_id", df.get("customer id"))
    elif "order_id" in cols:
        print("⚠️  No sku/product_id/item_id/customer_id found — using order_id as SKU surrogate.")
        df["sku"] = df["order_id"]
    else:
        raise KeyError("No sku/product_id/item_id/customer_id/order_id column found")

    # quantity
    if "quantity" in cols:
        df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce").fillna(0)
    elif "qty" in cols:
        df["quantity"] = pd.to_numeric(df["qty"], errors="coerce").fillna(0)
    elif "units" in cols:
        df["quantity"] = pd.to_numeric(df["units"], errors="coerce").fillna(0)
    elif "count" in cols:
        df["quantity"] = pd.to_numeric(df["count"], errors="coerce").fillna(0)
    else:
        # if completely missing, assume 1 per row
        df["quantity"] = 1.0

    # 3) ---- Final cleanup & checks -----------------------------------------
    df = df.dropna(subset=["order_date"])
    df["quantity"] = df["quantity"].astype(float)

    missing = REQUIRED_COLS - set(df.columns)
    if missing:
        raise KeyError(f"Missing required columns after normalization: {missing}. "
                       f"Found columns: {list(df.columns)}")

    return df
def daily_series_for_sku(df: pd.DataFrame, sku: str) -> pd.Series:
    """
    Convert orders dataframe to daily time series for a specific SKU.
    
    Args:
        df: Orders dataframe with columns order_date, sku, quantity
        sku: SKU identifier to filter for
        
    Returns:
        pd.Series indexed by date with daily quantities
    """
    # Filter for the specific SKU
    sku_data = df[df["sku"] == sku].copy()
    
    if sku_data.empty:
        raise ValueError(f"No data found for SKU: {sku}")
    
    # Group by date and sum quantities
    daily = sku_data.groupby("order_date")["quantity"].sum()
    
    # Fill missing dates with 0
    daily = daily.asfreq("D", fill_value=0)
    
    return daily