app.py

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

# --- 1. Helper Functions ---

def categorize_rank(r):
    """Categorizes the rank into SEO buckets."""
    if pd.isna(r) or r == 0 or r == "NA":
        return "Not Talking (Intent Not Correct)"
    elif r <= 3: return "01–03"
    elif r <= 10: return "04–10"
    elif r <= 20: return "11–20"
    elif r <= 30: return "21–30"
    elif r <= 40: return "31–40"
    elif r <= 50: return "41–50"
    elif r <= 100: return "51–100"
    else: return "100+"

def clean_and_process_sheet(df):
    """
    Takes a raw dataframe from a single sheet, cleans columns, 
    identifies dates, and adds Rank Range columns.
    """
    # Clean Headers
    df.columns = df.columns.map(lambda x: str(x).strip())
    non_date_cols = ["Keyword", "Avg. monthly searches"]
    available_non_date = [c for c in non_date_cols if c in df.columns]
    
    # Identify Date Columns
    potential_date_cols = [col for col in df.columns if col not in non_date_cols and "Unnamed" not in col]
    rename_dict = {}
    for col in potential_date_cols:
        try:
            cleaned = str(col).replace("_", " ").replace(".", " ").strip()
            d = pd.to_datetime(cleaned, errors="coerce", dayfirst=True)
            if pd.notna(d):
                rename_dict[col] = d.strftime("%d_%b_%Y")
        except: pass
    
    df.rename(columns=rename_dict, inplace=True)
    date_cols = list(rename_dict.values())
    
    # Build Processed DataFrame
    processed_df = df[available_non_date].copy()
    
    for col in date_cols:
        range_col_name = f"{col}_Rank Range"
        raw_vals = pd.to_numeric(df[col], errors="coerce")
        processed_df[f"{col}_Rank_Numeric"] = raw_vals.fillna(0).astype(int)
        processed_df[range_col_name] = raw_vals.apply(categorize_rank)
        
    return processed_df

def build_chart_and_filters(df):
    """
    Generates the Plotly figure and filter choices for a specific dataframe.
    """
    if df is None or df.empty:
        return None, [], []

    # 1. Prepare Data for Chart
    viz_data = []
    # Identify all date columns by looking for "_Rank Range"
    range_cols = [c for c in df.columns if "_Rank Range" in c]
    
    for range_col in range_cols:
        # Extract date from column name (e.g., "27_Oct_2025_Rank Range" -> "27_Oct_2025")
        month_name = range_col.replace("_Rank Range", "")
        
        counts = df[range_col].value_counts().reset_index()
        counts.columns = ['Range', 'Keyword Count']
        counts['Month'] = month_name
        viz_data.append(counts)

    full_viz_df = pd.concat(viz_data) if viz_data else pd.DataFrame()
    
    # 2. Define Sorting Order
    correct_order = [
        "01–03", "04–10", "11–20", "21–30", "31–40", 
        "41–50", "51–100", "100+", "Not Talking (Intent Not Correct)"
    ]
    
    fig = None
    unique_months = []
    
    if not full_viz_df.empty:
        unique_months = list(full_viz_df['Month'].unique())
        fig = px.bar(
            full_viz_df, 
            x='Range', 
            y='Keyword Count', 
            color='Month',
            barmode='group', # Side-by-side bars
            text_auto=True,
            title="Keyword Performance Distribution",
            category_orders={"Range": correct_order}
        )
    
    return fig, unique_months, correct_order


# --- 2. Gradio Event Functions ---

def process_upload_initial(file):
    """
    Reads ALL sheets, stores them in State, and renders the FIRST sheet.
    """
    if file is None:
        return None, None, gr.Dropdown(choices=[]), None, None, gr.Dropdown(choices=[]), gr.Dropdown(choices=[])
    
    excel = pd.ExcelFile(file.name)
    all_sheets_data = {}
    
    # Process every sheet and store in dictionary
    for sheet_name in excel.sheet_names:
        raw_df = pd.read_excel(file.name, sheet_name=sheet_name)
        all_sheets_data[sheet_name] = clean_and_process_sheet(raw_df)
    
    sheet_names = list(all_sheets_data.keys())
    first_sheet_name = sheet_names[0]
    first_df = all_sheets_data[first_sheet_name]
    
    # Generate View for First Sheet
    fig, months, ranges = build_chart_and_filters(first_df)
    
    return (
        all_sheets_data,                                      # State: All Data
        first_df,                                             # State: Current Sheet Data
        gr.Dropdown(choices=sheet_names, value=first_sheet_name), # Sheet Selector
        fig,                                                  # Chart
        first_df,                                             # Table
        gr.Dropdown(choices=months, value=months[0] if months else None), # Month Filter
        gr.Dropdown(choices=ranges, value=ranges[0] if ranges else None)  # Range Filter
    )

