create table author
(
    id int auto_increment primary key,
    name        varchar(255) not null,
    author_id   varchar(100) not null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint author_id_unique unique (author_id)
);

create table book
(
    id int auto_increment primary key,
    book_name     varchar(255) not null,
    book_id       varchar(100) not null,
    book_md5      varchar(32)  not null,
    language      varchar(50) null comment '语言',
    date          date null comment '创建时间',
    creatorFileAs varchar(255) null comment '电子书创建人',
    UUID          varchar(36) null comment '电子书唯一编号',
    ISBN          varchar(20) null comment '电子书出版编号',
    author_id     varchar(100) not null comment '作者id',
    category_id   varchar(255) null comment '书籍类别编号',
    Introduction  text null comment '简介',
    create_time   timestamp default CURRENT_TIMESTAMP null,
    update_time   timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint book_id_unique unique (book_id)
) comment '书籍信息';

create table category
(
    id int auto_increment primary key,
    name        varchar(255) not null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP
);

create table chapter
(
    id int auto_increment primary key,
    name        varchar(255) not null,
    book_id     varchar(100) not null,
    author_id   varchar(100) not null,
    content     longtext null,
    level       int null,
    order_index int null,
    order_id    varchar(255) null,
    old_path    varchar(255) null,
    path        varchar(255) null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint fk_chapter_author foreign key (author_id) references author (author_id) on delete cascade,
    constraint fk_chapter_book foreign key (book_id) references book (book_id) on delete cascade
);

create table files
(
    id int auto_increment primary key,
    file_id     varchar(100) not null,
    md5         varchar(32)  not null,
    mimetype    varchar(255) not null,
    size        int          not null,
    name        varchar(255) null,
    path        varchar(255) null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint file_id_unique unique (file_id)
);

create table book_link_file
(
    id int auto_increment primary key,
    file_id     varchar(100) not null,
    book_id     varchar(100) not null,
    author_id   varchar(100) not null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint fk_book_file foreign key (file_id) references files (file_id) on delete cascade,
    constraint fk_link_author foreign key (author_id) references author (author_id) on delete cascade,
    constraint fk_link_book foreign key (book_id) references book (book_id) on delete cascade
);

create table style
(
    id int auto_increment primary key,
    name        varchar(255) not null,
    style_id    varchar(100) not null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint style_id_unique unique (style_id)
);

create table style_link_book
(
    id int auto_increment primary key,
    style_id    varchar(100) not null,
    book_id     varchar(100) not null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint style_book_unique unique (style_id, book_id),
    constraint fk_book foreign key (book_id) references book (book_id) on delete cascade,
    constraint fk_style foreign key (style_id) references style (style_id) on delete cascade
);



ALTER TABLE epub_manage.chapter ADD level INT NULL;
ALTER TABLE epub_manage.chapter ADD order_index INT NULL;
ALTER TABLE epub_manage.chapter ADD order_id VARCHAR(255) NULL;
ALTER TABLE epub_manage.chapter ADD old_path VARCHAR(255) NULL;
ALTER TABLE epub_manage.chapter ADD path VARCHAR(255) NULL;




alter table chapter
    modify content varchar(255) null;



CREATE INDEX idx_files_source_id ON files (source_id);