import streamlit as st
import pandas as pd
import sqlite3
from datetime import datetime
import plotly.express as px

# Database connection
conn = sqlite3.connect('cafm.db', check_same_thread=False)
c = conn.cursor()

# Database initialization
def create_tables():
    c.execute('''
        CREATE TABLE IF NOT EXISTS assets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            site_name TEXT,
            asset_name TEXT,
            asset_type TEXT,
            maintenance_history TEXT,
            warranty TEXT,
            current_condition TEXT
        )
    ''')
    c.execute('''
        CREATE TABLE IF NOT EXISTS work_orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            site_name TEXT,
            work_order_description TEXT,
            assigned_to TEXT,
            status TEXT,
            created_at TEXT
        )
    ''')
    c.execute('''
        CREATE TABLE IF NOT EXISTS preventive_maintenance (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            site_name TEXT,
            task_name TEXT,
            schedule_date TEXT,
            status TEXT
        )
    ''')

create_tables()

# Sidebar Navigation with Session State to retain page state
if "page" not in st.session_state:
    st.session_state.page = "Home"

def navigate_to(page):
    st.session_state.page = page

st.sidebar.title("CAFM Navigation")

# Define navigation options
pages = {
    "Home": "🏠",
    "Asset Management": "📋",
    "Work Order Management": "🛠️",
    "Preventive Maintenance": "⏰",
    "Reporting & Analytics": "📊"
}

st.sidebar.markdown("---")
for page, icon in pages.items():
    if st.sidebar.button(f"{icon} {page}"):
        navigate_to(page)

# Main Page Logic
selected_page = st.session_state.page

# Home Page
if selected_page == "Home":
    st.title("Welcome to the Computer-Aided Facility Management (CAFM) System")
    st.image(
        "https://via.placeholder.com/800x300.png?text=CAFM+System",
        use_container_width=True
    )
    st.markdown("""
    This application centralizes all facility management tasks to improve efficiency and decision-making. 
    Explore the options to manage assets, track work orders, plan maintenance, and analyze data trends.
    """)

# Asset Management
elif selected_page == "Asset Management":
    st.title("Asset Management")
    st.markdown("Manage and monitor your facility's assets efficiently.")
    
    with st.form("asset_form"):
        site_name = st.text_input("Site Name")
        asset_name = st.text_input("Asset Name")
        asset_type = st.text_input("Asset Type")
        maintenance_history = st.text_area("Maintenance History")
        warranty = st.text_input("Warranty (e.g., 1 year)")
        current_condition = st.selectbox("Current Condition", ["Good", "Needs Repair", "Critical"])
        submitted = st.form_submit_button("Add Asset")
        if submitted:
            c.execute('''
                INSERT INTO assets (site_name, asset_name, asset_type, maintenance_history, warranty, current_condition)
                VALUES (?, ?, ?, ?, ?, ?)
            ''', (site_name, asset_name, asset_type, maintenance_history, warranty, current_condition))
            conn.commit()
            st.success("Asset added successfully!")
            st.session_state.page = "Asset Management"

    st.subheader("Asset List")
    assets = pd.read_sql_query("SELECT * FROM assets", conn)
    st.dataframe(assets)

# Work Order Management
elif selected_page == "Work Order Management":
    st.title("Work Order Management")
    st.markdown("Streamline the management of work orders from creation to completion.")

    with st.form("work_order_form"):
        site_name = st.text_input("Site Name")
        work_order_description = st.text_area("Work Order Description")
        assigned_to = st.text_input("Assigned To")
        status = st.selectbox("Status", ["Pending", "In Progress", "Completed"])
        submitted = st.form_submit_button("Create Work Order")
        if submitted:
            c.execute('''
                INSERT INTO work_orders (site_name, work_order_description, assigned_to, status, created_at)
                VALUES (?, ?, ?, ?, ?)
            ''', (site_name, work_order_description, assigned_to, status, datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
            conn.commit()
            st.success("Work order created successfully!")
            st.session_state.page = "Work Order Management"

    st.subheader("Work Orders")
    work_orders = pd.read_sql_query("SELECT * FROM work_orders", conn)
    st.dataframe(work_orders)

# Preventive Maintenance
elif selected_page == "Preventive Maintenance":
    st.title("Preventive Maintenance")
    st.markdown("Plan and manage preventive maintenance tasks to ensure asset longevity.")

    with st.form("maintenance_form"):
        site_name = st.text_input("Site Name")
        task_name = st.text_input("Task Name")
        schedule_date = st.date_input("Schedule Date")
        status = st.selectbox("Status", ["Scheduled", "Completed"])
        submitted = st.form_submit_button("Schedule Task")
        if submitted:
            c.execute('''
                INSERT INTO preventive_maintenance (site_name, task_name, schedule_date, status)
                VALUES (?, ?, ?, ?)
            ''', (site_name, task_name, schedule_date, status))
            conn.commit()
            st.success("Task scheduled successfully!")
            st.session_state.page = "Preventive Maintenance"

    st.subheader("Scheduled Maintenance")
    maintenance = pd.read_sql_query("SELECT * FROM preventive_maintenance", conn)
    st.dataframe(maintenance)

# Reporting & Analytics
elif selected_page == "Reporting & Analytics":
    st.title("Reporting & Analytics")
    st.markdown("Visualize data to make informed, data-driven decisions.")

    st.subheader("Asset Overview")
    asset_data = pd.read_sql_query("SELECT site_name, COUNT(*) as total_assets FROM assets GROUP BY site_name", conn)
    if not asset_data.empty:
        fig = px.bar(asset_data, x="site_name", y="total_assets", title="Total Assets by Site")
        st.plotly_chart(fig)

    st.subheader("Work Order Summary")
    work_order_data = pd.read_sql_query("SELECT site_name, COUNT(*) as total_work_orders FROM work_orders GROUP BY site_name", conn)
    if not work_order_data.empty:
        fig = px.pie(work_order_data, values="total_work_orders", names="site_name", title="Work Orders Distribution by Site")
        st.plotly_chart(fig)

    st.subheader("Maintenance Tasks Overview")
    maintenance_data = pd.read_sql_query("SELECT site_name, COUNT(*) as total_tasks FROM preventive_maintenance GROUP BY site_name", conn)
    if not maintenance_data.empty:
        fig = px.line(maintenance_data, x="site_name", y="total_tasks", title="Maintenance Tasks by Site")
        st.plotly_chart(fig)

# Close database connection
conn.close()