def change_sheet(selected_sheet, all_data):
    """
    Switches the view when a new sheet is selected from dropdown.
    """
    if not selected_sheet or not all_data:
        return None, None, None, gr.Dropdown(choices=[]), gr.Dropdown(choices=[])
        
    new_df = all_data[selected_sheet]
    fig, months, ranges = build_chart_and_filters(new_df)
    
    return (
        new_df, # Update Current Sheet State
        fig,    # Update Chart
        new_df, # Update Table
        gr.Dropdown(choices=months, value=months[0] if months else None), # Update Month Choices
        gr.Dropdown(choices=ranges, value=ranges[0] if ranges else None)  # Update Range Choices
    )

def filter_table(current_df, selected_month, selected_range):
    """
    Filters the CURRENTLY active sheet's dataframe.
    """
    if current_df is None or current_df.empty: return None
    
    target_col = f"{selected_month}_Rank Range"
    
    if target_col in current_df.columns:
        filtered_df = current_df[current_df[target_col] == selected_range]
        return filtered_df
    
    return current_df

def reset_table(current_df):
    return current_df


# --- 3. UI Layout ---

with gr.Blocks(theme=gr.themes.Soft()) as demo:
    # STATES
    all_sheets_state = gr.State({}) # Stores DICTIONARY of all sheets {name: df}
    current_sheet_state = gr.State(pd.DataFrame()) # Stores currently visible sheet DF

    gr.Markdown("# 🚀 SEO Multi-Sheet Dashboard")
    
    # TOP ROW: Upload & Sheet Selection
    with gr.Row():
        file_input = gr.File(label="Upload Excel (Multiple Sheets Supported)", file_types=[".xlsx"])
        with gr.Column():
            process_btn = gr.Button("📊 Load File", variant="primary")
            # NEW: Sheet Selector Dropdown
            sheet_dropdown = gr.Dropdown(label="📑 Select Sheet to Analyze", choices=[], interactive=True)

    # VISUALIZATION
    plot_output = gr.Plot(label="Keyword Distribution")
    
    # FILTER ROW
    gr.Markdown("### 🔍 Filter Data")
    with gr.Row():
        month_dropdown = gr.Dropdown(label="Select Month", choices=[])
        range_dropdown = gr.Dropdown(label="Select Rank Range", choices=[])
        filter_btn = gr.Button("Apply Filter")
        reset_btn = gr.Button("Show All Rows")

    table_output = gr.DataFrame(interactive=False)

    # --- EVENTS ---
    
    # 1. Upload & Process (Loads all sheets, defaults to first)
    process_btn.click(
        fn=process_upload_initial,
        inputs=file_input,
        outputs=[
            all_sheets_state,    # Save all sheets
            current_sheet_state, # Save current sheet
            sheet_dropdown,      # Update dropdown options
            plot_output,         # Show chart
            table_output,        # Show table
            month_dropdown,      # Update filters
            range_dropdown
        ]
    )
    
    # 2. Change Sheet (User selects "RD Monthly" etc.)
    sheet_dropdown.change(
        fn=change_sheet,
        inputs=[sheet_dropdown, all_sheets_state],
        outputs=[
            current_sheet_state,
            plot_output,
            table_output,
            month_dropdown,
            range_dropdown
        ]
    )

    # 3. Filter Data (Applied to current sheet)
    filter_btn.click(
        fn=filter_table,
        inputs=[current_sheet_state, month_dropdown, range_dropdown],
        outputs=table_output
    )

    # 4. Reset Data
    reset_btn.click(
        fn=reset_table,
        inputs=current_sheet_state,
        outputs=table_output
    )

if __name__ == "__main__":
    demo.launch()