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 gradio as gr
import datefinder
from queue import Queue
from nltk.stem import PorterStemmer
from sentence_transformers import SentenceTransformer
import os
import gc
import torch
from concurrent.futures import ThreadPoolExecutor

# Assuming CUDA is available and you want to use the first GPU
os.environ["CUDA_VISIBLE_DEVICES"] = "0"
torch.backends.cudnn.benchmark = True
torch.backends.cudnn.enabled = True




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


# new_retrieval_model_path = "NlpSql//model//"
# new_retrieval_model = hub.load(new_retrieval_model_path)
# NlpSql/model/saved_model.pb


conn_global = sqlite3.connect("sales_database.db",check_same_thread=False)
cursor_global = conn_global.cursor()

cursor_global.execute('''
     CREATE TABLE IF NOT EXISTS sales (
        salestype TEXT, --Type of sales like Sellin, SellThru, Sellout
        salescategory TEXT,--Sales Category like Normal, Demo, Free of Cost etc
        channel TEXT,--Sales Channel like General Trade, Modern Trade, Online, Business to Business or NA
        priceband TEXT, --Price bands of Product like Less than 6K, 6K to 10K, 10K to 15K, 15K to 20K, 20K to 30K, 30K to 40K, Greater than 40K etc
        region TEXT,--Sales Regions like Online, B2B and for GT the regions are East1, East2, West1, West2, West3, North1, North2, North3, North4, South1, South2, South3
        year INTEGER,--Year of sale
        month INTEGER,--Month of sale
        day INTEGER,--Day of sale
        date INTEGER,--Date of sale
        weekyear TEXT,--Week and Year of sale
        segment TEXT,--Segment of sale like Smatphone, Tablet, Note PC Laptop, Wearables
        model TEXT,--Product name or Market name or Model name
        quantity INTEGER,--Sales quantity
        amount INTEGER,--Sales amount in INR
        mtdquantity INTEGER,--Month till date Quantity
        mtdamount INTEGER,--Month till date Amount in INR
        lmtdquantity INTEGER,--Last Month till date quantity
        lmtdamount INTEGER--Last Month till date amount in INR
      );
    ''')

conn_global.commit()

sales_dlb_file = "mx sales 19mar.txt"
df_dlb = pd.read_csv(sales_dlb_file, sep='\t',encoding='cp1252')
df_dlb.to_sql("sales", conn_global, if_exists='replace', index=False)
df_dlb.head()

cursor_global.execute('''
    CREATE TABLE IF NOT EXISTS sales_dlb (
                     year INTEGER, -- year of sellout/sale,
                     week TEXT,--week of sellout,
                     month INteger,--month of sellout
                     counterselloutvalue INTEGER,-- value of sellout/amount
                     counterselloutvolume INTEGER,-- volume of sellout
                     region TEXT,-- region of sale
                     competitor TEXT,--competitor/competition name
                     priceband TEXT,-- price bands of different model/modelcode sold
                     salestype TEXT,-- salestype of the sale
                     channel TEXT,-- channel through which it is sold
                     status TEXT, -- status of sale
                     modelname TEXT,-- model name or model or market name
                     productgroup TEXT -- product group
        );

    ''')

						
conn_global.commit()

sales_dlb_file = "mx_dlb_26mar_1.txt"
df_dlb = pd.read_csv(sales_dlb_file, sep='\t')
df_dlb.to_sql("sales_dlb", conn_global, if_exists='replace', index=False)
df_dlb.head()


# Load LLM model
model_path = "weights/sql-weights"
tokenizer = AutoTokenizer.from_pretrained(model_path)
model = AutoModelForCausalLM.from_pretrained(model_path,trust_remote_code=True,
                        use_cache=True,)
device = torch.device('cuda' if torch.cuda.is_available() else 'cpu')
model = model.to(device)
model.eval()


# Executor for concurrent request handling
executor = ThreadPoolExecutor(max_workers=4)  # Adjust workers based on your system’s capabilities

