DROP DATABASE kodi_music60;
CREATE DATABASE kodi_music60
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

USE kodi_music60;

CREATE TABLE artist ( idArtist integer primary key, 
 strArtist varchar(256), strMusicBrainzArtistID text, 
 strBorn text, strFormed text, strGenres text, strMoods text, 
 strStyles text, strInstruments text, strBiography text, 
 strDied text, strDisbanded text, strYearsActive text, 
 strImage text, strFanart text, 
 lastScraped varchar(20) default NULL);
INSERT INTO artist (idArtist, strArtist, strMusicBrainzArtistID) VALUES( 1, '[Missing Tag]', 'Artist Tag Missing' );

CREATE TABLE album (idAlbum integer primary key, 
 strAlbum varchar(256), strMusicBrainzAlbumID text, 
 strArtists text, strGenres text, 
 iYear integer, idThumb integer, 
 bCompilation integer not null default '0', 
 strMoods text, strStyles text, strThemes text, 
 strReview text, strImage text, strLabel text, 
 strType text, 
 fRating FLOAT NOT NULL DEFAULT 0, 
 iUserrating INTEGER NOT NULL DEFAULT 0, 
 lastScraped varchar(20) default NULL, 
 strReleaseType text, 
 iVotes INTEGER NOT NULL DEFAULT 0);
CREATE TABLE album_artist (idArtist integer, idAlbum integer, iOrder integer, strArtist text);
CREATE TABLE album_genre (idGenre integer, idAlbum integer, iOrder integer);

CREATE TABLE genre (idGenre integer primary key, strGenre varchar(256));
CREATE TABLE path (idPath integer primary key, strPath varchar(512), strHash text);
CREATE TABLE song (idSong integer primary key, 
 idAlbum integer, idPath integer, 
 strArtists text, strGenres text, strTitle varchar(512), 
 iTrack integer, iDuration integer, iYear integer, 
 dwFileNameCRC text, 
 strFileName text, strMusicBrainzTrackID text, 
 iTimesPlayed integer, iStartOffset integer, iEndOffset integer, 
 idThumb integer, 
 lastplayed varchar(20) default NULL, 
 rating FLOAT NOT NULL DEFAULT 0, userrating INTEGER NOT NULL DEFAULT 0, 
 comment text, mood text, dateAdded text, votes INTEGER NOT NULL DEFAULT 0);
CREATE TABLE song_artist (idArtist integer, idSong integer, idRole integer, iOrder integer, strArtist text);
CREATE TABLE song_genre (idGenre integer, idSong integer, iOrder integer);

CREATE TABLE role (idRole integer primary key, strRole text);
INSERT INTO role(idRole, strRole) VALUES (1, 'Artist');

CREATE TABLE albuminfosong (idAlbumInfoSong integer primary key, idAlbumInfo integer, iTrack integer, strTitle text, iDuration integer);

CREATE TABLE content (strPath text, strScraperPath text, strContent text, strSettings text);
CREATE TABLE discography (idArtist integer, strAlbum text, strYear text);

CREATE TABLE art(art_id INTEGER PRIMARY KEY, media_id INTEGER, media_type TEXT, type TEXT, url TEXT);

CREATE TABLE cue (idPath integer, strFileName text, strCuesheet text);


CREATE INDEX idxAlbum ON album(strAlbum(255));
CREATE INDEX idxAlbum_1 ON album(bCompilation);
CREATE UNIQUE INDEX idxAlbum_2 ON album(strMusicBrainzAlbumID(36));

CREATE UNIQUE INDEX idxAlbumArtist_1 ON album_artist ( idAlbum, idArtist );
CREATE UNIQUE INDEX idxAlbumArtist_2 ON album_artist ( idArtist, idAlbum );

CREATE UNIQUE INDEX idxAlbumGenre_1 ON album_genre ( idAlbum, idGenre );
CREATE UNIQUE INDEX idxAlbumGenre_2 ON album_genre ( idGenre, idAlbum );

CREATE INDEX idxGenre ON genre(strGenre(255));

CREATE INDEX idxArtist ON artist(strArtist(255));
CREATE UNIQUE INDEX idxArtist1 ON artist(strMusicBrainzArtistID(36));

CREATE INDEX idxPath ON path(strPath(255));

CREATE INDEX idxSong ON song(strTitle(255));
CREATE INDEX idxSong1 ON song(iTimesPlayed);
CREATE INDEX idxSong2 ON song(lastplayed);
CREATE INDEX idxSong3 ON song(idAlbum);
CREATE INDEX idxSong6 ON song( idPath, strFileName(255) );
CREATE UNIQUE INDEX idxSong7 ON song( idAlbum, strMusicBrainzTrackID(36) );

CREATE UNIQUE INDEX idxSongArtist_1 ON song_artist ( idSong, idArtist, idRole );
CREATE INDEX idxSongArtist_2 ON song_artist ( idSong, idRole );
CREATE INDEX idxSongArtist_3 ON song_artist ( idArtist, idRole );
CREATE INDEX idxSongArtist_4 ON song_artist ( idRole );

CREATE UNIQUE INDEX idxSongGenre_1 ON song_genre ( idSong, idGenre );
CREATE UNIQUE INDEX idxSongGenre_2 ON song_genre ( idGenre, idSong );

CREATE INDEX idxRole on role(strRole(255));

