-- 022-reports

-- clients

CREATE VIEW report_clients AS
  SELECT
    CASE
    WHEN a."date" IS NOT NULL
      THEN a."date"
    ELSE
      n."date"
    END AS "date",

    CASE
    WHEN a.project_id IS NOT NULL
      THEN a.project_id
    ELSE
      n.project_id
    END AS project_id,

    CASE
    WHEN a.channel_id IS NOT NULL
      THEN a.channel_id
    ELSE
      n.channel_id
    END AS channel_id,

    CASE
    WHEN a.client_type IS NOT NULL
      THEN a.client_type
    ELSE
      n.client_type
    END AS client_id,

    a.total_active,
    n.total_new
  FROM
    (
      SELECT
        TRUNC(t.created_at, 'HH')   AS "date",
        t.project_id,
        t.channel_id,
        c.type                      AS client_type,
        COUNT(DISTINCT t.client_id) AS total_active
      FROM tickets t
        JOIN clients c ON c.id = t.client_id
      GROUP BY
        TRUNC(t.created_at, 'HH'),
        t.project_id,
        t.channel_id,
        c.type
    ) a

    FULL OUTER JOIN
    (
      SELECT
        TRUNC(c.created_at, 'HH') AS "date",
        c.project_id,
        c.channel_id,
        c.type                    AS client_type,
        COUNT(*)                  AS total_new
      FROM clients c
      GROUP BY
        TRUNC(c.created_at, 'HH'),
        c.project_id,
        c.channel_id,
        c.type
    ) n
      ON n."date" = a."date"
         AND n.project_id = a.project_id
         AND n.channel_id = a.channel_id
         AND n.client_type = a.client_type;

-- users
CREATE VIEW report_users AS
  SELECT
    CASE
    WHEN t."date" IS NOT NULL
      THEN t."date"
    ELSE m."date"
    END AS "date",

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

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

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

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

    t.total_tickets,
    t.avg_resp_time,
    t.avg_total_time,
    m.total_messages
  FROM
    (
      SELECT
        TRUNC(t.created_at, 'HH')
                             AS "date",
        t.project_id,
        t.assignee_id        AS user_id,
        t.category_id,
        t.channel_id,
        COUNT(*)           AS total_tickets,
        AVG(t.response_time) AS avg_resp_time,
        AVG(t.total_time)    AS avg_total_time
      FROM tickets t
      GROUP BY
        TRUNC(t.created_at, 'HH'),
        t.project_id,
        t.assignee_id,
        t.category_id,
        t.channel_id
    ) t

    FULL OUTER JOIN
    (
      SELECT
        TRUNC(m.created_at, 'HH') AS "date",
        t.project_id,
        m.user_id,
        t.category_id,
        t.channel_id,
        COUNT(*)                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
        TRUNC(m.created_at, 'HH'),
        t.project_id,
        m.user_id,
        t.category_id,
        t.channel_id
    ) 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;
