one more INSERT

use ichibasupermarket;

-- povoando produtos

/*
    Foreign keys

    foreign key (id_unidade) references unidade (cod),
    foreign key (id_marca) references marca (codmarca),
    foreign key (id_ncm) references ncm (id),
    foreign key (id_categoria) references categoria (codcategoria),
    foreign key (id_subcategoria) references subcategoria (codsubcategoria),
    foreign key (id_fornecedor) references fornecedor (cod)

*/


/*
    Id unidade

 unidade (cod, descricao , sigla)

('0001', 'quilogramas', 'kg'),
('0002' , 'mililitros', 'mL'),
('0003', 'gramas' , 'g'),
('0004', 'litros' , 'L'),
('0005', 'miligramas' , 'mg');

    ID marca


 marca ( codmarca , descricaomarca )

('0001', 'Sadia'),
('0002' , 'Knorr'),
('0003' , 'Camponesa'),
('0004' , 'Kicaldo'),
('0005' , 'Vitarela'),
('0006' , 'Bauduco'),
('0007', 'OMO'),
('0008' , 'Dona Benta'),
('0009', 'Nestle');



    id NCM


 NCM ( id , descricao , cod_mercosul)

('0001', ' ' , '000000001'),
('0002' , ' ' , '000000002'),
('0003' , ' ', '000000003'),
('0004' , ' ', '000000004'),
('0005', ' ' , '000000005');


    id categoria


 categoria ( codcategoria , descricaocategoria )

('0001', 'Condimentos' ),
('0002' , 'Laticinios'),
('0003', 'HortiFruti'),
('0004' , 'Conservas'),
('0005', 'Limpeza'),
('0006' , 'Bebidas Alcoolicas'),
('0007' , 'Bebidas nao Alcoolicas'),
('0008' , 'Graos e Cereais');


    id subcategoria

    subcategoria ( codsubcategoria , cod_categoria , descricaosubcategoria )

('0001' , '0001' , 'Vinagres'),
('0002' , '0001' , 'Temperos'),
('0003' , '0001' , 'Sal' ),
('0004' , '0001', 'Azeites'),
('0005' , '0001', 'Oleos'),
('0006' , '0001', 'Especiarias'),
('0007' , '0002', 'Leites'),
('0008' , '0002', 'Iogurtes'),
('0009' , '0002', 'Fermentados'),
('0010' , '0002', 'Queijos'),
('0011' , '0003', 'Ovos'),
('0012' , '0003', 'Frutas secas'),
('0013' , '0003', 'Legumes'),
('0014' , '0004' , 'Frutas'),
('0015' , '0004', 'Peixes'),
('0016' , '0004', 'Vegetais'),
('0017' , '0004', 'Cogumelos'),
('0018' , '0005', 'Detergente'),
('0019' , '0005', 'Desinfetante'),
('0020' , '0005', 'Sabao em po'),
('0021' , '0005', 'Sabao em barra'),
('0022' , '0005', 'Amaciante');

    id fornecedor

    fornecedor ( cod , nome , CNPJ , rua , bairro , CEP , estado , ativo ) VALUES

('0001' , 'Sadia'     , '55274471000180' , 'Rua valadares' , 'ipsep', '54330315' , 'Pernambuco' , true ) ,
('0002' , 'Pampers'   , '26724671000180' , 'Rua cartomante' , 'ibura', '54202010' , 'Pernambuco' , true ) ,
('0003' , 'Vitarela'  , '21712241000162' , 'Rua maniac' , 'algodão', '54215322' , 'São paulo' , true ) ,
('0004' , 'Coca-cola' , '26804531000180' , 'Rua argola' , 'vale tinhaem ', '51215020' , 'Rio de janeiro' , true ) ,
('0005' , 'Helmans'   , '77411981000180' , 'Rua sartre' , 'gitacity ', '44650201' , 'São paulo' , true ),
('0006' , 'Bombril'   , '53686527000188' , 'Rua 3' , 'Varzea' , '50980320' , 'Recife' , true );

*/

insert into fornecedor ( cod , nome , CNPJ , rua , bairro , CEP , estado , ativo ) VALUES

