#!/usr/bin/env python3
"""
read-cc-config.py — read the LIVE, server-pushed Crowd Connected config out of each
installed app's private Room database (root) and decode the operative kill-switch
(on/off) and geofence km-range.

For every installed package matching --filter it:
  * checks (root) for <data>/databases/CrowdConnected-RoomDatabase;
  * copies the DB (+ -wal/-shm) to /sdcard, adb-pulls it, deletes the /sdcard copy;
  * introspects the schema and decodes, tolerant of both layouts:
      - new SDK : `Settings.active` column (1=ON), `GeoZone.radius1/radius2`;
      - legacy  : an `activeAPIResponse` heartbeat string like `120,false,...`;
  * REDACTS device/account identifiers (deviceId -> sha256 prefix; alias values dropped).

This NEVER contacts Crowd Connected — it only reads the cached result the SDK already
fetched. Server config drifts over time, so re-running may differ; each run stamps a
capture header. Usage:

  python read-cc-config.py --serial <id> --out data/cc_live_config_<date> \
      --workdir <tmp>            # tmp dir for pulled DBs (keep private)
"""
from __future__ import annotations
import argparse, csv, hashlib, io, json, os, re, sqlite3, subprocess, sys, datetime

HEARTBEAT = re.compile(rb"^\s*\d+,(true|false)", re.I)


def adb(serial, *args, **kw):
    return subprocess.run(["adb", "-s", serial, *args], capture_output=True, text=True, **kw)


def adb_su(serial, inner):
    # android paths have no spaces; single-quote the inner command for su
    return adb(serial, "shell", f"su -c '{inner}'")


def list_pkgs(serial, filt):
    out = adb(serial, "shell", "pm", "list", "packages", "-3").stdout
    pkgs = [l.replace("package:", "").strip() for l in out.splitlines() if l.strip()]
    rx = re.compile(filt)
    return sorted(p for p in pkgs if rx.search(p))


def redact_id(v):
    if v is None:
        return None
    s = str(v)
    return "sha256:" + hashlib.sha256(s.encode()).hexdigest()[:12] + " (redacted)"


def pull_db(serial, pkg, workdir):
    """Copy CC Room DB (+wal/shm) off-device with root; return local DB path or None."""
    dbdir = f"/data/data/{pkg}/databases"
    ls = adb_su(serial, f"ls {dbdir}").stdout
    files = [f.strip() for f in ls.splitlines() if "CrowdConnected" in f]
    if not files:
        return None
    sd = f"/sdcard/_cc/{pkg}"
    adb_su(serial, f"rm -rf {sd}; mkdir -p {sd}")
    for f in files:
        adb_su(serial, f"cp {dbdir}/{f} {sd}/{f}")
    # Also grab the SharedPreferences that hold the legacy `activeAPIResponse`
    # kill-switch heartbeat (older SDK schema has no Settings.active column).
    adb_su(serial, f"cp /data/data/{pkg}/shared_prefs/CrowdConnected.xml {sd}/CrowdConnected.xml")
    adb_su(serial, f"chmod -R 666 {sd}/*")
    local = os.path.join(workdir, pkg)
    os.makedirs(local, exist_ok=True)
    adb(serial, "pull", sd, workdir)            # pulls the <pkg> folder
    adb_su(serial, f"rm -rf {sd}")
    main = os.path.join(local, "CrowdConnected-RoomDatabase")
    return main if os.path.exists(main) else None


def col_map(cur, table):
    try:
        cur.execute(f"PRAGMA table_info('{table}')")
        return [r[1] for r in cur.fetchall()]
    except sqlite3.Error:
        return []


