<?php
/**
 ***********************************************************************************************
 * @copyright The Admidio Team
 * @see https://www.admidio.org/
 * @license https://www.gnu.org/licenses/gpl-2.0.html GNU General Public License v2.0 only
 ***********************************************************************************************
 * Server side script for Datatables to return the requested messages list
 *
 * This script will read all necessary messages and their data from the database. It is optimized to
 * work with the javascript DataTables and will return the data in json format.
 *
 * **Code example**
 * ```
 * // the returned json data string
 * {
 *    "draw":1,
 *    "recordsTotal":"147",
 *    "data": [  [ 1,
 *                 "Link to profile",
 *                 "Smith, Heinz",
 *                 "Admin",
 *                 "Gender",
 *                 "16.06.1991",
 *                 "14.02.2009 15:24",
 *                 "Functions"],
 *                [ ... ],
 *             ],
 *    "recordsFiltered":"147"
 * }
 * ```
 *
 * Parameters:
 *
 * draw          - Number to validate the right inquiry from DataTables.
 * start         - Paging first record indicator. This is the start point in the current data set
 *                 (0 index based - i.e. 0 is the first record).
 * length        - Number of records that the table can display in the current draw. It is expected that
 *                 the number of records returned will be equal to this number, unless the server has
 *                 fewer records to return. Note that this can be -1 to indicate that all records should
 *                 be returned (although that negates any benefits of server-side processing!)
 * search[value] - Global search value.
 *****************************************************************************/

use Admidio\Infrastructure\Database;
use Admidio\Infrastructure\Exception;
use Admidio\Infrastructure\Utils\SecurityUtils;

require_once(__DIR__ . '/../../system/common.php');
require(__DIR__ . '/../../system/login_valid.php');

