CREATE TABLE merged(
    _id INTEGER NOT NULL PRIMARY KEY,
    info_manga INTEGER AS Boolean NOT NULL,
    get_chapter_updates INTEGER AS Boolean NOT NULL,
    chapter_sort_mode INTEGER NOT NULL,
    chapter_priority INTEGER NOT NULL,
    download_chapters INTEGER AS Boolean NOT NULL,
    merge_id INTEGER NOT NULL,
    merge_url TEXT NOT NULL,
    manga_id INTEGER,
    manga_url TEXT NOT NULL,
    manga_source INTEGER NOT NULL,
    FOREIGN KEY(manga_id) REFERENCES mangas (_id)
    ON DELETE SET NULL,
    FOREIGN KEY(merge_id) REFERENCES mangas (_id)
    ON DELETE CASCADE
);

CREATE INDEX merged_merge_id_index ON merged(merge_id);

selectByMergeId:
SELECT * FROM merged WHERE merge_id = ?;

selectByMergeUrl:
SELECT * FROM merged WHERE merge_url = ?;

deleteByMergeId:
DELETE FROM merged WHERE merge_id = ?;

selectMergedMangasById:
SELECT mangas.*
FROM (
    SELECT manga_id FROM merged WHERE merge_id = ?
) AS M
JOIN mangas
ON mangas._id = M.manga_id;

selectMergedMangasForDownloadingById:
SELECT mangas.*
FROM (
    SELECT manga_id FROM merged WHERE merge_id = ? AND download_chapters = 1
) AS M
JOIN mangas
ON mangas._id = M.manga_id;

selectMergedMangasByUrl:
SELECT mangas.*
FROM (
    SELECT manga_id FROM merged WHERE merge_url = ?
) AS M
JOIN mangas
ON mangas._id = M.manga_id;

selectAllMergedMangas:
SELECT mangas.*
FROM (
    SELECT manga_id FROM merged
) AS M
JOIN mangas
ON mangas._id = M.manga_id;

deleteByMergeUrl:
DELETE FROM merged WHERE merge_url = ?;

selectAll:
SELECT * FROM merged;

selectChaptersByMergedId:
SELECT chapters.*
FROM (
    SELECT manga_id FROM merged WHERE merge_id = ?
) AS M
JOIN chapters
ON chapters.manga_id = M.manga_id;

insert:
INSERT INTO merged(
    info_manga,
    get_chapter_updates,
    chapter_sort_mode,
    chapter_priority,
    download_chapters,
    merge_id,
    merge_url,
    manga_id,
    manga_url,
    manga_source
)
VALUES (
    :infoManga,
    :getChapterUpdates,
    :chapterSortMode,
    :chapterPriority,
    :downloadChapters,
    :mergeId,
    :mergeUrl,
    :mangaId,
    :mangaUrl,
    :mangaSource
);

updateSettingsById:
UPDATE merged
SET
    get_chapter_updates = coalesce(:getChapterUpdates, get_chapter_updates),
    download_chapters = coalesce(:downloadChapters, download_chapters),
    info_manga = coalesce(:infoManga, info_manga),
    chapter_priority = coalesce(:chapterPriority, chapter_priority)
WHERE _id = :id;

deleteById:
DELETE FROM merged WHERE _id = ?;

deleteAll:
DELETE FROM merged;

selectLastInsertedRowId:
SELECT last_insert_rowid();