<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 05.01.2024
 * Time: 8:49 AM
 */

namespace IMATHUZH\Qfq\External;


use IMATHUZH\Qfq\Core\Database\Database;
use IMATHUZH\Qfq\Core\Evaluate;
use IMATHUZH\Qfq\Core\Helper\EncryptDecrypt;
use IMATHUZH\Qfq\Core\Helper\HelperFile;
use IMATHUZH\Qfq\Core\Helper\Logger;
use IMATHUZH\Qfq\Core\Helper\OnString;
use IMATHUZH\Qfq\Core\Helper\Path;
use IMATHUZH\Qfq\Core\Imap\OAuth2TokenManager;
use IMATHUZH\Qfq\Core\Report\SendMail;
use IMATHUZH\Qfq\Core\Store\Store;

use Webklex\PHPIMAP\ClientManager;
use Webklex\PHPIMAP\Client;
use Webklex\PHPIMAP\Exceptions\ConnectionFailedException;
use Webklex\PHPIMAP\Exceptions\FolderFetchingException;
use Webklex\PHPIMAP\Exceptions\GetMessagesFailedException;
use Webklex\PHPIMAP\Exceptions\RuntimeException;

//TODO Logging
//TODO Fehlermeldungen: user/pw wrong, server unknown, protocol - werden die sauber geloggt?
//TODO Falls PW falsch ist scheint PHP bei 'disconnect' mit 100% CPU zu craschen: Ganz schlecht.
//TODO Check das folder selektiert werden kann.
//TODO Wenn der Prozess via Browser aufgerufen wird: Alles ausgeben.
//TODO Mail als gelesen/ ungelesen markieren
//TODO Mails mit Tags versehen (Farbe a'la Thunderbird)


/**
 * Class EmailSync
 * @package qfq
 */
class EmailSync {

    /**
     * @var Store
     */
    protected $store = null;

    /**
     * @var Evaluate
     */
    protected $evaluate = null;

    /**
     * @var Database[] - Array of Database instantiated class
     */
    private $dbArray = array();

    private $dbIndexQfq = '';
    private $dbIndexData = '';

    /**
     * @var string|null MODE_LIST_ALL_FOLDER | MODE_SYNC | MODE_ACTION
     */
    private $mode = null;
    /**
     * @var Client[]
     */
    private $client = array();
    private $emailSyncColumns = '';

    private $syncAttachments = false;
    private $attachmentMaxBytes = 0;

    private $imapLogLevelMin = null;
    private $imapLogAbsolute = null;

    /**
     * AutoCron constructor.
     *
     * @param string $mode
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function __construct($mode = null) {

        $this->mode = $mode;

        mb_internal_encoding("UTF-8");

//        set_error_handler("\\IMATHUZH\\Qfq\\Core\\Exception\\ErrorHandler::exception_error_handler");

        $this->store = Store::getInstance();
        $this->store->fillStoreSystemBySql(); // Do this after the DB-update

        // Set Log Mode
        $this->imapLogLevelMin = Logger::nameToLevel($this->store->getVar(SYSTEM_IMAP_LOG_MODE, STORE_SYSTEM));
        $this->syncAttachments = $this->store->getVar(SYSTEM_IMAP_SYNC_ATTACHMENTS, STORE_SYSTEM) == '1';
        $maxMb = (int)$this->store->getVar(SYSTEM_IMAP_ATTACHMENT_MAX_MB, STORE_SYSTEM);
        $this->attachmentMaxBytes = ($maxMb > 0 ? $maxMb : ES_ATTACHMENT_MAX_MB_DEFAULT) * 1024 * 1024;
        $this->imapLogAbsolute = Path::absoluteImapLogFile();

        $this->dbIndexQfq = $this->store->getVar(SYSTEM_DB_INDEX_QFQ, STORE_SYSTEM);
        $this->dbIndexData = $this->store->getVar(SYSTEM_DB_INDEX_DATA, STORE_SYSTEM);

        $this->dbArray[$this->dbIndexData] = new Database($this->dbIndexData);
        // extractImapCredentials() reads table Setting via dbIndexQfq - instantiate it too if distinct.
        if (!isset($this->dbArray[$this->dbIndexQfq])) {
            $this->dbArray[$this->dbIndexQfq] = new Database($this->dbIndexQfq);
        }

        $this->evaluate = new Evaluate($this->store, $this->dbArray[$this->dbIndexData]);
        $this->emailSyncColumns = "`" . ES_COL_ACCOUNT . "`,`" . ES_COL_MESSAGE_ID . "`,`" . ES_COL_FOLDER_PATH . "`,`" .
            ES_COL_SENDER . "`,`" . ES_COL_RECEIVER . "`,`" . ES_COL_CC . "`,`" . ES_COL_SUBJECT . "`,`" .
            ES_COL_BODYTEXT . "`,`" . ES_COL_BODYHTML . "`,`" . ES_COL_ATTACHMENT . "`,`" . ES_COL_HEADER . "`,`" .
            ES_COL_FLAGS . "`,`" . ES_COL_MAIL_DATE . "`";

    }

    /**
     * @param array $accountList
     * @return string
     * @throws \CodeException
     * @throws \UserFormException
     * @throws \Webklex\PHPIMAP\Exceptions\ConnectionFailedException
     * @throws \Webklex\PHPIMAP\Exceptions\FolderFetchingException
     * @throws \Webklex\PHPIMAP\Exceptions\RuntimeException
     */
    private function showAllAccountAllFolder(array $accountList): string {

        $content = '';
        foreach ($accountList as $accountName) {
            if (null === ($folders = $this->client[$accountName]->getFolders(false))) {
                Logger::logMessage(Logger::linePreTimestamp() . "Failed to fetch folder list: $accountName", $this->imapLogAbsolute, FILE_MODE_APPEND, LOG_LEVEL_ERROR, $this->imapLogLevelMin);
                return '';
            }

            foreach ($folders as $folder) {
                $content .= "[$accountName]: path=" . $folder->path . "\n";
                if ($this->mode == ES_MODE_LIST_ALL_FOLDER_ALL_MAIL) {
                    // Header only and capped: a full-folder body fetch overflows the server.
                    $messages = $folder->query()->all()->setFetchBody(false)->limit(IMAP_MAIL_LIST_LIMIT, 1)->get();
                    foreach ($messages as $message) {
                        $content .= "..." . $message->getSubject() . "\n";
                    }
                    $content .= "\n";
                }
            }
        }
        return $content;
    }

