import streamlit as st
import pandas as pd
import io
import base64

st.set_page_config(initial_sidebar_state="collapsed")
def create_excel_file():
    data_structure = {
        "Status": {"pipedrive": [], "bexio_contact": [], "klicktipp": [], "comment": []},
        "Teacher": {"pipedrive": [], "bexio": [], "klicktipp": [], "comment": []},
        "Location": {"pipedrive": [], "bexio": [], "klicktipp": [], "comment": []},
        "Billing": {"pipedrive": [], "bexio": [], "klicktipp": [], "comment": []},
        "Salutation": {"pipedrive": [], "bexio": [], "comment": []}
    }
    
    buffer = io.BytesIO()
    with pd.ExcelWriter(buffer, engine='xlsxwriter') as writer:
        for sheet_name, data in data_structure.items():
            df = pd.DataFrame(data)
            df.to_excel(writer, index=False, sheet_name=sheet_name)
    
    return buffer

def read_excel_file(uploaded_file, sheet_name):
    return pd.read_excel(uploaded_file, sheet_name=sheet_name)

def format_value(value):
    if pd.notna(value):
        if isinstance(value, (int, float)):
            return f"{value:.0f}"
        return str(value)
    return ""

def generate_output(df, check_pointer, sheet_name):
    output_lines = []
    for _, row in df.iterrows():
        pipedrive_value = format_value(row["pipedrive"])
        bexio_value = format_value(row["bexio" if sheet_name != "Status" else "bexio_contact"])
        comment_value = format_value(row["comment"])
        
        if sheet_name != "Salutation" and "klicktipp" in df.columns:
            klicktipp_value = format_value(row["klicktipp"])
        else:
            klicktipp_value = ""
        
        if sheet_name == "Status":
            output_line = f'{{{{if(contains({check_pointer}; {pipedrive_value}); ",{{""comment"": ""{comment_value}"",""bexio_contact"": ""{bexio_value}"",""klicktipp_id"": ""{klicktipp_value}""}}" )}}}}'
        elif sheet_name == "Salutation":
            output_line = f'{{{{if({check_pointer} = "{pipedrive_value}"; ",{{""comment"": ""{comment_value}"",""bexio_salutation"": ""{bexio_value}""}}")}}}}'
        elif sheet_name == "Location":
            output_line = f'{{{{if(contains(map({check_pointer}; "label"); "{pipedrive_value}"); ",{{""comment"": ""{comment_value}"",""bexio_id"": ""{bexio_value}"",""klicktipp_id"": ""{klicktipp_value}""}}")}}}}'
        else:
            output_line = f'{{{{if({check_pointer} = "{pipedrive_value}"; ",{{""comment"": ""{comment_value}"",""bexio_id"": ""{bexio_value}"",""klicktipp_id"": ""{klicktipp_value}""}}")}}}}'
        output_lines.append(output_line)
    return "\n".join(output_lines)

def main():
    st.title("Klicktipp Config Erstellen")

    st.sidebar.header("Erweiterte Einstellungen")
    check_pointer_status = st.sidebar.text_input("Status Check Pointer", value="5.label_ids")
    check_pointer_teacher = st.sidebar.text_input("Teacher Check Pointer", value="5.ea95be8ab4afe56e68db0d16cf66959233bf0e97.email[].value")
    check_pointer_location = st.sidebar.text_input("Location Check Pointer", value="5.df23d6f3996c70b638bf4ac34ed3df8db3f0f596")
    check_pointer_billing = st.sidebar.text_input("Billing Check Pointer", value="5.`055146166b11175a5b0119a4f5a1c0ca4839330f`.label")
    check_pointer_salutation = st.sidebar.text_input("Salutation Check Pointer", value="5.`3addae121d321616cd7eb175541d3a439ecbe1c0`.label")

    if st.button("Vorlage Excel herunterladen"):
        buffer = create_excel_file()
        b64 = base64.b64encode(buffer.getvalue()).decode()
        href = f'<a href="data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,{b64}" download="empty_template.xlsx">Excel herunterladen</a>'
        st.markdown(href, unsafe_allow_html=True)

    uploaded_file = st.file_uploader("Excel auswählen, um Konfig zu erstellen", type="xlsx")

    if uploaded_file is not None:
        if st.button("Verarbeitung starten"):
            sheets = ["Status", "Teacher", "Location", "Billing", "Salutation"]
            check_pointers = [check_pointer_status, check_pointer_teacher, check_pointer_location, check_pointer_billing, check_pointer_salutation]
            
            all_output_lines = []
            for sheet, check_pointer in zip(sheets, check_pointers):
                df = read_excel_file(uploaded_file, sheet)
                result = generate_output(df, check_pointer, sheet)
                all_output_lines.extend(result)  # Extend the list instead of appending

            final_output = f'''
{{
  "data": [
    {{"comment": "Pipedrive","bexio_id": "88","klicktipp_id": "11093548"}}
    {"".join(all_output_lines)}
  ]
}}
'''
            st.text("Fertig. Kopiere diesen Code und füge ihn in Make ein.")
            st.code(final_output, language="json")

if __name__ == "__main__":
    main()