CREATE TABLE LIBRARY
(
    ID                          varchar  NOT NULL PRIMARY KEY,
    CREATED_DATE                datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LAST_MODIFIED_DATE          datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    NAME                        varchar  NOT NULL,
    ROOT                        varchar  NOT NULL,
    IMPORT_COMICINFO_BOOK       boolean  NOT NULL DEFAULT 1,
    IMPORT_COMICINFO_SERIES     boolean  NOT NULL DEFAULT 1,
    IMPORT_COMICINFO_COLLECTION boolean  NOT NULL DEFAULT 1,
    IMPORT_EPUB_BOOK            boolean  NOT NULL DEFAULT 1,
    IMPORT_EPUB_SERIES          boolean  NOT NULL DEFAULT 1
);
CREATE TABLE USER
(
    ID                   varchar  NOT NULL PRIMARY KEY,
    CREATED_DATE         datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LAST_MODIFIED_DATE   datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EMAIL                varchar  NOT NULL UNIQUE,
    PASSWORD             varchar  NOT NULL,
    SHARED_ALL_LIBRARIES boolean  NOT NULL DEFAULT 1,
    ROLE_ADMIN           boolean  NOT NULL DEFAULT 0,
    ROLE_FILE_DOWNLOAD   boolean  NOT NULL DEFAULT 1,
    ROLE_PAGE_STREAMING  boolean  NOT NULL DEFAULT 1
);
CREATE TABLE USER_LIBRARY_SHARING
(
    USER_ID    varchar NOT NULL,
    LIBRARY_ID varchar NOT NULL,
    PRIMARY KEY (USER_ID, LIBRARY_ID),
    FOREIGN KEY (USER_ID) REFERENCES USER (ID),
    FOREIGN KEY (LIBRARY_ID) REFERENCES LIBRARY (ID)
);
CREATE TABLE SERIES
(
    ID                 varchar  NOT NULL PRIMARY KEY,
    CREATED_DATE       datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LAST_MODIFIED_DATE datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FILE_LAST_MODIFIED datetime NOT NULL,
    NAME               varchar  NOT NULL,
    URL                varchar  NOT NULL,
    LIBRARY_ID         varchar  NOT NULL,
    FOREIGN KEY (LIBRARY_ID) REFERENCES LIBRARY (ID)
);
CREATE TABLE SERIES_METADATA
(
    CREATED_DATE       datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LAST_MODIFIED_DATE datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    STATUS             varchar  NOT NULL,
    STATUS_LOCK        boolean  NOT NULL DEFAULT 0,
    TITLE              varchar  NOT NULL,
    TITLE_LOCK         boolean  NOT NULL DEFAULT 0,
    TITLE_SORT         varchar  NOT NULL,
    TITLE_SORT_LOCK    boolean  NOT NULL DEFAULT 0,
    SERIES_ID          varchar  NOT NULL PRIMARY KEY,
    FOREIGN KEY (SERIES_ID) REFERENCES SERIES (ID)
);
CREATE TABLE BOOK
(
    ID                 varchar  NOT NULL PRIMARY KEY,
    CREATED_DATE       datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LAST_MODIFIED_DATE datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FILE_LAST_MODIFIED datetime NOT NULL,
    NAME               varchar  NOT NULL,
    URL                varchar  NOT NULL,
    SERIES_ID          varchar  NOT NULL,
    FILE_SIZE          int8     NOT NULL DEFAULT 0,
    NUMBER             int      NOT NULL DEFAULT 0,
    LIBRARY_ID         varchar  NOT NULL,
    FOREIGN KEY (LIBRARY_ID) REFERENCES LIBRARY (ID),
    FOREIGN KEY (SERIES_ID) REFERENCES SERIES (ID)
);
CREATE TABLE MEDIA
(
    MEDIA_TYPE         varchar  NULL,
    STATUS             varchar  NOT NULL,
    THUMBNAIL          blob     NULL,
    CREATED_DATE       datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LAST_MODIFIED_DATE datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    COMMENT            varchar  NULL,
    BOOK_ID            varchar  NOT NULL PRIMARY KEY,
    PAGE_COUNT         int      NOT NULL DEFAULT 0,
    FOREIGN KEY (BOOK_ID) REFERENCES BOOK (ID)
);
CREATE TABLE MEDIA_PAGE
(
    FILE_NAME  varchar NOT NULL,
    MEDIA_TYPE varchar NOT NULL,
    NUMBER     int     NOT NULL,
    BOOK_ID    varchar NOT NULL,
    PRIMARY KEY (BOOK_ID, NUMBER),
    FOREIGN KEY (BOOK_ID) REFERENCES BOOK (ID)
);
CREATE TABLE MEDIA_FILE
(
    FILE_NAME varchar NOT NULL,
    BOOK_ID   varchar NOT NULL,
    FOREIGN KEY (BOOK_ID) REFERENCES BOOK (ID)
);
CREATE TABLE BOOK_METADATA
(
    CREATED_DATE           datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LAST_MODIFIED_DATE     datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    AGE_RATING             int      NULL,
    AGE_RATING_LOCK        boolean  NOT NULL DEFAULT 0,
    NUMBER                 varchar  NOT NULL,
    NUMBER_LOCK            boolean  NOT NULL DEFAULT 0,
    NUMBER_SORT            real     NOT NULL,
    NUMBER_SORT_LOCK       boolean  NOT NULL DEFAULT 0,
    PUBLISHER              varchar  NOT NULL DEFAULT '',
    PUBLISHER_LOCK         boolean  NOT NULL DEFAULT 0,
    READING_DIRECTION      varchar  NULL,
    READING_DIRECTION_LOCK boolean  NOT NULL DEFAULT 0,
    RELEASE_DATE           date     NULL,
    RELEASE_DATE_LOCK      boolean  NOT NULL DEFAULT 0,
    SUMMARY                varchar  NOT NULL DEFAULT '',
    SUMMARY_LOCK           boolean  NOT NULL DEFAULT 0,
    TITLE                  varchar  NOT NULL,
    TITLE_LOCK             boolean  NOT NULL DEFAULT 0,
    AUTHORS_LOCK           boolean  NOT NULL DEFAULT 0,
    BOOK_ID                varchar  NOT NULL PRIMARY KEY,
    FOREIGN KEY (BOOK_ID) REFERENCES BOOK (ID)
);
CREATE TABLE BOOK_METADATA_AUTHOR
(
    NAME    varchar NOT NULL,
    ROLE    varchar NOT NULL,
    BOOK_ID varchar NOT NULL,
    FOREIGN KEY (BOOK_ID) REFERENCES BOOK (ID)
);
CREATE TABLE READ_PROGRESS
(
    BOOK_ID            varchar  NOT NULL,
    USER_ID            varchar  NOT NULL,
    CREATED_DATE       datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LAST_MODIFIED_DATE datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PAGE               int      NOT NULL,
    COMPLETED          boolean  NOT NULL,
    PRIMARY KEY (BOOK_ID, USER_ID),
    FOREIGN KEY (BOOK_ID) REFERENCES BOOK (ID),
    FOREIGN KEY (USER_ID) REFERENCES USER (ID)
);
CREATE TABLE COLLECTION
(
    ID                 varchar  NOT NULL PRIMARY KEY,
    NAME               varchar  NOT NULL,
    ORDERED            boolean  NOT NULL DEFAULT 0,
    SERIES_COUNT       int      NOT NULL,
    CREATED_DATE       datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LAST_MODIFIED_DATE datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE COLLECTION_SERIES
(
    COLLECTION_ID varchar NOT NULL,
    SERIES_ID     varchar NOT NULL,
    NUMBER        int     NOT NULL,
    PRIMARY KEY (COLLECTION_ID, SERIES_ID),
    FOREIGN KEY (COLLECTION_ID) REFERENCES COLLECTION (ID),
    FOREIGN KEY (SERIES_ID) REFERENCES SERIES (ID)
);
