from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"
# import tensorflow_hub as hub
import pandas as pd
import re
import numpy as np
from sentence_transformers import SentenceTransformer
import gradio as gr
import sqlite3
import torch


from transformers import AutoTokenizer, AutoModelForCausalLM
from nltk.stem import PorterStemmer
import pandas as pd
import sqlite3
import torch
import transformers 
from transformers import AutoTokenizer, AutoModelForCausalLM, pipeline
import datefinder
from queue import Queue
from nltk.stem import PorterStemmer
# Load similarity model
from sentence_transformers import SentenceTransformer
import psycopg2 
import warnings
warnings.filterwarnings('ignore')

conn = None
username_query = ''
generated_sql = ''
try: 
	# connect to the PostgreSQL server 
	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
	# Creating a cursor with name cur. 
	cur = conn.cursor() 
	print('Connected to the PostgreSQL database') 
	
	# Execute a query: 
	# To display the PostgreSQL 
	# database server version 
	
	
except(Exception, psycopg2.DatabaseError) as error: 
	print(error) 

# import tensorflow as tf

retrieval_model_path = "NlpSql//model_jatin//"
retrieval_model = SentenceTransformer(retrieval_model_path)


# Load LLM model
model_path = "weights/sql-weights/"

# model_path = "mistral_model/"
tokenizer = AutoTokenizer.from_pretrained(model_path,trust_remote_code=True)
model = AutoModelForCausalLM.from_pretrained(model_path,
        trust_remote_code=True,
        torch_dtype=torch.float16,
        device_map="cuda",
        use_cache=True,)
# 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)

# model_path_1 = "phi3/"

# tokenizer_1 = AutoTokenizer.from_pretrained(model_path_1,trust_remote_code=True)

# model_1 = AutoModelForCausalLM.from_pretrained(model_path_1,
#         device_map="cuda", 
#     torch_dtype="auto", 
#     trust_remote_code=True,)
# model_1 = model_1.to(device)


# Load data
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')

# Convert dates back to a string format, or 'NO_DATE' for NaT/NaN values
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
import json

def load_glossary(file_path):
    with open(file_path, 'r') as f:
        return json.load(f)

def preprocess_query(query, glossary):
    for key,value in glossary.items():
        query = query.replace(key,value)
    return query
glossary = load_glossary('glossary_1.json')

import re

def replace_terms(sentence, glossary):
    # Sort glossary by length of keys to ensure multi-word keys are checked first
    sorted_glossary = sorted(glossary.items(), key=lambda x: len(x[0]), reverse=True)
    
    # Use word boundaries to prevent partial replacements and unnecessary repetitions
    for key, value in sorted_glossary:
        sentence = re.sub(r'\b{}\b'.format(re.escape(key)), value, sentence)
        
    return sentence

from collections import OrderedDict

def remove_duplicate_words(sentence):
    words = sentence.split()
    # Use an ordered dict to maintain original word order while removing duplicates
    seen = OrderedDict()
    for word in words:
        seen[word] = None
    return ' '.join(seen.keys())
def get_most_similar_question(userText,history):
    print(userText)
