package me.devsaki.hentoid.database;

import static com.annimon.stream.Collectors.toList;

import android.content.Context;
import android.util.SparseIntArray;

import androidx.annotation.NonNull;

import com.annimon.stream.Collectors;
import com.annimon.stream.Stream;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.tuple.ImmutablePair;
import org.threeten.bp.Instant;

import java.util.ArrayList;
import java.util.Collections;
import java.util.EnumMap;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;

import javax.annotation.Nullable;

import io.objectbox.Box;
import io.objectbox.BoxStore;
import io.objectbox.Property;
import io.objectbox.android.AndroidObjectBrowser;
import io.objectbox.query.Query;
import io.objectbox.query.QueryBuilder;
import io.objectbox.query.QueryCondition;
import io.objectbox.relation.RealmResults;
import io.realm.Realm;
import me.devsaki.hentoid.BuildConfig;
import me.devsaki.hentoid.core.Consts;
import me.devsaki.hentoid.database.domains.Attribute;
import me.devsaki.hentoid.database.domains.AttributeLocation;
import me.devsaki.hentoid.database.domains.AttributeMap;
import me.devsaki.hentoid.database.domains.Attribute_;
import me.devsaki.hentoid.database.domains.Chapter;
import me.devsaki.hentoid.database.domains.Chapter_;
import me.devsaki.hentoid.database.domains.Content;
import me.devsaki.hentoid.database.domains.Content_;
import me.devsaki.hentoid.database.domains.ErrorRecord;
import me.devsaki.hentoid.database.domains.ErrorRecord_;
import me.devsaki.hentoid.database.domains.Group;
import me.devsaki.hentoid.database.domains.GroupItem;
import me.devsaki.hentoid.database.domains.GroupItem_;
import me.devsaki.hentoid.database.domains.Group_;
import me.devsaki.hentoid.database.domains.ImageFile;
import me.devsaki.hentoid.database.domains.ImageFile_;
import me.devsaki.hentoid.database.domains.MyObjectBox;
import me.devsaki.hentoid.database.domains.QueueRecord;
import me.devsaki.hentoid.database.domains.QueueRecord_;
import me.devsaki.hentoid.database.domains.SearchRecord;
import me.devsaki.hentoid.database.domains.ShuffleRecord;
import me.devsaki.hentoid.database.domains.SiteBookmark;
import me.devsaki.hentoid.database.domains.SiteBookmark_;
import me.devsaki.hentoid.database.domains.SiteHistory;
import me.devsaki.hentoid.database.domains.SiteHistory_;
import me.devsaki.hentoid.enums.AttributeType;
import me.devsaki.hentoid.enums.Grouping;
import me.devsaki.hentoid.enums.Site;
import me.devsaki.hentoid.enums.StatusContent;
import me.devsaki.hentoid.util.file.ArchiveHelper;
import me.devsaki.hentoid.util.ContentHelper;
import me.devsaki.hentoid.util.Helper;
import me.devsaki.hentoid.util.Preferences;
import me.devsaki.hentoid.util.RandomSeedSingleton;
import me.devsaki.hentoid.widget.ContentSearchManager;
import timber.log.Timber;

public class ObjectBoxDB {

    // Status displayed in the library view (all books of the library; both internal and external)
    private static final int[] libraryStatus = ContentHelper.getLibraryStatuses();
    private static final int[] queueStatus = ContentHelper.getQueueStatuses();
    private static final int[] libraryQueueStatus = ArrayUtils.addAll(libraryStatus, queueStatus);

    private static final long DAY_IN_MILLIS = 1000L * 60 * 60 * 24;

    private static ObjectBoxDB instance;

    private static Realm realm;

    // Cached queries
    private final Query<Content> contentFromAttributesSearchQ;
    private final Query<Content> contentFromSourceSearchQ;

    private ObjectBoxDB(Context context) {
        realm = Realm.getDefaultInstance();

        // Pre-cache intensive search queries
        contentFromAttributesSearchQ = buildContentFromAttributesSearchQ();
        contentFromSourceSearchQ = buildContentFromSourceSearchQ();
    }

    // For testing (store generated by the test framework)
    private ObjectBoxDB(Realm realm) {
        this.realm = realm;
        // Pre-cache intensive search queries
        contentFromAttributesSearchQ = buildContentFromAttributesSearchQ();
        contentFromSourceSearchQ = buildContentFromSourceSearchQ();
    }

    private Query<Content> buildContentFromAttributesSearchQ() {
        final QueryBuilder<Content> contentFromAttributesQueryBuilder = store.boxFor(Content.class).query();
        contentFromAttributesQueryBuilder.in(Content_.status, libraryStatus);

        contentFromAttributesQueryBuilder.link(Content_.attributes)
                .equal(Attribute_.type, 0)
                .equal(Attribute_.name, "", QueryBuilder.StringOrder.CASE_INSENSITIVE);
        return contentFromAttributesQueryBuilder.build();
    }

    private Query<Content> buildContentFromSourceSearchQ() {
        final QueryBuilder<Content> contentFromSourceQueryBuilder = store.boxFor(Content.class).query();
        contentFromSourceQueryBuilder.in(Content_.status, libraryStatus);
        contentFromSourceQueryBuilder.equal(Content_.site, 1);

        return contentFromSourceQueryBuilder.build();
    }


    // Use this to get db instance
    public static synchronized ObjectBoxDB getInstance(Context context) {
        // Use application context only
        if (instance == null) {
            instance = new ObjectBoxDB(context);
        }
        return instance;
    }

    // Use this to get db instance for testing (store generated by the test framework)
    public static synchronized ObjectBoxDB getInstance(Realm realm) {
        // Use application context only
        if (instance == null) {
            instance = new ObjectBoxDB(realm);
        }
        return instance;
    }


    void closeThreadResources() {
    }

    long getDbSizeBytes() {
        return 1;
    }

    void insertContent(Content content) {
        realm.executeTransaction(r -> {
            r.insertOrUpdate(content);
        });
    }

    void insertContentCore(@NonNull Content content) {
        insertContent(content);
    }

    // Faster alternative to insertContent when Content fields only need to be updated
    void updateContentObject(Content content) {
        insertContent(content);
    }

    void updateContentStatus(@NonNull final StatusContent updateFrom, @NonNull final StatusContent updateTo) {
        List<Content> contentList = selectContentByStatus(updateFrom);
        for (Content c : contentList) c.setStatus(updateTo);
        realm.executeTransaction(r -> {
            r.insertOrUpdate(contentList);
        });
    }

    List<Content> selectContentByStatus(StatusContent status) {
        return selectContentByStatusCodes(new int[]{status.getCode()});
    }

    private List<Content> selectContentByStatusCodes(int[] statusCodes) {
        return store.boxFor(Content.class).query().in(Content_.status, statusCodes).build().find();
    }

    Query<Content> selectAllInternalBooksQ(boolean favsOnly, boolean includePlaceholders) {
        // All statuses except SAVED, DOWNLOADING, PAUSED and ERROR that imply the book is in the download queue
        // and EXTERNAL because we only want to manage internal books here
        int[] storedContentStatus = {
                StatusContent.DOWNLOADED.getCode(),
                StatusContent.MIGRATED.getCode(),
                StatusContent.IGNORED.getCode(),
                StatusContent.UNHANDLED_ERROR.getCode(),
                StatusContent.CANCELED.getCode()
        };
        if (includePlaceholders)
            storedContentStatus = ArrayUtils.addAll(storedContentStatus, StatusContent.PLACEHOLDER.getCode());
        QueryBuilder<Content> query = store.boxFor(Content.class).query().in(Content_.status, storedContentStatus);
        if (favsOnly) query.equal(Content_.favourite, true);
        return query.build();
    }

    Query<Content> selectAllExternalBooksQ() {
        return store.boxFor(Content.class).query().equal(Content_.status, StatusContent.EXTERNAL.getCode()).build();
    }

    Query<Content> selectAllErrorJsonBooksQ() {
        return store.boxFor(Content.class).query().equal(Content_.status, StatusContent.ERROR.getCode()).notNull(Content_.jsonUri).notEqual(Content_.jsonUri, "", QueryBuilder.StringOrder.CASE_INSENSITIVE).build();
    }

    Query<Content> selectAllQueueBooksQ() {
        return store.boxFor(Content.class).query().in(Content_.status, ContentHelper.getQueueStatuses()).build();
    }

    Query<Content> selectAllFlaggedBooksQ() {
        return store.boxFor(Content.class).query().equal(Content_.isFlaggedForDeletion, true).build();
    }

    Query<Content> selectAllMarkedBooksQ() {
        return store.boxFor(Content.class).query().equal(Content_.isBeingDeleted, true).build();
    }

    void flagContentsForDeletion(List<Content> contentList, boolean flag) {
        for (Content c : contentList) c.setFlaggedForDeletion(flag);
        store.boxFor(Content.class).put(contentList);
    }

    void markContentsAsBeingDeleted(List<Content> contentList, boolean flag) {
        for (Content c : contentList) c.setIsBeingDeleted(flag);
        store.boxFor(Content.class).put(contentList);
    }

    void deleteContentById(long contentId) {
        deleteContentById(new long[]{contentId});
    }

