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

// Check if employee is logged in
if (!isset($_SESSION['employee_id']) || !isset($_SESSION['cnic'])) {
    header('HTTP/1.1 401 Unauthorized');
    echo 'Unauthorized access';
    exit();
}

include 'connect.php';

// Get employee ID from query parameter
$employee_id = isset($_GET['employee_id']) ? intval($_GET['employee_id']) : 0;

if ($employee_id <= 0) {
    echo '<div class="alert alert-warning">Invalid employee ID</div>';
    exit();
}

// FIXED SQL QUERY - Extracts numeric ID from "BoardXX" or "UniXX" format
$academic_sql = "SELECT aq.*,
                 -- Extract and resolve institution name
                 CASE
                     -- If it starts with 'Board', extract number and lookup from boards table
                     WHEN aq.university_board LIKE 'Board%' THEN
                         COALESCE(
                             (SELECT b_name FROM boards 
                              WHERE b_id = CAST(SUBSTRING(aq.university_board, 6) AS UNSIGNED)),
                             aq.university_board
                         )
                     -- If it starts with 'Uni', extract number and lookup from universities table
                     WHEN aq.university_board LIKE 'Uni%' THEN
                         COALESCE(
                             (SELECT uni_name FROM universities 
                              WHERE uni_id = CAST(SUBSTRING(aq.university_board, 4) AS UNSIGNED)),
                             aq.university_board
                         )
                     -- If it's just a number, try both tables
                     WHEN aq.university_board REGEXP '^[0-9]+$' THEN
                         COALESCE(
                             (SELECT b_name FROM boards WHERE b_id = CAST(aq.university_board AS UNSIGNED)),
                             (SELECT uni_name FROM universities WHERE uni_id = CAST(aq.university_board AS UNSIGNED)),
                             aq.university_board
                         )
                     -- Otherwise, it's custom text
                     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'
                     WHEN aq.university_board REGEXP '^[0-9]+$' AND 
                          EXISTS(SELECT 1 FROM boards WHERE b_id = CAST(aq.university_board AS UNSIGNED)) THEN 'Board'
                     WHEN aq.university_board REGEXP '^[0-9]+$' AND 
                          EXISTS(SELECT 1 FROM universities WHERE uni_id = CAST(aq.university_board AS UNSIGNED)) THEN 'University'
                     ELSE 'Custom'
                 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", $employee_id);
$stmt_academic->execute();
$academic_result = $stmt_academic->get_result();

$qualifications = [];
while($row = $academic_result->fetch_assoc()) {
    $qualifications[] = $row;
}

// 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 get institution icon
function getInstitutionIcon($type) {
    switch($type) {
        case 'Board':
            return '<i class="fas fa-school"></i>';
        case 'University':
            return '<i class="fas fa-university"></i>';
        default:
            return '<i class="fas fa-landmark"></i>';
    }
}

// Display qualifications
if (count($qualifications) > 0): 
    $total_qualifications = count($qualifications);
    // Count by degree type
    $degree_counts = [];
    foreach($qualifications as $qual) {
        $degree_type = $qual['degree_type'];
        if (!isset($degree_counts[$degree_type])) {
            $degree_counts[$degree_type] = 0;
        }
        $degree_counts[$degree_type]++;
    }
