script.js

// State management
const state = {
    fileA: null,
    fileB: null,
    headersA: [],
    headersB: [],
    dataA: [],
    dataB: []
};

// DOM Elements
const elements = {
    step2: document.getElementById('step2'),
    step3: document.getElementById('step3'),
    keyA: document.getElementById('keyA'),
    keyB: document.getElementById('keyB'),
    columnList: document.getElementById('columnList'),
    errorMsg: document.getElementById('errorMsg')
};

/**
 * Handles file upload and parsing
 */
async function handleFileUpload(input, type) {
    const file = input.files[0];
    if (!file) return;

    // UI Feedback
    document.getElementById(`placeholder${type}`).classList.add('hidden');
    document.getElementById(`preview${type}`).classList.remove('hidden');
    document.getElementById(`filename${type}`).textContent = file.name;

    try {
        const data = await readExcelFile(file);
        
        // Get Headers (Row 1)
        const headers = data[0].map((cell, index) => {
            // Handle null/undefined headers
            return cell ? cell.trim() : `Column_${index + 1}`; 
        });

        // Remove header row from data
        const rows = data.slice(1);

        if (type === 'A') {
            state.fileA = file;
            state.headersA = headers;
            state.dataA = rows;
            document.getElementById('infoA').textContent = `${rows.length} rows found. Headers: ${headers.join(', ')}`;
            
            // Enable Step 2
            elements.step2.classList.remove('opacity-50', 'pointer-events-none');
            
            // Populate Select A for Key
            populateSelect(elements.keyA, headers);
            
            // Populate Checkboxes for Transfer
            populateCheckboxes(headers);

        } else {
            state.fileB = file;
            state.headersB = headers;
            state.dataB = rows;
            document.getElementById('infoB').textContent = `${rows.length} rows found. Headers: ${headers.join(', ')}`;

            // Populate Select B for Key
            populateSelect(elements.keyB, headers);

            // If both files are ready, show Step 3
            if (state.fileA && state.fileB) {
                elements.step3.classList.remove('hidden');
                elements.step3.classList.add('fade-in');
                scrollToStep3();
            }
        }

    } catch (error) {
        showError(`Error reading file: ${error.message}`);
    }
}

/**
 * Reads Excel file using SheetJS
 */
function readExcelFile(file) {
    return new Promise((resolve, reject) => {
        const reader = new FileReader();
        reader.onload = (e) => {
            try {
                const data = new Uint8Array(e.target.result);
                const workbook = XLSX.read(data, { type: 'array', cellDates: true });
                const firstSheetName = workbook.SheetNames[0];
                const worksheet = workbook.Sheets[firstSheetName];
                // Parse to JSON (Array of Arrays)
                const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1, defval: "" });
                resolve(jsonData);
            } catch (err) {
                reject(err);
            }
        };
        reader.onerror = reject;
        reader.readAsArrayBuffer(file);
    });
}

/**
 * Populates a select dropdown
 */
function populateSelect(selectElement, options) {
    selectElement.innerHTML = '<option value="">-- Select Column --</option>';
    options.forEach((opt, index) => {
        const option = document.createElement('option');
        option.value = index; // Store index, not name, to handle duplicates
        option.textContent = opt;
        selectElement.appendChild(option);
    });
}

/**
 * Populates checkboxes for column selection
 */
function populateCheckboxes(headers) {
    elements.columnList.innerHTML = '';
    headers.forEach((header, index) => {
        const div = document.createElement('div');
        div.className = 'flex items-center gap-2';
        div.innerHTML = `
            <input type="checkbox" id="col_${index}" value="${index}" class="w-4 h-4 text-indigo-600 border-gray-300 rounded focus:ring-indigo-500">
            <label for="col_${index}" class="text-sm text-gray-700 truncate" title="${header}">${header}</label>
        `;
        elements.columnList.appendChild(div);
    });
}

function scrollToStep3() {
    elements.step3.scrollIntoView({ behavior: 'smooth' });
}
function showError(msg) {
    elements.errorMsg.textContent = msg;
    elements.errorMsg.classList.remove('hidden');
    setTimeout(() => {
        elements.errorMsg.classList.add('hidden');
    }, 5000);
}

/**
 * Loads dummy data for testing purposes
 */
