DROP VIEW IF EXISTS tickets_export_view;

CREATE VIEW tickets_export_view AS
SELECT
    t.created_at,

    t.id,
    t.project_id,
    t.channel_id,
    t.chat_id,
    t.category_id,
    t.client_id,
    

    t.rating_id,
    t.state,
    t.fcr,

    projects.name    AS project,
    clients.name     AS client,
    clients.crm_client_id AS client_crm_id,
    client_details.inn,

    CASE WHEN clients.marker_type = 'red' THEN markers.red_marker_name
         WHEN clients.marker_type = 'green' THEN markers.green_marker_name END
                     AS marker_type,
    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,
    groups.id        AS group_id,
    groups.title     AS "group",

    t.assignee_id,
    assignees.name   AS assignee,
    assignees.email  AS assignee_email,
    cci_assignee.custom_crm_id AS assignee_custom_crm_id,

    t.accepter_id,
    accepters.name   AS accepter,
    accepters.email  AS accepter_email,
    cci_accepter.custom_crm_id AS accepter_custom_crm_id,

    ratings.value    AS rating,
    ratings.state    AS rating_state,

    (case
         when t.violation_id is not null then true
         else false
        end) as violation,

    CAST((CASE
              WHEN t.accept_time IS NULL OR t.accept_time = 0 THEN 0
              ELSE t.accept_time
        END) AS INTEGER) as accept_time,

    t.assign_time,
    t.response_time,
    t.total_response_time,
    t.total_time,

    t.total_messages,
    t.total_members,
    t.total_assignments,
    (CASE 
        WHEN t.total_transferred IS NOT NULL THEN  t.total_transferred
        ELSE 0
    END) as total_transferred,

    t.open,
    t.closed_at,

    t.ip,
    t.ip_city,
    t.ip_country,

    t.ua,
    t.ua_browser,
    t.ua_browser_version,
    t.ua_mobile,
    t.ua_os,
    t.ua_os_version,
    t.ua_platform,
    t.system_ticket,
    t.deleted

FROM tickets t

JOIN projects ON projects.id = t.project_id
JOIN clients ON clients.id = t.client_id
LEFT OUTER JOIN client_details ON client_details.client_id = clients.id

LEFT OUTER JOIN markers ON  t.project_id = markers.project_id
LEFT OUTER JOIN channels ON channels.id = t.channel_id
LEFT OUTER JOIN categories ON categories.id = t.category_id
LEFT OUTER JOIN groups ON groups.id = t.group_id
LEFT OUTER JOIN ratings ON ratings.id = t.rating_id
LEFT OUTER JOIN users assignees ON assignees.id = t.assignee_id
LEFT OUTER JOIN users accepters ON accepters.id = t.accepter_id
LEFT OUTER JOIN categories cat ON cat.id IN (
        SELECT unnest(cast(string_to_array(t.category_ids, ',') as bigint[]))
)
LEFT OUTER JOIN custom_crm_ids cci_assignee ON (cci_assignee.user_id = assignees.id AND cci_assignee.project_id = t.project_id)
LEFT OUTER JOIN custom_crm_ids cci_accepter ON (cci_accepter.user_id = accepters.id AND cci_accepter.project_id = t.project_id)

GROUP BY
t.id,
projects.id,
clients.id,
client_details.client_id,
markers.id,
channels.id, 
categories.id, 
groups.id,
ratings.id,
assignees.id,
cci_assignee.custom_crm_id,
accepters.id,
cci_accepter.custom_crm_id