create database floricultura;

use floricultura;

create table clientes (
  cod_cliente int unsigned auto_increment,
  nome varchar(100) not null,
  dt_nasc date null,
  cpf char(11) not null,
  primary key (cod_cliente),
  unique index cpf_unique (cpf)
);

create table funcionarios (
  matricula int(8) zerofill,
  nome varchar(100) not null,
  dt_adms date not null,
  cargo varchar(30) not null,
  primary key (matricula)
);

create table categorias (
  cod_categoria int unsigned auto_increment,
  descricao varchar(100) not null,
  primary key (cod_categoria)
);

create table produtos (
  cod_produto int unsigned auto_increment,
  cod_categoria int unsigned,
  descricao varchar(100) binary not null,
  vlr_unit decimal(10,2) not null,
  primary key (cod_produto),
  index fk_produto_categoria_idx (cod_categoria),
  constraint fk_produto_categoria foreign key (cod_categoria) references categorias (cod_categoria)
);

create table vendas (
  cod_venda int unsigned auto_increment,
  cod_funcionario int(8) zerofill,
  cod_cliente int unsigned not null,
  vlr_total decimal(10,2) unsigned not null,
  data date not null,
  primary key (cod_venda),
  index fk_vendas_funcionario_idx (cod_funcionario),
  index fk_vendas_cliente_idx (cod_cliente),
  constraint fk_vendas_funcionario foreign key (cod_funcionario) references funcionarios (matricula),
  constraint fk_vendas_cliente foreign key (cod_cliente) references clientes (cod_cliente)
);

create table itens_venda (
  cod_venda int unsigned not null,
  item int not null,
  cod_produto int unsigned not null,
  qtd int unsigned not null,
  vlr_unit decimal(10, 2) not null,
  vlr_pedido decimal(10,2) GENERATED ALWAYS AS ((vlr_unit*qtd)) STORED,
  primary key (item, cod_venda),
  index fk_itensvenda_venda_idx (cod_venda),
  index fk_itensvenda_produto_idx (cod_produto),
  constraint fk_itensvenda_venda foreign key (cod_venda) references vendas (cod_venda),
  constraint fk_itensvenda_produto foreign key (cod_produto) references produtos (cod_produto)
);

insert into clientes (nome, dt_nasc, cpf) values
  ("Arthur Felix", "2003-11-10", "07725082913"),
  ("Gustavo Nunes", "2002-11-09", "07893820139");

insert into funcionarios (matricula, nome, dt_adms, cargo) values
  ("84923", "Pedro Moratelli", "2018-08-10", "Vendedor"),
  ("019123", "Thiago SobrenomeDificil", "2019-10-10", "Gerente");

insert into categorias values
  (null, "Plantas"),
  (null, "Jardinagem"),
  (null, "Outros");

insert into produtos values
  (null, 1, "Orquideas", 15.00),
  (null, 1, "Muda de Arvores", 25.00),
  (null, 2, "Vaso", 20.00),
  (null, 2, "Pa", 10.00),
  (null, 2, "Adubo", 8.00),
  (null, 3, "Cesto", 5.00),
  (null, 3, "Inseticida", 12.00);

insert into vendas (cod_funcionario, cod_cliente, vlr_total, data) values
  (84923, 2, 45, "2019-10-15"),
  (019123, 1, 29, "2019-05-10");

insert into itens_venda (cod_venda, item, cod_produto, qtd, vlr_unit) values
  (1, 1, 1, 1, 15.00),
  (1, 2, 3, 1, 20.00),
  (1, 3, 4, 1, 10.00),
  (2, 1, 6, 1, 5.00),
  (2, 2, 7, 2, 12.00);