import os 
import aiohttp
import asyncio
import requests
from fastapi import FastAPI, HTTPException
import openai
from typing import Optional


# Initialize FastAPI app
app = FastAPI()

# Set your OpenAI API key here
openai.api_key = os.environ["OPENAI_API_KEY"]

# Authorization headers
AUTH_HEADERS = {
    'X-Tenant': 'royalexpress',
    'Accept': 'application/json',
    'Authorization': 'Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJhdWQiOiIxIiwianRpIjoiYjczMDAxYzMyMTE1ZGIyNTY4ODUzMTg2OWVjMzUwNTdjZmE3YTJmN2I3ZWZjMjQzNjdmZTA2ZTk4ZjY4ZjMwMTE0ZTIzOGUwY2I0ZmQ0YjIiLCJpYXQiOjE3MzA3ODIxNjEuMjkyNDM0LCJuYmYiOjE3MzA3ODIxNjEuMjkyNDM3LCJleHAiOjQ4ODY0NTU3NjEuMDc2ODExLCJzdWIiOiIxIiwic2NvcGVzIjpbXX0.I4wGFzoepmAC2RaADetE95BdbY4AYPUfUouFepVthZq_KewQLoYEiYMmxErgAOvYDL9IdhTg8pHm3KtCjtfF79Toigvzl-4RIYE9qwavVYabUaIMtxdkvLmzC2uSSxNkQ-Jx4ZsEVt34NpMMZ6ZsMsgszkreed_s7i5I6ek6T2-p9cZYPpFfGhlRIrgAhOL1yZe0t5HQMM7P1cULB7IMb3s0fvwLNBimPC4Iznick5o2lWO6KcubsKSAMyPwBaCQhjGTKd0eJCde1IvL8mEaMvhu8v853AIDSiBsC83hjK41hPAaiBHeev1JjdDhEd6_qO9dpucKaGCqYiVfBFH_pgnynErmhKlPEIz7sZlBWz8zxISDW5PRo9d-jXRP-A31W76Q3H-ZKfnam0D8yYFY0EIZHhvgvZUl3r0dR4PRh7PYlNZgnyfAcAYmK9Bektjbbx5RuzH6gtT9hLQrxYiQNg0irCNwgTYnuQ4AjPA3BpZuOfWtygeDZKgv1gnveTzMJG7T6s95k8yNSNT1_OfRQONPX8LBasRwZWCGkWj7fopO6K8gcrEU5FIpql0UviwGJOTZeFmqwWJ1AIcOM0MHWNp--Y8evHrvuNGk3SDcjBcvhF58I2Hd5F4MefN_ZB9N7oxUUDBYbxnTH6SN7Wx-VsluEOlf9ShfBNvHZaUi61E'
}


#db managments section
#get all data
import mysql.connector
from datetime import datetime

# Define the connection parameters
host = "68.183.225.237"
user = "sm_ml"
password = "Fz6/I733"
database = "sm_qa_1"



def delete_tread(tread_id_to_delete):
    # Connect to the database
    connection_load_chat = mysql.connector.connect(
        host=host,
        user=user,
        password=password,
        database=database
    )
    
    cursor = connection_load_chat.cursor()
    
    # SQL query to delete a row from curfox_chat based on the tread ID
    delete_query = "DELETE FROM curfox_chat WHERE tread = %s"
    
    # Specify the thread ID to delete
    #tread_id_to_delete = 1
    
    # Execute the delete query
    cursor.execute(delete_query, (tread_id_to_delete,))
    
    # Commit the transaction
    connection_load_chat.commit()
    print(f"Thread ID {tread_id_to_delete} deleted successfully.")
    
    # Close the cursor and connection
    cursor.close()
    connection_load_chat.close()
    return {"message":f"Thread ID {tread_id_to_delete} deleted successfully."}
    


