/*
 * Copyright 2015 Synced Synapse. All rights reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.xbmc.kore.provider;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

import org.xbmc.kore.host.HostInfo;
import org.xbmc.kore.utils.LogUtils;


/**
 * Manages the XBMC local database. Info is stored in a {@link SQLiteDatabase}.
 */
public class MediaDatabase extends SQLiteOpenHelper {
	private static final String TAG = LogUtils.makeLogTag(MediaDatabase.class);

	private static final String DB_NAME = "xbmc.sqlite";
    private static final int DB_VERSION_PRE_EVENT_SERVER = 4,
            DB_VERSION_PRE_SONG_ARTISTS = 5,
            DB_VERSION_PRE_SONG_DISPLAY_ARTIST = 6,
            DB_VERSION_PRE_SONG_DISC = 7,
            DB_VERSION_PRE_HOST_VERSION = 8,
            DB_VERSION_PRE_HOST_HTTPS = 9,
            DB_VERSION_PRE_LAST_PLAYED = 10,
            DB_VERSION_PER_HOST_DIRECT_SHARE_TARGET = 11,
            DB_VERSION = 12;

	/**
	 * Tables exposed
	 */
	public interface Tables {
		String HOSTS = "hosts";
        String MOVIES = "movies";
        String MOVIE_CAST = "movie_cast";
        String TVSHOWS = "tvshows";
        String TVSHOWS_CAST = "tvshows_cast";
        String SEASONS = "seasons";
        String EPISODES = "episodes";
        String ARTISTS = "artists";
        String ALBUMS = "albums";
        String SONGS = "songs";
        String SONG_ARTISTS = "song_artists";
        String AUDIO_GENRES = "audio_genres";
        String ALBUM_ARTISTS = "album_artists";
        String ALBUM_GENRES = "album_genres";
        String MUSIC_VIDEOS = "music_videos";

        /**
         * Join to get Albums for an Artist
         */
        String ALBUMS_FOR_ARTIST_JOIN =
                ALBUM_ARTISTS + " JOIN " + ALBUMS + " ON " +
                ALBUM_ARTISTS + "." + MediaContract.AlbumArtists.HOST_ID + "=" + ALBUMS + "." + MediaContract.Albums.HOST_ID +
                " AND " +
                ALBUM_ARTISTS + "." + MediaContract.AlbumArtists.ALBUMID + "=" + ALBUMS + "." + MediaContract.Albums.ALBUMID;

        /**
         * Join to get Artists for an Album
         */
        String ARTISTS_FOR_ALBUM_JOIN =
                ALBUM_ARTISTS + " JOIN " + ARTISTS + " ON " +
                ALBUM_ARTISTS + "." + MediaContract.AlbumArtists.HOST_ID + "=" + ARTISTS + "." + MediaContract.Artists.HOST_ID +
                " AND " +
                ALBUM_ARTISTS + "." + MediaContract.AlbumArtists.ARTISTID + "=" + ARTISTS + "." + MediaContract.Artists.ARTISTID;

        /**
         * Join to get Album for a Genre
         */
        String ALBUMS_FOR_GENRE_JOIN =
                ALBUM_GENRES + " JOIN " + ALBUMS + " ON " +
                ALBUM_GENRES + "." + MediaContract.AlbumGenres.HOST_ID + "=" + ALBUMS + "." + MediaContract.Albums.HOST_ID +
                " AND " +
                ALBUM_GENRES + "." + MediaContract.AlbumGenres.ALBUMID + "=" + ALBUMS + "." + MediaContract.Albums.ALBUMID;

        /**
         * Join to get Genres for an Album
         */
        String GENRES_FOR_ALBUM_JOIN =
                ALBUM_GENRES + " JOIN " + AUDIO_GENRES + " ON " +
                ALBUM_GENRES + "." + MediaContract.AlbumGenres.HOST_ID + "=" + AUDIO_GENRES + "." + MediaContract.AudioGenres.HOST_ID +
                " AND " +
                ALBUM_GENRES + "." + MediaContract.AlbumGenres.GENREID + "=" + AUDIO_GENRES + "." + MediaContract.AudioGenres.GENREID;

