#!/usr/bin/env python3
"""
Crashlytics events -> multi-sheet Excel workbook.
From Shubham Singh Bisht's Android helpers: https://shubhamsinghbisht.com/android-roadmap/#helpers

Usage:
  1. Drop a fresh `auth.json` next to this file (or pass --auth) with shape:
         {
           "project_number": "<PROJECT_NUMBER>",
           "app_id":         "android:<your.application.id>",
           "key":            "<API_KEY_FROM_CURL>",
           "authorization":  "SAPISIDHASH <...>",
           "cookie":         "<cookie string from the cURL -b '...'>"
         }
     All five values come from the two cURLs the user copied from Firebase
     Console DevTools (Network tab, Copy as cURL on listSessionEventIds and
     processedevents).
  2. Edit ISSUES, INTERVAL, VERSION_FILTERS (and optionally GROUP_KEY,
     SIZE_KEY, ERROR_KEYS) below.
  3. python3 crashlytics_to_excel.py [--auth auth.json] [--out report.xlsx]
  4. Delete auth.json + events_*.json afterwards: session credentials.
"""

import argparse
import json
import sys
import time
import urllib.request
import urllib.error
from collections import Counter
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

ROOT = Path(__file__).resolve().parent

# --- Per-run config ---------------------------------------------------------

# One entry per Crashlytics issue. `tag` becomes the sheet name (<=31 chars,
# no `/` or `\`). `custom_keys` lists the keys you expect in that issue's
# subtitle; they become columns in the issue sheet.
ISSUES = [
    {
        "tag": "1_UploadFailed",
        "id": "<ISSUE_ID>",
        "label": "UPLOAD_FAILED",
        "custom_keys": [
            "file_name", "file_size_bytes", "failure_reason", "http_code",
        ],
    },
    # add more issues here…
]

INTERVAL = {
    "startTime": "2026-01-01T00:00:00.000Z",
    "endTime":   "2026-01-07T23:59:59.000Z",
}

# Match the version filter the user sees in the Firebase Console UI. Set to
# [] to query across all versions.
VERSION_FILTERS = [
    {"buildVersions": ["<BUILD_NUMBER>"], "displayVersion": "<VERSION_NAME>"},
]

# Optional custom key to pivot on in the Insights sheet (e.g. a session or
# account id you log as a custom key). None to skip that pivot.
GROUP_KEY = None

# Optional custom key holding a byte count; summed in the Insights sheet and
# formatted as a number. None to skip.
SIZE_KEY = "file_size_bytes"

# Custom keys checked, in order, for the "Specific Error" column.
ERROR_KEYS = ["error", "failure_reason", "primary_error_code"]

PAGE_SIZE = 100
REQ_PAUSE_S = 0.1

# Filled in by main() from auth.json.
BASE = ""
KEY = ""
HEADERS = {}

# --- HTTP -------------------------------------------------------------------


def load_auth(path):
    global BASE, KEY, HEADERS
    auth = json.loads(Path(path).read_text())
    BASE = (
        f"https://crashlytics-pa.clients6.google.com/v1/projects/"
        f"{auth['project_number']}/clients/{auth['app_id']}"
    )
    KEY = auth["key"]
    HEADERS = {
        "accept": "application/json, text/plain, */*",
        "accept-language": "en-GB,en;q=0.5",
        "authorization": auth["authorization"],
        "cookie": auth["cookie"],
        "origin": "https://console.firebase.google.com",
        "referer": "https://console.firebase.google.com/",
        "user-agent": auth.get("user_agent") or (
            "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) "
            "AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36"
        ),
        # Copy x-client-version from the cURL if the server rejects this one.
        "x-client-version": auth.get("client_version", "firebase-console/20260507.09_p0"),
        "x-goog-authuser": "0",
    }


def http(url, method="GET", body=None):
    headers = dict(HEADERS)
    data = None
    if body is not None:
        headers["content-type"] = "application/json"
        data = json.dumps(body).encode()
    req = urllib.request.Request(url, data=data, method=method, headers=headers)
    try:
        with urllib.request.urlopen(req, timeout=30) as r:
            return r.status, json.loads(r.read())
    except urllib.error.HTTPError as e:
        return e.code, e.read().decode(errors="replace")


# --- API calls --------------------------------------------------------------

