"""Signalmark — a quiet tracker for sent Gmail messages.

Flask backend that serves the vanilla JS dashboard and implements the
tracking API contract used by the Gmail extension.
"""
from __future__ import annotations

import base64
import json
import logging
import os
import random
import sys
import re
import sqlite3
import string
import threading
import time
from datetime import datetime, timedelta, timezone

import gmail_client
import tracking
from flask import Flask, Response, g, jsonify, redirect, render_template, request, session

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, "signalmark.db")

# ── Load credentials from .env ──────────────────────────────────────────────
def _load_env_file(path: str) -> None:
    """Minimal .env loader — no extra dependency needed."""
    if not os.path.isfile(path):
        return
    with open(path, encoding="utf-8") as fh:
        for line in fh:
            line = line.strip()
            if not line or line.startswith("#") or "=" not in line:
                continue
            key, _, value = line.partition("=")
            os.environ.setdefault(key.strip(), value.strip())

_load_env_file(os.path.join(BASE_DIR, ".env"))
LOGIN_EMAIL = os.environ.get("SIGNALMARK_EMAIL", "")
LOGIN_PASSWORD = os.environ.get("SIGNALMARK_PASSWORD", "")

TRANSPARENT_GIF = base64.b64decode(
    "R0lGODlhAQABAIAAAAAAAP///ywAAAAAAQABAAACAUwAOw=="
)


def _broadcast_track_event(event_type: str, track_row: dict) -> None:
    """Push a track event to all connected WebSocket clients."""
    broadcast.broadcast({
        "type": event_type,
        "track_id": track_row.get("track_id"),
        "track": track_row,
    })

EMAIL_RE = re.compile(r"^[^@\s]+@[^@\s]+\.[^@\s]+$")

SCHEMA = """
CREATE TABLE IF NOT EXISTS tracks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    track_id TEXT UNIQUE NOT NULL,
    user_email TEXT NOT NULL,
    recipient TEXT NOT NULL,
    subject TEXT,
    status TEXT NOT NULL DEFAULT 'sent' CHECK (status IN ('sent', 'opened')),
    sent_at TEXT NOT NULL,
    opened_at TEXT,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_tracks_user_email ON tracks(user_email);
CREATE INDEX IF NOT EXISTS idx_tracks_track_id ON tracks(track_id);

CREATE TABLE IF NOT EXISTS clicks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    track_id TEXT NOT NULL,
    original_url TEXT NOT NULL,
    user_agent TEXT,
    user_ip TEXT,
    clicked_at TEXT NOT NULL,
    FOREIGN KEY (track_id) REFERENCES tracks(track_id)
);
CREATE INDEX IF NOT EXISTS idx_clicks_track_id ON clicks(track_id);
"""

KV_SCHEMA = """
CREATE TABLE IF NOT EXISTS app_settings (
    key TEXT PRIMARY KEY,
    value TEXT
);
"""

GMAIL_SYNC_INTERVAL = int(os.environ.get("GMAIL_SYNC_INTERVAL", "30"))
GMAIL_LOOKBACK_DAYS = int(os.environ.get("GMAIL_LOOKBACK_DAYS", "30"))

EXTRA_COLUMNS = (
    ("gmail_id", "TEXT"),
    ("source", "TEXT NOT NULL DEFAULT 'manual'"),
    ("thread_id", "TEXT"),
    ("webhook_url", "TEXT"),
    ("click_count", "INTEGER NOT NULL DEFAULT 0"),
    ("sender_fingerprint", "TEXT"),
)

app = Flask(
    __name__,
    template_folder=os.path.join(BASE_DIR, "templates"),
    static_folder=os.path.join(BASE_DIR, "static"),
)
app.secret_key = os.environ.get("SECRET_KEY", "signalmark-secret-change-me")


# ─── SSE (Server-Sent Events) broadcast ──────────────────────────────────
import queue as _queue

class SSEBroadcast:
    """Simple SSE broadcast: each listener gets its own queue."""

    def __init__(self) -> None:
        self._clients: set[_queue.Queue] = set()
        self._lock = threading.Lock()

    def subscribe(self) -> _queue.Queue:
        q: _queue.Queue = _queue.Queue()
        with self._lock:
            self._clients.add(q)
        return q

    def unsubscribe(self, q: _queue.Queue) -> None:
        with self._lock:
            self._clients.discard(q)

    def broadcast(self, message: dict) -> None:
        payload = json.dumps(message)
        with self._lock:
            clients = list(self._clients)
        for q in clients:
            try:
                q.put_nowait(payload)
            except _queue.Full:
                pass


broadcast = SSEBroadcast()


@app.get("/api/events")
def sse_stream():
    """SSE endpoint — clients connect here for real-time push."""
    q = broadcast.subscribe()

    def generate():
        try:
            yield f"data: {json.dumps({'type': 'connected'})}\n\n"
            while True:
                try:
                    payload = q.get(timeout=30)
                    yield f"data: {payload}\n\n"
                except _queue.Empty:
                    # Send keepalive comment to prevent proxy/browser timeouts
                    yield ": keepalive\n\n"
        except GeneratorExit:
            pass
        finally:
            broadcast.unsubscribe(q)

    response = Response(generate(), mimetype="text/event-stream")
    response.headers["Cache-Control"] = "no-cache, no-store, must-revalidate"
    response.headers["Pragma"] = "no-cache"
    response.headers["Expires"] = "0"
    response.headers["X-Accel-Buffering"] = "no"
    response.headers["Access-Control-Allow-Origin"] = "*"
    response.headers["Connection"] = "keep-alive"
    response.headers["Content-Type"] = "text/event-stream; charset=utf-8"
    response.direct_passthrough = True
    return response


def _detect_tunnel_url() -> str | None:
    """Read cloudflared tunnel log to extract the public trycloudflare.com URL.
    Returns the most recent tunnel URL found, preferring active (non-shutdown) tunnels.
    """
    tunnel_log = os.path.join(BASE_DIR, "tunnel.log")
    if not os.path.exists(tunnel_log):
        return None
    try:
        import re as _re
        urls = []  # list of (line_number, url)
        shutdown_found = False
        with open(tunnel_log, encoding="utf-8", errors="replace") as fh:
            for line in fh:
                if "trycloudflare.com" in line:
                    m = _re.search(r"https://[a-z0-9-]+\.trycloudflare\.com", line)
                    if m:
                        urls.append(m.group(0))
                if "shutdown" in line.lower() or "exiting" in line.lower():
                    shutdown_found = True
        if not urls:
            return None
        # If no shutdown found, the last tunnel URL is likely active
        if not shutdown_found:
            return urls[-1]
        # If shutdown found, return the URL before the shutdown
        return urls[-1] if urls else None
    except Exception:
        pass
    return None


