--1
CREATE TABLE AUDIOBOOK_CHAPTERS(
       _id INTEGER PRIMARY KEY,
       ASIN VARCHAR(255) NOT NULL,
       ACR VARCHAR(255),
       TITLE VARCHAR(255) NOT NULL,
       START_TIME_MS INTEGER,
       UNIQUE(ASIN, ACR, START_TIME_MS) ON CONFLICT REPLACE
);

CREATE TABLE AUDIOBOOK_FORMATS(
       _id INTEGER PRIMARY KEY,
       ASIN VARCHAR(255) NOT NULL,
       FORMAT_VALUE VARCHAR(255) NOT NULL
);

CREATE TABLE AUDIOBOOK_CONTRIBUTORS(
       _id INTEGER PRIMARY KEY,
       ASIN VARCHAR(255) NOT NULL,
       FIRST_NAME VARCHAR(255),
       LAST_NAME VARCHAR(255),
       CONTRIBUTOR_TYPE VARCHAR(255) NOT NULL,
       POSITION INTEGER
);

CREATE TABLE AUDIOBOOKS(
       _id INTEGER PRIMARY KEY,
       ASIN VARCHAR(255)  NOT NULL,
       PARENT_ASIN VARCHAR(255),
       PARENT_PRODUCT_ID VARCHAR(255),
       SORTABLE_CONTRIBUTOR_AUTHOR VARCHAR(255) NOT NULL,
       SORTABLE_CONTRIBUTOR_NARRATOR VARCHAR(255) NOT NULL,
       PRODUCT_ID VARCHAR(255),
       TITLE VARCHAR(255) NOT NULL,
       SUB_TITLE VARCHAR(255),
       COPYRIGHT VARCHAR(255) NOT NULL,
       PUBLISH_DATE_MS INTEGER,
       DURATION_MS INTEGER,
       PUBLISHER VARCHAR(255) NOT NULL,
       PUBLISHER_SUMMARY VARCHAR(255) NOT NULL,
       CONTENT_TYPE VARCHAR(255) NOT NULL,
       DESCRIPTION VARCHAR(255) NOT NULL,
       PART_NUMBER INTEGER DEFAULT 1,
       READ_ALONG_SUPPORT INTEGER DEFAULT 0,
       FORMAT VARCHAR(255),
       UNIQUE(ASIN) ON CONFLICT REPLACE
);

CREATE INDEX INDEX_AUDIOBOOKS_ASIN ON AUDIOBOOKS(ASIN);
CREATE INDEX INDEX_AUDIOBOOK_CONTRIBUTORS_ASIN ON AUDIOBOOK_CONTRIBUTORS(ASIN);
CREATE INDEX INDEX_AUDIOBOOK_FORMATS_ASIN ON AUDIOBOOK_FORMATS(ASIN);
CREATE INDEX INDEX_AUDIOBOOK_CHAPTERS_ASIN ON AUDIOBOOK_CHAPTERS(ASIN);

CREATE TRIGGER DELETE_CHAPTERS_ON_AUDIOBOOK_DELETE AFTER DELETE ON AUDIOBOOKS BEGIN DELETE FROM AUDIOBOOK_CHAPTERS WHERE ASIN = old.ASIN; END;
CREATE TRIGGER DELETE_FORMATS_ON_AUDIOBOOK_DELETE AFTER DELETE ON AUDIOBOOKS BEGIN DELETE FROM AUDIOBOOK_FORMATS WHERE ASIN = old.ASIN; END;
CREATE TRIGGER DELETE_CONTRIBUTORS_ON_AUDIOBOOK_DELETE AFTER DELETE ON AUDIOBOOKS BEGIN DELETE FROM AUDIOBOOK_CONTRIBUTORS WHERE ASIN = old.ASIN; END;

