"""Merge monthly sales CSVs with different layouts into one tidy file plus a store x month summary.
Usage: python merge_months.py input/   -> merged_sales.csv, monthly_summary.csv, merge_log.txt"""
import csv, re, sys, pathlib
from datetime import datetime
from collections import defaultdict
ALIASES = {"date": ["date", "order date", "sold on"], "store": ["store", "branch", "store_name", "location"],
           "product": ["product", "item", "sku_name", "product name"], "qty": ["qty", "quantity", "units"],
           "amount": ["amount", "total ($)", "net_amount", "value"]}
DATE_FORMATS = ["%Y-%m-%d", "%d/%m/%Y", "%d-%b-%Y", "%Y/%m/%d"]
def parse_date(s):
    for f in DATE_FORMATS:
        try: return datetime.strptime(s.strip(), f).date()
        except ValueError: pass
    raise ValueError(s)
def money(s): return round(float(re.sub(r"[^0-9.\-]", "", s)), 2)
def store(s): return re.sub(r"\s+store$", "", s.strip(), flags=re.I).title()
def main(folder):
    out, log, summary = [], [], defaultdict(float)
    for path in sorted(pathlib.Path(folder).glob("*.csv")):
        rows = list(csv.reader(open(path, newline="")))
        hdr = [h.strip().lower() for h in rows[0]]
        idx = {k: next(i for i, h in enumerate(hdr) if h in v) for k, v in ALIASES.items()}
        kept = skipped = 0
        for r in rows[1:]:
            if not r or not r[idx["date"]].strip() or r[idx["date"]].strip().upper() == "TOTAL": skipped += 1; continue
            d = parse_date(r[idx["date"]]); rec = {"date": d.isoformat(), "month": d.strftime("%Y-%m"), "store": store(r[idx["store"]]),
                   "product": r[idx["product"]].strip(), "qty": int(r[idx["qty"]]), "amount": money(r[idx["amount"]]), "source_file": path.name}
            out.append(rec); summary[(rec["store"], rec["month"])] += rec["amount"]; kept += 1
        log.append(f"{path.name}: {kept} rows kept, {skipped} blank/total rows skipped, columns mapped {idx}")
    out.sort(key=lambda r: (r["date"], r["store"]))
    with open("merged_sales.csv", "w", newline="") as f:
        w = csv.DictWriter(f, fieldnames=list(out[0])); w.writeheader(); w.writerows(out)
    months = sorted({m for _, m in summary}); stores = sorted({s for s, _ in summary})
    with open("monthly_summary.csv", "w", newline="") as f:
        w = csv.writer(f); w.writerow(["store"] + months + ["total"])
        for s in stores: vals = [round(summary[(s, m)], 2) for m in months]; w.writerow([s] + vals + [round(sum(vals), 2)])
    pathlib.Path("merge_log.txt").write_text("\n".join(log) + f"\nTotal rows merged: {len(out)}\nStores after cleanup: {', '.join(stores)}\n")
if __name__ == "__main__": main(sys.argv[1] if len(sys.argv) > 1 else "input")