[FEAT]: re-enable watch up/next row for next episodes or movies in collection not watched

[courville]

Description

For now the feature is disabled because of performance issue. Inclusion of the working sql queries provided in the additional information section in the WatchUpNextLoader.java is not straightforward because we loose the archos_lastTimePlayed order from previous episode since the FROM video WHERE video_online_id IN query lead to 0 archos_lastTimePlayed.

Additional information

These sql queries provides the solution:

-- next episode in show and movie in collection to watch
-- note: faster with union

WITH v AS 
  (SELECT video_online_id, scraper_name, m_coll_id, m_year, s_id, e_season, e_episode
    FROM video WHERE (m_coll_id NOT NULL OR s_id NOT NULL) AND Archos_lastTimePlayed=0 AND archos_hiddenbyuser = 0 GROUP BY video_online_id),
l AS 
  (SELECT m_coll_id, s_id, MAX(e_season) AS e_season, max(e_episode) AS e_episode, archos_lasttimeplayed, MAX(m_year) AS m_year 
    FROM video WHERE Archos_lastTimePlayed!=0 AND (m_coll_id NOT NULL OR s_id NOT NULL) AND archos_hiddenbyuser = 0 GROUP BY m_coll_id, s_id LIMIT 100)
SELECT v.video_online_id, v.scraper_name, v.e_season, v.e_episode, l.archos_lasttimeplayed 
  FROM v INNER JOIN l ON v.s_id = l.s_id AND
    (CASE WHEN l.e_episode = (SELECT MAX(e_episode) FROM v WHERE s_id = l.s_id AND e_season = l.e_season)
      THEN v.e_season = l.e_season + 1 AND v.e_episode = (SELECT MIN(e_episode) FROM v WHERE s_id = v.s_id AND e_season = l.e_season + 1)
      ELSE v.e_season = l.e_season AND v.e_episode  = (SELECT MIN(e_episode) FROM v WHERE s_id = l.s_id  AND e_season = l.e_season  AND e_episode > l.e_episode)
    END)
UNION
SELECT v.video_online_id, v.scraper_name, v.e_season, v.e_episode, l.archos_lasttimeplayed 
  FROM v INNER JOIN l ON v.m_coll_id = l.m_coll_id
    AND v.m_year = (SELECT Min(m_year) FROM v WHERE m_coll_id = l.m_coll_id AND m_year > l.m_year) 
ORDER BY l.archos_lasttimeplayed DESC

This query is based on the concatenation of the two:

-- next movies in collections to watch
-- v contains all not played movies from collections taking max of archos_lasttimeplayed for each video_online_id
-- l contains latest movie (latest m_year) viewed from each collection

WITH v AS 
  (SELECT video_online_id, scraper_name, m_coll_id, MAX(archos_lasttimeplayed) AS archos_lasttimeplayed, m_year 
    FROM video WHERE m_coll_id NOT NULL AND Archos_lastTimePlayed=0 GROUP BY video_online_id),
l AS 
  (SELECT m_coll_id, MAX(archos_lasttimeplayed) AS archos_lasttimeplayed, MAX(m_year) AS m_year 
    FROM video WHERE Archos_lastTimePlayed!=0 AND m_coll_id NOT NULL GROUP BY m_coll_id LIMIT 100) 
SELECT v.video_online_id, v.scraper_name, l.archos_lasttimeplayed 
  FROM v INNER JOIN l ON v.m_coll_id = l.m_coll_id
    AND v.m_year = (SELECT Min(m_year) FROM v WHERE m_coll_id = l.m_coll_id AND m_year > l.m_year)
ORDER BY l.archos_lasttimeplayed DESC

and

-- next episode to watch
-- v contains all not played episodes
-- l contains latest episode viewed from each show

WITH v AS 
  (SELECT video_online_id, scraper_name, s_id, e_season, e_episode
    FROM video WHERE e_id NOT NULL AND archos_hiddenbyuser = 0 AND Archos_lastTimePlayed=0 GROUP BY video_online_id),
  l AS 
  (SELECT s_id, MAX(e_season) AS e_season, max(e_episode) AS e_episode, archos_lasttimeplayed 
    FROM video WHERE Archos_lastTimePlayed!=0 AND s_id IS NOT NULL AND archos_hiddenbyuser = 0 GROUP BY s_id LIMIT 100)
SELECT v.s_id, v.video_online_id, v.scraper_name, v.e_season, v.e_episode, l.archos_lasttimeplayed FROM v INNER JOIN l ON v.s_id = l.s_id AND
  (CASE WHEN l.e_episode = (SELECT MAX(e_episode) FROM v WHERE s_id = l.s_id AND e_season = l.e_season)
    THEN v.e_season = l.e_season + 1 AND v.e_episode = (SELECT MIN(e_episode) FROM v WHERE s_id = v.s_id AND e_season = l.e_season + 1)
    ELSE v.e_season = l.e_season AND v.e_episode  = (SELECT MIN(e_episode) FROM v WHERE s_id = l.s_id  AND e_season = l.e_season  AND e_episode > l.e_episode)
  END)
ORDER BY l.archos_lasttimeplayed DESC

[courville]

Note that watch up/next row should not be the first. Code can be re-enabled by setting in MainFragment.java FEATURE_WATCH_UP_NEXT = true.