CREATE TABLE IF NOT EXISTS fs_connected_mailboxes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id VARCHAR(64) NOT NULL,
  user_id VARCHAR(64) NOT NULL,
  display_name VARCHAR(190) DEFAULT NULL,
  email_address VARCHAR(190) NOT NULL,
  provider_type VARCHAR(60) DEFAULT 'custom_smtp',
  smtp_host VARCHAR(190) DEFAULT NULL,
  smtp_port INT DEFAULT NULL,
  smtp_encryption VARCHAR(20) DEFAULT NULL,
  smtp_username VARCHAR(190) DEFAULT NULL,
  smtp_password_encrypted TEXT,
  forwarding_address VARCHAR(190) NOT NULL UNIQUE,
  sending_enabled TINYINT(1) DEFAULT 1,
  receiving_enabled TINYINT(1) DEFAULT 0,
  connection_status VARCHAR(40) DEFAULT 'created',
  last_tested_at DATETIME DEFAULT NULL,
  last_successful_send_at DATETIME DEFAULT NULL,
  last_failed_send_at DATETIME DEFAULT NULL,
  last_error TEXT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_tenant_user (tenant_id, user_id),
  INDEX idx_email (email_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fs_mailbox_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id VARCHAR(64) NOT NULL,
  user_id VARCHAR(64) DEFAULT NULL,
  connected_mailbox_id INT DEFAULT NULL,
  direction VARCHAR(20) NOT NULL,
  from_email VARCHAR(190) DEFAULT NULL,
  to_email TEXT,
  cc TEXT,
  bcc TEXT,
  subject VARCHAR(255) DEFAULT NULL,
  body_html MEDIUMTEXT,
  body_text MEDIUMTEXT,
  message_id_header VARCHAR(255) DEFAULT NULL,
  in_reply_to_header VARCHAR(255) DEFAULT NULL,
  references_header TEXT,
  thread_key VARCHAR(190) DEFAULT NULL,
  status VARCHAR(40) DEFAULT 'logged',
  raw_payload LONGTEXT,
  sent_at DATETIME DEFAULT NULL,
  received_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_tenant (tenant_id),
  INDEX idx_thread (thread_key),
  INDEX idx_mailbox (connected_mailbox_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fs_send_attempts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id VARCHAR(64) NOT NULL,
  user_id VARCHAR(64) DEFAULT NULL,
  connected_mailbox_id INT DEFAULT NULL,
  message_id INT DEFAULT NULL,
  status VARCHAR(40) NOT NULL,
  response_message TEXT,
  attempted_at DATETIME NOT NULL,
  INDEX idx_message (message_id),
  INDEX idx_mailbox (connected_mailbox_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fs_audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id VARCHAR(64) DEFAULT NULL,
  user_id VARCHAR(64) DEFAULT NULL,
  action VARCHAR(120) NOT NULL,
  details TEXT,
  created_at DATETIME NOT NULL,
  INDEX idx_action (action),
  INDEX idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;