CREATE TABLE IF NOT EXISTS `EmailSync`
(
    `id`         INT(11)       NOT NULL AUTO_INCREMENT,
    `account`    VARCHAR(64)   NOT NULL DEFAULT '',
    `folderPath` VARCHAR(128)  NOT NULL DEFAULT '',
    `messageId`  VARCHAR(255)  NOT NULL DEFAULT '',
    `sender`     VARCHAR(128)  NOT NULL DEFAULT '',
    `receiver`   VARCHAR(2048) NOT NULL DEFAULT '',
    `cc`         VARCHAR(2048) NOT NULL DEFAULT '',
    `subject`    VARCHAR(1024) NOT NULL DEFAULT '',
    -- MEDIUMTEXT (16 MB), not TEXT (64 KB): HTML newsletters routinely exceed 64 KB and would
    -- otherwise fail the INSERT under MySQL strict mode. Existing installs: ALTER TABLE EmailSync
    -- MODIFY bodyText MEDIUMTEXT, MODIFY bodyHtml MEDIUMTEXT, MODIFY header MEDIUMTEXT.
    `bodyText`   MEDIUMTEXT    NOT NULL DEFAULT '',
    `bodyHtml`   MEDIUMTEXT    NOT NULL DEFAULT '',
    -- JSON metadata of the mail's attachments (name/size/mime/disposition/file). TEXT, not
    -- VARCHAR(4096): many/long filenames overflow. Files themselves live on disk. Existing
    -- installs: ALTER TABLE EmailSync MODIFY attachment TEXT.
    `attachment` TEXT          NOT NULL DEFAULT '',
    `header`     MEDIUMTEXT    NOT NULL DEFAULT '',
    `flags`      VARCHAR(2048) NOT NULL DEFAULT '',
    `mailDate`   DATETIME      DEFAULT NULL,
    `created`    DATETIME      NOT NULL DEFAULT current_timestamp(),
    `modified`   DATETIME      NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (`id`),
    KEY `accountFolderPathMessageId` (`account`, `folderPath`, `messageId`) USING BTREE,
    KEY `accountMailDate` (`account`, `mailDate`) USING BTREE,
    KEY `messageId` (`messageId`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  AUTO_INCREMENT = 0;
