from datetime import date, datetime, timedelta
from typing import List, Dict, Tuple
from flask import Flask, request, jsonify
import pymysql
import os
import re
from dateutil import parser as dateparser

app = Flask(__name__)

DB = dict(
    host=os.getenv("DB_HOST","127.0.0.1"),
    user=os.getenv("DB_USER","root"),
    password=os.getenv("DB_PASS",""),
    database=os.getenv("DB_NAME","cashflow"),
    cursorclass=pymysql.cursors.DictCursor,
    autocommit=True
)

def db():
    return pymysql.connect(**DB)

def parse_date(s):
    if not s: return None
    s = str(s).strip()
    for fmt in ("%Y-%m-%d","%m/%d/%Y","%m/%d/%y","%Y/%m/%d","%d-%b-%Y"):
        try: return datetime.strptime(s, fmt).date()
        except: pass
    try:
        return datetime.fromisoformat(s).date()
    except:
        return dateparser.parse(s).date()

def month_last_day(d: date) -> int:
    nm = date(d.year + (d.month//12), (d.month%12)+1, 1)
    return (nm - timedelta(days=1)).day

def occurs_on(rec, day: date) -> float:
    rtype = (rec["rtype"] or "").lower()
    amount = float(rec["amount"])
    sd = rec["start_date"]; ed = rec["end_date"]
    if sd and day < sd: return 0.0
    if ed and day > ed: return 0.0

    if rtype == "monthly_dom":
        dom = rec["day_of_month"]
        if not dom: return 0.0
        actual_dom = min(int(dom), month_last_day(day))
        return amount if day.day == actual_dom else 0.0

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

    if rtype == "weekly":
        wd = rec["weekday"]  # 1..7 Mon..Sun
        if not wd: return 0.0
        return amount if (day.weekday() == int(wd) - 1) else 0.0

    return 0.0

def fetch_account(conn, account_id: int):
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM budget_accounts WHERE id=%s", (account_id,))
        acc = cur.fetchone()
        if not acc: raise ValueError("Account not found")
        return acc

def fetch_recurring(conn, user_id: int, account_id: int):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT * FROM budget_recurring_items
            WHERE user_id=%s AND account_id=%s
            ORDER BY display_order, id
        """, (user_id, account_id))
        rows = cur.fetchall()
        for r in rows:
            r["start_date"] = r["start_date"] if isinstance(r["start_date"], date) else (parse_date(r["start_date"]) if r["start_date"] else None)
            r["end_date"]   = r["end_date"]   if isinstance(r["end_date"], date) else (parse_date(r["end_date"]) if r["end_date"] else None)
        return rows

def fetch_oneoffs(conn, user_id: int, account_id: int, start: date, end: date):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT * FROM budget_oneoff_items
            WHERE user_id=%s AND account_id=%s AND tx_date BETWEEN %s AND %s
            ORDER BY tx_date, id
        """, (user_id, account_id, start, end))
        return cur.fetchall()

def build_ledger(conn, user_id: int, account_id: int, horizon_days: int) -> List[Dict]:
    acc = fetch_account(conn, account_id)
    start_today = date.today()
    end_day = start_today + timedelta(days=horizon_days)

    # Roll starting balance forward to today if needed
    start_balance_date = acc["starting_balance_date"]
    if not isinstance(start_balance_date, date):
        start_balance_date = parse_date(start_balance_date)

    balance = float(acc["starting_balance"])

    if start_balance_date and start_balance_date < start_today:
        recs = fetch_recurring(conn, acc["user_id"], account_id)
        days = [start_balance_date + timedelta(days=i) for i in range((start_today - start_balance_date).days)]
        for d in days[1:]:
            for r in recs:
                balance += occurs_on(r, d)
        for row in fetch_oneoffs(conn, acc["user_id"], account_id, start_balance_date + timedelta(days=1), start_today - timedelta(days=1)):
            balance += float(row["amount"])

    # Build horizon
    recs = fetch_recurring(conn, acc["user_id"], account_id)
    days = [start_today + timedelta(days=i) for i in range(horizon_days+1)]
    ledger = []

    ledger.append({"date": start_today, "name": "Opening Balance", "amount": 0.0, "running_balance": balance})

    oo = fetch_oneoffs(conn, acc["user_id"], account_id, start_today, end_day)
    by_day_oo = {}
    for row in oo:
        by_day_oo.setdefault(row["tx_date"], []).append(row)

    for d in days:
        # one-offs first
        for row in sorted(by_day_oo.get(d, []), key=lambda x: (x["name"].lower(), x["id"])):
            amt = float(row["amount"])
            balance += amt
            ledger.append({"date": d, "name": row["name"], "amount": amt, "running_balance": balance})
        # recurring
        for r in recs:
            amt = occurs_on(r, d)
            if abs(amt) > 1e-9:
                balance += amt
                ledger.append({"date": d, "name": r["name"], "amount": amt, "running_balance": balance})

    ledger.sort(key=lambda x: (x["date"], 0 if x["name"]=="Opening Balance" else 1, x["name"].lower()))
    return ledger

def compute_spendable_now(ledger: List[Dict], buffer: float = 0.0) -> Tuple[float, date]:
    min_row = min(ledger, key=lambda x: x["running_balance"])
    min_running = float(min_row["running_balance"])
    spendable = max(0.0, min_running - buffer)
    return (spendable, min_row["date"])

@app.get("/api/forecast")
def api_forecast():
    account_id = int(request.args.get("account_id", "1"))
    days = int(request.args.get("days", "90"))
    with db() as conn:
        acc = fetch_account(conn, account_id)
        ledger = build_ledger(conn, acc["user_id"], account_id, days)
        for row in ledger:
            row["date"] = row["date"].isoformat()
        return jsonify(ledger)

@app.get("/api/spendable_now")
def api_spendable_now():
    account_id = int(request.args.get("account_id", "1"))
    days = int(request.args.get("days", "90"))
    buffer = float(request.args.get("buffer", "0"))
    with db() as conn:
        acc = fetch_account(conn, account_id)
        ledger = build_ledger(conn, acc["user_id"], account_id, days)
        spendable, worst_day = compute_spendable_now(ledger, buffer=buffer)
        return jsonify({
            "account_id": account_id,
            "horizon_days": days,
            "buffer": buffer,
            "spendable_now": round(spendable, 2),
            "worst_day": worst_day.isoformat()
        })

@app.post("/api/recurring")
def api_create_recurring():
    data = request.json or {}
    with db() as conn, conn.cursor() as cur:
        cur.execute("""
            INSERT INTO budget_recurring_items
              (user_id, account_id, name, amount, rtype, day_of_month, interval_days, weekday, start_date, end_date, notes, display_order)
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
        """, (
            data["user_id"], data["account_id"], data["name"], data["amount"], data["rtype"],
            data.get("day_of_month"), data.get("interval_days"), data.get("weekday"),
            data.get("start_date"), data.get("end_date"), data.get("notes"), data.get("display_order", 0)
        ))
    return jsonify({"ok": True})

@app.post("/api/oneoff")
def api_create_oneoff():
    data = request.json or {}
    with db() as conn, conn.cursor() as cur:
        cur.execute("""
            INSERT INTO budget_oneoff_items (user_id, account_id, tx_date, name, amount, notes)
            VALUES (%s,%s,%s,%s,%s,%s)
        """, (
            data["user_id"], data["account_id"], data["tx_date"], data["name"], data["amount"], data.get("notes")
        ))
    return jsonify({"ok": True})

# --- Tiny AI quick-add (regex + dateparser) ---
@app.post("/api/nl")
def api_nl():
    text = (request.json or {}).get("text","")
    account_id = int((request.json or {}).get("account_id", 1))
    user_id = int((request.json or {}).get("user_id", 1))
    # amount
    m = re.search(r'([-+]?\$?\d+(?:\.\d{1,2})?)', text.replace(',', ''))
    amt = float(m.group(1).replace('$','')) if m else 0.0
    # default to expense unless hinted otherwise
    if not any(k in text.lower() for k in ("income","pay","refund","deposit","reimburse")):
        if amt > 0: amt = -amt
    # date
    try:
        tx_date = dateparser.parse(text, default=datetime.combine(date.today(), datetime.min.time())).date()
    except Exception:
        tx_date = date.today()
    # name
    cleaned = re.sub(r'\$?\d+(?:\.\d{1,2})?', '', text)
    name = cleaned.strip().title() or "Manual Entry"

    with db() as conn, conn.cursor() as cur:
        cur.execute("""
            INSERT INTO budget_oneoff_items (user_id, account_id, tx_date, name, amount)
            VALUES (%s,%s,%s,%s,%s)
        """, (user_id, account_id, tx_date, name, amt))
    return jsonify({"ok": True, "parsed": {"date": tx_date.isoformat(), "name": name, "amount": amt}})

if __name__ == "__main__":
    app.run(debug=True)