import gradio as gr
import mysql.connector
from mysql.connector import Error
import matplotlib.pyplot as plt


def clear_form():
    return "", "Submitted"  # Default values


def fetch_data(pr_number, status):
    details_html = ''
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='records',
            user='root',
            password='Nikki@1234'  # Placeholder: Use a secure method to store and use passwords
        )

        query = "SELECT * FROM PR_Details WHERE 1=1"
        params = []

        if pr_number:
            query += " AND PR_Number = %s"
            params.append(pr_number)
        if status:
            query += " AND status = %s"  # Correct column name
            params.append(status)

        cursor = connection.cursor(dictionary=True)
        cursor.execute(query, params)
        records = cursor.fetchall()

        details_html = "<div class='pr_record'><table>"
        if records:
            for record in records:
                for column, value in record.items():
                    details_html += f"<tr><td><strong>{column}:</strong></td><td>{value}</td></tr>"
            details_html += "</table></div><br>"
        else:
            details_html = "No matching records found."

    except Error as e:
        print(f"Database Error: {e}")
        details_html = "Failed to fetch data due to a database error."
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

    return details_html


def plot_pie_chart():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='records',
            user='root',
            password='Nikki@1234'  # Placeholder: Use a secure method to store and use passwords
        )

        status_query = """
        SELECT status, COUNT(PR_Number) as Total
        FROM PR_Details
        WHERE status IN ('Submitted', 'Ordered', 'Composing')
        GROUP BY status
        ORDER BY status
        """

        cursor = connection.cursor()
        cursor.execute(status_query)
        result = cursor.fetchall()

        if result:
            labels = [row[0] for row in result]
            sizes = [row[1] for row in result]

            plt.figure(figsize=(8, 6))
            plt.pie(sizes, labels=labels, autopct='%1.1f%%', startangle=140)
            plt.axis('equal')
            pie_chart = plt.gcf()
            plt.close()  # Close the plot to prevent it from displaying in the background
            return pie_chart
        else:
            print("No data to display in the pie chart.")
            plt.figure(figsize=(8, 6))
            plt.text(0.5, 0.5, 'No data available', fontsize=12, ha='center')
            pie_chart = plt.gcf()
            plt.close()
            return pie_chart

    except Error as e:
        print(f"Database Error: {e}")
        plt.figure(figsize=(8, 6))
        plt.text(0.5, 0.5, 'Failed to fetch data', fontsize=12, ha='center')
        pie_chart = plt.gcf()
        plt.close()
        return pie_chart
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()


with gr.Blocks() as app:
    with gr.Tabs():
        with gr.Tab("PR Details"):
            pr_number_input = gr.Textbox(label="PR Number", placeholder="Enter PR Number…", value="")
            status_input = gr.Radio(choices=['Submitted', 'Ordered', 'Composing'], label="Status", value='Submitted')
            details_output = gr.HTML(label="Details")

            fetch_btn = gr.Button("Fetch PR Details")
            clear_btn = gr.Button("Clear")

            fetch_btn.click(
                fetch_data,  # fetch_data function will be executed when the button is clicked
                inputs=[pr_number_input, status_input],
                outputs=details_output
            )

            clear_btn.click(
                clear_form,  # clear_form function will be executed when the button is clicked
                inputs=[],
                outputs=[pr_number_input, status_input]
            )

        with gr.Tab("Live Pie Chart"):
            pie_chart_output = gr.Plot(label="Status Pie Chart")

            # Using plot_pie_chart directly in gr.update
            gr.update(
                fn=plot_pie_chart,  # Using plot_pie_chart function to update the live pie chart
                output=pie_chart_output,
                interval=5  # Every 5 seconds
            )

    app.launch(share=True)  # Run the Gradio app


When I am giving the pr number or slecting any status radio button it is fetching the details in the vertical table but after fetching one PR row details there is no gap they were all combined. Please keep them in boxes.