import streamlit as st
from streamlit_option_menu import option_menu
from sqlalchemy import create_engine
import sites.services_reporting as sr
import sites.userlist as us
import sites.server as s
import sites.tickets as ti
import sites.lastrun as lr
from datetime import datetime
from dateutil.relativedelta import relativedelta
import mysql.connector
import pandas as pd
import os

# Page Settings
st.set_page_config(page_title="Reporting", layout="wide")

start_date = datetime.today().replace(day=1) - relativedelta(months=1)
end_date = datetime.today().replace(day=1) - relativedelta(days=1)
start_date_lastmonth = datetime.today().replace(day=1) - relativedelta(months=2)
end_date_lastmonth = datetime.today().replace(day=1) - relativedelta(months=1) - relativedelta(days=1)

# Datumsformatierung
start_date_format = start_date.strftime("%Y-%m-%d")
end_date_format = end_date.strftime("%Y-%m-%d")

# Load custom CSS
def load_css(file_name):
    with open(file_name) as f:
        st.markdown(f'<style>{f.read()}</style>', unsafe_allow_html=True)

def get_customer_used_service(end_date):
    mydb = mysql.connector.connect(
        host=os.getenv("MYSQL_HOST"),
        user=os.getenv("MYSQL_USER"),
        password=os.getenv("MYSQL_PASSWORD"),
        database=os.getenv("MYSQL_DATABASE")
    )

    mycursor = mydb.cursor()

    mycursor.execute(f"""SELECT cs.companyname, cs.customer_ID, cs.services_ID,cs.name from Kunden.`daily.customer.services` cs WHERE add_date LIKE '%{end_date}%'""")

    myresult = mycursor.fetchall()
    mydb.close()
    return myresult

def load_server_list(start_date, end_date):
    db_url = (
        f"mysql+mysqlconnector://{os.getenv('MYSQL_USER')}:"
        f"{os.getenv('MYSQL_PASSWORD')}@{os.getenv('MYSQL_HOST')}/"
        f"{os.getenv('MYSQL_DATABASE')}"
    )
    engine = create_engine(db_url)
    query = f"""
                SELECT server, cores, customer_id
                FROM Kunden.`daily.spla.server` sr
                WHERE sr.timestamp BETWEEN '{start_date}' AND '{end_date}'
    """
    max_server_count = pd.read_sql_query(query, engine)
    return max_server_count


def load_user_service_list(service_id, customer_id, start_date, end_date):
    db_url = (
        f"mysql+mysqlconnector://{os.getenv('MYSQL_USER')}:"
        f"{os.getenv('MYSQL_PASSWORD')}@{os.getenv('MYSQL_HOST')}/"
        f"{os.getenv('MYSQL_DATABASE')}"
    )
    engine = create_engine(db_url)

    query = f"""
            SELECT COUNT(*) AS max_count
            FROM (
                SELECT username
                FROM Kunden.`daily.user.enabled` sr
                WHERE sr.customer_ID = {customer_id}
                AND sr.services_ID = {service_id}
                AND sr.timestamp BETWEEN '{start_date_format}' AND '{end_date_format}'
            ) AS sub;
    """
    max_user_count = pd.read_sql_query(query, engine)
    return max_user_count.iloc[0]['max_count'] if not max_user_count.empty else 0

def load_user_disabled(start_date, end_date,customer_id):
    db_url = (
        f"mysql+mysqlconnector://{os.getenv('MYSQL_USER')}:"
        f"{os.getenv('MYSQL_PASSWORD')}@{os.getenv('MYSQL_HOST')}/"
        f"{os.getenv('MYSQL_DATABASE')}"
    )
    engine = create_engine(db_url)

    query = f"""
            SELECT disabledate, username, service_name, customer_name, ticketnumber, comment
            FROM Kunden.`daily.user.disabled` sud
            WHERE sud.disabledate BETWEEN '{start_date}' AND '{end_date}' AND customer_id = '{customer_id}' AND services_id IN (100,101,116,120) ORDER BY service_name
    """
    all = pd.read_sql_query(query, engine)
    return all

