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

def process_data(df):
    # Clean and transform data
    df = df[df['Project Category'].notna()]
    
    # Create Week buckets
    df['Start Date'] = pd.to_datetime(df['Date'].str.split(' to ').str[0], format='%d/%b/%y')
    df['Week'] = df['Start Date'].apply(lambda x: 1 if x <= pd.Timestamp('2025-01-05') else 2)
    
    # Aggregate utilization data
    utilization = df.groupby(['Week', 'Project Category'])['Logged'].sum().unstack(fill_value=0)
    
    # Calculate percentages
    total_hours = utilization.sum(axis=1)
    utilization_percent = utilization.div(total_hours, axis=0) * 100
    
    # Select relevant categories
    utilization_percent = utilization_percent[['Fixed Bid Projects - Billable', 
                                              'Non-Billable', 
                                              'Leaves']].rename(columns={
        'Fixed Bid Projects - Billable': 'Billable',
        'Non-Billable': 'Non-Billable',
        'Leaves': 'Leaves'
    })
    
    return utilization_percent

def create_utilization_chart(week_data, week_number):
    fig, ax = plt.subplots()
    wedges, texts, autotexts = ax.pie(
        week_data.values,
        labels=week_data.index,
        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 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')
        utilization_percent = process_data(df)
        
        # Page 4 Visualization
        st.header("Bi-Weekly Utilization Report")
        col1, col2 = st.columns(2)
        
        with col1:
            week1 = utilization_percent.loc[1]
            st.pyplot(create_utilization_chart(week1, 1))
        
        with col2:
            week2 = utilization_percent.loc[2]
            st.pyplot(create_utilization_chart(week2, 2))

if __name__ == "__main__":
    main()