server.py

#!/usr/bin/env python3
"""Free local SQLite API and static server for the Simplinsured prototype."""

from __future__ import annotations

import argparse
import json
import mimetypes
import os
import secrets
import sqlite3
import time
from datetime import datetime, timezone
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
from pathlib import Path
from urllib.parse import parse_qs, unquote, urlparse


ROOT = Path(__file__).resolve().parent
DB_PATH = ROOT / "data" / "simplinsured.sqlite3"


def utc_now() -> str:
  return datetime.now(timezone.utc).isoformat(timespec="seconds")


def connect() -> sqlite3.Connection:
  DB_PATH.parent.mkdir(parents=True, exist_ok=True)
  db = sqlite3.connect(DB_PATH)
  db.row_factory = sqlite3.Row
  db.execute("PRAGMA journal_mode=WAL")
  db.execute("PRAGMA foreign_keys=ON")
  return db


def init_db() -> None:
  with connect() as db:
    db.executescript(
      """
      CREATE TABLE IF NOT EXISTS accounts (
        email TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        phone TEXT DEFAULT '',
        payload_json TEXT DEFAULT '{}',
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );

      CREATE TABLE IF NOT EXISTS quotes (
        id TEXT PRIMARY KEY,
        email TEXT,
        name TEXT,
        phone TEXT,
        category_id TEXT NOT NULL,
        category_title TEXT NOT NULL,
        quote_type TEXT,
        quote_mode TEXT,
        selected_plan_id TEXT,
        selected_plan_name TEXT,
        status TEXT NOT NULL,
        risk_score REAL DEFAULT 0,
        needs_final_check INTEGER DEFAULT 0,
        estimated_seconds REAL DEFAULT 0,
        actual_ms REAL DEFAULT 0,
        answers_json TEXT NOT NULL,
        recommendations_json TEXT NOT NULL,
        assessment_json TEXT NOT NULL,
        metrics_json TEXT DEFAULT '{}',
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );

      CREATE TABLE IF NOT EXISTS quote_events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        quote_id TEXT,
        event_type TEXT NOT NULL,
        payload_json TEXT DEFAULT '{}',
        created_at TEXT NOT NULL,
        FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE SET NULL
      );

      CREATE TABLE IF NOT EXISTS quote_drafts (
        email TEXT PRIMARY KEY,
        payload_json TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );

      CREATE TABLE IF NOT EXISTS billing_records (
        id TEXT PRIMARY KEY,
        quote_id TEXT,
        email TEXT,
        billing_type TEXT NOT NULL,
        method TEXT NOT NULL,
        amount TEXT,
        status TEXT NOT NULL,
        payment_id TEXT,
        invoice_id TEXT,
        token TEXT NOT NULL,
        masked_reference TEXT NOT NULL,
        payload_json TEXT DEFAULT '{}',
        created_at TEXT NOT NULL,
        FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE SET NULL
      );

      CREATE TABLE IF NOT EXISTS claims (
        id TEXT PRIMARY KEY,
        email TEXT,
        policy TEXT NOT NULL,
        category_id TEXT,
        category_title TEXT NOT NULL,
        incident_date TEXT,
        amount TEXT,
        description TEXT NOT NULL,
        summary TEXT,
        status TEXT NOT NULL,
        documents_json TEXT NOT NULL,
        timeline_json TEXT NOT NULL,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );

      CREATE TABLE IF NOT EXISTS comments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT NOT NULL,
        phone TEXT DEFAULT '',
        comment TEXT NOT NULL,
        created_at TEXT NOT NULL
      );

      CREATE TABLE IF NOT EXISTS auth_codes (
        email TEXT PRIMARY KEY,
        code TEXT NOT NULL,
        expires_at INTEGER NOT NULL,
        created_at TEXT NOT NULL
      );

      CREATE TABLE IF NOT EXISTS test_runs (
        id TEXT PRIMARY KEY,
        agent_version TEXT NOT NULL,
        quote_count INTEGER NOT NULL,
        summary_json TEXT NOT NULL,
        metrics_json TEXT NOT NULL,
        created_at TEXT NOT NULL
      );

      CREATE INDEX IF NOT EXISTS idx_quotes_category ON quotes(category_id);
      CREATE INDEX IF NOT EXISTS idx_quotes_created ON quotes(created_at);
      CREATE INDEX IF NOT EXISTS idx_billing_quote ON billing_records(quote_id);
      CREATE INDEX IF NOT EXISTS idx_claims_email ON claims(email);
      """
    )


