import gradio as gr
import pandas as pd
import re
import numpy as np
import sqlite3
import torch
from transformers import AutoTokenizer, AutoModelForCausalLM
from nltk.stem import PorterStemmer
import datefinder
from sentence_transformers import SentenceTransformer
import psycopg2
import warnings
warnings.filterwarnings('ignore')
from flask import Flask, request, jsonify

# Initialize Flask app
app = Flask(__name__)

# Initialize SQL connection
conn = None
username_query = ''
generated_sql = ''



# Flask route for Gradio
@app.route('/')
def index():
    username = request.args.get('username')
    token = request.args.get('token')
    
    if not username or not token:
        return jsonify({"error": "Missing username or token parameters"}), 400
    
    # Authenticate the user using the username and token (adjust logic as needed)
    if not authenticate_user(username, token):
        return jsonify({"error": "Authentication failed"}), 401
    
    
#     gr.ChatInterface(get_most_similar_question,title=title, retry_btn=None,theme=gr.themes.Base(),examples=["Hi", "What is the aggregated sales amount for yesterday", "What is the aggregated sales for each region for GT channel yesterday","Aggregate counterselloutvalue for each competitor from mx_dlb of may 2024.Also show result in percentage"],css="footer{display:none !important}",autofocus=True,textbox=gr.Textbox(lines=3)).queue().launch(auth=authenticate_user, share=True,show_error=False,server_name= '0.0.0.0', server_port=7898)
    # If authentication passes, launch the Gradio app.
    return gr.ChatInterface(
        fn=get_most_similar_question,
        title=title,
        retry_btn=None,
        theme=gr.themes.Base(),
        examples=["Hi", "What is the aggregated sales amount for yesterday", "What is the aggregated sales for each region for GT channel yesterday", "Aggregate counterselloutvalue for each competitor from mx_dlb of May 2024. Also show result in percentage"],
        css="footer{display:none !important}",
        autofocus=True,
        textbox=gr.Textbox(lines=3)
    ).launch(auth=authenticate_user, inline=True, share=True, show_error=False)

# Function for connecting to PostgreSQL
def connect_db():
    global conn
    try:
        print('Connecting to the PostgreSQL database…')
        conn = psycopg2.connect(
            host='107.110.152.126',
            dbname='new',
            user='datamartuser',
            password='Password1234',
            port=4432
        )
        conn.autocommit = True
        print('Connected to the PostgreSQL database')
    except(Exception, psycopg2.DatabaseError) as error:
        print(error)

connect_db()

# Define and load your models and data
retrieval_model_path = "NlpSql//model_jatin//"
retrieval_model = SentenceTransformer(retrieval_model_path)

model_path = "weights/sql-weights"
tokenizer = AutoTokenizer.from_pretrained(model_path)
model = AutoModelForCausalLM.from_pretrained(model_path, device_map="cuda", torch_dtype="auto", trust_remote_code=True)
device = torch.device('cuda' if torch.cuda.is_available() else 'cpu')
model = model.to(device)

data = pd.read_csv('NlpSql/Data/dataaa.txt', sep="\t", header=None)
headers = data.iloc[0]
new_df = pd.DataFrame(data.values[1:], columns=headers)
new_df['date'] = pd.to_datetime(new_df['date']).dt.strftime('%d-%b-%Y')
new_df['date'] = pd.to_datetime(new_df['date'], errors='coerce')
new_df['date'] = new_df['date'].dt.strftime('%d-%b-%Y').fillna('NO_DATE')








def preprocess_input(usertext):
    dates = list(datefinder.find_dates(usertext))
    if dates:
        # Use the first detected date
        formatted_date = dates[0].strftime("%d-%b-%Y")
        filtered_df = new_df[new_df['date'] == formatted_date]  # Filter for matching date
    else:
        # Filter for entries without a valid date
        filtered_df = new_df[new_df['date'] == 'NO_DATE']
    if filtered_df.empty:
        return None, "No matching data found for your query."
    return filtered_df, None