#     print(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
        preprocessed_query = remove_duplicate_words(replace_terms(userText, glossary))
        print('preprocessed_query_from_glosssary',preprocessed_query)
        return predict(preprocessed_query, 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.90:
        preprocessed_query = remove_duplicate_words(replace_terms(userText, glossary))
        print('preprocessed_query_from_glosssary',preprocessed_query)
        return predict(preprocessed_query, 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"},
    {"username": "rakesh.pk", "password": "qwer@345"},
    {"username": "trilok.n", "password": "qwer@345"},
    {"username": "yogesh.b2", "password": "qwer@345"},
    {"username": "ritesh.s", "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,model):
#     new_user_input_ids = tokenizer.encode(input + tokenizer.eos_token, return_tensors='pt')

#     # append the new user input tokens to the chat history
#     bot_input_ids = torch.cat([torch.LongTensor(history), new_user_input_ids], dim=-1)

#     # generate a response 
#     history = model.generate(bot_input_ids, max_length=1000, pad_token_id=tokenizer.eos_token_id).tolist()

#     # convert the tokens to text, and then split the responses into the right format
#     response = tokenizer.decode(history[0]).split("
")
#     response = [(response[i], response[i+1]) for i in range(0, len(response)-1, 2)]  # convert to tuples of list
#     return response, history
    model = model
    
    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=800,
        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]
#-----    
metadata=[{"column_name" :'Segment',"unique_values": {'Accessories','Note Pc','Smart Phone','Tab','Wearable'}},
          
        {"column_name" : 'Region',"unique_values": {	 'B2b',	 'East 1',	 'East 2',	 'MT',	 'North 1',	 'North 2',	 'North 3',	 'North 4',	 'ONLINE',	 'South 1',	 'South 2',	 'South 3',	 'West 1',	 'West 2',	 'West 3',	 'West 4'}},																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																			
        {"column_name" :  'Channel',"unique_values": {	 'B2B',	 'GT',	 'MT',	 'ON',	 'ONLINE'}},																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																															
        {"column_name" : 'PriceBand',"unique_values": {	 '6 K - 10 K',	 '10 K-15 K',	 '15 K-20 K',	 '20 K - 30 K',	 '30 K - 40 K',	 'Above 40K',	 'Below 6K'}}]																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																							



def format_value(value, scale, suffix):
    if pd.isna(value):
        return value
    return f"{value / scale:.2f}{suffix}"

def postprocess_data(df):
    volume_keywords = ['volume', 'quantity', 'units']
    value_keywords = ['sales', 'revenue', 'profit', 'total','sale']
    percentage_keywords = ['share', 'market_share', 'growth_rate']

    numeric_cols = df.select_dtypes(include=[np.number]).columns.tolist()

    for col in numeric_cols:
        if any(keyword in col.lower() for keyword in volume_keywords):
            df[col] = df[col].apply(format_value, args=(1e3, 'k'))
        elif any(keyword in col.lower() for keyword in value_keywords):
            df[col] = df[col].apply(format_value, args=(1e7, 'cr'))
        elif any(keyword in col.lower() for keyword in percentage_keywords):
            df[col] = df[col].apply(lambda x: f"{x:.2f}%")

    return df

def generate_response(df):
    response = df.to_string(index=False)
    return response

def predict(question,history):
#     history_1 =[i[::-3]for i in history]
#     print(type)
    print('Question',question)
    try:

        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]

        
        
        
        
        
        
        
   
        
#         prompt = """
#         ### Instructions:
#         You are a helpful Postgres SQL expert.
#         Consider the metadata as `{metadata}` to get the context of the question asked by user and create a accurate prompt using metadata and `{question}`asked by user.
#         Always give answers in appropriate units of measurement. Use the following aliases where applicable:
#         -Limit results upto two decimal points.
#         -For amounts or sum/aggregated amounts, use crores or lakhs.
#         -For volumes, use thousands (k).
#         -For market share or percentage-related values, use the % sign.
#         - use CURRENT_Date to get the current date.
        
#         Given an input question, first create a syntactically correct Postgres query to run.When required, 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 12 July 2024. Yesterday's date is 11 July 2024. Remember to use the Indian numbering system using lakhs and crores.
#         Remember to always use the units like crores, lakhs, thousands, percentage sign in the alias as well as obtained result. while generating query. 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,
#             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",
#             selloutquantity integer,
#             selloutamount bigint,
#             sellinquantity integer,
#             sellinamount bigint,
#             price integer,
#             familyname character varying(100) COLLATE pg_catalog."default"

#         );
      
        
           
#         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
#                          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
#         );
        
#         ###Some examples of SQL queries that corrsespond to questions are:
        
#         1.What is competitor wise market share for month may and year 2024.
#         Query :
#         ------Using OVER()--------------------------------------------------------------
#         WITH monthly_sales AS (
#         SELECT 
#             competitor,
#             year,
#             month,
#             SUM(counterSelloutValue) AS total_sales
#           FROM mx_dlb
#           WHERE year = 2024 AND month = 5
#           GROUP BY competitor, year, month
#         )

#         SELECT 
#           competitor,
#           ROUND(total_sales * 100.0 / SUM(total_sales) OVER (), 0) || '%' AS market_share_percent
#         FROM monthly_sales;
#         -----Using CTE------------------------------------------------------------------
#         WITH cte AS (
#         SELECT competitor, SUM(counterselloutvalue) AS CV
#         FROM mx_dlb
#         WHERE year = 2024 AND month = 5
#         GROUP BY competitor
#         )
#         SELECT competitor, ROUND(CV * 100.0 / (SELECT SUM(CV) FROM cte), 2) AS market_share || '%'
#         FROM cte;

#         2. show me SAMSUNG market share for month may and year 2024.
#         Query :
#         WITH monthly_sales AS (
#           SELECT 
#             competitor,
#             year,
#             month,
#             SUM(counterSelloutValue) AS total_sales
#           FROM mx_dlb
#           WHERE year = 2024 AND month = 5
#           GROUP BY competitor, year, month
#         )

#         SELECT 
#           competitor,
#           ROUND(total_sales * 100.0 / SUM(total_sales) OVER (), 0) AS market_share_percent || '%'
#           FROM monthly_sales
#         WHERE competitor = 'Samsung';



#         3.top 5 performing competitors by sellout value under GT/MT channel for month may and year 2024.
#         Query :
#         ----for all channels---------------------------------------------------------------
#         WITH monthly_sales AS (
#           SELECT 
#             competitor,
#             year,
#             month,
#             SUM(counterSelloutValue) AS total_sales
#           FROM mx_dlb
#           WHERE year = 2024 AND month = 5
#           GROUP BY competitor, year, month
#         )

#         SELECT 
#           competitor,
#           ROUND(total_sales / 10000000, 0) AS total_sales_in_crores || 'cr'
#         FROM monthly_sales
#         ORDER BY total_sales DESC
#         LIMIT 5;



#         4.show me Samsung market share for above 40 k priceband for month may and year 2024.
#         Query :
#         select competitor,sum(counterselloutvalue)*100 /
#         (Select sum(counterselloutvalue)   from mx_dlb
#         where year=2024 and month=5 and priceband='Above 40K' )  as marketshare 
#         from mx_dlb
#         where year=2024 and month=5 and priceband='Above 40K' and competitor='Samsung'
#         group by competitor;

#         5.show me Samsung sellout value priceband wise for month may and year 2024.
#         Query :
#         select priceband,sum(counterselloutvalue) as counterselloutvalue
#         from mx_dlb
#         where year=2024 and month=5 and competitor='Samsung'
#         group by priceband

#         5. How much is the smart phone sales this month for North 1 region.
#         Query:
#         SELECT ROUND(SUM(selloutamount) / 10000000, 2) as total_amount_in_crores
#         FROM mx_sales
#         WHERE region = 'North 1' AND salescategory = 'Smart Phone' AND month = 6;
        
        
#         ### Response:
#         Based on your instructions, here is the SQL query I have generated to answer the question `{question}`:
#         ```sql
#         """.format(question=question,history=history,metadata=metadata)
        
        ### Prompt
#         prompt = """
#         Instructions:
#         You are a helpful PostgreSQL SQL expert in India. Your task is to understand the context of the user's question using the given metadata, and generate an accurate SQL query. The query should always provide answers using appropriate units of measurement. Here's how you should set the units:
#         - For amounts or aggregated amounts: use Rupees as currency and show units as crores or lakhs.
#         - For volumes: use thousands (k).
#         - For market share or percentage values: use the % sign.
#         - Limit results to two decimal points.

#         Considerations for Dates:
#         - Use CURRENT_DATE to get the current date.
#         - If the question involves relative dates like "today", "yesterday", etc., use appropriate date functions.

#         Query Formatting Guidelines:
#         - Limit query results to at most 5 entries unless specified otherwise.
#         - Always use double quotes for column names and avoid querying all columns.
#         - If the question is unrelated to the provided database, return "I don't know".
#         - Never make DML statements (INSERT, UPDATE, DELETE or DROP).
        
#         Remember to use metadata from provided in this prompt to understand the user query well and apply appropriate filters.

#         ### Metadata
#         `{metadata}`

#         ### Database Schema

#         CREATE TABLE IF NOT EXISTS mx_sales (
#             "year" integer,
#             "month" integer,
#             "weekyear" character varying(100) COLLATE pg_catalog."default",
#             "day" integer,
#             "date" date,
#             "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",
#             "selloutquantity" integer,
#             "selloutamount" bigint,
#             "sellinquantity" integer,
#             "sellinamount" bigint,
#             "price" integer,
#             "familyname" character varying(100) COLLATE pg_catalog."default"
#         );

#         CREATE TABLE IF NOT EXISTS mx_dlb (
#             "year" INTEGER,
#             "weekyear" character varying(100) COLLATE pg_catalog."default",
#             "day" integer,
#             "date" date,
#             "month" integer,
#             "counterselloutvalue" double precision,
#             "counterselloutvolume" double precision,
#             "region" character varying(100) COLLATE pg_catalog."default",
#             "competitor" character varying(100) COLLATE pg_catalog."default",
#             "priceband" character varying(100) COLLATE pg_catalog."default",
#             "channel" character varying(100) COLLATE pg_catalog."default",
#             "segment" character varying(100) COLLATE pg_catalog."default",
#             "modelname" character varying(100) COLLATE pg_catalog."default"
#         );

#         ### Response:
#         Based on your instructions, here is the SQL query I have generated to answer the question {question}:
#         ```sql""".format(question=question,metadata=metadata)
        prompt = """
        ### Instructions:
        You are a helpful Postgres SQL expert.
        Always give answers in appropriate units of measurement. Use the following aliases where applicable:
        -Limit results upto two decimal points.
        -For amounts or sum/aggregated amounts, use crores or lakhs.
        -For volumes, use thousands (k).
        -For market share or percentage-related values, use the % sign.
        -use CURRENT_Date to get the current date.
      
        Given an input question, first create a syntactically correct Postgres query to run.Remeber not to apply any additional filters other then what is being asked in the question. When required, 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. Remember to use the Indian numbering system using lakhs and crores.
        Remember to always use the units like crores, lakhs, thousands, percentage sign in the alias as well as obtained result. while generating query. 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,
            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",
            selloutquantity integer,
            selloutamount bigint,
            sellinquantity integer,
            sellinamount bigint,
            price integer,
            familyname character varying(100) COLLATE pg_catalog."default"

        );
      
        
           
        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 numeric,-- value of sellout/amount
                         counterselloutvolume numeric,-- 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
                         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,
                         price integer
        );
        
        ###Some examples of SQL queries that corrsespond to questions are:
        
        1. What is the market share of each competitor in EAST 1 region:
        WITH east_region_total AS (
         SELECT competitor,SUM(counterselloutvalue) AS east_1_sales
            FROM mx_dlb
            WHERE region = 'EAST 1'
            group by competitor
        ),
        total_sales AS (
            SELECT SUM(counterselloutvalue) AS total_sales
            FROM mx_dlb where region = 'EAST 1'
        )
        SELECT competitor, round( (cast(east_1_sales as numeric)/ cast(total_sales as numeric)) * 100 ,2) as per 
        FROM east_region_total, total_sales;
        
        2.What is competitor wise market share for month may and year 2024.
        Query :
        ------Using OVER()--------------------------------------------------------------
        WITH monthly_sales AS (
        SELECT 
            competitor,
            year,
            month,
            SUM(counterSelloutValue) AS total_sales
          FROM mx_dlb
          WHERE year = 2024 AND month = 5
          GROUP BY competitor, year, month
        )

        SELECT 
          competitor,
          ROUND(total_sales * 100.0 / SUM(total_sales) OVER (), 0)  || '%' AS market_share_percent
        FROM monthly_sales;
       
        3. show me Samsung market share for each month
        Query :
        SELECT month ,
            competitor,
            ROUND(total_sales * 100.0 / (SELECT SUM(counterselloutvalue) FROM mx_dlb), 0) || ‘%’ AS market_share_percent
        FROM (
            SELECT month,
                competitor,
                SUM(counterselloutvalue) AS total_sales
            FROM mx_dlb
            GROUP BY month,competitor
        ) AS monthly_sales
        WHERE competitor = 'Samsung';


        4.top 5 performing competitors by sellout value under GT/MT channel for month may and year 2024.
        Query :
        ----for all channels---------------------------------------------------------------
        WITH monthly_sales AS (
          SELECT 
            competitor,
            year,
            month,
            SUM(counterSelloutValue) AS total_sales
          FROM mx_dlb
          WHERE year = 2024 AND month = 5
          GROUP BY competitor, year, month
        )

        SELECT 
          competitor,
          ROUND(total_sales / 10000000, 0) AS total_sales_in_crores || 'cr' AS units_of_measurement
        FROM monthly_sales
        ORDER BY total_sales DESC
        LIMIT 5;



        5.show me Samsung market share for above 40 k priceband for month may and year 2024.
        Query :
        select competitor,sum(counterselloutvalue)*100 /
        (Select sum(counterselloutvalue)   from mx_dlb
        where year=2024 and month=5 and priceband='Above 40K' )  as marketshare 
        from mx_dlb
        where year=2024 and month=5 and priceband='Above 40K' and competitor='Samsung'
        group by competitor;

        6.show me Samsung sellout value priceband wise for month may and year 2024.
        Query :
        select priceband,sum(counterselloutvalue) as counterselloutvalue
        from mx_dlb
        where year=2024 and month=5 and competitor='Samsung'
        group by priceband

        7. How much is the smart phone sales this month for North 1 region.
        Query:
        SELECT ROUND(SUM(selloutamount) / 10000000, 2) as total_amount_in_crores
        FROM mx_sales
        WHERE region = 'North 1' AND salescategory = 'Smart Phone' AND month = 6;
        
        
        ### Response:
        Based on your instructions, here is the SQL query I have generated to answer the question `{question}`:
        ```sql
        """.format(question=question,history=history,metadata=metadata)
        
        

        
        outputs = generate(tokenizer,prompt,model)
        
        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()
        table =result
#         print(type(result))
#         print('result',result)
#         print('table',table)
#         prompt_1 = """
#         ### Instructions:
#         You are a financial data analyst and you are required to summarize the key insights of given table and question `{question}`

#         `{table}`
#         This table is generated using `{generated_sql}` sql.You can consider the query for your reference to know how exaclty we are deriving the results.
#         - Please list important, but no more than one, highlights in the given table.
#         - Please write in a professional and business-neutral tone.
#         - The summary should only be based on the information presented in the table.
#         - When needed, Use indian numerical numbering system like crores for sales amount, percentage for market share or counter share, for volume use thousands as K etc 
#         - 1 crore =1,00,00,000, 1 lakh = 1,00,000, 1 thousand = 1,000, use 'K' for thousand.
#         - Generate the insight in well formatted manner with proper line formatting.
#         ### Response:
#         Based on your instructions, here are the concise insights I have derived from {table} in response to the question {question}:
#         ```prompt
#         """.format(table=table,question =question,generated_sql=generated_sql)
        
        
#         outputs_1 = generate(tokenizer_1,prompt_1,model_1)
        
#         global generated_prompt
#         generated_prompt = outputs_1.split("```prompt")[-1].split("```")[0].split(";")[0].strip()
#         print(generated_prompt)
        
#         print(result)
#         cur.close() 
        df = pd.DataFrame(result,columns=[desc[0] for desc in cur.description])
    
#         df_processed = postprocess_data(df)
#         response = generate_response(df_processed)
#         print('response',response)
        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)
