[IMP] im_livechat : optimize reports queries

Before, the duration and time to answer where computed based on the
mail channel creation date and
- the first message date from operator (for time to answer)
- the last message date (for duration)
But as the mail channel can be created long ago before the first message,
the result was not really representative.

Now, we are basing the computation only based on the messages.
Time to answer : first message date from operator - first message date
Duration : last message date - first message date

It allows, with the add of the second join on mail_message,
to simplify the extraction of those two indicator by removing the long
subselects.

Also, we fix the nbr_message in operator by adding the missing distinct.

Special mention to @jem-odoo who clearly did (almost) all the work :
This guy is amazing ! After a long and mature reflexion,
he finally put all his mighty power to enhance thoses reports
to give the users a wonderfull experience full of flowers and
peacefull sun, but also fast as a energized rabbit superhero !
Hail to him ! The One, the Only, the Chosen..
If you see him around the corner, please give him all your love
and gratitude ! Voilà voilà..

Task ID : 1895998
Closes PR #28283

2 files changed, 20 insertions, 13 deletions

diff --git a/addons/im_livechat/report/im_livechat_report_channel.py b/addons/im_livechat/report/im_livechat_report_channel.py
index c6314fbc2af..21305ab7b99 100644
--- a/addons/im_livechat/report/im_livechat_report_channel.py
+++ b/addons/im_livechat/report/im_livechat_report_channel.py
@@ -55,12 +55,14 @@ class ImLivechatReportChannel(models.Model):
                     to_char(date_trunc('hour', C.create_date), 'YYYY-MM-DD HH24:MI:SS') as start_date_hour,
                     to_char(date_trunc('hour', C.create_date), 'HH24') as start_hour,
                     extract(dow from  C.create_date) as day_number, 
-                    EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration,
-                    EXTRACT('epoch' from ((SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=C.livechat_operator_id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id)-(SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id IS NULL AND R.mail_channel_id = C.id AND R.mail_message_id = M.id))) as time_to_answer,
+                    EXTRACT('epoch' FROM MAX(M.create_date) - MIN(M.create_date)) AS duration,
+                    EXTRACT('epoch' FROM MIN(MO.create_date) - MIN(M.create_date)) AS time_to_answer,
                     count(distinct C.livechat_operator_id) as nbr_speaker,
                     count(distinct M.id) as nbr_message,
                     CASE 
-                        WHEN EXISTS (select distinct M.author_id FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=C.livechat_operator_id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id and C.livechat_operator_id = M.author_id)
+                        WHEN EXISTS (select distinct M.author_id FROM mail_message M, mail_message_mail_channel_rel R 
+                                        WHERE M.author_id=C.livechat_operator_id AND R.mail_channel_id = C.id 
+                                        AND R.mail_message_id = M.id and C.livechat_operator_id = M.author_id)
                         THEN 0
                         ELSE 1
                     END as is_without_answer,
@@ -87,10 +89,12 @@ class ImLivechatReportChannel(models.Model):
                     END as is_unrated,
                     C.livechat_operator_id as partner_id
                 FROM mail_channel C
-                    JOIN mail_message_mail_channel_rel R ON (C.id = R.mail_channel_id and C.livechat_operator_id is not null)
+                    JOIN mail_message_mail_channel_rel R ON (C.id = R.mail_channel_id)
                     JOIN mail_message M ON (M.id = R.mail_message_id)
                     JOIN im_livechat_channel L ON (L.id = C.livechat_channel_id)
+                    LEFT JOIN mail_message MO ON (R.mail_message_id = MO.id AND MO.author_id = C.livechat_operator_id)
                     LEFT JOIN rating_rating Rate ON (Rate.res_id = C.id and Rate.res_model = 'mail.channel' and Rate.parent_res_model = 'im_livechat.channel')
+                    WHERE C.livechat_operator_id is not null
                 GROUP BY C.livechat_operator_id, C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid, Rate.rating
             )
         """)
diff --git a/addons/im_livechat/report/im_livechat_report_operator.py b/addons/im_livechat/report/im_livechat_report_operator.py
index a212cd5633b..a9391e2a6be 100644
--- a/addons/im_livechat/report/im_livechat_report_operator.py
+++ b/addons/im_livechat/report/im_livechat_report_operator.py
@@ -28,15 +28,18 @@ class ImLivechatReportOperator(models.Model):
             CREATE OR REPLACE VIEW im_livechat_report_operator AS (
                 SELECT
                     row_number() OVER () AS id,
-                    C.livechat_operator_id as partner_id,
-                    L.id as livechat_channel_id,
-                    count(C.id) as nbr_channel,
-                    C.id as channel_id,
-                    C.create_date as start_date,
-                    EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration,
-                    EXTRACT('epoch' from ((SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=C.livechat_operator_id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id)-(SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id IS NULL AND R.mail_channel_id = C.id AND R.mail_message_id = M.id))) as time_to_answer
+                    C.livechat_operator_id AS partner_id,
+                    C.livechat_channel_id AS livechat_channel_id,
+                    COUNT(DISTINCT C.id) AS nbr_channel,
+                    C.id AS channel_id,
+                    C.create_date AS start_date,
+                    EXTRACT('epoch' FROM MAX(M.create_date) - MIN(M.create_date)) AS duration,
+                    EXTRACT('epoch' FROM MIN(MO.create_date) - MIN(M.create_date)) AS time_to_answer
                 FROM mail_channel C
-                    JOIN im_livechat_channel L ON (L.id = C.livechat_channel_id and C.livechat_operator_id is not null)
-                GROUP BY C.livechat_operator_id, L.id, C.id, C.create_date
+                    JOIN mail_message_mail_channel_rel R ON R.mail_channel_id = C.id
+                    JOIN mail_message M ON R.mail_message_id = M.id
+                    LEFT JOIN mail_message MO ON (R.mail_message_id = MO.id AND MO.author_id = C.livechat_operator_id)
+                WHERE C.livechat_channel_id IS NOT NULL
+                GROUP BY C.id, C.livechat_operator_id
             )
         """)