#!/usr/bin/env python3
"""Burrow box: one family's private server on Burrow Cloud, holding only sealed data (design: cloud/E2E.md).

Owner, Nov 16, 2026: "only storing encrypted location data in to box that only that box has a key for and i want the only
way for me to get that data is for the user to grant access". Locations, places, notes, sketches, photos and arrive/leave
events arrive already sealed by the family's phones with a key the server never has; this keeps them and hands them back.

It speaks the part of Traccar's web API the Burrow app uses (so the app's screens work the same as on a self-hosted
Traccar), plus /api/burrow/* for sealed reports, public keys, sealed family keys and settings. Standard library only:
http.server, sqlite3, a small WebSocket for /api/socket. Runs as its own user in a read-only container with no network
but the one Caddy reaches it on; its data is DATA/box.db.
"""
import base64, hashlib, hmac, json, os, queue, re, secrets, socket, sqlite3, struct, sys, threading, time, urllib.parse
from datetime import datetime, timezone
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer

DATA = os.environ.get("BOX_DATA", "/data")
PORT = int(os.environ.get("BOX_PORT", "8082"))
HOST = os.environ.get("BOX_HOST", "0.0.0.0")
DB = os.path.join(DATA, "box.db")
MAX_BODY = 4 << 20
lock = threading.RLock()
db = None
sockets = set()          # open live connections: queues of messages to send
tries = {}               # sign-in attempts per email: rate limit


# ---------- storage ----------

SCHEMA = """
CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL, name TEXT NOT NULL, pw TEXT NOT NULL,
  admin INTEGER NOT NULL DEFAULT 0, attrs TEXT NOT NULL DEFAULT '{}');
CREATE TABLE IF NOT EXISTS sessions (hash TEXT PRIMARY KEY, user_id INTEGER NOT NULL, kind TEXT NOT NULL, expires INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS groups_ (id INTEGER PRIMARY KEY, name TEXT NOT NULL, attrs TEXT NOT NULL DEFAULT '{}');
CREATE TABLE IF NOT EXISTS devices (id INTEGER PRIMARY KEY, unique_id TEXT UNIQUE NOT NULL, name TEXT NOT NULL, group_id INTEGER,
  user_id INTEGER, attrs TEXT NOT NULL DEFAULT '{}', last_ms INTEGER);
CREATE TABLE IF NOT EXISTS geofences (id INTEGER PRIMARY KEY, body TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS positions (id INTEGER PRIMARY KEY, device_id INTEGER NOT NULL, fix_ms INTEGER NOT NULL, server_ms INTEGER NOT NULL,
  ev INTEGER NOT NULL DEFAULT 0, sealed TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS pos_dev_fix ON positions(device_id, fix_ms);
CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, device_id INTEGER NOT NULL, position_id INTEGER NOT NULL, ms INTEGER NOT NULL);
CREATE INDEX IF NOT EXISTS ev_ms ON events(ms);
CREATE TABLE IF NOT EXISTS pubkeys (device_id INTEGER PRIMARY KEY, pub TEXT NOT NULL, mac TEXT NOT NULL DEFAULT '', ms INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS keyboxes (id INTEGER PRIMARY KEY, to_device INTEGER NOT NULL, from_device INTEGER, box TEXT NOT NULL, ms INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS settings (k TEXT PRIMARY KEY, v TEXT NOT NULL);
"""


def q(sql, args=(), one=False):
    with lock:
        cur = db.execute(sql, args)
        rows = cur.fetchall()
        db.commit()
        return (rows[0] if rows else None) if one else rows


def ins(sql, args=()):
    with lock:
        cur = db.execute(sql, args); db.commit(); return cur.lastrowid


def setting(k, default):
    r = q("SELECT v FROM settings WHERE k=?", (k,), one=True)
    return json.loads(r[0]) if r else default


def now_ms(): return int(time.time() * 1000)


def iso(ms): return datetime.fromtimestamp(ms / 1000, timezone.utc).strftime("%Y-%m-%dT%H:%M:%S.") + "%03d+00:00" % (ms % 1000)


def parse_iso(s):
    try: return int(datetime.fromisoformat(s.replace("Z", "+00:00")).timestamp() * 1000)
    except (ValueError, AttributeError): return None


