import streamlit as st
import pandas as pd
import matplotlib.pyplot as plt

def process_data(df):
    # Convert dates and filter relevant period
    df['Start Date'] = pd.to_datetime(df['Date'].str.split(' to ').str[0], format='%d/%b/%y')
    df['End Date'] = pd.to_datetime(df['Date'].str.split(' to ').str[1], format='%d/%b/%y')
    
    # Categorize into weeks
    df['Week'] = df['Start Date'].apply(lambda x: 1 if x <= pd.Timestamp('2025-01-05') else 2)
    
    return df

def create_utilization_chart(week_data, week_number):
    fig, ax = plt.subplots()
    wedges, texts, autotexts = ax.pie(
        week_data[['Billable', 'Non-Billable', 'Leaves']].values[0],
        labels=['Billable', 'Non-Billable', 'Leaves'],
        autopct='%1.1f%%',
        colors=['#4CAF50', '#FFC107', '#9E9E9E']
    )
    plt.setp(autotexts, size=10, weight="bold", color='white')
    ax.set_title(f'Week {week_number} Utilization', pad=20)
    return fig

def create_non_billable_breakdown(df):
    non_billable = df[df['Project Category'] == 'Non-Billable']
    breakdown = non_billable.groupby('Epic')['Logged'].sum().reset_index()
    breakdown = breakdown[breakdown['Epic'] != 'No Epic']
    
    fig, ax = plt.subplots()
    breakdown.plot(kind='bar', x='Epic', y='Logged', ax=ax, legend=False)
    ax.set_title('Non-Billable Time Breakdown')
    ax.set_ylabel('Hours')
    plt.xticks(rotation=45)
    return fig

def main():
    st.title('QA Team Utilization Dashboard')
    
    uploaded_file = st.file_uploader("Upload Tempo Timesheet", type=['xls', 'xlsx'])
    
    if uploaded_file:
        df = pd.read_excel(uploaded_file, sheet_name='Report')
        df = process_data(df)
        
        # Page 4 Visualization
        st.header("Bi-Weekly Utilization Report")
        col1, col2 = st.columns(2)
        
        with col1:
            week1 = df[df['Week'] == 1]
            st.pyplot(create_utilization_chart(week1, 1))
        
        with col2:
            week2 = df[df['Week'] == 2]
            st.pyplot(create_utilization_chart(week2, 2))
        
        # Page 5 Visualization
        st.header("Non-Billable Time Breakdown")
        st.pyplot(create_non_billable_breakdown(df))
        
        # Page 6 Visualization
        st.header("Solution Accelerators Progress")
        accelerators = df[(df['Project Category'] == 'Non-Billable') & 
                         (df['Epic'] == 'Solution Accelerators')]
        
        st.dataframe(
            accelerators[['Project', 'Logged', 'Key']].rename(columns={
                'Project': 'Initiative',
                'Logged': 'Hours',
                'Key': 'Status'
            }),
            hide_index=True
        )

if __name__ == "__main__":
    main()