DROP VIEW IF EXISTS ticket_messages_export_view;

CREATE VIEW ticket_messages_export_view AS
SELECT
    m.id,
    m.ticket_id,

    projects.name    AS project,
    channels.title   AS channel,
    CASE WHEN categories.title IS NOT NULL THEN
        (string_agg(cat.title, ',') || ',' || categories.title) 
    ELSE 
        (string_agg(cat.title, ','))
    END AS categories,

    clients.name     AS client,
    m.client_id,
    clients.crm_id   AS client_crm_id,
    users.name       AS "user",
    m.user_id,
    users.email      AS user_email,
    cci.custom_crm_id,

    tickets.assignee_id,
    tickets.accepter_id,

    case when m.autoreply_id is not null then 
    'auto'
    ELSE 
    m.type
    END AS type,
    m.text,
    files.id AS link,

    m.received,
    m.read,
    m.deleted,
    tickets.created_at as ticket_created_at,
    m.created_at as sent_at,
    m.received_at,
    m.read_at

FROM ticket_messages m
         LEFT JOIN tickets ON tickets.id = m.ticket_id
         LEFT OUTER JOIN projects ON projects.id = tickets.project_id
         LEFT OUTER JOIN channels ON channels.id = tickets.channel_id
         LEFT OUTER JOIN categories ON categories.id = tickets.category_id
         LEFT OUTER JOIN categories cat ON cat.id IN (
            SELECT unnest(cast(string_to_array(tickets.category_ids, ',') as bigint[]))
         )
         LEFT OUTER JOIN custom_crm_ids cci ON (cci.user_id = m.user_id AND cci.project_id = tickets.project_id)

         LEFT OUTER JOIN files ON files.id = m.file_id
         LEFT OUTER JOIN clients ON clients.id = m.client_id
         LEFT OUTER JOIN users ON users.id = m.user_id

GROUP BY
m.id,
cci.custom_crm_id,
users.id,
files.id,
tickets.id,
projects.id,
clients.id,
channels.id, 
categories.id