const xlsx = require('xlsx');

// Create test excel file with exact headers
const data = [
    ["Mock Test Results for Madurai"],
    [],
    ["Roll No", "Name", "Total Marks", "Correct Answers", "Incorrect Answers", "Not attempted", "Physics", "Physics _Correct Answers", "Physics _Incorrect Answers", "Physics _Not attempted", "Chemistry", "Chemistry_Correct Answers", "Chemistry_Incorrect Answers", "Chemistry_Not attempted", "Zoology", "Zoology_Correct Answers", "Zoology_Incorrect Answers", "Zoology_Not attempted"],
    ["101", "John", 500, 100, 20, 0, 150, 40, 10, 0, 150, 40, 10, 0, 200, 50, 10, 0]
];

const worksheet = xlsx.utils.aoa_to_sheet(data);
const workbook = xlsx.utils.book_new();
xlsx.utils.book_append_sheet(workbook, worksheet, "Marks");
xlsx.writeFile(workbook, "test_file.xlsx");

// Test parsing logic
const rows = xlsx.utils.sheet_to_json(workbook.Sheets["Marks"], { header: 1 });
let headerRowIndex = -1;
let colMap = {};

for (let i = 0; i < Math.min(20, rows.length); i++) {
    const row = rows[i] || [];
    const rowStr = row.map(c => String(c).toLowerCase().replace(/[^a-z]/g, '')).join(',');
    console.log(`Row ${i} str: ${rowStr}`);
    if (rowStr.includes('name') && (rowStr.includes('roll') || rowStr.includes('id'))) {
        headerRowIndex = i;
        row.forEach((col, idx) => {
            const c = String(col).toLowerCase().replace(/[^a-z]/g, '');
            if (c.includes('roll')) colMap.rollNo = idx;
            else if (c === 'name' || c.includes('studentname')) colMap.name = idx;
            else if (c === 'physics') colMap.phys = idx;
            else if (c === 'chemistry') colMap.chem = idx;
            else if (c === 'biology' || c === 'zoology' || c === 'botany') colMap.bio = idx;
            else if (c === 'totalmarks' || c === 'score' || c === 'total') colMap.score = idx;
        });
        break;
    }
}

console.log("Header row:", headerRowIndex);
console.log("Col map:", colMap);

if (headerRowIndex !== -1) {
    for (let i = headerRowIndex + 1; i < rows.length; i++) {
        const r = rows[i];
        if (!r || r.length === 0) continue;
        
        const rollNo = r[colMap.rollNo];
        const name = r[colMap.name];
        
        console.log("Parsed:", rollNo, name);
    }
}
