<?php
/**
 * SeekQuarry/Yioop --
 * Open Source Pure PHP Search Engine, Crawler, and Indexer
 *
 * Copyright (C) 2009 - 2026  Chris Pollett chris@pollett.org
 *
 * LICENSE:
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 * END LICENSE
 *
 * @author Chris Pollett chris@pollett.org
 * @license https://www.gnu.org/licenses/ GPL3
 * @link https://www.seekquarry.com/
 * @copyright 2009 - 2026
 * @filesource
 */
namespace seekquarry\yioop\models;

use seekquarry\yioop\configs as C;
use seekquarry\yioop\library\CredentialCipher;

/**
 * Persists per-user mail account records used by the Mail activity
 * when MAIL_MODE allows external accounts. Each row records the
 * connection settings for one remote mail service the user has
 * registered (Gmail, Fastmail, a corporate server, etc.): the
 * incoming (IMAP) settings, and the outgoing (SMTP) settings.
 *
 * Passwords are encrypted at rest via CredentialCipher (libsodium
 * sodium_crypto_secretbox); both the IMAP password and the SMTP
 * password get their own nonce + ciphertext pair, sharing the
 * master key. The encrypted payloads sit in this (public) table;
 * the master key sits in the private database. Reads decrypt
 * lazily; a password plaintext is only resurrected when a caller
 * explicitly requests it via getAccountWithPassword() (IMAP) or
 * getAccountWithSmtpPassword() (SMTP).
 *
 * All public methods are user-scoped: the caller passes $user_id and
 * the model refuses to surface rows belonging to any other user. This
 * is the access-control boundary; controllers should not bypass it.
 *
 * @author Chris Pollett
 */
