<?php
session_start();
require_once 'config.php';

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

$pdo = getDB();
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$limit = 20;
$offset = ($page - 1) * $limit;
$search = isset($_GET['search']) ? sanitize($_GET['search']) : '';

// Handle Delete (Soft Delete - Deflection)
if (isset($_GET['delete']) && isset($_GET['id'])) {
    $id = intval($_GET['id']);
    $reason = isset($_GET['reason']) ? sanitize($_GET['reason']) : 'No reason provided';
    
    try {
        // Begin transaction
        $pdo->beginTransaction();
        
        // Get certificate details before deletion for logging
        $stmt = $pdo->prepare("SELECT registration_no, name, certificate_no FROM certificates WHERE id = ?");
        $stmt->execute([$id]);
        $cert = $stmt->fetch();
        
        if (!$cert) {
            throw new Exception('Certificate not found');
        }
        
        // Update certificate status to 'deflected' (soft delete)
        $stmt = $pdo->prepare("UPDATE certificates SET certificate_status = 'deflected', updated_at = NOW() WHERE id = ?");
        $stmt->execute([$id]);
        
        // Log the deletion
        $logStmt = $pdo->prepare("INSERT INTO certificate_logs (certificate_id, action, user_id, ip_address, details, timestamp) 
                                  VALUES (?, 'deflected', ?, ?, ?, NOW())");
        $logStmt->execute([
            $id,
            $_SESSION['user_id'],
            $_SERVER['REMOTE_ADDR'],
            "Certificate deflected: {$cert['registration_no']} - {$cert['name']}. Reason: $reason"
        ]);
        
        $pdo->commit();
        
        $_SESSION['message'] = "Certificate Deleted successfully!";
        $_SESSION['message_type'] = 'success';
        
    } catch (Exception $e) {
        $pdo->rollBack();
        $_SESSION['message'] = "Error Deleting certificate: " . $e->getMessage();
        $_SESSION['message_type'] = 'error';
    }
    
    header('Location: view_certificates.php');
    exit();
}

// Handle Permanent Delete (Hard Delete) - Admin only
if (isset($_GET['permanent_delete']) && isset($_GET['id'])) {
    $id = intval($_GET['id']);
    
    // Check if user has admin role
    if ($_SESSION['user_role'] !== 'admin') {
        $_SESSION['message'] = "Only admins can permanently delete certificates.";
        $_SESSION['message_type'] = 'error';
        header('Location: view_certificates.php');
        exit();
    }
    
    try {
        $pdo->beginTransaction();
        
        // Get certificate details before deletion
        $stmt = $pdo->prepare("SELECT registration_no, name, certificate_no FROM certificates WHERE id = ?");
        $stmt->execute([$id]);
        $cert = $stmt->fetch();
        
        if (!$cert) {
            throw new Exception('Certificate not found');
        }
        
        // Log before deleting
        $logStmt = $pdo->prepare("INSERT INTO certificate_logs (certificate_id, action, user_id, ip_address, details, timestamp) 
                                  VALUES (?, 'permanently_deleted', ?, ?, ?, NOW())");
        $logStmt->execute([
            $id,
            $_SESSION['user_id'],
            $_SERVER['REMOTE_ADDR'],
            "Permanently deleted: {$cert['registration_no']} - {$cert['name']}"
        ]);
        
        // Delete the certificate
        $stmt = $pdo->prepare("DELETE FROM certificates WHERE id = ?");
        $stmt->execute([$id]);
        
        $pdo->commit();
        
        $_SESSION['message'] = "Certificate permanently deleted!";
        $_SESSION['message_type'] = 'success';
        
    } catch (Exception $e) {
        $pdo->rollBack();
        $_SESSION['message'] = "Error deleting certificate: " . $e->getMessage();
        $_SESSION['message_type'] = 'error';
    }
    
    header('Location: view_certificates.php');
    exit();
}

// Build query - exclude deflected certificates from main view
$where = "WHERE c.certificate_status != 'deflected'";
$params = [];
if ($search) {
    $where .= " AND (c.registration_no LIKE ? OR c.name LIKE ? OR c.certificate_no LIKE ?)";
    $params = ["%$search%", "%$search%", "%$search%"];
}

// Get total count
$count_sql = "SELECT COUNT(*) as total FROM certificates c $where";
$stmt = $pdo->prepare($count_sql);
$stmt->execute($params);
$total = $stmt->fetch()['total'];
$total_pages = ceil($total / $limit);

// Get certificates
$sql = "SELECT c.*, u.full_name as generated_by_name 
        FROM certificates c 
        LEFT JOIN users u ON c.generated_by = u.id 
        $where 
        ORDER BY c.created_at DESC 
        LIMIT $offset, $limit";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$certificates = $stmt->fetchAll();

// Update status to issued
if (isset($_GET['issue']) && isset($_GET['id'])) {
    $id = intval($_GET['id']);
    try {
        $pdo->beginTransaction();
        
        $stmt = $pdo->prepare("UPDATE certificates SET certificate_status = 'issued', updated_at = NOW() WHERE id = ?");
        $stmt->execute([$id]);
        
        // Log the issuance
        $logStmt = $pdo->prepare("INSERT INTO certificate_logs (certificate_id, action, user_id, ip_address, timestamp) 
                                  VALUES (?, 'issued', ?, ?, NOW())");
        $logStmt->execute([$id, $_SESSION['user_id'], $_SERVER['REMOTE_ADDR']]);
        
        $pdo->commit();
        $_SESSION['message'] = "Certificate issued successfully!";
        $_SESSION['message_type'] = 'success';
    } catch (Exception $e) {
        $pdo->rollBack();
        $_SESSION['message'] = "Error issuing certificate: " . $e->getMessage();
        $_SESSION['message_type'] = 'error';
    }
    header('Location: view_certificates.php');
    exit();
}

// Get message from session
$message = '';
$message_type = '';
if (isset($_SESSION['message'])) {
    $message = $_SESSION['message'];
    $message_type = $_SESSION['message_type'] ?? 'success';
    unset($_SESSION['message']);
    unset($_SESSION['message_type']);
}

$active = 'view_certificates';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>View Certificates - SKUAST-K Portal</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; background: #f5f7fb; }
        .sidebar { width: 250px; background: #2d3748; color: white; height: 100vh; position: fixed; padding: 20px 0; overflow-y: auto; }
        .sidebar h2 { padding: 0 20px 20px; border-bottom: 1px solid #4a5568; font-size: 18px; }
        .sidebar .user-info { padding: 20px; border-bottom: 1px solid #4a5568; margin-bottom: 20px; }
        .sidebar .user-info .name { font-weight: bold; font-size: 16px; }
        .sidebar .user-info .email { font-size: 12px; color: #a0aec0; }
        .sidebar a { display: block; padding: 12px 20px; color: #a0aec0; text-decoration: none; transition: all 0.3s; }
        .sidebar a:hover, .sidebar a.active { background: #4a5568; color: white; }
        .sidebar a i { width: 20px; margin-right: 10px; }
        .main-content { margin-left: 250px; padding: 30px; }
        .header { background: white; padding: 20px 30px; border-radius: 10px; margin-bottom: 30px; box-shadow: 0 2px 4px rgba(0,0,0,0.05); display: flex; justify-content: space-between; align-items: center; flex-wrap: wrap; gap: 10px; }
        .header h1 { font-size: 24px; color: #2d3748; }
        .search-box { display: flex; gap: 10px; margin-bottom: 20px; flex-wrap: wrap; }
        .search-box form { display: flex; gap: 10px; width: 100%; flex-wrap: wrap; }
        .search-box input { flex: 1; padding: 10px; border: 1px solid #e2e8f0; border-radius: 5px; font-size: 14px; min-width: 200px; }
        .search-box button { padding: 10px 20px; background: #667eea; color: white; border: none; border-radius: 5px; cursor: pointer; }
        .search-box button:hover { background: #5a67d8; }
        .table-container { background: white; border-radius: 10px; padding: 20px; box-shadow: 0 2px 4px rgba(0,0,0,0.05); overflow-x: auto; }
        table { width: 100%; border-collapse: collapse; }
        th { text-align: left; padding: 12px; background: #f7fafc; color: #4a5568; font-weight: 600; font-size: 14px; white-space: nowrap; }
        td { padding: 12px; border-bottom: 1px solid #e2e8f0; font-size: 14px; vertical-align: middle; }
        td img.thumb { width: 50px; height: 50px; object-fit: cover; border-radius: 5px; }
        .status-badge { padding: 4px 12px; border-radius: 20px; font-size: 12px; font-weight: 600; display: inline-block; }
        .status-draft { background: #fefcbf; color: #975a16; }
        .status-generated { background: #bee3f8; color: #2a69ac; }
        .status-issued { background: #c6f6d5; color: #22543d; }
        .status-deflected { background: #fed7d7; color: #9b2c2c; }
        .btn { padding: 5px 12px; border: none; border-radius: 3px; cursor: pointer; font-size: 12px; transition: all 0.3s; text-decoration: none; display: inline-block; margin: 2px; }
        .btn-primary { background: #667eea; color: white; }
        .btn-primary:hover { background: #5a67d8; }
        .btn-success { background: #38a169; color: white; }
        .btn-success:hover { background: #2f855a; }
        .btn-secondary { background: #718096; color: white; }
        .btn-secondary:hover { background: #4a5568; }
        .btn-danger { background: #e53e3e; color: white; }
        .btn-danger:hover { background: #c53030; }
        .btn-warning { background: #dd6b20; color: white; }
        .btn-warning:hover { background: #c05621; }
        .btn-sm { padding: 4px 8px; font-size: 11px; }
        .pagination { display: flex; justify-content: center; gap: 5px; margin-top: 20px; flex-wrap: wrap; }
        .pagination a, .pagination span { padding: 8px 15px; border: 1px solid #e2e8f0; border-radius: 5px; text-decoration: none; color: #4a5568; }
        .pagination .active { background: #667eea; color: white; border-color: #667eea; }
        .pagination a:hover { background: #f7fafc; }
        .message { padding: 12px 20px; border-radius: 5px; margin-bottom: 20px; }
        .message.success { background: #c6f6d5; color: #22543d; border-left: 4px solid #38a169; }
        .message.error { background: #fed7d7; color: #9b2c2c; border-left: 4px solid #e53e3e; }
        .action-buttons { display: flex; flex-wrap: wrap; gap: 4px; }
        .modal { display: none; position: fixed; z-index: 1000; left: 0; top: 0; width: 100%; height: 100%; background: rgba(0,0,0,0.5); }
        .modal-content { background: white; margin: 10% auto; padding: 30px; border-radius: 10px; max-width: 500px; position: relative; }
        .modal-content h2 { margin-bottom: 20px; }
        .modal-content textarea { width: 100%; padding: 10px; border: 1px solid #e2e8f0; border-radius: 5px; min-height: 100px; margin: 10px 0; }
        .modal-content .btn { padding: 10px 20px; }
        .modal-actions { display: flex; gap: 10px; justify-content: flex-end; margin-top: 15px; }
        .status-badge.status-deflected { background: #fed7d7; color: #9b2c2c; }
        
        @media (max-width: 768px) {
            .sidebar { width: 60px; }
            .sidebar h2, .sidebar .user-info, .sidebar a span { display: none; }
            .sidebar a { padding: 12px; text-align: center; }
            .sidebar a i { margin-right: 0; }
            .main-content { margin-left: 60px; padding: 15px; }
            .header { flex-direction: column; text-align: center; }
            .action-buttons { justify-content: center; }
        }
    </style>
</head>
<body>
    <!-- Modal for Delete Reason -->
    <div id="deleteModal" class="modal">
        <div class="modal-content">
            <h2><i class="fas fa-exclamation-triangle" style="color: #dd6b20;"></i> Delete Certificate</h2>
            <p>Please provide a reason for deleting this certificate:</p>
            <form id="deleteForm" method="GET" action="">
                <input type="hidden" name="delete" value="1">
                <input type="hidden" name="id" id="deleteId">
                <textarea name="reason" placeholder="Enter reason for deflection..." required></textarea>
                <div class="modal-actions">
                    <button type="button" class="btn btn-secondary" onclick="closeModal()">Cancel</button>
                    <button type="submit" class="btn btn-warning">Confirm Deletion</button>
                </div>
            </form>
        </div>
    </div>

    <div class="sidebar">
        <h2>🎓 SKUAST-K Portal</h2>
        <div class="user-info">
            <div class="name"><?php echo htmlspecialchars($_SESSION['user_name']); ?></div>
            <div class="email"><?php echo htmlspecialchars($_SESSION['user_email']); ?></div>
        </div>
        <a href="dashboard.php"><i class="fas fa-tachometer-alt"></i> Dashboard</a>
        <a href="add_certificate.php"><i class="fas fa-plus-circle"></i> Add Certificate</a>
        <a href="bulk_upload.php"><i class="fas fa-upload"></i> Bulk Upload</a>
        <a href="map_photo.php"><i class="fas fa-image"></i> Map Photo</a>
        <a href="generate_certificate.php"><i class="fas fa-file-pdf"></i> Generate Certificate</a>
        <a href="view_certificates.php" class="active"><i class="fas fa-list"></i> View All</a>
        <a href="logout.php" style="margin-top: 30px; border-top: 1px solid #4a5568; padding-top: 20px;"><i class="fas fa-sign-out-alt"></i> Logout</a>
    </div>
    
    <div class="main-content">
        <div class="header">
            <h1>All Certificates</h1>
            <div>
                <a href="view_deflected.php" class="btn btn-secondary"><i class="fas fa-trash-alt"></i> Deleted</a>
                <a href="view_logs.php" class="btn btn-secondary"><i class="fas fa-history"></i> Logs</a>
                <a href="dashboard.php" class="btn btn-secondary">Back</a>
            </div>
        </div>
        
        <?php if ($message): ?>
            <div class="message <?php echo $message_type; ?>">
                <?php echo htmlspecialchars($message); ?>
            </div>
        <?php endif; ?>
        
        <div class="search-box">
            <form method="GET" action="">
                <input type="text" name="search" placeholder="Search by Registration No, Name, or Certificate No" value="<?php echo htmlspecialchars($search); ?>">
                <button type="submit"><i class="fas fa-search"></i> Search</button>
                <?php if ($search): ?>
                    <a href="view_certificates.php" class="btn btn-secondary" style="padding: 10px 20px;">Clear</a>
                <?php endif; ?>
            </form>
        </div>
        
        <div class="table-container">
            <table>
                <thead>
                    <tr>
                        <th>Photo</th>
                        <th>Reg No</th>
                        <th>Name</th>
                        <th>Degree</th>
                        <th>GPA</th>
                        <th>Certificate No</th>
                        <th>Status</th>
                        <th>Generated By</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (count($certificates) > 0): ?>
                        <?php foreach($certificates as $cert): ?>
                        <tr>
                            <td>
                                <?php if ($cert['photo']): ?>
                                    <img src="data:image/jpeg;base64,<?php echo base64_encode($cert['photo']); ?>" alt="Photo" class="thumb">
                                <?php else: ?>
                                    <span style="color: #999;">No photo</span>
                                <?php endif; ?>
                            </td>
                            <td><?php echo htmlspecialchars($cert['registration_no']); ?></td>
                            <td><?php echo htmlspecialchars($cert['name']); ?></td>
                            <td><?php echo htmlspecialchars($cert['degree']); ?></td>
                            <td><?php echo $cert['grade_point_average']; ?></td>
                            <td><?php echo htmlspecialchars($cert['certificate_no']); ?></td>
                            <td>
                                <span class="status-badge status-<?php echo $cert['certificate_status']; ?>">
                                    <?php echo ucfirst($cert['certificate_status']); ?>
                                </span>
                            </td>
                            <td><?php echo htmlspecialchars($cert['generated_by_name'] ?? 'N/A'); ?></td>
                            <td>
                                <div class="action-buttons">
                                    <!-- View Button -->
                                    <a href="generate_certificate.php?reg=<?php echo urlencode($cert['registration_no']); ?>" class="btn btn-primary btn-sm">
                                        <i class="fas fa-eye"></i> View
                                    </a>
                                    
                                    <!-- Generate Button (only for draft status) -->
                                    <?php if ($cert['certificate_status'] == 'draft'): ?>
                                        <a href="generate_certificate.php?reg=<?php echo urlencode($cert['registration_no']); ?>" class="btn btn-success btn-sm">
                                            <i class="fas fa-sync-alt"></i> Generate
                                        </a>
                                    <?php endif; ?>
                                    
                                    <!-- Issue Button (only for generated status) -->
                                    <?php if ($cert['certificate_status'] == 'generated'): ?>
                                        <a href="?issue=1&id=<?php echo $cert['id']; ?>" class="btn btn-success btn-sm" onclick="return confirm('Mark this certificate as issued?')">
                                            <i class="fas fa-check"></i> Issue
                                        </a>
                                    <?php endif; ?>
                                    
                                    <!-- Deflect/Delete Button -->
                                    <button class="btn btn-warning btn-sm" onclick="openDeleteModal(<?php echo $cert['id']; ?>)">
                                        <i class="fas fa-trash-alt"></i> Delete
                                    </button>
                                    
                                    <!-- Permanent Delete (Admin Only) -->
                                    <?php if ($_SESSION['user_role'] == 'admin'): ?>
                                        <a href="?permanent_delete=1&id=<?php echo $cert['id']; ?>" class="btn btn-danger btn-sm" 
                                           onclick="return confirm('⚠️ PERMANENT DELETE: This action cannot be undone! Are you sure?')">
                                            <i class="fas fa-times-circle"></i> Delete
                                        </a>
                                    <?php endif; ?>
                                </div>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="9" style="text-align: center; padding: 30px; color: #718096;">
                                No certificates found.
                            </td>
                        </tr>
                    <?php endif; ?>
                </tbody>
            </table>
            
            <?php if ($total_pages > 1): ?>
            <div class="pagination">
                <?php for($i = 1; $i <= $total_pages; $i++): ?>
                    <?php if ($i == $page): ?>
                        <span class="active"><?php echo $i; ?></span>
                    <?php else: ?>
                        <a href="?page=<?php echo $i; ?><?php echo $search ? '&search=' . urlencode($search) : ''; ?>"><?php echo $i; ?></a>
                    <?php endif; ?>
                <?php endfor; ?>
            </div>
            <?php endif; ?>
        </div>
    </div>

    <script>
        function openDeleteModal(id) {
            document.getElementById('deleteId').value = id;
            document.getElementById('deleteModal').style.display = 'block';
        }
        
        function closeModal() {
            document.getElementById('deleteModal').style.display = 'none';
        }
        
        // Close modal when clicking outside
        window.onclick = function(event) {
            const modal = document.getElementById('deleteModal');
            if (event.target == modal) {
                closeModal();
            }
        }
    </script>
</body>
</html>