public class DatabaseHelper extends SQLiteOpenHelper {

    private static DatabaseHelper helperInstance;

    private SQLiteDatabase myDataBase;
    private final Context myContext;
    private static final String DATABASE_NAME = "data.sqlite";
    public static final int DATABASE_VERSION = 1;

    final private static int COLUMN_ID_COMPOSER_ID = 0;
    final private static int COLUMN_ID_COMPOSER_LASTNAME = 1;
    final private static int COLUMN_ID_COMPOSER_FIRSTNAME = 2;
    final private static int COLUMN_ID_COMPOSER_BIRTHDATES = 3;
    final private static int COLUMN_ID_COMPOSER_WIKIPEDIA_URL = 4;
    final private static int COLUMN_ID_COMPOSER_IMSLP_URL = 5;
    final private static int COLUMN_ID_COMPOSER_IMG_BIG = 6;
    final private static int COLUMN_ID_COMPOSER_IMG_SMALL = 7;
    final private static int COLUMN_ID_COMPOSER_IS_POPULAR = 8;

    /**
     * Constructor
     */
    private DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.myContext = context;
    }

    public static DatabaseHelper getInstance(Context context) {

        // Use the application context, which will ensure that you
        // don't accidentally leak an Activity's context.
        // See this article for more information: http://bit.ly/6LRzfx
        if (helperInstance == null) {
            helperInstance = new DatabaseHelper(context.getApplicationContext());
        }
        return helperInstance;
    }

    // Create a empty database on the system
    public void createDatabase() throws IOException {

        boolean dbExist = checkDataBase();

        if (dbExist) {
            Logger.info("Database already exists.");
            // By calling this method here onUpgrade will be called on a
            // writeable database, but only if the version number has been
            // bumped
            // onUpgrade(myDataBase, DATABASE_VERSION_old, DATABASE_VERSION);
        } else {
            Logger.info("Database does not exist, copying from assets.");
            this.getReadableDatabase();
            try {
                this.close();
                copyDatabase();
            } catch (IOException e) {
                throw new Error("Error copying database: " + e.getMessage());
            }
        }

    }

    // Check database already exist or not
    private boolean checkDataBase() {
        boolean checkDB = false;
        try {
            String myPath = myContext.getDatabasePath(DATABASE_NAME).getPath();
            File databaseFile = new File(myPath);
            checkDB = databaseFile.exists();
        } catch (SQLiteException e) {
            e.printStackTrace();
        }
        return checkDB;
    }

    // Copies your database from your local assets-folder to the just created
    // empty database in the system folder
    private void copyDatabase() throws IOException {

        String outFileName = myContext.getDatabasePath(DATABASE_NAME).getPath();

        OutputStream myOutput = new FileOutputStream(outFileName);
        InputStream myInput = myContext.getAssets().open(DATABASE_NAME);

        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }
        myInput.close();
        myOutput.flush();
        myOutput.close();
    }

    // delete database
    public void deleteDatabase() {
        File databaseFile = new File(myContext.getDatabasePath(DATABASE_NAME).getPath());
        if (databaseFile.exists()) {
            databaseFile.delete();
            Logger.info("Deleted database file.");
        }
    }

    // Open database
    public void openDatabase() throws SQLException {
        String myPath = myContext.getDatabasePath(DATABASE_NAME).getPath();
        myDataBase = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READWRITE);
    }

    public synchronized void closeDatabase() throws SQLException {
        super.close();
        if (myDataBase != null) {
            myDataBase.close();
        }
    }

    public void onCreate(SQLiteDatabase db) {
        // Empty implementation.
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            Log.w("Database", "Database version higher than old.");
            deleteDatabase();
        }
    }

    public ArrayList<Composer> getPopularComposers() {
        return getComposerList("SELECT * FROM AllComposers WHERE is_popular = 1");
    }

    /**
     * @param downloadedFile
     * @deprecated
     */
    public void addDownloadedFile(DownloadedFile downloadedFile) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put("composer_id", 0);
        values.put("path", downloadedFile.getPath());
        values.put("piece_name", downloadedFile.getPieceName());
        values.put("downloaded_at", downloadedFile.getTimestamp().toString());

        db.insert("Downloaded", null, values);
        db.close();

    }

    public ArrayList<DownloadedFile> getDownloadedFiles() {

        ArrayList<DownloadedFile> resultList = new ArrayList<DownloadedFile>();
        SQLiteDatabase db = this.getReadableDatabase();

        if (db == null) {
            throw new NullPointerException();
        }

        Cursor cursor = db.rawQuery("SELECT * FROM Downloaded", null);

        if (cursor == null) {
            throw new NullPointerException();
        }

        cursor.moveToFirst();

        while (!(cursor.isAfterLast())) {
            final int id = cursor.getInt(0);
            final int composerId = cursor.getInt(1);
            final String path = cursor.getString(2);
            final String pieceName = cursor.getString(3);
            final String rawTimestamp = cursor.getString(4);
            final Timestamp timestamp = Timestamp.valueOf(rawTimestamp);
            DownloadedFile file = new DownloadedFile(id, composerId, pieceName, path, "-",
                    timestamp);
            resultList.add(file);
            cursor.moveToNext();
        }

        db.close();
        cursor.close();
        return resultList;
    }

    public ArrayList<Composer> getAllComposers() {
        return getComposerList("SELECT * FROM AllComposers");
    }

    public ArrayList<Composer> getFavoriteComposers() {
        String query = "SELECT * FROM AllComposers, FavoriteComposers WHERE composer_id = AllComposers.id";
        return getComposerList(query);
    }

    public ArrayList<Composer> getRecentComposers() {
        return getComposerList("SELECT * FROM AllComposers, Recent WHERE composer_id = AllComposers.id");
    }


    private ArrayList<Composer> getComposerList(String query) {
        ArrayList<Composer> resultList = new ArrayList<Composer>();
        SQLiteDatabase db = this.getReadableDatabase();

        if (db == null) {
            throw new NullPointerException();
        }

        Cursor cursor = db.rawQuery(query, null);

        if (cursor == null) {
            db.close();
            throw new NullPointerException("Database cursor is null!");
        }

        cursor.moveToFirst();

        while (!(cursor.isAfterLast())) {
            resultList.add(cursorToComposer(cursor));
            cursor.moveToNext();
        }

        db.close();
        cursor.close();
        return resultList;
    }


    public Composer getComposerFromUrl(String url) {

        SQLiteDatabase db = this.getReadableDatabase();

        if (db == null) {
            throw new NullPointerException();
        }

        Cursor cursor = db.rawQuery("SELECT * FROM AllComposers WHERE imslp_url = \"" + url + "\"", null);

        if (cursor == null) {
            throw new NullPointerException();
        }

        cursor.moveToFirst();

        while (!(cursor.isAfterLast())) {
            Composer composer = cursorToComposer(cursor);
            db.close();
            cursor.close();
            boolean isFavorite = isFavoriteComposer(composer);
            composer.setIsFavorite(isFavorite);
            return composer;
        }

        db.close();
        cursor.close();
        return null;
    }

    private final static String IMSLP_BASE_URL = "http://imslp.org";

    private Composer cursorToComposer(Cursor cursor) {
        final int id = cursor.getInt(COLUMN_ID_COMPOSER_ID);
        final String lastName = cursor.getString(COLUMN_ID_COMPOSER_LASTNAME);
        final String firstName = cursor.getString(COLUMN_ID_COMPOSER_FIRSTNAME);
        final String birthDates = cursor.getString(COLUMN_ID_COMPOSER_BIRTHDATES);
        final String rawImageBigUrl = cursor.getString(COLUMN_ID_COMPOSER_IMG_BIG);
        final String imageBigUrl = rawImageBigUrl == null ? null : IMSLP_BASE_URL + rawImageBigUrl;
        final String rawImageSmallUrl = cursor.getString(COLUMN_ID_COMPOSER_IMG_SMALL);
        final String imageSmallUrl = rawImageSmallUrl == null ? null : IMSLP_BASE_URL + rawImageSmallUrl;
        final String imslpUrl = cursor.getString(COLUMN_ID_COMPOSER_IMSLP_URL);
        final String wikipediaUrl = cursor
                .getString(COLUMN_ID_COMPOSER_WIKIPEDIA_URL);
        return new Composer(id, firstName, lastName, birthDates, imslpUrl, wikipediaUrl,
                imageBigUrl, imageSmallUrl);
    }


    public void removeFavoriteComposer(Composer composer) {
        SQLiteDatabase db = this.getWritableDatabase();

        db.delete("FavoriteComposers", "composer_id = " + composer.getId(), null);
        db.close();

    }

    public void addFavoriteComposer(Composer composer) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put("composer_id", composer.getId());

        db.insert("FavoriteComposers", null, values);
        db.close();
    }

    public boolean isFavoriteComposer(Composer composer) {
        SQLiteDatabase db = this.getReadableDatabase();

        if (db == null) {
            throw new NullPointerException();
        }

        Cursor cursor = db.rawQuery("SELECT * FROM FavoriteComposers WHERE composer_id = " + composer.getId(), null);

        if (cursor == null) {
            db.close();
            return false;
        }

        boolean isFavorite = cursor.getCount() > 0;
        db.close();
        cursor.close();

        return isFavorite;

    }

    public void addToRecentComposers(Composer composer) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put("composer_id", composer.getId());

        db.insert("Recent", null, values);
        db.close();
    }


    @Deprecated
    public void removeDownloadedFilesByUrl(String sourceUrl) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("Downloaded", "source_url = " + sourceUrl, null);
        db.close();

    }

}