
CREATE TABLE role (
 id INTEGER NOT NULL PRIMARY KEY,
 name VARCHAR(50) NOT NULL);

CREATE TABLE user (
  username VARCHAR(256) NOT NULL PRIMARY KEY,
  password VARCHAR(256) NOT NULL,
  bytes_streamed BIGINT DEFAULT 0 NOT NULL,
  bytes_downloaded BIGINT DEFAULT 0 NOT NULL,
  bytes_uploaded BIGINT DEFAULT 0 NOT NULL,
  ldap_authenticated BOOLEAN DEFAULT FALSE NOT NULL,
  email VARCHAR(256),
  group_id INTEGER NOT NULL,
  locked VARCHAR(4) NOT NULL,
  comment VARCHAR(256));

CREATE TABLE user_security (
  username VARCHAR(256) NOT NULL,
  token VARCHAR(256) NOT NULL,
  CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE);

CREATE TABLE user_role (
 username VARCHAR(256) NOT NULL,
 role_id INTEGER NOT NULL,
 PRIMARY KEY (username, role_id),
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username),
 CONSTRAINT FOREIGN KEY (role_id) REFERENCES role (id));

CREATE TABLE music_folder (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 path VARCHAR(512) NOT NULL,
 name VARCHAR(256) NOT NULL,
 enabled BOOLEAN NOT NULL,
 changed DATETIME DEFAULT 0 NOT NULL,
 MF_INDEX   INTEGER DEFAULT 1 NOT NULL,
 MF_TYPE    INTEGER DEFAULT 1 NOT NULL,
 MF_GROUPBY INTEGER DEFAULT 0 NOT NULL,
 MF_TIMER   TIMESTAMP,
 MF_INTERVAL INTEGER DEFAULT 360000);

 CREATE TABLE music_folder_tasks (
  id INT NOT NULL AUTO_INCREMENT UNIQUE,
  MUSIC_FOLDER_ID INTEGER NOT NULL,
  FOUND INTEGER DEFAULT 0 NOT NULL,
  SCAN TIMESTAMP NOT NULL,
  STATUS VARCHAR(256),
  RESULT VARCHAR(256),
  COMMENT VARCHAR(256),
  ENABLED BOOLEAN DEFAULT TRUE NOT NULL,
  CONSTRAINT FOREIGN KEY (MUSIC_FOLDER_ID) REFERENCES MUSIC_FOLDER (ID) ON DELETE CASCADE);  
 
 CREATE TABLE music_file_info (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 path VARCHAR(512) NOT NULL,
 rating INTEGER,
 comment VARCHAR(2048),
 play_count INTEGER,
 last_played DATETIME,
 enabled BOOLEAN DEFAULT TRUE NOT NULL);

 CREATE TABLE internet_radio (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 name VARCHAR(256) NOT NULL,
 stream_url VARCHAR(256) NOT NULL,
 homepage_url VARCHAR(256),
 enabled BOOLEAN NOT NULL,
 changed DATETIME DEFAULT 0 NOT NULL);

 CREATE TABLE player (
 id INTEGER NOT NULL PRIMARY KEY,
 name VARCHAR(128),
 type VARCHAR(512),
 username VARCHAR(256),
 ip_address VARCHAR(64),
 auto_control_enabled BOOLEAN NOT NULL,
 last_seen DATETIME,
 cover_art_scheme VARCHAR(32) NOT NULL,
 transcode_scheme VARCHAR(32) NOT NULL,
 dynamic_ip BOOLEAN DEFAULT TRUE NOT NULL,
 client_side_playlist BOOLEAN DEFAULT FALSE NOT NULL,
 jukebox BOOLEAN DEFAULT FALSE NOT NULL,
 technology VARCHAR(32) DEFAULT 'web' NOT NULL,
 client_id VARCHAR(64));

