<!DOCTYPE html>
<html lang="it">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Elaboratore XLS</title>
    <style>
        /* Reset e stili base */
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }

        body, table {
            font-family: Arial;
            font-size: 14px;
            background-color: black;
        }

        .container {
            max-width: 1600px;
            margin: 0 auto;
            background-color: #000;
            padding: 30px;
            border-radius: 12px;
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
        }

        h1 {
            color: #2c3e50;
            margin-bottom: 30px;
            text-align: center;
            font-size: 2.5em;
            font-weight: 600;
        }

        .btn {
            display: inline-block;
            padding: 12px 24px;
            margin: 8px;
            border: none;
            border-radius: 6px;
            cursor: pointer;
            font-size: 16px;
            font-weight: 500;
            text-transform: uppercase;
            letter-spacing: 0.5px;
            transition: all 0.3s ease;
            box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
        }

        .btn:hover {
            transform: translateY(-2px);
            box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
        }

        .btn-primary {
            background-color: #3498db;
            color: white;
        }

        .btn-primary:hover {
            background-color: #2980b9;
        }

        .btn-success {
            background-color: #2ecc71;
            color: white;
        }

        .btn-success:hover {
            background-color: #27ae60;
        }

        .loader-container {
            display: none;
            position: fixed;
            top: 0;
            left: 0;
            width: 100%;
            height: 100%;
            background: rgba(0, 0, 0, 0.8);
            z-index: 1000;
            justify-content: center;
            align-items: center;
            flex-direction: column;
        }

        .loader {
            width: 60px;
            height: 60px;
            border: 6px solid #f3f3f3;
            border-top: 6px solid #3498db;
            border-radius: 50%;
            animation: spin 1s linear infinite;
        }

        .loader-text {
            color: white;
            margin-top: 20px;
            font-size: 20px;
            font-weight: 500;
            text-align: center;
        }

        @keyframes spin {
            0% { transform: rotate(0deg); }
            100% { transform: rotate(360deg); }
        }

        .table-container {
            overflow-x: auto;
            margin-top: 30px;
            border-radius: 8px;
            box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
        }

        .preview-table {
            width: 100%;
            border-collapse: collapse;
            background-color: white;
            font-size: 14px;
        }

        .preview-table th,
        .preview-table td {
            border: 1px solid #e1e1e1;
            padding: 12px;
            text-align: left;
            overflow-wrap: break-word;
        }

        .preview-table th {
            background-color: #f8f9fa;
            color: #2c3e50;
            font-weight: 600;
            white-space: normal;
        }

        .preview-table tr:nth-child(even) {
            background-color: #f9f9f9;
        }

        .preview-table tr:hover {
            background-color: #f5f5f5;
        }

        .checkbox-container {
            margin: 20px 0;
            max-height: 300px;
            overflow-y: auto;
            padding: 20px;
            border: 1px solid #e1e1e1;
            border-radius: 8px;
            background-color: black;
            color: #fff;
            width: fit-content;
        }


        .checkbox-container h3 {
            color: #2c3e50;
            margin-bottom: 15px;
        }

.checkbox-container label {
    display: block;
    padding: 8px;
    margin: 5px 0;
    cursor: pointer;
    transition: background-color 0.8s;
    border-radius: 4px;
}

        .checkbox-container label:hover {
            background-color: #377584;
        }

        .checkbox-container input[type="checkbox"] {
            margin-right: 10px;
        }

        .status {
            margin: 15px 0;
            padding: 15px;
            border-radius: 6px;
            font-weight: 500;
            text-align: center;
        }

        .status.success {
            background-color: #d4edda;
            color: #155724;
            border: 1px solid #c3e6cb;
        }

        .status.error {
            background-color: #f8d7da;
            color: #721c24;
            border: 1px solid #f5c6cb;
        }

        .upload-section {
            text-align: center;
            margin-bottom: 30px;
            padding: 20px;
            border: 2px dashed #e1e1e1;
            border-radius: 8px;
            background-color: #000;
        }

        @media (max-width: 768px) {
            .container {
                padding: 15px;
            }

            h1 {
                font-size: 2em;
            }

            .btn {
                padding: 10px 20px;
                font-size: 14px;
                width: 100%;
                margin: 5px 0;
            }

            .preview-table {
                font-size: 12px;
            }

            .preview-table th,
            .preview-table td {
                padding: 8px;
            }
        }
    </style>
