-- Users table
CREATE TABLE IF NOT EXISTS users(
    identifier INTEGER PRIMARY KEY AUTO_INCREMENT,
    surname TEXT NOT NULL,
    first_name TEXT NOT NULL
);
INSERT INTO users(surname,first_name) VALUES ('Smith', 'Alice');
INSERT INTO users(surname,first_name) VALUES ('Johnson', 'Bob');
INSERT INTO users(surname,first_name) VALUES ('Taylor', 'Charles');
INSERT INTO users(surname,first_name) VALUES ('Davies', 'Diana');

-- Genres table
CREATE TABLE IF NOT EXISTS genres(
    identifier INTEGER PRIMARY KEY AUTO_INCREMENT,
    genre TEXT NOT NULL
);
INSERT INTO genres(genre) VALUES ('Poetry');
INSERT INTO genres(genre) VALUES ('Novel');
INSERT INTO genres(genre) VALUES ('Theater');

-- Books table
CREATE TABLE IF NOT EXISTS books(
    identifier INTEGER PRIMARY KEY AUTO_INCREMENT,
    author TEXT NOT NULL,
    title TEXT NOT NULL,
    genre INTEGER NOT NULL,
    CONSTRAINT fk_genre FOREIGN KEY(genre) REFERENCES genres(identifier)
);
INSERT INTO books(author,title,genre) VALUES ('Baudelaire', 'Les fleurs du mal', 1);
INSERT INTO books(author,title,genre) VALUES ('Steinbeck', 'The Grapes of Wrath', 2);
INSERT INTO books(author,title,genre) VALUES ('Molière', 'L`avare', 3);
INSERT INTO books(author,title,genre) VALUES ('Corneille', 'Le Cid', 3);
INSERT INTO books(author,title,genre) VALUES ('Hugo', 'Les misérables', 2);
INSERT INTO books(author,title,genre) VALUES ('Prévert', 'Paroles', 1);

-- Borrowings table
CREATE TABLE IF NOT EXISTS borrowings(
    identifier INTEGER PRIMARY KEY AUTO_INCREMENT,
    borrower_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    date_from DATE NOT NULL,
    date_to DATE,
    CONSTRAINT fk_borrower_id FOREIGN KEY(borrower_id) REFERENCES users(identifier),
    CONSTRAINT fk_book_id FOREIGN KEY(book_id) REFERENCES books(identifier)
);
INSERT INTO borrowings(borrower_id,book_id,date_from,date_to) VALUES (4, 2, '2021-01-01', NULL);
INSERT INTO borrowings(borrower_id,book_id,date_from,date_to) VALUES (1, 4, '2021-01-02', '2021-01-08');
INSERT INTO borrowings(borrower_id,book_id,date_from,date_to) VALUES (4, 5, '2021-01-04', '2021-01-16');

