<?php
// Start session
require_once 'session_config_emp.php';

// Check if employee is logged in
if (!isset($_SESSION['employee_id']) || !isset($_SESSION['cnic'])) {
    header('Location: login_emp.php');
    exit();
}

include 'connect.php';

// Get employee data from session
$employee_id = $_SESSION['employee_id'];
$cnic = $_SESSION['cnic'];

// First, get the personal_no from registrations_emp
$sql_reg = "SELECT emp_numb FROM registrations_emp WHERE id = ? AND cnic = ?";
$stmt_reg = $conn->prepare($sql_reg);
$stmt_reg->bind_param("is", $employee_id, $cnic);
$stmt_reg->execute();
$result_reg = $stmt_reg->get_result();

if ($result_reg->num_rows > 0) {
    $reg_data = $result_reg->fetch_assoc();
    $personal_no = $reg_data['emp_numb'];
    
    // Get employee_id from employees table
    $emp_sql = "SELECT employee_id FROM employees WHERE personal_no = ? LIMIT 1";
    $stmt_emp = $conn->prepare($emp_sql);
    $stmt_emp->bind_param("s", $personal_no);
    $stmt_emp->execute();
    $emp_result = $stmt_emp->get_result();
    
    if ($emp_result->num_rows > 0) {
        $employee_data = $emp_result->fetch_assoc();
        $db_employee_id = $employee_data['employee_id'];
        
        // CORRECTED SQL QUERY - Fetches board/university names properly
        $academic_sql = "SELECT aq.*,
                        -- Resolve institution name from boards table
                        CASE
                            WHEN aq.university_board LIKE 'Board%' AND SUBSTRING(aq.university_board, 6) REGEXP '^[0-9]+$' THEN
                                COALESCE((SELECT b_name FROM boards WHERE b_id = CAST(SUBSTRING(aq.university_board, 6) AS UNSIGNED)), aq.university_board)
                            -- Resolve from universities table  
                            WHEN aq.university_board LIKE 'Uni%' AND SUBSTRING(aq.university_board, 4) REGEXP '^[0-9]+$' THEN
                                COALESCE((SELECT uni_name FROM universities WHERE uni_id = CAST(SUBSTRING(aq.university_board, 4) AS UNSIGNED)), aq.university_board)
                            -- If it's a custom entry, use it directly
                            ELSE aq.university_board
                        END as institution_name,
                        -- Determine institution type
                        CASE
                            WHEN aq.university_board LIKE 'Board%' THEN 'Board'
                            WHEN aq.university_board LIKE 'Uni%' THEN 'University'
                            ELSE 'Custom Institution'
                        END as institution_type
                        FROM academic_qualifications aq
                        WHERE aq.employee_id = ?
                        ORDER BY 
                        CASE aq.degree_type
                            WHEN 'Ph.D' THEN 1
                            WHEN 'M.Phil' THEN 2
                            WHEN 'Master' THEN 3
                            WHEN 'Bachelor' THEN 4
                            WHEN 'Higher Secondary' THEN 5
                            WHEN 'Secondary' THEN 6
                            ELSE 7
                        END, aq.passing_year DESC";
        
        $stmt_academic = $conn->prepare($academic_sql);
        $stmt_academic->bind_param("i", $db_employee_id);
        $stmt_academic->execute();
        $academic_result = $stmt_academic->get_result();
        
        $qualifications = [];
        while($row = $academic_result->fetch_assoc()) {
            $qualifications[] = $row;
        }
        
        // Calculate totals
        $total_qualifications = count($qualifications);
        
        // Function to calculate percentage and grade
        function calculatePercentageAndGrade($obtained, $total) {
            if ($total == 0) return ['percentage' => 0, 'grade' => 'N/A'];
            
            $percentage = ($obtained / $total) * 100;
            
            if ($percentage >= 60) {
                $grade = '1st Division';
            } elseif ($percentage >= 45) {
                $grade = '2nd Division';
            } elseif ($percentage > 0) {
                $grade = '3rd Division';
            } else {
                $grade = 'Failed';
            }
            
            return [
                'percentage' => number_format($percentage, 2),
                'grade' => $grade
            ];
        }
        
        // Function to format passing year/date
        function formatPassingDate($date) {
            if (empty($date) || $date == '0000-00-00' || $date == '0000-00-00 00:00:00') {
                return 'N/A';
            }
            
            // Try to parse as date
            $timestamp = strtotime($date);
            if ($timestamp === false) {
                return $date; // Return as-is if not a valid date
            }
            
            // Check if it's just a year (YYYY format)
            if (preg_match('/^\d{4}$/', $date)) {
                return $date; // Return year as-is
            }
            
            // Format as Month Year (e.g., "June 2023")
            return date('F Y', $timestamp);
        }
        
        // Count by institution type for statistics
        $institution_stats = ['Board' => 0, 'University' => 0, 'Custom Institution' => 0];
        foreach($qualifications as $qual) {
            if (isset($qual['institution_type'])) {
                $institution_stats[$qual['institution_type']] = ($institution_stats[$qual['institution_type']] ?? 0) + 1;
            }
        }
    }
}