#         generated_prompt = generated_prompt
# Hi, Your question is {preprocessed_question_var}
#         html_output_1 = f"<h3>Sql Query : \n\n{generated_sql}\n\n</h3>{df_html_with_sql}\n<h4>{generated_prompt}</h4>\n\n<h4>Above results are derived using the Generated SQL query.</h4>"
        html_output_1 = f"<h5>\n\n{df_html_with_sql}\n\n{generated_sql}\n\n</h5>\n\n<h4>Above results are derived using the Generated SQL query.</h4>"

        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"
chatbot = gr.Chatbot(show_copy_button=True,likeable=True,sanitize_html=True)

desc ="""This chatbot is designed to provide answers based on information from the sales and DLB databases. Below are the fields we have taken into account. Please feel free to ask any relevant questions.
1. mx_sales( year, month,weekyear ,day , date ,salescategory,channel, priceband ,region,productgroup,segment,model,modelname,selloutquantity,selloutamount,sellinquantity,sellinamount,price,familyname)
2. mx_dlb (year,weekyear,day,date,month integer,counterselloutvalue,counterselloutvolume,region,competitor,priceband,channel,segment,modelname,price"""

gr.ChatInterface(get_most_similar_question,chatbot=chatbot,fill_height=True,title=title,theme=gr.themes.Base(),
                 concurrency_limit=None,description=desc,
                 examples=["Hi", "What is the sales for yesterday",
                           "What is the sales for each region for GT channel yesterday","Sales of smart phone this year Diwali",
                           "What is competitor wise market share for month may and year 2024",
                           "top 5 performing competitors by sellout value under GT/MT channel for month may and year 2024",
                           "what is the market share of Samsung in EAST 1"],
                 css="footer{display:none !important}",autofocus=True,analytics_enabled=False).queue(max_size=30).launch(auth=authenticate_user, 
                                                                                                                         share=True,show_error=False,server_name= '0.0.0.0', server_port=7898)

