-- Migration 019: zentrale Tabelle für versendete und vorbereitete E-Mails.
-- message_status: 0 = entwurf/erneut zu versenden, 1 = versendet (oder versucht)

USE `pacim_saas`;

CREATE TABLE IF NOT EXISTS messages (
    message_id        INT AUTO_INCREMENT PRIMARY KEY,
    message_status    TINYINT(1) NOT NULL DEFAULT 0,
    message_count     INT NOT NULL DEFAULT 0,
    message_template  VARCHAR(80) NULL,
    message_customer  INT NULL,
    message_invoice   INT NULL,
    message_booking   INT NULL,
    message_event     INT NULL,
    message_subject   VARCHAR(255) NOT NULL DEFAULT '',
    message_sender    VARCHAR(255) NULL,
    message_receiver  VARCHAR(255) NOT NULL,
    message_content   MEDIUMTEXT NULL,
    message_datetime  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    transport         VARCHAR(40) NULL,
    last_error        VARCHAR(500) NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_msg_status   (message_status),
    INDEX idx_msg_datetime (message_datetime),
    INDEX idx_msg_customer (message_customer),
    INDEX idx_msg_booking  (message_booking),
    INDEX idx_msg_invoice  (message_invoice)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;