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

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

echo "<h1>🔍 Check Exam Types Inconsistency</h1>";

// Check all exam types in results table
echo "<h2>📊 Exam Types in Results Table:</h2>";
$stmt = $pdo->query("
    SELECT exam_type, COUNT(*) as count, 
           MIN(created_at) as first_entry, 
           MAX(created_at) as last_entry
    FROM results 
    GROUP BY exam_type 
    ORDER BY count DESC
");
$exam_types_results = $stmt->fetchAll();

echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
echo "<tr style='background: #f8f9fa;'>";
echo "<th>Exam Type</th><th>Count</th><th>First Entry</th><th>Last Entry</th><th>Language</th>";
echo "</tr>";

foreach ($exam_types_results as $exam) {
    $language = 'Unknown';
    if (preg_match('/[a-zA-Z]/', $exam['exam_type'])) {
        $language = 'English';
    } elseif (preg_match('/[\x{0980}-\x{09FF}]/u', $exam['exam_type'])) {
        $language = 'Bengali';
    }
    
    $row_style = '';
    if ($language == 'English') {
        $row_style = 'style="background: #e3f2fd;"';
    } elseif ($language == 'Bengali') {
        $row_style = 'style="background: #f3e5f5;"';
    }
    
    echo "<tr $row_style>";
    echo "<td><strong>{$exam['exam_type']}</strong></td>";
    echo "<td>{$exam['count']}</td>";
    echo "<td>{$exam['first_entry']}</td>";
    echo "<td>{$exam['last_entry']}</td>";
    echo "<td>$language</td>";
    echo "</tr>";
}
echo "</table>";

// Check exam types in subject_mark_distribution
echo "<h2>📚 Exam Types in Subject Mark Distribution:</h2>";
$stmt = $pdo->query("
    SELECT exam_type, COUNT(*) as count
    FROM subject_mark_distribution 
    GROUP BY exam_type 
    ORDER BY count DESC
");
$exam_types_smd = $stmt->fetchAll();

echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
echo "<tr style='background: #f8f9fa;'>";
echo "<th>Exam Type</th><th>Count</th><th>Language</th>";
echo "</tr>";

foreach ($exam_types_smd as $exam) {
    $language = 'Unknown';
    if (preg_match('/[a-zA-Z]/', $exam['exam_type'])) {
        $language = 'English';
    } elseif (preg_match('/[\x{0980}-\x{09FF}]/u', $exam['exam_type'])) {
        $language = 'Bengali';
    }
    
    $row_style = '';
    if ($language == 'English') {
        $row_style = 'style="background: #e3f2fd;"';
    } elseif ($language == 'Bengali') {
        $row_style = 'style="background: #f3e5f5;"';
    }
    
    echo "<tr $row_style>";
    echo "<td><strong>{$exam['exam_type']}</strong></td>";
    echo "<td>{$exam['count']}</td>";
    echo "<td>$language</td>";
    echo "</tr>";
}
echo "</table>";

// Check exam types in mark_distributions
echo "<h2>📋 Exam Types in Mark Distributions:</h2>";
$stmt = $pdo->query("
    SELECT exam_type, COUNT(*) as count
    FROM mark_distributions 
    GROUP BY exam_type 
    ORDER BY count DESC
");
$exam_types_md = $stmt->fetchAll();

echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
echo "<tr style='background: #f8f9fa;'>";
echo "<th>Exam Type</th><th>Count</th><th>Language</th>";
echo "</tr>";

foreach ($exam_types_md as $exam) {
    $language = 'Unknown';
    if (preg_match('/[a-zA-Z]/', $exam['exam_type'])) {
        $language = 'English';
    } elseif (preg_match('/[\x{0980}-\x{09FF}]/u', $exam['exam_type'])) {
        $language = 'Bengali';
    }
    
    $row_style = '';
    if ($language == 'English') {
        $row_style = 'style="background: #e3f2fd;"';
    } elseif ($language == 'Bengali') {
        $row_style = 'style="background: #f3e5f5;"';
    }
    
    echo "<tr $row_style>";
    echo "<td><strong>{$exam['exam_type']}</strong></td>";
    echo "<td>{$exam['count']}</td>";
    echo "<td>$language</td>";
    echo "</tr>";
}
echo "</table>";

// Analysis
echo "<h2>🔍 Analysis & Recommendations:</h2>";

$all_exam_types = [];
foreach ($exam_types_results as $exam) {
    $all_exam_types[] = $exam['exam_type'];
}
foreach ($exam_types_smd as $exam) {
    if (!in_array($exam['exam_type'], $all_exam_types)) {
        $all_exam_types[] = $exam['exam_type'];
    }
}
foreach ($exam_types_md as $exam) {
    if (!in_array($exam['exam_type'], $all_exam_types)) {
        $all_exam_types[] = $exam['exam_type'];
    }
}

$english_types = [];
$bengali_types = [];
$mixed_found = false;

foreach ($all_exam_types as $type) {
    if (preg_match('/[a-zA-Z]/', $type)) {
        $english_types[] = $type;
    } elseif (preg_match('/[\x{0980}-\x{09FF}]/u', $type)) {
        $bengali_types[] = $type;
    }
}

if (!empty($english_types) && !empty($bengali_types)) {
    $mixed_found = true;
}

if ($mixed_found) {
    echo "<div style='background: #fff3cd; padding: 20px; border: 1px solid #ffc107; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>⚠️ Mixed Language Issue Detected!</h3>";
    echo "<p><strong>English Exam Types:</strong> " . implode(', ', $english_types) . "</p>";
    echo "<p><strong>Bengali Exam Types:</strong> " . implode(', ', $bengali_types) . "</p>";
    echo "<p><strong>Problem:</strong> This causes inconsistency in marksheet display and search.</p>";
    echo "</div>";
    
    echo "<div style='background: #d1ecf1; padding: 20px; border: 1px solid #bee5eb; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>💡 Recommended Solutions:</h3>";
    echo "<ol>";
    echo "<li><strong>Standardize to Bengali:</strong> Convert all English exam types to Bengali</li>";
    echo "<li><strong>Standardize to English:</strong> Convert all Bengali exam types to English</li>";
    echo "<li><strong>Create Mapping:</strong> Add translation system in code</li>";
    echo "</ol>";
    echo "</div>";
    
    // Mapping suggestions
    echo "<div style='background: #f8f9fa; padding: 20px; border: 1px solid #dee2e6; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>🔄 Suggested Mappings:</h3>";
    echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
    echo "<tr style='background: #e9ecef;'>";
    echo "<th>English</th><th>Bengali</th><th>Action</th>";
    echo "</tr>";
    
    $mappings = [
        'midterm' => 'মধ্যবর্ষিক',
        'final' => 'বার্ষিক',
        'test' => 'পরীক্ষা',
        'monthly' => 'মাসিক',
        'weekly' => 'সাপ্তাহিক',
        'half_yearly' => 'অর্ধবার্ষিক',
        'annual' => 'বার্ষিক'
    ];
    
    foreach ($mappings as $eng => $ben) {
        $found_eng = in_array($eng, $english_types);
        $found_ben = in_array($ben, $bengali_types);
        
        if ($found_eng || $found_ben) {
            $status = '';
            if ($found_eng && $found_ben) {
                $status = '<span style="color: red;">Both exist - Need merge</span>';
            } elseif ($found_eng) {
                $status = '<span style="color: blue;">English exists</span>';
            } elseif ($found_ben) {
                $status = '<span style="color: green;">Bengali exists</span>';
            }
            
            echo "<tr>";
            echo "<td>$eng</td>";
            echo "<td>$ben</td>";
            echo "<td>$status</td>";
            echo "</tr>";
        }
    }
    echo "</table>";
    echo "</div>";
    
} else {
    echo "<div style='background: #d1edff; padding: 20px; border: 1px solid #0d6efd; border-radius: 5px;'>";
    echo "<h3>✅ No Mixed Language Issue!</h3>";
    echo "<p>All exam types are in the same language.</p>";
    echo "</div>";
}

// Test specific case
echo "<h2>🧪 Test Specific Case: Student 1647</h2>";
$stmt = $pdo->prepare("
    SELECT r.exam_type, COUNT(*) as count
    FROM results r
    WHERE r.student_id = ? AND r.session = '2024'
    GROUP BY r.exam_type
");
$stmt->execute([1647]);
$student_exam_types = $stmt->fetchAll();

if (!empty($student_exam_types)) {
    echo "<p><strong>Student 1647 has results for:</strong></p>";
    echo "<ul>";
    foreach ($student_exam_types as $exam) {
        echo "<li><strong>{$exam['exam_type']}</strong> ({$exam['count']} subjects)</li>";
    }
    echo "</ul>";
    
    echo "<p><strong>URL Test:</strong></p>";
    foreach ($student_exam_types as $exam) {
        $exam_type = $exam['exam_type'];
        echo "<p><a href='reports/marksheet.php?student_id=1647&session=2024&exam_type=" . urlencode($exam_type) . "&debug=1' style='background: #007bff; color: white; padding: 5px 10px; text-decoration: none; border-radius: 3px;'>Test: $exam_type</a></p>";
    }
} else {
    echo "<p>❌ Student 1647 has no results in 2024.</p>";
}

// Quick fix tools
echo "<hr>";
echo "<h2>🛠️ Quick Fix Tools:</h2>";
echo "<div style='text-align: center; margin: 20px 0;'>";
echo "<a href='standardize_exam_types.php' style='background: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; margin: 5px;'>🔄 Standardize Exam Types</a>";
echo "<a href='debug_marksheet_form.php?student_id=1647&session=2024&exam_type=midterm' style='background: #17a2b8; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; margin: 5px;'>🔍 Debug Student 1647</a>";
echo "</div>";

echo "<p><a href='reports/marksheet.php'>← Back to Marksheet</a></p>";
?>