    /**
     * @param string $accountName
     * @param string $folderName
     * @param \Webklex\PHPIMAP\Message $msg
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function syncMessage2Db(string $accountName, string $folderName, \Webklex\PHPIMAP\Message $msg): void {

        $arr = $this->message2array($accountName, $folderName, $msg);
        $sql = "SELECT id FROM " . ES_TABLENAME . " WHERE messageId=?";
        if (empty($ds = $this->dbArray[$this->dbIndexData]->sql($sql, ROW_EXPECT_0_1, [$arr[ES_COL_MESSAGE_ID]]))) {
            $sql = "INSERT INTO `" . ES_TABLENAME . "` (" . $this->emailSyncColumns . ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            $this->dbArray[$this->dbIndexData]->sql($sql, ROW_REGULAR, $arr);
        }
    }

    /**
     * @param Message $message
     * @return array
     */
    private function message2array(string $accountName, string $folderName, \Webklex\PHPIMAP\Message $msg): array {
//
//        @method Attribute getPriority()
//        * @method Attribute getSubject()
//        * @method Attribute getMessageId()
//        * @method Attribute getMessageNo()
//        * @method Attribute getReferences()
//        * @method Attribute getDate()
//        * @method Attribute getFrom()
//        * @method Attribute getTo()
//        * @method Attribute getCc()
//        * @method Attribute getBcc()
//        * @method Attribute getReplyTo()
//        * @method Attribute getInReplyTo()
//        * @method Attribute getSender()

        // Decode RFC 2047 MIME encoded-words (subject/name fields), then truncate to the VARCHAR
        // widths (qfqTableEmailSync.sql) so an oversized header can't fail the INSERT (strict mode).
        $arr[ES_COL_ACCOUNT] = mb_substr($accountName, 0, 64);
        $arr[ES_COL_MESSAGE_ID] = mb_substr((string)$msg->getMessageId(), 0, 255);
        $arr[ES_COL_FOLDER_PATH] = mb_substr($folderName, 0, 128);
        $arr[ES_COL_SENDER] = mb_substr(OnString::decodeMimeHeader((string)$msg->getFrom()), 0, 128);
        $arr[ES_COL_RECEIVER] = mb_substr(OnString::decodeMimeHeader((string)$msg->getTo()), 0, 2048);
        $arr[ES_COL_CC] = mb_substr(OnString::decodeMimeHeader((string)$msg->getCc()), 0, 2048);
        $arr[ES_COL_SUBJECT] = mb_substr(OnString::decodeMimeHeader((string)$msg->getSubject()), 0, 1024);
        $arr[ES_COL_BODYTEXT] = $msg->getTextBody() ?? '';
        $arr[ES_COL_BODYHTML] = $msg->getHTMLBody() ?? '';

        $arr[ES_COL_ATTACHMENT] = $this->buildAttachments($msg, $accountName);

        $ii = (array)$msg->getHeader();
        $arr[ES_COL_HEADER] = $ii['raw'];

//        $ii=(array)$msg->getFlags();
//        $jj=$ii->items;
        $arr[ES_COL_FLAGS] = '';
//        $arr[ES_COL_FLAGS] = implode(',', $ii);

        // Real send/receive time from the mail's Date header (NULL if missing/unparseable). The
        // report shows this as 'Received'; created/modified are only the DB sync timestamps.
        $arr[ES_COL_MAIL_DATE] = self::mailDateTime($msg);

//        foreach($arr as $value){
//            $new[]=$value;
//        }

        return $arr;
    }

    /**
     * Saves the mail's attachments to disk and returns their metadata as a JSON string for the
     * EmailSync.attachment column. Off unless the ext_conf flag imapSyncAttachments is 'yes'.
     *
     * Files go to <project>/emailSync/<account>/<hash-of-messageId>/<filename> (protected dir).
     * Attachments larger than the configured size limit keep metadata only (file = null). A failure
     * on a single attachment is logged and skipped - it must not abort the sync.
     *
     * @param \Webklex\PHPIMAP\Message $msg
     * @param string $account
     * @return string - JSON array [{name,size,mime,disposition,file}], or '' if none / disabled
     */
    private function buildAttachments($msg, string $account): string {

        if (!$this->syncAttachments) {
            return '';
        }

        try {
            $attachments = $msg->getAttachments();
        } catch (\Exception $e) {
            return '';
        }
        if ($attachments === null || $attachments->count() === 0) {
            return '';
        }

        $messageId = (string)$msg->getMessageId();
        $hash = md5($messageId !== '' ? $messageId : (string)$msg->getUid());
        $accDir = self::sanitizeAttachmentFilename(self::imapAccountKey($account));
        // Group by mail year to keep the per-account directory from growing without bound.
        $absDir = Path::absoluteProject(ES_ATTACHMENT_DIR, self::mailYear($msg), $accDir, $hash);

        $meta = [];
        $index = 0;
        foreach ($attachments as $attachment) {
            $index++;
            $rawName = OnString::decodeMimeHeader((string)$attachment->getName());
            // Readable name for the metadata (umlauts kept, control chars stripped); ASCII-safe
            // name for the file on disk.
            $displayName = mb_substr(trim((string)preg_replace('/[\x00-\x1F\x7F]+/u', '', $rawName)), 0, 200);
            $safeName = self::sanitizeAttachmentFilename($rawName);
            if ($displayName === '') {
                $displayName = 'attachment-' . $index;
            }
            if ($safeName === '') {
                $safeName = 'attachment-' . $index;
            }
            $size = (int)$attachment->getSize();

            $entry = [
                'name' => $displayName,
                'size' => $size,
                // getContentType() is the declared header (no content fetch, unlike getMimeType()).
                'mime' => (string)($attachment->getContentType() ?? ''),
                'disposition' => (string)($attachment->getDisposition() ?? ''),
                'file' => null,
            ];

            if ($size <= $this->attachmentMaxBytes) {
                try {
                    HelperFile::mkDirParent($absDir . '/.');
                    // Avoid collisions between same-named attachments in one mail.
                    $saveName = file_exists($absDir . '/' . $safeName) ? $index . '-' . $safeName : $safeName;
                    if ($attachment->save($absDir, $saveName)) {
                        $entry['file'] = self::appRelative($absDir . '/' . $saveName);
                    }
                } catch (\Exception $e) {
                    Logger::logMessage(Logger::linePreTimestamp() . "Attachment save failed ('$name'): " . $e->getMessage(),
                        $this->imapLogAbsolute, FILE_MODE_APPEND, LOG_LEVEL_ERROR, $this->imapLogLevelMin);
                }
            }

            $meta[] = $entry;
        }

        // Unescaped slashes/unicode: readable paths ('fileadmin/...' not 'fileadmin\/...') and
        // readable non-ASCII names, consistent with the other file/upload tables.
        return json_encode($meta, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) ?: '';
    }