?>
    <!-- SUMMARY SECTION -->
    <div class="summary-card mb-4">
        <div class="row align-items-center">
            <div class="col-md-3">
                <div class="summary-value"><?php echo $total_qualifications; ?></div>
                <div class="summary-label">Total Qualifications</div>
            </div>
            <div class="col-md-9">
                <h5 class="summary-title">
                    <i class="fas fa-info-circle"></i>Academic Profile Summary
                </h5>
                <p class="mb-2">
                    <?php 
                    $degree_types = [];
                    foreach($degree_counts as $type => $count) {
                        $degree_types[] = "$count $type";
                    }
                    echo implode(', ', $degree_types);
                    ?>
                </p>
                <p class="mb-0 text-muted">All your academic qualifications are listed below.</p>
            </div>
        </div>
    </div>
    
    <!-- QUALIFICATIONS LIST -->
    <?php foreach($qualifications as $qual): 
        $grade_info = calculatePercentageAndGrade($qual['obtained_marks'], $qual['total_marks']);
        $badge_class = 'badge-' . strtolower(str_replace(['.', ' ', '/'], ['', '-', '-'], $qual['degree_type']));
        $institution_icon = getInstitutionIcon($qual['institution_type'] ?? 'Custom');
        $institution_name = htmlspecialchars($qual['institution_name'] ?? 'N/A');
        
        // Format passing year
        $passing_year = 'N/A';
        if (!empty($qual['passing_year']) && $qual['passing_year'] != '0000-00-00' && $qual['passing_year'] != '0000-00-00 00:00:00') {
            $passing_year = date('Y', strtotime($qual['passing_year']));
        }
        
        // Debug info - show what's stored and resolved
        $debug_info = '';
        if (isset($_GET['debug'])) {
            $debug_info = '<small class="text-muted d-block mt-1">
                Stored as: ' . htmlspecialchars($qual['university_board'] ?? '') . ' 
                → Resolved to: ' . $institution_name . '
            </small>';
        }
    ?>
        <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-type-badge">
                            <?php echo $institution_icon; ?>
                            <?php echo htmlspecialchars($qual['institution_type'] ?? 'Institution'); ?>
                        </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 $institution_name; ?>
                        <?php echo $debug_info; ?>
                    </p>
                    <div class="d-flex gap-3">
                        <span class="text-muted">
                            <i class="fas fa-calendar-alt me-1"></i>
                            Year: <?php echo $passing_year; ?>
                        </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 $grade_info['percentage']; ?>%</small>
                    </div>
                </div>
            </div>
            
            <div class="row mt-3">
                <div class="col-md-4">
                    <div class="detail-item">
                        <span class="detail-label">Marks:</span>
                        <span class="detail-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-4">
                    <div class="detail-item">
                        <span class="detail-label">Percentage:</span>
                        <span class="detail-value percentage-<?php echo ($grade_info['percentage'] >= 60 ? 'high' : ($grade_info['percentage'] >= 45 ? 'medium' : 'low')); ?>">
                            <?php echo $grade_info['percentage']; ?>%
                        </span>
                    </div>
                </div>
                <div class="col-md-4">
                    <div class="detail-item">
                        <span class="detail-label">Institution Type:</span>
                        <span class="detail-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="detail-item">
                        <span class="detail-label">Full Subjects:</span>
                        <span class="detail-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-sm btn-primary">
                    <i class="fas fa-edit me-1"></i>Edit
                </a>
                <button type="button" class="btn btn-sm btn-danger" 
                        onclick="deleteQualification(<?php echo $qual['qualification_id']; ?>)">
                    <i class="fas fa-trash me-1"></i>Delete
                </button>
            </div>
        </div>
    <?php endforeach; ?>
    
    <!-- VIEW FULL PAGE LINK -->
    <div class="text-center mt-4">
        <a href="academic_qualification_view.php" class="btn btn-primary">
            <i class="fas fa-external-link-alt me-2"></i>View Full Academic Qualifications Page
        </a>
    </div>
    
    <!-- STATISTICS CARD -->
    <div class="statistics-card mt-4">
        <h5><i class="fas fa-chart-bar me-2"></i>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 
                        $board_count = array_reduce($qualifications, function($carry, $item) {
                            return $carry + (($item['institution_type'] ?? '') == 'Board' ? 1 : 0);
                        }, 0);
                        echo $board_count;
                        ?>
                    </div>
                    <div class="stat-label">Board Qualifications</div>
                </div>
            </div>
            <div class="col-md-4">
                <div class="stat-item">
                    <div class="stat-value">
                        <?php 
                        $uni_count = array_reduce($qualifications, function($carry, $item) {
                            return $carry + (($item['institution_type'] ?? '') == 'University' ? 1 : 0);
                        }, 0);
                        echo $uni_count;
                        ?>
                    </div>
                    <div class="stat-label">University Degrees</div>
                </div>
            </div>
        </div>
    </div>
    