CREATE TABLE system_avatar (
 id INT NOT NULL,
 name VARCHAR(128),
 created_date DATETIME NOT NULL,
 mime_type VARCHAR(64) NOT NULL,
 width INTEGER NOT NULL,
 height INTEGER NOT NULL,
 data BLOB NOT NULL,
 PRIMARY KEY (id));

 CREATE TABLE custom_avatar (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 name VARCHAR(128),
 created_date DATETIME NOT NULL,
 mime_type VARCHAR(64) NOT NULL,
 width INTEGER NOT NULL,
 height INTEGER NOT NULL,
 data BLOB NOT NULL,
 username VARCHAR(256) NOT NULL,
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE);
 
 CREATE TABLE user_settings (
 username VARCHAR(256) NOT NULL PRIMARY KEY,
 locale VARCHAR(16),
 theme_id VARCHAR(64),
 final_version_notification BOOLEAN DEFAULT TRUE NOT NULL,
 beta_version_notification BOOLEAN DEFAULT FALSE NOT NULL,
 main_caption_cutoff INTEGER DEFAULT 35 NOT NULL,
 main_disc_number BOOLEAN DEFAULT FALSE NOT NULL,
 main_track_number BOOLEAN DEFAULT TRUE NOT NULL,
 main_artist BOOLEAN DEFAULT TRUE NOT NULL,
 main_album BOOLEAN DEFAULT FALSE NOT NULL,
 main_genre BOOLEAN DEFAULT FALSE NOT NULL,
 main_mood BOOLEAN DEFAULT FALSE NOT NULL,
 main_bpm BOOLEAN DEFAULT FALSE NOT NULL,
 main_composer BOOLEAN DEFAULT FALSE NOT NULL,
 main_year BOOLEAN DEFAULT TRUE NOT NULL,
 main_bit_rate BOOLEAN DEFAULT FALSE NOT NULL,
 main_duration BOOLEAN DEFAULT TRUE NOT NULL,
 main_format BOOLEAN DEFAULT FALSE NOT NULL,
 main_file_size BOOLEAN DEFAULT FALSE NOT NULL,
 playlist_caption_cutoff INTEGER DEFAULT 35 NOT NULL,
 playlist_disc_number BOOLEAN DEFAULT FALSE NOT NULL,
 playlist_track_number BOOLEAN DEFAULT FALSE NOT NULL,
 playlist_artist BOOLEAN DEFAULT TRUE NOT NULL,
 playlist_album BOOLEAN DEFAULT TRUE NOT NULL,
 playlist_genre BOOLEAN DEFAULT FALSE NOT NULL,
 playlist_mood BOOLEAN DEFAULT FALSE NOT NULL,
 playlist_bpm BOOLEAN DEFAULT FALSE NOT NULL,
 playlist_composer BOOLEAN DEFAULT FALSE NOT NULL,
 playlist_year BOOLEAN DEFAULT TRUE NOT NULL,
 playlist_bit_rate BOOLEAN DEFAULT FALSE NOT NULL,
 playlist_duration BOOLEAN DEFAULT TRUE NOT NULL,
 playlist_format BOOLEAN DEFAULT TRUE NOT NULL,
 playlist_file_size BOOLEAN DEFAULT TRUE NOT NULL,
 last_fm_enabled BOOLEAN DEFAULT FALSE NOT NULL,
 last_fm_username VARCHAR(256),
 last_fm_password VARCHAR(256),
 transcode_scheme VARCHAR(32) DEFAULT 'off' NOT NULL,
 show_now_playing BOOLEAN DEFAULT TRUE NOT NULL,
 selected_music_folder_id INTEGER DEFAULT -1 NOT NULL,
 party_mode_enabled BOOLEAN DEFAULT FALSE NOT NULL,
 web_player_default BOOLEAN DEFAULT FALSE NOT NULL,
 now_playing_allowed BOOLEAN DEFAULT TRUE NOT NULL,
 avatar_scheme VARCHAR(32) DEFAULT 'none' NOT NULL,
 system_avatar_id INTEGER,
 changed DATETIME DEFAULT 0 NOT NULL,
 show_chat BOOLEAN DEFAULT TRUE NOT NULL,
 list_type VARCHAR(32) DEFAULT 'random' NOT NULL,
 list_rows INTEGER DEFAULT 2,
 list_columns INTEGER DEFAULT 5,
 playqueue_resize BOOLEAN DEFAULT FALSE NOT NULL,
 leftframe_resize BOOLEAN DEFAULT FALSE NOT NULL,
 customscrollbar BOOLEAN DEFAULT TRUE NOT NULL,
 customaccordion BOOLEAN DEFAULT FALSE NOT NULL,
 autohide_chat BOOLEAN DEFAULT FALSE NOT NULL,
 selected_genre VARCHAR(256),
 button_rank BOOLEAN DEFAULT TRUE NOT NULL,
 button_starred BOOLEAN DEFAULT TRUE NOT NULL,
 button_play BOOLEAN DEFAULT TRUE NOT NULL,
 button_playadd BOOLEAN DEFAULT TRUE NOT NULL,
 button_playmore BOOLEAN DEFAULT FALSE NOT NULL,
 button_addcontext BOOLEAN DEFAULT TRUE NOT NULL,
 button_addnext BOOLEAN DEFAULT FALSE NOT NULL,
 button_addlast BOOLEAN DEFAULT FALSE NOT NULL,
 button_download BOOLEAN DEFAULT TRUE NOT NULL,
 button_youtube BOOLEAN DEFAULT TRUE NOT NULL,
 view_as VARCHAR(32) DEFAULT 'mixed' NOT NULL,
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE);
 
CREATE TABLE user_rating (
 username VARCHAR(256) NOT NULL,
 path VARCHAR(512) NOT NULL,
 rating DOUBLE NOT NULL,
 PRIMARY KEY (username, path),
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE);

CREATE TABLE podcast_channel (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 url VARCHAR(256) NOT NULL,
 title VARCHAR(512),
 description VARCHAR(2048),
 status VARCHAR(32) NOT NULL,
 image_url VARCHAR(256) NOT NULL,
 error_message VARCHAR(512));

