import csv, collections, sys
GL, MAP = "general-ledger.csv", "account-mapping.csv"
amap = {r["account"]: r for r in csv.DictReader(open(MAP))}

# 1) Actuals: monthly P&L movements and balance-sheet closing balances by line
pl = collections.defaultdict(lambda: collections.defaultdict(float))
bal = collections.defaultdict(float); bs = {}
unmapped = set()
rows = sorted(csv.DictReader(open(GL)), key=lambda r: r["date"])
months = sorted({r["date"][:7] for r in rows})
for m in months:
    for r in (r for r in rows if r["date"][:7] == m):
        a = amap.get(r["account"])
        if not a: unmapped.add(r["account"]); continue
        v = float(r["debit"]) - float(r["credit"])
        if a["statement"] == "PL": pl[m][a["line"]] += v
        else: bal[a["line"]] += v
    bs[m] = dict(bal)
assert not unmapped, f"Unmapped accounts: {unmapped}"
tb = sum(float(r["debit"]) - float(r["credit"]) for r in rows)
assert abs(tb) < 0.01, "General ledger does not balance"

SCEN = {  # driver: (base, optimistic, pessimistic)
 "growth": (0.05, 0.10, -0.05), "gm_shift": (0.0, 0.01, -0.02),
 "dso": (60, 55, 70), "dpo": (45, 45, 38), "dio": (55, 52, 62)}
CAPEX = {"2027-03": 30000}; INFL = 0.03; TAX = 0.25; RATE = 0.045; INSTAL = 5000
last = months[-1]; hist12 = months[-12:]
rev12 = -sum(pl[m]["Revenue"] for m in hist12)
gm12 = 1 - sum(pl[m]["COGS"] for m in hist12) / rev12
opex3 = sum(pl[m]["Opex"] for m in months[-3:]) / 3
pay3 = sum(pl[m]["Payroll"] for m in months[-3:]) / 3

def run(k):
    g = {d: v[k] for d, v in SCEN.items()}
    o = {l: -v if l not in ("Cash","Receivables","Inventory","PPE") else v for l, v in bs[last].items()}
    eq = o["Equity"] + -sum(sum(pl[m].values()) for m in months)  # equity incl. retained earnings
    cash, ar, inv, ppe, ap, taxp, debt = o["Cash"], o["Receivables"], o["Inventory"], o["PPE"], o["Payables"], o["Tax payable"], o["Debt"]
    out = []
    for i in range(12):
        y, mo = divmod(int(last[5:]) + i, 12); m = f"{int(last[:4]) + y}-{mo + 1:02d}"
        ly = f"{int(m[:4]) - 1}{m[4:]}"
        rev = -pl[ly]["Revenue"] * (1 + g["growth"])
        cogs = rev * (1 - (gm12 + g["gm_shift"]))
        opex = opex3 * (1 + INFL); payroll = pay3 * (1 + (INFL if m >= "2027-01" else 0))
        capex = CAPEX.get(m, 0); dep = 3000 + sum(c / 60 for cm, c in CAPEX.items() if cm < m)
        interest = debt * RATE / 12
        pbt = rev - cogs - opex - payroll - dep - interest
        tax = max(pbt, 0) * TAX; ni = pbt - tax
        ar1, inv1 = rev * g["dso"] / 30, cogs * g["dio"] / 30
        purch = cogs + inv1 - inv; ap1 = purch * g["dpo"] / 30
        taxpaid = taxp if m.endswith("-07") else 0
        cfo = ni + dep - (ar1 - ar) - (inv1 - inv) + (ap1 - ap) + (tax - taxpaid)
        cfi = -capex; cff = -min(INSTAL, debt)
        cash += cfo + cfi + cff
        ar, inv, ap = ar1, inv1, ap1; ppe += capex - dep; taxp += tax - taxpaid; debt += cff; eq += ni
        check = round(cash + ar + inv + ppe - ap - taxp - debt - eq, 2)
        out.append(dict(month=m, revenue=rev, ebitda=rev-cogs-opex-payroll, net_income=ni, cfo=cfo,
                        cash=cash, receivables=ar, inventory=inv, payables=ap, debt=debt, equity=eq, check=check))
    return out

if __name__ == "__main__":
    names = ("base", "optimistic", "pessimistic")
    with open("forecast-output.csv", "w", newline="") as f:
        w = None
        for k, n in enumerate(names):
            for r in run(k):
                r = {"scenario": n, **{a: (round(b, 2) if isinstance(b, float) else b) for a, b in r.items()}}
                if not w: w = csv.DictWriter(f, fieldnames=list(r)); w.writeheader()
                w.writerow(r)
    print(f"actuals {months[0]}..{last}; LTM revenue {rev12:,.0f}; LTM gross margin {gm12:.1%}; opening cash {bs[last]['Cash']:,.0f}")
    for k, n in enumerate(names):
        o = run(k)
        print(n, f"rev {sum(r['revenue'] for r in o):,.0f}", f"ebitda {sum(r['ebitda'] for r in o):,.0f}",
              f"NI {sum(r['net_income'] for r in o):,.0f}", f"cash end {o[-1]['cash']:,.0f}",
              f"min cash {min(r['cash'] for r in o):,.0f} ({min(o, key=lambda r: r['cash'])['month']})",
              "checks", "OK" if all(abs(r['check']) < 0.01 for r in o) else "FAIL")