#!/usr/bin/env python3
"""
dashboard-data.py — NMC Operating Dashboard Data Fetcher
Pulls live data from QuickBooks, Airtable, Slack, GHL.
Writes www/dashboard-data.json for the dashboard to consume.

Usage:
  python3 scripts/dashboard-data.py           # update once
  python3 scripts/dashboard-data.py --watch   # loop every 5 min
  python3 scripts/dashboard-data.py --print   # print JSON to stdout

Add to cron:
  */15 * * * * python3 /path/to/workspace/scripts/dashboard-data.py
"""

import json, os, sys, subprocess, datetime, urllib.request, urllib.parse, re, time

BASE = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
SECRETS = os.path.expanduser("~/.openclaw/secrets")
OUT = os.path.join(BASE, "www", "dashboard-data.json")

def secret(name):
    try:
        return open(os.path.join(SECRETS, name)).read().strip()
    except:
        return None


# ── QuickBooks ────────────────────────────────────────────────────────────────

def qb_run(cmd):
    """Run the existing quickbooks.py tool and parse output."""
    try:
        result = subprocess.run(
            ["python3", os.path.join(BASE, "tools", "quickbooks.py")] + cmd.split(),
            capture_output=True, text=True, timeout=30, cwd=BASE
        )
        return result.stdout.strip()
    except Exception as e:
        return f"ERROR: {e}"

def get_qb_data():
    data = {}
    try:
        # P&L current month
        raw = qb_run("pl 1")
        lines = raw.split("\n")
        for line in lines:
            if "Revenue" in line or "Income" in line:
                m = re.search(r'\$?([\d,]+\.?\d*)', line.replace(",", ""))
                if m:
                    data["revenue_mtd"] = float(m.group(1).replace(",", ""))
            if "Expense" in line and "Total" in line:
                m = re.search(r'\$?([\d,]+\.?\d*)', line.replace(",", ""))
                if m:
                    data["expenses_mtd"] = float(m.group(1).replace(",", ""))
            if "Net" in line:
                m = re.search(r'-?\$?([\d,]+\.?\d*)', line.replace(",", ""))
                if m:
                    val = float(m.group(1).replace(",", ""))
                    if "-" in line or "(" in line:
                        val = -val
                    data["net_income_mtd"] = val

        # Outstanding invoices
        raw_inv = qb_run("invoices")
        inv_total = 0.0
        inv_count = 0
        for line in raw_inv.split("\n"):
            m = re.search(r'\$?([\d,]+\.?\d*)', line)
            if m and ("Balance" in line or "Due" in line or "Total" in line):
                try:
                    inv_total += float(m.group(1).replace(",", ""))
                    inv_count += 1
                except:
                    pass
        data["invoices_outstanding"] = inv_total
        data["invoices_count"] = inv_count

        # Last 3 months trend
        raw3 = qb_run("pl 3")
        data["pl_3mo_raw"] = raw3[:500] if raw3 else ""

    except Exception as e:
        data["qb_error"] = str(e)
    return data


# ── Airtable ──────────────────────────────────────────────────────────────────

def airtable_request(base_id, table, params=None):
    token = secret("airtable-api-key.txt")
    if not token:
        return None
    url = f"https://api.airtable.com/v0/{base_id}/{urllib.parse.quote(table)}"
    if params:
        url += "?" + urllib.parse.urlencode(params)
    req = urllib.request.Request(url, headers={"Authorization": f"Bearer {token}"})
    try:
        with urllib.request.urlopen(req, timeout=15) as r:
            return json.loads(r.read())
    except Exception as e:
        return {"error": str(e)}

