-- Mailbox settings for the admin-managed email mailbox (send + receive)
-- Provider: cPanel-hosted email (e.g. a Namecheap shared-hosting cPanel account)
--   Get the exact mail server hostname/ports from cPanel > Email Accounts > Connect Devices
--   (often mail.yourdomain.com, but may be a shared server hostname like server123.web-hosting.com)
--   Typical ports: SMTP 465 (SSL) or 587 (TLS); IMAP 993 (SSL)
-- Mirrors the key/value pattern used by `backup_settings`.

CREATE TABLE IF NOT EXISTS `mailbox_settings` (
  `setting_id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_description` varchar(255) DEFAULT NULL,
  `is_encrypted` tinyint(1) DEFAULT 0,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`setting_id`),
  UNIQUE KEY `setting_key` (`setting_key`),
  KEY `updated_by` (`updated_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Configuration for the admin email mailbox (cPanel-hosted send/receive)';

ALTER TABLE `mailbox_settings`
  ADD CONSTRAINT `mailbox_settings_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`);

INSERT INTO `mailbox_settings` (`setting_key`, `setting_value`, `setting_description`, `is_encrypted`) VALUES
('mailbox_enabled', 'false', 'Whether the mailbox is active', 0),
('mailbox_display_name', 'IMS Admin Mailbox', 'Display/From name used on outgoing mail', 0),
('mailbox_email', '', 'Full mailbox email address (also used as SMTP/IMAP username)', 0),
('mailbox_password', '', 'Mailbox password (encrypted at rest)', 1),
('smtp_host', '', 'cPanel mail server hostname for SMTP (see Email Accounts > Connect Devices)', 0),
('smtp_port', '465', 'SMTP port (465=SSL, 587=TLS)', 0),
('smtp_encryption', 'ssl', 'SMTP encryption: tls or ssl', 0),
('imap_host', '', 'cPanel mail server hostname for IMAP (see Email Accounts > Connect Devices)', 0),
('imap_port', '993', 'IMAP port (993=SSL)', 0),
('imap_encryption', 'ssl', 'IMAP encryption: ssl or tls', 0),
('verify_cert', 'true', 'Verify the mail server TLS certificate hostname (disable only if your shared host serves a cert for a different hostname)', 0)
ON DUPLICATE KEY UPDATE `setting_key` = `setting_key`;