</head>
<body>
    <div class="loader-container">
        <div class="loader"></div>
        <div class="loader-text">Elaborazione dati in corso...</div>
    </div>

    <div class="container">
        <h1>Elaboratore XLS</h1>

        <div class="upload-section">
            <input type="file" id="fileInput" accept=".xls,.xlsx" style="display: none;">
            <label for="fileInput" class="btn btn-primary">Seleziona file XLS</label>
            <button id="processButton" class="btn btn-success" onclick="processFile()" disabled>Elabora File</button>
        </div>

        <div id="status" class="status" style="display: none;"></div>

        <div id="columnSelection" class="checkbox-container" style="display: none;">
            <h3>Seleziona le colonne da rimuovere:</h3>
        </div>

        <div id="preview" class="table-container" style="display: none;"></div>

        <button id="saveButton" class="btn btn-success" onclick="saveFile()" style="display: none;">
            Salva File Elaborato
        </button>
    </div>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.4/xlsx.full.min.js"></script>
    <script>
        let workbook = null;
        let processedData = [];
        let headers = [];
        let rawData = [];

        document.getElementById('fileInput').addEventListener('change', function(e) {
            const file = e.target.files[0];
            document.getElementById('processButton').disabled = !file;

            if (file && !file.name.match(/\.(xls|xlsx)$/i)) {
                showStatus('Seleziona un file XLS o XLSX valido', true);
                document.getElementById('processButton').disabled = true;
            }
        });

        function showStatus(message, isError = false) {
            const status = document.getElementById('status');
            status.textContent = message;
            status.className = `status ${isError ? 'error' : 'success'}`;
            status.style.display = 'block';
        }

        function showLoader() {
            document.querySelector('.loader-container').style.display = 'flex';
        }

        function hideLoader() {
            document.querySelector('.loader-container').style.display = 'none';
        }