    /**
     * The mail's year (from its date), for grouping attachment directories. Falls back to the
     * current year if the mail date is missing or unparseable.
     *
     * @param \Webklex\PHPIMAP\Message $msg
     * @return string - 4-digit year
     */
    private static function mailYear($msg): string {
        try {
            $date = (string)$msg->getDate();
            if ($date !== '') {
                $year = (new \DateTimeImmutable($date))->format('Y');
                if (preg_match('/^\d{4}$/', $year)) {
                    return $year;
                }
            }
        } catch (\Exception $e) {
            // fall through to the current year
        }
        return date('Y');
    }

    /**
     * The mail's Date header as a MySQL DATETIME string ('Y-m-d H:i:s'), or NULL if the header is
     * missing or unparseable. Stored in EmailSync.mailDate (the real send/receive time).
     *
     * @param \Webklex\PHPIMAP\Message $msg
     * @return string|null
     */
    private static function mailDateTime($msg): ?string {
        try {
            $date = $msg->getDate();
            // Empty guard first: Attribute::toDate() on a missing date runs Carbon::parse(null),
            // which returns "now" - we must return NULL instead.
            if ((string)$date === '') {
                return null;
            }
            // webklex parsed the Date header into a Carbon that keeps the sender's offset (e.g.
            // +0800). Convert to the server timezone so the stored value is comparable across
            // senders and matches what a mail client shows in that timezone. The previous
            // '(string)getDate()' dropped the offset and stored the raw sender wall-clock time.
            return $date->toDate()
                ->setTimezone(new \DateTimeZone(date_default_timezone_get()))
                ->format('Y-m-d H:i:s');
        } catch (\Throwable $e) {
            // unparseable date -> NULL
        }
        return null;
    }

    /**
     * Sanitizes an attachment file name for safe filesystem use (no path traversal, safe chars).
     *
     * @param string $name
     * @return string
     */
    public static function sanitizeAttachmentFilename(string $name): string {
        $name = basename(str_replace(['\\', "\0"], '', $name)); // strip path + null bytes
        $name = preg_replace('/[^A-Za-z0-9._@ -]/u', '_', $name); // keep only safe chars
        $name = trim((string)$name, ' .'); // no leading/trailing dot or space (hidden files, '..')
        return mb_substr($name, 0, 200);
    }

    /**
     * Makes an absolute path relative to the app root (for the stored 'file' value). Falls back to
     * the absolute path if it is outside the app (project configured outside the app directory).
     *
     * @param string $pathFileNameAbsolute
     * @return string
     * @throws \UserFormException
     */
    private static function appRelative(string $pathFileNameAbsolute): string {
        $app = rtrim(Path::absoluteApp(), '/') . '/';
        return strpos($pathFileNameAbsolute, $app) === 0
            ? substr($pathFileNameAbsolute, strlen($app))
            : $pathFileNameAbsolute;
    }

    /**
     * @param $accountList
     * @param $mode   ES_MODE_SYNC|ES_MODE_SYNC_IDLE
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws ConnectionFailedException
     * @throws FolderFetchingException
     * @throws GetMessagesFailedException
     * @throws RuntimeException
     */
    private function syncEmailToDb($accountList, $mode = ES_MODE_SYNC): void {
        $errorAlreadyLoggedUnknownFolder = false;
        $cycle = 0;

        while (true) {
            // Full reconciliation walk every Nth cycle, cheap incremental search in between.
            $fullWalk = ($cycle % ES_SYNC_FULL_WALK_EVERY_CYCLES) === 0;

            foreach ($accountList as $accountName => $value) {

                // Skip accounts that failed to connect (already logged in connectAll).
                if (empty($this->client[$accountName])) {
                    continue;
                }

                # "account1=INBOX,Sent|account2=INBOX"
                $folderNameList = explode(',', $value);
                foreach ($folderNameList as $folderName) {
                    $folderName = trim($folderName);
                    if ($folderName === '') {
                        continue;
                    }

                    if (null === ($folder = $this->client[$accountName]->getFolderByPath($folderName))) {
                        if (!$errorAlreadyLoggedUnknownFolder) {
                            Logger::logMessage(Logger::linePreTimestamp() . "Folder not found: $value:$folderName", $this->imapLogAbsolute, FILE_MODE_APPEND, LOG_LEVEL_ERROR, $this->imapLogLevelMin);
                            Logger::logMessage(Logger::linePreTimestamp() . "Available folder:\n" . $this->showAllAccountAllFolder([$accountName]), $this->imapLogAbsolute, FILE_MODE_APPEND, LOG_LEVEL_DEBUG, $this->imapLogLevelMin);
                            # do not log over and over again
                            $errorAlreadyLoggedUnknownFolder = true;
                        }
                        continue;
                    }

                    try {
                        $this->syncFolderToDb($accountName, $folderName, $folder, $fullWalk);
                    } catch (\Exception $e) {
                        // Log and continue: a transient folder error must not kill the daemon.
                        $msg = "Email sync failed for account '" . ($this->client[$accountName]->username ?: $accountName)
                            . "', folder '$folderName': " . $e->getMessage();
                        Logger::logMessage(Logger::linePreTimestamp() . $msg, $this->imapLogAbsolute, FILE_MODE_APPEND, LOG_LEVEL_ERROR, $this->imapLogLevelMin);
                        echo $msg . "\n";
                    }
                }
            }

            switch ($mode) {
                case ES_MODE_SYNC:
                    return;
                case ES_MODE_SYNC_POLL:
                default:
                    sleep(10);
                    break;
            }
            $cycle++;

            // Re-evaluate re-authorization state ~daily so a long-running daemon keeps reminding
            // about accounts whose refresh token died (the marker caps mails to one per 24h).
            if (($cycle % ES_REAUTH_CHECK_EVERY_CYCLES) === 0) {
                $this->notifyReauthIfNeeded(
                    self::extractImapCredentials($this->store, $this->dbArray, $this->dbIndexQfq));
            }
        }
    }