--2
CREATE TABLE RELATIONSHIP(
    _id INTEGER PRIMARY KEY,
    CUSTOMER_ID VARCHAR(255) NOT NULL,
    ASIN VARCHAR(255) NOT NULL,
    RELATIONSHIP_TYPE VARCHAR(255),
    RELATIONSHIP_ESTABLISHED_DATE DATE,
    LAST_ACCESSED_DATE DATE,
    UNIQUE(ASIN, CUSTOMER_ID) ON CONFLICT IGNORE
);

CREATE INDEX INDEX_CUSTOMER_ID ON RELATIONSHIP(CUSTOMER_ID);

ALTER TABLE AUDIOBOOKS RENAME TO AUDIOBOOKS_TEMP;

CREATE TABLE AUDIOBOOKS(
       _id INTEGER PRIMARY KEY,
       ASIN VARCHAR(255)  NOT NULL,
       PARENT_ASIN VARCHAR(255),
       PARENT_PRODUCT_ID VARCHAR(255),
       SORTABLE_CONTRIBUTOR_AUTHOR VARCHAR(255) NOT NULL,
       SORTABLE_CONTRIBUTOR_NARRATOR VARCHAR(255) NOT NULL,
       PRODUCT_ID VARCHAR(255),
       TITLE VARCHAR(255) NOT NULL,
       SUB_TITLE VARCHAR(255),
       COPYRIGHT VARCHAR(255),
       PUBLISH_DATE_MS INTEGER,
       DURATION_MS INTEGER,
       PUBLISHER VARCHAR(255),
       PUBLISHER_SUMMARY VARCHAR(255),
       CONTENT_TYPE VARCHAR(255) NOT NULL,
       DESCRIPTION VARCHAR(255),
       PART_NUMBER INTEGER DEFAULT 1,
       READ_ALONG_SUPPORT INTEGER DEFAULT 0,
       FORMAT VARCHAR(255),
       UNIQUE(ASIN) ON CONFLICT REPLACE
);

INSERT INTO AUDIOBOOKS (
        _id,
        ASIN,
        PARENT_ASIN,
        PARENT_PRODUCT_ID,
        SORTABLE_CONTRIBUTOR_AUTHOR,
        SORTABLE_CONTRIBUTOR_NARRATOR,
        PRODUCT_ID,
        TITLE,
        SUB_TITLE,
        COPYRIGHT,
        PUBLISH_DATE_MS,
        DURATION_MS,
        PUBLISHER,
        PUBLISHER_SUMMARY,
        CONTENT_TYPE,
        DESCRIPTION,
        PART_NUMBER,
        READ_ALONG_SUPPORT,
        FORMAT
    )
    SELECT
        _id,
        ASIN,
        PARENT_ASIN,
        PARENT_PRODUCT_ID,
        SORTABLE_CONTRIBUTOR_AUTHOR,
        SORTABLE_CONTRIBUTOR_NARRATOR,
        PRODUCT_ID,
        TITLE,
        SUB_TITLE,
        COPYRIGHT,
        PUBLISH_DATE_MS,
        DURATION_MS,
        PUBLISHER,
        PUBLISHER_SUMMARY,
        CONTENT_TYPE,
        DESCRIPTION,
        PART_NUMBER,
        READ_ALONG_SUPPORT,
        FORMAT
    FROM AUDIOBOOKS_TEMP;

DROP TABLE AUDIOBOOKS_TEMP;

--3
ALTER TABLE AUDIOBOOKS ADD COLUMN SAMPLE_URL VARCHAR(255);
ALTER TABLE AUDIOBOOKS ADD COLUMN PDF_LINK TEXT;

--4
ALTER TABLE AUDIOBOOK_CHAPTERS RENAME TO AUDIOBOOK_CHAPTERS_TEMP;

CREATE TABLE AUDIOBOOK_CHAPTERS(
       _id INTEGER PRIMARY KEY,
       ASIN VARCHAR(255) NOT NULL,
       ACR VARCHAR(255) NOT NULL DEFAULT '',
       TITLE VARCHAR(255) NOT NULL,
       START_TIME_MS INTEGER,
       UNIQUE(ASIN, ACR, START_TIME_MS) ON CONFLICT REPLACE
);

