create database biblioteca;

use biblioteca;

create table categoriaObra(
	codigo int primary key auto_increment,
    nome varchar(100) 
);

create table obra(
codigo bigint primary key auto_increment,
isbn varchar(30),
titulo varchar(100),
autores varchar(100),
palavrasChave varchar(100),
dataPublicacao date,
numeroEdicao int,
editora varchar(100), 
numeroPagina int,
categoria int , foreign key (categoria) references categoriaObra(codigo)
);

create table categoriaLeitor(
	codigo int primary key auto_increment,
    nome varchar(100) ,
    dias int default(7)
);

create table leitor(
	codigo bigint primary key auto_increment,
    documento varchar(30) not null,
	nome varchar(100) not null, 
    endereco varchar(100), 
    cidade varchar(100), 
    estado char(2), 
    telefone varchar(30) not null, 
    email varchar(100) unique, 
    categoria int,
    dataNascimento date,
    foreign key (categoria) references categoriaLeitor(codigo)
);

create table statusCopia(
	codigo int primary key auto_increment,
    nome varchar(100)    
);

create table copia(
	codigo bigint primary key auto_increment,
	status int,
     foreign key (status) references statusCopia(codigo)
    );
    
create table funcionario(
	codigo bigint primary key auto_increment,
	nome varchar(100) not null, 
    endereco varchar(100), 
    cidade varchar(100), 
    estado varchar(2), 
    telefone varchar(30) not null,
    dataNascimento date
);    

create table reserva(
codigo bigint primary key auto_increment,
dataReserva date, 
dataPrevistaRetirada date, 
dataPrevistaDevolucao date, 
leitor bigint, 
obra bigint, 
funcionario bigint,
foreign key (leitor) references leitor(codigo),
foreign key (obra) references obra(codigo),
foreign key (funcionario) references funcionario(codigo)
);    

create table emprestimo(
codigo bigint primary key auto_increment,
dataEmprestimo timestamp, 
dataPrevistaDevolucao timestamp, 
dataDevolucao timestamp, 
multa decimal(18,2) default 0,
obs varchar(400),
leitor bigint, 
copia bigint, 
funcionario bigint,
foreign key (leitor) references leitor(codigo),
foreign key (copia) references copia(codigo),
foreign key (funcionario) references funcionario(codigo)
);    


use biblioteca;

insert into categoriaLeitor(codigo, nome, dias) values(1, 'Aluno', 14);
insert into categoriaLeitor(codigo, nome, dias) values(2, 'Professor', 30);
insert into categoriaLeitor(codigo, nome, dias) values(3, 'Visitante', 7);
/*select * from categoriaLeitor */
insert into categoriaObra(codigo, nome) values(1, 'Livro');
insert into categoriaObra(codigo, nome) values(2, 'Revista');
insert into categoriaObra(codigo, nome) values(3, 'TCC');
/*select * from categoriaObra */
insert into statuscopia(codigo, nome) values(1, 'Disponivel');
insert into statuscopia(codigo, nome) values(2, 'Emprestado');
insert into statuscopia(codigo, nome) values(3, 'Danificado');
insert into statuscopia(codigo, nome) values(4, 'Estraviado');
/*select * from statusCopia */

insert into funcionario(nome, endereco, cidade, estado, telefone)  values('Maria da Silva', 'Rua Xpto 233', 'São Paulo', 'SP', '3455-2344');
insert into funcionario(nome, endereco, cidade, estado, telefone)  values('João de Almeida', 'Rua Xpto 673', 'São Paulo', 'SP', '6577-2344');
insert into funcionario(nome, endereco, cidade, estado, telefone)  values('Mario de Alcantara', 'Rua Xpto 452', 'São Paulo', 'SP', '3425-3334');
insert into funcionario(nome, endereco, cidade, estado, telefone)  values('Jose Romualdo', 'Rua Azuma 1233', 'São Bernardo do Campo', 'SP', '1235-2344');
/*select * from funcionario */