CREATE TABLE podcast_episode (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 channel_id INTEGER NOT NULL,
 url VARCHAR(256) NOT NULL,
 path VARCHAR(512),
 title VARCHAR(512),
 description VARCHAR(2048),
 publish_date DATETIME,
 duration VARCHAR(32),
 bytes_total BIGINT,
 bytes_downloaded BIGINT,
 status VARCHAR(32) NOT NULL,
 error_message VARCHAR(512),
 CONSTRAINT FOREIGN KEY (channel_id) REFERENCES podcast_channel (id)
 ON DELETE CASCADE);

CREATE TABLE media_file (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 path VARCHAR(512) NOT NULL,
 folder VARCHAR(512),
 type VARCHAR(32) NOT NULL,
 override BOOLEAN NOT NULL, 
 format VARCHAR(16),
 data VARCHAR(512), 
 title VARCHAR(512),
 album VARCHAR(256),
 album_name VARCHAR(256), 
 artist VARCHAR(256),
 album_artist VARCHAR(256),
 disc_number INTEGER,
 track_number INTEGER,
 year INTEGER,
 genre VARCHAR(64),
 mood VARCHAR(64),
 bpm DOUBLE,
 composer VARCHAR(256),
 bit_rate INTEGER,
 variable_bit_rate BOOLEAN NOT NULL,
 duration_seconds INTEGER,
 file_size BIGINT,
 width INTEGER,
 height INTEGER,
 cover_art_path VARCHAR(512),
 parent_path VARCHAR(512),
 play_count INTEGER NOT NULL,
 last_played DATETIME,
 comment VARCHAR(2048),
 created DATETIME NOT NULL,
 changed DATETIME NOT NULL,
 last_scanned DATETIME NOT NULL,
 first_scanned DATETIME NOT NULL, 
 children_last_updated DATETIME NOT NULL,
 present BOOLEAN NOT NULL,
 version INTEGER NOT NULL,
 rank INTEGER DEFAULT 0 NOT NULL, 
 CONSTRAINT UNIQUE (path));

CREATE TABLE artist (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 name VARCHAR(256) NOT NULL,
 cover_art_path VARCHAR(512),
 album_count INTEGER DEFAULT 0 NOT NULL,
 last_scanned DATETIME NOT NULL,
 present BOOLEAN NOT NULL,
 play_count INTEGER DEFAULT 0 NOT NULL,
 song_count INTEGER DEFAULT 0 NOT NULL,
 topplay_found INTEGER DEFAULT 0 NOT NULL,
 topplay_count INTEGER DEFAULT 0 NOT NULL,
 artist_folder VARCHAR(512),
 genre VARCHAR(64),

 CONSTRAINT UNIQUE (name));

CREATE TABLE album (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 path VARCHAR(512) NOT NULL,
 name VARCHAR(256) NOT NULL,
 artist VARCHAR(256) NOT NULL,
 song_count INTEGER DEFAULT 0 NOT NULL,
 duration_seconds INTEGER DEFAULT 0 NOT NULL,
 cover_art_path VARCHAR(512),
 play_count INTEGER DEFAULT 0 NOT NULL,
 last_played DATETIME,
 comment VARCHAR(2048),
 created DATETIME NOT NULL,
 last_scanned DATETIME NOT NULL,
 present BOOLEAN NOT NULL,
 year INTEGER,
 genre VARCHAR(64),
 folder_id INTEGER,
 mediafileid INTEGER,
 albumartist VARCHAR(512),
 CONSTRAINT UNIQUE (artist, name));

CREATE TABLE share (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 name VARCHAR(32) NOT NULL,
 description VARCHAR(512),
 username VARCHAR(256) NOT NULL,
 created DATETIME NOT NULL,
 expires DATETIME,
 last_visited DATETIME,
 visit_count INTEGER DEFAULT 0 NOT NULL,
 CONSTRAINT UNIQUE (name),
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username)
 ON DELETE CASCADE);

CREATE TABLE share_file (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 share_id INTEGER NOT NULL,
 path VARCHAR(512) NOT NULL,
 CONSTRAINT FOREIGN KEY (share_id) REFERENCES share (id) ON DELETE CASCADE);

CREATE TABLE transcoding2 (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 name VARCHAR(128) NOT NULL,
 source_formats VARCHAR(256) NOT NULL,
 target_format VARCHAR(32) NOT NULL,
 step1 VARCHAR(1024) NOT NULL,
 step2 VARCHAR(1024),
 step3 VARCHAR(1024),
 default_active BOOLEAN DEFAULT TRUE NOT NULL);

CREATE TABLE player_transcoding2 (
 player_id INTEGER NOT NULL,
 transcoding_id INTEGER NOT NULL,
 PRIMARY KEY (player_id, transcoding_id),
 CONSTRAINT FOREIGN KEY (player_id) REFERENCES player (id) ON DELETE CASCADE,
 CONSTRAINT FOREIGN KEY (transcoding_id) REFERENCES transcoding2 (id) ON DELETE CASCADE);

CREATE TABLE starred_media_file (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 media_file_id INTEGER NOT NULL,
 username VARCHAR(256) NOT NULL,
 created DATETIME NOT NULL,
 CONSTRAINT FOREIGN KEY (media_file_id) REFERENCES media_file (id) ON DELETE CASCADE,
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE,
 CONSTRAINT UNIQUE (media_file_id, username));

