-- Mail (SMTP / IMAP) configuration stored in DB so admins can manage it from the app.
-- Falls back to server env vars when a column is NULL/empty.

CREATE TABLE IF NOT EXISTS mail_settings (
    id           TINYINT UNSIGNED NOT NULL PRIMARY KEY DEFAULT 1,
    smtp_host    VARCHAR(190)  NULL,
    smtp_port    SMALLINT UNSIGNED NULL,
    smtp_secure  VARCHAR(10)   NULL, -- '', 'tls', 'ssl'
    smtp_user    VARCHAR(190)  NULL,
    smtp_pass    VARCHAR(255)  NULL,
    imap_host    VARCHAR(255)  NULL, -- e.g. {mail.example.com:993/imap/ssl}
    imap_user    VARCHAR(190)  NULL,
    imap_pass    VARCHAR(255)  NULL,
    sent_folder  VARCHAR(120)  NULL,
    trash_folder VARCHAR(120)  NULL,
    from_name    VARCHAR(190)  NULL,
    from_email   VARCHAR(190)  NULL,
    updated_by   VARCHAR(120)  NULL,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT mail_settings_singleton CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed with the school's known defaults (idempotent).
INSERT INTO mail_settings (id, smtp_host, smtp_port, smtp_secure, smtp_user,
    imap_host, sent_folder, trash_folder, from_name, from_email)
VALUES (1, 'mail.purbomodukhaliss.edu.bd', 465, 'ssl', 'info@purbomodukhaliss.edu.bd',
    '{mail.purbomodukhaliss.edu.bd:993/imap/ssl}', 'INBOX.Sent', 'INBOX.Trash',
    'Purbo Modukhali SS', 'info@purbomodukhaliss.edu.bd')
ON DUPLICATE KEY UPDATE id = id;