#
# E-Mail Sync
# Table of all IMAP credentials, folder and sync status
#
# action: folderList, mailList, checkAccount, mailMove, seen, unseen, folderNew, folderDelete
#         oauthStart, oauthPoll (OAuth2 device code authorization, e.g. Outlook/Office365)

# Disable SQL logging for this whole report: the account-list query decrypts the password via
# escape 'D' ({{passwordEnc:RE:all:Ds}}) to decide OAuth2-vs-classic, and at sqlLogMode=all/modify
# the substituted (plaintext) query would otherwise be written to sql.log. Trade-off: SQL errors
# in this report are not logged either.
sqlLogMode = none

form = {{form:SE}}

{
    sql = SELECT '{{action:SE:alnumx}}' AS _action
                 , '{{username:SE:alnumx}}' AS _username
                 , '{{folder:SE:alnumx}}' AS _folder
                 , '{{settingId:SE:digit:::0}}' AS _settingId
            FROM DUAL
            WHERE '{{action:SE:alnumx:w}}' != ''

    shead = <div class="alert alert-info">
               Don't forget to restart <b>email-sync.php</b> script after changing the configuration -
               use the <i>Start / restart service</i> button below.
            </div>
    head = <div class="panel panel-info">
             <div class="panel-heading">
                 <h3 class="panel-title">{{infoType:SE:alnumx}}: {{username:SE:alnumx}}</h3>
             </div>
             <div class="panel-body">
    tail = </div></div>

    {
        sql = SELECT 'J:folderList::::{{username:RE}}' AS _link
                FROM DUAL
                WHERE '{{action:RE}}' = 'folderList'
    }

    {
        sql = SELECT CONCAT('<h4>', TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX('{{folder:RE}}', ',', seq), ',', -1)), '</h4>') AS currentFolder
                      , CONCAT('J:mailList:',
                         TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX('{{folder:RE}}', ',', seq), ',', -1)),
                         ':::',
                         '{{username:RE}}'
                      ) AS _link
              FROM seq_1_to_10
              WHERE seq <= (CHAR_LENGTH('{{folder:RE}}') - CHAR_LENGTH(REPLACE('{{folder:RE}}', ',', '')) + 1)
                  AND TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX('{{folder:RE}}', ',', seq), ',', -1)) != ''
                  AND '{{action:RE}}' = 'mailList'
    }

    # OAuth2 device code flow, step 1: request device code, show browser login instructions.
    {
        sql = SELECT 'F:typo3conf/ext/qfq/Classes/External/emailSyncOAuth2.php|call:emailSyncOAuth2Start|arg:settingId={{settingId:RE}}' AS _script
                     , CONCAT('p:{{pageSlug:T}}?action=oauthPoll&infoType=OAuth2 Authorization&username=', '{{username:RE}}', '&settingId=', '{{settingId:RE}}', '&checkUsername=', '{{username:RE}}', '|s|b|t:Check login|o:Fetch the tokens after completing the browser login') AS _link
                FROM DUAL
                WHERE '{{action:RE}}' = 'oauthStart'
    }

    # OAuth2 device code flow, step 2: one token poll per click.
    {
        sql = SELECT 'F:typo3conf/ext/qfq/Classes/External/emailSyncOAuth2.php|call:emailSyncOAuth2Poll|arg:settingId={{settingId:RE}}' AS _script
                     , CONCAT('p:{{pageSlug:T}}?action=oauthPoll&infoType=OAuth2 Authorization&username=', '{{username:RE}}', '&settingId=', '{{settingId:RE}}', '&checkUsername=', '{{username:RE}}', '|s|b|t:Check again|o:Poll once more whether the browser login is completed') AS '_link|oauthPollBtn|_hide'
                     , CONCAT('p:{{pageSlug:T}}?action=oauthStart&infoType=OAuth2 Authorization&username=', '{{username:RE}}', '&settingId=', '{{settingId:RE}}', '|s|b|t:Restart authorization|o:Request a new device code and start over') AS '_link|oauthRestartBtn|_hide'
                FROM DUAL
                WHERE '{{action:RE}}' = 'oauthPoll'
        {
            sql = SELECT '{{&oauthPollBtn:RE}} {{&oauthRestartBtn:RE}}'
        }
    }

    # Start/restart the email-sync systemd service. Absolute paths everywhere: the PHP process
    # runs sh with a minimal PATH (php-fpm clear_env), 'sudo ...' alone fails with rc=127.
    # sudo -n: fail immediately instead of prompting. Needs a sudoers rule for the webserver
    # user (see IMAP-EMAILSYNC.md):
    #   www-data ALL=(root) NOPASSWD: /usr/bin/systemctl restart emailSync
    {
        sql = SELECT '/usr/bin/sudo -n /usr/bin/systemctl restart emailSync 2>&1' AS '_exec|serviceRestartResult|_hide'
                FROM DUAL
                WHERE '{{action:RE}}' = 'serviceRestart'
        {
            sql = SELECT IF('{{&serviceRestartResult:RE}}' = '',
                            '<div class="alert alert-success">Service restarted - status see below.</div>',
                            CONCAT('<div class="alert alert-danger">Service restart failed: <code>', '{{&serviceRestartResult:RE}}', '</code>',
                                   '<br><b>sudo: not found</b> (rc=127): install sudo on the host (<code>apt-get install sudo</code>) or fix the path (<code>command -v sudo</code>).',
                                   '<br><b>a password is required</b>: create the sudo rule on the host:',
                                   '<pre># /etc/sudoers.d/emailSync  (mode 0440)<br>www-data ALL=(root) NOPASSWD: /usr/bin/systemctl restart emailSync</pre>',
                                   '<b>Unit emailSync.service not found</b>: create <code>/etc/systemd/system/emailSync.service</code>:',
                                   '<pre>[Unit]<br>Description=IMAP Service<br>After=network.target<br><br>[Service]<br>Type=simple<br>User=www-data<br>Group=www-data<br>Environment="SERVICE_FILE_PATH=/etc/systemd/system/emailSync.service"<br>ExecStart=/etc/scripts/emailSync.sh<br>Restart=no<br>',
                                   '#RestartSec=5s<br><br>[Install]<br>WantedBy=multi-user.target</pre>',
                                   'then: <code>systemctl daemon-reload &amp;&amp; systemctl enable --now emailSync</code> (as root).',
                                   '<br>Adjust webserver user and unit name if they differ. Details: IMAP-EMAILSYNC.md</div>'))
        }
    }
}

