· 8 years ago · Apr 03, 2018, 12:38 PM
1--DROP SCHEMA public CASCADE;
2--CREATE SCHEMA public;
3--GRANT ALL ON SCHEMA public TO postgres;
4--GRANT ALL ON SCHEMA public TO public;
5--
6CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
7SET client_encoding = 'UTF8';
8
9CREATE TYPE REASON AS ENUM ('R1', 'R2', 'R3');
10
11CREATE TABLE admins (
12 id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
13 created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
14 updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
15 last_login_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL,
16 email CHARACTER VARYING(60) NOT NULL UNIQUE,
17 last_name CHARACTER VARYING(60) DEFAULT NULL :: CHARACTER VARYING,
18 first_name CHARACTER VARYING(60) DEFAULT NULL :: CHARACTER VARYING,
19 password CHARACTER VARYING(64) NOT NULL
20);
21
22CREATE TABLE admin_logs (
23 id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
24 created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
25 admin_id UUID NOT NULL,
26 action_name CHARACTER VARYING(60) NOT NULL,
27 status CHARACTER VARYING(60) NOT NULL,
28 description CHARACTER VARYING(255) DEFAULT NULL :: CHARACTER VARYING,
29 CONSTRAINT FK_admin_logs_admin_id
30 FOREIGN KEY (admin_id) REFERENCES admins (id) ON DELETE CASCADE
31);
32
33CREATE TABLE counselors(
34 id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
35 created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
36 updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
37 manager_import_id CHARACTER VARYING(20) DEFAULT NULL :: CHARACTER VARYING,
38 manager_email CHARACTER VARYING(60) DEFAULT NULL :: CHARACTER VARYING,
39 manager_first_name CHARACTER VARYING(60) DEFAULT NULL :: CHARACTER VARYING,
40 manager_last_name CHARACTER VARYING(60) DEFAULT NULL :: CHARACTER VARYING,
41 import_id CHARACTER VARYING(20) DEFAULT NULL :: CHARACTER VARYING,
42 email CHARACTER VARYING(60) DEFAULT NULL :: CHARACTER VARYING,
43 first_name CHARACTER VARYING(60) DEFAULT NULL :: CHARACTER VARYING,
44 last_name CHARACTER VARYING(60) DEFAULT NULL :: CHARACTER VARYING,
45 sector CHARACTER VARYING(10) NOT NULL,
46 code_position INTEGER NOT NULL UNIQUE
47);
48
49CREATE TABLE postal_codes(
50 id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
51 created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
52 code CHARACTER VARYING(10) NOT NULL UNIQUE
53);
54
55CREATE TABLE postal_codes_positions(
56 created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
57 code_id UUID NOT NULL,
58 position_value INTEGER NOT NULL,
59 CONSTRAINT FK_postal_codes_positions_code_id
60 FOREIGN KEY (code_id) REFERENCES postal_codes (id) ON DELETE NO ACTION,
61 PRIMARY KEY (code_id,position_value)
62);
63
64CREATE TABLE clients (
65 id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
66 created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
67 birthday DATE DEFAULT NULL,
68 last_name CHARACTER VARYING(60) DEFAULT NULL :: CHARACTER VARYING,
69 first_name CHARACTER VARYING(60) DEFAULT NULL :: CHARACTER VARYING
70);
71
72CREATE TABLE client_keys (
73 id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
74 created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
75 secret CHARACTER VARYING(128) NOT NULL UNIQUE,
76 grc_id CHARACTER VARYING(64) DEFAULT NULL,
77 reason REASON DEFAULT NULL,
78 code_id UUID NOT NULL,
79 client_id UUID NOT NULL,
80 CONSTRAINT FK_client_keys_client_id
81 FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE NO ACTION,
82 CONSTRAINT FK_client_keys_code_id
83 FOREIGN KEY (code_id) REFERENCES postal_codes (id) ON DELETE NO ACTION
84);
85
86CREATE TABLE intervals (
87 id UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
88 time_value CHARACTER VARYING(24) NOT NULL UNIQUE
89);
90
91CREATE TABLE client_info (
92 key_id UUID PRIMARY KEY,
93 interval_id UUID NOT NULL,
94 counselor_id DATE NOT NULL,
95 meet_date DATE NOT NULL,
96 CONSTRAINT FK_client_info_key_id
97 FOREIGN KEY (key_id) REFERENCES client_keys (id) ON DELETE NO ACTION,
98 CONSTRAINT FK_client_info_counselor_id
99 FOREIGN KEY (counselor_id) REFERENCES counselors (id) ON DELETE NO ACTION,
100 CONSTRAINT FK_client_info_interval_id
101 FOREIGN KEY (interval_id) REFERENCES intervals (id) ON DELETE NO ACTION
102);
103
104CREATE TABLE key_action_logs (
105 id UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
106 created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
107 key_id UUID NOT NULL,
108 key CHARACTER VARYING(60) DEFAULT NULL :: CHARACTER VARYING,
109 description CHARACTER VARYING(200) DEFAULT NULL :: CHARACTER VARYING,
110 step INTEGER NOT NULL,
111 CONSTRAINT FK_key_action_logs_key_id
112 FOREIGN KEY (key_id) REFERENCES client_keys (id) ON DELETE CASCADE
113);
114
115INSERT INTO intervals(time_value)
116 VALUES('08:00-10:00'),('10:00-12:00'),('12:00-14:00'),('14:00-16:00'),('16:00-18:00');
117
118--password=123
119INSERT INTO admins(email,password)
120 VALUES('admin@gmail.com', '$2a$10$gN6qty/4zIFOSLc4E7kV0.HueqQJAEDoT9meJOlfVXWCRXIB7DMDy');