def get_pixel_base_url() -> str:
    """Return the public URL used for tracking pixels.

    Priority:
    1. PIXEL_BASE_URL env var (explicit override)
    2. Auto-detected cloudflared tunnel URL
    3. Auto-detected LAN IP
    4. Flask request.url_root (localhost fallback)
    """
    env_url = os.environ.get("PIXEL_BASE_URL")
    if env_url:
        return env_url.rstrip("/")
    tunnel = _detect_tunnel_url()
    if tunnel:
        return tunnel
    ip = _detect_lan_ip()
    if ip:
        return f"http://{ip}:{os.environ.get('PORT', '5055')}"
    return None  # will fall back to request.url_root at call site


def _detect_lan_ip() -> str | None:
    """Detect the machine's LAN IP address."""
    try:
        import socket
        s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
        s.settimeout(0.1)
        s.connect(("8.8.8.8", 80))
        ip = s.getsockname()[0]
        s.close()
        return ip
    except Exception:
        pass
    return None


def utcnow() -> str:
    return datetime.now(timezone.utc).isoformat()


def parse_iso(value) -> str | None:
    if value is None or str(value).strip() == "":
        return None
    try:
        dt = datetime.fromisoformat(str(value).replace("Z", "+00:00"))
    except ValueError:
        return None
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)
    return dt.astimezone(timezone.utc).isoformat()


def get_db() -> sqlite3.Connection:
    db = getattr(g, "_db", None)
    if db is None:
        db = sqlite3.connect(DB_PATH)
        db.row_factory = sqlite3.Row
        db.execute("PRAGMA journal_mode=WAL")
        db.execute("PRAGMA busy_timeout=5000")
        g._db = db
    return db


@app.teardown_appcontext
def close_db(_exc) -> None:
    db = getattr(g, "_db", None)
    if db is not None:
        db.close()