CREATE TABLE starred_album (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 album_id INTEGER NOT NULL,
 username VARCHAR(256) NOT NULL,
 created DATETIME NOT NULL,
 CONSTRAINT FOREIGN KEY (album_id) REFERENCES album (id) ON DELETE CASCADE,
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE,
 CONSTRAINT UNIQUE (album_id, username));

CREATE TABLE starred_artist (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 artist_id INTEGER NOT NULL,
 username VARCHAR(256) NOT NULL,
 created DATETIME NOT NULL,
 CONSTRAINT FOREIGN KEY (artist_id) REFERENCES artist (id) ON DELETE CASCADE,
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE,
 CONSTRAINT UNIQUE (artist_id, username));

CREATE TABLE playlist (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 username VARCHAR(256) NOT NULL,
 is_public BOOLEAN NOT NULL,
 name VARCHAR(256) NOT NULL,
 comment VARCHAR(2048),
 file_count INTEGER DEFAULT 0 NOT NULL,
 duration_seconds INTEGER DEFAULT 0 NOT NULL,
 created DATETIME NOT NULL,
 changed DATETIME NOT NULL,
 imported_from VARCHAR(512),
 sharelevel INTEGER,
 offline_count INTEGER DEFAULT 0 NOT NULL,
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE);

CREATE TABLE playlist_file (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 playlist_id INTEGER NOT NULL,
 media_file_id INTEGER NOT NULL,
 CONSTRAINT FOREIGN KEY (playlist_id) REFERENCES playlist (id) ON DELETE CASCADE,
 CONSTRAINT FOREIGN KEY (media_file_id) REFERENCES media_file (id) ON DELETE CASCADE);

CREATE TABLE playlist_user (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 playlist_id INTEGER NOT NULL,
 username VARCHAR(256) NOT NULL,
 CONSTRAINT UNIQUE (playlist_id, username),
 CONSTRAINT FOREIGN KEY (playlist_id) REFERENCES playlist (id) ON DELETE CASCADE,
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE);

CREATE TABLE bookmark (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 media_file_id INTEGER NOT NULL,
 position_millis BIGINT NOT NULL,
 username VARCHAR(256) NOT NULL,
 comment VARCHAR(1024),
 created DATETIME NOT NULL,
 changed DATETIME NOT NULL,
 CONSTRAINT FOREIGN KEY (media_file_id) REFERENCES media_file (id) ON DELETE CASCADE,
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE,
 CONSTRAINT UNIQUE (media_file_id, username));

CREATE TABLE genre (
 name VARCHAR(64) NOT NULL,
 song_count INTEGER NOT NULL,
 album_count INTEGER DEFAULT 0 NOT NULL);

CREATE TABLE music_folder_user (
 music_folder_id INTEGER NOT NULL,
 username VARCHAR(256) NOT NULL,
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE,
 CONSTRAINT FOREIGN KEY (music_folder_id) REFERENCES music_folder (id) ON DELETE CASCADE);

CREATE TABLE play_queue (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 username VARCHAR(256) NOT NULL,
 current INTEGER, 
 position_millis BIGINT,
 changed DATETIME NOT NULL,
 changed_by VARCHAR(256) NOT NULL,
 CONSTRAINT FOREIGN KEY (username) REFERENCES user (username) ON DELETE CASCADE);

CREATE TABLE play_queue_file (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 sequence_id INTEGER NOT NULL,
 play_queue_id INTEGER NOT NULL,
 media_file_id INTEGER NOT NULL,
 CONSTRAINT FOREIGN KEY (play_queue_id) REFERENCES play_queue (id) ON DELETE CASCADE,
 CONSTRAINT FOREIGN KEY (media_file_id) REFERENCES media_file (id) ON DELETE CASCADE);

CREATE TABLE audio_ad (
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 media_file_id INTEGER NOT NULL,
 weight DOUBLE NOT NULL,
 comment VARCHAR(1024),
 enabled BOOLEAN NOT NULL,
 created DATETIME NOT NULL,
 changed DATETIME NOT NULL,
 CONSTRAINT FOREIGN KEY (media_file_id) REFERENCES media_file (id) ON DELETE CASCADE);

CREATE TABLE statistic_user(
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 username VARCHAR(256) NOT NULL,
 media_file_id INTEGER NOT NULL,
 played TIMESTAMP NOT NULL,
 FOREIGN KEY(media_file_id) REFERENCES media_file (id) ON DELETE CASCADE,
 FOREIGN KEY(username) REFERENCES user (username) ON DELETE CASCADE);

CREATE TABLE user_group(
 id INT NOT NULL AUTO_INCREMENT UNIQUE,
 name VARCHAR(256) NOT NULL,
 audio_default_bitrate INTEGER DEFAULT 0 NOT NULL,
 audio_max_bitrate INTEGER DEFAULT 0 NOT NULL,
 video_default_bitrate INTEGER DEFAULT 1000 NOT NULL,
 video_max_bitrate INTEGER DEFAULT 10000 NOT NULL);
 