# 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

def get_most_similar_question(userText):
    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.85:
        return predict(userText)
    
    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)
    html_output = f"<h3>Here is the most similar question for your query:</h3>{matched_data_df.to_html(index=False)}"
    
    torch.cuda.empty_cache()  # Clear unused memory
    gc.collect()  # Force garbage collection
    return html_output

# Define dummy users for authentication
dummy_users = [
    {"username": "jatin", "password": "qwer@345"},
    {"username": "ajoy", "password": "qwer@345"},
    {"username": "user3", "password": "pass3"},
    {"username": "user4", "password": "pass4"},
    {"username": "user5", "password": "pass5"}
]

# Authenticate user function
def authenticate_user(username, password):
    for user in dummy_users:
        if user["username"] == username and user["password"] == password:
            return True
    return False
porter_stemmer =PorterStemmer()
#--------------------------------------
def fn_preprocess_question(question):
    return ' '.join([porter_stemmer.stem(word) for word in question.split()])
dict_predefined_answers ={
    "What is the highest selling region?": "SELECT Region, Max(counterselloutval) from sales_dlb Group by region;",
    "hi":"I am SAMS bot",

}
#-----    
def predict(question):
    try:
#         preprocessed_question_var = fn_preprocess_question(question)
#         for predefined_question_var,predefined_answer_var in dict_predefined_answers.items():
#             if fn_preprocess_question(predefined_question_var) in preprocessed_question_var:
#                 return predefined_answer_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:
        Your task is convert a question into a SQL query, given a sqlite3 database schema.
        Adhere to these rules:
        - **Deliberately go through the question and database schema word by word** to appropriately answer the question
        - **Use Table Aliases** to prevent ambiguity. For example, `SELECT table1.col1, table2.col1 FROM table1 JOIN table2 ON table1.id = table2.id`.
        - When creating a ratio, always cast the numerator as int
        - Ensure that the SQL queries generated do not perform any typecasting and maintain the original data types,even during rounding off any value.Please adjust the queries accoridngly
        - While calculating contribution, always include '%' sign in the header
        - When asked for 'Market share' use this formula:
            market share of a product or competitor = 100 * sum(counterselloutvalue of that product)/sum(total aggregated countersellout value of all products)
        - Counter share means total countersellout value of that product/competitor and then divide it by toal of all other products/competitor
        ### 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 sales (
        salestype TEXT, --Type of sales like Sellin, SellThru, Sellout
        salescategory TEXT,--Sales Category like Normal, Demo, Free of Cost etc
        channel TEXT,--Sales Channel like General Trade, Modern Trade, Online, Business to Business or NA
        priceband TEXT, --Price bands of Product like Less than 6K, 6K to 10K, 10K to 15K, 15K to 20K, 20K to 30K, 30K to 40K, Greater than 40K etc
        region TEXT,--Sales Regions like Online, B2B and for GT the regions are East1, East2, West1, West2, West3, North1, North2, North3, North4, South1, South2, South3
        year INTEGER,--Year of sale
        month INTEGER,--Month of sale
        day INTEGER,--Day of sale
        date INTEGER,--Date of sale
        weekyear TEXT,--Week and Year of sale
        segment TEXT,--Segment of sale like Smatphone, Tablet, Note PC Laptop, Wearables
        model TEXT,--Product name or Market name or Model name
        quantity INTEGER,--Sales quantity
        amount INTEGER,--Sales amount in INR
        mtdquantity INTEGER,--Month till date Quantity
        mtdamount INTEGER,--Month till date Amount in INR
        lmtdquantity INTEGER,--Last Month till date quantity
        lmtdamount INTEGER,--Last Month till date amount in INR
      );

        CREATE TABLE IF NOT EXISTS sales_dlb (
                      year INTEGER, -- year of sellout/sale,
                     week TEXT,--week of sellout,
                     month INteger,--month of sellout
                     counterselloutvalue INTEGER,-- value of sellout/amount
                     counterselloutvolume INTEGER,-- volume of sellout
                     region TEXT,-- region of sale
                     competitor TEXT,--competitor/competition name
                     priceband TEXT,-- price bands of different model/modelcode sold
                     salestype TEXT,-- salestype of the sale
                     channel TEXT,-- channel through which it is sold
                     status TEXT, -- status of sale
                     modelname TEXT,-- market name
                     productgroup TEXT -- product group
        );
        
        ### I am mentioning some examples for actual user questions and their sql queries,you can use them or device your own query :
        
        question => "What is the highest selling region?":
        sql query =>"SELECT region, MAX(amount) AS highest_sales FROM sales GROUP BY region ORDER BY highest_sales DESC LIMIT 1;",
        question =>"What is the total sales quantity for last year?":
        sql query =>"SELECT SUM(quantity) FROM sales WHERE year = strftime('%Y', date('now', '-1 year'));",
        question =>"Which model has the highest sales amount?":
        sql query =>"SELECT model, MAX(amount) AS highest_sales FROM sales GROUP BY model ORDER BY highest_sales DESC LIMIT 1;",
        question =>"How many sales were completed online?":
        sql query => "SELECT COUNT() FROM sales WHERE channel = 'Online';",
        question =>"What is the average sales amount per region?":
        sql query =>"SELECT region, AVG(amount) FROM sales GROUP BY region;",
        question =>"What is the total sales quantity this month?":
        sql query =>"SELECT SUM(quantity) FROM sales WHERE month = strftime('%m', date('now')) AND year = strftime('%Y', date('now'));",
        question =>"Which price band has the most sales?":
        sql query =>"SELECT priceband, COUNT() AS count FROM sales GROUP BY priceband ORDER BY count DESC LIMIT 1;",
        question =>"List all sales types":
        sql query =>"SELECT DISTINCT salestype FROM sales;",
        question =>"What is the total sales volume for smartphones?":
        sql query =>"SELECT SUM(quantity) FROM sales WHERE segment = 'Smartphone';",
        question =>"Show top 5 sales channels by quantity":
        sql query =>"SELECT channel, SUM(quantity) AS total_quantity FROM sales GROUP BY channel ORDER BY total_quantity DESC LIMIT 5;",
        question =>"What is the total sales amount for each year?":
        sql query =>"SELECT year, SUM(amount) AS yearly_sales FROM sales GROUP BY year;",
        question =>"How many products sold in each price band?": 
        sql query =>"SELECT priceband, COUNT() AS product_count FROM sales GROUP BY priceband;",
        question =>"What is the least selling product?":
        sql query =>"SELECT model, MIN(quantity) FROM sales GROUP BY model ORDER BY MIN(quantity) LIMIT 1;",
        question =>"Show total sales by region and year":
        sql query =>"SELECT region, year, SUM(amount) FROM sales GROUP BY region, year;",
        question =>"What is the month with the highest sales volume?":
        sql query =>"SELECT month, SUM(quantity) AS total_quantity FROM sales GROUP BY month ORDER BY total_quantity DESC LIMIT 1;",
        question =>"List sales quantity for each segment":
        sql query =>"SELECT segment, SUM(quantity) FROM sales GROUP BY segment;",
        question =>"Show the sales trend for product X":
        sql query =>"SELECT month, SUM(quantity) FROM sales WHERE model = 'Product X' GROUP BY month;",
        question =>"Top selling product groups":
        sql query =>"SELECT productgroup, SUM(quantity) AS total_quantity FROM sales GROUP BY productgroup ORDER BY total_quantity DESC;",
        question =>"Average sales amount by channel":
        sql query =>"SELECT channel, AVG(amount) FROM sales GROUP BY channel;",
        question =>"Year over year sales growth":
        sql query =>"SELECT year, (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY year)) / LAG(SUM(amount)) OVER (ORDER BY year) * 100 AS growth_percentage FROM sales GROUP BY year;",
        question =>"Which competitor has the highest counterselloutvalue?":
        sql query =>"SELECT competitor, MAX(counterselloutvalue) FROM sales_dlb GROUP BY competitor ORDER BY MAX(counterselloutvalue) DESC LIMIT 1;",
        question =>"What is the contribution of each sales type to the total sales?":
        sql query =>"SELECT salestype, SUM(amount) / (SELECT SUM(amount) FROM sales) * 100 AS contribution_percentage FROM sales GROUP BY salestype;",
        question =>"Show sales distribution by price band for online channel":
        sql query =>"SELECT priceband, COUNT() FROM sales WHERE channel = 'Online' GROUP BY priceband;",
        question =>"Number of sales transactions each month this year":
        sql query =>"SELECT month, COUNT(*) FROM sales WHERE year = strftime('%Y', date('now')) GROUP BY month;",
        question =>"Which model has the lowest mtdamount this quarter?":
        sql query =>"SELECT model, MIN(mtdamount) FROM sales WHERE month BETWEEN strftime('%m', date('now', 'start of month', '-2 month')) AND strftime('%m', date('now')) GROUP BY model ORDER BY MIN(mtdamount) LIMIT 1;",
        question =>"Total sales by competitors":
        sql query =>"SELECT competitor, SUM(counterselloutvalue) FROM sales_dlb GROUP BY competitor;",
        question =>"List top 3 regions by mtdquantity in current year":
        sql query =>"SELECT region, SUM(mtdquantity) FROM sales WHERE year = strftime('%Y', date('now')) GROUP BY region ORDER BY SUM(mtdquantity) DESC LIMIT 3;",
        question =>"What is the growth percentage of wearables segment from last year?":
        sql query =>"SELECT (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY year)) / LAG(SUM(amount)) OVER (ORDER BY year) * 100 AS growth_percentage FROM sales WHERE segment = 'Wearables' GROUP BY year;"
        question =>"What is the highest selling region?":
        sql query =>"SELECT region, MAX(amount) AS highest_sales FROM sales GROUP BY region ORDER BY highest_sales DESC LIMIT 1;",
        question =>"What is the total sales quantity for last year?":
        sql query =>"SELECT SUM(quantity) FROM sales WHERE year = strftime('%Y', date('now', '-1 year'));",
        question =>"Which model has the highest sales amount?":
        sql query =>"SELECT model, MAX(amount) AS highest_sales FROM sales GROUP BY model ORDER BY highest_sales DESC LIMIT 1;",
        question =>"How many sales were completed online?":
        sql query =>"SELECT COUNT() FROM sales WHERE channel = 'Online';",
        question =>"What is the average sales amount per region?":
        sql query =>"SELECT region, AVG(amount) FROM sales GROUP BY region;",
        
        ### Response:
        Based on your instructions, here is the SQL query I have generated to answer the question `{question}`:
        ```sql
        """.format(question=question)

        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.001,
            do_sample=True
        )


        outputs = tokenizer.batch_decode(generated_ids, skip_special_tokens=True)

        generated_sql = outputs[0].split("```sql")[-1].split("```")[0].split(";")[0].strip()
        print(generated_sql)
        generated_sql = generated_sql.replace('::numeric','')

        cursor_local.execute(generated_sql)
        result = cursor_local.fetchall()
#         print(result)
        cursor_local.close()
        conn_local.close()
#         for row in result:
#             print(row)
        df = pd.DataFrame(result,columns=[desc[0] for desc in cursor_local.description])
        sql_datagram = 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)

#         df_final = df + dff
    #     df_str = df.to_string(index=False)
        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>"
        
        return html_output_1
    except Exception as e:
        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."
def gradio_interface(userText,history):
    future = executor.submit(get_most_similar_question, userText)
    return future.result()
    
gr.ChatInterface(gradio_interface).launch(auth=authenticate_user,inline=True, share=True,server_name= '0.0.0.0', server_port=7898)



I need to maintain chat history so that back and forth answering can be done