--- user-reports

DROP VIEW report_users;
CREATE VIEW report_users AS
SELECT
    CASE
        WHEN t.date IS NOT NULL
            THEN t.date
        ELSE m.date
        END,

    CASE
        WHEN t.project_id IS NOT NULL
            THEN t.project_id
        ELSE m.project_id
        END,

    CASE
        WHEN t.user_id IS NOT NULL
            THEN t.user_id
        ELSE m.user_id
        END,

    CASE
        WHEN t.category_id IS NOT NULL
            THEN t.category_id
        ELSE m.category_id
        END,

    CASE
        WHEN t.channel_id IS NOT NULL
            THEN t.channel_id
        ELSE m.channel_id
        END,

    t.total_tickets,
    t.total_open_tickets,
    t.tickets_with_fcr,
    t.avg_rating,
    t.avg_resp_time,
    t.max_resp_time,
    t.avg_total_time,
    t.max_total_time,
    t.total_time,
    m.total_messages,
    t.sl_total_response_time_count,
    t.sl_accept_time_count,
    t.sl_total_time_count
FROM
    (
      SELECT
        date_trunc('hour', t.created_at)
                             AS date,
        t.project_id,
        t.assignee_id        AS user_id,
        t.category_id,
        t.channel_id,
        COUNT(t.*)           AS total_tickets,
        COUNT(CASE WHEN t.open = TRUE THEN 1 END) as total_open_tickets,
        COUNT(CASE WHEN t.fcr = TRUE THEN 1 END) as tickets_with_fcr,
        AVG(ratings.value)   AS avg_rating,
        AVG(t.response_time) AS avg_resp_time,
        MAX(t.response_time) AS max_resp_time,
        MAX(t.total_time)      AS max_total_time,
        AVG(t.total_time)    AS avg_total_time,
        SUM(t.total_time)    AS total_time,
        COUNT(CASE WHEN t.total_response_time_ok = TRUE then 1 end) as sl_total_response_time_count,
        COUNT(CASE WHEN t.accept_time_ok = TRUE THEN 1 END) as sl_accept_time_count,
        COUNT(CASE WHEN t.total_time_ok = TRUE THEN 1 END) as sl_total_time_count
      FROM tickets t
      LEFT OUTER JOIN ratings ON ratings.id = t.rating_id
      GROUP BY
        date,
        t.project_id,
        t.assignee_id,
        t.category_id,
        t.channel_id
    ) AS t

    FULL OUTER JOIN
    (
      SELECT
        date_trunc('hour', m.created_at)
                   AS date,
        t.project_id,
        m.user_id,
        t.category_id,
        t.channel_id,
        COUNT(m.*) AS total_messages
      FROM ticket_messages m
        JOIN tickets t ON t.id = m.ticket_id
      WHERE m.user_id IS NOT NULL
      GROUP BY
        date,
        t.project_id,
        m.user_id,
        t.category_id,
        t.channel_id
    ) AS m
      ON m.date = t.date
         AND m.project_id = t.project_id
         AND m.user_id = t.user_id
         AND m.category_id = t.category_id
         AND m.channel_id = t.channel_id;
