<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: POST');
header('Access-Control-Allow-Headers: Content-Type');

// Error reporting for development
error_reporting(E_ALL);
ini_set('display_errors', 1);

require_once '../config/database.php'; // Make sure this file exists

// Check if file was uploaded
if (!isset($_FILES['studentDataFile'])) {
    echo json_encode([
        'success' => false,
        'message' => 'No file uploaded',
        'errors' => ['Please select an Excel file']
    ]);
    exit;
}

$file = $_FILES['studentDataFile'];

// Check for upload errors
if ($file['error'] !== UPLOAD_ERR_OK) {
    echo json_encode([
        'success' => false,
        'message' => 'File upload error',
        'errors' => ['Upload error code: ' . $file['error']]
    ]);
    exit;
}

// Validate file type
$allowed_extensions = ['xlsx', 'xls'];
$file_extension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));

if (!in_array($file_extension, $allowed_extensions)) {
    echo json_encode([
        'success' => false,
        'message' => 'Invalid file type',
        'errors' => ['Please upload only Excel files (.xlsx or .xls)']
    ]);
    exit;
}

// Check file size (max 500MB)
if ($file['size'] > 500 * 1024 * 1024) {
    echo json_encode([
        'success' => false,
        'message' => 'File too large',
        'errors' => ['Maximum file size is 500MB']
    ]);
    exit;
}

// Create uploads directory if it doesn't exist
$upload_dir = '../uploads/excel/';
if (!file_exists($upload_dir)) {
    mkdir($upload_dir, 0777, true);
}

// Generate unique filename
$filename = uniqid('student_data_') . '_' . time() . '.' . $file_extension;
$filepath = $upload_dir . $filename;

// Move uploaded file
if (!move_uploaded_file($file['tmp_name'], $filepath)) {
    echo json_encode([
        'success' => false,
        'message' => 'Failed to save file',
        'errors' => ['Could not save uploaded file']
    ]);
    exit;
}