insert into leitor(documento, nome, endereco, cidade, estado, telefone, email, categoria) values('1654545', 'Mariana Moreira',  'Rua Tirol 233',  'São Paulo', 'SP', '2344-2345', 'maria@norton.net.br', 1);
insert into leitor(documento, nome, endereco, cidade, estado, telefone, email, categoria) values('1653445', 'Daiane Stein',  'Rua Polonia 233',  'São Paulo', 'SP', '2344-2345', 'daiane@norton.net.br', 1);
insert into leitor(documento, nome, endereco, cidade, estado, telefone, email, categoria) values('145545', 'Mariele Souza',  'Rua Kuwait 1233',  'São Paulo', 'SP', '2324-2345', 'mariele@norton.net.br', 1);
insert into leitor(documento, nome, endereco, cidade, estado, telefone, email, categoria) values('788945', 'Marlon Moritz',  'Rua Moldavia 1233',  'São Caetano do Sul', 'SP', '2333-2345', 'marlon@norton.net.br', 1);
insert into leitor(documento, nome, endereco, cidade, estado, telefone, email, categoria) values('156723545', 'Jonas Yakov',  'Rua Vilnus 23433',  'São Paulo', 'SP', '2234-2345', 'jonas@norton.net.br', 1);
insert into leitor(documento, nome, endereco, cidade, estado, telefone, email, categoria) values('1345835', 'Ana Cecilia Raines',  'Rua Romenia 443',  'São Paulo', 'SP', '4124-2345', 'anacecila@norton.net.br', 2);
insert into leitor(documento, nome, endereco, cidade, estado, telefone, email, categoria) values('1634555', 'João Paulo Figueiredo',  'Rua Estonia 1233',  'Santo Andre', 'SP', '2344-2345', 'joaopaulo@norton.net.br', 2);
insert into leitor(documento, nome, endereco, cidade, estado, telefone, email, categoria) values('165555', 'Meire Hanover',  'Rua Volga 1233',  'São Paulo', 'SP', '2334-2345', 'meire.hanover@email.com.br', 3);
insert into leitor(documento, nome, endereco, cidade, estado, telefone, email, categoria) values('16534534545', 'Jason Meirs',  'Rua Tenesse 233',  'São Paulo', 'SP', '12334-2345', 'jason@email.com.br', 3);
/*select * from leitor */

insert into obra(isbn, titulo, autores, editora, numeroPagina, categoria) values('234444', 'Oracle 12c', 'John Simth', 'Worx', 200, 1);
insert into obra(isbn, titulo, autores, editora, numeroPagina, categoria) values('234', 'Mysql 8', 'Andrew Harlow', 'Worx', 100, 1);
insert into obra(isbn, titulo, autores, editora, numeroPagina, categoria) values('s/n', 'Nature', 'Varios', 'Nature', 50, 2);
insert into obra(isbn, titulo, autores, editora, numeroPagina, categoria) values('s/n', 'Sistema de Delivery', 'Joao da Silva, Maria de Oliveira', 'Fatec', 100, 3);
/*select * from obra */

insert into copia(obra, status) value(1, 1);
insert into copia(obra, status) value(1, 1);
insert into copia(obra, status) value(2, 1);
insert into copia(obra, status) value(2, 1);
insert into copia(obra, status) value(3, 1);
insert into copia(obra, status) value(4, 1);
/* select * from copia */

insert into reserva(dataReserva, dataPrevistaRetirada, leitor, obra, funcionario) values(current_date(), current_date()+10, 1, 1, 2);
insert into reserva(dataReserva, dataPrevistaRetirada, leitor, obra, funcionario) values(current_date(), current_date()+10, 3, 2, 3);
insert into reserva(dataReserva, dataPrevistaRetirada, leitor, obra, funcionario) values(current_date(), current_date()+10, 4, 1, 1);
/*select * from reserva */

/*select * from emprestimo*/
insert into emprestimo(dataEmprestimo, dataPrevistaDevolucao, multa, leitor, copia, funcionario)
values(current_date(), current_date()+10, 0, 6, 4, 1);

insert into emprestimo(dataEmprestimo, dataPrevistaDevolucao, multa, leitor, copia, funcionario)
values(current_date(), current_date(), 20, 3, 6, 1);

insert into emprestimo(dataEmprestimo, dataPrevistaDevolucao, multa, leitor, copia, funcionario)
values(current_date(), current_date()+10, 0, 3, 1, 2);