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

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

$student_id = $_GET['student_id'] ?? '1647';
$session = $_GET['session'] ?? '2024';
$exam_type = $_GET['exam_type'] ?? 'মধ্যবর্ষিক';

echo "<h1>🔧 Fix Duplicate Marksheet Issue</h1>";
echo "<p><strong>Testing Student ID:</strong> $student_id</p>";
echo "<p><strong>Session:</strong> $session</p>";
echo "<p><strong>Exam Type:</strong> $exam_type</p>";

// Step 1: Check raw data
echo "<h2>🔍 Step 1: Raw Database Check</h2>";
$stmt = $pdo->prepare("
    SELECT r.*, sub.name as subject_name, sub.code as subject_code
    FROM results r
    JOIN subjects sub ON r.subject_id = sub.id
    WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ?
    ORDER BY r.id
");
$stmt->execute([$student_id, $session, $exam_type]);
$raw_results = $stmt->fetchAll();

echo "<p><strong>Raw Results Found:</strong> " . count($raw_results) . "</p>";

if (empty($raw_results)) {
    echo "<div style='background: #f8d7da; padding: 15px; border: 1px solid #dc3545; border-radius: 5px;'>";
    echo "<h3>❌ No Results Found!</h3>";
    echo "<p>No results found for Student ID: $student_id, Session: $session, Exam Type: $exam_type</p>";
    
    // Try different exam types
    echo "<h4>🔍 Trying Different Exam Types:</h4>";
    $stmt = $pdo->prepare("
        SELECT DISTINCT exam_type, COUNT(*) as count
        FROM results 
        WHERE student_id = ? AND session = ?
        GROUP BY exam_type
    ");
    $stmt->execute([$student_id, $session]);
    $available_exam_types = $stmt->fetchAll();
    
    if (!empty($available_exam_types)) {
        echo "<p><strong>Available exam types for this student:</strong></p>";
        foreach ($available_exam_types as $exam) {
            echo "<p><a href='?student_id=$student_id&session=$session&exam_type=" . urlencode($exam['exam_type']) . "' style='background: #007bff; color: white; padding: 5px 10px; text-decoration: none; border-radius: 3px;'>{$exam['exam_type']} ({$exam['count']} subjects)</a></p>";
        }
    } else {
        echo "<p>❌ No results found for this student in $session</p>";
        
        // Try different student IDs
        echo "<h4>🔍 Available Students with Results:</h4>";
        $stmt = $pdo->query("
            SELECT DISTINCT s.id, s.student_id, s.first_name, s.last_name, COUNT(r.id) as result_count
            FROM students s
            JOIN results r ON s.id = r.student_id
            WHERE r.session = '2024'
            GROUP BY s.id
            ORDER BY s.id
            LIMIT 10
        ");
        $available_students = $stmt->fetchAll();
        
        foreach ($available_students as $student) {
            echo "<p><a href='?student_id={$student['id']}&session=2024&exam_type=মধ্যবর্ষিক' style='background: #28a745; color: white; padding: 5px 10px; text-decoration: none; border-radius: 3px;'>ID: {$student['id']} - {$student['first_name']} {$student['last_name']} ({$student['result_count']} results)</a></p>";
        }
    }
    echo "</div>";
    exit();
}

// Display raw results
echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
echo "<tr style='background: #f8f9fa;'>";
echo "<th>Result ID</th><th>Subject ID</th><th>Subject Name</th><th>CQ</th><th>MCQ</th><th>Practical</th><th>Total</th><th>Grade</th>";
echo "</tr>";

$subject_name_counts = [];
$subject_id_counts = [];

foreach ($raw_results as $result) {
    $subject_name = $result['subject_name'];
    $subject_id = $result['subject_id'];
    
    // Count occurrences
    if (!isset($subject_name_counts[$subject_name])) {
        $subject_name_counts[$subject_name] = 0;
    }
    $subject_name_counts[$subject_name]++;
    
    if (!isset($subject_id_counts[$subject_id])) {
        $subject_id_counts[$subject_id] = 0;
    }
    $subject_id_counts[$subject_id]++;
    
    // Highlight duplicates
    $row_style = '';
    if ($subject_name_counts[$subject_name] > 1) {
        $row_style = 'style="background: #ffeeee;"'; // Red for name duplicates
    } elseif ($subject_id_counts[$subject_id] > 1) {
        $row_style = 'style="background: #fff3cd;"'; // Yellow for ID duplicates
    }
    
    echo "<tr $row_style>";
    echo "<td>{$result['id']}</td>";
    echo "<td>{$result['subject_id']}</td>";
    echo "<td><strong>{$result['subject_name']}</strong></td>";
    echo "<td>{$result['cq_marks']}</td>";
    echo "<td>{$result['mcq_marks']}</td>";
    echo "<td>{$result['practical_marks']}</td>";
    echo "<td>{$result['marks_obtained']}</td>";
    echo "<td>{$result['grade']}</td>";
    echo "</tr>";
}
echo "</table>";

// Analysis
echo "<h2>📊 Step 2: Duplicate Analysis</h2>";

$duplicates_found = false;
$duplicate_names = [];
$duplicate_ids = [];

foreach ($subject_name_counts as $name => $count) {
    if ($count > 1) {
        $duplicates_found = true;
        $duplicate_names[] = "$name ($count times)";
    }
}

foreach ($subject_id_counts as $id => $count) {
    if ($count > 1) {
        $duplicates_found = true;
        $duplicate_ids[] = "$id ($count times)";
    }
}

if ($duplicates_found) {
    echo "<div style='background: #f8d7da; padding: 15px; border: 1px solid #dc3545; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>⚠️ Duplicates Found!</h3>";
    if (!empty($duplicate_names)) {
        echo "<p><strong>Duplicate Subject Names:</strong> " . implode(', ', $duplicate_names) . "</p>";
    }
    if (!empty($duplicate_ids)) {
        echo "<p><strong>Duplicate Subject IDs:</strong> " . implode(', ', $duplicate_ids) . "</p>";
    }
    echo "</div>";
} else {
    echo "<div style='background: #d1edff; padding: 15px; border: 1px solid #0d6efd; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>✅ No Duplicates in Raw Data!</h3>";
    echo "<p>The issue might be in the display logic, not the data.</p>";
    echo "</div>";
}

// Step 3: Apply GUARANTEED fix
echo "<h2>🔧 Step 3: Apply Guaranteed Fix</h2>";

// Method 1: Latest entry per subject name
$fixed_results_method1 = [];
$latest_by_name = [];

foreach ($raw_results as $result) {
    $subject_name = $result['subject_name'];
    
    if (!isset($latest_by_name[$subject_name]) || $result['id'] > $latest_by_name[$subject_name]['id']) {
        $latest_by_name[$subject_name] = $result;
    }
}

$fixed_results_method1 = array_values($latest_by_name);
usort($fixed_results_method1, function($a, $b) {
    return strcmp($a['subject_name'], $b['subject_name']);
});

echo "<h4>Method 1: Latest Entry Per Subject Name</h4>";
echo "<p><strong>Results:</strong> " . count($fixed_results_method1) . " unique subjects</p>";

echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
echo "<tr style='background: #d1edff;'>";
echo "<th>Subject Name</th><th>CQ</th><th>MCQ</th><th>Practical</th><th>Total</th><th>Grade</th>";
echo "</tr>";

foreach ($fixed_results_method1 as $result) {
    echo "<tr>";
    echo "<td><strong>{$result['subject_name']}</strong></td>";
    echo "<td>{$result['cq_marks']}</td>";
    echo "<td>{$result['mcq_marks']}</td>";
    echo "<td>{$result['practical_marks']}</td>";
    echo "<td>{$result['marks_obtained']}</td>";
    echo "<td>{$result['grade']}</td>";
    echo "</tr>";
}
echo "</table>";

// Check if Bangla is present
$bangla_found = false;
foreach ($fixed_results_method1 as $result) {
    if (stripos($result['subject_name'], 'বাংলা') !== false || stripos($result['subject_name'], 'bangla') !== false) {
        $bangla_found = true;
        break;
    }
}

if ($bangla_found) {
    echo "<div style='background: #d1edff; padding: 10px; border: 1px solid #0d6efd; border-radius: 5px; margin-bottom: 15px;'>";
    echo "✅ <strong>বাংলা বিষয় পাওয়া গেছে!</strong>";
    echo "</div>";
} else {
    echo "<div style='background: #f8d7da; padding: 10px; border: 1px solid #dc3545; border-radius: 5px; margin-bottom: 15px;'>";
    echo "❌ <strong>বাংলা বিষয় এখনো নেই!</strong>";
    echo "</div>";
}

// Step 4: Create a working marksheet
echo "<h2>📄 Step 4: Working Marksheet Preview</h2>";

echo "<div style='border: 2px solid #000; padding: 20px; background: white; margin: 20px 0;'>";
echo "<div style='text-align: center; border-bottom: 2px solid #000; padding-bottom: 15px; margin-bottom: 20px;'>";
echo "<h3>বিদ্যা নিকেতন উচ্চ বিদ্যালয়</h3>";
echo "<h4 style='text-decoration: underline;'>একাডেমিক ট্রান্সক্রিপ্ট</h4>";
echo "</div>";

// Get student info
$stmt = $pdo->prepare("SELECT s.*, c.name as class_name FROM students s JOIN classes c ON s.class_id = c.id WHERE s.id = ?");
$stmt->execute([$student_id]);
$student = $stmt->fetch();

if ($student) {
    echo "<div style='display: grid; grid-template-columns: 1fr 1fr; gap: 30px; margin-bottom: 20px;'>";
    echo "<div>";
    echo "<p><strong>নাম:</strong> {$student['first_name']} {$student['last_name']}</p>";
    echo "<p><strong>পিতার নাম:</strong> {$student['father_name']}</p>";
    echo "</div>";
    echo "<div>";
    echo "<p><strong>রোল নং:</strong> {$student['student_id']}</p>";
    echo "<p><strong>শ্রেণি:</strong> {$student['class_name']}</p>";
    echo "</div>";
    echo "</div>";
}

echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
echo "<tr style='background: #f8f9fa;'>";
echo "<th>ক্রমিক</th><th>বিষয়</th><th>সৃজনশীল</th><th>বহুনির্বাচনি</th><th>ব্যবহারিক</th><th>মোট নম্বর</th><th>গ্রেড</th>";
echo "</tr>";

$serial = 1;
$total_marks = 0;
foreach ($fixed_results_method1 as $result) {
    echo "<tr>";
    echo "<td>$serial</td>";
    echo "<td style='text-align: left; padding-left: 10px;'>{$result['subject_name']}</td>";
    echo "<td>{$result['cq_marks']}</td>";
    echo "<td>{$result['mcq_marks']}</td>";
    echo "<td>{$result['practical_marks']}</td>";
    echo "<td><strong>{$result['marks_obtained']}</strong></td>";
    echo "<td><strong>{$result['grade']}</strong></td>";
    echo "</tr>";
    $serial++;
    $total_marks += $result['marks_obtained'];
}

echo "<tr style='background: #f8f9fa; font-weight: bold;'>";
echo "<td colspan='5'>মোট</td>";
echo "<td>$total_marks</td>";
echo "<td>-</td>";
echo "</tr>";
echo "</table>";
echo "</div>";

// Action buttons
echo "<div style='text-align: center; margin: 30px 0;'>";
echo "<a href='reports/marksheet.php?student_id=$student_id&session=$session&exam_type=" . urlencode($exam_type) . "&debug=1' style='background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; margin: 5px;'>🔍 Test Original Marksheet (Debug)</a>";
echo "<a href='reports/marksheet.php?student_id=$student_id&session=$session&exam_type=" . urlencode($exam_type) . "' style='background: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; margin: 5px;'>📄 Test Original Marksheet</a>";
echo "</div>";

// Quick fix form
echo "<hr>";
echo "<h2>🧪 Quick Test Form</h2>";
echo "<form method='GET' style='background: #f8f9fa; padding: 20px; border-radius: 5px;'>";
echo "<div style='display: grid; grid-template-columns: 1fr 1fr 1fr 1fr; gap: 15px; align-items: end;'>";
echo "<div>";
echo "<label>Student ID:</label>";
echo "<input type='number' name='student_id' value='$student_id' class='form-control'>";
echo "</div>";
echo "<div>";
echo "<label>Session:</label>";
echo "<input type='text' name='session' value='$session' class='form-control'>";
echo "</div>";
echo "<div>";
echo "<label>Exam Type:</label>";
echo "<input type='text' name='exam_type' value='$exam_type' class='form-control'>";
echo "</div>";
echo "<div>";
echo "<button type='submit' class='btn btn-primary'>🔧 Test Fix</button>";
echo "</div>";
echo "</div>";
echo "</form>";

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