#
# E-Mail Sync List - browse the synced mails of one account from the EmailSync table.
#
# Account selector + folder filter ('All' + folders), an attachment column WITH download links,
# and per-row buttons (read / unread / move). The buttons run a LIVE IMAP command (J:...), so you
# directly see whether the action works.
#
# The 'move' button is a real dropdown (QFQ 'z:' token). Its entries are ALL folders that exist
# on the server (fetched live once via J:folderList) - not only the synced ones - so a mail can be
# moved anywhere. Fetching the folder list talks to the IMAP server: if the account's auth is
# broken the report shows the IMAP connection error (that is intended - the action buttons need a
# working connection anyway).
#
# Requires MariaDB (seq_1_to_N engine) + JSON functions (JSON_LENGTH/JSON_EXTRACT).
#

# ---------------------------------------------------------------------------------------------
# 0) Read every request param ONCE into a record-store variable. Reading a param with the ':w'
#    (wipe) escape empties it for the rest of the report - so wipe here once, then use {{x:RE}}
#    (strings) / {{esId:R0}} (number) everywhere below.
# ---------------------------------------------------------------------------------------------
{
    sql = SELECT '{{account:SE:alnumx:w}}' AS _account
                 , '{{folder:SE:alnumx:w}}' AS _folder
                 , '{{imapAction:SE:alnumx:w}}' AS _imapAction
                 , '{{esId:SE:digit:w}}' AS _esId
                 , '{{moveTarget:SE:alnumx:w}}' AS _moveTarget
                 , '{{showAll:SE:digit:w}}' AS _showAll
            FROM DUAL
}

# ---------------------------------------------------------------------------------------------
# 0a) Row limit: newest 100 by default, everything when 'Show all' (showAll=1) is checked.
# ---------------------------------------------------------------------------------------------
{
    sql = SELECT IF('{{showAll:R0}}' = '1', 999999999, 100) AS _rowLimit FROM DUAL
}

# ---------------------------------------------------------------------------------------------
# 0b) GROUP_CONCAT below builds the whole move-dropdown in one string - lift the default 1024
#     byte cap so a long folder list is never truncated.
# ---------------------------------------------------------------------------------------------
{
    sql = SET SESSION group_concat_max_len = 1000000
}

# ---------------------------------------------------------------------------------------------
# 0c) Fetch ALL folders of the selected account ONCE (live IMAP). The processed output lands in
#     {{&imapFolders}} ('INBOX<br>Sent<br>...') and is reused below to build every move dropdown.
#     Only fetched when an account is selected.
# ---------------------------------------------------------------------------------------------
{
    sql = SELECT 'J:folderList::::{{account:RE}}' AS '_link|imapFolders|_hide'
            FROM DUAL WHERE '{{account:RE}}' != ''
}

# ---------------------------------------------------------------------------------------------
# 1) Execute a per-row action and show the result ('' = OK). 'mailMove' needs a moveTarget (set
#    by the dropdown entry that was clicked).
# ---------------------------------------------------------------------------------------------
{
    sql = SELECT es.messageId AS _msgId
                 , es.folderPath AS _srcFolder
                 , es.account AS _acc
            FROM EmailSync AS es
            WHERE es.id = '{{esId:R0}}'
              AND '{{imapAction:RE}}' != ''
              AND NOT ('{{imapAction:RE}}' = 'mailMove' AND '{{moveTarget:RE}}' = '')

    shead = <div class="alert alert-info">IMAP action result: <b>
    stail = </b> &nbsp;<small>(empty = OK)</small></div>

    {
        sql = SELECT CONCAT('J:', '{{imapAction:RE}}', ':', '{{moveTarget:RE}}', ':',
                            '{{srcFolder:RE}}', ':', '{{msgId:RE}}', ':', '{{acc:RE}}') AS _link
    }
}

