DROP INDEX IF EXISTS chapters_manga_id_index;
DROP INDEX IF EXISTS chapters_unread_by_manga_index;
DROP INDEX IF EXISTS history_history_chapter_id_index;
DROP INDEX IF EXISTS library_favorite_index;
DROP INDEX IF EXISTS mangas_url_index;

DROP INDEX IF EXISTS search_metadata_uploader_index;
DROP INDEX IF EXISTS search_metadata_indexed_extra_index;
DROP INDEX IF EXISTS search_tags_manga_id_index;
DROP INDEX IF EXISTS search_tags_namespace_name_index;
DROP INDEX IF EXISTS search_titles_manga_id_index;
DROP INDEX IF EXISTS search_titles_title_index;
DROP INDEX IF EXISTS merged_merge_id_index;
DROP INDEX IF EXISTS feed_saved_search_saved_search_index;

ALTER TABLE mangas RENAME TO manga_temp;
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 NOT NULL,
    last_update INTEGER AS Long,
    next_update INTEGER AS Long,
    initialized INTEGER AS Boolean NOT NULL,
    viewer INTEGER NOT NULL,
    chapter_flags INTEGER NOT NULL,
    cover_last_modified INTEGER AS Long NOT NULL,
    date_added INTEGER AS Long NOT NULL,
    filtered_scanlators TEXT
);
INSERT INTO mangas
SELECT _id,source,url,artist,author,description,genre,title,status,thumbnail_url,favorite,last_update,next_update,initialized,viewer,chapter_flags,cover_last_modified,date_added,filtered_scanlators
FROM manga_temp;

ALTER TABLE categories RENAME TO categories_temp;
CREATE TABLE categories(
    _id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    sort INTEGER NOT NULL,
    flags INTEGER NOT NULL,
    manga_order TEXT NOT NULL
);
INSERT INTO categories
SELECT _id,name,sort,flags,manga_order
FROM categories_temp;

ALTER TABLE chapters RENAME TO chapters_temp;
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,
    chapter_number REAL AS Float NOT NULL,
    source_order INTEGER NOT NULL,
    date_fetch INTEGER AS Long NOT NULL,
    date_upload INTEGER AS Long NOT NULL,
    FOREIGN KEY(manga_id) REFERENCES mangas (_id)
    ON DELETE CASCADE
);
INSERT INTO chapters
SELECT _id,manga_id,url,name,scanlator,read,bookmark,last_page_read,chapter_number,source_order,date_fetch,date_upload
FROM chapters_temp;

ALTER TABLE history RENAME TO history_temp;
CREATE TABLE history(
    history_id INTEGER NOT NULL PRIMARY KEY,
    history_chapter_id INTEGER NOT NULL UNIQUE,
    history_last_read INTEGER AS Long,
    history_time_read INTEGER AS Long,
    FOREIGN KEY(history_chapter_id) REFERENCES chapters (_id)
    ON DELETE CASCADE
);
INSERT INTO history
SELECT history_id, history_chapter_id, history_last_read, history_time_read
FROM history_temp;

ALTER TABLE mangas_categories RENAME TO mangas_categories_temp;
CREATE TABLE mangas_categories(
    _id INTEGER NOT NULL PRIMARY KEY,
    manga_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    FOREIGN KEY(category_id) REFERENCES categories (_id)
    ON DELETE CASCADE,
    FOREIGN KEY(manga_id) REFERENCES mangas (_id)
    ON DELETE CASCADE
);
INSERT INTO mangas_categories
SELECT _id, manga_id, category_id
FROM mangas_categories_temp;

ALTER TABLE manga_sync RENAME TO manga_sync_temp;
CREATE TABLE manga_sync(
    _id INTEGER NOT NULL PRIMARY KEY,
    manga_id INTEGER NOT NULL,
    sync_id INTEGER NOT NULL,
    remote_id INTEGER NOT NULL,
    library_id INTEGER,
    title TEXT NOT NULL,
    last_chapter_read REAL NOT NULL,
    total_chapters INTEGER NOT NULL,
    status INTEGER NOT NULL,
    score REAL AS Float NOT NULL,
    remote_url TEXT NOT NULL,
    start_date INTEGER AS Long NOT NULL,
    finish_date INTEGER AS Long NOT NULL,
    UNIQUE (manga_id, sync_id) ON CONFLICT REPLACE,
    FOREIGN KEY(manga_id) REFERENCES mangas (_id)
    ON DELETE CASCADE
);
INSERT INTO manga_sync
SELECT _id, manga_id, sync_id, remote_id, library_id, title, last_chapter_read, total_chapters, status, score, remote_url, start_date, finish_date
FROM manga_sync_temp;

ALTER TABLE eh_favorites RENAME TO eh_favorites_temp;
CREATE TABLE eh_favorites (
    _id INTEGER NOT NULL PRIMARY KEY,
    title TEXT NOT NULL,
    gid TEXT NOT NULL,
    token TEXT NOT NULL,
    category INTEGER NOT NULL
);
INSERT INTO eh_favorites
SELECT _id,title,gid,token,category
FROM eh_favorites_temp;

