CREATE TYPE tipo_logradouro AS ENUM ('AVE','PRA','RUA','TRA','ROD','VIL');
CREATE TYPE tipo_cliente AS ENUM ('F','J');
CREATE TYPE sexo AS ENUM ('M','F');
CREATE TYPE situacao AS ENUM('A','P','T');
CREATE TYPE estado_civil AS ENUM('S','C','D','V','O');

CREATE TABLE funcao (
    codigo  SERIAL,
    nome    VARCHAR(50),
    salario money,
    CONSTRAINT  KFUNCAO PRIMARY KEY(codigo)
    );

CREATE TABLE pais (
    sigla   CHAR(2) NOT NULL,
    nome    VARCHAR(40),
    CONSTRAINT  KPAIS   PRIMARY KEY(sigla)
    );

CREATE TABLE uf (
    sigla   CHAR(2) NOT NULL,
    pais    CHAR(2) NOT NULL,
    nome    CHAR(30),
    CONSTRAINT  KUF PRIMARY KEY(sigla),
    CONSTRAINT  DEP_PAIS_UF FOREIGN KEY(pais)   REFERENCES pais(sigla)  MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION
    );

CREATE TABLE cidade(
    codigo  SERIAL,
    nome    VARCHAR(40),
    uf  CHAR(2) NOT NULL,
    pais    CHAR(2) NOT NULL,
    CONSTRAINT  KCIDADE PRIMARY KEY(codigo),
    CONSTRAINT  DEP_UF_CIDADE   FOREIGN KEY(uf) REFERENCES uf(sigla)    MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION,
    CONSTRAINT  DEP_UF_PAIS FOREIGN KEY(pais)   REFERENCES pais(sigla)  MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION
    );

CREATE TABLE funcionario (
    codigo  SERIAL,
    nome    VARCHAR(30) NOT NULL,
    sexo    sexo    NOT NULL,
    setor   SMALLINT    NOT NULL,
    ramal_individual    VARCHAR(4),
    estado_civil    estado_civil NOT NULL,
    data_nascimento DATE    NOT NULL,
    rg_numero   VARCHAR(15),
    nacionalidade   CHAR(2) NOT NULL,
    naturalidade    INT NOT NULL,
    cpf CHAR(11),
    sangue_fator    VARCHAR(2),
    sangue_rh   CHAR(1),
    tipo_logradouro tipo_logradouro,
    logradouro  VARCHAR(30) NOT NULL,
    complemento VARCHAR(30),
    bairro  VARCHAR(20),
    cidade  INT NOT NULL,
    uf  CHAR(2),
    cep CHAR(8),
    fone    VARCHAR(14),
    funcao  INT NOT NULL,
    admissao    DATE    NOT NULL,
    email   VARCHAR(40),
    salario money   NOT NULL,
    CONSTRAINT  KFUNCIONARIO    PRIMARY KEY(codigo),
    CONSTRAINT  DEP_CIDADE_FUNCIONARIO  FOREIGN KEY(cidade) REFERENCES cidade(codigo) MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION,
    CONSTRAINT  DEP_UF_FUNCIONARIO  FOREIGN KEY(uf) REFERENCES uf(sigla) MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION,
    CONSTRAINT  DEP_FUNCAO_FUNCIONARIO  FOREIGN KEY(funcao) REFERENCES funcao(codigo) MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION
    );
ALTER SEQUENCE funcionario_codigo_seq RESTART WITH 100000;

CREATE TABLE setor (
    codigo  SERIAL,
    nome    VARCHAR(50),
    sigla   CHAR(10),
    ramal   CHAR(3),
    superior    SMALLINT,
    responsavel INT,
    CONSTRAINT  KSETOR  PRIMARY KEY(codigo),
    CONSTRAINT  DEP_RESPONSAVEL_SETOR   FOREIGN KEY(responsavel)    REFERENCES funcionario(codigo)  MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION,
    CONSTRAINT  DEP_SETOR_SETOR FOREIGN KEY(superior)   REFERENCES setor(codigo)    MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION
    );