# ---------------------------------------------------------------------------------------------
# 1a) Reflect a successful move in the DB. The daemon is INSERT-only and dedups messageId
#     globally, so it would never rewrite folderPath - the row would keep pointing at the old
#     folder forever. This runs AFTER block 1: a failed move throws and aborts the report before
#     reaching here, so the UPDATE only fires when the server-side move actually succeeded.
# ---------------------------------------------------------------------------------------------
{
    sql = UPDATE EmailSync SET folderPath = '{{moveTarget:RE}}'
            WHERE id = '{{esId:R0}}'
              AND '{{imapAction:RE}}' = 'mailMove'
              AND '{{moveTarget:RE}}' != ''
}

# ---------------------------------------------------------------------------------------------
# 2) Account selector - one button per account in EmailSync.
# ---------------------------------------------------------------------------------------------
{
    sql = SELECT CONCAT('p:{{pageSlug:T}}?account=', account,
                        '|s|b|t:', account, '|o:Show mails of this account') AS _link
            FROM EmailSync
            GROUP BY account
            ORDER BY account
    shead = <p><b>Account:</b>&nbsp;
    stail = </p>
    fsep = &nbsp;
}

# ---------------------------------------------------------------------------------------------
# 3) Folder filter for the selected account: 'All' plus one button per synced folder.
# ---------------------------------------------------------------------------------------------
{
    sql = SELECT 'x' AS _dummy FROM DUAL WHERE '{{account:RE}}' != ''
    shead = <p><b>Folder:</b>&nbsp;
    stail = </p>
    {
        sql = SELECT 'p:{{pageSlug:T}}?account={{account:RE}}&folder=All|s|b|t:All|o:All folders' AS _link
                FROM DUAL
    }
    {
        sql = SELECT CONCAT('p:{{pageSlug:T}}?account={{account:RE}}&folder=', folderPath,
                            '|s|b|t:', folderPath) AS _link
                FROM EmailSync
                WHERE account = '{{account:RE}}'
                GROUP BY folderPath
                ORDER BY folderPath
        fsep = &nbsp;
    }
}

# ---------------------------------------------------------------------------------------------
# 3b) 'Show all' checkbox: default lists the newest 100 mails; checking it loads all. The total
#     count for the current account/folder filter is shown next to it.
# ---------------------------------------------------------------------------------------------
{
    sql = SELECT CONCAT(
                     '<label style="font-weight:normal;cursor:pointer">',
                     '<input type="checkbox" onchange="location.href=''{{pageSlug:T}}?account={{account:RE}}&amp;folder={{folder:RE}}&amp;showAll=''+(this.checked?1:0)"',
                     IF('{{showAll:R0}}' = '1', ' checked', ''),
                     '> Show all</label> ',
                     '<small class="text-muted">', COUNT(*), ' mails',
                     IF('{{showAll:R0}}' = '1' OR COUNT(*) <= 100, '', ' &mdash; showing newest 100'),
                     '</small>')
            FROM EmailSync
            WHERE account = '{{account:RE}}'
              AND ('{{folder:RE}}' = '' OR '{{folder:RE}}' = 'All' OR folderPath = '{{folder:RE}}')
            HAVING '{{account:RE}}' != ''
    shead = <p>
    stail = </p>
}