    /**
     * Remove the given content and all related objects from the DB
     * NB : ObjectBox v2.3.1 does not support cascade delete, so everything has to be done manually
     *
     * @param contentId IDs of the contents to be removed from the DB
     */
    void deleteContentById(long[] contentId) {
        Box<ErrorRecord> errorBox = store.boxFor(ErrorRecord.class);
        Box<ImageFile> imageFileBox = store.boxFor(ImageFile.class);
        Box<Chapter> chapterBox = store.boxFor(Chapter.class);
        Box<Content> contentBox = store.boxFor(Content.class);
        Box<GroupItem> groupItemBox = store.boxFor(GroupItem.class);
        Box<Group> groupBox = store.boxFor(Group.class);

        for (long id : contentId) {
            Content c = contentBox.get(id);
            if (c != null) {
                store.runInTx(() -> {
                    if (c.getImageFiles() != null) {
                        imageFileBox.remove(c.getImageFiles());
                        c.getImageFiles().clear();                                      // Clear links to all imageFiles
                    }

                    if (c.getChapters() != null) {
                        chapterBox.remove(c.getChapters());
                        c.getChapters().clear();                                      // Clear links to all chapters
                    }

                    if (c.getErrorLog() != null) {
                        errorBox.remove(c.getErrorLog());
                        c.getErrorLog().clear();                                    // Clear links to all errorRecords
                    }

                    // Clear links to all attributes
                    // NB : Properly removing all attributes here is too costly, especially on large collections
                    // It's done by calling cleanupOrphanAttributes
                    c.getAttributes().clear();

                    // Delete corresponding groupItem
                    List<GroupItem> groupItems = groupItemBox.query().equal(GroupItem_.contentId, id).build().find();
                    for (GroupItem groupItem : groupItems) {
                        // If we're not in the Custom grouping and it's the only item of its group, delete the group
                        Group g = groupItem.group.getTarget();
                        if (g != null && !g.grouping.equals(Grouping.CUSTOM) && g.items.size() < 2)
                            groupBox.remove(g);
                        // Delete the item
                        groupItemBox.remove(groupItem);
                    }

                    contentBox.remove(c);                                           // Remove the content itself
                });
            }
        }
    }

    /**
     * Cleanup all Attributes that don't have any backlink among content
     */
    public void cleanupOrphanAttributes() {
        Box<Attribute> attributeBox = store.boxFor(Attribute.class);
        Box<AttributeLocation> locationBox = store.boxFor(AttributeLocation.class);

        // Stream the collection to get the attributes to clean
        List<Attribute> attrsToClean = attributeBox.query().filter(attr -> attr.contents.isEmpty()).build().find();

        // Clean the attributes
        for (Attribute attr : attrsToClean) {
            Timber.v(">> Found empty attr : %s", attr.getName());
            locationBox.remove(attr.getLocations());
            attr.getLocations().clear();                                           // Clear location links
            attributeBox.remove(attr);                                             // Delete the attribute itself
        }
    }

    List<Content> selectQueueContents() {
        List<Content> result = new ArrayList<>();
        List<QueueRecord> queueRecords = selectQueueRecordsQ(null).find();
        for (QueueRecord q : queueRecords) result.add(q.getContent().getTarget());
        return result;
    }

    Query<QueueRecord> selectQueueRecordsQ(String query) {
        QueryBuilder<QueueRecord> qb = store.boxFor(QueueRecord.class).query();
        // Universal search inside contents
        if (query != null && !query.isEmpty()) {
            ContentSearchManager.ContentSearchBundle bundle = new ContentSearchManager.ContentSearchBundle();
            bundle.setQuery(query);
            bundle.setSortField(Preferences.Constant.ORDER_FIELD_NONE);
            long[] contentIds = selectContentUniversalId(bundle, ContentHelper.getQueueTabStatuses());
            qb.in(QueueRecord_.contentId, contentIds);
        }
        return qb.order(QueueRecord_.rank).build();
    }

    boolean isContentInQueue(@NonNull final Content c) {
        return store.boxFor(QueueRecord.class).query().equal(QueueRecord_.contentId, c.getId()).build().count() > 0;
    }

    long selectMaxQueueOrder() {
        return store.boxFor(QueueRecord.class).query().build().property(QueueRecord_.rank).max();
    }

    void insertQueue(long contentId, int order) {
        store.boxFor(QueueRecord.class).put(new QueueRecord(contentId, order));
    }

    void updateQueue(@NonNull final List<QueueRecord> queue) {
        Box<QueueRecord> queueRecordBox = store.boxFor(QueueRecord.class);
        queueRecordBox.put(queue);
    }

    void deleteQueue(@NonNull Content content) {
        deleteQueue(content.getId());
    }

    void deleteQueue(int queueIndex) {
        store.boxFor(QueueRecord.class).remove(selectQueueRecordsQ(null).find().get(queueIndex).id);
    }

    void deleteQueue() {
        store.boxFor(QueueRecord.class).removeAll();
    }

    private void deleteQueue(long contentId) {
        Box<QueueRecord> queueRecordBox = store.boxFor(QueueRecord.class);
        QueueRecord record = queueRecordBox.query().equal(QueueRecord_.contentId, contentId).build().findFirst();

        if (record != null) queueRecordBox.remove(record);
    }

    Query<Content> selectVisibleContentQ() {
        ContentSearchManager.ContentSearchBundle bundle = new ContentSearchManager.ContentSearchBundle();
        bundle.setSortField(Preferences.Constant.ORDER_FIELD_NONE);
        return selectContentSearchContentQ(bundle, Collections.emptyList());
    }

    @Nullable
    Content selectContentById(long id) {
        return store.boxFor(Content.class).get(id);
    }

    @Nullable
    List<Content> selectContentById(List<Long> id) {
        return store.boxFor(Content.class).get(id);
    }

    @Nullable
    Content selectContentBySourceAndUrl(@NonNull Site site, @NonNull String contentUrl, @NonNull String coverUrlStart) {
        QueryCondition<Content> urlCondition = Content_.url.notEqual("", QueryBuilder.StringOrder.CASE_INSENSITIVE).and(Content_.url.equal(contentUrl, QueryBuilder.StringOrder.CASE_INSENSITIVE)).and(Content_.site.equal(site.getCode()));
        if (!coverUrlStart.isEmpty()) {
            QueryCondition<Content> coverCondition = Content_.coverImageUrl.notEqual("", QueryBuilder.StringOrder.CASE_INSENSITIVE).and(Content_.coverImageUrl.equal(coverUrlStart, QueryBuilder.StringOrder.CASE_INSENSITIVE)).and(Content_.site.equal(site.getCode()));
            return store.boxFor(Content.class).query(urlCondition.or(coverCondition)).build().findFirst();
        } else return store.boxFor(Content.class).query(urlCondition).build().findFirst();
    }

    Set<String> selectAllContentUrls(int siteCode) {
        Query<Content> allContentQ = store.boxFor(Content.class).query().equal(Content_.site, siteCode).in(Content_.status, libraryStatus).build();
        return new HashSet<>(Stream.of(allContentQ.property(Content_.url).findStrings()).toList());
    }

    Set<String> selectAllMergedContentUrls(Site site) {
        Query<Chapter> allChapterQ = store.boxFor(Chapter.class).query().startsWith(Chapter_.url,site.getUrl(), QueryBuilder.StringOrder.CASE_INSENSITIVE).build();
        return new HashSet<>(Stream.of(allChapterQ.property(Chapter_.url).findStrings()).toList());
    }

    @Nullable
    Content selectContentEndWithStorageUri(@NonNull final String folderUriEnd, boolean onlyFlagged) {
        QueryBuilder<Content> queryBuilder = store.boxFor(Content.class).query().endsWith(Content_.storageUri, folderUriEnd, QueryBuilder.StringOrder.CASE_INSENSITIVE);
        if (onlyFlagged) queryBuilder.equal(Content_.isFlaggedForDeletion, true);

        return queryBuilder.build().findFirst();
    }

    private long[] getIdsFromAttributes(@NonNull List<Attribute> attrs) {
        if (attrs.isEmpty()) return new long[0];

        if (attrs.get(0).isExcluded()) {
            long[] filteredBooks = selectFilteredContent(attrs);

            // Find all content positively matching the given attributes
            // TODO... but the attrs are already negative ^^"
            QueryBuilder<Content> query = store.boxFor(Content.class).query();
            query.in(Content_.status, libraryStatus);
            query.in(Content_.id, filteredBooks);
            List<Content> content = query.build().find();

            // Extract sites from them
            Map<Site, List<Content>> contentPerSite = Stream.of(content).collect(Collectors.groupingBy(Content::getSite));
            Set<Long> filteredSiteCodes = new HashSet<>();
            if (contentPerSite != null) {
                for (Map.Entry<Site, List<Content>> entry : contentPerSite.entrySet()) {
                    Site site = entry.getKey();
                    filteredSiteCodes.add((long) site.getCode());
                }
            }
            List<Long> result = new ArrayList<>(filteredSiteCodes);

            return Helper.getPrimitiveArrayFromList(result);
        } else {
            long[] result = new long[attrs.size()];
            if (!attrs.isEmpty()) {
                int index = 0;
                for (Attribute a : attrs) result[index++] = a.getId();
            }
            return result;
        }
    }

