<?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';

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

$file = $_FILES['certificateRecordsFile'];

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

$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;
}

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

$upload_dir = '../uploads/excel/';
if (!file_exists($upload_dir)) {
    mkdir($upload_dir, 0777, true);
}

$filename = uniqid('certificate_records_') . '_' . time() . '.' . $file_extension;
$filepath = $upload_dir . $filename;

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;
}

try {
    require_once '../vendor/autoload.php';
    
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filepath);
    $worksheet = $spreadsheet->getActiveSheet();
    $rows = $worksheet->toArray();
    
    $header = array_shift($rows);
    
    $batch_id = 'CERT_BATCH_' . date('YmdHis') . '_' . uniqid();
    
    $conn = getDBConnection();
    
    if (!$conn) {
        throw new Exception('Database connection failed');
    }
    
    $conn->begin_transaction();
    
    $total_records = 0;
    $processed = 0;
    $updated = 0;
    $created = 0;
    $errors = [];
    
    $updateGrades = isset($_POST['updateGrades']) && $_POST['updateGrades'] == '1';
    $generateIds = isset($_POST['generateIds']) && $_POST['generateIds'] == '1';
    
    foreach ($rows as $index => $row) {
        $total_records++;
        $row_number = $index + 2;
        
        try {
            if (empty($row[0]) && empty($row[2])) {
                continue;
            }
            
            $data = [
                'serial_number' => $row[0] ?? null,      // S/N
                'session_name' => $row[1] ?? null,       // SessionName
                'matric_num' => $row[2] ?? null,         // MatricNum
                'surname' => $row[3] ?? null,            // Surname
                'firstname' => $row[4] ?? null,          // Firstname
                'othername' => $row[5] ?? null,          // Othername
                'programme' => $row[6] ?? null,          // Programme
                'cgpa' => is_numeric($row[7]) ? $row[7] : null, // Cgpa
                'grade' => $row[8] ?? null,              // Grade
                'level_name' => $row[9] ?? null,         // LevelName
                'semester' => $row[10] ?? null,          // Semester
                'date_graduated' => !empty($row[11]) ? date('Y-m-d', strtotime($row[11])) : null, // DateGraduated
                'phone' => $row[12] ?? null,             // Phone
                'email' => $row[13] ?? null,             // Email
                'upload_batch' => $batch_id
            ];
            
            // Validate required fields
            if (empty($data['matric_num']) || empty($data['surname']) || empty($data['grade'])) {
                $errors[] = "Row $row_number: Missing required fields";
                continue;
            }
            
            // Insert into temporary table
            $temp_stmt = $conn->prepare("
                INSERT INTO certificate_records_temp (
                    serial_number, session_name, matric_num, surname, firstname, othername,
                    programme, cgpa, grade, level_name, semester, date_graduated, phone, email, upload_batch
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ");
            
            $temp_stmt->bind_param(
                "sssssssdsssssss",
                $data['serial_number'],
                $data['session_name'],
                $data['matric_num'],
                $data['surname'],
                $data['firstname'],
                $data['othername'],
                $data['programme'],
                $data['cgpa'],
                $data['grade'],
                $data['level_name'],
                $data['semester'],
                $data['date_graduated'],
                $data['phone'],
                $data['email'],
                $data['upload_batch']
            );
            
            if ($temp_stmt->execute()) {
                // Update 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 - update with certificate data
                    if ($updateGrades) {
                        $update_stmt = $conn->prepare("
                            UPDATE students SET 
                                surname = COALESCE(?, surname),
                                firstname = COALESCE(?, firstname),
                                othername = COALESCE(?, othername),
                                full_name = COALESCE(?, full_name),
                                program = COALESCE(?, program),
                                grade = ?,
                                cgpa = ?,
                                graduation_date = COALESCE(?, graduation_date),
                                session_name = ?,
                                level_name = ?,
                                semester = ?,
                                phone = COALESCE(?, phone),
                                email = COALESCE(?, email),
                                last_updated = NOW()
                            WHERE student_id = ?
                        ");
                        
                        $grade = $this->normalizeGrade($data['grade']);
                        
                        $update_stmt->bind_param(
                            "ssssssdssssss",
                            $data['surname'],
                            $data['firstname'],
                            $data['othername'],
                            $full_name,
                            $data['programme'],
                            $grade,
                            $data['cgpa'],
                            $data['date_graduated'],
                            $data['session_name'],
                            $data['level_name'],
                            $data['semester'],
                            $data['phone'],
                            $data['email'],
                            $data['matric_num']
                        );
                        
                        if ($update_stmt->execute()) {
                            $updated++;
                            $processed++;
                            
                            // Generate certificate ID if needed
                            if ($generateIds) {
                                $this->generateCertificateId($conn, $data['matric_num']);
                            }
                        } else {
                            $errors[] = "Row $row_number: Failed to update student - " . $update_stmt->error;
                        }
                        $update_stmt->close();
                    }
                } else {
                    // Student doesn't exist - create new record
                    $insert_stmt = $conn->prepare("
                        INSERT INTO students (
                            student_id, surname, firstname, othername, full_name,
                            program, department, grade, cgpa, graduation_date,
                            session_name, level_name, semester, phone, email, created_at
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
                    ");
                    
                    $program = $data['programme'] ?? 'Unknown Program';
                    $grade = $this->normalizeGrade($data['grade']);
                    $department = $this->determineDepartment($program);
                    
                    $insert_stmt->bind_param(
                        "ssssssssdssssss",
                        $data['matric_num'],
                        $data['surname'],
                        $data['firstname'],
                        $data['othername'],
                        $full_name,
                        $program,
                        $department,
                        $grade,
                        $data['cgpa'],
                        $data['date_graduated'],
                        $data['session_name'],
                        $data['level_name'],
                        $data['semester'],
                        $data['phone'],
                        $data['email']
                    );
                    
                    if ($insert_stmt->execute()) {
                        $created++;
                        $processed++;
                        
                        // Generate certificate ID if needed
                        if ($generateIds) {
                            $this->generateCertificateId($conn, $data['matric_num']);
                        }
                    } else {
                        $errors[] = "Row $row_number: Failed to create student - " . $insert_stmt->error;
                    }
                    $insert_stmt->close();
                }
                
                $check_stmt->close();
            }
            
            $temp_stmt->close();
            
        } catch (Exception $e) {
            $errors[] = "Row $row_number: " . $e->getMessage();
        }
    }
    
    $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' : ($processed > 0 ? 'partial' : 'failed');
    $log_stmt->bind_param(
        "siiiss",
        $filename,
        $total_records,
        $created,
        $updated,
        count($errors),
        $status
    );
    $log_stmt->execute();
    $log_stmt->close();
    
    $conn->close();
    
    echo json_encode([
        'success' => true,
        'message' => 'Certificate records processed successfully',
        'total_records' => $total_records,
        'processed' => $processed,
        'created' => $created,
        'updated' => $updated,
        'errors' => $errors,
        'batch_id' => $batch_id,
        'filename' => $filename
    ]);
    
} catch (Exception $e) {
    if (isset($conn) && $conn instanceof mysqli) {
        $conn->rollback();
        $conn->close();
    }
    
    echo json_encode([
        'success' => false,
        'message' => 'Error processing certificate records',
        'errors' => [$e->getMessage()]
    ]);
}

// Helper functions
function normalizeGrade($grade) {
    $grade = strtoupper(trim($grade));
    
    if (strpos($grade, 'DISTINCTION') !== false) {
        return 'Distinction';
    } elseif (strpos($grade, 'UPPER CREDIT') !== false) {
        return 'Upper Credit';
    } elseif (strpos($grade, 'LOWER CREDIT') !== false) {
        return 'Lower Credit';
    } elseif (strpos($grade, 'PASS') !== false) {
        return 'Pass';
    } else {
        return 'Pass'; // Default
    }
}

function determineDepartment($program) {
    $program = strtoupper($program);
    
    if (strpos($program, 'ACCOUNTANCY') !== false || strpos($program, 'MANAGEMENT') !== false) {
        return 'School of Management';
    } elseif (strpos($program, 'ENGINEERING') !== false) {
        return 'School of Engineering';
    } elseif (strpos($program, 'SCIENCE') !== false || strpos($program, 'TECHNOLOGY') !== false) {
        return 'School of Science and Technology';
    } else {
        return 'General Studies';
    }
}

function generateCertificateId($conn, $matric_num) {
    // Check if certificate ID already exists
    $check_stmt = $conn->prepare("SELECT certificate_id FROM students WHERE student_id = ? AND certificate_id IS NOT NULL");
    $check_stmt->bind_param("s", $matric_num);
    $check_stmt->execute();
    $result = $check_stmt->get_result();
    
    if ($result->num_rows > 0) {
        $check_stmt->close();
        return; // Certificate ID already exists
    }
    
    // Generate new certificate ID
    $student_stmt = $conn->prepare("SELECT id, program, graduation_date FROM students WHERE student_id = ?");
    $student_stmt->bind_param("s", $matric_num);
    $student_stmt->execute();
    $student_result = $student_stmt->get_result();
    
    if ($student_row = $student_result->fetch_assoc()) {
        $year = date('Y', strtotime($student_row['graduation_date']));
        $program_abbr = getProgramAbbreviation($student_row['program']);
        $serial = str_pad($student_row['id'], 3, '0', STR_PAD_LEFT);
        
        $certificate_id = "GIPS-{$program_abbr}-{$year}-{$serial}";
        
        $update_stmt = $conn->prepare("UPDATE students SET certificate_id = ? WHERE student_id = ?");
        $update_stmt->bind_param("ss", $certificate_id, $matric_num);
        $update_stmt->execute();
        $update_stmt->close();
    }
    
    $student_stmt->close();
}

function getProgramAbbreviation($program) {
    $program = strtoupper($program);
    
    if (strpos($program, 'ACCOUNTANCY') !== false) {
        return 'ACC';
    } elseif (strpos($program, 'ENGINEERING') !== false) {
        return 'ENG';
    } elseif (strpos($program, 'SCIENCE') !== false) {
        return 'SCI';
    } elseif (strpos($program, 'TECHNOLOGY') !== false) {
        return 'TECH';
    } else {
        return 'GEN';
    }
}
?>