('0007' , 'Parmalat' , '66288196000170' , 'Rua 4' , 'Barro' , '87055100'  , 'Pernambuco' , true),
('0008' , 'Camponesa', '70190394000173' , 'Rua nova' , 'Dois Carneiros' , '54280642' , 'Pernambuco' , true),
('0009' , 'Camil' ,    '87536865000155' , 'Rua Sao Braz' , 'Vila Rica' , '54100604' , 'Pernambuco' , true),
('0010' , 'Veja' ,     '31089938000153' , 'Travessa 2' , 'San Martin' , '50760172' , 'Pernambuco' , true),
('0011' , 'OMO'  ,     '10339397000102' , 'Rua Perfeita' , 'Conquista' , '53190380' , 'Pernambuco' , true),
('0012' , 'Jonhsons' , '19967649000104' ,'Real da Torre' , 'Madalena' , '50650340' , 'Pernambuco' , true),
('0013' , 'Ypê' ,      '26305112000144' , 'Rua Vale'  , 'Cosme e Damiao' , '53110805' , 'Pernambuco' , true);


insert into produto_ref (cod, id_unidade , id_marca , id_ncm , id_categoria, id_subcategoria, id_fornecedor ) values

('0021' , '0001' , '0001' , '0005' , '0002' , '0010' , '0001' ),
('0022' , '0001' , '0001' , '0005' , '0008' , '0013' , '0001'),
('0023' , '0004' , '0003' , '0005' , '0002' , '0007' , '0008' ),
('0024' , '0004' , '0007' , '0005' , '0005' , '0018' , '0011' ),
('0025' , '0001' , '0002' , '0001' , '0001' , '0002' , '0008' );



/*

    ITEM ESTOQUE TIHNGS


    foreign key (cod_lote) references lote (cod),
    foreign key (id_estoque) references estoque (id),
    CONSTRAINT fk_produtoestoque foreign key ( cod_produto ) references produto_ref( cod) ON DELETE CASCADE ON UPDATE CASCADE,
    foreign key (id_avaria ) references avaria ( id),
    foreign key (id_prateleira ) references prateleira ( codigo)


    id Lote

    lote ( cod, cod_pedido_fornecedor, descricao , data_chegada) VALUES

( '0001' , '0001', 'Lote de coca-cola ' , '2017-03-03' ),
( '0002' , '0002', 'Lote de pepsi ' , '2017-03-20' ),
( '0003' , '0003', 'Lote de margarina deline ' , '2017-05-03' ),
( '0004' , '0004', 'Lote de açucar pretinho ' , '2017-02-03' ),
( '0005' , '0005', null , '2017-04-03' );


    id Estoque

    estoque ( id , seq_filial , cnpj_matriz , descricao , dt_ultima_entrada ) VALUES

( '0001' , '0001' ,  '23416393000114' , null , '2018-01-04'),
( '0002' , '0002' ,  '23416393000114' , null , '2018-01-04'),
( '0003' , '0003' ,  '23416393000140' , null , '2018-01-04'),
( '0004' , '0004' ,  '23416393000169' , null , '2018-01-04');



    id Produto_ref



    id avaria

    avaria (id , causa , preco , obs ) VALUES

('0001' , 'Queda' , 30 , null ),
('0002' , 'Queda' , 10 , null ),
('0003' , 'Queda' , 200 , null ),
('0004' , 'Queda' , 32.2 , null ),
('0005' , 'Queda' , 230 , null ),
('0006' , 'Queda' , 92.2 , null );

    id prateleira

    prateleira ( codigo , altura , comprimento , posicao_nivel ) VALUES

('0001' , 5 , 10 , 'A1' ),
('0002' , 5 , 10 , 'A2' ),
('0003' , 5 , 10 , 'A3' ),
('0004' , 5 , 10 , 'A4' ),
('0005' , 5 , 10 , 'A5' ),
('0006' , 5 , 10 , 'B1' ),
('0007' , 5 , 10 , 'B2' ),
('0008' , 5 , 10 , 'B3' ),
('0009' , 5 , 10 , 'B4' ),
('0010' , 5 , 10 , 'B5' ),
('0011' , 5 , 10 , 'C1' ),
('0012' , 5 , 10 , 'C2' ),
('0013' , 5 , 10 , 'C3' ),
('0014' , 5 , 10 , 'C4' ),
('0015' , 5 , 10 , 'C5' );

*/