    private void applySortOrder(
            QueryBuilder<Content> query,
            int orderField,
            boolean orderDesc) {
        // Random ordering is tricky (see https://github.com/objectbox/objectbox-java/issues/17)
        // => Implemented post-query build
        if (orderField == Preferences.Constant.ORDER_FIELD_RANDOM) return;
        // Custom ordering depends on another "table"
        // => Implemented post-query build
        if (orderField == Preferences.Constant.ORDER_FIELD_CUSTOM) {
            //query.sort(new Content.GroupItemOrderComparator(groupId)); // doesn't work with PagedList because it uses Query.find(final long offset, final long limit)
            //query.backlink(GroupItem_.content).order(GroupItem_.order); // doesn't work yet (see https://github.com/objectbox/objectbox-java/issues/141)
            return;
        }

        Property<Content> field = getPropertyFromField(orderField);
        if (null == field) return;

        if (orderDesc) query.orderDesc(field);
        else query.order(field);

        // Specifics sub-sorting fields when ordering by reads
        if (orderField == Preferences.Constant.ORDER_FIELD_READS) {
            if (orderDesc) query.orderDesc(Content_.lastReadDate);
            else query.order(Content_.lastReadDate).orderDesc(Content_.downloadDate);
        }
    }

    @Nullable
    private Property<Content> getPropertyFromField(int prefsFieldCode) {
        switch (prefsFieldCode) {
            case Preferences.Constant.ORDER_FIELD_TITLE:
                return Content_.title;
            case Preferences.Constant.ORDER_FIELD_ARTIST:
                return Content_.author; // Might not be what users want when there are multiple authors
            case Preferences.Constant.ORDER_FIELD_NB_PAGES:
                return Content_.qtyPages;
            case Preferences.Constant.ORDER_FIELD_DOWNLOAD_PROCESSING_DATE:
                return Content_.downloadDate;
            case Preferences.Constant.ORDER_FIELD_DOWNLOAD_COMPLETION_DATE:
                return Content_.downloadCompletionDate;
            case Preferences.Constant.ORDER_FIELD_UPLOAD_DATE:
                return Content_.uploadDate;
            case Preferences.Constant.ORDER_FIELD_READ_DATE:
                return Content_.lastReadDate;
            case Preferences.Constant.ORDER_FIELD_READS:
                return Content_.reads;
            case Preferences.Constant.ORDER_FIELD_SIZE:
                return Content_.size;
            case Preferences.Constant.ORDER_FIELD_READ_PROGRESS:
                return Content_.readProgress;
            default:
                return null;
        }
    }

    Query<Content> selectNoContentQ() {
        return store.boxFor(Content.class).query().equal(Content_.id, -1).build();
    }

    Query<Content> selectContentSearchContentQ(
            ContentSearchManager.ContentSearchBundle searchBundle,
            List<Attribute> metadata) {
        if (Preferences.Constant.ORDER_FIELD_CUSTOM == searchBundle.getSortField())
            return store.boxFor(Content.class).query().build();

        AttributeMap metadataMap = new AttributeMap();
        metadataMap.addAll(metadata);

        boolean hasTitleFilter = (!searchBundle.getQuery().isEmpty());
        boolean hasGroupFilter = (searchBundle.getGroupId() > 0);
        List<Attribute> sources = metadataMap.get(AttributeType.SOURCE);
        boolean hasSiteFilter = metadataMap.containsKey(AttributeType.SOURCE)
                && (sources != null)
                && !(sources.isEmpty());
        boolean hasTagFilter = metadataMap.keySet().size() > (hasSiteFilter ? 1 : 0);

        QueryCondition<Content> qc = Content_.status.oneOf(libraryStatus);
        if (hasSiteFilter) qc = qc.and(Content_.site.oneOf(getIdsFromAttributes(sources)));
        if (searchBundle.getFilterBookFavourites()) qc = qc.and(Content_.favourite.equal(true));

        if (searchBundle.getFilterBookCompleted()) qc = qc.and(Content_.completed.equal(true));
        else if (searchBundle.getFilterBookNotCompleted())
            qc = qc.and(Content_.completed.equal(false));

        if (searchBundle.getFilterRating() > -1)
            qc = qc.and(Content_.rating.equal(searchBundle.getFilterRating()));

        if (hasTitleFilter)
            qc = qc.and(Content_.title.contains(searchBundle.getQuery(), QueryBuilder.StringOrder.CASE_INSENSITIVE));

        if (hasTagFilter) {
            for (Map.Entry<AttributeType, List<Attribute>> entry : metadataMap.entrySet()) {
                AttributeType attrType = entry.getKey();
                if (!attrType.equals(AttributeType.SOURCE)) { // Not a "real" attribute in database
                    List<Attribute> attrs = entry.getValue();
                    if (attrs != null && !attrs.isEmpty()) {
                        qc = qc.and(Content_.id.oneOf(selectFilteredContent(attrs)));
                    }
                }
            }
        }
        if (hasGroupFilter) qc = applyContentGroupFilter(qc, searchBundle.getGroupId());

        qc = applyContentLocationFilter(qc, searchBundle.getLocation());
        qc = applyContentTypeFilter(qc, searchBundle.getContentType());

        QueryBuilder<Content> query = store.boxFor(Content.class).query(qc);

        if (searchBundle.getFilterPageFavourites()) filterWithPageFavs(query);

        applySortOrder(query, searchBundle.getSortField(), searchBundle.getSortDesc());
        return query.build();
    }

    long[] selectContentSearchContentByGroupItem(
            ContentSearchManager.ContentSearchBundle searchBundle,
            List<Attribute> metadata) {
        if (searchBundle.getSortField() != Preferences.Constant.ORDER_FIELD_CUSTOM)
            return new long[]{};

        AttributeMap metadataMap = new AttributeMap();
        metadataMap.addAll(metadata);

        boolean hasTitleFilter = (!searchBundle.getQuery().isEmpty());
        boolean hasGroupFilter = (searchBundle.getGroupId() > 0);
        List<Attribute> sources = metadataMap.get(AttributeType.SOURCE);
        boolean hasSiteFilter = metadataMap.containsKey(AttributeType.SOURCE)
                && (sources != null)
                && !(sources.isEmpty());
        boolean hasTagFilter = metadataMap.keySet().size() > (hasSiteFilter ? 1 : 0);

        // Pre-filter and order on GroupItem
        QueryBuilder<GroupItem> query = store.boxFor(GroupItem.class).query();
        if (hasGroupFilter) query.equal(GroupItem_.groupId, searchBundle.getGroupId());

        if (searchBundle.getSortDesc()) query.orderDesc(GroupItem_.order);
        else query.order(GroupItem_.order);

        // Get linked Content
        QueryBuilder<Content> contentQuery = query.link(GroupItem_.content);
        if (hasSiteFilter)
            contentQuery.in(Content_.site, getIdsFromAttributes(sources));
        if (searchBundle.getFilterBookFavourites()) contentQuery.equal(Content_.favourite, true);

        if (searchBundle.getFilterBookCompleted()) contentQuery.equal(Content_.completed, true);
        else if (searchBundle.getFilterBookNotCompleted())
            contentQuery.equal(Content_.completed, false);

        if (searchBundle.getFilterRating() > -1)
            contentQuery.equal(Content_.rating, searchBundle.getFilterRating());

        if (hasTitleFilter)
            contentQuery.contains(Content_.title, searchBundle.getQuery(), QueryBuilder.StringOrder.CASE_INSENSITIVE);
        if (hasTagFilter) {
            for (Map.Entry<AttributeType, List<Attribute>> entry : metadataMap.entrySet()) {
                AttributeType attrType = entry.getKey();
                if (!attrType.equals(AttributeType.SOURCE)) { // Not a "real" attribute in database
                    List<Attribute> attrs = entry.getValue();
                    if (attrs != null && !attrs.isEmpty()) {
                        contentQuery.in(Content_.id, selectFilteredContent(attrs));
                    }
                }
            }
        }
        return Helper.getPrimitiveArrayFromList(Stream.of(query.build().find()).map(gi -> gi.content.getTargetId()).toList());
    }

    private Query<Content> selectContentUniversalAttributesQ(
            ContentSearchManager.ContentSearchBundle searchBundle,
            int[] statuses) {
        QueryBuilder<Content> query = store.boxFor(Content.class).query();
        query.in(Content_.status, statuses);

        if (searchBundle.getFilterBookFavourites()) query.equal(Content_.favourite, true);

        if (searchBundle.getFilterBookCompleted()) query.equal(Content_.completed, true);
        else if (searchBundle.getFilterBookNotCompleted()) query.equal(Content_.completed, false);

        if (searchBundle.getFilterRating() > -1)
            query.equal(Content_.rating, searchBundle.getFilterRating());

        if (searchBundle.getFilterPageFavourites()) filterWithPageFavs(query);
        query.link(Content_.attributes).contains(Attribute_.name, searchBundle.getQuery(), QueryBuilder.StringOrder.CASE_INSENSITIVE);

        if (searchBundle.getGroupId() > 0)
            query.in(Content_.id, selectFilteredContent(searchBundle.getGroupId()));

        return query.build();
    }

