CREATE TABLE IF NOT EXISTS mail_accounts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  from_name VARCHAR(190) NULL,
  imap_host VARCHAR(190) NOT NULL,
  imap_port INT NOT NULL DEFAULT 993,
  imap_encryption VARCHAR(10) NOT NULL DEFAULT 'ssl',
  imap_username VARCHAR(190) NOT NULL,
  imap_password_enc TEXT NOT NULL,
  smtp_host VARCHAR(190) NOT NULL,
  smtp_port INT NOT NULL DEFAULT 465,
  smtp_encryption VARCHAR(10) NOT NULL DEFAULT 'ssl',
  smtp_username VARCHAR(190) NOT NULL,
  smtp_password_enc TEXT NOT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_mail_accounts_active(active),
  INDEX idx_mail_accounts_default(is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mail_drafts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id INT UNSIGNED NOT NULL,
  mail_to TEXT NULL,
  cc TEXT NULL,
  bcc TEXT NULL,
  subject VARCHAR(500) NULL,
  body_html MEDIUMTEXT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_mail_drafts_account(account_id),
  INDEX idx_mail_drafts_updated(updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mail_sent_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id INT UNSIGNED NOT NULL,
  mail_to TEXT NULL,
  cc TEXT NULL,
  subject VARCHAR(500) NULL,
  message_id VARCHAR(255) NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'sent',
  sent_at DATETIME NULL,
  INDEX idx_mail_sent_account(account_id),
  INDEX idx_mail_sent_date(sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;