CREATE TABLE IF NOT EXISTS fs_tenants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  external_ref VARCHAR(190) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fs_connected_mailboxes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  email_address VARCHAR(190) NOT NULL,
  display_name VARCHAR(190) NULL,
  provider_type VARCHAR(80) NOT NULL DEFAULT 'custom_smtp',
  smtp_host VARCHAR(190) NOT NULL,
  smtp_port INT NOT NULL DEFAULT 587,
  smtp_encryption VARCHAR(20) NOT NULL DEFAULT 'tls',
  smtp_username VARCHAR(190) NOT NULL,
  smtp_password_encrypted TEXT NOT NULL,
  sending_enabled TINYINT(1) NOT NULL DEFAULT 1,
  receiving_enabled TINYINT(1) NOT NULL DEFAULT 0,
  forwarding_address VARCHAR(255) NOT NULL UNIQUE,
  connection_status VARCHAR(50) NOT NULL DEFAULT 'pending',
  daily_send_limit INT NOT NULL DEFAULT 50,
  sent_today INT NOT NULL DEFAULT 0,
  last_send_count_date DATE NULL,
  last_tested_at DATETIME NULL,
  last_successful_send_at DATETIME NULL,
  last_failed_send_at DATETIME NULL,
  last_error TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  INDEX(tenant_id), INDEX(user_id), INDEX(email_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fs_mailbox_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  connected_mailbox_id BIGINT UNSIGNED NULL,
  direction ENUM('outbound','inbound') NOT NULL,
  from_email VARCHAR(190) NULL,
  to_email TEXT NULL,
  cc TEXT NULL,
  bcc TEXT NULL,
  subject VARCHAR(500) NULL,
  body_html MEDIUMTEXT NULL,
  body_text MEDIUMTEXT NULL,
  message_id_header VARCHAR(500) NULL,
  in_reply_to_header VARCHAR(500) NULL,
  references_header TEXT NULL,
  thread_key VARCHAR(190) NULL,
  related_contact_id VARCHAR(190) NULL,
  related_lead_id VARCHAR(190) NULL,
  related_ticket_id VARCHAR(190) NULL,
  related_project_id VARCHAR(190) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'new',
  raw_path VARCHAR(500) NULL,
  sent_at DATETIME NULL,
  received_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id), INDEX(connected_mailbox_id), INDEX(direction), INDEX(thread_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fs_mailbox_send_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  connected_mailbox_id BIGINT UNSIGNED NOT NULL,
  message_id BIGINT UNSIGNED NULL,
  status VARCHAR(50) NOT NULL,
  error_message TEXT NULL,
  attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id), INDEX(connected_mailbox_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fs_inbound_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  forwarding_address VARCHAR(255) NULL,
  raw_path VARCHAR(500) NULL,
  parse_status VARCHAR(50) NOT NULL DEFAULT 'received',
  webhook_status VARCHAR(50) NULL,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fs_audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  event VARCHAR(190) NOT NULL,
  details_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id), INDEX(user_id), INDEX(event)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;