    private Query<Content> selectContentUniversalContentQ(
            ContentSearchManager.ContentSearchBundle searchBundle,
            long[] additionalIds,
            int[] statuses) {
        if (Preferences.Constant.ORDER_FIELD_CUSTOM == searchBundle.getSortField())
            return store.boxFor(Content.class).query().build();

        QueryCondition<Content> qc = Content_.status.oneOf(statuses);

        if (searchBundle.getFilterBookFavourites()) qc = qc.and(Content_.favourite.equal(true));

        if (searchBundle.getFilterBookCompleted()) qc = qc.and(Content_.completed.equal(true));
        else if (searchBundle.getFilterBookNotCompleted())
            qc = qc.and(Content_.completed.equal(false));

        if (searchBundle.getFilterRating() > -1)
            qc = qc.and(Content_.rating.equal(searchBundle.getFilterRating()));

        qc = qc.and(
                Content_.title.contains(searchBundle.getQuery(), QueryBuilder.StringOrder.CASE_INSENSITIVE)
                        .or(Content_.uniqueSiteId.equal(searchBundle.getQuery(), QueryBuilder.StringOrder.CASE_INSENSITIVE))
                        .or(Content_.id.oneOf(additionalIds))
        );

        if (searchBundle.getGroupId() > 0) {
            Group group = store.boxFor(Group.class).get(searchBundle.getGroupId());
            if (group.grouping.equals(Grouping.DL_DATE)) // According to days since download date
                qc = applyContentDownloadDateFilter(qc, group.propertyMin, group.propertyMax);
            else // Direct link to group
                qc = qc.and(Content_.id.oneOf(selectFilteredContent(searchBundle.getGroupId())));
        }

        QueryBuilder<Content> query = store.boxFor(Content.class).query(qc);
        if (searchBundle.getFilterPageFavourites()) filterWithPageFavs(query);

        applySortOrder(query, searchBundle.getSortField(), searchBundle.getSortDesc());

        return query.build();
    }

    private long[] selectContentUniversalContentByGroupItem(
            ContentSearchManager.ContentSearchBundle searchBundle,
            long[] additionalIds) {
        if (searchBundle.getSortField() != Preferences.Constant.ORDER_FIELD_CUSTOM)
            return new long[]{};

        // Pre-filter and order on GroupItem
        QueryBuilder<GroupItem> query = store.boxFor(GroupItem.class).query();
        if (searchBundle.getGroupId() > 0)
            query.equal(GroupItem_.groupId, searchBundle.getGroupId());
        if (searchBundle.getSortDesc()) query.orderDesc(GroupItem_.order);
        else query.order(GroupItem_.order);

        // Get linked content
        QueryBuilder<Content> contentQuery = query.link(GroupItem_.content);
        contentQuery.in(Content_.status, libraryStatus);

        if (searchBundle.getFilterBookFavourites()) contentQuery.equal(Content_.favourite, true);

        if (searchBundle.getFilterBookCompleted()) contentQuery.equal(Content_.completed, true);
        else if (searchBundle.getFilterBookNotCompleted())
            contentQuery.equal(Content_.completed, false);

        if (searchBundle.getFilterRating() > -1)
            contentQuery.equal(Content_.rating, searchBundle.getFilterRating());


        contentQuery.contains(Content_.title, searchBundle.getQuery(), QueryBuilder.StringOrder.CASE_INSENSITIVE);
        contentQuery.or().equal(Content_.uniqueSiteId, searchBundle.getQuery(), QueryBuilder.StringOrder.CASE_INSENSITIVE);
        //        query.or().link(Content_.attributes).contains(Attribute_.name, queryStr, QueryBuilder.StringOrder.CASE_INSENSITIVE); // Use of or() here is not possible yet with ObjectBox v2.3.1
        contentQuery.or().in(Content_.id, additionalIds);
        if (searchBundle.getGroupId() > 0)
            contentQuery.in(Content_.id, selectFilteredContent(searchBundle.getGroupId()));

        return Helper.getPrimitiveArrayFromList(Stream.of(query.build().find()).map(gi -> gi.content.getTargetId()).toList());
    }

    Query<Content> selectContentUniversalQ(ContentSearchManager.ContentSearchBundle searchBundle) {
        // Due to objectBox limitations (see https://github.com/objectbox/objectbox-java/issues/497 and https://github.com/objectbox/objectbox-java/issues/201)
        // querying Content and attributes have to be done separately
        Query<Content> contentAttrSubQuery = selectContentUniversalAttributesQ(searchBundle, libraryStatus);
        return selectContentUniversalContentQ(searchBundle, contentAttrSubQuery.findIds(), libraryStatus);
    }

    long[] selectContentUniversalByGroupItem(ContentSearchManager.ContentSearchBundle searchBundle) {
        // Due to objectBox limitations (see https://github.com/objectbox/objectbox-java/issues/497 and https://github.com/objectbox/objectbox-java/issues/201)
        // querying Content and attributes have to be done separately
        Query<Content> contentAttrSubQuery = selectContentUniversalAttributesQ(searchBundle, libraryStatus);
        return selectContentUniversalContentByGroupItem(searchBundle, contentAttrSubQuery.findIds());
    }

    List<Long> getShuffledIds() {
        return Stream.of(store.boxFor(ShuffleRecord.class).getAll()).map(ShuffleRecord::getContentId).toList();
    }

    void shuffleContentIds() {
        // Clear previous shuffled list
        Box<ShuffleRecord> shuffleStore = store.boxFor(ShuffleRecord.class);
        shuffleStore.removeAll();
        // Populate with a new list
        List<Long> allBooksIds = Helper.getListFromPrimitiveArray(selectStoredContentQ(false, false, -1, false).build().findIds());
        Collections.shuffle(allBooksIds, new Random(RandomSeedSingleton.getInstance().getSeed(Consts.SEED_CONTENT)));
        shuffleStore.put(Stream.of(allBooksIds).map(ShuffleRecord::new).toList());
    }

    private long[] shuffleRandomSortId(Query<Content> query) {
        Set<Long> queryIds = Helper.getSetFromPrimitiveArray(query.findIds());
        List<Long> shuffleIds = getShuffledIds();
        LinkedHashSet<Long> shuffledSet = new LinkedHashSet<>(shuffleIds.size());
        shuffledSet.addAll(shuffleIds);

        // Keep common IDs
        shuffledSet.retainAll(queryIds);

        // Isolate new IDs that have never been shuffled and append them at the end
        if (shuffledSet.size() < queryIds.size()) {
            queryIds.removeAll(shuffledSet);
            shuffledSet.addAll(queryIds);
        }

        return Helper.getPrimitiveArrayFromList(Stream.of(shuffledSet).toList());
    }

    long[] selectContentSearchId(ContentSearchManager.ContentSearchBundle searchBundle, List<Attribute> metadata) {
        long[] result;
        Query<Content> query = selectContentSearchContentQ(searchBundle, metadata);

        if (searchBundle.getSortField() != Preferences.Constant.ORDER_FIELD_RANDOM) {
            result = query.findIds();
        } else {
            result = shuffleRandomSortId(query);
        }
        return result;
    }

    long[] selectContentUniversalId(
            ContentSearchManager.ContentSearchBundle searchBundle,
            int[] statuses) {
        long[] result;
        // Due to objectBox limitations (see https://github.com/objectbox/objectbox-java/issues/497 and https://github.com/objectbox/objectbox-java/issues/201)
        // querying Content and attributes have to be done separately
        Query<Content> contentAttrSubQuery = selectContentUniversalAttributesQ(searchBundle, statuses);
        Query<Content> query = selectContentUniversalContentQ(searchBundle, contentAttrSubQuery.findIds(), statuses);

        if (searchBundle.getSortField() != Preferences.Constant.ORDER_FIELD_RANDOM) {
            result = query.findIds();
        } else {
            result = shuffleRandomSortId(query);
        }
        return result;
    }

    private long[] selectFilteredContent(long groupId) {
        if (groupId < 1) return new long[0];

        QueryBuilder<Content> qb = store.boxFor(Content.class).query();
        qb.link(Content_.groupItems).equal(GroupItem_.groupId, groupId);
        return qb.build().findIds();

        // https://github.com/objectbox/objectbox-java/issues/1028
        /*
        return store.boxFor(GroupItem.class).query()
                .equal(GroupItem_.groupId, groupId)
                .build()
                .property(GroupItem_.contentId)
                .findLongs();
         */
    }

    private long[] selectFilteredContent(List<Attribute> attrs) {
        return selectFilteredContent(-1, attrs, ContentHelper.Location.ANY, ContentHelper.Type.ANY);
    }

