{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# SpecklePy Model Data Analytics\n",
        "\n",
        "This notebook shows how to:\n",
        "\n",
        "1. Authenticate with SpecklePy.\n",
        "2. Resolve IDs from environment input.\n",
        "3. Check dataset availability.\n",
        "4. Download main and EAV datasets.\n",
        "5. Query them with DuckDB.\n",
        "\n",
        "## Scope notes\n",
        "\n",
        "- Current example queries are Revit-oriented (`category`, `proxy.level`).\n",
        "- Use versions published from Speckle Connectors `>3.20`.\n",
        "- Older published versions (from before automatic dataset generation) can fail with availability/download errors.\n",
        "\n",
        "## Setup with `.env`\n",
        "\n",
        "Create a `.env` file next to this notebook:\n",
        "\n",
        "```bash\n",
        "SPECKLE_TOKEN=your_personal_access_token\n",
        "SPECKLE_MODEL_URL=https://app.speckle.systems/projects/your_project_id/models/your_model_id\n",
        "# Optional:\n",
        "# SPECKLE_HOST=https://app.speckle.systems\n",
        "# SPECKLE_VERSION_ID=optional_specific_version\n",
        "```\n",
        "\n",
        "If you need to create a token first, see [Building with PATs](https://docs.speckle.systems/developers/authentication/pats)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install -q specklepy duckdb pandas requests python-dotenv"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import os\n",
        "from pathlib import Path\n",
        "from urllib.parse import urlparse\n",
        "\n",
        "import duckdb\n",
        "import pandas as pd\n",
        "import requests\n",
        "from dotenv import load_dotenv\n",
        "from specklepy.api.client import SpeckleClient\n",
        "\n",
        "load_dotenv()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "HOST = os.getenv(\"SPECKLE_HOST\", \"https://app.speckle.systems\")\n",
        "TOKEN = os.getenv(\"SPECKLE_TOKEN\")\n",
        "MODEL_URL = os.getenv(\"SPECKLE_MODEL_URL\", \"\").strip()\n",
        "VERSION_ID = os.getenv(\"SPECKLE_VERSION_ID\", \"\").strip() or None\n",
        "\n",
        "if not TOKEN:\n",
        "    raise ValueError(\"Set SPECKLE_TOKEN in your .env file.\")\n",
        "if not MODEL_URL:\n",
        "    raise ValueError(\"Set SPECKLE_MODEL_URL in your .env file.\")\n",
        "\n",
        "parsed = urlparse(MODEL_URL)\n",
        "parts = [p for p in parsed.path.split(\"/\") if p]\n",
        "if len(parts) < 4 or parts[0] != \"projects\" or parts[2] != \"models\":\n",
        "    raise ValueError(\"SPECKLE_MODEL_URL must look like /projects/{projectId}/models/{modelRef}\")\n",
        "\n",
        "PROJECT_ID = parts[1]\n",
        "model_ref = parts[3]\n",
        "if \"@\" in model_ref:\n",
        "    MODEL_ID, parsed_version_id = model_ref.split(\"@\", 1)\n",
        "    VERSION_ID = VERSION_ID or parsed_version_id\n",
        "else:\n",
        "    MODEL_ID = model_ref\n",
        "\n",
        "client = SpeckleClient(host=HOST)\n",
        "client.authenticate_with_token(TOKEN)\n",
        "print(f\"Authenticated. project={PROJECT_ID} model={MODEL_ID} version={VERSION_ID or 'latest'}\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "def graphql_post(query: str, variables: dict) -> dict:\n",
        "    response = requests.post(\n",
        "        f\"{HOST}/graphql\",\n",
        "        headers={\"Authorization\": f\"Bearer {TOKEN}\", \"Content-Type\": \"application/json\"},\n",
        "        json={\"query\": query, \"variables\": variables},\n",
        "        timeout=60,\n",
        "    )\n",
        "    response.raise_for_status()\n",
        "    payload = response.json()\n",
        "    if payload.get(\"errors\"):\n",
        "        raise RuntimeError(payload[\"errors\"])\n",
        "    return payload[\"data\"]\n",
        "\n",
        "if not VERSION_ID:\n",
        "    latest_query = \"\"\"\n",
        "    query LatestVersion($projectId: String!, $modelId: String!) {\n",
        "      project(id: $projectId) {\n",
        "        model(id: $modelId) {\n",
        "          versions(limit: 1) {\n",
        "            items {\n",
        "              id\n",
        "            }\n",
        "          }\n",
        "        }\n",
        "      }\n",
        "    }\n",
        "    \"\"\"\n",
        "    latest_data = graphql_post(latest_query, {\"projectId\": PROJECT_ID, \"modelId\": MODEL_ID})\n",
        "    items = latest_data[\"project\"][\"model\"][\"versions\"][\"items\"]\n",
        "    if not items:\n",
        "        raise RuntimeError(\"No versions found for this model.\")\n",
        "    VERSION_ID = items[0][\"id\"]\n",
        "\n",
        "availability_query = \"\"\"\n",
        "query DatasetAvailability($projectId: String!, $modelId: String!, $versionId: String!) {\n",
        "  project(id: $projectId) {\n",
        "    model(id: $modelId) {\n",
        "      version(id: $versionId) {\n",
        "        id\n",
        "        objectKey\n",
        "        packfileSize\n",
        "        referencedObject\n",
        "        createdAt\n",
        "      }\n",
        "    }\n",
        "  }\n",
        "}\n",
        "\"\"\"\n",
        "\n",
        "availability_data = graphql_post(\n",
        "    availability_query,\n",
        "    {\"projectId\": PROJECT_ID, \"modelId\": MODEL_ID, \"versionId\": VERSION_ID},\n",
        ")\n",
        "version = availability_data[\"project\"][\"model\"][\"version\"]\n",
        "if not version:\n",
        "    raise RuntimeError(\"Version not found.\")\n",
        "if not version.get(\"objectKey\"):\n",
        "    raise RuntimeError(\n",
        "        \"Primary dataset not available for this version. \"\n",
        "        \"This often means the version is historical (published before auto-generation).\"\n",
        "    )\n",
        "\n",
        "print(f\"Using version: {VERSION_ID}\")\n",
        "version"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "output_dir = Path(\"packfiles\") / PROJECT_ID / MODEL_ID / VERSION_ID\n",
        "output_dir.mkdir(parents=True, exist_ok=True)\n",
        "\n",
        "main_url = f\"{HOST}/api/v1/projects/{PROJECT_ID}/models/{MODEL_ID}/versions/{VERSION_ID}/download\"\n",
        "eav_url = f\"{HOST}/api/v1/projects/{PROJECT_ID}/models/{MODEL_ID}/versions/{VERSION_ID}/eav/download\"\n",
        "\n",
        "main_path = output_dir / f\"{VERSION_ID}.duckdb\"\n",
        "eav_path = output_dir / f\"{VERSION_ID}.eav.duckdb\"\n",
        "headers = {\"Authorization\": f\"Bearer {TOKEN}\"}\n",
        "\n",
        "def download_file(url: str, target: Path) -> None:\n",
        "    with requests.get(url, headers=headers, stream=True, timeout=300) as response:\n",
        "        response.raise_for_status()\n",
        "        with target.open(\"wb\") as file:\n",
        "            for chunk in response.iter_content(chunk_size=1024 * 1024):\n",
        "                if chunk:\n",
        "                    file.write(chunk)\n",
        "\n",
        "download_file(main_url, main_path)\n",
        "main_path"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "con = duckdb.connect()\n",
        "con.execute(f\"ATTACH '{main_path.as_posix()}' AS main_pf (READ_ONLY)\")\n",
        "\n",
        "summary = con.execute(\n",
        "    \"\"\"\n",
        "    SELECT\n",
        "      (SELECT COUNT(*) FROM main_pf.objects) AS object_count\n",
        "    \"\"\"\n",
        ").fetchdf()\n",
        "\n",
        "summary"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "download_file(eav_url, eav_path)\n",
        "con.execute(f\"ATTACH '{eav_path.as_posix()}' AS eav_pf (READ_ONLY)\")\n",
        "\n",
        "category_counts = con.execute(\n",
        "    \"\"\"\n",
        "    SELECT value_text AS category, COUNT(*) AS count\n",
        "    FROM eav_pf.properties\n",
        "    WHERE path = 'category' AND value_text IS NOT NULL\n",
        "    GROUP BY value_text\n",
        "    ORDER BY count DESC\n",
        "    LIMIT 25\n",
        "    \"\"\"\n",
        ").fetchdf()\n",
        "\n",
        "category_counts"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "level_counts = con.execute(\n",
        "    \"\"\"\n",
        "    SELECT value_text AS level_name, COUNT(*) AS count\n",
        "    FROM eav_pf.properties\n",
        "    WHERE path = 'proxy.level' AND value_text IS NOT NULL\n",
        "    GROUP BY value_text\n",
        "    ORDER BY count DESC\n",
        "    LIMIT 25\n",
        "    \"\"\"\n",
        ").fetchdf()\n",
        "\n",
        "level_counts"
      ],
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}