Mysql profiles and watched status

Code:
CREATE DATABASE xbmc_User1video90;

USE xbmc_User1video90;



CREATE VIEW `actorlinkepisode` AS SELECT * FROM `xbmc_video90`.`actorlinkepisode`;

CREATE VIEW `actorlinkmovie` AS SELECT * FROM `xbmc_video90`.`actorlinkmovie`;

CREATE VIEW `actorlinktvshow` AS SELECT * FROM `xbmc_video90`.`actorlinktvshow`;

CREATE VIEW `actors` AS SELECT * FROM `xbmc_video90`.`actors`;

CREATE VIEW `art` AS SELECT * FROM `xbmc_video90`.`art`;

CREATE VIEW `artistlinkmusicvideo` AS SELECT * FROM `xbmc_video90`.`artistlinkmusicvideo`;

CREATE VIEW `country` AS SELECT * FROM `xbmc_video90`.`country`;

CREATE VIEW `countrylinkmovie` AS SELECT * FROM `xbmc_video90`.`countrylinkmovie`;

CREATE VIEW `directorlinkepisode` AS SELECT * FROM `xbmc_video90`.`directorlinkepisode`;

CREATE VIEW `directorlinkmovie` AS SELECT * FROM `xbmc_video90`.`directorlinkmovie`;

CREATE VIEW `directorlinkmusicvideo` AS SELECT * FROM `xbmc_video90`.`directorlinkmusicvideo`;

CREATE VIEW `directorlinktvshow` AS SELECT * FROM `xbmc_video90`.`directorlinktvshow`;

CREATE VIEW `episode` AS SELECT * FROM `xbmc_video90`.`episode`;

CREATE VIEW `genre` AS SELECT * FROM `xbmc_video90`.`genre`;

CREATE VIEW `genrelinkmovie` AS SELECT * FROM `xbmc_video90`.`genrelinkmovie`;

CREATE VIEW `genrelinkmusicvideo` AS SELECT * FROM `xbmc_video90`.`genrelinkmusicvideo`;

CREATE VIEW `genrelinktvshow` AS SELECT * FROM `xbmc_video90`.`genrelinktvshow`;

CREATE VIEW `movie` AS SELECT * FROM `xbmc_video90`.`movie`;

CREATE VIEW `movielinktvshow` AS SELECT * FROM `xbmc_video90`.`movielinktvshow`;

CREATE VIEW `musicvideo` AS SELECT * FROM `xbmc_video90`.`musicvideo`;

CREATE VIEW `path` AS SELECT * FROM `xbmc_video90`.`path`;

CREATE VIEW `seasons` AS SELECT * FROM `xbmc_video90`.`seasons`;

CREATE VIEW `settings` AS SELECT * FROM `xbmc_video90`.`settings`;

CREATE VIEW `sets` AS SELECT * FROM `xbmc_video90`.`sets`;

CREATE VIEW `stacktimes` AS SELECT * FROM `xbmc_video90`.`stacktimes`;

CREATE VIEW `streamdetails` AS SELECT * FROM `xbmc_video90`.`streamdetails`;

CREATE VIEW `studio` AS SELECT * FROM `xbmc_video90`.`studio`;

CREATE VIEW `studiolinkmovie` AS SELECT * FROM `xbmc_video90`.`studiolinkmovie`;

CREATE VIEW `studiolinkmusicvideo` AS SELECT * FROM `xbmc_video90`.`studiolinkmusicvideo`;

CREATE VIEW `studiolinktvshow` AS SELECT * FROM `xbmc_video90`.`studiolinktvshow`;

CREATE VIEW `tag` AS SELECT * FROM `xbmc_video90`.`tag`;

CREATE VIEW `taglinks` AS SELECT * FROM `xbmc_video90`.`taglinks`;

CREATE VIEW `tvshow` AS SELECT * FROM `xbmc_video90`.`tvshow`;

CREATE VIEW `tvshowlinkpath` AS SELECT * FROM `xbmc_video90`.`tvshowlinkpath`;

CREATE VIEW `version` AS SELECT * FROM `xbmc_video90`.`version`;

CREATE VIEW `writerlinkepisode` AS SELECT * FROM `xbmc_video90`.`writerlinkepisode`;

