create database db_nostravamus_1dsb;

use db_nostravamus_1dsb;

create table tb_mesa (
cd_mesa int auto_increment primary key,
qt_lugar int not null
);

create table tb_funcionario (
cd_funcionario int auto_increment primary key,
nm_funcionario varchar(60) not null,
nm_email varchar(60) not null, 
dt_registro_funcionario datetime default current_timestamp,
constraint email_unico unique(nm_email)
);

create table tb_categoria (
cd_categoria int auto_increment primary key,
nm_categoria varchar(60) not null unique
)engine=InnoDB default charset=utf8mb4;

create table tb_produto (
cd_produto int auto_increment primary key,
nm_produto varchar(60) not null,
vl_produto decimal(8,2) not null,
ds_produto longtext not null,
id_categoria int not null,
foreign key (id_categoria) references tb_categoria (cd_categoria)
);

create table tb_reserva (
cd_reserva int auto_increment primary key,
nm_cliente varchar(60) not null,
dt_reserva date not null,
hr_reserva time not null, 
cd_ddd char(2) not null,
cd_telefone varchar(13) not null,
id_funcionario int,
foreign key (id_funcionario) references 
tb_funcionario (cd_funcionario)
);

create table tb_pedido (
cd_pedido int auto_increment primary key,
dt_pedido datetime default current_timestamp,
id_produto int not null,
vl_produto decimal(8,2) not null,
qt_produto int not null,
id_mesa int not null, 
id_funcionario int not null,
foreign key (id_produto) references tb_produto (cd_produto),
foreign key (id_mesa) references tb_mesa (cd_mesa),
foreign key (id_funcionario) references tb_funcionario (cd_funcionario)
);

-- INSERÇÃO DE DADOS 

insert into tb_mesa set 
qt_lugar = 4;

update tb_mesa set
qt_lugar = 4 
where
cd_mesa = 2;

select * from tb_mesa;

delete from tb_mesa 
where 
cd_mesa = 15;

show tables;

/**
cadastrar:
3 categorias
3 produtos por categoria
2 funcionários
5 reservas
5 pedidos
*/
select * from tb_categoria;

insert into tb_categoria (nm_categoria) values ("Cozinha Italiana"), ("Cozinha Japonesa");

select * from tb_funcionario;
insert into tb_funcionario (nm_funcionario, nm_email) values 
("Claudio", "claudio@mail.com"), 
("Maiara", "maiara@mail.com");

select * from tb_reserva;

insert into tb_reserva set
nm_cliente = "Jhonny Cleyson",
dt_reserva = "2023-08-12",
hr_reserva = "20:00:00", 
cd_ddd = "12", 
cd_telefone = "987654-3210";

insert into tb_reserva set
nm_cliente = "Alvinaldo",
dt_reserva = "2023-08-12",
hr_reserva = "20:30:00", 
cd_ddd = "12", 
cd_telefone = "99876-5432";

insert into tb_reserva set
nm_cliente = "Uóxitom",
dt_reserva = "2023-08-13",
hr_reserva = "19:00:00", 
cd_ddd = "12", 
cd_telefone = "99887-6543", 
id_funcionario = 3;

insert into tb_reserva set
nm_cliente = "Juleska",
dt_reserva = "2023-08-13",
hr_reserva = "21:30:00", 
cd_ddd = "12", 
cd_telefone = "99123-4567", 
id_funcionario = 3;


insert into tb_reserva set
nm_cliente = "Uóxitom",
dt_reserva = "2023-08-13",
hr_reserva = "19:00:00", 
cd_ddd = "12", 
cd_telefone = "99887-6543", 
id_funcionario = 3;


insert into tb_reserva set
nm_cliente = "Genivaldson",
dt_reserva = "2023-08-14",
hr_reserva = "22:00:00", 
cd_ddd = "12", 
cd_telefone = "91234-3214", 
id_funcionario = 4;

