--- tickets

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.assign_time,
    t.response_time,
    t.total_time,

    t.total_messages,
    t.total_members,

    t.created_at,
    t.closed_at

  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 users assignees ON assignees.id = t.assignee_id;

-- ticket messages

CREATE VIEW ticket_messages_export_view AS
  SELECT
    m.id,
    m.ticket_id,

    projects.name    AS project,
    channels.title   AS channel,
    categories.title AS category,

    m.client_id,
    clients.name     AS client,
    users.name       AS "user",
    users.email      AS user_email,

    m.type,
    m.text,

    m.received,
    m.read,

    m.created_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 categories ON categories.id = tickets.category_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;