// Close statements
if (isset($stmt_reg)) $stmt_reg->close();
if (isset($stmt_emp)) $stmt_emp->close();
if (isset($stmt_academic)) $stmt_academic->close();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Academic Qualifications</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
<style>
    :root {
        --primary-green: #198754;
        --secondary-green: #157347;
        --primary-blue: #0d6efd;
    }
    
    body {
        font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
        background-color: #f8f9fa;
        padding: 20px;
    }
    
    .academic-container {
        max-width: 1200px;
        margin: 0 auto;
    }
    
    .academic-card {
        border: none;
        border-radius: 15px;
        box-shadow: 0 5px 25px rgba(0,0,0,0.1);
        background: white;
        overflow: hidden;
    }
    
    .academic-header {
        background: linear-gradient(135deg, #198754 0%, #0d6efd 100%);
        color: white;
        padding: 25px 30px;
    }
    
    .qualification-card {
        border: 1px solid #dee2e6;
        border-radius: 10px;
        padding: 20px;
        margin-bottom: 20px;
        transition: all 0.3s ease;
        background: white;
    }
    
    .qualification-card:hover {
        box-shadow: 0 5px 15px rgba(0,0,0,0.1);
        transform: translateY(-2px);
        border-color: #198754;
    }
    
    .degree-badge {
        display: inline-block;
        padding: 5px 12px;
        border-radius: 20px;
        font-size: 0.85rem;
        font-weight: 500;
        color: white;
        margin-bottom: 10px;
    }
    
    .badge-secondary {
        background: #6c757d;
    }
    
    .badge-higher-secondary {
        background: #0dcaf0;
    }
    
    .badge-bachelor {
        background: #198754;
    }
    
    .badge-master {
        background: #6f42c1;
    }
    
    .badge-mphil {
        background: #fd7e14;
    }
    
    .badge-phd {
        background: #dc3545;
    }
    
    .badge-other {
        background: #20c997;
    }
    
    .grade-badge {
        display: inline-block;
        padding: 5px 12px;
        border-radius: 20px;
        font-size: 0.85rem;
        font-weight: 500;
        color: white;
    }
    
    .grade-1st {
        background: #198754;
    }
    
    .grade-2nd {
        background: #ffc107;
        color: #000;
    }
    
    .grade-3rd {
        background: #dc3545;
    }
    
    .institution-badge {
        display: inline-flex;
        align-items: center;
        gap: 5px;
        padding: 3px 10px;
        border-radius: 4px;
        font-size: 0.8rem;
        font-weight: 500;
    }
    
    .badge-board {
        background: #e7f1ff;
        color: #0d6efd;
        border: 1px solid #a5d8ff;
    }
    
    .badge-university {
        background: #d1e7dd;
        color: #198754;
        border: 1px solid #badbcc;
    }
    
    .badge-custom {
        background: #f8f9fa;
        color: #6c757d;
        border: 1px solid #dee2e6;
    }
    
    .info-row {
        display: flex;
        align-items: center;
        margin-bottom: 8px;
        padding-bottom: 8px;
        border-bottom: 1px solid #f0f0f0;
    }
    
    .info-label {
        font-weight: 500;
        color: #666;
        min-width: 150px;
    }
    
    .info-value {
        font-weight: 500;
        color: #333;
    }
    
    .empty-state {
        text-align: center;
        padding: 50px 20px;
        color: #6c757d;
    }
    
    .empty-state i {
        font-size: 64px;
        margin-bottom: 20px;
        color: #dee2e6;
    }
    
    .btn-add {
        background: var(--primary-green);
        color: white;
        border: none;
        padding: 10px 20px;
        border-radius: 5px;
        font-weight: 500;
        transition: all 0.3s ease;
    }
    
    .btn-add:hover {
        background: var(--secondary-green);
        color: white;
        transform: translateY(-2px);
        box-shadow: 0 5px 15px rgba(25, 135, 84, 0.2);
    }
    
    .btn-edit {
        background: #0d6efd;
        color: white;
        border: none;
        padding: 6px 15px;
        border-radius: 5px;
        font-weight: 500;
        transition: all 0.3s ease;
        font-size: 0.85rem;
    }
    
    .btn-edit:hover {
        background: #0b5ed7;
        color: white;
        transform: translateY(-1px);
        box-shadow: 0 3px 10px rgba(13, 110, 253, 0.2);
    }
    
    .btn-delete {
        background: #dc3545;
        color: white;
        border: none;
        padding: 6px 15px;
        border-radius: 5px;
        font-weight: 500;
        transition: all 0.3s ease;
        font-size: 0.85rem;
    }
    
    .btn-delete:hover {
        background: #bb2d3b;
        color: white;
        transform: translateY(-1px);
        box-shadow: 0 3px 10px rgba(220, 53, 69, 0.2);
    }
    
    .btn-back {
        background: #6c757d;
        color: white;
        border: none;
        padding: 10px 20px;
        border-radius: 5px;
        font-weight: 500;
        transition: all 0.3s ease;
    }
    
    .btn-back:hover {
        background: #5a6268;
        color: white;
        transform: translateY(-2px);
        box-shadow: 0 5px 15px rgba(108, 117, 125, 0.2);
    }
    
    .action-buttons {
        display: flex;
        gap: 10px;
        flex-wrap: wrap;
    }
    
    .summary-card {
        background: linear-gradient(135deg, #198754 0%, #0d6efd 100%);
        color: white;
        border-radius: 10px;
        padding: 20px;
        margin-bottom: 25px;
        box-shadow: 0 4px 12px rgba(0,0,0,0.1);
    }
    
    .summary-title {
        font-weight: 600;
        margin-bottom: 15px;
        font-size: 1.1rem;
        display: flex;
        align-items: center;
        gap: 10px;
    }
    
    .summary-value {
        font-size: 2.5rem;
        font-weight: 700;
        text-align: center;
        text-shadow: 1px 1px 2px rgba(0,0,0,0.2);
    }
    
    .summary-label {
        text-align: center;
        opacity: 0.9;
        font-size: 0.9rem;
    }
    
    .statistics-card {
        background: #f8f9fa;
        border: 1px solid #dee2e6;
        border-radius: 10px;
        padding: 20px;
        margin-top: 20px;
    }
    
    .stat-item {
        text-align: center;
        padding: 15px;
    }
    
    .stat-value {
        font-size: 2rem;
        font-weight: 700;
        color: var(--primary-green);
    }
    
    .stat-label {
        color: #6c757d;
        font-size: 0.9rem;
    }
    
    .institution-icon {
        color: #6c757d;
        margin-right: 5px;
    }
    
    .percentage-high {
        color: #198754;
        font-weight: 600;
    }
    
    .percentage-medium {
        color: #ffc107;
        font-weight: 600;
    }
    
    .percentage-low {
        color: #dc3545;
        font-weight: 600;
    }
    
    .date-badge {
        background: #f8f9fa;
        border: 1px solid #dee2e6;
        border-radius: 4px;
        padding: 3px 8px;
        font-size: 0.8rem;
        color: #6c757d;
        display: inline-flex;
        align-items: center;
        gap: 5px;
    }
</style>
</head>
<body>
<div class="academic-container">
    <div class="academic-card">
        <div class="academic-header">
            <h2 class="mb-0">
                <i class="fas fa-graduation-cap me-2"></i>Academic Qualifications
            </h2>
            <p class="mb-0 mt-2">View and manage your academic qualifications</p>
        </div>
        
        <div class="p-4">
            <?php if(isset($db_employee_id)): ?>
                <!-- SUMMARY SECTION -->
                <div class="summary-card">
                    <div class="row align-items-center">
                        <div class="col-md-3">
                            <div class="summary-value"><?php echo $total_qualifications ?? 0; ?></div>
                            <div class="summary-label">Total Qualifications</div>
                        </div>
                        <div class="col-md-9">
                            <h5 class="summary-title">
                                <i class="fas fa-chart-line"></i>Academic Profile Summary
                            </h5>
                            <p class="mb-2">
                                <?php if(isset($institution_stats)): ?>
                                    <?php 
                                    $stats_text = [];
                                    foreach($institution_stats as $type => $count) {
                                        if ($count > 0) {
                                            $stats_text[] = "$count $type" . ($count > 1 ? 's' : '');
                                        }
                                    }
                                    echo implode(', ', $stats_text);
                                    ?>
                                <?php endif; ?>
                            </p>
                            <p class="mb-0 opacity-75">All your academic qualifications are listed below</p>
                        </div>
                    </div>
                </div>
                
                <!-- ACTION BUTTONS -->
                <div class="d-flex justify-content-between align-items-center mb-4">
                    <div>
                        <h5 class="mb-0 text-success">
                            <i class="fas fa-book me-2"></i>Your Qualifications
                        </h5>
                        <p class="text-muted mb-0">All your academic records in one place</p>
                    </div>
                    <div class="action-buttons">
                        <a href="academic_qualification_edit.php" class="btn btn-add">
                            <i class="fas fa-plus me-2"></i>Add New Qualification
                        </a>
                        <a href="employee_profile_view.php" class="btn btn-back">
                            <i class="fas fa-user me-2"></i>View Profile
                        </a>
                    </div>
                </div>
                
                <!-- QUALIFICATIONS LIST -->
                <?php if(isset($qualifications) && count($qualifications) > 0): ?>
                    <?php foreach($qualifications as $qual): 
                        $grade_info = calculatePercentageAndGrade($qual['obtained_marks'], $qual['total_marks']);
                        $badge_class = 'badge-' . strtolower(str_replace(['.', ' ', '/'], ['', '-', '-'], $qual['degree_type']));
                        
                        // Get institution badge class
                        $institution_badge_class = '';
                        switch($qual['institution_type'] ?? '') {
                            case 'Board':
                                $institution_badge_class = 'badge-board';
                                $institution_icon = '<i class="fas fa-school"></i>';
                                break;
                            case 'University':
                                $institution_badge_class = 'badge-university';
                                $institution_icon = '<i class="fas fa-university"></i>';
                                break;
                            default:
                                $institution_badge_class = 'badge-custom';
                                $institution_icon = '<i class="fas fa-landmark"></i>';
                        }
                        
                        // Format passing date using the new function
                        $passing_date = formatPassingDate($qual['passing_year'] ?? '');
                        
                        // Determine percentage class
                        $percentage_class = '';
                        $percentage = floatval(str_replace('%', '', $grade_info['percentage']));
                        if ($percentage >= 60) {
                            $percentage_class = 'percentage-high';
                        } elseif ($percentage >= 45) {
                            $percentage_class = 'percentage-medium';
                        } else {
                            $percentage_class = 'percentage-low';
                        }
                    ?>
                        <div class="qualification-card">
                            <div class="d-flex justify-content-between align-items-start mb-3">
                                <div class="flex-grow-1">
                                    <div class="d-flex align-items-center mb-2">
                                        <span class="degree-badge <?php echo $badge_class; ?> me-2">
                                            <i class="fas fa-graduation-cap me-1"></i>
                                            <?php echo htmlspecialchars($qual['degree_type']); ?>
                                        </span>
                                        <span class="institution-badge <?php echo $institution_badge_class; ?>">
                                            <?php echo $institution_icon; ?>
                                            <?php echo htmlspecialchars($qual['institution_type'] ?? 'Institution'); ?>
                                        </span>
                                        <span class="date-badge ms-2">
                                            <i class="fas fa-calendar-alt me-1"></i>
                                            <?php echo $passing_date; ?>
                                        </span>
                                    </div>
                                    <h5 class="mb-1"><?php echo htmlspecialchars($qual['degree_name'] ?? 'N/A'); ?></h5>
                                    <p class="text-muted mb-2">
                                        <?php echo $institution_icon; ?>
                                        <?php echo htmlspecialchars($qual['institution_name'] ?? 'N/A'); ?>
                                    </p>
                                    <div class="d-flex gap-3">
                                        <span class="text-muted">
                                            <i class="fas fa-calendar-check me-1"></i>
                                            Passed: <?php echo $passing_date; ?>
                                        </span>
                                        <?php if (!empty($qual['subjects'])): ?>
                                            <span class="text-muted">
                                                <i class="fas fa-book me-1"></i>
                                                Subjects: <?php echo htmlspecialchars(substr($qual['subjects'], 0, 50)); ?>
                                                <?php if (strlen($qual['subjects']) > 50): ?>...<?php endif; ?>
                                            </span>
                                        <?php endif; ?>
                                    </div>
                                </div>
                                <div class="text-end">
                                    <span class="grade-badge grade-<?php echo strtolower(explode(' ', $grade_info['grade'])[0]); ?>">
                                        <?php echo $grade_info['grade']; ?>
                                    </span>
                                    <div class="mt-2">
                                        <small class="text-muted <?php echo $percentage_class; ?>">
                                            <?php echo $grade_info['percentage']; ?>%
                                        </small>
                                    </div>
                                </div>
                            </div>
                            
                            <div class="row mt-3">
                                <div class="col-md-3">
                                    <div class="info-row">
                                        <span class="info-label">Marks:</span>
                                        <span class="info-value">
                                            <?php echo number_format($qual['obtained_marks'] ?? 0, 2); ?> / 
                                            <?php echo number_format($qual['total_marks'] ?? 0, 2); ?>
                                        </span>
                                    </div>
                                </div>
                                <div class="col-md-3">
                                    <div class="info-row">
                                        <span class="info-label">Percentage:</span>
                                        <span class="info-value <?php echo $percentage_class; ?>">
                                            <?php echo $grade_info['percentage']; ?>%
                                        </span>
                                    </div>
                                </div>
                                <div class="col-md-3">
                                    <div class="info-row">
                                        <span class="info-label">Passing Date:</span>
                                        <span class="info-value">
                                            <i class="fas fa-calendar-alt me-1"></i>
                                            <?php echo $passing_date; ?>
                                        </span>
                                    </div>
                                </div>
                                <div class="col-md-3">
                                    <div class="info-row">
                                        <span class="info-label">Institution Type:</span>
                                        <span class="info-value">
                                            <?php echo $institution_icon; ?>
                                            <?php echo htmlspecialchars($qual['institution_type'] ?? 'Custom'); ?>
                                        </span>
                                    </div>
                                </div>
                            </div>
                            
                            <?php if (!empty($qual['subjects']) && strlen($qual['subjects']) > 50): ?>
                                <div class="mt-3">
                                    <div class="info-row">
                                        <span class="info-label">Full Subjects:</span>
                                        <span class="info-value"><?php echo htmlspecialchars($qual['subjects']); ?></span>
                                    </div>
                                </div>
                            <?php endif; ?>
                            
                            <div class="d-flex justify-content-end gap-2 mt-3 pt-3 border-top">
                                <a href="academic_qualification_edit.php?qualification_id=<?php echo $qual['qualification_id']; ?>" 
                                   class="btn btn-edit">
                                    <i class="fas fa-edit me-1"></i>Edit
                                </a>
                                <button type="button" class="btn btn-delete" 
                                        onclick="deleteQualification(<?php echo $qual['qualification_id']; ?>)">
                                    <i class="fas fa-trash me-1"></i>Delete
                                </button>
                            </div>
                        </div>
                    <?php endforeach; ?>
                    
                    <!-- STATISTICS -->
                    <?php if(isset($institution_stats)): ?>
                    <div class="statistics-card">
                        <h5 class="mb-3"><i class="fas fa-chart-bar me-2"></i>Qualification Statistics</h5>
                        <div class="row">
                            <div class="col-md-4">
                                <div class="stat-item">
                                    <div class="stat-value"><?php echo $total_qualifications; ?></div>
                                    <div class="stat-label">Total Qualifications</div>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <div class="stat-item">
                                    <div class="stat-value">
                                        <?php echo $institution_stats['Board'] ?? 0; ?>
                                    </div>
                                    <div class="stat-label">Board Qualifications</div>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <div class="stat-item">
                                    <div class="stat-value">
                                        <?php echo $institution_stats['University'] ?? 0; ?>
                                    </div>
                                    <div class="stat-label">University Degrees</div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <?php endif; ?>
                    
                <?php else: ?>
                    <!-- EMPTY STATE -->
                    <div class="empty-state">
                        <i class="fas fa-graduation-cap"></i>
                        <h4>No Academic Qualifications Found</h4>
                        <p class="mb-4">You haven't added any academic qualifications yet.</p>
                        <a href="academic_qualification_edit.php" class="btn btn-add">
                            <i class="fas fa-plus me-2"></i>Add Your First Qualification
                        </a>
                    </div>
                <?php endif; ?>
                
                <!-- BACK BUTTON -->
                <div class="text-center mt-4">
                    <a href="employee_profile_view.php" class="btn btn-back">
                        <i class="fas fa-arrow-left me-2"></i>Back to Profile
                    </a>
                </div>
            <?php else: ?>
                <div class="alert alert-warning">
                    <h4><i class="fas fa-exclamation-triangle me-2"></i>Profile Not Found</h4>
                    <p>Your employee profile is not linked to the main database.</p>
                    <p>Please contact the administrator to complete your profile setup.</p>
                </div>
            <?php endif; ?>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
<script>
    // Delete qualification confirmation
    function deleteQualification(qualificationId) {
        if (confirm('Are you sure you want to delete this qualification? This action cannot be undone.')) {
            // Create a form and submit it
            const form = document.createElement('form');
            form.method = 'POST';
            form.action = 'delete_academic.php';
            
            const input = document.createElement('input');
            input.type = 'hidden';
            input.name = 'qualification_id';
            input.value = qualificationId;
            
            // Add CSRF token if needed
            <?php if(isset($_SESSION['csrf_token'])): ?>
                const csrfInput = document.createElement('input');
                csrfInput.type = 'hidden';
                csrfInput.name = 'csrf_token';
                csrfInput.value = '<?php echo $_SESSION['csrf_token']; ?>';
                form.appendChild(csrfInput);
            <?php endif; ?>
            
            form.appendChild(input);
            document.body.appendChild(form);
            form.submit();
        }
    }
    
    // Auto-hide alerts after 5 seconds
    setTimeout(function() {
        const alerts = document.querySelectorAll('.alert');
        alerts.forEach(alert => {
            const bsAlert = new bootstrap.Alert(alert);
            bsAlert.close();
        });
    }, 5000);
</script>
</body>
</html>