# attempt to delete tables named Playlists, PlaylistTracks, or Tracks that already exist
# if your database already has table(s) 
#
DROP TABLE IF EXISTS Playlists;
DROP TABLE IF EXISTS PlaylistTracks;
DROP TABLE IF EXISTS Tracks;


# holds all information regarding an iTunes track
# 
# 
# 
# 
CREATE TABLE Tracks (	trackId INT UNSIGNED NOT NULL,
						trackAlbum VARCHAR(128),
						trackArtist VARCHAR(128),
						trackBitRate INT DEFAULT 128,
						trackComment VARCHAR(128),
						trackComposer VARCHAR(128),
						trackDateAdded DATETIME,
						trackDateModified DATETIME,
						trackDiscCount SMALLINT DEFAULT 1,
						trackDiscNumber SMALLINT DEFAULT 1,
						trackDuration SMALLINT UNSIGNED NOT NULL,
						trackEqualizer VARCHAR(32),
						trackGenre VARCHAR(128) DEFAULT "",
						trackKind VARCHAR(32),	
						trackLocation VARCHAR(128) NOT NULL,
						trackName VARCHAR(128) DEFAULT "",
						trackPlayedCount SMALLINT UNSIGNED DEFAULT 0,
						trackPlayedDate DATETIME,
						trackRating SMALLINT UNSIGNED DEFAULT 0,
						trackSampleRate INT DEFAULT 44100,
						trackSize INT,
						trackCount SMALLINT DEFAULT 0,
						trackNumber SMALLINT DEFAULT 0,						
						trackYear SMALLINT DEFAULT 0,
						trackCurrent ENUM("true", "false"),
						
						PRIMARY KEY(trackId),
						INDEX(trackId)		
);


# holds all the information regarding an iTunes playlist
# EXCEPT for the tracks that it contains; because a playlist can
# (and should) have more than track, we'll need a separate table
# to map this relationship
#
CREATE TABLE Playlists (	playlistId SMALLINT UNSIGNED,
							playlistName VARCHAR(32),
							playlistSmart enum("true", "false"),
							playlistCurrent enum("true", "false"),

							PRIMARY KEY(playlistId),
							INDEX(playlistId)
);


# maps the many-to-one relationship betwee Tracks and Playlists
# 
CREATE TABLE PlaylistTracks (	playlistId SMALLINT UNSIGNED NOT NULL,
								trackId INT UNSIGNED NOT NULL,
								trackIndex SMALLINT UNSIGNED NOT NULL,								
						
								PRIMARY KEY(playlistId, trackId, trackIndex),
								INDEX (trackId)
);