/*
 * Copyright 2016-2023 Soren Stoutner <soren@stoutner.com>.
 *
 * This file is part of Privacy Browser Android <https://www.stoutner.com/privacy-browser-android>.
 *
 * Privacy Browser Android is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * Privacy Browser Android is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Privacy Browser Android.  If not, see <http://www.gnu.org/licenses/>.
 */

package com.stoutner.privacybrowser.helpers

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.DatabaseUtils
import android.database.MatrixCursor
import android.database.MergeCursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper

import com.stoutner.privacybrowser.activities.HOME_FOLDER_ID

import java.util.Date

// Define the class constants.
private const val SCHEMA_VERSION = 2

// Define the public database constants.
const val BOOKMARKS_DATABASE = "bookmarks.db"
const val BOOKMARKS_TABLE = "bookmarks"

// Define the public schema constants.
const val BOOKMARK_NAME = "bookmarkname"
const val BOOKMARK_URL = "bookmarkurl"
const val DISPLAY_ORDER = "displayorder"
const val FAVORITE_ICON = "favoriteicon"
const val FOLDER_ID = "folder_id"
const val IS_FOLDER = "isfolder"
const val PARENT_FOLDER_ID = "parent_folder_id"

// Define the public table creation constant.
const val CREATE_BOOKMARKS_TABLE = "CREATE TABLE $BOOKMARKS_TABLE (" +
        "$ID INTEGER PRIMARY KEY, " +
        "$BOOKMARK_NAME TEXT, " +
        "$BOOKMARK_URL TEXT, " +
        "$PARENT_FOLDER_ID INTEGER, " +
        "$DISPLAY_ORDER INTEGER, " +
        "$IS_FOLDER BOOLEAN, " +
        "$FOLDER_ID INTEGER, " +
        "$FAVORITE_ICON BLOB)"

class BookmarksDatabaseHelper(context: Context) : SQLiteOpenHelper(context, BOOKMARKS_DATABASE, null, SCHEMA_VERSION) {
    override fun onCreate(bookmarksDatabase: SQLiteDatabase) {
        // Create the bookmarks table.
        bookmarksDatabase.execSQL(CREATE_BOOKMARKS_TABLE)
    }

