Subversion Repositories SmartDukaan

-- Mail Outbox tables for async mail sending
-- Run against the dtr schema

CREATE TABLE IF NOT EXISTS mail_outbox (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email_to VARCHAR(2000) NOT NULL,
    email_cc VARCHAR(2000),
    email_bcc VARCHAR(2000),
    subject VARCHAR(500) NOT NULL,
    body LONGTEXT NOT NULL,
    is_html TINYINT(1) DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    retry_count INT DEFAULT 0,
    error_message VARCHAR(1000),
    created_at DATETIME NOT NULL,
    sent_at DATETIME,
    source VARCHAR(200),
    sender_type VARCHAR(20) DEFAULT 'SENDGRID',
    INDEX idx_mail_outbox_status (status),
    INDEX idx_mail_outbox_created (created_at),
    INDEX idx_mail_outbox_status_retry (status, retry_count)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mail_outbox_attachment (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    mail_outbox_id BIGINT NOT NULL,
    file_name VARCHAR(500) NOT NULL,
    file_data LONGBLOB NOT NULL,
    content_type VARCHAR(100),
    INDEX idx_mail_attachment_mail_id (mail_outbox_id),
    CONSTRAINT fk_mail_attachment_outbox FOREIGN KEY (mail_outbox_id) REFERENCES mail_outbox(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;