<?php
include('../includes/db.php');
header('Content-Type: application/json');

// Check if file was uploaded
if (!isset($_FILES['csv_file']) || $_FILES['csv_file']['error'] !== UPLOAD_ERR_OK) {
    echo json_encode(['success' => false, 'error' => 'No file uploaded or upload error']);
    exit;
}

$file = $_FILES['csv_file']['tmp_name'];

// Check if file is CSV
$file_ext = strtolower(pathinfo($_FILES['csv_file']['name'], PATHINFO_EXTENSION));
if ($file_ext !== 'csv') {
    echo json_encode(['success' => false, 'error' => 'Only CSV files are allowed']);
    exit;
}

// Read CSV file
$handle = fopen($file, 'r');
if (!$handle) {
    echo json_encode(['success' => false, 'error' => 'Failed to open CSV file']);
    exit;
}

// Skip BOM if present
$bom = fread($handle, 3);
if ($bom !== "\xEF\xBB\xBF") {
    rewind($handle);
}

// Read headers
$headers = fgetcsv($handle);
if (!$headers || count($headers) < 6) {
    echo json_encode(['success' => false, 'error' => 'Invalid CSV format. Please use the template.']);
    fclose($handle);
    exit;
}

// Expected headers
$expected_headers = ['Student ID', 'Registration ID', 'Student Name', 'Roll Number', 'Section', 'Existing Symbol', 'New Symbol', 'Remarks'];
if (count(array_intersect($headers, $expected_headers)) < 3) {
    echo json_encode(['success' => false, 'error' => 'Invalid CSV headers. Please use the template.']);
    fclose($handle);
    exit;
}

$success_count = 0;
$error_count = 0;
$errors = [];
$processed_students = [];

// Get exam_id from session or form (you may need to modify this based on your implementation)
$exam_id = isset($_POST['exam_id']) ? intval($_POST['exam_id']) : 0;
// If exam_id is not in POST, you might want to add it to your form or store it in session

// Begin transaction
$conn->begin_transaction();

try {
    $row_number = 1;
    while (($row = fgetcsv($handle)) !== false) {
        $row_number++;
        
        // Skip empty rows
        if (empty(array_filter($row))) {
            continue;
        }
        
        // Map row to headers
        $data = array_combine($headers, array_pad($row, count($headers), ''));
        
        $student_id = isset($data['Student ID']) ? intval(trim($data['Student ID'])) : 0;
        $new_symbol = isset($data['New Symbol']) ? trim($data['New Symbol']) : '';
        
        // Skip if no student ID or no new symbol
        if (!$student_id || empty($new_symbol)) {
            $error_count++;
            $errors[] = "Row $row_number: Missing student ID or symbol";
            continue;
        }
        
        // Check if student exists
        $student_check = $conn->prepare("SELECT student_id, Class_id FROM students WHERE student_id = ? AND status = 'active'");
        $student_check->bind_param("i", $student_id);
        $student_check->execute();
        $student_result = $student_check->get_result();
        
        if ($student_result->num_rows === 0) {
            $student_check->close();
            $error_count++;
            $errors[] = "Row $row_number: Student ID $student_id not found";
            continue;
        }
        
        $student_row = $student_result->fetch_assoc();
        $class_id = $student_row['Class_id'];
        $student_check->close();
        
        // Check for duplicate symbol number
        if ($exam_id) {
            $duplicate_check = $conn->prepare("SELECT student_id FROM symbolno_data WHERE exam_id = ? AND symbol_no = ? AND student_id != ?");
            $duplicate_check->bind_param("isi", $exam_id, $new_symbol, $student_id);
            $duplicate_check->execute();
            $duplicate_result = $duplicate_check->get_result();
            
            if ($duplicate_result->num_rows > 0) {
                $error_count++;
                $errors[] = "Row $row_number: Symbol '$new_symbol' already assigned to another student";
                $duplicate_check->close();
                continue;
            }
            $duplicate_check->close();
        }
        
        // Insert or update symbol number
        if ($exam_id) {
            $query = "INSERT INTO symbolno_data (student_id, class_id, exam_id, symbol_no) 
                      VALUES (?, ?, ?, ?)
                      ON DUPLICATE KEY UPDATE symbol_no = ?";
            
            $stmt = $conn->prepare($query);
            $stmt->bind_param("iiiss", $student_id, $class_id, $exam_id, $new_symbol, $new_symbol);
        } else {
            // If no exam_id, update only students table
            $query = "UPDATE students SET symbol_no = ? WHERE student_id = ?";
            $stmt = $conn->prepare($query);
            $stmt->bind_param("si", $new_symbol, $student_id);
        }
        
        if ($stmt->execute()) {
            // Also update students table
            $update_student = $conn->prepare("UPDATE students SET symbol_no = ? WHERE student_id = ?");
            $update_student->bind_param("si", $new_symbol, $student_id);
            $update_student->execute();
            $update_student->close();
            
            $success_count++;
            $processed_students[] = [
                'student_id' => $student_id,
                'symbol_no' => $new_symbol
            ];
        } else {
            $error_count++;
            $errors[] = "Row $row_number: Failed to save symbol - " . $stmt->error;
        }
        
        $stmt->close();
    }
    
    // Commit transaction
    $conn->commit();
    
    fclose($handle);
    
    $message = "CSV upload completed.<br>";
    $message .= "Successfully processed: $success_count records<br>";
    $message .= "Failed: $error_count records";
    
    if ($error_count > 0 && count($errors) > 0) {
        $message .= "<br>Errors: " . implode('<br>', array_slice($errors, 0, 5));
        if (count($errors) > 5) {
            $message .= "<br>... and " . (count($errors) - 5) . " more errors";
        }
    }
    
    echo json_encode([
        'success' => true,
        'message' => $message,
        'updated' => $success_count,
        'failed' => $error_count,
        'errors' => $errors
    ]);
    
} catch (Exception $e) {
    // Rollback transaction on error
    $conn->rollback();
    fclose($handle);
    
    echo json_encode([
        'success' => false,
        'error' => 'Upload failed: ' . $e->getMessage()
    ]);
}
?>