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

def process_data(df):
    # Clean and prepare data
    df = df[['Project Category', 'Logged']].copy()
    df = df.dropna(subset=['Project Category'])
    
    # Consolidate categories
    df['Main Category'] = df['Project Category'].apply(
        lambda x: 'Billable' if 'Billable' in str(x) else str(x).strip()
    )
    
    # Group by main categories
    main_categories = df.groupby('Main Category')['Logged'].sum().reset_index()
    total_hours = main_categories['Logged'].sum()
    main_categories['Percentage'] = (main_categories['Logged'] / total_hours * 100).round(1)
    
    # Prepare non-billable breakdown
    non_billable = df[df['Main Category'] == 'Non-Billable']
    non_billable_breakdown = non_billable.groupby('Project Category')['Logged'].sum().reset_index()
    
    return main_categories, non_billable_breakdown

def create_pie_chart(data):
    fig, ax = plt.subplots(figsize=(6, 6))
    wedges, texts, autotexts = ax.pie(
        data['Logged'],
        labels=data['Main Category'],
        autopct='%1.1f%%',
        colors=['#4CAF50', '#FFC107', '#9E9E9E'],
        startangle=90
    )
    plt.setp(autotexts, size=10, weight="bold", color='white')
    ax.set_title('Overall Utilization Distribution', pad=20)
    return fig

def create_bar_chart(data):
    fig, ax = plt.subplots(figsize=(10, 5))
    data.plot(kind='bar', x='Project Category', y='Logged', ax=ax, legend=False)
    ax.set_title('Non-Billable Time Breakdown')
    ax.set_ylabel('Hours')
    ax.set_xlabel('')
    plt.xticks(rotation=45, ha='right')
    plt.tight_layout()
    return fig

def main():
    st.title('QA Team Utilization Dashboard')
    
    uploaded_file = st.file_uploader("Upload Timesheet Excel File", type=['xls', 'xlsx'])
    
    if uploaded_file:
        try:
            df = pd.read_excel(uploaded_file, sheet_name='Report')
            main_cats, non_billable = process_data(df)
            
            # Main utilization section
            st.header("Overall Utilization")
            col1, col2 = st.columns([2, 1])
            
            with col1:
                st.pyplot(create_pie_chart(main_cats))
            
            with col2:
                st.dataframe(
                    main_cats[['Main Category', 'Logged', 'Percentage']],
                    hide_index=True,
                    column_config={
                        'Main Category': 'Category',
                        'Logged': st.column_config.NumberColumn('Hours', format="%.2f"),
                        'Percentage': st.column_config.NumberColumn(format="%.1f%%")
                    }
                )
            
            # Non-billable breakdown
            st.header("Non-Billable Detailed Breakdown")
            st.pyplot(create_bar_chart(non_billable))
            
            # Raw data preview
            st.subheader("Raw Data Preview")
            st.dataframe(df.head(10), hide_index=True)
            
        except Exception as e:
            st.error(f"Error processing file: {str(e)}")

if __name__ == "__main__":
    main()