Untitled

with cte_last_staff_answer as ( -- выбор последнего сообщения 
    select distinct lsa.conversation_slug_id,
                    lsa.last_staff_message_dttm,
                    m.user_name as last_staff_message_nm
    from raw_reamaze_personal_1640.messages as m
    right join (select conversation_slug as conversation_slug_id,
                        max(created_at) as last_staff_message_dttm
                from raw_reamaze_personal_1640.messages
                where user_staff
                group by conversation_slug
                ) as lsa on lsa.conversation_slug_id = m.conversation_slug
                        and lsa.last_staff_message_dttm = m.created_at
),
cte_first_staff_answer as ( -- выбор первого сообщения 
    select distinct fsa.conversation_slug_id,
                    fsa.first_staff_message_dttm,
                    m.user_name as first_staff_message_nm
    from raw_reamaze_personal_1640.messages as m
    right join (select conversation_slug as conversation_slug_id,
                        min(created_at) as first_staff_message_dttm
                from raw_reamaze_personal_1640.messages
                where user_staff
                group by conversation_slug
                ) as fsa on fsa.conversation_slug_id = m.conversation_slug
                         and fsa.first_staff_message_dttm = m.created_at
),
cte_reamaze_conversations as ( -- объединение диалога и сообщений  
    select 
       c.slug as slug_id,
       c.created_at as slug_created_dttm,
       c.tag_list,
       replace(replace(replace(unnest(string_to_array(c.tag_list, ', ''')), '[', ''), ']', ''), '''', '') as tag_txt,
       c.subject as subject_nm,
       c.updated_at as updated_dttm,
       c.perma_url,
       c.status as status_code,
       c.display_subject as display_subject_txt,
       c.message_count as messages_cnt,
       c.author_id,
       c.assignee_id,
       c.assignee_name as assignee_nm,
       c.last_customer_message_body as last_customer_message_txt,
        c.last_customer_message_created_at as last_customer_message_created_dttm,
       fsa.first_staff_message_dttm,
       fsa.first_staff_message_nm,
       c.last_staff_message_body as last_staff_message_txt,
       lsa.last_staff_message_dttm,
       lsa.last_staff_message_nm,
       c.message_body as message_txt,
       c.origin as slug_origin_code,
       c.category_name as category_code,
       mes.created_at as message_created_dttm,
       mes.visibility as visibility_message_code,
       mes.origin as message_origin_code,
       mes.body as current_message_txt,
       mes.user_name as message_user_nm,
       mes.user_staff as is_user_staff_message
from raw_reamaze_personal_1640.conversations as c
left join cte_last_staff_answer as lsa on lsa.conversation_slug_id = c.slug
left join cte_first_staff_answer as fsa on fsa.conversation_slug_id = c.slug
left join raw_reamaze_personal_1640.messages as mes on mes.conversation_slug = c.slug 
and mes.mt_actual_flag is true 
and mes.user_staff is true --оставляем только имена сотрудников, чтобы не включать ПД в витрину
where c.mt_actual_flag is true
)
select ('x'||MD5(concat(c.slug_id, c.slug_created_dttm, c.tag_txt, c.message_created_dttm)))::bit(64)::int8 as reamaze_conversations_rk,
       SUBSTRING(c.slug_id,0,5000) as slug_id,
       c.slug_created_dttm,
       c.tag_list,
       c.tag_txt,
       SUBSTRING(c.subject_nm,0,5000) as subject_nm,
       c.updated_dttm,
       c.perma_url,
       c.status_code,
       SUBSTRING(c.display_subject_txt,0,5000) as display_subject_txt,
       c.messages_cnt::int8,
       c.author_id,
       c.assignee_id,
       c.assignee_nm,
       SUBSTRING(c.last_customer_message_txt,0,5000) as last_customer_message_txt,
       c.last_customer_message_created_dttm,
       c.first_staff_message_dttm, --на 13.12.2023 поле рассчитано неверно из-за неоднородных таймзон, использовать для аналитики нельзя
       c.first_staff_message_nm,
       SUBSTRING(c.last_staff_message_txt,0,5000) as last_staff_message_txt,
       c.last_staff_message_dttm, --на 13.12.2023 поле рассчитано неверно из-за неоднородных таймзон, использовать для аналитики нельзя
       c.last_staff_message_nm,
       SUBSTRING(c.message_txt,0,5000) as message_txt,
       c.slug_origin_code,
       c.category_code,
       c.message_created_dttm, --на 13.12.2023 поле рассчитано неверно из-за неоднородных таймзон, использовать для аналитики нельзя
       c.visibility_message_code,
       c.message_origin_code,
       SUBSTRING(c.current_message_txt,0,5000) as current_message_txt,
       c.message_user_nm,
       c.is_user_staff_message
from cte_reamaze_conversations as c