#!/usr/bin/env python3
import pandas as pd
from datetime import date, timedelta, datetime
from pathlib import Path

# Settings
HORIZON_DAYS = 90

def parse_date(d):
    if pd.isna(d) or d == "" or d is None:
        return None
    if isinstance(d, (pd.Timestamp,)):
        return d.date()
    s = str(d).strip()
    # Try several common formats
    fmts = [
        "%Y-%m-%d",  # 2025-10-22
        "%m/%d/%Y",  # 10/22/2025
        "%m/%d/%y",  # 10/22/25
        "%Y/%m/%d",  # 2025/10/22
        "%d-%b-%Y",  # 22-Oct-2025
    ]
    for f in fmts:
        try:
            return datetime.strptime(s, f).date()
        except ValueError:
            pass
    # Last resort: let pandas try
    try:
        return pd.to_datetime(s).date()
    except Exception:
        raise ValueError(f"Unrecognized date format: {s} (use YYYY-MM-DD or mm/dd/yy)")

def occurs_on(rec, day):
    """Return amount if this recurring item fires on a given day, else 0."""
    rtype = str(rec.get("type", "")).strip().lower()
    amount = float(rec["amount"])
    start_date = parse_date(rec.get("start_date", ""))
    end_date = parse_date(rec.get("end_date", ""))

    # Window check
    if start_date and day < start_date:
        return 0.0
    if end_date and day > end_date:
        return 0.0

    if rtype == "monthly_dom":
        dom = rec.get("day_of_month", "")
        try:
            dom = int(dom)
        except:
            return 0.0
        # If the month doesn't have that many days (e.g., 31), pay on the last day of month.
        next_month = date(day.year + (day.month // 12), (day.month % 12) + 1, 1)
        last_dom = (next_month - timedelta(days=1)).day
        actual_dom = min(dom, last_dom)
        return amount if day.day == actual_dom else 0.0

    if rtype == "every_n_days":
        interval = rec.get("interval_days", "")
        try:
            interval = int(interval)
        except:
            return 0.0
        if not start_date:
            return 0.0
        delta = (day - start_date).days
        return amount if delta >= 0 and (delta % interval == 0) else 0.0

    if rtype == "weekly":
        # weekday: 1=Monday .. 7=Sunday (Excel-style user mapping)
        wd = rec.get("weekday", "")
        try:
            wd = int(wd)
        except:
            return 0.0
        # Python weekday(): Monday=0..Sunday=6
        return amount if (day.weekday() == wd - 1) else 0.0

    return 0.0

def build_transactions(start_day: date, horizon_days: int, recurring_csv: Path, oneoff_csv: Path) -> pd.DataFrame:
    # Recurring
    rec = pd.read_csv(recurring_csv).fillna("")
    for col in ["day_of_month", "interval_days", "weekday"]:
        if col in rec.columns:
            rec[col] = pd.to_numeric(rec[col], errors="coerce")
    # Keep original order to make same-day recurring items stable
    rec = rec.reset_index().rename(columns={"index": "rec_order"})

    # One-offs
    oo = pd.read_csv(oneoff_csv).fillna("")
    if not oo.empty:
        oo["date"] = oo["date"].apply(parse_date)

    days = [start_day + timedelta(days=i) for i in range(horizon_days)]
    rows = []

    for d in days:
        # One-offs on this date (Starting Balance first if present)
        if not oo.empty:
            todays = oo[oo["date"] == d].copy()
            if not todays.empty:
                todays["is_starting"] = todays["name"].astype(str).str.contains("Starting Balance", case=False, na=False)
                todays["oo_order"] = (~todays["is_starting"]).astype(int)  # 0 for starting balance, 1 for others
                todays = todays.sort_values(["oo_order", "name"])
                for _, row in todays.iterrows():
                    rows.append({
                        "date": d,
                        "name": str(row["name"]),
                        "amount": float(row["amount"]),
                        "rec_order": 999999,  # ensure one-offs come before recurring when sorting
                        "is_recurring": False,
                        "is_starting": bool(row["is_starting"])
                    })

        # Recurring on this date
        for _, r in rec.iterrows():
            amt = occurs_on(r, d)
            if abs(amt) > 1e-9:
                rows.append({
                    "date": d,
                    "name": str(r["name"]),
                    "amount": float(amt),
                    "rec_order": int(r["rec_order"]),
                    "is_recurring": True,
                    "is_starting": False
                })

    # Build ledger DataFrame
    df = pd.DataFrame(rows)
    if df.empty:
        return pd.DataFrame(columns=["date", "name", "amount", "running_balance"])

    # Sort: date asc, then Starting Balance first, then one-offs, then recurring by rec_order, then name
    df = df.sort_values(
        by=["date", "is_starting", "is_recurring", "rec_order", "name"],
        ascending=[True, False, True, True, True]
    ).reset_index(drop=True)

    # Running balance (cumulative sum)
    df["running_balance"] = df["amount"].cumsum()

    # Final columns
    df = df[["date", "name", "amount", "running_balance"]]
    return df

def main():
    base = Path(".")
    recurring_csv = base / "recurring.csv"
    oneoff_csv = base / "oneoff.csv"
    out_xlsx = base / "forecast_90d.xlsx"

    start_day = date.today()
    df = build_transactions(start_day, HORIZON_DAYS, recurring_csv, oneoff_csv)

    # Save Excel with formatting
    with pd.ExcelWriter(out_xlsx, engine="xlsxwriter", datetime_format="yyyy-mm-dd", date_format="yyyy-mm-dd") as writer:
        df.to_excel(writer, index=False, sheet_name="Ledger")
        # Include inputs for easy editing
        pd.read_csv(recurring_csv).to_excel(writer, index=False, sheet_name="Recurring")
        pd.read_csv(oneoff_csv).to_excel(writer, index=False, sheet_name="OneOff")

        wb  = writer.book
        ws  = writer.sheets["Ledger"]
        money = wb.add_format({"num_format": "$#,##0.00"})
        datefmt = wb.add_format({"num_format": "yyyy-mm-dd"})

        ws.set_column("A:A", 12, datefmt)  # Date
        ws.set_column("B:B", 36)           # Name
        ws.set_column("C:D", 16, money)    # Amount, Running Balance

        # Highlight negative running balances
        red_fmt = wb.add_format({"font_color": "#9C0006"})
        ws.conditional_format("D2:D1048576", {
            "type": "cell",
            "criteria": "<",
            "value": 0,
            "format": red_fmt
        })

if __name__ == "__main__":
    main()