def home():
    st.title("Dashboard")
    edit_start_date = start_date.strftime("%d.%m.%Y")
    edit_end_date = end_date.strftime("%d.%m.%Y")
    edit_start_date_lastmonth = start_date_lastmonth.strftime("%d.%m.%Y")
    edit_end_date_lastmonth = end_date_lastmonth.strftime("%d.%m.%Y")
    st.subheader(f"Übersicht {edit_start_date} - {edit_end_date}")
    previous_value = None
    columns = None
    
    c = 0
    print(end_date)
    for i in get_customer_used_service(end_date.strftime("%Y-%m-%d")):
        print(i)
        if previous_value != i[1]:
            st.subheader(f"{i[0]}")
            columns = st.columns(4)
            df = load_user_disabled(start_date_lastmonth, end_date_lastmonth, i[1])
            
            if not df.empty:
                st.text(f"Deaktivierte User {edit_start_date_lastmonth} - {edit_end_date_lastmonth}")
                st.data_editor(df,use_container_width=True)
            c = 0
        
        active_users = load_user_service_list(i[2], i[1], start_date, end_date)
        
        # Filter nach dem spezifischen Service und zähle die eindeutigen Benutzernamen
        disabled_users_count = 0
        if not df.empty and 'service_name' in df.columns and 'username' in df.columns:
            # Annahme: i[3] enthält den Service-Namen, der mit der 'service_name'-Spalte übereinstimmt
            service_filtered_df = df[df['service_name'] == i[3]]
            disabled_users_count = service_filtered_df['username'].nunique()
        
        if not active_users:
            st.info(f"Kunde {i[0]} - Service {i[3]} - Not Data found!")
        else:
            columns[c].metric(
                label=f"Aktive {i[3]} User", 
                value=active_users,
                delta=f"-{disabled_users_count}" if disabled_users_count > 0 else None,
                delta_color="inverse"
            )
            c += 1 
            previous_value = i[1]

    col1, = st.columns(1)
    df = load_server_list(start_date,end_date)
    grouped = df.groupby('server')['cores'].count()*8/2
    grouped_series = df.groupby('server')['cores'].max()
    grouped_str = grouped_series.to_string(header=False)
    df['cores'] = pd.to_numeric(df['cores'], errors='coerce')
    server_cores = df.groupby('server')['cores'].max()
    count_cores = server_cores.count()
    with col1:
        st.header("CPU Liste")
        st.text(
            f"Anzahl der Cores:\n{grouped_str}\n "
        )
        st.text(f"Gesamte Anzahl der Cores: {count_cores}")
        st.text(f"Berechung der Core-Pakete = Anzahl der Cores ({count_cores}) * Core-Pakete aus SPLA (8) / 2")
        st.header(f"Gesamt : {str(grouped.sum()).split('.')[0]} Pakete")

    # Ausgabe der Ergebnisse
            
# Navigation bar using streamlit-option-menu
with st.sidebar:
    selected_page = option_menu(
        menu_title="Navigation",  # required
        options=["Dashboard", "Services Reporting", "User Filter", "Server", "Tickets", "Last-Run"],  # required
        icons=["house", "bar-chart", "filter", "server", "ticket", "clock"],  # optional
        menu_icon="cast",  # optional
        default_index=0,  # optional
        orientation="vertikal",  # horizontal navigation
    )

# Page display logic based on selected option
if selected_page == "Dashboard":
    home()
elif selected_page == "Services Reporting":
    sr.services_reporting()
elif selected_page == "User Filter":
    us.user_filter()
elif selected_page == "Server":
    s.server_filter()
elif selected_page == "Tickets":
    ti.ticket_filter()
elif selected_page == "Last-Run":
    lr.user_filter()