<?php
$conn = new mysqli('localhost', 'zemaraim', '8wwxk]-X3Kx]CJ]5', 'zemaraim_interior');
if ($conn->connect_error) {
    echo 'Error: ' . $conn->connect_error;
    exit;
}

echo "<h2>Setting Up Contact Messages Table</h2>";

// Check if contact_messages table exists
$check = $conn->query("SHOW TABLES LIKE 'contact_messages'");

if ($check->num_rows == 0) {
    echo "<p>Creating contact_messages table...</p>";
    
    $sql = "CREATE TABLE contact_messages (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL,
        phone VARCHAR(20) NOT NULL,
        subject VARCHAR(255) NOT NULL,
        message LONGTEXT NOT NULL,
        privacy_agreed TINYINT(1) DEFAULT 0,
        status ENUM('new', 'read', 'responded', 'archived') DEFAULT 'new',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_email (email),
        INDEX idx_status (status),
        INDEX idx_created (created_at)
    )";
    
    if ($conn->query($sql)) {
        echo "<p style='color: green;'><strong>✓ contact_messages table created successfully!</strong></p>";
    } else {
        echo "<p style='color: red;'><strong>✗ Error creating table: " . $conn->error . "</strong></p>";
    }
} else {
    echo "<p style='color: green;'><strong>✓ contact_messages table already exists</strong></p>";
}

// Show table structure
echo "<h2>Table Structure</h2>";
$result = $conn->query("DESCRIBE contact_messages");
echo "<table border='1' style='border-collapse: collapse; margin-top: 10px;'>";
echo "<tr style='background: #333; color: white;'><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
while($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row['Field'] . "</td>";
    echo "<td>" . $row['Type'] . "</td>";
    echo "<td>" . $row['Null'] . "</td>";
    echo "<td>" . $row['Key'] . "</td>";
    echo "<td>" . ($row['Default'] ?? 'NULL') . "</td>";
    echo "<td>" . $row['Extra'] . "</td>";
    echo "</tr>";
}
echo "</table>";

echo "<h2>Sample Data</h2>";
$result = $conn->query("SELECT COUNT(*) as count FROM contact_messages");
$row = $result->fetch_assoc();
echo "<p>Total messages: <strong>" . $row['count'] . "</strong></p>";

if ($row['count'] > 0) {
    $result = $conn->query("SELECT id, name, email, subject, created_at FROM contact_messages ORDER BY created_at DESC LIMIT 5");
    echo "<table border='1' style='border-collapse: collapse; margin-top: 10px;'>";
    echo "<tr style='background: #333; color: white;'><th>ID</th><th>Name</th><th>Email</th><th>Subject</th><th>Date</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . htmlspecialchars($row['name']) . "</td>";
        echo "<td>" . htmlspecialchars($row['email']) . "</td>";
        echo "<td>" . htmlspecialchars($row['subject']) . "</td>";
        echo "<td>" . date('M d, Y H:i', strtotime($row['created_at'])) . "</td>";
        echo "</tr>";
    }
    echo "</table>";
}

echo "<p style='margin-top: 20px;'><a href='contact.php' class='btn' style='background: #d4a574; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px;'>Go to Contact Page</a></p>";

$conn->close();
?>
