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

def process_data(df):
    # Clean data and consolidate categories
    df = df[['Project Category', 'Logged']].copy()
    
    # Map to main categories
    df['Category'] = df['Project Category'].apply(
        lambda x: 'Billable' if 'Billable' in x else x.strip()
    )
    
    # Aggregate data
    summary = df.groupby('Category')['Logged'].sum().reset_index()
    total = summary['Logged'].sum()
    summary['Percentage'] = (summary['Logged'] / total * 100).round(1)
    
    return summary

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

def create_bar_chart(data):
    fig, ax = plt.subplots(figsize=(10, 4))
    data[data['Category'] == 'Non-Billable'].plot(
        kind='bar',
        x='Project Category',
        y='Logged',
        ax=ax,
        legend=False
    )
    ax.set_title('Non-Billable Details')
    ax.set_ylabel('Hours')
    plt.xticks(rotation=45)
    return fig

def main():
    st.title('QA Utilization Dashboard')
    
    uploaded_file = st.file_uploader("Upload Timesheet", type=['xls', 'xlsx'])
    
    if uploaded_file:
        df = pd.read_excel(uploaded_file, sheet_name='Report')
        processed_data = process_data(df)
        
        # Show main visualization
        st.header("Overall Utilization")
        col1, col2 = st.columns([2, 1])
        
        with col1:
            st.pyplot(create_pie_chart(processed_data))
            
        with col2:
            st.dataframe(
                processed_data[['Category', 'Logged', 'Percentage']],
                hide_index=True,
                column_config={
                    'Logged': 'Hours',
                    'Percentage': st.column_config.NumberColumn(format="%.1f%%")
                }
            )
        
        # Show non-billable details
        st.header("Non-Billable Breakdown")
        st.pyplot(create_bar_chart(df))

if __name__ == "__main__":
    main()