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

// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<h2>Exam Routine Delete Issue Debug</h2>";

// Check if we have any exams
echo "<h3>Current Exams in Database:</h3>";
$stmt = $pdo->query("SELECT * FROM exams");
$exams = $stmt->fetchAll();

if (empty($exams)) {
    echo "<p>No exams found in database</p>";
} else {
    echo "<ul>";
    foreach ($exams as $exam) {
        echo "<li>ID: {$exam['id']} - Name: {$exam['name']} - Class ID: {$exam['class_id']}</li>";
        
        // Check related exam subjects
        $stmt2 = $pdo->prepare("SELECT * FROM exam_subjects WHERE exam_id = ?");
        $stmt2->execute([$exam['id']]);
        $subjects = $stmt2->fetchAll();
        
        echo "<ul>Subjects: ";
        if (empty($subjects)) {
            echo "<li>None</li>";
        } else {
            foreach ($subjects as $subject) {
                echo "<li>{$subject['id']} - Subject ID: {$subject['subject_id']} - Date: {$subject['exam_date']}</li>";
            }
        }
        echo "</ul>";
    }
    echo "</ul>";
}

// Check table structures
echo "<h3>Table Structures:</h3>";

// Check exams table structure
echo "<h4>Exams Table:</h4>";
$stmt = $pdo->query("SHOW CREATE TABLE exams");
$result = $stmt->fetch();
echo "<pre>" . htmlspecialchars($result['Create Table']) . "</pre>";

// Check exam_subjects table structure
echo "<h4>Exam Subjects Table:</h4>";
$stmt = $pdo->query("SHOW CREATE TABLE exam_subjects");
$result = $stmt->fetch();
echo "<pre>" . htmlspecialchars($result['Create Table']) . "</pre>";

// Check foreign key constraints
echo "<h4>Foreign Key Constraints:</h4>";
$stmt = $pdo->query("SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_NAME IS NOT NULL 
AND TABLE_SCHEMA = 'school_management'
AND (TABLE_NAME = 'exam_subjects' OR REFERENCED_TABLE_NAME = 'exams')
ORDER BY TABLE_NAME, CONSTRAINT_NAME");
$fks = $stmt->fetchAll();

if (empty($fks)) {
    echo "<p>No foreign key constraints found</p>";
} else {
    echo "<ul>";
    foreach ($fks as $fk) {
        echo "<li>{$fk['CONSTRAINT_NAME']}: {$fk['TABLE_NAME']}.{$fk['COLUMN_NAME']} -> {$fk['REFERENCED_TABLE_NAME']}.{$fk['REFERENCED_COLUMN_NAME']}</li>";
    }
    echo "</ul>";
}

// Test deletion manually
if (isset($_GET['test_delete']) && isset($_GET['id'])) {
    $test_id = (int)$_GET['id'];
    echo "<h3>Testing Manual Deletion of Exam ID: $test_id</h3>";
    
    try {
        $pdo->beginTransaction();
        
        // Check if exam exists
        $stmt = $pdo->prepare("SELECT * FROM exams WHERE id = ?");
        $stmt->execute([$test_id]);
        $exam = $stmt->fetch();
        
        if (!$exam) {
            echo "<p style='color: red;'>Exam with ID $test_id not found</p>";
        } else {
            echo "<p>Found exam: {$exam['name']}</p>";
            
            // Delete results first (if any)
            echo "<p>Deleting results...</p>";
            $stmt = $pdo->prepare("DELETE FROM results WHERE exam_id = ?");
            $stmt->execute([$test_id]);
            echo "<p>Deleted {$stmt->rowCount()} results</p>";
            
            // Delete exam subjects
            echo "<p>Deleting exam subjects...</p>";
            $stmt = $pdo->prepare("DELETE FROM exam_subjects WHERE exam_id = ?");
            $stmt->execute([$test_id]);
            echo "<p>Deleted {$stmt->rowCount()} exam subjects</p>";
            
            // Delete the exam
            echo "<p>Deleting exam...</p>";
            $stmt = $pdo->prepare("DELETE FROM exams WHERE id = ?");
            $stmt->execute([$test_id]);
            echo "<p>Deleted {$stmt->rowCount()} exams</p>";
            
            $pdo->commit();
            echo "<p style='color: green;'>Successfully deleted exam ID: $test_id</p>";
        }
    } catch (Exception $e) {
        $pdo->rollBack();
        echo "<p style='color: red;'>Error deleting exam: " . $e->getMessage() . "</p>";
    }
    
    echo "<p><a href='?'>Refresh</a></p>";
}
?>