app.py

import pandas as pd
import openpyxl
import gradio as gr
import io
import base64
from datetime import datetime

def convert_schedule(file_path, direction):
    if file_path is None:
        return pd.DataFrame({'Error': ['Please upload a file']}), None
        
    try:
        # 1. Load raw header rows to determine day labels
        raw = pd.read_excel(file_path, header=None)
        header1 = raw.iloc[0, 1:].astype(object)
        header2 = raw.iloc[1, 1:].astype(object)

        # Decide which header row to use: prefer second if fully populated
        # FIX: Convert to string before using .str accessor and handle NaN values
        header2_str = header2.fillna('').astype(str)
        if header2.notna().all() and not header2_str.str.startswith('Unnamed').any():
            days = header2.tolist()
            data_start = 2
        else:
            # Forward-fill merged first-row headers
            days = []
            last = None
            for val in header1:
                if pd.isna(val) or str(val).startswith('Unnamed'):
                    days.append(last)
                else:
                    last = str(val)
                    days.append(last)
            data_start = 1

        # 2. Load actual data using resolved day columns
        df = pd.read_excel(
            file_path,
            header=data_start,
            index_col=0,
            usecols=[0] + list(range(1, len(days) + 1))
        )
        df.columns = [str(day) for day in days]

        # 3. Retain original day column order
        day_cols = list(df.columns)

        # 4. Build assignment mapping via explicit iteration
        assignments = {}
        if direction == 'A to B':
            # Models in rows → Texters as rows
            for model in df.index.astype(str):
                for day in day_cols:
                    cell = df.at[model, day]
                    if pd.isna(cell):
                        continue
                    for texter in str(cell).split(','):
                        texter = texter.strip()
                        if not texter or texter.lower() in ['nan', 'none', '']:
                            continue
                        assignments.setdefault(texter, {d: [] for d in day_cols})
                        assignments[texter][day].append(model)
            
            if not assignments:
                result = pd.DataFrame(columns=day_cols)
                first_col_name = 'Texter'
            else:
                index = sorted(assignments.keys())
                result = pd.DataFrame(index=index, columns=day_cols)
                first_col_name = 'Texter'
                for texter, days_map in assignments.items():
                    for day in day_cols:
                        models = days_map.get(day, [])
                        result.at[texter, day] = ', '.join(models) if models else 'OFF'
        else:
            # Texters in rows → Models as rows
            for texter in df.index.astype(str):
                for day in day_cols:
                    cell = df.at[texter, day]
                    if pd.isna(cell):
                        continue
                    for model in str(cell).split(','):
                        model = model.strip()
                        if not model or model.lower() in ['nan', 'none', '']:
                            continue
                        assignments.setdefault(model, {d: [] for d in day_cols})
                        assignments[model][day].append(texter)
            
            if not assignments:
                result = pd.DataFrame(columns=day_cols)
                first_col_name = 'Model'
            else:
                index = sorted(assignments.keys())
                result = pd.DataFrame(index=index, columns=day_cols)
                first_col_name = 'Model'
                for model, days_map in assignments.items():
                    for day in day_cols:
                        texters = days_map.get(day, [])
                        result.at[model, day] = ', '.join(texters) if texters else 'OFF'

        # 5. Cleanup axis names
        result.index.name = None
        result.columns.name = None

        # For display, include index as a column
        display_df = result.reset_index().rename(columns={'index': first_col_name})

        # 6. Create downloadable file using in-memory approach
        result_clean = result.copy().fillna('OFF')
        
        # Ensure all values are strings
        for col in result_clean.columns:
            result_clean[col] = result_clean[col].astype(str)

        # Create CSV file in memory (more reliable than Excel)
        download_df = result_clean.reset_index().rename(columns={'index': first_col_name})
        
        # Create CSV content
        csv_buffer = io.StringIO()
        download_df.to_csv(csv_buffer, index=False)
        csv_content = csv_buffer.getvalue()
        
        # Create filename with timestamp
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        filename = f"converted_schedule_{timestamp}.csv"
        
        # Return the CSV content for download
        return display_df, (csv_content.encode('utf-8'), filename)

    except Exception as e:
        error_msg = f"Error processing file: {str(e)}"
        print(f"DEBUG: {error_msg}")
        error_df = pd.DataFrame({'Error': [error_msg]})
        return error_df, None

# Wrapper function to handle the file download properly
def process_and_download(file_path, direction):
    display_result, download_data = convert_schedule(file_path, direction)
    
    if download_data is None:
        return display_result, None
    
    # Create a temporary file that Gradio can serve
    import tempfile
    import os
    
    excel_content, filename = download_data
    
    # Save to a temporary file in the system temp directory
    temp_dir = tempfile.gettempdir()
    temp_path = os.path.join(temp_dir, filename)
    
    # Write CSV content
    with open(temp_path, 'w', encoding='utf-8') as f:
        f.write(excel_content.decode('utf-8'))
    
    return display_result, temp_path

# Create the interface
iface = gr.Interface(
    fn=process_and_download,
    inputs=[
        gr.File(
            label='Upload Weekly Schedule (.xlsx)', 
            file_count='single', 
            file_types=['.xlsx', '.xls']
        ),
        gr.Radio(
            ['A to B', 'B to A'], 
            label='Convert Direction', 
            value='A to B',
            info='A to B: Models→Texters, B to A: Texters→Models'
        )
    ],
    outputs=[
        gr.Dataframe(label='Converted Schedule (Preview)', wrap=True),
        gr.File(label='Download Converted Schedule (.csv)')
    ],
    title='🔄 7-Day Schedule Converter',
    description=(
        '**How to use:**\n'
        '1. Upload your Excel file with a 7-day schedule\n'
        '2. Choose conversion direction\n'
        '3. Preview the result and download the converted file\n\n'
        '*Supports merged headers and handles Models ↔ Texters conversion*'
    ),
    flagging_mode='never'
)

if __name__ == "__main__":
    iface.launch(
        server_name='0.0.0.0', 
        server_port=7860,
        share=False
    )