// Process Excel file
try {
    require_once '../vendor/autoload.php'; // For PhpSpreadsheet
    
    // Load the Excel file
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filepath);
    $worksheet = $spreadsheet->getActiveSheet();
    $rows = $worksheet->toArray();
    
    // Remove header row
    $header = array_shift($rows);
    
    // Generate batch ID
    $batch_id = 'BATCH_' . date('YmdHis') . '_' . uniqid();
    
    // Database connection
    $conn = getDBConnection(); // Assuming this function exists in database.php
    
    if (!$conn) {
        throw new Exception('Database connection failed');
    }
    
    // Begin transaction
    $conn->begin_transaction();
    
    $total_records = 0;
    $inserted = 0;
    $updated = 0;
    $skipped = 0;
    $errors = [];
    
    // Get upload options
    $updateExisting = isset($_POST['updateExisting']) && $_POST['updateExisting'] == '1';
    $skipDuplicates = isset($_POST['skipDuplicates']) && $_POST['skipDuplicates'] == '1';
    
    foreach ($rows as $index => $row) {
        $total_records++;
        $row_number = $index + 2; // +2 because we removed header and Excel is 1-indexed
        
        try {
            // Skip empty rows
            if (empty($row[0]) && empty($row[1])) {
                $skipped++;
                continue;
            }
            
            // Map columns (adjust based on your Excel structure)
            $data = [
                'serial_number' => $row[0] ?? null, // S/N
                'matric_num' => $row[1] ?? null,    // Matricnum
                'app_num' => $row[2] ?? null,       // Appnum
                'surname' => $row[3] ?? null,       // Surname
                'firstname' => $row[4] ?? null,     // Firstname
                'othername' => $row[5] ?? null,     // Othername
                'programme' => $row[6] ?? null,     // Programme
                'level' => $row[7] ?? null,         // Level
                'amount' => is_numeric($row[8]) ? $row[8] : null, // Amount
                'receipt_no' => $row[9] ?? null,    // Receipt No.
                'phone' => $row[10] ?? null,        // Phone
                'payment_date' => !empty($row[11]) ? date('Y-m-d', strtotime($row[11])) : null, // Date
                'upload_batch' => $batch_id
            ];
            
            // Validate required fields
            if (empty($data['matric_num']) || empty($data['surname']) || empty($data['firstname'])) {
                $errors[] = "Row $row_number: Missing required fields (Matric, Surname, or Firstname)";
                $skipped++;
                continue;
            }
            
            // Insert into temporary table
            $stmt = $conn->prepare("
                INSERT INTO student_data_temp (
                    serial_number, matric_num, app_num, surname, firstname, othername,
                    programme, level, amount, receipt_no, phone, payment_date, upload_batch
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ");
            
            $stmt->bind_param(
                "ssssssssdssss",
                $data['serial_number'],
                $data['matric_num'],
                $data['app_num'],
                $data['surname'],
                $data['firstname'],
                $data['othername'],
                $data['programme'],
                $data['level'],
                $data['amount'],
                $data['receipt_no'],
                $data['phone'],
                $data['payment_date'],
                $data['upload_batch']
            );
            
            if ($stmt->execute()) {
                $temp_id = $conn->insert_id;
                
                // Process to main students table
                $full_name = trim($data['surname'] . ' ' . $data['firstname'] . ' ' . ($data['othername'] ?? ''));
                $full_name = preg_replace('/\s+/', ' ', $full_name);
                
                // Check if student exists
                $check_stmt = $conn->prepare("SELECT id FROM students WHERE student_id = ?");
                $check_stmt->bind_param("s", $data['matric_num']);
                $check_stmt->execute();
                $check_result = $check_stmt->get_result();
                
                if ($check_result->num_rows > 0) {
                    // Student exists
                    if ($updateExisting) {
                        // Update existing student
                        $update_stmt = $conn->prepare("
                            UPDATE students SET 
                                surname = ?,
                                firstname = ?,
                                othername = ?,
                                full_name = ?,
                                app_num = ?,
                                program = ?,
                                department = CASE 
                                    WHEN ? LIKE '%ACCOUNTANCY%' THEN 'School of Management'
                                    WHEN ? LIKE '%ENGINEERING%' THEN 'School of Engineering'
                                    WHEN ? LIKE '%SCIENCE%' OR ? LIKE '%TECHNOLOGY%' THEN 'School of Science and Technology'
                                    ELSE 'General Studies'
                                END,
                                phone = ?,
                                amount_paid = ?,
                                receipt_no = ?,
                                payment_date = ?,
                                last_updated = NOW()
                            WHERE student_id = ?
                        ");
                        
                        $program = $data['programme'];
                        $update_stmt->bind_param(
                            "ssssssssssdsss",
                            $data['surname'],
                            $data['firstname'],
                            $data['othername'],
                            $full_name,
                            $data['app_num'],
                            $program,
                            $program, $program, $program, $program,
                            $data['phone'],
                            $data['amount'],
                            $data['receipt_no'],
                            $data['payment_date'],
                            $data['matric_num']
                        );
                        
                        if ($update_stmt->execute()) {
                            $updated++;
                        } else {
                            $errors[] = "Row $row_number: Failed to update student - " . $update_stmt->error;
                        }
                        $update_stmt->close();
                    } else {
                        $skipped++;
                    }
                } else {
                    // Insert new student
                    $insert_stmt = $conn->prepare("
                        INSERT INTO students (
                            student_id, app_num, surname, firstname, othername, full_name,
                            program, department, grade, graduation_date, cgpa,
                            phone, amount_paid, receipt_no, payment_date, created_at
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'Pass', CURDATE(), NULL, ?, ?, ?, ?, NOW())
                    ");
                    
                    // Determine department based on program
                    $program = $data['programme'];
                    $department = 'General Studies';
                    if (stripos($program, 'ACCOUNTANCY') !== false) {
                        $department = 'School of Management';
                    } elseif (stripos($program, 'ENGINEERING') !== false) {
                        $department = 'School of Engineering';
                    } elseif (stripos($program, 'SCIENCE') !== false || stripos($program, 'TECHNOLOGY') !== false) {
                        $department = 'School of Science and Technology';
                    }
                    
                    $insert_stmt->bind_param(
                        "sssssssssdsss",
                        $data['matric_num'],
                        $data['app_num'],
                        $data['surname'],
                        $data['firstname'],
                        $data['othername'],
                        $full_name,
                        $program,
                        $department,
                        $data['phone'],
                        $data['amount'],
                        $data['receipt_no'],
                        $data['payment_date']
                    );
                    
                    if ($insert_stmt->execute()) {
                        $inserted++;
                    } else {
                        $errors[] = "Row $row_number: Failed to insert student - " . $insert_stmt->error;
                    }
                    $insert_stmt->close();
                }
                
                $check_stmt->close();
            }
            
            $stmt->close();
            
        } catch (Exception $e) {
            $errors[] = "Row $row_number: " . $e->getMessage();
            $skipped++;
        }
    }
    
    // Commit transaction
    $conn->commit();
    
    // Log the upload
    $log_stmt = $conn->prepare("
        INSERT INTO upload_logs (filename, records_processed, inserted_count, updated_count, error_count, status)
        VALUES (?, ?, ?, ?, ?, ?)
    ");
    
    $status = empty($errors) ? 'success' : ($inserted > 0 || $updated > 0 ? 'partial' : 'failed');
    $log_stmt->bind_param(
        "siiiss",
        $filename,
        $total_records,
        $inserted,
        $updated,
        count($errors),
        $status
    );
    $log_stmt->execute();
    $log_stmt->close();
    
    $conn->close();
    
    echo json_encode([
        'success' => true,
        'message' => 'Student data processed successfully',
        'total_records' => $total_records,
        'imported' => $inserted,
        'updated' => $updated,
        'skipped' => $skipped,
        'errors' => $errors,
        'batch_id' => $batch_id,
        'filename' => $filename
    ]);
    
} catch (Exception $e) {
    // Rollback transaction on error
    if (isset($conn) && $conn instanceof mysqli) {
        $conn->rollback();
        $conn->close();
    }
    
    echo json_encode([
        'success' => false,
        'message' => 'Error processing Excel file',
        'errors' => [$e->getMessage()]
    ]);
}
?>