<?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>🔄 Standardize Exam Types</h1>";

// Define standard mappings
$exam_type_mappings = [
    // English to Bengali
    'midterm' => 'মধ্যবর্ষিক',
    'final' => 'বার্ষিক',
    'test' => 'পরীক্ষা',
    'monthly' => 'মাসিক',
    'weekly' => 'সাপ্তাহিক',
    'half_yearly' => 'অর্ধবার্ষিক',
    'annual' => 'বার্ষিক',
    'quarterly' => 'ত্রৈমাসিক',
    'unit_test' => 'ইউনিট টেস্ট',
    'class_test' => 'ক্লাস টেস্ট'
];

// Reverse mapping (Bengali to English)
$reverse_mappings = array_flip($exam_type_mappings);

$action = $_POST['action'] ?? '';
$target_language = $_POST['target_language'] ?? 'bengali';

if ($action == 'standardize') {
    echo "<div style='background: #fff3cd; padding: 15px; border: 1px solid #ffc107; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>🔄 Standardization Process Started...</h3>";
    
    try {
        $pdo->beginTransaction();
        
        $updates_made = 0;
        $tables_to_update = ['results', 'subject_mark_distribution', 'mark_distributions'];
        
        foreach ($tables_to_update as $table) {
            echo "<h4>📋 Processing table: $table</h4>";
            
            // Get all unique exam types in this table
            $stmt = $pdo->query("SELECT DISTINCT exam_type FROM $table");
            $exam_types = $stmt->fetchAll(PDO::FETCH_COLUMN);
            
            foreach ($exam_types as $exam_type) {
                $new_exam_type = null;
                
                if ($target_language == 'bengali') {
                    // Convert English to Bengali
                    if (isset($exam_type_mappings[strtolower($exam_type)])) {
                        $new_exam_type = $exam_type_mappings[strtolower($exam_type)];
                    }
                } else {
                    // Convert Bengali to English
                    if (isset($reverse_mappings[$exam_type])) {
                        $new_exam_type = $reverse_mappings[$exam_type];
                    }
                }
                
                if ($new_exam_type && $new_exam_type != $exam_type) {
                    // Update records
                    $update_stmt = $pdo->prepare("UPDATE $table SET exam_type = ? WHERE exam_type = ?");
                    $affected = $update_stmt->execute([$new_exam_type, $exam_type]);
                    
                    if ($update_stmt->rowCount() > 0) {
                        echo "<p>✅ Updated '$exam_type' → '$new_exam_type' in $table ({$update_stmt->rowCount()} records)</p>";
                        $updates_made += $update_stmt->rowCount();
                    }
                }
            }
        }
        
        $pdo->commit();
        
        echo "<div style='background: #d1edff; padding: 15px; border: 1px solid #0d6efd; border-radius: 5px; margin-top: 15px;'>";
        echo "<h4>✅ Standardization Complete!</h4>";
        echo "<p><strong>Total updates made:</strong> $updates_made records</p>";
        echo "<p><strong>Target language:</strong> " . ucfirst($target_language) . "</p>";
        echo "</div>";
        
    } catch (Exception $e) {
        $pdo->rollBack();
        echo "<div style='background: #f8d7da; padding: 15px; border: 1px solid #dc3545; border-radius: 5px;'>";
        echo "<h4>❌ Error occurred!</h4>";
        echo "<p>Error: " . $e->getMessage() . "</p>";
        echo "</div>";
    }
    
    echo "</div>";
}

// Show current status
echo "<h2>📊 Current Exam Types Status:</h2>";