def seed_demo(db: sqlite3.Connection) -> None:
    now = datetime.now(timezone.utc)
    rows = [
        (
            "SM-welcome-2026",
            "demo@example.com",
            "maya@northstar.studio",
            "Quick follow-up from our conversation",
            "opened",
            now - timedelta(minutes=42),
            now - timedelta(minutes=18),
        ),
        (
            "SM-brief-2026",
            "demo@example.com",
            "jordan@fieldnotes.co",
            "The brief for next week",
            "sent",
            now - timedelta(hours=4),
            None,
        ),
        (
            "SM-intro-2026",
            "demo@example.com",
            "alex@harborcollective.com",
            "Introduction — Alex, meet the team",
            "opened",
            now - timedelta(hours=26),
            now - timedelta(hours=23),
        ),
    ]
    db.executemany(
        """INSERT INTO tracks
           (track_id, user_email, recipient, subject, status, sent_at, opened_at, created_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
        [
            (
                track_id,
                user_email,
                recipient,
                subject,
                status,
                sent_at.isoformat(),
                opened_at.isoformat() if opened_at else None,
                sent_at.isoformat(),
            )
            for track_id, user_email, recipient, subject, status, sent_at, opened_at in rows
        ],
    )
    db.commit()


def init_db() -> None:
    with sqlite3.connect(DB_PATH) as db:
        db.executescript(SCHEMA)
        # Demo data removed - start with empty database


def serialize(row: sqlite3.Row) -> dict:
    keys = row.keys()
    return {
        "id": row["id"],
        "track_id": row["track_id"],
        "user_email": row["user_email"],
        "recipient": row["recipient"],
        "subject": row["subject"],
        "status": row["status"],
        "sent_at": row["sent_at"],
        "opened_at": row["opened_at"],
        "created_at": row["created_at"],
        "source": row["source"] if "source" in keys else "manual",
        "gmail_id": row["gmail_id"] if "gmail_id" in keys else None,
        "webhook_url": row["webhook_url"] if "webhook_url" in keys else None,
        "click_count": row["click_count"] if "click_count" in keys else 0,
        "sender_fingerprint": row["sender_fingerprint"] if "sender_fingerprint" in keys else None,
    }


def migrate_db() -> None:
    with sqlite3.connect(DB_PATH) as db:
        db.executescript(KV_SCHEMA)
        existing = {row[1] for row in db.execute("PRAGMA table_info(tracks)")}
        for name, decl in EXTRA_COLUMNS:
            if name not in existing:
                db.execute(f"ALTER TABLE tracks ADD COLUMN {name} {decl}")
        db.execute("CREATE INDEX IF NOT EXISTS idx_tracks_gmail_id ON tracks(gmail_id)")


def get_setting(key: str, default=None) -> str | None:
    try:
        row = get_db().execute(
            "SELECT value FROM app_settings WHERE key = ?", (key,)
        ).fetchone()
        return row["value"] if row else default
    except RuntimeError:
        # Fallback when called outside request context (e.g. background sync)
        with sqlite3.connect(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            row = conn.execute(
                "SELECT value FROM app_settings WHERE key = ?", (key,)
            ).fetchone()
            return row["value"] if row else default


def set_setting(key: str, value: str) -> None:
    try:
        get_db().execute(
            "INSERT INTO app_settings (key, value) VALUES (?, ?) "
            "ON CONFLICT(key) DO UPDATE SET value = excluded.value",
            (key, value),
        )
        get_db().commit()
    except RuntimeError:
        with sqlite3.connect(DB_PATH) as conn:
            conn.execute(
                "INSERT INTO app_settings (key, value) VALUES (?, ?) "
                "ON CONFLICT(key) DO UPDATE SET value = excluded.value",
                (key, value),
            )
            conn.commit()


def perform_sync() -> dict:
    """Run a sync cycle.  Only tracks SENT messages.

    Raises on critical errors so callers can surface them to the user.
    """
    log.info("perform_sync: starting sync cycle")
    service = gmail_client.get_service()
    if service is None:
        raise RuntimeError("Gmail is not connected.")

    profile = gmail_client.get_profile(service)
    history_id = profile.get("historyId")
    last_history_id = get_setting("gmail_last_history_id")

    added = 0
    if last_history_id and history_id and int(history_id) > int(last_history_id):
        log.info("perform_sync: processing history %s -> %s", last_history_id, history_id)
        added = _process_history(service, last_history_id)
    else:
        log.info("perform_sync: no history gap (last=%s, current=%s), using sent list", last_history_id, history_id)

    # If history returned nothing, fall back to direct sent-message query.
    if added == 0:
        log.info("perform_sync: history returned 0, falling back to sent list query")
        added = _sync_from_sent_list(service, user_email=get_setting("gmail_account") or "")

    set_setting("gmail_last_history_id", str(history_id) if history_id else "")
    set_setting("gmail_last_sync_epoch", str(time.time()))
    log.info("perform_sync: complete — added %d sent tracks", added)
    return {"added": added, "scanned": added}


def _process_history(service, start_history_id: str) -> int:
    """Process Gmail history events.  ONLY tracks messages that carry the
    SENT label — inbox, drafts, and other labels are rejected immediately
    without any extra API call (the history event itself includes labelIds).
    """
    added = 0
    seen = set()
    skipped_inbox = 0
    for record in gmail_client.fetch_history(service, start_history_id):
        # ── messagesAdded ──────────────────────────────────────────────
        # The history event includes labelIds directly, so we can reject
        # inbox messages without a costly get_message_metadata() call.
        for change in record.get("messagesAdded", []):
            msg = change.get("message", {})
            gmail_id = msg.get("id")
            if not gmail_id:
                continue
            if gmail_id in seen:
                continue
            seen.add(gmail_id)
            label_ids = msg.get("labelIds", [])
            # STRICT: only track messages with the SENT label.
            # Reject anything that is inbox-only, draft, spam, etc.
            if "SENT" not in label_ids:
                skipped_inbox += 1
                continue
            try:
                if _create_track_from_message(service, gmail_id):
                    added += 1
            except Exception as exc:
                log.error("_process_history: failed to track gmail_id=%s: %s", gmail_id, exc)

        # ── labelsAdded (e.g. SENT label added to existing message) ────
        for entry in record.get("labelsAdded", []):
            msg = entry.get("message", {})
            gmail_id = msg.get("id")
            if not gmail_id:
                continue
            if gmail_id in seen:
                continue
            seen.add(gmail_id)
            # Check if the added label is SENT
            added_label = entry.get("label", {})
            label_id = added_label.get("id", "")
            if label_id != "SENT" and label_id.upper() != "SENT":
                skipped_inbox += 1
                continue
            try:
                if _create_track_from_message(service, gmail_id):
                    added += 1
            except Exception as exc:
                log.error("_process_history: failed to track gmail_id=%s: %s", gmail_id, exc)

    if skipped_inbox:
        log.info("_process_history: skipped %d non-SENT messages", skipped_inbox)
    return added


def _sync_from_sent_list(service, user_email: str = "") -> int:
    """Fallback sync: query sent messages directly via Gmail search.

    Used when the history API returns nothing (gap pruned or too far behind).
    Looks back GMAIL_LOOKBACK_DAYS days.
    """
    added = 0
    after_epoch = int(time.time()) - (GMAIL_LOOKBACK_DAYS * 86400)
    for msg_data in gmail_client.fetch_sent(service, after_epoch, user_email=user_email):
        gmail_id = msg_data.get("gmail_id")
        if not gmail_id:
            continue
        existing = get_db().execute(
            "SELECT id FROM tracks WHERE gmail_id = ?", (gmail_id,)
        ).fetchone()
        if existing:
            continue
        recipients = msg_data.get("recipients", [])
        if not recipients or recipients == ["unknown@unknown"]:
            continue
        user_email = get_setting("gmail_account") or ""
        track_id = f"gm_{gmail_id}"
        try:
            get_db().execute(
                """INSERT INTO tracks
                   (track_id, user_email, recipient, subject, status,
                    sent_at, opened_at, created_at, source, gmail_id, thread_id)
                   VALUES (?, ?, ?, ?, 'sent', ?, NULL, ?, 'history', ?, ?)""",
                (
                    track_id,
                    user_email,
                    recipients[0],
                    msg_data.get("subject"),
                    msg_data.get("sent_at").isoformat() if msg_data.get("sent_at") else utcnow(),
                    utcnow(),
                    gmail_id,
                    msg_data.get("thread_id"),
                ),
            )
            get_db().commit()
            added += 1
        except sqlite3.IntegrityError:
            continue
        broadcast.broadcast({
            "type": "track_updated",
            "track_id": track_id,
            "gmail_id": gmail_id,
            "status": "delivered",
            "recipient": recipients[0],
            "subject": msg_data.get("subject", ""),
        })
    return added


def _create_track_from_message(service, gmail_id: str) -> bool:
    """Track an outgoing message.  Only creates a track if the message
    carries the SENT label (i.e. it was sent by the user, not received).

    Raises on critical errors so the caller can surface them.
    """
    try:
        meta = gmail_client.get_message_metadata(service, gmail_id)
    except Exception as exc:
        log.error("Failed to fetch metadata for gmail_id=%s: %s", gmail_id, exc)
        raise

    # Only track outgoing (SENT) messages — never inbox.
    label_ids = meta.get("labelIds", [])
    if "SENT" not in label_ids:
        log.debug("Skipping gmail_id=%s — no SENT label (labels=%s)", gmail_id, label_ids)
        return False

    if not meta.get("recipients"):
        return False

    # Skip automated/system emails (OTP, notifications, Google internal)
    from_header = (meta.get("from") or "").lower()
    sender_domain = gmail_client._extract_sender_domain(from_header)
    if sender_domain in gmail_client._AUTOMATED_SENDERS:
        return False
    subject_lower = (meta.get("subject") or "").lower()
    for pat in ("one-time", "otp", "verification code", "verify your", "security alert"):
        if pat in subject_lower:
            return False

    existing = get_db().execute(
        "SELECT id FROM tracks WHERE gmail_id = ?", (gmail_id,)
    ).fetchone()
    if existing:
        return False

    user_email = get_setting("gmail_account") or ""
    track_id = f"gm_{gmail_id}"
    try:
        get_db().execute(
            """INSERT INTO tracks
               (track_id, user_email, recipient, subject, status,
                sent_at, opened_at, created_at, source, gmail_id, thread_id)
               VALUES (?, ?, ?, ?, 'sent', ?, NULL, ?, 'history', ?, ?)""",
            (
                track_id,
                user_email,
                meta["recipients"][0],
                meta.get("subject") or None,
                meta.get("sent_at").isoformat() if meta.get("sent_at") else utcnow(),
                utcnow(),
                gmail_id,
                meta.get("thread_id"),
            ),
        )
        get_db().commit()
    except sqlite3.IntegrityError:
        return False

    broadcast.broadcast({
        "type": "track_updated",
        "track_id": track_id,
        "gmail_id": gmail_id,
        "status": "delivered",
        "recipient": meta["recipients"][0],
        "subject": meta.get("subject", ""),
    })
    return True


# ── Background sync (safe for LSAPI — threads, no fork) ───────────────────
# Uses threading.Timer within the same process.  No fork() is involved,
# so this is safe for LiteSpeed LSAPI workers.
_sync_lock = threading.Lock()
_main_pid = os.getpid()  # kept for logging only



def json_error(message: str, status: int):
    return jsonify({"error": message}), status


def require_email(value: str) -> str | None:
    value = (value or "").strip()
    if not EMAIL_RE.match(value):
        return None
    return value


def new_track_id() -> str:
    # Use os.urandom for uniqueness across forked LSAPI workers
    return "sm_" + os.urandom(9).hex()


@app.get("/")
def index():
    return render_template("index.html")


@app.get("/api/health")
def health():
    import subprocess
    try:
        result = subprocess.run(
            ["ps", "aux"], capture_output=True, text=True, timeout=5
        )
        processes = [
            line for line in result.stdout.splitlines()[1:]
            if "signalmark" in line.lower() or "app.py" in line.lower()
        ]
        proc_count = len(processes)
    except Exception:
        proc_count = 0
    healthy = proc_count < 8
    return jsonify({
        "status": "ok" if healthy else "warn",
        "service": "signalmark",
        "processes": proc_count,
        "process_limit": 8,
        "processes_healthy": healthy,
    })


# ── Login / Logout ──────────────────────────────────────────────────────────
@app.post("/api/login")
def login():
    data = request.get_json(silent=True) or {}
    email = str(data.get("email") or "").strip().lower()
    password = str(data.get("password") or "")
    if email == LOGIN_EMAIL.lower() and password == LOGIN_PASSWORD:
        session["logged_in"] = True
        session["user"] = email
        return jsonify({"ok": True, "email": email})
    return jsonify({"ok": False, "error": "Invalid email or password"}), 401


@app.post("/api/logout")
def logout():
    session.clear()
    return jsonify({"ok": True})


@app.get("/api/session")
def session_status():
    if session.get("logged_in"):
        return jsonify({"logged_in": True, "email": session.get("user", "")})
    return jsonify({"logged_in": False})


@app.get("/api/tracks")
def list_tracks():
    user_email = require_email(request.args.get("user_email", ""))
    if user_email is None:
        return json_error("A valid user_email query parameter is required.", 400)
    rows = get_db().execute(
        "SELECT * FROM tracks WHERE user_email = ? ORDER BY sent_at DESC",
        (user_email,),
    ).fetchall()
    return jsonify([serialize(row) for row in rows])


@app.post("/api/tracks")
def create_track():
    data = request.get_json(silent=True) or {}

    user_email = require_email(str(data.get("user_email") or ""))
    if user_email is None:
        return json_error("A valid user_email is required.", 400)

    recipient = str(data.get("recipient") or "").strip()
    if not recipient:
        return json_error("Recipient is required.", 400)

    subject = data.get("subject")
    subject = str(subject).strip() if subject is not None else ""

    raw_sent_at = data.get("sent_at")
    sent_at = parse_iso(raw_sent_at)
    if raw_sent_at and sent_at is None:
        return json_error("sent_at must be an ISO 8601 timestamp.", 400)

    webhook_url = str(data.get("webhook_url") or "").strip()
    provided_track_id = str(data.get("track_id") or "").strip()
    db = get_db()
    attempts = 0
    while True:
        track_id = provided_track_id or new_track_id()
        try:
            db.execute(
                """INSERT INTO tracks
                   (track_id, user_email, recipient, subject, status,
                    sent_at, opened_at, created_at, webhook_url)
                   VALUES (?, ?, ?, ?, 'sent', ?, NULL, ?, ?)""",
                (track_id, user_email, recipient, subject or None,
                 sent_at or utcnow(), utcnow(), webhook_url or None),
            )
            db.commit()
            break
        except sqlite3.IntegrityError:
            if provided_track_id:
                return json_error("A track with this ID already exists.", 409)
            attempts += 1
            if attempts >= 5:
                raise

    row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
    serialized = serialize(row)
    _broadcast_track_event("track_created", serialized)
    return jsonify(serialized), 201


@app.get("/api/tracks/<track_id>")
def get_track(track_id: str):
    row = get_db().execute(
        "SELECT * FROM tracks WHERE track_id = ?", (track_id,)
    ).fetchone()
    if row is None:
        return json_error("Track not found", 404)
    return jsonify(serialize(row))


@app.post("/api/tracks/<track_id>/update")
def update_track(track_id: str):
    """Update a track's recipient and subject (used after compose Send)."""
    data = request.get_json(silent=True) or {}
    db = get_db()
    row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
    if row is None:
        return json_error("Track not found", 404)
    recipient = str(data.get("recipient") or "").strip()
    subject = str(data.get("subject") or "").strip()
    user_email = str(data.get("user_email") or "").strip()
    updates = []
    params = []
    if recipient:
        updates.append("recipient = ?")
        params.append(recipient)
    if subject:
        updates.append("subject = ?")
        params.append(subject)
    if user_email:
        updates.append("user_email = ?")
        params.append(user_email)
    if updates:
        params.append(track_id)
        db.execute(f"UPDATE tracks SET {', '.join(updates)} WHERE track_id = ?", params)
        db.commit()
    row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
    return jsonify(serialize(row))


# ── Shared open-recording logic ──────────────────────────────────────────
TRANSPARENT_SVG = b'<svg xmlns="http://www.w3.org/2000/svg" width="1" height="1"/>'

def _normalize_ua(ua: str) -> str:
    """Normalize a User-Agent string for comparison.
    
    Strips version numbers and minor details so that two sessions
    of the same browser on the same OS compare as equal.
    e.g. 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) ...Chrome/137.0 ...'
      → 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) ...Chrome/ ...'
    """
    import re as _re
    # Remove version numbers after browser name tokens
    normalized = _re.sub(r'(Chrome|Firefox|Safari|Edg|OPR|MSIE|Trident)/[\d.]+', r'\1/', ua)
    # Remove minor version in OS
    normalized = _re.sub(r'Windows NT [\d.]+', 'Windows NT', normalized)
    # Remove specific Safari version
    normalized = _re.sub(r'Version/[\d.]+', 'Version/', normalized)
    return normalized.strip()


def _is_sender_viewing(sender_fingerprint: str) -> bool:
    """Check if the current pixel request matches the sender's fingerprint.
    
    The sender_fingerprint is a normalized User-Agent recorded when
    the track was created.  If the current request's UA matches, it's
    the sender opening their own email — not the recipient.
    """
    if not sender_fingerprint:
        return False
    current_ua = request.headers.get("User-Agent", "")
    if not current_ua:
        return False
    return _normalize_ua(current_ua) == _normalize_ua(sender_fingerprint)


def _mark_track_opened(track_id: str) -> bool:
    """Mark a track as opened if it exists and is still 'sent'.
    
    Uses sender fingerprinting: the sender's User-Agent is recorded
    when the track is created.  If the pixel request matches that
    fingerprint, it's the sender viewing their own email — ignored.
    Only pixel loads from a DIFFERENT fingerprint count as the
    recipient opening the email.
    Returns True if the record was actually updated (first real open)."""
    db = get_db()
    row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
    if not row:
        return False
    if row["status"] == "opened":
        return False  # already recorded
    # ── Sender fingerprint check ──
    # If the pixel request matches the sender's recorded fingerprint,
    # this is the sender opening their own email — don't count it.
    sender_fp = row["sender_fingerprint"] if "sender_fingerprint" in row.keys() else None
    if sender_fp and _is_sender_viewing(sender_fp):
        log.debug("track %s: self-open detected (sender UA match), ignoring", track_id)
        return False
    # ── Fallback: short time guard for tracks without fingerprint ──
    # Tracks created by sync (not extension) have no fingerprint.
    # Use a 30-second guard to skip Gmail prefetch for those.
    if not sender_fp and row["created_at"]:
        try:
            created = datetime.fromisoformat(row["created_at"])
            if created.tzinfo is None:
                created = created.replace(tzinfo=timezone.utc)
            age = (datetime.now(timezone.utc) - created).total_seconds()
            if age < 30:
                return False  # Gmail prefetch
        except Exception:
            pass
    db.execute(
        "UPDATE tracks SET status = 'opened', opened_at = ? WHERE track_id = ?",
        (utcnow(), track_id),
    )
    db.commit()
    row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
    if row:
        _broadcast_track_event("track_opened", serialize(row))
        wh = row["webhook_url"] if "webhook_url" in row.keys() else None
        if wh:
            tracking.fire_webhook(wh, {
                "event": "open",
                "track_id": track_id,
                "metadata": {},
                "user_agent": request.headers.get("User-Agent", ""),
                "user_ip": request.headers.get("X-Forwarded-For", request.remote_addr),
                "timestamp": int(time.time()),
            })
    return True


def _no_cache_headers(response):
    response.headers["Cache-Control"] = "no-store, no-cache, must-revalidate, max-age=0"
    response.headers["Pragma"] = "no-cache"
    response.headers["Access-Control-Allow-Origin"] = "*"
    return response


# Layer 1: Pixel (existing 1×1 GIF)
@app.get("/api/tracks/<track_id>/open")
def record_open(track_id: str):
    _mark_track_opened(track_id)
    resp = Response(TRANSPARENT_GIF, mimetype="image/gif")
    return _no_cache_headers(resp)


# Layer 2: CSS background-image ping (empty GIF — catches img-blocked clients)
@app.get("/api/tracks/<track_id>/open2")
def record_open_css(track_id: str):
    _mark_track_opened(track_id)
    resp = Response(TRANSPARENT_GIF, mimetype="image/gif")
    return _no_cache_headers(resp)


# Layer 3: SVG beacon (tiny transparent SVG — catches CSS-blocked clients)
@app.get("/api/tracks/<track_id>/open3")
def record_open_svg(track_id: str):
    _mark_track_opened(track_id)
    resp = Response(TRANSPARENT_SVG, mimetype="image/svg+xml")
    return _no_cache_headers(resp)


# Layer 4: Font tracker — CSS that loads a tiny custom WOFF2
TINY_WOFF2 = bytes([
    0x77, 0x4F, 0x46, 0x32, 0x00, 0x01, 0x00, 0x00, 0x00, 0x0A,
    0x00, 0x80, 0x00, 0x03, 0x00, 0x70, 0x4F, 0x53, 0x2F, 0x64,
    0x65, 0x66, 0x61, 0x75, 0x6C, 0x74, 0x00, 0x00, 0x00, 0x02,
    0x00, 0x00, 0x00, 0x0A, 0x00, 0x01, 0x00, 0x00, 0x00, 0x18,
    0x00, 0x04, 0x67, 0x6C, 0x79, 0x66, 0x00, 0x00, 0x00, 0x6E,
    0x00, 0x01, 0x00, 0x00, 0x00, 0x20, 0x00, 0x04, 0x68, 0x65,
    0x61, 0x64, 0x00, 0x00, 0x00, 0x0C, 0x00, 0x20, 0x00, 0x10,
    0x00, 0x01, 0x00, 0x00, 0x00, 0x10, 0x00, 0x08, 0x00, 0x00,
    0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x01,
    0x00, 0x00, 0x00, 0x18, 0x00, 0x04, 0x68, 0x68, 0x65, 0x61,
    0x00, 0x00, 0x00, 0x14, 0x00, 0x08, 0x00, 0x00, 0x00, 0x00,
    0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00,
    0x00, 0x02, 0x00, 0x01, 0x00, 0x00, 0x00, 0x20, 0x00, 0x04,
    0x6D, 0x61, 0x70, 0x70, 0x00, 0x00, 0x00, 0x0C, 0x00, 0x08,
    0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01,
    0x00, 0x00, 0x00, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01,
    0x00, 0x00, 0x00, 0x14, 0x00, 0x04, 0x6E, 0x61, 0x6D, 0x65,
    0x00, 0x00, 0x00, 0x0F, 0x00, 0x18, 0x00, 0x01, 0x00, 0x00,
    0x00, 0x0F, 0x00, 0x54, 0x72, 0x61, 0x63, 0x6B, 0x65, 0x72,
    0x20, 0x46, 0x6F, 0x6E, 0x74, 0x00, 0x00, 0x00, 0x00, 0x01,
    0x00, 0x00, 0x00, 0x08, 0x00, 0x04, 0x70, 0x6F, 0x73, 0x74,
    0x00, 0x00, 0x00, 0x06, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01,
    0x00, 0x00, 0x00, 0x10, 0x00, 0x0C, 0x77, 0x47, 0x4C, 0x59,
    0x46, 0x5F, 0x48, 0x45, 0x41, 0x44, 0x45, 0x52, 0x00, 0x00,
])

@app.get("/api/tracks/<track_id>/font.css")
def record_font(track_id: str):
    _mark_track_opened(track_id)
    css = (
        "@font-face{{font-family:'TK';"
        "src:url(/api/tracks/{id}/font.woff2) format('woff2');"
        "font-display:block}}"
    ).format(id=track_id)
    resp = Response(css, mimetype="text/css")
    return _no_cache_headers(resp)


@app.get("/api/tracks/<track_id>/font.woff2")
def serve_font(track_id: str):
    _mark_track_opened(track_id)
    resp = Response(TINY_WOFF2, mimetype="font/woff2")
    return _no_cache_headers(resp)


# ── Click tracking proxy ────────────────────────────────────────────────
@app.get("/api/click/<path_token>/<url_b64>")
def record_click(path_token: str, url_b64: str):
    """Decode the click proxy URL, record the click, and redirect."""
    data, original_url = tracking.decode_click_url(path_token, url_b64)
    if not data or not original_url:
        return json_error("Invalid or tampered tracking link", 400)

    track_id = data.get("tid", "")
    webhook_url = data.get("wh", "")
    metadata = data.get("m", {})

    # Record the click
    db = get_db()
    row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
    if row:
        db.execute(
            """INSERT INTO clicks (track_id, original_url, user_agent, user_ip, clicked_at)
               VALUES (?, ?, ?, ?, ?)""",
            (
                track_id,
                original_url,
                request.headers.get("User-Agent", ""),
                request.headers.get("X-Forwarded-For", request.remote_addr),
                utcnow(),
            ),
        )
        db.execute(
            "UPDATE tracks SET click_count = click_count + 1 WHERE track_id = ?",
            (track_id,),
        )
        db.commit()

        # Broadcast SSE event
        row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
        if row:
            _broadcast_track_event("track_clicked", serialize(row))

        # Fire outbound webhook
        effective_webhook = webhook_url or (row["webhook_url"] if row and "webhook_url" in row.keys() else "")
        if effective_webhook:
            tracking.fire_webhook(effective_webhook, {
                "event": "click",
                "track_id": track_id,
                "original_url": original_url,
                "metadata": metadata,
                "user_agent": request.headers.get("User-Agent", ""),
                "user_ip": request.headers.get("X-Forwarded-For", request.remote_addr),
                "timestamp": int(time.time()),
            })

        # Also fire open webhook if this is first interaction and track has a webhook
        if row and row["status"] == "sent":
            effective_webhook = webhook_url or (row["webhook_url"] if "webhook_url" in row.keys() else "")
            if effective_webhook:
                tracking.fire_webhook(effective_webhook, {
                    "event": "open",
                    "track_id": track_id,
                    "metadata": metadata,
                    "timestamp": int(time.time()),
                })

    return redirect(original_url, code=302)


@app.get("/api/tracks/<track_id>/clicks")
def get_clicks(track_id: str):
    """Return all clicks for a given track."""
    db = get_db()
    row = db.execute("SELECT id FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
    if not row:
        return json_error("Track not found", 404)
    clicks = db.execute(
        "SELECT * FROM clicks WHERE track_id = ? ORDER BY clicked_at DESC",
        (track_id,),
    ).fetchall()
    return jsonify([
        {
            "id": c["id"],
            "track_id": c["track_id"],
            "original_url": c["original_url"],
            "user_agent": c["user_agent"],
            "user_ip": c["user_ip"],
            "clicked_at": c["clicked_at"],
        }
        for c in clicks
    ])


@app.delete("/api/tracks/<track_id>")
def delete_track(track_id: str):
    db = get_db()
    row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
    if row is None:
        return json_error("Track not found", 404)
    db.execute("DELETE FROM tracks WHERE track_id = ?", (track_id,))
    db.commit()
    broadcast.broadcast({"type": "track_deleted", "track_id": track_id})
    return jsonify({"ok": True})


@app.delete("/api/tracks")
def clear_all_tracks():
    user_email = require_email(request.args.get("user_email", ""))
    if user_email is None:
        return json_error("A valid user_email query parameter is required.", 400)
    db = get_db()
    db.execute("DELETE FROM tracks WHERE user_email = ?", (user_email,))
    db.commit()
    broadcast.broadcast({"type": "tracks_cleared", "user_email": user_email})
    return jsonify({"ok": True})


@app.post("/api/tracks/<track_id>/simulate-open")
def simulate_open(track_id: str):
    """Manually mark a track as opened (for testing)."""
    db = get_db()
    result = db.execute(
        """UPDATE tracks
           SET status = 'opened', opened_at = ?
           WHERE track_id = ? AND status = 'sent'""",
        (utcnow(), track_id),
    )
    db.commit()
    if result.rowcount == 0:
        row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
        if row is None:
            return json_error("Track not found", 404)
        # Already opened — still return success
    row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
    serialized = serialize(row)
    _broadcast_track_event("track_opened", serialized)
    return jsonify(serialized)


@app.get("/api/tracking/summary")
def tracking_summary():
    user_email = require_email(request.args.get("user_email", ""))
    if user_email is None:
        return json_error("A valid user_email query parameter is required.", 400)
    rows = get_db().execute(
        "SELECT * FROM tracks WHERE user_email = ?", (user_email,)
    ).fetchall()
    total = len(rows)
    opened = sum(1 for row in rows if row["status"] == "opened")
    latest_open = max(
        (row["opened_at"] for row in rows if row["opened_at"] is not None),
        default=None,
    )
    total_clicks = sum(
        row["click_count"] if "click_count" in row.keys() else 0
        for row in rows
    )
    return jsonify(
        {
            "total": total,
            "opened": opened,
            "pending": total - opened,
            "open_rate": round((opened / total) * 100) if total else 0,
            "latest_open": latest_open,
            "total_clicks": total_clicks,
        }
    )


@app.get("/api/gmail/status")
def gmail_status():
    last_raw = get_setting("gmail_last_sync_epoch")
    last_sync = (
        datetime.fromtimestamp(float(last_raw), tz=timezone.utc).isoformat()
        if last_raw
        else None
    )
    try:
        creds = gmail_client.get_credentials()
        connected = creds is not None
    except Exception as exc:
        log.warning("gmail_status: get_credentials() failed: %s", exc)
        connected = False
    account = get_setting("gmail_account")
    try:
        pixel_base = get_pixel_base_url()
    except Exception as exc:
        log.warning("gmail_status: get_pixel_base_url() failed: %s", exc)
        pixel_base = None
    history_id = get_setting("gmail_last_history_id")
    watch_topic = get_setting("gmail_watch_topic") or gmail_client.WATCH_TOPIC
    # Check if the token has the gmail.modify scope needed for inject-pixel
    needs_reauth = False
    try:
        with open(gmail_client.TOKEN_PATH) as _tf:
            _token_data = json.load(_tf)
        _granted = set(_token_data.get("scopes", []))
        needs_reauth = "https://www.googleapis.com/auth/gmail.modify" not in _granted
    except Exception:
        needs_reauth = True  # assume reauth needed if we can't check
    return jsonify(
        {
            "configured": gmail_client.is_configured(),
            "lib_error": gmail_client.lib_error(),
            "connected": connected,
            "account": account if connected and account else None,
            "last_sync": last_sync if connected else None,
            "last_history_id": history_id,
            "auto_sync": get_setting("gmail_auto_sync", "1") == "1",
            "lookback_days": GMAIL_LOOKBACK_DAYS,
            "pixel_base_url": pixel_base or request.url_root.rstrip("/"),
            "pixel_reachable": pixel_base is not None and "127.0.0.1" not in pixel_base and "localhost" not in pixel_base,
            "watch_topic": watch_topic if watch_topic else None,
            "needs_reauth": needs_reauth,
        }
    )


@app.get("/api/gmail/auth-url")
def gmail_auth_url():
    if not gmail_client.is_configured():
        return json_error(
            "Gmail is not configured. Place google_credentials.json in the app folder.",
            400,
        )
    return jsonify({"url": gmail_client.auth_url()})


@app.post("/api/gmail/watch")
def gmail_watch():
    if gmail_client.get_credentials() is None:
        return json_error("Gmail is not connected.", 401)
    topic = gmail_client.WATCH_TOPIC or get_setting("gmail_watch_topic") or ""
    if not topic:
        return json_error("No Pub/Sub topic configured. Set SIGNALMARK_WATCH_TOPIC.", 400)
    try:
        service = gmail_client.get_service()
        result = gmail_client.setup_watch(service, topic)
        set_setting("gmail_watch_topic", topic)
        return jsonify({"ok": True, "watch": result})
    except Exception as exc:
        return json_error(str(exc), 502)


@app.post("/api/gmail/stop-watch")
def gmail_stop_watch():
    if gmail_client.get_credentials() is None:
        return json_error("Gmail is not connected.", 401)
    try:
        service = gmail_client.get_service()
        gmail_client.stop_watch(service)
        return jsonify({"ok": True})
    except Exception as exc:
        return json_error(str(exc), 502)


@app.post("/api/gmail/webhook")
def gmail_webhook():
    data = request.get_json(silent=True) or {}
    message = data.get("message", {})
    if not message:
        return jsonify({"ok": True})
    try:
        decoded = base64.b64decode(message.get("data", "")).decode("utf-8", errors="replace")
        notif = json.loads(decoded)
        if notif.get("emailAddress"):
            set_setting("gmail_account", notif["emailAddress"])
    except Exception:
        pass
    # Run sync inline — no background threads, keeps process count low.
    with _sync_lock:
        with app.app_context():
            try:
                perform_sync()
            except Exception as exc:
                log.error("Webhook sync failed: %s", exc, exc_info=True)
    return jsonify({"ok": True})


@app.post("/api/gmail/instant-sync")
def gmail_instant_sync():
    """Force an immediate sync — called by the Chrome extension after
    it detects that a tracked email was just sent.
    
    This runs the sync inline (not in a background thread) so the
    response tells the caller whether new tracks were found.
    """
    if gmail_client.get_credentials() is None:
        return json_error("Gmail is not connected.", 401)
    try:
        with _sync_lock:
            result = perform_sync()
        return jsonify(result)
    except Exception as exc:
        log.error("Instant sync failed: %s", exc, exc_info=True)
        return json_error(str(exc), 502)


@app.post("/api/gmail/inject-pixel")
def inject_pixel():
    creds = gmail_client.get_credentials()
    if creds is None:
        return json_error("Gmail is not connected.", 401)
    # Check for gmail.modify scope — required to update already-sent messages
    try:
        with open(gmail_client.TOKEN_PATH) as _tf:
            _token_data = json.load(_tf)
        granted = set(_token_data.get("scopes", []))
    except Exception:
        granted = set()
    required = "https://www.googleapis.com/auth/gmail.modify"
    if required not in granted:
        public_redirect = os.environ.get("GOOGLE_REDIRECT_URI", "https://signalmark.isaack.co.ke/oauth/callback")
        return jsonify({
            "error": "Re-authorization needed",
            "needs_reauth": True,
            "detail": "Your Gmail access needs an update to inject pixels into sent emails.",
            "auth_url": gmail_client.auth_url(redirect_uri=public_redirect),
        }), 403
    data = request.get_json(silent=True) or {}
    gmail_id = str(data.get("gmail_id") or "").strip()
    track_id = str(data.get("track_id") or "").strip()
    if not gmail_id or not track_id:
        return json_error("gmail_id and track_id are required.", 400)
    try:
        service = gmail_client.get_service()
        pixel_base = get_pixel_base_url() or request.url_root.rstrip("/")
        pixel_url = f"{pixel_base}/api/tracks/{track_id}/open"
        # Pass click_base_url for link rewriting
        gmail_client.update_message_with_pixel(
            service, gmail_id, pixel_url,
            click_base_url=pixel_base, track_id=track_id,
        )
        return jsonify({"ok": True})
    except Exception as exc:
        return json_error(str(exc), 502)


log = logging.getLogger(__name__)

if not logging.getLogger().handlers:
    logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(name)s: %(message)s")


@app.get("/oauth/callback")
def oauth_callback():
    error = request.args.get("error")
    code = request.args.get("code")
    if error or not code:
        log.error("OAuth callback error: %s", error or "no_code")
        return redirect(f"/#/settings?gmail=failed&detail={error or 'no_code'}")
    try:
        gmail_client.exchange_code(code)
        service = gmail_client.get_service()
        if service is None:
            log.error("OAuth callback: exchange_code succeeded but get_service() returned None")
            return redirect("/#/settings?gmail=failed&detail=token_invalid")
        email_addr = gmail_client.account_email(service) or ""
        set_setting("gmail_account", email_addr)
        set_setting("gmail_owner", email_addr)
        return redirect("/#/settings?gmail=connected")
    except Exception as exc:
        log.error("OAuth callback failed: %s", exc, exc_info=True)
        return redirect(f"/#/settings?gmail=failed&detail={str(exc)[:120]}")


@app.post("/api/gmail/sync")
def gmail_sync():
    if gmail_client.get_credentials() is None:
        return json_error("Gmail is not connected.", 401)
    try:
        result = perform_sync()
    except Exception as exc:
        return json_error(str(exc), 502)
    return jsonify(result)


@app.post("/api/gmail/disconnect")
def gmail_disconnect():
    gmail_client.disconnect()
    set_setting("gmail_account", "")
    return jsonify({"ok": True})


@app.post("/api/gmail/auto-sync")
def gmail_auto_sync():
    data = request.get_json(silent=True) or {}
    enabled = bool(data.get("enabled", True))
    set_setting("gmail_auto_sync", "1" if enabled else "0")
    return jsonify({"auto_sync": enabled})


@app.post("/api/tracks/backfill-recipients")
def backfill_recipients():
    """Re-read Gmail headers for tracks with unknown recipients and fix them.

    Only touches tracks that have a gmail_id (synced from history) and whose
    recipient looks like a placeholder (unknown, unknown@pending, unknown@unknown).
    """
    if gmail_client.get_credentials() is None:
        return json_error("Gmail is not connected.", 401)

    db = get_db()
    rows = db.execute(
        """SELECT id, track_id, gmail_id, recipient FROM tracks
           WHERE gmail_id IS NOT NULL AND gmail_id != ''
             AND (recipient LIKE '%unknown%' OR recipient LIKE '%pending%')
           ORDER BY id DESC"""
    ).fetchall()

    if not rows:
        return jsonify({"updated": 0, "total": 0})

    service = gmail_client.get_service()
    updated = 0
    errors = 0

    for row in rows:
        gmail_id = row["gmail_id"]
        try:
            meta = gmail_client.get_message_metadata(service, gmail_id)
            new_recipient = meta.get("recipients", [None])[0] if meta.get("recipients") else None
            if new_recipient and new_recipient != row["recipient"]:
                db.execute(
                    "UPDATE tracks SET recipient = ? WHERE id = ?",
                    (new_recipient, row["id"]),
                )
                updated += 1
                log.info("Backfilled track %s: %s -> %s", row["track_id"], row["recipient"], new_recipient)
            # Also fix subject if it was empty and we got a better one
            if meta.get("subject") and meta["subject"] != "(no subject)":
                existing_subject = db.execute(
                    "SELECT subject FROM tracks WHERE id = ?", (row["id"],)
                ).fetchone()["subject"]
                if not existing_subject or existing_subject in ("", "(no subject)"):
                    db.execute(
                        "UPDATE tracks SET subject = ? WHERE id = ?",
                        (meta["subject"], row["id"]),
                    )
        except Exception as exc:
            errors += 1
            log.warning("Backfill failed for gmail_id=%s: %s", gmail_id, exc)

    db.commit()
    return jsonify({"updated": updated, "total": len(rows), "errors": errors})


@app.post("/api/tracks/prepare")
def prepare_track():
    """Create a track record before send and return the pixel URL.
    Deduplicates: if the same user created a track for the same recipient
    within the last 60 seconds, return the existing one.
    """
    data = request.get_json(silent=True) or {}
    recipient = str(data.get("recipient") or "unknown@pending").strip()
    subject = str(data.get("subject") or "").strip()
    user_email = get_setting("gmail_account") or str(data.get("user_email") or "").strip()
    webhook_url = str(data.get("webhook_url") or "").strip()
    sender_fingerprint = str(data.get("sender_fingerprint") or "").strip()

    db = get_db()

    # Dedup: if a recent inject track exists for this recipient+user, return it
    recent = db.execute(
        """SELECT * FROM tracks
           WHERE user_email = ?
             AND recipient = ?
             AND source = 'inject'
             AND created_at > datetime('now', '-60 seconds')
           ORDER BY id DESC LIMIT 1""",
        (user_email, recipient or "unknown@pending"),
    ).fetchone()
    if recent:
        pixel_base = get_pixel_base_url() or request.url_root.rstrip("/")
        return jsonify({
            "track_id": recent["track_id"],
            "pixel_url": f"{pixel_base}/api/tracks/{recent['track_id']}/open",
            "urls": {
                "pixel": f"{pixel_base}/api/tracks/{recent['track_id']}/open",
                "css_ping": f"{pixel_base}/api/tracks/{recent['track_id']}/open2",
                "svg_beacon": f"{pixel_base}/api/tracks/{recent['track_id']}/open3",
                "font_css": f"{pixel_base}/api/tracks/{recent['track_id']}/font.css",
            },
            "duplicate": True,
        }), 200

    track_id = new_track_id()
    db.execute(
        """INSERT INTO tracks
           (track_id, user_email, recipient, subject, status,
            sent_at, opened_at, created_at, source, webhook_url, sender_fingerprint)
           VALUES (?, ?, ?, ?, 'sent', ?, NULL, ?, 'inject', ?, ?)""",
        (track_id, user_email, recipient or "unknown@pending", subject or None,
         utcnow(), utcnow(), webhook_url or None, sender_fingerprint or None),
    )
    db.commit()

    pixel_base = get_pixel_base_url() or request.url_root.rstrip("/")
    pixel_url = f"{pixel_base}/api/tracks/{track_id}/open"
    row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
    if row:
        _broadcast_track_event("track_created", serialize(row))
    return jsonify({
        "track_id": track_id,
        "pixel_url": pixel_url,
        "urls": {
            "pixel": f"{pixel_base}/api/tracks/{track_id}/open",
            "css_ping": f"{pixel_base}/api/tracks/{track_id}/open2",
            "svg_beacon": f"{pixel_base}/api/tracks/{track_id}/open3",
            "font_css": f"{pixel_base}/api/tracks/{track_id}/font.css",
        },
    }), 201


@app.post("/api/send")
def send_tracked_email():
    if gmail_client.get_credentials() is None:
        return json_error("Gmail is not connected.", 401)

    data = request.get_json(silent=True) or {}
    recipient = require_email(str(data.get("recipient") or ""))
    if recipient is None:
        return json_error("A valid recipient email is required.", 400)
    subject = str(data.get("subject") or "").strip()
    text_body = str(data.get("body") or "").strip()
    html_body = str(data.get("html") or "").strip()
    if not text_body and not html_body:
        return json_error("A message body is required.", 400)

    webhook_url = str(data.get("webhook_url") or "").strip()
    sender_fingerprint = str(data.get("sender_fingerprint") or "").strip()
    track_id = new_track_id()
    db = get_db()
    db.execute(
        """INSERT INTO tracks
           (track_id, user_email, recipient, subject, status,
            sent_at, opened_at, created_at, source, webhook_url, sender_fingerprint)
           VALUES (?, ?, ?, ?, 'sent', ?, NULL, ?, 'send', ?, ?)""",
        (
            track_id,
            get_setting("gmail_account") or "",
            recipient,
            subject or None,
            utcnow(),
            utcnow(),
            webhook_url or None,
            sender_fingerprint or None,
        ),
    )
    db.commit()

    if not html_body:
        safe = (
            text_body.replace("&", "&amp;")
            .replace("<", "&lt;")
            .replace(">", "&gt;")
        )
        html_body = "<p>" + safe.replace("\n", "<br>") + "</p>"

    pixel_base = get_pixel_base_url() or request.url_root.rstrip('/')
    pixel_url = f"{pixel_base}/api/tracks/{track_id}/open"
    # Rewrite HTML links to go through click tracking proxy
    html_body = tracking.rewrite_html_links_safe(
        html_body, pixel_base, track_id,
    )
    service = gmail_client.get_service()
    sender = get_setting("gmail_account") or ""
    try:
        sent = gmail_client.send_tracked(
            service, sender, recipient, subject, html_body, text_body, pixel_url
        )
    except Exception as exc:
        db.execute("DELETE FROM tracks WHERE track_id = ?", (track_id,))
        db.commit()
        return json_error(f"Gmail send failed: {exc}", 502)

    db.execute(
        "UPDATE tracks SET gmail_id = ?, thread_id = ? WHERE track_id = ?",
        (sent.get("id"), sent.get("threadId"), track_id),
    )
    db.commit()
    row = db.execute("SELECT * FROM tracks WHERE track_id = ?", (track_id,)).fetchone()
    serialized = serialize(row)
    _broadcast_track_event("track_created", serialized)
    return jsonify(serialized), 201


@app.errorhandler(404)
def not_found(_error):
    if request.path.startswith("/api/"):
        return json_error("Not found", 404)
    return render_template("index.html")


@app.errorhandler(500)
def server_error(_error):
    if request.path.startswith("/api/"):
        return json_error("Something went wrong on our side.", 500)
    return json_error("Something went wrong on our side.", 500)


@app.errorhandler(503)
def service_unavailable(_error):
    return json_error("The server is temporarily busy, try again later!", 503)


@app.after_request
def add_cors_headers(response: Response) -> Response:
    if request.path.startswith("/api/"):
        response.headers.setdefault("Access-Control-Allow-Origin", "*")
        response.headers.setdefault("Access-Control-Allow-Headers", "Content-Type")
        response.headers.setdefault("Access-Control-Allow-Methods", "GET, POST, OPTIONS")
    return response



def _init_once() -> None:
    """Run one-time initialisation (DB schema only).

    No background sync threads — those cause fork() failures in LSAPI.
    Sync is triggered by: webhooks, instant-sync endpoint, manual "Sync now".
    """
    try:
        init_db()
    except Exception as _e:
        print(f"[SIGNALMARK] init_db failed: {_e}", file=sys.stderr, flush=True)
    try:
        migrate_db()
    except Exception as _e:
        print(f"[SIGNALMARK] migrate_db failed: {_e}", file=sys.stderr, flush=True)
    print(f"[SIGNALMARK] Initialised (pid={os.getpid()}, port={os.environ.get('PORT', '5055')})",
          file=sys.stderr, flush=True)


_init_once()

if __name__ == "__main__":
    app.run(
        host=os.environ.get("HOST", "0.0.0.0"),
        port=int(os.environ.get("PORT", "5055")),
        debug=os.environ.get("FLASK_DEBUG", "").lower() in {"1", "true", "yes"},
        threaded=True,
    )