try {
    // Initialize and check the parameters
    $getDraw = admFuncVariableIsValid($_GET, 'draw', 'int', array('requireValue' => true));
    $getStart = admFuncVariableIsValid($_GET, 'start', 'int', array('requireValue' => true));
    $getLength = admFuncVariableIsValid($_GET, 'length', 'int', array('requireValue' => true));
    $getSearch = admFuncVariableIsValid($_GET['search'], 'value', 'string');

    $jsonArray = array('draw' => $getDraw);

    header('Content-Type: application/json');

    // create order statement
    $orderCondition = '';
    $orderColumns = array('msg_type', 'msg_subject', 'recipients', 'attachments', 'msg_timestamp');

    if (array_key_exists('order', $_GET)) {
        foreach ($_GET['order'] as $order) {
            if (is_numeric($order['column'])) {
                if ($orderCondition === '') {
                    $orderCondition = ' ORDER BY ';
                } else {
                    $orderCondition .= ', ';
                }

                if (strtoupper($order['dir']) === 'ASC') {
                    $orderCondition .= $orderColumns[$order['column']] . ' ASC ';
                } else {
                    $orderCondition .= $orderColumns[$order['column']] . ' DESC ';
                }
            }
        }
    } else {
        $orderCondition = ' ORDER BY msg_timestamp DESC ';
    }

    // create search conditions
    $searchCondition = '';
    $queryParamsSearch = array();
    $searchColumns = array(
        'COALESCE(msg_subject, \' \')'
    );

    if ($getSearch !== '' && count($searchColumns) > 0) {
        $searchString = explode(' ', $getSearch);

        if (DB_ENGINE === Database::PDO_ENGINE_PGSQL) {
            $searchValue = ' ?::text ';
        } else {
            // mysql
            $searchValue = ' ? ';
        }

        foreach ($searchString as $searchWord) {
            $searchCondition .= ' AND concat(' . implode(', ', $searchColumns) . ') LIKE CONCAT(\'%\', ' . $searchValue . ', \'%\') ';
            $queryParamsSearch[] = htmlspecialchars_decode($searchWord, ENT_QUOTES | ENT_HTML5);
        }

        $searchCondition = ' WHERE ' . substr($searchCondition, 4);
    }

    // get count of all found users
    $sql = 'SELECT COUNT(*)
          FROM ' . TBL_MESSAGES . '
         WHERE (  msg_usr_id_sender = ? -- $gCurrentUserId
                  OR EXISTS (
                      SELECT 1
                        FROM ' . TBL_MESSAGES_RECIPIENTS . '
                       WHERE msr_msg_id = msg_id
                         AND msg_type   = \'PM\'
                         AND msr_usr_id = ? -- $gCurrentUserId
                  )
                )';
    $queryParams = array(
        $gCurrentUserId,
        $gCurrentUserId
    );
    $countTotalStatement = $gDb->queryPrepared($sql, $queryParams); // TODO add more params

    $jsonArray['recordsTotal'] = (int)$countTotalStatement->fetchColumn();

    // SQL-Statement zusammensetzen
    $mainSql = 'SELECT msg_id, msg_uuid, msg_type, msg_subject, msg_usr_id_sender, msg_timestamp, msg_read,
                    (SELECT count(1) FROM ' . TBL_MESSAGES_ATTACHMENTS . ' WHERE msa_msg_id = msg_id) AS attachments
              FROM ' . TBL_MESSAGES . '
             WHERE (  msg_usr_id_sender = ? -- $gCurrentUserId
                   OR EXISTS (
                      SELECT 1
                        FROM ' . TBL_MESSAGES_RECIPIENTS . '
                       WHERE msr_msg_id = msg_id
                         AND msg_type   = \'PM\'
                         AND msr_usr_id = ? -- $gCurrentUserId
                      )
                   )';
    $queryParamsMain = array(
        $gCurrentUserId,
        $gCurrentUserId
    );

    $limitCondition = '';
    if ($getLength > 0) {
        $limitCondition = ' LIMIT ' . $getLength . ' OFFSET ' . $getStart;
    }

    if ($getSearch === '') {
        // no search condition entered then return all records in dependence of order, limit and offset
        $sql = $mainSql . $orderCondition . $limitCondition;
    } else {
        $sql = 'SELECT msg_id, msg_uuid, msg_type, msg_subject, attachments, msg_timestamp
              FROM (' . $mainSql . ') AS members
               ' . $searchCondition
            . $orderCondition
            . $limitCondition;
    }
    $messageStatement = $gDb->queryPrepared($sql, array_merge($queryParamsMain, $queryParamsSearch)); // TODO add more params

    $rowNumber = $getStart; // count for every row

    // show rows with all organization users
    while ($message = $messageStatement->fetch()) {
        ++$rowNumber;
        $arrContent = array();
        $cssClass = 'fw-normal';
        $iconAttachments = '';

        $messageObject = new Admidio\Messages\Entity\Message($gDb);
        $messageObject->setArray($message);

        // Icon fuer Orgamitglied und Nichtmitglied auswaehlen
        if ($message['msg_type'] === Admidio\Messages\Entity\Message::MESSAGE_TYPE_EMAIL) {
            $icon = 'bi-envelope-fill';
            $iconText = $gL10n->get('SYS_EMAIL');
            $links = '<a class="admidio-icon-link" href="' . SecurityUtils::encodeUrl(ADMIDIO_URL . FOLDER_MODULES . '/messages/messages_write.php', array('msg_uuid' => $message['msg_uuid'], 'forward' => '1')) . '">
                    <i class="bi bi-forward" data-bs-toggle="tooltip" title="' . $gL10n->get('SYS_FORWARD') . '"></i></a>';
        } else {
            $icon = 'bi-chat-left-fill';
            $iconText = $gL10n->get('SYS_PRIVATE_MESSAGES');
            $links = '';
        }

        if ($messageObject->isUnread()) {
            $cssClass = 'fw-bold';
        }

        if ((int)$message['attachments'] === 1) {
            $iconAttachments = '<i class="bi bi-paperclip" data-bs-toggle="tooltip" title="' . $gL10n->get('SYS_ATTACHMENT_ONE') . '"></i>';
        } elseif ($message['attachments'] > 1) {
            $iconAttachments = '<i class="bi bi-paperclip" data-bs-toggle="tooltip" title="' . $gL10n->get('SYS_ATTACHMENTS_VAR', array($message['attachments'])) . '"></i>';
        }

        $arrContent['DT_RowId'] = 'row_message_' . $message['msg_uuid'];
        $arrContent['DT_RowClass'] = $cssClass;
        $arrContent['0'] = '<i class="bi ' . $icon . '" data-bs-toggle="tooltip" title="' . $iconText . '"></i>';
        $arrContent['1'] = '<a href="' . SecurityUtils::encodeUrl(ADMIDIO_URL . FOLDER_MODULES . '/messages/messages_write.php', array('msg_uuid' => $message['msg_uuid'])) . '">' . $messageObject->getValue('msg_subject') . '</a>';
        $arrContent['2'] = $messageObject->getRecipientsNamesString();
        $arrContent['3'] = $iconAttachments;
        $arrContent['4'] = $messageObject->getValue('msg_timestamp');
        $arrContent['5'] = $links . '
            <a class="admidio-icon-link admidio-messagebox" href="javascript:void(0);" data-buttons="yes-no"
                data-message="' . $gL10n->get('SYS_DELETE_MESSAGE', array($messageObject->getValue('msg_subject', 'database'))) . '"
                data-href="callUrlHideElement(\'row_message_' . $message['msg_uuid'] . '\', \'' . SecurityUtils::encodeUrl(ADMIDIO_URL . FOLDER_MODULES . '/messages/messages.php', array('msg_uuid' => $message['msg_uuid'])) . '\', \'' . $gCurrentSession->getCsrfToken() . '\')">
                <i class="bi bi-trash" data-bs-toggle="tooltip" title="' . $gL10n->get('SYS_REMOVE_MESSAGE') . '"></i>
            </a>';

        // create array with all column values and add it to the json array
        $jsonArray['data'][] = $arrContent;
    }

    // set count of filtered records
    if ($getSearch !== '') {
        if ($rowNumber < $getStart + $getLength) {
            $jsonArray['recordsFiltered'] = $rowNumber;
        } else {
            // read count of all filtered records without limit and offset
            $sql = 'SELECT COUNT(*) AS count
                  FROM (' . $mainSql . ') AS members
                       ' . $searchCondition;
            $countFilteredStatement = $gDb->queryPrepared($sql, array_merge($queryParamsMain, $queryParamsSearch));
            $jsonArray['recordsFiltered'] = (int)$countFilteredStatement->fetchColumn();
        }
    } else {
        $jsonArray['recordsFiltered'] = $jsonArray['recordsTotal'];
    }

    // add empty data element if no rows where found
    if (!array_key_exists('data', $jsonArray)) {
        $jsonArray['data'] = array();
    }

    echo json_encode($jsonArray);
} catch (Exception $e) {
    $jsonArray['error'] = $e->getMessage();
    echo json_encode($jsonArray);
    exit();
}
