import streamlit as st
import pandas as pd
import json
import re
from io import BytesIO

st.set_page_config(page_title="JSON to Excel Converter", layout="wide")

st.title("JSON to Excel Converter")

# Text Area
json_text = st.text_area(
    label="JSON Data",
    height=300,
    placeholder="Paste your JSON data here..."
)

def clean_json(text):
    """Remove trailing commas to avoid JSON errors"""
    text = re.sub(r",\s*}", "}", text)
    text = re.sub(r",\s*]", "]", text)
    return text

def flatten_json(data):
    rows = []

    def recurse(obj):
        if isinstance(obj, dict):
            for k, v in obj.items():
                rows.append({
                    "Object Name / Key Name": k,
                    "English_Values": None if isinstance(v, dict) else v
                })
                recurse(v)

    recurse(data)
    return rows

# Button
if st.button("Generate Excel Data"):
    if not json_text.strip():
        st.warning("Please paste JSON data first.")
    else:
        try:
            cleaned_json = clean_json(json_text)
            parsed_json = json.loads(cleaned_json)

            flattened_rows = flatten_json(parsed_json)
            df = pd.DataFrame(flattened_rows)

            # Replace None with NULL
            df["English_Values"] = df["English_Values"].fillna("NULL")

            st.subheader("Excel Output (Copy & Paste Ready)")
            st.dataframe(
                df,
                use_container_width=True,
                hide_index=True
            )

            # -------- Excel Export --------
            output = BytesIO()
            with pd.ExcelWriter(output, engine="xlsxwriter") as writer:
                df.to_excel(writer, index=False, sheet_name="JSON_Data")

            excel_data = output.getvalue()

            st.download_button(
                label="📥 Download Excel File",
                data=excel_data,
                file_name="JSON_To_Excel.xlsx",
                mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            )

        except Exception as e:
            st.error(f"Invalid JSON format ❌\n\n{e}")