CREATE SEQUENCE country_seq INCREMENT BY 1 START WITH CREATE TABLE readers
(
    reader_id NUMBER CONSTRAINT reader_pk PRIMARY KEY CONSTRAINT c_reader NOT NULL,
    password varchar2(30),
    firstname varchar2(50),
    surname varchar2(50),
    email varchar2(50),
    date_create DATE,
    ID_number varchar2(11)
);

CREATE TABLE authors
(
    author_id NUMBER(10)  CONSTRAINT author_pk PRIMARY KEY CONSTRAINT c_author NOT NULL,
    country_id NUMBER(10) CONSTRAINT a_country_fk REFERENCES countries(country_id),
    firstname varchar2(50),
    surname varchar2(50)
);

CREATE TABLE publishers
(
    publisher_id NUMBER(10)  CONSTRAINT publisher_pk PRIMARY KEY CONSTRAINT c_publisher NOT NULL,
    publisher_name VARCHAR(100)
);

CREATE TABLE categories
(
    category_id NUMBER(10)  CONSTRAINT category_pk PRIMARY KEY CONSTRAINT c_category NOT NULL,
    category_name VARCHAR(100)
);

CREATE TABLE type_documents
(
    type_document_id NUMBER(2) CONSTRAINT type_document_pk PRIMARY KEY CONSTRAINT c_type_document NOT NULL,
    type_document_name VARCHAR(100)
);

CREATE TABLE documents
(
    document_id NUMBER(10)  CONSTRAINT document_pk PRIMARY KEY CONSTRAINT c_document NOT NULL,
    category_id NUMBER(10) CONSTRAINT d_category_fk REFERENCES categories(category_id),
    type_document_id NUMBER(2) CONSTRAINT d_type_document_fk REFERENCES type_documents(type_document_id),
    publisher_id NUMBER(10) CONSTRAINT d_publisher_fk REFERENCES publishers(publisher_id),
    title VARCHAR2(200),
    isbn VARCHAR2(13),
    pages VARCHAR2(10),
    release_date DATE,
    description VARCHAR2(4000),
    quantity NUMBER(5),
    date_create DATE,
    date_edit DATE,
    state_of_book CHAR(1)
);

CREATE TABLE author_document
(
    author_document_id NUMBER(2) CONSTRAINT author_document_pk PRIMARY KEY CONSTRAINT c_author_document NOT NULL,
    author_id NUMBER(10) CONSTRAINT ad_author_fk REFERENCES authors(author_id),
    document_id NUMBER(10) CONSTRAINT ad_document_fk REFERENCES documents(document_id)
);

CREATE TABLE reservations
(
    reservation_id NUMBER(10) CONSTRAINT reservation_pk PRIMARY KEY CONSTRAINT c_reservation NOT NULL,
    reader_id NUMBER(10) CONSTRAINT re_reader_fk REFERENCES readers(reader_id),
    document_id NUMBER(10) CONSTRAINT re_document_fk REFERENCES documents(document_id),
    date_reservation DATE,
    date_expiration DATE,
    active CHAR(1),
    quantity_documents NUMBER(20)
);