def jl(s):
    try: v = json.loads(s); return v if isinstance(v, dict) else {}
    except (TypeError, ValueError): return {}


# ---------- people and sign-in ----------

def pw_hash(pw, salt=None):
    salt = salt or secrets.token_bytes(16)
    h = hashlib.scrypt(pw.encode(), salt=salt, n=1 << 14, r=8, p=1, dklen=32)
    return "scrypt$" + base64.b64encode(salt).decode() + "$" + base64.b64encode(h).decode()


def pw_ok(pw, stored):
    try:
        _, s, h = stored.split("$")
        return hmac.compare_digest(pw_hash(pw, base64.b64decode(s)).split("$")[2], h)
    except Exception:
        return False


def tok_hash(t): return hashlib.sha256(t.encode()).hexdigest()


def user_json(r):
    i, email, name, _, admin, attrs = r
    return {"id": i, "name": name, "email": email, "administrator": bool(admin), "readonly": False, "disabled": False,
            "deviceLimit": -1, "userLimit": 0, "attributes": jl(attrs)}


def user_row(uid): return q("SELECT * FROM users WHERE id=?", (uid,), one=True)


def new_session(uid, kind, ms):
    t = secrets.token_urlsafe(32)
    ins("INSERT INTO sessions VALUES (?,?,?,?)", (tok_hash(t), uid, kind, now_ms() + ms))
    return t


def end_sessions(uid, keep=None):
    """Signs a person out everywhere (a password change or removal): every session and saved sign-in token."""
    q("DELETE FROM sessions WHERE user_id=? AND hash IS NOT ?", (uid, tok_hash(keep) if keep else None))
    for s in list(sockets):
        if s.uid == uid and s.token != keep: s.close()


def throttled(email):
    t = time.time()
    with lock:
        a = [x for x in tries.get(email, []) if t - x < 600]
        tries[email] = a + [t]
        if len(tries) > 5000: tries.clear()
        return len(a) >= 10


# ---------- the family's objects ----------

def device_json(r):
    i, uniq, name, gid, uid, attrs, last = r
    a = jl(attrs)
    if uid: a["userId"] = uid
    p = q("SELECT id FROM positions WHERE device_id=? ORDER BY fix_ms DESC LIMIT 1", (i,), one=True)
    return {"id": i, "name": name, "uniqueId": uniq, "groupId": gid or 0, "disabled": False, "attributes": a,
            "lastUpdate": iso(last) if last else None, "positionId": p[0] if p else 0,
            "status": "online" if last and now_ms() - last < 10 * 60_000 else "offline"}


def position_json(r):
    i, dev, fix, srv, ev, sealed = r
    return {"id": i, "deviceId": dev, "fixTime": iso(fix), "deviceTime": iso(fix), "serverTime": iso(srv), "valid": True,
            "latitude": 0, "longitude": 0, "sealed": sealed, "attributes": {"ev": bool(ev)}}


# each phone's newest location by when it was taken (a queued report sent late can be older than one already here)
LATEST = ("SELECT (SELECT id FROM positions p2 WHERE p2.device_id=d.device_id ORDER BY fix_ms DESC, id DESC LIMIT 1) "
          "FROM (SELECT DISTINCT device_id FROM positions) d")


def broadcast(msg):
    for s in list(sockets): s.send(msg)


def purge():
    """Keeps history only as long as the family chose (default 30 days); each phone's latest location stays."""
    days = int(setting("retentionDays", 30))
    cut = now_ms() - days * 86400_000
    with lock:
        db.execute("DELETE FROM events WHERE ms < ?", (cut,))
        db.execute("DELETE FROM positions WHERE fix_ms < ? AND id NOT IN (%s)" % LATEST, (cut,))
        db.execute("DELETE FROM sessions WHERE expires < ?", (now_ms(),))
        db.execute("DELETE FROM keyboxes WHERE ms < ?", (now_ms() - 365 * 86400_000,))
        db.commit()


# ---------- live updates (WebSocket) ----------

