tinker_tickets/models/TicketModel.php

<?php
class TicketModel {
    private $conn;
    
    public function __construct($conn) {
        $this->conn = $conn;
    }
    
    public function getTicketById($id) {
        $sql = "SELECT * FROM tickets WHERE ticket_id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result->num_rows === 0) {
            return null;
        }
        
        return $result->fetch_assoc();
    }
    
    public function getTicketComments($ticketId) {
        $sql = "SELECT * FROM ticket_comments WHERE ticket_id = ? ORDER BY created_at DESC";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $ticketId);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $comments = [];
        while ($row = $result->fetch_assoc()) {
            $comments[] = $row;
        }
        
        return $comments;
    }
    
    public function getAllTickets($page = 1, $limit = 15, $status = 'Open', $sortColumn = 'ticket_id', $sortDirection = 'desc', $category = null, $type = null, $search = null) {
        // Calculate offset
        $offset = ($page - 1) * $limit;
        
        // Build WHERE clause
        $whereConditions = [];
        $params = [];
        $paramTypes = '';
        
        // Status filtering
        if ($status) {
            $statuses = explode(',', $status);
            $placeholders = str_repeat('?,', count($statuses) - 1) . '?';
            $whereConditions[] = "status IN ($placeholders)";
            $params = array_merge($params, $statuses);
            $paramTypes .= str_repeat('s', count($statuses));
        }
        
        // Category filtering
        if ($category) {
            $categories = explode(',', $category);
            $placeholders = str_repeat('?,', count($categories) - 1) . '?';
            $whereConditions[] = "category IN ($placeholders)";
            $params = array_merge($params, $categories);
            $paramTypes .= str_repeat('s', count($categories));
        }
        
        // Type filtering
        if ($type) {
            $types = explode(',', $type);
            $placeholders = str_repeat('?,', count($types) - 1) . '?';
            $whereConditions[] = "type IN ($placeholders)";
            $params = array_merge($params, $types);
            $paramTypes .= str_repeat('s', count($types));
        }
        
        // Search Functionality
        if ($search && !empty($search)) {
            $whereConditions[] = "(title LIKE ? OR description LIKE ? OR ticket_id LIKE ? OR category LIKE ? OR type LIKE ?)";
            $searchTerm = "%$search%";
            $params = array_merge($params, [$searchTerm, $searchTerm, $searchTerm, $searchTerm, $searchTerm]);
            $paramTypes .= 'sssss';
        }
        
        $whereClause = '';
        if (!empty($whereConditions)) {
            $whereClause = 'WHERE ' . implode(' AND ', $whereConditions);
        }
        
        // Validate sort column to prevent SQL injection
        $allowedColumns = ['ticket_id', 'title', 'status', 'priority', 'category', 'type', 'created_at', 'updated_at'];
        if (!in_array($sortColumn, $allowedColumns)) {
            $sortColumn = 'ticket_id';
        }
        
        // Validate sort direction
        $sortDirection = strtolower($sortDirection) === 'asc' ? 'ASC' : 'DESC';
        
        // Get total count for pagination
        $countSql = "SELECT COUNT(*) as total FROM tickets $whereClause";
        $countStmt = $this->conn->prepare($countSql);
        
        if (!empty($params)) {
            $countStmt->bind_param($paramTypes, ...$params);
        }
        
        $countStmt->execute();
        $totalResult = $countStmt->get_result();
        $totalTickets = $totalResult->fetch_assoc()['total'];
        
        // Get tickets with pagination
        $sql = "SELECT * FROM tickets $whereClause ORDER BY $sortColumn $sortDirection LIMIT ? OFFSET ?";
        $stmt = $this->conn->prepare($sql);
        
        // Add limit and offset parameters
        $params[] = $limit;
        $params[] = $offset;
        $paramTypes .= 'ii';
        
        if (!empty($params)) {
            $stmt->bind_param($paramTypes, ...$params);
        }
        
        $stmt->execute();
        $result = $stmt->get_result();
        
        $tickets = [];
        while ($row = $result->fetch_assoc()) {
            $tickets[] = $row;
        }
        
        return [
            'tickets' => $tickets,
            'total' => $totalTickets,
            'pages' => ceil($totalTickets / $limit),
            'current_page' => $page
        ];
    }
    
    public function updateTicket($ticketData) {
        // Debug function
        $debug = function($message, $data = null) {
            $log_message = date('Y-m-d H:i:s') . " - [Model] " . $message;
            if ($data !== null) {
                $log_message .= ": " . (is_string($data) ? $data : json_encode($data));
            }
            $log_message .= "\n";
            file_put_contents('/tmp/api_debug.log', $log_message, FILE_APPEND);
        };

        $debug("updateTicket called with data", $ticketData);
        
        $sql = "UPDATE tickets SET 
                title = ?, 
                priority = ?, 
                status = ?, 
                description = ?,
                category = ?,
                type = ?,
                updated_at = NOW() 
                WHERE ticket_id = ?";
        
        $debug("SQL query", $sql);
        
        try {
            $stmt = $this->conn->prepare($sql);
            if (!$stmt) {
                $debug("Prepare statement failed", $this->conn->error);
                return false;
            }
            
            $debug("Binding parameters");
            $stmt->bind_param(
                "sissssi",
                $ticketData['title'],
                $ticketData['priority'],
                $ticketData['status'],
                $ticketData['description'],
                $ticketData['category'],
                $ticketData['type'],
                $ticketData['ticket_id']
            );
            
            $debug("Executing statement");
            $result = $stmt->execute();
            
            if (!$result) {
                $debug("Execute failed", $stmt->error);
                return false;
            }
            
            $debug("Update successful");
            return true;
        } catch (Exception $e) {
            $debug("Exception", $e->getMessage());
            $debug("Stack trace", $e->getTraceAsString());
            throw $e;
        }
    }
    
    public function createTicket($ticketData) {
        // Generate ticket ID (9-digit format with leading zeros)
        $ticket_id = sprintf('%09d', mt_rand(1, 999999999));
        
        $sql = "INSERT INTO tickets (ticket_id, title, description, status, priority, category, type) 
                VALUES (?, ?, ?, ?, ?, ?, ?)";
                
        $stmt = $this->conn->prepare($sql);
        
        // Set default values if not provided
        $status = $ticketData['status'] ?? 'Open';
        $priority = $ticketData['priority'] ?? '4';
        $category = $ticketData['category'] ?? 'General';
        $type = $ticketData['type'] ?? 'Issue';
        
        $stmt->bind_param(
            "sssssss",
            $ticket_id,
            $ticketData['title'],
            $ticketData['description'],
            $status,
            $priority,
            $category,
            $type
        );
        
        if ($stmt->execute()) {
            return [
                'success' => true,
                'ticket_id' => $ticket_id
            ];
        } else {
            return [
                'success' => false,
                'error' => $this->conn->error
            ];
        }
    }
    
    public function addComment($ticketId, $commentData) {
        $sql = "INSERT INTO ticket_comments (ticket_id, user_name, comment_text, markdown_enabled) 
                VALUES (?, ?, ?, ?)";
                
        $stmt = $this->conn->prepare($sql);
        
        // Set default username
        $username = $commentData['user_name'] ?? 'User';
        $markdownEnabled = $commentData['markdown_enabled'] ? 1 : 0;
        
        $stmt->bind_param(
            "sssi",
            $ticketId,
            $username,
            $commentData['comment_text'],
            $markdownEnabled
        );
        
        if ($stmt->execute()) {
            return [
                'success' => true,
                'user_name' => $username,
                'created_at' => date('M d, Y H:i')
            ];
        } else {
            return [
                'success' => false,
                'error' => $this->conn->error
            ];
        }
    }
}