CREATE TABLE IF NOT EXISTS booking_email_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    email_type VARCHAR(80) NOT NULL,
    email_to TEXT NULL,
    email_cc TEXT NULL,
    email_bcc TEXT NULL,
    subject VARCHAR(255) NULL,
    body MEDIUMTEXT NULL,
    attachment_name VARCHAR(255) NULL,
    provider VARCHAR(40) NOT NULL DEFAULT 'microsoft_graph',
    provider_message_id VARCHAR(255) NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'sent',
    error_message TEXT NULL,
    sent_by INT UNSIGNED NULL,
    sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_booking_email_order (order_id),
    INDEX idx_booking_email_type (email_type),
    INDEX idx_booking_email_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