# email-sync.php daemon status. Adjust the systemd unit name ('emailSync') to the host's setup
# (three places: is-active, the restart _exec below and the sudoers rule).
# Note: _exec prefixes non-zero return codes as '[rc=N] ' (trailing space, output appended) -
# therefore compare against the plain 'active' output instead of matching return codes.
# pgrep pattern: '[e]mail...' bracket trick so the _exec shell wrapper does not count itself;
# anchored on the php binary so wrapper processes (runuser, sh) are not counted either.
{
    sql = SELECT '/usr/bin/systemctl is-active emailSync 2>&1' AS '_exec|emailSyncServiceState|_hide'
                 , '/usr/bin/pgrep -fc "^([^ ]*/)?php[0-9.]* .*[e]mail-sync[.]php"' AS '_exec|emailSyncDaemonCount|_hide'

    {
        sql = SELECT IF('{{&emailSyncServiceState:RE}}' = 'active',
                        'green|o:systemd service active',
                        CONCAT('red|o:systemd service: ', '{{&emailSyncServiceState:RE}}')) AS _bullet
                     , CONCAT(' email-sync service: <b>', '{{&emailSyncServiceState:RE}}',
                        '</b> &nbsp;|&nbsp; daemon processes: <b>', REPLACE('{{&emailSyncDaemonCount:RE}}', '[rc=1] ', ''), '</b> &nbsp;')
                     , 'p:{{pageSlug:T}}?action=serviceRestart&infoType=Service&username=email-sync&checkAll=1|s|b|t:<i class="fas fa-play"></i> Start / restart service|o:Start or restart the email-sync systemd service (needs the sudo rule), then check all accounts|q:Start/restart the email-sync service? A running sync is interrupted (safe, mails are deduplicated).' AS _link
        rbeg = <p>
        rend = </p>
    }
}

{
    sql = SELECT 'p:{{pageSlug:T}}?form=emailSync&r=0|s|b|N|o:Add a new email account' AS '_link|newEmailBtn|_hide'
                 , 'p:{{pageSlug:T}}?checkAll=1|s|b|t:<i class="fas fa-plug"></i> Check all|o:Test the IMAP connection of every account (one live connect per account, may take a moment)' AS '_link|checkAllBtn|_hide'
}

