{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Multi-tenant Authentication with API Keys - CLI Usage\n",
    "\n",
    "This notebook demonstrates how to use TimeDB's authentication system using the **CLI commands** with shell syntax.\n",
    "\n",
    "## What we'll cover:\n",
    "1. Setting up the database schema with users table\n",
    "2. Creating tenants and users via CLI\n",
    "3. Using API keys to authenticate requests\n",
    "4. Demonstrating tenant isolation (each user only sees their own data)\n",
    "5. Testing authentication enforcement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import uuid\n",
    "import requests\n",
    "import pandas as pd\n",
    "import time\n",
    "from datetime import datetime, timezone, timedelta\n",
    "\n",
    "# Use TEST_DATABASE_URL for testing (falls back to DATABASE_URL if not set)\n",
    "DATABASE_URL = os.environ.get(\"TEST_DATABASE_URL\") or os.environ.get(\"DATABASE_URL\")\n",
    "if not DATABASE_URL:\n",
    "    raise ValueError(\"Please set TEST_DATABASE_URL or DATABASE_URL environment variable\")\n",
    "\n",
    "# IMPORTANT: Override both TIMEDB_DSN and DATABASE_URL so the API server uses the same database\n",
    "os.environ[\"TIMEDB_DSN\"] = DATABASE_URL\n",
    "os.environ[\"DATABASE_URL\"] = DATABASE_URL\n",
    "\n",
    "# API base URL\n",
    "API_BASE_URL = \"http://127.0.0.1:8000\"\n",
    "\n",
    "print(f\"Database URL configured: {DATABASE_URL[:50]}...\")\n",
    "print(f\"Environment variables set for API server\")\n",
    "print(f\"API URL: {API_BASE_URL}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 1: Setup Database Schema with Users Table\n",
    "\n",
    "First, we'll create the database schema including the users table for authentication using CLI commands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete existing schema (for a clean start)\n",
    "!timedb delete tables --dsn \"{DATABASE_URL}\" -y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create schema with users table\n",
    "!timedb create tables --dsn \"{DATABASE_URL}\" --with-users -y"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 2: Create Tenants and Users\n",
    "\n",
    "Now we'll create two separate tenants, each with their own user. Each user gets a unique API key that is tied to their tenant_id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate two tenant IDs\n",
    "tenant_a_id = str(uuid.uuid4())\n",
    "tenant_b_id = str(uuid.uuid4())\n",
    "\n",
    "print(f\"Tenant A ID: {tenant_a_id}\")\n",
    "print(f\"Tenant B ID: {tenant_b_id}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create User A for Tenant A\n",
    "!timedb users create --dsn \"{DATABASE_URL}\" --tenant-id {tenant_a_id} --email alice@tenant-a.com"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract API key from the output above\n",
    "# You'll need to copy the API key manually from the output above\n",
    "api_key_a = input(\"Enter API Key for User A from output above: \")\n",
    "print(f\"Stored API Key A: {api_key_a}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create User B for Tenant B\n",
    "!timedb users create --dsn \"{DATABASE_URL}\" --tenant-id {tenant_b_id} --email bob@tenant-b.com"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract API key from the output above\n",
    "api_key_b = input(\"Enter API Key for User B from output above: \")\n",
    "print(f\"Stored API Key B: {api_key_b}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List all users to verify\n",
    "!timedb users list --dsn \"{DATABASE_URL}\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 3: Start the API Server\n",
    "\n",
    "Now we start the API server. With the users_table present, authentication will be **required** for all endpoints."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Kill any existing API server first\n",
    "!pkill -f \"uvicorn.*timedb\" || true\n",
    "time.sleep(2)\n",
    "print(\"Killed any existing API server\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start API server in background using CLI\n",
    "!timedb api --host 127.0.0.1 --port 8000 &"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Give the server time to start\n",
    "time.sleep(3)\n",
    "\n",
    "# Verify API is running\n",
    "try:\n",
    "    response = requests.get(f\"{API_BASE_URL}/\")\n",
    "    print(f\"API is running: {response.json()['name']}\")\n",
    "except Exception as e:\n",
    "    print(f\"API check failed: {e}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 4: Test Authentication Enforcement\n",
    "\n",
    "Let's verify that authentication is required when the users_table exists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test request WITHOUT API key - should return 401\n",
    "response = requests.get(f\"{API_BASE_URL}/values\")\n",
    "print(f\"Request without API key:\")\n",
    "print(f\"  Status: {response.status_code}\")\n",
    "print(f\"  Response: {response.json()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test request with INVALID API key - should return 401\n",
    "response = requests.get(\n",
    "    f\"{API_BASE_URL}/values\",\n",
    "    headers={\"X-API-Key\": \"invalid-key-12345\"}\n",
    ")\n",
    "print(f\"Request with invalid API key:\")\n",
    "print(f\"  Status: {response.status_code}\")\n",
    "print(f\"  Response: {response.json()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test request with VALID API key - should return 200\n",
    "response = requests.get(\n",
    "    f\"{API_BASE_URL}/values\",\n",
    "    headers={\"X-API-Key\": api_key_a}\n",
    ")\n",
    "print(f\"Request with valid API key (User A):\")\n",
    "print(f\"  Status: {response.status_code}\")\n",
    "print(f\"  Response: {response.json()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 5: Demonstrate Tenant Isolation\n",
    "\n",
    "Now let's upload data from both users and verify that each user can only see their own tenant's data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# User A uploads temperature data\n",
    "base_time = datetime(2025, 1, 1, 0, 0, tzinfo=timezone.utc)\n",
    "\n",
    "value_rows_a = [\n",
    "    {\"valid_time\": (base_time + timedelta(hours=i)).isoformat(), \n",
    "     \"value_key\": \"temperature_a\", \n",
    "     \"value\": 20.0 + i}\n",
    "    for i in range(6)\n",
    "]\n",
    "\n",
    "response = requests.post(\n",
    "    f\"{API_BASE_URL}/upload\",\n",
    "    json={\n",
    "        \"run_start_time\": datetime.now(timezone.utc).isoformat(),\n",
    "        \"value_rows\": value_rows_a\n",
    "    },\n",
    "    headers={\"X-API-Key\": api_key_a, \"Content-Type\": \"application/json\"}\n",
    ")\n",
    "print(f\"User A upload status: {response.status_code}\")\n",
    "print(f\"Response: {response.json()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# User B uploads humidity data\n",
    "value_rows_b = [\n",
    "    {\"valid_time\": (base_time + timedelta(hours=i)).isoformat(), \n",
    "     \"value_key\": \"humidity_b\", \n",
    "     \"value\": 50.0 + i}\n",
    "    for i in range(6)\n",
    "]\n",
    "\n",
    "response = requests.post(\n",
    "    f\"{API_BASE_URL}/upload\",\n",
    "    json={\n",
    "        \"run_start_time\": datetime.now(timezone.utc).isoformat(),\n",
    "        \"value_rows\": value_rows_b\n",
    "    },\n",
    "    headers={\"X-API-Key\": api_key_b, \"Content-Type\": \"application/json\"}\n",
    ")\n",
    "print(f\"User B upload status: {response.status_code}\")\n",
    "print(f\"Response: {response.json()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# User A reads data - should ONLY see their own data (temperature_a)\n",
    "response = requests.get(\n",
    "    f\"{API_BASE_URL}/values\",\n",
    "    params={\n",
    "        \"start_valid\": base_time.isoformat(),\n",
    "        \"end_valid\": (base_time + timedelta(hours=6)).isoformat(),\n",
    "        \"mode\": \"flat\"\n",
    "    },\n",
    "    headers={\"X-API-Key\": api_key_a}\n",
    ")\n",
    "data_a = response.json()\n",
    "print(f\"User A sees {data_a['count']} records:\")\n",
    "if data_a['count'] > 0:\n",
    "    df_a = pd.DataFrame(data_a['data'])\n",
    "    print(df_a[['valid_time', 'series_key', 'value']])\n",
    "    print(f\"\\nSeries keys visible to User A: {df_a['series_key'].unique().tolist()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# User B reads data - should ONLY see their own data (humidity_b)\n",
    "response = requests.get(\n",
    "    f\"{API_BASE_URL}/values\",\n",
    "    params={\n",
    "        \"start_valid\": base_time.isoformat(),\n",
    "        \"end_valid\": (base_time + timedelta(hours=6)).isoformat(),\n",
    "        \"mode\": \"flat\"\n",
    "    },\n",
    "    headers={\"X-API-Key\": api_key_b}\n",
    ")\n",
    "data_b = response.json()\n",
    "print(f\"User B sees {data_b['count']} records:\")\n",
    "if data_b['count'] > 0:\n",
    "    df_b = pd.DataFrame(data_b['data'])\n",
    "    print(df_b[['valid_time', 'series_key', 'value']])\n",
    "    print(f\"\\nSeries keys visible to User B: {df_b['series_key'].unique().tolist()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify tenant isolation\n",
    "print(\"Tenant Isolation Verification:\")\n",
    "print(f\"  User A (Tenant A) sees: {data_a['count']} records\")\n",
    "print(f\"  User B (Tenant B) sees: {data_b['count']} records\")\n",
    "print(\"\")\n",
    "\n",
    "# Check that they see different data\n",
    "if data_a['count'] > 0 and data_b['count'] > 0:\n",
    "    series_a = set(pd.DataFrame(data_a['data'])['series_key'].unique())\n",
    "    series_b = set(pd.DataFrame(data_b['data'])['series_key'].unique())\n",
    "    overlap = series_a & series_b\n",
    "    \n",
    "    if len(overlap) == 0:\n",
    "        print(\"SUCCESS: No overlapping series between tenants!\")\n",
    "        print(f\"  Tenant A series: {series_a}\")\n",
    "        print(f\"  Tenant B series: {series_b}\")\n",
    "    else:\n",
    "        print(f\"WARNING: Found overlapping series: {overlap}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 6: User Deactivation\n",
    "\n",
    "Let's demonstrate how deactivating a user revokes their API access using CLI commands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Deactivate User B\n",
    "!timedb users deactivate --dsn \"{DATABASE_URL}\" --email bob@tenant-b.com -y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Try to access API with deactivated user's key - should return 401\n",
    "response = requests.get(\n",
    "    f\"{API_BASE_URL}/values\",\n",
    "    headers={\"X-API-Key\": api_key_b}\n",
    ")\n",
    "print(f\"Request with deactivated user's API key:\")\n",
    "print(f\"  Status: {response.status_code}\")\n",
    "print(f\"  Response: {response.json()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reactivate User B\n",
    "!timedb users activate --dsn \"{DATABASE_URL}\" --email bob@tenant-b.com"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify access is restored\n",
    "response = requests.get(\n",
    "    f\"{API_BASE_URL}/values\",\n",
    "    headers={\"X-API-Key\": api_key_b}\n",
    ")\n",
    "print(f\"Request after reactivation:\")\n",
    "print(f\"  Status: {response.status_code}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "This notebook demonstrated TimeDB's multi-tenant authentication system using **CLI commands**.\n",
    "\n",
    "### Key CLI Commands Used:\n",
    "\n",
    "1. **Schema Management**:\n",
    "   - `timedb create tables --dsn <url> --with-users -y` - Create schema with users table\n",
    "   - `timedb delete tables --dsn <url> -y` - Delete schema\n",
    "\n",
    "2. **User Management**:\n",
    "   - `timedb users create --dsn <url> --tenant-id <uuid> --email <email>` - Create user with API key\n",
    "   - `timedb users list --dsn <url>` - List all users\n",
    "   - `timedb users deactivate --dsn <url> --email <email> -y` - Revoke API access\n",
    "   - `timedb users activate --dsn <url> --email <email>` - Restore API access\n",
    "   - `timedb users regenerate-key --dsn <url> --email <email>` - Generate new API key\n",
    "\n",
    "3. **API Server**:\n",
    "   - `timedb api --host <host> --port <port>` - Start API server\n",
    "\n",
    "### Python Variable Injection in Shell Commands:\n",
    "\n",
    "Notice how we used `{variable}` syntax to inject Python variables into shell commands. **Important**: When the variable contains special characters (like URLs with `?` or `&`), you must wrap it in quotes:\n",
    "\n",
    "```python\n",
    "DATABASE_URL = \"postgresql://user:pass@host/db?sslmode=require\"\n",
    "\n",
    "# CORRECT - with quotes around the variable\n",
    "!timedb create tables --dsn \"{DATABASE_URL}\"\n",
    "\n",
    "# WRONG - without quotes, shell will interpret ? and & as special characters\n",
    "!timedb create tables --dsn {DATABASE_URL}\n",
    "```\n",
    "\n",
    "### Authentication Concepts:\n",
    "\n",
    "- When users_table exists, authentication is **required** for all endpoints\n",
    "- Use `X-API-Key` header with all requests\n",
    "- Invalid or missing API keys return 401 Unauthorized\n",
    "- Each user's API key is tied to a tenant_id for data isolation"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.14.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}