create database Library;
use Library;

create table Person (id int(10) auto_increment, firstName varchar(20),  lastName varchar(20),  isAuthor boolean default false, primary key (id));

create table Book (id int(10) auto_increment, authorId int(10), bookName varchar(100), numberOfBooks int(10) default 1, currentNumberOfBooks int(10) default 1, primary key (id), foreign key (authorId) references Person(id) );

create table TakenBooks (id int(10) auto_increment, bookId int(10), readerId int(10), timeBookGot date not null, timeBookReturned date, foreign key (bookId) references Book (id), foreign key (readerId) references Person (id), primary key (id));

create procedure newPerson (in fName varchar(20),  lName varchar(20),  isAu boolean, out id int(10))
begin
	insert into Person (firstName, lastName, isAuthor) values (fName, lName, isAu);
	Set id = LAST_INSERT_ID(); 
end$$

create procedure newBook (authorId int(10), bookName varchar(100), numberOfBooks int(10), currentNumberOfBooks int(10))
begin
	insert into Book ( authorId , bookName, numberOfBooks , currentNumberOfBooks ) values (authorId , bookName, numberOfBooks , currentNumberOfBooks);
end$$

create procedure addBooks ( fNameAuthor varchar(20),  lNameAuthor varchar(20),  bName varchar(100), numOfBooks int(10))
begin
	Declare idOAuth int(10);

	if (select count(*) from Person where fNameAuthor = Person.firstName and lNameAuthor = Person.lastName) = 0
		then
			call newPerson( fNameAuthor ,  lNameAuthor , true, @idOAuth);
			call newBook (@idOAuth, bName, numOfBooks, numOfBooks);
	end if;

#	if (select id from Book where Book.bookName = bname and Book.authorId = (select id from Person where Person.firstName = fNameAuthor and Person.lastName = lNameAuthor)) > 0
#		then
#			Set idOfBook = Book.id;

#			Set idOfBook = (select id from Book where Book.bookName = bname and Book.authorId = (select id from Person where Person.firstName = fNameAuthor and Person.lastName = lNameAuthor));
#			Update Book set numberOfBooks = numberOfBooks + numOfBooks and currentNumberOfBooks = currentNumberOfBooks + numOfBooks 
#					where Book.id = idOfBook;
#	else 
#		insert into Book  (authorId, bookName, numberOfBooks, currentNumberOfBooks) values (fNameAuthor, lNameAuthor, numOfBooks, numOfBooks);
#	end if;

end$$

DELIMITER $$

use Library$$

drop procedure if exists newPerson$$
drop procedure if exists newBook$$
drop procedure if exists addBooks$$

create procedure newPerson (in fName varchar(20),  lName varchar(20),  isAu boolean, out id int(10))
begin
	insert into Person (firstName, lastName, isAuthor) values (fName, lName, isAu);
	Set id = LAST_INSERT_ID(); 
end$$

create procedure newBook (authorId int(10), bookName varchar(100), numberOfBooks int(10), currentNumberOfBooks int(10))
begin
	insert into Book ( authorId , bookName, numberOfBooks , currentNumberOfBooks ) values (authorId , bookName, numberOfBooks , currentNumberOfBooks);
end$$

create procedure addBooks ( fNameAuthor varchar(20),  lNameAuthor varchar(20),  bName varchar(100), numOfBooks int(10))
begin
	Declare idOAuth int(10);
	Declare authExists boolean;
	Declare bookExists boolean;

	Set	authExists = (select count(*) from Person where fNameAuthor = firstName and lNameAuthor = lastName > 0);

	# если нету такого автора, то создать автора и создать книгу этого автора
	if (!authExists)
	then
		call newPerson( fNameAuthor ,  lNameAuthor , true, @idOAuth);
		call newBook (@idOAuth, bName, numOfBooks, numOfBooks);
	# автор существует, насчет книги пока неизвестно
	else 
		Set idOAuth = (select id from Person where firstName = fNameAuthor and lastName = lNameAuthor);
		Set bookExists = (select count(*) from Book where authorId = idOAuth and bookName = bName > 0);

		# если книги не существует, то создаем новую книгу	
		if (!bookExists)
		then 
			call newBook (idOAuth, bName, numOfBooks, numOfBooks);
		# существует автор, сущестивует книга. Надо увеличить запасы книги на величину numOfBooks
		else 
			update Book Set numberOfBooks = numberOfBooks + numOfBooks, currentNumberOfBooks = currentNumberOfBooks + numOfBooks where authorId = idOAuth and bookName = bname;

		end if;
	
	end if;

end$$

create table Writer (id int(10) auto_increment,  firstName varchar(20),  lastName varchar(20), fullName varchar(60), info varchar (1000), primary key (id));

create table LiteraryWork (id int(10) auto_increment, literaryWorkName varchar(100), primary key (id));

create table WriterOfLiteraryWork(id int(10) auto_increment, authorId int(10), literaryWorkId int(10), primary key (id), foreign key (authorId) references Writer(id), 
	foreign key (literaryWorkId) references LiteraryWork(id));

create table Book (id int(10) auto_increment, bookName varchar(100), numberOfBooks int(10) default 1, currentNumberOfBooks int(10) default 1, primary key (id) );


create table BookInfo (id int(10) auto_increment, literaryWorkId int(10), bookId int(10), primary key (id),  foreign key (bookId) references Book(id), 
	foreign key (literaryWorkId) references LiteraryWork(id));

select Book.id, Writer.fullName, LiteraryWork.literaryWorkName from Book, bookinfo, LiteraryWork, WriterOfLiteraryWork, Writer 
where Book.id = bookinfo.bookId and bookinfo.literaryWorkId = Literarywork.id and Literarywork.id = 
	WriterOfLiteraryWork.literaryWorkId and WriterOfLiteraryWork.authorId = Writer.id;