# checkAccount is a live IMAP connect. To keep the report fast it is NOT run on every render:
# checkAccountStatus is only the 'J:checkAccount' action for the row that was explicitly
# requested (per-row check button -> checkUsername, or "Check all" -> checkAll). For all other
# rows the column is empty, so rendering {{&checkAccountStatus}} does nothing (no connect).
# The authorize button is disabled (r:3) for non OAuth2 accounts (password not 'oauth2token:').
{
    sql = SELECT e.view AS _passwordEnc
                 , CONCAT('p:{{pageSlug:T}}?form=emailSync&r=', e.id, '|s|b|E|o:Edit account') AS '_link|editEmailBtn|_hide'
                 , CONCAT('p:{{pageSlug:T}}?action=folderList&infoType=Folder List&username=', e.feUser, '|s|b|t:<i class="far fa-folder-open"></i>|o:List all folders of this account') AS '_link|showAllFolder|_hide'
                 , CONCAT('p:{{pageSlug:T}}?action=mailList&infoType=Mail List&username=', e.feUser, '&folder=', e.name2, '|s|b|t:<i class="fas fa-inbox"></i>|o:List mails of the configured folder(s)') AS '_link|showInboxMails|_hide'
                 , CONCAT('p:{{pageSlug:T}}?action=oauthStart&infoType=OAuth2 Authorization&username=', e.feUser, '&settingId=', e.id, '|s|b|t:<i class="fas fa-key"></i>|o:Authorize OAuth2 account (e.g. Outlook/Office365)') AS '_link|oauthStartBtn|_hide'
                 , CONCAT('p:{{pageSlug:T}}?action=oauthStart|s|t:<span class="btn btn-default disabled" title="Not an OAuth2 account - password does not start with oauth2token:"><i class="fas fa-key"></i></span>|r:3') AS '_link|oauthStartBtnOff|_hide'
                 , CONCAT('p:{{pageSlug:T}}?checkUsername=', e.feUser, '|s|b|t:<i class="fas fa-plug"></i>|o:Test this account IMAP connection now') AS '_link|checkBtn|_hide'
                 , e.name AS _host
                 , e.feUser AS _username
                 , e.name2 AS _folder
                 , e.id AS _settingId
                 , IF(e.feUser = '{{checkUsername:SE:alnumx}}' OR '{{checkAll:SE:digit:::0}}' = '1', CONCAT('J:checkAccount::::', e.feUser), '') AS '_link|checkAccountStatus|_hide'
            FROM Setting AS e
            WHERE e.type = 'credentials'

    shead = <div class="panel panel-default">
              <div class="panel-heading">
                <h3 class="panel-title">E-Mail IMAP Configuration</h3>
              </div>
              <div class="panel-body">
                <table class="table tablesorter">
                  <thead>
                    <tr>
                      <th class="filter-false sorter-false">{{&newEmailBtn:RE::-}} {{&checkAllBtn:RE::-}}</th>
                      <th>Host</th>
                      <th>Username</th>
                      <th>Folder</th>
                      <th>Status</th>
                      <th class="filter-false sorter-false"></th>
                    </tr>
                  </thead>
                  <tbody>

    stail =      </tbody>
                 </table>
               </div>
             </div>

    {
        sql = SELECT CONCAT('{{&editEmailBtn:RE}} {{&showAllFolder:RE}} {{&showInboxMails:RE}} ',
                            IF(LEFT('{{passwordEnc:RE:all:Ds}}', 12) = 'oauth2token:', '{{&oauthStartBtn:RE}}', '{{&oauthStartBtnOff:RE}}'),
                            ' {{&checkBtn:RE}}')
                     , '{{host:RE}}'
                     , '{{username:RE}}'
                     , '{{folder:RE}}'
                     , CASE '{{&checkAccountStatus:R0}}' WHEN '1' THEN 'green|o:Active' WHEN '0' THEN 'red|o:Inactive' ELSE 'gray|o:Not checked - use the check button' END AS _bullet
                     , CONCAT('U:form=emailSync&r={{settingId:R0}}|q:Do you want delete this configuration?|o:Delete account configuration') AS _paged
        rbeg = <tr>
        rend = </tr>
        fbeg = <td>
        fend = </td>

    }
}