{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ca0b3cde",
   "metadata": {},
   "source": [
    "# POL to Binance Netflow\n",
    "\n",
    "This notebook visualizes **cumulative netflow** of native POL on Polygon to/from Binance.\n",
    "\n",
    "**How it works**\n",
    "- Reads `netflow` table to draw a time series of the cumulative netflow.\n",
    "- Optionally shows a simple *live* refresh loop (press the stop button or interrupt kernel to stop).\n",
    "\n",
    "\n",
    "> Keep your indexer running in another terminal while you watch this notebook update."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c13974d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import time\n",
    "import sqlite3\n",
    "from decimal import Decimal\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from IPython.display import display, clear_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef22f569",
   "metadata": {},
   "outputs": [],
   "source": [
    "DB_PATH = \"pol_indexer.sqlite\"\n",
    "POLL_SECONDS = float(os.getenv(\"POLL_SECONDS\", \"2.0\"))  # refresh interval for live chart\n",
    "\n",
    "print(\"DB_PATH =\", DB_PATH)\n",
    "print(\"POLL_SECONDS =\", POLL_SECONDS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea246fed",
   "metadata": {},
   "outputs": [],
   "source": [
    "def _connect(db_path: str):\n",
    "    conn = sqlite3.connect(db_path, timeout=10)\n",
    "    conn.row_factory = sqlite3.Row\n",
    "    return conn\n",
    "\n",
    "def load_netflow(db_path: str) -> pd.DataFrame:\n",
    "    with _connect(db_path) as c:\n",
    "        df = pd.read_sql_query(\n",
    "            \"\"\"\n",
    "            SELECT created_at, cumulative_value\n",
    "            FROM netflow\n",
    "            ORDER BY created_at ASC\n",
    "            \"\"\", c\n",
    "        )\n",
    "    # Convert to numeric & datetime\n",
    "    if not df.empty:\n",
    "        df[\"created_at_dt\"] = pd.to_datetime(df[\"created_at\"], unit=\"s\")\n",
    "        # Convert decimal strings safely to float for plotting\n",
    "        df[\"cumulative_float\"] = df[\"cumulative_value\"].astype(float)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88d0261a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = load_netflow(DB_PATH)\n",
    "if df.empty:\n",
    "    print(\"No netflow data yet. Keep the indexer running; this will populate as Binance touches happen.\")\n",
    "else:\n",
    "    fig = plt.figure(figsize=(8,4))\n",
    "    plt.plot(df[\"created_at_dt\"], df[\"cumulative_float\"])\n",
    "    plt.title(\"Cumulative POL Netflow to Binance (since indexer start)\")\n",
    "    plt.xlabel(\"Time\")\n",
    "    plt.ylabel(\"Cumulative Netflow (POL)\")\n",
    "    plt.grid(True, which=\"both\", axis=\"both\")\n",
    "    display(fig)\n",
    "    plt.close(fig)\n",
    "\n",
    "    latest = df.iloc[-1]\n",
    "    print(\"Latest cumulative:\", latest[\"cumulative_value\"], \"POL at\", latest[\"created_at_dt\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b96adcf1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run this cell to start a live loop. Stop the cell to end.\n",
    "try:\n",
    "    while True:\n",
    "        df = load_netflow(DB_PATH)\n",
    "        clear_output(wait=True)\n",
    "        if df.empty:\n",
    "            print(\"No netflow data yet. Waiting for new rows...\")\n",
    "        else:\n",
    "            fig = plt.figure(figsize=(8,4))\n",
    "            plt.plot(df[\"created_at_dt\"], df[\"cumulative_float\"])\n",
    "            plt.title(\"Cumulative POL Netflow to Binance (LIVE)\")\n",
    "            plt.xlabel(\"Time\")\n",
    "            plt.ylabel(\"Cumulative Netflow (POL)\")\n",
    "            plt.grid(True, which=\"both\", axis=\"both\")\n",
    "            display(fig)\n",
    "            plt.close(fig)\n",
    "\n",
    "            latest = df.iloc[-1]\n",
    "            print(\"Latest cumulative:\", latest[\"cumulative_value\"], \"POL at\", latest[\"created_at_dt\"])\n",
    "            print(\"Rows:\", len(df))\n",
    "        time.sleep(POLL_SECONDS)\n",
    "except KeyboardInterrupt:\n",
    "    print(\"Live loop stopped.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82ebd3ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = load_netflow(DB_PATH)\n",
    "display(df.tail(10))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv (3.13.3)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}