        /**
         * Join to get Songs for an Artist or Album with artist info and album info only if available
         */
        String SONGS_FOR_ARTIST_AND_OR_ALBUM_JOIN =
                SONGS + " JOIN " + SONG_ARTISTS + " ON " +
                SONG_ARTISTS + "." + MediaContract.SongArtists.HOST_ID + "=" + SONGS + "." + MediaContract.Songs.HOST_ID +
                " AND " +
                SONG_ARTISTS + "." + MediaContract.SongArtists.SONGID + "=" + SONGS + "." + MediaContract.Songs.SONGID +
                " LEFT JOIN " + ARTISTS + " ON " +
                SONG_ARTISTS + "." + MediaContract.SongArtists.HOST_ID + "=" + ARTISTS + "." + MediaContract.Artists.HOST_ID +
                " AND " +
                SONG_ARTISTS + "." + MediaContract.SongArtists.ARTISTID + "=" + ARTISTS + "." + MediaContract.Artists.ARTISTID +
                " LEFT JOIN " + ALBUM_ARTISTS + " ON " +
                SONG_ARTISTS + "." + MediaContract.SongArtists.HOST_ID + "=" + ALBUM_ARTISTS + "." + MediaContract.AlbumArtists.HOST_ID +
                " AND " +
                SONGS + "." + MediaContract.Songs.ALBUMID + "=" + ALBUM_ARTISTS + "." + MediaContract.AlbumArtists.ALBUMID +
                " LEFT JOIN " + ALBUMS + " ON " +
                SONG_ARTISTS + "." + MediaContract.SongArtists.HOST_ID + "=" + ALBUMS + "." + MediaContract.Albums.HOST_ID +
                " AND " +
                ALBUM_ARTISTS + "." + MediaContract.AlbumArtists.ALBUMID + "=" + ALBUMS + "." + MediaContract.Albums.ALBUMID;
    }



    private interface References {
        String HOST_ID =
                "REFERENCES " + Tables.HOSTS + "(" + BaseColumns._ID + ")";
        String ALBUMID =
                "REFERENCES " + Tables.ALBUMS + "(" + MediaContract.AlbumsColumns.ALBUMID + ")";
        String ARTISTID =
                "REFERENCES " + Tables.ARTISTS + "(" + MediaContract.ArtistsColumns.ARTISTID + ")";
        String GENREID =
                "REFERENCES " + Tables.AUDIO_GENRES + "(" + MediaContract.AudioGenresColumns.GENREID + ")";
        String SONGID =
                "REFERENCES " + Tables.SONGS + "(" + MediaContract.Songs.SONGID + ")";
    }