insert into tb_produto (nm_produto, vl_produto, ds_produto, id_categoria) values
("Kibe", 15.00, "carne moída, trigo, hortelã, cebola, sal", 1),
("Tabule", 60.00, "trigo fino, tomates maduros, cebola, folhas de salsinha, folhas de hortelã fresca, limão,  azeite, sal, folhas de minialface romana para servir", 1),
("Babaganush", 50.00, "pasta a base de berinjela, tahine e limão", 1);

insert into tb_produto (nm_produto, vl_produto, ds_produto, id_categoria) values
("Comercial", 20.00, "Arroz, feijão, bife de contra filé, ovo frito, salada de alface e tomate", 3),
("Frango à passarinho", 35.00, "frango frito, farinha panco, sal, azeite", 3),
("Feijoada", 45.00, "Feijão preto ou carioca, paio, linguiça calabresa fresca, bacon, carne seca, costelinha de porco, couve manteiga, arroz", 3);

insert into tb_produto (nm_produto, vl_produto, ds_produto, id_categoria) values
("Spaguetti", 45.00, "massa fresca de spaguetti, alho, azeite, manteiga, acompanha parmesão fresco ralado", 2),
("Lasagna à Bolognesa", 60.00, "massa fresca em folhas, mozzarella, presunto fresco, molho de tomates frescos, boi ralado, parmesão fresco ralado", 2),
("Rondelle ao molho Branco", 50.00, "massa fresca, mozzarella, presunto fresco, leite, creme de leite, farinha branca", 2);

select * from tb_pedido;

insert into tb_pedido set
dt_pedido = "2023-08-13", 
id_produto = 1, 
vl_produto = ((select vl_produto from tb_produto where cd_produto = 1)),
qt_produto = 1, 
id_mesa = 4, 
id_funcionario = 3;

insert into tb_pedido set
id_produto = 5,
vl_produto = ((select vl_produto from tb_produto where cd_produto = 5)),
qt_produto = 2,
id_mesa = 8,
id_funcionario = 4;

insert into tb_pedido set
id_produto = 5,
vl_produto = ((select vl_produto from tb_produto where cd_produto = 3)),
qt_produto = 3,
id_mesa = 5,
id_funcionario = 4;

/* para alterar um registro específico*/
update tb_pedido set 
qt_produto = 2
where
cd_pedido = 3;

/* para excluir um registro específico */
delete from tb_pedido 
where
cd_pedido = 5;

select * from tb_reserva;
select * from tb_categoria;
select * from tb_produto;
select * from tb_pedido;

/* alteração da estrutura da table pedido, adicionando um campo 
que realiza automaticamente o cálculo do subtotal*/

alter table tb_pedido add column 
vl_subtotal decimal(8,2) not null
default (vl_produto*qt_produto);

/* DQL - DATA QUERY LANGUAGE (MATÉRIA NOVA) */

SELECT * FROM TB_PEDIDO;
SELECT * FROM TB_RESERVA;

SELECT * FROM TB_RESERVA
WHERE
ID_FUNCIONARIO IS NOT NULL;

SELECT * FROM TB_RESERVA
WHERE
ID_FUNCIONARIO IS NULL;

SELECT * FROM TB_RESERVA
WHERE
ID_FUNCIONARIO = 3;

SELECT * FROM TB_PRODUTO
WHERE 
ID_CATEGORIA = 2;

SELECT * FROM TB_PRODUTO
WHERE
VL_PRODUTO between 40 AND 60;

SELECT * FROM TB_PRODUTO
WHERE
VL_PRODUTO <= 30;

SELECT * FROM TB_PRODUTO
WHERE
NM_PRODUTO = "KIBE";

SELECT * FROM TB_PRODUTO
WHERE
NM_PRODUTO LIKE "%T%";

SELECT
NM_PRODUTO, DS_PRODUTO, VL_PRODUTO
FROM TB_PRODUTO;