simples-whatsapp/database/create.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE empresa (
  id SERIAL NOT NULL PRIMARY KEY,
  nome varchar NOT NULL,
  token varchar NOT NULL UNIQUE,
  email varchar NOT NULL UNIQUE,
  cnpj varchar NOT NULL UNIQUE
);

CREATE TABLE usuarios (
  id SERIAL NOT NULL PRIMARY KEY,
  nome varchar NOT NULL,
  email varchar NOT NULL UNIQUE,
  matricula varchar NOT NULL UNIQUE,
  status boolean NULL DEFAULT true,
  updateat timestamp DEFAULT now(),
  perm varchar NOT NULL
);

CREATE TABLE usuario_empresa (
  id SERIAL NOT NULL PRIMARY KEY,
  id_usuario int NOT NULL,
  id_empresa int NOT NULL,
  is_ativo boolean NOT NULL,
  CONSTRAINT fk_usuario FOREIGN KEY(id_usuario) REFERENCES usuarios(id),
  CONSTRAINT fk_empresa FOREIGN KEY(id_empresa) REFERENCES empresa(id)
);

CREATE TABLE supervisor (
  id SERIAL NOT NULL PRIMARY KEY,
  id_usuario int NOT NULL,
  matricula varchar NOT NULL,
  nome varchar NOT NULL,
  tempo_login timestamp NULL,
  fila varchar NOT NULL,
  status varchar NOT NULL,
  duracao timestamp NULL,
  status_agente int NOT NULL DEFAULT 0,
  id_empresa int NOT NULL,
  motivo_pausa varchar NULL,
  id_number_channel int NOT NULL,
  CONSTRAINT fk_usuario FOREIGN KEY(id_usuario) REFERENCES usuarios(id),
  CONSTRAINT fk_empresa FOREIGN KEY(id_empresa) REFERENCES empresa(id),
  CONSTRAINT fk_number_channel FOREIGN KEY(id_number_channel) REFERENCES number_channel(id)
);

CREATE TABLE number_channel (
  id SERIAL NOT NULL PRIMARY KEY,
  id_empresa int NOT NULL,
  number varchar NOT NULL UNIQUE,
  token varchar NOT NULL,
  channel varchar NOT NULL,
  work_space varchar NULL,
  title varchar NULL,
  CONSTRAINT fk_empresa FOREIGN KEY(id_empresa) REFERENCES empresa(id)
);

CREATE TABLE atendimento (
  id SERIAL NOT NULL PRIMARY KEY,
  uniqueid uuid UNIQUE DEFAULT uuid_generate_v4(),
  matricula varchar NULL,
  id_usuario int NULL,
  cliente_id varchar NOT NULL,
  media varchar NOT NULL,
  data_reg timestamp DEFAULT now(),
  id_empresa int NOT NULL,
  id_number_channel int NOT NULL,
  nome varchar NULL,
  CONSTRAINT fk_usuario FOREIGN KEY(id_usuario) REFERENCES usuarios(id),
  CONSTRAINT fk_number_channel FOREIGN KEY(id_number_channel) REFERENCES number_channel(id),
  CONSTRAINT fk_empresa FOREIGN KEY(id_empresa) REFERENCES empresa(id)
);

CREATE TABLE message (
  id SERIAL NOT NULL PRIMARY KEY,
  uniqueid uuid NOT NULL,
  src varchar NOT NULL,
  dst varchar NOT NULL,
  type varchar NOT NULL,
  content varchar NOT NULL,
  profile_name varchar NOT NULL,
  msg_date timestamp NULL DEFAULT now(),
  media varchar NULL,
  status varchar NULL,
  file_name varchar NULL,
  eventos_atendimento json null,
  id_provedor varchar NULL,
  mimetype varchar NULL,
  CONSTRAINT fk_atendimento FOREIGN KEY(uniqueid) REFERENCES atendimento(uniqueid)
);