CREATE TABLE cliente (
    codigo  SERIAL,
    cgc_cpf VARCHAR(15) NOT NULL,
    tipo    tipo_cliente    NOT NULL,
    razao_social    VARCHAR(30) NOT NULL,
    tipo_logradouro tipo_logradouro NOT NULL,
    logradouro  VARCHAR(30),
    complemento VARCHAR(20),
    bairro  VARCHAR(20) NOT NULL,
    cidade  INT NOT NULL,
    uf  CHAR(2),
    pais    CHAR(2),
    cep CHAR(8) NOT NULL,
    fone    VARCHAR(14) NOT NULL,
    contato VARCHAR(30) NOT NULL,
    fax VARCHAR(14),
    insc_est    VARCHAR(16),
    email   VARCHAR(40),
    obs text,
    CONSTRAINT KCLIENT  PRIMARY KEY (codigo),
    CONSTRAINT  DEP_CIDADE_CLIENTE  FOREIGN KEY(cidade) REFERENCES cidade(codigo) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT  DEP_UF_CLIENTE  FOREIGN KEY(uf) REFERENCES uf(sigla) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT  DEP_PAIS_CLIENTE    FOREIGN KEY(pais)   REFERENCES pais(sigla) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE NO ACTION
    );
ALTER SEQUENCE cliente_codigo_seq RESTART WITH 2000000;


CREATE TABLE tipo_produto (
    codigo  SERIAL,
    nome    VARCHAR(50),
    CONSTRAINT  KTIPOPRODUTO    PRIMARY KEY(codigo)
    );

CREATE TABLE produto (
    codigo  SERIAL,
    nome    VARCHAR(40) NOT NULL,
    tipo    SMALLINT    NOT NULL,
    unidade CHAR(3) NOT NULL,
    estoque_real    NUMERIC(16,3),
    estoque_virtual NUMERIC(16,3),
    estoque_minimo  NUMERIC(16,3),
    custo   money,
    venda   money,
    CONSTRAINT  KPRODUTO    PRIMARY KEY(codigo),
    CONSTRAINT  DEP_TIPO    FOREIGN KEY(tipo)   REFERENCES tipo_produto(codigo) MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE CASCADE
    );
ALTER SEQUENCE produto_codigo_seq RESTART WITH 1000;

CREATE TABLE pedido (
    codigo  SERIAL,
    cliente SMALLINT    NOT NULL,
    data_pedido DATE    NOT NULL,
    total   money,
    situacao    situacao,
    vendedor    INT,
    CONSTRAINT  KPEDIDO PRIMARY KEY(codigo),
    CONSTRAINT  DEP_CLIENTE_PEDIDO  FOREIGN KEY(cliente)    REFERENCES cliente(codigo)  MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION,
    CONSTRAINT  DEP_VENDEDEDOR_PEDIDO   FOREIGN KEY(vendedor)   REFERENCES funcionario(codigo)  MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION
    );
ALTER SEQUENCE pedido_codigo_seq RESTART WITH 10000000;

CREATE TABLE itens_pedido (
    pedido  INT NOT NULL,
    produto SMALLINT    NOT NULL,
    quant   NUMERIC(10,3)   NOT NULL,
    total   money,
    situacao    situacao,
    CONSTRAINT  KITENSPEDIDO    PRIMARY KEY(pedido, produto),
    CONSTRAINT  DEP_PEDIDO_ITEM FOREIGN KEY(pedido) REFERENCES pedido(codigo)   MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION,
    CONSTRAINT  DEP_PRODUTO_ITEM    FOREIGN KEY(produto)    REFERENCES produto(codigo)  MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION
    );

ALTER TABLE public.funcionario
    ADD CONSTRAINT DEP_SETOR_FUNCIONARIO    FOREIGN KEY(setor)  REFERENCES setor(codigo)    MATCH SIMPLE
    ON UPDATE CASCADE   ON DELETE NO ACTION;


-- Preenchendo Tabela de Países
-- * Utilizando apenas 2 países, Brasil e EUA.
INSERT INTO public.pais (sigla,nome) VALUES ('BR', 'Brasil');
INSERT INTO public.pais (sigla,nome) VALUES ('EU', 'Estados Unidos');

