import pandas as pd
import sqlite3
import gradio as gr
import matplotlib.pyplot as plt


DB_PATH = "traffic_analytics.db"


# --------------------------------------------------
# Load Analytics Data
# --------------------------------------------------

def load_data():

    conn = sqlite3.connect(DB_PATH)

    query = """
    SELECT *
    FROM vehicle_records
    """

    df = pd.read_sql(query, conn)

    conn.close()

    return df


# --------------------------------------------------
# Calculate Metrics
# --------------------------------------------------

def calculate_metrics():

    try:

        df = load_data()

        total_vehicles = len(df)

        ev_count = len(
            df[df["is_ev"] == 1]
        )

        non_ev_count = total_vehicles - ev_count

        ev_rate = (
            ev_count / total_vehicles * 100
            if total_vehicles > 0
            else 0
        )

        total_toll = df["original_toll"].sum()

        toll_discount = (
            df["toll_discount_amount"].sum()
        )

        total_parking = (
            df["original_parking"].sum()
        )

        parking_discount = (
            df["parking_discount_amount"].sum()
        )

        govt_incentive = (
            df["government_incentive"].sum()
        )

        avg_green_score = (
            df["green_score"].mean()
        )

        avg_processing_time = (
            df["processing_time"].mean()
            if "processing_time" in df.columns
            else 0
        )

        return {
            "Total Vehicles":
                total_vehicles,

            "EV Vehicles":
                ev_count,

            "Non-EV Vehicles":
                non_ev_count,

            "EV Adoption Rate (%)":
                round(ev_rate, 2),

            "Total Toll Revenue":
                round(total_toll, 2),

            "Toll Discounts":
                round(toll_discount, 2),

            "Total Parking Revenue":
                round(total_parking, 2),

            "Parking Discounts":
                round(parking_discount, 2),

            "Govt Incentives":
                round(govt_incentive, 2),

            "Avg Green Score":
                round(avg_green_score, 2),

            "Avg Processing Time":
                round(avg_processing_time, 2)
        }

    except Exception as e:

        return {
            "Error": str(e)
        }


# --------------------------------------------------
# Create Charts
# --------------------------------------------------

def create_charts():

    df = load_data()

    # EV vs Non-EV
    plt.figure(figsize=(5, 4))

    counts = [
        len(df[df["is_ev"] == 1]),
        len(df[df["is_ev"] == 0])
    ]

    plt.bar(
        ["EV", "Non-EV"],
        counts
    )

    plt.title("Vehicle Distribution")

    vehicle_chart = "vehicle_distribution.png"

    plt.savefig(vehicle_chart)

    plt.close()

    # Savings Chart

    plt.figure(figsize=(5, 4))

    savings = [
        df["toll_discount_amount"].sum(),
        df["parking_discount_amount"].sum()
    ]

    plt.bar(
        ["Toll Savings",
         "Parking Savings"],
        savings
    )

    plt.title("Savings Distribution")

    savings_chart = "savings_distribution.png"

    plt.savefig(savings_chart)

    plt.close()

    return vehicle_chart, savings_chart


# --------------------------------------------------
# Dashboard Refresh
# --------------------------------------------------

def refresh_dashboard():

    metrics = calculate_metrics()

    metric_text = ""

    for key, value in metrics.items():

        metric_text += (
            f"{key}: {value}\n"
        )

    chart1, chart2 = create_charts()

    return metric_text, chart1, chart2


# --------------------------------------------------
# Gradio UI
# --------------------------------------------------

with gr.Blocks() as demo:

    gr.Markdown(
        "# 🚦 Smart Traffic & EV Analytics Dashboard"
    )

    refresh_btn = gr.Button(
        "Refresh Dashboard"
    )

    metrics_box = gr.Textbox(
        label="Evaluation Metrics",
        lines=15
    )

    vehicle_chart = gr.Image(
        label="EV vs Non-EV Vehicles"
    )

    savings_chart = gr.Image(
        label="Discount Savings"
    )

    refresh_btn.click(
        fn=refresh_dashboard,
        inputs=[],
        outputs=[
            metrics_box,
            vehicle_chart,
            savings_chart
        ]
    )

demo.launch()