def list_event_ids(issue_id):
    """Paginate listSessionEventIds. The API uses a positional pageToken
    {sessionId, eventId} of the LAST entry from the previous page, not a
    server-returned nextPageToken. Loop exits when no new entries arrive."""
    url = f"{BASE}/issues/{issue_id}/metrics:listSessionEventIds?alt=json&key={KEY}"
    body = {
        "direction": "BOTH",
        "filters": {
            "categories": [], "customKeys": [], "excludedSubIssues": [],
            "includedSubIssues": [], "manufacturerModels": [], "osVersions": [],
            "rollouts": [], "versionFilters": VERSION_FILTERS,
        },
        "interval": INTERVAL,
        "maxNumResults": PAGE_SIZE,
    }
    all_ids, seen = [], set()
    page = 0
    while True:
        page += 1
        status, resp = http(url, method="POST", body=body)
        if status != 200:
            print(f"  listSessionEventIds page {page} -> {status}: {str(resp)[:300]}", file=sys.stderr)
            return all_ids
        ids = resp.get("sessionEventKeys", [])
        new = 0
        for entry in ids:
            k = (entry.get("sessionId"), entry.get("eventId"))
            if k not in seen:
                seen.add(k)
                all_ids.append(entry)
                new += 1
        print(f"  page {page}: {len(ids)} returned, {new} new (total {len(all_ids)})", file=sys.stderr)
        if new == 0 or len(ids) < PAGE_SIZE:
            break
        body["pageToken"] = {
            "sessionId": ids[-1]["sessionId"],
            "eventId":   ids[-1]["eventId"],
        }
        time.sleep(REQ_PAUSE_S)
    return all_ids


def fetch_event(session_id, event_id):
    url = f"{BASE}/processedevents/{session_id}_{event_id}?alt=json&key={KEY}"
    status, resp = http(url)
    if status != 200:
        print(f"    event {session_id}_{event_id} -> {status}", file=sys.stderr)
        return None
    return resp


# --- Event parsing ----------------------------------------------------------

def parse_subtitle_kv(sub):
    """Crashlytics packs custom keys into the stacktrace caption subtitle as
    `Some Title: k1=v1, k2=v2`. Strip the prefix then split."""
    out = {}
    if not sub:
        return out
    body = sub.split(":", 1)[1].strip() if ":" in sub else sub
    for part in body.split(","):
        part = part.strip()
        if "=" in part:
            k, v = part.split("=", 1)
            out[k.strip()] = v.strip()
    return out


def extract(ev):
    ext = ev.get("eventDataExternal", {})
    device = ext.get("device", {})
    os_info = ext.get("operatingSystem", {})
    app = ext.get("application", {})
    user = ext.get("user", {})
    sg = ev.get("stacktraceGroup", {}).get("exceptions", [])
    cap = sg[0].get("stacktrace", {}).get("caption", {}) if sg else {}
    frames = sg[0].get("stacktrace", {}).get("frames", []) if sg else []
    sub = cap.get("subtitle", "")
    top = frames[0] if frames else {}
    return {
        "Event Time":     ext.get("eventTime", ""),
        "App Version":    f"{app.get('displayVersion','')} ({app.get('buildVersion','')})",
        "OS Version":     f"Android {os_info.get('displayVersion','')}",
        "Manufacturer":   device.get("manufacturer", ""),
        "Model":          device.get("model", ""),
        "Marketing Name": device.get("marketingName", ""),
        "Company":        device.get("companyName", ""),
        "User ID":        user.get("id", ""),
        "Top Frame":      f"{top.get('symbol','')} ({top.get('file','')}:{top.get('line','')})",
        "Exception Title": cap.get("title", ""),
        "Session ID":     ev.get("sessionId", "").split("_DNE_")[0],
        "Event ID":       ev.get("eventId", ""),
        "Full Subtitle":  sub,
        "_parsed":        parse_subtitle_kv(sub),
    }


def to_int(s):
    try:
        return int(s) if s not in (None, "", "(none)") else None
    except (TypeError, ValueError):
        return None


def fmt_bytes(b):
    if not b:
        return ""
    v = float(b)
    for u in ("B", "KB", "MB", "GB", "TB"):
        if v < 1024:
            return f"{v:.2f} {u}"
        v /= 1024
    return f"{v:.2f} PB"


# --- Workbook styling -------------------------------------------------------

HEADER_FONT = Font(bold=True, color="FFFFFF")
HEADER_FILL = PatternFill("solid", fgColor="1F4E78")
ALT_FILL    = PatternFill("solid", fgColor="F2F6FC")
THIN        = Side(border_style="thin", color="CCCCCC")
BORDER      = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)

COMMON_COLS = [
    "Event Time", "App Version", "OS Version", "Manufacturer", "Model",
    "Marketing Name", "Company", "User ID",
]
TAIL_COLS = ["Top Frame", "Session ID", "Event ID", "Full Subtitle"]

