import pandas as pd
import gradio as gr
import tempfile

def reformat_excel(file):
    df = pd.read_excel(file)

    # Drop rows where all expected columns are empty
    df = df.dropna(subset=['Email', 'Status', 'Student', 'Answers/Question', 'Answers/Free Text answer'], how='all')

    # Fill forward participant info
    df[['Email', 'Status', 'Student']] = df[['Email', 'Status', 'Student']].ffill()

    # Drop rows that still have missing Questions or Answers
    df = df.dropna(subset=['Answers/Question', 'Answers/Free Text answer'])

    # Debug info (optional for logs)
    print("Unique Respondents:", df['Email'].nunique())
    print("Unique Questions:", df['Answers/Question'].nunique())

    # Pivot the cleaned data
    pivot_df = df.pivot_table(
        index=['Email', 'Status', 'Student'],
        columns='Answers/Question',
        values='Answers/Free Text answer',
        aggfunc='first'
    ).reset_index()

    pivot_df.columns.name = None  # remove pivot index name

    # Write to temp file
    with tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx") as tmp:
        pivot_df.to_excel(tmp.name, index=False)
        return tmp.name

# Gradio interface
iface = gr.Interface(
    fn=reformat_excel,
    inputs=gr.File(label="Upload Excel File"),
    outputs=gr.File(label="Download Reformatted Excel"),
    title="Survey Formatter",
    description="Converts raw survey Excel with one row per Q/A into a table with questions as columns."
)

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