<?php
include('session_validator.php');
include('includes/db.php');

// Make sure we send JSON response
header('Content-Type: application/json');

if (isset($_FILES['csv_file']) && $_FILES['csv_file']['error'] == 0) {
    $file = $_FILES['csv_file']['tmp_name'];
    $handle = fopen($file, "r");

    // Skip the header row
    fgetcsv($handle);

    $conn->begin_transaction();
    try {
        $stmt = $conn->prepare("
            INSERT INTO symbolno_data (student_id, class_id, exam_id, symbol_no) 
            VALUES (?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE symbol_no = VALUES(symbol_no)
        ");
        
        while (($data = fgetcsv($handle, 9000, ",")) !== FALSE) {
            if (count($data) < 6) continue;

            $studentId = $data[0]; // Column A: student_id
            $classId = $data[2];   // Column C: class_id
            $examId = $data[4];    // Column E: exam_id
            $symbolNo = $data[5];  // Column F: symbol_no

            $stmt->bind_param("iiis", $studentId, $classId, $examId, $symbolNo);
            $stmt->execute();
        }

        $conn->commit();
        fclose($handle);

        // Return success response as JSON
        echo json_encode(["success" => true, "message" => "CSV file uploaded and data inserted successfully."]);
    } catch (Exception $e) {
        $conn->rollback();
        // Return error response as JSON
        echo json_encode(["success" => false, "error" => $e->getMessage()]);
    }
} else {
    // Return error response if no file was uploaded
    echo json_encode(["success" => false, "error" => "No valid CSV file uploaded."]);
}
?>
