-- Host - Statistics
--
-- A subset of the daemon's statistics.   This is used to permit comparisons 
-- between the daemon database and our own.
-- 
-- artists: number of artists
-- songs: number of songs
-- db_update: last db update in UNIX time
-- playtime: time length of music played

CREATE TABLE Statistic (
    Key         TEXT    NOT NULL,
    Value       TEXT    NOT NULL DEFAULT "0",
    CONSTRAINT pkHost_Statistic PRIMARY KEY (Key)
);

-- Statistical Defaults
INSERT INTO Statistic (Key) VALUES ("artists");
INSERT INTO Statistic (Key) VALUES ("songs");
INSERT INTO Statistic (Key) VALUES ("db_playtime");
INSERT INTO Statistic (Key) VALUES ("db_update");
INSERT INTO Statistic (Key, Value) VALUES ("program_version","1.0.2");


-- Host - Song
-- 
-- A complete list of all the songs are retrieved from the host and stored in
-- this table.  This permits a faster retrieval from the server and allows us
-- to tack-on additional information if so needed.
--
-- RELATIONS: NIL

CREATE TABLE Song (
    File        TEXT    NOT NULL,
    Artist      TEXT,
    Album       TEXT,
    Title       TEXT    NOT NULL,
    Genre       TEXT,
    Time        NUMBER  NOT NULL,
    Track       TEXT,
    Date        NUMBER,
    Rating      NUMBER,
    Art         TEXT,
    CONSTRAINT pkHost_Song PRIMARY KEY (File)
);


-- Host - Audit
-- 
-- A database which keeps track of database activity for SONG elements.  Audit
-- could be in turn be used to generate play lists with certain features, black 
-- list songs and so forth.
--
-- RELATIONS: Song

CREATE TABLE Audit (
    Num         INTEGER     PRIMARY KEY AUTOINCREMENT NOT NULL,
    File        TEXT        NOT NULL,
    Action      TEXT        NOT NULL,
    Stamp       TEXT        NOT NULL
);

-- Filtering by operation by File
CREATE INDEX idxHost_Audits ON Audit (File, Action);


-- Host - Playlist
-- 
-- Lyra supports two methods of saving playlists, locally which results in the
-- list being stored in this database and the other remote.   
--
-- RELATIONS: Nil

CREATE TABLE Playlist (
    Name        TEXT        PRIMARY KEY NOT NULL
);


-- Host - PlaylistSong
--
-- This table will join the many to many relationship which exists between
-- playlists and their associated songs.

CREATE TABLE PlaylistSong (
    Name        TEXT        NOT NULL,
    File        TEXT        NOT NULL,
    CONSTRAINT pk_PlaylistSong         PRIMARY KEY (Name,File),
    CONSTRAINT fkPlaylist_PlaylistSong FOREIGN KEY (Name) REFERENCES Playlist (Name) ON UPDATE CASCADE ON DELETE CASCADE, 
    CONSTRAINT fkSong_PlaylistSong     FOREIGN KEY (File) REFERENCES Song (File) ON UPDATE CASCADE ON DELETE CASCADE    
);


-- HOST - Decades
--
-- A view which takes out the specific year component and uses that to create
-- a list of all decades.

CREATE VIEW Decades AS
	SELECT 
	    File AS File,
		CASE
			WHEN (Song.Date > 1000 AND Song.Date < 3000) THEN Song.Date / 10 * 10
			ELSE NULL
		END AS Decade
 	FROM Song
 	ORDER BY Decade;

 	
-- HOST - Favourite Songs
--
-- Compiles a list of the favourite songs, as in those that have been added
-- and played the most as recorded by the auditor.

CREATE VIEW favSongs AS
	SELECT 
		File AS File,
		COUNT(*) AS Instance
	  FROM Audit
	 WHERE UPPER(Action) IN ('ADDED','PLAY')
  GROUP BY File
  ORDER BY Instance DESC
     LIMIT 150;

     
-- HOST - Favourites
--
-- Compiles a list of the favourites based on the songs chosen.  This is
-- an inexact science due to the fact that people may be selecting one song and
-- skewing results.  However when a group is selected it tends to generate more
-- hits as a result.

-- ALBUMS
CREATE VIEW favAlbum AS
	SELECT 
		so.Album AS Album,
		COUNT(*) AS Instance
	  FROM Song so
 LEFT JOIN  Audit au ON so.File = au.File
	 WHERE UPPER(au.Action) IN ('ADDED','PLAY')
  GROUP BY Album
  ORDER BY Instance DESC
	 LIMIT 10;
	 
-- ARTISTS
CREATE VIEW favArtist AS
	SELECT 
		so.Artist AS Artist,
		COUNT(*) AS Instance
	  FROM Song so
 LEFT JOIN  Audit au ON so.File = au.File
	 WHERE UPPER(au.Action) IN ('ADDED','PLAY')
  GROUP BY Artist
  ORDER BY Instance DESC
	 LIMIT 10;
	
-- GENRE	 
CREATE VIEW favGenre AS
	SELECT 
		so.Genre AS Genre,
		COUNT(*) AS Instance
	  FROM Song so
 LEFT JOIN  Audit au ON so.File = au.File
	 WHERE UPPER(au.Action) IN ('ADDED','PLAY')
  GROUP BY Genre
  ORDER BY Instance DESC
	 LIMIT 10;
	 
-- RATING	 
CREATE VIEW favRating AS
    SELECT 
        File AS File,
        Rating AS Rating
    FROM Song
    WHERE Rating IS NOT NULL 
           AND Rating > 0
    ORDER BY Rating DESC
    LIMIT 150
    