DROP DATABASE IF EXISTS booksClub;
CREATE DATABASE booksClub;
USE booksClub;

CREATE TABLE userRole(
	id INT AUTO_INCREMENT PRIMARY KEY,
    roleName ENUM('Admin', 'Librariar', 'Student', 'Teacher') NOT NULL
);

CREATE TABLE publishers (
	id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);

CREATE TABLE books (
	id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    publisherId INT NOT NULL,
    CONSTRAINT FOREIGN KEY (publisherId)
		REFERENCES publishers(id)
);

CREATE TABLE genres (
	id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE books_genres (
	bookId INT NOT NULL ,
	genreId INT NOT NULL ,
	CONSTRAINT FOREIGN KEY (bookId)
		REFERENCES books(id) ,
	CONSTRAINT FOREIGN KEY (genreId)
		REFERENCES genres(id) ,
	PRIMARY KEY(bookId,genreId)
);

CREATE TABLE users (
	id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    egn VARCHAR(10) NOT NULL UNIQUE,
    pass VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    roleId INT NOT NULL,
    CONSTRAINT FOREIGN KEY (roleId)
		REFERENCES userRole(id)
);

CREATE TABLE loanBooks (
	id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    userId INT NOT NULL,
    CONSTRAINT FOREIGN KEY (userId)
		REFERENCES users(id),
	bookId INT NOT NULL,
    CONSTRAINT FOREIGN KEY (bookId)
		REFERENCES books(id)
);

CREATE TABLE authors (
	id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    info TEXT
);

CREATE TABLE authors_books (
	bookId INT NOT NULL ,
	authorId INT NOT NULL ,
	CONSTRAINT FOREIGN KEY (bookId)
		REFERENCES books(id) ,
	CONSTRAINT FOREIGN KEY (authorId)
		REFERENCES authors(id) ,
	PRIMARY KEY(bookId,authorId)
);