$tables = ['results', 'subject_mark_distribution', 'mark_distributions'];
foreach ($tables as $table) {
    echo "<h3>📋 Table: $table</h3>";
    
    $stmt = $pdo->query("
        SELECT exam_type, COUNT(*) as count
        FROM $table 
        GROUP BY exam_type 
        ORDER BY count DESC
    ");
    $exam_types = $stmt->fetchAll();
    
    if (!empty($exam_types)) {
        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><th>Mapping Available</th>";
        echo "</tr>";
        
        foreach ($exam_types as $exam) {
            $language = 'Unknown';
            $mapping_available = 'No';
            
            if (preg_match('/[a-zA-Z]/', $exam['exam_type'])) {
                $language = 'English';
                if (isset($exam_type_mappings[strtolower($exam['exam_type'])])) {
                    $mapping_available = 'Yes → ' . $exam_type_mappings[strtolower($exam['exam_type'])];
                }
            } elseif (preg_match('/[\x{0980}-\x{09FF}]/u', $exam['exam_type'])) {
                $language = 'Bengali';
                if (isset($reverse_mappings[$exam['exam_type']])) {
                    $mapping_available = 'Yes → ' . $reverse_mappings[$exam['exam_type']];
                }
            }
            
            $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 "<td>$mapping_available</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p>No exam types found in this table.</p>";
    }
}

// Standardization form
echo "<hr>";
echo "<h2>🛠️ Standardization Tool:</h2>";

echo "<form method='POST' style='background: #f8f9fa; padding: 20px; border-radius: 5px; margin-bottom: 20px;'>";
echo "<div style='margin-bottom: 15px;'>";
echo "<h4>Choose Target Language:</h4>";
echo "<label style='margin-right: 20px;'>";
echo "<input type='radio' name='target_language' value='bengali' " . ($target_language == 'bengali' ? 'checked' : '') . "> Bengali (বাংলা)";
echo "</label>";
echo "<label>";
echo "<input type='radio' name='target_language' value='english' " . ($target_language == 'english' ? 'checked' : '') . "> English";
echo "</label>";
echo "</div>";

echo "<div style='background: #fff3cd; padding: 15px; border: 1px solid #ffc107; border-radius: 5px; margin-bottom: 15px;'>";
echo "<h5>⚠️ Warning:</h5>";
echo "<p>This will update ALL exam types in the database. Make sure to backup your data first!</p>";
echo "<p><strong>Available Mappings:</strong></p>";
echo "<ul style='columns: 2; font-size: 12px;'>";
foreach ($exam_type_mappings as $eng => $ben) {
    echo "<li>$eng ↔ $ben</li>";
}
echo "</ul>";
echo "</div>";

echo "<button type='submit' name='action' value='standardize' class='btn btn-warning' onclick='return confirm(\"Are you sure you want to standardize all exam types? This cannot be undone easily!\")'>🔄 Standardize Exam Types</button>";
echo "</form>";

// Test links for student 1647
echo "<hr>";
echo "<h2>🧪 Test Student 1647 with Different Exam Types:</h2>";

$stmt = $pdo->prepare("
    SELECT DISTINCT exam_type, COUNT(*) as count
    FROM results 
    WHERE student_id = ? AND session = '2024'
    GROUP BY exam_type
");
$stmt->execute([1647]);
$student_exam_types = $stmt->fetchAll();

if (!empty($student_exam_types)) {
    echo "<div style='background: #d1edff; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h4>📊 Student 1647 has results for:</h4>";
    foreach ($student_exam_types as $exam) {
        $exam_type = $exam['exam_type'];
        echo "<div style='margin: 10px 0; padding: 10px; background: white; border-radius: 3px;'>";
        echo "<strong>$exam_type</strong> ({$exam['count']} subjects) - ";
        echo "<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; margin: 2px;'>Debug</a> ";
        echo "<a href='reports/marksheet.php?student_id=1647&session=2024&exam_type=" . urlencode($exam_type) . "' style='background: #28a745; color: white; padding: 5px 10px; text-decoration: none; border-radius: 3px; margin: 2px;'>View</a>";
        echo "</div>";
    }
    echo "</div>";
} else {
    echo "<div style='background: #f8d7da; padding: 15px; border: 1px solid #dc3545; border-radius: 5px;'>";
    echo "<p>❌ Student 1647 has no results in 2024.</p>";
    echo "</div>";
}

echo "<div style='text-align: center; margin: 30px 0;'>";
echo "<a href='check_exam_types.php' style='background: #17a2b8; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; margin: 5px;'>🔍 Check Exam Types</a>";
echo "<a href='debug_marksheet_form.php' style='background: #6f42c1; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; margin: 5px;'>🧪 Debug Marksheet</a>";
echo "</div>";

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