import sqlite3
import pandas as pd
import openai
import os
import streamlit as st
import datetime

# Set OpenAI API Key (Ensure it's set properly)
openai.api_key = os.getenv("sk-NOBe-504FBda5dOQPesE8xKYgzmvBhG_Z_21UZGXtvT3BlbkFJF2vDvLgwzMMrIYgqAC6ezqMnupr9ZAelUCMH4XBP8A") or "sk-NOBe-504FBda5dOQPesE8xKYgzmvBhG_Z_21UZGXtvT3BlbkFJF2vDvLgwzMMrIYgqAC6ezqMnupr9ZAelUCMH4XBP8A"

# Create sample house data
house_data = [
    ("Alice", "1234567890", 15000, True, "Delhi", "2BHK", "Furnished"),
    ("Bob", "9876543210", 45000, False, "Mumbai", "3BHK", "Semi Furnished"),
    ("Charlie", "5556667777", 30000, True, "Pune", "4BHK", "Non Furnished"),
    ("David", "4445556666", 25000, True, "Jaipur", "1BHK", "Furnished"),
    ("Eve", "3332221111", 40000, False, "Ahmedabad", "3BHK", "Semi Furnished")
]

# Create DataFrame and save to CSV
df = pd.DataFrame(house_data, columns=["owner_name", "contact", "price", "for_sale", "location", "house_type", "house_details"])
df.to_csv("houses.csv", index=False)

# Connect to SQLite Database
conn = sqlite3.connect("houses.db")
cursor = conn.cursor()

# Create Table if it doesn't exist
cursor.execute('''
    CREATE TABLE IF NOT EXISTS houses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        owner_name TEXT,
        contact TEXT,
        price INTEGER,
        for_sale BOOLEAN,
        location TEXT,
        house_type TEXT,
        house_details TEXT
    )
''')

# Load data from CSV and insert into database (replace table content if exists)
df = pd.read_csv("houses.csv")
df.to_sql("houses", conn, if_exists="replace", index=False)
conn.commit()

# Function to retrieve data from SQLite Database
def retrieve_data():
    cursor.execute("SELECT * FROM houses")
    rows = cursor.fetchall()
    # Assuming the table now includes the 'id' column, we include it in the DataFrame.
    return pd.DataFrame(rows, columns=["owner_name", "contact", "price", "for_sale", "location", "house_type", "house_details"])

# Chatbot function that uses the database records in its prompt
def chatbot(query):
    df = retrieve_data()
    relevant_data = df.to_string(index=False)
    prompt = f"Given the following real estate records:\n{relevant_data}\n\nAnswer the user's query based on the provided data.\n\nUser Query: {query}\nResponse:"
    
    response = openai.ChatCompletion.create(
        model="gpt-3.5-turbo", 
        messages=[
            {"role": "system", "content": "You are an AI assistant that uses the provided database records to answer user queries."},
            {"role": "user", "content": prompt}
        ]
    )
    return response["choices"][0]["message"]["content"]

# Streamlit UI
st.title("Real Estate Chatbot")

st.write("### Available Houses")
st.dataframe(retrieve_data())

query = st.text_input("Ask about available houses:")
if st.button("Ask Chatbot"):
    if query:
        response_text = chatbot(query)
        # Get the current date and time
        now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        st.write("### User Query:")
        st.write(query)
        st.write(f"### Chatbot Response (Generated on {now}):")
        st.write(response_text)
    else:
        st.warning("Please enter a query.")

conn.close()