import streamlit as st
import pandas as pd
from io import BytesIO
from html_templates import logo, BigQuery_upload_title, table_id_placeholder, uploader, button_styles, tooltip_message_bq, Merger_title, tooltip_message_merger, uploader, Appended_data_title, download_button_styles, spinner_css



st.markdown(logo, unsafe_allow_html=True)
st.logo("alerter_4.jpeg")



st.markdown(Merger_title, unsafe_allow_html=True)
st.write("")
st.markdown(tooltip_message_merger, unsafe_allow_html = True)


st.markdown(uploader, unsafe_allow_html = True)
# File uploader for multiple CSV and Excel files
uploaded_files = st.file_uploader("", type=["csv", "xlsx", "xls"], accept_multiple_files=True)

if uploaded_files:
    # Initialize an empty list to store DataFrames
    st.markdown(spinner_css, unsafe_allow_html=True)
    with st.spinner('Processing your files...'):
        df_list = []

        for i, uploaded_file in enumerate(uploaded_files):
            # Get the file name without extension
            file_name = uploaded_file.name

            # Check the file type and read accordingly
            if uploaded_file.type == "text/csv":
                if i == 0:
                    # For the first file, include the header
                    df = pd.read_csv(uploaded_file)
                    # Store the columns from the first file
                    headers = df.columns
                else:
                    # For subsequent files, set the header using the stored headers
                    df = pd.read_csv(uploaded_file, names=headers, header=0)
            elif uploaded_file.type == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
                if i == 0:
                    # For the first file, include the header
                    df = pd.read_excel(uploaded_file)
                    # Store the columns from the first file
                    headers = df.columns
                else:
                    # For subsequent files, set the header using the stored headers
                    df = pd.read_excel(uploaded_file, names=headers, header=0)

            elif uploaded_file.type == "application/vnd.ms-excel":
            # This is an .xls file
                if i == 0:
                # For the first file, include the header
                    df = pd.read_excel(uploaded_file, engine='xlrd')
                    # Store the columns from the first file
                    headers = df.columns
                else:
                    # For subsequent files, set the header using the stored headers
                    df = pd.read_excel(uploaded_file, names=headers, header=0, engine='xlrd')

            # Add a column for the file name
            df['file_name'] = file_name
            df_list.append(df)

        # Concatenate all DataFrames, ignoring index to avoid duplication
        merged_df = pd.concat(df_list, ignore_index=True)

        # Display the merged DataFrame
        st.dataframe(merged_df)

        # Create a layout with 3 columns for buttons
        col1, col2, col3 = st.columns(3)

    with col1:
        # Provide a download button for the merged CSV file
        csv_output = BytesIO()
        merged_df.to_csv(csv_output, index=False)
        csv_output.seek(0)
        st.markdown(download_button_styles, unsafe_allow_html = True)
        st.download_button(
            label="Download Merged CSV File",
            data=csv_output,
            file_name="merged_data.csv",
            mime="text/csv"
        )

    with col2:
        # Provide an option to convert to Excel
        excel_output = BytesIO()
        with pd.ExcelWriter(excel_output, engine='xlsxwriter') as writer:
            merged_df.to_excel(writer, index=False)
        excel_output.seek(0)
        st.markdown(download_button_styles, unsafe_allow_html = True)
        st.download_button(
            label="Download Merged Excel File",
            data=excel_output,
            file_name="merged_data.xlsx",
            mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        )
else:
    st.write("")