-- ## 21: Tickets waiting for user feedback, by last modified date ## --

-- This report displays the active tickets that have the "needinfo" keyword, ordered by last modified date. 
-- 
-- This complements the {20} report, which doesn't contain the needinfo tickets anymore.
-- 
-- As a rule of thumb, unanswered tickets over 3 months old can be closed as `worksforme` or `wontfix` (see TracTicketTriage#StatusandResolution for the details).

-- 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,
  id AS ticket, summary, component, version, t.severity AS severity,
  (CASE milestone WHEN '' THEN 'No milestone set (untriaged tickets)' ELSE milestone END) AS __group__,
  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,
  tc.change_cnt || ' by ' || tc.author_cnt as Chgs_Auths,
  t.description AS _description,
  reporter AS _reporter

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 LIKE '%needinfo%'
  AND p.name = t.priority AND p.type = 'priority'
--  AND t.milestone = ''
ORDER BY milestone, changetime desc, p.value, t.severity, time