    private long[] selectFilteredContent(long groupId,
                                         List<Attribute> attributesFilter,
                                         @ContentHelper.Location int location,
                                         @ContentHelper.Type int contentType) {
        List<Attribute> attrs = (null == attributesFilter) ? Collections.emptyList() : attributesFilter;
        if (attrs.isEmpty()
                && groupId < 1
                && ContentHelper.Location.ANY == location
                && ContentHelper.Type.ANY == contentType
        ) return new long[0];

        // Handle simple case where no attributes have been selected
        if (attrs.isEmpty()) {
            QueryCondition<Content> qc = Content_.status.oneOf(libraryStatus);
            if (groupId > 0) qc = applyContentGroupFilter(qc, groupId);
            qc = applyContentLocationFilter(qc, location);
            qc = applyContentTypeFilter(qc, contentType);

            return store.boxFor(Content.class).query(qc).build().findIds();
        }

        // Pre-build queries to reuse them efficiently within the loops

        // Content from attribute
        final Query<Content> contentFromAttributesQuery;
        if (groupId < 1 && ContentHelper.Location.ANY == location && ContentHelper.Type.ANY == contentType) { // Standard cached query
            contentFromAttributesQuery = contentFromAttributesSearchQ;
        } else { // On-demand query
            QueryCondition<Content> qc = Content_.status.oneOf(libraryStatus);

            qc = applyContentLocationFilter(qc, location);
            qc = applyContentTypeFilter(qc, contentType);
            if (groupId > 0) qc = applyContentGroupFilter(qc, groupId);

            QueryBuilder<Content> contentFromAttributesQueryBuilder = store.boxFor(Content.class).query(qc);

            contentFromAttributesQueryBuilder.link(Content_.attributes)
                    .equal(Attribute_.type, 0)
                    .equal(Attribute_.name, "", QueryBuilder.StringOrder.CASE_INSENSITIVE);

            contentFromAttributesQuery = contentFromAttributesQueryBuilder.build();
        }

        // Content from source (distinct query as source is not an actual Attribute of the data model)
        final Query<Content> contentFromSourceQuery;
        if (groupId < 1 && ContentHelper.Location.ANY == location && ContentHelper.Type.ANY == contentType) { // Standard cached query
            contentFromSourceQuery = contentFromSourceSearchQ;
        } else { // On-demand query
            QueryCondition<Content> qc = Content_.status.oneOf(libraryStatus);
            qc.and(Content_.site.equal(1));

            qc = applyContentLocationFilter(qc, location);
            qc = applyContentTypeFilter(qc, contentType);
            if (groupId > 0) qc = applyContentGroupFilter(qc, groupId);

            contentFromSourceQuery = store.boxFor(Content.class).query(qc).build();
        }


        // Prepare first iteration for exclusion mode
        // If first tag is to be excluded, start with the whole database and _remove_ IDs (inverse logic)
        List<Long> idsFull = Collections.emptyList();
        if (!attrs.isEmpty() && attrs.get(0).isExcluded()) {
            final QueryBuilder<Content> contentFromAttributesQueryBuilder1 = store.boxFor(Content.class).query();
            contentFromAttributesQueryBuilder1.in(Content_.status, libraryStatus);
            /*
            if (filterFavourites)
                contentFromAttributesQueryBuilder1.equal(Content_.favourite, true);
             */
            idsFull = Helper.getListFromPrimitiveArray(contentFromAttributesQueryBuilder1.build().findIds());
        }

        // Cumulative query loop
        // Each iteration restricts the results of the next because advanced search uses an AND logic
        List<Long> results = Collections.emptyList();
        long[] ids;

        for (Attribute attr : attrs) {
            if (attr.getType().equals(AttributeType.SOURCE)) {
                ids = contentFromSourceQuery.setParameter(Content_.site, attr.getId()).findIds();
            } else {
                ids = contentFromAttributesQuery.setParameter(Attribute_.type, attr.getType().getCode())
                        .setParameter(Attribute_.name, attr.getName()).findIds();
            }
            if (results.isEmpty()) { // First iteration
                results = Helper.getListFromPrimitiveArray(ids);

                // If first tag is to be excluded, start trimming results
                if (attr.isExcluded()) {
                    idsFull.removeAll(results);
                    results = idsFull;
                }
            } else {
                // Filter results with newly found IDs (only common IDs should stay)
                List<Long> idsAsList = Helper.getListFromPrimitiveArray(ids);
                // Remove ids that fit the attribute from results
                if (attr.isExcluded())
                    results.removeAll(idsAsList);
                else
                    results.retainAll(idsAsList); // Careful with retainAll performance
            }
        }

        return Helper.getPrimitiveArrayFromList(results);
    }

    private void filterWithPageFavs(QueryBuilder<Content> builder) {
        builder.link(Content_.imageFiles).equal(ImageFile_.favourite, true);
    }

    List<Attribute> selectAvailableSources() {
        return selectAvailableSources(-1, null, ContentHelper.Location.ANY, ContentHelper.Type.ANY);
    }

    List<Attribute> selectAvailableSources(
            long groupId,
            List<Attribute> filter,
            @ContentHelper.Location int location,
            @ContentHelper.Type int contentType
    ) {
        List<Attribute> result = new ArrayList<>();

        QueryCondition<Content> qc = Content_.status.oneOf(libraryStatus);

        if (filter != null && !filter.isEmpty()) {
            AttributeMap metadataMap = new AttributeMap();
            metadataMap.addAll(filter);

            List<Attribute> params = metadataMap.get(AttributeType.SOURCE);
            if (params != null && !params.isEmpty())
                qc = qc.and(Content_.site.oneOf(getIdsFromAttributes(params)));

            for (Map.Entry<AttributeType, List<Attribute>> entry : metadataMap.entrySet()) {
                AttributeType attrType = entry.getKey();
                if (!attrType.equals(AttributeType.SOURCE)) { // Not a "real" attribute in database
                    List<Attribute> attrs = entry.getValue();
                    if (attrs != null && !attrs.isEmpty())
                        qc = qc.and(Content_.id.oneOf(selectFilteredContent(attrs)));
                }
            }
        }

        if (groupId > 0) qc = applyContentGroupFilter(qc, groupId);
        qc = applyContentLocationFilter(qc, location);
        qc = applyContentTypeFilter(qc, contentType);

        QueryBuilder<Content> query = store.boxFor(Content.class).query(qc);
        List<Content> content = query.build().find();

        // SELECT field, COUNT(*) GROUP BY (field) is not implemented in ObjectBox v2.3.1
        // (see https://github.com/objectbox/objectbox-java/issues/422)
        // => Group by and count have to be done manually (thanks God Stream exists !)
        // Group and count by source
        Map<Site, List<Content>> map = Stream.of(content).collect(Collectors.groupingBy(Content::getSite));
        if (map != null) {
            for (Map.Entry<Site, List<Content>> entry : map.entrySet()) {
                Site site = entry.getKey();
                int size = (null == entry.getValue()) ? 0 : entry.getValue().size();
                result.add(new Attribute(AttributeType.SOURCE, site.getDescription()).setExternalId(site.getCode()).setCount(size));
            }
        }
        // Order by count desc
        result = Stream.of(result).sortBy(a -> -a.getCount()).collect(toList());

        return result;
    }

    Query<Content> selectErrorContentQ() {
        return store.boxFor(Content.class).query().equal(Content_.status, StatusContent.ERROR.getCode()).orderDesc(Content_.downloadDate).build();
    }

    List<Content> selectContentByDlDate(int minDays, int maxDays) {
        QueryCondition<Content> qc = Content_.status.oneOf(libraryStatus);
        qc = applyContentDownloadDateFilter(qc, minDays, maxDays);
        return store.boxFor(Content.class).query(qc).build().find();
    }

    private QueryCondition<Content> applyContentGroupFilter(@NonNull final QueryCondition<Content> qc, long groupId) {
        Group group = store.boxFor(Group.class).get(groupId);
        if (group != null && group.grouping.equals(Grouping.DL_DATE)) // According to days since download date
            return applyContentDownloadDateFilter(qc, group.propertyMin, group.propertyMax);
        else if (group != null && group.grouping.equals(Grouping.CUSTOM) && 1 == group.subtype) // Books with no CUSTOM group attached
            return qc.and(Content_.id.notOneOf(selectCustomGroupedContent()));
        else // Direct link to group
            return qc.and(Content_.id.oneOf(selectFilteredContent(groupId)));
    }

    private QueryCondition<Content> applyContentDownloadDateFilter(@NonNull final QueryCondition<Content> qc, int minDays, int maxDays) {
        long today = Instant.now().toEpochMilli();
        long minDownloadDate = today - (maxDays * DAY_IN_MILLIS);
        long maxDownloadDate = today - (minDays * DAY_IN_MILLIS);
        return qc.and(Content_.downloadDate.between(minDownloadDate, maxDownloadDate));
    }

    private Query<Attribute> queryAvailableAttributes(
            @NonNull final AttributeType type,
            String filter,
            long[] filteredContent) {
        QueryBuilder<Attribute> query = store.boxFor(Attribute.class).query();
        query.equal(Attribute_.type, type.getCode());
        if (filter != null && !filter.trim().isEmpty())
            query.contains(Attribute_.name, filter.trim(), QueryBuilder.StringOrder.CASE_INSENSITIVE);
        if (filteredContent.length > 0)
            query.link(Attribute_.contents).in(Content_.id, filteredContent).in(Content_.status, libraryStatus);
        else
            query.link(Attribute_.contents).in(Content_.status, libraryStatus);

        return query.build();
    }

