main.py

import streamlit as st
import pandas as pd

from db_functions import (
connect_to_db,
get_basic_info,
get_additional_tables,
add_new_manual_id,
get_suppliers,
get_categories,
get_all_products,
get_product_history,
place_reorder,
get_pending_reorders,
mark_reorder_as_received

)


# side_bar

st.sidebar.title("Inventory Management Dashboard")
option = st.sidebar.radio("Select Option:",["Basic Information","Operational Tasks"])


# main space
st.title("Inventory and Supply Chain Dashboard")

db = connect_to_db()
cursor = db.cursor(dictionary=True)

# Basic Information Page

if option == "Basic Information":
    st.subheader("Basic Metrics")


    basic_info = get_basic_info(cursor)

    cols = st.columns(3)
    keys = list(basic_info.keys())

    for i in range(3):
        cols[i].metric(label=keys[i], value=basic_info[keys[i]])

    cols = st.columns(3)
    for i in range(3,6):
        cols[i-3].metric(label=keys[i], value=basic_info[keys[i]])

    st.divider()

    # fetch and display details tables

    tables = get_additional_tables(cursor)

    for label,data in tables.items():
        st.subheader(label)
        df = pd.DataFrame(data)
        st.dataframe(df)
        st.divider()

elif option == "Operational Tasks":
    st.subheader("Operational Tasks")

    selected_task = st.selectbox("Select Operational Task Type",["Add New Product","Product History","Place Reorder","Receive Reorder"])

    if selected_task == "Add New Product":
        st.subheader("Add New Product")
        category = get_categories(cursor)
        suppliers = get_suppliers(cursor)

        with st.form("Add New Product"):
            product_name = st.text_input("Enter Product Name")
            product_category = st.selectbox("Select Product Category",(category))
            product_price = st.number_input("Enter Product Price",min_value=0.00)
            product_stock = st.number_input("Enter Product Stock",min_value=0,step=1)
            product_level = st.number_input("Enter Reorder Level",min_value=0,step=1)

            supplier_ids = [s["supplier_id"] for s in suppliers]
            supplier_names = [s["supplier_name"] for s in suppliers]

            supplier_id = st.selectbox("Select Supplier ID",options=supplier_ids,format_func=lambda x:supplier_names[supplier_ids.index(x)])


            submitted_product = st.form_submit_button("Add Product")

            if submitted_product:
                if not product_name:
                    st.error("Product Name is required")

                else:
                    try:
                        add_new_manual_id(cursor,db,product_name,product_category,product_price,product_stock,product_level,supplier_id)
                        st.success(f"Product {product_name} Added Successfully")

                    except Exception as e:
                        st.error(e)


# Product History
    elif selected_task == "Product History":
        st.subheader("Product Inventory History")

        # get product list

        products = get_all_products(cursor)
        product_names = [p["product_name"] for p in products]
        product_ids = [p["product_id"] for p in products]

        selected_product_name = st.selectbox("Select Product Name",options=product_names)

        if selected_product_name:
            selected_product_id = product_ids[product_names.index(selected_product_name)]
            history_data = get_product_history(cursor,selected_product_id)

            if history_data:
                df = pd.DataFrame.from_dict(history_data)
                st.dataframe(df)
            else:
                st.info("Product History Not Found")


# Place Reorder

    elif selected_task == "Place Reorder":
        st.subheader("Place Reorder")

        # get product list

        products = get_all_products(cursor)
        product_names = [p["product_name"] for p in products]
        product_ids = [p["product_id"] for p in products]

        selected_product_name = st.selectbox("Select Product Name",options=product_names)
        reorder_qty = st.number_input("Select Reorder Quantity",min_value=1,step=1)

        if st.button("Reorder Quantity"):
            if not selected_product_name:
                st.error("Product Select Product")
            elif reorder_qty <= 0:
                st.error("Reorder Quantity must be greater than 0")

            else:
                selected_product_id = product_ids[product_names.index(selected_product_name)]
                try:
                   place_reorder(cursor,db,selected_product_id,reorder_qty)
                   st.success(f"Product {selected_product_name} Reordered Successfully with Quantity : {reorder_qty}")


                except Exception as e:
                    st.error(e)

# Receiving an Order
    elif selected_task == "Receive Reorder":
        st.header("Mark Reorder as Received")
        # Fetch orders in Ordered Stage
        pending_reorders = get_pending_reorders(cursor)
        if not pending_reorders:
            st.info("No Pending Orders to Receive.")
        else:
            reorder_ids = [r['reorder_id'] for r in pending_reorders]
            reorder_labels = [f"ID {r['reorder_id']} - {r['product_name']}" for r in pending_reorders]

            selected_label = st.selectbox("Select Reorder to mark As Received", options=reorder_labels)

            if selected_label:
                selected_reorder_id = reorder_ids[reorder_labels.index(selected_label)]

                if st.button("Mark as Received"):
                    try:
                        mark_reorder_as_received(cursor, db, selected_reorder_id)
                        st.success(f"Reorder ID {selected_reorder_id} marked as received")
                    except Exception as e:
                        st.error(f"Error {e}")