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

def process_data(df):
    # Clean data and handle date parsing
    df = df[df['Project Category'].notna()]
    
    # Convert date strings to datetime
    df['Start Date'] = pd.to_datetime(
        df['Date'].str.split(' to ').str[0], 
        format='%d/%b/%y', 
        errors='coerce'
    )
    
    # Filter valid dates and assign weeks
    df = df.dropna(subset=['Start Date'])
    df['Week'] = df['Start Date'].apply(
        lambda x: 1 if x <= pd.Timestamp('2025-01-05') else 2
    )
    
    # Consolidate billable categories
    df['Category'] = df['Project Category'].apply(
        lambda x: 'Billable' if 'Billable' in x else x
    )
    
    # Aggregate data
    utilization = df.groupby(['Week', 'Category'])['Logged'].sum().unstack(fill_value=0)
    
    # Select relevant categories and calculate percentages
    categories = ['Billable', 'Non-Billable', 'Leaves']
    utilization = utilization.reindex(categories, axis=1, fill_value=0)
    total_hours = utilization.sum(axis=1)
    utilization_percent = utilization.div(total_hours, axis=0) * 100
    
    return utilization_percent

def create_utilization_chart(week_data, week_number):
    fig, ax = plt.subplots(figsize=(6, 6))
    labels = week_data.index[week_data > 0]
    sizes = week_data[week_data > 0]
    
    wedges, texts, autotexts = ax.pie(
        sizes,
        labels=labels,
        autopct='%1.1f%%',
        colors=['#4CAF50', '#FFC107', '#9E9E9E'],
        startangle=90
    )
    
    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:
        try:
            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:
                if 1 in utilization_percent.index:
                    week1 = utilization_percent.loc[1]
                    st.pyplot(create_utilization_chart(week1, 1))
                else:
                    st.warning("No data for Week 1")
            
            with col2:
                if 2 in utilization_percent.index:
                    week2 = utilization_percent.loc[2]
                    st.pyplot(create_utilization_chart(week2, 2))
                else:
                    st.warning("No data for Week 2")
            
            # Show raw data for verification
            st.subheader("Processed Data Preview")
            st.dataframe(utilization_percent)
            
        except Exception as e:
            st.error(f"Error processing file: {str(e)}")

if __name__ == "__main__":
    main()