    /**
     * Incremental, chunked sync of one folder.
     *
     * Pass 1 walks the folder with header-only fetches in pages of ES_SYNC_CHUNK_SIZE - fetching
     * a large folder including bodies in one go exceeds server limits/read timeouts (SSL
     * connection reset). Only messages unknown to the EmailSync table (messageId) are then
     * fetched completely, one by one via UID, and stored.
     *
     * Without $fullWalk the IMAP SEARCH is restricted to mails younger than the newest stored
     * mail minus ES_SYNC_SINCE_BUFFER_DAYS - that makes the 10s poll cycle nearly free. Mails
     * moved into the folder keep their (old) internal date and escape this filter; the periodic
     * full walk picks them up.
     *
     * @param string $accountName - account key in $this->client
     * @param string $folderName
     * @param \Webklex\PHPIMAP\Folder $folder
     * @param bool $fullWalk - true: walk the whole folder, false: incremental since-search
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function syncFolderToDb(string $accountName, string $folderName, $folder, bool $fullWalk): void {

        // Store the real username (not the sanitized account key) - EmailSync.account is user
        // facing and referenced by the report actions.
        $username = $this->client[$accountName]->username ?: $accountName;

        $sinceDate = $fullWalk ? null : $this->newestStoredMailDate($username, $folderName);

        // Search the folder ONCE, then paginate the UID list locally. webklex's ->limit($n, $page)
        // ->get() re-runs a FULL-folder IMAP SEARCH on every page (get() -> search()), i.e.
        // O(pages x folder size) - crippling on large folders / throttled Office365. Searching once
        // and fetching each chunk by an explicit UID set (whereUidIn) keeps the walk linear.
        $query = $folder->query();
        if ($sinceDate === null) {
            $query->all();
        } else {
            $query->whereSince($sinceDate);
        }
        // search() returns the matching UIDs ascending (IMAP UID SEARCH), i.e. oldest arrival first.
        // array_chunk drives the walk, so unparseable messages that webklex drops later (per chunk,
        // when building them) are simply skipped and the loop still terminates - the reason the old
        // code sized pages from the SEARCH count, not the built MessageCollection.
        $uids = $query->setFetchBody(false)->search()->all();

        // Walk newest first (highest UID = most recent arrival): during a large backfill the recent
        // mails then land in the table right away and the long history fills in behind them, instead
        // of the newest mail being processed last.
        $uids = array_reverse($uids);

        $db = $this->dbArray[$this->dbIndexData];

        foreach (array_chunk($uids, ES_SYNC_CHUNK_SIZE) as $chunkUids) {
            // Header-only fetch for exactly this chunk's UIDs - a targeted search, not a folder walk.
            /** @var \Webklex\PHPIMAP\Support\MessageCollection $headers */
            $headers = $folder->query()->whereUidIn($chunkUids)->setFetchBody(false)->get();

            // Batch the whole chunk's INSERTs into ONE commit. Autocommit would fsync per row and
            // saturate the disk during a large backfill. The dedup SELECTs run inside the same
            // transaction, so they still see this chunk's own inserts (within-chunk duplicates stay
            // skipped). On error the chunk is rolled back and retried next run (dedup by messageId).
            $db->beginTransaction();
            try {
                /** @var \Webklex\PHPIMAP\Message $header */
                foreach ($headers as $header) {
                    $messageId = (string)$header->getMessageId();
                    if ($messageId !== '' && $this->messageIdExists($messageId)) {
                        continue;
                    }

                    try {
                        // Unknown mail: now fetch it completely.
                        $message = $folder->query()->getMessageByUid($header->getUid());
                    } catch (\Exception $e) {
                        // Vanished between header walk and fetch (moved/deleted meanwhile): skip.
                        continue;
                    }

                    $this->syncMessage2Db($username, $folderName, $message);
                    echo $message->getSubject() . "\n";

                    // webklex's Message <-> Attachment form a reference cycle (the Attachment holds
                    // its Message), which PHP's refcount GC cannot free on its own. Drop the strong
                    // ref now; the cycle itself is reclaimed by gc_collect_cycles() below.
                    unset($message);
                }
                $db->commit();
            } catch (\Throwable $e) {
                $db->rollback();
                throw $e;
            }

            // Reclaim this chunk's reference cycles so a large import / long-running daemon stays
            // memory-bounded - PHP only frees cycle memory when the collector actually runs, and its
            // automatic threshold is reached late when each object holds a whole fetched mail.
            gc_collect_cycles();

