import pandas as pd
import numpy as np
import streamlit as st
import streamlit as ste
from typing import Optional, List, Dict, Any

from st_aggrid import AgGrid
from st_aggrid.grid_options_builder import GridOptionsBuilder

from io import BytesIO
import xlsxwriter
from datetime import date, datetime

from helpertools_TM import helpers

helper = helpers()


class makeInterface:
    """
    A class to create and manage the user interface for data validation and processing.
    
    This class handles file uploads, creates the interface layout, manages validation checks,
    and provides functionality for displaying and downloading results.

    Attributes:
        valori: Configuration values and constants for the interface
        ANNO_RIFERIMENTO (str): The reference year for data validation
        DEBUGWARNINGS (bool): Flag for showing debug warnings
        TOLLERANZAZERO (bool): Flag for zero tolerance mode
        ERRORDICT (dict): Dictionary of error types and descriptions
        ERROR_ICONS (dict): Dictionary mapping error types to their icons
    """

    def __init__(self, valori):
        """
        Initialize the interface with configuration values.

        Args:
            valori: Object containing configuration values and constants
        """
        self.valori = valori
        # self.ERRDICT = ERRDICT
        # st.write(ERRDICT)
        # Add spacer at the top of sidebar to push elements down
        st.sidebar.markdown('<div style="margin-top: 220px;"></div>', unsafe_allow_html=True)
        # st.write(self.ERRDICT)
        self.ANNO_RIFERIMENTO = st.sidebar.selectbox(
            "ANNO RIFERIMENTO", (["2023","2024", "2025", "2026"]), index=1
        )
        self.make_sidebar()
        self.DEBUGWARNINGS = False
        self.TOLLERANZAZERO = False

        self.STATUS = st.sidebar.empty()
        self.STATUS2 = st.sidebar.empty()
        self.STATUS3 = st.sidebar.empty()

        try:
            self.ERRORDICT = valori.ERRORDICT[self.ANNO_RIFERIMENTO]
            self.ERROR_ICONS = valori.ERROR_ICONS
        except Exception as e:
            self.STATUS.error(
                f"Non e' stato possibile creare la lista dei controlli. Forse non sono stati definiti per l'anno {self.ANNO_RIFERIMENTO}? Errore: {e}"
            )
            if self.TOLLERANZAZERO or self.DEBUGWARNINGS:
                st.error("Elaborazione interrotta")
                st.stop()

        self.make_form()

        # Use the unified file upload method instead of the separate ones
        self.upload_all_files()
        self.choose_checks()
        self.apply_custom_theme()

    def apply_custom_theme(self):
        """Apply custom styling to improve the overall appearance of the interface."""
        st.markdown("""
        <style>
            .main {
                padding: 1rem 1rem;
            }
            .stTabs [data-baseweb="tab-list"] {
                gap: 10px;
            }
            .stTabs [data-baseweb="tab"] {
                height: 50px;
                white-space: pre-wrap;
                border-radius: 5px 5px 0px 0px;
                padding: 10px 16px;
                background-color: #f0f2f6;
            }
            .stTabs [aria-selected="true"] {
                background-color: #4e8cff !important;
                color: white !important;
            }
            /* Success message styling */
            div.element-container div[data-testid="stAlert"] {
                border-radius: 8px;
            }
            /* Button styling */
            .stButton button {
                border-radius: 6px;
                font-weight: 500;
                padding: 0.5rem 1rem;
                transition: all 0.3s;
            }
            .stButton button:hover {
                transform: translateY(-2px);
                box-shadow: 0 4px 8px rgba(0,0,0,0.1);
            }
        </style>
        """, unsafe_allow_html=True)

    def get_version(self) -> str:
        return "Interface 1.0.0"

    def make_sidebar(self):
        """
        Create the sidebar interface with configuration options.

        Creates checkboxes and radio buttons for various settings including:
        - Integrity message display
        - Date string conversion
        - Processing interruption settings
        - Constants display
        - General debug mode
        """

        # st.sidebar.info("Einstellungen")
        # expander = st.sidebar.expander("Impostazioni generali")
        with st.sidebar.expander("Configurazione"):
            self.COMUNETEDESCO = st.checkbox(
                "Mostra comuni in tedesco", False
            )
            self.MOSTRAERRORIINTEGRITA = st.checkbox(
                "Mostra messaggi integrita' strutturale", True
            )
            self.CONVERTIDATESTRINGHE = st.checkbox(
                "Convertire le date in stringhe", True
            )
            self.INTERRUZIONE = st.radio(
                "Interrompere elaborazione se...",
                ("Non interrompere", "Warnings", "Tolleranza zero"),
            )

            if self.INTERRUZIONE == "Warnings":
                self.DEBUGWARNINGS = True
            elif self.INTERRUZIONE == "Tolleranza zero":
                self.TOLLERANZAZERO = True

            self.MOSTRACOSTANTI = st.checkbox("Mostra costanti per anno riferimento")
            if self.MOSTRACOSTANTI:
                try:
                    st.info(self.valori.COSTANTI[self.ANNO_RIFERIMENTO])
                except:
                    st.error("Impossibile mostrare costanti")

            self.DEBUGGENERAL = st.checkbox("Debug generale")




