/*
 * @Copyright 2018-2026 HardBackNutter
 * @License GNU General Public License
 *
 * This file is part of NeverTooManyBooks.
 *
 * NeverTooManyBooks 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.
 *
 * NeverTooManyBooks 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 NeverTooManyBooks. If not, see <http://www.gnu.org/licenses/>.
 */
package com.hardbacknutter.nevertoomanybooks.database.dao.impl;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;

import androidx.annotation.IntRange;
import androidx.annotation.NonNull;
import androidx.annotation.Nullable;
import androidx.annotation.WorkerThread;
import androidx.core.util.Pair;

import java.io.IOException;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Locale;
import java.util.Optional;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;

import com.hardbacknutter.nevertoomanybooks.ServiceLocator;
import com.hardbacknutter.nevertoomanybooks.bookreadstatus.ReadingProgress;
import com.hardbacknutter.nevertoomanybooks.core.database.DaoCoverException;
import com.hardbacknutter.nevertoomanybooks.core.database.DaoInsertException;
import com.hardbacknutter.nevertoomanybooks.core.database.DaoUpdateException;
import com.hardbacknutter.nevertoomanybooks.core.database.DaoWriteException;
import com.hardbacknutter.nevertoomanybooks.core.database.SqlEncode;
import com.hardbacknutter.nevertoomanybooks.core.database.SynchronizedDb;
import com.hardbacknutter.nevertoomanybooks.core.database.SynchronizedStatement;
import com.hardbacknutter.nevertoomanybooks.core.database.Synchronizer;
import com.hardbacknutter.nevertoomanybooks.core.database.TableInfo;
import com.hardbacknutter.nevertoomanybooks.core.database.TypedCursor;
import com.hardbacknutter.nevertoomanybooks.core.parsers.DateParser;
import com.hardbacknutter.nevertoomanybooks.core.parsers.ISODateParser;
import com.hardbacknutter.nevertoomanybooks.core.storage.StorageException;
import com.hardbacknutter.nevertoomanybooks.core.tasks.ASyncExecutor;
import com.hardbacknutter.nevertoomanybooks.entities.codes.ProductCodeType;
import com.hardbacknutter.nevertoomanybooks.core.utils.LocaleListUtils;
import com.hardbacknutter.nevertoomanybooks.entities.codes.ProductCode;
import com.hardbacknutter.nevertoomanybooks.database.DBDefinitions;
import com.hardbacknutter.nevertoomanybooks.database.DBKey;
import com.hardbacknutter.nevertoomanybooks.database.dao.BookDao;
import com.hardbacknutter.nevertoomanybooks.database.dao.BookshelfDao;
import com.hardbacknutter.nevertoomanybooks.debug.SanityCheck;
import com.hardbacknutter.nevertoomanybooks.entities.Author;
import com.hardbacknutter.nevertoomanybooks.entities.Book;
import com.hardbacknutter.nevertoomanybooks.entities.BookLite;
import com.hardbacknutter.nevertoomanybooks.entities.Bookshelf;
import com.hardbacknutter.nevertoomanybooks.entities.Identifier;
import com.hardbacknutter.nevertoomanybooks.entities.Series;
import com.hardbacknutter.nevertoomanybooks.utils.AppLocale;
import com.hardbacknutter.nevertoomanybooks.utils.ReorderHelper;
import com.hardbacknutter.util.logger.LoggerFactory;

import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_BOOKS;
import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_BOOK_LOANEE;
import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_CALIBRE_BOOKS;
import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_STRIPINFO_COLLECTION;

/**
 * Database access helper class.
 * <p>
 * insert/update of a Book failures are handled with {@link DaoWriteException}
 * which makes the deep nesting of calls easier to handle.
 * <p>
 * All others follow the pattern of:
 * insert: return new id, or {@code -1} for error.
 * update: return rows affected, can be 0; or boolean when appropriate.
 * <p>
 * Individual deletes return boolean (i.e. 0 or 1 row affected)
 * Multi-deletes return either void, or the number of rows deleted.
 * <p>
 * TODO: some places ignore insert/update failures. A storage full could trigger a failure.
 */