def get_treadlist_of_user(user_name):
    # Connect to the database
    connection_load_chat = mysql.connector.connect(
        host=host,
        user=user,
        password=password,
        database=database
    )
    
    cursor = connection_load_chat.cursor()
    #user_name = 'John Doe'
    # SQL query to select all columns from curfox_chat
    select_query = f"SELECT * FROM curfox_chat WHERE user_name='{user_name}'"
    
    # Execute the select query
    cursor.execute(select_query)
    
    # Fetching all rows from the result
    rows = cursor.fetchall()
    # Close the cursor and connection
    cursor.close()
    connection_load_chat.close()
    import json
    # Displaying the chat data
    tread_list = []
    for row in rows:
        try:
          js = json.loads(f'[{row[2]}]')
          tread_list.append({"user_name":row[0],
                "thread_id":row[1],
                "chat_title":js[0]['user']['message']
                })
        except:
          pass
    
    return {"tread_list":tread_list,"message":"done"}
    
    

def get_messages_of_thread(thread_id):
    # Connect to the database
    connection_load_chat = mysql.connector.connect(
        host=host,
        user=user,
        password=password,
        database=database
    )
    
    cursor = connection_load_chat.cursor()
    
    # SQL query to select all columns from curfox_chat
    select_query = f"SELECT * FROM curfox_chat WHERE tread={thread_id}"
    
    # Execute the select query
    cursor.execute(select_query)
    
    # Fetching all rows from the result
    rows = cursor.fetchall()
    # Close the cursor and connection
    cursor.close()
    connection_load_chat.close()
    
    import json
    # Displaying the chat data
    for row in rows:
        js = json.loads(f'[{row[2]}]')
        return ({"user_name":row[0],
               "thread_id":row[1],
               "chat_title":js[0]['user']['message'],
               "chat_list":js})
    

def insert_newchat(user_name,user_message,bot_message):
    bot_message = bot_message.replace('"', '\\"')
    #insert data
    # Connect to the database
    connection_load_chat = mysql.connector.connect(
        host=host,
        user=user,
        password=password,
        database=database
    )
    
    cursor = connection_load_chat.cursor()
    
    # SQL query to insert a row into curfox_chat table
    insert_query = """
    INSERT INTO curfox_chat (user_name, conversation)
    VALUES (%s, %s)
    """

    created_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # Current timestamp
    conversation = f'{{"user": {{"message":"{user_message}","time":"{created_at}"}} , "bot": {{"message":"{bot_message}","time":"{created_at}"}}}}'
    
    # Execute the insert query with data
    cursor.execute(insert_query, (user_name, conversation))
    
    # Commit the transaction
    connection_load_chat.commit()
    print("Data inserted successfully")
    
    # Close the cursor and connection
    cursor.close()
    #connection_load_chat.close()
    
    cursor = connection_load_chat.cursor()
    sql_query = f"SELECT tread from curfox_chat WHERE user_name='"+user_name+"' ORDER BY tread DESC LIMIT 1"
    
    cursor.execute(sql_query)
    current_thread_id = cursor.fetchone()[0]
    print(current_thread_id)
    return {"current_thread_id":current_thread_id,"message":"chat stored successfully"}

def update_existingchat(tread_id,user_message,bot_message):
    bot_message = bot_message.replace('"', '\\"')
    # Connect to the database
    connection_load_chat = mysql.connector.connect(
        host=host,
        user=user,
        password=password,
        database=database
    )
    
    cursor = connection_load_chat.cursor()
    
    # SQL query to update the conversation column
    update_query = """
    UPDATE curfox_chat
    SET conversation = CONCAT(conversation, %s)
    WHERE tread = %s
    """

    created_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # Current timestamp
    new_conversation = f', {{"user": {{"message":"{user_message}","time":"{created_at}"}} , "bot": {{"message":"{bot_message}","time":"{created_at}"}}}}'
    
    # Execute the update query with data

    cursor.execute(update_query, (new_conversation, tread_id))
    
    # Commit the transaction
    connection_load_chat.commit()
    print(f"Conversation for thread ID {tread_id} updated successfully.")
    
    # Close the cursor and connection
    cursor.close()
    connection_load_chat.close()
    return {"current_thread_id":tread_id,"message":f"Conversation for thread ID {tread_id} updated successfully."}



