<?php
/**
 * Debug script to check and fix support_tickets table
 */
require_once 'config.php';

echo "<h2>Support Tickets Table Debug</h2>";

try {
    // Disable foreign key checks
    db()->exec("SET FOREIGN_KEY_CHECKS = 0");
    
    // Check if table exists
    $table_check = db()->query("SHOW TABLES LIKE 'support_tickets'");
    if ($table_check->rowCount() > 0) {
        echo "<p style='color: orange;'>Table exists, dropping and recreating to ensure correct structure...</p>";
        
        // Drop existing table
        db()->exec("DROP TABLE IF EXISTS support_tickets");
        echo "<p style='color: orange;'>Table dropped.</p>";
    } else {
        echo "<p style='color: red;'>Table doesn't exist. Creating it...</p>";
    }
    
    // Create table with correct structure
    $create_table = "CREATE TABLE support_tickets (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        ticket_number VARCHAR(20) NOT NULL UNIQUE,
        subject VARCHAR(255) NOT NULL,
        message TEXT NOT NULL,
        priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
        status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
        admin_reply TEXT NULL,
        admin_id INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )";
    
    db()->exec($create_table);
    echo "<p style='color: green;'>Table created successfully!</p>";
    
    // Re-enable foreign key checks
    db()->exec("SET FOREIGN_KEY_CHECKS = 1");
    
    echo "<h3>Final Table Structure:</h3>";
    $describe = db()->query("DESCRIBE support_tickets");
    echo "<table border='1'><tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th></tr>";
    while ($row = $describe->fetch()) {
        echo "<tr><td>{$row['Field']}</td><td>{$row['Type']}</td><td>{$row['Null']}</td><td>{$row['Key']}</td></tr>";
    }
    echo "</table>";
    
    // Test insert
    echo "<h3>Testing Insert...</h3>";
    $test_ticket = [
        'user_id' => 1,
        'ticket_number' => 'TEST' . date('YmdHis'),
        'subject' => 'Test Ticket',
        'message' => 'This is a test message',
        'priority' => 'medium'
    ];
    
    $stmt = db()->prepare("INSERT INTO support_tickets 
        (user_id, ticket_number, subject, message, priority, status, created_at)
        VALUES (?, ?, ?, ?, ?, 'open', NOW())");
    
    if ($stmt->execute([$test_ticket['user_id'], $test_ticket['ticket_number'], $test_ticket['subject'], $test_ticket['message'], $test_ticket['priority']])) {
        echo "<p style='color: green;'>Test insert successful!</p>";
        
        // Clean up test record
        db()->prepare("DELETE FROM support_tickets WHERE ticket_number = ?")->execute([$test_ticket['ticket_number']]);
        echo "<p style='color: blue;'>Test record cleaned up.</p>";
    } else {
        echo "<p style='color: red;'>Test insert failed!</p>";
    }
    
    echo "<p style='color: green; font-weight: bold;'>Table is now ready for support tickets!</p>";
    echo "<p><a href='user/create-ticket.php'>Go to Create Ticket Page</a></p>";
    
} catch (Exception $e) {
    // Ensure foreign key checks are re-enabled even on error
    try {
        db()->exec("SET FOREIGN_KEY_CHECKS = 1");
    } catch (Exception $e2) {
        // Ignore if this fails
    }
    
    echo "<p style='color: red;'>Error: " . $e->getMessage() . "</p>";
}
?>
