import kotlin.Boolean;

CREATE TABLE chapters(
    _id INTEGER NOT NULL PRIMARY KEY,
    manga_id INTEGER NOT NULL,
    url TEXT NOT NULL,
    name TEXT NOT NULL,
    scanlator TEXT,
    read INTEGER AS Boolean NOT NULL,
    bookmark INTEGER AS Boolean  NOT NULL,
    last_page_read INTEGER NOT NULL,
    pages_left INTEGER NOT NULL,
    chapter_number REAL NOT NULL,
    source_order INTEGER NOT NULL,
    date_fetch INTEGER NOT NULL,
    date_upload INTEGER NOT NULL,
    FOREIGN KEY(manga_id) REFERENCES mangas (_id)
    ON DELETE CASCADE
);

CREATE INDEX chapters_manga_id_index ON chapters(manga_id);
CREATE INDEX chapters_unread_by_manga_index ON chapters(manga_id, read) WHERE read = 0;

getChaptersByMangaId:
SELECT C.*
FROM chapters AS C
LEFT JOIN scanlators_view AS S
ON C.manga_id = S.manga_id
AND C.scanlator = S.name
WHERE C.manga_id = :manga_id
AND (
    :apply_filter = 0 OR S.name IS NULL
);

getChaptersById:
SELECT * FROM chapters
WHERE _id = :id;

getChaptersByUrl:
SELECT C.*
FROM chapters AS C
LEFT JOIN scanlators_view AS S
ON C.manga_id = S.manga_id
AND C.scanlator = S.name
WHERE C.url = :url
AND (
    :apply_filter = 0 OR S.name IS NULL
);

getChaptersByUrlAndMangaId:
SELECT C.*
FROM chapters AS C
LEFT JOIN scanlators_view AS S
ON C.manga_id = S.manga_id
AND C.scanlator = S.name
WHERE C.url = :url AND C.manga_id = :manga_id
AND (
    :apply_filter = 0 OR S.name IS NULL
);

findUnreadByMangaId:
SELECT C.*
FROM chapters AS C
LEFT JOIN scanlators_view AS S
ON C.manga_id = S.manga_id
AND C.scanlator = S.name
WHERE C.manga_id = :manga_id AND C.read = 0
AND (
    :apply_filter = 0 OR S.name IS NULL
);

getRecents:
SELECT
    M.*,
    C.*
FROM mangas AS M
JOIN chapters AS C
ON M._id = C.manga_id
LEFT JOIN scanlators_view AS S
ON C.manga_id = S.manga_id
AND C.scanlator = S.name
WHERE M.favorite = 1
AND C.date_fetch > M.date_added
AND lower(M.title) LIKE '%' || :search || '%'
AND (
    :apply_filter = 0 OR S.name IS NULL
)
ORDER BY C.date_fetch DESC
LIMIT :limit OFFSET :offset;

getScanlatorsByMangaId:
SELECT scanlator
FROM chapters
WHERE manga_id = :mangaId;

delete:
DELETE FROM chapters
WHERE _id = :chapterId;

update:
UPDATE chapters SET
    manga_id = coalesce(:mangaId, manga_id),
    url = coalesce(:url, url),
    name = coalesce(:name, name),
    scanlator = coalesce(:scanlator, scanlator),
    read = coalesce(:read, read),
    bookmark = coalesce(:bookmark, bookmark),
    last_page_read = coalesce(:lastPageRead, last_page_read),
    pages_left = coalesce(:pagesLeft, pages_left),
    chapter_number = coalesce(:chapterNumber, chapter_number),
    source_order = coalesce(:sourceOrder, source_order),
    date_fetch = coalesce(:dateFetch, date_fetch),
    date_upload = coalesce(:dateUpload, date_upload)
WHERE _id = :chapterId;

fixSourceOrder:
UPDATE chapters SET source_order = :sourceOrder
WHERE url = :url AND manga_id = :mangaId;

insert:
INSERT INTO chapters (manga_id, url, name, scanlator, read, bookmark, last_page_read, pages_left, chapter_number, source_order, date_fetch, date_upload)
VALUES (:mangaId, :url, :name, :scanlator, :read, :bookmark, :lastPageRead, :pagesLeft, :chapterNumber, :sourceOrder, :dateFetch, :dateUpload);

selectLastInsertedRowId:
SELECT last_insert_rowid();