    public MediaDatabase(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

        // Hosts
        db.execSQL("CREATE TABLE " + Tables.HOSTS + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.HostsColumns.NAME + " TEXT, " +
                   MediaContract.HostsColumns.ADDRESS + " TEXT, " +
                   MediaContract.HostsColumns.PROTOCOL + " INTEGER, " +
                   MediaContract.HostsColumns.HTTP_PORT + " INTEGER, " +
                   MediaContract.HostsColumns.TCP_PORT + " INTEGER, " +
                   MediaContract.HostsColumns.USERNAME + " TEXT, " +
                   MediaContract.HostsColumns.PASSWORD + " TEXT, " +
                   MediaContract.HostsColumns.MAC_ADDRESS + " TEXT, " +
                   MediaContract.HostsColumns.WOL_PORT + " INTEGER, " +
                   MediaContract.HostsColumns.DIRECT_SHARE + " INTEGER, " +
                   MediaContract.HostsColumns.USE_EVENT_SERVER + " INTEGER, " +
                   MediaContract.HostsColumns.EVENT_SERVER_PORT + " INTEGER, " +
                   MediaContract.HostsColumns.KODI_VERSION_MAJOR + " INTEGER, " +
                   MediaContract.HostsColumns.KODI_VERSION_MINOR + " INTEGER, " +
                   MediaContract.HostsColumns.KODI_VERSION_REVISION + " TEXT, " +
                   MediaContract.HostsColumns.KODI_VERSION_TAG + " TEXT, " +
                   MediaContract.HostsColumns.IS_HTTPS + " INTEGER)"
		);

        // Movies
        db.execSQL("CREATE TABLE " + Tables.MOVIES + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.MoviesColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.MoviesColumns.MOVIEID + " INTEGER NOT NULL, " +
                   MediaContract.MoviesColumns.FANART + " TEXT, " +
                   MediaContract.MoviesColumns.POSTER + " TEXT, " +
                   MediaContract.MoviesColumns.PLAYCOUNT + " INTEGER, " +
                   MediaContract.MoviesColumns.TITLE + " TEXT, " +
                   MediaContract.MoviesColumns.FILE + " TEXT, " +
                   MediaContract.MoviesColumns.PLOT + " TEXT, " +
                   MediaContract.MoviesColumns.DIRECTOR + " TEXT, " +
                   MediaContract.MoviesColumns.RUNTIME + " INTEGER, " +
                   MediaContract.MoviesColumns.AUDIO_CHANNELS + " INTEGER, " +
                   MediaContract.MoviesColumns.AUDIO_CODEC + " TEXT, " +
                   MediaContract.MoviesColumns.AUDIO_LANGUAGE + " TEXT, " +
                   MediaContract.MoviesColumns.SUBTITLES_LANGUAGES + " TEXT, " +
                   MediaContract.MoviesColumns.VIDEO_ASPECT + " REAL, " +
                   MediaContract.MoviesColumns.VIDEO_CODEC + " TEXT, " +
                   MediaContract.MoviesColumns.VIDEO_HEIGHT + " INTEGER, " +
                   MediaContract.MoviesColumns.VIDEO_WIDTH + " INTEGER, " +
                   MediaContract.MoviesColumns.COUNTRIES + " TEXT, " +
                   MediaContract.MoviesColumns.GENRES + " TEXT, " +
                   MediaContract.MoviesColumns.IMDBNUMBER + " TEXT, " +
                   MediaContract.MoviesColumns.MPAA + " TEXT, " +
                   MediaContract.MoviesColumns.RATING + " REAL, " +
                   MediaContract.MoviesColumns.SET + " TEXT, " +
                   MediaContract.MoviesColumns.SETID + " INTEGER, " +
                   MediaContract.MoviesColumns.STUDIOS + " TEXT, " +
                   MediaContract.MoviesColumns.TAGLINE + " TEXT, " +
                   MediaContract.MoviesColumns.TOP250 + " INTEGER, " +
                   MediaContract.MoviesColumns.TRAILER + " TEXT, " +
                   MediaContract.MoviesColumns.VOTES + " TEXT, " +
                   MediaContract.MoviesColumns.WRITERS + " TEXT, " +
                   MediaContract.MoviesColumns.YEAR + " INTEGER, " +
                   MediaContract.MoviesColumns.DATEADDED + " TEXT, " +
                   MediaContract.MoviesColumns.LASTPLAYED + " TEXT, " +
                   "UNIQUE (" + MediaContract.MoviesColumns.HOST_ID + ", " + MediaContract.MoviesColumns.MOVIEID + ") ON CONFLICT REPLACE)"
        );

        // Movie Cast
        db.execSQL("CREATE TABLE " + Tables.MOVIE_CAST + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.MovieCastColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.MovieCastColumns.MOVIEID + " INTEGER NOT NULL, " +
                   MediaContract.MovieCastColumns.NAME + " TEXT, " +
                   MediaContract.MovieCastColumns.ORDER + " INTEGER, " +
                   MediaContract.MovieCastColumns.ROLE + " TEXT, " +
                   MediaContract.MovieCastColumns.THUMBNAIL + " TEXT, " +
                   "UNIQUE (" +
                   MediaContract.MovieCastColumns.HOST_ID + ", " +
                   MediaContract.MovieCastColumns.MOVIEID + ", " +
                   MediaContract.MovieCastColumns.NAME +
                   ") ON CONFLICT REPLACE)"
        );

        // TVShows
        db.execSQL("CREATE TABLE " + Tables.TVSHOWS + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.TVShowsColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.TVShowsColumns.TVSHOWID + " INTEGER NOT NULL, " +
                   MediaContract.TVShowsColumns.FANART + " TEXT, " +
                   MediaContract.TVShowsColumns.POSTER + " TEXT, " +
                   MediaContract.TVShowsColumns.PLAYCOUNT + " INTEGER, " +
                   MediaContract.TVShowsColumns.TITLE + " TEXT, " +
                   MediaContract.TVShowsColumns.DATEADDED + " TEXT, " +
                   MediaContract.TVShowsColumns.LASTPLAYED + " TEXT, " +
                   MediaContract.TVShowsColumns.FILE + " TEXT, " +
                   MediaContract.TVShowsColumns.PLOT + " TEXT, " +
                   MediaContract.TVShowsColumns.EPISODE + " INTEGER, " +
                   MediaContract.TVShowsColumns.IMDBNUMBER + " TEXT, " +
                   MediaContract.TVShowsColumns.MPAA + " TEXT, " +
                   MediaContract.TVShowsColumns.PREMIERED + " TEXT, " +
                   MediaContract.TVShowsColumns.RATING + " REAL, " +
                   MediaContract.TVShowsColumns.STUDIO + " TEXT, " +
                   MediaContract.TVShowsColumns.WATCHEDEPISODES + " INTEGER, " +
                   MediaContract.MoviesColumns.GENRES + " TEXT, " +
                   "UNIQUE (" +
                   MediaContract.TVShowsColumns.HOST_ID + ", " +
                   MediaContract.TVShowsColumns.TVSHOWID +
                   ") ON CONFLICT REPLACE)"
        );

        // TVShows Cast
        db.execSQL("CREATE TABLE " + Tables.TVSHOWS_CAST + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.TVShowCastColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.TVShowCastColumns.TVSHOWID + " INTEGER NOT NULL, " +
                   MediaContract.TVShowCastColumns.NAME + " TEXT, " +
                   MediaContract.TVShowCastColumns.ORDER + " INTEGER, " +
                   MediaContract.TVShowCastColumns.ROLE + " TEXT, " +
                   MediaContract.TVShowCastColumns.THUMBNAIL + " TEXT, " +
                   "UNIQUE (" +
                   MediaContract.TVShowCastColumns.HOST_ID + ", " +
                   MediaContract.TVShowCastColumns.TVSHOWID + ", " +
                   MediaContract.TVShowCastColumns.NAME +
                   ") ON CONFLICT REPLACE)"
        );

        // Seasons
        db.execSQL("CREATE TABLE " + Tables.SEASONS + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.SeasonsColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.SeasonsColumns.TVSHOWID + " INTEGER NOT NULL, " +
                   MediaContract.SeasonsColumns.SEASON + " INTEGER NOT NULL, " +
                   MediaContract.SeasonsColumns.LABEL + " TEXT, " +
                   MediaContract.SeasonsColumns.FANART + " TEXT, " +
                   MediaContract.SeasonsColumns.POSTER + " TEXT, " +
                   MediaContract.SeasonsColumns.EPISODE + " INTEGER, " +
                   MediaContract.SeasonsColumns.SHOWTITLE + " TEXT, " +
                   MediaContract.SeasonsColumns.WATCHEDEPISODES + " INTEGER, " +
                   "UNIQUE (" +
                   MediaContract.SeasonsColumns.HOST_ID + ", " +
                   MediaContract.SeasonsColumns.TVSHOWID + ", " +
                   MediaContract.SeasonsColumns.SEASON +
                   ") ON CONFLICT REPLACE)"
        );

        // Episodes
        db.execSQL("CREATE TABLE " + Tables.EPISODES + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.EpisodesColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.EpisodesColumns.EPISODEID + " INTEGER NOT NULL, " +
                   MediaContract.SeasonsColumns.TVSHOWID + " INTEGER NOT NULL, " +
                   MediaContract.SeasonsColumns.SEASON + " INTEGER NOT NULL, " +
                   MediaContract.SeasonsColumns.EPISODE + " INTEGER NOT NULL, " +
                   MediaContract.EpisodesColumns.FANART + " TEXT, " +
                   MediaContract.EpisodesColumns.THUMBNAIL + " TEXT, " +
                   MediaContract.EpisodesColumns.PLAYCOUNT + " INTEGER, " +
                   MediaContract.EpisodesColumns.TITLE + " TEXT, " +
                   MediaContract.EpisodesColumns.DATEADDED + " TEXT, " +
                   MediaContract.EpisodesColumns.FILE + " TEXT, " +
                   MediaContract.EpisodesColumns.PLOT + " TEXT, " +
                   MediaContract.EpisodesColumns.DIRECTOR + " TEXT, " +
                   MediaContract.EpisodesColumns.RUNTIME + " INTEGER, " +
                   MediaContract.EpisodesColumns.FIRSTAIRED + " TEXT, " +
                   MediaContract.EpisodesColumns.RATING + " REAL, " +
                   MediaContract.EpisodesColumns.SHOWTITLE + " TEXT, " +
                   MediaContract.EpisodesColumns.WRITER + " TEXT, " +
                   MediaContract.EpisodesColumns.AUDIO_CHANNELS + " INTEGER, " +
                   MediaContract.EpisodesColumns.AUDIO_CODEC + " TEXT, " +
                   MediaContract.EpisodesColumns.AUDIO_LANGUAGE + " TEXT, " +
                   MediaContract.EpisodesColumns.SUBTITLES_LANGUAGES + " TEXT, " +
                   MediaContract.EpisodesColumns.VIDEO_ASPECT + " REAL, " +
                   MediaContract.EpisodesColumns.VIDEO_CODEC + " TEXT, " +
                   MediaContract.EpisodesColumns.VIDEO_HEIGHT + " INTEGER, " +
                   MediaContract.EpisodesColumns.VIDEO_WIDTH + " INTEGER, " +
                   "UNIQUE (" +
                   MediaContract.EpisodesColumns.HOST_ID + ", " +
                   MediaContract.EpisodesColumns.EPISODEID +
                   ") ON CONFLICT REPLACE)"
        );

        // Artists
        db.execSQL("CREATE TABLE " + Tables.ARTISTS + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.ArtistsColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.ArtistsColumns.ARTISTID + " INTEGER NOT NULL, " +
                   MediaContract.ArtistsColumns.ARTIST + " TEXT, " +
                   MediaContract.ArtistsColumns.DESCRIPTION + " TEXT, " +
                   MediaContract.ArtistsColumns.GENRE + " TEXT, " +
                   MediaContract.ArtistsColumns.FANART + " TEXT, " +
                   MediaContract.ArtistsColumns.THUMBNAIL + " TEXT, " +
                   "UNIQUE (" +
                   MediaContract.ArtistsColumns.HOST_ID + ", " +
                   MediaContract.ArtistsColumns.ARTISTID +
                   ") ON CONFLICT REPLACE)"
        );

        // Albums
        db.execSQL("CREATE TABLE " + Tables.ALBUMS + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.AlbumsColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.AlbumsColumns.ALBUMID + " INTEGER NOT NULL, " +
                   MediaContract.AlbumsColumns.FANART + " TEXT, " +
                   MediaContract.AlbumsColumns.THUMBNAIL + " TEXT, " +
                   MediaContract.AlbumsColumns.DISPLAYARTIST + " TEXT, " +
                   MediaContract.AlbumsColumns.RATING + " INTEGER, " +
                   MediaContract.AlbumsColumns.TITLE + " TEXT, " +
                   MediaContract.AlbumsColumns.YEAR + " INTEGER, " +
                   MediaContract.AlbumsColumns.ALBUMLABEL + " TEXT, " +
                   MediaContract.AlbumsColumns.DESCRIPTION + " TEXT, " +
                   MediaContract.AlbumsColumns.PLAYCOUNT + " INTEGER, " +
                   MediaContract.AlbumsColumns.GENRE + " TEXT, " +
                   "UNIQUE (" +
                   MediaContract.AlbumsColumns.HOST_ID + ", " +
                   MediaContract.AlbumsColumns.ALBUMID +
                   ") ON CONFLICT REPLACE)"
        );

        // Songs
        db.execSQL("CREATE TABLE " + Tables.SONGS + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.SongsColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.SongsColumns.ALBUMID + " INTEGER NOT NULL, " +
                   MediaContract.SongsColumns.DISC + " INTEGER NOT NULL, " +
                   MediaContract.SongsColumns.SONGID + " INTEGER NOT NULL, " +
                   MediaContract.SongsColumns.DURATION + " INTEGER, " +
                   MediaContract.SongsColumns.THUMBNAIL + " TEXT, " +
                   MediaContract.SongsColumns.FILE + " TEXT, " +
                   MediaContract.SongsColumns.TRACK + " INTEGER, " +
                   MediaContract.SongsColumns.TITLE + " TEXT, " +
                   MediaContract.SongsColumns.DISPLAYARTIST + " TEXT, " +
                   "UNIQUE (" +
                   MediaContract.SongsColumns.HOST_ID + ", " +
                   MediaContract.SongsColumns.ALBUMID + ", " +
                   MediaContract.SongsColumns.SONGID +
                   ") ON CONFLICT REPLACE)"
        );

        createSongArtistsTable(db);

        // AudioGenres
        db.execSQL("CREATE TABLE " + Tables.AUDIO_GENRES + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.AudioGenresColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.AudioGenresColumns.GENREID + " INTEGER NOT NULL, " +
                   MediaContract.AudioGenresColumns.THUMBNAIL + " TEXT, " +
                   MediaContract.AudioGenresColumns.TITLE + " TEXT, " +
                   "UNIQUE (" +
                   MediaContract.AudioGenresColumns.HOST_ID + ", " +
                   MediaContract.AudioGenresColumns.GENREID +
                   ") ON CONFLICT REPLACE)"
        );

        // AlbumArtists
        db.execSQL("CREATE TABLE " + Tables.ALBUM_ARTISTS + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.AlbumArtistsColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.AlbumArtistsColumns.ALBUMID + " INTEGER NOT NULL " + References.ALBUMID + ", " +
                   MediaContract.AlbumArtistsColumns.ARTISTID + " INTEGER NOT NULL " + References.ARTISTID + ", " +
                   "UNIQUE (" +
                   MediaContract.AlbumArtistsColumns.HOST_ID + ", " +
                   MediaContract.AlbumArtistsColumns.ALBUMID + ", " +
                   MediaContract.AlbumArtistsColumns.ARTISTID +
                   ") ON CONFLICT REPLACE)"
        );

        // AlbumGenres
        db.execSQL("CREATE TABLE " + Tables.ALBUM_GENRES + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.AlbumGenresColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.AlbumGenresColumns.ALBUMID + " INTEGER NOT NULL " + References.ALBUMID + ", " +
                   MediaContract.AlbumGenresColumns.GENREID + " INTEGER NOT NULL " + References .GENREID + ", " +
                   "UNIQUE (" +
                   MediaContract.AlbumGenresColumns.HOST_ID + ", " +
                   MediaContract.AlbumGenresColumns.ALBUMID + ", " +
                   MediaContract.AlbumGenresColumns.GENREID +
                   ") ON CONFLICT REPLACE)"
        );

        // Music videos
        db.execSQL("CREATE TABLE " + Tables.MUSIC_VIDEOS + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SyncColumns.UPDATED + " INTEGER NOT NULL," +
                   MediaContract.MusicVideosColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.MusicVideosColumns.MUSICVIDEOID + " INTEGER NOT NULL, " +
                   MediaContract.MusicVideosColumns.FANART + " TEXT, " +
                   MediaContract.MusicVideosColumns.THUMBNAIL + " TEXT, " +
                   MediaContract.MusicVideosColumns.PLAYCOUNT + " INTEGER, " +
                   MediaContract.MusicVideosColumns.TITLE + " TEXT, " +
                   MediaContract.MusicVideosColumns.FILE + " TEXT, " +
                   MediaContract.MusicVideosColumns.PLOT + " TEXT, " +
                   MediaContract.MusicVideosColumns.DIRECTOR + " TEXT, " +
                   MediaContract.MusicVideosColumns.RUNTIME + " INTEGER, " +
                   MediaContract.MusicVideosColumns.AUDIO_CHANNELS + " INTEGER, " +
                   MediaContract.MusicVideosColumns.AUDIO_CODEC + " TEXT, " +
                   MediaContract.MusicVideosColumns.AUDIO_LANGUAGE + " TEXT, " +
                   MediaContract.MusicVideosColumns.SUBTITLES_LANGUAGES + " TEXT, " +
                   MediaContract.MusicVideosColumns.VIDEO_ASPECT + " REAL, " +
                   MediaContract.MusicVideosColumns.VIDEO_CODEC + " TEXT, " +
                   MediaContract.MusicVideosColumns.VIDEO_HEIGHT + " INTEGER, " +
                   MediaContract.MusicVideosColumns.VIDEO_WIDTH + " INTEGER, " +
                   MediaContract.MusicVideosColumns.ALBUM + " TEXT, " +
                   MediaContract.MusicVideosColumns.ARTIST + " TEXT, " +
                   MediaContract.MusicVideosColumns.GENRES + " TEXT, " +
                   MediaContract.MusicVideosColumns.STUDIOS + " TEXT, " +
                   MediaContract.MusicVideosColumns.TAG + " TEXT, " +
                   MediaContract.MusicVideosColumns.TRACK + " INTEGER, " +
                   MediaContract.MusicVideosColumns.YEAR + " INTEGER, " +
                   "UNIQUE (" + MediaContract.MusicVideosColumns.HOST_ID + ", " +
                   "" + MediaContract.MusicVideosColumns.MUSICVIDEOID + ") ON CONFLICT REPLACE)"
        );


        // TODO: Indices?

        // Triggers on host delete
        db.execSQL(buildHostsDeleteTrigger(Tables.MOVIES, MediaContract.MoviesColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.MOVIE_CAST, MediaContract.MovieCastColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.TVSHOWS, MediaContract.TVShowsColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.TVSHOWS_CAST, MediaContract.TVShowCastColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.EPISODES, MediaContract.EpisodesColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.SEASONS, MediaContract.SeasonsColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.ARTISTS, MediaContract.ArtistsColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.ALBUMS, MediaContract.AlbumsColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.SONGS, MediaContract.SongsColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.AUDIO_GENRES, MediaContract.AudioGenresColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.ALBUM_ARTISTS, MediaContract.AlbumArtistsColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.SONG_ARTISTS, MediaContract.SongArtistsColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.ALBUM_GENRES, MediaContract.AlbumGenresColumns.HOST_ID));
        db.execSQL(buildHostsDeleteTrigger(Tables.MUSIC_VIDEOS, MediaContract.MusicVideosColumns.HOST_ID));

    }