insert into item_estoque (cod_lote, id_estoque , cod_produto , id_avaria , id_prateleira , data_validade ,
 data_fabricacao , data_entrada , valor_compra, quantidade)  VALUES

 ( '0001' , '0001' , '0025' , null , '0003' , '2020-03-01' , '2017-02-01' , '2018-01-01' , 400 , 200),
 ( '0001' , '0003' , '0024' , null , '0015' , '2019-05-02' , '2017-02-05' , '2017-09-02' , 320 , 500),
 ( '0001' , '0001' , '0021' , null , '0015' , '2019-08-08' , '2018-01-10' , '2018-02-11' , 200.56 , 100),
 ( '0001' , '0002' , '0021' , '0001' , '0006' , '2018-02-02' , '2017-09-09'  , '2017-01-10' , 500  , 780),
 ( '0001' , '0003' , '0022' , null , '0007' , '2020-10-10' , '2017-03-03' , '2017-09-03' , 20 ,  90),
 ( '0002' , '0003' , '0022' , '0003' , '0007' , '2020-09-12' , '2018-01-01' , '2018-02-01' , 300 , 600),
 ( '0001' , '0003' , '0025' , null , '0003' , '2020-03-01' , '2017-02-03' , '2018-02-12' , 500 , 1000),
 ( '0001' , '0003' , '0020' , '0005' , '0003' , '2019-06-05' , '2017-03-01' , '2017-03-10' , 300 , 300),
 ( '0001' , '0003' , '0021' , null , '0006' , '2018-02-02' , '2017-09-09'  , '2017-01-10' , 500  , 780),
 ( '0003' , '0003' , '0001' , null , '0002' , '2019-02-10' , '2018-12-12' , '2018-01-02' , 500, 100),
 ( '0001' , '0003' , '0016' , null , '0002' , '2020-02-12' , '2017-12-12' , '2018-01-01' , 1000.50 , 300),
 ( '0002' , '0003' , '0020',  null,  '0004' , '2018-02-02' , '2017-01-01' , '2017-08-10' , 300 , 60),
 ( '0001' , '0003' , '0018' , '0003' , '0005' , '2019-04-04' , '2017-01-01'  , '2018-03-02' , 600 , 93),
 ( '0001 ', '0002' , '0015'  ,null , '0002' , '2020-04-12' , '2017-12-12' , '2018--2-13' , 450.50 , 200),
 ( '0001' , '0002', '0014' ,  null, '0005',  '2021-11-10' , '2018-01-10' , '2018-01-15' , 3000.00 , 600),
 ( '0002' , '0002' , '0019' , null,  '0003', '2018-01-02' , '2017-01-01' , '2017-11-06' , 500 , 300),
 ( '0001'  ,'0002' , '0017' , null , '0003', '2019-01-13', '2017-12-14' , '2017-09-15' , 600 , 100),
 ( '0001' , '0002' , '0016' , null , '0003' , '2020-04-17' , '2017-06-13' , '2018-01-22' , 567 , 80 ),
 ( '0001' , '0002' , '0011' , null , '0006' , '2019-03-15' , '2016-07-22', '2017-01-19' , 2000 , 500),
 ( '0001',  '0002',  '0012',  '0006' , '0003' , '2018-01-01' , '2016-05-21' , '2017-03-23' , 300 , 35),
 ( '0001' , '0002' , '0013' , null , '0001' , '2019-03-01' , '2017-07-04' , '2017-09-09' , 1200 , 356),
 ( '0002' , '0004',  '0007' , null , '0003' , '2020-02-13' , '2017-06-21' , '2018-07-15' , 600.50 , 215),
 ( '0001' , '0002' , '0008' , null , '0003' , '2018-01-01' , '2016-09-09' , '2017-11-02' , 300 , 50),
 ( '0002' , '0004' , '0009' , null , '0002' , '2019-01-18' , '2016-05-12' , '2017-11-11' , 1200 , 599),
 ( '0001' , '0004' , '0010 ', null , '0002' , '2019-01-18' , '2016-05-12' , '2017-10-01' , 500.75 , 260),
 ( '0003' , '0004' , '0011' , null , '0001' , '2018-02-13' , '2016-01-03' , '2017-01-10' , 370 , 120),
 ( '0004' , '0004' , '0013' , null , '0003' , '2020-02-10' , '2017-03-15' , '2017-12-30' , 500 , 110),
 ( '0002' , '0003' , '0015' , null , '0004' , '2018-01-01', '2016-12-12' , '2017-01-22' , 900 , 450),
 ( '0001' , '0003 ', '0014' , null , '0004' , '2019-03-04' ,'2017-12-11' , '2017-05-19' , 300 , 123),
 ( '0003' , '0003' , '0010', null , '0001' , '2019-10-12' , '2017-03-04' , '2017-04-15' , 450 , 120),
 ( '0002 ', '0004 ', '0013' , null ,'0004' , '2018-02-01' , '2016-03-12' , '2017-04-11' , 500, 220),
 ( '0001' , '0003' , '0012', null , '0004' , '2019-01-23' , '2016-01-17' , '2017-04-28' , 600 , 300),
 ( '0003' , '0004' , '0002', null , '0003' , '2019-03-19' , '2016-09-27' , '2017-05-18' , 300 , 79);