def get_most_similar_question(userText, history):
    filtered_df, error_msg = preprocess_input(userText.lower())
    if filtered_df is None:
        # The provided implementation of predict was not given, but it's assumed to handle the null case
        return predict(userText, history)
    filtered_df.loc[:, 'Question'] = filtered_df['Question'].str.lower()
#     filtered_df['Question'] = filtered_df['Question'].str.lower()
    question_list = filtered_df["Question"].to_list()
    question_embeddings = retrieval_model.encode(question_list)
    user_text_embedding = retrieval_model.encode([userText.lower()])

    # Calculate similarity scores
    similarity_scores = np.inner(user_text_embedding, question_embeddings).flatten()
    top_match_idx = np.argmax(similarity_scores)
#     print('similarity_scores',similarity_scores)
    print('top_match_idx',top_match_idx)
    print(similarity_scores[top_match_idx])
    if similarity_scores[top_match_idx] < 0.75:
        return predict(userText, history)
    
    if similarity_scores[top_match_idx] == 0:
        return "Sorry, no similar questions were found for your query."
    
    matched_data = filtered_df.iloc[top_match_idx,[10]]
    
    # Convert matched data to HTML for presentation
    matched_data_df = pd.DataFrame([matched_data.values], columns=matched_data.index)
    print(type(matched_data.values))
    print(matched_data.values)
    print(username_query)
    arr_str = np.array2string(matched_data.values)
    log_user_interaction(
        username=username_query,
        question=userText,
        generated_sql=arr_str,
        source='similarity',
        answer='sample',
        status='success',
        exception_text='',
        feedback=True
        )
    html_output = f"<h3>Here is the most similar question for your query:</h3>{matched_data_df.to_html(index=False)}"
    return html_output
# gr.ChatInterface(get_most_similar_question).launch(auth=authenticate_user,inline=True, share=True,server_name= '0.0.0.0', server_port=7897)



def log_user_interaction(username, question, generated_sql, source, answer, status, exception_text, feedback):
    """
    Log details of user interaction into the PostgreSQL database.

    Parameters:
    - username: The name of the user who asked the question.
    - question: The text of the user's question.
    - generated_sql: The SQL generated in response to the question, if applicable.
    - source: The source of the answer ('Predefined', 'Similarity Model', or 'LLM').
    - answer: The text of the answer provided to the user.
    - status: The status of the interaction ('success', 'failure', etc.).
    - exception_text: Any exception text if an error occurred during processing.
    - feedback: Boolean flag indicating user feedback on the answer.
    """
    try:
        # Create a cursor from the connection
        cur = conn.cursor()

        # SQL insert statement
        insert_query = """
            INSERT INTO user_queries (username, question, generated_sql, source, answer, status, exception_text, feedback)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s);
        """
        
        # Execute the insert statement
        cur.execute(insert_query, (username, question, generated_sql, source, answer, status, exception_text, feedback))

        # Commit the transaction
        conn.commit()

        # Close the cursor
        cur.close()
        
        print("Interaction logged successfully.")
    except (Exception, psycopg2.DatabaseError) as error:
        print(f"Failed to log the interaction: {error}")
        # Optionally, handle the error (e.g., retry logic, logging)




# Define dummy users for authentication
dummy_users = [
    {"username": "bansal.jatin", "password": "qwer@345"},
    {"username": "ajoy.kc", "password": "qwer@345"},
    {"username": "deepak.sachan", "password": "qwer@345"},
    {"username": "anand.mi", "password": "qwer@345"},
    {"username": "p.prasad", "password": "qwer@345"},
    {"username": "amit.s14", "password": "qwer@345"},
    {"username": "palash.b", "password": "qwer@345"},
    {"username": "test", "password": "qwer@345"},
    {"username": "prabhat.ks", "password": "qwer@345"},
    {"username": "yashjeet.v", "password": "qwer@345"},
    {"username": "deepak.kala", "password": "qwer@345"},
    {"username": "priyank.b", "password": "qwer@345"}
]
# Authenticate user function
def authenticate_user(username, password):
    global username_query  # Declare username_query as global to modify the global instance
    for user in dummy_users:
        if user["username"] == username and user["password"] == password:
            username_query = username  # This will now update the global variable
            print(username)
            print(username_query)
            return True
    return False

