<?php
// includes/check-certificate.php - Auto Certificate System (FIXED)

function generateCertificateNumber($pdo, $course_id, $user_id) {
    $stmt = $pdo->prepare("SELECT title_en FROM courses WHERE id = ?");
    $stmt->execute([$course_id]);
    $course = $stmt->fetch();
    
    $words = explode(' ', $course['title_en'] ?? 'COURSE');
    $course_code = '';
    foreach($words as $word) {
        if(!empty($word)) $course_code .= strtoupper(substr($word, 0, 1));
    }
    if(strlen($course_code) < 2) $course_code = 'CRS';
    if(strlen($course_code) > 4) $course_code = substr($course_code, 0, 4);
    
    $stmt = $pdo->prepare("SELECT enrolled_at FROM enrollments WHERE user_id = ? AND course_id = ? AND payment_status = 'completed' ORDER BY enrolled_at LIMIT 1");
    $stmt->execute([$user_id, $course_id]);
    $enrollment = $stmt->fetch();
    $reg_date = $enrollment ? $enrollment['enrolled_at'] : date('Y-m-d H:i:s');
    
    $year = date('Y', strtotime($reg_date));
    $month = date('m', strtotime($reg_date));
    
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM certificates WHERE course_id = ?");
    $stmt->execute([$course_id]);
    $serial = str_pad($stmt->fetchColumn() + 1, 4, '0', STR_PAD_LEFT);
    
    return $course_code . '-' . $year . '-' . $month . '-' . $serial;
}

function checkAndIssueCertificate($pdo, $user_id, $course_id) {
    // Check if already has certificate
    $stmt = $pdo->prepare("SELECT id FROM certificates WHERE user_id = ? AND course_id = ?");
    $stmt->execute([$user_id, $course_id]);
    if($stmt->fetch()) return false; // Already has certificate
    
    // Check if ALL lessons are completed
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(DISTINCT l.id) as total_lessons,
            COUNT(DISTINCT CASE WHEN lp.completed = 1 THEN lp.lesson_id END) as completed_lessons
        FROM lessons l
        LEFT JOIN lesson_progress lp ON l.id = lp.lesson_id AND lp.user_id = ?
        WHERE l.course_id = ?
    ");
    $stmt->execute([$user_id, $course_id]);
    $progress = $stmt->fetch();
    
    $total = (int)($progress['total_lessons'] ?? 0);
    $completed = (int)($progress['completed_lessons'] ?? 0);
    
    // ONLY issue if ALL lessons completed AND there are lessons
    if($total > 0 && $total == $completed) {
        // Get student and course info
        $stmt = $pdo->prepare("SELECT u.full_name, c.title_bn FROM users u JOIN courses c ON c.id = ? WHERE u.id = ?");
        $stmt->execute([$course_id, $user_id]);
        $data = $stmt->fetch();
        
        // Get instructor
        $stmt = $pdo->prepare("SELECT u.full_name FROM users u JOIN courses c ON c.instructor_id = u.id WHERE c.id = ?");
        $stmt->execute([$course_id]);
        $instructor = $stmt->fetch();
        
        $cert_number = generateCertificateNumber($pdo, $course_id, $user_id);
        $verification = md5($user_id . $course_id . time() . rand());
        
        $stmt = $pdo->prepare("INSERT INTO certificates (user_id, course_id, certificate_number, student_name, course_name, instructor_name, completion_date, grade, certificate_type, verification_code) VALUES (?, ?, ?, ?, ?, ?, CURDATE(), 'Pass', 'auto', ?)");
        $stmt->execute([$user_id, $course_id, $cert_number, $data['full_name'], $data['title_bn'], $instructor['full_name'] ?? 'স্বাস্থ্য কানন', $verification]);
        
        return $pdo->lastInsertId();
    }
    
    return false; // Not all lessons completed
}
?>