CREATE VIEW `writerlinkmovie` AS SELECT * FROM `xbmc_video90`.`writerlinkmovie`;



CREATE TABLE `bookmark` (`idBookmark` int(11) NOT NULL auto_increment,`idFile` int(11) default NULL,`timeInSeconds` double default NULL,`totalTimeInSeconds` double default NULL,`thumbNailImage` text,`player` text,`playerState` text,`type` int(11) default NULL,PRIMARY KEY  (`idBookmark`),KEY `ix_bookmark` (`idFile`,`type`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE `watched_history` (`idFile` int(11) NOT NULL,`playCount` int(11) DEFAULT NULL,`lastPlayed` text,PRIMARY KEY (`idFile`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 SELECT `idFile` FROM `xbmc_video90`.`files`;

CREATE VIEW `files` AS SELECT `f`.`idFile` AS `idFile`,`f`.`idPath` AS `idPath`,`f`.`strFilename` AS `strFilename`,`wh`.`playCount` AS `playCount`,`wh`.`lastPlayed` AS `lastPlayed`, `f`.`dateAdded` AS `dateAdded` FROM `xbmc_video90`.`files` `f` LEFT JOIN `xbmc_User1video90`.`watched_history` `wh` ON `f`.`idFile` = `wh`.`idFile`;

CREATE VIEW `tvshowcounts` AS SELECT `tvshow`.`idShow` AS `idShow`,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`,max(`files`.`dateAdded`) AS `dateAdded` FROM ((`tvshow` LEFT JOIN `episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) LEFT JOIN `files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;



CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` SQL SECURITY DEFINER VIEW `episodeview` AS SELECT `episode`.`idEpisode` AS `idEpisode`,`episode`.`idFile` AS `idFile`,`episode`.`c00` AS `c00`,`episode`.`c01` AS `c01`,`episode`.`c02` AS `c02`,`episode`.`c03` AS `c03`,`episode`.`c04` AS `c04`,`episode`.`c05` AS `c05`,`episode`.`c06` AS `c06`,`episode`.`c07` AS `c07`,`episode`.`c08` AS `c08`,`episode`.`c09` AS `c09`,`episode`.`c10` AS `c10`,`episode`.`c11` AS `c11`,`episode`.`c12` AS `c12`,`episode`.`c13` AS `c13`,`episode`.`c14` AS `c14`,`episode`.`c15` AS `c15`,`episode`.`c16` AS `c16`,`episode`.`c17` AS `c17`,`episode`.`c18` AS `c18`,`episode`.`c19` AS `c19`,`episode`.`c20` AS `c20`,`episode`.`c21` AS `c21`,`episode`.`c22` AS `c22`,`episode`.`c23` AS `c23`,`episode`.`idShow` AS `idShow`,`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`.`c13` AS `mpaa`,`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 ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` SQL SECURITY DEFINER VIEW `movieview` AS SELECT `movie`.`idMovie` AS `idMovie`,`movie`.`idFile` AS `idFile`,`movie`.`c00` AS `c00`,`movie`.`c01` AS `c01`,`movie`.`c02` AS `c02`,`movie`.`c03` AS `c03`,`movie`.`c04` AS `c04`,`movie`.`c05` AS `c05`,`movie`.`c06` AS `c06`,`movie`.`c07` AS `c07`,`movie`.`c08` AS `c08`,`movie`.`c09` AS `c09`,`movie`.`c10` AS `c10`,`movie`.`c11` AS `c11`,`movie`.`c12` AS `c12`,`movie`.`c13` AS `c13`,`movie`.`c14` AS `c14`,`movie`.`c15` AS `c15`,`movie`.`c16` AS `c16`,`movie`.`c17` AS `c17`,`movie`.`c18` AS `c18`,`movie`.`c19` AS `c19`,`movie`.`c20` AS `c20`,`movie`.`c21` AS `c21`,`movie`.`c22` AS `c22`,`movie`.`c23` AS `c23`,`movie`.`idSet` AS `idSet`,`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))));

CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` SQL SECURITY DEFINER VIEW `musicvideoview` AS SELECT `musicvideo`.`idMVideo` AS `idMVideo`,`musicvideo`.`idFile` AS `idFile`,`musicvideo`.`c00` AS `c00`,`musicvideo`.`c01` AS `c01`,`musicvideo`.`c02` AS `c02`,`musicvideo`.`c03` AS `c03`,`musicvideo`.`c04` AS `c04`,`musicvideo`.`c05` AS `c05`,`musicvideo`.`c06` AS `c06`,`musicvideo`.`c07` AS `c07`,`musicvideo`.`c08` AS `c08`,`musicvideo`.`c09` AS `c09`,`musicvideo`.`c10` AS `c10`,`musicvideo`.`c11` AS `c11`,`musicvideo`.`c12` AS `c12`,`musicvideo`.`c13` AS `c13`,`musicvideo`.`c14` AS `c14`,`musicvideo`.`c15` AS `c15`,`musicvideo`.`c16` AS `c16`,`musicvideo`.`c17` AS `c17`,`musicvideo`.`c18` AS `c18`,`musicvideo`.`c19` AS `c19`,`musicvideo`.`c20` AS `c20`,`musicvideo`.`c21` AS `c21`,`musicvideo`.`c22` AS `c22`,`musicvideo`.`c23` AS `c23`,`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 ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` SQL SECURITY DEFINER VIEW `tvshowview` AS SELECT `tvshow`.`idShow` AS `idShow`,`tvshow`.`c00` AS `c00`,`tvshow`.`c01` AS `c01`,`tvshow`.`c02` AS `c02`,`tvshow`.`c03` AS `c03`,`tvshow`.`c04` AS `c04`,`tvshow`.`c05` AS `c05`,`tvshow`.`c06` AS `c06`,`tvshow`.`c07` AS `c07`,`tvshow`.`c08` AS `c08`,`tvshow`.`c09` AS `c09`,`tvshow`.`c10` AS `c10`,`tvshow`.`c11` AS `c11`,`tvshow`.`c12` AS `c12`,`tvshow`.`c13` AS `c13`,`tvshow`.`c14` AS `c14`,`tvshow`.`c15` AS `c15`,`tvshow`.`c16` AS `c16`,`tvshow`.`c17` AS `c17`,`tvshow`.`c18` AS `c18`,`tvshow`.`c19` AS `c19`,`tvshow`.`c20` AS `c20`,`tvshow`.`c21` AS `c21`,`tvshow`.`c22` AS `c22`,`tvshow`.`c23` AS `c23`,`path`.`idParentPath` AS `idParentPath`,`path`.`strPath` AS `strPath`,max(`files`.`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 ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` SQL SECURITY DEFINER VIEW `seasonview` AS SELECT `seasons`.`idSeason` AS `idSeason`,`seasons`.`idShow` AS `idShow`,`seasons`.`season` AS `season`,`tvshowview`.`strPath` AS `strPath`,`tvshowview`.`c00` AS `showTitle`,`tvshowview`.`c01` AS `plot`,`tvshowview`.`c05` AS `premiered`,`tvshowview`.`c08` AS `genre`,`tvshowview`.`c14` AS `strStudio`,`tvshowview`.`c13` AS `mpaa`,count(distinct `episodeview`.`idEpisode`) AS `episodes`,count(`files`.`playCount`) AS `playCount` FROM (((`seasons` JOIN `tvshowview` on((`tvshowview`.`idShow` = `seasons`.`idShow`))) JOIN `episodeview` on(((`episodeview`.`idShow` = `seasons`.`idShow`) and (`episodeview`.`c12` = `seasons`.`season`)))) JOIN `files` on((`files`.`idFile` = `episodeview`.`idFile`))) group by `seasons`.`idSeason`;





USE xbmc_video90;



CREATE TRIGGER insert_new_watched AFTER INSERT ON xbmc_video90.files

    FOR EACH ROW

        INSERT INTO xbmc_User1video90.watched_history(idFile) VALUES (NEW.idFile);



CREATE TRIGGER remove_deleted_watched AFTER DELETE ON xbmc_video90.files

    FOR EACH ROW

        DELETE FROM xbmc_User1video90.watched_history WHERE idFile=OLD.idFile LIMIT 1;