# def store_interaction(username, question, generated_sql, source, answer):
#         """Store details of a user interaction."""
        
#         # Assuming conn is your psycopg2 connection object
#         cur = conn.cursor()
        
#         insert_query = """
#         INSERT INTO user_queries (username, question, generated_sql, source, answer)
#         VALUES (%s, %s, %s, %s, %s)
#         """
        
#         cur.execute(insert_query, (username, question, generated_sql, source, answer))
        
#         # Commit the transaction
#         conn.commit()
        
#         cur.close()

porter_stemmer =PorterStemmer()
#--------------------------------------
def fn_preprocess_question(question):
    return ' '.join([porter_stemmer.stem(word) for word in question.split()])
dict_predefined_answers ={
    "Who are you":"I am the SAMS bot, your Virtual Sales Assistant. I’m here to help you navigate through SAMS databases and provide the information you need.",
    "Hi":"Hello, Welcome to SAMS Virtual Sales Assistant. I am designed to assist you in retrieving information from various SAMS databases. Please feel free to ask your queries, such as, 'What is the total sellout across India for July 16, 2022? among others.",
    "Hello":"Hello, Welcome to SAMS Virtual Sales Assistant. I am designed to assist you in retrieving information from various SAMS databases. Please feel free to ask your queries, such as, 'What is the total sellout across India for July 16, 2022? among others.",
    "What can you do":"I am equipped to offer you comprehensive insights and data from SAMS databases. Whether you need sales figures or specific reports, just ask, and I’ll provide the most accurate information available.Although my training is still going on.",
    "How can I use this service":"Using this service is simple. Just type in your query about any sales or database-related information, like ‘Show the sales trend for product XYZ during the last month,' and I'll fetch the details for you.",
    "I'm not sure what to ask.":"No problem at all. You can start with general queries like 'What were the top-selling products last month?'' or 'Update me on the current monthly sales report.' I am here to guide you through accessing the precise information you need.",
    "Thank you":"You're most welcome! If you have any more questions or need further assistance, I'm here to help. Your success is my priority.",
    "Goodbye":"Farewell! If you ever require any assistance or have more queries in the future, don't hesitate to reach out. Have a great day ahead!",
    "Bye":"Farewell! If you ever require any assistance or have more queries in the future, don't hesitate to reach out. Have a great day ahead!",
    "How are you":"I am good,Please ask anything related to SAMS and I will try to provide you best possible solution.Thanks"
}
def generate(tokenizer,prompt):
    eos_token_id = tokenizer.convert_tokens_to_ids(["```"])[0]
    inputs = tokenizer(prompt, return_tensors="pt").to(device)
    generated_ids = model.generate(
        **inputs,
        num_return_sequences=1,
        eos_token_id=eos_token_id,
        pad_token_id=eos_token_id,
        max_new_tokens=400,
        num_beams=5,
        temperature=0.6,
        do_sample=True
    )


    outputs = tokenizer.batch_decode(generated_ids, skip_special_tokens=True)
#     print('outputs',outputs[0])
    return outputs[0]
#-----    
def predict(question,history):
    print('Question',question)
#     print('history',history)
    try:
