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"
            params.append(status)

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

        if records:
            # Enclose each record in a stylish div
            details_html = "<div style='display: flex; flex-direction: column; gap: 10px;'>"
            for record in records:
                details_html += f"<div style='border: 1px solid #ccc; padding: 10px; margin-top: 5px; border-radius: 5px;'><table>"
                for column, value in record.items():
                    details_html += f"<tr><td><strong>{column}:</strong></td><td>{value}</td></tr>"
                details_html += "</table></div>"
            details_html += "</div>"
        else:
            details_html = "<p>No matching records found.</p>"

    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

# Modify the plot_status_pie_chart function to handle a new 'Combined' option.
def plot_status_pie_chart():
    try:
        connection = mysql.connector.connect(
            host=‘localhost’,
            database=‘records’,
            user=‘root’,
            password=‘Nikki@1234’
        )
        cursor = connection.cursor()

        # Query for the ‘Submitted’ and ‘Ordered’ statuses only.
        status_query = “”“
        SELECT status, COUNT(PR_Number) as Total
        FROM PR_Details
        WHERE status IN (‘Submitted’, ‘Ordered’)
        GROUP BY status
        ORDER BY Total DESC
        “””
        cursor.execute(status_query)
        result = cursor.fetchall()

        labels = [row[0] for row in result]
        sizes = [row[1] for row in result]
        colors = [‘gold’, ‘lightcoral’][:len(result)]  # Two colors for ‘Submitted’ and ‘Ordered’

        plt.figure(figsize=(8, 6))
        plt.pie(sizes, labels=labels, autopct=‘%1.1f%%’, startangle=140, colors=colors)
        plt.axis(‘equal’)
        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, ‘Database error occurred’, fontsize=12, ha=‘center’)
        pie_chart = plt.gcf()
        plt.close()
        return pie_chart
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

# Update the update_outputs function to correctly handle status changes and fetch details
def update_outputs(pr_number, status):
    details = fetch_data(pr_number, status)
    pie_chart = None  # Will remain None unless ‘Combined’ status is selected.
    
    # If ‘Combined’ is selected, generate the pie chart for ‘Submitted’ and ‘Ordered’
    if status == ‘Combined’:
        pie_chart = plot_status_pie_chart()
    
    return details, (pie_chart or “Select ‘Combined’ to view the pie chart.”)  # Add a placeholder message when pie_chart is None.



with gr.Blocks() as app:
    with gr.Tab(“PR Details and Pie Chart”):
        pr_number_input = gr.Textbox(label=“PR Number”, placeholder=“Enter PR Number…”, value=”")
        status_input = gr.Radio(choices=[‘Submitted’, ‘Ordered’, ‘Composing’, ‘Combined’], label=“Status”, value=‘Submitted’)
        details_output = gr.HTML(label=“Details”)
        pie_chart_output = gr.Plot(label=“Status Pie Chart”, visible=False)  # Initially not visible

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

        # Binding the function to the ‘Fetch PR Details’ button click
        fetch_btn.click(
            update_outputs,
            inputs=[pr_number_input, status_input],
            outputs=[details_output, pie_chart_output]
        )

        # Binding the function to the ‘Clear’ button click
        clear_btn.click(
            clear_form,
            inputs=[],
            outputs=[pr_number_input, status_input, details_output, pie_chart_output]
        )

        # Modify visibility of the pie_chart_output when ‘Combined’ is selected
        def toggle_pie_chart_visibility(status):
            # Make the plot visible only when ‘Combined’ is selected
            pie_chart_output.visible = status == “Combined”
            return status == “Combined”  # Return value is a placeholder and not used in the interface.

        # Modify the behavior when the radio buttons value changes
        status_input.change(
            toggle_pie_chart_visibility,
            inputs=[status_input],
            outputs=[]
        )

app.launch(share=True)