-- ## 20: Untriaged - Active tickets with no Milestone, by last modified date ## --

-- This report displays the active tickets that have not yet been assigned to a Milestone, ordered by last modified date.
-- 
-- Those are the tickets that have not yet been analyzed by the TracTeam and are meant to be [TracTicketTriage triaged].
-- 
-- Notes:
--  - the untriaged tickets for which a request for more information has been made (''[query:?milestone=&keywords=~needinfo&status=!closed needinfo]'' keyword) have been excluded from this report. [[br]]
--    See {21} for ''all'' `needinfo` tickets.
--  - the ''Chgs_Auths'' field corresponds to: ''(number of changes) by (number of different change authors)'', and can be used as an indicator of the popularity of the issue

-- Note: this is for a PostgreSQL database, things like:
--
--         (SELECT extract(epoch from current_timestamp) as now) time
--
--       probably won't work with other databases.

SELECT p.value AS __color__,
  t.type AS __group__,
  id AS ticket, summary, component, version, t.severity AS severity,
  owner, status,
  (SELECT (CASE
           WHEN delta > 86400 THEN round(delta/86400.0) || ' days'
           WHEN delta > 3600  THEN round(delta/3600.0) || ' hours'
           ELSE round(delta/60) || ' mins' 
           END)
   FROM (SELECT ((time.now * 1000000 - changetime) / 1000000.0) AS delta
        ) AS ct
  ) AS LastModified,
  COALESCE(tc.change_cnt || ' by ' || tc.author_cnt, '-') as Chgs_Auths,
  t.description AS _description,
  reporter AS _reporter,
  milestone AS _milestone
FROM ticket t LEFT JOIN 
   (SELECT ticket, count(distinct author) as author_cnt, 
          count(*) as change_cnt
    FROM ticket_change 
    WHERE field = 'comment' GROUP BY ticket) tc ON (t.id = tc.ticket), 
   enum p, 
   (SELECT extract(epoch from current_timestamp) as now) time
WHERE t.status != 'closed'
  AND (t.keywords IS NULL OR t.keywords NOT LIKE '%needinfo%')
  AND p.name = t.priority AND p.type = 'priority'
  AND t.milestone = ''
ORDER BY t.type, changetime desc, p.value, t.severity, time