#         if username not in user_sessions:
#             return 'User Authentication Failed.'
#         preprocessed_question_var = fn_preprocess_question(question)
#         print('question',question)
#         preprocessed_question_var = question
#         print('preprocessed_question_var',preprocessed_question_var)
#         for predefined_question_var,predefined_answer_var in dict_predefined_answers.items():
#             print('predefined_question_var,predefined_answer_var',predefined_question_var,predefined_answer_var)
#             if fn_preprocess_question(predefined_question_var) == preprocessed_question_var:
#                 return predefined_answer_var
        print('question',question)
        preprocessed_question_var = question
        print('preprocessed_question_var',preprocessed_question_var)
        for predefined_question_var in dict_predefined_answers:
            if predefined_question_var.lower() == preprocessed_question_var.lower():
                log_user_interaction(
                username=username_query,
                question=question,
                generated_sql='',
                source='predefined',
                answer=dict_predefined_answers[predefined_question_var],
                status='Success',
                exception_text='',
                feedback=True
                )
                return dict_predefined_answers[predefined_question_var]



#         conn_local = sqlite3.connect("sales_database.db",check_same_thread=False)
#         cursor_local = conn_local.cursor()

        ### Answer Given the database schema,here is the SQL query that answers [QUESTION]{question}[/QUESTION] [SQL] """.format(question=question)
        prompt = """
        ### Instructions:
        You are a Postgres SQL expert. Given an input question, first create a syntactically correct Postgres query to run.Remember to use date('now') to get the current date or today's date,yesterday's date,day,month,year etc.
        also, if the question involves "today","yesterday","last 2 days","this month","last month","last year","this year" etc.Today's date is 21 May 2024.Remember to use the Indian numbering system using lakhs and crores.
        Unless the user specifies in the question a specific number of examples to obtain, query for at most 5 results using the LIMIT clause as per Postgres. You can order the results to return the most informative data in the database.
        Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (") to denote them as delimited identifiers.
        Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
        Please make accurate predictions regarding the column values and return the right column name. If values are repeating in more than one column(s) return the first column name where the value was found.
                  
        ### Restrictions
        1. DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database. It is not our database.
        2. If the question does not seem related to the database, just return "I don't know" as the answer.
        3. DO NOT MAKE ANSWER.
        
        ### Input:
        Generate a SQL query that answers the question `{question}`.
        This query will run on a database whose schema is represented in this string:        
        
        CREATE TABLE IF NOT EXISTS mx_sales(
            year integer,
            month integer,
            weekyear character varying(100) COLLATE pg_catalog."default",
            day integer,
            date date,
            salestype character varying(100) COLLATE pg_catalog."default",
            salescategory character varying(100) COLLATE pg_catalog."default",
            channel character varying(100) COLLATE pg_catalog."default",
            priceband character varying(100) COLLATE pg_catalog."default",
            region character varying(100) COLLATE pg_catalog."default",
            productgroup character varying(100) COLLATE pg_catalog."default",
            segment character varying(100) COLLATE pg_catalog."default",
            model character varying(100) COLLATE pg_catalog."default",
            modelname character varying(100) COLLATE pg_catalog."default",
            quantity integer,
            amount integer
        );
           
        CREATE TABLE IF NOT EXISTS mx_dlb (
                         year INTEGER, -- year of sellout/sale,
                         weekyear character varying(100) COLLATE pg_catalog."default",--weekyear of sellout,
                         day integer,
                         date date,
                         month integer,--month of sellout
                         counterselloutvalue double precision,-- value of sellout/amount
                         counterselloutvolume double precision,-- volume of sellout
                         region character varying(100) COLLATE pg_catalog."default",-- region of sale
                         competitor character varying(100) COLLATE pg_catalog."default",--competitor/competition name
                         priceband character varying(100) COLLATE pg_catalog."default",-- price bands of different model/modelcode sold
                         salestype character varying(100) COLLATE pg_catalog."default",-- salestype of the sale
                         channel character varying(100) COLLATE pg_catalog."default",-- channel through which it is sold
                         segment character varying(100) COLLATE pg_catalog."default",
                         modelname character varying(100) COLLATE pg_catalog."default",-- market name
        );

        ### Response:
        Based on your instructions, here is the SQL query I have generated to answer the question `{question}`:
        ```sql
        """.format(question=question,history=history)
        outputs = generate(tokenizer,prompt)
        global generated_sql
        generated_sql = outputs.split("```sql")[-1].split("```")[0].split(";")[0].strip()
        print(generated_sql)
