tinker_tickets/models/BulkOperationsModel.php

<?php
/**
 * BulkOperationsModel - Handles bulk ticket operations (Admin only)
 */
class BulkOperationsModel {
    private $conn;

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

    /**
     * Create a new bulk operation record
     *
     * @param string $type Operation type (bulk_close, bulk_assign, bulk_priority)
     * @param array $ticketIds Array of ticket IDs
     * @param int $userId User performing the operation
     * @param array|null $parameters Operation parameters
     * @return int|false Operation ID or false on failure
     */
    public function createBulkOperation($type, $ticketIds, $userId, $parameters = null) {
        // Validate ticket IDs to prevent injection via implode
        $ticketIds = array_values(array_filter(
            array_map('strval', $ticketIds),
            fn($id) => preg_match('/^[0-9]+$/', $id)
        ));
        if (empty($ticketIds)) {
            return false;
        }
        $ticketIdsStr = implode(',', $ticketIds);
        $totalTickets = count($ticketIds);
        $parametersJson = $parameters ? json_encode($parameters) : null;

        $sql = "INSERT INTO bulk_operations (operation_type, ticket_ids, performed_by, parameters, total_tickets)
                VALUES (?, ?, ?, ?, ?)";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("ssisi", $type, $ticketIdsStr, $userId, $parametersJson, $totalTickets);

        if ($stmt->execute()) {
            $operationId = $this->conn->insert_id;
            $stmt->close();
            return $operationId;
        }

        $stmt->close();
        return false;
    }

