import gradio as gr
import pandas as pd
import openpyxl
import os

# -------------------------
# GLOBAL VARIABLES
# -------------------------
merged_data = pd.DataFrame()
total_ads_cost = 0.0


# ============================================================
# STEP 1 — USER UPLOADS EXCEL FILES → GENERATE SHEET2
# ============================================================
def step1_generate_sheet2(files):
    global merged_data, total_ads_cost

    if not files:
        return None, "❌ Please upload at least one Excel file."

    merged_data = pd.DataFrame()
    total_ads_cost = 0.0

    for f in files:
        try:
            # Gradio 6.x gives file path directly
            filepath = str(f)

            df = pd.read_excel(
                filepath,
                sheet_name="Order Payments",
                header=1,
                skiprows=[2]
            )

            df["Source_File"] = os.path.basename(filepath)

            merged_data = pd.concat(
                [merged_data, df],
                ignore_index=True
            )

            # -------------------------
            # Ads Cost (3rd Sheet)
            # -------------------------
            ads_df = pd.read_excel(
                filepath,
                sheet_name=2,
                skiprows=2
            )

            ads_total = pd.to_numeric(
                ads_df.iloc[:, 7],
                errors="coerce"
            ).sum()

            total_ads_cost += ads_total

        except Exception as e:
            print(f"Skipping {filepath}: {e}")

    # -------------------------
    # Filter Required Statuses
    # -------------------------
    # Auto detect status column
    status_column = None

    for col in merged_data.columns:
      if "status" in str(col).lower():
        status_column = col
        break

    if status_column is None:
        return None, f"❌ Status column not found.\nColumns: {list(merged_data.columns)}"

    # Auto detect SKU column
    sku_column = None

    for col in merged_data.columns:
        if "sku" in str(col).lower():
           sku_column = col
           break

    if sku_column is None:
        return None, f"❌ SKU column not found.\nColumns: {list(merged_data.columns)}"

    status_keep = [
        "Delivered",
        "Return",
        "Exchange",
        "Shipped"
    ]

    merged_data = merged_data[
       merged_data[status_column].astype(str).isin(status_keep)
    ]

    merged_data = merged_data.sort_values(by=sku_column)

    # -------------------------
    # Create Sheet2
    # -------------------------
    sheet2 = pd.DataFrame()

    sheet2["Supplier SKU"] = (
         merged_data[sku_column]
         .dropna()
         .unique()
    )

    sheet2["Cost"] = ""
    sheet2["Final Settlement Amount"] = ""
    sheet2["Final Purchase"] = ""
    sheet2["Total Sale"] = ""

    filename = "Sheet2_Fill_Cost.xlsx"

    sheet2.to_excel(
        filename,
        index=False
    )

    return (
        filename,
        "✅ Sheet2 created! Download and fill COST column."
    )


# ============================================================
# STEP 2 — USER UPLOADS FILLED SHEET2 → FINAL REPORT
# ============================================================
def step2_generate_final(sheet2_file):
    global merged_data, total_ads_cost

    if merged_data.empty:
        return None, "❌ You must complete Step 1 first."

    if sheet2_file is None:
        return None, "❌ Upload the filled Sheet2 Excel file."

    filepath = str(sheet2_file)

    sheet2_filled = pd.read_excel(filepath)

    # -------------------------
    # Merge Cost
    # -------------------------
    final_sheet1 = merged_data.merge(
        sheet2_filled[
            ["Supplier SKU", "Cost"]
        ],
        on="Supplier SKU",
        how="left"
    )

    final_sheet1["Cost"] = pd.to_numeric(
        final_sheet1["Cost"],
        errors="coerce"
    ).fillna(0)

    final_sheet1["Quantity"] = pd.to_numeric(
        final_sheet1["Quantity"],
        errors="coerce"
    ).fillna(0)

    # Purchase
    final_sheet1["Purchase"] = (
        final_sheet1["Cost"]
        * final_sheet1["Quantity"]
    )

    # -------------------------
    # Summary Sheet
    # -------------------------
    sheet2_final = (
        final_sheet1.groupby("Supplier SKU")
        .agg({
            "Final Settlement Amount": "sum",
            "Purchase": "sum"
        })
        .reset_index()
    )

    sheet2_final["Total Sale"] = (
        sheet2_final["Final Settlement Amount"]
        - sheet2_final["Purchase"]
    )

    sheet2_final = sheet2_final.sort_values(
        by="Total Sale"
    )

    # -------------------------
    # Ads Cost
    # -------------------------
    sheet2_final["Ads Cost"] = 0.0

    if not sheet2_final.empty:
        sheet2_final.loc[
            sheet2_final.index[0],
            "Ads Cost"
        ] = round(total_ads_cost, 2)

    # Rename Columns
    sheet2_final.columns = [
        "Supplier SKU",
        "Final Settlement Amount",
        "Final Purchase",
        "Total Sale",
        "Ads Cost"
    ]

    sheet2_final.insert(
        1,
        "Cost",
        sheet2_filled["Cost"]
    )

    # -------------------------
    # Save Output
    # -------------------------
    output_file = "Final_Meesho_Report.xlsx"

    with pd.ExcelWriter(output_file) as writer:
        final_sheet1.to_excel(
            writer,
            sheet_name="Sheet1",
            index=False
        )

        sheet2_final.to_excel(
            writer,
            sheet_name="Sheet2",
            index=False
        )

    # -------------------------
    # Excel Formula
    # -------------------------
    wb = openpyxl.load_workbook(output_file)

    ws = wb["Sheet1"]

    ws["M1"] = "Purchase"

    last_row = ws.max_row

    for row in range(2, last_row + 1):
        ws[f"M{row}"] = (
            f'=VLOOKUP(E{row},Sheet2!$A:$B,2,FALSE)*I{row}'
        )

    wb.save(output_file)

    return (
        output_file,
        "🎉 Final Profit Report generated successfully!"
    )


# ============================================================
# UI
# ============================================================
with gr.Blocks(title="MeeProfit Analyzer") as app:

    gr.Markdown(
        "# 📊 MeeProfit Analyzer\n"
        "Smart Excel Profit Automation for Meesho Sellers"
    )

    # STEP 1
    with gr.Tab("✨ Step 1 — Create Cost Sheet"):

        gr.Markdown(
            """
Upload Meesho Order Payments files.

Download generated Sheet2 and fill Cost column.
            """
        )

        files_step1 = gr.File(
            file_count="multiple",
            label="📂 Upload Excel Files"
        )

        btn1 = gr.Button(
            "🚀 Generate Sheet2"
        )

        sheet2_download = gr.File(
            label="⬇️ Download Sheet2"
        )

        msg1 = gr.Markdown()

        btn1.click(
            step1_generate_sheet2,
            inputs=files_step1,
            outputs=[
                sheet2_download,
                msg1
            ]
        )

    # STEP 2
    with gr.Tab("📘 Step 2 — Generate Final Report"):

        gr.Markdown(
            """
Upload filled Sheet2 file.
            """
        )

        file_step2 = gr.File(
            label="📂 Upload Filled Sheet2"
        )

        btn2 = gr.Button(
            "📊 Generate Final Report"
        )

        final_download = gr.File(
            label="⬇️ Download Final Report"
        )

        msg2 = gr.Markdown()

        btn2.click(
            step2_generate_final,
            inputs=file_step2,
            outputs=[
                final_download,
                msg2
            ]
        )

app.launch()