class Live:
    def __init__(self, conn, uid, token):
        self.conn, self.uid, self.token, self.q, self.alive = conn, uid, token, queue.Queue(maxsize=200), True

    def send(self, msg):
        try: self.q.put_nowait(msg)
        except queue.Full: self.close()

    def close(self):
        self.alive = False
        try: self.conn.shutdown(socket.SHUT_RDWR)
        except OSError: pass

    @staticmethod
    def frame(op, payload):
        n = len(payload)
        head = bytes([0x80 | op]) + (bytes([n]) if n < 126 else bytes([126]) + struct.pack(">H", n) if n < 65536 else bytes([127]) + struct.pack(">Q", n))
        return head + payload

    def run(self):
        sockets.add(self)
        writer = threading.Thread(target=self.write, daemon=True); writer.start()
        try:
            # The phone pings only when the box has been quiet, so a busy family can mean nothing comes back for minutes:
            # the box pings a quiet phone itself (also keeps Cloudflare, which drops idle connections after 100 s, happy)
            # and gives up only when a ping of its own got no answer.
            self.conn.settimeout(45)
            waiting = False
            while self.alive:
                try: h = self.read(2)
                except socket.timeout:
                    if waiting: break
                    self.send((0x9, b"")); waiting = True; continue
                waiting = False
                op, n = h[0] & 0x0f, h[1] & 0x7f
                if n == 126: n = struct.unpack(">H", self.read(2))[0]
                elif n == 127: n = struct.unpack(">Q", self.read(8))[0]
                if n > 65536: break
                mask = self.read(4) if h[1] & 0x80 else b"\0\0\0\0"
                p = bytes(b ^ mask[i % 4] for i, b in enumerate(self.read(n)))
                if op == 0x8: break
                if op == 0x9: self.send((0xA, p))
        except Exception:
            pass
        finally:
            self.alive = False; sockets.discard(self); self.q.put(None)
            try: self.conn.close()
            except OSError: pass

    def read(self, n):
        b = b""
        while len(b) < n:
            c = self.conn.recv(n - len(b))
            if not c: raise EOFError
            b += c
        return b

    def write(self):
        while self.alive:
            m = self.q.get()
            if m is None: return
            try:
                if isinstance(m, tuple): self.conn.sendall(self.frame(m[0], m[1]))
                else: self.conn.sendall(self.frame(0x1, json.dumps(m).encode()))
            except OSError:
                self.close(); return


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

