import gradio as gr
import pandas as pd
import plotly.express as px

def clean_and_analyze(file):
    if file is None:
        return None, "Please upload a file.", gr.update(choices=[])
    
    # Load data
    try:
        if file.name.endswith('.csv'):
            df_raw = pd.read_csv(file.name, header=None)
        else:
            df_raw = pd.read_excel(file.name, header=None)
        
        # Smart Header Detection (Finding the 'real' table start)
        non_null_counts = df_raw.notnull().sum(axis=1)
        header_idx = non_null_counts.idxmax()
        
        df = df_raw.iloc[header_idx + 1:].reset_index(drop=True)
        df.columns = [str(c).strip() for c in df_raw.iloc[header_idx].values]
        df = df.dropna(axis=1, how='all').dropna(axis=0, how='all')

        # Convert numeric columns automatically
        for col in df.columns:
            numeric_conv = pd.to_numeric(df[col], errors='coerce')
            if numeric_conv.notnull().sum() > (len(df) * 0.4):
                df[col] = numeric_conv

        cols = df.columns.tolist()
        summary = f"✅ Successfully cleaned! Found {len(df)} rows and {len(cols)} columns."
        
        return df, summary, gr.update(choices=cols, value=cols[0]), gr.update(choices=cols, value=cols[-1])
    except Exception as e:
        return None, f"Error: {str(e)}", gr.update(choices=[]), gr.update(choices=[])

def create_plot(df, x_col, y_col):
    if df is None:
        return None
    fig = px.bar(df, x=x_col, y=y_col, color=y_col, 
                 title=f"{y_col} Analysis", template="plotly_white")
    return fig

# --- Gradio UI Layout ---
with gr.Blocks(theme=gr.themes.Soft()) as demo:
    gr.Markdown("# 📊 Course Quality Tracker (Product Ops)")
    gr.Markdown("Upload raw exports (Zoom, LMS, CSV) and generate instant quality reports.")
    
    current_data = gr.State()
    
    with gr.Row():
        file_input = gr.File(label="Upload Messy CSV or Excel")
        with gr.Column():
            status_msg = gr.Textbox(label="System Status", interactive=False)
            x_sel = gr.Dropdown(label="Select Course/Identity Column")
            y_sel = gr.Dropdown(label="Select Quality Metric (Numeric)")
            plot_btn = gr.Button("Generate Insights", variant="primary")

    with gr.Tabs():
        with gr.TabItem("Visualization"):
            plot_output = gr.Plot()
        with gr.TabItem("Cleaned Data"):
            table_output = gr.DataFrame()

    # Logic Flows
    file_input.change(
        clean_and_analyze, 
        inputs=[file_input], 
        outputs=[current_data, status_msg, x_sel, y_sel]
    ).then(lambda df: df, inputs=[current_data], outputs=[table_output])

    plot_btn.click(
        create_plot, 
        inputs=[current_data, x_sel, y_sel], 
        outputs=[plot_output]
    )

demo.launch()