xp_management_handler.php

<?php
// Handle AJAX requests for XP management

// Start session if not already started
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// Include config and check database connection
try {
    include 'config.php';
    if (!$conn) {
        throw new Exception('Database connection failed');
    }
} catch (Exception $e) {
    header('Content-Type: application/json');
    echo json_encode(['success' => false, 'message' => 'Configuration error: ' . $e->getMessage()]);
    exit();
}

if (isset($_POST['action'])) {
    header('Content-Type: application/json');

    if ($_POST['action'] === 'get_pending_requests') {
        $query = "
            SELECT
                r.id,
                r.student_id,
                r.item_id,
                r.xp_cost,
                r.requested_at,
                r.status,
                u.first_name,
                u.last_name,
                u.class_group,
                i.title as item_title,
                i.description as item_description
            FROM xp_spending_requests r
            JOIN users u ON r.student_id = u.id
            JOIN xp_spending_items i ON r.item_id = i.id
            WHERE r.status = 'pending'
            ORDER BY r.requested_at DESC
        ";

        $result = $conn->query($query);
        $requests = [];

        while ($row = $result->fetch_assoc()) {
            $requests[] = [
                'id' => $row['id'],
                'student_name' => $row['first_name'] . ' ' . $row['last_name'],
                'class_group' => $row['class_group'],
                'item_title' => $row['item_title'],
                'item_description' => $row['item_description'],
                'xp_cost' => (int)$row['xp_cost'],
                'requested_at' => $row['requested_at']
            ];
        }

        echo json_encode(['success' => true, 'requests' => $requests]);
        exit();
    }

    if ($_POST['action'] === 'process_request') {
        try {
            $request_id = (int)$_POST['request_id'];
            $action = $_POST['request_action']; // 'approve' or 'deny'
            $admin_response = isset($_POST['admin_response']) ? trim($_POST['admin_response']) : '';

            // Get request details
            $request_query = "
                SELECT r.student_id, r.item_id, r.xp_cost, i.title
                FROM xp_spending_requests r
                JOIN xp_spending_items i ON r.item_id = i.id
                WHERE r.id = ?
            ";
            $stmt = $conn->prepare($request_query);
            if (!$stmt) {
                throw new Exception('Prepare failed: ' . $conn->error);
            }
            $stmt->bind_param('i', $request_id);
            $stmt->execute();
            $request_result = $stmt->get_result();

            if ($request_result->num_rows === 0) {
                echo json_encode(['success' => false, 'message' => 'Request not found']);
                exit();
            }

            $request = $request_result->fetch_assoc();
            $stmt->close();

            if ($action === 'approve') {
                // Check if student still has enough XP
                $xp_query = "SELECT COALESCE(SUM(xp_earned), 0) as total_xp FROM user_activity WHERE user_id = ?";
                $stmt_xp = $conn->prepare($xp_query);
                if (!$stmt_xp) {
                    throw new Exception('XP query prepare failed: ' . $conn->error);
                }
                $stmt_xp->bind_param('i', $request['student_id']);
                $stmt_xp->execute();
                $xp_result = $stmt_xp->get_result();
                $xp_row = $xp_result->fetch_assoc();
                $current_xp = (int)$xp_row['total_xp'];
                $stmt_xp->close();

                if ($current_xp < $request['xp_cost']) {
                    echo json_encode(['success' => false, 'message' => 'Student no longer has enough XP']);
                    exit();
                }

                // Deduct XP by logging a negative XP activity
                $log_result = log_user_activity($request['student_id'], 'xp_spending', 'XP spent on: ' . $request['title'], -$request['xp_cost'], 0);
                if (!$log_result) {
                    throw new Exception('Failed to log XP deduction');
                }

                // Update request status
                $update_query = "UPDATE xp_spending_requests SET status = 'approved', admin_response = ?, processed_at = NOW(), processed_by = ? WHERE id = ?";
                $stmt_update = $conn->prepare($update_query);
                if (!$stmt_update) {
                    throw new Exception('Update query prepare failed: ' . $conn->error);
                }
                $stmt_update->bind_param('sii', $admin_response, $_SESSION['user_id'], $request_id);
                $stmt_update->execute();
                $stmt_update->close();

                // Create notification for student
                $notification_message = "Your XP spending request for '{$request['title']}' has been approved!";
                if (!empty($admin_response)) {
                    $notification_message .= "\n\nTeacher's note: " . $admin_response;
                }

                $notif_query = "INSERT INTO notifications (user_id, title, message, type) VALUES (?, 'XP Request Approved', ?, 'success')";
                $stmt_notif = $conn->prepare($notif_query);
                if (!$stmt_notif) {
                    throw new Exception('Notification query prepare failed: ' . $conn->error);
                }
                $stmt_notif->bind_param('is', $request['student_id'], $notification_message);
                $stmt_notif->execute();
                $stmt_notif->close();

                echo json_encode(['success' => true, 'message' => 'Request approved successfully']);

            } elseif ($action === 'deny') {
                // Update request status
                $update_query = "UPDATE xp_spending_requests SET status = 'denied', admin_response = ?, processed_at = NOW(), processed_by = ? WHERE id = ?";
                $stmt_update = $conn->prepare($update_query);
                if (!$stmt_update) {
                    throw new Exception('Deny update query prepare failed: ' . $conn->error);
                }
                $stmt_update->bind_param('sii', $admin_response, $_SESSION['user_id'], $request_id);
                $stmt_update->execute();
                $stmt_update->close();

                // Create notification for student
                $notification_message = "Your XP spending request for '{$request['title']}' has been denied.";
                if (!empty($admin_response)) {
                    $notification_message .= "\n\nReason: " . $admin_response;
                }

                $notif_query = "INSERT INTO notifications (user_id, title, message, type) VALUES (?, 'XP Request Denied', ?, 'warning')";
                $stmt_notif = $conn->prepare($notif_query);
                if (!$stmt_notif) {
                    throw new Exception('Deny notification query prepare failed: ' . $conn->error);
                }
                $stmt_notif->bind_param('is', $request['student_id'], $notification_message);
                $stmt_notif->execute();
                $stmt_notif->close();

                echo json_encode(['success' => true, 'message' => 'Request denied successfully']);
            } else {
                echo json_encode(['success' => false, 'message' => 'Invalid action']);
            }
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        }
        exit();
    }

    if ($_POST['action'] === 'get_xp_items') {
        $query = "SELECT * FROM xp_spending_items ORDER BY xp_cost ASC";
        $result = $conn->query($query);
        $items = [];

        while ($row = $result->fetch_assoc()) {
            $items[] = [
                'id' => (int)$row['id'],
                'title' => $row['title'],
                'description' => $row['description'],
                'xp_cost' => (int)$row['xp_cost'],
                'category' => $row['category'],
                'is_active' => (bool)$row['is_active'],
                'created_at' => $row['created_at']
            ];
        }

        echo json_encode(['success' => true, 'items' => $items]);
        exit();
    }

    if ($_POST['action'] === 'save_xp_item') {
        $item_id = isset($_POST['item_id']) ? (int)$_POST['item_id'] : null;
        $title = trim($_POST['title']);
        $description = trim($_POST['description']);
        $xp_cost = (int)$_POST['xp_cost'];
        $category = trim($_POST['category']);
        $is_active = isset($_POST['is_active']) ? 1 : 0;

        if (empty($title) || $xp_cost <= 0) {
            echo json_encode(['success' => false, 'message' => 'Title and XP cost are required']);
            exit();
        }

        if ($item_id) {
            // Update existing item
            $query = "UPDATE xp_spending_items SET title = ?, description = ?, xp_cost = ?, category = ?, is_active = ? WHERE id = ?";
            $stmt = $conn->prepare($query);
            $stmt->bind_param('ssisii', $title, $description, $xp_cost, $category, $is_active, $item_id);
        } else {
            // Create new item
            $query = "INSERT INTO xp_spending_items (title, description, xp_cost, category, is_active) VALUES (?, ?, ?, ?, ?)";
            $stmt = $conn->prepare($query);
            $stmt->bind_param('ssisi', $title, $description, $xp_cost, $category, $is_active);
        }

        if ($stmt->execute()) {
            echo json_encode(['success' => true, 'message' => $item_id ? 'Item updated successfully' : 'Item created successfully']);
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $conn->error]);
        }
        $stmt->close();
        exit();
    }

    if ($_POST['action'] === 'delete_xp_item') {
        $item_id = (int)$_POST['item_id'];

        // Check if item has pending requests
        $check_query = "SELECT COUNT(*) as pending_count FROM xp_spending_requests WHERE item_id = ? AND status = 'pending'";
        $stmt_check = $conn->prepare($check_query);
        $stmt_check->bind_param('i', $item_id);
        $stmt_check->execute();
        $check_result = $stmt_check->get_result();
        $check_row = $check_result->fetch_assoc();

        if ($check_row['pending_count'] > 0) {
            echo json_encode(['success' => false, 'message' => 'Cannot delete item with pending requests']);
            exit();
        }

        $stmt_check->close();

        // Delete the item
        $query = "DELETE FROM xp_spending_items WHERE id = ?";
        $stmt = $conn->prepare($query);
        $stmt->bind_param('i', $item_id);

        if ($stmt->execute()) {
            echo json_encode(['success' => true, 'message' => 'Item deleted successfully']);
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $conn->error]);
        }
        $stmt->close();
        exit();
    }

    if ($_POST['action'] === 'get_request_history') {
        $query = "
            SELECT
                r.id,
                r.student_id,
                r.item_id,
                r.xp_cost,
                r.status,
                r.admin_response,
                r.requested_at,
                r.processed_at,
                u.first_name,
                u.last_name,
                u.class_group,
                i.title as item_title,
                p.first_name as processor_first_name,
                p.last_name as processor_last_name
            FROM xp_spending_requests r
            JOIN users u ON r.student_id = u.id
            JOIN xp_spending_items i ON r.item_id = i.id
            LEFT JOIN users p ON r.processed_by = p.id
            WHERE r.status IN ('approved', 'denied')
            ORDER BY r.processed_at DESC
            LIMIT 100
        ";

        $result = $conn->query($query);
        $history = [];

        while ($row = $result->fetch_assoc()) {
            $history[] = [
                'id' => $row['id'],
                'student_name' => $row['first_name'] . ' ' . $row['last_name'],
                'class_group' => $row['class_group'],
                'item_title' => $row['item_title'],
                'xp_cost' => (int)$row['xp_cost'],
                'status' => $row['status'],
                'admin_response' => $row['admin_response'],
                'requested_at' => $row['requested_at'],
                'processed_at' => $row['processed_at'],
                'processor_name' => $row['processor_first_name'] ? $row['processor_first_name'] . ' ' . $row['processor_last_name'] : 'System'
            ];
        }

        echo json_encode(['success' => true, 'history' => $history]);
        exit();
    }

    if ($_POST['action'] === 'delete_request_history') {
        $request_id = (int)$_POST['request_id'];

        // Only allow deletion of processed requests (not pending ones)
        $check_query = "SELECT status FROM xp_spending_requests WHERE id = ? AND status IN ('approved', 'denied')";
        $stmt_check = $conn->prepare($check_query);
        $stmt_check->bind_param('i', $request_id);
        $stmt_check->execute();
        $check_result = $stmt_check->get_result();

        if ($check_result->num_rows === 0) {
            echo json_encode(['success' => false, 'message' => 'Request not found or still pending']);
            exit();
        }

        $stmt_check->close();

        // Delete the request from history
        $query = "DELETE FROM xp_spending_requests WHERE id = ?";
        $stmt = $conn->prepare($query);
        $stmt->bind_param('i', $request_id);

        if ($stmt->execute()) {
            echo json_encode(['success' => true, 'message' => 'Request deleted from history successfully']);
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $conn->error]);
        }
        $stmt->close();
        exit();
    }
}

// Helper function
function log_user_activity($user_id, $activity_type, $activity_name, $xp_earned = 0, $points_earned = 0) {
    global $conn;

    $stmt = mysqli_prepare($conn, "
        INSERT INTO user_activity (user_id, activity_type, activity_name, xp_earned, points_earned, completed_at)
        VALUES (?, ?, ?, ?, ?, NOW())
    ");

    mysqli_stmt_bind_param($stmt, 'issii', $user_id, $activity_type, $activity_name, $xp_earned, $points_earned);
    $stmt_execute = $stmt->execute();
    $stmt->close();

    return $stmt_execute;
}
?>