<?php
/**
 * Database Structure Viewer
 * * This script includes the database connection and outputs
 * the schema (tables and columns) in a readable HTML format.
 */

// 1. Include the database connection
// Ensure this path is correct relative to where you run this script
require_once 'config/db.php';

// Check if the connection variable exists (assuming $pdo from the config above)
if (!isset($pdo)) {
    die("Database connection variable (\$pdo) not found. Check config/db.php.");
}

try {
    // 2. Fetch all tables from the database
    $stmt = $pdo->query("SHOW TABLES");
    $tables = $stmt->fetchAll(PDO::FETCH_COLUMN);

} catch (PDOException $e) {
    die("Error fetching tables: " . $e->getMessage());
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Database Structure</title>
    <style>
        body {
            font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Helvetica, Arial, sans-serif;
            background-color: #f4f6f8;
            color: #333;
            padding: 20px;
            margin: 0;
        }
        .container {
            max-width: 1000px;
            margin: 0 auto;
        }
        header {
            margin-bottom: 25px;
            border-bottom: 2px solid #e1e4e8;
            padding-bottom: 15px;
            display: flex;
            justify-content: space-between;
            align-items: center;
            flex-wrap: wrap;
            gap: 15px;
        }
        h1 {
            margin: 0;
            color: #2c3e50;
            font-size: 1.8rem;
        }
        .search-box {
            position: relative;
        }
        .search-box input {
            padding: 10px 15px;
            border: 1px solid #ddd;
            border-radius: 20px;
            width: 250px;
            font-size: 14px;
            outline: none;
            transition: all 0.2s;
        }
        .search-box input:focus {
            border-color: #3498db;
            box-shadow: 0 0 5px rgba(52, 152, 219, 0.3);
        }

        .table-card {
            background: white;
            border-radius: 8px;
            box-shadow: 0 2px 5px rgba(0,0,0,0.05);
            margin-bottom: 30px;
            overflow: hidden;
            border: 1px solid #e1e4e8;
            transition: transform 0.2s;
        }
        .table-header {
            background-color: #2c3e50;
            color: white;
            padding: 15px 20px;
            font-size: 1.2rem;
            font-weight: 600;
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
        .row-count {
            background-color: rgba(255,255,255,0.15);
            padding: 4px 12px;
            border-radius: 12px;
            font-size: 0.85rem;
            font-weight: 500;
        }
        .table-responsive {
            overflow-x: auto;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            font-size: 0.95rem;
        }
        th, td {
            padding: 12px 20px;
            text-align: left;
            border-bottom: 1px solid #eee;
        }
        th {
            background-color: #f8f9fa;
            font-weight: 600;
            font-size: 0.85rem;
            text-transform: uppercase;
            letter-spacing: 0.5px;
            color: #555;
            width: 20%;
        }
        tr:last-child td {
            border-bottom: none;
        }
        tr:hover {
            background-color: #fcfcfc;
        }
        
        /* Badges */
        .badge {
            display: inline-block;
            padding: 3px 8px;
            border-radius: 4px;
            font-size: 0.75rem;
            font-weight: 700;
            line-height: 1;
        }
        .badge-pk { background-color: #e74c3c; color: white; } /* Primary Key */
        .badge-fk { background-color: #9b59b6; color: white; } /* Foreign Key (Guess) */
        .badge-null { background-color: #95a5a6; color: white; } /* Nullable */
        .badge-auto { background-color: #3498db; color: white; } /* Auto Increment */
        .badge-idx { background-color: #f39c12; color: white; } /* Index */
        
        .field-name {
            font-weight: 600;
            color: #2c3e50;
        }
        .text-muted {
            color: #999;
            font-style: italic;
        }

        .empty-state {
            text-align: center;
            padding: 50px;
            color: #7f8c8d;
        }
    </style>
</head>
<body>

<div class="container">
    <header>
        <div>
            <h1>Database Structure</h1>
            <small style="color:#7f8c8d">Generated: <?php echo date('Y-m-d H:i'); ?></small>
        </div>
        <div class="search-box">
            <input type="text" id="tableSearch" placeholder="Search tables..." onkeyup="filterTables()">
        </div>
    </header>

    <?php if (empty($tables)): ?>
        <div class="empty-state">
            <h2>No tables found in this database.</h2>
        </div>
    <?php else: ?>
        
        <div id="tablesContainer">
        <?php foreach ($tables as $tableName): ?>
            <?php
            // Get columns for the current table
            try {
                $stmt = $pdo->prepare("SHOW FULL COLUMNS FROM `$tableName`");
                $stmt->execute();
                $columns = $stmt->fetchAll();
                
                // Get row count
                $countStmt = $pdo->query("SELECT COUNT(*) FROM `$tableName`");
                $rowCount = $countStmt->fetchColumn();
            } catch (PDOException $e) {
                echo "<div class='table-card error' style='padding:20px; color:red;'>Error reading table $tableName: " . $e->getMessage() . "</div>";
                continue;
            }
            ?>

            <div class="table-card" data-name="<?php echo strtolower($tableName); ?>">
                <div class="table-header">
                    <span><?php echo htmlspecialchars($tableName); ?></span>
                    <span class="row-count"><?php echo number_format($rowCount); ?> Rows</span>
                </div>
                
                <div class="table-responsive">
                    <table>
                        <thead>
                            <tr>
                                <th>Field</th>
                                <th>Type</th>
                                <th>Attributes</th>
                                <th>Default</th>
                                <th>Extra</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($columns as $col): ?>
                                <?php 
                                    // Logic for badges
                                    $isPri = ($col['Key'] === 'PRI');
                                    $isUni = ($col['Key'] === 'UNI');
                                    $isMul = ($col['Key'] === 'MUL');
                                    $isAuto = (strpos($col['Extra'], 'auto_increment') !== false);
                                    
                                    // Simple logic to guess Foreign Keys: ends in _id and is not the PRI key of this table
                                    $isFk = (!$isPri && substr($col['Field'], -3) === '_id');
                                ?>
                                <tr>
                                    <td>
                                        <div class="field-name"><?php echo htmlspecialchars($col['Field']); ?></div>
                                    </td>
                                    <td style="color:#555;"><?php echo htmlspecialchars($col['Type']); ?></td>
                                    <td>
                                        <?php if ($isPri): ?>
                                            <span class="badge badge-pk" title="Primary Key">PK</span>
                                        <?php endif; ?>
                                        
                                        <?php if ($isFk): ?>
                                            <span class="badge badge-fk" title="Potential Foreign Key">FK</span>
                                        <?php endif; ?>

                                        <?php if ($isUni): ?>
                                            <span class="badge badge-idx" title="Unique Index">UNI</span>
                                        <?php elseif ($isMul): ?>
                                            <span class="badge badge-idx" style="background:#7f8c8d" title="Index">IDX</span>
                                        <?php endif; ?>

                                        <?php if ($col['Null'] === 'YES'): ?>
                                            <span class="badge badge-null">NULL</span>
                                        <?php endif; ?>
                                    </td>
                                    <td>
                                        <?php 
                                        if ($col['Default'] === null && $col['Null'] === 'YES') {
                                            echo '<span class="text-muted">NULL</span>';
                                        } elseif ($col['Default'] === null) {
                                            echo '<span class="text-muted">None</span>';
                                        } elseif ($col['Default'] === 'CURRENT_TIMESTAMP') {
                                            echo '<small>NOW()</small>';
                                        } else {
                                            echo '<span style="font-family:monospace; background:#eee; padding:2px 5px; border-radius:3px;">' . htmlspecialchars($col['Default']) . '</span>';
                                        }
                                        ?>
                                    </td>
                                    <td>
                                        <?php if ($isAuto): ?>
                                            <span class="badge badge-auto">AUTO_INC</span>
                                        <?php else: ?>
                                            <?php echo htmlspecialchars(str_replace('on update current_timestamp()', 'On Update: NOW()', $col['Extra'])); ?>
                                        <?php endif; ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        <?php endforeach; ?>
        </div>

    <?php endif; ?>
</div>

<script>
    function filterTables() {
        const input = document.getElementById('tableSearch');
        const filter = input.value.toLowerCase();
        const cards = document.getElementsByClassName('table-card');

        for (let i = 0; i < cards.length; i++) {
            const name = cards[i].getAttribute('data-name');
            if (name.indexOf(filter) > -1) {
                cards[i].style.display = "";
            } else {
                cards[i].style.display = "none";
            }
        }
    }
</script>

</body>
</html>