def decode_db(path):
    """Return a dict of decoded, redacted config from one CC Room DB."""
    res = {"tables": [], "settings": {}, "zones": [], "sdkstatus": {},
           "schema": "none", "kill_switch": "unknown", "heartbeat": None}
    con = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    cur = con.cursor()
    cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = [r[0] for r in cur.fetchall()]
    res["tables"] = tables

    # --- Settings (new schema) ---
    if "Settings" in tables:
        cols = col_map(cur, "Settings")
        cur.execute("SELECT * FROM Settings LIMIT 1")
        row = cur.fetchone()
        if row:
            d = dict(zip(cols, row))
            keep = ["appKey", "active", "geoEnabled", "sendPositionData", "pollingFrequency",
                    "geoHighFrequency", "geoMediumFrequency", "debugEnabled", "sendRawData"]
            res["settings"] = {k: d.get(k) for k in keep if k in d}
            if "active" in d and d["active"] is not None:
                res["schema"] = "settings-column"
                res["kill_switch"] = "ON" if str(d["active"]) in ("1", "True", "true") else "OFF"

    # --- GeoZone ---
    if "GeoZone" in tables:
        cols = col_map(cur, "GeoZone")
        cur.execute("SELECT * FROM GeoZone")
        for row in cur.fetchall():
            d = dict(zip(cols, row))
            r2 = d.get("radius2")
            res["zones"].append({
                "appKey": d.get("appKey"),
                "lat": d.get("lat"), "lng": d.get("lng"),
                "radius1_m": d.get("radius1"), "radius2_m": r2,
                "radius2_km": (round(r2 / 1000.0, 3) if isinstance(r2, (int, float)) else None),
            })

    # --- SDKStatus (redact identity) ---
    for t in tables:
        if t.lower() == "sdkstatus":
            cols = col_map(cur, t)
            cur.execute(f"SELECT * FROM '{t}' LIMIT 1")
            row = cur.fetchone()
            if row:
                d = dict(zip(cols, row))
                res["sdkstatus"] = {
                    "sdkVersion": d.get("sdkVersion") or d.get("version"),
                    "mode": d.get("Mode") or d.get("mode"),
                    "modules": d.get("modules"),
                    "deviceId": redact_id(d.get("deviceId") or d.get("DeviceId")),
                }

    # --- Legacy heartbeat: scan all text cells for `<n>,<bool>,...` ---
    if res["schema"] == "none":
        for t in tables:
            cols = col_map(cur, t)
            try:
                cur.execute(f"SELECT * FROM '{t}'")
                rows = cur.fetchall()
            except sqlite3.Error:
                continue
            for row in rows:
                for v in row:
                    if isinstance(v, (str, bytes)):
                        b = v.encode() if isinstance(v, str) else v
                        if HEARTBEAT.match(b):
                            res["schema"] = "legacy-heartbeat"
                            res["heartbeat"] = v if isinstance(v, str) else v.decode("utf-8", "ignore")
                            res["kill_switch"] = "ON" if b.split(b",")[1].lower().startswith(b"true") else "OFF"
                            break
                if res["heartbeat"]:
                    break
            if res["heartbeat"]:
                break

    con.close()

    # --- Legacy/foreground SDK: the kill-switch lives in shared_prefs, not the DB.
    # CrowdConnected.xml holds `activeAPIResponse` = "<pollSec>,<active>,<...>,<ts>".
    # Parse ONLY the non-personal config fields (heartbeat + appKey); never the
    # authToken / deviceId / aliases that also sit in that file.
    pref = os.path.join(os.path.dirname(path), "CrowdConnected.xml")
    if os.path.exists(pref):
        try:
            txt = io.open(pref, encoding="utf-8", errors="ignore").read()
        except Exception:
            txt = ""
        m = re.search(r'name="activeAPIResponse">([^<]+)<', txt)
        if m:
            hb = m.group(1).strip()
            res["heartbeat"] = hb
            parts = hb.split(",")
            if len(parts) >= 2:
                res["settings"].setdefault("pollingFrequency", parts[0])
                if res["kill_switch"] == "unknown":
                    res["schema"] = "legacy-heartbeat-pref"
                    res["kill_switch"] = "ON" if parts[1].lower().startswith("true") else "OFF"
        mk = re.search(r'name="appKey">([^<]+)<', txt)
        if mk and not res["settings"].get("appKey"):
            res["settings"]["appKey"] = mk.group(1).strip()

    return res


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--serial", default="")
    ap.add_argument("--filter", default=r"appmiral|appstrakt|mysticfestival|triple\.verstappen")
    ap.add_argument("--workdir", required=True, help="local dir for pulled DBs (private)")
    ap.add_argument("--out", required=True, help="output path prefix (no extension)")
    args = ap.parse_args()

    serial = args.serial
    if not serial:
        out = subprocess.run(["adb", "devices"], capture_output=True, text=True).stdout
        for l in out.splitlines()[1:]:
            if l.strip().endswith("device"):
                serial = l.split()[0]
                break
    if not serial:
        sys.exit("No adb device found")

    os.makedirs(args.workdir, exist_ok=True)
    ts = datetime.datetime.now().astimezone().strftime("%Y-%m-%dT%H:%M:%S%z")
    pkgs = list_pkgs(serial, args.filter)
    print(f"Checking {len(pkgs)} packages for Crowd Connected Room DB (serial {serial})")

    rows = []
    for i, pkg in enumerate(pkgs, 1):
        try:
            db = pull_db(serial, pkg, args.workdir)
        except Exception as e:
            print(f"  [{i}/{len(pkgs)}] {pkg}: pull error {e}")
            continue
        if not db:
            continue
        dec = decode_db(db)
        dec["package"] = pkg
        dec["captured_ts"] = ts
        rows.append(dec)
        widest = max((z["radius2_km"] for z in dec["zones"] if z["radius2_km"]), default=None)
        print(f"  [{i}/{len(pkgs)}] {pkg:<42} schema={dec['schema']:<16} "
              f"kill_switch={dec['kill_switch']:<8} widest_radius_km={widest}")

    # CSV (headline per app)
    os.makedirs(os.path.dirname(os.path.abspath(args.out)) or ".", exist_ok=True)
    with io.open(args.out + ".csv", "w", newline="", encoding="utf-8") as f:
        w = csv.writer(f)
        w.writerow(["package", "appKey", "schema", "kill_switch", "active_raw", "geoEnabled",
                    "sendPositionData", "pollingFrequency", "geoHighFrequency", "geoMediumFrequency",
                    "zone_lat", "zone_lng", "radius1_m", "radius2_m", "radius2_km",
                    "sdkVersion", "mode", "n_zones", "captured_ts"])
        for r in sorted(rows, key=lambda x: x["package"]):
            s = r["settings"]
            z = max(r["zones"], key=lambda x: (x["radius2_m"] or 0), default=None) if r["zones"] else None
            w.writerow([
                r["package"], s.get("appKey", ""), r["schema"], r["kill_switch"], s.get("active", ""),
                s.get("geoEnabled", ""), s.get("sendPositionData", ""), s.get("pollingFrequency", ""),
                s.get("geoHighFrequency", ""), s.get("geoMediumFrequency", ""),
                (z or {}).get("lat", ""), (z or {}).get("lng", ""), (z or {}).get("radius1_m", ""),
                (z or {}).get("radius2_m", ""), (z or {}).get("radius2_km", ""),
                r["sdkstatus"].get("sdkVersion", ""), r["sdkstatus"].get("mode", ""),
                len(r["zones"]), r["captured_ts"],
            ])

    with io.open(args.out + ".json", "w", encoding="utf-8") as f:
        json.dump({"captured_ts": ts, "serial": serial, "apps": rows}, f, indent=2, ensure_ascii=False)

    print(f"\n{len(rows)} apps carry a Crowd Connected DB. Wrote {args.out}.csv / .json")


if __name__ == "__main__":
    main()