class H(BaseHTTPRequestHandler):
    server_version = "burrow-box"; sys_version = ""; protocol_version = "HTTP/1.1"; timeout = 60

    def log_message(self, fmt, *a): pass        # nothing about callers is logged

    # -- plumbing
    def out(self, code, body=None, ctype="application/json", cookie=None):
        data = b"" if body is None else body if isinstance(body, bytes) else body.encode() if isinstance(body, str) else json.dumps(body).encode()
        self.send_response(code)
        try: unread = not getattr(self, "_read", False) and int(self.headers.get("Content-Length") or 0) > 0
        except ValueError: unread = True
        if unread: self.close_connection = True; self.send_header("Connection", "close")   # don't misread a body we skipped as the next request
        self.send_header("Content-Type", ctype if body is not None else "text/plain")
        self.send_header("Content-Length", str(len(data))); self.send_header("Cache-Control", "no-store")
        if cookie: self.send_header("Set-Cookie", "JSESSIONID=%s; Path=/; HttpOnly; Secure; SameSite=Strict" % cookie)
        self.end_headers(); self.wfile.write(data)

    def raw_body(self):
        try: n = int(self.headers.get("Content-Length") or 0)
        except ValueError: n = -1
        if n < 0 or n > MAX_BODY: raise ValueError("too large")
        self._read = True
        return self.rfile.read(n) if n else b""

    def body(self):
        b = self.raw_body()
        if "application/x-www-form-urlencoded" in (self.headers.get("Content-Type") or ""):
            return {k: v[0] for k, v in urllib.parse.parse_qs(b.decode(), keep_blank_values=True).items()}
        if not b: return {}
        v = json.loads(b)
        if not isinstance(v, dict): raise ValueError("not an object")
        return v

    def token(self):
        a = self.headers.get("Authorization") or ""
        if a.startswith("Bearer "): return a[7:].strip()
        for part in (self.headers.get("Cookie") or "").split(";"):
            k, _, v = part.strip().partition("=")
            if k == "JSESSIONID" and v: return v
        return None

    def me(self):
        t = self.token()
        if not t: return None
        s = q("SELECT user_id, expires FROM sessions WHERE hash=?", (tok_hash(t),), one=True)
        if not s or s[1] < now_ms(): return None
        r = user_row(s[0])
        return user_json(r) if r else None

    def my_device(self, me, want=None):
        rows = q("SELECT id FROM devices WHERE user_id=? ORDER BY id", (me["id"],))
        ids = [r[0] for r in rows]
        if want is not None: return want if want in ids else None
        return ids[0] if ids else None

    def route(self, method):
        self._read = False      # per request: the handler object lives as long as the (kept-alive) connection
        u = urllib.parse.urlsplit(self.path)
        path, qs = u.path.rstrip("/") or "/", urllib.parse.parse_qs(u.query)
        try:
            if path == "/api/socket" and method == "GET": return self.socket_()
            if path == "/api/session": return self.session_(method, qs)
            if path == "/api/session/token" and method == "POST": return self.token_new()
            if path == "/api/session/token/revoke" and method == "POST": return self.token_revoke()
            if path == "/api/users" and method == "POST" and not q("SELECT 1 FROM users LIMIT 1"): return self.users_(method, None, None)
            if path == "/api/server" and method == "GET": return self.server_(method, None, None, qs)   # public, as on Traccar
            me = self.me()
            if not me: return self.out(401, "Unauthorized", "text/plain")
            m = re.fullmatch(r"/api/([a-z]+)(?:/(\d+))?", path)
            if m and m[1] in ("users", "groups", "devices", "geofences", "permissions", "positions", "server"):
                return getattr(self, m[1] + "_")(method, int(m[2]) if m[2] else None, me, qs)
            if path == "/api/reports/events" and method == "GET": return self.events_(me, qs)
            m = re.fullmatch(r"/api/burrow/([a-z]+)", path)
            if m and m[1] in ("report", "pubkeys", "pubkey", "keyboxes", "settings"): return getattr(self, "b_" + m[1])(method, me, qs)
            return self.out(404, "Not found", "text/plain")
        except (ValueError, KeyError, TypeError) as e:
            return self.out(400, "Bad request: %s" % str(e)[:100], "text/plain")
        except sqlite3.IntegrityError:
            return self.out(400, "Duplicate entry", "text/plain")

    def do_GET(self): self.route("GET")
    def do_POST(self): self.route("POST")
    def do_PUT(self): self.route("PUT")
    def do_DELETE(self): self.route("DELETE")

    # -- sign-in
    def session_(self, method, qs):
        if method == "POST":
            b = self.body(); email = str(b.get("email", "")).strip().lower()
            if throttled(email): return self.out(429, "Too many tries", "text/plain")
            r = q("SELECT * FROM users WHERE email=?", (email,), one=True)
            if not r or not pw_ok(str(b.get("password", "")), r[3]): return self.out(401, "Unauthorized", "text/plain")
            return self.out(200, user_json(r), cookie=new_session(r[0], "cookie", 30 * 86400_000))
        if method == "GET":
            if "token" in qs:
                s = q("SELECT user_id, expires FROM sessions WHERE hash=? AND kind='token'", (tok_hash(qs["token"][0]),), one=True)
                if not s or s[1] < now_ms(): return self.out(401, "Unauthorized", "text/plain")
                return self.out(200, user_json(user_row(s[0])), cookie=new_session(s[0], "cookie", 30 * 86400_000))
            me = self.me()
            return self.out(200, me) if me else self.out(404, "Not found", "text/plain")
        if method == "DELETE":
            t = self.token()
            if t: q("DELETE FROM sessions WHERE hash=? AND kind='cookie'", (tok_hash(t),))
            return self.out(204)
        return self.out(405, "Method not allowed", "text/plain")

    def token_new(self):
        me = self.me()
        if not me: return self.out(401, "Unauthorized", "text/plain")
        exp = parse_iso(self.body().get("expiration", "")) or now_ms() + 3650 * 86400_000
        exp = min(exp, now_ms() + 3660 * 86400_000)
        return self.out(200, new_session(me["id"], "token", exp - now_ms()), "text/plain")

    def token_revoke(self):
        me = self.me()
        if not me: return self.out(401, "Unauthorized", "text/plain")
        t = str(self.body().get("token", ""))
        q("DELETE FROM sessions WHERE hash=? AND kind='token' AND (user_id=? OR ?)", (tok_hash(t), me["id"], 1 if me["administrator"] else 0))
        return self.out(204)

    # -- Traccar-shaped objects
    def server_(self, method, _id, me, qs):
        # newServer, as Traccar says it: no account yet, so the app offers to create the first (the organizer). Used by
        # self-hosted boxes (selfhost/); on Burrow Cloud the cloud service makes the first account itself.
        info = {"id": 1, "registration": False, "readonly": False, "version": "burrow-box 1", "newServer": not q("SELECT 1 FROM users LIMIT 1"),
                "attributes": {"burrowBox": 1, "retentionDays": setting("retentionDays", 30)}}
        if method == "PUT" and not (me and me["administrator"]): return self.out(403, "Forbidden", "text/plain")
        if method == "PUT": self.raw_body()
        return self.out(200, info)

    def users_(self, method, uid, me, qs=None):
        if method == "GET":
            rows = q("SELECT * FROM users ORDER BY id") if me["administrator"] else [user_row(me["id"])]
            return self.out(200, [user_json(r) for r in rows])
        b = self.body() if method in ("POST", "PUT") else {}
        if method == "POST":
            first = me is None
            if not first and not me["administrator"]: return self.out(403, "Forbidden", "text/plain")
            email, name, pw = str(b.get("email", "")).strip().lower(), str(b.get("name", "")).strip()[:80], str(b.get("password", ""))
            if not email or not name or len(pw) < 6: return self.out(400, "Name, email and a password of 6 or more are needed", "text/plain")
            admin = 1 if first or (b.get("administrator") and me and me["administrator"]) else 0
            i = ins("INSERT INTO users (email, name, pw, admin, attrs) VALUES (?,?,?,?,?)",
                    (email, name, pw_hash(pw), admin, json.dumps(b.get("attributes") or {})))
            return self.out(200, user_json(user_row(i)))
        r = user_row(uid) if uid else None
        if not r: return self.out(404, "Not found", "text/plain")
        if method == "PUT":
            if not (me["administrator"] or me["id"] == uid): return self.out(403, "Forbidden", "text/plain")
            name = str(b.get("name", r[2])).strip()[:80] or r[2]
            email = str(b.get("email", r[1])).strip().lower() or r[1]
            admin = (1 if b.get("administrator") else 0) if me["administrator"] and "administrator" in b else r[4]
            if r[4] and not admin and not q("SELECT 1 FROM users WHERE admin=1 AND id<>?", (uid,)):
                return self.out(400, "The family needs at least one organizer", "text/plain")
            attrs = json.dumps(b["attributes"]) if isinstance(b.get("attributes"), dict) else r[5]
            q("UPDATE users SET name=?, email=?, admin=?, attrs=? WHERE id=?", (name, email, admin, attrs, uid))
            pw = b.get("password")
            if isinstance(pw, str) and pw:
                if len(pw) < 6: return self.out(400, "Password too short", "text/plain")
                q("UPDATE users SET pw=? WHERE id=?", (pw_hash(pw), uid))
                end_sessions(uid, keep=self.token() if me["id"] == uid else None)   # a new password signs every other phone out
            return self.out(200, user_json(user_row(uid)))
        if method == "DELETE":
            if not me["administrator"]: return self.out(403, "Forbidden", "text/plain")
            if r[4] and not q("SELECT 1 FROM users WHERE admin=1 AND id<>?", (uid,)): return self.out(400, "The family needs at least one organizer", "text/plain")
            end_sessions(uid)
            q("DELETE FROM users WHERE id=?", (uid,))
            return self.out(204)
        return self.out(405, "Method not allowed", "text/plain")

    def groups_(self, method, gid, me, qs):
        if method == "GET": return self.out(200, [{"id": r[0], "name": r[1], "groupId": 0, "attributes": jl(r[2])} for r in q("SELECT * FROM groups_ ORDER BY id")])
        b = self.body() if method in ("POST", "PUT") else {}
        if method == "POST":
            if not me["administrator"]: return self.out(403, "Forbidden", "text/plain")
            i = ins("INSERT INTO groups_ (name, attrs) VALUES (?,?)", (str(b.get("name", "Family"))[:80], json.dumps(b.get("attributes") or {})))
            return self.out(200, {"id": i, "name": str(b.get("name", "Family"))[:80], "groupId": 0, "attributes": b.get("attributes") or {}})
        if not q("SELECT 1 FROM groups_ WHERE id=?", (gid,)): return self.out(404, "Not found", "text/plain")
        if method == "PUT":
            q("UPDATE groups_ SET name=?, attrs=? WHERE id=?", (str(b.get("name", "Family"))[:80], json.dumps(b.get("attributes") or {}), gid))
            broadcast({"devices": []})
            return self.out(200, {"id": gid, "name": b.get("name", "Family"), "groupId": 0, "attributes": b.get("attributes") or {}})
        if method == "DELETE":
            if not me["administrator"]: return self.out(403, "Forbidden", "text/plain")
            q("DELETE FROM groups_ WHERE id=?", (gid,)); return self.out(204)
        return self.out(405, "Method not allowed", "text/plain")

    def permissions_(self, method, _id, me, qs):
        self.raw_body()          # one family per box: everything in it is shared with everyone in it
        return self.out(204)

    def devices_(self, method, did, me, qs):
        if method == "GET":
            ids = [int(x) for x in qs.get("id", [])]
            rows = [r for r in q("SELECT * FROM devices ORDER BY id") if not ids or r[0] in ids]
            return self.out(200, [device_json(r) for r in rows])
        b = self.body() if method in ("POST", "PUT") else {}
        a = dict(b.get("attributes") or {})
        if method == "POST":
            owner = int(a.pop("userId", me["id"]) or me["id"])
            if owner != me["id"] and not me["administrator"]: return self.out(403, "Forbidden", "text/plain")
            uniq = str(b.get("uniqueId", "")).strip()[:64] or "fm-" + secrets.token_hex(8)
            i = ins("INSERT INTO devices (unique_id, name, group_id, user_id, attrs) VALUES (?,?,?,?,?)",
                    (uniq, str(b.get("name", "Phone"))[:80], int(b.get("groupId") or 0), owner, json.dumps(a)))
            return self.out(200, device_json(q("SELECT * FROM devices WHERE id=?", (i,), one=True)))
        r = q("SELECT * FROM devices WHERE id=?", (did,), one=True)
        if not r: return self.out(404, "Not found", "text/plain")
        if r[4] != me["id"] and not me["administrator"]: return self.out(403, "Forbidden", "text/plain")
        if method == "PUT":
            owner = int(a.pop("userId", r[4]) or r[4])
            if owner != r[4] and not me["administrator"]: owner = r[4]
            q("UPDATE devices SET name=?, user_id=?, attrs=? WHERE id=?", (str(b.get("name", r[2]))[:80], owner, json.dumps(a), did))
            broadcast({"devices": [{"id": did}]})
            return self.out(200, device_json(q("SELECT * FROM devices WHERE id=?", (did,), one=True)))
        if method == "DELETE":
            with lock:
                for t in ("positions", "events"): db.execute("DELETE FROM %s WHERE device_id=?" % t, (did,))
                db.execute("DELETE FROM pubkeys WHERE device_id=?", (did,)); db.execute("DELETE FROM keyboxes WHERE to_device=?", (did,))
                db.execute("DELETE FROM devices WHERE id=?", (did,)); db.commit()
            return self.out(204)
        return self.out(405, "Method not allowed", "text/plain")

    def geofences_(self, method, gid, me, qs):
        def js(i, body): return dict(jl(body), id=i)
        if method == "GET": return self.out(200, [js(r[0], r[1]) for r in q("SELECT * FROM geofences ORDER BY id")])
        b = self.body() if method in ("POST", "PUT") else {}
        keep = {k: b[k] for k in ("name", "area", "description", "attributes", "calendarId") if k in b}
        if len(json.dumps(keep)) > 65536: raise ValueError("place too large")
        if method == "POST":
            i = ins("INSERT INTO geofences (body) VALUES (?)", (json.dumps(keep),)); broadcast({"devices": []}); return self.out(200, js(i, json.dumps(keep)))
        if not q("SELECT 1 FROM geofences WHERE id=?", (gid,)): return self.out(404, "Not found", "text/plain")
        if method == "PUT": q("UPDATE geofences SET body=? WHERE id=?", (json.dumps(keep), gid)); broadcast({"devices": []}); return self.out(200, js(gid, json.dumps(keep)))
        if method == "DELETE": q("DELETE FROM geofences WHERE id=?", (gid,)); return self.out(204)
        return self.out(405, "Method not allowed", "text/plain")

    def positions_(self, method, _id, me, qs):
        if method != "GET": return self.out(405, "Method not allowed", "text/plain")
        if "id" in qs:
            ids = [int(x) for x in qs["id"]][:500]
            rows = q("SELECT * FROM positions WHERE id IN (%s)" % ",".join("?" * len(ids)), ids) if ids else []
        elif "deviceId" in qs:
            f, t = parse_iso(qs.get("from", [""])[0]) or 0, parse_iso(qs.get("to", [""])[0]) or now_ms() + 86400_000
            rows = q("SELECT * FROM positions WHERE device_id=? AND fix_ms BETWEEN ? AND ? ORDER BY fix_ms LIMIT 20000", (int(qs["deviceId"][0]), f, t))
        else:
            rows = q("SELECT * FROM positions WHERE id IN (%s)" % LATEST)
        return self.out(200, [position_json(r) for r in rows])

    def events_(self, me, qs):
        f, t = parse_iso(qs.get("from", [""])[0]) or now_ms() - 86400_000, parse_iso(qs.get("to", [""])[0]) or now_ms() + 60_000
        rows = q("SELECT id, device_id, position_id, ms FROM events WHERE ms BETWEEN ? AND ? ORDER BY id LIMIT 5000", (f, t))
        return self.out(200, [{"id": r[0], "deviceId": r[1], "positionId": r[2], "eventTime": iso(r[3]), "type": "burrow", "attributes": {}} for r in rows])

    # -- Burrow's own: sealed reports, keys, settings
    def b_report(self, method, me, qs):
        if method != "POST": return self.out(405, "Method not allowed", "text/plain")
        b = self.body()
        dev = self.my_device(me, int(b["deviceId"]) if b.get("deviceId") else None)
        if not dev: return self.out(400, "This phone isn't in the family", "text/plain")
        sealed = str(b.get("sealed", ""))
        if not re.fullmatch(r"b1\.\d{1,6}\.[A-Za-z0-9_-]{20,}", sealed) or len(sealed) > 400_000: raise ValueError("sealed report expected")
        t = int(b.get("t") or now_ms())
        t = min(t, now_ms() + 300_000)
        srv = now_ms()
        pid = ins("INSERT INTO positions (device_id, fix_ms, server_ms, ev, sealed) VALUES (?,?,?,?,?)", (dev, t, srv, 1 if b.get("ev") else 0, sealed))
        q("UPDATE devices SET last_ms=? WHERE id=?", (srv, dev))
        msg = {"positions": [{"id": pid, "deviceId": dev}]}
        if b.get("ev"):
            eid = ins("INSERT INTO events (device_id, position_id, ms) VALUES (?,?,?)", (dev, pid, srv))
            msg["events"] = [{"id": eid, "deviceId": dev, "positionId": pid}]
        broadcast(msg)
        return self.out(200, {"id": pid})

    def b_pubkeys(self, method, me, qs):
        rows = q("SELECT p.device_id, d.user_id, d.name, p.pub, p.mac, p.ms FROM pubkeys p JOIN devices d ON d.id=p.device_id ORDER BY p.device_id")
        return self.out(200, [{"deviceId": r[0], "userId": r[1], "name": r[2], "pub": r[3], "mac": r[4], "time": iso(r[5])} for r in rows])

    def b_pubkey(self, method, me, qs):
        if method != "PUT": return self.out(405, "Method not allowed", "text/plain")
        b = self.body()
        dev = self.my_device(me, int(b["deviceId"]) if b.get("deviceId") else None)
        if not dev: return self.out(400, "This phone isn't in the family", "text/plain")
        pub, mac = str(b.get("pub", "")), str(b.get("mac", ""))
        if not re.fullmatch(r"[A-Za-z0-9_-]{40,200}", pub) or not re.fullmatch(r"[A-Za-z0-9_-]{0,100}", mac): raise ValueError("bad key")
        q("INSERT INTO pubkeys VALUES (?,?,?,?) ON CONFLICT(device_id) DO UPDATE SET pub=excluded.pub, mac=excluded.mac, ms=excluded.ms",
          (dev, pub, mac, now_ms()))
        broadcast({"keys": [{"deviceId": dev}]})
        return self.out(200, {"deviceId": dev})

    def b_keyboxes(self, method, me, qs):
        if method == "POST":
            b = self.body()
            to, box = int(b["to"]), str(b.get("box", ""))
            if not q("SELECT 1 FROM devices WHERE id=?", (to,)) or not re.fullmatch(r"[A-Za-z0-9_.-]{40,20000}", box): raise ValueError("bad key box")
            i = ins("INSERT INTO keyboxes (to_device, from_device, box, ms) VALUES (?,?,?,?)", (to, self.my_device(me), box, now_ms()))
            q("DELETE FROM keyboxes WHERE to_device=? AND id NOT IN (SELECT id FROM keyboxes WHERE to_device=? ORDER BY id DESC LIMIT 20)", (to, to))
            broadcast({"keys": [{"deviceId": to}]})
            return self.out(200, {"id": i})
        mine = [r[0] for r in q("SELECT id FROM devices WHERE user_id=?", (me["id"],))]
        rows = q("SELECT id, to_device, from_device, box, ms FROM keyboxes WHERE to_device IN (%s) ORDER BY id" % ",".join("?" * len(mine)), mine) if mine else []
        return self.out(200, [{"id": r[0], "to": r[1], "from": r[2], "box": r[3], "time": iso(r[4])} for r in rows])

    def b_settings(self, method, me, qs):
        if method == "PUT":
            if not me["administrator"]: return self.out(403, "Forbidden", "text/plain")
            d = int(self.body().get("retentionDays", 30))
            if not 1 <= d <= 365: raise ValueError("retentionDays 1 to 365")
            ins("INSERT INTO settings VALUES ('retentionDays', ?) ON CONFLICT(k) DO UPDATE SET v=excluded.v", (json.dumps(d),))
            purge()
        return self.out(200, {"retentionDays": setting("retentionDays", 30)})

    def socket_(self):
        me = self.me()
        if not me or (self.headers.get("Upgrade") or "").lower() != "websocket": return self.out(401, "Unauthorized", "text/plain")
        key = self.headers.get("Sec-WebSocket-Key", "")
        acc = base64.b64encode(hashlib.sha1((key + "258EAFA5-E914-47DA-95CA-C5AB0DC85B11").encode()).digest()).decode()
        self.wfile.write(("HTTP/1.1 101 Switching Protocols\r\nUpgrade: websocket\r\nConnection: Upgrade\r\nSec-WebSocket-Accept: %s\r\n\r\n" % acc).encode())
        self.wfile.flush()
        Live(self.connection, me["id"], self.token()).run()
        self.close_connection = True


def main():
    global db
    os.makedirs(DATA, exist_ok=True)
    db = sqlite3.connect(DB, check_same_thread=False)
    db.execute("PRAGMA journal_mode=WAL"); db.execute("PRAGMA foreign_keys=OFF")
    db.executescript(SCHEMA); db.commit()
    os.chmod(DB, 0o600)

    def janitor():
        while True:
            try: purge()
            except Exception as e: print("purge:", e, flush=True)
            time.sleep(3600)
    threading.Thread(target=janitor, daemon=True).start()
    srv = ThreadingHTTPServer((HOST, PORT), H); srv.daemon_threads = True
    print("burrow box on :%d" % PORT, flush=True)
    srv.serve_forever()


if __name__ == "__main__":
    main()
