<?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'] ?? '';
$session = $_GET['session'] ?? '';
$exam_type = $_GET['exam_type'] ?? '';

if (!$student_id || !$session || !$exam_type) {
    die('Please provide student_id, session, and exam_type parameters');
}

echo "<h1>🔍 Compare Tabulation Sheet vs Marksheet Data</h1>";
echo "<p><strong>Student ID:</strong> $student_id</p>";
echo "<p><strong>Session:</strong> $session</p>";
echo "<p><strong>Exam Type:</strong> $exam_type</p>";

// Get student details
$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='background: #f8d7da; padding: 20px; border: 1px solid #dc3545; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>❌ Student Not Found!</h3>";
    echo "<p>No student found with ID: <strong>$student_id</strong></p>";
    echo "<p>This could be because:</p>";
    echo "<ul>";
    echo "<li>The student ID doesn't exist in the database</li>";
    echo "<li>The student has been deleted or deactivated</li>";
    echo "<li>There's a typo in the student ID</li>";
    echo "</ul>";
    echo "</div>";
    
    // Show available students
    echo "<h3>📋 Available Students (with 2024 midterm results):</h3>";
    $stmt = $pdo->query("
        SELECT DISTINCT s.id, s.student_id, s.first_name, s.last_name, c.name as class_name
        FROM students s
        JOIN classes c ON s.class_id = c.id
        JOIN results r ON s.id = r.student_id
        WHERE r.session = '2024' AND r.exam_type = 'midterm'
        ORDER BY s.id
        LIMIT 15
    ");
    $available_students = $stmt->fetchAll();
    
    if (!empty($available_students)) {
        echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
        echo "<tr style='background: #f8f9fa;'>";
        echo "<th>Database ID</th><th>Student ID</th><th>Name</th><th>Class</th><th>Action</th>";
        echo "</tr>";
        
        foreach ($available_students as $avail_student) {
            echo "<tr>";
            echo "<td><strong>{$avail_student['id']}</strong></td>";
            echo "<td>{$avail_student['student_id']}</td>";
            echo "<td>{$avail_student['first_name']} {$avail_student['last_name']}</td>";
            echo "<td>{$avail_student['class_name']}</td>";
            echo "<td><a href='?student_id={$avail_student['id']}&session=$session&exam_type=$exam_type' style='background: #007bff; color: white; padding: 5px 10px; text-decoration: none; border-radius: 3px; font-size: 12px;'>Compare</a></td>";
            echo "</tr>";
        }
        echo "</table>";
        
        echo "<div style='background: #d1ecf1; padding: 15px; border: 1px solid #bee5eb; border-radius: 5px;'>";
        echo "<h4>💡 How to use:</h4>";
        echo "<p>Click 'Compare' next to any student to see the comparison for that student.</p>";
        echo "<p><strong>Note:</strong> Use the <strong>Database ID</strong> (first column) in the URL, not the Student ID.</p>";
        echo "</div>";
    } else {
        echo "<div style='background: #f8d7da; padding: 15px; border: 1px solid #dc3545; border-radius: 5px;'>";
        echo "<p>No students found with results for 2024 midterm exam.</p>";
        echo "</div>";
    }
    
    echo "<p><a href='reports/marksheet.php'>← Back to Marksheet</a></p>";
    exit();
}

echo "<p><strong>Student:</strong> {$student['first_name']} {$student['last_name']} - Class: {$student['class_name']} - Group: {$student['group_name']}</p>";
echo "<hr>";

// Method 1: Tabulation Sheet Approach (Simple Query)
echo "<h2>📊 Method 1: Tabulation Sheet Approach</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 sub.name
");
$stmt->execute([$student_id, $session, $exam_type]);
$tabulation_results = $stmt->fetchAll();

echo "<p><strong>Raw Results Found:</strong> " . count($tabulation_results) . "</p>";
echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
echo "<tr style='background: #e3f2fd;'>";
echo "<th>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>";