-- Preenchendo tabela de UF
-- * Utilizando alguns estados do nordeste.
INSERT INTO public.uf (sigla,pais,nome) VALUES ('PB', 'BR', 'Paraíba');
INSERT INTO public.uf (sigla,pais,nome) VALUES ('PE', 'BR', 'Pernambuco');
INSERT INTO public.uf (sigla,pais,nome) VALUES ('BA', 'BR', 'Bahia');
INSERT INTO public.uf (sigla,pais,nome) VALUES ('CE', 'BR', 'Ceará');
INSERT INTO public.uf (sigla,pais,nome) VALUES ('SE', 'BR', 'Sergipe');
INSERT INTO public.uf (sigla,pais,nome) VALUES ('AL', 'BR', 'Alagoas');
INSERT INTO public.uf (sigla,pais,nome) VALUES ('RN', 'BR', 'Rio Grande do Norte');

-- Preenchendo tabela de cidades
-- * Algumas cidades aleatórias.
INSERT INTO public.cidade (nome, uf, pais) VALUES ('João Pessoa', 'PB', 'BR');
INSERT INTO public.cidade (nome, uf, pais) VALUES ('Campina Grande', 'PB', 'BR');
INSERT INTO public.cidade (nome, uf, pais) VALUES ('Salvador', 'BA', 'BR');
INSERT INTO public.cidade (nome, uf, pais) VALUES ('Feira de Santana', 'BA', 'BR');
INSERT INTO public.cidade (nome, uf, pais) VALUES ('Fortaleza', 'CE', 'BR');
INSERT INTO public.cidade (nome, uf, pais) VALUES ('Natal', 'RN', 'BR');
INSERT INTO public.cidade (nome, uf, pais) VALUES ('Aracaju', 'SE', 'BR');

-- Prenchendo clientes
-- * Alguns campos não obrigatorios foram deixados e branco.
-- * O preenchimento utiliza um select para buscar informações de outros campos.
INSERT INTO public.cliente (cgc_cpf,    tipo,   razao_social,   tipo_logradouro,    logradouro,     complemento,    bairro,     cidade,                         uf,                         pais,                           cep,        fone,           contato,        email)
        VALUES ('02312354365',  'J',    'MR. Veiculos', 'RUA',          'Jose Domenico',    'apto 203', 'Cabo Branco',  (SELECT codigo FROM cidade WHERE nome = 'João Pessoa'), (SELECT uf FROM cidade WHERE nome = 'João Pessoa'), (SELECT pais FROM cidade WHERE nome = 'João Pessoa'),   '52012421', '+5583988654592',   'Paulo Ferreira',   'paulo@gmail.com');
INSERT INTO public.cliente (cgc_cpf,    tipo,   razao_social,       tipo_logradouro,    logradouro, bairro,     cidade,                             uf,                                 pais,                               cep,        fone,           contato,        email)
        VALUES ('09583859235',  'J',    'Hyundai Peças',    'RUA',          'Ouro Claro',   'Cabo Branco',  (SELECT codigo FROM cidade WHERE nome = 'Feira de Santana'),    (SELECT uf FROM cidade WHERE nome = 'Feira de Santana'),    (SELECT pais FROM cidade WHERE nome = 'Feira de Santana'),  '52012421', '+5583988654592',   'Paulo Ferreira',   'paulo@gmail.com');
INSERT INTO public.cliente (cgc_cpf,    tipo,   razao_social,       tipo_logradouro,    logradouro,     complemento,    bairro,     cidade,                         uf,                         pais,                           cep,        fone,           contato,        email)
        VALUES ('12382481234',  'F',    'José Aristides',   'AVE',          'Presidente Vargas',    'apto 1001',    'Aurora',   (SELECT codigo FROM cidade WHERE nome = 'Salvador'),    (SELECT uf FROM cidade WHERE nome = 'Salvador'),    (SELECT pais FROM cidade WHERE nome = 'Salvador'),  '12345144', '+5575983827492',   'José Aristides',   'jose@gmail.com');
