-- This is a multi-steps migration, mixing 2 SQL migrations and a Java migration in-between
CREATE INDEX idx__thumbnail_book__book_id on THUMBNAIL_BOOK (BOOK_ID);

-- Remove column THUMBNAIL from table MEDIA
PRAGMA foreign_keys= OFF;

ALTER TABLE MEDIA
    RENAME TO _MEDIA_OLD;

CREATE TABLE MEDIA
(
    MEDIA_TYPE         varchar  NULL,
    STATUS             varchar  NOT 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)
);

INSERT INTO MEDIA (MEDIA_TYPE, STATUS, CREATED_DATE, LAST_MODIFIED_DATE, COMMENT, BOOK_ID, PAGE_COUNT)
SELECT MEDIA_TYPE, STATUS, CREATED_DATE, LAST_MODIFIED_DATE, COMMENT, BOOK_ID, PAGE_COUNT
FROM _MEDIA_OLD;

DROP TABLE _MEDIA_OLD;

PRAGMA foreign_keys= ON;
