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

echo "<h2>Database Structure Check</h2>";

// Check if student is logged in
if (!isset($_SESSION['user_id'])) {
    echo "<p style='color:red;'>Please log in first</p>";
    exit;
}

$student_id = $_SESSION['user_id'];
$school_id = $_SESSION['school_id'];

echo "<p><strong>Student ID:</strong> {$student_id}</p>";
echo "<p><strong>School ID:</strong> {$school_id}</p>";

// Check users table
echo "<h3>1. Student Information (users table)</h3>";
$sql = "SELECT id, first_name, last_name, class_id, stream_id, ADM FROM users WHERE id = ? AND school_id = ?";
if ($stmt = $conn->prepare($sql)) {
    $stmt->bind_param("ii", $student_id, $school_id);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        echo "<table border='1' style='border-collapse:collapse;'>";
        foreach ($row as $key => $value) {
            echo "<tr><td><strong>{$key}</strong></td><td>{$value}</td></tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color:red;'>No student record found</p>";
    }
    $stmt->close();
}

// Check marks table
echo "<h3>2. Marks Records for Student</h3>";
$sql = "SELECT COUNT(*) as total FROM marks WHERE student_id = ? AND school_id = ?";
if ($stmt = $conn->prepare($sql)) {
    $stmt->bind_param("ii", $student_id, $school_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    echo "<p><strong>Total marks records:</strong> {$row['total']}</p>";
    $stmt->close();
}

// Get sample marks
echo "<h3>3. Sample Marks Data</h3>";
$sql = "SELECT m.mark, m.academic_year, m.term, m.exam_id, s.subject_name, m.subject_id 
        FROM marks m 
        LEFT JOIN subjects s ON m.subject_id = s.subject_id 
        WHERE m.student_id = ? AND m.school_id = ? 
        LIMIT 5";
if ($stmt = $conn->prepare($sql)) {
    $stmt->bind_param("ii", $student_id, $school_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows > 0) {
        echo "<table border='1' style='border-collapse:collapse;'>";
        echo "<tr><th>Subject</th><th>Mark</th><th>Academic Year</th><th>Term</th><th>Exam ID</th></tr>";
        while ($row = $result->fetch_assoc()) {
            echo "<tr>";
            echo "<td>" . htmlspecialchars($row['subject_name'] ?? 'Unknown Subject') . "</td>";
            echo "<td>" . htmlspecialchars($row['mark']) . "</td>";
            echo "<td>" . htmlspecialchars($row['academic_year']) . "</td>";
            echo "<td>" . htmlspecialchars($row['term']) . "</td>";
            echo "<td>" . htmlspecialchars($row['exam_id']) . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color:red;'>No marks records found for this student</p>";
    }
    $stmt->close();
}

// Check available academic years
echo "<h3>4. Available Academic Years & Terms</h3>";
$sql = "SELECT DISTINCT academic_year, term, COUNT(*) as records 
        FROM marks 
        WHERE student_id = ? AND school_id = ? 
        GROUP BY academic_year, term 
        ORDER BY academic_year DESC, term DESC";
if ($stmt = $conn->prepare($sql)) {
    $stmt->bind_param("ii", $student_id, $school_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows > 0) {
        echo "<table border='1' style='border-collapse:collapse;'>";
        echo "<tr><th>Academic Year</th><th>Term</th><th>Records Count</th></tr>";
        while ($row = $result->fetch_assoc()) {
            echo "<tr>";
            echo "<td>" . htmlspecialchars($row['academic_year']) . "</td>";
            echo "<td>" . htmlspecialchars($row['term']) . "</td>";
            echo "<td>" . $row['records'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color:red;'>No academic data found</p>";
    }
    $stmt->close();
}

// Check subjects
echo "<h3>5. Available Subjects</h3>";
$sql = "SELECT subject_id, subject_name FROM subjects WHERE school_id = ? ORDER BY subject_name";
if ($stmt = $conn->prepare($sql)) {
    $stmt->bind_param("i", $school_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows > 0) {
        echo "<table border='1' style='border-collapse:collapse;'>";
        echo "<tr><th>Subject ID</th><th>Subject Name</th></tr>";
        while ($row = $result->fetch_assoc()) {
            echo "<tr>";
            echo "<td>" . $row['subject_id'] . "</td>";
            echo "<td>" . htmlspecialchars($row['subject_name']) . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color:red;'>No subjects found</p>";
    }
    $stmt->close();
}

// Check grading scales
echo "<h3>6. Grading Scales</h3>";
$sql = "SELECT academic_year, term_name, grade_letter, min_score, max_score, remarks 
        FROM grading_scales 
        WHERE school_id = ? 
        ORDER BY academic_year DESC, term_name, min_score 
        LIMIT 10";
if ($stmt = $conn->prepare($sql)) {
    $stmt->bind_param("i", $school_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows > 0) {
        echo "<table border='1' style='border-collapse:collapse;'>";
        echo "<tr><th>Academic Year</th><th>Term</th><th>Grade</th><th>Min Score</th><th>Max Score</th><th>Remarks</th></tr>";
        while ($row = $result->fetch_assoc()) {
            echo "<tr>";
            echo "<td>" . htmlspecialchars($row['academic_year']) . "</td>";
            echo "<td>" . htmlspecialchars($row['term_name']) . "</td>";
            echo "<td>" . htmlspecialchars($row['grade_letter']) . "</td>";
            echo "<td>" . $row['min_score'] . "</td>";
            echo "<td>" . $row['max_score'] . "</td>";
            echo "<td>" . htmlspecialchars($row['remarks']) . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color:red;'>No grading scales found</p>";
    }
    $stmt->close();
}

echo "<hr>";
echo "<h3>Summary</h3>";
echo "<p><strong>Next Steps:</strong></p>";
echo "<ul>";
echo "<li>If marks records = 0: Add sample marks data</li>";
echo "<li>If academic years are missing: Check data entry</li>";
echo "<li>If subjects are missing: Add subjects in admin panel</li>";
echo "<li>If grading scales are missing: Configure grading scales</li>";
echo "</ul>";

$conn->close();
?>