INSERT INTO public.cliente (cgc_cpf,    tipo,   razao_social,       tipo_logradouro,    logradouro,     complemento,    bairro,     cidade,                         uf,                         pais,                           cep,        fone,           contato,        email)
        VALUES ('02491823842',  'F',    'Claudio Costa',    'RUA',          'Francisco Carneiro',   'apto 502', 'Estrela',  (SELECT codigo FROM cidade WHERE nome = 'João Pessoa'), (SELECT uf FROM cidade WHERE nome = 'João Pessoa'), (SELECT pais FROM cidade WHERE nome = 'João Pessoa'),   '64353623', '+5583945436322',   'Claudio Costa',    'claudio@gmail.com');
INSERT INTO public.cliente (cgc_cpf,    tipo,   razao_social,       tipo_logradouro,    logradouro, bairro,     cidade,                             uf,                             pais,                               cep,        fone,           contato,        email)
        VALUES ('12731284711',  'F',    'Andre Bezerra',    'RUA',          'José Americo', 'Liberdade',    (SELECT codigo FROM cidade WHERE nome = 'Feira de Santana'),    (SELECT uf FROM cidade WHERE nome = 'Feira de Santana'),    (SELECT pais FROM cidade WHERE nome = 'Feira de Santana'),  '12435325',     '+5583927461747',   'Andre Bezerra',    'andre@gmail.com');
INSERT INTO public.cliente (cgc_cpf,    tipo,   razao_social,       tipo_logradouro,    logradouro,     bairro,     cidade,                             uf,                             pais,                               cep,        fone,           contato,        email)
        VALUES ('17284912824',  'J',    'Martelinho de Ouro',   'AVE',          'General Osorio',   'Cabula',   (SELECT codigo FROM cidade WHERE nome = 'Campina Grande'),  (SELECT uf FROM cidade WHERE nome = 'Campina Grande'),  (SELECT pais FROM cidade WHERE nome = 'Campina Grande'),    '81274812', '+5583923535321',   'Ademar Gonçalves', 'ademar@gmail.com');
INSERT INTO public.cliente (cgc_cpf,    tipo,   razao_social,       tipo_logradouro,    logradouro,     bairro,     cidade,                         uf,                         pais,                           cep,        fone,           contato,        email)
        VALUES ('91249821498',  'J',    'Irmãos Coragem',   'AVE',          'Presidente Dutra', 'Panorama', (SELECT codigo FROM cidade WHERE nome = 'Fortaleza'),   (SELECT uf FROM cidade WHERE nome = 'Fortaleza'),   (SELECT pais FROM cidade WHERE nome = 'Fortaleza'), '12847842', '+5583988611241',   'Guilherme Paiva',  'guilherme@gmail.com');
INSERT INTO public.cliente (cgc_cpf,    tipo,   razao_social,       tipo_logradouro,    logradouro, complemento,    bairro,     cidade,                         uf,                         pais,                           cep,        fone,           contato,        email)
        VALUES ('01249249483',  'J',    'Sucatão do Nelson',    'AVE',          'JK',       'apto 203', 'Fraternidade', (SELECT codigo FROM cidade WHERE nome = 'Salvador'),    (SELECT uf FROM cidade WHERE nome = 'Salvador'),    (SELECT pais FROM cidade WHERE nome = 'Salvador'),  '14872812', '+5588918371823',   'Joao Carlos',  'joao@gmail.com');

-- Preenchendo setores
-- * Primeiramente os setores não possuem responsáveis
-- * Após o preenchimento dos funcionarios, uma rotina ALTER TABLE, preencherá os responsáveis
INSERT INTO setor (nome, sigla)
        VALUES ('Setor de Administração e Contabilidade', 'ADM');
INSERT INTO setor (nome, sigla)
        VALUES ('Setor de estoque e armazenagem', 'ESTOQUE');
INSERT INTO setor (nome, sigla, superior)
        VALUES ('Setor de entregas e distribuição', 'ENTREGA', (SELECT codigo FROM setor WHERE sigla = 'ESTOQUE'));
INSERT INTO setor (nome, sigla, superior)
        VALUES ('Setor de Logística', 'LOGISTICA', (SELECT codigo FROM setor WHERE sigla = 'ADM'));
INSERT INTO setor (nome, sigla, superior)
        VALUES ('Setor de Vendas', 'VENDAS',  (SELECT codigo FROM setor WHERE sigla = 'ADM'));