<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

include_once 'config/database.php';

// Create uploads directory if it doesn't exist
if (!file_exists('uploads')) {
    mkdir('uploads', 0777, true);
}
if (!file_exists('uploads/temp')) {
    mkdir('uploads/temp', 0777, true);
}

class BulkUpload {
    private $conn;
    private $table_name = "students";

    public function __construct($db) {
        $this->conn = $db;
    }

    public function processUpload($filePath) {
        require_once 'vendor/autoload.php'; // Include PhpSpreadsheet

        $results = [
            'inserted' => 0,
            'updated' => 0,
            'errors' => 0,
            'error_messages' => []
        ];

        try {
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            $data = $worksheet->toArray();

            // Remove header row
            $header = array_shift($data);

            foreach ($data as $rowIndex => $row) {
                // Skip empty rows
                if (empty(array_filter($row))) {
                    continue;
                }

                // Map row data to columns
                $studentData = array_combine($header, $row);

                // Validate required fields
                if (!$this->validateStudentData($studentData)) {
                    $results['errors']++;
                    $results['error_messages'][] = "Row " . ($rowIndex + 2) . ": Missing required fields";
                    continue;
                }

                // Check if student exists
                $existingStudent = $this->getStudentByID($studentData['student_id']);

                if ($existingStudent) {
                    // Update existing student
                    if ($this->updateStudent($existingStudent['id'], $studentData)) {
                        $results['updated']++;
                    } else {
                        $results['errors']++;
                        $results['error_messages'][] = "Row " . ($rowIndex + 2) . ": Failed to update student";
                    }
                } else {
                    // Insert new student
                    if ($this->insertStudent($studentData)) {
                        $results['inserted']++;
                    } else {
                        $results['errors']++;
                        $results['error_messages'][] = "Row " . ($rowIndex + 2) . ": Failed to insert student";
                    }
                }
            }

            // Log upload
            $this->logUpload(basename($filePath), $results);

            return $results;

        } catch (Exception $e) {
            $results['errors']++;
            $results['error_messages'][] = "File processing error: " . $e->getMessage();
            return $results;
        }
    }

    private function validateStudentData($data) {
        $required = ['student_id', 'full_name', 'program', 'department', 'grade', 'graduation_date'];
        
        foreach ($required as $field) {
            if (empty($data[$field])) {
                return false;
            }
        }

        // Validate grade
        $validGrades = ['Distinction', 'Upper Credit', 'Lower Credit', 'Pass'];
        if (!in_array($data['grade'], $validGrades)) {
            return false;
        }

        // Validate date format
        if (!strtotime($data['graduation_date'])) {
            return false;
        }

        return true;
    }

    private function getStudentByID($student_id) {
        $query = "SELECT id FROM " . $this->table_name . " WHERE student_id = ? LIMIT 1";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $student_id);
        $stmt->execute();

        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    private function insertStudent($data) {
        $query = "INSERT INTO " . $this->table_name . " 
                  (student_id, full_name, program, department, grade, graduation_date, photo_path, certificate_id) 
                  VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        $stmt = $this->conn->prepare($query);

        return $stmt->execute([
            $data['student_id'],
            $data['full_name'],
            $data['program'],
            $data['department'],
            $data['grade'],
            $data['graduation_date'],
            $data['photo_path'] ?? null,
            $data['certificate_id'] ?? null
        ]);
    }

    private function updateStudent($id, $data) {
        $query = "UPDATE " . $this->table_name . " 
                  SET full_name = ?, program = ?, department = ?, grade = ?, 
                      graduation_date = ?, photo_path = ?, certificate_id = ?
                  WHERE id = ?";

        $stmt = $this->conn->prepare($query);

        return $stmt->execute([
            $data['full_name'],
            $data['program'],
            $data['department'],
            $data['grade'],
            $data['graduation_date'],
            $data['photo_path'] ?? null,
            $data['certificate_id'] ?? null,
            $id
        ]);
    }

    private function logUpload($filename, $results) {
        $query = "INSERT INTO upload_logs 
                  (filename, records_processed, inserted_count, updated_count, error_count, status) 
                  VALUES (?, ?, ?, ?, ?, ?)";

        $stmt = $this->conn->prepare($query);
        
        $total = $results['inserted'] + $results['updated'] + $results['errors'];
        $status = $results['errors'] == 0 ? 'success' : 'partial';

        $stmt->execute([
            $filename,
            $total,
            $results['inserted'],
            $results['updated'],
            $results['errors'],
            $status
        ]);
    }
}

// Main processing
if ($_FILES['file']['error'] === UPLOAD_ERR_OK) {
    $database = new Database();
    $db = $database->getConnection();

    $uploader = new BulkUpload($db);

    $temp_file = $_FILES['file']['tmp_name'];
    $results = $uploader->processUpload($temp_file);

    echo json_encode([
        'success' => true,
        'inserted' => $results['inserted'],
        'updated' => $results['updated'],
        'errors' => $results['errors'],
        'error_messages' => $results['error_messages']
    ]);
} else {
    echo json_encode([
        'success' => false,
        'message' => 'File upload failed. Error code: ' . $_FILES['file']['error']
    ]);
}
?>