<?php
session_start();
include 'layouts/session.php';
include 'layouts/main.php';
include 'layouts/dbconn.php';

if (!isset($_SESSION['user_id'])) {
   header('Location: login.php');
   exit();
}

// Build query based on user role
$sql = "SELECT t.*, u.full_name, 
        COALESCE(c.comment_count, 0) as comment_count,
        COALESCE(a.attachment_count, 0) as attachment_count
        FROM support_tickets t
        JOIN users u ON t.user_id = u.user_id
        LEFT JOIN (
            SELECT ticket_id, COUNT(*) as comment_count 
            FROM ticket_comments 
            GROUP BY ticket_id
        ) c ON t.ticket_id = c.ticket_id
        LEFT JOIN (
            SELECT ticket_id, COUNT(*) as attachment_count 
            FROM ticket_attachments 
            GROUP BY ticket_id
        ) a ON t.ticket_id = a.ticket_id";

if ($_SESSION['role_id'] !== 1) {
    $sql .= " WHERE t.user_id = ?";
}
$sql .= " ORDER BY t.created_at DESC";

$stmt = $conn->prepare($sql);
if ($_SESSION['role_id'] !== 1) {
    $stmt->bind_param('i', $_SESSION['user_id']);
}
$stmt->execute();
$tickets = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
?>

<!DOCTYPE html>
<html>
<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Support Tickets')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="https://cdn.datatables.net/1.13.7/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/buttons/2.4.2/css/buttons.bootstrap5.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/responsive/2.5.0/css/responsive.bootstrap5.min.css" rel="stylesheet">

</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <?php includeFileWithVariables('layouts/page-title.php', array('pagetitle' => 'Support', 'title' => 'Tickets')); ?>

                    <div class="row">
                        <div class="col-lg-12">
                            <div class="card">
                                <div class="card-header">
                                    <div class="d-flex align-items-center">
                                        <h4 class="card-title mb-0 flex-grow-1">Support Tickets</h4>
                                        <a href="submit_ticket.php" class="btn btn-primary">
                                            <i class="ri-add-line align-bottom me-1"></i> New Ticket
                                        </a>
                                    </div>
                                </div>

                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table id="tickets-table" class="table table-nowrap align-middle display">
                                            <thead>
                                                <tr>
                                                    <th>Ticket ID</th>
                                                    <th>Subject</th>
                                                    <th>Category</th>
                                                    <th>Created By</th>
                                                    <th>Priority</th>
                                                    <th>Status</th>
                                                    <th>Created</th>
                                                    <th>Actions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach ($tickets as $ticket): ?>
                                                <tr>
                                                    <td>#<?php echo $ticket['ticket_id']; ?></td>
                                                    <td>
                                                        <?php echo htmlspecialchars($ticket['subject']); ?>
                                                        <div class="text-muted small">
                                                            <i class="ri-message-3-line"></i> <?php echo $ticket['comment_count']; ?> 
                                                            <i class="ri-attachment-line ms-2"></i> <?php echo $ticket['attachment_count']; ?>
                                                        </div>
                                                    </td>
                                                    <td><?php echo ucfirst($ticket['category']); ?></td>
                                                    <td><?php echo htmlspecialchars($ticket['full_name']); ?></td>
                                                    <td>
                                                        <span class="badge bg-<?php 
                                                            echo $ticket['priority'] === 'urgent' ? 'danger' : 
                                                                ($ticket['priority'] === 'high' ? 'warning' : 
                                                                ($ticket['priority'] === 'medium' ? 'primary' : 'success')); 
                                                        ?>"><?php echo ucfirst($ticket['priority']); ?></span>
                                                    </td>
                                                    <td>
                                                        <span class="badge bg-<?php 
                                                            echo $ticket['status'] === 'OPEN' ? 'success' : 
                                                                ($ticket['status'] === 'CLOSED' ? 'dark' : 'secondary'); 
                                                        ?>"><?php echo $ticket['status']; ?></span>
                                                    </td>
                                                    <td><?php echo date('d M Y H:i', strtotime($ticket['created_at'])); ?></td>
                                                    <td>
                                                        <a href="view_ticket.php?id=<?php echo $ticket['ticket_id']; ?>" 
                                                           class="btn btn-sm btn-soft-primary">
                                                            <i class="ri-eye-line"></i>
                                                        </a>
                                                    </td>
                                                </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/js/app.js"></script>
    <script src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.7/js/dataTables.bootstrap5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.bootstrap5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/dataTables.responsive.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/responsive.bootstrap5.min.js"></script>

    <script>
$(document).ready(function() {
   $('#tickets-table').DataTable({
       responsive: true,
       dom: 'Bfrtip',
       buttons: [
           'copyHtml5',
           'excelHtml5',
           'csvHtml5',
           'pdfHtml5'
       ],
       order: [[6, 'desc']], // Order by created date descending
       pageLength: 25,
       language: {
           search: "Search:",
           lengthMenu: "Show _MENU_ entries per page",
           info: "Showing _START_ to _END_ of _TOTAL_ tickets"
       }
   });
});
</script>
</body>
</html>