def rows_to_dicts(rows: list[sqlite3.Row]) -> list[dict]:
  return [dict(row) for row in rows]


def json_loads(value: str | None, fallback):
  if not value:
    return fallback
  try:
    return json.loads(value)
  except json.JSONDecodeError:
    return fallback


def normalize_quote(row: sqlite3.Row) -> dict:
  quote = dict(row)
  quote["needs_final_check"] = bool(quote["needs_final_check"])
  quote["answers"] = json_loads(quote.pop("answers_json"), {})
  quote["recommendations"] = json_loads(quote.pop("recommendations_json"), [])
  quote["assessment"] = json_loads(quote.pop("assessment_json"), {})
  quote["metrics"] = json_loads(quote.pop("metrics_json"), {})
  return quote


def tokenized_payment(method: str, identifier: str) -> dict:
  clean = str(identifier or "").strip()
  method_key = method.lower().replace(" ", "_")
  token = f"tok_{method_key}_{secrets.token_hex(6)}"
  if method.upper() == "UPI":
    handle = clean.split("@", 1)
    masked = f"{clean[:2]}***@{handle[1] if len(handle) == 2 else 'upi'}"
  elif "card" in method.lower():
    digits = "".join(ch for ch in clean if ch.isdigit())
    masked = f"**** {digits[-4:] if digits else '0000'}"
  else:
    masked = "No payment token collected"
  return {"token": token, "maskedReference": masked}


