import io

import numpy as np
import pandas as pd
import streamlit as st


st.set_page_config(page_title="Aplikasi Penggabungan THC Final", layout="wide")

st.title("Aplikasi Pengolahan THC")
st.divider()
st.caption("Ini digunakan untuk menyatukan file THC FINAL, TAK, TLP dan KDP")
st.caption("Satukan terlebih dahulu file yang sudah di vlookup, di poin nomor 4 pada site pengolahan")
st.caption(":red[pivot_TLP_na.xlsx ke TLP.xlsx], :red[pivot_KDP_na.xlsx ke KDP.xlsx]")
st.divider()

st.subheader("File yang dibutuhkan:")
st.write("1. THC FINAL.xlsx")
st.write("2. TAK.xlsx")
st.write("3. TLP.xlsx")
st.write("4. KDP.xlsx")

EXPECTED_FILES = ["THC FINAL.xlsx", "TAK.xlsx", "TLP.xlsx", "KDP.xlsx"]
KEY_COLUMNS = [
    "ID ANGGOTA",
    "DUMMY",
    "NAMA",
    "CENTER",
    "KEL",
    "HARI",
    "JAM",
    "SL",
    "TRANS. DATE",
]
DESIRED_ORDER = [
    "ID ANGGOTA",
    "DUMMY",
    "NAMA",
    "CENTER",
    "KEL",
    "HARI",
    "JAM",
    "SL",
    "TRANS. DATE",
    "Db Qurban",
    "Cr Qurban",
    "Db Khusus",
    "Cr Khusus",
    "Db Sihara",
    "Cr Sihara",
    "Db Pensiun",
    "Cr Pensiun",
    "Db Pokok",
    "Cr Pokok",
    "Db SIPADAN",
    "Cr SIPADAN",
    "Db Sukarela",
    "Cr Sukarela",
    "Db Wajib",
    "Cr Wajib",
    "Db Total",
    "Cr Total",
    "Db PTN",
    "Cr PTN",
    "Db PRT",
    "Cr PRT",
    "Db DTP",
    "Cr DTP",
    "Db PMB",
    "Cr PMB",
    "Db PRR",
    "Cr PRR",
    "Db PSA",
    "Cr PSA",
    "Db PU",
    "Cr PU",
    "Db Total2",
    "Cr Total2",
]
NUMERIC_COLUMNS = [col for col in DESIRED_ORDER if col not in KEY_COLUMNS]


@st.cache_data(show_spinner=False)
def load_excel(file_obj):
    return pd.read_excel(file_obj, engine="openpyxl")


def ensure_columns(df, columns, default_value=0):
    for col in columns:
        if col not in df.columns:
            df[col] = default_value
    return df


def process_dataframe(df, new_columns, rename_dict):
    df = ensure_columns(df.copy(), new_columns)
    df = df.rename(columns=rename_dict)
    df = ensure_columns(df, DESIRED_ORDER)
    return df[DESIRED_ORDER]


def normalize_numeric(df, numeric_columns):
    df = df.copy()
    for col in numeric_columns:
        if col not in df.columns:
            df[col] = 0
            continue

        cleaned = (
            df[col]
            .astype(str)
            .str.replace(",", "", regex=False)
            .str.replace(" ", "", regex=False)
        )
        df[col] = pd.to_numeric(cleaned, errors="coerce").fillna(0)
    return df


uploaded_files = st.file_uploader(
    "Unggah file Excel", accept_multiple_files=True, type=["xlsx"]
)