DEFAULT_WIDTHS = {
    "Issue Type": 26, "Event Time": 22, "App Version": 14, "OS Version": 12,
    "Manufacturer": 14, "Model": 22, "Marketing Name": 22, "Company": 18,
    "User ID": 38, "Top Frame": 60, "Session ID": 34, "Event ID": 22,
    "Full Subtitle": 80, "Exception Title": 40, "Specific Error": 36,
    "file_name": 30, "file_size_bytes": 14, "failure_reason": 20,
    "http_code": 10, "retry_count": 10, "max_retries": 10,
    "primary_error_code": 18, "all_error_codes": 22, "severity": 12,
}


def all_custom_keys():
    """Union of every issue's custom keys, in first-seen order."""
    keys = []
    for issue in ISSUES:
        for k in issue["custom_keys"]:
            if k not in keys:
                keys.append(k)
    return keys


def write_table(ws, headers, rows):
    for ci, h in enumerate(headers, 1):
        c = ws.cell(row=1, column=ci, value=h)
        c.font, c.fill, c.border = HEADER_FONT, HEADER_FILL, BORDER
        c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    for ri, row in enumerate(rows, 2):
        fill = ALT_FILL if ri % 2 == 0 else None
        for ci, h in enumerate(headers, 1):
            v = row.get(h, "")
            c = ws.cell(row=ri, column=ci, value=v)
            c.border = BORDER
            c.alignment = Alignment(vertical="top")
            if fill:
                c.fill = fill
            if SIZE_KEY and h == SIZE_KEY:
                n = to_int(v)
                if n is not None:
                    c.value = n
                    c.number_format = "#,##0"
        ws.row_dimensions[ri].height = 16
    for ci, h in enumerate(headers, 1):
        ws.column_dimensions[get_column_letter(ci)].width = DEFAULT_WIDTHS.get(h, 18)
    ws.freeze_panes = "A2"
    if rows:
        ws.auto_filter.ref = ws.dimensions


def write_kv_section(ws, start_row, title, pairs):
    c = ws.cell(row=start_row, column=1, value=title)
    c.font = Font(bold=True, size=13, color="1F4E78")
    for col, label in ((1, "Value"), (2, "Count")):
        h = ws.cell(row=start_row + 1, column=col, value=label)
        h.font, h.fill, h.border = HEADER_FONT, HEADER_FILL, BORDER
    for i, (k, n) in enumerate(pairs, start=start_row + 2):
        ws.cell(row=i, column=1, value=str(k)).border = BORDER
        ws.cell(row=i, column=2, value=n).border = BORDER
    return start_row + 2 + len(pairs) + 1


# --- Build sheets -----------------------------------------------------------

def build_issue_rows(issue, events):
    rows = []
    for ev in events:
        e = extract(ev)
        row = {k: e[k] for k in COMMON_COLS}
        for k in issue["custom_keys"]:
            row[k] = e["_parsed"].get(k, "")
        row["Top Frame"]    = e["Top Frame"]
        row["Session ID"]   = e["Session ID"]
        row["Event ID"]     = e["Event ID"]
        row["Full Subtitle"] = e["Full Subtitle"]
        rows.append(row)
    return rows


def build_combined(per_issue):
    combined = []
    custom = all_custom_keys()
    for issue, rows in per_issue:
        for r in rows:
            specific = next((r.get(k) for k in ERROR_KEYS if r.get(k)), "")
            cr = {"Issue Type": issue["label"]}
            for k in COMMON_COLS:
                cr[k] = r.get(k, "")
            for k in custom:
                cr[k] = r.get(k, "")
            cr["Specific Error"] = specific
            cr["Session ID"] = r.get("Session ID", "")
            cr["Event ID"] = r.get("Event ID", "")
            combined.append(cr)
    combined.sort(key=lambda x: x["Event Time"])
    return combined


def multi_issue(combined, key):
    """Values of `key` seen under more than one issue type, most first."""
    seen = {}
    for r in combined:
        if r.get(key):
            seen.setdefault(r[key], set()).add(r["Issue Type"])
    return sorted(
        ((v, len(s)) for v, s in seen.items() if len(s) > 1),
        key=lambda x: -x[1],
    )


