-- Replaces the single-account EAV `mailbox_settings` table with a real
-- multi-account table, one row per mailbox, optionally scoped to a branch.
-- branch_id = NULL means a head-office/admin-only account not tied to a branch.
-- Branch-level user access is out of scope for now (this pass is admin-only
-- tooling); branch_id is stored so branch-scoped access can be layered on
-- later without another schema migration.

CREATE TABLE IF NOT EXISTS `mailbox_accounts` (
  `account_id` int(11) NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) DEFAULT NULL,
  `account_label` varchar(150) NOT NULL,
  `display_name` varchar(150) NOT NULL DEFAULT 'IMS Admin Mailbox',
  `email` varchar(190) NOT NULL,
  `password_encrypted` text DEFAULT NULL,
  `smtp_host` varchar(190) DEFAULT NULL,
  `smtp_port` int(11) NOT NULL DEFAULT 587,
  `smtp_encryption` enum('tls','ssl') NOT NULL DEFAULT 'tls',
  `imap_host` varchar(190) DEFAULT NULL,
  `imap_port` int(11) NOT NULL DEFAULT 993,
  `imap_encryption` enum('tls','ssl') NOT NULL DEFAULT 'ssl',
  `verify_cert` tinyint(1) NOT NULL DEFAULT 1,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `email` (`email`),
  KEY `branch_id` (`branch_id`),
  KEY `updated_by` (`updated_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
  COMMENT='One mailbox (SMTP/IMAP) account per branch or head office; admin can view/send-as any of them';

ALTER TABLE `mailbox_accounts`
  ADD CONSTRAINT `mailbox_accounts_ibfk_1` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`branch_id`) ON DELETE SET NULL,
  ADD CONSTRAINT `mailbox_accounts_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL;

-- One-time carry-over from the old single-account `mailbox_settings` EAV table, if present and populated.
-- The password ciphertext is copied as-is: it was encrypted with the same AES-256-CBC scheme keyed on
-- ENCRYPTION_KEY, so no re-encryption is needed.
INSERT INTO `mailbox_accounts`
  (branch_id, account_label, display_name, email, password_encrypted,
   smtp_host, smtp_port, smtp_encryption, imap_host, imap_port, imap_encryption,
   verify_cert, is_enabled, is_default)
SELECT
  NULL,
  'Head Office (migrated)',
  COALESCE(MAX(CASE WHEN setting_key='mailbox_display_name' THEN setting_value END), 'IMS Admin Mailbox'),
  MAX(CASE WHEN setting_key='mailbox_email' THEN setting_value END),
  MAX(CASE WHEN setting_key='mailbox_password' THEN setting_value END),
  MAX(CASE WHEN setting_key='smtp_host' THEN setting_value END),
  COALESCE(MAX(CASE WHEN setting_key='smtp_port' THEN setting_value END), 587),
  COALESCE(MAX(CASE WHEN setting_key='smtp_encryption' THEN setting_value END), 'tls'),
  MAX(CASE WHEN setting_key='imap_host' THEN setting_value END),
  COALESCE(MAX(CASE WHEN setting_key='imap_port' THEN setting_value END), 993),
  COALESCE(MAX(CASE WHEN setting_key='imap_encryption' THEN setting_value END), 'ssl'),
  COALESCE(MAX(CASE WHEN setting_key='verify_cert' THEN setting_value END), 'true') != 'false',
  COALESCE(MAX(CASE WHEN setting_key='mailbox_enabled' THEN setting_value END), 'false') = 'true',
  1
FROM `mailbox_settings`
HAVING MAX(CASE WHEN setting_key='mailbox_email' THEN setting_value END) IS NOT NULL
   AND MAX(CASE WHEN setting_key='mailbox_email' THEN setting_value END) != ''
   AND NOT EXISTS (SELECT 1 FROM `mailbox_accounts`);

DROP TABLE IF EXISTS `mailbox_settings`;