    long countAvailableAttributes(
            AttributeType type,
            long groupId,
            List<Attribute> attributeFilter,
            @ContentHelper.Location int location,
            @ContentHelper.Type int contentType,
            String filter) {
        return queryAvailableAttributes(type, filter, selectFilteredContent(groupId, attributeFilter, location, contentType)).count();
    }

    @SuppressWarnings("squid:S2184")
        // In our case, limit() argument has to be human-readable -> no issue concerning its type staying in the int range
    List<Attribute> selectAvailableAttributes(
            @NonNull AttributeType type,
            long groupId,
            List<Attribute> attributeFilter,
            @ContentHelper.Location int location,
            @ContentHelper.Type int contentType,
            String filter,
            int sortOrder,
            int page,
            int itemsPerPage) {
        long[] filteredContent = selectFilteredContent(groupId, attributeFilter, location, contentType);
        if (filteredContent.length == 0 && attributeFilter != null && !attributeFilter.isEmpty())
            return Collections.emptyList();
        Set<Long> filteredContentAsSet = Helper.getSetFromPrimitiveArray(filteredContent);
        Set<Integer> libraryStatusAsSet = Helper.getSetFromPrimitiveArray(libraryStatus);
        List<Attribute> result = queryAvailableAttributes(type, filter, filteredContent).find();

        // Compute attribute count for sorting
        if (Preferences.getSearchAttributesCount()) {
            long count;
            for (Attribute a : result) {
                // Only count the relevant Contents
                count = Stream.of(a.contents)
                        .filter(c -> libraryStatusAsSet.contains(c.getStatus().getCode()))
                        .filter(c -> filteredContentAsSet.isEmpty() || filteredContentAsSet.contains(c.getId()))
                        .count();
                a.setCount((int) count);
            }
        }

        // Apply sort order
        Stream<Attribute> s = Stream.of(result);
        if (Preferences.Constant.SEARCH_ORDER_ATTRIBUTES_ALPHABETIC == sortOrder) {
            s = s.sortBy(a -> -a.getCount()).sortBy(Attribute::getName);
        } else {
            s = s.sortBy(Attribute::getName).sortBy(a -> -a.getCount());
        }

        // Apply paging on sorted items
        if (itemsPerPage > 0) {
            int start = (page - 1) * itemsPerPage;
            s = s.limit(page * itemsPerPage).skip(start); // squid:S2184 here because int * int -> int (not long)
        }
        return s.collect(toList());
    }

    SparseIntArray countAvailableAttributesPerType() {
        return countAvailableAttributesPerType(-1, null, ContentHelper.Location.ANY, ContentHelper.Type.ANY);
    }

    SparseIntArray countAvailableAttributesPerType(
            long groupId,
            List<Attribute> attributeFilter,
            @ContentHelper.Location int location,
            @ContentHelper.Type int contentType
    ) {
        // Get Content filtered by current selection
        long[] filteredContent = selectFilteredContent(groupId, attributeFilter, location, contentType);
        // Get available attributes of the resulting content list
        QueryBuilder<Attribute> query = store.boxFor(Attribute.class).query();

        if (filteredContent.length > 0)
            query.link(Attribute_.contents).in(Content_.id, filteredContent).in(Content_.status, libraryStatus);
        else
            query.link(Attribute_.contents).in(Content_.status, libraryStatus);

        List<Attribute> attributes = query.build().find();

        SparseIntArray result = new SparseIntArray();
        // SELECT field, COUNT(*) GROUP BY (field) is not implemented in ObjectBox v2.3.1
        // (see https://github.com/objectbox/objectbox-java/issues/422)
        // => Group by and count have to be done manually (thanks God Stream exists !)
        // Group and count by type
        Map<AttributeType, List<Attribute>> map = Stream.of(attributes).collect(Collectors.groupingBy(Attribute::getType));

        if (map != null) {
            for (Map.Entry<AttributeType, List<Attribute>> entry : map.entrySet()) {
                AttributeType t = entry.getKey();
                if (filteredContent.length == 0 && attributeFilter != null)
                    result.append(t.getCode(), 0);
                else {
                    int size = (null == entry.getValue()) ? 0 : entry.getValue().size();
                    result.append(t.getCode(), size);
                }
            }
        }

        return result;
    }

    List<Content> selectContentWithTitle(@NonNull String word, int[] contentStatusCodes) {
        QueryBuilder<Content> query = store.boxFor(Content.class).query();
        query.contains(Content_.title, word, QueryBuilder.StringOrder.CASE_INSENSITIVE);
        query.in(Content_.status, contentStatusCodes);
        return query.build().find();
    }

    private QueryCondition<Content> applyContentLocationFilter(@NonNull QueryCondition<Content> qc, @ContentHelper.Location int location) {
        if (ContentHelper.Location.PRIMARY == location) {
            return qc.and(Content_.status.notEqual(StatusContent.EXTERNAL.getCode()));
        } else if (ContentHelper.Location.EXTERNAL == location) {
            return qc.and(Content_.status.equal(StatusContent.EXTERNAL.getCode()));
        }
        return qc;
    }

    private QueryCondition<Content> applyContentTypeFilter(@NonNull QueryCondition<Content> qc, @ContentHelper.Type int contentType) {
        switch (contentType) {
            case ContentHelper.Type.STREAMED:
                return qc.and(Content_.downloadMode.equal(Content.DownloadMode.STREAM));
            case ContentHelper.Type.ARCHIVE:
                qc = qc.and(Content_.status.equal(StatusContent.EXTERNAL.getCode()));
                QueryCondition<Content> combinedCondition = null;
                for (String ext : ArchiveHelper.getSupportedExtensions()) {
                    if (null == combinedCondition)
                        combinedCondition = Content_.storageUri.endsWith(ext, QueryBuilder.StringOrder.CASE_INSENSITIVE);
                    else
                        combinedCondition = combinedCondition.or(Content_.storageUri.endsWith(ext, QueryBuilder.StringOrder.CASE_INSENSITIVE));
                }
                if (combinedCondition != null) return qc.and(combinedCondition);
                else return qc;
            case ContentHelper.Type.PLACEHOLDER:
                return qc.and(Content_.status.equal(StatusContent.PLACEHOLDER.getCode()));
            case ContentHelper.Type.FOLDER:
                qc = qc.and(Content_.downloadMode.equal(Content.DownloadMode.DOWNLOAD));
                return qc.and(Content_.status.notEqual(StatusContent.PLACEHOLDER.getCode()));
            case ContentHelper.Type.ANY:
            default:
                return qc;
        }
    }

    void updateImageFileStatusParamsMimeTypeUriSize(@NonNull ImageFile image) {
        Box<ImageFile> imgBox = store.boxFor(ImageFile.class);
        ImageFile img = imgBox.get(image.getId());
        if (img != null) {
            img.setStatus(image.getStatus());
            img.setDownloadParams(image.getDownloadParams());
            img.setMimeType(image.getMimeType());
            img.setFileUri(image.getFileUri());
            img.setSize(image.getSize());
            imgBox.put(img);
        }
    }

    void updateImageContentStatus(
            long contentId,
            @Nullable StatusContent updateFrom,
            @NonNull StatusContent updateTo) {
        QueryBuilder<ImageFile> query = store.boxFor(ImageFile.class).query();
        if (updateFrom != null) query.equal(ImageFile_.status, updateFrom.getCode());
        List<ImageFile> imgs = query.equal(ImageFile_.contentId, contentId).build().find();
        if (imgs.isEmpty()) return;

        for (ImageFile img : imgs) img.setStatus(updateTo);
        store.boxFor(ImageFile.class).put(imgs);
    }

    void updateImageFileUrl(@NonNull final ImageFile image) {
        Box<ImageFile> imgBox = store.boxFor(ImageFile.class);
        ImageFile img = imgBox.get(image.getId());
        if (img != null) {
            img.setUrl(image.getUrl());
            imgBox.put(img);
        }
    }

    // Returns a list of processed images grouped by status, with count and filesize (in bytes)
    Map<StatusContent, ImmutablePair<Integer, Long>> countProcessedImagesById(long contentId) {
        QueryBuilder<ImageFile> imgQuery = store.boxFor(ImageFile.class).query();
        imgQuery.equal(ImageFile_.contentId, contentId);
        List<ImageFile> images = imgQuery.build().find();

        Map<StatusContent, ImmutablePair<Integer, Long>> result = new EnumMap<>(StatusContent.class);
        // SELECT field, COUNT(*) GROUP BY (field) is not implemented in ObjectBox v2.3.1
        // (see https://github.com/objectbox/objectbox-java/issues/422)
        // => Group by and count have to be done manually (thanks God Stream exists !)
        // Group and count by type
        Map<StatusContent, List<ImageFile>> map = Stream.of(images).collect(Collectors.groupingBy(ImageFile::getStatus));
        if (map != null) {
            for (Map.Entry<StatusContent, List<ImageFile>> entry : map.entrySet()) {
                StatusContent t = entry.getKey();
                int count = 0;
                long sizeBytes = 0;
                if (entry.getValue() != null) {
                    count = entry.getValue().size();
                    for (ImageFile img : entry.getValue()) sizeBytes += img.getSize();
                }
                result.put(t, new ImmutablePair<>(count, sizeBytes));
            }
        }

        return result;
    }