</script>
</body>
</html>
<script>
        async function processFile() {
            try {
                const fileInput = document.getElementById('fileInput');
                const file = fileInput.files[0];

                if (!file) {
                    throw new Error('Seleziona un file prima di procedere');
                }

                showLoader();
                showStatus('Elaborazione in corso...');
                await new Promise(resolve => setTimeout(resolve, 3000));
                const data = await file.arrayBuffer();
                workbook = XLSX.read(data, { type: 'array' });

                const firstSheet = workbook.Sheets[workbook.SheetNames[0]];
                const jsonData = XLSX.utils.sheet_to_json(firstSheet, { header: 1 });

                headers = jsonData[0];
                const paxColumnIndex = headers.findIndex(header => header === 'Utilizzo delle risorse 1');
                if (paxColumnIndex !== -1) {
                    headers[paxColumnIndex] = 'PAX';
                }

                rawData = jsonData.slice(1);

                processedData = [...rawData];
                processedData = processBookingNumbers(processedData);
                processedData = filterCancelledBookings(processedData);
                processedData = cleanProductNames(processedData);
                processedData = handlePartnerInfo(processedData);
                processedData = processMoneyColumns(processedData);

if (paxColumnIndex !== -1) {
    processedData = processedData.map(row => {
        if (row[paxColumnIndex]) {
            row[paxColumnIndex] = parseInt(row[paxColumnIndex]) || 0;
        }
        return row;
    });
}

                processedData.sort((a, b) => {
                    if (a[5] < b[5]) return -1;
                    if (a[5] > b[5]) return 1;
                    return 0;
                });

                processedData = organizeByTimeBlocks(processedData);

                hideLoader();
                showColumnSelection();
                showPreview();
                document.getElementById('saveButton').style.display = 'block';
                showStatus('File elaborato con successo!');

            } catch (error) {
                hideLoader();
                console.error('Errore durante l\'elaborazione:', error);
                showStatus(error.message, true);
            }
        }

        function processBookingNumbers(data) {
            const uniqueNumbers = [...new Set(data.map(row => row[0]))];

            return uniqueNumbers.map(number => {
                const duplicateRows = data.filter(row => row[0] === number);
                const sum = duplicateRows.reduce((total, row) => total + (parseFloat(row[9]) || 0), 0);
                const baseRow = [...duplicateRows[0]];
                baseRow[9] = sum;
                return baseRow;
            });
        }

        function filterCancelledBookings(data) {
            return data.filter(row => {
                const status = row[15];
                return !(status && (status.includes('Annullato (canale commerciale)') || status.includes('Annullata')));
            });
        }

        function cleanProductNames(data) {
            return data.map(row => {
                if (row[5]) {
                    row[5] = row[5].replace(/OTA|RIV/g, '').trim();
                }
                return row;
            });
        }

        function handlePartnerInfo(data) {
            return data.map(row => {
                const agentEmail = row[18];
                const salesChannel = row[16];

                if (row[19] && row[19].includes('GetYourGuide Deutschland GmbH')) {
                    row[19] = 'GYG';
                }
                if (row[19] && row[19].toLowerCase().includes('viator')) {
                    row[19] = 'VIATOR';
                }

                if (salesChannel && salesChannel.includes('Il proprio Ticketshop')) {
                    row[19] = 'PARMALOOK';
                }

                if (agentEmail && agentEmail.includes('@')) {
                    const prefix = agentEmail.split('@')[0].toUpperCase();

                    if (agentEmail.toLowerCase().includes('sergio') || agentEmail.toLowerCase().includes('matteo')) {
                        row[19] = 'PARMALOOK';
                    } else {
                        row[19] = prefix;
                    }
                }

                if (salesChannel && (salesChannel === 'OTA' || salesChannel === 'RIV')) {
                    row[19] = row[19] ? row[19].replace(/OTA|RIV/g, '').trim() : '';
                }

                if (row[19] && row[19].includes('GYG')) {
                    row[21] = 'NH Hotel';
                }

                return row;
            });
        }

        function processMoneyColumns(data) {
            return data.map(row => {
                if (row[13]) {
                    const value = parseFloat(row[13].toString().replace(/[^0-9.,]/g, '').replace(',', '.')) || 0;
                    row[13] = value.toLocaleString('it-IT', { style: 'currency', currency: 'EUR' });
                }
                if (row[14]) {
                    const value = parseFloat(row[14].toString().replace(/[^0-9.,]/g, '').replace(',', '.')) || 0;
                    row[14] = value.toLocaleString('it-IT', { style: 'currency', currency: 'EUR' });
                }
                return row;
            });
        }

        function organizeByTimeBlocks(data) {
            const paxColumnIndex = headers.findIndex(header => 
                header === 'PAX' || header === 'Utilizzo delle risorse 1'
            );
            const timeColumnIndex = 4;

function addTotalRow(block) {
    if (block.length === 0) return block;
    
    const totalRow = new Array(headers.length).fill('');
    
    if (paxColumnIndex !== -1) {
        const total = block.reduce((sum, row) => {
            const paxValue = row[paxColumnIndex];
            return sum + (parseInt(paxValue) || 0);
        }, 0);
        
        totalRow[paxColumnIndex] = total;
        totalRow[timeColumnIndex] = 'TOTALE';
        totalRow.isTotal = true; // Flag for styling
    }
    return [...block, emptyRow, totalRow];
}

            const block0830 = data.filter(row => row[timeColumnIndex] === '08:30');
            const block0900 = data.filter(row => row[timeColumnIndex] === '09:00');
            const block1000 = data.filter(row => row[timeColumnIndex] === '10:00');
            const block1100 = data.filter(row => row[timeColumnIndex] === '11:00');
            const block1400 = data.filter(row => row[timeColumnIndex] === '14:00');

            const emptyRow = new Array(headers.length).fill('');
            emptyRow.isEmpty = true; // Flag for styling

            return [
                ...addTotalRow(block0830),
                emptyRow,
                ...addTotalRow(block0900),
                emptyRow,
                ...addTotalRow(block1000),
                emptyRow,
                ...addTotalRow(block1100),
                emptyRow,
                ...addTotalRow(block1400)
            ];
        }

        function showColumnSelection() {
            const container = document.getElementById('columnSelection');
            container.style.display = 'block';
            container.innerHTML = '<h3>Seleziona le colonne da rimuovere:</h3>';

            headers.forEach((header, index) => {
                const label = document.createElement('label');
                label.style.display = 'block';
                label.style.margin = '2px';
                label.innerHTML = `<input type="checkbox" data-index="${index}"> ${header || `Colonna ${index + 1}`}`;
                label.querySelector('input').addEventListener('change', showPreview);
                container.appendChild(label);
            });

            const savedColumns = localStorage.getItem('removedColumns');
            if (savedColumns) {
                try {
                    const removedColumns = JSON.parse(savedColumns);
                    const checkboxes = document.querySelectorAll('#columnSelection input[type="checkbox"]');
                    checkboxes.forEach(checkbox => {
                        const index = parseInt(checkbox.dataset.index);
                        checkbox.checked = removedColumns.includes(index);
                    });
                } catch (error) {
                    console.error("Error parsing local storage data:", error);
                    showStatus("Error loading previously removed columns", true);
                }
            }
        }

        function showPreview() {
            const preview = document.getElementById('preview');
            preview.style.display = 'block';
            preview.innerHTML = '';

            const table = document.createElement('table');
            table.className = 'preview-table';

            // Add header row
            const headerRow = table.insertRow();
            headerRow.style.position = 'sticky';
            headerRow.style.top = '0';
            headerRow.style.backgroundColor = '#f8f9fa';
            headerRow.style.zIndex = '1';

            headers.forEach((header, index) => {
                const checkbox = document.querySelector(`#columnSelection input[data-index="${index}"]`);
                if (!checkbox || !checkbox.checked) {
                    const th = document.createElement('th');
                    th.textContent = header || `Colonna ${index + 1}`;
                    th.style.padding = '10px';
                    th.style.borderBottom = '2px solid #dee2e6';
                    headerRow.appendChild(th);
                }
            });

            // Add data rows
            processedData.forEach((row, rowIndex) => {
                if (row.isEmpty) {
                    const tr = table.insertRow();
                    tr.style.height = '20px';
                    tr.style.backgroundColor = '#f8f9fa';
                    return;
                }

                const tr = table.insertRow();
                
                // Style for total rows
                if (row.isTotal) {
                    tr.style.backgroundColor = '#e9ecef';
                    tr.style.fontWeight = 'bold';
                    tr.style.borderTop = '1px solid #dee2e6';
                }

                // Add cells
                row.forEach((cell, index) => {
                    const checkbox = document.querySelector(`#columnSelection input[data-index="${index}"]`);
                    if (!checkbox || !checkbox.checked) {
                        const td = tr.insertCell();
                        td.textContent = cell || '';
                        td.style.padding = '8px';
                        td.style.borderBottom = '1px solid #dee2e6';
                    }
                });
            });

            preview.appendChild(table);
        }

        function saveFile() {
            try {
                const selectedColumns = Array.from(document.querySelectorAll('#columnSelection input:checked'))
                    .map(cb => parseInt(cb.dataset.index))
                    .sort((a, b) => b - a);

                localStorage.setItem('removedColumns', JSON.stringify(selectedColumns));

                const finalHeaders = headers.filter((_, i) => !selectedColumns.includes(i));
                const finalData = processedData.map(row =>
                    row.filter((_, i) => !selectedColumns.includes(i))
                        .map(cell => cell === null || cell === undefined ? "" : cell.toString())
                );

                const newWorkbook = XLSX.utils.book_new();
                const newSheet = XLSX.utils.aoa_to_sheet([finalHeaders, ...finalData]);

                const wscols = finalHeaders.map((header, index) => {
                    let maxLen = header.length;
                    finalData.forEach(row => {
                        const cell = row[index];
                        maxLen = Math.max(maxLen, (cell || "").length);
                    });
                    return { wch: maxLen + 2 };
                });
                newSheet['!cols'] = wscols;

                const dateColumnIndex = headers.findIndex(header => 
                    header === 'Data di partecipazione'
                );

                let fileName = 'default.xlsx';
                
                if (dateColumnIndex !== -1 && processedData[0] && processedData[0][dateColumnIndex]) {
                    const participationDate = processedData[0][dateColumnIndex];
                    const dateParts = participationDate.match(/(\d{2})[-.\/](\d{2})[-.\/](\d{4})/);
                    
                    if (dateParts) {
                        const year = dateParts[3];
                        const month = dateParts[2];
                        const day = dateParts[1];
                        fileName = `${year}${month}${day}.xlsx`;
                    }
                }

                XLSX.utils.book_append_sheet(newWorkbook, newSheet, "Elaborato");
                XLSX.writeFile(newWorkbook, fileName);

                showStatus('File salvato con successo!');
            } catch (error) {
                console.error('Errore durante il salvataggio:', error);
                showStatus(`Errore durante il salvataggio: ${error.message}`, true);
            }
        }
    </script>
