-- ── Kasper® Email Tables ─────────────────────────────────────────
-- Запустить: mysql -u USER -p DATABASE < email_schema.sql

CREATE TABLE IF NOT EXISTS `mail_accounts` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`     INT UNSIGNED NOT NULL,
  `email`       VARCHAR(255) NOT NULL,
  `label`       VARCHAR(100) DEFAULT '',
  -- IMAP
  `imap_host`   VARCHAR(255) NOT NULL,
  `imap_port`   SMALLINT UNSIGNED NOT NULL DEFAULT 993,
  `imap_ssl`    TINYINT(1)  NOT NULL DEFAULT 1,
  `imap_user`   VARCHAR(255) NOT NULL,
  `imap_pass`   VARCHAR(255) NOT NULL,
  -- SMTP
  `smtp_host`   VARCHAR(255) DEFAULT '',
  `smtp_port`   SMALLINT UNSIGNED DEFAULT 587,
  `smtp_ssl`    TINYINT(1)  DEFAULT 1,
  `smtp_user`   VARCHAR(255) DEFAULT '',
  `smtp_pass`   VARCHAR(255) DEFAULT '',
  `active`      TINYINT(1)  NOT NULL DEFAULT 1,
  `created_at`  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `emails` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_id`    INT UNSIGNED NOT NULL,
  `user_id`       INT UNSIGNED NOT NULL,
  `folder`        ENUM('inbox','sent','trash','spam') NOT NULL DEFAULT 'inbox',
  `msg_uid`       VARCHAR(128) NOT NULL DEFAULT '',   -- IMAP UID
  `message_id`    VARCHAR(512) DEFAULT '',            -- Message-ID header
  `from_addr`     VARCHAR(512) NOT NULL DEFAULT '',
  `from_name`     VARCHAR(255) DEFAULT '',
  `to_addr`       TEXT DEFAULT NULL,
  `cc_addr`       TEXT DEFAULT NULL,
  `subject`       VARCHAR(1000) DEFAULT '',
  `body_text`     MEDIUMTEXT DEFAULT NULL,
  `body_html`     MEDIUMTEXT DEFAULT NULL,
  `attachments`   JSON DEFAULT NULL,
  `is_read`       TINYINT(1)  NOT NULL DEFAULT 0,
  `is_starred`    TINYINT(1)  NOT NULL DEFAULT 0,
  `is_lead`       TINYINT(1)  NOT NULL DEFAULT 0,
  `lead_status`   VARCHAR(50) DEFAULT '',             -- new/contacted/closed
  `sent_at`       DATETIME    DEFAULT NULL,
  `created_at`    DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_account_uid` (`account_id`, `msg_uid`),
  KEY `idx_user_folder` (`user_id`, `folder`),
  KEY `idx_user_lead`   (`user_id`, `is_lead`),
  KEY `idx_sent_at`     (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
