app.py

import gradio as gr
from PIL import Image, ImageFilter
import os 
from txt2sql import SQLPromptModel
from gradio import Button
import time
import google.generativeai as genai
from rag import TicketResolver

image = Image.open(os.path.join(os.path.abspath(''), "ticket_database.jpg"))

def image_display(steps=0):
    return image

query = None
rows = None
columns = None
user_choices = None

def execute_sql_query(input_prompt):
    global query, rows, columns, user_choices
    
    model_dir = "multi_table_demo/checkpoint-2600"
    database = r"ticket_dataset.db"
    sql_model = SQLPromptModel(model_dir, database)

    user_prompt = "Select all rows ,Ticket priority High , Ticket status Open tasks"
    for _ in range(3):  # Retry logic, try 3 times
        try:
            table_schema = sql_model.fetch_table_schema("ticket_dataset")
            if table_schema:
                description_index = [index for index, column_name, *_ in table_schema if column_name == 'Ticket_Description'][0]
                type_index=[index for index, column_name, *_ in table_schema if column_name == 'Ticket_Type'][0]
                print(type_index)
                if input_prompt.strip():  
                    # query = sql_model.text2sql(table_schema, user_prompt, input_prompt)
                    # query = sql_model.text2sql_chatgpt(table_schema, user_prompt, input_prompt)
                    query = sql_model.text2sql_gemini(table_schema, user_prompt, input_prompt)
                else:
                    # query = sql_model.text2sql(table_schema, user_prompt, user_prompt)
                    # query = sql_model.text2sql_chatgpt(table_schema, user_prompt, user_prompt)
                    query = sql_model.text2sql_gemini(table_schema, user_prompt, user_prompt)
                rows, columns = sql_model.execute_query(query)
                print(rows)
                user_choices = {}
                for row in rows:
                    description = row[description_index]
                    ticket_type = row[type_index] 
                    print(ticket_type)
                    user_choices[description] = ticket_type
                return rows
            else:
                print("Table schema not found.")
                return None
        except Exception as e:
            print(f"An error occurred: {e}")
            print("Retrying...")
            time.sleep(1) 
    return None  

def ticket_resolver(input,ticket_type):
    csv_path = "ticket_dataset.csv"
    output_string = ""
    if ticket_type is not None:
        ticket_resolver = TicketResolver(csv_path)
        if input is not None:
            query=input
        else:
            query = "Server out of memory"
        issue_resolution_pairs,resolutions = ticket_resolver.find_relevant_issues(query,ticket_type)
        final_resolution = ticket_resolver.generate_resolution(query,resolutions)
        print("Final resolution:", final_resolution)

        output_string += f"Ticket Type :{ticket_type} \n"
        for issue, resolution in issue_resolution_pairs:
            output_string += f"Issue: {issue}\nResolution: {resolution}\n\n"
        output_string += f"Final Resolution: {final_resolution}"
        
        print(output_string) 
    return output_string

def update_choices(nothing):
    print("callback called")
    print("user_choices",user_choices)
    if user_choices:
        return gr.Dropdown(choices=list(user_choices.keys()), label="Ticket Choice", info="List of all tickets", interactive=True)
    else:
        return gr.Dropdown(choices=[], label="Ticket Choice",info="List of all tickets",interactive=True)

# def ticket_resolver_interface(description):
#     ticket_type = user_choices[description]
#     return ticket_resolver(description, ticket_type)
    
def ticket_resolver_interface(description):
    print("description",description)
    if not description:
        print("description is None or falsy")
        return ""  
    elif description in user_choices:
        print("description available")
        ticket_type = user_choices[description]
        return ticket_resolver(description, ticket_type)
    else:
        print("description is not None but not in user_choices")
        return "Please select a ticket description or reset the selection."

user_dropdown=gr.Dropdown(choices=[], label="Ticket Choice",info="List of all tickets")
properties_text=gr.components.Textbox(lines=2,label="User Database Query",placeholder="Click on an query from 'examples' below or write your own query based on the database above. Default : 'High priority Open tasks'")
interface_1_output=gr.Json(label="json")
stage2_text=gr.components.Textbox(lines=2,label="Question on database",placeholder="Enter a question to know more about related resolved queries , you can write a own question Default: 'Server OOM'",)
stage2_output=gr.Text(label="Resolution",lines=25)

with gr.Blocks(title="Ticket Excel Query") as demo: 

    gr.Markdown("# Knowledge Model")
    generated_image = image_display()
    gr.Image(generated_image)
    gr.Markdown("""### The database provided contains information about different Issues and Resolutions, including their fundamental details. 
                \n ### Once the data (extracted rows) is retrieved based on the query, you can utilize the user interface (UI) below to retrieve similarly resolved issues.  You will receive an generic answer based on the available information.""")

    interface_1 = gr.Interface(
        execute_sql_query,
        inputs=properties_text,
        # "textbox",
        outputs=interface_1_output,
        # live=True,
        # cache_examples=["Give me all details of properties from India"],
        examples=["Urgent priority Open tasks "," Technical Issue Open tasks","High priority Open tasks"],
    )
   
    interface_2 = gr.Interface(
        ticket_resolver,
        inputs=user_dropdown,
        # inputs=[gr.Dropdown.change(fn=update_choices),gr.components.Textbox(lines=2,label="Question on property",placeholder="Enter a question to know more about the properties")],
        outputs=stage2_output,
        live=True,
        # examples=["Server out of memory","Wifi connection issues","OS boot failure","Procedure to apply leave requests"]
        
    )

    # gr.Examples(["How many floors does the property have "," Total square feet of the property " ," Total area of the property"],inputs=stage2_text,outputs=stage2_output,fn=qa_infer_interface)
    
    properties_text.change(update_choices,inputs=[properties_text],outputs=[user_dropdown])
    interface_1_output.change(update_choices,inputs=[interface_1_output],outputs=[user_dropdown])
    user_dropdown.change(fn=ticket_resolver_interface, inputs=[user_dropdown], outputs=[stage2_output])

if __name__ == "__main__":
    demo.launch(debug=True)#,share=True)