-- Email Notifications Table Schema
-- Add this to your database to track email notifications

CREATE TABLE email_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    registration_id INT,
    email_type ENUM('pending_registration', 'registration_confirmation', 'payment_failure', 'payment_reminder_24h', 'payment_reminder_48h', 'payment_reminder_7d', 'admin_notification') NOT NULL,
    recipient_email VARCHAR(255) NOT NULL,
    recipient_name VARCHAR(255),
    subject VARCHAR(500) NOT NULL,
    status ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    brevo_message_id VARCHAR(255),
    error_message TEXT,
    
    FOREIGN KEY (registration_id) REFERENCES registrations(id) ON DELETE CASCADE,
    INDEX idx_registration_id (registration_id),
    INDEX idx_email_type (email_type),
    INDEX idx_status (status),
    INDEX idx_sent_at (sent_at)
);

-- Add email configuration settings to admin_settings table
INSERT INTO admin_settings (setting_key, setting_value) VALUES 
('brevoApiKey', ''),
('senderEmail', 'noreply@xchange2025.com'),
('senderName', 'XChange 2025 Team'),
('adminEmails', '["admin@xchange2025.com"]'),
('bankTransferDetails', 'Bank Name: [Your Bank Name]\nAccount Name: [Account Name]\nAccount Number: [Account Number]\nSwift Code: [Swift Code]\nReference: Use your registration number')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);
