<?php
// Database connection parameters - update these with your actual credentials
$host = "localhost";     // Your database host
$username = "root";      // Your database username
$password = "";          // Your database password
$database = "rbc";   // Your database name

// Set error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Create connection
try {
    $conn = new PDO("mysql:host=$host;dbname=$database", $username, $password);
    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "<h1>Database Schema for: " . htmlspecialchars($database) . "</h1>";
    
    // Get all tables
    $tables_query = $conn->query("SHOW TABLES");
    $tables = $tables_query->fetchAll(PDO::FETCH_COLUMN);
    
    if (empty($tables)) {
        echo "<p>No tables found in the database.</p>";
    } else {
        echo "<h2>Tables Found: " . count($tables) . "</h2>";
        
        // Loop through each table
        foreach ($tables as $table) {
            echo "<div style='margin-bottom: 30px; padding: 15px; border: 1px solid #ddd; border-radius: 5px;'>";
            echo "<h3>Table: " . htmlspecialchars($table) . "</h3>";
            
            // Get table structure
            $columns_query = $conn->query("DESCRIBE `$table`");
            $columns = $columns_query->fetchAll(PDO::FETCH_ASSOC);
            
            echo "<table style='width: 100%; border-collapse: collapse; margin-bottom: 15px;'>";
            echo "<thead style='background-color: #f3f3f3;'>";
            echo "<tr>";
            echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Column</th>";
            echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Type</th>";
            echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Null</th>";
            echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Key</th>";
            echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Default</th>";
            echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Extra</th>";
            echo "</tr>";
            echo "</thead>";
            echo "<tbody>";
            
            foreach ($columns as $column) {
                echo "<tr>";
                echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($column['Field']) . "</td>";
                echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($column['Type']) . "</td>";
                echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($column['Null']) . "</td>";
                echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($column['Key']) . "</td>";
                echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . (is_null($column['Default']) ? 'NULL' : htmlspecialchars($column['Default'])) . "</td>";
                echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($column['Extra']) . "</td>";
                echo "</tr>";
            }
            
            echo "</tbody>";
            echo "</table>";
            
            // Get indexes
            try {
                $indexes_query = $conn->query("SHOW INDEXES FROM `$table`");
                $indexes = $indexes_query->fetchAll(PDO::FETCH_ASSOC);
                
                if (!empty($indexes)) {
                    echo "<h4>Indexes:</h4>";
                    echo "<table style='width: 100%; border-collapse: collapse;'>";
                    echo "<thead style='background-color: #f3f3f3;'>";
                    echo "<tr>";
                    echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Key Name</th>";
                    echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Column</th>";
                    echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Non Unique</th>";
                    echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Type</th>";
                    echo "</tr>";
                    echo "</thead>";
                    echo "<tbody>";
                    
                    foreach ($indexes as $index) {
                        echo "<tr>";
                        echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($index['Key_name']) . "</td>";
                        echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($index['Column_name']) . "</td>";
                        echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($index['Non_unique']) . "</td>";
                        echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($index['Index_type']) . "</td>";
                        echo "</tr>";
                    }
                    
                    echo "</tbody>";
                    echo "</table>";
                }
            } catch (PDOException $e) {
                echo "<p>Error fetching indexes: " . $e->getMessage() . "</p>";
            }
            
            // Get foreign keys (if supported by the database)
            try {
                $create_table_query = $conn->query("SHOW CREATE TABLE `$table`");
                $create_table = $create_table_query->fetch(PDO::FETCH_ASSOC);
                
                if (isset($create_table['Create Table'])) {
                    // Extract FOREIGN KEY constraints using regex
                    preg_match_all('/CONSTRAINT\s+`([^`]+)`\s+FOREIGN\s+KEY\s+\(`([^`]+)`\)\s+REFERENCES\s+`([^`]+)`\s+\(`([^`]+)`\)(\s+ON\s+DELETE\s+([A-Z\s]+))?(\s+ON\s+UPDATE\s+([A-Z\s]+))?/i', $create_table['Create Table'], $matches, PREG_SET_ORDER);
                    
                    if (!empty($matches)) {
                        echo "<h4>Foreign Keys:</h4>";
                        echo "<table style='width: 100%; border-collapse: collapse;'>";
                        echo "<thead style='background-color: #f3f3f3;'>";
                        echo "<tr>";
                        echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Constraint Name</th>";
                        echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>Column</th>";
                        echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>References Table</th>";
                        echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>References Column</th>";
                        echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>On Delete</th>";
                        echo "<th style='border: 1px solid #ddd; padding: 8px; text-align: left;'>On Update</th>";
                        echo "</tr>";
                        echo "</thead>";
                        echo "<tbody>";
                        
                        foreach ($matches as $match) {
                            echo "<tr>";
                            echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($match[1]) . "</td>";
                            echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($match[2]) . "</td>";
                            echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($match[3]) . "</td>";
                            echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($match[4]) . "</td>";
                            echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . (isset($match[6]) ? htmlspecialchars($match[6]) : 'N/A') . "</td>";
                            echo "<td style='border: 1px solid #ddd; padding: 8px;'>" . (isset($match[8]) ? htmlspecialchars($match[8]) : 'N/A') . "</td>";
                            echo "</tr>";
                        }
                        
                        echo "</tbody>";
                        echo "</table>";
                    }
                }
            } catch (PDOException $e) {
                echo "<p>Error fetching foreign keys: " . $e->getMessage() . "</p>";
            }
            
            echo "</div>";
        }
    }
    
} catch(PDOException $e) {
    echo "<div style='color: red; padding: 20px; border: 1px solid red; margin: 20px;'>";
    echo "<h2>Connection failed</h2>";
    echo "<p>" . $e->getMessage() . "</p>";
    echo "</div>";
}

// Close connection
$conn = null;
?>

<style>
body {
    font-family: Arial, sans-serif;
    line-height: 1.6;
    margin: 20px;
    color: #333;
}
h1 {
    color: #2c3e50;
    border-bottom: 2px solid #3498db;
    padding-bottom: 10px;
}
h2 {
    color: #2980b9;
    margin-top: 30px;
}
h3 {
    color: #3498db;
    margin-bottom: 15px;
}
h4 {
    color: #2c3e50;
    margin-top: 20px;
    margin-bottom: 10px;
}
</style>
