src/streamlit_app.py

import streamlit as st
from pathlib import Path
import pandas as pd
import re
import json
import warnings
from io import BytesIO

warnings.filterwarnings("ignore", category=UserWarning, module="openpyxl")

# --- helper functions (kept from your script) ---
STYLE_PATTERNS = [re.compile(p, re.I) for p in [
    r"^style[_\s\-]?id$", r"styleid", r"style[_\s\-]?code", r"^sku$"
]]

def find_styleid_column(columns):
    for col in columns:
        s = str(col)
        for p in STYLE_PATTERNS:
            if p.search(s):
                return col
    for col in columns:
        low = str(col).lower()
        if 'style' in low and 'id' in low:
            return col
    return None


def find_brand_size_start(columns):
    for col in columns:
        s = str(col).lower()
        if "brand" in s and "size" in s:
            return col
    for col in columns:
        if "size" in str(col).lower():
            return col
    return None


def unique_preserve_order(seq):
    seen = set()
    out = []
    for x in seq:
        if x not in seen:
            seen.add(x)
            out.append(x)
    return out


# --- Streamlit UI ---
st.set_page_config(page_title="Size Chart Merger", layout="wide")
st.title("Size Chart \u2194 Product Details Merger")
st.write("Upload a Size Chart workbook and a Product Details workbook (matching sheet names). This app merges size columns into product details.")

col1, col2 = st.columns(2)
with col1:
    size_file = st.file_uploader("Upload Size Chart Excel", type=["xlsx", "xlsm"], key="size")
with col2:
    prod_file = st.file_uploader("Upload Product Details Excel", type=["xlsx", "xlsm"], key="prod")

output_name = st.text_input("Output filename (optional)", value="input.xlsx")
show_logs = st.checkbox("Show detailed log", value=True)

if st.button("Run merge"):
    if size_file is None or prod_file is None:
        st.error("Please upload both files before running the merge.")
    else:
        try:
            size_xl = pd.ExcelFile(size_file, engine="openpyxl")
            prod_xl = pd.ExcelFile(prod_file, engine="openpyxl")
            size_sheets = size_xl.sheet_names
            prod_sheets = prod_xl.sheet_names

            product_dfs = {name: prod_xl.parse(name, dtype=str) for name in prod_sheets}
            log = []

            progress_bar = st.progress(0)
            total = len(size_sheets)

            for i, sheet_name in enumerate(size_sheets):
                # update progress
                progress_bar.progress(int((i / max(total, 1)) * 100))
                st.write(f"Processing sheet: **{sheet_name}**")

                if sheet_name not in prod_sheets:
                    log.append(f"Skipped '{sheet_name}': not present in Product Details.")
                    continue

                size_df = size_xl.parse(sheet_name, dtype=str)
                prod_df = product_dfs[sheet_name]

                size_df.columns = [str(c) for c in size_df.columns]
                prod_df.columns = [str(c) for c in prod_df.columns]

                style_col_size = find_styleid_column(size_df.columns)
                style_col_prod = find_styleid_column(prod_df.columns)

                if style_col_size is None:
                    log.append(f"Sheet '{sheet_name}': could not detect style id in Size Chart.")
                    continue

                if style_col_prod is None:
                    style_col_prod = style_col_size
                    prod_df[style_col_prod] = pd.NA
                    log.append(f"Sheet '{sheet_name}': Product Details missing style id; created '{style_col_prod}'.")

                brand_size_col = find_brand_size_start(size_df.columns)
                if brand_size_col is None:
                    log.append(f"Sheet '{sheet_name}': no size column found. Skipping.")
                    continue

                size_cols = list(size_df.columns)
                start_idx = size_cols.index(brand_size_col)
                size_columns_to_merge = size_cols[start_idx:]

                if brand_size_col not in prod_df.columns:
                    prod_df[brand_size_col] = pd.NA
                    log.append(f"Sheet '{sheet_name}': created '{brand_size_col}' in Product Details.")

                for col in size_columns_to_merge:
                    if col not in prod_df.columns:
                        prod_df[col] = pd.NA
                        log.append(f"Sheet '{sheet_name}': inserted missing column '{col}'.")

                long = size_df.melt(id_vars=[style_col_size], value_vars=size_columns_to_merge,
                                    var_name="col_name", value_name="value")

                long["value"] = long["value"].astype(str).str.strip()
                invalid = long["value"].isin(["", "nan", "none", "na"])
                long = long[~invalid]

                if long.empty:
                    log.append(f"Sheet '{sheet_name}': no valid size entries to merge.")
                    continue

                grouped = (
                    long.groupby([style_col_size, "col_name"])['value']
                    .apply(lambda s: json.dumps(unique_preserve_order(list(s)), ensure_ascii=False))
                    .reset_index()
                )

                pivot = grouped.pivot(index=style_col_size, columns="col_name", values="value")
                pivot.reset_index(inplace=True)
                pivot.rename(columns={style_col_size: style_col_prod}, inplace=True)

                merged = prod_df.merge(pivot, on=style_col_prod, how="outer", suffixes=("", "_new"))

                for col in size_columns_to_merge:
                    newcol = col + "_new"
                    if newcol in merged.columns:
                        merged[col] = merged[newcol].combine_first(merged[col])
                        merged.drop(columns=newcol, inplace=True)

                product_dfs[sheet_name] = merged
                log.append(f"Sheet '{sheet_name}': merged {pivot.shape[0]} style ids.")

            progress_bar.progress(100)

            # write result to an in-memory Excel file
            output = BytesIO()
            with pd.ExcelWriter(output, engine="xlsxwriter") as writer:
                for name, df in product_dfs.items():
                    df.to_excel(writer, sheet_name=name[:31], index=False)
            output.seek(0)

            st.success("Merge complete!")
            st.write(f"Output ready: **{output_name}**")

            if show_logs:
                st.subheader("Merge Log")
                for line in log:
                    st.write("- ", line)

            # show previews and download
            st.subheader("Preview of merged sheets")
            for name, df in product_dfs.items():
                with st.expander(f"Sheet: {name} ({len(df)} rows)"):
                    st.dataframe(df.head(200))

            st.download_button(
                label="Download merged workbook",
                data=output.getvalue(),
                file_name=output_name if output_name.endswith('.xlsx') else output_name + '.xlsx',
                mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            )

        except Exception as e:
            st.exception(e)