<?php
require_once 'config/database.php';

echo "<h2>Debug: Subject Mark Distribution</h2>";

// Check if table exists
try {
    $result = $pdo->query("SHOW TABLES LIKE 'subject_mark_distribution'");
    if ($result->rowCount() > 0) {
        echo "<p style='color: green;'>✓ subject_mark_distribution table exists</p>";
    } else {
        echo "<p style='color: red;'>✗ subject_mark_distribution table does not exist</p>";
        exit;
    }
} catch (Exception $e) {
    echo "<p style='color: red;'>Error checking table: " . $e->getMessage() . "</p>";
    exit;
}

// Show table structure
echo "<h3>Table Structure:</h3>";
try {
    $result = $pdo->query("DESCRIBE subject_mark_distribution");
    echo "<table border='1' style='border-collapse: collapse;'>";
    echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
    while ($row = $result->fetch()) {
        echo "<tr>";
        echo "<td>" . $row['Field'] . "</td>";
        echo "<td>" . $row['Type'] . "</td>";
        echo "<td>" . $row['Null'] . "</td>";
        echo "<td>" . $row['Key'] . "</td>";
        echo "<td>" . $row['Default'] . "</td>";
        echo "<td>" . $row['Extra'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} catch (Exception $e) {
    echo "<p style='color: red;'>Error getting table structure: " . $e->getMessage() . "</p>";
}

// Check for specific record
echo "<h3>Checking for specific record:</h3>";
echo "<p>Looking for: subject_id=2, class_id=11, session=2024, exam_type=midterm, group_name=''</p>";

try {
    $stmt = $pdo->prepare("
        SELECT * FROM subject_mark_distribution 
        WHERE subject_id = ? AND class_id = ? AND session = ? AND exam_type = ?
        AND (group_name = ? OR (group_name IS NULL AND ? = ''))
    ");
    $stmt->execute([2, 11, '2024', 'midterm', '', '']);
    $record = $stmt->fetch();
    
    if ($record) {
        echo "<p style='color: green;'>✓ Record found!</p>";
        echo "<pre>";
        print_r($record);
        echo "</pre>";
    } else {
        echo "<p style='color: red;'>✗ Record not found</p>";
    }
} catch (Exception $e) {
    echo "<p style='color: red;'>Error searching record: " . $e->getMessage() . "</p>";
}

// Show all records
echo "<h3>All Records in subject_mark_distribution:</h3>";
try {
    $result = $pdo->query("SELECT * FROM subject_mark_distribution ORDER BY id DESC LIMIT 10");
    $records = $result->fetchAll();
    
    if (count($records) > 0) {
        echo "<table border='1' style='border-collapse: collapse;'>";
        echo "<tr><th>ID</th><th>Subject ID</th><th>Class ID</th><th>Session</th><th>Group Name</th><th>Exam Type</th><th>Total Marks</th></tr>";
        foreach ($records as $record) {
            echo "<tr>";
            echo "<td>" . $record['id'] . "</td>";
            echo "<td>" . $record['subject_id'] . "</td>";
            echo "<td>" . $record['class_id'] . "</td>";
            echo "<td>" . $record['session'] . "</td>";
            echo "<td>" . ($record['group_name'] ?? 'NULL') . "</td>";
            echo "<td>" . $record['exam_type'] . "</td>";
            echo "<td>" . $record['total_marks'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color: orange;'>No records found in subject_mark_distribution table</p>";
    }
} catch (Exception $e) {
    echo "<p style='color: red;'>Error getting records: " . $e->getMessage() . "</p>";
}

// Check subjects table
echo "<h3>Available Subjects:</h3>";
try {
    $result = $pdo->query("SELECT id, name, code FROM subjects WHERE status = 'active' ORDER BY name");
    $subjects = $result->fetchAll();
    
    if (count($subjects) > 0) {
        echo "<table border='1' style='border-collapse: collapse;'>";
        echo "<tr><th>ID</th><th>Name</th><th>Code</th></tr>";
        foreach ($subjects as $subject) {
            echo "<tr>";
            echo "<td>" . $subject['id'] . "</td>";
            echo "<td>" . $subject['name'] . "</td>";
            echo "<td>" . $subject['code'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color: orange;'>No active subjects found</p>";
    }
} catch (Exception $e) {
    echo "<p style='color: red;'>Error getting subjects: " . $e->getMessage() . "</p>";
}

// Check classes table
echo "<h3>Available Classes:</h3>";
try {
    $result = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name, section");
    $classes = $result->fetchAll();
    
    if (count($classes) > 0) {
        echo "<table border='1' style='border-collapse: collapse;'>";
        echo "<tr><th>ID</th><th>Name</th><th>Section</th></tr>";
        foreach ($classes as $class) {
            echo "<tr>";
            echo "<td>" . $class['id'] . "</td>";
            echo "<td>" . $class['name'] . "</td>";
            echo "<td>" . $class['section'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color: orange;'>No active classes found</p>";
    }
} catch (Exception $e) {
    echo "<p style='color: red;'>Error getting classes: " . $e->getMessage() . "</p>";
}
?>