app.py

import gradio as gr
import pandas as pd
import tempfile
from io import BytesIO

def process_woocommerce_data_in_memory(netcom_file):
    """
    Reads the uploaded NetCom CSV file in-memory, processes it to the WooCommerce format,
    and returns the resulting CSV as bytes, suitable for download.
    """
    # Define the brand-to-logo mapping
    brand_logo_map = {
        "Amazon Web Services": "https://devthe.tech/wp-content/uploads/2025/02/aws.png",
        "Cisco": "https://devthe.tech/wp-content/uploads/2025/02/cisco-e1738593292198-1.webp",
        "Microsoft": "https://devthe.tech/wp-content/uploads/2025/01/Microsoft-e1737494120985-1.png"
    }

    # 1. Read the uploaded CSV into a DataFrame
    netcom_df = pd.read_csv(netcom_file.name, encoding='latin1')
    netcom_df.columns = netcom_df.columns.str.strip()  # standardize column names

    # 2. Create aggregated dates and times for each Course ID
    date_agg = (
        netcom_df.groupby('Course ID')['Course Start Date']
        .apply(lambda x: ','.join(x.astype(str).unique()))
        .reset_index(name='Aggregated_Dates')
    )

    time_agg = (
        netcom_df.groupby('Course ID')
        .apply(
            lambda df: ','.join(
                f"{st}-{et} {tz}"
                for st, et, tz in zip(df['Course Start Time'], 
                                      df['Course End Time'], 
                                      df['Time Zone'])
            )
        )
        .reset_index(name='Aggregated_Times')
    )

    # 3. Extract unique parent products
    parent_products = netcom_df.drop_duplicates(subset=['Course ID'])

    # 4. Merge aggregated dates and times
    parent_products = parent_products.merge(date_agg, on='Course ID', how='left')
    parent_products = parent_products.merge(time_agg, on='Course ID', how='left')

    # 5. Create parent (variable) products
    woo_parent_df = pd.DataFrame({
        'Type': 'variable',
        'SKU': parent_products['Course ID'],
        'Name': parent_products['Course Name'],
        'Published': 1,
        'Visibility in catalog': 'visible',
        'Short description': parent_products['Decription'],
        'Description': parent_products['Decription'],
        'Tax status': 'taxable',
        'In stock?': 1,
        'Stock': 1,
        'Sold individually?': 1,
        'Regular price': parent_products['SRP Pricing'].replace('[\$,]', '', regex=True),
        'Categories': 'courses',
        'Images': parent_products['Vendor'].map(brand_logo_map).fillna(''),
        'Parent': '',
        'Brands': parent_products['Vendor'],
        'Attribute 1 name': 'Date',
        'Attribute 1 value(s)': parent_products['Aggregated_Dates'],
        'Attribute 1 visible': 'visible',
        'Attribute 1 global': 1,
        'Attribute 2 name': 'Location',
        'Attribute 2 value(s)': 'Virtual',
        'Attribute 2 visible': 'visible',
        'Attribute 2 global': 1,
        'Attribute 3 name': 'Time',
        'Attribute 3 value(s)': parent_products['Aggregated_Times'],
        'Attribute 3 visible': 'visible',
        'Attribute 3 global': 1,
        'Meta: outline': parent_products['Outline'],
        'Meta: days': parent_products['Duration'],
        'Meta: location': 'Virtual',
        'Meta: overview': parent_products['Target Audience'],
        'Meta: objectives': parent_products['Objectives'],
        'Meta: prerequisites': parent_products['RequiredPrerequisite'].fillna(''),
        'Meta: agenda': parent_products['Outline']  # Agenda now copies the outline
    })

    # 6. Create child (variation) products
    woo_child_df = pd.DataFrame({
        'Type': 'variation, virtual',
        'SKU': netcom_df['Course SID'],
        'Name': netcom_df['Course Name'],
        'Published': 1,
        'Visibility in catalog': 'visible',
        'Short description': netcom_df['Decription'],
        'Description': netcom_df['Decription'],
        'Tax status': 'taxable',
        'In stock?': 1,
        'Stock': 1,
        'Sold individually?': 1,
        'Regular price': netcom_df['SRP Pricing'].replace('[\$,]', '', regex=True),
        'Categories': 'courses',
        'Images': netcom_df['Vendor'].map(brand_logo_map).fillna(''),
        'Parent': netcom_df['Course ID'],
        'Brands': netcom_df['Vendor'],
        'Attribute 1 name': 'Date',
        'Attribute 1 value(s)': netcom_df['Course Start Date'],
        'Attribute 1 visible': 'visible',
        'Attribute 1 global': 1,
        'Attribute 2 name': 'Location',
        'Attribute 2 value(s)': 'Virtual',
        'Attribute 2 visible': 'visible',
        'Attribute 2 global': 1,
        'Attribute 3 name': 'Time',
        'Attribute 3 value(s)': netcom_df.apply(
            lambda row: f"{row['Course Start Time']}-{row['Course End Time']} {row['Time Zone']}", axis=1
        ),
        'Attribute 3 visible': 'visible',
        'Attribute 3 global': 1,
        'Meta: outline': netcom_df['Outline'],
        'Meta: days': netcom_df['Duration'],
        'Meta: location': 'Virtual',
        'Meta: overview': netcom_df['Target Audience'],
        'Meta: objectives': netcom_df['Objectives'],
        'Meta: prerequisites': netcom_df['RequiredPrerequisite'].fillna(''),
        'Meta: agenda': netcom_df['Outline']  # Agenda now copies the outline
    })

    # 7. Combine parent + child
    woo_final_df = pd.concat([woo_parent_df, woo_child_df], ignore_index=True)

    # 8. Desired column order
    column_order = [
        'Type', 'SKU', 'Name', 'Published', 'Visibility in catalog',
        'Short description', 'Description', 'Tax status', 'In stock?',
        'Stock', 'Sold individually?', 'Regular price', 'Categories', 'Images',
        'Parent', 'Brands', 'Attribute 1 name', 'Attribute 1 value(s)', 'Attribute 1 visible',
        'Attribute 1 global', 'Attribute 2 name', 'Attribute 2 value(s)', 'Attribute 2 visible',
        'Attribute 2 global', 'Attribute 3 name', 'Attribute 3 value(s)', 'Attribute 3 visible',
        'Attribute 3 global', 'Meta: outline', 'Meta: days', 'Meta: location', 'Meta: overview',
        'Meta: objectives', 'Meta: prerequisites', 'Meta: agenda'
    ]
    woo_final_df = woo_final_df[column_order]

    # 9. Convert to CSV (in memory)
    output_buffer = BytesIO()
    woo_final_df.to_csv(output_buffer, index=False, encoding='utf-8-sig')
    output_buffer.seek(0)

    return output_buffer

def process_file_and_return_csv(uploaded_file):
    """
    - Takes the uploaded file,
    - Processes it,
    - Writes the CSV to a temp file,
    - Returns that path for Gradio to provide as a downloadable file.
    """
    processed_csv_io = process_woocommerce_data_in_memory(uploaded_file)

    with tempfile.NamedTemporaryFile(delete=False, suffix=".csv") as tmp:
        tmp.write(processed_csv_io.getvalue())
        tmp.flush()  # ensure data is written to disk
        temp_path = tmp.name

    return temp_path

app = gr.Interface(
    fn=process_file_and_return_csv,
    inputs=gr.File(label="Upload NetCom CSV", file_types=["text", "csv"]),
    outputs=gr.File(label="Download WooCommerce CSV"),
    title="NetCom to WooCommerce CSV Processor",
    description="Upload your NetCom Reseller Schedule CSV to generate the WooCommerce import-ready CSV."
)

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