app.py

import gradio as gr
import pandas as pd
from datetime import datetime
import pytz
import re

# Updated Gradio app for YunExpress:
# - New output headers (as provided)
# - Email -> Email mapping
# - FOBPrice1 = 2 (replaces UnitPrice1)
# - ZIP padding fix for US 3- and 4-digit ZIPs (no leading apostrophe; sanitize non [A-Za-z0-9 ] chars)
# - RoutingCode logic: US/NO/FR -> HK-ASS-PF; others -> HKTHZXR
# - Save Excel with ZipCode column set to Text (@) to preserve leading zeros without apostrophes

def process_file(file):
    file_name = file.name.lower()
    try:
        if file_name.endswith(('.xls', '.xlsx', '.xlsm')):
            # Read data from the "YunExpress" sheet
            df = pd.read_excel(file, sheet_name="YunExpress")
        else:
            return f"Unsupported file format: {file_name}", None
    except Exception as e:
        return f"Error reading file: {e}", None

    # === New output headers (exact order) ===
    output_headers = [
        "CustomerOrderNo.", "RoutingCode", "Trackingnumber", "AdditionalServices",
        "ShipmentProtectionPlusService", "CustomDeclaredValue", "SignatureService",
        "VatNumber", "EoriNumber", "IossCode", "ManufactureSalesName",
        "UnifiedSocialCreditCode", "CountryCode", "Name", "CertificateCode", "Company",
        "Street", "City", "Province/State", "ZipCode", "phone", "HouseNumber", "Email",
        "PackageNumber", "PackageWeight", "SenderFiastName", "SenderCompany",
        "SenderStreet", "SenderCity", "SenderProvince", "SenderPostalCode",
        "SenderCountry", "SenderTelephone", "SenderEmail", "SenderUSCI", "PlatformName",
        "PlatformProvince", "PlatformAddress", "PlatformPostalCode", "PlatformPhoneNumber",
        "PlatformEmail", "EcommercePlatformCode", "SalesPlatformLink", "CurrencyCode",
        "SKU1", "ItemDescription1", "ForeignItemDescription1", "DeclaredQuantity1",
        "FOBPrice1", "SellingPrice1", "UnitWeight1", "HsCode1", "Remarks1", "SalesLink1",
        "Materials1", "Use1", "Brand1", "ModelType1", "Specs1", "FabricCreationMethod1",
        "ManufacturerID1", "ManufacturerName1", "ManufacturerCountry1",
        "ManufacturerState1", "ManufacturerCity1", "ManufacturerPostalCode1",
        "ManufacturerAddress1", "CargoCategory", "PaymentPlatform", "PaymentAccount",
        "PaymentTransactionNumber"
    ]

    # Initialize empty output DataFrame
    output_df = pd.DataFrame("", index=range(len(df)), columns=output_headers)

    # 1) Order Number -> CustomerOrderNo. (prefix "LB")
    if "Order Number" in df.columns:
        output_df["CustomerOrderNo."] = "LB" + df["Order Number"].astype(str)

    # 2) Name: First + Last + Company -> Name
    if {"Shipping First Name", "Shipping Last Name", "Shipping Company"}.issubset(df.columns):
        first = df["Shipping First Name"].fillna("").astype(str).str.strip()
        last  = df["Shipping Last Name"].fillna("").astype(str).str.strip()
        comp  = df["Shipping Company"].fillna("").astype(str).str.strip()
        output_df["Name"] = (first + " " + last + " " + comp).str.replace(r"\s+", " ", regex=True).str.strip()

    # 3) Street: Address 1 + Address 2 -> Street
    addr1 = df.get("Shipping Address 1", pd.Series([""] * len(df), index=df.index)).fillna("").astype(str).str.strip()
    addr2 = df.get("Shipping Address 2", pd.Series([""] * len(df), index=df.index)).fillna("").astype(str).str.strip()
    output_df["Street"] = (addr1 + " " + addr2).str.replace(r"\s+", " ", regex=True).str.strip()

    # 4) City
    if "Shipping City" in df.columns:
        output_df["City"] = df["Shipping City"]

    # 5) Province/State
    if "Shipping Province" in df.columns:
        output_df["Province/State"] = df["Shipping Province"]

    # 6) CountryCode (before ZIP handling)
    if "Shipping Country Code" in df.columns:
        output_df["CountryCode"] = df["Shipping Country Code"]

    # 7) ZipCode (sanitize; US 3/4-digit -> pad to 5; do NOT add apostrophe)
    if "Shipping ZIP" in df.columns:
        zip_raw = (
            df["Shipping ZIP"]
            .astype(str)
            .str.strip()
            .str.replace(r"\.0$", "", regex=True)  # clean "1234.0"
        )
        # Keep only letters, digits, spaces (avoid "only letters, numbers and spaces" API error)
        zip_clean = zip_raw.str.replace(r"[^A-Za-z0-9 ]+", "", regex=True)

        mask_us  = output_df["CountryCode"].eq("US")
        # For US: if 3 or 4 numeric digits -> zero-fill to 5
        mask_3_4_digits = zip_clean.str.fullmatch(r"\d{3,4}")
        zip_padded = zip_clean.where(~(mask_us & mask_3_4_digits), zip_clean.str.zfill(5))

        # Do NOT add any leading apostrophe
        output_df["ZipCode"] = zip_padded

    # 8) phone
    if "Shipping Address Phone" in df.columns:
        output_df["phone"] = df["Shipping Address Phone"]

    # 9) NEW: Email -> Email (direct mapping)
    if "Email" in df.columns:
        output_df["Email"] = df["Email"]

    # 10) PackageWeight: Total Weight (g) -> kg
    if "Total Weight" in df.columns:
        output_df["PackageWeight"] = df["Total Weight"] / 1000

    # 11) DeclaredQuantity1: sum Quantity per Order Number
    if "Order Number" in df.columns and "Quantity" in df.columns:
        output_df["DeclaredQuantity1"] = df.groupby("Order Number")["Quantity"].transform("sum")

    # Fixed defaults & RoutingCode
    mask = output_df["CustomerOrderNo."].astype(str).str.len() > 0

    # RoutingCode: HK-ASS-PF if US/NO/FR else HKTHZXR
    mask_us_no_fr = mask & output_df["CountryCode"].isin(["US", "NO", "FR"])
    mask_other    = mask & ~output_df["CountryCode"].isin(["US", "NO", "FR"])
    output_df.loc[mask_us_no_fr, "RoutingCode"] = "HK-ASS-PF"
    output_df.loc[mask_other,    "RoutingCode"] = "HKTHZXR"

    # Pricing / descriptions / weights
    output_df.loc[mask, "FOBPrice1"]               = 2          # CHANGED from UnitPrice1=2
    output_df.loc[mask, "CurrencyCode"]            = "USD"
    output_df.loc[mask, "ItemDescription1"]        = "Eye Cosmetic Accessories"
    output_df.loc[mask, "ForeignItemDescription1"] = "Eye Cosmetic Accessories"
    output_df.loc[mask, "UnitWeight1"]             = 0.02

    # EU AdditionalServices = v1
    EU_COUNTRIES = {"AT","BE","BG","CY","CZ","DE","DK","EE","ES","FI",
                    "FR","HR","HU","IE","IT","LT","LU","LV","MT","NL",
                    "PL","PT","RO","SE","SI","SK","GR"}
    mask_eu = mask & output_df["CountryCode"].isin(EU_COUNTRIES)
    output_df.loc[mask_eu, "AdditionalServices"] = "v1"

    # Remove duplicates by CustomerOrderNo.
    output_df = output_df.drop_duplicates(subset=["CustomerOrderNo."], keep="first")

    # Output filename (HK date)
    hk_tz = pytz.timezone("Asia/Hong_Kong")
    today_hk = datetime.now(hk_tz).strftime("%y%m%d")
    output_file_name = f"yunexpress {today_hk}.xlsx"

    # Save to Excel with ZipCode column forced to Text (@) using xlsxwriter
    try:
        import xlsxwriter
        from xlsxwriter.utility import xl_col_to_name

        with pd.ExcelWriter(output_file_name, engine="xlsxwriter") as writer:
            output_df.to_excel(writer, index=False, sheet_name="Sheet1")
            workbook  = writer.book
            worksheet = writer.sheets["Sheet1"]

            # Text format to preserve leading zeros without adding apostrophes
            text_fmt = workbook.add_format({"num_format": "@"})

            # Locate ZipCode column and set entire column to text
            zip_col_idx = output_df.columns.get_loc("ZipCode")  # 0-based index
            col_letter = xl_col_to_name(zip_col_idx)
            worksheet.set_column(f"{col_letter}:{col_letter}", None, text_fmt)
    except Exception:
        # Fallback to default writer if xlsxwriter not available
        output_df.to_excel(output_file_name, index=False)

    return output_df, output_file_name


