-- ## 32: Popular Tickets - TODO ## --

-- This lists tickets that are on the TODO list for a long time.
-- 
-- The criterions for inclusion is that the feature request or bug report is that '''at least 10 different people participated to the ticket activity'''.
-- 
-- Tickets are ordered by ''popularity'', defined as the amount of activity on a ticket over time (using a secret formula taking into account the number of participants in the discussion, the number of people on CC:, ...)
-- 
-- See also:
--  - {33} for a similar list but this time with tickets that have been completed (the idea is that eventually all the {32} tickets will move to {33} one day :-) )
--  - {34} for the list of popular tickets that have received an explicit ''voted''

-- 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__,
  (SELECT
    ROUND(( (tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(t.cc) * 1.5) 
             / (delta/86400.0)
          ) * 10.0
         ) / 10.0
   FROM (SELECT ((time.now * 1000000 - changetime) / 1000000.0) AS delta
        ) AS ct
  ) AS Popularity,
  t.type,
  id AS ticket, summary,
  component, 
  version,
  time as created
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 time.now - t.time > 86400.0 * 30 * 2
  -- well, the above was not really needed as they are all older than 1 year...
  AND tc.author_cnt >= 10
  AND p.name = t.priority AND p.type = 'priority'
ORDER BY
 ( (tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(t.cc) * 1.5) 
    / ((time.now * 1000000 - changetime) / 1000000.0) ) desc,  
 p.value, t.severity, time