INSERT INTO AUDIOBOOK_CHAPTERS (
        _id,
        ASIN,
        ACR,
        TITLE,
        START_TIME_MS
    )
    SELECT
        _id,
        ASIN,
        ifnull(ACR, ''),
        TITLE,
        START_TIME_MS
    FROM AUDIOBOOK_CHAPTERS_TEMP;

DROP TABLE AUDIOBOOK_CHAPTERS_TEMP;

CREATE INDEX INDEX_AUDIOBOOK_CHAPTERS_ASIN ON AUDIOBOOK_CHAPTERS(ASIN);

CREATE TRIGGER DELETE_CHAPTERS_ON_AUDIOBOOK_DELETE AFTER DELETE ON AUDIOBOOKS BEGIN DELETE FROM AUDIOBOOK_CHAPTERS WHERE ASIN = old.ASIN; END;

--5
ALTER TABLE AUDIOBOOK_CHAPTERS ADD COLUMN LEVEL INTEGER DEFAULT 0;
ALTER TABLE AUDIOBOOK_CHAPTERS ADD COLUMN CHAPTER_ID INTEGER;
ALTER TABLE AUDIOBOOK_CHAPTERS ADD COLUMN PARENT_ID INTEGER;

--6
ALTER TABLE AUDIOBOOK_CHAPTERS ADD COLUMN LENGTH_MS INTEGER DEFAULT 0;

--7
ALTER TABLE AUDIOBOOK_CHAPTERS ADD COLUMN GLOBAL_INDEX INTEGER DEFAULT 0;

--8
DROP INDEX INDEX_AUDIOBOOK_CHAPTERS_ASIN;

DROP TRIGGER DELETE_CHAPTERS_ON_AUDIOBOOK_DELETE;

ALTER TABLE AUDIOBOOK_CHAPTERS RENAME TO AUDIOBOOK_CHAPTERS_TEMP;

CREATE TABLE AUDIOBOOK_CHAPTERS(
       _id INTEGER PRIMARY KEY,
       ASIN VARCHAR(255) NOT NULL,
       ACR VARCHAR(255) NOT NULL DEFAULT '',
       TITLE VARCHAR(255) NOT NULL,
       START_TIME_MS INTEGER,
       LEVEL INTEGER DEFAULT 0,
       CHAPTER_ID INTEGER,
       PARENT_ID INTEGER,
       LENGTH_MS INTEGER DEFAULT 0,
       GLOBAL_INDEX INTEGER DEFAULT 0,
       UNIQUE(ASIN, ACR, START_TIME_MS, GLOBAL_INDEX) ON CONFLICT REPLACE
);

INSERT INTO AUDIOBOOK_CHAPTERS (
        _id,
        ASIN,
        ACR,
        TITLE,
        START_TIME_MS,
        LEVEL,
        CHAPTER_ID,
        PARENT_ID,
        LENGTH_MS,
        GLOBAL_INDEX
    )
    SELECT
        _id,
        ASIN,
        ifnull(ACR, ''),
        TITLE,
        START_TIME_MS,
        LEVEL,
        CHAPTER_ID,
        PARENT_ID,
        LENGTH_MS,
        GLOBAL_INDEX
    FROM AUDIOBOOK_CHAPTERS_TEMP;



DROP TABLE AUDIOBOOK_CHAPTERS_TEMP;

CREATE INDEX INDEX_AUDIOBOOK_CHAPTERS_ASIN ON AUDIOBOOK_CHAPTERS(ASIN);

CREATE TRIGGER DELETE_CHAPTERS_ON_AUDIOBOOK_DELETE AFTER DELETE ON AUDIOBOOKS BEGIN DELETE FROM AUDIOBOOK_CHAPTERS WHERE ASIN = old.ASIN; END;