def get_airtable_data():
    data = {}
    BASE_ID = "applisWTLgZJOnYtj"
    try:
        # Active recurring customers
        result = airtable_request(BASE_ID, "Customers", {
            "filterByFormula": "AND({Status}='Active', {Frequency}!='One Time')",
            "fields[]": ["Full name", "Frequency", "Total Spent"],
            "pageSize": 100
        })
        if result and "records" in result:
            records = result["records"]
            # Handle pagination
            while result.get("offset"):
                result = airtable_request(BASE_ID, "Customers", {
                    "filterByFormula": "AND({Status}='Active', {Frequency}!='One Time')",
                    "fields[]": ["Full name", "Frequency", "Total Spent"],
                    "pageSize": 100,
                    "offset": result["offset"]
                })
                if result and "records" in result:
                    records.extend(result["records"])
                else:
                    break
            data["active_recurring"] = len(records)

            # Frequency breakdown
            freq = {}
            for r in records:
                f = r.get("fields", {}).get("Frequency", "Unknown")
                freq[f] = freq.get(f, 0) + 1
            data["frequency_breakdown"] = freq

        # Recent bookings (last 7 days)
        week_ago = (datetime.date.today() - datetime.timedelta(days=7)).isoformat()
        result2 = airtable_request(BASE_ID, "Bookings", {
            "filterByFormula": f"{{Date}} >= '{week_ago}'",
            "fields[]": ["Date", "Status", "Total"],
            "pageSize": 100
        })
        if result2 and "records" in result2:
            bookings = result2["records"]
            data["bookings_7d"] = len(bookings)
            total_7d = sum(
                float(str(r.get("fields", {}).get("Total", 0)).replace("$","").replace(",","") or 0)
                for r in bookings
            )
            data["revenue_7d"] = round(total_7d, 2)

            # Today's bookings
            today = datetime.date.today().isoformat()
            data["bookings_today"] = sum(
                1 for r in bookings
                if r.get("fields", {}).get("Date", "") == today
            )

    except Exception as e:
        data["airtable_error"] = str(e)
    return data


# ── Slack ─────────────────────────────────────────────────────────────────────

def slack_request(endpoint, params=None):
    token = secret("slack-token.txt")
    if not token:
        return None
    url = f"https://slack.com/api/{endpoint}"
    if params:
        url += "?" + urllib.parse.urlencode(params)
    req = urllib.request.Request(url, headers={"Authorization": f"Bearer {token}"})
    try:
        with urllib.request.urlopen(req, timeout=10) as r:
            return json.loads(r.read())
    except:
        return None

def get_slack_data():
    data = {}
    try:
        # Recent #calls messages (last 24h)
        oldest = str(time.time() - 86400)
        result = slack_request("conversations.history", {
            "channel": "CDP3Y6YB0",
            "oldest": oldest,
            "limit": 50
        })
        if result and result.get("ok"):
            msgs = result.get("messages", [])
            data["calls_24h"] = len(msgs)

            # Count keywords
            complaints = sum(1 for m in msgs if any(w in m.get("text","").lower()
                for w in ["complaint", "damage", "unhappy", "issue", "problem", "cancel", "refund"]))
            otw_sent = sum(1 for m in msgs if "OTW Alert" in m.get("text",""))
            data["complaints_24h"] = complaints
            data["otw_sent_24h"] = otw_sent

            # Last 3 call summaries (strip to first 120 chars)
            summaries = []
            for m in msgs[:10]:
                text = m.get("text", "")
                if len(text) > 40 and not text.startswith("Harvey"):
                    summaries.append(text[:120].replace("\n", " ").strip())
                if len(summaries) >= 3:
                    break
            data["recent_calls"] = summaries

        # #emilys-team last 24h
        result2 = slack_request("conversations.history", {
            "channel": "C02NFPKNZ1U",
            "oldest": oldest,
            "limit": 20
        })
        if result2 and result2.get("ok"):
            data["ops_messages_24h"] = len(result2.get("messages", []))

    except Exception as e:
        data["slack_error"] = str(e)
    return data


# ── GHL ───────────────────────────────────────────────────────────────────────