#    def get_file_list(self):
#        """
#        Create a file uploader for selecting multiple Excel files.
#        """
#        self.uploaded_files = st.file_uploader(
#            "ExcelDateienAuswaehlen", accept_multiple_files=True
#        )
#        # st.write(self.filelist)

    def make_expander(self, title1, title2, count, dftemp):
        """
        Create an expandable section to display data results.

        Args:
            title1 (str): Primary title for the expander
            title2 (str): Secondary title/description
            count (int): Number of occurrences found
            dftemp (pd.DataFrame): DataFrame containing the results to display
        """
        with st.expander(f"Trovate **{count}** occorrenze per {title1}"):
            st.info(title2)
            # st.write(dftemp)
            # Create a grid display of the sorted dataframe
            self.make_grid(
                dftemp.sort_values(by=["Cognome", "Nome"]),
                title1,
            )

            self.download_excel_file(
                dftemp.sort_values(by=["Cognome", "Nome"]),
                f"{title1}.xlsx",
            )

    def make_form(self) -> None:
        """
        Create a sidebar form with a submit button.

        The form includes a submit button that triggers the start of data processing and error checking.
        """
        frm = st.sidebar.form("Auswahl", clear_on_submit=True)
        with frm:
            # This solution is somewhat awkward, but we want to move the expanders outside of the form
            # so that we can use the button for download
            self.submit = frm.form_submit_button("Iniziare elaborazione e controlli")

#    def choose_files(self, c1) -> None:
#        """Allow users to upload multiple Excel files for checking."""
#        self.uploaded_files: List[FileUploadBase] = c1.file_uploader(
#            "SCEGLIERE FILE EXCEL DA CONTROLLARE", accept_multiple_files=True
#        )

#    def choose_allegato666(self, c2) -> None:
#        """
#        Allow users to upload the Allegato 666 file for checking.#