    private String buildHostsDeleteTrigger(String onTable, String hostIdColumn) {
        return "CREATE TRIGGER host_" + onTable + "_delete AFTER DELETE ON " + Tables.HOSTS +
               " BEGIN DELETE FROM " + onTable +
               " WHERE " + onTable + "." + hostIdColumn + "=old." + BaseColumns._ID +
               ";" + " END;";
    }

    @Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        switch (oldVersion) {
            case DB_VERSION_PRE_EVENT_SERVER:
                // Add USE_EVENT_SERVER and EVENT_SERVER_PORT columns to HOSTS table
                db.execSQL("ALTER TABLE " + Tables.HOSTS +
                           " ADD COLUMN " + MediaContract.HostsColumns.USE_EVENT_SERVER +
                           " INTEGER DEFAULT 1;");
                db.execSQL("ALTER TABLE " + Tables.HOSTS +
                           " ADD COLUMN " + MediaContract.HostsColumns.EVENT_SERVER_PORT +
                           " INTEGER DEFAULT " + HostInfo.DEFAULT_EVENT_SERVER_PORT + ";");
            case DB_VERSION_PRE_SONG_ARTISTS:
                createSongArtistsTable(db);
            case DB_VERSION_PRE_SONG_DISPLAY_ARTIST:
                db.execSQL("ALTER TABLE " + Tables.SONGS +
                           " ADD COLUMN " + MediaContract.SongsColumns.DISPLAYARTIST +
                           " TEXT;");
            case DB_VERSION_PRE_SONG_DISC:
                db.execSQL("ALTER TABLE " + Tables.SONGS +
                           " ADD COLUMN " + MediaContract.SongsColumns.DISC +
                           " INTEGER DEFAULT 1;");
            case DB_VERSION_PRE_HOST_VERSION:
                db.execSQL("ALTER TABLE " + Tables.HOSTS +
                           " ADD COLUMN " + MediaContract.HostsColumns.KODI_VERSION_MAJOR +
                           " INTEGER DEFAULT " + String.valueOf(HostInfo.DEFAULT_KODI_VERSION_MAJOR) + ";");
                db.execSQL("ALTER TABLE " + Tables.HOSTS +
                           " ADD COLUMN " + MediaContract.HostsColumns.KODI_VERSION_MINOR +
                           " INTEGER DEFAULT " + String.valueOf(HostInfo.DEFAULT_KODI_VERSION_MINOR) + ";");
                db.execSQL("ALTER TABLE " + Tables.HOSTS +
                           " ADD COLUMN " + MediaContract.HostsColumns.KODI_VERSION_REVISION +
                           " TEXT DEFAULT " + HostInfo.DEFAULT_KODI_VERSION_REVISION + ";");
                db.execSQL("ALTER TABLE " + Tables.HOSTS +
                           " ADD COLUMN " + MediaContract.HostsColumns.KODI_VERSION_TAG +
                           " TEXT DEFAULT " + HostInfo.DEFAULT_KODI_VERSION_TAG + ";");
            case DB_VERSION_PRE_HOST_HTTPS:
                db.execSQL("ALTER TABLE " + Tables.HOSTS +
                           " ADD COLUMN " + MediaContract.HostsColumns.IS_HTTPS +
                           " INTEGER DEFAULT 0;");
            case DB_VERSION_PRE_LAST_PLAYED:
                db.execSQL("ALTER TABLE " + Tables.MOVIES +
                           " ADD COLUMN " + MediaContract.MoviesColumns.LASTPLAYED +
                           " TEXT;");
                db.execSQL("ALTER TABLE " + Tables.TVSHOWS +
                           " ADD COLUMN " + MediaContract.TVShowsColumns.LASTPLAYED +
                           " TEXT;");
            case DB_VERSION_PER_HOST_DIRECT_SHARE_TARGET:
                // Add DIRECT_SHARE columns to HOSTS table
                db.execSQL("ALTER TABLE " + Tables.HOSTS +
                        " ADD COLUMN " + MediaContract.HostsColumns.DIRECT_SHARE +
                        " INTEGER DEFAULT 1;");
        }
	}

    /**
     * Tokens to move from prefix to suffix when sorting titles
     *
     * TODO: Extract from host's advancedsettings.xml - sortTokens if available via JSONAPI
     */
    private static String[] commonTokens = {"The", /* "An", "A" */};

    /**
     * Given column create SQLite column expression to convert any sortTokens prefixes to suffixes
     *
     * eg.column = "title", commonTokens = {"The", "An", "A"};
     *
     *      (
     *          CASE
     *              WHEN title LIKE 'The %' THEN SUBSTR(title,5) || ', The'
     *              WHEN title LIKE 'An %'  THEN SUBSTR(title,4) || ', An'
     *              WHEN title LIKE 'A %'   THEN SUBSTR(title,3) || ', A'
     *              ELSE title
     *          END
     *      )
     *
     * This allows it to be used in SQL where a column expression is expected ( SELECT, ORDER BY )
     */
    public static String sortCommonTokens(String column) {
        StringBuilder order = new StringBuilder();

        order.append(" (CASE ");

        // Create WHEN for each token, eg 'The Dog' would become 'Dog, The'
        for (String token: commonTokens) {
            order.append(
                 " WHEN " + column + " LIKE '" + token + " %'" +
                 " THEN SUBSTR(" + column + "," + String.valueOf(token.length() + 2) + ")" +
                 " || ', " + token + "' "
            );
        }

        order.append(" ELSE " + column + " END) ");

        return order.toString();
    }

    private void createSongArtistsTable(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + Tables.SONG_ARTISTS + "(" +
                   BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   MediaContract.SongArtistsColumns.HOST_ID + " INTEGER NOT NULL " + References.HOST_ID + ", " +
                   MediaContract.SongArtistsColumns.SONGID + " INTEGER NOT NULL " + References.SONGID + ", " +
                   MediaContract.SongArtistsColumns.ARTISTID + " INTEGER NOT NULL " + References .ARTISTID + ", " +
                   "UNIQUE (" +
                   MediaContract.SongArtistsColumns.HOST_ID + ", " +
                   MediaContract.SongArtistsColumns.SONGID + ", " +
                   MediaContract.SongArtistsColumns.ARTISTID +
                   ") ON CONFLICT REPLACE)"
                  );
    }
}

