/* VIDEO DB */


/* "create episodeview" */

DROP VIEW IF EXISTS episodeview;
CREATE VIEW episodeview AS SELECT episode.*, files.strFileName AS strFileName, path.strPath AS strPath, files.playCount AS playCount, files.lastPlayed AS lastPlayed, files.dateAdded AS dateAdded, tvshow.c00 AS strTitle, tvshow.c14 AS strStudio, tvshow.c05 AS premiered, tvshow.c12 AS mpaa, tvshow.c16 AS strShowPath, bookmark.timeInSeconds AS resumeTimeInSeconds, bookmark.totalTimeInSeconds AS totalTimeInSeconds,  seasons.idSeason AS idSeason  FROM episode JOIN files ON files.idFile=episode.idFile JOIN tvshow ON tvshow.idShow=episode.idShow LEFT JOIN seasons ON seasons.idShow=episode.idShow AND seasons.season=episode.c12 JOIN path ON files.idPath=path.idPath LEFT JOIN bookmark ON bookmark.idFile=episode.idFile AND bookmark.type=1;

/* "create tvshowview" */
DROP VIEW IF EXISTS tvshowview;
CREATE VIEW tvshowview AS SELECT tvshow.*, path.strPath AS strPath, path.dateAdded AS dateAdded, MAX(files.lastPlayed) AS lastPlayed, NULLIF(COUNT(episode.c12), 0) AS totalCount, COUNT(files.playCount) AS watchedcount, NULLIF(COUNT(DISTINCT(episode.c12)), 0) AS totalSeasons FROM tvshow LEFT JOIN tvshowlinkpath ON tvshowlinkpath.idShow=tvshow.idShow LEFT JOIN path ON path.idPath=tvshowlinkpath.idPath LEFT JOIN episode ON episode.idShow=tvshow.idShow LEFT JOIN files ON files.idFile=episode.idFile GROUP BY tvshow.idShow;

/* "create musicvideoview"
DROP VIEW IF EXISTS musicvideoview;
CREATE VIEW musicvideoview AS SELECT musicvideo.*, files.strFileName as strFileName, path.strPath as strPath, files.playCount as playCount, files.lastPlayed as lastPlayed, files.dateAdded as dateAdded, bookmark.timeInSeconds AS resumeTimeInSeconds, bookmark.totalTimeInSeconds AS totalTimeInSeconds FROM musicvideo JOIN files ON files.idFile=musicvideo.idFile JOIN path ON path.idPath=files.idPath LEFT JOIN bookmark ON bookmark.idFile=musicvideo.idFile AND bookmark.type=1;

/* "create movieview" */
DROP VIEW IF EXISTS movieview;
CREATE VIEW movieview AS SELECT movie.*, sets.strSet AS strSet, files.strFileName AS strFileName, path.strPath AS strPath, files.playCount AS playCount, files.lastPlayed AS lastPlayed, files.dateAdded AS dateAdded, bookmark.timeInSeconds AS resumeTimeInSeconds, bookmark.totalTimeInSeconds AS totalTimeInSeconds FROM movie LEFT JOIN sets ON sets.idSet = movie.idSet JOIN files ON files.idFile=movie.idFile JOIN path ON path.idPath=files.idPath LEFT JOIN bookmark ON bookmark.idFile=movie.idFile AND bookmark.type=1;