<?php
// Check if email_queue table exists and view pending emails
require_once 'config/db.php';

echo "<h2>Email Queue Status Check</h2>";

// Check if table exists
$check_table = $connection->query("SHOW TABLES LIKE 'email_queue'");
if ($check_table && $check_table->num_rows > 0) {
    echo "<p style='color: green;'>✓ Email queue table exists</p>";
} else {
    echo "<p style='color: red;'>✗ Email queue table does NOT exist - creating it now...</p>";
    
    $create_table_sql = "
    CREATE TABLE IF NOT EXISTS email_queue (
        id INT PRIMARY KEY AUTO_INCREMENT,
        consultation_id INT,
        recipient_email VARCHAR(255) NOT NULL,
        subject VARCHAR(255) NOT NULL,
        body LONGTEXT NOT NULL,
        email_type VARCHAR(50),
        status VARCHAR(20) DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        sent_at TIMESTAMP NULL,
        attempts INT DEFAULT 0,
        last_attempt TIMESTAMP NULL,
        error_message TEXT
    )
    ";
    
    if ($connection->query($create_table_sql)) {
        echo "<p style='color: green;'>✓ Email queue table created successfully</p>";
    } else {
        echo "<p style='color: red;'>✗ Failed to create table: " . $connection->error . "</p>";
    }
}

echo "<h3>Pending Emails in Queue:</h3>";

$result = $connection->query("SELECT id, recipient_email, subject, email_type, status, created_at, attempts FROM email_queue WHERE status = 'pending' ORDER BY created_at DESC");

if ($result && $result->num_rows > 0) {
    echo "<table border='1' cellpadding='10' style='width: 100%; border-collapse: collapse;'>";
    echo "<tr style='background: #f0f0f0;'><th>ID</th><th>Email</th><th>Type</th><th>Subject</th><th>Created</th><th>Attempts</th></tr>";
    
    while ($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . htmlspecialchars($row['recipient_email']) . "</td>";
        echo "<td>" . htmlspecialchars($row['email_type']) . "</td>";
        echo "<td>" . htmlspecialchars($row['subject']) . "</td>";
        echo "<td>" . $row['created_at'] . "</td>";
        echo "<td>" . $row['attempts'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "<p style='color: orange;'>No pending emails in queue</p>";
}

echo "<h3>All Emails (Last 20):</h3>";

$all_result = $connection->query("SELECT id, recipient_email, subject, email_type, status, created_at, sent_at, attempts FROM email_queue ORDER BY created_at DESC LIMIT 20");

if ($all_result && $all_result->num_rows > 0) {
    echo "<table border='1' cellpadding='10' style='width: 100%; border-collapse: collapse;'>";
    echo "<tr style='background: #f0f0f0;'><th>ID</th><th>Email</th><th>Type</th><th>Status</th><th>Created</th><th>Sent</th><th>Attempts</th></tr>";
    
    while ($row = $all_result->fetch_assoc()) {
        $status_color = $row['status'] == 'sent' ? 'green' : ($row['status'] == 'pending' ? 'orange' : 'red');
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . htmlspecialchars($row['recipient_email']) . "</td>";
        echo "<td>" . htmlspecialchars($row['email_type']) . "</td>";
        echo "<td style='color: " . $status_color . "; font-weight: bold;'>" . strtoupper($row['status']) . "</td>";
        echo "<td>" . $row['created_at'] . "</td>";
        echo "<td>" . ($row['sent_at'] ?? '-') . "</td>";
        echo "<td>" . $row['attempts'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "<p>No emails found in queue</p>";
}

?>