CREATE INDEX idxAlbumInfoSong_1 ON albuminfosong ( idAlbumInfo );

CREATE INDEX idxDiscography_1 ON discography ( idArtist );

CREATE INDEX ix_art ON art(media_id, media_type(20), type(20));

CREATE UNIQUE INDEX idxCue ON cue(idPath, strFileName(255));

CREATE TRIGGER tgrDeleteAlbum AFTER delete ON album FOR EACH ROW BEGIN
  DELETE FROM song WHERE song.idAlbum = old.idAlbum;
  DELETE FROM album_artist WHERE album_artist.idAlbum = old.idAlbum;
  DELETE FROM album_genre WHERE album_genre.idAlbum = old.idAlbum;
  DELETE FROM albuminfosong WHERE albuminfosong.idAlbumInfo=old.idAlbum;
  DELETE FROM art WHERE media_id=old.idAlbum AND media_type='album';
 END
CREATE TRIGGER tgrDeleteArtist AFTER delete ON artist FOR EACH ROW BEGIN
  DELETE FROM album_artist WHERE album_artist.idArtist = old.idArtist;
  DELETE FROM song_artist WHERE song_artist.idArtist = old.idArtist;
  DELETE FROM discography WHERE discography.idArtist = old.idArtist;
  DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist';
 END
CREATE TRIGGER tgrDeleteSong AFTER delete ON song FOR EACH ROW BEGIN
  DELETE FROM song_artist WHERE song_artist.idSong = old.idSong;
  DELETE FROM song_genre WHERE song_genre.idSong = old.idSong;
  DELETE FROM art WHERE media_id=old.idSong AND media_type='song';
 END
CREATE TRIGGER tgrDeletePath AFTER delete ON path FOR EACH ROW BEGIN
  DELETE FROM cue WHERE cue.idPath = old.idPath;
 END

CREATE VIEW songview AS SELECT 
        song.idSong AS idSong, 
        song.strArtists AS strArtists,
        song.strGenres AS strGenres,
        strTitle, 
        iTrack, iDuration, 
        song.iYear AS iYear, 
        strFileName, 
        strMusicBrainzTrackID, 
        iTimesPlayed, iStartOffset, iEndOffset, 
        lastplayed, 
        song.rating, 
        song.userrating, 
        song.votes, 
        comment, 
        song.idAlbum AS idAlbum, 
        strAlbum, 
        strPath, 
        album.bCompilation AS bCompilation,
        album.strArtists AS strAlbumArtists,
        album.strReleaseType AS strAlbumReleaseType,
        song.mood as mood,
        song.dateAdded as dateAdded 
FROM song
  JOIN album ON
    song.idAlbum=album.idAlbum
  JOIN path ON
    song.idPath=path.idPath

CREATE VIEW albumview AS SELECT 
        album.idAlbum AS idAlbum, 
        strAlbum, 
        strMusicBrainzAlbumID, 
        album.strArtists AS strArtists, 
        album.strGenres AS strGenres, 
        album.iYear AS iYear, 
        album.strMoods AS strMoods, 
        album.strStyles AS strStyles, 
        strThemes, 
        strReview, 
        strLabel, 
        strType, 
        album.strImage as strImage, 
        album.fRating, 
        album.iUserrating, 
        album.iVotes, 
        bCompilation, 
        (SELECT AVG(song.iTimesPlayed) FROM song WHERE song.idAlbum = album.idAlbum) AS iTimesPlayed, 
        strReleaseType, 
        (SELECT MAX(song.dateAdded) FROM song WHERE song.idAlbum = album.idAlbum) AS dateAdded, 
        (SELECT MAX(song.lastplayed) FROM song WHERE song.idAlbum = album.idAlbum) AS lastplayed 
FROM album


CREATE VIEW artistview AS SELECT
  idArtist, strArtist, 
  strMusicBrainzArtistID, 
  strBorn, strFormed, strGenres,
  strMoods, strStyles, strInstruments, 
  strBiography, strDied, strDisbanded, 
  strYearsActive, strImage, strFanart, 
  (SELECT MAX(song.dateAdded) FROM song_artist INNER JOIN song ON song.idSong = song_artist.idSong 
  WHERE song_artist.idArtist = artist.idArtist) AS dateAdded 
FROM artist

CREATE VIEW albumartistview AS SELECT
  album_artist.idAlbum AS idAlbum, 
  album_artist.idArtist AS idArtist, 
  0 AS idRole, 
  'AlbumArtist' AS strRole, 
  artist.strArtist AS strArtist, 
  artist.strMusicBrainzArtistID AS strMusicBrainzArtistID, 
  album_artist.iOrder AS iOrder 
FROM album_artist 
JOIN artist ON 
     album_artist.idArtist = artist.idArtist

CREATE VIEW songartistview AS SELECT
  song_artist.idSong AS idSong, 
  song_artist.idArtist AS idArtist, 
  song_artist.idRole AS idRole, 
  role.strRole AS strRole, 
  artist.strArtist AS strArtist, 
  artist.strMusicBrainzArtistID AS strMusicBrainzArtistID, 
  song_artist.iOrder AS iOrder 
FROM song_artist 
JOIN artist ON 
     song_artist.idArtist = artist.idArtist 
JOIN role ON 
     song_artist.idRole = role.idRole