function loadDummyData() {
    // Define Dummy Headers and Data
    
    // File A (Source)
    const headersA = ["Student ID", "Name", "Math", "Science", "History"];
    const dataA = [
        ["101", "Alice", 85, 90, 78],
        ["102", "Bob", 70, 75, 80],
        ["103", "Charlie", 92, 88, 85]
    ];

    // File B (Target) - Structure is slightly different but compatible
    const headersB = ["ID", "Student Name", "Math", "Science", "English"];
    const dataB = [
        ["101", "Alice", 0, 0, 0], // Will be updated
        ["102", "Bob", 0, 0, 0],   // Will be updated
        ["103", "Charlie", 0, 0, 0], // Will be updated
        ["104", "David", 0, 0, 0]  // No match in A
    ];

    // Update State
    state.fileA = { name: "Dummy_A.xlsx" }; // Mock file object
    state.fileB = { name: "Dummy_B.xlsx" }; // Mock file object
    state.headersA = headersA;
    state.headersB = headersB;
    state.dataA = dataA;
    state.dataB = dataB;

    // Update UI for File A
    document.getElementById('placeholderA').classList.add('hidden');
    document.getElementById('previewA').classList.remove('hidden');
    document.getElementById('filenameA').textContent = "Dummy_A.xlsx";
    document.getElementById('infoA').textContent = `${dataA.length} rows loaded. Headers: ${headersA.join(', ')}`;

    // Enable Step 2 & Update UI for File B
    elements.step2.classList.remove('opacity-50', 'pointer-events-none');
    document.getElementById('placeholderB').classList.add('hidden');
    document.getElementById('previewB').classList.remove('hidden');
    document.getElementById('filenameB').textContent = "Dummy_B.xlsx";
    document.getElementById('infoB').textContent = `${dataB.length} rows loaded. Headers: ${headersB.join(', ')}`;

    // Populate Selects
    populateSelect(elements.keyA, headersA);
    populateSelect(elements.keyB, headersB);
    
    // Auto-select matching keys if headers are similar (optional, but nice for UX)
    // A: "Student ID", B: "ID" - usually user has to pick manually or if exact match.
    // Let's leave them empty or try to guess. 
    // Let's try exact match on "ID" or "Student ID".
    const matchA = headersA.findIndex(h => h.toLowerCase().includes('id'));
    const matchB = headersB.findIndex(h => h.toLowerCase().includes('id'));
    if(matchA !== -1) elements.keyA.value = matchA;
    if(matchB !== -1) elements.keyB.value = matchB;

    // Populate Checkboxes (Using File A headers)
    populateCheckboxes(headersA);

    // Show Step 3
    elements.step3.classList.remove('hidden');
    scrollToStep3();
}
/**
 * Main Logic: Merge A into B
 */
function processFiles() {
    const keyIndexA = parseInt(elements.keyA.value);
    const keyIndexB = parseInt(elements.keyB.value);
    
    // Get selected columns to transfer
    const checkboxes = document.querySelectorAll('#columnList input[type="checkbox"]:checked');
    const transferIndices = Array.from(checkboxes).map(cb => parseInt(cb.value));

    if (isNaN(keyIndexA) || isNaN(keyIndexB)) {
        showError("Please select the matching Student ID column for both files.");
        return;
    }

    if (transferIndices.length === 0) {
        showError("Please select at least one column to transfer from Excel A.");
        return;
    }

    // 1. Create a Lookup Map for File A
    const mapA = new Map();
    
    state.dataA.forEach(row => {
        if (row[keyIndexA]) {
            const key = String(row[keyIndexA]).trim();
            if (!mapA.has(key)) {
                mapA.set(key, row);
            }
        }
    });

    // 2. Process File B
    const workflowLog = [];
    const errors = [];
    let successCount = 0;
    let errorCount = 0;

    state.dataB.forEach((rowB, index) => {
        const rowNum = index + 2; // +1 for header, +1 for 1-based index
        const keyB = String(rowB[keyIndexB] || "").trim();
        
        let status = "Pending";
        let message = "";

        if (!keyB) {
            status = "Error";
            message = "Missing Student ID in Official Sheet";
            errorCount++;
        } else if (!mapA.has(keyB)) {
            status = "Error";
            message = "Student ID not found in Tailor-made Sheet";
            errorCount++;
        } else {
            // ID Found in A
            const rowA = mapA.get(keyB);
            const missingCols = [];
            
            transferIndices.forEach(colIndex => {
                const headerNameA = state.headersA[colIndex];
                const targetIndexInB = state.headersB.indexOf(headerNameA);

                if (targetIndexInB !== -1) {
                    rowB[targetIndexInB] = rowA[colIndex];
                } else {
                    missingCols.push(headerNameA);
                }
            });

            if (missingCols.length > 0) {
                status = "Partial";
                message = `Marks for [${missingCols.join(', ')}] not found in Official Sheet`;
                successCount++;
            } else {
                status = "Success";
                message = "Marks merged successfully";
                successCount++;
            }
        }

        workflowLog.push({ rowNum, keyB, status, message });
        if (status === "Error" || status === "Partial") {
            errors.push({ rowNum, keyB, status, message });
        }
    });

    // 3. Update UI
    renderWorkflow(workflowLog, errors, successCount, errorCount);

    // 4. Generate New Excel File
    generateDownload(successCount);
}

