SELECT c.*,
       IF(d.id, 'true', IF(c.operator_id < 0, 'true', 'false'))             AS dispatch,
       IF(r.id, 'true', 'false')                                            AS redirected,
       IF(c.guest = 0, u.avatar, NULL)                                      AS avatar,
       IF(c.operator_id > 0, CONCAT(op.firstname, ' ', op.lastname), '- -') AS operator,
       IF(c.operator_id > 0, op.avatar, '')                                 AS operatorAvatar,
       IF(r.id, 'true', 'false')                                            AS redirected,
       IF(r.fromId = ?, 'true', 'false')                                    AS redirectedFrom,
       IF(r.toId = ?, 'true', 'false')                                      AS redirectedTo
FROM chat AS c
       LEFT JOIN dispatch AS d ON d.chat_id = c.id
       LEFT JOIN `user` AS op ON op.id = c.operator_id
       LEFT JOIN `user` AS u ON u.id = c.client_id
       LEFT JOIN guest AS g ON g.id = c.client_id
       LEFT JOIN redirect AS r ON r.chatId = c.id AND r.toId = ? AND r.submit = 0 AND r.cancel = 0
WHERE c.id = ?
  AND c.site_id = ?

SELECT s.id                                                                 AS id,
       SUM((SELECT IF(COUNT(*) > 0, 1, 0) AS unread
            FROM message AS m
            WHERE m.chat_id = c.id AND m.status = 0 AND m.type = 'CLIENT')) AS unread
FROM chat AS c
       LEFT JOIN site AS s ON s.id = c.site_id
       LEFT JOIN operator AS op ON op.user_id = ?
WHERE c.complete = 0
  AND ((s.userId = ? AND c.site_id = s.id) OR (op.id > 0 AND c.site_id = op.site_id))
GROUP BY id
ORDER BY id ASC;

SELECT DISTINCT c.id
FROM message AS ct
       LEFT JOIN chat AS c
                 ON c.id = ct.chat_id AND (c.operator_id = ? OR c.operator_id = -1)
       LEFT JOIN dispatch AS d ON d.site_id = 1 AND d.chat_id = c.id AND d.user_id = ?
WHERE ct.type = 'CLIENT'
  AND ct.status = 0
  AND c.site_id = ?
  AND c.complete = 0
  AND (c.operator_id = ? OR (c.operator_id = -1 AND d.id > 0));

SELECT c.id                   AS id,
       (SELECT COUNT(*)
        FROM message AS ctu
        WHERE ctu.chat_id = c.id
          AND ctu.type = 'CLIENT'
          AND ctu.status = 0) AS unread
FROM chat AS c
       LEFT JOIN dispatch AS d ON d.chat_id = c.id
WHERE c.site_id = ?
  AND (c.operator_id = ? OR c.operator_id = -1)
GROUP BY c.id;

SELECT m.*,
       IF(f.id > 0, 'true', 'false') AS file,
       f.id                          AS fileId,
       f.type                        AS fileType,
       f.name                        AS fileName,
       f.path                        AS filePath,
       c.site_id                     AS siteId,
       m.chat_id                     AS chatId
FROM message AS m
       LEFT JOIN chat AS c ON c.id = m.chat_id
       LEFT JOIN file AS f ON f.id = m.file_id
WHERE m.chat_id = ?;