CREATE TABLE user_group_access (
 user_group_id INTEGER NOT NULL,
 music_folder_id INTEGER NOT NULL,
 enabled BOOLEAN DEFAULT TRUE NOT NULL,
 PRIMARY KEY(user_group_id, music_folder_id),
 FOREIGN KEY(user_group_id) REFERENCES user_group(id) ON DELETE CASCADE,
 FOREIGN KEY(music_folder_id) REFERENCES music_folder(id) ON DELETE CASCADE);

CREATE TABLE statistic_server (
 ID INT NOT NULL AUTO_INCREMENT UNIQUE,
 MUSIC_FOLDER_ID INTEGER NOT NULL,
 ARTIST_ALL INTEGER,
 ARTIST_ALBUM INTEGER,
 ALBUMS INTEGER,
 GENRE INTEGER,
 MUSIC_FILES INTEGER,
 MUSIC_FILES_SIZE BIGINT,
 VIDEO_FILES INTEGER,
 VIDEO_FILES_SIZE BIGINT,
 PODCAST_FILES INTEGER,
 PODCAST_FILES_SIZE BIGINT,
 AUDIOBOOKS_FILES INTEGER,
 AUDIOBOOKS_FILES_SIZE BIGINT,
 FOREIGN KEY(MUSIC_FOLDER_ID) REFERENCES MUSIC_FOLDER(ID) ON DELETE CASCADE, UNIQUE(MUSIC_FOLDER_ID));

CREATE TABLE lastfm_artist(
 ID INT NOT NULL AUTO_INCREMENT UNIQUE,
 ARTISTNAME VARCHAR(512),
 MBID VARCHAR(256),
 SINCE VARCHAR(32),
 GENRE VARCHAR(256),
 URL VARCHAR(512),
 FANART VARCHAR(512),
 COVERART1 VARCHAR(512),
 COVERART2 VARCHAR(512),
 COVERART3 VARCHAR(512),
 COVERART4 VARCHAR(512),
 COVERART5 VARCHAR(512),
 TOPTAG VARCHAR(256),
 TOPALBUM VARCHAR(256),
 BIO VARCHAR(256),
 SUMMARY VARCHAR(256),
 PLAY_COUNT INTEGER DEFAULT 0);

CREATE TABLE lastfm_artist_similar(
 ID INT NOT NULL AUTO_INCREMENT UNIQUE,
 ARTIST_NAME VARCHAR(256),
 ARTIST_MBID VARCHAR(256),
 SIMILAR_NAME VARCHAR(256),
 SIMILAR_MBID VARCHAR(256));

CREATE TABLE lastfm_artist_toptracks(
 ID INT NOT NULL AUTO_INCREMENT UNIQUE,
 ARTISTNAME VARCHAR(256),
 ALTERNATE VARCHAR(256),
 ARTISTMBID VARCHAR(256),
 SONGNAME VARCHAR(256),
 SONGMBID VARCHAR(256),
 COVERART VARCHAR(256),
 RANK INTEGER DEFAULT 0,
 PLAY_COUNT INTEGER DEFAULT 0);

CREATE INDEX idx_share_name ON share (name);
CREATE INDEX idx_music_file_info_path ON music_file_info (path);
CREATE INDEX idx_media_file_path ON media_file (path);
CREATE INDEX idx_media_file_parent_path ON media_file (parent_path);
CREATE INDEX idx_media_file_type ON media_file (type);
CREATE INDEX idx_media_file_album ON media_file (album);
CREATE INDEX idx_media_file_artist ON media_file (artist);
CREATE INDEX idx_media_file_album_artist ON media_file (album_artist);
CREATE INDEX idx_media_file_present ON media_file (present);
CREATE INDEX idx_media_file_genre ON media_file (genre);
CREATE INDEX idx_media_file_play_count ON media_file (play_count);
CREATE INDEX idx_media_file_created ON media_file (created);
CREATE INDEX idx_media_file_last_played ON media_file (last_played);
CREATE INDEX idx_artist_name ON artist (name);
CREATE INDEX idx_artist_present ON artist (present);
CREATE INDEX idx_album_artist_name ON album (artist, name);
CREATE INDEX idx_album_play_count ON album (play_count);
CREATE INDEX idx_album_last_played ON album (last_played);
CREATE INDEX idx_album_present ON album (present);
CREATE INDEX idx_album_name ON album (name);
CREATE INDEX idx_starred_media_file_media_file_id ON starred_media_file (media_file_id);
CREATE INDEX idx_starred_media_file_username ON starred_media_file (username);
CREATE INDEX idx_starred_album_album_id ON starred_album (album_id);
CREATE INDEX idx_starred_album_username ON starred_album (username);
CREATE INDEX idx_starred_artist_artist_id ON starred_artist (artist_id);
CREATE INDEX idx_starred_artist_username ON starred_artist (username);
CREATE INDEX idx_bookmark_media_file_id ON bookmark (media_file_id);
CREATE INDEX idx_bookmark_username ON bookmark (username);
CREATE INDEX idx_music_folder_user_username ON music_folder_user (username);
CREATE INDEX idx_user_group_access on user_group_access(user_group_id, music_folder_id, enabled);