function renderWorkflow(logs, errors, successCount, errorCount) {
    const section = document.getElementById('workflowSection');
    const summaryContainer = document.getElementById('workflowSummary');
    const errorTableBody = document.getElementById('errorTableBody');
    const workflowTableBody = document.getElementById('workflowTableBody');
    const noErrorsMsg = document.getElementById('noErrorsMsg');

    // Show section
    section.classList.remove('hidden');
    
    // Scroll to result
    section.scrollIntoView({ behavior: 'smooth' });

    // Render Summary
    summaryContainer.innerHTML = `
        <div class="bg-green-50 p-4 rounded-lg border border-green-200">
            <p class="text-sm text-green-600 font-medium">Successful / Partial</p>
            <p class="text-2xl font-bold text-green-700">${successCount}</p>
        </div>
        <div class="bg-red-50 p-4 rounded-lg border border-red-200">
            <p class="text-sm text-red-600 font-medium">Errors</p>
            <p class="text-2xl font-bold text-red-700">${errorCount}</p>
        </div>
        <div class="bg-blue-50 p-4 rounded-lg border border-blue-200">
            <p class="text-sm text-blue-600 font-medium">Total Rows</p>
            <p class="text-2xl font-bold text-blue-700">${logs.length}</p>
        </div>
    `;

    // Render Errors Table
    errorTableBody.innerHTML = '';
    if (errors.length === 0) {
        noErrorsMsg.classList.remove('hidden');
    } else {
        noErrorsMsg.classList.add('hidden');
        errors.forEach(err => {
            const row = document.createElement('tr');
            row.className = "bg-white border-b hover:bg-red-50";
            row.innerHTML = `
                <td class="px-6 py-2 font-medium text-gray-900">${err.rowNum}</td>
                <td class="px-6 py-2">${err.keyB}</td>
                <td class="px-6 py-2">
                    <span class="px-2 py-1 text-xs font-semibold rounded-full ${err.status === 'Error' ? 'bg-red-100 text-red-800' : 'bg-yellow-100 text-yellow-800'}">
                        ${err.status}
                    </span>
                </td>
                <td class="px-6 py-2 text-red-600">${err.message}</td>
            `;
            errorTableBody.appendChild(row);
        });
    }

    // Render Full Workflow Log
    workflowTableBody.innerHTML = '';
    logs.forEach(log => {
        const row = document.createElement('tr');
        const statusColor = log.status === 'Success' ? 'text-green-600' : (log.status === 'Error' ? 'text-red-600' : 'text-yellow-600');
        const badgeClass = log.status === 'Success' ? 'bg-green-100 text-green-800' : (log.status === 'Error' ? 'bg-red-100 text-red-800' : 'bg-yellow-100 text-yellow-800');
        
        row.className = "bg-white border-b hover:bg-gray-50";
        row.innerHTML = `
            <td class="px-6 py-2 font-medium text-gray-900">${log.rowNum}</td>
            <td class="px-6 py-2">${log.keyB || '<span class="text-gray-400">Empty</span>'}</td>
            <td class="px-6 py-2">
                 <span class="px-2 py-1 text-xs font-semibold rounded-full ${badgeClass}">
                    ${log.status}
                </span>
            </td>
            <td class="px-6 py-2 text-xs ${statusColor}">${log.message}</td>
        `;
        workflowTableBody.appendChild(row);
    });

    // Re-initialize feather icons for the new section
    feather.replace();
}
function generateDownload(matches) {
    // Reconstruct worksheet from updated dataB
    // Combine Headers + Data
    const finalData = [state.headersB, ...state.dataB];
    
    const worksheet = XLSX.utils.aoa_to_sheet(finalData);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, "Merged Results");

    // Generate filename with timestamp
    const date = new Date().toISOString().slice(0,10);
    XLSX.writeFile(workbook, `Excel_C_Merged_${date}.xlsx`);
    
    alert(`Processing Complete! Updated ${matches} students.`);
}