CREATE TABLE eventos_atendimento (
  id SERIAL NOT NULL PRIMARY KEY,
  uniqueid uuid NOT NULL,
  evento varchar NOT NULL,
  data_evento timestamp NULL DEFAULT now(),
  data_reg timestamp NULL DEFAULT now(),
  fila varchar NOT NULL,
  id_usuario int NULL,
  CONSTRAINT fk_usuario FOREIGN KEY(id_usuario) REFERENCES usuarios(id),
  CONSTRAINT fk_atendimento FOREIGN KEY(uniqueid) REFERENCES atendimento(uniqueid)
);

CREATE TABLE system_message (
  id SERIAL NOT NULL PRIMARY KEY,
  data_reg timestamp DEFAULT now(),
  texto varchar NOT NULL,
  ordem int NOT NULL,
  fila varchar NULL,
  id_empresa int NOT NULL,
  momento varchar NOT NULL,
  CONSTRAINT fk_empresa FOREIGN KEY(id_empresa) REFERENCES empresa(id)
);

CREATE TABLE notifica_media (
  id SERIAL NOT NULL PRIMARY KEY,
  uniqueid uuid NOT NULL,
  src varchar NOT NULL,
  msg varchar NOT NULL,
  notif_date timestamp NULL DEFAULT now()
);

CREATE TABLE lista_negra_palavras (
  id SERIAL NOT NULL PRIMARY KEY,
  palavra varchar NOT NULL,
  date_create timestamp NULL DEFAULT now()
);

CREATE TABLE queues (
  id SERIAL NOT NULL PRIMARY KEY,
  nome varchar(100) NOT NULL,
  id_empresa int NOT NULL,
  is_ativa boolean NOT NULL DEFAULT true,
  CONSTRAINT fk_empresa FOREIGN KEY(id_empresa) REFERENCES empresa(id)
);

CREATE TABLE eventos_usuarios (
  id SERIAL NOT NULL PRIMARY KEY,
  id_usuario int NOT NULL,
  entrada_pausa timestamp NULL,
  saida_pausa timestamp NULL,
  id_motivo_pausa int4 NULL,
  login timestamp NULL,
  logoff timestamp NULL,
  duracao timestamp NULL,
  id_dac int4 NULL,
  entrada_indisponivel timestamp NULL,
  saida_indisponivel timestamp NULL,
  CONSTRAINT fk_usuario FOREIGN KEY(id_usuario) REFERENCES usuarios(id)
);

CREATE TABLE motivos_pausas (
  id SERIAL NOT NULL PRIMARY KEY,
  motivo varchar(60) NOT NULL,
  id_empresa int NOT NULL,
  is_ativo boolean NOT NULL DEFAULT true,
  CONSTRAINT fk_empresa FOREIGN KEY(id_empresa) REFERENCES empresa(id)
);

CREATE TABLE protocolo_reg (
  id SERIAL NOT NULL PRIMARY KEY,
  uniqueid uuid NOT NULL,
  ano int4 NOT NULL DEFAULT 0,
  numproto int4 NOT NULL DEFAULT 0,
  protocolo int8 NOT NULL DEFAULT 0,
  dataReg timestamp NOT NULL DEFAULT now()
);

CREATE TABLE config_atendimento (
  id SERIAL NOT NULL PRIMARY KEY,
  id_empresa int NOT NULL,
  quantidade_simutaneo int NOT NULL DEFAULT 3,
  CONSTRAINT fk_empresa FOREIGN KEY(id_empresa) REFERENCES empresa(id)
);

CREATE TABLE channel (
  id SERIAL NOT NULL PRIMARY KEY,
  name varchar NOT NULL,
  channel_code varchar NOT NULL
);

-- CREATE TABLE grupo (
--   id SERIAL NOT NULL PRIMARY KEY,
--   id_empresa int NOT NULL,
--   name varchar NOT NULL,
--   CONSTRAINT fk_empresa FOREIGN KEY(id_empresa) REFERENCES empresa(id)
-- );
-- CREATE TABLE permissao (
--   id SERIAL NOT NULL PRIMARY KEY,
--   id_empresa int NOT NULL,
--   name varchar NOT NULL,
--   CONSTRAINT fk_empresa FOREIGN KEY(id_empresa) REFERENCES empresa(id)
-- );