# Helper function to get the order ID from the waybill number
def get_order_id(waybill_no):
    url = f"https://dev7.api.curfox.parallaxtec.com/api/ml/order/{waybill_no}"
    response = requests.get(url, headers=AUTH_HEADERS)
    if response.status_code == 200:
        return response.json().get("data", {}).get("id")
    else:
        raise HTTPException(status_code=404, detail="Order ID not found")

# Asynchronous function to fetch data concurrently
async def fetch_data(session, url):
    async with session.get(url, headers=AUTH_HEADERS) as response:
        return await response.text()

# Asynchronous function to get all order data
async def get_all_order_data(order_id):
    urls = [
        f"https://dev7.api.curfox.parallaxtec.com/api/ml/order/{order_id}/time-line",
        f"https://dev7.api.curfox.parallaxtec.com/api/ml/order/{order_id}/finance-time-line",
        f"https://dev7.api.curfox.parallaxtec.com/api/ml/order/{order_id}/invoice-time-line"
    ]

    async with aiohttp.ClientSession() as session:
        tasks = [fetch_data(session, url) for url in urls]
        responses = await asyncio.gather(*tasks)

    full_data = {
        "Timeline Details": responses[0],
        "Finance Time Line": responses[1],
        "Invoice Time Line": responses[2]
    }
    return full_data

# Function to interact with OpenAI API
def ask_openai(messages):
    response = openai.ChatCompletion.create(model="gpt-4o-mini", messages=messages)
    return response.choices[0].message['content']





# Main endpoint to handle user queries
@app.post("/get_response")
async def process_query(query: str,
                       user_name:str,
                       thread_id:Optional[str] = None):
    # Initial message to check for waybill number in the query
    messages = [
        {"role": "system", "content": "You are a helpful assistant for Curfox delivery system."},
        {"role": "user", "content": f"""always check user query mentioned the waybill number/id (example : CA000001)
                                   if waybill number/id provided then output is only : "done"
                                   if user not given the waybill number then ask the number from user (include or response to greetings)
                                   user query : {query}""" }
    ]
    result = ask_openai(messages)
    print(result)
    if result == "done":
        # # Extract the waybill number
        # extract_message = [
        #     {"role": "system", "content": "You are a helpful assistant for Curfox delivery system."},
        #     {"role": "user", "content": f"""extract the waybill number from the user query example output is only number : CA000001
        #                             user query : {query}""" }
        # ]
        # waybill_number = ask_openai(extract_message)
        # print("waybill number",waybill_number)
        import re
                
        # Regular expression to extract the waybill number (e.g., CA followed by digits)
        pattern = r'\bCA\d{6}\b'
        
        # Search for the pattern in the query
        match = re.search(pattern, query)
        
        # Extract the waybill number if found
        if match:
            waybill_number = match.group(0)
            print(waybill_number)
            # Fetch order ID and order details
            try:
                order_id = get_order_id(waybill_number)
                full_data = await get_all_order_data(order_id)
                # Generate final response based on collected data
                response_message = [
                    {"role": "system", "content": "You are a helpful assistant for Curfox delivery system."},
                    {"role": "user", "content": f"Answer based on the provided data only. Data: {full_data}. User query: {query}"}
                ]
                result = ask_openai(response_message)
            except Exception as e:
                result = str(e)
        else:
            print("Waybill number not correct.")
            result = "Waybill number not correct."




        
    if thread_id==None:
        return {"meta_data":insert_newchat(user_name,query,result),"answer": result}
    else:
        return {"meta_data":update_existingchat(thread_id,query,result),"answer": result}


@app.post("/get_messagesOfThread")
async def get_thread_chats(thread_id:str):
    return get_messages_of_thread(thread_id)


@app.post("/get_thread_lists")
async def get_thread_lists(user_name:str):
    return get_treadlist_of_user(user_name)

@app.post("/delete_thread")
async def get_thread_chats(tread_id_to_delete:str):
    return delete_tread(tread_id_to_delete)
# Start the FastAPI app
# Run the app on Colab using Uvicorn
# import uvicorn
# uvicorn.run(app, host="0.0.0.0", port=8000)