# Lesson 12: project - monthly report automation
# Reads every CSV in the 'incoming' folder, combines them, and writes one Excel report per run.
import pandas as pd, pathlib, datetime as dt
src = pathlib.Path("incoming"); src.mkdir(exist_ok=True)
base = pd.read_csv("sales.csv", parse_dates=["date"])
for month, part in base.groupby(base["date"].dt.strftime("%Y-%m")):   # simulate monthly files arriving
    part.to_csv(src / f"sales_{month}.csv", index=False)
files = sorted(src.glob("sales_*.csv"))
df = pd.concat([pd.read_csv(f, parse_dates=["date"]).assign(source=f.name) for f in files], ignore_index=True)
df["amount"] = df["qty"] * df["rate"]
kpis = pd.DataFrame({"metric": ["Files", "Orders", "Total sales", "Average order"],
                     "value": [len(files), len(df), df["amount"].sum(), round(df["amount"].mean())]})
by_month = df.pivot_table(values="amount", index=df["date"].dt.strftime("%Y-%m"), columns="region", aggfunc="sum", fill_value=0)
out = f"sales_report_{dt.date(2026, 10, 4):%Y%m%d}.xlsx"     # use dt.date.today() in real use
with pd.ExcelWriter(out) as xw:
    kpis.to_excel(xw, sheet_name="KPIs", index=False)
    by_month.to_excel(xw, sheet_name="By month")
    df.to_excel(xw, sheet_name="All orders", index=False)
print(kpis.to_string(index=False))
print("Report written:", out)