class SimplinsuredHandler(BaseHTTPRequestHandler):
  server_version = "SimplinsuredSQLite/1.0"

  def log_message(self, fmt: str, *args) -> None:
    print(f"[{self.log_date_time_string()}] {fmt % args}")

  def send_json(self, payload, status: int = 200) -> None:
    body = json.dumps(payload, ensure_ascii=True).encode("utf-8")
    self.send_response(status)
    self.send_header("Content-Type", "application/json; charset=utf-8")
    self.send_header("Content-Length", str(len(body)))
    self.send_header("Access-Control-Allow-Origin", "*")
    self.send_header("Access-Control-Allow-Methods", "GET, POST, PATCH, DELETE, OPTIONS")
    self.send_header("Access-Control-Allow-Headers", "Content-Type")
    self.end_headers()
    self.wfile.write(body)

  def read_json(self) -> dict:
    length = int(self.headers.get("Content-Length", "0") or "0")
    if length <= 0:
      return {}
    raw = self.rfile.read(length).decode("utf-8")
    return json.loads(raw or "{}")

  def do_OPTIONS(self) -> None:
    self.send_response(204)
    self.send_header("Access-Control-Allow-Origin", "*")
    self.send_header("Access-Control-Allow-Methods", "GET, POST, PATCH, DELETE, OPTIONS")
    self.send_header("Access-Control-Allow-Headers", "Content-Type")
    self.end_headers()

  def do_GET(self) -> None:
    parsed = urlparse(self.path)
    if parsed.path.startswith("/api/"):
      self.handle_api_get(parsed)
    else:
      self.serve_static(parsed.path)

  def do_POST(self) -> None:
    parsed = urlparse(self.path)
    if not parsed.path.startswith("/api/"):
      self.send_json({"error": "Not found"}, 404)
      return
    try:
      self.handle_api_post(parsed, self.read_json())
    except json.JSONDecodeError:
      self.send_json({"error": "Invalid JSON"}, 400)

  def do_PATCH(self) -> None:
    parsed = urlparse(self.path)
    if not parsed.path.startswith("/api/"):
      self.send_json({"error": "Not found"}, 404)
      return
    try:
      self.handle_api_patch(parsed, self.read_json())
    except json.JSONDecodeError:
      self.send_json({"error": "Invalid JSON"}, 400)

  def do_DELETE(self) -> None:
    parsed = urlparse(self.path)
    if not parsed.path.startswith("/api/"):
      self.send_json({"error": "Not found"}, 404)
      return
    self.handle_api_delete(parsed)

  def serve_static(self, path: str) -> None:
    clean = unquote(path).lstrip("/") or "index.html"
    target = (ROOT / clean).resolve()
    if ROOT not in target.parents and target != ROOT:
      self.send_error(403)
      return
    if target.is_dir():
      target = target / "index.html"
    if not target.exists():
      self.send_error(404)
      return
    body = target.read_bytes()
    content_type = mimetypes.guess_type(str(target))[0] or "application/octet-stream"
    self.send_response(200)
    self.send_header("Content-Type", content_type)
    self.send_header("Content-Length", str(len(body)))
    self.end_headers()
    self.wfile.write(body)

  def handle_api_get(self, parsed) -> None:
    query = parse_qs(parsed.query)
    with connect() as db:
      if parsed.path == "/api/health":
        counts = {
          "quotes": db.execute("SELECT COUNT(*) FROM quotes").fetchone()[0],
          "claims": db.execute("SELECT COUNT(*) FROM claims").fetchone()[0],
          "billingRecords": db.execute("SELECT COUNT(*) FROM billing_records").fetchone()[0],
          "comments": db.execute("SELECT COUNT(*) FROM comments").fetchone()[0],
        }
        self.send_json({"ok": True, "database": str(DB_PATH), "counts": counts})
        return

      if parsed.path == "/api/quotes":
        limit = min(int(query.get("limit", ["50"])[0]), 500)
        category = query.get("category", [""])[0]
        if category:
          rows = db.execute("SELECT * FROM quotes WHERE category_id=? ORDER BY created_at DESC LIMIT ?", (category, limit)).fetchall()
        else:
          rows = db.execute("SELECT * FROM quotes ORDER BY created_at DESC LIMIT ?", (limit,)).fetchall()
        self.send_json({"quotes": [normalize_quote(row) for row in rows]})
        return

      if parsed.path.startswith("/api/quotes/"):
        quote_id = parsed.path.rsplit("/", 1)[-1]
        row = db.execute("SELECT * FROM quotes WHERE id=?", (quote_id,)).fetchone()
        if not row:
          self.send_json({"error": "Quote not found"}, 404)
          return
        self.send_json({"quote": normalize_quote(row)})
        return

      if parsed.path == "/api/accounts":
        email = query.get("email", [""])[0].lower()
        if not email:
          self.send_json({"error": "email is required"}, 400)
          return
        account = db.execute("SELECT * FROM accounts WHERE email=?", (email,)).fetchone()
        quotes = db.execute("SELECT * FROM quotes WHERE email=? ORDER BY created_at DESC LIMIT 50", (email,)).fetchall()
        claims = db.execute("SELECT * FROM claims WHERE email=? ORDER BY created_at DESC LIMIT 50", (email,)).fetchall()
        billing = db.execute("SELECT * FROM billing_records WHERE email=? ORDER BY created_at DESC LIMIT 50", (email,)).fetchall()
        self.send_json({
          "account": dict(account) if account else None,
          "quotes": [normalize_quote(row) for row in quotes],
          "claims": rows_to_dicts(claims),
          "billing": rows_to_dicts(billing),
        })
        return

      if parsed.path == "/api/metrics/latest":
        row = db.execute("SELECT * FROM test_runs ORDER BY created_at DESC LIMIT 1").fetchone()
        self.send_json({"testRun": dict(row) if row else None})
        return

    self.send_json({"error": "Not found"}, 404)

  def handle_api_post(self, parsed, payload: dict) -> None:
    now = utc_now()
    with connect() as db:
      if parsed.path == "/api/accounts":
        email = str(payload.get("email", "")).lower()
        if not email:
          self.send_json({"error": "email is required"}, 400)
          return
        db.execute(
          """
          INSERT INTO accounts(email, name, phone, payload_json, created_at, updated_at)
          VALUES(?, ?, ?, ?, ?, ?)
          ON CONFLICT(email) DO UPDATE SET
            name=excluded.name,
            phone=excluded.phone,
            payload_json=excluded.payload_json,
            updated_at=excluded.updated_at
          """,
          (
            email,
            payload.get("name") or email,
            payload.get("phone") or "",
            json.dumps(payload),
            now,
            now,
          ),
        )
        self.send_json({"ok": True, "email": email}, 201)
        return

      if parsed.path == "/api/quotes":
        quote_id = payload.get("id") or f"QT-{secrets.token_hex(5).upper()}"
        db.execute(
          """
          INSERT INTO quotes(
            id, email, name, phone, category_id, category_title, quote_type, quote_mode,
            selected_plan_id, selected_plan_name, status, risk_score, needs_final_check,
            estimated_seconds, actual_ms, answers_json, recommendations_json, assessment_json,
            metrics_json, created_at, updated_at
          ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
          ON CONFLICT(id) DO UPDATE SET
            selected_plan_id=excluded.selected_plan_id,
            selected_plan_name=excluded.selected_plan_name,
            status=excluded.status,
            risk_score=excluded.risk_score,
            needs_final_check=excluded.needs_final_check,
            estimated_seconds=excluded.estimated_seconds,
            actual_ms=excluded.actual_ms,
            answers_json=excluded.answers_json,
            recommendations_json=excluded.recommendations_json,
            assessment_json=excluded.assessment_json,
            metrics_json=excluded.metrics_json,
            updated_at=excluded.updated_at
          """,
          (
            quote_id,
            payload.get("email", ""),
            payload.get("name", ""),
            payload.get("phone", ""),
            payload["categoryId"],
            payload.get("categoryTitle", payload["categoryId"]),
            payload.get("quoteType", ""),
            payload.get("quoteMode", "new"),
            payload.get("selectedPlanId", ""),
            payload.get("selectedPlanName", ""),
            payload.get("status", "recommendations_shown"),
            float(payload.get("riskScore", 0) or 0),
            1 if payload.get("needsFinalCheck") else 0,
            float(payload.get("estimatedSeconds", 0) or 0),
            float(payload.get("actualMs", 0) or 0),
            json.dumps(payload.get("answers", {})),
            json.dumps(payload.get("recommendations", [])),
            json.dumps(payload.get("assessment", {})),
            json.dumps(payload.get("metrics", {})),
            payload.get("createdAt", now),
            now,
          ),
        )
        db.execute(
          "INSERT INTO quote_events(quote_id, event_type, payload_json, created_at) VALUES(?, ?, ?, ?)",
          (quote_id, "quote_saved", json.dumps(payload), now),
        )
        self.send_json({"ok": True, "id": quote_id}, 201)
        return

      if parsed.path == "/api/quote-events":
        db.execute(
          "INSERT INTO quote_events(quote_id, event_type, payload_json, created_at) VALUES(?, ?, ?, ?)",
          (payload.get("quoteId"), payload.get("eventType", "event"), json.dumps(payload), now),
        )
        self.send_json({"ok": True}, 201)
        return

      if parsed.path == "/api/quote-drafts":
        email = str(payload.get("email", "")).lower()
        if not email:
          self.send_json({"error": "email is required"}, 400)
          return
        db.execute(
          """
          INSERT INTO quote_drafts(email, payload_json, updated_at)
          VALUES(?, ?, ?)
          ON CONFLICT(email) DO UPDATE SET payload_json=excluded.payload_json, updated_at=excluded.updated_at
          """,
          (email, json.dumps(payload.get("draft", payload)), now),
        )
        self.send_json({"ok": True}, 201)
        return

      if parsed.path == "/api/billing":
        method = payload.get("method", "")
        token = payload.get("tokenized") or tokenized_payment(method, payload.get("identifier", ""))
        billing_id = payload.get("id") or f"BILL-{secrets.token_hex(4).upper()}"
        db.execute(
          """
          INSERT INTO billing_records(
            id, quote_id, email, billing_type, method, amount, status, payment_id,
            invoice_id, token, masked_reference, payload_json, created_at
          ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
          ON CONFLICT(id) DO UPDATE SET
            status=excluded.status,
            token=excluded.token,
            masked_reference=excluded.masked_reference,
            payload_json=excluded.payload_json
          """,
          (
            billing_id,
            payload.get("quoteId", ""),
            payload.get("email", ""),
            payload.get("billingType", ""),
            method,
            payload.get("amount", ""),
            payload.get("status", ""),
            payload.get("paymentId", ""),
            payload.get("invoiceId", ""),
            token.get("token", ""),
            token.get("maskedReference", ""),
            json.dumps(payload),
            now,
          ),
        )
        self.send_json({"ok": True, "id": billing_id, "tokenized": token}, 201)
        return

      if parsed.path == "/api/claims":
        claim_id = payload.get("id") or f"CLM-{secrets.randbelow(9000) + 1000}"
        db.execute(
          """
          INSERT INTO claims(
            id, email, policy, category_id, category_title, incident_date, amount,
            description, summary, status, documents_json, timeline_json, created_at, updated_at
          ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
          ON CONFLICT(id) DO UPDATE SET
            status=excluded.status,
            documents_json=excluded.documents_json,
            timeline_json=excluded.timeline_json,
            updated_at=excluded.updated_at
          """,
          (
            claim_id,
            payload.get("email", ""),
            payload.get("policy", ""),
            payload.get("categoryId", ""),
            payload.get("categoryTitle", ""),
            payload.get("date", ""),
            payload.get("amount", ""),
            payload.get("description", ""),
            payload.get("summary", ""),
            payload.get("status", "Documents requested"),
            json.dumps(payload.get("documents", [])),
            json.dumps(payload.get("timeline", [])),
            now,
            now,
          ),
        )
        self.send_json({"ok": True, "id": claim_id}, 201)
        return

      if parsed.path == "/api/comments":
        if not payload.get("name") or not payload.get("email") or not payload.get("comment"):
          self.send_json({"error": "name, email, and comment are required"}, 400)
          return
        db.execute(
          "INSERT INTO comments(name, email, phone, comment, created_at) VALUES(?, ?, ?, ?, ?)",
          (payload["name"], payload["email"], payload.get("phone", ""), payload["comment"], now),
        )
        self.send_json({"ok": True}, 201)
        return

      if parsed.path == "/api/auth-codes":
        email = str(payload.get("email", "")).lower()
        code = str(payload.get("code", ""))
        expires_at = int(payload.get("expiresAt", 0) or 0)
        if not email or not code or not expires_at:
          self.send_json({"error": "email, code, and expiresAt are required"}, 400)
          return
        db.execute(
          """
          INSERT INTO auth_codes(email, code, expires_at, created_at)
          VALUES(?, ?, ?, ?)
          ON CONFLICT(email) DO UPDATE SET
            code=excluded.code,
            expires_at=excluded.expires_at,
            created_at=excluded.created_at
          """,
          (email, code, expires_at, now),
        )
        self.send_json({"ok": True}, 201)
        return

      if parsed.path == "/api/test-runs":
        run_id = payload.get("id") or f"RUN-{int(time.time())}"
        db.execute(
          "INSERT INTO test_runs(id, agent_version, quote_count, summary_json, metrics_json, created_at) VALUES(?, ?, ?, ?, ?, ?)",
          (
            run_id,
            payload.get("agentVersion", "unknown"),
            int(payload.get("quoteCount", 0) or 0),
            json.dumps(payload.get("summary", {})),
            json.dumps(payload.get("metrics", {})),
            now,
          ),
        )
        self.send_json({"ok": True, "id": run_id}, 201)
        return

    self.send_json({"error": "Not found"}, 404)

  def handle_api_patch(self, parsed, payload: dict) -> None:
    now = utc_now()
    with connect() as db:
      if parsed.path.startswith("/api/quotes/"):
        quote_id = parsed.path.rsplit("/", 1)[-1]
        row = db.execute("SELECT * FROM quotes WHERE id=?", (quote_id,)).fetchone()
        if not row:
          self.send_json({"error": "Quote not found"}, 404)
          return
        db.execute(
          """
          UPDATE quotes
          SET selected_plan_id=?, selected_plan_name=?, status=?, updated_at=?
          WHERE id=?
          """,
          (
            payload.get("selectedPlanId", row["selected_plan_id"]),
            payload.get("selectedPlanName", row["selected_plan_name"]),
            payload.get("status", row["status"]),
            now,
            quote_id,
          ),
        )
        db.execute(
          "INSERT INTO quote_events(quote_id, event_type, payload_json, created_at) VALUES(?, ?, ?, ?)",
          (quote_id, "quote_updated", json.dumps(payload), now),
        )
        self.send_json({"ok": True, "id": quote_id})
        return

    self.send_json({"error": "Not found"}, 404)

  def handle_api_delete(self, parsed) -> None:
    with connect() as db:
      if parsed.path.startswith("/api/quote-drafts/"):
        email = unquote(parsed.path.rsplit("/", 1)[-1]).lower()
        db.execute("DELETE FROM quote_drafts WHERE email=?", (email,))
        self.send_json({"ok": True})
        return
    self.send_json({"error": "Not found"}, 404)


def main() -> None:
  parser = argparse.ArgumentParser(description="Run Simplinsured with a free local SQLite database.")
  parser.add_argument("--host", default="127.0.0.1")
  parser.add_argument("--port", type=int, default=int(os.environ.get("PORT", "8099")))
  args = parser.parse_args()
  init_db()
  server = ThreadingHTTPServer((args.host, args.port), SimplinsuredHandler)
  print(f"Simplinsured running at http://{args.host}:{args.port}")
  print(f"SQLite database: {DB_PATH}")
  server.serve_forever()


if __name__ == "__main__":
  main()