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


def fetch_data(pr_number, status):
    """
    Fetch data from the database based on PR number and status.

    Args:
    - pr_number (str): PR number to filter the records.
    - status (str): Status to filter the records.

    Returns:
    - str: HTML representation of fetched data or an error message.
    """
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='records',
            user='root',
            password='Nikki@1234'  # Use your actual password and manage it securely
        )

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

        if pr_number:
            query += " AND PR_Number = %s"
            params.append(pr_number)
        if status:
            query += " AND Current_Status = %s"
            params.append(status)

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

        if records:
            details_html = "<div class='pr_record'><table>"
            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():
    """
    Plot a pie chart based on the count of different PR statuses.

    Returns:
    - matplotlib.figure.Figure: Pie chart figure.
    """
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='records',
            user='root',
            password='Nikki@1234'  # Placeholder for your password
        )
        if connection.is_connected():
            cursor = connection.cursor()
            cursor.execute("SELECT Current_Status, COUNT(Current_Status) FROM PR_Details GROUP BY Current_Status")
            result = cursor.fetchall()

            labels = [i[0] for i in result]
            sizes = [i[1] for i in result]

            cursor.close()
            connection.close()

            plt.figure(figsize=(8, 6))
            plt.pie(sizes, labels=labels, autopct='%1.1f%%', startangle=140)
            plt.axis('equal')
            return plt.gcf()
    except Error as e:
        print(f"Database Error: {e}")
        return plt.figure()


# Use the Blocks interface to layout multiple components
with gr.Blocks() as app:
    gr.Markdown("# Purchase PO Automation Management System")

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

            # Define the callback for fetching PR details
            def fetch_details(pr_number, status):
                details_output.html("Fetching details...")  # Indicate to the user that data is being fetched
                return fetch_data(pr_number, status)

            details_btn = gr.Button("Fetch PR Details")
            details_btn.click(
                fetch_details,
                inputs=[pr_number_input, status_input],
                outputs=details_output
            )

        with gr.TabItem("Live Pie Chart"):
            # Define the pie chart output
            pie_chart_output = gr.Plot(label="Status Pie Chart")

            # Function to be called at each interval for updating the pie chart
            def live_update():
                live_pie_chart = plot_pie_chart()
                pie_chart_output.update(live_pie_chart)

            # Setup an Interval component to trigger the live_update function
            gr.Interval(live_update, seconds=5)

# Launch the app
app.launch(share=True)