{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c5066026",
   "metadata": {},
   "source": [
    "### Using the TimeDB REST API\n",
    "\n",
    "This notebook demonstrates the REST API for reading and writing time series data:\n",
    "1. Setting up the database and starting the API server\n",
    "2. Creating series via `POST /series`\n",
    "3. Inserting data via `POST /values` (using name+labels or series_id)\n",
    "4. Reading data via `GET /values` (with series filtering and versions mode)\n",
    "5. Listing and filtering series via `GET /series`\n",
    "6. Updating records via `PUT /values`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c951cc83",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ready\n"
     ]
    }
   ],
   "source": [
    "from timedb import TimeDataClient\n",
    "import pandas as pd\n",
    "import requests\n",
    "import json\n",
    "from datetime import datetime, timezone, timedelta\n",
    "\n",
    "td = TimeDataClient()\n",
    "API_BASE_URL = \"http://127.0.0.1:8000\"\n",
    "headers = {\"Content-Type\": \"application/json\"}\n",
    "print(\"Ready\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf1860a3",
   "metadata": {},
   "source": [
    "## Part 1: Setup\n",
    "\n",
    "Create the database schema via SDK (admin task — the API cannot create/delete schemas)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e7d798a4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Creating database schema...\n",
      "✓ Schema created successfully\n"
     ]
    }
   ],
   "source": [
    "# Delete existing schema (optional - only if you want to start fresh)\n",
    "# Uncomment the line below if you want to start with a clean database\n",
    "td.delete()\n",
    "\n",
    "# Create database schema\n",
    "td.create()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd50c230",
   "metadata": {},
   "source": [
    "## Part 2: Start the API Server\n",
    "\n",
    "Start the server before making API calls. In a notebook we run it as a background process."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5a264e58",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✓ API is running\n",
      "  Name: TimeDB API\n",
      "  Version: 0.2.0\n"
     ]
    }
   ],
   "source": [
    "# Start the API server in a separate terminal:\n",
    "# timedb api --host 127.0.0.1 --port 8000\n",
    "\n",
    "# Or using subprocess (for notebook use):\n",
    "import subprocess\n",
    "import time\n",
    "\n",
    "# Kill any existing API server\n",
    "subprocess.run([\"pkill\", \"-f\", \"uvicorn.*timedb\"], capture_output=True)\n",
    "time.sleep(1)\n",
    "\n",
    "# Start API server in background\n",
    "process = subprocess.Popen(\n",
    "    [\"timedb\", \"api\", \"--host\", \"127.0.0.1\", \"--port\", \"8000\"],\n",
    "    stdout=subprocess.DEVNULL,\n",
    "    stderr=subprocess.DEVNULL\n",
    ")\n",
    "time.sleep(3)  # Wait for server to start\n",
    "\n",
    "# Check if API is running\n",
    "try:\n",
    "    response = requests.get(f\"{API_BASE_URL}/\")\n",
    "    print(\"✓ API is running\")\n",
    "    print(f\"  Name: {response.json()['name']}\")\n",
    "    print(f\"  Version: {response.json().get('version', 'unknown')}\")\n",
    "except Exception as e:\n",
    "    print(f\"❌ API not running: {e}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ae14808",
   "metadata": {},
   "source": [
    "## Part 3: Insert Data Using the API\n",
    "\n",
    "Now let's create some sample time series data and insert it using the REST API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "efc22941",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✓ Created series 'wind_speed': 1\n",
      "  Message: Series created successfully\n",
      "✓ Created series 'power_forecast': 2\n",
      "  Message: Series created successfully\n",
      "\n",
      "✓ Created 2 time series\n"
     ]
    }
   ],
   "source": [
    "# First, create the time series using the /series endpoint\n",
    "# Use overlapping=true so we can demonstrate updates later\n",
    "# (updates only work on overlapping, not flat)\n",
    "series_configs = [\n",
    "    {\n",
    "        \"name\": \"wind_speed\",\n",
    "        \"description\": \"Wind speed measurements\",\n",
    "        \"unit\": \"m/s\",\n",
    "        \"labels\": {\"site\": \"Gotland\", \"type\": \"measurement\"},\n",
    "        \"overlapping\": False\n",
    "    },\n",
    "    {\n",
    "        \"name\": \"power_forecast\",\n",
    "        \"description\": \"Forecasted power values with overlapping revisions\",\n",
    "        \"unit\": \"MW\",\n",
    "        \"labels\": {\"model\": \"linear\", \"site\": \"Gotland\", \"type\": \"forecast\"},\n",
    "        \"overlapping\": True\n",
    "    }\n",
    "]\n",
    "\n",
    "created_series = {}\n",
    "for series_info in series_configs:\n",
    "    response = requests.post(\n",
    "        f\"{API_BASE_URL}/series\",\n",
    "        json=series_info,\n",
    "        headers=headers\n",
    "    )\n",
    "    response.raise_for_status()\n",
    "    result = response.json()\n",
    "    series_name = series_info[\"name\"]\n",
    "    created_series[series_name] = result[\"series_id\"]\n",
    "    print(f\"✓ Created series '{series_name}': {result['series_id']}\")\n",
    "    print(f\"  Message: {result['message']}\")\n",
    "\n",
    "print(f\"\\n✓ Created {len(created_series)} time series\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "prepare_data",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Prepared 24 wind_speed data points\n",
      "Prepared 24 power_forecast data points\n",
      "Time range: 2025-01-01 00:00:00+00:00 to 2025-01-01 23:00:00+00:00\n"
     ]
    }
   ],
   "source": [
    "# Create sample time series data\n",
    "base_time = datetime(2025, 1, 1, 0, 0, tzinfo=timezone.utc)\n",
    "dates = [base_time + timedelta(hours=i) for i in range(24)]\n",
    "\n",
    "# Prepare data for wind_speed (24 data points)\n",
    "wind_speed_data = [\n",
    "    {\"valid_time\": date.isoformat(), \"value\": 20.0 + i * 0.3}\n",
    "    for i, date in enumerate(dates)\n",
    "]\n",
    "\n",
    "# Prepare data for power_forecast (24 data points)\n",
    "power_forecast_data = [\n",
    "    {\"valid_time\": date.isoformat(), \"value\": 60.0 - i * 0.5}\n",
    "    for i, date in enumerate(dates)\n",
    "]\n",
    "\n",
    "print(f\"Prepared {len(wind_speed_data)} wind_speed data points\")\n",
    "print(f\"Prepared {len(power_forecast_data)} power_forecast data points\")\n",
    "print(f\"Time range: {dates[0]} to {dates[-1]}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d68fad34",
   "metadata": {},
   "source": [
    "### 3.1: Insert Data via POST /values\n",
    "\n",
    "The new `POST /values` endpoint resolves series by name+labels (like the SDK).\n",
    "No need to manage batch_start_time or value_key — just provide `name`, `labels`, and `data`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fab8523c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✓ Wind Speed: batch_id=None, series_id=1, rows=24\n",
      "✓ Power Forecast: batch_id=1, series_id=2, rows=24\n"
     ]
    }
   ],
   "source": [
    "# Insert wind_speed data via POST /values (using name + labels)\n",
    "response = requests.post(\n",
    "    f\"{API_BASE_URL}/values\",\n",
    "    json={\n",
    "        \"name\": \"wind_speed\",\n",
    "        \"labels\": {\"site\": \"Gotland\", \"type\": \"measurement\"},\n",
    "        \"data\": wind_speed_data,\n",
    "    },\n",
    "    headers=headers,\n",
    ")\n",
    "response.raise_for_status()\n",
    "result = response.json()\n",
    "print(f\"✓ Wind Speed: batch_id={result['batch_id']}, series_id={result['series_id']}, rows={result['rows_inserted']}\")\n",
    "\n",
    "# Insert power_forecast data (using series_id directly)\n",
    "response = requests.post(\n",
    "    f\"{API_BASE_URL}/values\",\n",
    "    json={\n",
    "        \"series_id\": created_series[\"power_forecast\"],\n",
    "        \"data\": power_forecast_data,\n",
    "    },\n",
    "    headers=headers,\n",
    ")\n",
    "response.raise_for_status()\n",
    "result = response.json()\n",
    "print(f\"✓ Power Forecast: batch_id={result['batch_id']}, series_id={result['series_id']}, rows={result['rows_inserted']}\")\n",
    "\n",
    "# Store for later use\n",
    "batch_id = result['batch_id']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5fc21dd0",
   "metadata": {},
   "source": [
    "### 3.2: List and Filter Series via GET /series\n",
    "\n",
    "The `GET /series` endpoint supports filtering by name, labels, unit, and series_id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "09f69fd7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✓ Found 2 time series\n",
      "\n",
      "  series_id=2: power_forecast (MW) labels={'site': 'Gotland', 'type': 'forecast', 'model': 'linear'} overlapping=True\n",
      "  series_id=1: wind_speed (m/s) labels={'site': 'Gotland', 'type': 'measurement'} overlapping=False\n",
      "\n",
      "Filter by name='wind_speed':\n",
      "  series_id=1: wind_speed labels={'site': 'Gotland', 'type': 'measurement'}\n",
      "\n",
      "Unique 'site' values:\n",
      "  {'label_key': 'site', 'values': ['Gotland']}\n",
      "\n",
      "Total series count: 2\n"
     ]
    }
   ],
   "source": [
    "# List all series\n",
    "response = requests.get(f\"{API_BASE_URL}/series\", headers=headers)\n",
    "response.raise_for_status()\n",
    "all_series = response.json()\n",
    "print(f\"✓ Found {len(all_series)} time series\\n\")\n",
    "for s in all_series:\n",
    "    print(f\"  series_id={s['series_id']}: {s['name']} ({s['unit']}) labels={s['labels']} overlapping={s['overlapping']}\")\n",
    "\n",
    "# Filter by name\n",
    "print(\"\\nFilter by name='wind_speed':\")\n",
    "response = requests.get(f\"{API_BASE_URL}/series\", params={\"name\": \"wind_speed\"}, headers=headers)\n",
    "filtered = response.json()\n",
    "for s in filtered:\n",
    "    print(f\"  series_id={s['series_id']}: {s['name']} labels={s['labels']}\")\n",
    "\n",
    "# List unique label values\n",
    "print(\"\\nUnique 'site' values:\")\n",
    "response = requests.get(f\"{API_BASE_URL}/series/labels\", params={\"label_key\": \"site\"}, headers=headers)\n",
    "print(f\"  {response.json()}\")\n",
    "\n",
    "# Count series\n",
    "response = requests.get(f\"{API_BASE_URL}/series/count\", headers=headers)\n",
    "print(f\"\\nTotal series count: {response.json()['count']}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee28ad3c",
   "metadata": {},
   "source": [
    "## Part 4: Read Data Using the API\n",
    "\n",
    "Let's read the time series data we just inserted using the API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "06ea2260",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✓ Retrieved 24 wind_speed records via API\n",
      "\n",
      "First few rows:\n",
      "                 valid_time  value\n",
      "0 2025-01-01 00:00:00+00:00   20.0\n",
      "1 2025-01-01 01:00:00+00:00   20.3\n",
      "2 2025-01-01 02:00:00+00:00   20.6\n",
      "3 2025-01-01 03:00:00+00:00   20.9\n",
      "4 2025-01-01 04:00:00+00:00   21.2\n",
      "5 2025-01-01 05:00:00+00:00   21.5\n",
      "\n",
      "DataFrame shape: (24, 2)\n",
      "Columns: ['valid_time', 'value']\n"
     ]
    }
   ],
   "source": [
    "# Read data via API — filter by series name\n",
    "params = {\n",
    "    \"name\": \"wind_speed\",\n",
    "    \"labels\": json.dumps({\"site\": \"Gotland\", \"type\": \"measurement\"}),\n",
    "    \"start_valid\": base_time.isoformat(),\n",
    "    \"end_valid\": (base_time + timedelta(hours=24)).isoformat(),\n",
    "}\n",
    "\n",
    "response = requests.get(f\"{API_BASE_URL}/values\", params=params, headers=headers)\n",
    "response.raise_for_status()\n",
    "\n",
    "data = response.json()\n",
    "print(f\"✓ Retrieved {data['count']} wind_speed records via API\")\n",
    "\n",
    "if data['count'] > 0:\n",
    "    df_api = pd.DataFrame(data['data'])\n",
    "    df_api['valid_time'] = pd.to_datetime(df_api['valid_time'])\n",
    "    print(\"\\nFirst few rows:\")\n",
    "    print(df_api.head(6))\n",
    "    print(f\"\\nDataFrame shape: {df_api.shape}\")\n",
    "    print(f\"Columns: {list(df_api.columns)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0bee3c9",
   "metadata": {},
   "source": [
    "### 4.1: Read with versions=true\n",
    "\n",
    "Set `versions=true` to get all forecast revisions with their known_time (useful for backtesting):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "61fd74b6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✓ Retrieved 6 records with versions=true\n",
      "\n",
      "First few rows (showing forecast revisions):\n",
      "                        known_time                valid_time  value\n",
      "0 2026-02-15 21:48:32.651934+00:00 2025-01-01 00:00:00+00:00   60.0\n",
      "1 2026-02-15 21:48:32.651934+00:00 2025-01-01 01:00:00+00:00   59.5\n",
      "2 2026-02-15 21:48:32.651934+00:00 2025-01-01 02:00:00+00:00   59.0\n",
      "3 2026-02-15 21:48:32.651934+00:00 2025-01-01 03:00:00+00:00   58.5\n",
      "4 2026-02-15 21:48:32.651934+00:00 2025-01-01 04:00:00+00:00   58.0\n",
      "5 2026-02-15 21:48:32.651934+00:00 2025-01-01 05:00:00+00:00   57.5\n"
     ]
    }
   ],
   "source": [
    "# Read with versions=true to see all forecast revisions\n",
    "params_versions = {\n",
    "    \"name\": \"power_forecast\",\n",
    "    \"start_valid\": base_time.isoformat(),\n",
    "    \"end_valid\": (base_time + timedelta(hours=6)).isoformat(),\n",
    "    \"versions\": \"true\",\n",
    "}\n",
    "\n",
    "response = requests.get(f\"{API_BASE_URL}/values\", params=params_versions, headers=headers)\n",
    "response.raise_for_status()\n",
    "\n",
    "data_versions = response.json()\n",
    "print(f\"✓ Retrieved {data_versions['count']} records with versions=true\")\n",
    "\n",
    "if data_versions['count'] > 0:\n",
    "    df_versions = pd.DataFrame(data_versions['data'])\n",
    "    df_versions['valid_time'] = pd.to_datetime(df_versions['valid_time'])\n",
    "    if 'known_time' in df_versions.columns:\n",
    "        df_versions['known_time'] = pd.to_datetime(df_versions['known_time'])\n",
    "    print(\"\\nFirst few rows (showing forecast revisions):\")\n",
    "    print(df_versions.head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bdc9501",
   "metadata": {},
   "source": [
    "## Part 5: Update Records Using the API\n",
    "\n",
    "Updates create a new version with a new `known_time` while preserving the original for audit trail.\n",
    "\n",
    "**Series identification**: use `name` + `labels` (like inserts) or `series_id`.\n",
    "\n",
    "**Three lookup methods for overlapping series** (mirroring the SDK):\n",
    "1. **`batch_id` + `valid_time`** — target a specific batch\n",
    "2. **`known_time` + `valid_time`** — target an exact version (known_time uniquely links to a batch)\n",
    "3. **Just `valid_time`** — target the latest version overall (most convenient)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "140136e2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Known time from original insert: 2026-02-15T21:48:32.651934+00:00\n",
      "\n",
      "\n",
      "Verified updated values:\n",
      "  2025-01-01T00:00:00+00:00: value=75.5\n",
      "  2025-01-01T01:00:00+00:00: value=80.0\n",
      "  2025-01-01T02:00:00+00:00: value=90.0\n"
     ]
    }
   ],
   "source": [
    "forecast_labels = {\"model\": \"linear\", \"site\": \"Gotland\", \"type\": \"forecast\"}\n",
    "\n",
    "# First, get the known_time from the inserted data (we'll need it for Method 2)\n",
    "params_versions = {\n",
    "    \"name\": \"power_forecast\",\n",
    "    \"labels\": json.dumps(forecast_labels),\n",
    "    \"start_valid\": base_time.isoformat(),\n",
    "    \"end_valid\": (base_time + timedelta(hours=1)).isoformat(),\n",
    "    \"versions\": \"true\",\n",
    "}\n",
    "response = requests.get(f\"{API_BASE_URL}/values\", params=params_versions, headers=headers)\n",
    "response.raise_for_status()\n",
    "known_time_from_insert = response.json()[\"data\"][0][\"known_time\"]\n",
    "print(f\"Known time from original insert: {known_time_from_insert}\\n\")\n",
    "\n",
    "# ── Method 1: Update by batch_id ────────────────────────────────────────────\n",
    "# Target a specific batch using batch_id (stored from the insert step)\n",
    "update_1 = {\n",
    "    \"updates\": [{\n",
    "        \"series_id\": created_series[\"power_forecast\"],\n",
    "        \"batch_id\": batch_id,\n",
    "        \"valid_time\": base_time.isoformat(),\n",
    "        \"value\": 75.5,\n",
    "        \"annotation\": \"Corrected via batch_id lookup\",\n",
    "        \"changed_by\": \"analyst@example.com\",\n",
    "    }]\n",
    "}\n",
    "response = requests.put(f\"{API_BASE_URL}/values\", json=update_1, headers=headers)\n",
    "response.raise_for_status()\n",
    "r1 = response.json()\n",
    "\n",
    "# ── Method 2: Update by known_time ──────────────────────────────────────────\n",
    "# Target an exact version using known_time (uniquely identifies a batch)\n",
    "# Here we use name+labels instead of series_id\n",
    "update_2 = {\n",
    "    \"updates\": [{\n",
    "        \"name\": \"power_forecast\",\n",
    "        \"labels\": forecast_labels,\n",
    "        \"known_time\": known_time_from_insert,\n",
    "        \"valid_time\": (base_time + timedelta(hours=1)).isoformat(),\n",
    "        \"value\": 80.0,\n",
    "        \"annotation\": \"Corrected via known_time lookup\",\n",
    "        \"changed_by\": \"analyst@example.com\",\n",
    "    }]\n",
    "}\n",
    "response = requests.put(f\"{API_BASE_URL}/values\", json=update_2, headers=headers)\n",
    "response.raise_for_status()\n",
    "r2 = response.json()\n",
    "\n",
    "# ── Method 3: Update latest version (just valid_time) ───────────────────────\n",
    "# Most convenient — targets the latest version overall, no batch_id or known_time needed\n",
    "update_3 = {\n",
    "    \"updates\": [{\n",
    "        \"name\": \"power_forecast\",\n",
    "        \"labels\": forecast_labels,\n",
    "        \"valid_time\": (base_time + timedelta(hours=2)).isoformat(),\n",
    "        \"value\": 90.0,\n",
    "        \"annotation\": \"Corrected via latest lookup (most convenient!)\",\n",
    "        \"tags\": [\"reviewed\"],\n",
    "        \"changed_by\": \"analyst@example.com\",\n",
    "    }]\n",
    "}\n",
    "response = requests.put(f\"{API_BASE_URL}/values\", json=update_3, headers=headers)\n",
    "response.raise_for_status()\n",
    "r3 = response.json()\n",
    "\n",
    "# ── Verify the updates ──────────────────────────────────────────────────────\n",
    "params_verify = {\n",
    "    \"name\": \"power_forecast\",\n",
    "    \"start_valid\": base_time.isoformat(),\n",
    "    \"end_valid\": (base_time + timedelta(hours=3)).isoformat(),\n",
    "}\n",
    "response = requests.get(f\"{API_BASE_URL}/values\", params=params_verify, headers=headers)\n",
    "response.raise_for_status()\n",
    "data_verify = response.json()\n",
    "\n",
    "print(f\"\\nVerified updated values:\")\n",
    "for row in data_verify['data']:\n",
    "    print(f\"  {row['valid_time']}: value={row['value']}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d1686e2",
   "metadata": {},
   "source": [
    "### Summary\n",
    "\n",
    "**Key Endpoints:**\n",
    "- `POST /series` — create a time series (with name, unit, labels, overlapping)\n",
    "- `POST /values` — insert data (specify series by name+labels or series_id)\n",
    "- `GET /values` — read values (filter by name, labels, series_id, time range; `versions=true` for all revisions)\n",
    "- `PUT /values` — update existing records (creates new version for overlapping series)\n",
    "- `GET /series` — list/filter series by name, labels, unit\n",
    "- `GET /series/labels` — list unique label values\n",
    "- `GET /series/count` — count matching series\n",
    "\n",
    "**Starting the server:**\n",
    "```bash\n",
    "timedb api --host 127.0.0.1 --port 8000\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "timedb",
   "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.14.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}