-- Outbound emails sent directly from Contacts CRM (not via campaign thread)

CREATE TABLE IF NOT EXISTS contact_email_messages (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT NOT NULL,
  smtp_account_id INT NOT NULL,
  direction ENUM('outbound', 'inbound') NOT NULL DEFAULT 'outbound',
  subject VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  body_text MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  body_html MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  from_email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  to_email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  message_id VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  sent_by_user_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_contact_email_contact (contact_id, created_at DESC),
  INDEX idx_contact_email_smtp (smtp_account_id),
  CONSTRAINT fk_cem_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
  CONSTRAINT fk_cem_smtp FOREIGN KEY (smtp_account_id) REFERENCES company_smtp_accounts(id)
);