if uploaded_files:
    dfs = {}
    for file in uploaded_files:
        try:
            dfs[file.name] = load_excel(file)
        except Exception as exc:
            st.error(f"Gagal membaca {file.name}: {exc}")

    missing_files = [name for name in EXPECTED_FILES if name not in dfs]
    if missing_files:
        st.warning(f"File yang belum diunggah: {', '.join(missing_files)}")

    combined_df_list = []

    if "THC FINAL.xlsx" in dfs:
        df_thc = dfs["THC FINAL.xlsx"].copy()
        df_thc = ensure_columns(df_thc, DESIRED_ORDER)
        df_thc = df_thc[DESIRED_ORDER]
        st.write("THC FINAL:")
        st.dataframe(df_thc, use_container_width=True)
        combined_df_list.append(df_thc)

    if "TAK.xlsx" in dfs:
        df_tak = dfs["TAK.xlsx"]
        new_columns_tak = [
            "DEBIT_PINJAMAN UMUM",
            "DEBIT_PINJAMAN RENOVASI RUMAH",
            "DEBIT_PINJAMAN SANITASI",
            "DEBIT_PINJAMAN ARTA",
            "DEBIT_PINJAMAN MIKROBISNIS",
            "DEBIT_PINJAMAN DT. PENDIDIKAN",
            "DEBIT_PINJAMAN PERTANIAN",
            "DEBIT_TOTAL",
            "CREDIT_PINJAMAN UMUM",
            "CREDIT_PINJAMAN RENOVASI RUMAH",
            "CREDIT_PINJAMAN SANITASI",
            "CREDIT_PINJAMAN ARTA",
            "CREDIT_PINJAMAN MIKROBISNIS",
            "CREDIT_PINJAMAN DT. PENDIDIKAN",
            "CREDIT_PINJAMAN PERTANIAN",
            "CREDIT_TOTAL",
        ]
        rename_dict_tak = {
            "KELOMPOK": "KEL",
            "DEBIT_PINJAMAN ARTA": "Db PRT",
            "DEBIT_PINJAMAN DT. PENDIDIKAN": "Db DTP",
            "DEBIT_PINJAMAN MIKROBISNIS": "Db PMB",
            "DEBIT_PINJAMAN SANITASI": "Db PSA",
            "DEBIT_PINJAMAN UMUM": "Db PU",
            "DEBIT_PINJAMAN RENOVASI RUMAH": "Db PRR",
            "DEBIT_PINJAMAN PERTANIAN": "Db PTN",
            "DEBIT_TOTAL": "Db Total2",
            "CREDIT_PINJAMAN ARTA": "Cr PRT",
            "CREDIT_PINJAMAN DT. PENDIDIKAN": "Cr DTP",
            "CREDIT_PINJAMAN MIKROBISNIS": "Cr PMB",
            "CREDIT_PINJAMAN SANITASI": "Cr PSA",
            "CREDIT_PINJAMAN UMUM": "Cr PU",
            "CREDIT_PINJAMAN RENOVASI RUMAH": "Cr PRR",
            "CREDIT_PINJAMAN PERTANIAN": "Cr PTN",
            "CREDIT_TOTAL": "Cr Total2",
        }
        df_tak = process_dataframe(df_tak, new_columns_tak, rename_dict_tak)
        df_tak = normalize_numeric(df_tak, NUMERIC_COLUMNS)
        st.write("TAK FINAL:")
        st.dataframe(df_tak, use_container_width=True)
        combined_df_list.append(df_tak)

    if "TLP.xlsx" in dfs:
        df_tlp = dfs["TLP.xlsx"]
        new_columns_tlp = [
            "DEBIT_Simpanan Pensiun",
            "DEBIT_Simpanan Pokok",
            "DEBIT_Simpanan Sukarela",
            "DEBIT_Simpanan Wajib",
            "DEBIT_Simpanan Hari Raya",
            "DEBIT_Simpanan Qurban",
            "DEBIT_Simpanan Sipadan",
            "DEBIT_Simpanan Khusus",
            "CREDIT_Simpanan Pensiun",
            "CREDIT_Simpanan Pokok",
            "CREDIT_Simpanan Sukarela",
            "CREDIT_Simpanan Wajib",
            "CREDIT_Simpanan Hari Raya",
            "CREDIT_Simpanan Qurban",
            "CREDIT_Simpanan Sipadan",
            "CREDIT_Simpanan Khusus",
        ]
        rename_dict_tlp = {
            "KELOMPOK": "KEL",
            "DEBIT_Simpanan Hari Raya": "Db Sihara",
            "DEBIT_Simpanan Pensiun": "Db Pensiun",
            "DEBIT_Simpanan Pokok": "Db Pokok",
            "DEBIT_Simpanan Sukarela": "Db Sukarela",
            "DEBIT_Simpanan Wajib": "Db Wajib",
            "DEBIT_Simpanan Qurban": "Db Qurban",
            "DEBIT_Simpanan Sipadan": "Db SIPADAN",
            "DEBIT_Simpanan Khusus": "Db Khusus",
            "DEBIT_TOTAL": "Db Total",
            "CREDIT_Simpanan Hari Raya": "Cr Sihara",
            "CREDIT_Simpanan Pensiun": "Cr Pensiun",
            "CREDIT_Simpanan Pokok": "Cr Pokok",
            "CREDIT_Simpanan Sukarela": "Cr Sukarela",
            "CREDIT_Simpanan Wajib": "Cr Wajib",
            "CREDIT_Simpanan Qurban": "Cr Qurban",
            "CREDIT_Simpanan Sipadan": "Cr SIPADAN",
            "CREDIT_Simpanan Khusus": "Cr Khusus",
            "CREDIT_TOTAL": "Cr Total",
        }
        df_tlp = process_dataframe(df_tlp, new_columns_tlp, rename_dict_tlp)
        df_tlp = normalize_numeric(df_tlp, NUMERIC_COLUMNS)
        st.write("TLP FINAL:")
        st.dataframe(df_tlp, use_container_width=True)
        combined_df_list.append(df_tlp)

    if "KDP.xlsx" in dfs:
        try:
            df_kdp = dfs["KDP.xlsx"]
            new_columns_kdp = [
                "DEBIT_Simpanan Pensiun",
                "DEBIT_Simpanan Pokok",
                "DEBIT_Simpanan Sukarela",
                "DEBIT_Simpanan Wajib",
                "DEBIT_Simpanan Hari Raya",
                "DEBIT_Simpanan Qurban",
                "DEBIT_Simpanan Sipadan",
                "DEBIT_Simpanan Khusus",
                "CREDIT_Simpanan Pensiun",
                "CREDIT_Simpanan Pokok",
                "CREDIT_Simpanan Sukarela",
                "CREDIT_Simpanan Wajib",
                "CREDIT_Simpanan Hari Raya",
                "CREDIT_Simpanan Qurban",
                "CREDIT_Simpanan Sipadan",
                "CREDIT_Simpanan Khusus",
                "DEBIT_PU",
                "CREDIT_PU",
                "DEBIT_TOTAL2",
                "CREDIT_TOTAL2",
            ]
            rename_dict_kdp = {
                "KELOMPOK": "KEL",
                "DEBIT_Simpanan Hari Raya": "Db Sihara",
                "DEBIT_Simpanan Pensiun": "Db Pensiun",
                "DEBIT_Simpanan Pokok": "Db Pokok",
                "DEBIT_Simpanan Sukarela": "Db Sukarela",
                "DEBIT_Simpanan Wajib": "Db Wajib",
                "DEBIT_Simpanan Qurban": "Db Qurban",
                "DEBIT_Simpanan Sipadan": "Db SIPADAN",
                "DEBIT_Simpanan Khusus": "Db Khusus",
                "DEBIT_TOTAL": "Db Total",
                "CREDIT_Simpanan Hari Raya": "Cr Sihara",
                "CREDIT_Simpanan Pensiun": "Cr Pensiun",
                "CREDIT_Simpanan Pokok": "Cr Pokok",
                "CREDIT_Simpanan Sukarela": "Cr Sukarela",
                "CREDIT_Simpanan Wajib": "Cr Wajib",
                "CREDIT_Simpanan Qurban": "Cr Qurban",
                "CREDIT_Simpanan Sipadan": "Cr SIPADAN",
                "CREDIT_Simpanan Khusus": "Cr Khusus",
                "CREDIT_TOTAL": "Cr Total",
                "DEBIT_PU": "Db PU",
                "CREDIT_PU": "Cr PU",
                "DEBIT_TOTAL2": "Db Total2",
                "CREDIT_TOTAL2": "Cr Total2",
            }

            df_kdp = process_dataframe(df_kdp, new_columns_kdp, rename_dict_kdp)
            df_kdp = df_kdp.loc[:, ~df_kdp.columns.duplicated()]
            df_kdp = normalize_numeric(df_kdp, NUMERIC_COLUMNS)

            numeric_group_cols = [col for col in NUMERIC_COLUMNS if col in df_kdp.columns]
            df_kdp = df_kdp.groupby(KEY_COLUMNS, as_index=False)[numeric_group_cols].sum()

            df_kdp = ensure_columns(df_kdp, DESIRED_ORDER)
            df_kdp = df_kdp[DESIRED_ORDER]

            st.write("KDP FINAL:")
            st.dataframe(df_kdp, use_container_width=True)
            combined_df_list.append(df_kdp)

        except Exception as exc:
            st.error(f"Error pada pemrosesan KDP: {exc}")

    combined_df_list = [df for df in combined_df_list if not df.empty]

    if combined_df_list:
        try:
            combined_df = pd.concat(combined_df_list, ignore_index=True)
            combined_df = normalize_numeric(combined_df, NUMERIC_COLUMNS)

            st.write("Combined DataFrame:")
            st.dataframe(combined_df, use_container_width=True)

            buffer = io.BytesIO()
            with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
                combined_df.to_excel(writer, index=False, sheet_name="Sheet1")
            buffer.seek(0)

            st.download_button(
                label="Unduh Format data THC gabungan.xlsx",
                data=buffer.getvalue(),
                file_name="Format data THC gabungan.xlsx",
                mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            )
        except Exception as exc:
            st.error(f"Terjadi kesalahan saat menggabungkan data: {exc}")
    else:
        st.error("Tidak ada DataFrame valid untuk diproses.")