#!/usr/bin/env python
# coding: utf-8

# ## Recopila información

# In[132]:


import gradio as gr
import csv
import smtplib
from email.message import EmailMessage

# Function to handle the form submission
def handle_form(product, country, business_data, your_name, your_mail, business_name):
    # Save data to a CSV file
    with open('data.csv', 'a', newline='') as csvfile:
        writer = csv.writer(csvfile)
        
        # Check if the CSV file is empty (no header), then write the header
        if csvfile.tell() == 0:
            writer.writerow(["Product", "Country", "Business Data", "Your Name", "Your Mail", "Business Name"])
        
        writer.writerow([product, country, business_data, your_name, your_mail, business_name])

    # Send an email
    send_email(your_mail)

    return "Thanks"

# Function to send an email
def send_email(email_address):
    msg = EmailMessage()
    msg.set_content("Thank you for your submission!")
    msg['Subject'] = 'Form Submission'
    msg['From'] = 'your_email@gmail.com'  # Replace with your email
    msg['To'] = email_address

    with smtplib.SMTP('smtp.gmail.com', 587) as server:
        server.starttls()
        server.login('your_email@gmail.com', 'your_password')  # Replace with your email and password
        server.send_message(msg)

# Create a Gradio interface
iface = gr.Interface(
    fn=handle_form,
    inputs=[
        gr.inputs.Textbox(label="Product"),
        gr.inputs.Textbox(label="Country"),
        gr.inputs.Textbox(label="Business data"),
        gr.inputs.Textbox(label="Your Name"),
        gr.inputs.Textbox(label="Your Mail"),
        gr.inputs.Textbox(label="Business Name")
    ],
    outputs=gr.outputs.Textbox()
)

# Launch the interface
iface.launch()


# ## Genera la consulta a BARD

# In[133]:


from bardapi import Bard
import os
import pandas as pd

# Set the BARD API key
os.environ['_BARD_API_KEY'] = "ZgiPqEGAj8g2ly1fEp-RIbegbHycIGqpxB0Z43awd5bZfqOdKB_LYcvLeh6KdOGuyJRSIg."

def generate_prompt(field1, field2):
    return f"what norms I need to export '{field1}' to '{field2}' from Colombia?"

# Read data from CSV file into a DataFrame
data_file = "/Users/manuelbeltran/Documents/EMPRESAS/SAPIENS/SpinOffs/2023-ai001/data.csv"
df = pd.read_csv(data_file)

# Extract values from DataFrame
field1 = df["Product"].iloc[0]  # Assuming you want the first value
field2 = df["Country"].iloc[0]   # Assuming you want the first value

# Generate the prompt
prompt = generate_prompt(field1, field2)

# Use the Bard API to get an answer
answer = Bard().get_answer(prompt)['content']
print(answer)


# In[134]:


answer


# In[135]:


import gradio as gr

# Define a function to get the value of an existing variable
def get_variable_value():
    # Replace this with your logic to retrieve the variable value
    my_variable = answer
    return f"The value of the variable is: {my_variable}"

# Create the Gradio interface
iface = gr.Interface(
    fn=get_variable_value,
    inputs=None,  # No input needed
    outputs="text",
    layout="vertical",
    title="Variable Value Displayer",
    description="This interface displays the value of an existing variable."
)

# Launch the interface
iface.launch()


# In[136]:


sender_email = "maniambuj@gmail.com"
sender_password = "imvqqabnoicocfhl"
recipient_email = df.iloc[0,4]
message = answer


# In[137]:


import smtplib
try:
    server = smtplib.SMTP("smtp.gmail.com", 587)
    server.starttls()
    server.login('miambeltranuj@gmail.com','imvqqabnoicocfhl')
    server.sendmail(sender_email, recipient_email, message)
    print("Email sent successfully.")
except Exception as e:
    print("Error sending email:", str(e))
finally:
    server.quit()


# In[144]:


df['answer'] = answer
df['answer']


# In[145]:


df.dtypes


# In[147]:


df.to_csv('data.csv', index=False)


# In[148]:


import psycopg2
from psycopg2 import sql
import csv

# Database connection parameters
dbname = "mania"
user = "postgres"
password = "Juana2016$"
host = "localhost"  # Update this with the correct host or IP address
port = "5433"

# Connect to the PostgreSQL database
conn = psycopg2.connect(dbname=dbname, user=user, password=password, host=host, port=port)
cur = conn.cursor()

# Table and CSV file information
table_name = "inorma"
csv_file_path = "/Users/manuelbeltran/Documents/EMPRESAS/SAPIENS/SpinOffs/2023-ai001/data.csv"

# Create a list of column names
columns = ["product", "country", "businessdata", "yourname", "yourmail", "businessname","answer"]

# Build the SQL query for copying data from the CSV file
copy_sql = sql.SQL("COPY {} ({}) FROM STDIN WITH CSV HEADER").format(
    sql.Identifier(table_name),
    sql.SQL(', ').join(map(sql.Identifier, columns))
)


# Load data from CSV file into the table
with open(csv_file_path, 'r') as f:
    cur.copy_expert(sql=copy_sql, file=f)
   # conn.commit()


# Commit the transaction
conn.commit()

cur.close()
conn.close()


# In[150]:


if os.path.exists('data.csv'):
    os.remove(csv_file_path)


# In[ ]: