{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c00-c3249650",
   "metadata": {},
   "source": [
    "# State: turning a stream into a table — practical\n",
    "\n",
    "The pricing service prices each finished session with its site's current tariff. It keeps the\n",
    "tariffs as a table built from the CDC topic, and its own state (open sessions, driver totals) in\n",
    "memory, backed by changelog topics. You change a price while it runs, restart it cleanly, crash\n",
    "it and restore it, and then run it without a changelog.\n",
    "\n",
    "**Lab services:** kafka-1..3, schema-registry, postgres, connect (`docker compose --profile connect up -d --wait`)\n",
    "· **Script:** `module-07/pricing_service.py` · **Lesson:** `modules/07-stream-processing/02-state-stream-to-table.md`"
   ]
  },
  {
   "cell_type": "code",
   "id": "c01-bb2e5d1c",
   "metadata": {},
   "source": [
    "# Setup: find the lab's shared helpers and check the lab is running\n",
    "import sys, pathlib\n",
    "lab_root = next(p for p in [pathlib.Path.cwd(), *pathlib.Path.cwd().parents] if (p / \"common\").is_dir())\n",
    "sys.path.insert(0, str(lab_root))\n",
    "\n",
    "import json, time\n",
    "from collections import defaultdict\n",
    "from common import settings, lab\n",
    "from common.scenario import Brightlane, to_json\n",
    "\n",
    "lab.require_kafka()\n",
    "for name, url in [(\"Schema Registry\", settings.SCHEMA_REGISTRY_URL + \"/subjects\"), (\"Kafka Connect\", settings.CONNECT_URL)]:\n",
    "    lab.require_http(name, url, start_hint=\"docker compose --profile connect up -d --wait\")\n",
    "lab.print_versions()"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c02-e0b3e14f",
   "metadata": {},
   "source": [
    "## Step 1: The tariffs table, from CDC\n",
    "The same flattening connector as in lesson 6.1 keeps the latest tariff per id in a compacted\n",
    "topic."
   ]
  },
  {
   "cell_type": "code",
   "id": "c03-e4910bc2",
   "metadata": {},
   "source": [
    "from confluent_kafka import Consumer, Producer, TopicPartition\n",
    "from confluent_kafka.admin import AdminClient\n",
    "\n",
    "TARIFFS, SESSIONS, APP = \"m07l02_pricing.tariffs\", \"m07l02-sessions\", \"m07l02-pricing\"\n",
    "admin = AdminClient({\"bootstrap.servers\": settings.KAFKA_BOOTSTRAP})\n",
    "lab.remove_connectors(\"m07l02-\")\n",
    "lab.reset_pricing_db()\n",
    "lab.delete_topics(admin, [t for t in admin.list_topics(timeout=10).topics if t.startswith(\"m07l02\")])\n",
    "for f in (lab_root / \".lab-data\").glob(\"m07l02-*.checkpoint.json\"):\n",
    "    f.unlink()\n",
    "admin.delete_consumer_groups([APP, \"m07l02-naive\"])\n",
    "lab.recreate_topic(admin, TARIFFS, partitions=1, config={\"cleanup.policy\": \"compact\"})\n",
    "lab.recreate_topic(admin, SESSIONS, partitions=3)\n",
    "lab.connect_api(\"PUT\", \"/connectors/m07l02-tariffs/config\", {\n",
    "    \"connector.class\": \"io.debezium.connector.postgresql.PostgresConnector\", \"plugin.name\": \"pgoutput\",\n",
    "    \"database.hostname\": \"postgres\", \"database.port\": \"5432\", \"database.dbname\": \"pricing\",\n",
    "    \"database.user\": \"brightlane\", \"database.password\": \"brightlane-lab\",\n",
    "    \"topic.prefix\": \"m07l02_src\", \"table.include.list\": \"public.tariffs\",\n",
    "    \"slot.name\": \"m07l02_tariffs\", \"publication.name\": \"m07l02_tariffs\", \"publication.autocreate.mode\": \"filtered\",\n",
    "    \"time.precision.mode\": \"connect\",\n",
    "    \"transforms\": \"unwrap,route\", \"transforms.unwrap.type\": \"io.debezium.transforms.ExtractNewRecordState\",\n",
    "    \"transforms.route.type\": \"org.apache.kafka.connect.transforms.RegexRouter\",\n",
    "    \"transforms.route.regex\": r\"m07l02_src\\.public\\.tariffs\", \"transforms.route.replacement\": TARIFFS})\n",
    "print(\"tariffs connector:\", lab.wait_connector(\"m07l02-tariffs\")[\"connector\"][\"state\"])\n",
    "peek = Consumer({\"bootstrap.servers\": settings.KAFKA_BOOTSTRAP, \"group.id\": \"m07l02-peek\"})\n",
    "lab.wait_until(lambda: peek.get_watermark_offsets(TopicPartition(TARIFFS, 0), timeout=10)[1] >= 36,\n",
    "               timeout=60, what=\"the 36 tariffs\")\n",
    "peek.close()\n",
    "print(\"tariffs in the table topic: 36\")"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c04-674a93c1",
   "metadata": {},
   "source": [
    "## Step 2: Start the service on the first sessions\n",
    "400 sessions give 800 events, keyed by charger. The first 400 events go in now; some sessions\n",
    "have started but not finished yet."
   ]
  },
  {
   "cell_type": "code",
   "id": "c05-5e9e0230",
   "metadata": {},
   "source": [
    "events = Brightlane().stream(sessions=400, readings=False)\n",
    "producer = Producer(settings.PRODUCER_DEFAULTS)\n",
    "\n",
    "def write(first, last):\n",
    "    for charger_id, event in events[first:last]:\n",
    "        producer.produce(SESSIONS, key=charger_id, value=to_json(event))\n",
    "    producer.flush(10)\n",
    "\n",
    "def run(*extra, app=APP, name=None):\n",
    "    return lab.start_script(\"module-07/pricing_service.py\", \"--app\", app, \"--sessions\", SESSIONS,\n",
    "                            \"--tariffs\", TARIFFS, *extra, name=name or app)\n",
    "\n",
    "def caught_up(app=APP):\n",
    "    return sum(lag for *_, lag in lab.group_lag(admin, app, SESSIONS, 3).values()) == 0\n",
    "\n",
    "write(0, 400)\n",
    "service = run()\n",
    "lab.wait_until(caught_up, timeout=120, interval=1, what=\"the service to catch up\")\n",
    "for line in service.lines():\n",
    "    print(line)"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c06-62c5dc24",
   "metadata": {},
   "source": [
    "## Step 3: A price change while it runs\n",
    "The pricing team raises the price at site-N03 in PostgreSQL. Then the next 150 events arrive."
   ]
  },
  {
   "cell_type": "code",
   "id": "c07-2ee0c891",
   "metadata": {},
   "source": [
    "lab.pg(\"UPDATE tariffs SET price_per_kwh = 0.6500 WHERE tariff_id = 'tar-site-N03-1'\")\n",
    "time.sleep(4)\n",
    "write(400, 550)\n",
    "lab.wait_until(caught_up, timeout=120, interval=1, what=\"the service to catch up\")\n",
    "print(service.lines(\"tariff changed\")[-1])"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c08-82e68f47",
   "metadata": {},
   "source": [
    "## Step 4: A clean restart, then a crash\n",
    "A clean stop writes a checkpoint: the state, and how far the changelogs went. The next start\n",
    "loads it and reads only what the changelogs got since. A crash leaves no checkpoint."
   ]
  },
  {
   "cell_type": "code",
   "id": "c09-6cb36860",
   "metadata": {},
   "source": [
    "lab.stop_script(service)\n",
    "service = run(name=f\"{APP}-2\")\n",
    "lab.wait_until(lambda: service.lines(\"restored\"), timeout=60, what=\"the restore\")\n",
    "print(service.lines(\"restored\")[-1])\n",
    "lab.stop_script(service)\n",
    "\n",
    "service = run(\"--crash-after\", \"20\", name=f\"{APP}-3\")\n",
    "write(550, 700)\n",
    "lab.wait_until(lambda: not service.running, timeout=60, what=\"the crash\")\n",
    "print(service.lines(\"CRASH\")[-1])\n",
    "\n",
    "service = run(name=f\"{APP}-4\")\n",
    "lab.wait_until(lambda: service.lines(\"restored\"), timeout=60, what=\"the restore\")\n",
    "print(service.lines(\"restored\")[-1])\n",
    "write(700, len(events))\n",
    "lab.wait_until(caught_up, timeout=120, interval=1, what=\"the service to catch up\")\n",
    "time.sleep(2)\n",
    "lab.stop_script(service)\n",
    "print(service.lines(\"stopped\")[-1])"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c10-b486870c",
   "metadata": {},
   "source": [
    "## Step 5: Is the state right?\n",
    "Read the committed charges and compare them with the service's driver totals (from the checkpoint\n",
    "its clean stop just wrote)."
   ]
  },
  {
   "cell_type": "code",
   "id": "c11-d1ec8cff",
   "metadata": {},
   "source": [
    "def read_charges(topic):\n",
    "    consumer = Consumer({\"bootstrap.servers\": settings.KAFKA_BOOTSTRAP, \"group.id\": \"m07l02-audit\",\n",
    "                         \"isolation.level\": \"read_committed\", \"enable.auto.commit\": False})\n",
    "    consumer.assign([TopicPartition(topic, p, 0) for p in range(3)])\n",
    "    charges = []\n",
    "    while (msg := consumer.poll(3)) is not None:\n",
    "        if not msg.error():\n",
    "            charges.append(json.loads(msg.value()))\n",
    "    consumer.close()\n",
    "    return charges\n",
    "\n",
    "def check(app):\n",
    "    charges = read_charges(f\"{app}-charges\")\n",
    "    print(f\"charges: {len(charges)} for {len({c['session_id'] for c in charges})} sessions (400 finished)\")\n",
    "    checkpoint = json.loads((lab_root / \".lab-data\" / f\"{app}.checkpoint.json\").read_text())[\"state\"]\n",
    "    summed = defaultdict(float)\n",
    "    for c in charges:\n",
    "        summed[c[\"driver_id\"]] += c[\"amount_eur\"]\n",
    "    wrong = [d for d, t in checkpoint[\"driver-totals\"].items() if abs(t[\"amount_eur\"] - summed[d]) > 0.011]\n",
    "    print(f\"driver totals: {len(checkpoint['driver-totals'])}, different from their charges: {len(wrong)}, \"\n",
    "          f\"open sessions left: {len(checkpoint['open-sessions'])}\")\n",
    "    return charges\n",
    "\n",
    "charges = check(APP)\n",
    "print(\"prices charged at site-N03:\", sorted({c[\"price_per_kwh\"] for c in charges if c[\"site_id\"] == \"site-N03\"}))"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c12-01d53cd7",
   "metadata": {},
   "source": [
    "## Break it: no changelog\n",
    "The same service with `--no-changelog` keeps its state in memory only, like a quick script would.\n",
    "It processes the same events, crashes after 150 sessions, and starts again. Predict first: what\n",
    "happens to the sessions that started before the crash and finish after it?"
   ]
  },
  {
   "cell_type": "code",
   "id": "c13-379a06ad",
   "metadata": {},
   "source": [
    "naive = run(\"--no-changelog\", \"--crash-after\", \"150\", app=\"m07l02-naive\")\n",
    "lab.wait_until(lambda: not naive.running, timeout=60, what=\"the crash\")\n",
    "naive = run(\"--no-changelog\", app=\"m07l02-naive\", name=\"m07l02-naive-2\")\n",
    "lab.wait_until(lambda: caught_up(\"m07l02-naive\"), timeout=120, interval=1, what=\"the naive service to catch up\")\n",
    "lab.stop_script(naive)\n",
    "print(naive.lines(\"restored\")[-1])\n",
    "print(naive.lines(\"stopped\")[-1])\n",
    "print(f\"charges: {len(read_charges('m07l02-naive-charges'))} (400 sessions finished)\")"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c14-0f1950b7",
   "metadata": {},
   "source": [
    "## Clean up\n",
    "Delete the connector, its slot, the topics, groups, checkpoint files and schemas, and reset the\n",
    "database. If anything gets stuck, `docker compose down -v` in the `code/` folder resets the lab."
   ]
  },
  {
   "cell_type": "code",
   "id": "c15-f80edfbb",
   "metadata": {},
   "source": [
    "from confluent_kafka.schema_registry import SchemaRegistryClient\n",
    "\n",
    "lab.remove_connectors(\"m07l02-\")\n",
    "lab.delete_topics(admin, [t for t in admin.list_topics(timeout=10).topics if t.startswith(\"m07l02\")])\n",
    "admin.delete_consumer_groups([APP, \"m07l02-naive\", \"m07l02-audit\", f\"{APP}-tariffs\", \"m07l02-naive-tariffs\"])\n",
    "registry = SchemaRegistryClient({\"url\": settings.SCHEMA_REGISTRY_URL})\n",
    "for subject in [s for s in registry.get_subjects() if s.startswith(\"m07l02\")]:\n",
    "    registry.delete_subject(subject)\n",
    "    registry.delete_subject(subject, permanent=True)\n",
    "for f in (lab_root / \".lab-data\").glob(\"m07l02-*.checkpoint.json\"):\n",
    "    f.unlink()\n",
    "lab.reset_pricing_db()\n",
    "print(\"cleaned up\")"
   ],
   "execution_count": null,
   "outputs": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "pygments_lexer": "ipython3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
