package com.viaccessorca.voplayerdemo.database;

import com.viaccessorca.voplayerdemo.Constants;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.HashMap;

public class MediaMetaInfoDB {

    private static final String TAG = "MediaMetaInfoDB";

    /** General database name for the whole app */
    private static final String APP_DATABASE_NAME = "voplayer_db";
    private static final int APP_DATABASE_VERSION = 3;

    // Columns ids for database :
    // Primary key, mandatory in SQLite database (used in all tables)
    private static final String KEY_MAIN_ID         = "_id";

    // Generic columns, common for all medias, used in generic table
    private static final String KEY_MEDIA_URI           = "media_uri";
    private static final String KEY_LAST_STOP_POS       = "last_stop";
    private static final String KEY_SUBTITLE_FILE       = "associated_sub_file";
    private static final String KEY_SUBTITLE_TRACK      = "associated_sub_trackname";
    private static final String KEY_SUBTITLE_TRACKTYPE  = "associated_sub_type";
    private static final String KEY_AUDIO_TRACK         = "associated_audio";
    private static final String KEY_MEDIA_DURATION      = "duration";

    private static final String KEY_DL_BITRATE       = "dl_bitrate";
    private static final String KEY_DL_PROGRESS      = "dl_progress";
    private static final String KEY_DL_LOCALURL      = "dl_localUrl";
    private static final String KEY_DL_STATUS        = "dl_fullylocal";

    private static final String KEY_DL_SUB_TRACKS    = "dl_subtracks";
    private static final String KEY_DL_AUDIO_TRACKS  = "dl_audiotracks";


    // Database base name
    private static final String LV_MEDIA_META_INFO_TABLE = "media_meta_info_table";

    private static final String LV_CREATE_MEDIA_META_INFO_TABLE_OP =
        "CREATE TABLE "+
        LV_MEDIA_META_INFO_TABLE + " (" +
        KEY_MAIN_ID             + " integer PRIMARY KEY AUTOINCREMENT, " +
        KEY_MEDIA_URI           + " text NOT NULL, " +
        KEY_LAST_STOP_POS       + " integer NOT NULL DEFAULT -1, " +
        KEY_MEDIA_DURATION      + " integer NOT NULL DEFAULT 0, " +
        KEY_SUBTITLE_FILE       + " text, " +
        KEY_SUBTITLE_TRACK  + " text, " +
        KEY_AUDIO_TRACK     + " text, " +
        KEY_SUBTITLE_TRACKTYPE  + " integer NOT NULL DEFAULT 0, " +

        KEY_DL_BITRATE + " integer NOT NULL DEFAULT -1, " +
        KEY_DL_PROGRESS + " integer NOT NULL DEFAULT -1, " +
        KEY_DL_LOCALURL + " text, " +
        KEY_DL_STATUS + " integer NOT NULL DEFAULT 0, " +
        KEY_DL_SUB_TRACKS + " text, " +
        KEY_DL_AUDIO_TRACKS + " text" +
        ");";

    /**
     * Mutex lock for database access protection
     */
    @SuppressLint("UseValueOf")
    private static final Integer databaseMutex = new Integer(0);

    private DatabaseHelper databaseHelper;
    private SQLiteDatabase currentDatabase;

    private HashMap<String, DatabaseInfo> localDBCache;

