-
Notifications
You must be signed in to change notification settings - Fork 0
/
DDL.sql
93 lines (75 loc) · 2.9 KB
/
DDL.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
-- drop table if EXISTS tarifas;
-- drop table if EXISTS servicos;
-- drop table if EXISTS instituicoes;
-- drop table if EXISTS grupo;
-- CRIANDO TABELA DE GRUPOS CONSOLIDADOS
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- CRIANDO TABELA DE INSTITUIÇÕES FINANCEIRAS
create table if not exists instituicoes (
"id" uuid primary key DEFAULT uuid_generate_v4 (),
"nome" varchar(100) not null,
"cnpj" varchar(14) not null,
"cnpj_formatado" varchar(14),
"created_at" timestamp not null default current_timestamp,
unique ("nome", "cnpj")
);
-- CRIANDO TABELA DE SERVIÇOS
create table if not exists servicos (
"id" uuid primary key default uuid_generate_v4 (),
"nome" varchar(100) not null,
"codigo" varchar(4) not null,
"tipo" varchar(4) not null,
"created_at" timestamp not null default current_timestamp,
unique ("codigo", "tipo")
);
-- CRIANDO TABELA DE TARIFAS
create table if not exists tarifas (
"id" uuid primary key default uuid_generate_v4 (),
"servico_id" uuid not null,
"instituicao_id" uuid not null,
"valor_maximo" float not null,
"data_vigencia" date not null,
"unidade" varchar(20),
"periodicidade" varchar(20),
"moeda" varchar(10),
"created_at" timestamp not null default current_timestamp,
constraint fk_servico foreign key("servico_id") references servicos(id),
constraint fk_instituicao foreign key("instituicao_id") references instituicoes(id),
unique ("servico_id", "instituicao_id", "valor_maximo", "data_vigencia", "periodicidade", "unidade")
);
-- CRIANDO TABELA DE SCORE
create table if not exists scores (
"id" uuid primary key default uuid_generate_v4 (),
"instituicao_id" uuid not null,
"score_pf" float not null,
"score_pj" float not null,
"score_ttl" float not null,
"qtd_servicos" float not null,
"created_at" timestamp not null default current_timestamp,
constraint fk_instituicao foreign key("instituicao_id") references instituicoes(id)
);
ALTER TABLE tarifas ADD COLUMN valor_minimo float null;
create table if not exists grupos (
"id" uuid primary key DEFAULT uuid_generate_v4 (),
"nome" varchar(100) not null,
"codigo" varchar(4) not null,
"created_at" timestamp not null default current_timestamp,
unique ("nome", "codigo")
);
create table if not exists instituicao_grupo (
"id" uuid primary key default uuid_generate_v4 (),
"instituicao_id" uuid not null,
"grupo_id" uuid not null,
"created_at" timestamp not null default current_timestamp,
unique ("instituicao_id", "grupo_id")
);
ALTER TABLE tarifas DROP CONSTRAINT tarifas_servico_id_instituicao_id_valor_maximo_data_vigenci_key;
ALTER TABLE tarifas ADD CONSTRAINT tarifas_unique_key UNIQUE ("servico_id", "instituicao_id", "valor_maximo", "created_at", "periodicidade", "unidade");
-- CRIANDO TABELA DE LOGS
create table if not exists logs (
"id" uuid primary key default uuid_generate_v4 (),
"date" date not null,
"type" varchar(20),
"content" text,
"created_at" timestamp not null default current_timestamp
);