--- ticket-category-ids-to-table
IF EXISTS (select 1 from sys.views where name='ticket_messages_export_view' and type='v')
BEGIN
drop view ticket_messages_export_view
END;

IF EXISTS (select 1 from sys.views where name='tickets_export_view' and type='v')
BEGIN
drop view tickets_export_view
END;

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
                COALESCE(agg_categories.categories, '') +  
                COALESCE(CASE WHEN agg_categories.categories <> '' THEN ',' ELSE null END, '')  + categories.title
        ELSE
                COALESCE(agg_categories.categories, '')
    END AS categories,

    clients.name     AS client,
    m.client_id,
    clients.crm_client_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,
    
    m.text,
    files.id AS link,

    CASE WHEN m.autoreply_id IS NOT NULL THEN 
    'auto'
    ELSE 
    m.type
    END AS type,

    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 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
LEFT OUTER JOIN categories ON categories.id = tickets.category_id
LEFT OUTER JOIN (
    SELECT
        t.id,
        COALESCE(STUFF((
            SELECT ',' + cat.title
            FROM categories cat
            INNER JOIN tickets_to_categories tc ON tc.category_id = cat.id
            WHERE tc.ticket_id = t.id
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''), '') AS categories
    FROM tickets t
    GROUP BY t.id
) AS agg_categories ON agg_categories.id = m.ticket_id;

CREATE VIEW tickets_export_view AS
WITH agg_categories AS (
    SELECT
            t.id,
            COALESCE(STUFF((
                SELECT ',' + cat.title
                FROM categories cat
                INNER JOIN tickets_to_categories tc ON tc.category_id = cat.id
                WHERE tc.ticket_id = t.id
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''), '') AS categories
            FROM tickets t
            GROUP BY t.id
)
SELECT
    t.id,
    t.created_at,
    CONVERT(
        VARCHAR(19),
        t.created_at,
        20
    ) AS created_at_text,
    
    CONVERT(
        VARCHAR(19),
            t.blocked_at,
        20
    ) AS blocked_at,

    CONVERT(
        VARCHAR(19),
        t.blocked_until_time,
        20
    ) AS blocked_until_time,

    CONVERT(
        VARCHAR(19),
        t.closed_at,
        20
    ) AS closed_at_text,

    (CASE
        WHEN t.violation_id is not null THEN 1
        ELSE 0
    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,

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

    t.project_id,
    t.channel_id,
    t.chat_id,
    t.client_id,
    t.assignee_id,
    t.accepter_id,
    t.rating_id,
    t.initiator_client,
    t.blocked,
    
    t.block_reason, 
    t.user_who_blocked_id,

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

    t.total_messages,
    t.total_members,
    t.total_assignments,

    t.[open],
    t.closed_at,

    t.state,
    t.fcr,

    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.[deleted],
    t.system_ticket,
    t.category_id,
    t.group_id,
    projects.name    AS project,
    channels.title   AS channel,
    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,

    groups.title     AS [group],

    assignees.name   AS assignee,
    assignees.email  AS assignee_email,
    cci_assignee.custom_crm_id AS assignee_custom_crm_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,

    blocked.name AS user_who_blocked,

    CASE WHEN categories.title IS NOT NULL THEN
                COALESCE(agg_categories.categories, '') +  
                COALESCE(CASE WHEN agg_categories.categories <> '' THEN ',' ELSE null END, '')  + categories.title
        ELSE
                COALESCE(agg_categories.categories, '')
    END AS categories

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 = t.client_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 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 users blocked ON blocked.id = t.user_who_blocked_id
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)
LEFT JOIN categories ON categories.id = t.category_id
LEFT OUTER JOIN agg_categories ON agg_categories.id = t.id;
