<?php
/**
 * Ticket Functions
 * Contains all functions related to ticket management
 */

require_once 'email_functions.php';

// Function to get all ticket types
function getTicketTypes($pdo) {
    $stmt = $pdo->query("SELECT * FROM ticket_types ORDER BY name");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to get ticket type by ID
function getTicketTypeById($pdo, $id) {
    $stmt = $pdo->prepare("SELECT * FROM ticket_types WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Function to create a new ticket
function createTicket($pdo, $user_id, $order_id, $type_id, $subject, $description, $priority = 'Medium') {
    try {
        $pdo->beginTransaction();
        
        $stmt = $pdo->prepare("INSERT INTO tickets (user_id, order_id, type_id, subject, description, priority) VALUES (?, ?, ?, ?, ?, ?)");
        $stmt->execute([$user_id, $order_id, $type_id, $subject, $description, $priority]);
        $ticket_id = $pdo->lastInsertId();
        
        // Send notification emails
        notifyTicketCreated($pdo, $ticket_id);
        notifyNewTicketToAgents($pdo, $ticket_id);
        
        $pdo->commit();
        return $ticket_id;
    } catch (Exception $e) {
        $pdo->rollback();
        return false;
    }
}

// Function to get ticket by ID
function getTicketById($pdo, $id) {
    $stmt = $pdo->prepare("
        SELECT t.*, tt.name as type_name, u.username as customer_name, 
               agent.username as agent_name, o.total_amount as order_total
        FROM tickets t
        LEFT JOIN ticket_types tt ON t.type_id = tt.id
        LEFT JOIN users u ON t.user_id = u.id
        LEFT JOIN users agent ON t.assigned_to = agent.id
        LEFT JOIN orders o ON t.order_id = o.id
        WHERE t.id = ?
    ");
    $stmt->execute([$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Function to get tickets by user ID (customer)
function getTicketsByUserId($pdo, $user_id) {
    $stmt = $pdo->prepare("
        SELECT t.*, tt.name as type_name
        FROM tickets t
        LEFT JOIN ticket_types tt ON t.type_id = tt.id
        WHERE t.user_id = ?
        ORDER BY t.created_at DESC
    ");
    $stmt->execute([$user_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to get all tickets (support agents)
function getAllTickets($pdo, $status_filter = null, $type_filter = null) {
    $sql = "
        SELECT t.*, tt.name as type_name, u.username as customer_name
        FROM tickets t
        LEFT JOIN ticket_types tt ON t.type_id = tt.id
        LEFT JOIN users u ON t.user_id = u.id
    ";
    
    $params = [];
    $conditions = [];
    
    if ($status_filter && $status_filter !== 'All') {
        $conditions[] = "t.status = ?";
        $params[] = $status_filter;
    }
    
    if ($type_filter && $type_filter !== 'All') {
        $conditions[] = "t.type_id = ?";
        $params[] = $type_filter;
    }
    
    if (!empty($conditions)) {
        $sql .= " WHERE " . implode(" AND ", $conditions);
    }
    
    $sql .= " ORDER BY t.updated_at DESC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to get tickets assigned to a support agent
function getAssignedTickets($pdo, $agent_id, $status_filter = null) {
    $sql = "
        SELECT t.*, tt.name as type_name, u.username as customer_name
        FROM tickets t
        LEFT JOIN ticket_types tt ON t.type_id = tt.id
        LEFT JOIN users u ON t.user_id = u.id
        WHERE t.assigned_to = ?
    ";
    
    $params = [$agent_id];
    
    if ($status_filter && $status_filter !== 'All') {
        $sql .= " AND t.status = ?";
        $params[] = $status_filter;
    }
    
    $sql .= " ORDER BY t.updated_at DESC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to update ticket status
function updateTicketStatus($pdo, $ticket_id, $status) {
    // Get current status
    $stmt = $pdo->prepare("SELECT status FROM tickets WHERE id = ?");
    $stmt->execute([$ticket_id]);
    $current_ticket = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$current_ticket) {
        return false;
    }
    
    $old_status = $current_ticket['status'];
    
    $stmt = $pdo->prepare("UPDATE tickets SET status = ? WHERE id = ?");
    $result = $stmt->execute([$status, $ticket_id]);
    
    if ($result) {
        // Send notification if status changed
        notifyTicketStatusUpdated($pdo, $ticket_id, $old_status, $status);
    }
    
    return $result;
}

// Function to assign ticket to agent
function assignTicketToAgent($pdo, $ticket_id, $agent_id) {
    $stmt = $pdo->prepare("UPDATE tickets SET assigned_to = ?, status = 'In Review' WHERE id = ?");
    return $stmt->execute([$agent_id, $ticket_id]);
}

// Function to add a reply to a ticket
function addTicketReply($pdo, $ticket_id, $author_id, $message, $is_internal = 0) {
    $pdo->beginTransaction();
    
    try {
        $stmt = $pdo->prepare("INSERT INTO ticket_replies (ticket_id, author_id, message, is_internal) VALUES (?, ?, ?, ?)");
        $stmt->execute([$ticket_id, $author_id, $message, $is_internal]);
        
        // If not internal, update ticket status to In Review
        if ($is_internal == 0) {
            $stmt = $pdo->prepare("UPDATE tickets SET status = 'In Review' WHERE id = ?");
            $stmt->execute([$ticket_id]);
        }
        
        $pdo->commit();
        
        // Send notification for new public replies
        if ($is_internal == 0) {
            notifyNewReply($pdo, $ticket_id, $author_id);
        }
        
        return true;
    } catch (Exception $e) {
        $pdo->rollback();
        return false;
    }
}

// Function to get ticket replies
function getTicketReplies($pdo, $ticket_id, $include_internal = false) {
    $sql = "
        SELECT tr.*, u.username as author_name, u.role as author_role
        FROM ticket_replies tr
        LEFT JOIN users u ON tr.author_id = u.id
        WHERE tr.ticket_id = ?
    ";
    
    $params = [$ticket_id];
    
    if (!$include_internal) {
        $sql .= " AND (tr.is_internal = 0 OR tr.is_internal IS NULL)";
    }
    
    $sql .= " ORDER BY tr.created_at ASC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to get internal notes for a ticket (only for support agents)
function getInternalNotes($pdo, $ticket_id) {
    $stmt = $pdo->prepare("
        SELECT tr.*, u.username as author_name
        FROM ticket_replies tr
        LEFT JOIN users u ON tr.author_id = u.id
        WHERE tr.ticket_id = ? AND tr.is_internal = 1
        ORDER BY tr.created_at ASC
    ");
    $stmt->execute([$ticket_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to get ticket attachments
function getTicketAttachments($pdo, $ticket_id) {
    $stmt = $pdo->prepare("SELECT * FROM ticket_attachments WHERE ticket_id = ? ORDER BY created_at ASC");
    $stmt->execute([$ticket_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to add attachment to ticket
function addTicketAttachment($pdo, $ticket_id, $file_path, $file_name, $file_size) {
    $stmt = $pdo->prepare("INSERT INTO ticket_attachments (ticket_id, file_path, file_name, file_size) VALUES (?, ?, ?, ?)");
    return $stmt->execute([$ticket_id, $file_path, $file_name, $file_size]);
}

// Function to get user orders for ticket creation
function getUserOrders($pdo, $user_id) {
    $stmt = $pdo->prepare("SELECT * FROM orders WHERE user_id = ? ORDER BY order_date DESC");
    $stmt->execute([$user_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to get ticket statistics for admin dashboard
function getTicketStats($pdo) {
    $stats = [];
    
    // Total tickets
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM tickets");
    $stats['total_tickets'] = $stmt->fetch(PDO::FETCH_ASSOC)['count'];
    
    // Tickets by status
    $stmt = $pdo->query("SELECT status, COUNT(*) as count FROM tickets GROUP BY status");
    $stats['by_status'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Tickets by type
    $stmt = $pdo->query("
        SELECT tt.name, COUNT(t.id) as count 
        FROM ticket_types tt 
        LEFT JOIN tickets t ON tt.id = t.type_id 
        GROUP BY tt.id, tt.name
    ");
    $stats['by_type'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Average resolution time (in hours)
    $stmt = $pdo->query("
        SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, updated_at)) as avg_resolution_time
        FROM tickets 
        WHERE status = 'Resolved' OR status = 'Closed'
    ");
    $stats['avg_resolution_time'] = round($stmt->fetch(PDO::FETCH_ASSOC)['avg_resolution_time'], 2);
    
    return $stats;
}

// Function to get recent tickets for dashboard
function getRecentTickets($pdo, $limit = 10) {
    $stmt = $pdo->prepare("
        SELECT t.*, tt.name as type_name, u.username as customer_name
        FROM tickets t
        LEFT JOIN ticket_types tt ON t.type_id = tt.id
        LEFT JOIN users u ON t.user_id = u.id
        ORDER BY t.created_at DESC
        LIMIT " . (int)$limit);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to update ticket satisfaction rating
function updateSatisfactionRating($pdo, $ticket_id, $rating) {
    $stmt = $pdo->prepare("UPDATE tickets SET satisfaction_rating = ?, status = 'Closed' WHERE id = ?");
    return $stmt->execute([$rating, $ticket_id]);
}

// Function to check if user can view ticket (owns it or is admin/agent)
function canViewTicket($pdo, $ticket_id, $user_id, $user_role) {
    if ($user_role === 'admin') {
        return true;
    }
    
    $stmt = $pdo->prepare("SELECT user_id, assigned_to FROM tickets WHERE id = ?");
    $stmt->execute([$ticket_id]);
    $ticket = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$ticket) {
        return false;
    }
    
    // Owner can view
    if ($ticket['user_id'] == $user_id) {
        return true;
    }
    
    // Assigned agent can view
    if ($user_role === 'agent' && $ticket['assigned_to'] == $user_id) {
        return true;
    }
    
    return false;
}

// Function to validate file upload
function validateAttachment($file) {
    $allowed_types = ['jpg', 'jpeg', 'png', 'gif', 'pdf', 'doc', 'docx'];
    $max_size = 5 * 1024 * 1024; // 5MB
    
    // Check if file was uploaded without errors
    if ($file['error'] !== UPLOAD_ERR_OK) {
        return false;
    }
    
    // Check file size
    if ($file['size'] > $max_size) {
        return false;
    }
    
    // Check file extension
    $extension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    if (!in_array($extension, $allowed_types)) {
        return false;
    }
    
    return true;
}

// Function to sanitize ticket input
function sanitizeTicketInput($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}
?>