ALTER TABLE saved_search RENAME TO saved_search_temp;
CREATE TABLE saved_search(
    _id INTEGER NOT NULL PRIMARY KEY,
    source INTEGER NOT NULL,
    name TEXT NOT NULL,
    query TEXT,
    filters_json TEXT
);
INSERT INTO saved_search
SELECT _id,source,name,query,filters_json
FROM saved_search_temp;

ALTER TABLE feed_saved_search RENAME TO feed_saved_search_temp;
CREATE TABLE feed_saved_search (
    _id INTEGER NOT NULL PRIMARY KEY,
    source INTEGER NOT NULL,
    saved_search INTEGER,
    global INTEGER AS Boolean NOT NULL,
    FOREIGN KEY(saved_search) REFERENCES saved_search (_id)
    ON DELETE CASCADE
);
INSERT INTO feed_saved_search
SELECT _id, source, saved_search, global
FROM feed_saved_search_temp;

ALTER TABLE search_metadata RENAME TO search_metadata_temp;
CREATE TABLE search_metadata (
    manga_id INTEGER NOT NULL PRIMARY KEY,
    uploader TEXT,
    extra TEXT NOT NULL,
    indexed_extra TEXT,
    extra_version INTEGER AS Int NOT NULL,
    FOREIGN KEY(manga_id) REFERENCES mangas (_id)
    ON DELETE CASCADE
);
INSERT INTO search_metadata
SELECT manga_id, uploader, extra, indexed_extra, extra_version
FROM search_metadata_temp;

ALTER TABLE search_tags RENAME TO search_tags_temp;
CREATE TABLE search_tags (
    _id INTEGER NOT NULL PRIMARY KEY,
    manga_id INTEGER NOT NULL,
    namespace TEXT,
    name TEXT NOT NULL,
    type INTEGER AS Int NOT NULL,
    FOREIGN KEY(manga_id) REFERENCES mangas (_id)
    ON DELETE CASCADE
);
INSERT INTO search_tags
SELECT _id, manga_id, namespace, name, type
FROM search_tags_temp;

ALTER TABLE search_titles RENAME TO search_titles_temp;
CREATE TABLE search_titles (
    _id INTEGER NOT NULL PRIMARY KEY,
    manga_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    type INTEGER AS Int NOT NULL,
    FOREIGN KEY(manga_id) REFERENCES mangas (_id)
    ON DELETE CASCADE
);
INSERT INTO search_titles
SELECT _id, manga_id, title, type
FROM search_titles_temp;

ALTER TABLE merged RENAME TO merged_temp;
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
);
INSERT INTO merged
SELECT _id,info_manga,get_chapter_updates,chapter_sort_mode,chapter_priority,download_chapters,merge_id,merge_url,manga_id,manga_url,manga_source
FROM merged_temp;

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;
CREATE INDEX history_history_chapter_id_index ON history(history_chapter_id);
CREATE INDEX library_favorite_index ON mangas(favorite) WHERE favorite = 1;
CREATE INDEX mangas_url_index ON mangas(url);
CREATE INDEX search_metadata_uploader_index ON search_metadata(uploader);
CREATE INDEX search_metadata_indexed_extra_index ON search_metadata(indexed_extra);
CREATE INDEX search_tags_manga_id_index ON search_tags(manga_id);
CREATE INDEX search_tags_namespace_name_index ON search_tags(namespace, name);
CREATE INDEX search_titles_manga_id_index ON search_titles(manga_id);
CREATE INDEX search_titles_title_index ON search_titles(title);
CREATE INDEX merged_merge_id_index ON merged(merge_id);
CREATE INDEX feed_saved_search_saved_search_index ON feed_saved_search(saved_search);

CREATE VIEW IF NOT EXISTS historyView AS
SELECT
history.history_id AS id,
mangas._id AS mangaId,
chapters._id AS chapterId,
mangas.title,
mangas.thumbnail_url AS thumnailUrl,
chapters.chapter_number AS chapterNumber,
history.history_last_read AS readAt,
max_last_read.history_last_read AS maxReadAt,
max_last_read.history_chapter_id AS maxReadAtChapterId
FROM mangas
JOIN chapters
ON mangas._id = chapters.manga_id
JOIN history
ON chapters._id = history.history_chapter_id
JOIN (
SELECT chapters.manga_id,chapters._id AS history_chapter_id, MAX(history.history_last_read) AS history_last_read
FROM chapters JOIN history
ON chapters._id = history.history_chapter_id
GROUP BY chapters.manga_id
) AS max_last_read
ON chapters.manga_id = max_last_read.manga_id;

DROP TABLE IF EXISTS manga_sync_temp;
DROP TABLE IF EXISTS mangas_categories_temp;
DROP TABLE IF EXISTS history_temp;
DROP TABLE IF EXISTS chapters_temp;
DROP TABLE IF EXISTS categories_temp;
DROP TABLE IF EXISTS eh_favorites_temp;
DROP TABLE IF EXISTS saved_search_temp;
DROP TABLE IF EXISTS feed_saved_search_temp;
DROP TABLE IF EXISTS search_metadata_temp;
DROP TABLE IF EXISTS search_tags_temp;
DROP TABLE IF EXISTS search_titles_temp;
DROP TABLE IF EXISTS merged_temp;
DROP TABLE IF EXISTS manga_temp;