    private static MediaMetaInfoDB singleton;

    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context)
        {
            super(context, APP_DATABASE_NAME, null, APP_DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db)
        {
            try {
                Log.v(TAG, "Creating media list table: \n" + LV_CREATE_MEDIA_META_INFO_TABLE_OP);
                db.execSQL(LV_CREATE_MEDIA_META_INFO_TABLE_OP);
            }
            catch(SQLException sqle) {
                Log.e(TAG, "Error chen creating database tables");
            }
        }

        @Override
        public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            //allow to use older version with new db
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion
                    + " to "
                    + newVersion);

            if ((oldVersion == 1) && (newVersion == 2)) {
                db.execSQL("ALTER TABLE " + LV_MEDIA_META_INFO_TABLE + " ADD COLUMN " + KEY_DL_SUB_TRACKS + " text");
                db.execSQL("ALTER TABLE " + LV_MEDIA_META_INFO_TABLE + " ADD COLUMN " + KEY_DL_AUDIO_TRACKS + " text");
            }
            if( (oldVersion <= 2) && (3 == newVersion )) {
                db.execSQL("ALTER TABLE " + LV_MEDIA_META_INFO_TABLE + " ADD COLUMN " + KEY_AUDIO_TRACK + " text");
            }
        }
    }

    public static MediaMetaInfoDB getInstance(Context ctx) {
        if(null == singleton) {
            singleton = new MediaMetaInfoDB();
            singleton.openDatabase(ctx);
            singleton.loadLocalDBCache();
        }
        return singleton;
    }

    private MediaMetaInfoDB() {}

    /**
     * Opens the database to make it available for usage.
     * A call to @see closeDatabase() should be done after use.
     * @param ctx application's context
     */
    private void openDatabase(Context ctx) {

        if(null != currentDatabase) {
            Log.w(TAG, "Database already opened !");
            return;
        }
        if(null == databaseHelper) {
            databaseHelper = new DatabaseHelper(ctx);
        }
        synchronized(databaseMutex) {
            currentDatabase = databaseHelper.getWritableDatabase();
        }
    }

    private void loadLocalDBCache() {

        localDBCache = new HashMap<>();

        // Checks for media entry existence in database
        synchronized(databaseMutex) {
            Cursor cursor = currentDatabase.rawQuery("select * from " + LV_MEDIA_META_INFO_TABLE, null);
            Log.d(TAG, "loadLocalDBCache - nb items " + cursor.getCount());
            if (cursor.moveToFirst() && cursor.getCount() > 0) {
                while (!cursor.isAfterLast()) {

                    String mediaId = cursor.getString(cursor.getColumnIndex(KEY_MEDIA_URI));
                    mediaId = mediaId.replaceAll("'", "");
                    DatabaseInfo info = new DatabaseInfo();

                    info.lastPlaybackPosInMs = cursor.getInt(cursor.getColumnIndex(KEY_LAST_STOP_POS));
                    info.mediaDurationInMs = cursor.getInt(cursor.getColumnIndex(KEY_MEDIA_DURATION));

                    info.associatedTimedTextType = cursor.getInt(cursor.getColumnIndex(KEY_SUBTITLE_TRACKTYPE));
                    info.associatedTimedTextTrack = cursor.getString(cursor.getColumnIndex(KEY_SUBTITLE_TRACK));
                    info.associatedSubtitleFile = cursor.getString(cursor.getColumnIndex(KEY_SUBTITLE_FILE));

                    info.associatedAudioTrack = cursor.getString(cursor.getColumnIndex(KEY_AUDIO_TRACK));

                    info.DlStatus = cursor.getInt(cursor.getColumnIndex(KEY_DL_STATUS));
                    info.DLProgress = cursor.getInt(cursor.getColumnIndex(KEY_DL_PROGRESS));
                    info.DlLocalServerUrl = cursor.getString(cursor.getColumnIndex(KEY_DL_LOCALURL));
                    info.DLBitrate = cursor.getInt(cursor.getColumnIndex(KEY_DL_BITRATE));

                    info.DLSelectedAudioTracks = cursor.getString(cursor.getColumnIndex(KEY_DL_AUDIO_TRACKS));
                    info.DLSelectedSubtitles = cursor.getString(cursor.getColumnIndex(KEY_DL_SUB_TRACKS));

                    localDBCache.put(mediaId, info);
                    cursor.moveToNext();
                }
            }
            cursor.close();
        }
    }

    /**
     * Closes the access to the database.
     */
    private void closeDatabase() {
        if(databaseHelper != null) {
            databaseHelper.close();
            databaseHelper = null;
        }
        synchronized(databaseMutex) {
            currentDatabase = null;
        }
    }

    @Override
    protected void finalize() throws Throwable {
        closeDatabase();
        super.finalize();
    }


    /**
     * Creates an entry in the database for the given mediaId
     * @param mediaId  the Uri of the media for the new entry
     * @return          true if the entry creation was successful, false otherwise
     */
    private boolean createNewMediaEntry(String mediaId) {

        if(null == mediaId) {
            Log.w(TAG, "createNewMediaEntry - mediaId null !");
            return false;
        }
        String escapedUri = DatabaseUtils.sqlEscapeString(mediaId);

        ContentValues mediaEntry = new ContentValues();
        mediaEntry.put(KEY_MEDIA_URI, escapedUri);

        try {
            synchronized(databaseMutex) {
                currentDatabase.insertOrThrow(LV_MEDIA_META_INFO_TABLE, null, mediaEntry);
            }
        }
        catch(SQLException sqle) {
            Log.e(TAG, "createNewMediaEntry - Error when adding new media entry");
            sqle.printStackTrace();
            return false;
        }

        localDBCache.put(mediaId, new DatabaseInfo());
        return true;
    }

    /**
     * Creates an entry in the database for the given mediaId if it does not exists yet.
     * @param mediaId  the Uri of the media for the new entry
     * @return          true if the entry is created or already exists, false otherwise
     *                  (the entry is not present and could not be created)
     */
    private boolean createMediaEntryIfNotExisting(String mediaId) {

        int entryCount;

        if(null == mediaId) {
            Log.w(TAG, "createMediaEntryIfNotExisting - mediaId null !");
            return false;
        }
        String escapedUri = DatabaseUtils.sqlEscapeString(mediaId);

        if(!localDBCache.containsKey(mediaId)) {
            localDBCache.put(mediaId, new DatabaseInfo());
        }

        // Checks for media entry existence in database
        synchronized(databaseMutex) {
            Cursor queryInfos = currentDatabase.query(LV_MEDIA_META_INFO_TABLE,
                    new String []{KEY_MAIN_ID},
                    KEY_MEDIA_URI + "=?", new String[]{escapedUri},
                    null, null, null);

            entryCount = queryInfos.getCount();
            queryInfos.close();
        }

        if(entryCount > 1) {
            Log.e(TAG, "createMediaEntryIfNotExisting - database corruption");
            return false;
        }
        if(entryCount == 0) {
            return createNewMediaEntry(mediaId);
        }
        return true;
    }


    /**
     * Stores the path of the subtitle file associated to the given media.
     * <br>
     * <b>Note:&nbsp;</b>
     * The database entry is automatically created is the media was not previously known.
     *
     * @param mediaId  Uri of the media
     * @param subtitlePath  path of the subtitle file to associate
     * @return          true if the storage went OK, false otherwise (database not opened, data corruption)
     */
    public synchronized boolean setAssociateSubtitleFileForMedia(String mediaId, String subtitlePath) {

        if(!createMediaEntryIfNotExisting(mediaId)) {
            return false;
        }
        DatabaseInfo localCacheInfo = localDBCache.get(mediaId);
        localCacheInfo.associatedSubtitleFile = subtitlePath;
        localDBCache.put(mediaId, localCacheInfo);
        return true;
    }

    public synchronized DatabaseInfo getAssociatedDataForMedia(String mediaId) {
        return localDBCache.get(mediaId);
    }


    /**
     * Stores the language of the close caption file associated to the given media.
     * <br>
     * <b>Note:&nbsp;</b>
     * The database entry is automatically created is the media was not previously known.
     *
     * @param mediaId  id of the media
     * @param subTrackName name of the subtitle track to associate
     * @param subTrackType type of the subtitle track to associate
     * @return             true if the storage went OK, false otherwise (database not opened, data corruption)
     */
    public synchronized boolean setAssociatedSubtitleTrackForMedia(String mediaId, String subTrackName, int subTrackType) {

        if(!createMediaEntryIfNotExisting(mediaId)) {
            return false;
        }
        DatabaseInfo localCacheInfo = localDBCache.get(mediaId);
        localCacheInfo.associatedTimedTextTrack = subTrackName;
        localCacheInfo.associatedTimedTextType = subTrackType;
        localDBCache.put(mediaId, localCacheInfo);
        return true;
    }

    /**
     * Stores the audio track associated to the given media.
     * <br>
     * <b>Note:&nbsp;</b>
     * The database entry is automatically created is the media was not previously known.
     *
     * @param mediaId  id of the media
     * @param audioTrack name of the audio track to associate
     * @return             true if the storage went OK, false otherwise (database not opened, data corruption)
     */
    public synchronized boolean setAssociatedAudioTrackForMedia(String mediaId, String audioTrack) {
        if(!createMediaEntryIfNotExisting(mediaId)) {
            return false;
        }
        DatabaseInfo localCacheInfo = localDBCache.get(mediaId);
        localCacheInfo.associatedAudioTrack = audioTrack;
        localDBCache.put(mediaId, localCacheInfo);
        return true;
    }

    /**
     * Updates the database information regarding HLS Download for a given uri
     * @param mediaId URI for which Download info must be updated
     * @param DlStatus   Download status (0: NONE, 1: LOCAL, 2: DOWNLOADING, 3: PAUSED)
     * @param duration complete stream duration (in ms)
     * @param bitrate selected bitrate for HLS Download
     * @param downloaded already download duration (in ms)
     * @param localUri local URI after download
     * @return true if the update went well, false otherwise
     */
    public synchronized boolean setDlInfoForMediaId(String mediaId, int DlStatus, int duration, int bitrate, int downloaded, String localUri, String dlSubTracks, String dlAudioTracks) {

        if(! createMediaEntryIfNotExisting(mediaId)) {
            return false;
        }
        DatabaseInfo localCacheInfo = localDBCache.get(mediaId);
        localCacheInfo.DlStatus = DlStatus;
        if(DlStatus > 0) {
            localCacheInfo.mediaDurationInMs = duration;
            localCacheInfo.DLBitrate = bitrate;
            localCacheInfo.DLProgress = downloaded;
            localCacheInfo.DlLocalServerUrl = localUri;
            localCacheInfo.DLSelectedSubtitles = dlSubTracks;
            localCacheInfo.DLSelectedAudioTracks = dlAudioTracks;
        }
        localDBCache.put(mediaId, localCacheInfo);
        return true;
    }

    private boolean writeDBInfoInDatabase(String mediaId, DatabaseInfo dbInfo) {

        if(null == currentDatabase) {
            Log.w(TAG, "storeDBInfoInDatabase - database not opened !");
            return false;
        }
        if(null == mediaId) {
            Log.w(TAG, "setDbInfoForMedia - mediaId null !");
            return false;
        }
        synchronized(databaseMutex) {

            int nbRows = 0;
            if(! createMediaEntryIfNotExisting(mediaId)) {
                return false;
            }
            ContentValues dbValues = new ContentValues();

            dbValues.put(KEY_LAST_STOP_POS,  dbInfo.lastPlaybackPosInMs);
            dbValues.put(KEY_MEDIA_DURATION,  dbInfo.mediaDurationInMs);

            dbValues.put(KEY_SUBTITLE_FILE,  dbInfo.associatedSubtitleFile);
            dbValues.put(KEY_SUBTITLE_TRACK,  dbInfo.associatedTimedTextTrack);
            dbValues.put(KEY_SUBTITLE_TRACKTYPE, dbInfo.associatedTimedTextType);

            dbValues.put(KEY_AUDIO_TRACK, dbInfo.associatedAudioTrack);

            dbValues.put(KEY_DL_STATUS, dbInfo.DlStatus);
            if(dbInfo.DlStatus > 0) {
                dbValues.put(KEY_DL_BITRATE, dbInfo.DLBitrate);
                dbValues.put(KEY_DL_PROGRESS, dbInfo.DLProgress);
                if(null != dbInfo.DlLocalServerUrl) {
                    dbValues.put(KEY_DL_LOCALURL, DatabaseUtils.sqlEscapeString(dbInfo.DlLocalServerUrl));
                }
                else {
                    dbValues.putNull(KEY_DL_LOCALURL);
                }

                if(null != dbInfo.DLSelectedAudioTracks) {
                    dbValues.put(KEY_DL_AUDIO_TRACKS, DatabaseUtils.sqlEscapeString(dbInfo.DLSelectedAudioTracks));
                }
                else {
                    dbValues.putNull(KEY_DL_AUDIO_TRACKS);
                }

                if(null != dbInfo.DLSelectedSubtitles) {
                    dbValues.put(KEY_DL_SUB_TRACKS, DatabaseUtils.sqlEscapeString(dbInfo.DLSelectedSubtitles));
                }
                else {
                    dbValues.putNull(KEY_DL_SUB_TRACKS);
                }
            }
            else {
                dbValues.putNull(KEY_DL_LOCALURL);
                dbValues.putNull(KEY_DL_AUDIO_TRACKS);
                dbValues.putNull(KEY_DL_SUB_TRACKS);
            }
            String escapedMediaId = DatabaseUtils.sqlEscapeString(mediaId);
            try {
                nbRows = currentDatabase.update(LV_MEDIA_META_INFO_TABLE, dbValues,
                        KEY_MEDIA_URI + "=?", new String [] {escapedMediaId});
            }
            catch(SQLException sqle) {
                Log.e(TAG, "setAssociateSubtitleFileForMedia - Error when set media information");
                sqle.printStackTrace();
                return false;
            }
            if(1 == nbRows) {
                localDBCache.put(mediaId, dbInfo);
                return true;
            }
        }
        return false;
    }

    public synchronized void storeLocalCacheToPersistent() {
        for(String mediaId: localDBCache.keySet()) {
            DatabaseInfo dbInfo = localDBCache.get(mediaId);
            this.writeDBInfoInDatabase(mediaId, dbInfo);
        }
    }

    public synchronized boolean setDbInfoForMedia(String mediaId, DatabaseInfo dbInfo) {
        localDBCache.put(mediaId, dbInfo);
        return true;
    }


    /**
     * Stores the resume position for a given media.
     * <br>
     * <b>Note:</b>
     * <br>The database entry is automatically created is the media was not previously known.
     * @param mediaId  Uri of the media
     * @param position  last playback position for the media in ms
     * @return          true if the storage went OK, false otherwise
     */
    public synchronized boolean setResumePositionForMedia(String mediaId, int position, int duration) {
        if(! createMediaEntryIfNotExisting(mediaId)) {
            return false;
        }
        DatabaseInfo info = localDBCache.get(mediaId);
        info.lastPlaybackPosInMs = position;
        info.mediaDurationInMs = duration;
        localDBCache.put(mediaId, info);
        return true;
    }


    /**
     * Removes all stored informations for a given media (removes media entry from database).
     * @param mediaId  Uri of the media
     * @return          true if the removal went OK, false otherwise
     */
    public synchronized boolean removeDbEntryForUri(String mediaId) {
        if(null == currentDatabase) {
            Log.w(TAG, "removeDbEntryForUri - database not opened !");
            return false;
        }
        if(null == mediaId) {
            Log.w(TAG, "removeDbEntryForUri - mediaId null !");
            return false;
        }
        int id = 0;
        synchronized(databaseMutex) {
            String escapedMediaId = DatabaseUtils.sqlEscapeString(mediaId);

            Cursor queryInfos = currentDatabase.query(LV_MEDIA_META_INFO_TABLE,
                    new String []{KEY_MAIN_ID},
                    KEY_MEDIA_URI + "=?", new String[]{escapedMediaId},
                    null, null, null);

            if(queryInfos.getCount() > 1) {
                Log.e(TAG, "removeDbEntryForUri - database corruption");
                queryInfos.close();
                return false;
            }

            if(queryInfos.getCount() == 1) {
                queryInfos.moveToFirst();
                id = queryInfos.getInt(queryInfos.getColumnIndex(KEY_MAIN_ID));
            }
            queryInfos.close();

            currentDatabase.beginTransaction();

            int nbDelete = currentDatabase.delete(LV_MEDIA_META_INFO_TABLE, KEY_MAIN_ID + "=" + id, null);
            if(nbDelete > 0) {
                currentDatabase.setTransactionSuccessful();
                currentDatabase.endTransaction();
                localDBCache.remove(mediaId);
                return true;
            }
            else {
                currentDatabase.endTransaction();
                return false;
            }
        }
    }
}