# def add_text(history, text):
#       #message[list] is defined globally
#     history = history + [(text,'')]
#     messages = text
#     return history, ""

# with gr.Blocks() as demo:
    
#     radio = gr.Radio(value='gpt-3.5-turbo', choices=['gpt-3.5-turbo','gpt-4'], label='models')
#     chatbot = gr.Chatbot(value=[], elem_id="chatbot")
#     with gr.Row():
#         with gr.Column():
#             txt = gr.Textbox(
#                 show_label=False,
#                 placeholder="Enter text and press enter",
#             ) 
        

#     txt.submit(add_text, [chatbot, txt], [chatbot, txt], queue=False).then(
#             get_most_similar_question, inputs =[chatbot,],outputs = chatbot,)
            
# demo.queue().launch(auth=authenticate_user, share=True,show_error=False,server_name= '0.0.0.0', server_port=7898)

# # Define the examples
# examples = [
# "Hi", 
# "What is the sales for yesterday", 
# "What is the sales for each region for GT channel yesterday", 
# "What is competitor wise market share for month May and year 2024", 
# "show me SAMSUNG market share for month May and year 2024", 
# "top 5 performing competitors by sellout value under GT/MT channel for month May and year 2024", 
# "show me Samsung market share for above 40 k priceband for month May and year 2024"
# ]

