import pandas as pd
import gradio as gr

def process_data(file):
    try:
        # file adalah path di Hugging Face Spaces
        df = pd.read_excel(file)

        output_data = []
        no = 1

        char1 = char2 = char3 = char4 = ""
        previous_char3 = ""
        nominal = tipe = 0
        anggaran1 = anggaran2 = anggaran3 = 0
        total_nominal = 0

        column_names = df.columns.tolist()

        for _, row in df.iterrows():
            kode = str(row[column_names[0]]).strip()
            anggaran = row[column_names[3]]

            if kode and kode[0] in ["3", "6"]:
                char1 = kode
                anggaran3 = anggaran

            elif kode.startswith("0"):
                char2 = kode

            elif kode != "nan" and kode.isalpha():
                char3 = kode
                previous_char3 = char3
                anggaran1 = anggaran

            elif kode.startswith("5"):
                char4 = kode
                anggaran2 = anggaran

                if pd.isna(row[column_names[9]]):
                    nominal = row[column_names[10]]
                    tipe = row[column_names[13]]
                else:
                    nominal = row[column_names[9]]
                    tipe = row[column_names[11]]

                if char3 == "":
                    char3 = previous_char3

                mak = f"{char1}.{char2}.{char3}.{char4}"

                output_data.append({
                    "No": no,
                    "MAK": mak,
                    "Anggaran3": anggaran3,
                    "Anggaran1": anggaran1,
                    "Anggaran2": anggaran2,
                    "Nominal": nominal,
                    "Tipe": tipe
                })

                no += 1
                char3 = ""

                if pd.notna(nominal):
                    total_nominal += float(nominal)

        output_data.append({
            "No": "",
            "MAK": "",
            "Anggaran3": "",
            "Anggaran1": "",
            "Anggaran2": "",
            "Nominal": f"Total Nominal: {total_nominal:.0f}",
            "Tipe": ""
        })

        output_df = pd.DataFrame(output_data)

        output_file = "/tmp/processed_data.xlsx"
        output_df.to_excel(output_file, index=False)

        return output_file

    except Exception as e:
        return f"Error: {e}"

with gr.Blocks(title="Konvert Mata Anggaran Sakti") as demo:
    gr.Markdown("### Konvert Mata Anggaran Sakti")
    gr.Markdown("Unggah file Excel, sistem akan mengonversi dan menghasilkan file Excel baru.")

    input_file = gr.File(label="Upload Excel File", file_types=[".xlsx"])
    output_file = gr.File(label="Download Hasil")

    btn = gr.Button("Proses")
    btn.click(process_data, inputs=input_file, outputs=output_file)

demo.launch()