import kotlin.Boolean;

CREATE TABLE mangas(
    _id INTEGER NOT NULL PRIMARY KEY,
    source INTEGER NOT NULL,
    url TEXT NOT NULL,
    artist TEXT,
    author TEXT,
    description TEXT,
    genre TEXT,
    title TEXT NOT NULL,
    status INTEGER NOT NULL,
    thumbnail_url TEXT,
    favorite INTEGER AS Boolean NOT NULL,
    last_update INTEGER,
    initialized INTEGER AS Boolean NOT NULL,
    viewer INTEGER NOT NULL,
    hide_title INTEGER AS Boolean NOT NULL,
    chapter_flags INTEGER NOT NULL,
    date_added INTEGER,
    filtered_scanlators TEXT,
    update_strategy INTEGER NOT NULL DEFAULT 0,
    cover_last_modified INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX mangas_url_index ON mangas(url);
CREATE INDEX library_favorite_index ON mangas(favorite) WHERE favorite = 1;

findAll:
SELECT *
FROM mangas;

findByUrlAndSource:
SELECT *
FROM mangas
WHERE url = :url AND source = :source;

findById:
SELECT *
FROM mangas
WHERE _id = :mangaId;

findDuplicateFavorite:
SELECT *
FROM mangas
WHERE favorite = 1 AND lower(title) = :title AND source != :source;

findFavorites:
SELECT *
FROM mangas
WHERE favorite = 1
ORDER BY title;

findReadNotFavorites:
SELECT *
FROM mangas
WHERE favorite = 0 AND _id IN (
    SELECT chapters.manga_id FROM chapters WHERE read = 1 OR last_page_read != 0
);

insert:
INSERT INTO mangas (source, url, artist, author, description, genre, title, status, thumbnail_url, favorite, last_update, initialized, viewer, hide_title, chapter_flags, date_added, filtered_scanlators, update_strategy, cover_last_modified)
VALUES (:source, :url, :artist, :author, :description, :genre, :title, :status, :thumbnailUrl, :favorite, :lastUpdate, :initialized, :viewer, :hideTitle, :chapterFlags, :dateAdded, :filteredScanlators, :updateStrategy, :coverLastModified);

update:
UPDATE mangas SET
    source = coalesce(:source, source),
    url = coalesce(:url, url),
    artist = coalesce(:artist, artist),
    author = coalesce(:author, author),
    description = coalesce(:description, description),
    genre = coalesce(:genre, genre),
    title = coalesce(:title, title),
    status = coalesce(:status, status),
    thumbnail_url = coalesce(:thumbnailUrl, thumbnail_url),
    favorite = coalesce(:favorite, favorite),
    last_update = coalesce(:lastUpdate, last_update),
    initialized = coalesce(:initialized, initialized),
    viewer = coalesce(:viewer, viewer),
    hide_title = coalesce(:hideTitle, hide_title),
    chapter_flags = coalesce(:chapterFlags, chapter_flags),
    date_added = coalesce(:dateAdded, date_added),
    filtered_scanlators = coalesce(:filteredScanlators, filtered_scanlators),
    update_strategy = coalesce(:updateStrategy, update_strategy),
    cover_last_modified = coalesce(:coverLastModified, cover_last_modified)
WHERE _id = :mangaId;

selectLastInsertedRowId:
SELECT last_insert_rowid();

deleteNotInLibraryBySourceIds:
DELETE FROM mangas
WHERE favorite = 0 AND source IN :sourceIds;

deleteNotInLibraryAndNotReadBySourceIds:
DELETE FROM mangas
WHERE favorite = 0 AND source IN :sourceIds AND _id NOT IN (
    SELECT chapters.manga_id FROM chapters WHERE read = 1 OR last_page_read != 0
);

getSourceIdsOfNotInLibrary:
SELECT source, count(*) AS count
FROM mangas WHERE favorite = 0
GROUP BY source;
