--- ticket-export-view


DROP VIEW tickets_export_view;

CREATE VIEW tickets_export_view AS
  SELECT
    t.*,
    projects.name    AS project,
    clients.name     AS client,

    channels.title   AS channel,
    categories.title AS category,
    groups.title     AS "group",
    assignees.name   AS assignee,
    assignees.email  AS assignee_email,
    ratings.value    AS rating

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