    override fun onUpgrade(bookmarksDatabase: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Upgrade from schema version 1, first used in Privacy Browser 1.8, to schema version 2, first used in Privacy Browser 3.15.
        if (oldVersion < 2) {
            // Add the folder ID column.
            bookmarksDatabase.execSQL("ALTER TABLE $BOOKMARKS_TABLE ADD COLUMN $FOLDER_ID INTEGER")

            // Get a cursor with all the folders.
            val foldersCursor = bookmarksDatabase.rawQuery("SELECT $ID FROM $BOOKMARKS_TABLE WHERE $IS_FOLDER = 1", null)

            // Get the folders cursor ID column index.
            val foldersCursorIdColumnIndex = foldersCursor.getColumnIndexOrThrow(ID)

            // Add a folder ID to each folder.
            while(foldersCursor.moveToNext()) {
                // Get the current folder database ID.
                val databaseId = foldersCursor.getInt(foldersCursorIdColumnIndex)

                // Generate a folder ID.
                val folderId = Date().time

                // Create a folder content values.
                val folderContentValues = ContentValues()

                // Store the new folder ID in the content values.
                folderContentValues.put(FOLDER_ID, folderId)

                // Update the folder with the new folder ID.
                bookmarksDatabase.update(BOOKMARKS_TABLE, folderContentValues, "$ID = $databaseId", null)

                // Wait 2 milliseconds to ensure that the next folder ID is unique.
                Thread.sleep(2)
            }

            // Close the folders cursor.
            foldersCursor.close()


            // Add the parent folder ID column.
            bookmarksDatabase.execSQL("ALTER TABLE $BOOKMARKS_TABLE ADD COLUMN $PARENT_FOLDER_ID INTEGER")

            // Get a cursor with all the bookmarks.
            val bookmarksCursor = bookmarksDatabase.rawQuery("SELECT $ID, parentfolder FROM $BOOKMARKS_TABLE", null)

            // Get the bookmarks cursor ID column index.
            val bookmarksCursorIdColumnIndex = bookmarksCursor.getColumnIndexOrThrow(ID)
            val bookmarksCursorParentFolderColumnIndex = bookmarksCursor.getColumnIndexOrThrow("parentfolder")

            // Populate the parent folder ID for each bookmark.
            while(bookmarksCursor.moveToNext()) {
                // Get the information from the cursor.
                val databaseId = bookmarksCursor.getInt(bookmarksCursorIdColumnIndex)
                val oldParentFolderString = bookmarksCursor.getString(bookmarksCursorParentFolderColumnIndex)

                // Initialize the new parent folder ID.
                var newParentFolderId = HOME_FOLDER_ID

                // Get the parent folder ID if the bookmark is not in the home folder.
                if (oldParentFolderString.isNotEmpty()) {
                    // SQL escape the old parent folder string.
                    val sqlEscapedFolderName = DatabaseUtils.sqlEscapeString(oldParentFolderString)

                    // Get the parent folder cursor.
                    val parentFolderCursor = bookmarksDatabase.rawQuery("SELECT $FOLDER_ID FROM $BOOKMARKS_TABLE WHERE $BOOKMARK_NAME = $sqlEscapedFolderName AND $IS_FOLDER = 1", null)

                    // Get the new parent folder ID if it exists.
                    if (parentFolderCursor.count > 0) {
                        // Move to the first entry.
                        parentFolderCursor.moveToFirst()

                        // Get the new parent folder ID.
                        newParentFolderId = parentFolderCursor.getLong(parentFolderCursor.getColumnIndexOrThrow(FOLDER_ID))
                    }

                    // Close the parent folder cursor.
                    parentFolderCursor.close()
                }

                // Create a bookmark content values.
                val bookmarkContentValues = ContentValues()

                // Store the new parent folder ID in the content values.
                bookmarkContentValues.put(PARENT_FOLDER_ID, newParentFolderId)

                // Update the folder with the new folder ID.
                bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, "$ID = $databaseId", null)
            }

            // Close the bookmarks cursor.
            bookmarksCursor.close()

            // This upgrade removed the old `parentfolder` string column.
            // SQLite amazingly only added a command to drop a column in version 3.35.0.  <https://www.sqlite.org/changes.html>
            // It will be a while before that is supported in Android.  <https://developer.android.com/reference/android/database/sqlite/package-summary>
            // Although a new table could be created and all the data copied to it, I think I will just leave the old parent folder column.  It will be wiped out the next time an import is run.
        }
    }

    // Get a cursor for all bookmarks and folders.
    val allBookmarks: Cursor
        get() {
            // Get a readable database handle.
            val bookmarksDatabase = this.readableDatabase

            // Return a cursor with the entire contents of the bookmarks table.  The cursor cannot be closed because it is used in the parent activity.
            return bookmarksDatabase.rawQuery("SELECT * FROM $BOOKMARKS_TABLE", null)
        }

    // Get a cursor for all bookmarks and folders ordered by display order.
    val allBookmarksByDisplayOrder: Cursor
        get() {
            // Get a readable database handle.
            val bookmarksDatabase = this.readableDatabase

            // Return a cursor with the entire contents of the bookmarks table ordered by the display order.  The cursor cannot be closed because it is used in the parent activity.
            return bookmarksDatabase.rawQuery("SELECT * FROM $BOOKMARKS_TABLE ORDER BY $DISPLAY_ORDER ASC", null)
        }

    // Create a bookmark.
    fun createBookmark(bookmarkName: String, bookmarkUrl: String, parentFolderId: Long, displayOrder: Int, favoriteIcon: ByteArray) {
        // Store the bookmark data in a content values.
        val bookmarkContentValues = ContentValues()

        // The ID is created automatically.
        bookmarkContentValues.put(BOOKMARK_NAME, bookmarkName)
        bookmarkContentValues.put(BOOKMARK_URL, bookmarkUrl)
        bookmarkContentValues.put(PARENT_FOLDER_ID, parentFolderId)
        bookmarkContentValues.put(DISPLAY_ORDER, displayOrder)
        bookmarkContentValues.put(IS_FOLDER, false)
        bookmarkContentValues.put(FAVORITE_ICON, favoriteIcon)

        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Insert a new row.
        bookmarksDatabase.insert(BOOKMARKS_TABLE, null, bookmarkContentValues)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Create a bookmark from content values.
    fun createBookmark(contentValues: ContentValues) {
        // Get a writable database.
        val bookmarksDatabase = this.writableDatabase

        // Insert a new row.
        bookmarksDatabase.insert(BOOKMARKS_TABLE, null, contentValues)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Create a folder.
    fun createFolder(folderName: String, parentFolderId: Long, favoriteIcon: ByteArray) {
        // Create a bookmark folder content values.
        val bookmarkFolderContentValues = ContentValues()

        // The ID is created automatically.  Folders are always created at the top of the list.
        bookmarkFolderContentValues.put(BOOKMARK_NAME, folderName)
        bookmarkFolderContentValues.put(PARENT_FOLDER_ID, parentFolderId)
        bookmarkFolderContentValues.put(DISPLAY_ORDER, 0)
        bookmarkFolderContentValues.put(IS_FOLDER, true)
        bookmarkFolderContentValues.put(FOLDER_ID, generateFolderId())
        bookmarkFolderContentValues.put(FAVORITE_ICON, favoriteIcon)

        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Insert the new folder.
        bookmarksDatabase.insert(BOOKMARKS_TABLE, null, bookmarkFolderContentValues)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Delete one bookmark.
    fun deleteBookmark(databaseId: Int) {
        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Deletes the row with the given database ID.
        bookmarksDatabase.delete(BOOKMARKS_TABLE, "$ID = $databaseId", null)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Get a cursor for the bookmark with the specified database ID.
    fun getBookmark(databaseId: Int): Cursor {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Return the cursor for the database ID.  The cursor can't be closed because it is used in the parent activity.
        return bookmarksDatabase.rawQuery("SELECT * FROM $BOOKMARKS_TABLE WHERE $ID = $databaseId", null)
    }

    // Get a cursor for all bookmarks and folders by display order except those with the specified IDs.
    fun getAllBookmarksByDisplayOrderExcept(exceptIdLongArray: LongArray): Cursor {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Prepare a string builder to contain the comma-separated list of IDs not to get.
        val idsNotToGetStringBuilder = StringBuilder()

        // Extract the array of IDs not to get to the string builder.
        for (databaseIdLong in exceptIdLongArray) {
            // Check to see if there is already a number in the builder.
            if (idsNotToGetStringBuilder.isNotEmpty()) {
                // This is not the first number, so place a `,` before the new number.
                idsNotToGetStringBuilder.append(",")
            }

            // Add the new number to the builder.
            idsNotToGetStringBuilder.append(databaseIdLong)
        }

        // Return a cursor with all the bookmarks except those specified ordered by display order.  The cursor cannot be closed because it is used in the parent activity.
        return bookmarksDatabase.rawQuery("SELECT * FROM $BOOKMARKS_TABLE WHERE $ID NOT IN ($idsNotToGetStringBuilder) ORDER BY $DISPLAY_ORDER ASC", null)
    }

    // Get a cursor for all bookmarks and folders except those with the specified IDs.
    fun getAllBookmarksExcept(exceptIdLongArray: LongArray): Cursor {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Prepare a string builder to contain the comma-separated list of IDs not to get.
        val idsNotToGetStringBuilder = StringBuilder()

        // Extract the array of IDs not to get to the string builder.
        for (databaseIdLong in exceptIdLongArray) {
            // Check to see if there is already a number in the builder.
            if (idsNotToGetStringBuilder.isNotEmpty()) {
                // This is not the first number, so place a `,` before the new number.
                idsNotToGetStringBuilder.append(",")
            }

            // Add the new number to the builder.
            idsNotToGetStringBuilder.append(databaseIdLong)
        }

        // Return a cursor with all the bookmarks except those specified.  The cursor cannot be closed because it is used in the parent activity.
        return bookmarksDatabase.rawQuery("SELECT * FROM $BOOKMARKS_TABLE WHERE $ID NOT IN ($idsNotToGetStringBuilder)", null)
    }

    // Get a cursor with just database ID of bookmarks and folders in the specified folder.  This is useful for deleting folders with bookmarks that have favorite icons too large to fit in a cursor.
    fun getBookmarkIds(parentFolderId: Long): Cursor {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Return a cursor with all the database IDs.  The cursor cannot be closed because it is used in the parent activity.
        return bookmarksDatabase.rawQuery("SELECT $ID FROM $BOOKMARKS_TABLE WHERE $PARENT_FOLDER_ID = $parentFolderId", null)
    }

    // Get a cursor for bookmarks and folders in the specified folder.
    fun getBookmarks(parentFolderId: Long): Cursor {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Return a cursor with all the bookmarks in a specified folder.  The cursor cannot be closed because it is used in the parent activity.
        return bookmarksDatabase.rawQuery("SELECT * FROM $BOOKMARKS_TABLE WHERE $PARENT_FOLDER_ID = $parentFolderId", null)
    }

    // Get a cursor for bookmarks and folders in the specified folder ordered by display order.
    fun getBookmarksByDisplayOrder(parentFolderId: Long): Cursor {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Return a cursor with all the bookmarks in the specified folder ordered by display order.  The cursor cannot be closed because it is used in the parent activity.
        return bookmarksDatabase.rawQuery("SELECT * FROM $BOOKMARKS_TABLE WHERE $PARENT_FOLDER_ID = $parentFolderId ORDER BY $DISPLAY_ORDER ASC", null)
    }

    // Get a cursor for bookmarks and folders in the specified folder by display order except those with the specified IDs.
    fun getBookmarksByDisplayOrderExcept(exceptIdLongArray: LongArray, parentFolderId: Long): Cursor {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Prepare a string builder to contain the comma-separated list of IDs not to get.
        val idsNotToGetStringBuilder = StringBuilder()

        // Extract the array of IDs not to get to the string builder.
        for (databaseIdLong in exceptIdLongArray) {
            // Check to see if there is already a number in the builder.
            if (idsNotToGetStringBuilder.isNotEmpty()) {
                // This is not the first number, so place a `,` before the new number.
                idsNotToGetStringBuilder.append(",")
            }

            // Add the new number to the builder.
            idsNotToGetStringBuilder.append(databaseIdLong)
        }

        // Return a cursor with all the bookmarks in the specified folder except for those database IDs specified ordered by display order.
        // The cursor cannot be closed because it will be used in the parent activity.
        return bookmarksDatabase.rawQuery("SELECT * FROM $BOOKMARKS_TABLE WHERE $PARENT_FOLDER_ID = $parentFolderId AND $ID NOT IN ($idsNotToGetStringBuilder) ORDER BY $DISPLAY_ORDER ASC", null)
    }

    // Get a cursor for bookmarks and folders in the specified folder except those with the specified IDs.
    fun getBookmarksExcept(exceptIdLongArray: LongArray, parentFolderId: Long): Cursor {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Prepare a string builder to contain the comma-separated list of IDs not to get.
        val idsNotToGetStringBuilder = StringBuilder()

        // Extract the array of IDs not to get to the string builder.
        for (databaseIdLong in exceptIdLongArray) {
            // Check to see if there is already a number in the builder.
            if (idsNotToGetStringBuilder.isNotEmpty()) {
                // This is not the first number, so place a `,` before the new number.
                idsNotToGetStringBuilder.append(",")
            }

            // Add the new number to the builder.
            idsNotToGetStringBuilder.append(databaseIdLong)
        }

        // Return a cursor with all the bookmarks in the specified folder except for those database IDs specified.  The cursor cannot be closed because it is used in the parent activity.
        return bookmarksDatabase.rawQuery("SELECT * FROM $BOOKMARKS_TABLE WHERE $PARENT_FOLDER_ID = $parentFolderId AND $ID NOT IN ($idsNotToGetStringBuilder)", null)
    }

    fun getFolderBookmarks(parentFolderId: Long): Cursor {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Return a cursor with all the bookmarks in the folder.  The cursor cannot be closed because it is used in the parent activity.
        return bookmarksDatabase.rawQuery("SELECT * FROM $BOOKMARKS_TABLE WHERE $PARENT_FOLDER_ID = $parentFolderId AND $IS_FOLDER = 0 ORDER BY $DISPLAY_ORDER ASC", null)
    }

    // Get the database ID for the specified folder name.
    fun getFolderDatabaseId(folderId: Long): Int {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Initialize the database ID.
        var databaseId = 0

        // Get the cursor for the folder with the specified name.
        val folderCursor = bookmarksDatabase.rawQuery("SELECT $ID FROM $BOOKMARKS_TABLE WHERE $FOLDER_ID = $folderId", null)

        // Get the database ID if it exists.
        if (folderCursor.count > 0) {
            // Move to the first record.
            folderCursor.moveToFirst()

            // Get the database ID.
            databaseId = folderCursor.getInt(folderCursor.getColumnIndexOrThrow(ID))
        }

        // Close the cursor and the database handle.
        folderCursor.close()
        bookmarksDatabase.close()

        // Return the database ID.
        return databaseId
    }

    // Get the folder ID for the specified folder database ID.
    fun getFolderId(folderDatabaseId: Int): Long {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Get the cursor for the folder with the specified database ID.
        val folderCursor = bookmarksDatabase.rawQuery("SELECT $FOLDER_ID FROM $BOOKMARKS_TABLE WHERE $ID = $folderDatabaseId", null)

        // Move to the first record.
        folderCursor.moveToFirst()

        // Get the folder ID.
        val folderId = folderCursor.getLong(folderCursor.getColumnIndexOrThrow(FOLDER_ID))

        // Close the cursor and the database handle.
        folderCursor.close()
        bookmarksDatabase.close()

        // Return the folder ID.
        return folderId
    }

    // Get the folder name for the specified folder ID.
    fun getFolderName(folderId: Long): String {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Initialize the folder name.
        var folderName = ""

        // Get the cursor for the folder with the specified folder ID.
        val folderCursor = bookmarksDatabase.rawQuery("SELECT $BOOKMARK_NAME FROM $BOOKMARKS_TABLE WHERE $FOLDER_ID = $folderId", null)

        // Get the folder name if it exists.
        if (folderCursor.count > 0) {
            // Move to the first record.
            folderCursor.moveToFirst()

            // Get the folder name.
            folderName = folderCursor.getString(folderCursor.getColumnIndexOrThrow(BOOKMARK_NAME))
        }

        // Close the cursor and the database handle.
        folderCursor.close()
        bookmarksDatabase.close()

        // Return the folder name.
        return folderName
    }

    // Get a cursor of all the folders except those specified.
    fun getFoldersExcept(exceptFolderIdLongList: List<Long>): Cursor {
        // Prepare a string builder to contain the comma-separated list of IDs not to get.
        val folderIdsNotToGetStringBuilder = StringBuilder()

        // Extract the array of IDs not to get to the string builder.
        for (folderId in exceptFolderIdLongList) {
            // Check to see if there is already a number in the builder.
            if (folderIdsNotToGetStringBuilder.isNotEmpty()) {
                // This is not the first number, so place a `,` before the new number.
                folderIdsNotToGetStringBuilder.append(",")
            }

            // Add the new number to the builder.
            folderIdsNotToGetStringBuilder.append(folderId)
        }

        // Get an array list with all of the requested subfolders.
        val subfoldersCursorArrayList = getSubfoldersExcept(HOME_FOLDER_ID, folderIdsNotToGetStringBuilder.toString())

        // Return a cursor.
        return if (subfoldersCursorArrayList.isEmpty()) {  // There are no folders.  Return an empty cursor.
            // A matrix cursor requires the definition of at least one column.
            MatrixCursor(arrayOf(ID))
        } else {  // There is at least one folder.
            // Use a merge cursor to return the folders.
            MergeCursor(subfoldersCursorArrayList.toTypedArray())
        }
    }

    // Determine if any folders exist beside the specified database IDs.  The array of database IDs can include both bookmarks and folders.
    fun hasFoldersExceptDatabaseId(exceptDatabaseIdLongArray: LongArray): Boolean {
        // Create a folder ID long list.
        val folderIdLongList = mutableListOf<Long>()

        // Populate the list.
        for (databaseId in exceptDatabaseIdLongArray) {
            // Convert the database ID to an Int.
            val databaseIdInt = databaseId.toInt()

            // Only process database IDs that are folders.
            if (isFolder(databaseIdInt)) {
                // Add the folder ID to the list.
                folderIdLongList.add(getFolderId(databaseIdInt))
            }
        }

        // Get a lit of all the folders except those specified and their subfolders.
        val foldersCursor = getFoldersExcept(folderIdLongList)

        // Determine if any other folders exists.
        val hasFolder = (foldersCursor.count > 0)

        // Close the cursor.
        foldersCursor.close()

        // Return the folder status.
        return hasFolder
    }

    // Get the name of the parent folder
    fun getParentFolderId(currentFolderId: Long): Long {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Get a cursor for the current folder.
        val bookmarkCursor = bookmarksDatabase.rawQuery("SELECT $PARENT_FOLDER_ID FROM $BOOKMARKS_TABLE WHERE $FOLDER_ID = $currentFolderId", null)

        // Move to the first record.
        bookmarkCursor.moveToFirst()

        // Store the parent folder ID.
        val parentFolderId = bookmarkCursor.getLong(bookmarkCursor.getColumnIndexOrThrow(PARENT_FOLDER_ID))

        // Close the cursor and the database.
        bookmarkCursor.close()
        bookmarksDatabase.close()

        // Return the parent folder string ID.
        return parentFolderId
    }

    // Get the name of the parent folder.
    fun getParentFolderId(databaseId: Int): Long {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Get a cursor for the specified database ID.
        val bookmarkCursor = bookmarksDatabase.rawQuery("SELECT $PARENT_FOLDER_ID FROM $BOOKMARKS_TABLE WHERE $ID = $databaseId", null)

        // Move to the first record.
        bookmarkCursor.moveToFirst()

        // Store the name of the parent folder.
        val parentFolderId = bookmarkCursor.getLong(bookmarkCursor.getColumnIndexOrThrow(PARENT_FOLDER_ID))

        // Close the cursor and the database.
        bookmarkCursor.close()
        bookmarksDatabase.close()

        // Return the parent folder string.
        return parentFolderId
    }

    // Get a cursor with the names and folder IDs of all the subfolders of the specified folder.
    fun getSubfolderNamesAndFolderIds(currentFolderId: Long): Cursor {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Return the cursor with the subfolders.  The cursor can't be closed because it is used in the parent activity.
        return bookmarksDatabase.rawQuery("SELECT $BOOKMARK_NAME, $FOLDER_ID FROM $BOOKMARKS_TABLE WHERE $PARENT_FOLDER_ID = $currentFolderId AND $IS_FOLDER = 1", null)
    }

    fun getSubfolderSpacer(folderId: Long): String {
        // Create a spacer string
        var spacerString = ""

        // Get the parent folder ID.
        val parentFolderId = getParentFolderId(folderId)

        // Check to see if the parent folder is not in the home folder.
        if (parentFolderId != HOME_FOLDER_ID) {
            // Add two spaces to the spacer string.
            spacerString += "  "

            // Check the parent folder recursively.
            spacerString += getSubfolderSpacer(parentFolderId)
        }

        // Return the spacer string.
        return spacerString
    }

    private fun getSubfoldersExcept(folderId: Long, exceptFolderIdString: String): ArrayList<Cursor> {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Create a cursor array list.
        val cursorArrayList = ArrayList<Cursor>()

        // Create a matrix cursor column names.
        val matrixCursorColumnNames = arrayOf(ID, BOOKMARK_NAME, FAVORITE_ICON, PARENT_FOLDER_ID, FOLDER_ID)

        // Get a cursor with the subfolders.
        val subfolderCursor = bookmarksDatabase.rawQuery(
            "SELECT * FROM $BOOKMARKS_TABLE WHERE $IS_FOLDER = 1 AND $PARENT_FOLDER_ID = $folderId AND $FOLDER_ID NOT IN ($exceptFolderIdString) ORDER BY $DISPLAY_ORDER ASC", null)

        // Get the subfolder cursor column indexes.
        val idColumnIndex = subfolderCursor.getColumnIndexOrThrow(ID)
        val nameColumnIndex = subfolderCursor.getColumnIndexOrThrow(BOOKMARK_NAME)
        val favoriteIconColumnIndex = subfolderCursor.getColumnIndexOrThrow(FAVORITE_ICON)
        val parentFolderIdColumnIndex = subfolderCursor.getColumnIndexOrThrow(PARENT_FOLDER_ID)
        val folderIdColumnIndex = subfolderCursor.getColumnIndexOrThrow(FOLDER_ID)

        while (subfolderCursor.moveToNext()) {
            // Create an array list.
            val matrixCursor = MatrixCursor(matrixCursorColumnNames)

            // Add the subfolder to the matrix cursor.
            matrixCursor.addRow(arrayOf<Any>(subfolderCursor.getInt(idColumnIndex), subfolderCursor.getString(nameColumnIndex), subfolderCursor.getBlob(favoriteIconColumnIndex),
                subfolderCursor.getLong(parentFolderIdColumnIndex), subfolderCursor.getLong(folderIdColumnIndex)))

            // Add the matrix cursor to the array list.
            cursorArrayList.add(matrixCursor)

            // Get all the sub-subfolders recursively
            cursorArrayList.addAll(getSubfoldersExcept(subfolderCursor.getLong(folderIdColumnIndex), exceptFolderIdString))
        }

        // Close the subfolder cursor.
        subfolderCursor.close()

        // Return the matrix cursor.
        return cursorArrayList
    }

    // Check if a database ID is a folder.
    fun isFolder(databaseId: Int): Boolean {
        // Get a readable database handle.
        val bookmarksDatabase = this.readableDatabase

        // Get a cursor with the is folder field for the specified database ID.
        val folderCursor = bookmarksDatabase.rawQuery("SELECT $IS_FOLDER FROM $BOOKMARKS_TABLE WHERE $ID = $databaseId", null)

        // Move to the first record.
        folderCursor.moveToFirst()

        // Ascertain if this database ID is a folder.
        val isFolder = (folderCursor.getInt(folderCursor.getColumnIndexOrThrow(IS_FOLDER)) == 1)

        // Close the cursor and the database handle.
        folderCursor.close()
        bookmarksDatabase.close()

        // Return the folder status.
        return isFolder
    }

    // Move one bookmark or folder to a new folder.
    fun moveToFolder(databaseId: Int, newFolderId: Long) {
        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Get a cursor for all the bookmarks in the new folder ordered by display order.
        val newFolderCursor = bookmarksDatabase.rawQuery("SELECT $DISPLAY_ORDER FROM $BOOKMARKS_TABLE WHERE $PARENT_FOLDER_ID = $newFolderId ORDER BY $DISPLAY_ORDER ASC", null)

        // Set the new display order.
        val displayOrder: Int = if (newFolderCursor.count > 0) {  // There are already bookmarks in the folder.
            // Move to the last bookmark.
            newFolderCursor.moveToLast()

            // Set the display order to be one greater that the last bookmark.
            newFolderCursor.getInt(newFolderCursor.getColumnIndexOrThrow(DISPLAY_ORDER)) + 1
        } else {  // There are no bookmarks in the new folder.
            // Set the display order to be `0`.
            0
        }

        // Close the cursor.
        newFolderCursor.close()

        // Create a content values.
        val bookmarkContentValues = ContentValues()

        // Store the new values.
        bookmarkContentValues.put(DISPLAY_ORDER, displayOrder)
        bookmarkContentValues.put(PARENT_FOLDER_ID, newFolderId)

        // Update the database.
        bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, "$ID = $databaseId", null)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Update the bookmark name and URL.
    fun updateBookmark(databaseId: Int, bookmarkName: String, bookmarkUrl: String) {
        // Initialize a content values.
        val bookmarkContentValues = ContentValues()

        // Store the updated values.
        bookmarkContentValues.put(BOOKMARK_NAME, bookmarkName)
        bookmarkContentValues.put(BOOKMARK_URL, bookmarkUrl)

        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Update the bookmark.
        bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, "$ID = $databaseId", null)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Update the bookmark name, URL, parent folder, and display order.
    fun updateBookmark(databaseId: Int, bookmarkName: String, bookmarkUrl: String, parentFolderId: Long, displayOrder: Int) {
        // Initialize a content values.
        val bookmarkContentValues = ContentValues()

        // Store the updated values.
        bookmarkContentValues.put(BOOKMARK_NAME, bookmarkName)
        bookmarkContentValues.put(BOOKMARK_URL, bookmarkUrl)
        bookmarkContentValues.put(PARENT_FOLDER_ID, parentFolderId)
        bookmarkContentValues.put(DISPLAY_ORDER, displayOrder)

        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Update the bookmark.
        bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, "$ID = $databaseId", null)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Update the bookmark name, URL, and favorite icon.
    fun updateBookmark(databaseId: Int, bookmarkName: String, bookmarkUrl: String, favoriteIcon: ByteArray) {
        // Initialize a content values.
        val bookmarkContentValues = ContentValues()

        // Store the updated values.
        bookmarkContentValues.put(BOOKMARK_NAME, bookmarkName)
        bookmarkContentValues.put(BOOKMARK_URL, bookmarkUrl)
        bookmarkContentValues.put(FAVORITE_ICON, favoriteIcon)

        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Update the bookmark.
        bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, "$ID = $databaseId", null)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Update the bookmark name, URL, parent folder, display order, and favorite icon.
    fun updateBookmark(databaseId: Int, bookmarkName: String, bookmarkUrl: String, parentFolderId: Long, displayOrder: Int, favoriteIcon: ByteArray) {
        // Initialize a content values.
        val bookmarkContentValues = ContentValues()

        // Store the updated values.
        bookmarkContentValues.put(BOOKMARK_NAME, bookmarkName)
        bookmarkContentValues.put(BOOKMARK_URL, bookmarkUrl)
        bookmarkContentValues.put(PARENT_FOLDER_ID, parentFolderId)
        bookmarkContentValues.put(DISPLAY_ORDER, displayOrder)
        bookmarkContentValues.put(FAVORITE_ICON, favoriteIcon)

        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Update the bookmark.
        bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, "$ID = $databaseId", null)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Update the display order for one bookmark or folder.
    fun updateDisplayOrder(databaseId: Int, displayOrder: Int) {
        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Create a content values.
        val bookmarkContentValues = ContentValues()

        // Store the new display order.
        bookmarkContentValues.put(DISPLAY_ORDER, displayOrder)

        // Update the database.
        bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, "$ID = $databaseId", null)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Update the folder name.
    fun updateFolder(databaseId: Int, newFolderName: String) {
        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Create a folder content values.
        val folderContentValues = ContentValues()

        // Store the new folder name.
        folderContentValues.put(BOOKMARK_NAME, newFolderName)

        // Run the update on the folder.
        bookmarksDatabase.update(BOOKMARKS_TABLE, folderContentValues, "$ID = $databaseId", null)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Update the folder name, parent folder, and display order.
    fun updateFolder(databaseId: Int, newFolderName: String, parentFolderId: Long, displayOrder: Int) {
        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Create a folder content values.
        val folderContentValues = ContentValues()

        // Store the new folder values.
        folderContentValues.put(BOOKMARK_NAME, newFolderName)
        folderContentValues.put(PARENT_FOLDER_ID, parentFolderId)
        folderContentValues.put(DISPLAY_ORDER, displayOrder)

        // Run the update on the folder.
        bookmarksDatabase.update(BOOKMARKS_TABLE, folderContentValues, "$ID = $databaseId", null)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Update the folder name and icon.
    fun updateFolder(databaseId: Int, newFolderName: String, folderIcon: ByteArray) {
        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Create a folder content values.
        val folderContentValues = ContentValues()

        // Store the updated values.
        folderContentValues.put(BOOKMARK_NAME, newFolderName)
        folderContentValues.put(FAVORITE_ICON, folderIcon)

        // Run the update on the folder.
        bookmarksDatabase.update(BOOKMARKS_TABLE, folderContentValues, "$ID = $databaseId", null)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    // Update the folder name and icon.
    fun updateFolder(databaseId: Int, newFolderName: String, parentFolderId: Long, displayOrder: Int, folderIcon: ByteArray) {
        // Get a writable database handle.
        val bookmarksDatabase = this.writableDatabase

        // Create a folder content values.
        val folderContentValues = ContentValues()

        // Store the updated values.
        folderContentValues.put(BOOKMARK_NAME, newFolderName)
        folderContentValues.put(PARENT_FOLDER_ID, parentFolderId)
        folderContentValues.put(DISPLAY_ORDER, displayOrder)
        folderContentValues.put(FAVORITE_ICON, folderIcon)

        // Run the update on the folder.
        bookmarksDatabase.update(BOOKMARKS_TABLE, folderContentValues, "$ID = $databaseId", null)

        // Close the database handle.
        bookmarksDatabase.close()
    }

    private fun generateFolderId(): Long {
        // Get the current time in epoch format (in milliseconds).
        val possibleFolderId = Date().time

        // Get a readable database.
        val bookmarksDatabase = this.readableDatabase

        // Get a cursor with any folders that already have this folder ID.
        val existingFolderCursor = bookmarksDatabase.rawQuery("SELECT $ID FROM $BOOKMARKS_TABLE WHERE $FOLDER_ID = $possibleFolderId", null)

        // Check if the folder ID is unique.
        val folderIdIsUnique = (existingFolderCursor.count == 0)

        // Close the cursor.
        existingFolderCursor.close()

        // Either return the folder ID or test a new one.
        return if (folderIdIsUnique)
            possibleFolderId
        else
            generateFolderId()
    }
}