def ghl_request(path, params=None):
    token = secret("ghl-v2-token.txt")
    if not token:
        return None
    url = f"https://services.leadconnectorhq.com{path}"
    if params:
        url += "?" + urllib.parse.urlencode(params)
    req = urllib.request.Request(url, headers={
        "Authorization": f"Bearer {token}",
        "Version": "2021-07-28",
        "User-Agent": "Harvey/1.0"
    })
    try:
        with urllib.request.urlopen(req, timeout=15) as r:
            return json.loads(r.read())
    except:
        return None

def get_ghl_data():
    data = {}
    try:
        loc_id = "BvrCS522k5EuY6TbZQsw"

        # Open opportunities in AI Agent Sales Pipeline
        pipeline_id = "43wROY9icVX1mP86G8eZ"
        result = ghl_request("/opportunities/search", {
            "location_id": loc_id,
            "pipeline_id": pipeline_id,
            "status": "open",
            "limit": 100
        })
        if result:
            opps = result.get("opportunities", [])
            data["open_leads"] = len(opps)
            # Count by stage
            stages = {}
            total_value = 0.0
            for o in opps:
                stage = o.get("pipelineStage", {}).get("name", "Unknown")
                stages[stage] = stages.get(stage, 0) + 1
                total_value += float(o.get("monetaryValue", 0) or 0)
            data["lead_stages"] = stages
            data["pipeline_value"] = round(total_value, 2)

        # Contacts created last 7 days (new leads)
        week_ago_ts = int((time.time() - 7*86400) * 1000)
        result2 = ghl_request("/contacts/", {
            "locationId": loc_id,
            "startAfter": str(week_ago_ts),
            "limit": 100
        })
        if result2:
            data["new_contacts_7d"] = result2.get("total", 0) or len(result2.get("contacts", []))

    except Exception as e:
        data["ghl_error"] = str(e)
    return data


# ── Main ──────────────────────────────────────────────────────────────────────

def build_dashboard_data():
    now = datetime.datetime.now(datetime.timezone.utc)
    eastern = datetime.timezone(datetime.timedelta(hours=-4))  # EDT
    now_et = now.astimezone(eastern)

    print(f"[{now_et.strftime('%H:%M')}] Fetching QuickBooks...", flush=True)
    qb = get_qb_data()

    print(f"[{now_et.strftime('%H:%M')}] Fetching Airtable...", flush=True)
    at = get_airtable_data()

    print(f"[{now_et.strftime('%H:%M')}] Fetching Slack...", flush=True)
    sl = get_slack_data()

    print(f"[{now_et.strftime('%H:%M')}] Fetching GHL...", flush=True)
    gh = get_ghl_data()

    # Known benchmarks from MEMORY.md
    benchmarks = {
        "target_monthly_revenue": 83333,   # $1M / 12
        "active_recurring_target": 200,    # growth target
        "churn_rate_monthly": 5.5,         # %
        "avg_recurring_visit": 250,
        "avg_onetime_visit": 402,
        "subcontractor_cost_pct": 56,
    }

    payload = {
        "generated_at": now_et.strftime("%Y-%m-%dT%H:%M:%S%z"),
        "generated_at_display": now_et.strftime("%b %d, %Y at %I:%M %p ET"),
        "qb": qb,
        "airtable": at,
        "slack": sl,
        "ghl": gh,
        "benchmarks": benchmarks,
    }

    os.makedirs(os.path.join(BASE, "www"), exist_ok=True)
    with open(OUT, "w") as f:
        json.dump(payload, f, indent=2)

    print(f"✅ Dashboard data written → www/dashboard-data.json", flush=True)
    return payload


if __name__ == "__main__":
    watch = "--watch" in sys.argv
    print_mode = "--print" in sys.argv

    if watch:
        print("Watching (refresh every 5 min). Ctrl+C to stop.", flush=True)
        while True:
            d = build_dashboard_data()
            if print_mode:
                print(json.dumps(d, indent=2))
            time.sleep(300)
    else:
        d = build_dashboard_data()
        if print_mode:
            print(json.dumps(d, indent=2))