<?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; ?>

<?php
$stmt_academic->close();
$conn->close();
?>

<style>
    /* Your existing CSS styles remain the same */
    .qualification-card {
        background: white;
        border: 1px solid #e0e0e0;
        border-radius: 10px;
        padding: 20px;
        margin-bottom: 15px;
        box-shadow: 0 2px 8px rgba(0,0,0,0.05);
    }
    
    .summary-card {
        background: linear-gradient(135deg, #198754 0%, #0d6efd 100%);
        color: white;
        padding: 20px;
        border-radius: 10px;
        box-shadow: 0 4px 12px rgba(0,0,0,0.1);
    }
    
    .summary-value {
        font-size: 2.5rem;
        font-weight: 700;
        text-align: center;
    }
    
    .summary-label {
        text-align: center;
        font-size: 0.9rem;
        opacity: 0.9;
    }
    
    .summary-title {
        font-weight: 600;
        margin-bottom: 10px;
    }
    
    .degree-badge {
        display: inline-block;
        padding: 4px 12px;
        border-radius: 20px;
        font-size: 0.85rem;
        font-weight: 500;
    }
    
    .badge-ph-d { background: #8b0000; color: white; }
    .badge-m-phil { background: #800080; color: white; }
    .badge-master { background: #006400; color: white; }
    .badge-bachelor { background: #0d6efd; color: white; }
    .badge-higher-secondary { background: #ff8c00; color: white; }
    .badge-secondary { background: #6c757d; color: white; }
    .badge-other { background: #17a2b8; color: white; }
    
    .grade-badge {
        display: inline-block;
        padding: 4px 12px;
        border-radius: 4px;
        font-weight: 600;
        font-size: 0.85rem;
    }
    
    .grade-1st { background: #d4edda; color: #155724; }
    .grade-2nd { background: #fff3cd; color: #856404; }
    .grade-3rd { background: #f8d7da; color: #721c24; }
    .grade-failed { background: #f5f5f5; color: #6c757d; }
    
    .institution-type-badge {
        display: inline-flex;
        align-items: center;
        gap: 5px;
        padding: 3px 10px;
        background: #e7f1ff;
        color: #0d6efd;
        border-radius: 4px;
        font-size: 0.8rem;
    }
    
    .detail-item {
        margin-bottom: 8px;
    }
    
    .detail-label {
        font-weight: 600;
        color: #666;
        margin-right: 10px;
        min-width: 120px;
        display: inline-block;
    }
    
    .detail-value {
        color: #333;
    }
    
    .percentage-high { color: #198754; font-weight: 600; }
    .percentage-medium { color: #ffc107; font-weight: 600; }
    .percentage-low { color: #dc3545; font-weight: 600; }
    
    .statistics-card {
        background: #f8f9fa;
        border: 1px solid #dee2e6;
        border-radius: 10px;
        padding: 20px;
    }
    
    .stat-item {
        text-align: center;
    }
    
    .stat-value {
        font-size: 2rem;
        font-weight: 700;
        color: #198754;
    }
    
    .stat-label {
        color: #6c757d;
        font-size: 0.9rem;
    }
    
    .empty-state {
        text-align: center;
        padding: 40px 20px;
        color: #6c757d;
    }
    
    .empty-state i {
        font-size: 4rem;
        color: #dee2e6;
        margin-bottom: 20px;
    }
    
    .btn-add {
        background: #198754;
        color: white;
        padding: 10px 25px;
        border-radius: 5px;
        text-decoration: none;
        display: inline-block;
    }
    
    .btn-add:hover {
        background: #157347;
        color: white;
    }
</style>

<script>
function deleteQualification(qualificationId) {
    if (confirm('Are you sure you want to delete this qualification? This action cannot be undone.')) {
        window.location.href = 'delete_qualification.php?id=' + qualificationId;
    }
}
</script>