/*
 * @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.os.LocaleList;

import androidx.annotation.IntRange;
import androidx.annotation.NonNull;
import androidx.annotation.WorkerThread;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Locale;
import java.util.Optional;
import java.util.function.BiConsumer;
import java.util.function.Function;

import com.hardbacknutter.nevertoomanybooks.BuildConfig;
import com.hardbacknutter.nevertoomanybooks.ServiceLocator;
import com.hardbacknutter.nevertoomanybooks.core.database.DaoInsertException;
import com.hardbacknutter.nevertoomanybooks.core.database.DaoUpdateException;
import com.hardbacknutter.nevertoomanybooks.core.database.DaoWriteException;
import com.hardbacknutter.nevertoomanybooks.core.database.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.core.utils.LocaleListUtils;
import com.hardbacknutter.nevertoomanybooks.database.CursorRow;
import com.hardbacknutter.nevertoomanybooks.database.DBKey;
import com.hardbacknutter.nevertoomanybooks.database.dao.IdentifierValueDao;
import com.hardbacknutter.nevertoomanybooks.database.dao.PublicationFrequencyDao;
import com.hardbacknutter.nevertoomanybooks.database.dao.SeriesDao;
import com.hardbacknutter.nevertoomanybooks.entities.Book;
import com.hardbacknutter.nevertoomanybooks.entities.EntityMergeHelper;
import com.hardbacknutter.nevertoomanybooks.entities.Identifier;
import com.hardbacknutter.nevertoomanybooks.entities.Series;
import com.hardbacknutter.nevertoomanybooks.entities.SeriesMergeHelper;
import com.hardbacknutter.nevertoomanybooks.utils.ReorderHelper;

import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_BOOKS;
import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_BOOK_SERIES;
import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_SERIES;
import static com.hardbacknutter.nevertoomanybooks.database.DBDefinitions.TBL_SERIES_PUBLICATION_FREQUENCY;

public class SeriesDaoImpl
        extends BaseDaoImpl
        implements SeriesDao {

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

    private static final String ERROR_INSERT_FROM = "Insert from\n";
    private static final String ERROR_UPDATE_FROM = "Update from\n";

    private final IdentifierValueDao seriesIdentifierDao;
    private final PublicationFrequencyDao publicationFrequencyDao;

    /**
     * Constructor.
     *
     * @param db Database Access
     */
    public SeriesDaoImpl(@NonNull final SynchronizedDb db) {
        super(db, TAG);
        final ServiceLocator serviceLocator = ServiceLocator.getInstance();
        seriesIdentifierDao = serviceLocator.getSeriesIdentifierDao();
        publicationFrequencyDao = serviceLocator.getPublicationFrequencyDao();
    }

    @NonNull
    @Override
    public Optional<Series> findById(@IntRange(from = 1) final long id) {
        try (Cursor cursor = db.rawQuery(Sql.FIND_BY_ID, new String[]{String.valueOf(id)})) {
            if (cursor.moveToFirst()) {
                return Optional.of(new Series(id, new CursorRow(cursor)));
            } else {
                return Optional.empty();
            }
        }
    }

    @Override
    @NonNull
    public Optional<Series> findByName(@NonNull final Context context,
                                       @NonNull final Series series,
                                       @NonNull final Locale locale) {

        final String title = series.getTitle();
        final String reorderedTitle = new ReorderHelper(LocaleListUtils.asList(
                context.getResources().getConfiguration().getLocales()))
                .reorder(context, title, locale);

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

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

    @Override
    @NonNull
    public List<Series> getByBookId(@IntRange(from = 1) final long bookId) {
        final List<Series> 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 Series(rowData.getLong(DBKey.PK_ID), rowData));
            }
        }
        return list;
    }

    @Override
    @NonNull
    public Optional<String> getLanguage(@IntRange(from = 1) final long id) {
        try (SynchronizedStatement stmt = db.compileStatement(Sql.GET_LANGUAGE)) {
            stmt.bindLong(1, id);
            return Optional.ofNullable(stmt.simpleQueryForStringOrNull());
        }
    }

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

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

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

    @Override
    public boolean setComplete(@NonNull final Series series,
                               final boolean complete) {
        final int rowsAffected;
        try (SynchronizedStatement stmt = db.compileStatement(Sql.SET_COMPLETE)) {
            stmt.bindBoolean(1, complete);

            stmt.bindLong(2, series.getId());
            rowsAffected = stmt.executeUpdateDelete();
        }

        if (rowsAffected > 0) {
            series.setComplete(complete);
            return true;
        }
        return false;
    }

    /**
     * Remove duplicates.
     * Consolidates series/- and series/number.
     * <p>
     * Remove Series from the list where the titles are the same, but one entry has a
     * {@code null} or empty number.
     * e.g. the following list should be processed as indicated:
     * <ul>
     * <li>foo(5)</li>
     * <li>foo <-- delete</li>
     * <li>bar <-- delete</li>
     * <li>bar <-- delete</li>
     * <li>bar(1)</li>
     * <li>foo(5) <-- delete</li>
     * <li>foo(6)</li>
     * </ul>
     * Note we keep BOTH foo(5) + foo(6)
     * <p>
     * {@inheritDoc}
     */
    @Override
    public boolean pruneList(@NonNull final Context context,
                             @NonNull final Collection<Series> list,
                             final boolean normalise,
                             @NonNull final Function<Series, Locale> localeSupplier,
                             @NonNull final BiConsumer<Series, Locale> idFixer) {
        // Reminder: only abort if empty. We rely on 'fixId' being called for ALL list values.
        if (list.isEmpty()) {
            return false;
        }

        if (normalise) {
            final LocaleList userLocales = context.getResources().getConfiguration().getLocales();
            final List<Locale> allLocales = LocaleListUtils.asList(userLocales);
            final ReorderHelper reorderHelper = new ReorderHelper(allLocales);
            list.forEach(series -> {
                final String title = reorderHelper.reverse(context, series.getTitle(),
                                                           localeSupplier.apply(series));
                series.setTitle(title);
            });
        }

        final EntityMergeHelper<Series> mergeHelper = new SeriesMergeHelper();
        return mergeHelper.merge(context, list, localeSupplier, idFixer);
    }

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

    @Override
    public void refresh(@NonNull final Context context,
                        @NonNull final Series series,
                        @NonNull final Locale locale) {

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

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

    @Override
    public void insertOrUpdate(@NonNull final Context context,
                               @IntRange(from = 1) final long bookId,
                               final boolean doUpdates,
                               @NonNull final Collection<Series> list,
                               @NonNull final Function<Series, Locale> localeSupplier)
            throws DaoWriteException {

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

        pruneList(context, list, localeSupplier);

        // Just delete all current links; we'll re-insert them for easier positioning
        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;
        }

        int position = 0;
        try (SynchronizedStatement stmt = db.compileStatement(Sql.INSERT_BOOK_LINK)) {
            for (final Series series : list) {
                final Locale locale = localeSupplier.apply(series);
                fixId(context, series, locale);

                // create if needed - do NOT do updates unless explicitly allowed
                if (series.getId() == 0) {
                    insert(context, series, locale);
                } else if (doUpdates) {
                    // https://stackoverflow.com/questions/6677517/update-if-different-changed
                    // ONLY update if there are actual changes.
                    // Otherwise, the trigger "after_update_on" + TBL_SERIES
                    // would set DATE_LAST_UPDATED__UTC for ALL books by that series
                    // while not needed.
                    final Optional<Series> oFound = findById(series.getId());

                    if (oFound.isPresent()) {
                        final Series found = oFound.get();
                        // always merge, but no need to check if modified or not
                        series.merge(found, true);
                        // Check for the name AND user/book fields being equals.
                        if (!found.isIdentical(series)) {
                            update(context, series, locale);
                        }
                    }
                }

                position++;

                stmt.bindLong(1, bookId);
                stmt.bindLong(2, series.getId());
                stmt.bindString(3, series.getNumber());
                stmt.bindLong(4, position);
                if (stmt.executeInsert() == -1) {
                    throw new DaoInsertException("insert Book-Series");
                }
            }
        }
    }

    @Override
    @IntRange(from = 1)
    public long insert(@NonNull final Context context,
                       @NonNull final Series series,
                       @NonNull final Locale locale)
            throws DaoWriteException {

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

            // REMINDER: do NOT resolve the locale using series.getLocale!
            // It's explicitly set as a parameter!

            final String title = series.getTitle();
            final String obTitle = new ReorderHelper(LocaleListUtils.asList(
                    context.getResources().getConfiguration().getLocales()))
                    .reorderForSorting(context, title, locale);

            final long iId;
            try (SynchronizedStatement stmt = db.compileStatement(Sql.INSERT)) {
                stmt.bindString(1, title);
                stmt.bindString(2, textNormaliser.strict(obTitle, locale));
                stmt.bindBoolean(3, series.isComplete());
                iId = stmt.executeInsert();
            }

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

                publicationFrequencyDao.setFrequency(series);
                seriesIdentifierDao.insertOrUpdate(Identifier.EntityType.Series,
                                                   series.getId(), series.getIdentifiers());

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

    @Override
    public void update(@NonNull final Context context,
                       @NonNull final Series series,
                       @NonNull final Locale locale)
            throws DaoWriteException {

        // REMINDER: do NOT resolve the locale using series.getLocale!
        // It's explicitly set as a parameter!

        final String text = series.getTitle();
        final String obTitle = new ReorderHelper(LocaleListUtils.asList(
                context.getResources().getConfiguration().getLocales()))
                .reorderForSorting(context, text, locale);

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

            final int rowsAffected;
            try (SynchronizedStatement stmt = db.compileStatement(Sql.UPDATE)) {
                stmt.bindString(1, series.getTitle());
                stmt.bindString(2, textNormaliser.strict(obTitle, locale));
                stmt.bindBoolean(3, series.isComplete());

                stmt.bindLong(4, series.getId());
                rowsAffected = stmt.executeUpdateDelete();
            }

            if (rowsAffected > 0) {
                publicationFrequencyDao.setFrequency(series);
                seriesIdentifierDao.insertOrUpdate(Identifier.EntityType.Series,
                                                   series.getId(), series.getIdentifiers());
                if (txLock != null) {
                    db.setTransactionSuccessful();
                }
                return;
            }

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

    @Override
    public boolean delete(@NonNull final Context context,
                          @NonNull final Series series) {
        Synchronizer.SyncLock txLock = null;
        try {
            if (!db.inTransaction()) {
                txLock = db.beginTransaction(true);
            }

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

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

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

        final Locale userLocale = context.getResources().getConfiguration().getLocales().get(0);
        int booksMoved;

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

            // Relink books with the target Series,
            // respecting the position of the Series in the list for each book.
            final List<Long> bookIds = getBookIds(source.getId());
            booksMoved = bookIds.size();

            for (final long bookId : bookIds) {
                final Book book = Book.from(bookId);

                final List<Series> fromBook = book.getSeries();
                final List<Series> destList = new ArrayList<>();

                for (final Series 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, false, destList, series ->
                        series.getLocale(userLocale)
                              .orElseGet(() -> book.getLocale(userLocale).orElse(userLocale)));
            }

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

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

        return booksMoved;
    }

    @Override
    @WorkerThread
    public int purge() {
        try (SynchronizedStatement stmt = db.compileStatement(Sql.PURGE)) {
            return stmt.executeUpdateDelete();
        }
    }

    @Override
    @WorkerThread
    public int rebuildOrderByColumns(@NonNull final Context context,
                                     @NonNull final Locale locale,
                                     @NonNull final ReorderHelper reorderHelper) {
        int i = 0;
        // We should use the locale from the 1st book in the series...
        // but that is a huge overhead so we use the users preferred Locale.
        try (Cursor cursor = db.rawQuery(Sql.OB_REBUILD_TITLES, null);
             SynchronizedStatement stmt = db.compileStatement(Sql.OB_REBUILD)) {

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

                final String rTitle = reorderHelper
                        .reorderForSorting(context, title, locale);
                final String rObTitle = textNormaliser.strict(rTitle, locale);

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

    @Override
    public int fixPositions(@NonNull final Context context)
            throws DaoWriteException {
        final Locale userLocale = context.getResources().getConfiguration().getLocales().get(0);

        final List<Long> bookIds = getColumnAsLongArrayList(Sql.REPOSITION);
        if (!bookIds.isEmpty()) {
            Synchronizer.SyncLock txLock = null;
            try {
                if (!db.inTransaction()) {
                    txLock = db.beginTransaction(true);
                }

                for (final long bookId : bookIds) {
                    final Book book = Book.from(bookId);
                    final Locale bookLocale = book.getLocale(userLocale).orElse(userLocale);
                    // We KNOW there are no updates needed.
                    insertOrUpdate(context, bookId, false,
                                   book.getSeries(),
                                   series -> bookLocale);
                }
                if (txLock != null) {
                    db.setTransactionSuccessful();
                }
            } finally {
                if (txLock != null) {
                    db.endTransaction(txLock);
                }
            }
        }
        return bookIds.size();
    }

    private static final class Sql {

        /** Insert a {@link Series}. */
        static final String INSERT =
                INSERT_INTO_ + TBL_SERIES.getName()
                + '(' + DBKey.SERIES.TITLE
                + ',' + DBKey.SERIES.TITLE_OB
                + ',' + DBKey.SERIES.COMPLETE
                + ") VALUES (?,?,?)";

        /** Update a {@link Series}. */
        static final String UPDATE =
                UPDATE_ + TBL_SERIES.getName()
                + _SET_ + DBKey.SERIES.TITLE + "=?"
                + ',' + DBKey.SERIES.TITLE_OB + "=?"
                + ',' + DBKey.SERIES.COMPLETE + "=?"
                + _WHERE_ + DBKey.PK_ID + "=?";

        static final String SET_COMPLETE =
                UPDATE_ + TBL_SERIES.getName()
                + _SET_ + DBKey.SERIES.COMPLETE + "=?"
                + _WHERE_ + DBKey.PK_ID + "=?";

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

        /** Purge all {@link Series}s which are no longer in use. */
        static final String PURGE =
                DELETE_FROM_ + TBL_SERIES.getName()
                + _WHERE_ + DBKey.PK_ID + _NOT_IN_
                + '(' + SELECT_DISTINCT_ + DBKey.FK_SERIES
                + _FROM_ + TBL_BOOK_SERIES.getName() + ')';

        /** Insert the link between a {@link Book} and a {@link Series}. */
        static final String INSERT_BOOK_LINK =
                INSERT_INTO_ + TBL_BOOK_SERIES.getName()
                + '(' + DBKey.FK_BOOK
                + ',' + DBKey.FK_SERIES
                + ',' + DBKey.SERIES.BOOK_SERIES_NUMBER
                + ',' + DBKey.SERIES.BOOK_SERIES_POSITION
                + ") VALUES(?,?,?,?)";
        /**
         * Delete the link between a {@link Book} and a {@link Series}.
         * <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_SERIES.getName() + _WHERE_ + DBKey.FK_BOOK + "=?";

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

        /** Count the number of {@link Book}'s in a {@link Series}. */
        static final String COUNT_BOOKS =
                SELECT_COUNT_FROM_ + TBL_BOOK_SERIES.getName()
                + _WHERE_ + DBKey.FK_SERIES + "=?";

        /** A list of all {@link Series}s, unordered. */
        static final String SELECT_ALL =
                "SELECT " + TBL_SERIES.dot("*")
                // LEFT OUTER JOIN, columns default to NULL
                + ',' + TBL_SERIES_PUBLICATION_FREQUENCY
                        .dotAs(DBKey.PUBLICATION_FREQUENCY.TYPE,
                               DBKey.PUBLICATION_FREQUENCY.CADENCE,
                               DBKey.PUBLICATION_FREQUENCY.IS_ORDINAL)
                + _FROM_ + TBL_SERIES.as()
                + TBL_SERIES.leftOuterJoin(TBL_SERIES_PUBLICATION_FREQUENCY);

        /** Find a {@link Series} by its id. */
        static final String FIND_BY_ID =
                SELECT_ALL + _WHERE_ + TBL_SERIES.dot(DBKey.PK_ID) + "=?";

        /**
         * Find a {@link Series} by Title.
         * The lookup is by EQUALITY and CASE-SENSITIVE.
         * Searches for a match of original OR reordered title.
         */
        static final String FIND_BY_NAME =
                SELECT_ALL
                + _WHERE_ + TBL_SERIES.dot(DBKey.SERIES.TITLE) + "=?" + _COLLATION
                + _OR_ + TBL_SERIES.dot(DBKey.SERIES.TITLE) + "=?" + _COLLATION;

        /**
         * All {@link Series}s for a {@link Book}.
         * Ordered by position in the book.
         */
        static final String FIND_BY_BOOK_ID =
                SELECT_DISTINCT_ + TBL_SERIES.dotAs(DBKey.PK_ID,
                                                    DBKey.SERIES.TITLE,
                                                    DBKey.SERIES.TITLE_OB,
                                                    DBKey.SERIES.COMPLETE)
                + ',' + TBL_BOOK_SERIES.dotAs(DBKey.SERIES.BOOK_SERIES_NUMBER,
                                              DBKey.SERIES.BOOK_SERIES_POSITION)
                // LEFT OUTER JOIN, columns default to NULL
                + ',' + TBL_SERIES_PUBLICATION_FREQUENCY
                        .dotAs(DBKey.PUBLICATION_FREQUENCY.TYPE,
                               DBKey.PUBLICATION_FREQUENCY.CADENCE,
                               DBKey.PUBLICATION_FREQUENCY.IS_ORDINAL)

                + _FROM_ + TBL_BOOK_SERIES.startJoin(TBL_SERIES)
                + TBL_SERIES.leftOuterJoin(TBL_SERIES_PUBLICATION_FREQUENCY)
                + _WHERE_ + TBL_BOOK_SERIES.dot(DBKey.FK_BOOK) + "=?"
                + _ORDER_BY_ + TBL_BOOK_SERIES.dot(DBKey.SERIES.BOOK_SERIES_POSITION);


        /** All {@link Book}s (id only) for a given {@link Series}. */
        static final String FIND_BOOK_IDS_BY_SERIES_ID =
                SELECT_ + TBL_BOOK_SERIES.dotAs(DBKey.FK_BOOK)
                + _FROM_ + TBL_BOOK_SERIES.as()
                + _WHERE_ + TBL_BOOK_SERIES.dot(DBKey.FK_SERIES) + "=?";

        /** Get a list of {@link Series} names for use in a dropdown selection. */
        static final String SELECT_ALL_NAMES =
                SELECT_DISTINCT_ + DBKey.SERIES.TITLE
                + ',' + DBKey.SERIES.TITLE_OB
                + _FROM_ + TBL_SERIES.getName()
                + _ORDER_BY_ + DBKey.SERIES.TITLE_OB + _COLLATION;

        /**
         * Get the language (ISO3) code for a Series.
         * This is defined as the language code for the first book in the Series.
         */
        static final String GET_LANGUAGE =
                SELECT_ + TBL_BOOKS.dotAs(DBKey.LANGUAGE)
                + _FROM_ + TBL_BOOK_SERIES.startJoin(TBL_BOOKS)
                + _WHERE_ + TBL_BOOK_SERIES.dot(DBKey.FK_SERIES) + "=?"
                + _ORDER_BY_ + TBL_BOOK_SERIES.dot(DBKey.SERIES.BOOK_SERIES_NUMBER)
                + " LIMIT 1";

        static final String REPOSITION =
                SELECT_ + DBKey.FK_BOOK
                + _FROM_
                + '(' + SELECT_ + DBKey.FK_BOOK
                + ",MIN(" + DBKey.SERIES.BOOK_SERIES_POSITION + ')' + _AS_ + "mp"
                + _FROM_ + TBL_BOOK_SERIES.getName()
                + _GROUP_BY_ + DBKey.FK_BOOK
                + ')'
                + _WHERE_ + "mp>1";

        /** All Series for a rebuild of the {@link DBKey.SERIES#TITLE_OB} column. */
        private static final String OB_REBUILD_TITLES =
                SELECT_ + DBKey.PK_ID
                + ',' + DBKey.SERIES.TITLE
                + ',' + DBKey.SERIES.TITLE_OB
                + _FROM_ + TBL_SERIES.getName();
        private static final String OB_REBUILD =
                UPDATE_ + TBL_SERIES.getName() + _SET_ + DBKey.SERIES.TITLE_OB + "=?"
                + _WHERE_ + DBKey.PK_ID + "=?";
    }
}