    Map<Site, ImmutablePair<Integer, Long>> selectPrimaryMemoryUsagePerSource() {
        return selectMemoryUsagePerSource(new int[]{StatusContent.DOWNLOADED.getCode(), StatusContent.MIGRATED.getCode()});
    }

    Map<Site, ImmutablePair<Integer, Long>> selectExternalMemoryUsagePerSource() {
        return selectMemoryUsagePerSource(new int[]{StatusContent.EXTERNAL.getCode()});
    }

    Map<Site, ImmutablePair<Integer, Long>> selectMemoryUsagePerSource(int[] statusCodes) {
        // Get all downloaded images regardless of the book's status
        QueryBuilder<Content> query = store.boxFor(Content.class).query();
        query.in(Content_.status, statusCodes);
        List<Content> books = query.build().find();

        Map<Site, ImmutablePair<Integer, Long>> result = new EnumMap<>(Site.class);
        // SELECT field, COUNT(*) GROUP BY (field) is not implemented in ObjectBox v2.3.1
        // (see https://github.com/objectbox/objectbox-java/issues/422)
        // => Group by and count have to be done manually (thanks God Stream exists !)
        // Group and count by type
        Map<Site, List<Content>> map = Stream.of(books).collect(Collectors.groupingBy(Content::getSite));
        if (map != null) {
            for (Map.Entry<Site, List<Content>> entry : map.entrySet()) {
                Site s = entry.getKey();
                int count = 0;
                long size = 0;
                if (entry.getValue() != null) {
                    count = entry.getValue().size();
                    for (Content c : entry.getValue()) size += c.getSize();
                }
                result.put(s, new ImmutablePair<>(count, size));
            }
        }

        return result;
    }

    void insertErrorRecord(@NonNull final ErrorRecord record) {
        store.boxFor(ErrorRecord.class).put(record);
    }

    List<ErrorRecord> selectErrorRecordByContentId(long contentId) {
        return store.boxFor(ErrorRecord.class).query().equal(ErrorRecord_.contentId, contentId).build().find();
    }

    void deleteErrorRecords(long contentId) {
        List<ErrorRecord> records = selectErrorRecordByContentId(contentId);
        store.boxFor(ErrorRecord.class).remove(records);
    }

    void insertImageFile(@NonNull ImageFile img) {
        if (img.getId() > 0) store.boxFor(ImageFile.class).put(img);
    }

    void deleteImageFiles(long contentId) {
        store.boxFor(ImageFile.class).query().equal(ImageFile_.contentId, contentId).build().remove();
    }

    void deleteImageFiles(List<ImageFile> images) {
        store.boxFor(ImageFile.class).remove(images);
    }

    void insertImageFiles(@NonNull List<ImageFile> imgs) {
        store.boxFor(ImageFile.class).put(imgs);
    }

    void replaceImageFiles(long contentId, @NonNull final List<ImageFile> newList) {
        store.runInTx(() -> {
            deleteImageFiles(contentId);
            for (ImageFile img : newList) img.setContentId(contentId);
            insertImageFiles(newList);
        });
    }

    @Nullable
    ImageFile selectImageFile(long id) {
        if (id > 0) return store.boxFor(ImageFile.class).get(id);
        else return null;
    }

    Query<ImageFile> selectDownloadedImagesFromContentQ(long id) {
        QueryBuilder<ImageFile> builder = store.boxFor(ImageFile.class).query();
        builder.equal(ImageFile_.contentId, id);
        builder.in(ImageFile_.status, new int[]{StatusContent.DOWNLOADED.getCode(), StatusContent.EXTERNAL.getCode(), StatusContent.ONLINE.getCode(), StatusContent.PLACEHOLDER.getCode()});
        builder.order(ImageFile_.order);
        return builder.build();
    }

    void insertSiteHistory(@NonNull Site site, @NonNull String url) {
        SiteHistory siteHistory = selectHistory(site);
        if (siteHistory != null) {
            siteHistory.setUrl(url);
            store.boxFor(SiteHistory.class).put(siteHistory);
        } else {
            store.boxFor(SiteHistory.class).put(new SiteHistory(site, url));
        }
    }

    @Nullable
    SiteHistory selectHistory(@NonNull Site s) {
        return store.boxFor(SiteHistory.class).query().equal(SiteHistory_.site, s.getCode()).build().findFirst();
    }

    // BOOKMARKS

    Query<SiteBookmark> selectBookmarksQ(@Nullable Site s) {
        QueryBuilder<SiteBookmark> qb = store.boxFor(SiteBookmark.class).query();
        if (s != null) qb.equal(SiteBookmark_.site, s.getCode());

        return qb.order(SiteBookmark_.order).build();
    }

    @Nullable
    SiteBookmark selectHomepage(@NonNull Site s) {
        QueryBuilder<SiteBookmark> qb = store.boxFor(SiteBookmark.class).query();
        qb.equal(SiteBookmark_.site, s.getCode());
        qb.equal(SiteBookmark_.isHomepage, true);

        return qb.build().findFirst();
    }

    String[] selectAllBooksmarkUrls() {
        return store.boxFor(SiteBookmark.class).query().build().property(SiteBookmark_.url).findStrings();
    }

    long insertBookmark(@NonNull final SiteBookmark bookmark) {
        return store.boxFor(SiteBookmark.class).put(bookmark);
    }

    void insertBookmarks(@NonNull final List<SiteBookmark> bookmarks) {
        store.boxFor(SiteBookmark.class).put(bookmarks);
    }

    void deleteBookmark(long bookmarkId) {
        store.boxFor(SiteBookmark.class).remove(bookmarkId);
    }

    int getMaxBookmarkOrderFor(@NonNull final Site site) {
        return (int) store.boxFor(SiteBookmark.class).query().equal(SiteBookmark_.site, site.getCode()).build().property(SiteBookmark_.order).max();
    }

    // Select all duplicate bookmarks that end with a "/"
    public Query<SiteBookmark> selectAllDuplicateBookmarks() {
        String[] urls = selectAllBooksmarkUrls();
        for (int i = 0; i < urls.length; i++) urls[i] = urls[i] + "/";

        QueryBuilder<SiteBookmark> query = store.boxFor(SiteBookmark.class).query();
        query.in(SiteBookmark_.url, urls, QueryBuilder.StringOrder.CASE_INSENSITIVE);

        return query.build();
    }

    // SEARCH RECORDS

    Query<SearchRecord> selectSearchRecordsQ() {
        QueryBuilder<SearchRecord> qb = store.boxFor(SearchRecord.class).query();
        return qb.build();
    }

    void deleteSearchRecord(long id) {
        store.boxFor(SearchRecord.class).remove(id);
    }

    void insertSearchRecords(@NonNull List<SearchRecord> records) {
        store.boxFor(SearchRecord.class).put(records);
    }

    // GROUPS

    long insertGroup(Group group) {
        return store.boxFor(Group.class).put(group);
    }

    long insertGroupItem(GroupItem item) {
        return store.boxFor(GroupItem.class).put(item);
    }

    List<GroupItem> selectGroupItems(long[] groupItemIds) {
        return store.boxFor(GroupItem.class).get(groupItemIds);
    }

    List<GroupItem> selectGroupItems(long contentId, int groupingId) {
        QueryBuilder<GroupItem> qb = store.boxFor(GroupItem.class).query().equal(GroupItem_.contentId, contentId);
        qb.link(GroupItem_.group).equal(Group_.grouping, groupingId);
        return qb.build().find();
    }

    void deleteGroupItems(long[] groupItemIds) {
        store.boxFor(GroupItem.class).remove(groupItemIds);
    }

    long countGroupsFor(@NonNull final Grouping grouping) {
        return store.boxFor(Group.class).query().equal(Group_.grouping, grouping.getId()).build().count();
    }

    int getMaxGroupOrderFor(@NonNull final Grouping grouping) {
        return (int) store.boxFor(Group.class).query().equal(Group_.grouping, grouping.getId()).build().property(Group_.order).max();
    }

    int getMaxGroupItemOrderFor(long groupid) {
        return (int) store.boxFor(GroupItem.class).query().equal(GroupItem_.groupId, groupid).build().property(GroupItem_.order).max();
    }

    Query<Group> selectGroupsQ(
            int grouping,
            @Nullable String query,
            int orderField,
            boolean orderDesc,
            int subType,
            boolean groupFavouritesOnly,
            int filterRating) {
        QueryBuilder<Group> qb = store.boxFor(Group.class).query().equal(Group_.grouping, grouping);
        if (query != null)
            qb.contains(Group_.name, query, QueryBuilder.StringOrder.CASE_INSENSITIVE);

        // Subtype filtering for artists groups
        if (subType > -1) {
            if (grouping == Grouping.ARTIST.getId() && subType != Preferences.Constant.ARTIST_GROUP_VISIBILITY_ARTISTS_GROUPS) {
                qb.equal(Group_.subtype, subType);
            }
            // Subtype filtering for custom groups
            if (grouping == Grouping.CUSTOM.getId()) {
                qb.equal(Group_.subtype, subType);
            }
        }

        if (groupFavouritesOnly) qb.equal(Group_.favourite, true);

        if (filterRating > -1) qb.equal(Group_.rating, filterRating);

        Property<Group> property = Group_.name;
        if (Preferences.Constant.ORDER_FIELD_CUSTOM == orderField || grouping == Grouping.DL_DATE.getId())
            property = Group_.order;
        // Order by number of children / download date is done by the DAO

        if (orderDesc) qb.orderDesc(property);
        else qb.order(property);

        return qb.build();
    }