def insights(combined):
    by = lambda key, default="(unknown)": Counter(
        (r.get(key) or default) for r in combined
    )
    total_size = (
        sum(to_int(r.get(SIZE_KEY)) or 0 for r in combined) if SIZE_KEY else 0
    )
    return {
        "by_issue":   by("Issue Type").most_common(),
        "by_company": by("Company").most_common(),
        "by_model":   by("Marketing Name").most_common(20),
        "by_os":      by("OS Version").most_common(),
        "by_user":    by("User ID").most_common(20),
        "by_group":   (
            Counter(r[GROUP_KEY] for r in combined if r.get(GROUP_KEY)).most_common(20)
            if GROUP_KEY else []
        ),
        "by_err":     Counter(r["Specific Error"] or "(none)" for r in combined).most_common(20),
        "multi_users":  multi_issue(combined, "User ID"),
        "multi_groups": multi_issue(combined, GROUP_KEY) if GROUP_KEY else [],
        "total_bytes":  total_size,
        "total_pretty": fmt_bytes(total_size),
    }


# --- Main -------------------------------------------------------------------

def main():
    ap = argparse.ArgumentParser(description="Export Crashlytics events to an Excel workbook.")
    ap.add_argument("--auth", default=str(ROOT / "auth.json"),
                    help="auth.json with project_number, app_id, key, authorization, cookie")
    ap.add_argument("--out", default=str(ROOT / "Crashlytics_Report.xlsx"),
                    help="output .xlsx path")
    args = ap.parse_args()
    load_auth(args.auth)
    out_dir = Path(args.out).resolve().parent

    per_issue = []
    for issue in ISSUES:
        print(f"\n=== {issue['tag']}: {issue['id']} ===", file=sys.stderr)
        keys = list_event_ids(issue["id"])
        print(f"  {len(keys)} event keys", file=sys.stderr)
        events = []
        for i, k in enumerate(keys, 1):
            ev = fetch_event(k["sessionId"], k["eventId"])
            if ev:
                events.append(ev)
            if i % 5 == 0 or i == len(keys):
                print(f"  fetched {i}/{len(keys)}", file=sys.stderr)
            time.sleep(REQ_PAUSE_S)
        (out_dir / f"events_{issue['tag']}.json").write_text(json.dumps(events, indent=2))
        per_issue.append((issue, build_issue_rows(issue, events)))

    wb = Workbook()
    wb.remove(wb.active)

    for issue, rows in per_issue:
        if not rows:
            continue
        ws = wb.create_sheet(issue["tag"][:31])
        headers = COMMON_COLS + issue["custom_keys"] + TAIL_COLS
        write_table(ws, headers, rows)

    combined = build_combined(per_issue)
    if combined:
        ws_c = wb.create_sheet("All_Events")
        c_headers = (
            ["Issue Type"] + COMMON_COLS + all_custom_keys()
            + ["Specific Error", "Session ID", "Event ID"]
        )
        write_table(ws_c, c_headers, combined)

        ins = insights(combined)
        ws_i = wb.create_sheet("Insights")
        ws_i.cell(row=1, column=1, value=f"Total events: {len(combined)}").font = Font(bold=True, size=14)
        if SIZE_KEY:
            ws_i.cell(row=2, column=1, value=f"Total {SIZE_KEY}: {ins['total_pretty']}  ({ins['total_bytes']:,} bytes)").font = Font(italic=True)
        row = 4
        row = write_kv_section(ws_i, row, "Events by Issue", ins["by_issue"])
        row = write_kv_section(ws_i, row, "Events by Company (OEM)", ins["by_company"])
        row = write_kv_section(ws_i, row, "Events by Marketing Name", ins["by_model"])
        row = write_kv_section(ws_i, row, "Events by OS Version", ins["by_os"])
        row = write_kv_section(ws_i, row, "Events by Specific Error", ins["by_err"])
        row = write_kv_section(ws_i, row, "Top Users (by event count)", ins["by_user"])
        row = write_kv_section(ws_i, row, "Users hit by MULTIPLE issue types", ins["multi_users"] or [("(none)", 0)])
        if GROUP_KEY:
            row = write_kv_section(ws_i, row, f"Top {GROUP_KEY} (by event count)", ins["by_group"])
            row = write_kv_section(ws_i, row, f"{GROUP_KEY} values hit by MULTIPLE issue types", ins["multi_groups"] or [("(none)", 0)])
        ws_i.column_dimensions["A"].width = 50
        ws_i.column_dimensions["B"].width = 14

    wb.save(args.out)
    print(f"\nSaved: {args.out}")
    for issue, rows in per_issue:
        print(f"  {issue['tag']}: {len(rows)} events")
    print(f"  All_Events: {len(combined)} events" if combined else "  (no combined sheet: no events)")


if __name__ == "__main__":
    main()