<?php
include '../includes/db.php';

echo "<h1>Database Schema Check: result_styling table</h1>";

try {
    // Check if table exists
    $stmt = $pdo->query("SHOW TABLES LIKE 'result_styling'");
    if ($stmt->rowCount() == 0) {
        echo "<p style='color: red; font-weight: bold;'>✗ TABLE DOES NOT EXIST!</p>";
        echo "<h2>Creating table...</h2>";
        
        $create_sql = "CREATE TABLE IF NOT EXISTS result_styling (
            id INT AUTO_INCREMENT PRIMARY KEY,
            event_id INT NOT NULL,
            config_id INT NULL,
            styling_data TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
            FOREIGN KEY (config_id) REFERENCES result_configurations(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        
        $pdo->exec($create_sql);
        echo "<p style='color: green;'>✓ Table created successfully!</p>";
    } else {
        echo "<p style='color: green; font-weight: bold;'>✓ Table exists</p>";
    }
    
    // Show table structure
    $stmt = $pdo->query("DESCRIBE result_styling");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<h2>Table Structure:</h2>";
    echo "<table border='1' style='border-collapse: collapse;'>";
    echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
    foreach ($columns as $col) {
        echo "<tr>";
        echo "<td>{$col['Field']}</td>";
        echo "<td>{$col['Type']}</td>";
        echo "<td>{$col['Null']}</td>";
        echo "<td>{$col['Key']}</td>";
        echo "<td>" . ($col['Default'] ?? 'NULL') . "</td>";
        echo "<td>{$col['Extra']}</td>";
        echo "</tr>";
    }
    echo "</table>";
    
    // Show existing data
    $stmt = $pdo->query("SELECT * FROM result_styling");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<h2>Existing Data (" . count($rows) . " rows):</h2>";
    if (empty($rows)) {
        echo "<p>No data in table yet.</p>";
    } else {
        echo "<table border='1' style='border-collapse: collapse;'>";
        echo "<tr><th>ID</th><th>Event ID</th><th>Config ID</th><th>Has Data</th><th>Created</th><th>Actions</th></tr>";
        foreach ($rows as $row) {
            echo "<tr>";
            echo "<td>{$row['id']}</td>";
            echo "<td>{$row['event_id']}</td>";
            echo "<td>" . ($row['config_id'] ?? 'NULL') . "</td>";
            echo "<td>" . (strlen($row['styling_data']) > 0 ? 'YES (' . strlen($row['styling_data']) . ' bytes)' : 'NO') . "</td>";
            echo "<td>{$row['created_at']}</td>";
            echo "<td><a href='?delete={$row['id']}'>Delete</a></td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
    // Handle delete
    if (isset($_GET['delete'])) {
        $delete_id = (int)$_GET['delete'];
        $stmt = $pdo->prepare("DELETE FROM result_styling WHERE id = ?");
        $stmt->execute([$delete_id]);
        echo "<p style='color: green;'>✓ Deleted row ID $delete_id</p>";
        echo "<meta http-equiv='refresh' content='1'>";
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>ERROR: " . $e->getMessage() . "</p>";
}
