#!/usr/bin/env python3
# quickbooks.py - QuickBooks data query tool for Harvey
#
# Usage:
#   python3 tools/quickbooks.py pl [months]          # P&L report (default: current month)
#   python3 tools/quickbooks.py revenue [months]     # Revenue summary
#   python3 tools/quickbooks.py expenses [months]    # Top expenses
#   python3 tools/quickbooks.py invoices             # Outstanding invoices
#   python3 tools/quickbooks.py cashflow [months]    # Cash flow
#   python3 tools/quickbooks.py summary              # Full financial snapshot

import sys
import json
import os
import re
import subprocess
from datetime import date, timedelta
from dateutil.relativedelta import relativedelta
import calendar

SECRETS = os.path.expanduser("~/.openclaw/secrets")

def read_secret(filename):
    path = os.path.join(SECRETS, filename)
    return re.sub(r'\s+', '', open(path).read())

def get_access_token():
    """Get valid access token, refreshing if needed."""
    client_id = read_secret("quickbooks-client-id.txt")
    client_secret = read_secret("quickbooks-client-secret.txt")
    refresh_token = read_secret("quickbooks-refresh-token.txt")

    result = subprocess.run([
        "curl", "-s", "-X", "POST",
        "https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer",
        "-H", "Content-Type: application/x-www-form-urlencoded",
        "-H", "Accept: application/json",
        "-u", f"{client_id}:{client_secret}",
        "-d", f"grant_type=refresh_token&refresh_token={refresh_token}"
    ], capture_output=True, text=True)

    tokens = json.loads(result.stdout)
    if "access_token" not in tokens:
        raise Exception(f"Token refresh failed: {tokens}")

    # Save new tokens
    open(os.path.join(SECRETS, "quickbooks-access-token.txt"), "w").write(tokens["access_token"])
    if "refresh_token" in tokens:
        open(os.path.join(SECRETS, "quickbooks-refresh-token.txt"), "w").write(tokens["refresh_token"])

    return tokens["access_token"]

def qb_report(report_name, params):
    """Call a QuickBooks report endpoint."""
    token = get_access_token()
    realm_id = read_secret("quickbooks-realm-id.txt")

    query = "&".join(f"{k}={v}" for k, v in params.items())
    url = f"https://quickbooks.api.intuit.com/v3/company/{realm_id}/reports/{report_name}?{query}&minorversion=65"

    result = subprocess.run([
        "curl", "-s", url,
        "-H", f"Authorization: Bearer {token}",
        "-H", "Accept: application/json"
    ], capture_output=True, text=True)

    return json.loads(result.stdout)

def qb_query(sql):
    """Run a QuickBooks query."""
    token = get_access_token()
    realm_id = read_secret("quickbooks-realm-id.txt")
    import urllib.parse

    url = f"https://quickbooks.api.intuit.com/v3/company/{realm_id}/query?query={urllib.parse.quote(sql)}&minorversion=65"

    result = subprocess.run([
        "curl", "-s", url,
        "-H", f"Authorization: Bearer {token}",
        "-H", "Accept: application/json"
    ], capture_output=True, text=True)

    return json.loads(result.stdout)

def fmt_money(val):
    try:
        return f"${float(val):,.2f}"
    except:
        return str(val)

def get_date_range(months_back=0):
    today = date.today()
    if months_back == 0:
        start = today.replace(day=1)
        end = today
    else:
        start = (today.replace(day=1) - relativedelta(months=months_back-1))
        end = today
    return start.strftime("%Y-%m-%d"), end.strftime("%Y-%m-%d")

def extract_rows(data, section_name=None):
    """Recursively extract rows from QB report structure."""
    rows = []
    for section in data.get("Rows", {}).get("Row", []):
        if section_name and section.get("group") != section_name:
            # Try to find nested
            sub = extract_rows(section, section_name)
            rows.extend(sub)
            continue
        if section.get("type") == "Section":
            rows.extend(extract_rows(section))
        elif section.get("type") == "Data":
            cols = section.get("ColData", [])
            if cols:
                rows.append(cols)
        elif "Rows" in section:
            rows.extend(extract_rows(section))
    return rows

def cmd_pl(months=1):
    """Profit & Loss report."""
    start, end = get_date_range(months)
    print(f"\n📊 Profit & Loss: {start} → {end}\n")

    data = qb_report("ProfitAndLoss", {
        "start_date": start,
        "end_date": end,
        "accounting_method": "Accrual"
    })

    def print_section(rows_data, indent=0):
        for row in rows_data.get("Row", []):
            if row.get("type") == "Section":
                header = row.get("Header", {}).get("ColData", [{}])[0].get("value", "")
                if header:
                    print(f"{'  ' * indent}{'─'*30}")
                    print(f"{'  ' * indent}{header.upper()}")
                print_section(row.get("Rows", {}), indent+1)
                summary = row.get("Summary", {}).get("ColData", [])
                if summary and len(summary) > 1:
                    print(f"{'  ' * indent}  {'Total ' + summary[0].get('value',''):30} {fmt_money(summary[1].get('value',0))}")
            elif row.get("type") == "Data":
                cols = row.get("ColData", [])
                if len(cols) >= 2:
                    name = cols[0].get("value", "")
                    val = cols[1].get("value", "0")
                    if name and float(val or 0) != 0:
                        print(f"{'  ' * indent}  {name:35} {fmt_money(val)}")

    print_section(data.get("Rows", {}))

