UseCases/Mortgage_Calculator_GenAI/Mortgage_Calculator_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "38bc532d-c78c-4c89-b191-242da0733f39",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Mortgage Calculator chatbot using Generative AI with Vantage\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fff71661-19b4-423a-867a-7c815b064c81",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233c'><b>Introduction:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the Mortgage Calculator chatbot using Generative AI demo, the combination of <b>RAG, Langchain, and LLM models</b> allows users to ask queries in layman's terms, retrieve relevant information from the Vantage tables, and generate accurate and concise answers based on the retrieved data. This integration of retrieval-based and generative-based approaches provides a powerful tool for extracting knowledge from structured sources and delivering user-friendly responses.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo we will build Generative Question-Answering using LangChain, a powerful library for working with LLMs like GPT-3.5, GPT-4, Mistral 7B, Mixtral 8x22B, etc. and JumpStart in ClearScape notebooks, a system is built where users can ask business questions in natural English and receive answers with data drawn from the relevant databases.</p>\n",
    "\n",
    "\n",
    "<center><img src=\"images/header.png\" alt=\"mortgage calc\"  width=800 height=800/></center>\n",
    "\n",
    "<br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Before going any farther, let's get a better understanding of RAG, LangChain, and LLM.</p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li> <b>Retrieval-Augmented Generation (RAG):</b></li></ul>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> &emsp;  &emsp;RAG is a framework that combines the strengths of retrieval-based and generative-based approaches in question-answering systems.It utilizes both a retrieval model and a generative model to generate high-quality answers to user queries. The retrieval model is responsible for retrieving relevant information from a knowledge source, such as a database or documents. The generative model then takes the retrieved information as input and generates concise and accurate answers in natural language.</p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li> <b>Langchain:</b></li></ul>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> &emsp;  &emsp; LangChain is a framework that facilitates the integration and chaining of large language models with other tools and sources to build more sophisticated AI applications. LangChain does not serve its own LLMs; instead, it provides a standard way of communicating with a variety of LLMs, including those from OpenAI and HuggingFace. LangChain accelerates the development of AI applications with building blocks. We learn the leverage the following building blocks in this notebook:</p>\n",
    " \n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li> <b> LLMs</b> – LangChain's <code>llm</code> class is designed to provide a standard interface for all LLM it supports.   </li>\n",
    "    <li> <b> PromptTemplate</b>  - LangChain’s <code>PromptTemplate</code> class are predefined structures for generating prompts for LLM’s. They can be reused across different LLM's.</li>\n",
    "    <li> <b> Chains</b> – When we build complex AI applications, we may need to combine multiple calls to LLM’s and to other components  LangChain’s <code>chain</code> class allows us to link calls to LLM’s and components. The most common type of chaining in any LLM application is combining a prompt template with an LLM and optionally an output parser. </li>\n",
    "</ol>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li> <b> LLM Models (Large Language Models):</b></li></ul>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> &emsp;  &emsp; LLM models refer to the large-scale language models that are trained on vast amounts of text data.\n",
    "These models, such as <code>GPT-3.5 (Generative Pre-trained Transformer)</code>, <code>GPT-4</code> , <code>LLaMA 2</code> ,<code> Google's Gemini 1.5 pro</code> , <code>Anthropic Claude 3.0</code> , <code>Mistral 7B</code> ,<code>Mixtral 8x22B.</code> etc. are capable of generating human-like text responses. LLM models have been pre-trained on diverse sources of text data, enabling them to learn patterns, grammar, and context from a wide range of topics. They can be fine-tuned for specific tasks, such as question-answering, natural language understanding, and text generation.\n",
    "LLM models have achieved impressive results in various natural language processing tasks and are widely used in AI applications for generating human-like text responses.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5d96beb-51cf-4cce-bbec-d7aaedbdfaba",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Why Vantage?</b></p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Chatbots, powered by Machine Learning and Artificial Intelligence, offer numerous advantages in the context of improving customer experience, particularly in the banking and financial institutions sector. Traditional methods of visiting a bank or financial institution in person and providing extensive information, which is then manually reviewed by an officer, are often time-consuming, error-prone, and rely on outdated manual processes.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vantage provides these same proven capabilities to search user details, proving personalized offers, helping to calcaulate mortgage,etc, integrated as native ClearScape Analytic functions. This allows organizations to drastically reduce human workforce by chatbot, while allowing for much more user friendly and easy interations.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ca49b9d-df0b-400a-acf8-0252ab8a2618",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'><b>Steps in the analysis:</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Configuring the environment</li>\n",
    "    <li>Connect to Vantage</li>\n",
    "    <li>Data Exploration</li>\n",
    "    <li>Setup LLM</li>\n",
    "    <li>Launch the Chatbot</li>\n",
    "    <li>Cleanup</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a833b5bf-74af-42be-8543-3782e1da95dc",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>1. Configuring the environment</b>\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b>The installation of the required libraries will take approximately <b>4 to 5 minutes</b> for the first-time installation. However, if the libraries are already installed, the execution will complete within 5 seconds.</i></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f6027a7-888d-441f-abc7-a6ea1c45f0a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install -r requirements.txt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca97cdce-0d5e-4e54-b404-da7bae24ef51",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <i>The above statements will install the required libraries to run this demo. To gain access to installed libraries after running this, restart the kernel.</i></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86b160ce-5ace-4116-86b6-394d6502553b",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b> The above statements may need to be uncommented if you run the notebooks on a platform other than ClearScape Analytics Experience that does not have the libraries installed. If you uncomment those installs, be sure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b>0 0</b></i></p></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a98b8169-9164-4916-9966-660aa51e5395",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b> To ensure that the Chatbot interface reflects the latest changes, please reload the page by clicking the 'Reload' button or pressing F5 on your keyboard for <b>first-time only</b> This will update the notebook with the latest modifications, and you'll be able to interact with the Chatbot using the new libraries.</i></p></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61067c88-2e9a-4c92-985b-34dc4ab74a13",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>1.1 Import the required libraries</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea50a4aa-1211-44fc-8166-317c35253207",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# teradata lib\n",
    "from teradataml import *\n",
    "\n",
    "# LLM\n",
    "from langchain_community.agent_toolkits import create_sql_agent\n",
    "from langchain.tools import tool\n",
    "from langchain_core.prompts.chat import (\n",
    "    ChatPromptTemplate,\n",
    "    MessagesPlaceholder,\n",
    ")\n",
    "from langchain_core.prompts import (\n",
    "    ChatPromptTemplate,\n",
    "    MessagesPlaceholder,\n",
    ")\n",
    "\n",
    "from langchain.memory import (\n",
    "    ConversationSummaryBufferMemory,\n",
    ")\n",
    "from langchain.tools.render import format_tool_to_openai_function\n",
    "from langchain.agents.output_parsers import OpenAIFunctionsAgentOutputParser\n",
    "from langchain.agents.format_scratchpad import format_to_openai_functions\n",
    "from langchain.agents.agent_toolkits import SQLDatabaseToolkit\n",
    "from langchain.agents import AgentType\n",
    "\n",
    "from langchain.agents import AgentExecutor\n",
    "from langchain_core.runnables import RunnablePassthrough\n",
    "\n",
    "# Suppress warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "display.print_sqlmr_query = False\n",
    "display.suppress_vantage_runtime_warnings = True"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a59718f8-7af4-4d1a-abc7-a860eb7cbae3",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>2. Connection to Vantage and OpenAI</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4153b889-0924-4e0f-acc8-6b0d440c77b3",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.1 Get the OpenAI API key</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73e10add-4b3c-4fc5-9359-0b44737bba0f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In order to utilize this demo, you will need an OpenAI API key. If you do not have one, please refer to the instructions provided in this guide to obtain your OpenAI API key: </p>\n",
    "\n",
    "[Openai_setup_api_key_guide](..//Openai_setup_api_key/Openai_setup_api_key.md)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afd64269-9393-434a-ac26-0b8386fc0a38",
   "metadata": {},
   "outputs": [],
   "source": [
    "import getpass\n",
    "\n",
    "# enter your openai api key\n",
    "api_key = getpass.getpass(prompt=\"\\n Please Enter Openai api key: \")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c60e4d23-3f17-4d43-b9b9-7e125f59b8c7",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.2 Connect to Vantage</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, and then use the down arrow to go to the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "164cfc91-93ed-45b9-98ba-73b91a50c28b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)\n",
    "execute_sql('''SET query_band='DEMO= Mortgage_Calculator_Python.ipynb;' UPDATE FOR SESSION;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b22db506-84a7-406b-be9f-9fe69d268ba4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8beec557-411b-4418-acf3-2f28d3c6beac",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.3 Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the cell below, we are synthetically generating a few records for the <b>Customer</b> and <b>Interest</b> tables. This will allow us to test the functionality of our Chatbot with a small set of data before loading the entire dataset.For the Customer table, we are generating 5 records with randomized names, emails, and phone numbers, account details, etc.  We are also assigning a unique customer ID to each record. For the Interest table, we are generating 5 records with randomized interest rates based on minimum and maximum of credit score.  We are also assigning a unique interest ID to each record.By generating this sample data, we can quickly test the application's ability to display customer information and interests.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In addition to this, we are loading data for <b>RealEstate, Locality, NearbyLocality, School, and ShoppingCenter</b>. We take RealEstate data from Kaggle, clean it first, and then add it to Vantage tables in the below code.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6b11997-40c6-4189-acef-97e0afe23f45",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>We iterate through a dictionary mapping file names to primary keys, read corresponding CSV files using Pandas, and copy the data to Vantage using the teradataml <b>copy_to_sql</b> function. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0036808e-ed2a-4b81-9f30-c61ba9c7f55c",
   "metadata": {},
   "outputs": [],
   "source": [
    "files_dict = {\n",
    "    \"Customer\": \"CustomerID\",\n",
    "    \"Interest\": \"ID\",\n",
    "    \"RealEstate\": \"PropertyID\",\n",
    "    \"Locality\": \"LocalityID\",\n",
    "    \"NearbyLocality\": \"LocalityID\",\n",
    "    \"School\": \"SchoolID\",\n",
    "    \"ShoppingCenter\": \"StoreID\",\n",
    "}\n",
    "\n",
    "\n",
    "print(\"-\" * 25, \"data ingestion started\", \"-\" * 25)\n",
    "for file in files_dict:\n",
    "    df = pd.read_csv(os.path.join(\"./data/\", f\"{file}.csv\"))\n",
    "    print(\"Copying data to Vantage for: \", file)\n",
    "    print(f\"Data information: {df.shape}\")\n",
    "    copy_to_sql(\n",
    "        df, table_name=file, primary_index=files_dict[file], if_exists=\"replace\"\n",
    "    )\n",
    "print(\"-\" * 25, \"data ingestion completed\", \"-\" * 25)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "490f3fe2-ed63-4838-bb19-4c0d0157453d",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>3. Data Exploration</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The goal of the Mortgage Chatbot is to provide a user-friendly interface for individuals to quickly and easily find personalized mortgage information. The chatbot aims to streamline the mortgage process by allowing users to ask questions in natural language and receive accurate, concise answers and calculations. By integrating with Vantage, the chatbot can retrieve relevant data and perform calculations to provide users with tailored mortgage options, empowering them to make informed decisions and save time in their home buying journey. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In our database, we have total seven tables, we can categorize it into two: </p>\n",
    "    <ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                <li> Financial tables</li>\n",
    "                <li> Property tables</li>\n",
    "    </ol>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'><li> <b>Financial tables:</b></li></ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> There are two tables: <b>customer</b> and <b>interest</b>. The customer table contains personal and financial information, while the interest table has details on credit score ranges and their corresponding interest rates.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data is synthetically generated, with a few rows of customer data, including name, address, email, account number, balance, income, and other relevant information.<p/>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Each row represents a snapshot of data captured at a specific point in time, and each column represents a different variable. The input dataset is as follows:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Customer data</b> i.e. first-name, last-name, date of birth, address, city, account number, Balance, branch id, account status, income etc.</li>\n",
    "    <li><b>Interest data</b>: min credit score, max credit score, interest rate.</li>\n",
    "</ol>\n",
    "</p>\n",
    "\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C', start=2><li> <b>Property tables:</b></li></ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have five tables that cover various aspects, ranging from real estate to locality details. For instance, let's take the locality of <b>Larrakeyah</b>, situated <b>9.0 km away from the airport</b>, boasting a <b>livability score of 4.3.</b> In Larrakeyah, we find <b>three schools:</b> St John's Catholic College, Larrakeyah Primary School, and Essington International School. Moreover, there are <b>two shopping centers:</b> Larrakeyah Shopping Complex and Larrakeyah Supermart.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see each row as a snapshot of data captured at a specific point in time, and each column represents a different variable. This is our input dataset.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "   <li><b>RealEstate data</b>:LocalityID, PropertyID, PropertyType, BuildingSize, LandSize, PreferredSize, OpenDate, ListingAgency, Price, Address, City, State, ZipCode, Phone, \n",
    "       BedroomCount, BathroomCount, ParkingCount</li>\n",
    "    <li><b>Locality data</b>: LocalityID, LocalityName, LocalityDescription, DistanceFromAirport, LivabilityScore</li>\n",
    "    <li><b>NearbyLocality data</b>: LocalityID, LocalityName, NearbyLocalityID</li>\n",
    "    <li><b>School data</b>: SchoolID, SchoolName, LocalityID</li>\n",
    "    <li><b>ShoppingCenter </b>: StoreID, StoreName, Type, LocalityID</li>\n",
    "</ol>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The source data from <a href=\"https://www.kaggle.com/datasets/thedevastator/australian-housing-data-1000-properties-sampled\">kaggle</a> is loaded in Vantage with table named <i>RealEstate</i>.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b><i>*Please scroll down to the end of the notebook for detailed column descriptions of the dataset.</i></b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eda8e565-b58b-4cc6-8d9c-50790ca3dcab",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1 Examine the Customer and Interest table</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's look at the sample data in Customer table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a9951cf-e2b0-40d7-9a8f-a01c7f48ef09",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_customer = DataFrame(\"Customer\")\n",
    "\n",
    "print(\"Data information: \\n\", tdf_customer.shape)\n",
    "tdf_customer.sort(\"CustomerID\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0d6d954-b72f-41c3-9f8c-bc15cf74d672",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There are 5 records in all, and there are 18 variables.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let's delve into the interest table. Here, we determine interest rates based on customers' minimum and maximum credit scores.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7cad00db-08e5-40d3-a3d2-2be92908a95a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_interest = DataFrame(\"Interest\")\n",
    "\n",
    "print(\"Data information: \\n\", tdf_interest.shape)\n",
    "tdf_interest"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "027b6add-e7fa-4381-8d73-2c6a86daaf7f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's examine that interest rate in light of a person's credit score. Here, we may determine the interest rate for each individual consumer by examining the credit score range min and max from the interest table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72874be1-a21c-4bd3-bbf3-8d346d89a4e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "    select c.FirstName, c.LastName, c.CreditScore, i.InterestRate from Customer c join Interest i on c.CreditScore between i.MinCreditScore and i.MaxCreditScore\n",
    "    where CustomerID=1001\n",
    "\"\"\"\n",
    "\n",
    "DataFrame.from_query(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "626e8ab6-5e2b-42b3-aee7-cdd7e921b205",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can observe that Customer John Doe possesses a credit score of 720, falling within the range of 670 to 739. Therefore, we will offer him a 3.9% interest rate.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59570bd2-6080-47ea-9221-29e0857b001e",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.2 Examine the property tables</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's look at the sample data in <b>RealEstate</b> table first.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad64fb76-c52d-47d6-9b56-33676481aaf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_real_estate = DataFrame(\"RealEstate\")\n",
    "\n",
    "print(\"Data information: \\n\", tdf_real_estate.shape)\n",
    "tdf_real_estate.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90fc813e-c9b4-4a0b-bd72-d8c0699fa09e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that we have ample details about the property here, such as property type, land size, bedroom count, contact details, price, full address, etc. These are crucial details for customers when we search for properties.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next, let's examine the sample data in our <b>Locality</b> table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6616ed3d-07ac-4e22-9de1-26d7ef8c4bf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_locality = DataFrame(\"Locality\")\n",
    "\n",
    "print(\"Data information: \\n\", tdf_locality.shape)\n",
    "tdf_locality.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa12939b-905a-44b5-a8cb-a7feac803778",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above table, we can gain a better understanding of the locality, including its distance from the airport and livability score.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thirdly, let's review the sample data in our <b>NearbyLocality</b> table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19d63118-b13c-4934-b75f-796c04fe2ce2",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_nearby_locality = DataFrame(\"NearbyLocality\")\n",
    "\n",
    "print(\"Data information: \\n\", tdf_nearby_locality.shape)\n",
    "tdf_nearby_locality.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d282ff89-5cce-4874-94b0-e77ed25ffbdf",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next, let's review the sample data in our <b>School</b> table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4cd070a-c269-4160-b99e-c7697c5fb423",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_school = DataFrame(\"School\")\n",
    "\n",
    "print(\"Data information: \\n\", tdf_school.shape)\n",
    "tdf_school.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e8a5128-f218-40db-94d8-d09a8c434e08",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next, let's review the sample data in our <b>ShoppingCenter</b> table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58b01a8f-c099-4bcf-86c3-86c351a0cb4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_shopping_center = DataFrame(\"ShoppingCenter\")\n",
    "\n",
    "print(\"Data information: \\n\", tdf_shopping_center.shape)\n",
    "tdf_shopping_center.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ffd19cf-7214-4cfc-b88f-74ccc4b6c845",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.3 Get database schema</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Build a consolidated view of Table Data Catalog by combining metadata stored for the database and table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81e9e675-7385-4a31-9f27-c779f693a3ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.sql_database import SQLDatabase\n",
    "\n",
    "#  Create the vantage connection\n",
    "db = SQLDatabase(eng)\n",
    "database = \"demo_user\"\n",
    "\n",
    "main_d = {\n",
    "    \"Customer\": tdf_customer.columns,\n",
    "    \"Interest\": tdf_interest.columns,\n",
    "    \"RealEstate\": tdf_real_estate.columns,\n",
    "    \"Locality\": tdf_locality.columns,\n",
    "    \"NearbyLocality\": tdf_nearby_locality.columns,\n",
    "    \"School\": tdf_school.columns,\n",
    "    \"ShoppingCenter\": tdf_shopping_center.columns,\n",
    "}\n",
    "\n",
    "\n",
    "def get_db_schema():\n",
    "    table_dicts = []\n",
    "    for k in main_d:\n",
    "        table_dicts.append(\n",
    "            {\n",
    "                \"database_name\": database,\n",
    "                \"table_name\": k,\n",
    "                \"column_names\": main_d[k],\n",
    "            }\n",
    "        )\n",
    "\n",
    "    database_schema_string = \"\\n\".join(\n",
    "        [\n",
    "            f\"Database: {table['database_name']}\\nTable: {table['table_name']}\\nColumns: {', '.join(table['column_names'])}\"\n",
    "            for table in table_dicts\n",
    "        ]\n",
    "    )\n",
    "\n",
    "    return database_schema_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80bd3ec9-1336-4b0c-9693-0e86f2b7b393",
   "metadata": {},
   "outputs": [],
   "source": [
    "database_schema = get_db_schema()\n",
    "print(database_schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89367d27-22ca-4a0e-b299-0f8e1a669157",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>4. Setup LLM </b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.1 Connect to database using SQLAlchemy and Initialize the Large Language Model</b></p>    \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Under the hood, LangChain uses SQLAlchemy to connect to SQL database. The SQLDatabaseToolkit can therefore be used with any SQL dialect supported by SQLAlchemy, such as Teradata Vantage, MS SQL, MySQL, MariaDB, PostgreSQL, Oracle SQL, and SQLite. Please refer to the <a href=\"https://docs.sqlalchemy.org/en/20/\"> SQLAlchemy documentation</a> for more information about requirements for connecting to your database. The SQLDatabaseToolkit builds off of SQLDatabaseChain and is designed to answer more general questions about a database, as well as recover from errors.</p>\n",
    "\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Important Note:</b> The code below establishes a database connection for data sources and Large Language Models. Please note that the solution will only work if the database connection for your sources is defined in the cell below. In addition to this, to use OpenAI models, we have to set OpenAI API key to environment variable.</i></p></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fbceafb4-793b-4d62-ab22-c9d49ac9a759",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain_openai import ChatOpenAI\n",
    "\n",
    "# OpenAI API\n",
    "os.environ[\"OPENAI_API_KEY\"] = api_key\n",
    "\n",
    "llm = ChatOpenAI(model=\"gpt-3.5-turbo-0125\", temperature=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6983640-c595-4185-9390-76ad933f3870",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo we will be using OpenAI’s <code>gpt-3.5-turbo-0125</code> model as LLM. To view list of available models on OpenAI <a href='https://platform.openai.com/docs/models'>click here</a></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In OpenAI's language models, the <b>temperature</b> parameter controls the randomness of the generated text. It affects the diversity and creativity of the model's responses. It is always a number between 0 and 1. A temperature of 0 means the responses will be very straightforward, almost deterministic (meaning you almost always get the same response to a given prompt). A temperature of 1 means the responses can vary wildly.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A higher temperature value, such as 1.0, increases the randomness and diversity of the generated output. This can lead to more varied and surprising responses, but it may also result in less coherence and occasional nonsensical outputs. A higher temperature means that the model might select a word with slightly lower probability, leading to more variation, randomness and creativity. A very high temperature therefore increases the risk of <b>hallucination</b>, meaning that the model starts selecting words that will make no sense or be off the topic.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>On the other hand, a lower temperature value, such as 0.2 or below, reduces randomness and makes the model's output more focused and deterministic. The generated text is likely to be more conservative, sticking closely to patterns observed in the training data. A temperature of 0 means roughly that the model will always select the highest probability word.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Choosing an appropriate temperature value depends on the desired output. Higher temperatures can be useful for creative tasks or brainstorming, while lower temperatures are preferred when you need more control over the output, such as when generating specific responses or following a particular style.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "680228a2-3bfa-44f4-8a02-b73fa8bf8e95",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.2 Setup SQLAgent</b></p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Before jumping into the chatbot, let us first understand what is an agent and why it might be preferred over a simple SQLChain.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>An agent is a component that has access to a suite of tools, including a Large Language Model (LLM). Its distinguishing characteristic lies in its ability to make informed decisions based on user input, utilizing the appropriate tools until it achieves a satisfactory answer. For example in the context of text-to-SQL, the LangChain SQLAgent will not give up if there is an error in executing the generated SQL. Instead, it will attempt to recover by interpreting the error in a subsequent LLM call and rectify the issue. Therefore, in theory, SQLAgent should outperform SQLChain in productivity and accuracy.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can think of agents as enabling tools for LLMs. Like how a human would use a calculator for maths or perform a Google search for information — agents allow an LLM to do the same thing.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1fbe3f75-c7ff-4142-9848-7aa89530a6ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "# define the tool\n",
    "@tool\n",
    "def generate_sql(query: str) -> str:\n",
    "    \"\"\"Given an input question, first create a syntactically correct Teradata-style query to run, then look at the results of the query and return the answer.\"\"\"\n",
    "    agent_executor = create_sql_agent(\n",
    "        llm=llm,\n",
    "        toolkit=SQLDatabaseToolkit(db=db, llm=llm),\n",
    "        verbose=True,\n",
    "        agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,\n",
    "        max_iterations=8,\n",
    "    )\n",
    "    return agent_executor.run(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e97e139d-d129-44c2-bf42-181d7b928164",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the code above, we're initializing SQLAgent by passing in LLM, SQLDatabaseToolkit, database, and agent type as ZeroShotReactAgent. Agents use an LLM to determine the best course of action and execute it. An action can either be using a tool and observing its output, or responding to the user. <a href='https://python.langchain.com/docs/modules/agents/agent_types/'>Here</a> are the agents available in LangChain.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The agent used here is a <b>zero-shot-react-description</b> agent. Zero-shot means the agent functions on the <b>current action only</b> — it has no memory. It uses the <a href='https://arxiv.org/pdf/2210.03629.pdf'>ReAct</a> framework to decide which tool to use, based solely on the tool’s description.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f923edc3-4242-4ab4-8512-a7d7353b7210",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>5. Launch the Chatbot</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo we are using ChatOpenAI model with Memory. This advanced technology allows us to store and recall conversations, enabling our chatbot to provide more personalized and informed responses.As a mortgage advisor, our chatbot is trained to assist with a wide range of loan-related inquiries. Whether you're looking to purchase a new home, refinance an existing loan, or simply have questions about the mortgage process, our chatbot is here to help.To begin, we'll set the prompt for our chatbot to work as a mortgage advisor. This will enable it to provide tailored advice and guidance based on your unique needs and circumstances.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4adb0962-73b3-4f07-abcb-377b2e382458",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b>Please note that our chatbot is specifically designed to address questions related to mortgage loans. If you have a question outside of this scope, we kindly ask that you refrain from asking it. This will help us provide you with the most accurate and efficient assistance possible.</i></p>\n",
    "</div>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36e35da2-17cd-4bc9-92f3-54aa70c212b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Current user\n",
    "CustomerID = 1001\n",
    "\n",
    "tools = [generate_sql]\n",
    "functions = [format_tool_to_openai_function(f) for f in tools]\n",
    "model = ChatOpenAI(temperature=0, streaming=True).bind(functions=functions)\n",
    "memory = ConversationSummaryBufferMemory(\n",
    "    return_messages=True, memory_key=\"chat_history\", llm=llm\n",
    ")\n",
    "\n",
    "prompt = ChatPromptTemplate.from_messages(\n",
    "    [\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"You'll be working as property and mortgage advisor and I will play the role of the user.\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"First act as property advisor, once you suggest the property, ask user Do you like this or shall I suggest more? Once the user say he likes it, \n",
    "            then only perform a role of mortgage advisor.\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"As a property advisor, you have direct access of property data includes tables like RealEstate, Locality, NearbyLocality,  ShoppingCenter, School.\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"As a property advisor first Ask a series of questions related to property like preferred locality, number of bedrooms, etc. Once user select the property then only ask for mortgage related questions like monthly income, credit score, bank balance etc, \n",
    "            Do not ask same question more than one time.\n",
    "            Always give property options with details like Property ID, Property Type, Building Size, Price, Address,Bedroom Count\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"Second, act as a mortgage advisor for the bank you have direct access to the banks data for user. The banks data includes tables like Customer and Interest\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"Once you suggest the property to the user, then ask about mortgage of property. You can ask question like below one by one:\n",
    "            1st question: Income: What is your total annual income, including any additional sources of income?\n",
    "            2nd question: What is your employment status?\n",
    "            3rd: What's your credit score? I can help you find out which interest rates you qualify for.\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"Ask questions sequentially - pausing between each question to wait for a response before proceeding to the next.\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"Start the chat with greeting and ask the questions to user as mentioned below. Greet user by starting the message: \n",
    "              ## Welcome to Property and Mortgage Chatbot! \\n\n",
    "              I'm here to provide you with expert advice and answers. \\n\n",
    "              ### Let's get started! \\n\n",
    "              Could you please share your property preferences with me?\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"Do not ask all the questions together, ask one by one.\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"\"Provide a succinct summary paragraph of these details as a response to the client but don't show the method of calculation. \n",
    "            Follow with a formatted table of monthly amounts (gross income, tax obligation, mortgage obligation, expenses, remaining disposable income).\n",
    "            Also provide best property options based on user's data.\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"Provide an opinion as to whether the mortgage is affordable and what the maximum borrowing amount could be\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"Do not append 'Query:' to SQLQuery. Do not remove dashes from the Query. Display SQLResult after the query is run in plain english that users can understand. \n",
    "            Provide answer in simple english statement.\n",
    "            Only use the following Column names: \\n\n",
    "             \"\"\"\n",
    "            + database_schema\n",
    "            + \"\"\" \"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"Given an input question, create a syntactically correct  query to run, then look at the results of the query and return the answer.\n",
    "            Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most 3 results by using SELECT TOP 3, note that LIMIT function does not works in Teradata DB. \"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            f\"\"\"If the user fails to provide a response or says 'I don't know' for a question, automatically call the 'generate_sql' function to retrieve the answer from the database. \n",
    "            The function should return the relevant answer for the question asked.\n",
    "            current user: {CustomerID}\"\"\",\n",
    "        ),\n",
    "        (\n",
    "            \"system\",\n",
    "            \"\"\"Whenever possible, give the answer in bulleted points and title in markup. like ##\"\"\",\n",
    "        ),\n",
    "        MessagesPlaceholder(variable_name=\"chat_history\"),\n",
    "        (\"user\", \"{input}\"),\n",
    "        MessagesPlaceholder(variable_name=\"agent_scratchpad\"),\n",
    "    ]\n",
    ")\n",
    "\n",
    "chain = (\n",
    "    RunnablePassthrough.assign(\n",
    "        agent_scratchpad=lambda x: format_to_openai_functions(x[\"intermediate_steps\"])\n",
    "    )\n",
    "    | prompt\n",
    "    | model\n",
    "    | OpenAIFunctionsAgentOutputParser()\n",
    ")\n",
    "\n",
    "agent_executor = AgentExecutor(\n",
    "    agent=chain,\n",
    "    tools=tools,\n",
    "    verbose=True,\n",
    "    memory=memory,\n",
    "    handle_parsing_errors=True,\n",
    "    max_iterations=8,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6228de91-fc5f-4d8e-a524-ed98215f5519",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b>Chatbot is accessing multiple components, including databases and LLMs. This may cause a brief delay in responses. Your patience is appreciated.</i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08f67705-7a3e-4c6d-93af-d6316a555c46",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This chatbot is designed to help you with any questions you have about loans and mortgages. You can start by typing any words or greeting, like \"Hi there!\" and the chatbot will respond with a friendly welcome message. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here are some ways we can interact with our Property and Mortgage Chatbot: This chatbot is tailored to help you find the ideal property based on your requirements and assists in calculating mortgages accurately.\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Property search: </b></li>\n",
    "</ol>\n",
    "    \n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Ask a question: </b> Type your question into the chat window, and the chatbot will do its best to provide a helpful response. For example, you could ask:\n",
    "        <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "            <li><b>Which is the best locality near the city center?</b></li>\n",
    "            <li><b>Can you suggest apartments with a land size greater than 300 square meters and featuring at least two bedrooms?</b> </li>\n",
    "        </ul>\n",
    "    </li>\n",
    "    \n",
    "<li><b>Provide information: </b> The chatbot may ask you for more information to better understand your question or provide a more accurate response. For example, it might ask:\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "            <li><b>Can you suggest good properties within my budget range of $250000 to \\$410000?</b> </li>\n",
    "            <li> <b>Could you please provide me with houses listed by the agency \"Ray White - NIGHTCLIFF\"?</b> </li>\n",
    "    </ul>\n",
    "    </li>\n",
    "\n",
    "    \n",
    "<li><b>Get advice: </b>The chatbot can provide guidance on the property search process and offer tips for finding the right properties for your needs. You can ask it questions like:\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li><b>What are the pros and cons of a buying a property on Larrakeyah area?</b> </li>\n",
    "        <li><b>What are the neighborhood areas within Larrakeyah?</b></li>\n",
    "        </ul>\n",
    "    </li>\n",
    "</ul>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C' start=2>\n",
    "    <li><b>Mortgage calculation: </b></li>\n",
    "</ol>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Ask a question: </b> Type your question into the chat window, and the chatbot will do its best to provide a helpful response. For example, you could ask:\n",
    "        <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "            <li><b>What is the best Interest rate would you offer to me?</b></li>\n",
    "            <li><b>What is the current balance in my account?</b> </li>\n",
    "        </ul>\n",
    "    \n",
    "<li><b>Provide information: </b> The chatbot may ask you for more information to better understand your question or provide a more accurate response. For example, it might ask:\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "            <li><b>Do you like any of these properties, or should I suggest more options?</b></li> \n",
    "            <li><b>Are you looking to purchase a home or refinance an existing mortgage?</b> </li>\n",
    "    </ul>\n",
    "\n",
    "<li><b>Get advice: </b>The chatbot can provide guidance on the mortgage process and offer tips for finding the right loan for your needs. You can ask it questions like:\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li><b>What are the pros and cons of a fixed-rate mortgage?</b></li>\n",
    "        <li><b>How do I choose the best lender?</b></li>\n",
    "    </ul>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Remember, the Mortgage Chatbot is here to help you, so feel free to ask any questions you have. It's available 24/7, so you can get the information you need at a time that's convenient for you.\n",
    "Start your conversation with the Mortgage Chatbot now by typing your first message in the chat window! </p>\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b>An example of the tentative output the chatbot will provide at the end is shown here.</i></p>\n",
    "</div>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thank you for providing your current monthly mortgage payment. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Based on the information you have provided, here is a summary of your financial details:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Total annual income: \\$850,000</li>\n",
    "<li>Employment status: Full-time</li>\n",
    "<li>Credit score: 720</li>\n",
    "<li>Monthly expenses: \\$12,000</li>\n",
    "<li>Term length: 15 years</li>\n",
    "<li>Additional assets value: \\$100,000</li>\n",
    "<li>Current monthly mortgage payment: \\$5,000</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let me calculate the maximum house price and monthly repayment for principal and interest based on a 5% APR. Please give me a moment.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>According to my calculations, the maximum house price you can afford is \\$1,500,000. The estimated monthly repayment for principal and interest would be approximately $9,500.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here is a breakdown of your monthly amounts:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Gross income: \\$70,833</li>\n",
    "<li>Tax obligation (25%): \\$17,708</li>\n",
    "<li>Mortgage obligation: \\$9,500</li>\n",
    "<li>Monthly expenses: \\$12,000</li>\n",
    "<li>Remaining disposable income: \\$31,625</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Based on these calculations, it appears that the mortgage is affordable for you. However, please keep in mind that this is just an estimate and other factors such as your debt-to-income ratio and credit history may also be considered by the bank.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If you have any further questions or if there's anything else I can assist you with, please let me know.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2398d71e-cdae-4a64-bc14-03a503b9523d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's chat with the bot!</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7df82c07-05d0-4630-ba74-7bd6b77955a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import panel as pn\n",
    "\n",
    "pn.extension(design=\"material\")\n",
    "\n",
    "# clear the memory\n",
    "memory.clear()\n",
    "\n",
    "def callback(contents, user, instance):\n",
    "    return agent_executor.invoke({\"input\": contents})[\"output\"]\n",
    "\n",
    "\n",
    "pn.chat.ChatInterface(\n",
    "    callback=callback,\n",
    "    show_rerun=False,\n",
    "    show_undo=False,\n",
    "    show_clear=False,\n",
    "    width=800,\n",
    "    height=400,\n",
    ").servable()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63e0695f-cb92-4c79-af24-535a31582f7b",
   "metadata": {},
   "source": [
    "<i>If the chatbot didn't work when you pressed ENTER, on your first time using this demo on your environment, did you use F5 to reload the site? See instructions at the top of the notebook.<br>\n",
    "If you asked a question and got no reponse after a few minutes, it is possible that you will need to type 0 0 to restart the kernel and re-run the demo. Questions outside the model seem to confuse the chatbot.  </i>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50b8b817-c0ff-42b3-a651-c8268ac40942",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>5 Cleanup</b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>5.1 Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cleanup work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0aacb87c-0d81-40f0-8754-608d47e602cc",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'> <b>5.2 Databases and Tables </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b398148-9486-4535-8c06-d3f77669bbdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "for t in [\n",
    "    \"Customer\",\n",
    "    \"Interest\",\n",
    "    \"RealEstate\",\n",
    "    \"Locality\",\n",
    "    \"NearbyLocality\",\n",
    "    \"School\",\n",
    "    \"ShoppingCenter\",\n",
    "]:\n",
    "\n",
    "    try:\n",
    "\n",
    "        db_drop_table(t)\n",
    "\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73ef4cbe-1beb-4c65-ac60-ffd6250668a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae971f90-bef1-4228-bcbf-c333e9843284",
   "metadata": {},
   "source": [
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>Dataset:</b>\n",
    "\n",
    "**Customer Table**\n",
    "- `CustomerID`: unique row customer id\n",
    "- `FirstName`: customer first name\n",
    "- `LastName`: customer last name\n",
    "- `DateOfBirth`: customer birth date\n",
    "- `Gender` : customer gender (categorical: \"M\",\"F\",)\n",
    "- `Address` : customer full address\n",
    "- `City`: city of customer (categorical: 'Almond','Geneva')\n",
    "- `State`: state of customer (categorical: 'NY','TX')\n",
    "- `Country`: country of customer (categorical: 'USA')\n",
    "- `Email`: customer email\n",
    "- `PhoneNumber`: customer phone number\n",
    "- `AccountNumber`: customer account number\n",
    "- `AccountType`: customer account type\n",
    "- `AccountStatus`: customer account status (categorical: 'Active','Inactive')\n",
    "- `Balance`: account balance, in dollar (numeric)\n",
    "- `BranchID`: branch id\n",
    "- `CreditScore`: customer credit score\n",
    "- `Income`: customer's monthly income, in dollar (numeric)\n",
    "\n",
    "**Interest Table**\n",
    "- `ID`: unique row id\n",
    "- `MinCreditScore`: min credit score\n",
    "- `MaxCreditScore`: max credit score\n",
    "- `InterestRate`: rate of interest\n",
    "\n",
    "**RealEstate**\n",
    "- `LocalityID`: unique row locality id (Numeric)\n",
    "- `PropertyID`: unique row property id (Numeric)\n",
    "- `PropertyType`: type of property being listed (categorical: 'Apartment','House', 'Unit')\n",
    "- `BuildingSize`: size of the property's building, in square meters. (Numeric)\n",
    "- `LandSize`: size of the property's land, in square meters. (Numeric)\n",
    "- `PreferredSize`: preferred size of the property, in square meters. (Numeric)\n",
    "- `OpenDate`: date that the property was first listed for sale. (Date)\n",
    "- `ListingAgency`: agency that is listing the property\n",
    "- `Price`: listing price of the property\n",
    "- `Address`: property's address\n",
    "- `City`: city that the property is located in\n",
    "- `State`: state that the property is located in\n",
    "- `ZipCode`: zip code that the property is located in\n",
    "- `Phone`: listing agent's phone number\n",
    "- `BedroomCount`: number of bedrooms in the property\n",
    "- `BathroomCount`: number of bathrooms in the property\n",
    "- `ParkingCount`: number of parking spaces in the property\n",
    "\n",
    " **Locality data**\n",
    " - `LocalityID`: unique row locality id (Numeric)\n",
    " - `LocalityName`: locality name\n",
    " - `LocalityDescription`: locality description\n",
    " - `DistanceFromAirport`: distance from airport to locality (Numeric)\n",
    " - `LivabilityScore`: locality livability score (Numeric)\n",
    " \n",
    "**NearbyLocality data**\n",
    "- `LocalityID`: unique row locality id (Numeric)\n",
    "- `LocalityName`: locality name (Numeric)\n",
    "- `NearbyLocalityID`: Unique row locality id (Numeric)\n",
    "\n",
    "**School data**\n",
    "- `SchoolID`: unique row school id (Numeric)\n",
    "- `SchoolName`: school name\n",
    "- `LocalityID`: unique row locality id (Numeric)\n",
    "\n",
    "**ShoppingCenter**\n",
    "- `StoreID`: unique row store id (Numeric)\n",
    "- `StoreName`: store name\n",
    "- `Type`: type of shopping center (categorical: 'Grocery Store','Shopping Mall')\n",
    "- `LocalityID`: unique row school id (Numeric)\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'><b>Dataset source:</b> <a href = 'https://www.kaggle.com/datasets/thedevastator/australian-housing-data-1000-properties-sampled'>kaggle</a></p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'><b>Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Teradataml Python reference: <a href = 'https://docs.teradata.com/search/all?query=Python+Package+User+Guide&content-lang=en-US'>here</a></li>\n",
    "    <li>Langchain Python reference: <a href='https://python.langchain.com/docs/get_started/introduction.html'>here</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5463848-592f-4321-b852-287e133872dd",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023, 2024. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}