-- Email conversation threads (chat-style) per campaign contact

CREATE TABLE IF NOT EXISTS email_conversations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT NOT NULL,
  recipient_id BIGINT NULL,
  contact_email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  subject VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  last_message_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_message_preview VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  unread_inbound_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_conv_campaign_contact (campaign_id, contact_email),
  INDEX idx_conv_campaign_last (campaign_id, last_message_at DESC),
  CONSTRAINT fk_conv_campaign FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_conv_recipient FOREIGN KEY (recipient_id) REFERENCES email_campaign_recipients(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS email_conversation_messages (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  conversation_id BIGINT NOT NULL,
  direction ENUM('inbound', 'outbound') NOT 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,
  subject VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  body_text MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  body_html MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  message_id VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  in_reply_to VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  sent_by_user_id INT NULL,
  legacy_reply_id BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_msg_conv_created (conversation_id, created_at),
  CONSTRAINT fk_msg_conv FOREIGN KEY (conversation_id) REFERENCES email_conversations(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS email_message_attachments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  message_id BIGINT NOT NULL,
  file_asset_id CHAR(36) NULL,
  filename VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  mime_type VARCHAR(127) NOT NULL,
  size_bytes INT UNSIGNED NULL,
  storage_key VARCHAR(512) NULL,
  INDEX idx_att_message (message_id),
  CONSTRAINT fk_att_message FOREIGN KEY (message_id) REFERENCES email_conversation_messages(id) ON DELETE CASCADE
);