<?php
// generate_student_id.php
error_reporting(E_ALL);
ini_set('display_errors', 1);
require 'db.php';
require 'helpers.php';

header('Content-Type: application/json');

// Get POST data
$college_id = $_POST['college_id'] ?? null;
$program_id = $_POST['program_id'] ?? null;
$section_id = $_POST['section_id'] ?? null;
$batch_id = $_POST['batch_id'] ?? null;

if (!$college_id || !$program_id || !$section_id || !$batch_id) {
    echo json_encode(['success' => false, 'message' => 'جميع الحقول مطلوبة']);
    exit;
}

try {
    // Get section and batch codes
    $section_stmt = $mysqli1->prepare("SELECT code FROM sections WHERE id = ?");
    $section_stmt->bind_param("i", $section_id);
    $section_stmt->execute();
    $section_result = $section_stmt->get_result();
    $section_data = $section_result->fetch_assoc();
    $section_code = $section_data['code'] ?? '';
    
    $batch_stmt = $mysqli1->prepare("SELECT code FROM batches WHERE id = ?");
    $batch_stmt->bind_param("i", $batch_id);
    $batch_stmt->execute();
    $batch_result = $batch_stmt->get_result();
    $batch_data = $batch_result->fetch_assoc();
    $batch_code = $batch_data['code'] ?? '';
    
    $prefix = $section_code . $batch_code;
    
    // ============================================================
    // COUNT 1: By ALL FOUR filters (your exact query)
    // ============================================================
    $count1_stmt = $mysqli1->prepare("
        SELECT COUNT(*) as total 
        FROM students 
        WHERE section_id = ? 
        AND program_id = ? 
        AND college_id = ? 
        AND batch_id = ?
        AND deleted_at IS NULL
    ");
    $count1_stmt->bind_param("iiii", $section_id, $program_id, $college_id, $batch_id);
    $count1_stmt->execute();
    $count1_result = $count1_stmt->get_result();
    $count1_data = $count1_result->fetch_assoc();
    $total_by_filter = (int)($count1_data['total'] ?? 0);
    
    // ============================================================
    // COUNT 2: By ID pattern (LAB24%)
    // ============================================================
    $count2_stmt = $mysqli1->prepare("
        SELECT COUNT(*) as total 
        FROM students 
        WHERE student_id LIKE CONCAT(?, '%')
        AND deleted_at IS NULL
    ");
    $count2_stmt->bind_param("s", $prefix);
    $count2_stmt->execute();
    $count2_result = $count2_stmt->get_result();
    $count2_data = $count2_result->fetch_assoc();
    $total_by_pattern = (int)($count2_data['total'] ?? 0);
    
    // ============================================================
    // FIND WHICH STUDENTS DON'T MATCH ALL CONDITIONS
    // ============================================================
    $mismatch_stmt = $mysqli1->prepare("
        SELECT 
            student_id,
            college_id,
            program_id, 
            section_id,
            batch_id
        FROM students 
        WHERE student_id LIKE CONCAT(?, '%')
        AND deleted_at IS NULL
        AND (
            college_id != ? 
            OR program_id != ? 
            OR section_id != ? 
            OR batch_id != ?
        )
        ORDER BY student_id
        LIMIT 20
    ");
    $mismatch_stmt->bind_param("siiii", $prefix, $college_id, $program_id, $section_id, $batch_id);
    $mismatch_stmt->execute();
    $mismatch_result = $mismatch_stmt->get_result();
    
    $mismatched_students = [];
    while ($row = $mismatch_result->fetch_assoc()) {
        $mismatched_students[] = $row;
    }
    
    // ============================================================
    // FIND HIGHEST SEQUENCE NUMBER
    // ============================================================
    $max_sequence = 0;
    $existing_ids = [];
    
    $sequence_stmt = $mysqli1->prepare("
        SELECT student_id 
        FROM students 
        WHERE student_id LIKE CONCAT(?, '%')
        AND deleted_at IS NULL
        ORDER BY student_id
    ");
    $sequence_stmt->bind_param("s", $prefix);
    $sequence_stmt->execute();
    $sequence_result = $sequence_stmt->get_result();
    
    while ($row = $sequence_result->fetch_assoc()) {
        $student_id = $row['student_id'];
        $existing_ids[] = $student_id;
        
        if (strpos($student_id, $prefix) === 0) {
            $sequence_part = substr($student_id, strlen($prefix));
            if (is_numeric($sequence_part)) {
                $current_seq = (int)$sequence_part;
                if ($current_seq > $max_sequence) {
                    $max_sequence = $current_seq;
                }
            }
        }
    }
    
    // ============================================================
    // GENERATE NEXT ID
    // ============================================================
    
    // Option A: Use total_by_filter + 1 (if you want strict matching)
    // $next_sequence = $total_by_filter + 1;
    
    // Option B: Use max_sequence + 1 (if you want sequential IDs)
    $next_sequence = $max_sequence + 1;
    
    // Make sure it's at least total_by_filter + 1
    if ($next_sequence < ($total_by_filter + 1)) {
        $next_sequence = $total_by_filter + 1;
    }
    
    $formatted_sequence = str_pad($next_sequence, 3, '0', STR_PAD_LEFT);
    $next_student_id = $prefix . $formatted_sequence;
    
    // Verify uniqueness
    $check_stmt = $mysqli1->prepare("SELECT id FROM students WHERE student_id = ? AND deleted_at IS NULL");
    $check_stmt->bind_param("s", $next_student_id);
    $check_stmt->execute();
    $check_result = $check_stmt->get_result();
    
    if ($check_result->num_rows > 0) {
        // Find next available
        for ($i = $next_sequence + 1; $i <= 999; $i++) {
            $test_id = $prefix . str_pad($i, 3, '0', STR_PAD_LEFT);
            $check_stmt->bind_param("s", $test_id);
            $check_stmt->execute();
            $check_result = $check_stmt->get_result();
            
            if ($check_result->num_rows === 0) {
                $next_student_id = $test_id;
                $next_sequence = $i;
                break;
            }
        }
    }
    
    // ============================================================
    // PREPARE RESPONSE WITH DETAILED INFO
    // ============================================================
    
    $format_info = "<div class='alert alert-primary p-2'>";
    $format_info .= "<strong>القسم:</strong> <span class='badge bg-primary'>{$section_code}</span> ";
    $format_info .= "<strong>الدفعة:</strong> <span class='badge bg-success'>{$batch_code}</span><br>";
    $format_info .= "<strong>البادئة:</strong> <code>{$prefix}</code><br>";
    $format_info .= "</div>";
    
    $format_info .= "<div class='alert alert-info p-2 mt-2'>";
    $format_info .= "<strong>إحصائيات:</strong><br>";
    $format_info .= "• عدد الطلاب الذين يطابقون جميع الشروط (قسم={$section_id}, برنامج={$program_id}, كلية={$college_id}, دفعة={$batch_id}): <strong>{$total_by_filter}</strong><br>";
    $format_info .= "• عدد الطلاب الذين لديهم بادئة '{$prefix}': <strong>{$total_by_pattern}</strong><br>";
    $format_info .= "• الفرق: <strong>" . ($total_by_pattern - $total_by_filter) . "</strong> طالب<br>";
    $format_info .= "• أعلى رقم تسلسلي موجود: <strong>{$max_sequence}</strong><br>";
    $format_info .= "• الرقم التسلسلي التالي: <strong>{$next_sequence}</strong>";
    $format_info .= "</div>";
    
    // Show mismatched students if any
    if (!empty($mismatched_students)) {
        $format_info .= "<div class='alert alert-warning p-2 mt-2'>";
        $format_info .= "<strong>ملاحظة:</strong> يوجد " . count($mismatched_students) . " طالب لديهم رقم '{$prefix}' ولكن شروطهم مختلفة:<br>";
        
        foreach ($mismatched_students as $student) {
            $differences = [];
            if ($student['college_id'] != $college_id) $differences[] = "كلية مختلفة";
            if ($student['program_id'] != $program_id) $differences[] = "برنامج مختلف";
            if ($student['section_id'] != $section_id) $differences[] = "قسم مختلف";
            if ($student['batch_id'] != $batch_id) $differences[] = "دفعة مختلفة";
            
            $format_info .= "• {$student['student_id']}: " . implode(", ", $differences) . "<br>";
        }
        $format_info .= "</div>";
    }
    
    $format_info .= "<div class='alert alert-success p-2 mt-2'>";
    $format_info .= "<i class='fas fa-id-card ms-2'></i> ";
    $format_info .= "<strong>الرقم المولد:</strong> <span class='fs-5 fw-bold'>{$next_student_id}</span>";
    $format_info .= "</div>";
    
    // Show first few existing IDs
    if (!empty($existing_ids)) {
        $format_info .= "<div class='small text-muted mt-2'>";
        $format_info .= "<i class='fas fa-list ms-2'></i> الأرقام الموجودة: ";
        $format_info .= implode(', ', array_slice($existing_ids, 0, 10));
        if (count($existing_ids) > 10) {
            $format_info .= " ... و " . (count($existing_ids) - 10) . " أكثر";
        }
        $format_info .= "</div>";
    }
    
    echo json_encode([
        'success' => true,
        'next_student_id' => $next_student_id,
        'format_info' => $format_info,
        'section_code' => $section_code,
        'batch_code' => $batch_code,
        'sequence_number' => $next_sequence,
        'total_by_filter' => $total_by_filter,
        'total_by_pattern' => $total_by_pattern,
        'max_sequence' => $max_sequence,
        'mismatch_count' => count($mismatched_students)
    ]);
    
} catch (Exception $e) {
    error_log("Error: " . $e->getMessage());
    echo json_encode(['success' => false, 'message' => 'خطأ: ' . $e->getMessage()]);
}
?>