DROP VIEW tickets_export_view;

CREATE VIEW tickets_export_view AS
SELECT
    t.created_at,
    t.id,

    t.project_id,
    projects.name    AS project,

    t.channel_id,
    channels.title   AS channel,

    t.chat_id,

    t.category_id,
    categories.title AS category,

    t.client_id,
    clients.name     AS client,
    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.id        AS group_id,
    groups.title     AS [group],

        t.assignee_id,
        assignees.name   AS assignee,
        assignees.email  AS assignee_email,

        t.accepter_id,
        accepters.name   AS accepter,
        accepters.email  AS accepter_email,

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

        (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 DATEDIFF(SECOND, t.created_at, SYSUTCDATETIME())
        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,

        t.[open],
        t.closed_at,
        t.state,

        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

        FROM tickets t
        JOIN projects ON projects.id = t.project_id
        JOIN clients ON clients.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 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;
