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

# Define the button and title styles
styles = """
<style>
/* Button styles */
div.stButton > button {
    color: #ffffff; /* Text color */
    font-size: 20px;
    background-image: linear-gradient(0deg, #a2c2e1 0%, #003b5c 100%); /* Light blue to deep blue gradient */
    border: none;
    padding: 10px 20px;
    cursor: pointer;
    border-radius: 15px;
    display: inline-block;
    width: 100%; /* Make button fit column width */
}
div.stButton > button:hover {
    background-color: #00ff00; /* Hover background color */
    color: #ff0000; /* Hover text color */
}
/* Title styles */
h1 {
    background-image: linear-gradient(0deg, #a2c2e1 0%, #003b5c 100%); /* Light blue to deep blue gradient */
    -webkit-background-clip: text;
    -webkit-text-fill-color: transparent;
    font-size: 36px;
}
</style>
"""

# Set up the Streamlit app
st.markdown(styles, unsafe_allow_html=True)

# Custom gradient title
st.markdown("<h1>CSV and Excel Appender</h1>", unsafe_allow_html=True)
st.write("A website that is used for appending multiple CSV and Excel files and converting them to a desired format.")

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

if uploaded_files:
    # Initialize an empty list to store DataFrames
    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.markdown("<h1>Appended Data</h1>", unsafe_allow_html=True)
    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.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.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("")