# Gradio interface
with gr.Blocks(title="Shipping - YunExpress") as demo:
    gr.Markdown("# Shipping - YunExpress")
    with gr.Row():
        file_input = gr.File(label="Upload Excel File with a YunExpress sheet")
        process_button = gr.Button("Process Data")
    with gr.Row():
        output_data = gr.DataFrame()
        output_file_component = gr.File(label="Download Processed File")
    process_button.click(fn=process_file, inputs=[file_input], outputs=[output_data, output_file_component])

    # Links to other tools
    gr.HTML(
        """
        <div style="text-align: center; font-size: 16px; margin-top: 20px;">
            <h3>Shipping Tools</h3>
            <a href="https://huggingface.co/spaces/leadingbridge/shipping-dhl-e-commerce">DHL</a> | 
            <a href="https://huggingface.co/spaces/leadingbridge/shipping-ec-ship">EC-Ship</a> | 
            <a href="https://huggingface.co/spaces/leadingbridge/shipping-fedex">Fedex</a> | 
            <a href="https://huggingface.co/spaces/leadingbridge/shipping-UPS">UPS</a><br> |
            <a href="https://huggingface.co/spaces/leadingbridge/shipping-yunexpress">Yunexpress</a>
        </div>
        <div style="text-align: center; font-size: 16px; margin-top: 20px;">
            <h3>Administration Tools</h3>
            <a href="https://huggingface.co/spaces/leadingbridge/email-template">Email Template</a> | 
            <a href="https://huggingface.co/spaces/leadingbridge/product-feeding">Google Merchant</a> | 
            <a href="https://huggingface.co/spaces/leadingbridge/tss">Order Processing</a>
        </div>
        """
    )

demo.launch()