-
Notifications
You must be signed in to change notification settings - Fork 0
/
00_init.sql
177 lines (157 loc) · 6.99 KB
/
00_init.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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
CREATE SCHEMA carmate;
CREATE TYPE accountstatus AS ENUM('Student', 'Teacher');
CREATE TABLE carmate.user (
id SERIAL PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email_address VARCHAR(255) NOT NULL UNIQUE,
"password" BYTEA NOT NULL,
account_status accountstatus NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
profile_picture BYTEA DEFAULT NULL
);
CREATE TABLE carmate.token (
token BYTEA PRIMARY KEY,
expire_at TIMESTAMP NOT NULL,
user_id INTEGER NOT NULL REFERENCES carmate.user(id)
);
CREATE TABLE carmate.message (
id SERIAL PRIMARY KEY,
sent_at DATE NOT NULL,
"message" TEXT NOT NULL,
is_read BOOLEAN NOT NULL DEFAULT FALSE,
sender_id INTEGER NOT NULL REFERENCES carmate.user(id),
receiver_id INTEGER NOT NULL REFERENCES carmate.user(id)
);
CREATE TYPE documenttype AS ENUM('Driver', 'Basic');
CREATE TYPE validationstatus AS ENUM('Pending', 'Rejected', 'Approved');
CREATE TABLE carmate.license (
id SERIAL PRIMARY KEY,
license_img BYTEA NOT NULL,
document_type documenttype NOT NULL DEFAULT 'Basic',
validation_status validationstatus NOT NULL DEFAULT 'Pending',
published_at TIMESTAMP NOT NULL DEFAULT NOW(),
user_id INTEGER REFERENCES carmate.user(id)
);
CREATE TABLE carmate.user_admin (
user_id INTEGER PRIMARY KEY REFERENCES carmate.user(id)
);
CREATE TABLE carmate.user_banned (
user_id INTEGER PRIMARY KEY REFERENCES carmate.user(id),
banned_by INTEGER NOT NULL REFERENCES carmate.user(id),
banned_at TIMESTAMP NOT NULL
);
CREATE TABLE carmate.user_email_validated (
token BYTEA PRIMARY KEY,
expire_at TIMESTAMP NOT NULL,
validated BOOLEAN NOT NULL DEFAULT FALSE,
user_id INTEGER NOT NULL REFERENCES carmate.user(id)
);
CREATE TABLE carmate.driver_profile (
id SERIAL PRIMARY KEY,
"description" TEXT NOT NULL DEFAULT ' ',
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
user_id INTEGER NOT NULL UNIQUE REFERENCES carmate.user(id)
);
CREATE TYPE weekday AS ENUM('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');
CREATE TABLE carmate.scheduled_carpooling (
id SERIAL PRIMARY KEY,
label VARCHAR NOT NULL DEFAULT ' ',
starting_point DOUBLE PRECISION[2] NOT NULL,
destination DOUBLE PRECISION[2] NOT NULL,
start_hour TIME NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
days weekday[7] NOT NULL,
max_passengers INTEGER NOT NULL,
driver_id INTEGER NOT NULL REFERENCES carmate.driver_profile(id),
/* Only in french country */
CHECK (starting_point[1] >= 41.3 AND starting_point[1] <= 51.1
AND starting_point[2] >= -5.142 AND starting_point[2] <= 9.561),
CHECK (destination[1] >= 41.3 AND destination[1] <= 51.1
AND destination[2] >= -5.142 AND destination[2] <= 9.561)
);
CREATE TABLE carmate.passengers_profile (
id SERIAL PRIMARY KEY,
"description" TEXT NOT NULL DEFAULT ' ',
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
user_id INTEGER NOT NULL UNIQUE REFERENCES carmate.user(id)
);
CREATE TABLE carmate.propose_scheduled_carpooling (
id SERIAL PRIMARY KEY,
label VARCHAR NOT NULL DEFAULT ' ',
starting_point DOUBLE PRECISION[2] NOT NULL,
destination DOUBLE PRECISION[2] NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
start_hour TIME NOT NULL,
days weekday[7] NOT NULL,
passenger_id INTEGER NOT NULL REFERENCES carmate.passengers_profile(id),
/* Only in french country */
CHECK (starting_point[1] >= 41.3
AND starting_point[1] <= 51.1
AND starting_point[2] >= -5.142
AND starting_point[2] <= 9.561),
CHECK (destination[1] >= 41.3
AND destination[1] <= 51.1
AND destination[2] >= -5.142
AND destination[2] <= 9.561)
);
CREATE TABLE carmate.review (
passenger_id INTEGER REFERENCES carmate.passengers_profile(id),
driver_id INTEGER REFERENCES carmate.driver_profile(id),
economic_driving_rating FLOAT NOT NULL,
safe_driving_rating FLOAT NOT NULL,
sociability_rating FLOAT NOT NULL,
review TEXT NOT NULL DEFAULT ' ',
rating_date TIMESTAMP NOT NULL,
updated_rating_date TIMESTAMP NOT NULL,
PRIMARY KEY(passenger_id, driver_id)
);
CREATE TABLE carmate.carpooling (
id SERIAL PRIMARY KEY,
starting_point DOUBLE PRECISION[2] NOT NULL,
destination DOUBLE PRECISION[2] NOT NULL,
max_passengers INTEGER NOT NULL DEFAULT 4,
price FLOAT(2) NOT NULL,
is_canceled BOOLEAN NOT NULL DEFAULT FALSE,
departure_date_time TIMESTAMP NOT NULL,
driver_id INTEGER NOT NULL REFERENCES carmate.driver_profile(id),
/* Only in french country */
CHECK (starting_point[1] >= 41.3
AND starting_point[1] <= 51.1
AND starting_point[2] >= -5.142
AND starting_point[2] <= 9.561),
CHECK (destination[1] >= 41.3
AND destination[1] <= 51.1
AND destination[2] >= -5.142
AND destination[2] <= 9.561)
);
CREATE TABLE carmate.reserve_carpooling (
user_id INTEGER NOT NULL REFERENCES carmate.user(id),
carpooling_id INTEGER NOT NULL REFERENCES carmate.carpooling(id),
passenger_code INTEGER NOT NULL,
passenger_code_validated BOOLEAN NOT NULL DEFAULT FALSE,
passenger_code_date_validated TIMESTAMP DEFAULT NULL,
canceled BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY(user_id, carpooling_id)
);
CREATE OR REPLACE FUNCTION day_of_week_to_int(day weekday)
RETURNS INTEGER AS
$$
BEGIN
CASE day
WHEN 'Monday' THEN RETURN 1;
WHEN 'Tuesday' THEN RETURN 2;
WHEN 'Wednesday' THEN RETURN 3;
WHEN 'Thursday' THEN RETURN 4;
WHEN 'Friday' THEN RETURN 5;
WHEN 'Saturday' THEN RETURN 6;
WHEN 'Sunday' THEN RETURN 7;
ELSE
RAISE EXCEPTION 'Invalid day_of_week value: %', day;
END CASE;
END;
$$
LANGUAGE plpgsql;
CREATE CAST(weekday AS integer) WITH FUNCTION day_of_week_to_int(weekday);