<?php
// Quick database schema inspector
// Access via: tools/db_inspect.php?password=inspect2026

$password = $_GET['password'] ?? '';
if ($password !== 'inspect2026') {
    die('Access denied: add ?password=inspect2026 to URL');
}

require_once __DIR__ . '/../config/database.php';
$pdo = getDbConnection();

$tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);

echo "<h1>Database Tables and Columns</h1>\n";

foreach ($tables as $table) {
    echo "<h2>Table: <code>$table</code></h2>\n";
    
    $columns = $pdo->query("SHOW COLUMNS FROM `$table`")->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<table border='1' cellpadding='4' cellspacing='0'>\n";
    echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>\n";
    foreach ($columns as $col) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($col['Field']) . "</td>";
        echo "<td>" . htmlspecialchars($col['Type']) . "</td>";
        echo "<td>" . htmlspecialchars($col['Null']) . "</td>";
        echo "<td>" . htmlspecialchars($col['Key']) . "</td>";
        echo "<td>" . htmlspecialchars($col['Default'] ?? 'NULL') . "</td>";
        echo "<td>" . htmlspecialchars($col['Extra']) . "</td>";
        echo "</tr>\n";
    }
    echo "</table>\n";
    
    // Show row count
    $count = $pdo->query("SELECT COUNT(*) FROM `$table`")->fetchColumn();
    echo "<p>Row count: <strong>$count</strong></p>\n";
    
    echo "<hr>\n";
}

echo "<h2>Specific checks:</h2>\n";

// Check if lesson_words exists
$hasLessonWords = in_array('lesson_words', $tables);
echo "<p>lesson_words table exists: <strong>" . ($hasLessonWords ? 'YES' : 'NO') . "</strong></p>\n";

// Check if activity_words exists
$hasActivityWords = in_array('activity_words', $tables);
echo "<p>activity_words table exists: <strong>" . ($hasActivityWords ? 'YES' : 'NO') . "</strong></p>\n";

// Check if topic_words exists
$hasTopicWords = in_array('topic_words', $tables);
echo "<p>topic_words table exists: <strong>" . ($hasTopicWords ? 'YES' : 'NO') . "</strong></p>\n";

// Check if words exists
$hasWords = in_array('words', $tables);
echo "<p>words table exists: <strong>" . ($hasWords ? 'YES' : 'NO') . "</strong></p>\n";

// Check if word_examples exists
$hasWordExamples = in_array('word_examples', $tables);
echo "<p>word_examples table exists: <strong>" . ($hasWordExamples ? 'YES' : 'NO') . "</strong></p>\n";