78 lines
2.9 KiB
SQL
Executable File
78 lines
2.9 KiB
SQL
Executable File
CREATE TABLE IF NOT EXISTS usuario_aux_status (
|
|
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
descricao VARCHAR(50) NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS usuario_aux_perfil (
|
|
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
descricao VARCHAR(50) NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS usuario (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
uuid UUID NOT NULL UNIQUE,
|
|
nome_completo VARCHAR(255) NOT NULL,
|
|
status_id INT REFERENCES usuario_aux_status(id) ON UPDATE CASCADE ON DELETE CASCADE DEFAULT 1,
|
|
documentCpf VARCHAR(14) DEFAULT NULL UNIQUE,
|
|
documentCrmv VARCHAR(20) DEFAULT NULL UNIQUE,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
senha VARCHAR(255) NOT NULL,
|
|
telefone VARCHAR(20) DEFAULT NULL,
|
|
data_nascimento DATE DEFAULT NULL,
|
|
endereco_rua VARCHAR(255) DEFAULT NULL,
|
|
endereco_numero VARCHAR(20) DEFAULT NULL,
|
|
endereco_complemento VARCHAR(255) DEFAULT NULL,
|
|
endereco_bairro VARCHAR(100) DEFAULT NULL,
|
|
endereco_cidade VARCHAR(100) DEFAULT NULL,
|
|
endereco_uf VARCHAR(2) DEFAULT NULL,
|
|
endereco_cep VARCHAR(10) DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT NULL,
|
|
deleted_at TIMESTAMP DEFAULT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS usuario_token(
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
usuario_id BIGINT REFERENCES usuario(id) ON UPDATE CASCADE ON DELETE CASCADE,
|
|
token TEXT NOT NULL UNIQUE,
|
|
revoked SMALLINT DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT NULL,
|
|
UNIQUE (usuario_id, token)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS pet_aux_especie (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
uuid UUID NOT NULL UNIQUE,
|
|
descricao VARCHAR(50) NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS pet_aux_raca (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
uuid UUID NOT NULL UNIQUE,
|
|
especie_id BIGINT REFERENCES pet_aux_especie(id) ON UPDATE CASCADE ON DELETE CASCADE,
|
|
descricao VARCHAR(50) NOT NULL,
|
|
UNIQUE (especie_id, descricao)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS pet (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
uuid UUID NOT NULL UNIQUE,
|
|
nome VARCHAR(100) NOT NULL,
|
|
especie_id BIGINT REFERENCES pet_aux_especie(id) ON UPDATE CASCADE ON DELETE CASCADE,
|
|
raca_id BIGINT REFERENCES pet_aux_raca(id) ON UPDATE CASCADE ON DELETE CASCADE,
|
|
caminho_foto TEXT DEFAULT NULL,
|
|
registro_geral_animal VARCHAR(50) DEFAULT NULL,
|
|
photo_path TEXT DEFAULT NULL,
|
|
data_nascimento DATE NOT NULL,
|
|
data_obito DATE DEFAULT NULL,
|
|
sexo CHAR(1) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT NULL,
|
|
deleted_at TIMESTAMP DEFAULT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS usuario_pet (
|
|
usuario_id BIGINT REFERENCES usuario(id) ON UPDATE CASCADE ON DELETE CASCADE,
|
|
pet_id BIGINT REFERENCES pet(id) ON UPDATE CASCADE ON DELETE CASCADE
|
|
); |