-- DDL - DATA DEFINITION LANGUAGE
-- CREATE | ALTER | DROP
/* 
comentários com mais de uma linha
*/
create database db_produto_fatec;

-- drop database db_produto_fatec;

-- drop table tb_cliente;

use db_produto_fatec;

create table tb_cliente (
codigo_cliente bigint unsigned auto_increment primary key,
nome_cliente varchar(80) not null,
email_cliente varchar(60) not null unique,
senha_cliente char(64) not null,
cpf_cnpj varchar(25) not null,
constraint cpf_cnpj_unico unique(cpf_cnpj),
data_registro_cliente datetime not null default current_timestamp
)engine=InnoDB default charset=utf8mb4;

create table tb_endereco (
codigo_endereco bigint unsigned auto_increment,
endereco varchar(100) not null,
numero varchar(10) not null,
complemento varchar(50),
ponto_referencia varchar(100),
bairro varchar(80) not null,
codigo_postal varchar(10) not null,
id_cliente bigint unsigned null,
primary key(codigo_endereco),
foreign key (id_cliente) references tb_cliente (codigo_cliente)
)engine=InnoDB default charset=utf8mb4;

alter table tb_endereco change id_cliente 
id_cliente bigint unsigned not null;

create table tb_cidade(
codigo_cidade int auto_increment primary key,
cidade varchar(100) not null
)charset=utf8mb4;

create table tb_estado(
uf_estado char(2) primary key
);

alter table tb_cidade add column 
uf char(2) not null;

alter table tb_cidade add
foreign key (uf) references tb_estado (uf_estado);

alter table tb_endereco add column
id_cidade int not null;

alter table tb_endereco add 
foreign key (id_cidade) references tb_cidade (codigo_cidade);

create table tb_telefone (
codigo_telefone bigint unsigned primary key auto_increment,
telefone varchar(15) not null,
tipo varchar(10) not null,
id_cliente bigint unsigned not null,
foreign key (id_cliente) references tb_cliente (codigo_cliente)
);

/*
criar as tables:
tb_categoria (codigo_categoria, nome_categoria)
tb_produto (codigo_produto, produto, descricao_produto, valor_produto, id_categoria)
*/

create table tb_categoria (
codigo_categoria smallint unsigned auto_increment primary key,
categoria varchar(60) not null
);

create table tb_produto (
codigo_produto int unsigned auto_increment primary key,
produto varchar(80) not null,
descricao_produto text,
valor_produto decimal(8,2) not null,
id_categoria smallint unsigned not null,
foreign key (id_categoria) references tb_categoria (codigo_categoria)
);


select * from tb_cidade;

show tables;

-- drop database db_produto_fatec;

create table tb_nota_fiscal (
cd_nota_fiscal bigint unsigned auto_increment,
chave_nota_fiscal varchar(60) not null unique,
primary key(cd_nota_fiscal, chave_nota_fiscal),
data_emissao datetime not null,
data_registro_nf datetime not null default current_timestamp,
id_cliente bigint unsigned,
cpf_cnpj_cliente varchar(25),
foreign key (id_cliente) references tb_cliente (codigo_cliente),
valor_nota_fiscal decimal(10,2) not null
);

-- INSERÇÃO DE DADOS

insert into tb_cliente (
codigo_cliente, 
nome_cliente, 
email_cliente, 
senha_cliente, 
cpf_cnpj, 
data_registro_cliente) VALUES
(null, 
"Claudio Lopes Ferrini Garcia", 
"claudio@mail.com", 
sha2("123", 256),
"321.654.987-85",
"2025-09-23 19:49:30");

select * from tb_cliente;

INSERT INTO tb_cliente 
(nome_cliente, email_cliente, senha_cliente, cpf_cnpj)
VALUES
("Maria Eduarda", "maria.eduarda@mail.com", sha2("456", 256), "987.654.321-12");

INSERT INTO tb_cliente VALUES 
(null, "Thiago Miranda", "tiagosemh!mail.com", sha2("789", 256), "654.987.123-65", "2025-09-23 20:02:25");

INSERT INTO tb_cliente VALUES
(null, "Leonardo", "leo@mail.com", sha2("1020", 256), "321.456.852-95", now());

INSERT INTO tb_cliente SET
nome_cliente = "Gleydson",
email_cliente = "gleydson@mail.com",
senha_cliente = sha2("102030", 256),
cpf_cnpj = "012.652.975-14";

-- INSERT MÚLTIPLO

INSERT INTO tb_categoria (categoria) VALUES 
("informática"), ("escritório"), ("casa & jardim"), ("cama, mesa e banho");

select * from tb_categoria;

-- inserção de dados com chave estrangeira

select * from tb_produto;

insert into tb_produto set
produto = "notebook Sony vaio",
descricao_produto = " 16GB RAM 500GB SSD i7 12Gen",
valor_produto = 4500.00,
id_categoria = 1;

insert into tb_produto 
(produto, descricao_produto, valor_produto, id_categoria)
VALUES
("monitor AOC 19'", "monitor LED fullscreen", 1200.00, 1),
("cadeira presidente", "cadeira em couro marrom e costuras pretas", 1500.00, 2),
("cama king size", "cama com box embutido", 6000.00, 4);

-- UPDATE DE REGISTROS
update tb_produto set
valor_produto = 500.00
where
codigo_produto = 2;

update tb_produto set
valor_produto = 500.00, 
id_categoria = 3
where
codigo_produto = 2;

-- excluir registro

delete from tb_produto
where
codigo_produto >= 1;

truncate table tb_produto;

select * from tb_produto;

show tables;

-- CRIAÇÃO DAS TABELAS PEDIDO E ITEM_PEDIDO

create table tb_pedido (
codigo_pedido bigint unsigned auto_increment primary key,
data_pedido datetime,
status_pedido char(1) not null default "2",
id_cliente bigint unsigned,
chave_pedido char(64) not null,
data_registro_pedido datetime not null default current_timestamp,
data_update_registro datetime not null default current_timestamp,
foreign key (id_cliente) references tb_cliente (codigo_cliente)
);

create table tb_item_pedido (
codigo_item bigint unsigned auto_increment primary key,
id_pedido bigint unsigned not null,
id_produto int unsigned not null,
qtde_produto decimal(6,3) not null default 1.000,
valor_produto decimal(10,2) not null,
valor_subtotal decimal(10,2) not null,
data_registro_item datetime not null default current_timestamp,
foreign key (id_pedido) references tb_pedido (codigo_pedido),
foreign key (id_produto) references tb_produto (codigo_produto)
);