#         generated_sql = generated_sql.replace('::numeric','')
        cur.execute(generated_sql) 
        result = cur.fetchall()
#         print(result)
#         cur.close() 
        df = pd.DataFrame(result,columns=[desc[0] for desc in cur.description])
        sql_datagram = generated_sql
#         print(generated_sql)
        # Create a DataFrame with the SQL datagram as the first row
        df_sql_row = pd.DataFrame([{'Generated_SQL': sql_datagram}])

        # Concatenate the SQL row with your existing DataFrame (df)
        df_with_sql = pd.concat([df,df_sql_row], keys=['',''],ignore_index=True)

        # Convert the DataFrame with SQL to HTML
        df_html_with_sql = df.to_html(index=False)
#         prompt1='''### Instructions:
#         You are a data analyst, please provide your two line comment on the given result `{result}` also keep in mind the question asked by user which is `{question}` . 
#         ### Response:
#         Based on your instructions, here is the Analysis I have generated for dataframe `{result}`:
#         ```insights'''.format(result=result,question=question)
#     #     df_str = df.to_string(index=False)
#         outputs_1= generate(tokenizer,prompt1)
#         print('outputs_1[0]',outputs_1)
#         insgihts = outputs_1.split("```insights")[-1].split("```")[0].split(";")[0].strip()
#         print(insgihts)
        html_output_1 = f"<h3>Sql Query : \n\n{generated_sql}\n\n</h3>{df_html_with_sql}\n<h2>Above results are derived using the Generated SQL query.</h2>"
        log_user_interaction(
        username=username_query,
        question=question,
        generated_sql=generated_sql,
        source='LLM',
        answer='sample',
        status='success',
        exception_text='',
        feedback=True
        )

        return html_output_1
    except Exception as e:
        log_user_interaction(
        username=username_query,
        question=question,
        generated_sql=generated_sql,
        source='LLM',
        answer='sample',
        status='Fail',
        exception_text=str(e),
        feedback=True
        )
        print(f"An exception occurred:{str(e)}")
        return "The answer for this query is not available right now.Kindly modify the query that can best fit the database schema.Please provide updated query."
title = "SAMS Virtual Sales Assistant"
# css = """.toast-wrap { display: none !important } """
# gr.ChatInterface(get_most_similar_question,title=title, retry_btn=None,theme=gr.themes.Base(),examples=["Hi", "What is the aggregated sales amount for yesterday", "What is the aggregated sales for each region for GT channel yesterday","Aggregate counterselloutvalue for each competitor from mx_dlb of may 2024.Also show result in percentage"],css="footer{display:none !important}",autofocus=True,textbox=gr.Textbox(lines=3)).queue().launch(auth=authenticate_user, share=True,show_error=False,server_name= '0.0.0.0', server_port=7898)







# Existing functions for processing and interacting with the DB
# Include all your functions here as they appear in your original code

# Add authenticate_user function and logging function
dummy_users = [
    {"username": "ban", "password": "qwer"},
]

def authenticate_user(username, token):
    global username_query
    for user in dummy_users:
        if user["username"] == username and user["password"] == token: # Token example logic
            username_query = username
            return True
    return False

# Run the Flask app
if __name__ == "__main__":
    app.run(port=7898, host='0.0.0.0')



correct this code wherever required,

right now it gives this error
TypeError: __call__() missing 1 required positional argument: 'send'
The view function did not return a valid response. The return type must be a string, dict, list, tuple with headers or status, Response instance, or WSGI callable, but it was a App.
127.0.0.6 - - [23/May/2024 05:24:40] "GET /?username=ban&token=qwer HTTP/1.1" 500 -