class MailAccountModel extends Model
{
    /**
     * Cached master encryption key (raw 32 bytes), loaded from the
     * private database on first use and reused for the rest of the
     * request so repeated encrypt/decrypt calls do not re-query.
     * @var string|null
     */
    protected $master_key = null;
    /**
     * Placeholder string shown in a password field on the edit form
     * when a credential is already stored for that section. It is a
     * fixed run of bullet characters: the real password is never
     * decrypted into the HTML. When the form is submitted, a
     * password field whose value is exactly this sentinel is
     * treated as "leave the stored credential unchanged" (same as
     * an empty field). Any other non-empty value is a real change.
     * @var string
     */
    const PASSWORD_SENTINEL = "\u{2022}\u{2022}\u{2022}\u{2022}" .
        "\u{2022}\u{2022}\u{2022}\u{2022}";
    /**
     * Per-provider preset map. The keys are the provider names stored
     * in the MAIL_ACCOUNT.PROVIDER column; "Custom" is implicit (the
     * absence of any value in PROVIDER, surfaced as "Custom" in the
     * dropdown). For each preset:
     *   - imap_host / imap_port / imap_tls and the smtp_* siblings
     *     are the well-known public endpoints the provider operates;
     *   - email_domain is set only when the provider's IMAP/SMTP
     *     login accepts a bare username (no @), in which case a
     *     bare-username input is stitched to <username>@<domain>
     *     for the From address. Gmail is currently the only entry
     *     that supports this; everyone else stores null and the
     *     username field must contain a full email address;
     *   - login_label is 'username' or 'email' and drives only the
     *     visible field label on the create-account form. It's a
     *     cue to the user, not a validator -- the server accepts
     *     whatever the form posts and trusts the resulting login
     *     attempt to fail informatively if it's wrong.
     * @var array
     */
    const PROVIDER_PRESETS = [
        "Gmail" => [
            'imap_host' => 'imap.gmail.com',
            'imap_port' => 993,
            'imap_tls' => 'imaps',
            'smtp_host' => 'smtp.gmail.com',
            'smtp_port' => 587,
            'smtp_tls' => 'starttls',
            'email_domain' => 'gmail.com',
            'login_label' => 'username',
        ],
        "Outlook.com" => [
            'imap_host' => 'outlook.office365.com',
            'imap_port' => 993,
            'imap_tls' => 'imaps',
            'smtp_host' => 'smtp-mail.outlook.com',
            'smtp_port' => 587,
            'smtp_tls' => 'starttls',
            'email_domain' => null,
            'login_label' => 'email',
        ],
        "Yahoo" => [
            'imap_host' => 'imap.mail.yahoo.com',
            'imap_port' => 993,
            'imap_tls' => 'imaps',
            'smtp_host' => 'smtp.mail.yahoo.com',
            'smtp_port' => 587,
            'smtp_tls' => 'starttls',
            'email_domain' => null,
            'login_label' => 'email',
        ],
        "iCloud" => [
            'imap_host' => 'imap.mail.me.com',
            'imap_port' => 993,
            'imap_tls' => 'imaps',
            'smtp_host' => 'smtp.mail.me.com',
            'smtp_port' => 587,
            'smtp_tls' => 'starttls',
            'email_domain' => null,
            'login_label' => 'email',
        ],
        "AOL" => [
            'imap_host' => 'imap.aol.com',
            'imap_port' => 993,
            'imap_tls' => 'imaps',
            'smtp_host' => 'smtp.aol.com',
            'smtp_port' => 587,
            'smtp_tls' => 'starttls',
            'email_domain' => null,
            'login_label' => 'email',
        ],
        "Fastmail" => [
            'imap_host' => 'imap.fastmail.com',
            'imap_port' => 993,
            'imap_tls' => 'imaps',
            'smtp_host' => 'smtp.fastmail.com',
            'smtp_port' => 587,
            'smtp_tls' => 'starttls',
            'email_domain' => null,
            'login_label' => 'email',
        ],
    ];
    /**
     * Returns the list of accounts the given user has registered, in
     * insertion order. Password ciphertext is NOT decrypted; callers
     * who need to make IMAP connections must use
     * getAccountWithPassword() for the specific account they intend
     * to talk to.
     *
     * @param int $user_id Yioop USER_ID whose accounts to list
     * @return array list of row arrays with keys ID, DISPLAY_NAME,
     *      PROVIDER, HOST, PORT, USERNAME, TLS_MODE, DEFAULT_FOLDER,
     *      ALLOW_SELF_SIGNED, SMTP_HOST, SMTP_PORT, SMTP_USERNAME,
     *      SMTP_TLS_MODE, CREATED_AT, UPDATED_AT (no password
     *      columns)
     */
    public function getAccountsForUser($user_id)
    {
        $db = $this->db;
        $sql = "SELECT ID, USER_ID, DISPLAY_NAME, PROVIDER, HOST, PORT, " .
            "USERNAME, TLS_MODE, DEFAULT_FOLDER, ALLOW_SELF_SIGNED, " .
            "SMTP_HOST, SMTP_PORT, SMTP_USERNAME, SMTP_TLS_MODE, " .
            "SORT_ORDER, CREATED_AT, UPDATED_AT FROM MAIL_ACCOUNT " .
            "WHERE USER_ID = ? " .
            "ORDER BY SORT_ORDER ASC, CREATED_AT ASC, ID ASC";
        $result = $db->execute($sql, [$user_id]);
        $accounts = [];
        if ($result) {
            while ($row = $db->fetchArray($result)) {
                $accounts[] = $row;
            }
        }
        return $accounts;
    }
    /**
     * Persists a user's chosen ordering of their mail accounts.
     * Each account id is written a SORT_ORDER equal to its position
     * in the supplied list (0-based), so a later getAccountsForUser
     * returns them in this order. Ids are scoped to the owning user
     * in the WHERE clause, so a forged id belonging to another user
     * updates nothing. Ids not belonging to the user, or not present
     * in the list, are left untouched.
     *
     * @param int $user_id owner of the accounts being reordered
     * @param array $ordered_ids account ids in the desired display
     *      order
     */
    public function updateAccountOrder($user_id, $ordered_ids)
    {
        $db = $this->db;
        $now = time();
        $position = 0;
        $sql = "UPDATE MAIL_ACCOUNT SET SORT_ORDER = ?, " .
            "UPDATED_AT = ? WHERE ID = ? AND USER_ID = ?";
        foreach ($ordered_ids as $account_id) {
            $db->execute($sql, [$position, $now,
                (int) $account_id, $user_id]);
            $position++;
        }
    }
    /**
     * Derives the sender email address from an account row,
     * applying the same precedence the compose UI uses for the
     * From: header. Lives on the model rather than a controller
     * helper so the MailScheduledDispatcher (a library class)
     * can use it without reaching into a controller component.
     *
     * Order:
     *  1. USERNAME if it already contains '@'
     *  2. SMTP_USERNAME if it contains '@'
     *  3. USERNAME + '@' + provider preset email_domain
     *     (e.g. Gmail's gmail.com), when both are present
     *  4. USERNAME + '@' + HOST, as a last-resort fallback for
     *     self-hosted accounts where USERNAME is just a local
     *     part and the IMAP host happens to match the email
     *     domain
     *
     * @param array $account row with USERNAME, SMTP_USERNAME,
     *      PROVIDER, HOST keys (subset is fine)
     * @return string the sender email address, or '' when no
     *      usable username is present
     */
    public static function senderEmail($account)
    {
        $username = trim($account['USERNAME'] ?? '');
        if (strpos($username, '@') !== false) {
            return $username;
        }
        $smtp_username = trim($account['SMTP_USERNAME'] ?? '');
        if (strpos($smtp_username, '@') !== false) {
            return $smtp_username;
        }
        $provider = trim($account['PROVIDER'] ?? '');
        if ($provider !== '' && isset(
                self::PROVIDER_PRESETS[$provider]
                ['email_domain']) &&
                self::PROVIDER_PRESETS[$provider]
                ['email_domain'] !== '' && $username !== '') {
            return $username . '@' .
                self::PROVIDER_PRESETS[$provider]['email_domain'];
        }
        $host = trim($account['HOST'] ?? '');
        return $username === '' ? '' :
            ($username . '@' . $host);
    }
    /**
     * Returns one account row by id, scoped to the supplied user so
     * a hand-crafted URL with another user's account id will get a
     * false return. Password ciphertext is not surfaced; instead two
     * derived boolean keys, HAS_PASSWORD and HAS_SMTP_PASSWORD,
     * report whether a credential is stored for each section. The
     * edit form uses these to decide whether to show the
     * masked-sentinel placeholder in the password fields.
     *
     * @param int $account_id MAIL_ACCOUNT.ID to fetch
     * @param int $user_id USER_ID the account must belong to
     * @return array|false the row, or false when no matching account
     *      exists for that user
     */
    public function getAccount($account_id, $user_id)
    {
        $db = $this->db;
        $sql = "SELECT ID, USER_ID, DISPLAY_NAME, PROVIDER, HOST, PORT, " .
            "USERNAME, TLS_MODE, DEFAULT_FOLDER, ALLOW_SELF_SIGNED, " .
            "SMTP_HOST, SMTP_PORT, SMTP_USERNAME, SMTP_TLS_MODE, " .
            "CREATED_AT, UPDATED_AT, " .
            "CASE WHEN LENGTH(PASSWORD_CIPHERTEXT) > 0 THEN 1 ELSE 0 END " .
            "AS HAS_PASSWORD, " .
            "CASE WHEN LENGTH(SMTP_PASSWORD_CIPHERTEXT) > 0 THEN 1 " .
            "ELSE 0 END AS HAS_SMTP_PASSWORD " .
            "FROM MAIL_ACCOUNT WHERE ID = ? AND USER_ID = ? " .
            $db->limitOffset(1);
        $result = $db->execute($sql, [$account_id, $user_id]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        return $row ?: false;
    }
    /**
     * Same as getAccount() but additionally decrypts the IMAP
     * password and places it under the 'PASSWORD' key in the
     * returned row. Throws \Exception if the stored ciphertext fails
     * authentication, which indicates either tampering or master-key
     * rotation that lost the old key. When no IMAP password was ever
     * stored the result's PASSWORD key is the empty string.
     *
     * @param int $account_id MAIL_ACCOUNT.ID to fetch
     * @param int $user_id USER_ID the account must belong to
     * @return array|false the row with PASSWORD populated, or false
     *      when no matching account exists
     */
    public function getAccountWithPassword($account_id, $user_id)
    {
        $db = $this->db;
        $sql = "SELECT * FROM MAIL_ACCOUNT " .
            "WHERE ID = ? AND USER_ID = ? " . $db->limitOffset(1);
        $result = $db->execute($sql, [$account_id, $user_id]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (!$row) {
            return false;
        }
        if (!empty($row['PASSWORD_NONCE'])) {
            $row['PASSWORD'] = CredentialCipher::decrypt($this->masterKey(),
                $row['PASSWORD_NONCE'], $row['PASSWORD_CIPHERTEXT']);
        } else {
            $row['PASSWORD'] = '';
        }
        unset($row['PASSWORD_NONCE'], $row['PASSWORD_CIPHERTEXT'],
            $row['SMTP_PASSWORD_NONCE'], $row['SMTP_PASSWORD_CIPHERTEXT']);
        return $row;
    }
    /**
     * Returns every mail account a user owns, each with its login
     * password decrypted and ready for a live IMAP connection. This
     * is the list the unread-badge probe walks: it needs each
     * account's password to log in and count unseen messages, so
     * this pairs the account list with a per-account credential
     * lookup in one call and skips any row whose credentials cannot
     * be loaded.
     *
     * @param int $user_id the user whose accounts to return
     * @return array list of account rows, each including a decrypted
     *      PASSWORD field; empty when the user has no mail accounts
     */
    public function getAccountsWithPassword($user_id)
    {
        $accounts = [];
        foreach ($this->getAccountsForUser($user_id) as $row) {
            $account = $this->getAccountWithPassword($row['ID'],
                $user_id);
            if ($account) {
                $accounts[] = $account;
            }
        }
        return $accounts;
    }
    /**
     * Same as getAccount() but additionally decrypts the SMTP
     * password and places it under the 'SMTP_PASSWORD' key in the
     * returned row. Used by the outbound-mail path. Throws
     * \Exception if the stored ciphertext fails authentication. When
     * no SMTP password was ever stored the decrypt yields the empty
     * string.
     *
     * @param int $account_id MAIL_ACCOUNT.ID to fetch
     * @param int $user_id USER_ID the account must belong to
     * @return array|false the row with SMTP_PASSWORD populated, or
     *      false when no matching account exists
     */
    public function getAccountWithSmtpPassword($account_id, $user_id)
    {
        $db = $this->db;
        $sql = "SELECT * FROM MAIL_ACCOUNT " .
            "WHERE ID = ? AND USER_ID = ? " . $db->limitOffset(1);
        $result = $db->execute($sql, [$account_id, $user_id]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (!$row) {
            return false;
        }
        if (!empty($row['SMTP_PASSWORD_NONCE'])) {
            $row['SMTP_PASSWORD'] = CredentialCipher::decrypt(
                $this->masterKey(), $row['SMTP_PASSWORD_NONCE'],
                $row['SMTP_PASSWORD_CIPHERTEXT']);
        } else {
            $row['SMTP_PASSWORD'] = '';
        }
        unset($row['PASSWORD_NONCE'], $row['PASSWORD_CIPHERTEXT'],
            $row['SMTP_PASSWORD_NONCE'], $row['SMTP_PASSWORD_CIPHERTEXT']);
        return $row;
    }
    /**
     * Returns the account row with both IMAP and SMTP credentials
     * decrypted, applying the SMTP-falls-back-to-IMAP rule for
     * username and password. When the stored SMTP_USERNAME is the
     * empty string the IMAP USERNAME is used in its place, and
     * likewise for the password. Callers wiring up an SMTP
     * transmission can read SMTP_USERNAME and SMTP_PASSWORD
     * directly without having to know about the dual-credential
     * convention.
     *
     * @param int $account_id MAIL_ACCOUNT.ID to fetch
     * @param int $user_id USER_ID the account must belong to
     * @return array|false the row with USERNAME, PASSWORD,
     *      SMTP_USERNAME, SMTP_PASSWORD populated (SMTP fields
     *      defaulting to their IMAP counterparts when blank), or
     *      false when no matching account exists
     */
    public function getAccountForSending($account_id, $user_id)
    {
        $db = $this->db;
        $sql = "SELECT * FROM MAIL_ACCOUNT " .
            "WHERE ID = ? AND USER_ID = ? " . $db->limitOffset(1);
        $result = $db->execute($sql, [$account_id, $user_id]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (!$row) {
            return false;
        }
        if (!empty($row['PASSWORD_NONCE'])) {
            $row['PASSWORD'] = CredentialCipher::decrypt(
                $this->masterKey(), $row['PASSWORD_NONCE'],
                $row['PASSWORD_CIPHERTEXT']);
        } else {
            $row['PASSWORD'] = '';
        }
        if (!empty($row['SMTP_PASSWORD_NONCE'])) {
            $row['SMTP_PASSWORD'] = CredentialCipher::decrypt(
                $this->masterKey(), $row['SMTP_PASSWORD_NONCE'],
                $row['SMTP_PASSWORD_CIPHERTEXT']);
        } else {
            $row['SMTP_PASSWORD'] = '';
        }
        /* SMTP_USERNAME and SMTP_PASSWORD default to the IMAP
           USERNAME / PASSWORD when blank. This is the common
           case: most providers accept the same credentials on
           both ports, and the Add-Account UI lets a user fill in
           SMTP fields only if they actually differ. */
        if (($row['SMTP_USERNAME'] ?? '') === '') {
            $row['SMTP_USERNAME'] = $row['USERNAME'];
        }
        if ($row['SMTP_PASSWORD'] === '') {
            $row['SMTP_PASSWORD'] = $row['PASSWORD'];
        }
        unset($row['PASSWORD_NONCE'], $row['PASSWORD_CIPHERTEXT'],
            $row['SMTP_PASSWORD_NONCE'], $row['SMTP_PASSWORD_CIPHERTEXT']);
        return $row;
    }
    /**
     * Encrypts a credential for storage, with one special case: an
     * empty-string plaintext is stored as an empty nonce and empty
     * ciphertext rather than being run through the cipher. Encrypting
     * the empty string still produces non-empty ciphertext (the
     * authentication tag), which would make a "is a password stored"
     * length check meaningless; storing genuinely empty columns keeps
     * the HAS_PASSWORD / HAS_SMTP_PASSWORD flags from getAccount()
     * honest. The test is strict (=== '') so a real password of "0"
     * is still encrypted normally.
     *
     * @param string $plaintext the credential to store, or '' for
     *      "no credential"
     * @return array ['nonce' => base64 string, 'ciphertext' =>
     *      base64 string]; both empty strings when $plaintext is ''
     */
    protected function encryptCredential(string $plaintext)
    {
        if ($plaintext === '') {
            return ['nonce' => '', 'ciphertext' => ''];
        }
        return CredentialCipher::encrypt($this->masterKey(), $plaintext);
    }
    /**
     * Loads this installation's master encryption key, generating and
     * storing a fresh one the first time it is needed. The key lives in
     * the private database (the MAIL_SECRET table) so that a dump of
     * the public database alone cannot decrypt stored passwords. The
     * raw key is cached on the model for the rest of the request so
     * repeated encrypt/decrypt calls do not re-query.
     *
     * @return string the raw 32-byte master key
     */
    protected function masterKey()
    {
        if ($this->master_key !== null) {
            return $this->master_key;
        }
        $db = $this->private_db;
        $sql = "SELECT KEY_VALUE FROM MAIL_SECRET WHERE KEY_ID = 1";
        $result = $db->execute($sql);
        $row = $result ? $db->fetchArray($result) : false;
        if ($row && !empty($row['KEY_VALUE'])) {
            $key = base64_decode($row['KEY_VALUE'], true);
            if ($key === false ||
                strlen($key) !== SODIUM_CRYPTO_SECRETBOX_KEYBYTES) {
                throw new \Exception(
                    "MailAccountModel: stored master key is corrupt");
            }
            $this->master_key = $key;
            return $key;
        }
        $key = random_bytes(SODIUM_CRYPTO_SECRETBOX_KEYBYTES);
        $insert = "INSERT INTO MAIL_SECRET (KEY_ID, KEY_VALUE) " .
            "VALUES (1, ?)";
        $db->execute($insert, [base64_encode($key)]);
        $this->master_key = $key;
        return $key;
    }
    /**
     * Inserts a new account row for the user. A non-empty IMAP or
     * SMTP password is encrypted before being persisted; the
     * plaintext is never stored, and an empty password is stored as
     * empty columns rather than encrypted. Returns the new account
     * id, or false on failure.
     *
     * @param int $user_id USER_ID the account belongs to
     * @param array $fields associative array with keys DISPLAY_NAME,
     *      PROVIDER, HOST, PORT, USERNAME, PASSWORD, TLS_MODE,
     *      DEFAULT_FOLDER, ALLOW_SELF_SIGNED, SMTP_HOST, SMTP_PORT,
     *      SMTP_USERNAME, SMTP_PASSWORD, SMTP_TLS_MODE
     * @return int|false the new MAIL_ACCOUNT.ID, or false
     */
    public function addAccount($user_id, $fields)
    {
        $encrypted = $this->encryptCredential($fields['PASSWORD'] ?? '');
        $smtp_encrypted = $this->encryptCredential(
            $fields['SMTP_PASSWORD'] ?? '');
        $now = time();
        $sql = "INSERT INTO MAIL_ACCOUNT " .
            "(USER_ID, DISPLAY_NAME, PROVIDER, HOST, PORT, USERNAME, " .
            "PASSWORD_NONCE, PASSWORD_CIPHERTEXT, TLS_MODE, " .
            "DEFAULT_FOLDER, ALLOW_SELF_SIGNED, SMTP_HOST, SMTP_PORT, " .
            "SMTP_USERNAME, SMTP_PASSWORD_NONCE, " .
            "SMTP_PASSWORD_CIPHERTEXT, SMTP_TLS_MODE, " .
            "CREATED_AT, UPDATED_AT) " .
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " .
            "?, ?)";
        $params = [
            $user_id,
            $fields['DISPLAY_NAME'] ?? '',
            $fields['PROVIDER'] ?? '',
            $fields['HOST'] ?? '',
            intval($fields['PORT'] ?? 993),
            $fields['USERNAME'] ?? '',
            $encrypted['nonce'],
            $encrypted['ciphertext'],
            $fields['TLS_MODE'] ?? 'imaps',
            $fields['DEFAULT_FOLDER'] ?? 'INBOX',
            empty($fields['ALLOW_SELF_SIGNED']) ? 0 : 1,
            $fields['SMTP_HOST'] ?? '',
            intval($fields['SMTP_PORT'] ?? 587),
            $fields['SMTP_USERNAME'] ?? '',
            $smtp_encrypted['nonce'],
            $smtp_encrypted['ciphertext'],
            $fields['SMTP_TLS_MODE'] ?? 'starttls',
            $now,
            $now,
        ];
        $result = $this->db->execute($sql, $params);
        if (!$result) {
            return false;
        }
        return $this->db->insertID('MAIL_ACCOUNT');
    }
    /**
     * Updates an account row. When $leave_password is true (the
     * typical case where the user submitted an Edit form with the
     * IMAP password field empty), the stored IMAP ciphertext is
     * preserved unchanged; likewise $leave_smtp_password for the
     * SMTP password. Otherwise the corresponding new password is
     * encrypted and persisted.
     *
     * @param int $account_id MAIL_ACCOUNT.ID to update
     * @param int $user_id USER_ID; updates only succeed when the
     *      account belongs to this user
     * @param array $fields associative array with the same keys as
     *      addAccount(); PASSWORD is honored only when
     *      $leave_password is false, SMTP_PASSWORD only when
     *      $leave_smtp_password is false
     * @param bool $leave_password when true, the existing stored
     *      IMAP ciphertext is kept and $fields['PASSWORD'] is ignored
     * @param bool $leave_smtp_password when true, the existing
     *      stored SMTP ciphertext is kept and $fields['SMTP_PASSWORD']
     *      is ignored
     * @return bool whether the row was updated
     */
    public function updateAccount($account_id, $user_id, $fields,
        $leave_password = true, $leave_smtp_password = true)
    {
        $existing = $this->getAccount($account_id, $user_id);
        if (!$existing) {
            return false;
        }
        $now = time();
        $set = "DISPLAY_NAME = ?, PROVIDER = ?, HOST = ?, PORT = ?, " .
            "USERNAME = ?, TLS_MODE = ?, DEFAULT_FOLDER = ?, " .
            "ALLOW_SELF_SIGNED = ?, SMTP_HOST = ?, SMTP_PORT = ?, " .
            "SMTP_USERNAME = ?, SMTP_TLS_MODE = ?, UPDATED_AT = ?";
        $params = [
            $fields['DISPLAY_NAME'] ?? $existing['DISPLAY_NAME'],
            $fields['PROVIDER'] ?? ($existing['PROVIDER'] ?? ''),
            $fields['HOST'] ?? $existing['HOST'],
            intval($fields['PORT'] ?? $existing['PORT']),
            $fields['USERNAME'] ?? $existing['USERNAME'],
            $fields['TLS_MODE'] ?? $existing['TLS_MODE'],
            $fields['DEFAULT_FOLDER'] ?? $existing['DEFAULT_FOLDER'],
            empty($fields['ALLOW_SELF_SIGNED']) ? 0 : 1,
            $fields['SMTP_HOST'] ?? $existing['SMTP_HOST'],
            intval($fields['SMTP_PORT'] ?? $existing['SMTP_PORT']),
            $fields['SMTP_USERNAME'] ?? $existing['SMTP_USERNAME'],
            $fields['SMTP_TLS_MODE'] ?? $existing['SMTP_TLS_MODE'],
            $now,
        ];
        if (!$leave_password) {
            $encrypted = $this->encryptCredential(
                $fields['PASSWORD'] ?? '');
            $set .= ", PASSWORD_NONCE = ?, PASSWORD_CIPHERTEXT = ?";
            $params[] = $encrypted['nonce'];
            $params[] = $encrypted['ciphertext'];
        }
        if (!$leave_smtp_password) {
            $smtp_encrypted = $this->encryptCredential(
                $fields['SMTP_PASSWORD'] ?? '');
            $set .= ", SMTP_PASSWORD_NONCE = ?, " .
                "SMTP_PASSWORD_CIPHERTEXT = ?";
            $params[] = $smtp_encrypted['nonce'];
            $params[] = $smtp_encrypted['ciphertext'];
        }
        $sql = "UPDATE MAIL_ACCOUNT SET $set WHERE ID = ? AND USER_ID = ?";
        $params[] = $account_id;
        $params[] = $user_id;
        return (bool) $this->db->execute($sql, $params);
    }
    /**
     * Updates only the DISPLAY_NAME column of a mail account.
     * Used by inline rename (long-press the account header in
     * the side panel). Scoped to user_id so a forged request
     * with another user's account_id is a no-op.
     *
     * @param int $account_id MAIL_ACCOUNT.ID to update
     * @param int $user_id USER_ID the account must belong to
     * @param string $display_name the new display name
     * @return bool whether the update statement succeeded
     */
    public function updateDisplayName($account_id, $user_id,
        $display_name)
    {
        $sql = "UPDATE MAIL_ACCOUNT SET DISPLAY_NAME = ?, " .
            "UPDATED_AT = ? WHERE ID = ? AND USER_ID = ?";
        return (bool) $this->db->execute($sql,
            [$display_name, time(), $account_id, $user_id]);
    }
    /**
     * Removes an account row. Scoped to the supplied user; a delete
     * with an account_id that does not belong to that user is a
     * no-op (returns true to keep the controller path simple, but
     * affects zero rows).
     *
     * @param int $account_id MAIL_ACCOUNT.ID to delete
     * @param int $user_id USER_ID the account must belong to
     * @return bool whether the delete statement succeeded
     */
    public function deleteAccount($account_id, $user_id)
    {
        $sql = "DELETE FROM MAIL_ACCOUNT " .
            "WHERE ID = ? AND USER_ID = ?";
        return (bool) $this->db->execute($sql,
            [$account_id, $user_id]);
    }
}