    /**
     * Process a bulk operation
     *
     * Uses database transaction to ensure atomicity - either all tickets
     * are updated or none are (on failure, changes are rolled back).
     *
     * @param int $operationId Operation ID
     * @param bool $atomic If true, rollback all changes on any failure
     * @return array Result with processed and failed counts
     */
    public function processBulkOperation($operationId, bool $atomic = false) {
        // Get operation details
        $sql = "SELECT * FROM bulk_operations WHERE operation_id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $operationId);
        $stmt->execute();
        $result = $stmt->get_result();
        $operation = $result->fetch_assoc();
        $stmt->close();

        if (!$operation) {
            return ['processed' => 0, 'failed' => 0, 'error' => 'Operation not found'];
        }

        $ticketIds = explode(',', $operation['ticket_ids']);
        $parameters = $operation['parameters'] ? json_decode($operation['parameters'], true) : [];
        $processed = 0;
        $failed = 0;
        $errors = [];

        // Load required models
        require_once dirname(__DIR__) . '/models/TicketModel.php';
        require_once dirname(__DIR__) . '/models/AuditLogModel.php';

        $ticketModel = new TicketModel($this->conn);
        $auditLogModel = new AuditLogModel($this->conn);

        // Batch load all tickets in one query to eliminate N+1 problem
        $ticketsById = $ticketModel->getTicketsByIds($ticketIds);

        // Start transaction for data consistency
        $this->conn->begin_transaction();

        try {
            foreach ($ticketIds as $ticketId) {
            $ticketId = trim($ticketId);
            $success = false;

            try {
                switch ($operation['operation_type']) {
                    case 'bulk_close':
                        // Get current ticket from pre-loaded batch
                        $currentTicket = $ticketsById[$ticketId] ?? null;
                        if ($currentTicket) {
                            $updateResult = $ticketModel->updateTicket([
                                'ticket_id' => $ticketId,
                                'title' => $currentTicket['title'],
                                'description' => $currentTicket['description'],
                                'category' => $currentTicket['category'],
                                'type' => $currentTicket['type'],
                                'status' => 'Closed',
                                'priority' => $currentTicket['priority']
                            ], $operation['performed_by']);
                            $success = $updateResult['success'];

                            if ($success) {
                                $auditLogModel->log($operation['performed_by'], 'update', 'ticket', $ticketId,
                                    ['status' => 'Closed', 'bulk_operation_id' => $operationId]);
                            }
                        }
                        break;

                    case 'bulk_assign':
                        if (isset($parameters['assigned_to'])) {
                            $success = $ticketModel->assignTicket($ticketId, $parameters['assigned_to'], $operation['performed_by']);
                            if ($success) {
                                $auditLogModel->log($operation['performed_by'], 'assign', 'ticket', $ticketId,
                                    ['assigned_to' => $parameters['assigned_to'], 'bulk_operation_id' => $operationId]);
                            }
                        }
                        break;

                    case 'bulk_priority':
                        if (isset($parameters['priority'])) {
                            $currentTicket = $ticketsById[$ticketId] ?? null;
                            if ($currentTicket) {
                                $updateResult = $ticketModel->updateTicket([
                                    'ticket_id' => $ticketId,
                                    'title' => $currentTicket['title'],
                                    'description' => $currentTicket['description'],
                                    'category' => $currentTicket['category'],
                                    'type' => $currentTicket['type'],
                                    'status' => $currentTicket['status'],
                                    'priority' => $parameters['priority']
                                ], $operation['performed_by']);
                                $success = $updateResult['success'];

                                if ($success) {
                                    $auditLogModel->log($operation['performed_by'], 'update', 'ticket', $ticketId,
                                        ['priority' => $parameters['priority'], 'bulk_operation_id' => $operationId]);
                                }
                            }
                        }
                        break;

                    case 'bulk_status':
                        if (isset($parameters['status'])) {
                            $currentTicket = $ticketsById[$ticketId] ?? null;
                            if ($currentTicket) {
                                $updateResult = $ticketModel->updateTicket([
                                    'ticket_id' => $ticketId,
                                    'title' => $currentTicket['title'],
                                    'description' => $currentTicket['description'],
                                    'category' => $currentTicket['category'],
                                    'type' => $currentTicket['type'],
                                    'status' => $parameters['status'],
                                    'priority' => $currentTicket['priority']
                                ], $operation['performed_by']);
                                $success = $updateResult['success'];

                                if ($success) {
                                    $auditLogModel->log($operation['performed_by'], 'update', 'ticket', $ticketId,
                                        ['status' => $parameters['status'], 'bulk_operation_id' => $operationId]);
                                }
                            }
                        }
                        break;
                }

                if ($success) {
                    $processed++;
                } else {
                    $failed++;
                    $errors[] = "Ticket $ticketId: Update failed";
                }
            } catch (Exception $e) {
                $failed++;
                $errors[] = "Ticket $ticketId: " . $e->getMessage();
                error_log("Bulk operation error for ticket $ticketId: " . $e->getMessage());
            }
            }

            // If atomic mode and any failures, rollback everything
            if ($atomic && $failed > 0) {
                $this->conn->rollback();
                error_log("Bulk operation $operationId rolled back due to $failed failures");

                // Update operation status as failed
                $sql = "UPDATE bulk_operations SET status = 'failed', processed_tickets = 0, failed_tickets = ?,
                        completed_at = NOW() WHERE operation_id = ?";
                $stmt = $this->conn->prepare($sql);
                $stmt->bind_param("ii", $failed, $operationId);
                $stmt->execute();
                $stmt->close();

                return [
                    'processed' => 0,
                    'failed' => $failed,
                    'rolled_back' => true,
                    'errors' => $errors
                ];
            }

            // Commit the transaction
            $this->conn->commit();

        } catch (Exception $e) {
            // Rollback on any unexpected error
            $this->conn->rollback();
            error_log("Bulk operation $operationId failed with exception: " . $e->getMessage());

            return [
                'processed' => 0,
                'failed' => count($ticketIds),
                'error' => 'Transaction failed: ' . $e->getMessage(),
                'rolled_back' => true
            ];
        }

        // Update operation status
        $status = $failed > 0 ? 'completed_with_errors' : 'completed';
        $sql = "UPDATE bulk_operations SET status = ?, processed_tickets = ?, failed_tickets = ?,
                completed_at = NOW() WHERE operation_id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("siii", $status, $processed, $failed, $operationId);
        $stmt->execute();
        $stmt->close();

        $result = ['processed' => $processed, 'failed' => $failed];
        if (!empty($errors)) {
            $result['errors'] = $errors;
        }
        return $result;
    }

    /**
     * Get bulk operation by ID
     *
     * @param int $operationId Operation ID
     * @return array|null Operation record or null
     */
    public function getOperationById($operationId) {
        $sql = "SELECT * FROM bulk_operations WHERE operation_id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $operationId);
        $stmt->execute();
        $result = $stmt->get_result();
        $operation = $result->fetch_assoc();
        $stmt->close();
        return $operation;
    }

    /**
     * Get bulk operations performed by a user
     *
     * @param int $userId User ID
     * @param int $limit Result limit
     * @return array Array of operations
     */
    public function getOperationsByUser($userId, $limit = 50) {
        $sql = "SELECT * FROM bulk_operations WHERE performed_by = ?
                ORDER BY created_at DESC LIMIT ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("ii", $userId, $limit);
        $stmt->execute();
        $result = $stmt->get_result();

        $operations = [];
        while ($row = $result->fetch_assoc()) {
            if ($row['parameters']) {
                $row['parameters'] = json_decode($row['parameters'], true);
            }
            $operations[] = $row;
        }

        $stmt->close();
        return $operations;
    }
}