--- ticket-export-view

DROP VIEW tickets_export_view;

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

    t.project_id,
    projects.name    AS project,

    t.channel_id,
    channels.title   AS channel,

    t.category_id,
    categories.title AS category,

    t.client_id,
    clients.name     AS client,

    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,

    CAST(
      (CASE
          WHEN t.violation_id IS NOT NULL THEN 1
          ELSE 0
      END)
      AS NUMBER(1)
     ) AS violation,

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

    t.total_messages,
    t.total_members,

    t.created_at,
    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

  FROM tickets t
    JOIN projects ON projects.id = t.project_id
    LEFT OUTER JOIN channels ON channels.id = t.channel_id
    LEFT OUTER JOIN categories ON categories.id = t.category_id
    JOIN clients ON clients.id = t.client_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;
