import streamlit as st
import pandas as pd
import sqlite3

# Establish database connection (create if it doesn't exist)
conn = sqlite3.connect('dispatch_data.db')
cursor = conn.cursor()

# Create tables if they don't exist
cursor.execute('''
    CREATE TABLE IF NOT EXISTS drivers (
        driver_id TEXT PRIMARY KEY,
        name TEXT,
        location TEXT,
        status TEXT
    )
''')
cursor.execute('''
    CREATE TABLE IF NOT EXISTS orders (
        order_id TEXT PRIMARY KEY,
        pickup_location TEXT,
        dropoff_location TEXT,
        status TEXT
    )
''')
cursor.execute('''
    CREATE TABLE IF NOT EXISTS zone_pressure (
        zone_id TEXT PRIMARY KEY,
        pressure_level INTEGER
    )
''')
cursor.execute('''
    CREATE TABLE IF NOT EXISTS analytics (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        performance_indicators TEXT,
        driver_trips TEXT,
        delivery_times TEXT,
        delivery_delay TEXT,
        customer_satisfaction TEXT
    )
''')

# Functions to interact with the database

def fetch_data(table_name):
    cursor.execute(f"SELECT * FROM {table_name}")
    data = cursor.fetchall()
    columns = [description[0] for description in cursor.description]
    return pd.DataFrame(data, columns=columns)

def insert_data(table_name, data):
    columns = ', '.join(data.keys())
    placeholders = ', '.join(['?'] * len(data))
    query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
    cursor.execute(query, tuple(data.values()))
    conn.commit()

# DataFrames to store information
drivers = pd.DataFrame(columns=["driver_id", "name", "location", "status"])
orders = pd.DataFrame(columns=["order_id", "order_time", "pickup_location", "dropoff_location", "status"])
zone_pressure = pd.DataFrame(columns=['zone_id', 'pressure_level'])
analytics = pd.DataFrame(columns=['performance_indicators', 'driver_trips', 'delivery_times', 'delivery_delay', 'customer_satisfaction'])

# Basic UI structure
st.title("Dispatch Call Scheduler")

# Sidebar for navigation and actions
with st.sidebar:
    st.header("Navigation")
    selected_page = st.radio("Go to", ["Order Management", "Driver Management", "Zone Pressure", "Analytics"])

    st.header("Actions")
    if st.button("Schedule Dispatch"):
        # Logic to schedule a dispatch based on current data
        st.write("Dispatch scheduled!")

# Order Management page
if selected_page == "Order Management":
    st.subheader("Order Management")

    # Add new order
    with st.form("add_order_form"):
        st.write("Add New Order")
        order_id = st.text_input("Order ID")
        order_time = st.text_input("Order Time")
        pickup_location = st.text_input("Pickup Location")
        dropoff_location = st.text_input("Dropoff Location")
        status = st.selectbox("Status", ["Pending", "In Progress", "Completed"])
        submitted = st.form_submit_button("Add Order")
        if submitted:
            new_order = pd.DataFrame({
                'order_id': [order_id],
                'order_time': [order_time],
                'pickup_location': [pickup_location],
                'dropoff_location': [dropoff_location],
                'status': [status]
            })
            orders = pd.concat([orders, new_order], ignore_index=True)
            # Insert into the database
            insert_data('orders', new_order.to_dict(orient='records')[0])
            # Re-fetch data to reflect the changes
            orders = fetch_data('orders')

    # Display order list
    st.write(orders)

# Driver Management page
elif selected_page == "Driver Management":
    st.subheader("Driver Management")

    # Add new driver
    with st.form("add_driver_form"):
        st.write("Add New Driver")
        driver_id = st.text_input("Driver ID")
        name = st.text_input("Name")
        location = st.text_input("Location")
        status = st.selectbox("Status", ["Available", "Unavailable"])
        submitted = st.form_submit_button("Add Driver")
        if submitted:
            new_driver = pd.DataFrame({
                'driver_id': [driver_id],
                'name': [name],
                'location': [location],
                'status': [status]
            })
            drivers = pd.concat([drivers, new_driver], ignore_index=True)
            # Insert into the database
            insert_data('drivers', new_driver.to_dict(orient='records')[0])
            # Re-fetch data to reflect the changes
            drivers = fetch_data('drivers')

    # Display driver list
    st.write(drivers)

# Zone Monitoring page
elif selected_page == "Zone Pressure":
    st.subheader("Dynamic Zone Pressure Monitoring")

    # Add new zone pressure data
    with st.form("add_zone_pressure_form"):
        st.write("Add Zone Pressure Data")
        zone_id = st.text_input("Zone ID")
        pressure_level = st.number_input("Pressure Level", min_value=0, max_value=10, value=0)
        submitted = st.form_submit_button("Add Zone Data")
        if submitted:
            new_zone_data = pd.DataFrame({
                'zone_id': [zone_id],
                'pressure_level': [pressure_level]
            })
            zone_pressure = pd.concat([zone_pressure, new_zone_data], ignore_index=True)
            # Insert into the database
            insert_data('zone_pressure', new_zone_data.to_dict(orient='records')[0])
            # Re-fetch data to reflect the changes
            zone_pressure = fetch_data('zone_pressure')

    # Display zone pressure data
    st.write(zone_pressure)

# Analytics Dashboard page
elif selected_page == "Analytics":
    st.subheader("Analytics Dashboard")

    # Add analytics information (consider using a more structured input method)
    with st.form("add_analytics_form"):
        st.write("Add Analytics Information")
        performance_indicators = st.text_input("Performance Indicators")
        driver_trips = st.text_input("Driver Trips")
        delivery_times = st.text_input("Delivery Times")
        delivery_delay = st.text_input("Delivery Delay")
        customer_satisfaction = st.text_input("Customer Satisfaction")
        submitted = st.form_submit_button("Add Analytics Data")
        if submitted:
            new_analytics_data = pd.DataFrame({
                'performance_indicators': [performance_indicators],
                'driver_trips': [driver_trips],
                'delivery_times': [delivery_times],
                'delivery_delay': [delivery_delay],
                'customer_satisfaction': [customer_satisfaction]
            })
            analytics = pd.concat([analytics, new_analytics_data], ignore_index=True)
            # Insert into the database
            insert_data('analytics', new_analytics_data.to_dict(orient='records')[0])
            # Re-fetch data to reflect the changes
            analytics = fetch_data('analytics')

    # Display analytics information
    st.write(analytics)

# Fetch initial data from the database
drivers = fetch_data('drivers')
orders = fetch_data('orders')
zone_pressure = fetch_data('zone_pressure')
analytics = fetch_data('analytics')

# Close the database connection when the app is done
st.session_state.on_session_end = conn.close