public class BookDaoImpl
        extends BaseDaoImpl
        implements BookDao {

    /** Log tag. */
    private static final String TAG = "BookDaoImpl";

    private static final String ERROR_CREATING_BOOK_FROM = "Failed creating book from\n";
    private static final String ERROR_UPDATING_BOOK_FROM = "Failed updating book from\n";
    private static final String ERROR_STORING_COVERS = "Failed storing the covers for book from\n";
    private static final String ERROR_UUID = "Invalid UUID";

    @NonNull
    private final DateParser<LocalDateTime> dateParser;
    @NonNull
    private final TableInfo tableInfo;

    /**
     * Constructor.
     *
     * @param db           Database Access
     * @param systemLocale the system Locale
     */
    public BookDaoImpl(@NonNull final SynchronizedDb db,
                       @NonNull final Locale systemLocale) {
        super(db, TAG);
        dateParser = new ISODateParser(systemLocale);
        tableInfo = db.getTableInfo(DBDefinitions.TBL_BOOKS);
    }

    @SuppressWarnings("UnusedReturnValue")
    @Override
    public boolean touch(@NonNull final Book book) {
        if (touch(book.getId())) {
            book.setLastModified(LocalDateTime.now(ZoneOffset.UTC));
            return true;
        } else {
            return false;
        }
    }

    @Override
    public boolean touch(final long bookId) {
        final boolean result;
        try (SynchronizedStatement stmt = db.compileStatement(Sql.TOUCH)) {
            stmt.bindLong(1, bookId);
            result = 0 < stmt.executeUpdateDelete();
        }
        return result;
    }

    /**
     * See {@link #touch(long)} but for a list of books.
     *
     * @param bookIds to update
     */
    private void touch(@NonNull final Collection<Long> bookIds) {
        db.execSQL(Sql.UPDATE_BOOKS_SET + _WHERE_ + Sql.inClause(DBKey.PK_ID, bookIds));
    }

    @Override
    @IntRange(from = 1)
    public long insert(@NonNull final Context context,
                       @NonNull final Book /* in/out */ book,
                       @NonNull final Set<BookFlag> flags)
            throws StorageException,
                   DaoWriteException {

        final List<Locale> userLocales = LocaleListUtils.asList(
                context.getResources().getConfiguration().getLocales());

        Synchronizer.SyncLock txLock = null;
        //noinspection OverlyBroadCatchBlock,CheckStyle
        try {
            if (!db.inTransaction()) {
                txLock = db.beginTransaction(true);
            }

            final BookDaoHelper bookDaoHelper = new BookDaoHelper(tableInfo, userLocales);
            final ContentValues cv = bookDaoHelper.process(context, book, true);

            // Make sure we have at least one author
            final List<Author> authors = book.getAuthors();
            if (authors.isEmpty()) {
                throw new DaoInsertException("No authors for book=" + book);
            }

            final String addedOrUpdatedNow = SqlEncode.dateTime(LocalDateTime.now(ZoneOffset.UTC));

            // if we do NOT have a date set, use 'now'
            if (!cv.containsKey(DBKey.DATE_ADDED__UTC)) {
                cv.put(DBKey.DATE_ADDED__UTC, addedOrUpdatedNow);
            }
            // if we do NOT have a date set, use 'now'
            if (!cv.containsKey(DBKey.DATE_LAST_UPDATED__UTC)) {
                cv.put(DBKey.DATE_LAST_UPDATED__UTC, addedOrUpdatedNow);
            }

            // if we have an id, and we're allowed, use it as-is.
            if (book.getId() > 0 && flags.contains(BookFlag.UseIdIfPresent)) {
                cv.put(DBKey.PK_ID, book.getId());
            } else {
                // in all other circumstances, make absolutely sure we DO NOT pass in an id.
                cv.remove(DBKey.PK_ID);
            }

            // go!
            final long newBookId = db.insert(TBL_BOOKS.getName(), cv);
            if (newBookId <= 0) {
                LoggerFactory.getLogger()
                             .e(TAG, new Throwable(), "Insert failed"
                                                      + "|table=" + TBL_BOOKS.getName()
                                                      + "|cv=" + cv);

                book.putLong(DBKey.PK_ID, 0);
                book.remove(DBKey.BOOK_UUID);
                throw new DaoInsertException(ERROR_CREATING_BOOK_FROM + book);
            }

            // Set the new id/uuid on the Book itself
            // We manually remove it again (see below) upon any error
            book.putLong(DBKey.PK_ID, newBookId);
            // always lookup the UUID
            // (even if we inserted with a uuid... to protect against future changes)
            final String uuid = getBookUuid(newBookId);
            SanityCheck.requireValue(uuid, ERROR_UUID);
            book.putString(DBKey.BOOK_UUID, uuid);

            // next we add the links to series, authors,...
            insertBookLinks(context, userLocales.get(0), book, flags);

            // and populate the search suggestions table
            ServiceLocator.getInstance().getFtsDao().insert(newBookId);

            // lastly we move the covers from the cache dir to their permanent dir/name
            try {
                bookDaoHelper.persistCovers(book);

            } catch (@NonNull final StorageException e) {
                book.putLong(DBKey.PK_ID, 0);
                book.remove(DBKey.BOOK_UUID);
                throw e;

            } catch (@NonNull final IOException e) {
                book.putLong(DBKey.PK_ID, 0);
                book.remove(DBKey.BOOK_UUID);
                throw new DaoCoverException(ERROR_STORING_COVERS + book, e);
            }

            if (txLock != null) {
                db.setTransactionSuccessful();
            }
            return newBookId;

        } catch (@NonNull final RuntimeException e) {
            // Theoretically there is no need to catch RTE here, but paranoia...
            LoggerFactory.getLogger().e(TAG, e);
            throw new DaoInsertException(ERROR_CREATING_BOOK_FROM + book, e);

        } finally {
            if (txLock != null) {
                db.endTransaction(txLock);
            }
        }
    }

    @Override
    public void update(@NonNull final Context context,
                       @NonNull final Book book,
                       @NonNull final Set<BookFlag> flags)
            throws StorageException,
                   DaoWriteException {

        final List<Locale> userLocales = LocaleListUtils.asList(
                context.getResources().getConfiguration().getLocales());

        Synchronizer.SyncLock txLock = null;
        //noinspection OverlyBroadCatchBlock,CheckStyle
        try {
            if (!db.inTransaction()) {
                txLock = db.beginTransaction(true);
            }

            final BookDaoHelper bookDaoHelper = new BookDaoHelper(tableInfo, userLocales);
            final ContentValues cv = bookDaoHelper.process(context, book, false);

            // Disallow UUID updates
            if (cv.containsKey(DBKey.BOOK_UUID)) {
                cv.remove(DBKey.BOOK_UUID);
            }

            // set the DATE_LAST_UPDATED__UTC to 'now' if we're allowed,
            // or if it's not already present.
            if (!flags.contains(BookFlag.UseUpdateDateIfPresent)
                || !cv.containsKey(DBKey.DATE_LAST_UPDATED__UTC)) {
                cv.put(DBKey.DATE_LAST_UPDATED__UTC, SqlEncode
                        .dateTime(LocalDateTime.now(ZoneOffset.UTC)));
            }

            // Reminder: We're updating ONLY the fields present in the ContentValues.
            // Other fields in the database row are not affected.
            // go !
            final int rowsAffected = db.update(TBL_BOOKS.getName(), cv, DBKey.PK_ID + "=?",
                                               new String[]{String.valueOf(book.getId())});

            if (rowsAffected > 0) {
                // always lookup the UUID
                final String uuid = getBookUuid(book.getId());
                SanityCheck.requireValue(uuid, ERROR_UUID);
                book.putString(DBKey.BOOK_UUID, uuid);

                insertBookLinks(context, userLocales.get(0), book, flags);

                ServiceLocator.getInstance().getFtsDao().update(book.getId());

                try {
                    bookDaoHelper.persistCovers(book);
                } catch (@NonNull final IOException e) {
                    throw new DaoCoverException(ERROR_STORING_COVERS + book);
                }

                if (txLock != null) {
                    db.setTransactionSuccessful();
                }
                return;
            }

            throw new DaoUpdateException(ERROR_UPDATING_BOOK_FROM + book);
        } catch (@NonNull final RuntimeException e) {
            // Theoretically there is no need to catch RTE here, but paranoia...
            LoggerFactory.getLogger().e(TAG, e);
            throw new DaoUpdateException(ERROR_UPDATING_BOOK_FROM + book, e);

        } finally {
            if (txLock != null) {
                db.endTransaction(txLock);
            }
        }
    }

    @Override
    public boolean delete(@NonNull final Book book) {
        final boolean success = delete(book.getId());
        if (success) {
            book.remove(DBKey.PK_ID);
            book.remove(DBKey.BOOK_UUID);
        }
        return success;
    }

    @Override
    public boolean delete(@NonNull final BookLite bookLite) {
        final boolean success = delete(bookLite.getId());
        if (success) {
            bookLite.setId(0);
        }
        return success;
    }

    @Override
    public boolean delete(@IntRange(from = 1) final long id) {
        final String uuid = getBookUuid(id);
        // sanity check
        if (uuid == null || uuid.isBlank()) {
            return false;
        }

        return deleteByUuid(List.of(uuid)) == 1;
    }

    /**
     * Delete the books for the given list of UUIDs.
     *
     * @param uuids list of book UUIDs
     *
     * @return the number of books deleted (i.e. rowsAffected)
     */
    @Override
    public int deleteByUuid(@NonNull final Collection<String> uuids) {
        final List<String> actuallyDeleted = new ArrayList<>();

        Synchronizer.SyncLock txLock = null;
        try {
            if (!db.inTransaction()) {
                txLock = db.beginTransaction(true);
            }

            // Delete the book, and remember which ones were really deleted.
            // We can't do a delete operation with uuid IN (...) as we need the
            // individual successful uuid deletions.
            try (SynchronizedStatement stmt = db.compileStatement(Sql.DELETE_BY_UUID)) {
                for (final String uuid : uuids) {
                    stmt.bindString(1, uuid);
                    if (stmt.executeUpdateDelete() > 0) {
                        actuallyDeleted.add(uuid);
                    }
                }
            }

            // At this point all database actions were successful.
            // Now delete the covers for the actually deleted books.
            ASyncExecutor.STORAGE_WRITES.execute(
                    () -> ServiceLocator.getInstance().getCoverStorage().delete(actuallyDeleted));

            if (txLock != null) {
                db.setTransactionSuccessful();
            }
        } finally {
            if (txLock != null) {
                db.endTransaction(txLock);
            }
        }

        return actuallyDeleted.size();
    }

    /**
     * Called during book insert & update.
     * Each step in this method will first delete all entries in the Book-[tableX] table
     * for this bookId, and then insert the new links.
     *
     * @param context    Current context
     * @param userLocale Current Locale
     * @param book       A collection with the columns to be set. May contain extra data.
     * @param flags      See {@link BookFlag} for flag definitions
     *
     * @throws DaoWriteException on failure
     */
    private void insertBookLinks(@NonNull final Context context,
                                 @NonNull final Locale userLocale,
                                 @NonNull final Book book,
                                 @NonNull final Set<BookFlag> flags)
            throws DaoWriteException {

        // Only lookup locales
        // when we're NOT in batch mode (i.e. NOT doing an import)
        final boolean lookupLocale = !flags.contains(BookFlag.RunInBatch);

        // FIXME: apply useIdIfPresent to the tags collection, perhaps to others as well
        //final boolean useIdIfPresent = !flags.contains(BookFlag.UseIdIfPresent);

        // unconditional lookup of the book locale!
        final Locale bookLocale = book.getLocale(userLocale).orElse(userLocale);

        final ServiceLocator serviceLocator = ServiceLocator.getInstance();

        if (book.contains(Book.BKEY_BOOKSHELF_LIST)) {
            // Bookshelves will be inserted if new, but never updated
            serviceLocator.getBookshelfDao().insertOrUpdate(context,
                                                            book.getId(),
                                                            book.getBookshelves());
        }

        if (book.contains(Book.BKEY_AUTHOR_LIST)) {
            serviceLocator.getAuthorDao().insertOrUpdate(context,
                                                         book.getId(), true,
                                                         book.getAuthors(),
                                                         author -> bookLocale);
        }


        if (book.contains(Book.BKEY_SERIES_LIST)) {
            final Function<Series, Locale> localeSupplier = series -> {
                if (lookupLocale) {
                    return series.getLocale(userLocale).orElse(bookLocale);
                } else {
                    return bookLocale;
                }
            };
            serviceLocator.getSeriesDao().insertOrUpdate(context,
                                                         book.getId(), true,
                                                         book.getSeries(),
                                                         localeSupplier);
        }

        if (book.contains(Book.BKEY_PUBLISHER_LIST)) {
            serviceLocator.getPublisherDao().insertOrUpdate(context,
                                                            book.getId(), true,
                                                            book.getPublishers(),
                                                            publisher -> bookLocale);
        }

        if (book.contains(Book.BKEY_TOC_LIST)) {
            // TOC entries are two steps away; they can exist in other books
            // Hence we will both insert new entries
            // AND update existing ones as needed.
            serviceLocator.getTocEntryDao().insertOrUpdate(context,
                                                           book.getId(),
                                                           book.getToc(),
                                                           tocEntry -> bookLocale);
        }

        if (book.contains(Book.BKEY_TAG_LIST)) {
            // These are two steps away; they can exist in other books.
            // We will insert new entries
            // AND update existing ones as needed.
            serviceLocator.getTagDao().insertOrUpdate(context,
                                                      book.getId(),
                                                      book.getTags(),
                                                      tag -> bookLocale);
        }

        if (book.contains(Identifier.Value.BKEY_LIST)) {
            // These are two steps away; they can exist in other books.
            // However, we in fact do NOT use id's except for the internal database references.
            // Instead, we always work with the String key.
            // We will insert new entries
            // but there is nothing to update as such.
            serviceLocator.getBookIdentifierDao()
                          .insertOrUpdate(Identifier.EntityType.Book,
                                          book.getId(), book.getIdentifiers());
        }

        // Returning a book == deleting the loanee,
        // is handled directly, here we only need to bother with insert/update
        if (book.contains(DBKey.LOANEE_NAME)) {
            serviceLocator.getLoaneeDao().setLoanee(book);
        }

        // Handle synchronisation field.
        if (book.contains(DBKey.CALIBRE.BOOK_UUID)) {
            // Calibre libraries will be inserted if new, but not updated
            serviceLocator.getCalibreDao().insertOrUpdate(context, book);
        }

        // Handle synchronisation field.
        if (book.getIdentifierValue(Identifier.SID_STRIP_INFO).isPresent()) {
            serviceLocator.getStripInfoDao().insertOrUpdate(book);
        }
    }

    @Override
    public boolean setBookshelves(@NonNull final Context context,
                                  @NonNull final Collection<Long> bookIds,
                                  @NonNull final Collection<Bookshelf> bookshelves) {

        // Sanity check
        if (bookIds.isEmpty() || bookshelves.isEmpty()) {
            return false;
        }

        Synchronizer.SyncLock txLock = null;
        try {
            if (!db.inTransaction()) {
                txLock = db.beginTransaction(true);
            }

            final BookshelfDao bookshelfDao = ServiceLocator.getInstance().getBookshelfDao();
            for (final long bookId : bookIds) {
                // Bookshelves will be inserted if new, but never updated
                bookshelfDao.insertOrUpdate(context, bookId, bookshelves);
            }

            touch(bookIds);

            if (txLock != null) {
                db.setTransactionSuccessful();
            }

        } catch (@NonNull final DaoWriteException e) {
            return false;

        } finally {
            if (txLock != null) {
                db.endTransaction(txLock);
            }
        }

        return true;
    }

    @Override
    public boolean setLocation(@NonNull final Collection<Long> bookIds,
                               @NonNull final String location) {
        // Sanity check
        if (bookIds.isEmpty()) {
            return false;
        }

        final String sql = Sql.UPDATE_BOOKS_SET + ',' + DBKey.LOCATION + "=?"
                           + _WHERE_ + Sql.inClause(DBKey.PK_ID, bookIds);

        final boolean success;

        try (SynchronizedStatement stmt = db.compileStatement(sql)) {
            stmt.bindString(1, location);
            success = 0 < stmt.executeUpdateDelete();
        }

        return success;
    }

    @Override
    public boolean setRead(@NonNull final Book book,
                           final boolean read) {
        final String now = SqlEncode.dateTime(LocalDateTime.now());
        final String endDate = read ? now : "";

        final boolean success;

        try (SynchronizedStatement stmt = db.compileStatement(Sql.UPDATE_READ_PROGRESS)) {
            stmt.bindBoolean(1, read);
            stmt.bindString(2, endDate);
            stmt.bindString(3, "");
            stmt.bindLong(4, book.getId());
            success = 0 < stmt.executeUpdateDelete();
        }

        if (success) {
            book.internalSetReadingProgress(read, endDate);
            book.putString(DBKey.DATE_LAST_UPDATED__UTC, now);
        }

        return success;
    }

    @Override
    public boolean setReadingProgress(@NonNull final Book book,
                                      @NonNull final ReadingProgress progress) {

        final String now = SqlEncode.dateTime(LocalDateTime.now());
        final String endDate = progress.isRead() ? now : "";

        final boolean success;

        // If the separate page-count field is empty, and we have a total-pages value,
        // set it as well.
        // KEEP THIS LOGIC IN SYNC with {@link BookDaoHelper#processReadProgress()} !
        String pageCount = book.getPages();
        if (!progress.asPercentage() && pageCount.isEmpty()) {
            pageCount = String.valueOf(progress.getTotalPages());
        }

        // We might be updating the page-count needlessly, but no harm done.
        try (SynchronizedStatement stmt = db.compileStatement(
                Sql.UPDATE_READ_PROGRESS_AND_PAGE_COUNT)) {
            stmt.bindBoolean(1, progress.isRead());
            stmt.bindString(2, endDate);
            stmt.bindString(3, progress.toJson());
            stmt.bindString(4, pageCount);
            stmt.bindLong(5, book.getId());
            success = 0 < stmt.executeUpdateDelete();
        }

        if (success) {
            book.internalSetReadingProgress(progress, endDate, pageCount);
            book.putString(DBKey.DATE_LAST_UPDATED__UTC, now);
        }

        return success;
    }

    @Override
    public int count() {
        try (SynchronizedStatement stmt = db.compileStatement(Sql.COUNT_ALL)) {
            return (int) stmt.simpleQueryForLongOrZero();
        }
    }

    /**
     * Return a Cursor with all Books selected by the passed arguments.
     *
     * @param whereClause   without the 'where' keyword, can be {@code null} or {@code ""}
     * @param selectionArgs You may include ?s in where clause in the query,
     *                      which will be replaced by the values from selectionArgs. The
     *                      values will be bound as Strings.
     * @param orderByClause without the 'order by' keyword, can be {@code null} or {@code ""}
     *
     * @return A Book Cursor with 0..1 row
     */
    @NonNull
    private TypedCursor getBookCursor(@Nullable final CharSequence whereClause,
                                      @Nullable final String[] selectionArgs,
                                      @Nullable final CharSequence orderByClause) {

        final String sql = Sql.SELECT_BOOK
                           // LEFT OUTER JOIN, COALESCE nulls to ""
                           + ",COALESCE(" + TBL_BOOK_LOANEE.dot(DBKey.LOANEE_NAME) + ", '')"
                           + _AS_ + DBKey.LOANEE_NAME

                           // LEFT OUTER JOIN, columns default to NULL
                           + ',' + TBL_CALIBRE_BOOKS.dotAs(DBKey.CALIBRE.BOOK_ID,
                                                           DBKey.CALIBRE.BOOK_UUID,
                                                           DBKey.CALIBRE.BOOK_MAIN_FORMAT,
                                                           DBKey.FK_CALIBRE_LIBRARY)

                           + _FROM_ + TBL_BOOKS.as()
                           + TBL_BOOKS.leftOuterJoin(TBL_BOOK_LOANEE)
                           + TBL_BOOKS.leftOuterJoin(TBL_CALIBRE_BOOKS)
                           + (whereClause != null && whereClause.length() > 0
                              ? _WHERE_ + whereClause : "")
                           + (orderByClause != null && orderByClause.length() > 0
                              ? _ORDER_BY_ + orderByClause : "")

                           + _COLLATION;

        final TypedCursor cursor = db.rawQueryWithTypedCursor(sql, selectionArgs, null);
        // force the TypedCursor to retrieve the real column types.
        cursor.setDb(db, TBL_BOOKS);
        return cursor;
    }

    @Override
    @NonNull
    public TypedCursor fetchById(@IntRange(from = 1) final long id) {
        return getBookCursor(TBL_BOOKS.dot(DBKey.PK_ID) + "=?",
                             new String[]{String.valueOf(id)},
                             null);
    }

    @Override
    @NonNull
    public TypedCursor fetchForAutoUpdate(@NonNull final List<Long> idList) {
        if (idList.isEmpty()) {
            throw new IllegalArgumentException("idList.isEmpty()");
        }

        if (idList.size() == 1) {
            // optimise for single book
            return getBookCursor(TBL_BOOKS.dot(DBKey.AUTO_UPDATE) + "=1"
                                 + _AND_ + TBL_BOOKS.dot(DBKey.PK_ID) + "=?",
                                 new String[]{String.valueOf(idList.get(0))},
                                 null);

        } else {
            return getBookCursor(TBL_BOOKS.dot(DBKey.AUTO_UPDATE) + "=1"
                                 + _AND_ + Sql.inClause(TBL_BOOKS.dot(DBKey.PK_ID), idList),
                                 null,
                                 TBL_BOOKS.dot(DBKey.PK_ID));
        }
    }

    @Override
    @NonNull
    public TypedCursor fetchForAutoUpdateFromIdOnwards(@IntRange(from = 1) final long id) {
        return getBookCursor(TBL_BOOKS.dot(DBKey.AUTO_UPDATE) + "=1"
                             + _AND_ + TBL_BOOKS.dot(DBKey.PK_ID) + ">=?",
                             new String[]{String.valueOf(id)},
                             TBL_BOOKS.dot(DBKey.PK_ID));
    }

    @Override
    public int countBooksForExport(@Nullable final LocalDateTime sinceDateTime) {
        if (sinceDateTime == null) {
            try (SynchronizedStatement stmt = db.compileStatement(Sql.COUNT_ALL)) {
                return (int) stmt.simpleQueryForLongOrZero();
            }
        } else {
            try (SynchronizedStatement stmt = db.compileStatement(
                    Sql.COUNT_ALL + _WHERE_ + DBKey.DATE_LAST_UPDATED__UTC + ">=?")) {
                stmt.bindString(1, SqlEncode.dateTime(sinceDateTime));
                return (int) stmt.simpleQueryForLongOrZero();
            }
        }
    }

    @Override
    @NonNull
    public TypedCursor fetchBooksForExport(@Nullable final LocalDateTime sinceDateTime) {
        if (sinceDateTime == null) {
            return getBookCursor(null, null, TBL_BOOKS.dot(DBKey.PK_ID));
        } else {
            return getBookCursor(TBL_BOOKS.dot(DBKey.DATE_LAST_UPDATED__UTC) + ">=?",
                                 new String[]{SqlEncode.dateTime(sinceDateTime)},
                                 TBL_BOOKS.dot(DBKey.PK_ID));
        }
    }

    @Override
    @NonNull
    public TypedCursor fetchBooksForExportToCalibre(@IntRange(from = 1) final long libraryId,
                                                    @Nullable final LocalDateTime sinceDateTime) {
        final String whereClause;
        final String[] selectionArgs;

        if (sinceDateTime == null) {
            whereClause = TBL_CALIBRE_BOOKS.dot(DBKey.FK_CALIBRE_LIBRARY) + "=?";
            selectionArgs = new String[]{String.valueOf(libraryId)};
        } else {
            whereClause = TBL_CALIBRE_BOOKS.dot(DBKey.FK_CALIBRE_LIBRARY) + "=?"
                          + _AND_ + TBL_BOOKS.dot(DBKey.DATE_LAST_UPDATED__UTC) + ">=?";
            selectionArgs = new String[]{String.valueOf(libraryId),
                    SqlEncode.dateTime(sinceDateTime)};
        }

        final String sql = Sql.SELECT_BOOK
                           + ',' + TBL_CALIBRE_BOOKS
                                   .dotAs(DBKey.CALIBRE.BOOK_ID,
                                          DBKey.CALIBRE.BOOK_UUID,
                                          DBKey.CALIBRE.BOOK_MAIN_FORMAT,
                                          DBKey.FK_CALIBRE_LIBRARY)

                           + _FROM_ + TBL_BOOKS.as()
                           + TBL_BOOKS.leftOuterJoin(TBL_CALIBRE_BOOKS)
                           + _WHERE_ + whereClause
                           + _ORDER_BY_ + TBL_BOOKS.dot(DBKey.PK_ID)
                           + _COLLATION;

        final TypedCursor cursor = db.rawQueryWithTypedCursor(sql, selectionArgs, null);
        // force the TypedCursor to retrieve the real column types.
        cursor.setDb(db, TBL_BOOKS);
        return cursor;
    }

    @Override
    @NonNull
    public TypedCursor fetchBooksForExportToStripInfo(@Nullable final LocalDateTime sinceDateTime) {
        final String whereClause;
        @Nullable
        final String[] selectionArgs;

        if (sinceDateTime == null) {
            whereClause =
                    TBL_STRIPINFO_COLLECTION.dot(
                            DBKey.STRIP_INFO.COLLECTION_ID) + _IS_NOT_NULL;
            selectionArgs = null;
        } else {
            whereClause =
                    TBL_STRIPINFO_COLLECTION.dot(
                            DBKey.STRIP_INFO.COLLECTION_ID) + _IS_NOT_NULL
                    + _AND_
                    + TBL_STRIPINFO_COLLECTION.dot(
                            DBKey.STRIP_INFO.LAST_SYNC_DATE__UTC) + ">=?";
            selectionArgs = new String[]{SqlEncode.dateTime(sinceDateTime)};
        }

        final String sql = Sql.SELECT_BOOK
                           + ',' + TBL_STRIPINFO_COLLECTION
                                   .dotAs(DBKey.STRIP_INFO.BOOK_ID,
                                          DBKey.STRIP_INFO.COLLECTION_ID,
                                          DBKey.STRIP_INFO.OWNED,
                                          DBKey.STRIP_INFO.DIGITAL,
                                          DBKey.STRIP_INFO.WANTED,
                                          DBKey.STRIP_INFO.AMOUNT,
                                          DBKey.STRIP_INFO.LAST_SYNC_DATE__UTC)
                           + _FROM_ + TBL_BOOKS.as()
                           + TBL_BOOKS.leftOuterJoin(TBL_STRIPINFO_COLLECTION)
                           + _WHERE_ + whereClause
                           + _ORDER_BY_ + TBL_BOOKS.dot(DBKey.PK_ID)
                           + _COLLATION;

        final TypedCursor cursor = db.rawQueryWithTypedCursor(sql, selectionArgs, null);
        // force the TypedCursor to retrieve the real column types.
        cursor.setDb(db, TBL_BOOKS);
        return cursor;
    }

    @Override
    @NonNull
    public TypedCursor fetch(@NonNull final List<ProductCode> list) {
        if (list.isEmpty()) {
            throw new IllegalArgumentException("list.isEmpty()");
        }

        if (list.size() == 1) {
            // optimise for single book
            return getBookCursor(TBL_BOOKS.dot(DBKey.ISBN) + "=?",
                                 new String[]{list.get(0).asText()}, null);
        } else {
            return getBookCursor(TBL_BOOKS.dot(DBKey.ISBN)
                                 + " IN ("
                                 + list.stream()
                                       .map(s -> '\'' + s.asText() + '\'')
                                       .collect(Collectors.joining(","))
                                 + ')',
                                 null,
                                 TBL_BOOKS.dot(DBKey.PK_ID));
        }
    }

    @Override
    @NonNull
    public List<String> getBookUuidList() {
        return getColumnAsStringArrayList(Sql.SELECT_ALL_UUID);
    }

    /**
     * Return the book UUID based on the id.
     *
     * @param bookId of the book
     *
     * @return the book UUID, or {@code null} if not found/failure
     */
    @Nullable
    private String getBookUuid(@IntRange(from = 1) final long bookId) {
        try (SynchronizedStatement stmt = db.compileStatement(Sql.FIND_UUID_BY_ID)) {
            stmt.bindLong(1, bookId);
            return stmt.simpleQueryForStringOrNull();
        }
    }

    @Override
    @IntRange(from = 0)
    public long getBookIdByUuid(@NonNull final String uuid) {
        try (SynchronizedStatement stmt = db.compileStatement(Sql.FIND_ID_BY_UUID)) {
            stmt.bindString(1, uuid);
            return stmt.simpleQueryForLongOrZero();
        }
    }

    @Override
    @NonNull
    public List<Pair<Long, String>> getBookIdAndTitle(@NonNull final ProductCode productCode) {
        final List<Pair<Long, String>> result = new ArrayList<>();

        // If the code is ISBN-10 compatible, search on both ISBN-10 and ISBN-13
        if (productCode.isIsbn10Compat()) {
            try (Cursor cursor = db.rawQuery(Sql.FIND_BY_ISBN_10_OR_13, new String[]{
                    productCode.asText(ProductCodeType.Isbn10),
                    productCode.asText(ProductCodeType.Isbn13)})) {
                while (cursor.moveToNext()) {
                    result.add(new Pair<>(cursor.getLong(0),
                                        cursor.getString(1)));
                }
            }
        } else {
            // Otherwise just search on the code as-is
            try (Cursor cursor = db.rawQuery(Sql.FIND_BY_PRODUCT_CODE, new String[]{
                    productCode.asText()})) {
                while (cursor.moveToNext()) {
                    result.add(new Pair<>(cursor.getLong(0),
                                        cursor.getString(1)));
                }
            }
        }

        return result;
    }

    @Override
    public boolean bookExists(@IntRange(from = 1) final long id) {
        try (SynchronizedStatement stmt = db.compileStatement(Sql.BOOK_ID_EXISTS)) {
            stmt.bindLong(1, id);
            return stmt.simpleQueryForLongOrZero() == 1;
        }
    }

    @Override
    public boolean bookExists(@NonNull final ProductCode productCode) {
        // Sanity check, we don't want false positives
        if (productCode.asText().isBlank()) {
            return false;
        }

        // If the code is ISBN-10 compatible, search on both ISBN-10 and ISBN-13
        if (productCode.isIsbn10Compat()) {
            try (SynchronizedStatement stmt = db.compileStatement(Sql.BOOK_ISBN_10_OR_13_EXISTS)) {
                stmt.bindString(1, productCode.asText(ProductCodeType.Isbn10));
                stmt.bindString(2, productCode.asText(ProductCodeType.Isbn13));
                return stmt.simpleQueryForLongOrZero() == 1;
            }
        } else {
            // Otherwise just search on the code as-is
            try (SynchronizedStatement stmt = db.compileStatement(Sql.BOOK_PRODUCT_CODE_EXISTS)) {
                stmt.bindString(1, productCode.asText());
                return stmt.simpleQueryForLongOrZero() == 1;
            }
        }
    }

    @Override
    @NonNull
    public List<String> getCurrencies(@NonNull final String key) {
        if (!DBKey.getMoneyKeys().contains(key)) {
            throw new IllegalArgumentException(key);
        }

        final String sql = "SELECT DISTINCT UPPER(" + key + DBKey.CURRENCY_SUFFIX
                           + ") FROM " + TBL_BOOKS.getName()
                           + _ORDER_BY_ + key + DBKey.CURRENCY_SUFFIX + _COLLATION;

        return getColumnAsStringArrayList(sql);
    }

    @NonNull
    public Optional<LocalDateTime> getLastUpdateDate(@IntRange(from = 1) final long id) {
        try (SynchronizedStatement stmt =
                     db.compileStatement(Sql.FIND_LAST_UPDATE_DATE_BY_BOOK_ID)) {
            stmt.bindLong(1, id);
            return dateParser.parse(stmt.simpleQueryForStringOrNull());
        }
    }

    @Override
    @WorkerThread
    public int rebuildOrderByColumns(@NonNull final Context context,
                                     @NonNull final Locale locale,
                                     @NonNull final ReorderHelper reorderHelper) {
        final AppLocale appLocale = ServiceLocator.getInstance().getAppLocale();
        int i = 0;
        try (Cursor cursor = db.rawQuery(Sql.OB_REBUILD_TITLES, null);
             SynchronizedStatement stmt = db.compileStatement(Sql.OB_REBUILD)) {

            while (cursor.moveToNext()) {
                final long id = cursor.getLong(0);
                final String title = cursor.getString(1);
                final String currentObTitle = cursor.getString(2);

                final Locale bookLocale = appLocale
                        .getLocale(cursor.getString(3), locale)
                        .orElse(locale);
                final String rTitle = reorderHelper
                        .reorderForSorting(context, title, bookLocale);
                final String rObTitle = textNormaliser.strict(rTitle, bookLocale);

                // only update the database if actually needed.
                if (!currentObTitle.equals(rObTitle)) {
                    stmt.bindString(1, rObTitle);
                    stmt.bindLong(2, id);
                    stmt.executeUpdateDelete();
                    i++;
                }
            }
        }
        return i;
    }

    private static final class Sql {

        /** Delete a {@link Book}. */
        static final String DELETE_BY_UUID =
                DELETE_FROM_ + TBL_BOOKS.getName() + _WHERE_ + DBKey.BOOK_UUID + "=?";

        /**
         * The base SQL to update a {@link Book}. Sets the last-updated column to 'now'.
         * Append columns and other clauses as needed.
         * <p>
         * This should always be used when updating the books table!
         */
        static final String UPDATE_BOOKS_SET =
                UPDATE_ + TBL_BOOKS.getName()
                + _SET_ + DBKey.DATE_LAST_UPDATED__UTC + "=current_timestamp";

        /** Update a single Book's read status/progress and read_end date. */
        static final String UPDATE_READ_PROGRESS =
                UPDATE_BOOKS_SET
                + ',' + DBKey.READ__BOOL + "=?"
                + ',' + DBKey.READ_END__DATE + "=?"
                + ',' + DBKey.READ_PROGRESS + "=?"
                + _WHERE_ + DBKey.PK_ID + "=?";

        /**
         * Update a single Book's read status/progress and read_end date.
         * Potentially update the book's page-count as well.
         */
        static final String UPDATE_READ_PROGRESS_AND_PAGE_COUNT =
                UPDATE_BOOKS_SET
                + ',' + DBKey.READ__BOOL + "=?"
                + ',' + DBKey.READ_END__DATE + "=?"
                + ',' + DBKey.READ_PROGRESS + "=?"
                + ',' + DBKey.PAGES + "=?"
                + _WHERE_ + DBKey.PK_ID + "=?";

        static final String TOUCH =
                UPDATE_BOOKS_SET + _WHERE_ + DBKey.PK_ID + "=?";

        /** Get a count of the {@link Book}s. */
        static final String COUNT_ALL =
                SELECT_COUNT_FROM_ + TBL_BOOKS.getName();

        /**
         * Find the {@link Book} id and title.
         * Search by an ISBN-10 compatible {@link ProductCode}.
         * Uses both ISBN-10 and ISBN-13 formats.
         */
        static final String FIND_BY_ISBN_10_OR_13 =
                SELECT_ + DBKey.PK_ID + ',' + DBKey.TITLE + _FROM_ + TBL_BOOKS.getName()
                + _WHERE_ + DBKey.ISBN + " LIKE ? OR " + DBKey.ISBN + " LIKE ?";

        /**
         * Find the {@link Book} id AND title.
         * Search by the {@link ProductCode}.
         * The {@link ProductCode} need not be valid.
         */
        static final String FIND_BY_PRODUCT_CODE =
                SELECT_ + DBKey.PK_ID + ',' + DBKey.TITLE + _FROM_ + TBL_BOOKS.getName()
                + _WHERE_ + DBKey.ISBN + " LIKE ?";

        /** Find the UUID of a {@link Book} by its id. */
        static final String FIND_UUID_BY_ID =
                SELECT_ + DBKey.BOOK_UUID + _FROM_ + TBL_BOOKS.getName()
                + _WHERE_ + DBKey.PK_ID + "=?";

        /** Find the id of a {@link Book} by its UUID. */
        static final String FIND_ID_BY_UUID =
                SELECT_ + DBKey.PK_ID + _FROM_ + TBL_BOOKS.getName()
                + _WHERE_ + DBKey.BOOK_UUID + "=?";

        /** Find the {@link DBKey#DATE_LAST_UPDATED__UTC} for a {@link Book} by its id. */
        static final String FIND_LAST_UPDATE_DATE_BY_BOOK_ID =
                SELECT_ + DBKey.DATE_LAST_UPDATED__UTC + _FROM_ + TBL_BOOKS.getName()
                + _WHERE_ + DBKey.PK_ID + "=?";

        /**
         * Check if a {@link Book} exists.
         * Search by the specified {@link DBKey#PK_ID}.
         * The rowcount/result will be {@code 0} or {@code 1}.
         */
        static final String BOOK_ID_EXISTS =
                SELECT_EXISTS_ + '('
                + SELECT_ + "null" + _FROM_ + TBL_BOOKS.getName() + _WHERE_ + DBKey.PK_ID + "=?"
                + ')';

        /**
         * Check if a {@link Book} exists.
         * Search by an ISBN-10 compatible {@link ProductCode}.
         * Uses both ISBN-10 and ISBN-13 formats.
         * The rowcount/result will be {@code 0} or {@code 1}.
         */
        static final String BOOK_ISBN_10_OR_13_EXISTS =
                SELECT_EXISTS_ + '(' + FIND_BY_ISBN_10_OR_13 + ')';

        /**
         * Check if a {@link Book} exists.
         * Search by the {@link ProductCode}.
         * The {@link ProductCode} need not be valid.
         * The rowcount/result will be {@code 0} or {@code 1}.
         */
        static final String BOOK_PRODUCT_CODE_EXISTS =
                SELECT_EXISTS_ + '(' + FIND_BY_PRODUCT_CODE + ')';

        /** Book UUID only, for accessing all cover image files. */
        static final String SELECT_ALL_UUID =
                SELECT_ + DBKey.BOOK_UUID + _FROM_ + TBL_BOOKS.getName();

        /**
         * The SELECT clause for getting a book (list).
         * <p>
         * Note we could use TBL_BOOKS.dot("*")
         * We'd fetch the unneeded TITLE_OB field, but that would be OK.
         * Nevertheless, listing the fields here gives a better understanding
         * <p>
         * NEWTHINGS: adding fields ? Now is a good time to update {@link Book#duplicate}
         */
        static final String SELECT_BOOK =
                SELECT_ + TBL_BOOKS.dotAs(
                        DBKey.PK_ID, DBKey.BOOK_UUID,
                        DBKey.TITLE,
                        DBKey.TRANSLATION_ORIGINAL_TITLE,
                        DBKey.TRANSLATION_ORIGINAL_LANGUAGE,

                        DBKey.ISBN, DBKey.CONTENT_TYPE,
                        DBKey.PUBLICATION_DATE, DBKey.PRINT_RUN,
                        DBKey.PRICE_LISTED, DBKey.PRICE_LISTED_CURRENCY,
                        DBKey.FIRST_PUBLICATION_DATE,
                        DBKey.FORMAT, DBKey.COLOR, DBKey.LANGUAGE, DBKey.PAGES,
                        // Main/public description about the content/publication
                        DBKey.DESCRIPTION,
                        // partially edition info, partially user-owned info.
                        DBKey.EDITION_FLAGS,
                        DBKey.EDITION_INFO,
                        // user notes
                        DBKey.PERSONAL_NOTES,
                        DBKey.CONDITION_BOOK, DBKey.CONDITION_COVER,
                        DBKey.LOCATION, DBKey.SIGNED__BOOL, DBKey.RATING,
                        DBKey.READ__BOOL, DBKey.READ_PROGRESS,
                        DBKey.READ_START__DATE, DBKey.READ_END__DATE,
                        DBKey.DATE_ACQUIRED,
                        DBKey.PRICE_PAID, DBKey.PRICE_PAID_CURRENCY,
                        // added/updated
                        DBKey.DATE_ADDED__UTC, DBKey.DATE_LAST_UPDATED__UTC,
                        DBKey.AUTO_UPDATE);

        /** All Book titles for a rebuild of the {@link DBKey#TITLE_OB} column. */
        private static final String OB_REBUILD_TITLES =
                SELECT_ + DBKey.PK_ID
                + ',' + DBKey.TITLE
                + ',' + DBKey.TITLE_OB
                + ',' + DBKey.LANGUAGE
                + _FROM_ + TBL_BOOKS.getName();

        private static final String OB_REBUILD =
                UPDATE_BOOKS_SET + ',' + DBKey.TITLE_OB + "=?"
                + _WHERE_ + DBKey.PK_ID + "=?";

        /**
         * Create an SQL fragment "column IN (csv-list)".
         *
         * @param column to use
         * @param idList the ids
         *
         * @return a valid IN clause for use with a WHERE
         */
        @NonNull
        private static String inClause(@NonNull final String column,
                                       @NonNull final Collection<Long> idList) {
            return idList.stream()
                         .map(String::valueOf)
                         .collect(Collectors.joining(",", column + " IN (", ")"));
        }
    }
}