# # Create a Markdown string to display examples
# markdown_examples = "\n".join([f"- {example}" for example in examples])

# # Prepare the layout with Gradio blocks
# with gr.Blocks(analytics_enabled=False) as interface:
# # Title
#     gr.Markdown(f"# {title}")


# # Chatbot area

#     chatbot = gr.Chatbot(show_copy_button=True,likeable=True,sanitize_html=True)
#     gr.ChatInterface(get_most_similar_question,
#                      chatbot=chatbot,
#                      title=title,
#                      theme=gr.themes.Base(),
#                      examples=["Hi", "What is the sales for yesterday", 
#                                "What is the sales for each region for GT channel yesterday",
#                                "What is competitor wise market share for month may and year 2024",
#                                "show me SAMSUNG market share for month may and year 2024",
#                                "top 5 performing competitors by sellout value under GT/MT channel for month may and year 2024",
#                                "show me Samsung market share for above 40 k priceband for month may and year 2024"],
#                      css="footer{display:none !important}",autofocus=True).queue().launch(auth=authenticate_user, share=True,show_error=False,server_name= '0.0.0.0', server_port=7898)

#     # Include insights checkbox
#     include_insights = gr.Checkbox(label="Include insights")

#     # Expandable/Collapsible examples
#     with gr.Accordion("Examples"):
#         gr.Markdown(markdown_examples)
#     # Bind submit actions





create a post api that will take user input and return the output in json format as per above code ,where userText is input and return the result in json format that can be later rendered as html using FLASK