foreach ($tabulation_results as $result) {
    echo "<tr>";
    echo "<td>{$result['id']}</td>";
    echo "<td>{$result['subject_id']}</td>";
    echo "<td>{$result['subject_name']}</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>";

// Method 2: Marksheet Approach (With Deduplication)
echo "<h2>📄 Method 2: Marksheet Approach (Current)</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 sub.name, r.id DESC
");
$stmt->execute([$student_id, $session, $exam_type]);
$all_marksheet_results = $stmt->fetchAll();

// Remove duplicates by keeping the first occurrence of each subject
$marksheet_results = [];
$seen_subjects = [];

foreach ($all_marksheet_results as $result) {
    $subject_id = $result['subject_id'];
    if (!in_array($subject_id, $seen_subjects)) {
        $marksheet_results[] = $result;
        $seen_subjects[] = $subject_id;
    }
}

echo "<p><strong>Raw Results Found:</strong> " . count($all_marksheet_results) . "</p>";
echo "<p><strong>After Deduplication:</strong> " . count($marksheet_results) . "</p>";

echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
echo "<tr style='background: #f3e5f5;'>";
echo "<th>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>";

foreach ($marksheet_results as $result) {
    echo "<tr>";
    echo "<td>{$result['id']}</td>";
    echo "<td>{$result['subject_id']}</td>";
    echo "<td>{$result['subject_name']}</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>";

// Method 3: Matrix Approach (Like Tabulation Sheet)
echo "<h2>🗂️ Method 3: Matrix Approach (Tabulation Style)</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 = ?
");
$stmt->execute([$student_id, $session, $exam_type]);
$all_results_matrix = $stmt->fetchAll();

// Organize results in matrix format (like tabulation sheet)
$results_matrix = [];
foreach ($all_results_matrix as $result) {
    $results_matrix[$result['subject_id']] = $result;
}

echo "<p><strong>Matrix Results:</strong> " . count($results_matrix) . " unique subjects</p>";
echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
echo "<tr style='background: #e8f5e8;'>";
echo "<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>";

foreach ($results_matrix as $subject_id => $result) {
    echo "<tr>";
    echo "<td>{$subject_id}</td>";
    echo "<td>{$result['subject_name']}</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>";

// Comparison Analysis
echo "<h2>🔍 Comparison Analysis</h2>";

$tabulation_count = count($tabulation_results);
$marksheet_count = count($marksheet_results);
$matrix_count = count($results_matrix);

echo "<div style='display: flex; gap: 20px; margin-bottom: 20px;'>";

echo "<div style='background: #e3f2fd; padding: 15px; border-radius: 5px; flex: 1;'>";
echo "<h4>📊 Tabulation Method</h4>";
echo "<p><strong>Results:</strong> $tabulation_count</p>";
echo "<p><strong>Approach:</strong> Simple query, no deduplication</p>";
echo "<p><strong>Issue:</strong> May show duplicates</p>";
echo "</div>";

echo "<div style='background: #f3e5f5; padding: 15px; border-radius: 5px; flex: 1;'>";
echo "<h4>📄 Marksheet Method</h4>";
echo "<p><strong>Results:</strong> $marksheet_count</p>";
echo "<p><strong>Approach:</strong> Query + PHP deduplication</p>";
echo "<p><strong>Issue:</strong> Complex logic</p>";
echo "</div>";

echo "<div style='background: #e8f5e8; padding: 15px; border-radius: 5px; flex: 1;'>";
echo "<h4>🗂️ Matrix Method</h4>";
echo "<p><strong>Results:</strong> $matrix_count</p>";
echo "<p><strong>Approach:</strong> Array key-based deduplication</p>";
echo "<p><strong>Benefit:</strong> Automatic uniqueness</p>";
echo "</div>";

echo "</div>";

// Recommendations
if ($tabulation_count != $marksheet_count || $marksheet_count != $matrix_count) {
    echo "<div style='background: #fff3cd; padding: 20px; border: 1px solid #ffc107; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>⚠️ Inconsistency Detected!</h3>";
    echo "<p>Different methods are returning different result counts:</p>";
    echo "<ul>";
    echo "<li><strong>Tabulation:</strong> $tabulation_count results</li>";
    echo "<li><strong>Marksheet:</strong> $marksheet_count results</li>";
    echo "<li><strong>Matrix:</strong> $matrix_count results</li>";
    echo "</ul>";
    echo "<p><strong>Recommended Solution:</strong> Use Matrix approach for consistency</p>";
    echo "</div>";
} else {
    echo "<div style='background: #d1edff; padding: 20px; border: 1px solid #0d6efd; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>✅ All Methods Consistent!</h3>";
    echo "<p>All three methods return the same number of results ($tabulation_count).</p>";
    echo "</div>";
}

// Subject-wise comparison
echo "<h2>📚 Subject-wise Comparison</h2>";
$tabulation_subjects = [];
$marksheet_subjects = [];
$matrix_subjects = array_keys($results_matrix);

foreach ($tabulation_results as $r) {
    if (!in_array($r['subject_id'], $tabulation_subjects)) {
        $tabulation_subjects[] = $r['subject_id'];
    }
}

foreach ($marksheet_results as $r) {
    $marksheet_subjects[] = $r['subject_id'];
}

$all_subjects = array_unique(array_merge($tabulation_subjects, $marksheet_subjects, $matrix_subjects));

echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
echo "<tr style='background: #f8f9fa;'>";
echo "<th>Subject ID</th><th>Subject Name</th><th>In Tabulation</th><th>In Marksheet</th><th>In Matrix</th><th>Status</th>";
echo "</tr>";

foreach ($all_subjects as $subject_id) {
    $subject_name = '';
    foreach ($tabulation_results as $r) {
        if ($r['subject_id'] == $subject_id) {
            $subject_name = $r['subject_name'];
            break;
        }
    }
    
    $in_tabulation = in_array($subject_id, $tabulation_subjects) ? '✅' : '❌';
    $in_marksheet = in_array($subject_id, $marksheet_subjects) ? '✅' : '❌';
    $in_matrix = in_array($subject_id, $matrix_subjects) ? '✅' : '❌';
    
    $status = ($in_tabulation == '✅' && $in_marksheet == '✅' && $in_matrix == '✅') ? 
              '<span style="color: green;">✅ Consistent</span>' : 
              '<span style="color: red;">❌ Inconsistent</span>';
    
    echo "<tr>";
    echo "<td>$subject_id</td>";
    echo "<td>$subject_name</td>";
    echo "<td>$in_tabulation</td>";
    echo "<td>$in_marksheet</td>";
    echo "<td>$in_matrix</td>";
    echo "<td>$status</td>";
    echo "</tr>";
}
echo "</table>";

echo "<hr>";
echo "<h2>🛠️ Quick Actions</h2>";
echo "<div style='text-align: center; margin: 20px 0;'>";
echo "<a href='reports/tabulation_sheet.php?class_id={$student['class_id']}&session=$session&exam_type=$exam_type&group_name={$student['group_name']}' style='background: #2196f3; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; margin: 5px;'>📊 View Tabulation Sheet</a>";
echo "<a href='reports/marksheet.php?student_id=$student_id&session=$session&exam_type=$exam_type' style='background: #9c27b0; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; margin: 5px;'>📄 View Marksheet</a>";
echo "<a href='cleanup_duplicate_results.php' style='background: #f44336; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; margin: 5px;'>🧹 Clean Duplicates</a>";
echo "</div>";

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