-- MAIL_MIGRATION (F24330): one row per migration pair (legacy source mailbox -> Outlook target),
-- owned by a FE user. The mailMigration daemon claims rows with status='queued' (guarded UPDATE,
-- affected-rows check) and copies the raw RFC822 messages folder by folder. The source is never
-- modified; the target is append-only plus the optional inbox reconciliation (move/quarantine).
CREATE TABLE IF NOT EXISTS `MailMigration`
(
    `id`               INT(11)      NOT NULL AUTO_INCREMENT,
    `feUser`           VARCHAR(64)  NOT NULL DEFAULT '',   -- owner (FE login), stamped by the form
    -- Source (classic IMAP, password auth)
    `srcHost`          VARCHAR(128) NOT NULL DEFAULT '',   -- 'host[:port]', parsed like EmailSync::parseImapHost()
    `srcUser`          VARCHAR(128) NOT NULL DEFAULT '',
    `srcPasswordEnc`   TEXT         NOT NULL DEFAULT '',   -- AES-256 via form encryption (like Setting.view)
    -- Target (Outlook / Office365, OAuth2 device code; token file path is derived from the id)
    `dstHost`          VARCHAR(128) NOT NULL DEFAULT 'outlook.office365.com',
    `dstUser`          VARCHAR(128) NOT NULL DEFAULT '',   -- UPN / primary address of the Outlook mailbox
    `inboxReconcile`   TINYINT(1)   NOT NULL DEFAULT 1,    -- clean up pre-existing target INBOX (move/quarantine)
    -- Lifecycle
    `status`           ENUM ('new','authorized','queued','running','done','error') NOT NULL DEFAULT 'new',
    `heartbeat`        DATETIME     NOT NULL DEFAULT '0000-00-00 00:00:00', -- worker updates per chunk; drives stale detection
    `workerPid`        INT(11)      NOT NULL DEFAULT 0,
    -- Progress counters (job totals; per-folder detail in folderStats JSON)
    `foldersTotal`     INT(11)      NOT NULL DEFAULT 0,
    `foldersDone`      INT(11)      NOT NULL DEFAULT 0,
    `mailsTotal`       INT(11)      NOT NULL DEFAULT 0,    -- fixed upfront (EXISTS count per source folder)
    `mailsCopied`      INT(11)      NOT NULL DEFAULT 0,
    `mailsSkipped`     INT(11)      NOT NULL DEFAULT 0,    -- already on target (dedup by Message-ID)
    `mailsFailed`      INT(11)      NOT NULL DEFAULT 0,    -- per-message errors/oversize (job continues)
    `mailsMoved`       INT(11)      NOT NULL DEFAULT 0,    -- reconcile: inbox copy moved to its source folder
    `mailsQuarantined` INT(11)      NOT NULL DEFAULT 0,    -- reconcile: not in source -> Migration-Backup/INBOX
    `bytesCopied`      BIGINT(20)   NOT NULL DEFAULT 0,
    `currentFolder`    VARCHAR(255) NOT NULL DEFAULT '',
    `folderStats`      MEDIUMTEXT   NOT NULL DEFAULT '',   -- JSON: [{"src":..,"dst":..,"total":n,"copied":n,"skipped":n,"failed":n}]
    `lastMessage`      VARCHAR(512) NOT NULL DEFAULT '',   -- human readable one-liner for the dashboard
    `lastError`        TEXT         NOT NULL DEFAULT '',
    `startedAt`        DATETIME     DEFAULT NULL,
    `finishedAt`       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 `feUser` (`feUser`) USING BTREE,
    KEY `status` (`status`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  AUTO_INCREMENT = 0;
