roteiro 3

DROP OWNED BY CURRENT_USER;

CREATE TYPE estados AS ENUM('PB', 'CE', 'PI', 'AL', 'MA', 'RN', 'BA', 'PE', 'SE');
CREATE TYPE cargos AS ENUM ('farmaceuticos', 'vendedores', 'entregadores', 'caixas', 'administradores');
CREATE TYPE tipo_endereco AS ENUM('residencia', 'trabalho', 'outro');

CREATE TABLE farmacias (
    id SERIAL PRIMARY KEY,
    sede CHAR(1),
    bairro TEXT,
    cidade TEXT,
    estado estados
);

CREATE TABLE funcionarios (
    id SERIAL,
    nome TEXT,
    gerente CHAR(1),
    cargo cargos,
    f_id INTEGER,
    PRIMARY KEY (id, cargo)
);

CREATE TABLE clientes (
    cpf CHAR(11) PRIMARY KEY,
    idade date,
    CHECK( age(now(), idade) > '18 years'::interval)
);

CREATE TABLE enderecos (
    id SERIAL PRIMARY KEY,
    cep CHAR(8),
    cliente CHAR(11),
    tipo tipo_endereco
);
CREATE TABLE medicamentos (
    id SERIAL PRIMARY KEY,
    exclusividade CHAR(1),
    PRIMARY KEY (id, exclusividade)
);

CREATE TABLE entregas (
    id SERIAL PRIMARY KEY,
    endereco INTEGER NOT NULL,
    produto INTEGER NOT NULL
);

CREATE TABLE vendas (
    id INTEGER,
    comprador CHAR(11),
    medicamento INTEGER,
    vendedor INTEGER
);

ALTER TABLE farmacias ALTER COLUMN sede SET DEFAULT 'F';
ALTER TABLE funcionarios ADD CONSTRAINT farmacias FOREIGN KEY (f_id) REFERENCES farmacias(id);
ALTER TABLE entregas ADD CONSTRAINT destino FOREIGN KEY (endereco) REFERENCES enderecos(id);
ALTER TABLE enderecos ADD CONSTRAINT residente FOREIGN KEY (cliente) REFERENCES clientes(cpf);
ALTER TABLE vendas ADD CONSTRAINT cliente_compra FOREIGN KEY (comprador) REFERENCES clientes(cpf) ON DELETE RESTRICT;
ALTER TABLE vendas ADD CONSTRAINT produto FOREIGN KEY (medicamento) REFERENCES medicamentos(id) ON DELETE RESTRICT;
ALTER TABLE vendas ADD CONSTRAINT intermediador FOREIGN KEY (vendedor) REFERENCES funcionarios(id);

ALTER TABLE funcionarios ADD CONSTRAINT unico_gerente EXCLUDE USING gist(f_id WITH =, id WITH <>) WHERE (gerente='T');
ALTER TABLE farmacias ADD CONSTRAINT unica_farmacia_bairro EXCLUDE USING gist(bairro WITH =);
ALTER TABLE farmacias ADD CONSTRAINT unica_sede EXCLUDE USING gist(sede WITH =) WHERE (sede='T');

ALTER TABLE farmacias ADD CONSTRAINT ck_bool CHECK(sede='T' OR sede='F');
ALTER TABLE funcionarios ADD CONSTRAINT ck_bool CHECK(gerente='T' OR gerente='F');
ALTER TABLE medicamentos ADD CONSTRAINT ck_bool CHECK(exclusividade='T' OR exclusividade='F');