/*
 * @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.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Build;

import androidx.annotation.IntRange;
import androidx.annotation.NonNull;
import androidx.annotation.RequiresApi;

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.BiConsumer;
import java.util.function.Supplier;
import java.util.stream.Collectors;

import com.hardbacknutter.nevertoomanybooks.BuildConfig;
import com.hardbacknutter.nevertoomanybooks.R;
import com.hardbacknutter.nevertoomanybooks.ServiceLocator;
import com.hardbacknutter.nevertoomanybooks.booklist.TopRowListPosition;
import com.hardbacknutter.nevertoomanybooks.booklist.filters.FilterFactory;
import com.hardbacknutter.nevertoomanybooks.booklist.filters.PEntityListFilter;
import com.hardbacknutter.nevertoomanybooks.booklist.filters.PFilter;
import com.hardbacknutter.nevertoomanybooks.booklist.style.BuiltinStyle;
import com.hardbacknutter.nevertoomanybooks.booklist.style.Style;
import com.hardbacknutter.nevertoomanybooks.core.database.DaoInsertException;
import com.hardbacknutter.nevertoomanybooks.core.database.DaoUpdateException;
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.TransactionException;
import com.hardbacknutter.nevertoomanybooks.database.CursorRow;
import com.hardbacknutter.nevertoomanybooks.database.DBKey;
import com.hardbacknutter.nevertoomanybooks.database.dao.BookshelfDao;
import com.hardbacknutter.nevertoomanybooks.database.dao.StylesHelper;
import com.hardbacknutter.nevertoomanybooks.entities.Book;
import com.hardbacknutter.nevertoomanybooks.entities.Bookshelf;
import com.hardbacknutter.nevertoomanybooks.entities.EntityMergeHelper;
import com.hardbacknutter.nevertoomanybooks.entities.Tag;

import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_BOOKLIST_STYLES;
import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_BOOKSHELF;
import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_BOOKSHELF_FILTERS;
import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_BOOK_BOOKSHELF;
import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_BOOK_LIST_NODE_STATE;

public class BookshelfDaoImpl
        extends BaseDaoImpl
        implements BookshelfDao {

    /**
     * Preference name - the bookshelf to load next time we start up.
     * Storing the name and not the id. If you export/import... the id will be different.
     * <p>
     * The CamelCase is legacy...
     */
    private static final String PK_BOOKSHELF_CURRENT = "Bookshelf.CurrentBookshelf";
    /**
     * The id of the default Bookshelf.
     * <p>
     * int: Initially always {@code 1}.
     *
     * @see #onPostCreate(Context, SQLiteDatabase)
     */
    private static final String PK_BOOKSHELF_DEFAULT = "bookshelf.default.id";

    /** Log tag. */
    private static final String TAG = "BookshelfDaoImpl";
    private static final String ERROR_INSERT_FROM = "Insert from\n";
    private static final String ERROR_UPDATE_FROM = "Update from\n";
    @NonNull
    private final Supplier<StylesHelper> stylesHelperSupplier;

    /**
     * Constructor.
     *
     * @param db                   Database Access
     * @param stylesHelperSupplier deferred supplier for the {@link StylesHelper}
     */
    public BookshelfDaoImpl(@NonNull final SynchronizedDb db,
                            @NonNull final Supplier<StylesHelper> stylesHelperSupplier) {
        super(db, TAG);
        this.stylesHelperSupplier = stylesHelperSupplier;
    }

    /**
     * Run at installation time to add the 'all' and default shelves to the database.
     *
     * @param context Current context
     * @param db      Underlying database
     */
    public static void onPostCreate(@NonNull final Context context,
                                    @NonNull final SQLiteDatabase db) {
        // inserts an 'All Books' bookshelf with _id==-1, see {@link Bookshelf}.
        db.execSQL(INSERT_INTO_ + TBL_BOOKSHELF
                   + '(' + DBKey.PK_ID
                   + ',' + DBKey.BOOKSHELF.NAME
                   + ',' + DBKey.FK_STYLE
                   + ") VALUES ("
                   + Bookshelf.ALL_BOOKS
                   + ",'" + context.getString(R.string.bookshelf_all_books)
                   + "'," + BuiltinStyle.HARD_DEFAULT_ID
                   + ')');

        // inserts a 'Default' bookshelf with _id==1
        db.execSQL(INSERT_INTO_ + TBL_BOOKSHELF
                   + '(' + DBKey.PK_ID
                   + ',' + DBKey.BOOKSHELF.NAME
                   + ',' + DBKey.FK_STYLE
                   + ") VALUES (1,'" + context.getString(R.string.bookshelf_my_books)
                   + "'," + BuiltinStyle.HARD_DEFAULT_ID
                   + ')');
    }

    @NonNull
    private Optional<Bookshelf> getAllBooksBookshelf(@NonNull final Context context) {
        final Bookshelf bookshelf = new Bookshelf(
                context.getString(R.string.bookshelf_all_books),
                stylesHelperSupplier.get().getDefault());
        bookshelf.setId(Bookshelf.ALL_BOOKS);
        return Optional.of(bookshelf);
    }

    @Override
    @NonNull
    public Bookshelf getDefault() {
        long id = ServiceLocator.getInstance().getSharedPreferences()
                                .getLong(PK_BOOKSHELF_DEFAULT, 1);
        final Optional<Bookshelf> bookshelf = findById(id);
        if (bookshelf.isPresent()) {
            return bookshelf.get();
        }
        // We should never get here... flw.
        // But if we do, find the smallest id (i.e. oldest added).
        // We trust we will ALWAYS have at least one shelf. If not, then we are in deep trouble.
        id = getAll().stream()
                     .map(Bookshelf::getId)
                     .sorted()
                     .findFirst()
                     .orElseThrow();
        setDefault(id);
        return findById(id).orElseThrow();
    }

    @Override
    public void setDefault(@NonNull final Bookshelf bookshelf) {
        setDefault(bookshelf.getId());
    }

    private void setDefault(final long id) {
        ServiceLocator.getInstance().getSharedPreferences()
                      .edit()
                      .putLong(PK_BOOKSHELF_DEFAULT, id)
                      .apply();
    }

    @Override
    @NonNull
    public Optional<Bookshelf> getCurrent() {
        final String name = ServiceLocator.getInstance().getSharedPreferences()
                                          .getString(PK_BOOKSHELF_CURRENT, null);
        if (name != null && !name.isEmpty()) {
            return findByName(name);
        }
        return Optional.empty();
    }

    @Override
    public void setCurrent(@NonNull final Bookshelf bookshelf) {
        ServiceLocator.getInstance().getSharedPreferences()
                      .edit()
                      .putString(PK_BOOKSHELF_CURRENT, bookshelf.getName())
                      .apply();
    }

    @NonNull
    public Optional<Bookshelf> getBookshelf(@NonNull final Context context,
                                            final long id) {
        if (id == Bookshelf.ALL_BOOKS) {
            return getAllBooksBookshelf(context);
        } else {
            return findById(id);
        }
    }

    @NonNull
    @Override
    public Optional<Bookshelf> findById(final long id) {
        if (id == 0) {
            return Optional.empty();
        }
        // Search for both positive and negative id's
        // (although the only negative id in use is the ALL_BOOKS bookshelf)
        try (Cursor cursor = db.rawQuery(Sql.FIND_BY_ID, new String[]{String.valueOf(id)})) {
            if (cursor.moveToFirst()) {
                return Optional.of(new Bookshelf(id, new CursorRow(cursor)));
            } else {
                return Optional.empty();
            }
        }
    }

    @Override
    @NonNull
    public Optional<Bookshelf> findByName(@NonNull final Context context,
                                          @NonNull final Bookshelf bookshelf,
                                          @NonNull final Locale locale) {
        return findByName(bookshelf.getName());
    }

    @Override
    @NonNull
    public Optional<Bookshelf> findByName(@NonNull final String name) {

        try (Cursor cursor = db.rawQuery(Sql.FIND_BY_NAME, new String[]{name})) {
            if (cursor.moveToFirst()) {
                final CursorRow rowData = new CursorRow(cursor);
                return Optional.of(new Bookshelf(rowData.getLong(DBKey.PK_ID), rowData));
            } else {
                return Optional.empty();
            }
        }
    }

    @Override
    @NonNull
    public List<Bookshelf> getAll() {
        final List<Bookshelf> list = new ArrayList<>();
        try (Cursor cursor = db.rawQuery(Sql.SELECT_ALL_ORDERED_BY_NAME, null)) {
            final CursorRow rowData = new CursorRow(cursor);
            while (cursor.moveToNext()) {
                list.add(new Bookshelf(rowData.getLong(DBKey.PK_ID), rowData));
            }
        }
        return list;
    }

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

    @Override
    public int countBooks(@NonNull final Bookshelf bookshelf) {
        try (SynchronizedStatement stmt = db.compileStatement(Sql.COUNT_BOOKS)) {
            stmt.bindLong(1, bookshelf.getId());
            return (int) stmt.simpleQueryForLongOrZero();
        }
    }

    @NonNull
    @Override
    public List<PFilter<?>> getFilters(final long bookshelfId) {
        final List<PFilter<?>> list = new ArrayList<>();
        try (Cursor cursor = db.rawQuery(Sql.FIND_FILTERS_BY_BOOKSHELF_ID,
                                         new String[]{String.valueOf(bookshelfId)})) {
            final CursorRow rowData = new CursorRow(cursor);
            while (cursor.moveToNext()) {
                final String dbKey = rowData.getString(DBKey.BOOKSHELF.FILTER_NAME);
                final String value = rowData.getString(DBKey.BOOKSHELF.FILTER_VALUE, null);
                // setPersistedValue accepts null values, but is there any point using null?
                if (value != null) {
                    final PFilter<?> filter = FilterFactory.createFilter(dbKey);
                    if (filter != null) {
                        filter.setPersistedValue(value);
                        list.add(filter);
                    }
                }
            }
        }
        return list;
    }

    @Override
    public void validate(@NonNull final Context context)
            throws DaoInsertException,
                   DaoUpdateException {
        final Locale userLocale = context.getResources().getConfiguration().getLocales().get(0);

        for (final Bookshelf bookshelf : getAll()) {
            if (validateFilters(context, bookshelf) || validateStyle(bookshelf)) {
                update(context, bookshelf, userLocale);
            }
        }
    }

    /**
     * Validate the Style for the given Bookshelf.
     * Does NOT alter the database.
     *
     * @param bookshelf to validate
     *
     * @return {@code true} if the Bookshelf was modified
     */
    private boolean validateStyle(@NonNull final Bookshelf bookshelf) {
        final String uuid = bookshelf.getStyleUuid();
        // Resolving the style might change it (i.e. a different UUID)
        final Style style = bookshelf.getStyle();
        return !uuid.equals(style.getUuid());
    }

    /**
     * Validate the Filters for the given Bookshelf.
     * Does NOT alter the database.
     *
     * @param context   Current context
     * @param bookshelf to validate
     *
     * @return {@code true} if the Bookshelf was modified
     */
    private boolean validateFilters(@NonNull final Context context,
                                    @NonNull final Bookshelf bookshelf) {
        final List<PFilter<?>> filters = bookshelf.getFilters();
        if (!filters.isEmpty()) {
            final List<PFilter<?>> keepFilters = filters
                    .stream()
                    .map(filter -> validateFilter(context, filter))
                    .flatMap(Optional::stream)
                    .collect(Collectors.toList());

            if (!keepFilters.equals(filters)) {
                bookshelf.setFilters(keepFilters);
                return true;
            }
        }
        return false;
    }

    /**
     * Validate the given filter.
     * Does NOT alter the database.
     *
     * @param context Current context
     * @param filter  to validate
     *
     * @return the potentially 'fixed' filter, or {@code Optional.empty()}
     *         if the filter <strong>should</strong> be dropped
     */
    @NonNull
    private Optional<PFilter<?>> validateFilter(@NonNull final Context context,
                                                @NonNull final PFilter<?> filter) {
        // A filter based on tags might contain previously deleted tag ids.
        if (DBKey.FK_TAG.equals(filter.getDBKey())) {
            final Set<Long> validTagIds = ServiceLocator.getInstance()
                                                        .getTagDao()
                                                        .getAll()
                                                        .stream()
                                                        .map(Tag::getId)
                                                        .collect(Collectors.toSet());
            //noinspection unchecked,TypeMayBeWeakened
            final PEntityListFilter<Tag> tagFilter = (PEntityListFilter<Tag>) filter;
            // collect valid tags
            final Set<Long> keepTags = tagFilter.getValue()
                                                .stream()
                                                .filter(validTagIds::contains)
                                                .collect(Collectors.toSet());
            if (keepTags.isEmpty()) {
                return Optional.empty();
            } else {
                tagFilter.setValue(context, keepTags);
                return Optional.of(filter);
            }
        } else {
            // all other filters are fine
            return Optional.of(filter);
        }
    }

    /**
     * Store the <strong>active filter</strong>.
     *
     * @param bookshelf to store the filters of
     *
     * @throws DaoInsertException on failure
     */
    private void storeFilters(@NonNull final Bookshelf bookshelf)
            throws DaoInsertException {

        // prune the filters so we only keep the active ones
        final List<PFilter<?>> list = bookshelf.pruneFilters();
        final long bookshelfId = bookshelf.getId();

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

            // Delete all existing ones.
            try (SynchronizedStatement stmt = db.compileStatement(
                    Sql.DELETE_FILTERS_BY_BOOKSHELF_ID)) {
                stmt.bindLong(1, bookshelfId);
                stmt.executeUpdateDelete();
            }

            // is there anything to insert ?
            if (list.isEmpty()) {
                return;
            }

            try (SynchronizedStatement stmt = db.compileStatement(Sql.INSERT_FILTER)) {
                for (final PFilter<?> filter : list) {
                    stmt.bindLong(1, bookshelfId);
                    stmt.bindString(2, filter.getDBKey());
                    stmt.bindString(3, filter.getPersistedValue());
                    if (stmt.executeInsert() == -1) {
                        throw new DaoInsertException(ERROR_INSERT_FROM + filter);
                    }
                }
            }

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

    @Override
    public boolean pruneList(@NonNull final Context context,
                             @NonNull final Collection<Bookshelf> list,
                             @NonNull final BiConsumer<Bookshelf, Locale> idFixer) {
        // Reminder: only abort if empty. We rely on 'fixId' being called for ALL list values.
        if (list.isEmpty()) {
            return false;
        }

        final Locale userLocale = context.getResources().getConfiguration().getLocales().get(0);
        final EntityMergeHelper<Bookshelf> mergeHelper = new EntityMergeHelper<>();
        return mergeHelper.merge(context, list, current -> userLocale, idFixer);
    }

    @Override
    public void fixId(@NonNull final Context context,
                      @NonNull final Bookshelf bookshelf,
                      @NonNull final Locale locale) {
        final long found = findByName(context, bookshelf, locale)
                .map(Bookshelf::getId).orElse(0L);
        bookshelf.setId(found);
    }

    @Override
    public void refresh(@NonNull final Context context,
                        @NonNull final Bookshelf bookshelf,
                        @NonNull final Locale locale) {

        // If needed, check if we already have it in the database.
        if (bookshelf.getId() == 0) {
            fixId(context, bookshelf, locale);
        }

        // If we do already have it, update the object
        if (bookshelf.getId() > 0) {
            final Optional<Bookshelf> dbBookshelf = findById(bookshelf.getId());
            // Sanity check
            if (dbBookshelf.isPresent()) {
                // copy any updated fields
                bookshelf.copyFrom(dbBookshelf.get());
            } else {
                // we shouldn't get here... but if we do, set it to 'new'
                bookshelf.setId(0);
            }
        }

        // Validates, but does not update the database at this point.
        validateStyle(bookshelf);
        validateFilters(context, bookshelf);
    }

    @Override
    public void insertOrUpdate(@NonNull final Context context,
                               @IntRange(from = 1) final long bookId,
                               @NonNull final Collection<Bookshelf> list)
            throws DaoInsertException {

        if (BuildConfig.DEBUG /* always */) {
            if (!db.inTransaction()) {
                throw new TransactionException(TransactionException.REQUIRED);
            }
        }

        // fix id's and remove duplicates; shelves don't use a Locale, hence no lookup done.
        pruneList(context, list);

        // Just delete all current links; we'll insert them from scratch.
        try (SynchronizedStatement stmt1 = db.compileStatement(Sql.DELETE_BOOK_LINKS_BY_BOOK_ID)) {
            stmt1.bindLong(1, bookId);
            stmt1.executeUpdateDelete();
        }

        // is there anything to insert ?
        if (list.isEmpty()) {
            return;
        }

        final Locale userLocale = context.getResources().getConfiguration().getLocales().get(0);
        try (SynchronizedStatement stmt = db.compileStatement(Sql.INSERT_BOOK_LINK)) {
            for (final Bookshelf bookshelf : list) {
                // create if needed - do NOT do updates here
                if (bookshelf.getId() == 0) {
                    insert(context, bookshelf, userLocale);
                }
                //2023-06-11: If we ever do updates here, then we need to check the triggers!
                // also: look at AuthorDaoImpl/PublisherDaoImpl how we avoid unneeded updates

                stmt.bindLong(1, bookId);
                stmt.bindLong(2, bookshelf.getId());
                if (stmt.executeInsert() == -1) {
                    throw new DaoInsertException("insert Book-Bookshelf");
                }
            }
        }
    }

    @Override
    @IntRange(from = 1)
    public long insert(@NonNull final Context context,
                       @NonNull final Bookshelf bookshelf,
                       @NonNull final Locale locale)
            throws DaoInsertException {

        // validate the style first
        final long styleId = bookshelf.getStyle().getId();

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

            final TopRowListPosition topRowListPosition = bookshelf.getTopRowPosition();
            final long iId;
            try (SynchronizedStatement stmt = db.compileStatement(Sql.INSERT)) {
                stmt.bindString(1, bookshelf.getName());
                stmt.bindLong(2, styleId);
                stmt.bindLong(3, topRowListPosition.getAdapterPosition());
                stmt.bindLong(4, topRowListPosition.getViewOffset());
                iId = stmt.executeInsert();
            }

            if (iId != -1) {
                bookshelf.setId(iId);
                storeFilters(bookshelf);

                if (txLock != null) {
                    db.setTransactionSuccessful();
                }
                return iId;
            }
        } catch (@NonNull final DaoInsertException e) {
            bookshelf.setId(0);
            throw e;
        } finally {
            if (txLock != null) {
                db.endTransaction(txLock);
            }
        }
        // The insert failed with -1
        bookshelf.setId(0);
        throw new DaoInsertException(ERROR_INSERT_FROM + bookshelf);
    }

    @Override
    public void update(@NonNull final Context context,
                       @NonNull final Bookshelf bookshelf,
                       @NonNull final Locale locale)
            throws DaoUpdateException, DaoInsertException {

        // validate the style first
        final long styleId = bookshelf.getStyle().getId();

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

            final TopRowListPosition topRowListPosition = bookshelf.getTopRowPosition();
            final int rowsAffected;
            try (SynchronizedStatement stmt = db.compileStatement(Sql.UPDATE)) {
                stmt.bindString(1, bookshelf.getName());
                stmt.bindLong(2, styleId);
                stmt.bindLong(3, topRowListPosition.getAdapterPosition());
                stmt.bindLong(4, topRowListPosition.getViewOffset());

                stmt.bindLong(5, bookshelf.getId());
                rowsAffected = stmt.executeUpdateDelete();
            }

            if (rowsAffected > 0) {
                storeFilters(bookshelf);

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

            throw new DaoUpdateException(ERROR_UPDATE_FROM + bookshelf);
        } finally {
            if (txLock != null) {
                db.endTransaction(txLock);
            }
        }
    }

    @Override
    public boolean delete(@NonNull final Context context,
                          @NonNull final Bookshelf bookshelf) {
        // Sanity check; we cannot delete 0==new; or -1==all_books,
        // and we're not allowed to delete the default shelf
        // The latter is normally prevented in the UI, but paranoia...
        if (bookshelf.getId() <= 0 || bookshelf.getId() == getDefault().getId()) {
            return false;
        }

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

            purgeNodeStates(bookshelf.getId());

            final int rowsAffected;
            try (SynchronizedStatement stmt = db.compileStatement(Sql.DELETE_BY_ID)) {
                stmt.bindLong(1, bookshelf.getId());
                rowsAffected = stmt.executeUpdateDelete();
            }
            if (rowsAffected > 0) {
                bookshelf.setId(0);

                if (txLock != null) {
                    db.setTransactionSuccessful();
                }
                return true;
            }
            return false;
        } catch (@NonNull final DaoUpdateException e) {
            return false;
        } finally {
            if (txLock != null) {
                db.endTransaction(txLock);
            }
        }
    }

    @Override
    public int moveBooks(@NonNull final Context context,
                         @NonNull final Bookshelf source,
                         @NonNull final Bookshelf target)
            throws DaoInsertException {

        int booksMoved;

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

            // We don't hold 'position' for bookshelves,
            // but a bulk update can fail if it creates duplicates.
            if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.R) {
                // UPDATE OR REPLACE is supported... just do a mass update
                try (SynchronizedStatement stmt = db.compileStatement(Sql.BULK_UPDATE_BOOKSHELF)) {
                    stmt.bindLong(1, target.getId());
                    stmt.bindLong(2, source.getId());
                    booksMoved = stmt.executeUpdateDelete();
                }
            } else {
                // manual labour...
                final List<Long> bookIds = getBookIds(source.getId());
                booksMoved = bookIds.size();
                for (final long bookId : bookIds) {
                    final Book book = Book.from(bookId);

                    final List<Bookshelf> fromBook = book.getBookshelves();
                    final List<Bookshelf> destList = new ArrayList<>();

                    for (final Bookshelf item : fromBook) {
                        if (source.getId() == item.getId()) {
                            destList.add(target);
                            // We could 'break' here as there should be no duplicates,
                            // but paranoia...
                        } else {
                            // just keep/copy
                            destList.add(item);
                        }
                    }
                    // delete old links and store all new links
                    // We KNOW there are no updates needed.
                    insertOrUpdate(context, bookId, destList);
                }
            }

            // delete the obsolete source.
            delete(context, source);

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

        return booksMoved;
    }

    @Override
    public void purgeNodeStates(final long bookshelfId)
            throws DaoUpdateException {
        try (SynchronizedStatement stmt = db
                .compileStatement(Sql.DELETE_NODE_STATE_BY_BOOKSHELF_ID)) {
            stmt.bindLong(1, bookshelfId);
            stmt.executeUpdateDelete();
        }
    }

    @NonNull
    @Override
    public List<Long> getBookIds(final long bookshelfId) {
        final List<Long> list = new ArrayList<>();
        try (Cursor cursor = db.rawQuery(Sql.FIND_BOOK_IDS_BY_BOOKSHELF_ID,
                                         new String[]{String.valueOf(bookshelfId)})) {
            while (cursor.moveToNext()) {
                list.add(cursor.getLong(0));
            }
        }
        return list;
    }

    @Override
    @NonNull
    public List<Bookshelf> getByBookId(@IntRange(from = 1) final long bookId) {
        final List<Bookshelf> list = new ArrayList<>();
        try (Cursor cursor = db.rawQuery(Sql.FIND_BY_BOOK_ID,
                                         new String[]{String.valueOf(bookId)})) {
            final CursorRow rowData = new CursorRow(cursor);
            while (cursor.moveToNext()) {
                list.add(new Bookshelf(rowData.getLong(DBKey.PK_ID), rowData));
            }
            return list;
        }
    }

    private static final class Sql {
        /** Insert a {@link Bookshelf}. */
        static final String INSERT =
                INSERT_INTO_ + TBL_BOOKSHELF.getName()
                + '(' + DBKey.BOOKSHELF.NAME
                + ',' + DBKey.FK_STYLE
                + ',' + DBKey.BOOKSHELF.BL_TOP_POS
                + ',' + DBKey.BOOKSHELF.BL_TOP_OFFSET
                + ") VALUES (?,?,?,?)";

        /** Update a {@link Bookshelf}. */
        static final String UPDATE =
                UPDATE_ + TBL_BOOKSHELF.getName()
                + _SET_ + DBKey.BOOKSHELF.NAME + "=?"
                + ',' + DBKey.FK_STYLE + "=?"
                + ',' + DBKey.BOOKSHELF.BL_TOP_POS + "=?"
                + ',' + DBKey.BOOKSHELF.BL_TOP_OFFSET + "=?"
                + _WHERE_ + DBKey.PK_ID + "=?";

        /** Delete a {@link Bookshelf}. */
        static final String DELETE_BY_ID =
                DELETE_FROM_ + TBL_BOOKSHELF.getName()
                + _WHERE_ + DBKey.PK_ID + "=?";

        /** Insert the link between a {@link Book} and a {@link Bookshelf}. */
        static final String INSERT_BOOK_LINK =
                INSERT_INTO_ + TBL_BOOK_BOOKSHELF.getName()
                + '(' + DBKey.FK_BOOK
                + ',' + DBKey.FK_BOOKSHELF
                + ") VALUES (?,?)";

        /**
         * Delete the link between a {@link Book} and a {@link Bookshelf}.
         * <p>
         * This is done when a book is updated; first delete all links, then re-create them.
         */
        static final String DELETE_BOOK_LINKS_BY_BOOK_ID =
                DELETE_FROM_ + TBL_BOOK_BOOKSHELF.getName() + _WHERE_ + DBKey.FK_BOOK + "=?";

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

        static final String COUNT_BOOKS =
                SELECT_COUNT_FROM_ + TBL_BOOK_BOOKSHELF.as()
                + _WHERE_ + TBL_BOOK_BOOKSHELF.dot(DBKey.FK_BOOKSHELF) + "=?";

        /** A list of all {@link Bookshelf}s, unordered. Joined with the styles table. */
        static final String SELECT_ALL =
                SELECT_ + TBL_BOOKSHELF.dotAs(DBKey.PK_ID,
                                              DBKey.BOOKSHELF.NAME,
                                              DBKey.BOOKSHELF.BL_TOP_POS,
                                              DBKey.BOOKSHELF.BL_TOP_OFFSET,
                                              DBKey.FK_STYLE)
                + ',' + TBL_BOOKLIST_STYLES.dotAs(DBKey.STYLE.UUID)
                + _FROM_ + TBL_BOOKSHELF.startJoin(TBL_BOOKLIST_STYLES);

        /**
         * Get a list of all {@link Bookshelf} ordered by name.
         * Do NOT include the 'All Books' (with id=-1).
         */
        static final String SELECT_ALL_ORDERED_BY_NAME =
                SELECT_ALL
                + _WHERE_ + TBL_BOOKSHELF.dot(DBKey.PK_ID) + ">0"
                + _ORDER_BY_ + DBKey.BOOKSHELF.NAME + _COLLATION;

        /** Find a {@link Bookshelf} by its id. Joined with the styles table. */
        static final String FIND_BY_ID =
                SELECT_ALL + _WHERE_ + TBL_BOOKSHELF.dot(DBKey.PK_ID) + "=?";

        /**
         * Find a {@link Bookshelf} by name; Joined with the styles table.
         * The lookup is by EQUALITY and CASE-SENSITIVE.
         */
        static final String FIND_BY_NAME =
                SELECT_ALL
                + _WHERE_ + TBL_BOOKSHELF.dot(DBKey.BOOKSHELF.NAME) + "=?" + _COLLATION;

        /**
         * All {@link Bookshelf}s for a {@link Book}.
         * Ordered by name.
         */
        static final String FIND_BY_BOOK_ID =
                SELECT_DISTINCT_ + TBL_BOOKSHELF.dotAs(DBKey.PK_ID,
                                                       DBKey.BOOKSHELF.NAME,
                                                       DBKey.BOOKSHELF.BL_TOP_POS,
                                                       DBKey.BOOKSHELF.BL_TOP_OFFSET,
                                                       DBKey.FK_STYLE)
                + ',' + TBL_BOOKLIST_STYLES.dotAs(DBKey.STYLE.UUID)

                + _FROM_ + TBL_BOOK_BOOKSHELF.startJoin(TBL_BOOKSHELF, TBL_BOOKLIST_STYLES)
                + _WHERE_ + TBL_BOOK_BOOKSHELF.dot(DBKey.FK_BOOK) + "=?"
                + _ORDER_BY_ + TBL_BOOKSHELF.dot(DBKey.BOOKSHELF.NAME) + _COLLATION;


        /** All {@link Book}s (id only!) for a given {@link Bookshelf}. */
        static final String FIND_BOOK_IDS_BY_BOOKSHELF_ID =
                SELECT_ + TBL_BOOK_BOOKSHELF.dotAs(DBKey.FK_BOOK)
                + _FROM_ + TBL_BOOK_BOOKSHELF.as()
                + _WHERE_ + TBL_BOOK_BOOKSHELF.dot(DBKey.FK_BOOKSHELF) + "=?";

        static final String DELETE_NODE_STATE_BY_BOOKSHELF_ID =
                DELETE_FROM_ + TBL_BOOK_LIST_NODE_STATE.getName()
                + _WHERE_ + DBKey.FK_BOOKSHELF + "=?";

        static final String INSERT_FILTER =
                INSERT_INTO_ + TBL_BOOKSHELF_FILTERS.getName()
                + '(' + DBKey.FK_BOOKSHELF
                + ',' + DBKey.BOOKSHELF.FILTER_NAME
                + ',' + DBKey.BOOKSHELF.FILTER_VALUE
                + ") VALUES (?,?,?)";

        static final String DELETE_FILTERS_BY_BOOKSHELF_ID =
                DELETE_FROM_ + TBL_BOOKSHELF_FILTERS.getName()
                + _WHERE_ + DBKey.FK_BOOKSHELF + "=?";

        static final String FIND_FILTERS_BY_BOOKSHELF_ID =
                SELECT_ + DBKey.BOOKSHELF.FILTER_NAME + ',' + DBKey.BOOKSHELF.FILTER_VALUE
                + _FROM_ + TBL_BOOKSHELF_FILTERS.getName()
                + _WHERE_ + DBKey.FK_BOOKSHELF + "=?";

        /**
         * Bulk update/replace one bookshelf id with another;
         * effectively moving books from one bookshelf to the other.
         */
        @RequiresApi(Build.VERSION_CODES.R)
        static final String BULK_UPDATE_BOOKSHELF =
                UPDATE_OR_REPLACE_ + TBL_BOOK_BOOKSHELF.getName()
                + _SET_ + DBKey.FK_BOOKSHELF + "=?"
                + _WHERE_ + DBKey.FK_BOOKSHELF + "=?";
    }
}