# ---------------------------------------------------------------------------------------------
# 4) The mail table. Text cells come from the outer SELECT (stored, rendered in the inner row);
#    the Attachments and Actions cells are nested levels that render their own <td>.
# ---------------------------------------------------------------------------------------------
{
    sql = SELECT es.id AS _mailId
                 # Received = real mail date (Date header); fall back to the sync time (created)
                 # for rows synced before mailDate existed / mails without a parseable Date.
                 , DATE_FORMAT(COALESCE(es.mailDate, es.created), '%Y-%m-%d %H:%i') AS _received
                 , es.folderPath AS _folderCol
                 , es.sender AS _from
                 , es.subject AS _subject
                 , CONCAT('p:{{pageSlug:T}}?account=', es.account, '&folder={{folder:RE}}',
                          '&imapAction=seen&esId=', es.id, '|s|b|t:read|o:Mark read (IMAP)') AS '_link|btnSeen|_hide'
                 , CONCAT('p:{{pageSlug:T}}?account=', es.account, '&folder={{folder:RE}}',
                          '&imapAction=unseen&esId=', es.id, '|s|b|t:unread|o:Mark unread (IMAP)') AS '_link|btnUnseen|_hide'
                 # Move dropdown (z:): one entry per live folder (except the mail's current one). Each
                 # entry navigates with moveTarget set -> block 1 runs the move in a single click.
                 , CONCAT('z|t:Move&hellip;|G:0|b|o:Move this mail to a folder',
                          COALESCE((
                              SELECT GROUP_CONCAT(
                                       CONCAT('||p:{{pageSlug:T}}?account=', es.account,
                                              '&folder={{folder:RE}}&imapAction=mailMove&esId=', es.id,
                                              '&moveTarget=', fl.folderName, '|s|t:', fl.folderName)
                                       ORDER BY fl.folderName SEPARATOR '')
                              FROM (
                                  SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX('{{&imapFolders:RE:all:s}}', '<br>', s.seq), '<br>', -1) AS folderName
                                  FROM seq_1_to_100 AS s
                                  WHERE s.seq <= (CHAR_LENGTH('{{&imapFolders:RE:all:s}}') - CHAR_LENGTH(REPLACE('{{&imapFolders:RE:all:s}}', '<br>', ''))) / 4
                              ) AS fl
                              WHERE fl.folderName != '' AND fl.folderName != es.folderPath
                          ), '')) AS '_link|btnMove|_hide'
            FROM EmailSync AS es
            WHERE es.account = '{{account:RE}}'
              AND ('{{folder:RE}}' = '' OR '{{folder:RE}}' = 'All' OR es.folderPath = '{{folder:RE}}')
            ORDER BY COALESCE(es.mailDate, es.created) DESC
            LIMIT {{rowLimit:R0}}

    shead = <table class="table tablesorter tablesorter-filter tablesorter-pager qfq-table-50">
              <thead><tr>
                <th>Received</th><th>Folder</th><th>From</th><th>Subject</th><th>Attachments</th>
                <th class="filter-false sorter-false">Actions</th>
              </tr></thead><tbody>
    stail = </tbody></table>

    # One <tr> per mail: 4 text cells, then the Attachments cell, then the Actions cell.
    {
        sql = SELECT '{{received:RE}}', '{{folderCol:RE}}', '{{from:RE}}', '{{subject:RE}}'
        rbeg = <tr>
        renr = </tr>
        fbeg = <td>
        fend = </td>

        # Attachments cell: one download link per stored attachment (re-read by mailId, so the
        # JSON stays in the DB). Over-size attachments (file = null) are skipped here.
        # F: = source file, d: = download filename, t: = link text.
        {
            sql = SELECT CONCAT('F:', JSON_UNQUOTE(JSON_EXTRACT(a.attachment, CONCAT('$[', s.seq - 1, '].file'))),
                                '|d:', JSON_UNQUOTE(JSON_EXTRACT(a.attachment, CONCAT('$[', s.seq - 1, '].name'))),
                                '|t:', JSON_UNQUOTE(JSON_EXTRACT(a.attachment, CONCAT('$[', s.seq - 1, '].name')))) AS _link
                    FROM EmailSync AS a, seq_1_to_10 AS s
                    WHERE a.id = '{{mailId:R0}}'
                      AND a.attachment != '' AND JSON_VALID(a.attachment)
                      AND s.seq <= JSON_LENGTH(a.attachment)
                      AND JSON_TYPE(JSON_EXTRACT(a.attachment, CONCAT('$[', s.seq - 1, '].file'))) != 'NULL'
                    ORDER BY s.seq
            shead = <td>
            stail = </td>
            fsep = <br>
        }

        # Actions cell.
        {
            sql = SELECT '{{&btnSeen:RE}} {{&btnUnseen:RE}} {{&btnMove:RE}}'
            shead = <td>
            stail = </td>
        }
    }
}
