"""Lesson 10: project - monthly report pipeline: read every CSV in monthly/, combine, clean, summarise, write Excel."""
import json, logging, sys
from pathlib import Path
import pandas as pd
HERE = Path(__file__).resolve().parent
logging.basicConfig(level=logging.INFO, format="%(levelname)s %(message)s", handlers=[logging.StreamHandler(sys.stdout)])

def load(folder):
    frames = []
    for f in sorted(folder.glob("sales-*.csv")):
        df = pd.read_csv(f); df["source"] = f.name; frames.append(df)
        logging.info("%s: %d rows", f.name, len(df))
    return pd.concat(frames, ignore_index=True)

def enrich(df):
    prod = pd.DataFrame(json.load(open(HERE / "products.json"))).T.rename_axis("code").reset_index()
    cust = pd.read_csv(HERE / "customers.csv")
    df = df.merge(prod, on="code").merge(cust, on="customer_id")
    df["amount"] = df["qty"] * df["price"].astype(float)
    df["gst_amt"] = (df["amount"] * df["gst"].astype(float)).round(2)
    df["month"] = pd.to_datetime(df["date"]).dt.strftime("%Y-%m")
    return df

df = enrich(load(HERE / "monthly"))
kpi = pd.Series({"invoices": len(df), "taxable": df["amount"].sum(), "gst": df["gst_amt"].sum(),
                 "collected %": round(100 * df.loc[df["status"] == "Paid", "amount"].sum() / df["amount"].sum(), 1)})
for k, v in kpi.items(): print(f"{k:<12} {v:>12,.0f}" if k == "invoices" else f"{k:<12} {v:>12,.2f}")
by_month = df.pivot_table(index="month", columns="region", values="amount", aggfunc="sum", fill_value=0)
out = HERE / "output"; out.mkdir(exist_ok=True)
with pd.ExcelWriter(out / "monthly-report.xlsx") as xw:
    kpi.to_frame("value").to_excel(xw, sheet_name="KPIs")
    by_month.to_excel(xw, sheet_name="By month")
    df.groupby("rep")["amount"].sum().sort_values(ascending=False).to_frame().to_excel(xw, sheet_name="By rep")
print(by_month.round(0).astype(int).to_string())
print("Saved output/monthly-report.xlsx")