    @Nullable
    Group selectGroup(long groupId) {
        return store.boxFor(Group.class).get(groupId);
    }

    @Nullable
    List<Group> selectGroups(long[] groupIds) {
        return store.boxFor(Group.class).get(groupIds);
    }

    @Nullable
    Group selectGroupByName(int grouping, @NonNull final String name) {
        return store.boxFor(Group.class).query().equal(Group_.grouping, grouping).equal(Group_.name, name, QueryBuilder.StringOrder.CASE_INSENSITIVE).build().findFirst();
    }

    void deleteGroup(long groupId) {
        store.boxFor(Group.class).remove(groupId);
    }

    Query<Group> selectGroupsByGroupingQ(int groupingId) {
        return store.boxFor(Group.class).query().equal(Group_.grouping, groupingId).build();
    }

    Query<Group> selectFlaggedGroupsQ() {
        return store.boxFor(Group.class).query().equal(Group_.isFlaggedForDeletion, true).build();
    }

    void flagGroupsForDeletion(List<Group> groupList) {
        for (Group g : groupList) g.setFlaggedForDeletion(true);
        store.boxFor(Group.class).put(groupList);
    }

    void deleteGroupItemsByGrouping(int groupingId) {
        QueryBuilder<GroupItem> qb = store.boxFor(GroupItem.class).query();
        qb.link(GroupItem_.group).equal(Group_.grouping, groupingId);
        qb.build().remove();
    }

    void deleteGroupItemsByGroup(long groupId) {
        QueryBuilder<GroupItem> qb = store.boxFor(GroupItem.class).query();
        qb.link(GroupItem_.group).equal(Group_.id, groupId);
        qb.build().remove();
    }

    List<Chapter> selectChapters(long contentId) {
        return store.boxFor(Chapter.class).query().equal(Chapter_.contentId, contentId).order(Chapter_.order).build().find();
    }

    void insertChapters(List<Chapter> chapters) {
        store.boxFor(Chapter.class).put(chapters);
    }

    void deleteChaptersByContentId(long contentId) {
        QueryBuilder<Chapter> qb = store.boxFor(Chapter.class).query();
        qb.equal(Chapter_.contentId, contentId);
        qb.build().remove();
    }

    void deleteChapter(long chapterId) {
        store.boxFor(Chapter.class).remove(chapterId);
    }


    /**
     * ONE-SHOT USE QUERIES (MIGRATION & MAINTENANCE)
     */

    List<Content> selectContentWithOldPururinHost() {
        return store.boxFor(Content.class).query().equal(Content_.site, Site.PURURIN.getCode()).contains(Content_.coverImageUrl, "://api.pururin.io/images/", QueryBuilder.StringOrder.CASE_INSENSITIVE).build().find();
    }

    List<Content> selectContentWithOldTsuminoCovers() {
        return store.boxFor(Content.class).query().equal(Content_.site, Site.TSUMINO.getCode()).contains(Content_.coverImageUrl, "://www.tsumino.com/Image/Thumb/", QueryBuilder.StringOrder.CASE_INSENSITIVE).build().find();
    }

    List<Content> selectContentWithOldHitomiCovers() {
        return store.boxFor(Content.class).query().equal(Content_.site, Site.HITOMI.getCode()).contains(Content_.coverImageUrl, "/smallbigtn/", QueryBuilder.StringOrder.CASE_INSENSITIVE).build().find();
    }

    List<Content> selectDownloadedM18Books() {
        return store.boxFor(Content.class).query().equal(Content_.site, Site.MANHWA18.getCode()).in(Content_.status, libraryStatus).build().find();
    }

    List<Chapter> selecChaptersEmptyName() {
        return store.boxFor(Chapter.class).query().equal(Chapter_.name, "", QueryBuilder.StringOrder.CASE_INSENSITIVE).build().find();
    }

    List<Content> selectDownloadedContentWithNoSize() {
        return store.boxFor(Content.class).query().in(Content_.status, libraryStatus).isNull(Content_.size).build().find();
    }

    List<Content> selectDownloadedContentWithNoReadProgress() {
        return store.boxFor(Content.class).query().in(Content_.status, libraryStatus).isNull(Content_.readProgress).build().find();
    }

    List<Group> selectGroupsWithNoCoverContent() {
        return store.boxFor(Group.class).query().isNull(Group_.coverContentId).or().equal(Group_.coverContentId, 0).build().find();
    }

    List<Content> selectContentWithNullCompleteField() {
        return store.boxFor(Content.class).query().isNull(Content_.completed).build().find();
    }

    List<Content> selectContentWithNullDlModeField() {
        return store.boxFor(Content.class).query().isNull(Content_.downloadMode).build().find();
    }

    List<Content> selectContentWithNullMergeField() {
        return store.boxFor(Content.class).query().isNull(Content_.manuallyMerged).build().find();
    }

    List<Content> selectContentWithNullDlCompletionDateField() {
        return store.boxFor(Content.class).query().isNull(Content_.downloadCompletionDate).build().find();
    }

    List<Content> selectContentWithInvalidUploadDate() {
        return store.boxFor(Content.class).query().greater(Content_.uploadDate, 0).less(Content_.uploadDate, 10000000000L).build().find();
    }

    List<Chapter> selectChapterWithNullUploadDate() {
        return store.boxFor(Chapter.class).query().isNull(Chapter_.uploadDate).build().find();
    }

    Query<Content> selectOldStoredContentQ() {
        QueryBuilder<Content> query = store.boxFor(Content.class).query();
        query.in(Content_.status, new int[]{
                StatusContent.DOWNLOADING.getCode(),
                StatusContent.PAUSED.getCode(),
                StatusContent.ERROR.getCode(),
                StatusContent.DOWNLOADED.getCode(),
                StatusContent.MIGRATED.getCode()});
        query.notNull(Content_.storageFolder);
        query.notEqual(Content_.storageFolder, "", QueryBuilder.StringOrder.CASE_INSENSITIVE);
        return query.build();
    }

    QueryBuilder<Content> selectStoredContentQ(boolean nonFavouritesOnly, boolean includeQueued, int orderField, boolean orderDesc) {
        QueryBuilder<Content> query = store.boxFor(Content.class).query();
        if (includeQueued)
            query.in(Content_.status, libraryQueueStatus);
        else
            query.in(Content_.status, libraryStatus);
        if (nonFavouritesOnly) query.equal(Content_.favourite, false);
        if (orderField > -1) {
            Property<Content> field = getPropertyFromField(orderField);
            if (null != field) {
                if (orderDesc) query.orderDesc(field);
                else query.order(field);
            }
        }
        return query;
    }

    Query<Content> selectNonHashedContent() {
        QueryBuilder<Content> query = store.boxFor(Content.class).query()
                .in(Content_.status, new int[]{
                        StatusContent.DOWNLOADED.getCode(),
                        StatusContent.MIGRATED.getCode()
                })
                .notNull(Content_.storageUri)
                .notEqual(Content_.storageUri, "", QueryBuilder.StringOrder.CASE_INSENSITIVE);

        QueryBuilder<ImageFile> imageQuery = query.backlink(ImageFile_.content);
        imageQuery.equal(ImageFile_.isCover, true)
                .isNull(ImageFile_.imageHash)
                .or()
                .in(ImageFile_.imageHash, new long[]{0, -1})
                .notEqual(ImageFile_.status, StatusContent.ONLINE.getCode());

        return query.build();
    }

    long[] selectCustomGroupedContent() {
        QueryBuilder<Content> customContentQB = store.boxFor(Content.class).query();
        customContentQB.link(Content_.groupItems).link(GroupItem_.group)
                .equal(Group_.grouping, Grouping.CUSTOM.getId()) // Custom group
                .equal(Group_.subtype, 0); // Not the Ungrouped group (subtype 1)
        return customContentQB.build().findIds();
        // See https://github.com/objectbox/objectbox-java/issues/1028
        /*
        QueryBuilder<GroupItem> customGContentQB = store.boxFor(GroupItem.class).query();
        customGContentQB.link(GroupItem_.group).equal(Group_.grouping, Grouping.CUSTOM.getId());
        return customGContentQB.build().property(GroupItem_.contentId).findLongs();
         */
    }

    Set<Long> selectUngroupedContentIds() {
        // Select all eligible content
        QueryBuilder<Content> allContentQ = store.boxFor(Content.class).query().in(Content_.status, libraryStatus);
        Set<Long> allContent = Helper.getSetFromPrimitiveArray(allContentQ.build().findIds());
        // Strip all content that have a Custom grouping
        allContent.removeAll(Helper.getSetFromPrimitiveArray(selectCustomGroupedContent()));
        return allContent;
    }
}
