import streamlit as st
import pandas as pd
from dataBaseSetup import create_connection

def get_stocks_by_category_store(category_name, store_name):
        conn = create_connection()
        sql = '''
        SELECT categories.category_name, stores.store_name, SUM(stocks.quantity) as total_stock
        FROM stocks
        JOIN products ON stocks.product_id = products.product_id
        JOIN categories ON products.category_id = categories.category_id
        JOIN stores ON stocks.store_id = stores.store_id
        WHERE categories.category_name = ? AND stores.store_name = ?
        GROUP BY categories.category_name, stores.store_name;
        '''
        df = pd.read_sql_query(sql, conn, params=(category_name, store_name))
        conn.close()
        return df

def get_order_items_by_category_store(category_name, store_name):
    conn = create_connection()
    sql = '''
    SELECT c.category_name, s.store_name, COUNT(oi.item_id) as total_items
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    JOIN products p ON oi.product_id = p.product_id
    JOIN categories c ON p.category_id = c.category_id
    JOIN stores s ON o.store_id = s.store_id
    WHERE c.category_name = ? AND s.store_name = ?
    GROUP BY c.category_name, s.store_name;
    '''
    df = pd.read_sql_query(sql, conn, params=(category_name, store_name))
    conn.close()
    return df

def get_total_sales_by_store_year_month(store_name, year_month):
    conn = create_connection()
    sql = '''
    SELECT strftime('%Y-%m', o.order_date) as year_month, SUM(oi.quantity * oi.list_price) as total_sales
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE strftime('%Y-%m', o.order_date) = ? AND o.store_id IN (
        SELECT store_id FROM stores WHERE store_name = ?
    )
    GROUP BY year_month;
    '''
    df = pd.read_sql_query(sql, conn, params=(year_month, store_name))
    conn.close()
    return df

def get_staff_order_counts(desc=True):
    conn = create_connection()
    sql = '''
    SELECT s.staff_id, s.first_name || ' ' || s.last_name AS staff_name, COUNT(o.order_id) as order_count
    FROM orders o
    JOIN staffs s ON o.staff_id = s.staff_id
    GROUP BY s.staff_id
    ORDER BY order_count {}
    LIMIT 1;
    '''.format('DESC' if desc else 'ASC')
    df = pd.read_sql_query(sql, conn)
    conn.close()
    return df

# STREAMLIT

import streamlit as st

def app():
    st.title("Bike Store Management System")

    # Query 1: Stocks by Category and Store
    st.sidebar.header("Query 1: Get Stocks")
    category_name_1 = st.text_input("Category Name for Stocks", key='1')
    store_name_1 = st.text_input("Store Name for Stocks", key='2')
    if st.sidebar.button("Execute Query 1", key='3'):
        df = get_stocks_by_category_store(category_name_1, store_name_1)
        st.write("### Query 1: Stocks by Category and Store")
        st.write(df)

    # Query 2: Order Items by Category and Store
    st.sidebar.header("Query 2: Get Order Items")
    category_name_2 = st.text_input("Category Name for Order Items", key='4')
    store_name_2 = st.text_input("Store Name for Order Items", key='5')
    if st.sidebar.button("Execute Query 2", key='6'):
        df = get_order_items_by_category_store(category_name_2, store_name_2)
        st.write("### Query 2: Order Items by Category and Store")
        st.write(df)

    # Query 3: Total Sales in Santa Cruz Bikes
    st.sidebar.header("Query 3: Total Sales in Santa Cruz Bikes")
    year_month_3 = st.text_input("Year-Month (YYYY-MM) for Santa Cruz Bikes", key='7')
    if st.sidebar.button("Execute Query 3", key='8'):
        df = get_total_sales_by_store_year_month("Santa Cruz Bikes", year_month_3)
        st.write("### Query 3: Total Sales in Santa Cruz Bikes")
        st.write(df)

    # Query 4: Total Sales in Baldwin Bikes
    st.sidebar.header("Query 4: Total Sales in Baldwin Bikes")
    year_month_4 = st.text_input("Year-Month (YYYY-MM) for Baldwin Bikes", key='9')
    if st.sidebar.button("Execute Query 4", key='10'):
        df = get_total_sales_by_store_year_month("Baldwin Bikes", year_month_4)
        st.write("### Query 4: Total Sales in Baldwin Bikes")
        st.write(df)

    # Query 5: Total Sales in Rowlett Bikes
    st.sidebar.header("Query 5: Total Sales in Rowlett Bikes")
    year_month_5 = st.text_input("Year-Month (YYYY-MM) for Rowlett Bikes", key='11')
    if st.sidebar.button("Execute Query 5", key='12'):
        df = get_total_sales_by_store_year_month("Rowlett Bikes", year_month_5)
        st.write("### Query 5: Total Sales in Rowlett Bikes")
        st.write(df)

    # Query 6: Staff with the Highest Number of Orders
    st.sidebar.header("Query 6: Staff with the Highest Number of Orders")
    if st.sidebar.button("Execute Query 6", key='13'):
        df = get_staff_order_counts(desc=True)
        st.write("### Query 6: Staff with the Highest Number of Orders")
        st.write(df)

    # Query 7: Staff with the Lowest Number of Orders
    st.sidebar.header("Query 7: Staff with the Lowest Number of Orders")
    if st.sidebar.button("Execute Query 7", key='14'):
        df = get_staff_order_counts(desc=False)
        st.write("### Query 7: Staff with the Lowest Number of Orders")
        st.write(df)

if __name__ == '__main__':
    app()