def cmd_revenue(months=3):
    """Revenue summary."""
    start, end = get_date_range(months)
    print(f"\n💰 Revenue: {start} → {end}\n")

    data = qb_report("ProfitAndLoss", {
        "start_date": start,
        "end_date": end,
        "accounting_method": "Accrual"
    })

    # Find Income section
    for row in data.get("Rows", {}).get("Row", []):
        if row.get("group") == "Income" or (row.get("type") == "Section" and
           row.get("Header", {}).get("ColData", [{}])[0].get("value", "").lower() in ["income", "revenue"]):
            for sub in row.get("Rows", {}).get("Row", []):
                if sub.get("type") == "Data":
                    cols = sub.get("ColData", [])
                    if len(cols) >= 2 and cols[1].get("value"):
                        print(f"  {cols[0].get('value',''):40} {fmt_money(cols[1].get('value',0))}")
            summary = row.get("Summary", {}).get("ColData", [])
            if summary and len(summary) > 1:
                print(f"\n  {'TOTAL REVENUE':40} {fmt_money(summary[1].get('value',0))}")

def cmd_expenses(months=1):
    """Top expenses breakdown."""
    start, end = get_date_range(months)
    print(f"\n💸 Expenses: {start} → {end}\n")

    data = qb_report("ProfitAndLoss", {
        "start_date": start,
        "end_date": end,
        "accounting_method": "Accrual"
    })

    expenses = []
    def collect_expenses(rows_data):
        for row in rows_data.get("Row", []):
            if row.get("type") == "Section":
                header = row.get("Header", {}).get("ColData", [{}])[0].get("value", "").lower()
                if any(x in header for x in ["expense", "cost", "operating"]):
                    for sub in row.get("Rows", {}).get("Row", []):
                        if sub.get("type") == "Data":
                            cols = sub.get("ColData", [])
                            if len(cols) >= 2:
                                try:
                                    expenses.append((cols[0].get("value",""), float(cols[1].get("value",0) or 0)))
                                except:
                                    pass
                collect_expenses(row.get("Rows", {}))

    collect_expenses(data.get("Rows", {}))
    expenses.sort(key=lambda x: x[1], reverse=True)

    total = 0
    for name, val in expenses[:15]:
        if val > 0:
            print(f"  {name:40} {fmt_money(val)}")
            total += val
    print(f"\n  {'TOTAL':40} {fmt_money(total)}")

def cmd_invoices():
    """Outstanding invoices."""
    print("\n🧾 Outstanding Invoices\n")

    data = qb_query("SELECT * FROM Invoice WHERE Balance > '0' ORDER BY DueDate ASC MAXRESULTS 20")
    invoices = data.get("QueryResponse", {}).get("Invoice", [])

    if not invoices:
        print("  No outstanding invoices.")
        return

    total = 0
    for inv in invoices:
        customer = inv.get("CustomerRef", {}).get("name", "Unknown")
        balance = float(inv.get("Balance", 0))
        due = inv.get("DueDate", "N/A")
        doc_num = inv.get("DocNumber", "")
        total += balance
        overdue = " ⚠️ OVERDUE" if due < date.today().isoformat() else ""
        print(f"  #{doc_num:6} {customer:30} {fmt_money(balance):12} Due: {due}{overdue}")

    print(f"\n  {'TOTAL OUTSTANDING':40} {fmt_money(total)}")

def cmd_cashflow(months=1):
    """Cash flow summary."""
    start, end = get_date_range(months)
    print(f"\n💵 Cash Flow: {start} → {end}\n")

    data = qb_report("CashFlow", {
        "start_date": start,
        "end_date": end
    })

    def print_cf(rows_data, indent=0):
        for row in rows_data.get("Row", []):
            if row.get("type") == "Section":
                header = row.get("Header", {}).get("ColData", [{}])[0].get("value", "")
                if header:
                    print(f"\n{'  '*indent}{header}")
                print_cf(row.get("Rows", {}), indent+1)
                summary = row.get("Summary", {}).get("ColData", [])
                if summary and len(summary) > 1:
                    print(f"{'  '*indent}  {'Total':35} {fmt_money(summary[1].get('value',0))}")
            elif row.get("type") == "Data":
                cols = row.get("ColData", [])
                if len(cols) >= 2 and float(cols[1].get("value",0) or 0) != 0:
                    print(f"{'  '*indent}  {cols[0].get('value',''):35} {fmt_money(cols[1].get('value',0))}")

    print_cf(data.get("Rows", {}))

def cmd_summary():
    """Full financial snapshot."""
    print("=" * 50)
    print("  QUICKBOOKS FINANCIAL SNAPSHOT")
    print(f"  As of {date.today().isoformat()}")
    print("=" * 50)
    cmd_revenue(1)
    cmd_expenses(1)
    cmd_invoices()

def main():
    # Install dateutil if needed
    try:
        from dateutil.relativedelta import relativedelta
    except ImportError:
        subprocess.run([sys.executable, "-m", "pip", "install", "python-dateutil", "-q"])
        from dateutil.relativedelta import relativedelta

    args = sys.argv[1:]
    cmd = args[0] if args else "summary"
    months = int(args[1]) if len(args) > 1 else 1

    cmds = {
        "pl": cmd_pl,
        "revenue": cmd_revenue,
        "expenses": cmd_expenses,
        "invoices": cmd_invoices,
        "cashflow": cmd_cashflow,
        "summary": cmd_summary,
    }

    if cmd not in cmds:
        print(f"Usage: quickbooks.py [{'|'.join(cmds.keys())}] [months]")
        sys.exit(1)

    if cmd in ["pl", "revenue", "expenses", "cashflow"]:
        cmds[cmd](months)
    else:
        cmds[cmd]()

if __name__ == "__main__":
    main()