INSERT INTO role VALUES (1, 'admin');
INSERT INTO role VALUES (2, 'download');
INSERT INTO role VALUES (3, 'upload');
INSERT INTO role VALUES (4, 'playlist');
INSERT INTO role VALUES (5, 'coverart');
INSERT INTO role VALUES (6, 'comment');
INSERT INTO role VALUES (7, 'podcast');
INSERT INTO role VALUES (8, 'stream');
INSERT INTO role VALUES (9, 'settings');
INSERT INTO role VALUES (10, 'jukebox');
INSERT INTO role VALUES (11, 'share');

INSERT INTO user VALUES ('admin', 'admin', 0, 0, 0, FALSE, NULL, 0, 0, '#admin user');

INSERT INTO user_role VALUES ('admin', 1);
INSERT INTO user_role VALUES ('admin', 2);
INSERT INTO user_role VALUES ('admin', 3);
INSERT INTO user_role VALUES ('admin', 4);
INSERT INTO user_role VALUES ('admin', 5);
INSERT INTO user_role VALUES ('admin', 6);
INSERT INTO user_role VALUES ('admin', 7);
INSERT INTO user_role VALUES ('admin', 8);
INSERT INTO user_role VALUES ('admin', 9);
INSERT INTO user_role VALUES ('admin', 10);
INSERT INTO user_role VALUES ('admin', 11);

ALTER TABLE user_settings ADD CONSTRAINT FOREIGN KEY (system_avatar_id) REFERENCES system_avatar (id);

INSERT INTO transcoding2 VALUES(1,'video->flv','avi flv mpg mpeg mp4 m4v mkv mov wmv ogv divx m2ts','flv','ffmpeg -ss %o -i %s -async 1 -b %bk -s %wx%h -ar 44100 -ac 2 -v 0 -f flv -c:v libx264 -preset superfast -threads 0 -',NULL,NULL,TRUE);
INSERT INTO transcoding2 VALUES(2,'video->mkv','avi flv mpg mpeg mp4 m4v mkv mov wmv ogv divx m2ts','mkv','ffmpeg -ss %o -i %s -c:v libx264 -preset superfast -b:v %bk -c:a libvorbis -f matroska -threads 0 -',NULL,NULL,TRUE);
INSERT INTO transcoding2 VALUES(3,'video->webm','avi flv mpg mpeg mp4 m4v mkv mov wmv ogv divx m2ts','webm','ffmpeg -ss %o -t %d -i %s -c:v libvpx -preset superfast -c:a libvorbis -async 1 -vf lutyuv=y=val*1.3 -b %bk -s %wx%h -ar 44100 -ac 2 -v 0 -f webm -threads 0 -',NULL,NULL,TRUE);
INSERT INTO transcoding2 VALUES(4,'wtv->flv','wtv','flv','ffmpeg -ss %o -i %s -c:v libx264 -preset fast -async 30 -b %bk -r 23-.976 -s 720x360 -ar 44100 -ac 2 -v 0 -f flv -threads 0 -',NULL,NULL,TRUE);
INSERT INTO transcoding2 VALUES(5,'hdrun->flv','hdrun','flv','ffmpeg -ss %o -i %z -c:v libx264 -preset superfast -async 1 -b %bk -s %wx%h -ar 44100 -ac 2 -v 0 -f flv -threads 0 -',NULL,NULL,TRUE);
INSERT INTO transcoding2 VALUES(6,'hdrun->mkv','hdrun','mkv','ffmpeg -ss %o -i %z -c:v libx264 -preset superfast -b:v %bk -c:a libvorbis -f matroska -threads 0 -',NULL,NULL,TRUE);
INSERT INTO transcoding2 VALUES(7,'tv->flv','tv','flv','ffmpeg -ss %o -i %p -c:v libx264 -preset superfast -async 1 -b %bk -s %wx%h -ar 44100 -ac 2 -v 0 -f flv -threads 0 -',NULL,NULL,TRUE);
INSERT INTO transcoding2 VALUES(8,'tv->mkv','tv','mkv','ffmpeg -ss %o -i %p -c:v libx264 -preset superfast -b:v %bk -c:a libvorbis -f matroska -threads 0 -',NULL,NULL,TRUE);
INSERT INTO transcoding2 VALUES(9,'audio->ogg','aac wav wma aif aiff ape mp3 m4a mpc shn flac','ogg','ffmpeg -i %s -map 0:0 -vn -c:a libvorbis -ar 44100 -b:a %bk -v 0 -f ogg -',NULL,NULL,FALSE);
INSERT INTO transcoding2 VALUES(10,'audio->mp3','ogg oga aac wav wma aif aiff ape m4a mpc shn flac','mp3','ffmpeg -i %s -map 0:0 -b:a %bk -v 0 -f mp3 -',NULL,NULL,TRUE);
INSERT INTO transcoding2 VALUES(11,'mod->mp3','alm 669 mdl far xm mod fnk imf it liq mod wow mtm ptm rtm stm s3m ult dmf dbm med okt emod sfx m15 mtn amf gdm stx gmc psm j2b umx amd rad hsc flx gtk mgt mtp','mp3','xmp -Dlittle-endian -q -c %s','lame -r -b %b -S --resample 44.1 - -',NULL,TRUE);