            // Pace the full-walk backfill so a large folder can't saturate disk/network I/O in one
            // sustained burst. The fast incremental poll (fullWalk = false) is left untouched.
            if ($fullWalk && ES_SYNC_THROTTLE_MS > 0) {
                usleep(ES_SYNC_THROTTLE_MS * 1000);
            }
        }
    }

    /**
     * 'created' of the newest stored mail for account+folder, minus the safety buffer.
     * Null if nothing is stored yet (triggers a full walk).
     *
     * @param string $username
     * @param string $folderName
     * @return \DateTime|null
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function newestStoredMailDate(string $username, string $folderName): ?\DateTime {
        $sql = "SELECT MAX(`created`) AS newest FROM `" . ES_TABLENAME . "` WHERE `" . ES_COL_ACCOUNT . "`=? AND `" . ES_COL_FOLDER_PATH . "`=?";
        $row = $this->dbArray[$this->dbIndexData]->sql($sql, ROW_EXPECT_1, [$username, $folderName]);

        if (empty($row['newest'])) {
            return null;
        }

        return (new \DateTime($row['newest']))->modify('-' . ES_SYNC_SINCE_BUFFER_DAYS . ' days');
    }

    /**
     * @param string $messageId
     * @return bool
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function messageIdExists(string $messageId): bool {
        $sql = "SELECT id FROM " . ES_TABLENAME . " WHERE messageId=?";
        return !empty($this->dbArray[$this->dbIndexData]->sql($sql, ROW_EXPECT_0_1, [$messageId]));
    }

    /**
     * @param $mode    ES_MODE_LIST_ALL_FOLDER | ES_MODE_LIST_ALL_FOLDER_ALL_MAIL | ES_MODE_SYNC | ES_MODE_SYNC_POLL
     * @param $accountListConfig
     * @param $accountListQfq
     * @return array
     * @throws \UserFormException
     */
    private function getAccountListToProcess($mode, $accountsConfig, $credentials): array {
        $accountList = array();

        // Create a lookup map for folders by account key (sanitized username)
        $folderMap = [];
        foreach ($credentials as $credential) {
            $accountName = !empty($credential[IMAP_CRED_USERNAME]) ? self::imapAccountKey($credential[IMAP_CRED_USERNAME]) : "account_" . count($folderMap);
            $folderMap[$accountName] = $credential[IMAP_CRED_FOLDER] ?? 'INBOX';
        }

        switch ($mode) {
            case ES_MODE_LIST_ALL_FOLDER:
            case ES_MODE_LIST_ALL_FOLDER_ALL_MAIL:
                // For listing, just use account names with flag
                $accountList = array_fill_keys(array_keys($accountsConfig), 1);
                break;
            case ES_MODE_SYNC:
            case ES_MODE_SYNC_POLL:
                // For syncing, use account names with their folders
                foreach (array_keys($accountsConfig) as $accountName) {
                    $accountList[$accountName] = $folderMap[$accountName] ?? 'INBOX';
                }
                break;
            case ES_MODE_ACTION:
                throw new \UserFormException("TBD");
                break;
            default:
                break;
        }

        return $accountList;
    }

    /**
     * @param ClientManager $cm
     * @param $accountList
     * @return void
     * @throws \Webklex\PHPIMAP\Exceptions\ConnectionFailedException
     * @throws \Webklex\PHPIMAP\Exceptions\MaskNotFoundException
     */
    private function connectAll(ClientManager &$cm, $accountList): void {

        // Open connections. A failing account is logged and skipped (its client is removed), so
        // one broken account (expired token, wrong host) does not take down the sync of the
        // healthy ones - callers must guard on isset($this->client[$accountName]).
        foreach ($accountList as $accountName => $value) {
            $client = $cm->account($accountName);
            try {
                $client->connect();
                $this->client[$accountName] = $client;
            } catch (\Exception $e) {
                // Include the actually used host: mismatches between the form value and what the
                // daemon reads (duplicate Setting rows, unsaved edits) show up immediately.
                $host = $client->host ?? '?';
                $username = $client->username ?: $accountName;
                $msg = "IMAP connect failed for account '$username' (host '$host'): " . $e->getMessage();
                // Server rejects AUTHENTICATE XOAUTH2: almost always a wrong host for an OAuth2
                // account - the token is only accepted by the provider's own IMAP server.
                if (stripos($e->getMessage(), 'mechanism not supported') !== false) {
                    $msg .= " - The IMAP server does not offer SASL XOAUTH2. For Outlook/Office365 accounts"
                        . " the host must be 'outlook.office365.com'.";
                }
                Logger::logMessage(Logger::linePreTimestamp() . $msg, $this->imapLogAbsolute, FILE_MODE_APPEND, LOG_LEVEL_ERROR, $this->imapLogLevelMin);
                echo $msg . "\n";
                unset($this->client[$accountName]);
            }
        }
    }

    /**
     * Iterates over all AutoCron jobs and fire pending.
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \ShellException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function process() {
        // Generate dynamic IMAP configuration from database
        $credentials = $this->extractImapCredentials($this->store, $this->dbArray, $this->dbIndexQfq);

//        $this->mode = MODE_LIST_ALL_FOLDER_ALL_MAIL;

        // Nothing to do?
        if (($this->mode == ES_MODE_SYNC || $this->mode == ES_MODE_SYNC_POLL) && empty($credentials)) {
            # Sync without QFQ configured accounts/folders: nothing to do. Say so instead of
            # exiting silently - a daemon that vanishes right after start is hard to diagnose.
            $msg = "Email sync: no accounts configured (table Setting, type='credentials') - exiting.";
            Logger::logMessage(Logger::linePreTimestamp() . $msg, $this->imapLogAbsolute, FILE_MODE_APPEND, LOG_LEVEL_ALL, $this->imapLogLevelMin);
            echo $msg . "\n";
            return;
        }

        // Notify the admin about delegated accounts whose refresh token died (re-auth needed).
        if ($this->mode == ES_MODE_SYNC || $this->mode == ES_MODE_SYNC_POLL) {
            $this->notifyReauthIfNeeded($credentials);
        }

        $imapConfig = $this->generateImapConfigArray($credentials);
        $cm = new ClientManager($imapConfig);

        $accountList = $this->getAccountListToProcess($this->mode, $imapConfig['accounts'], $credentials);
        $this->connectAll($cm, $accountList);

        // Process
        switch ($this->mode) {
            case ES_MODE_LIST_ALL_FOLDER:
            case ES_MODE_LIST_ALL_FOLDER_ALL_MAIL:
                // Dump all configured accounts
                echo str_replace($this->showAllAccountAllFolder($accountList), '\n', '<br>');
                break;
            case ES_MODE_SYNC:
            case ES_MODE_SYNC_POLL:
                $this->syncEmailToDb($accountList, $this->mode);
                break;
            case ES_MODE_ACTION:
                break;
            default:
        }
    }

    /**
     * Extract imap credentials from Setting table.
     *
     * @param Store $store
     * @param Database[] $db
     * @param string $dbIndex
     * @param string|null $onlyUsername - when set, only this account's OAuth2 token is resolved
     *   (locking the token file, possibly refreshing). All other accounts get an empty password
     *   - they are still listed (webklex needs the account map) but never connected in this call.
     *   Used by the report actions (Imap.php), which touch exactly one account per request, to
     *   avoid resolving/refreshing every account's token on every click.
     * @return array
     * @throws \CodeException
     * @throws \UserFormException
     */
    public static function extractImapCredentials($store, $db, $dbIndex, ?string $onlyUsername = null): array {
        $encryptionKey = $store->getVar(SYSTEM_ENCRYPTION_KEY, STORE_SYSTEM, SANITIZE_ALLOW_ALL);

        $imapValidateCert = true;
        $imapCredentials = [];

        $sql = "SELECT `feUser`, `view`, `name`, `name2` FROM `Setting` WHERE `type` = ?";
        // ROW_REGULAR always returns a (possibly empty) list of rows.
        $result = $db[$dbIndex]->sql($sql, ROW_REGULAR, [SETTING_TABLE_COLUMN_TYPE_CREDENTIALS], "No IMAP credentials found in Setting table.");

        if (!is_array($result)) {
            return [];
        }

        foreach ($result as $row) {
            $username = $row['feUser'] ?? '';

            if ($onlyUsername !== null && $username !== $onlyUsername) {
                // Not the targeted account: skip token resolution entirely (no file lock/refresh).
                $password = '';
                $authentication = null;
                $reauthNeeded = false;
                $tokenPath = '';
            } else {
                list($password, $authentication, $reauthNeeded, $tokenPath) = self::resolveImapAuthentication(
                    EncryptDecrypt::getPlaintext($row['view'] ?? '', $encryptionKey), $store);
            }

            list($host, $port, $encryption) = self::parseImapHost($row['name'] ?? '');

            $imapCredentials[] = [
                IMAP_CRED_HOST => $host,
                IMAP_CRED_USERNAME => $username,
                IMAP_CRED_PASSWORD => $password,
                IMAP_CRED_AUTHENTICATION => $authentication,
                IMAP_CRED_REAUTH_NEEDED => $reauthNeeded,
                IMAP_CRED_TOKEN_PATH => $tokenPath,
                IMAP_CRED_FOLDER => $row['name2'] ?? '',
                IMAP_CRED_PORT => $port,
                IMAP_CRED_ENCRYPTION => $encryption,
                IMAP_CRED_VALIDATE_CERT => $imapValidateCert
            ];
        }

        return $imapCredentials;
    }

    /**
     * Account key for the Webklex\PHPIMAP config.
     *
     * Webklex resolves 'accounts.<name>' with dot notation (Config::get() explodes the key on
     * '.'): a key containing dots - i.e. every e-mail address - is never found and silently
     * falls back to the package default account (host 'localhost'). Therefore dots are replaced.
     * Idempotent: an already sanitized key maps to itself.
     *
     * @param string $username
     * @return string
     */
    public static function imapAccountKey(string $username): string {
        return str_replace('.', '_', trim($username));
    }

    /**
     * Parses the Setting 'name' column as 'host[:port]'.
     *
     * Default is port 993 with implicit TLS ('ssl'). Port 143 connects plain and upgrades via
     * STARTTLS - e.g. 'imap.example.com:143'. Any other explicit port keeps implicit TLS.
     *
     * IPv6 literals must be given in brackets to add a port ('[2001:db8::1]:143'); a bare IPv6
     * literal ('2001:db8::1', '::1') has more than one colon and is taken as the host as-is - the
     * colons must not be mistaken for a port separator.
     *
     * @param string $name
     * @return array - [host, port, encryption]
     */
    public static function parseImapHost(string $name): array {
        $host = trim($name);
        $port = 993;

        if (preg_match('/^\[(.+)\](?::(\d+))?$/', $host, $matches) === 1) {
            // Bracketed IPv6 literal, optional port: [2001:db8::1] or [2001:db8::1]:143
            $host = $matches[1];
            if (isset($matches[2]) && $matches[2] !== '') {
                $port = (int)$matches[2];
            }
        } elseif (substr_count($host, ':') === 1 && preg_match('/^(.+):(\d+)$/', $host, $matches) === 1) {
            // Exactly one colon: 'host:port' (hostname or IPv4). A bare IPv6 literal has 2+ colons
            // and is left untouched.
            $host = $matches[1];
            $port = (int)$matches[2];
        }

        $encryption = ($port === 143) ? 'starttls' : 'ssl';

        return [$host, $port, $encryption];
    }

    /**
     * Maps the stored password to a pair [password, authentication] for Webklex\PHPIMAP.
     *
     * A regular password is passed through unchanged (authentication=null).
     * The convention 'oauth2token:<pathFileName>' switches the account to SASL XOAUTH2
     * (authentication='oauth'): the password becomes a fresh OAuth2 access token taken from the
     * given token file - see Core/Imap/OAuth2TokenManager.
     *
     * If fetching the access token fails, the error is logged and the account gets an empty
     * password: connect fails for this account only, all other accounts remain functional.
     *
     * @param string $password - decrypted content of Setting.view
     * @param Store $store
     * @return array - [password, authentication, reauthNeeded, tokenPathAbsolute]. reauthNeeded is
     *   true only when a delegated account's refresh token was rejected (interactive re-auth needed).
     *   tokenPathAbsolute is the resolved token file path for OAuth accounts ('' otherwise) - used
     *   for the re-auth notification marker.
     * @throws \CodeException
     * @throws \UserFormException
     */
    private static function resolveImapAuthentication(string $password, $store): array {

        if (strpos($password, IMAP_OAUTH2_TOKEN_PREFIX) !== 0) {
            return [$password, null, false, ''];
        }

        $pathFileNameToken = trim(substr($password, strlen(IMAP_OAUTH2_TOKEN_PREFIX)));
        $tokenPathAbsolute = self::safeResolveTokenPath($pathFileNameToken);

        try {
            $tokenManager = new OAuth2TokenManager(
                trim((string)$store->getVar(SYSTEM_IMAP_OAUTH2_CLIENT_ID, STORE_SYSTEM)));
            return [$tokenManager->getAccessToken($pathFileNameToken), IMAP_AUTHENTICATION_OAUTH, false, $tokenPathAbsolute];
        } catch (\Exception $e) {
            $logLevelMin = Logger::nameToLevel($store->getVar(SYSTEM_IMAP_LOG_MODE, STORE_SYSTEM));
            Logger::logMessage(Logger::linePreTimestamp() . 'OAuth2 access token failed: ' . $e->getMessage(),
                Path::absoluteImapLogFile(), FILE_MODE_APPEND, LOG_LEVEL_ERROR, $logLevelMin);
            return ['', IMAP_AUTHENTICATION_OAUTH, OAuth2TokenManager::isReauthError($e), $tokenPathAbsolute];
        }
    }

    /**
     * Resolves the token file path to absolute, returning '' on any failure (e.g. empty path) -
     * only used to locate the notification marker, so a failure must not break credential loading.
     *
     * @param string $pathFileNameToken
     * @return string
     */
    private static function safeResolveTokenPath(string $pathFileNameToken): string {
        try {
            return OAuth2TokenManager::resolveTokenPath($pathFileNameToken);
        } catch (\Exception $e) {
            return '';
        }
    }

    /**
     * Notifies the admin (once per ES_REAUTH_NOTIFY_INTERVAL_SECONDS) about delegated OAuth
     * accounts whose refresh token was rejected and which therefore need an interactive
     * re-authorization (key button). Opt-in via the ext_conf recipient SYSTEM_IMAP_NOTIFICATION_EMAIL.
     *
     * De-duplication uses a sidecar marker file next to the token file (its mtime = last notified
     * time). On recovery the marker is removed, so a later outage notifies again. An account that
     * is removed from the Setting table is simply no longer iterated (no further mails).
     *
     * @param array $credentials - as returned by extractImapCredentials()
     * @return void
     * @throws \CodeException
     * @throws \UserFormException
     */
    private function notifyReauthIfNeeded(array $credentials): void {

        $recipient = trim((string)$this->store->getVar(SYSTEM_IMAP_NOTIFICATION_EMAIL, STORE_SYSTEM));

        foreach ($credentials as $credential) {
            $tokenPath = $credential[IMAP_CRED_TOKEN_PATH] ?? '';
            if ($tokenPath === '') {
                continue; // not an OAuth account (or path unresolvable)
            }
            $marker = $tokenPath . ES_REAUTH_MARKER_SUFFIX;

            if (!empty($credential[IMAP_CRED_REAUTH_NEEDED])) {
                // Feature is opt-in: without a configured recipient just leave the imap.log entry.
                if ($recipient !== '' && self::shouldNotifyReauth($marker, time(), ES_REAUTH_NOTIFY_INTERVAL_SECONDS)) {
                    $this->sendReauthMail($recipient, (string)($credential[IMAP_CRED_USERNAME] ?? ''), $tokenPath);
                    @touch($marker);
                }
            } elseif (@file_exists($marker)) {
                // Account works again: clear the marker so a future outage notifies afresh.
                @unlink($marker);
            }
        }
    }

    /**
     * True if no notification was sent yet for this outage, or the last one is older than $interval.
     * Pure/static for testability - the marker file's mtime is the last-notified timestamp.
     *
     * @param string $markerPathAbsolute
     * @param int $now - unix timestamp
     * @param int $interval - seconds
     * @return bool
     */
    public static function shouldNotifyReauth(string $markerPathAbsolute, int $now, int $interval): bool {
        if (!file_exists($markerPathAbsolute)) {
            return true;
        }
        return ($now - (int)filemtime($markerPathAbsolute)) >= $interval;
    }

    /**
     * Sends the "account needs re-authorization" mail via the QFQ SendMail (same as AutoCron).
     * Never lets a mail failure bubble up into the sync - it only logs.
     *
     * @param string $recipient
     * @param string $account
     * @param string $tokenPathAbsolute
     * @return void
     */
    private function sendReauthMail(string $recipient, string $account, string $tokenPathAbsolute): void {

        $sender = trim((string)$this->store->getVar(SYSTEM_EMAIL_USER, STORE_SYSTEM));
        if ($sender === '') {
            $sender = $recipient; // fallback: send from the recipient address
        }
        $host = gethostname() ?: '';

        // Include the QFQ instance URL when it is usable (a configured baseUrl reaches the CLI;
        // otherwise it is the FAILED_TO_SET_BASE_URL / fake sentinel and is omitted).
        $baseUrl = trim((string)$this->store->getVar(SYSTEM_BASE_URL, STORE_SYSTEM, SANITIZE_ALLOW_ALL));
        $baseUrlUsable = $baseUrl !== '' && $baseUrl !== FAILED_TO_SET_BASE_URL
            && strpos($baseUrl, 'i_am_set_in_constants') === false;
        $instanceLine = $baseUrlUsable ? "QFQ instance: $baseUrl\n" : '';

        $mail = [
            SENDMAIL_TOKEN_RECEIVER => $recipient,
            SENDMAIL_TOKEN_SENDER => $sender,
            SENDMAIL_TOKEN_SUBJECT => "QFQ Email Sync: account '$account' needs re-authorization"
                . ($baseUrlUsable ? " ($baseUrl)" : ''),
            SENDMAIL_TOKEN_BODY =>
                "The IMAP account '$account'" . ($host !== '' ? " (server $host)" : '')
                . " can no longer refresh its OAuth2 access token.\n\n"
                . "This typically means the refresh token was invalidated - e.g. by a Conditional-Access "
                . "sign-in-frequency policy. The account is not syncing until it is re-authorized.\n\n"
                . "Fix: open the emailSync report in QFQ and click the key button for this account to "
                . "re-authorize it (browser login).\n\n"
                . $instanceLine
                . "Token file: $tokenPathAbsolute\n"
                . "See the imap log for details.\n",
            SENDMAIL_TOKEN_FLAG_AUTO_SUBMIT => 'on',
            SENDMAIL_TOKEN_SRC => 'EmailSync: OAuth2 re-authorization needed',
        ];

        try {
            (new SendMail())->process($mail);
            Logger::logMessage(Logger::linePreTimestamp() . "Re-auth notification sent to $recipient for account '$account'.",
                $this->imapLogAbsolute, FILE_MODE_APPEND, LOG_LEVEL_ALL, $this->imapLogLevelMin);
        } catch (\Exception $e) {
            Logger::logMessage(Logger::linePreTimestamp() . "Re-auth notification FAILED for account '$account': " . $e->getMessage(),
                $this->imapLogAbsolute, FILE_MODE_APPEND, LOG_LEVEL_ERROR, $this->imapLogLevelMin);
        }
    }

    /**
     * Generate the IMAP configuration array for Webklex\PHPIMAP\ClientManager
     *
     * @param array $credentials
     * @return array
     */
    public static function generateImapConfigArray(array $credentials): array {
        $accounts = [];
        $defaultAccount = '';

        foreach ($credentials as $index => $credential) {
            $accountName = !empty($credential[IMAP_CRED_USERNAME]) ? self::imapAccountKey($credential[IMAP_CRED_USERNAME]) : "account_{$index}";

            // Set first account as default
            if ($index === 0) {
                $defaultAccount = $accountName;
            }

            $accounts[$accountName] = [
                'host' => $credential[IMAP_CRED_HOST],
                'port' => $credential[IMAP_CRED_PORT],
                'protocol' => 'imap',
                'encryption' => $credential[IMAP_CRED_ENCRYPTION],
                'validate_cert' => $credential[IMAP_CRED_VALIDATE_CERT],
                'username' => $credential[IMAP_CRED_USERNAME],
                'password' => $credential[IMAP_CRED_PASSWORD],
                // 'oauth' (IMAP_AUTHENTICATION_OAUTH): password is an OAuth2 access token, login via SASL XOAUTH2.
                'authentication' => $credential[IMAP_CRED_AUTHENTICATION] ?? null,
                'rfc' => 'RFC822',
                'proxy' => [
                    'socket' => null,
                    'request_fulluri' => false,
                    'username' => null,
                    'password' => null,
                ],
                // Read timeout per IMAP command: the initial full-folder fetch of a large
                // mailbox easily exceeds the webklex default of 30s.
                'timeout' => 120,
                'extensions' => []
            ];
        }

        return [
            'date_format' => 'd-M-Y',
            'default' => $defaultAccount,

            'security' => [
                'detect_spoofing' => true,
                'detect_spoofing_exception' => false,
                'sanitize_filenames' => true,
            ],

            'accounts' => $accounts,

            'options' => [
                'delimiter' => '/',
                'fetch' => \Webklex\PHPIMAP\IMAP::FT_PEEK,
                'sequence' => \Webklex\PHPIMAP\IMAP::ST_UID,
                'fetch_body' => true,
                'fetch_flags' => true,
                'soft_fail' => false,
                'rfc822' => true,
                'debug' => false,
                'unescaped_search_dates' => false,
                'uid_cache' => true,
                'boundary' => '/boundary=(.*?(?=;)|(.*))/i',
                'message_key' => 'list',
                'fetch_order' => 'asc',
                'dispositions' => ['attachment', 'inline'],
                'common_folders' => [
                    'root' => 'INBOX',
                    'junk' => 'INBOX/Junk',
                    'draft' => 'INBOX/Drafts',
                    'sent' => 'INBOX/Sent',
                    'trash' => 'INBOX/Trash',
                ],
                'open' => []
            ],

            'decoding' => [
                'options' => [
                    'header' => 'utf-8',
                    'message' => 'utf-8',
                    'attachment' => 'utf-8'
                ],
                'decoder' => [
                    'header' => \Webklex\PHPIMAP\Decoder\HeaderDecoder::class,
                    'message' => \Webklex\PHPIMAP\Decoder\MessageDecoder::class,
                    'attachment' => \Webklex\PHPIMAP\Decoder\AttachmentDecoder::class
                ]
            ],

            'flags' => ['recent', 'flagged', 'answered', 'deleted', 'seen', 'draft'],

            'events' => [
                'message' => [
                    'new' => \Webklex\PHPIMAP\Events\MessageNewEvent::class,
                    'moved' => \Webklex\PHPIMAP\Events\MessageMovedEvent::class,
                    'copied' => \Webklex\PHPIMAP\Events\MessageCopiedEvent::class,
                    'deleted' => \Webklex\PHPIMAP\Events\MessageDeletedEvent::class,
                    'restored' => \Webklex\PHPIMAP\Events\MessageRestoredEvent::class,
                ],
                'folder' => [
                    'new' => \Webklex\PHPIMAP\Events\FolderNewEvent::class,
                    'moved' => \Webklex\PHPIMAP\Events\FolderMovedEvent::class,
                    'deleted' => \Webklex\PHPIMAP\Events\FolderDeletedEvent::class,
                ],
                'flag' => [
                    'new' => \Webklex\PHPIMAP\Events\FlagNewEvent::class,
                    'deleted' => \Webklex\PHPIMAP\Events\FlagDeletedEvent::class,
                ],
            ],

            'masks' => [
                'message' => \Webklex\PHPIMAP\Support\Masks\MessageMask::class,
                'attachment' => \Webklex\PHPIMAP\Support\Masks\AttachmentMask::class
            ]
        ];
    }

}