#        Args:
#            None

 #       Returns:
 #           None
 #       """
 #       self.uploaded_allegato666: FileUploadBase = c2.file_uploader(
 #           "CARICARE ALLEGATO 666", accept_multiple_files=False
 #       )

    def choose_checks(self) -> None:
        """
        Creates a dynamic list of checkboxes based on a dictionary containing error names and descriptions.
        Updates the 'checks' dictionary with the selected checkboxes.

        :return: None
        """
        # Initialize the checks dictionary
        self.checks: Dict[str, bool] = {}

        # Create an expander to hold the checkboxes
        expndr = st.expander(
            "SELEZIONE CONTROLLI DA ESEGUIRE (i controlli sull'integrità dei dati vengono eseguiti sempre)",
            expanded=False,
        )

        try:
            with expndr:
                # Create a 'Select All' checkbox
                select_all = st.checkbox(
                    "🗂️ Selezionare/deselezionare tutti i controlli",
                    value=True,
                    key="select_all_checks",
                )

                # Create 4 columns to distribute checkboxes evenly
                columns = expndr.columns(4)

                # Iterate over the ERRORDICT items with enumeration
                for idx, (error_key, error_description) in enumerate(
                    self.ERRORDICT.items()
                ):
                    # Determine the appropriate column for the current checkbox
                    col = columns[idx % 4]

                    # Retrieve the corresponding icon, default to a warning emoji if not found
                    icon = self.ERROR_ICONS.get(error_key, "⚠️")

                    # Create the checkbox within the determined column
                    self.checks[error_key] = col.checkbox(
                        label=f"{icon} {error_description}",
                        value=select_all,
                        key=f"{error_key}_checkbox",
                    )

        except Exception as e:
            with expndr:
                # Display a warning message if an error occurs
                st.warning(
                    f"Non è stato possibile creare la lista dei controlli. Forse non sono stati definiti per l'anno {self.ANNO_RIFERIMENTO}? Errore: {e}"
                )

                # Optionally display an error and stop execution based on flags
                if getattr(self, "TOLLERANZAZERO", False) or getattr(
                    self, "DEBUGWARNINGS", False
                ):
                    st.error("Elaborazione interrotta")
                    st.stop()

    def make_tabs(self) -> None:
        """
        Create the main navigation tabs for the application.

        Creates three tabs:
        - Data Integrity
        - Errors and Inconsistencies
        - Final Tables
        """
        self.TAB1, self.TAB2, self.TAB3 = st.tabs(
            [
                "INTEGRITÀ DATI",
                "ERRORI e INCONGRUENZE RISCONTRATI",
                "TABELLE FINALI",
            ]
        )

    @st.fragment()
    def buildGrid(self, data: pd.DataFrame):
        """
        Build a grid configuration for displaying tabular data.

        Args:
            data (pd.DataFrame): The DataFrame to be displayed in the grid

        Returns:
            dict: Grid options configuration for AG-Grid
        """
        gb = GridOptionsBuilder.from_dataframe(data)
        # Configure pagination settings
        gb.configure_pagination(paginationAutoPageSize=True, paginationPageSize=20)
        # Configure side bar for column filtering and selection
        gb.configure_side_bar()
        # Configure selection mode to multiple rows with checkbox
        gb.configure_selection(selection_mode="multiple", use_checkbox=True)
        # Configure default column properties
        gb.configure_default_column(
            groupable=True,
            value=True,
            enableRowGroup=True,
            aggFunc="count",
            editable=True,
        )

        gridOptions = gb.build()
        return gridOptions

    @st.fragment()
    def make_grid(self, dff: pd.DataFrame, k: str) -> None:
        """
        Create and display an interactive data grid.

        Args:
            dff (pd.DataFrame): DataFrame to be displayed in the grid
            k (str): Unique key identifier for the grid component
        """
        # st.write(dff)
        # Drop unnecessary columns from the DataFrame
        helper = helpers()
        dff = helper.drop_columns(dff, self.ERRORDICT)
        # st.write(dff)
        # Append a new row with "filename" set to an empty string
        new_row_df = pd.DataFrame({"filename": "  "}, index=[0])
        dff = pd.concat([dff, new_row_df], ignore_index=True)

        # Append another new row with "filename" set to the current date
        new_row_data = {
            "filename": "file generato in data: " + date.today().strftime("%d/%m/%Y")
        }
        new_row_df = pd.DataFrame(new_row_data, index=[0])
        dff = pd.concat([dff, new_row_df], ignore_index=True)

        # Build the grid options object
        gridOptions = self.buildGrid(dff)

        # Display the grid using AgGrid
        AgGrid(dff, gridOptions=gridOptions, enable_enterprise_modules=True, key=k)

    @st.fragment()
    def download_excel_file(self, dftosave: pd.DataFrame, nome_file: str, flag: Optional[str] = None) -> None:
        """
        Create and provide a download button for an Excel file with improved styling.
        
        Args:
            dftosave (pd.DataFrame): DataFrame to be saved as Excel
            nome_file (str): Name of the output file
            flag (Optional[str]): Flag to control column handling ('mantieniColonneErrore' to keep error columns)
            
        Raises:
            KeyError: If required columns are missing from the DataFrame
            ValueError: If there are invalid or incompatible values
            Exception: For other unexpected errors
        """
        try:
            # Step 1: Remove 'Colonna_L_da_cancellare_dopo' if it exists
            dftosave = dftosave.drop(
                columns=["Colonna_L_da_cancellare_dopo"], errors="ignore"
            )

            # Step 2: Add timestamp to the file name
            timestamp = datetime.now().strftime("%Y-%m-%d_%H-%M-%S")
            nome_file = f"{timestamp}_{nome_file}"

            # Step 3: Drop columns based on the flag
            if flag == "mantieniColonneErrore":
                pass
            else:
                dftosave = helper.drop_columns(dftosave, self.ERRORDICT)

            # Step 4: Initialize an in-memory buffer
            buffer = BytesIO()

            # Step 5: Write DataFrame to the buffer using ExcelWriter
            with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
                dftosave.to_excel(writer, sheet_name="risultato", index=False)
                workbook = writer.book
                worksheet = writer.sheets["risultato"]

                # Add a header format
                header_format = workbook.add_format({
                    'bold': True,
                    'bg_color': '#4E8CFF',
                    'color': 'white',
                    'border': 1,
                    'align': 'center'
                })
                
                # Add data format
                data_format = workbook.add_format({
                    'border': 1
                })
                
                # Apply the header format to the first row
                for col_num, value in enumerate(dftosave.columns.values):
                    worksheet.write(0, col_num, value, header_format)

                # Step 6: Dynamically set column widths based on content
                for idx, col in enumerate(dftosave.columns):
                    # Calculate the maximum length of the column content
                    max_length = dftosave[col].astype(str).map(len).max()
                    max_length = max(max_length, len(col)) + 2  # Adding extra space
                    worksheet.set_column(idx, idx, max_length, data_format)
                    
                # Add autofilter
                worksheet.autofilter(0, 0, len(dftosave), len(dftosave.columns) - 1)

            # Step 7: Retrieve the Excel file bytes from the buffer
            buffer.seek(0)
            file_bytes = buffer.read()

            # Better styled download section
            col1, col2 = st.columns([1, 2])
            
            with col1:
                # Step 8: Add an icon to the download button using an emoji
                ste.download_button(
                    label="📥 Scarica Excel",
                    data=file_bytes,
                    file_name=nome_file,
                    mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    use_container_width=True,
                )
                
            with col2:
                st.markdown(f"""
                <div style="padding: 10px; border-radius: 4px; background-color: #f0f2f6; font-size: 0.9rem;">
                    <strong>File:</strong> {nome_file}<br>
                    <strong>Righe:</strong> {len(dftosave)} | <strong>Colonne:</strong> {len(dftosave.columns)}
                </div>
                """, unsafe_allow_html=True)

        except KeyError as ke:
            st.error(f"Chiave mancante nel DataFrame: {ke}")
            st.stop()  # Stop further execution

        except ValueError as ve:
            st.error(f"Valore non valido o incompatibile: {ve}")
            st.stop()  # Stop further execution

        except Exception as e:
            st.error(f"Si è verificato un errore inaspettato: {e}")
            st.stop()  # Stop further execution

    def upload_all_files(self) -> None:
        """
        Allow users to upload all Excel files at once, automatically identifying the Allegato 666 file.
        Enhanced with better visual styling and a single clear upload area.
        """
        st.markdown("### 📁 Upload file Excel")
        
        # Apply custom CSS to style the file uploader and hide the uploaded files display
        st.markdown("""
        <style>
            /* Style the file uploader */
            .stFileUploader {
                padding: 0 !important;
            }
            
            /* Style the file uploader box - the actual drop area */
            .stFileUploader > div > div {
                padding: 2rem 1rem 1rem 1rem !important;
                border: 2px dashed #4e8cff !important;
                border-radius: 10px !important;
            }
            
            /* Add the instruction text directly within the file uploader area */
            .stFileUploader > div > div::before {
                content: "Trascina qui i file Excel o fai clic per selezionarli" !important;
                display: block !important;
                color: #4e8cff !important;
                text-align: center !important;
                font-size: 1rem !important;
                margin-bottom: 0.5rem !important;
                font-weight: 500 !important;
            }
            
            /* Add the secondary instruction text */
            .stFileUploader > div > div::after {
                content: "(Incluso Allegato 666)" !important;
                display: block !important;
                color: #4e8cff !important;
                text-align: center !important;
                font-size: 0.8rem !important;
                font-style: italic !important;
            }
            
            /* Hide the uploaded file list that Streamlit shows by default */
            .stFileUploader [data-testid="stFileUploaderDropzone"] + div {
                display: none !important;
            }
        </style>
        """, unsafe_allow_html=True)
        
        # File uploader with a descriptive label (hidden visually by CSS/label_visibility)
        all_uploaded_files = st.file_uploader(
            "CaricaExcelInclusoAllegato666)", 
            accept_multiple_files=True,
            label_visibility="collapsed"
        )
        
        # Initialize as empty lists
        self.uploaded_files = []
        self.uploaded_allegato666 = None
        
        # Check if any files were uploaded
        if all_uploaded_files:
            # Define valid Excel file extensions
            valid_extensions = ['.xlsx', '.xls']
            
            # Separate valid Excel files from invalid ones
            valid_files = []
            invalid_files = []
            
            for file in all_uploaded_files:
                # Check if file has a valid Excel extension
                file_extension = '.' + file.name.split('.')[-1].lower()
                if file_extension in valid_extensions:
                    valid_files.append(file)
                else:
                    invalid_files.append(file)
            
            # Display warning for invalid files
            if invalid_files:
                invalid_file_names = [file.name for file in invalid_files]
                st.markdown(f"""
                <div style="background-color: #ffdcdc; padding: 10px; border-radius: 8px; border-left: 5px solid #d32f2f; margin-bottom: 10px;">
                    <p style="margin:0"><strong>❌ Errore:</strong> I seguenti file non sono in formato Excel e non verranno elaborati:</p>
                    <ul style="margin-top: 5px; margin-bottom: 0;">
                        {''.join([f'<li>{name}</li>' for name in invalid_file_names])}
                    </ul>
                </div>
                """, unsafe_allow_html=True)
            
            # Process only valid Excel files
            allegato_666_files = []
            other_files = []
            
            for file in valid_files:
                if "666" in file.name:
                    allegato_666_files.append(file)
                else:
                    other_files.append(file)
            
            # Assign the regular files to self.uploaded_files
            self.uploaded_files = other_files
            
            # Handle Allegato 666 file(s) and display messages using styled cards
            if len(allegato_666_files) > 0 or len(other_files) > 0:
                col1, col2 = st.columns(2)
                
                # Handle Allegato 666 file(s) with better styling
                with col1:
                    if len(allegato_666_files) == 1:
                        self.uploaded_allegato666 = allegato_666_files[0]
                        st.markdown(f"""
                        <div style="background-color: #d1f0d9; padding: 10px; border-radius: 8px; border-left: 5px solid #2e7d32;">
                            <p style="margin:0"><strong>✅ Allegato 666:</strong> {self.uploaded_allegato666.name}</p>
                        </div>
                        """, unsafe_allow_html=True)
                    elif len(allegato_666_files) > 1:
                        st.markdown(f"""
                        <div style="background-color: #ffdcdc; padding: 10px; border-radius: 8px; border-left: 5px solid #d32f2f;">
                            <p style="margin:0"><strong>❌ Errore:</strong> Più file contengono '666' nel nome ({len(allegato_666_files)})</p>
                        </div>
                        """, unsafe_allow_html=True)
                        self.uploaded_allegato666 = None
                    else:
                        st.markdown(f"""
                        <div style="background-color: #fff8e1; padding: 10px; border-radius: 8px; border-left: 5px solid #ff9800;">
                            <p style="margin:0"><strong>⚠️ Attenzione:</strong> Allegato 666 mancante</p>
                        </div>
                        """, unsafe_allow_html=True)
                
                # Display count of regular files with better styling
                with col2:
                    if len(other_files) > 0:
                        st.markdown(f"""
                        <div style="background-color: #d1f0d9; padding: 10px; border-radius: 8px; border-left: 5px solid #1976d2;">
                            <p style="margin:0"><strong>ℹ️ Elenchi caricati:</strong> {len(other_files)}</p>
                        </div>
                        """, unsafe_allow_html=True)
                        
                        # Add an expander to show file list
                        with st.expander("Mostra file caricati"):
                            for file in other_files:
                                st.markdown(f"📄 {file.name}")
                    else:
                        st.markdown(f"""
                        <div style="background-color: #fff8e1; padding: 10px; border-radius: 8px; border-left: 5px solid #ff9800;">
                            <p style="margin:0"><strong>⚠️ Attenzione:</strong> Nessun file Excel aggiuntivo</p>
                        </div>
                        """, unsafe_allow_html=True)
                
                # Add vertical spacing after the status boxes
                st.markdown("<div style='margin-bottom: 25px;'></div>", unsafe_allow_html=True)