INSERT INTO MUSIC_FOLDER VALUES(1,'F:/MEDIA/Artists','Music',TRUE,'2015-04-13 23:18:37.605000',1,1,1,'2015-01-01 00:00:00.100000',360000);

INSERT INTO user_group VALUES(1,'ALL',0,0,1000,10000);
INSERT INTO user_group VALUES(2,'GUEST',0,0,1000,10000);
INSERT INTO user_group VALUES(3,'FAMILY',0,0,1000,10000);
INSERT INTO user_group VALUES(4,'FRIENDS',0,0,1000,10000);
INSERT INTO user_group VALUES(5,'LIMITED',0,0,1000,10000);

INSERT INTO user_group_access VALUES(1,1,TRUE);
INSERT INTO user_group_access VALUES(2,1,TRUE);
INSERT INTO user_group_access VALUES(3,1,TRUE);
INSERT INTO user_group_access VALUES(4,1,TRUE);
INSERT INTO user_group_access VALUES(5,1,TRUE);

INSERT INTO system_avatar VALUES(1,'001','2015-04-24 04:41:04.566000','image/png',48,48, x'[encoded data, 4950 characters]');
INSERT INTO system_avatar VALUES(2,'002','2015-04-14 01:50:18.181000','image/png',48,48, x'[encoded data, 5834 characters]');
INSERT INTO system_avatar VALUES(3,'003','2015-04-14 01:50:18.192000','image/png',48,48, x'[encoded data, 4868 characters]');
INSERT INTO system_avatar VALUES(4,'004','2015-04-14 01:50:18.203000','image/png',48,48, x'[encoded data, 5072 characters]');
INSERT INTO system_avatar VALUES(5,'005','2015-04-14 01:50:18.226000','image/png',48,48, x'[encoded data, 4960 characters]');
INSERT INTO system_avatar VALUES(6,'006','2015-04-14 01:50:18.265000','image/png',48,48, x'[encoded data, 5076 characters]');
INSERT INTO system_avatar VALUES(7,'007','2015-04-14 01:50:18.288000','image/png',48,48, x'[encoded data, 5088 characters]');
INSERT INTO system_avatar VALUES(8,'008','2015-04-14 01:50:18.310000','image/png',48,48, x'[encoded data, 5092 characters]');
INSERT INTO system_avatar VALUES(9,'009','2015-04-14 01:50:18.321000','image/png',48,48, x'[encoded data, 5764 characters]');
INSERT INTO system_avatar VALUES(10,'010','2015-04-14 01:50:18.332000','image/png',48,48, x'[encoded data, 5460 characters]');
INSERT INTO system_avatar VALUES(11,'011','2015-04-14 01:50:18.343000','image/png',48,48, x'[encoded data, 4936 characters]');
INSERT INTO system_avatar VALUES(12,'012','2015-04-14 01:50:18.355000','image/png',48,48, x'[encoded data, 6780 characters]');
INSERT INTO system_avatar VALUES(13,'013','2015-04-14 01:50:18.377000','image/png',48,48, x'[encoded data, 6706 characters]');
INSERT INTO system_avatar VALUES(14,'014','2015-04-14 01:50:18.399000','image/png',48,48, x'[encoded data, 6424 characters]');
INSERT INTO system_avatar VALUES(15,'015','2015-04-14 01:50:18.409000','image/png',48,48, x'[encoded data, 6792 characters]');
INSERT INTO system_avatar VALUES(16,'016','2015-04-14 01:50:18.420000','image/png',48,48, x'[encoded data, 6032 characters]');
INSERT INTO system_avatar VALUES(17,'017','2015-04-14 01:50:18.432000','image/png',48,48, x'[encoded data, 6724 characters]');
INSERT INTO system_avatar VALUES(18,'018','2015-04-14 01:50:18.443000','image/png',48,48, x'[encoded data, 6196 characters]');
INSERT INTO system_avatar VALUES(19,'019','2015-04-14 01:50:18.454000','image/png',48,48, x'[encoded data, 6590 characters]');
INSERT INTO system_avatar VALUES(20,'020','2015-04-14 01:50:18.465000','image/png',48,48, x'[encoded data, 6582 characters]');
INSERT INTO system_avatar VALUES(21,'021','2015-04-14 01:50:18.476000','image/png',48,48, x'[encoded data, 6454 characters]');
INSERT INTO system_avatar VALUES(22,'022','2015-04-14 01:50:18.487000','image/png',48,48, x'[encoded data, 6528 characters]');
INSERT INTO system_avatar VALUES(23,'023','2015-04-14 01:50:18.498000','image/png',48,48, x'[encoded data, 6098 characters]');
INSERT INTO system_avatar VALUES(24,'024','2015-04-14 01:50:18.509000','image/png',48,48, x'[encoded data, 6428 characters]');
INSERT INTO system_avatar VALUES(25,'025','2015-04-14 01:50:18.520000','image/png',48,48, x'[encoded data, 6452 characters]');
INSERT INTO system_avatar VALUES(26,'026','2015-04-14 01:50:18.531000','image/png',48,48, x'[encoded data, 6568 characters]');
INSERT INTO system_avatar VALUES(27,'027','2015-04-14 01:50:18.543000','image/png',48,48, x'[encoded data, 5984 characters]');
INSERT INTO system_avatar VALUES(28,'028','2015-04-14 01:50:18.554000','image/png',48,48, x'[encoded data, 6708 characters]');
INSERT INTO system_avatar VALUES(29,'029','2015-04-14 01:50:18.564000','image/png',48,48, x'[encoded data, 6112 characters]');
INSERT INTO system_avatar VALUES(30,'030','2015-04-14 01:50:18.575000','image/png',48,48, x'[encoded data, 6040 characters]');
INSERT INTO system_avatar VALUES(31,'031','2015-04-14 01:50:18.586000','image/png',48,48, x'[encoded data, 6780 characters]');
INSERT INTO system_avatar VALUES(32,'032','2015-04-14 01:50:18.597000','image/png',48,48, x'[encoded data, 6694 characters]');
INSERT INTO system_avatar VALUES(33,'033','2015-04-14 01:50:18.609000','image/png',48,48, x'[encoded data, 6812 characters]');
INSERT INTO system_avatar VALUES(34,'034','2015-04-14 01:50:18.620000','image/png',48,48, x'[encoded data, 6022 characters]');
INSERT INTO system_avatar VALUES(35,'035','2015-04-14 01:50:18.631000','image/png',48,48, x'[encoded data, 6732 characters]');
INSERT INTO system_avatar VALUES(36,'036','2015-04-14 01:50:18.642000','image/png',48,48, x'[encoded data, 6218 characters]');
INSERT INTO system_avatar VALUES(37,'037','2015-04-14 01:50:18.653000','image/png',48,48, x'[encoded data, 6640 characters]');
INSERT INTO system_avatar VALUES(38,'038','2015-04-14 01:50:18.664000','image/png',48,48, x'[encoded data, 6196 characters]');
INSERT INTO system_avatar VALUES(39,'039','2015-04-14 01:50:18.675000','image/png',48,48, x'[encoded data, 6122 characters]');
INSERT INTO system_avatar VALUES(40,'041','2015-04-14 01:50:18.686000','image/png',48,48, x'[encoded data, 6572 characters]');
INSERT INTO system_avatar VALUES(41,'042','2015-04-14 01:50:18.697000','image/png',48,48, x'[encoded data, 6246 characters]');
INSERT INTO system_avatar VALUES(42,'043','2015-04-14 01:50:18.708000','image/png',48,48, x'[encoded data, 6270 characters]');
INSERT INTO system_avatar VALUES(43,'044','2015-04-14 01:50:18.719000','image/png',48,48, x'[encoded data, 6614 characters]');
INSERT INTO system_avatar VALUES(44,'045','2015-04-14 01:50:18.731000','image/png',48,48, x'[encoded data, 6088 characters]');
INSERT INTO system_avatar VALUES(45,'046','2015-04-14 01:50:18.741000','image/png',48,48, x'[encoded data, 6362 characters]');
INSERT INTO system_avatar VALUES(46,'047','2015-04-14 01:50:18.753000','image/png',48,48, x'[encoded data, 6890 characters]');
INSERT INTO system_avatar VALUES(47,'048','2015-04-14 01:50:18.764000','image/png',48,48, x'[encoded data, 6368 characters]');
INSERT INTO system_avatar VALUES(48,'049','2015-04-14 01:50:18.775000','image/png',48,48, x'[encoded data, 7100 characters]');
INSERT INTO system_avatar VALUES(49,'050','2015-04-14 01:50:18.786000','image/png',48,48, x'[encoded data, 6402 characters]');
INSERT INTO system_avatar VALUES(50,'051','2015-04-14 01:50:18.797000','image/png',48,48, x'[encoded data, 6496 characters]');
INSERT INTO system_avatar VALUES(51,'052','2015-04-14 01:50:18.812000','image/png',48,48, x'[encoded data, 7038 characters]');
INSERT INTO system_avatar VALUES(52,'053','2015-04-14 01:50:18.830000','image/png',48,48, x'[encoded data, 7094 characters]');
INSERT INTO system_avatar VALUES(53,'054','2015-04-14 01:50:18.841000','image/png',48,48, x'[encoded data, 6554 characters]');
INSERT INTO system_avatar VALUES(54,'055','2015-04-14 01:50:18.852000','image/png',48,48, x'[encoded data, 7492 characters]');
INSERT INTO system_avatar VALUES(55,'056','2015-04-14 01:50:18.864000','image/png',48,48, x'[encoded data, 6356 characters]');
INSERT INTO system_avatar VALUES(56,'057','2015-04-14 01:50:18.875000','image/png',48,48, x'[encoded data, 7068 characters]');
INSERT INTO system_avatar VALUES(57,'058','2015-04-14 01:50:18.887000','image/png',48,48, x'[encoded data, 7234 characters]');
INSERT INTO system_avatar VALUES(58,'059','2015-04-14 01:50:18.897000','image/png',48,48, x'[encoded data, 7470 characters]');
