{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a600d7fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json \n",
    "with open('metadata.jsonl', 'r') as f: \n",
    "    json_list = list(f)\n",
    "\n",
    "json_QA = []\n",
    "for json_str in json_list: \n",
    "    json_data = json.loads(json_str)\n",
    "    json_QA.append(json_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fa5d8eb8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================================================\n",
      "Task ID: 0a65cb96-cb6e-4a6a-8aae-c1084f613456\n",
      "Question: During the first week of August 2015, one of the NASA Astronomy Pictures of the Day shows the lights of a city on the horizon. The namesake of this city also has a landmark building in Chicago named after him. What is the name of the architectural firm that designed this landmark building? Give the first name appearing in the name of the firm as of June 2023.\n",
      "Level: 2\n",
      "Final Answer: Holabird\n",
      "Annotator Metadata: \n",
      "  ├── Steps: \n",
      "  │      ├── 1. Use search engine to search for \"NASA Astronomy Pictures of the Day August 2015\".\n",
      "  │      ├── 2. Navigate to the NASA Astronomy Picture of the Day Archive.\n",
      "  │      ├── 3. Open the Astronomy Picture of the Day for 2015 August 1-7.\n",
      "  │      ├── 4. Read the descriptions to check which picture shows the lights of a city on the horizon (2015 August 3) and note the name of the city (Marquette, Michigan, USA).\n",
      "  │      ├── 5. Go to the Wikipedia article for Marquette, Michigan and note that the city was named after Jacques Marquette.\n",
      "  │      ├── 6. Go to the Wikipedia article for Jacques Marquette and note that the Marquette Building in Chicago was named after him.\n",
      "  │      ├── 7. Go to the Wikipedia page for the Marquette Building and verify that it is a Chicago landmark.\n",
      "  │      ├── 8. Read the article and note that it was designed by architects Holabird & Roche.\n",
      "  │      ├── 9. Go to the Wikipedia page for Holabird & Roche.\n",
      "  │      ├── 10. Under \"View history\", select the latest version of the page revised during or before June 2023.\n",
      "  │      ├── 11. Note that the name of the firm is Holabird & Root as of June 2023.\n",
      "  ├── Number of steps: 11\n",
      "  ├── How long did this take?: 15 minutes\n",
      "  ├── Tools:\n",
      "  │      ├── 1. Web browser\n",
      "  │      ├── 2. Search engine\n",
      "  └── Number of tools: 2\n",
      "==================================================\n"
     ]
    }
   ],
   "source": [
    "import random\n",
    "random_samples = random.sample(json_QA, 1)\n",
    "for sample in random_samples:\n",
    "    print(\"=\" * 50)\n",
    "    print(f\"Task ID: {sample['task_id']}\")\n",
    "    print(f\"Question: {sample['Question']}\")\n",
    "    print(f\"Level: {sample['Level']}\")\n",
    "    print(f\"Final Answer: {sample['Final answer']}\")\n",
    "    print(f\"Annotator Metadata: \")\n",
    "    print(f\"  ├── Steps: \")\n",
    "    for step in sample['Annotator Metadata']['Steps'].split('\\n'):\n",
    "        print(f\"  │      ├── {step}\")\n",
    "    print(f\"  ├── Number of steps: {sample['Annotator Metadata']['Number of steps']}\")\n",
    "    print(f\"  ├── How long did this take?: {sample['Annotator Metadata']['How long did this take?']}\")\n",
    "    print(f\"  ├── Tools:\")\n",
    "    for tool in sample['Annotator Metadata']['Tools'].split('\\n'):\n",
    "        print(f\"  │      ├── {tool}\")\n",
    "    print(f\"  └── Number of tools: {sample['Annotator Metadata']['Number of tools']}\")\n",
    "print(\"=\" * 50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "05076516",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/kudo/miniconda3/envs/myenv/lib/python3.11/site-packages/tqdm/auto.py:21: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html\n",
      "  from .autonotebook import tqdm as notebook_tqdm\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "from dotenv import load_dotenv\n",
    "from langchain_huggingface import HuggingFaceEmbeddings\n",
    "from langchain_community.vectorstores import SupabaseVectorStore\n",
    "from supabase.client import Client, create_client\n",
    "from pathlib import Path\n",
    "env_path = Path(\"..\") / \".env\"\n",
    "\n",
    "load_dotenv(dotenv_path=  env_path)\n",
    "embeddings = HuggingFaceEmbeddings(model_name=\"sentence-transformers/all-mpnet-base-v2\") #  dim=768\n",
    "\n",
    "supabase_url = os.environ.get(\"SUPABASE_URL\")\n",
    "supabase_key = os.environ.get(\"SUPABASE_SERVICE_ROLE_KEY\")\n",
    "supabase: Client = create_client(supabase_url, supabase_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a61acb8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "aa1402e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.schema import Document\n",
    "docs = []\n",
    "cnt = 0 \n",
    "for sample in json_QA:\n",
    "    content = f\"Question : {sample['Question']}\\n\\nFinal answer : {sample['Final answer']}\"\n",
    "    doc = {\n",
    "        \"id\" : cnt,\n",
    "        \"content\" : content,\n",
    "        \"metadata\" : {\n",
    "            \"source\" : sample['task_id']\n",
    "        },\n",
    "        \"embedding\" : embeddings.embed_query(content),\n",
    "    }\n",
    "    docs.append(doc)\n",
    "    cnt += 1\n",
    "\n",
    "# upload the documents to the vector database\n",
    "try:\n",
    "    response = (\n",
    "        supabase.table(\"documents2\")\n",
    "        .insert(docs)\n",
    "        .execute()\n",
    "    )\n",
    "except Exception as exception:\n",
    "    print(\"Error inserting data into Supabase:\", exception)\n",
    "\n",
    "# Save the documents (a list of dict) into a csv file, and manually upload it to Supabase\n",
    "# import pandas as pd\n",
    "# df = pd.DataFrame(docs)\n",
    "# df.to_csv('supabase_docs.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9aa7eb5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add items to vector database\n",
    "vector_store = SupabaseVectorStore(\n",
    "    client=supabase,\n",
    "    embedding= embeddings,\n",
    "    table_name=\"documents2\",\n",
    "    query_name=\"match_documents_2\",\n",
    ")\n",
    "retriever = vector_store.as_retriever()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9eecafd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"On June 6, 2023, an article by Carolyn Collins Petersen was published in Universe Today. This article mentions a team that produced a paper about their observations, linked at the bottom of the article. Find this paper. Under what NASA award number was the work performed by R. G. Arendt supported by?\"\n",
    "# matched_docs = vector_store.similarity_search(query, k=2)\n",
    "docs = retriever.invoke(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ff917840",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Document(metadata={'source': '840bfca7-4f7b-481a-8794-c560c340185d'}, page_content='Question : On June 6, 2023, an article by Carolyn Collins Petersen was published in Universe Today. This article mentions a team that produced a paper about their observations, linked at the bottom of the article. Find this paper. Under what NASA award number was the work performed by R. G. Arendt supported by?\\n\\nFinal answer : 80GSFC21M0002')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "docs[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "01c8f337",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "List of tools used in all samples:\n",
      "Total number of tools used: 83\n",
      "  ├── web browser: 107\n",
      "  ├── image recognition tools (to identify and parse a figure with three axes): 1\n",
      "  ├── search engine: 101\n",
      "  ├── calculator: 34\n",
      "  ├── unlambda compiler (optional): 1\n",
      "  ├── a web browser.: 2\n",
      "  ├── a search engine.: 2\n",
      "  ├── a calculator.: 1\n",
      "  ├── microsoft excel: 5\n",
      "  ├── google search: 1\n",
      "  ├── ne: 9\n",
      "  ├── pdf access: 7\n",
      "  ├── file handling: 2\n",
      "  ├── python: 3\n",
      "  ├── image recognition tools: 12\n",
      "  ├── jsonld file access: 1\n",
      "  ├── video parsing: 1\n",
      "  ├── python compiler: 1\n",
      "  ├── video recognition tools: 3\n",
      "  ├── pdf viewer: 7\n",
      "  ├── microsoft excel / google sheets: 3\n",
      "  ├── word document access: 1\n",
      "  ├── tool to extract text from images: 1\n",
      "  ├── a word reversal tool / script: 1\n",
      "  ├── counter: 1\n",
      "  ├── excel: 3\n",
      "  ├── image recognition: 5\n",
      "  ├── color recognition: 3\n",
      "  ├── excel file access: 3\n",
      "  ├── xml file access: 1\n",
      "  ├── access to the internet archive, web.archive.org: 1\n",
      "  ├── text processing/diff tool: 1\n",
      "  ├── gif parsing tools: 1\n",
      "  ├── a web browser: 7\n",
      "  ├── a search engine: 7\n",
      "  ├── a speech-to-text tool: 2\n",
      "  ├── code/data analysis tools: 1\n",
      "  ├── audio capability: 2\n",
      "  ├── pdf reader: 1\n",
      "  ├── markdown: 1\n",
      "  ├── a calculator: 5\n",
      "  ├── access to wikipedia: 3\n",
      "  ├── image recognition/ocr: 3\n",
      "  ├── google translate access: 1\n",
      "  ├── ocr: 4\n",
      "  ├── bass note data: 1\n",
      "  ├── text editor: 1\n",
      "  ├── xlsx file access: 1\n",
      "  ├── powerpoint viewer: 1\n",
      "  ├── csv file access: 1\n",
      "  ├── calculator (or use excel): 1\n",
      "  ├── computer algebra system: 1\n",
      "  ├── video processing software: 1\n",
      "  ├── audio processing software: 1\n",
      "  ├── computer vision: 1\n",
      "  ├── google maps: 1\n",
      "  ├── access to excel files: 1\n",
      "  ├── calculator (or ability to count): 1\n",
      "  ├── a file interface: 3\n",
      "  ├── a python ide: 1\n",
      "  ├── spreadsheet editor: 1\n",
      "  ├── tools required: 1\n",
      "  ├── b browser: 1\n",
      "  ├── image recognition and processing tools: 1\n",
      "  ├── computer vision or ocr: 1\n",
      "  ├── c++ compiler: 1\n",
      "  ├── access to google maps: 1\n",
      "  ├── youtube player: 1\n",
      "  ├── natural language processor: 1\n",
      "  ├── graph interaction tools: 1\n",
      "  ├── bablyonian cuniform -> arabic legend: 1\n",
      "  ├── access to youtube: 1\n",
      "  ├── image search tools: 1\n",
      "  ├── calculator or counting function: 1\n",
      "  ├── a speech-to-text audio processing tool: 1\n",
      "  ├── access to academic journal websites: 1\n",
      "  ├── pdf reader/extracter: 1\n",
      "  ├── rubik's cube model: 1\n",
      "  ├── wikipedia: 1\n",
      "  ├── video capability: 1\n",
      "  ├── image processing tools: 1\n",
      "  ├── age recognition software: 1\n",
      "  ├── youtube: 1\n"
     ]
    }
   ],
   "source": [
    "# list of the tools used in all the samples\n",
    "from collections import Counter, OrderedDict\n",
    "\n",
    "tools = []\n",
    "for sample in json_QA:\n",
    "    for tool in sample['Annotator Metadata']['Tools'].split('\\n'):\n",
    "        tool = tool[2:].strip().lower()\n",
    "        if tool.startswith(\"(\"):\n",
    "            tool = tool[11:].strip()\n",
    "        tools.append(tool)\n",
    "tools_counter = OrderedDict(Counter(tools))\n",
    "print(\"List of tools used in all samples:\")\n",
    "print(\"Total number of tools used:\", len(tools_counter))\n",
    "for tool, count in tools_counter.items():\n",
    "    print(f\"  ├── {tool}: {count}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "myenv",
   "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.11.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}