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

echo "<h3>ডাটাবেস Constraint চেক</h3>";

try {
    // Check foreign key checks status
    $stmt = $pdo->query("SELECT @@foreign_key_checks");
    $fk_status = $stmt->fetchColumn();
    echo "<p>Foreign Key Checks: " . ($fk_status ? 'Enabled' : 'Disabled') . "</p>";
    
    // Check MySQL version
    $stmt = $pdo->query("SELECT VERSION()");
    $version = $stmt->fetchColumn();
    echo "<p>MySQL Version: " . $version . "</p>";
    
    // Check constraints related to students table (simple version)
    $stmt = $pdo->query("
        SELECT 
            TABLE_NAME,
            COLUMN_NAME,
            CONSTRAINT_NAME,
            REFERENCED_TABLE_NAME,
            REFERENCED_COLUMN_NAME
        FROM 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
        WHERE 
            REFERENCED_TABLE_NAME = 'students'
            AND TABLE_SCHEMA = DATABASE()
    ");
    
    $constraints = $stmt->fetchAll();
    
    echo "<h4>Students টেবিলের সাথে সম্পর্কিত Constraints:</h4>";
    if ($constraints) {
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>Table</th><th>Column</th><th>Constraint Name</th></tr>";
        foreach ($constraints as $constraint) {
            echo "<tr>";
            echo "<td>" . $constraint['TABLE_NAME'] . "</td>";
            echo "<td>" . $constraint['COLUMN_NAME'] . "</td>";
            echo "<td>" . $constraint['CONSTRAINT_NAME'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p>কোন constraint পাওয়া যায়নি</p>";
    }
    
    // Check if we can find constraint details from SHOW CREATE TABLE
    echo "<h4>টেবিল Structure চেক:</h4>";
    $tables_to_check = ['attendance', 'results', 'student_subjects', 'fees'];
    
    foreach ($tables_to_check as $table) {
        try {
            $stmt = $pdo->query("SHOW CREATE TABLE `$table`");
            $result = $stmt->fetch();
            if ($result) {
                echo "<h5>$table টেবিল:</h5>";
                $create_sql = $result['Create Table'];
                // Look for foreign key constraints
                if (strpos($create_sql, 'student_id') !== false) {
                    if (strpos($create_sql, 'ON DELETE CASCADE') !== false) {
                        echo "<p style='color: green;'>✓ CASCADE DELETE আছে</p>";
                    } else if (strpos($create_sql, 'ON DELETE') !== false) {
                        echo "<p style='color: orange;'>⚠ অন্য DELETE rule আছে</p>";
                    } else {
                        echo "<p style='color: red;'>✗ কোন DELETE rule নেই</p>";
                    }
                }
            }
        } catch (Exception $e) {
            echo "<p>$table চেক করতে সমস্যা: " . $e->getMessage() . "</p>";
        }
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>এরর: " . $e->getMessage() . "</p>";
}
?>