{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 이 문서를 수정할 당신에게...\n",
    "#### 현재 상황은 아래와 같습니다.\n",
    "1. 아래 4가지의 도구를 사용하고 있습니다.\n",
    "- 필터 query로 데이터베이스 정보 추출(추천을 위함): chroma를 활용해 수집된 wine database에 필터를 적용하여 특정 와인만 뽑아올 수 있습니다.    \n",
    "    - input: {body:3, price:100000~120000}\n",
    "    - output: [Document(page_content='포지오 데이 젤시', metadata={'price': '30000', ...}),]\n",
    "- 자연어 query로 데이터베이스 정보 추출(추천을 위함): DocArrayInMemorySearch을 활용해 wine database에 자연어로 특정 데이터를 뽑아 올 수 있습니다.\n",
    "    - input: \"프랑스에서 만들어진 와인은?\"\n",
    "- 특정 URL query로 웹 페이지에서 정보 추출(와인 구매링크에서 정보를 가져올 때 사용): SeleniumURLLoader를 이용해 특정 url로 부터 웹페이지 정보를 얻을 수 있습니다.\n",
    "- 자연어 구글 검색으로 웹 페이지에서 정보 추출(Q&A처럼 그 외 검색이 필요한 경우 사용): SerpAPI를 이용해 구글에서 검색 결과를 가져올 수 있습니다.\n",
    "\n",
    "#### 당신의 목표는 아래와 같습니다.\n",
    "1. 다양한 도구를 사용하거나 알고리즘을 탐구하여 데이터베이스를 연결할 수 있는 좋은 방식을 찾습니다. (예를 들면 와인바의 상세정보를 검색하기 위한 카카오 위치 정보 API등을 추가할 수 있을 듯 합니다.)\n",
    "2. 도구를 사용할 때는 적절한 format을 사용하는 것이 성능에 영향을 줍니다. 각 도구들에 대한 적절한 input format을 찾습니다.\n",
    "\n",
    "\n",
    "#### 참고사항\n",
    "현재 말이 너무 모호하게 써있고, 도구의 사용처가 확실하지 않습니다. 이를 구체화하는 과정이 필요하기도 합니다."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-------------------------------------------------------------------------------"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### API 키 불러오기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import configparser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['./secrets.ini']"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read('./secrets.ini')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "openai_api_key = config['OPENAI']['OPENAI_API_KEY']\n",
    "serper_api_key = config['SERPER']['SERPER_API_KEY']\n",
    "serp_api_key = config['SERPAPI']['SERPAPI_API_KEY']\n",
    "os.environ.update({'OPENAI_API_KEY': openai_api_key})\n",
    "os.environ.update({'SERPER_API_KEY': serper_api_key})\n",
    "os.environ.update({'SERPAPI_API_KEY': serp_api_key})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from typing import List, Union\n",
    "import re\n",
    "import json\n",
    "\n",
    "import pandas as pd\n",
    "from langchain import SerpAPIWrapper, LLMChain\n",
    "from langchain.agents import Tool, AgentType, AgentExecutor, LLMSingleActionAgent, AgentOutputParser\n",
    "from langchain.chat_models import ChatOpenAI\n",
    "from langchain.chains import LLMChain, SimpleSequentialChain\n",
    "from langchain.chains.query_constructor.base import AttributeInfo\n",
    "from langchain.document_loaders import DataFrameLoader, SeleniumURLLoader\n",
    "from langchain.embeddings import OpenAIEmbeddings\n",
    "from langchain.indexes import VectorstoreIndexCreator\n",
    "from langchain.prompts import PromptTemplate, StringPromptTemplate, load_prompt, BaseChatPromptTemplate\n",
    "from langchain.llms import OpenAI\n",
    "from langchain.retrievers.self_query.base import SelfQueryRetriever\n",
    "from langchain.schema import AgentAction, AgentFinish, HumanMessage\n",
    "from langchain.vectorstores import DocArrayInMemorySearch, Chroma"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load wine database json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_json('./data/unified_wine_data.json', encoding='utf-8', lines=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>url</th>\n",
       "      <th>site_name</th>\n",
       "      <th>price</th>\n",
       "      <th>name</th>\n",
       "      <th>en_name</th>\n",
       "      <th>img_url</th>\n",
       "      <th>body</th>\n",
       "      <th>acidity</th>\n",
       "      <th>tannin</th>\n",
       "      <th>sweetness</th>\n",
       "      <th>alcohol</th>\n",
       "      <th>wine_type</th>\n",
       "      <th>country</th>\n",
       "      <th>grape</th>\n",
       "      <th>rating</th>\n",
       "      <th>pickup_location</th>\n",
       "      <th>vivino_link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.winenara.com/shop/product/product_...</td>\n",
       "      <td>winenara</td>\n",
       "      <td>49000</td>\n",
       "      <td>모노폴 클라시코</td>\n",
       "      <td>Monopole Classico</td>\n",
       "      <td>https://www.winenara.com/uploads/product/550/1...</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>화이트</td>\n",
       "      <td>스페인</td>\n",
       "      <td></td>\n",
       "      <td>3.8</td>\n",
       "      <td></td>\n",
       "      <td>https://www.vivino.com/monopole-la-rioja-blanc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.winenara.com/shop/product/product_...</td>\n",
       "      <td>winenara</td>\n",
       "      <td>32000</td>\n",
       "      <td>슐럼베르거 로제 스페셜 브뤼</td>\n",
       "      <td>Schlumberger Rose Special Brut</td>\n",
       "      <td>https://www.winenara.com/uploads/product/550/d...</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>스파클링</td>\n",
       "      <td>독일</td>\n",
       "      <td></td>\n",
       "      <td>3.8</td>\n",
       "      <td></td>\n",
       "      <td>https://www.vivino.com/schlumberger-spring-edi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.winenara.com/shop/product/product_...</td>\n",
       "      <td>winenara</td>\n",
       "      <td>50000</td>\n",
       "      <td>SET)페데럴리스트 샤르도네 원통 패키지</td>\n",
       "      <td>SET)THE FEDERALIST CHARDONNAY</td>\n",
       "      <td>https://www.winenara.com/uploads/product/550/d...</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>화이트</td>\n",
       "      <td>미국</td>\n",
       "      <td></td>\n",
       "      <td>3.7</td>\n",
       "      <td></td>\n",
       "      <td>https://www.vivino.com/federalist-chardonnay-m...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.winenara.com/shop/product/product_...</td>\n",
       "      <td>winenara</td>\n",
       "      <td>55000</td>\n",
       "      <td>베니카 트레 비니스</td>\n",
       "      <td>VENICA TRE VIGNIS</td>\n",
       "      <td>https://www.winenara.com/uploads/product/550/c...</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>화이트</td>\n",
       "      <td>이탈리아</td>\n",
       "      <td></td>\n",
       "      <td>3.9</td>\n",
       "      <td></td>\n",
       "      <td>https://www.vivino.com/US-CA/en/venica-venica-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.winenara.com/shop/product/product_...</td>\n",
       "      <td>winenara</td>\n",
       "      <td>24900</td>\n",
       "      <td>SET)빌라엠비앙코 + 글라스2개 윈터패키지</td>\n",
       "      <td>SET)VILLA M Bianco + GLASS WINTER PACKAGE</td>\n",
       "      <td>https://www.winenara.com/uploads/product/550/a...</td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4</td>\n",
       "      <td>-1</td>\n",
       "      <td>디저트</td>\n",
       "      <td>이탈리아</td>\n",
       "      <td></td>\n",
       "      <td>3.9</td>\n",
       "      <td></td>\n",
       "      <td>https://www.vivino.com/villa-m-bianco/w/1774733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 url site_name  price  \\\n",
       "0  https://www.winenara.com/shop/product/product_...  winenara  49000   \n",
       "1  https://www.winenara.com/shop/product/product_...  winenara  32000   \n",
       "2  https://www.winenara.com/shop/product/product_...  winenara  50000   \n",
       "3  https://www.winenara.com/shop/product/product_...  winenara  55000   \n",
       "4  https://www.winenara.com/shop/product/product_...  winenara  24900   \n",
       "\n",
       "                       name                                    en_name  \\\n",
       "0                  모노폴 클라시코                          Monopole Classico   \n",
       "1           슐럼베르거 로제 스페셜 브뤼             Schlumberger Rose Special Brut   \n",
       "2    SET)페데럴리스트 샤르도네 원통 패키지              SET)THE FEDERALIST CHARDONNAY   \n",
       "3                베니카 트레 비니스                          VENICA TRE VIGNIS   \n",
       "4  SET)빌라엠비앙코 + 글라스2개 윈터패키지  SET)VILLA M Bianco + GLASS WINTER PACKAGE   \n",
       "\n",
       "                                             img_url  body acidity tannin  \\\n",
       "0  https://www.winenara.com/uploads/product/550/1...     3                  \n",
       "1  https://www.winenara.com/uploads/product/550/d...     3                  \n",
       "2  https://www.winenara.com/uploads/product/550/d...     3                  \n",
       "3  https://www.winenara.com/uploads/product/550/c...     4                  \n",
       "4  https://www.winenara.com/uploads/product/550/a...    -1                  \n",
       "\n",
       "   sweetness  alcohol wine_type country grape  rating pickup_location  \\\n",
       "0         -1       -1       화이트     스페인           3.8                   \n",
       "1         -1       -1      스파클링      독일           3.8                   \n",
       "2         -1       -1       화이트      미국           3.7                   \n",
       "3         -1       -1       화이트    이탈리아           3.9                   \n",
       "4          4       -1       디저트    이탈리아           3.9                   \n",
       "\n",
       "                                         vivino_link  \n",
       "0  https://www.vivino.com/monopole-la-rioja-blanc...  \n",
       "1  https://www.vivino.com/schlumberger-spring-edi...  \n",
       "2  https://www.vivino.com/federalist-chardonnay-m...  \n",
       "3  https://www.vivino.com/US-CA/en/venica-venica-...  \n",
       "4    https://www.vivino.com/villa-m-bianco/w/1774733  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare Langchain Tool"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tool1: Wine database 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "loader =DataFrameLoader(data_frame=df, page_content_column='name')\n",
    "docs = loader.load()\n",
    "embeddings = OpenAIEmbeddings()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "아래는 wine database1에 metadata_field Attribute이다. 아래를 기준으로 서치를 진행하게 된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata_field_info = [\n",
    "    AttributeInfo(\n",
    "        name=\"body\",\n",
    "        description=\"1-5 rating for the body of wine\",\n",
    "        type=\"int\",\n",
    "    ),\n",
    "    AttributeInfo(\n",
    "        name=\"sweetness\",\n",
    "        description=\"1-5 rating for the sweetness of wine\",\n",
    "        type=\"int\",\n",
    "    ),\n",
    "    AttributeInfo(\n",
    "        name=\"alcohol\",\n",
    "        description=\"1-5 rating for the alcohol of wine\",\n",
    "        type=\"int\",\n",
    "    ),\n",
    "    AttributeInfo(\n",
    "        name=\"price\",\n",
    "        description=\"The price of the wine\",\n",
    "        type=\"int\",\n",
    "    ),\n",
    "    AttributeInfo(\n",
    "        name=\"rating\", \n",
    "        description=\"1-5 rating for the wine\", \n",
    "        type=\"float\"\n",
    "    ),\n",
    "    AttributeInfo(\n",
    "        name=\"wine_type\", \n",
    "        description=\"The type of wine. It can be '레드', '로제', '스파클링', '화이트', '디저트', '주정강화'\", \n",
    "        type=\"string\"\n",
    "    ),\n",
    "    AttributeInfo(\n",
    "        name=\"country\", \n",
    "        description=\"The country of wine. It can be '기타 신대륙', '기타구대륙', '뉴질랜드', '독일', '미국', '스페인', '아르헨티나', '이탈리아', '칠레', '포루투칼', '프랑스', '호주'\", \n",
    "        type=\"float\"\n",
    "    ),\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "vectorstore = Chroma.from_documents(docs, embeddings)\n",
    "document_content_description = \"Database of a wine\"\n",
    "llm = OpenAI(temperature=0)\n",
    "retriever = SelfQueryRetriever.from_llm(\n",
    "    llm, vectorstore, document_content_description, metadata_field_info, verbose=True\n",
    ")  # Added missing closing parenthesis\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "query=' ' filter=Operation(operator=<Operator.AND: 'and'>, arguments=[Comparison(comparator=<Comparator.EQ: 'eq'>, attribute='wine_type', value='레드'), Operation(operator=<Operator.AND: 'and'>, arguments=[Comparison(comparator=<Comparator.GT: 'gt'>, attribute='body', value=0), Comparison(comparator=<Comparator.LT: 'lt'>, attribute='body', value=3)])]) limit=None\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[Document(page_content='바 독 피노누아', metadata={'url': 'https://www.winenara.com/shop/product/product_view?product_cd=03P976', 'site_name': 'winenara', 'price': 29000, 'en_name': 'BAR DOG PINOT NOIR', 'img_url': 'https://www.winenara.com/uploads/product/550/1936_detail_084.png', 'body': 2, 'acidity': '', 'tannin': '', 'sweetness': -1, 'alcohol': -1, 'wine_type': '레드', 'country': '미국', 'grape': '', 'rating': 3.6, 'pickup_location': '', 'vivino_link': 'https://www.vivino.com/US-CA/en/bar-dog-pinot-noir/w/7129723'}),\n",
       " Document(page_content='루이라뚜르 피노누아', metadata={'url': 'https://www.winenara.com/shop/product/product_view?product_cd=03H965', 'site_name': 'winenara', 'price': 52000, 'en_name': 'LOUIS LATOUR PINOT NOIR', 'img_url': 'https://www.winenara.com/uploads/product/550/493_detail_025.png', 'body': 2, 'acidity': '', 'tannin': '', 'sweetness': -1, 'alcohol': -1, 'wine_type': '레드', 'country': '프랑스', 'grape': '', 'rating': 3.6, 'pickup_location': '', 'vivino_link': 'https://www.vivino.com/GB/en/louis-latour-bourgogne-pinot-noir/w/7343'}),\n",
       " Document(page_content='루이라뚜르 상뜨네', metadata={'url': 'https://www.winenara.com/shop/product/product_view?product_cd=03P299', 'site_name': 'winenara', 'price': 79000, 'en_name': 'LOUIS LATOUR SANTENAY', 'img_url': 'https://www.winenara.com/uploads/product/550/489_detail_096.png', 'body': 2, 'acidity': '', 'tannin': '', 'sweetness': -1, 'alcohol': -1, 'wine_type': '레드', 'country': '프랑스', 'grape': '', 'rating': 3.8, 'pickup_location': '', 'vivino_link': 'https://www.vivino.com/louis-latour-santenay-rouge/w/7369'}),\n",
       " Document(page_content='비알레또 로소', metadata={'url': 'https://www.winenara.com/shop/product/product_view?product_cd=033704', 'site_name': 'winenara', 'price': 12000, 'en_name': 'VIALETTO ROSSO', 'img_url': 'https://www.winenara.com/uploads/product/550/d1ef6058de3661b565084b815e359852.png', 'body': 2, 'acidity': '', 'tannin': '', 'sweetness': -1, 'alcohol': -1, 'wine_type': '레드', 'country': '이탈리아', 'grape': '', 'rating': 3.1, 'pickup_location': '', 'vivino_link': 'https://www.vivino.com/US-CA/en/vialetto-rosso-dolce/w/2213764'})]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "retriever.get_relevant_documents('{\"wine_type\":\"레드\", \"body\": \"lt 3 gt 0\"}') # gt means greater than, lt means less than, eq means equal to"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tool2: Search specific wine with url"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def search_with_url(query):\n",
    "    return SeleniumURLLoader(urls=[query]).load()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tool3: Wine database 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "index = VectorstoreIndexCreator(\n",
    "    vectorstore_cls=DocArrayInMemorySearch\n",
    ").from_loaders([loader])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tool4: Search in Google"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "search = SerpAPIWrapper()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tools = [\n",
    "    Tool(\n",
    "        name=\"Wine database\",\n",
    "        func=retriever.get_relevant_documents,\n",
    "        description=\"\"\"\n",
    "Database about the wines in wine store. You can get information such as the price of the wine, purchase URL, features, rating information, and more.\n",
    "You can search wines with the following attributes:\n",
    "- body: 1-5 rating int for the body of wine. You have to specify greater than or less than. For example, if you want to search for wines with a body rating of less than 3, enter 'body: gt 0 lt 3'\n",
    "- price: The price range of the wine. Please enter the price range in the form of range. For example, if you want to search for wines that cost less than 20,000 won, enter 'price: gt 0 lt20000'\n",
    "- rating: 1-5 rating float for the wine. You have to specify greater than or less than. For example, if you want to search for wines with a rating of less than 3, enter 'rating: gt 0 lt 3'\n",
    "- wine_type: The type of wine. It can be '레드', '로제', '스파클링', '화이트', '디저트', '주정강화'\n",
    "- name: The name of wine. 입력할 때는 '와인 이름은 \"비냐 조잘\" 입니다' 이런 식으로 입력해주세요.\n",
    "\"\"\"\n",
    "    ),\n",
    "    Tool(\n",
    "        name = \"Search specific wine with url\",\n",
    "        func=search_with_url,\n",
    "        description=\"Search specific wine with url. Query must be url\"\n",
    "    ),\n",
    "    Tool(\n",
    "        name = \"Wine database 2\",\n",
    "        func=index.query,\n",
    "        description=\"Database about the wines in wine store. You can use this tool if you're having trouble getting information from the wine database tool above. Query must be in String\"\n",
    "    ),\n",
    "    Tool(\n",
    "        name = \"Search\",\n",
    "        func=search.run,\n",
    "        description=\"Useful for when you need to ask with search. Search in English only.\"\n",
    "    ),\n",
    "]"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}