source: petify-backend/sql/ddl.sql@ f6ed6e4

Last change on this file since f6ed6e4 was f6ed6e4, checked in by veronika-ils <ilioskaveronika@…>, 17 hours ago

feat: The app is consistent with the changes made in phase 1 and phase 2
  • Property mode set to 100644
File size: 14.3 KB
Line 
1BEGIN;
2
3DROP TABLE IF EXISTS health_records CASCADE;
4DROP TABLE IF EXISTS clinic_reviews CASCADE;
5DROP TABLE IF EXISTS user_reviews CASCADE;
6DROP TABLE IF EXISTS reviews CASCADE;
7DROP TABLE IF EXISTS appointments CASCADE;
8DROP TABLE IF EXISTS favorite_listings CASCADE;
9DROP TABLE IF EXISTS listings CASCADE;
10DROP TABLE IF EXISTS animals CASCADE;
11DROP TABLE IF EXISTS vet_clinic_applications CASCADE;
12DROP TABLE IF EXISTS vet_clinics CASCADE;
13DROP TABLE IF EXISTS notifications CASCADE;
14DROP TABLE IF EXISTS owners CASCADE;
15DROP TABLE IF EXISTS clients CASCADE;
16DROP TABLE IF EXISTS admins CASCADE;
17DROP TABLE IF EXISTS users CASCADE;
18drop table if exists clinic_unavailable_slots cascade ;
19
20CREATE TABLE users (
21 user_id BIGSERIAL,
22 username VARCHAR(30) NOT NULL,
23 email VARCHAR(254) NOT NULL,
24 name VARCHAR(60) NOT NULL,
25 surname VARCHAR(60) NOT NULL,
26 password_hash TEXT NOT NULL,
27 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
28 CONSTRAINT users_PK PRIMARY KEY (user_id),
29 CONSTRAINT users_username_UQ UNIQUE (username),
30 CONSTRAINT users_email_UQ UNIQUE (email)
31);
32
33CREATE TABLE admins (
34 user_id BIGINT,
35 CONSTRAINT admins_PK PRIMARY KEY (user_id),
36 CONSTRAINT admins_user_FK FOREIGN KEY (user_id)
37 REFERENCES users(user_id)
38 ON DELETE RESTRICT
39);
40
41CREATE TABLE clients (
42 user_id BIGINT,
43 is_blocked BOOLEAN NOT NULL DEFAULT FALSE,
44 blocked_at TIMESTAMP,
45 blocked_reason TEXT,
46 blocked_by BIGINT,
47 CONSTRAINT clients_PK PRIMARY KEY (user_id),
48 CONSTRAINT clients_user_FK FOREIGN KEY (user_id)
49 REFERENCES users(user_id)
50 ON DELETE RESTRICT,
51 CONSTRAINT clients_blocked_by_FK FOREIGN KEY (blocked_by)
52 REFERENCES admins(user_id)
53 ON DELETE SET NULL
54);
55
56CREATE TABLE owners (
57 user_id BIGINT,
58 CONSTRAINT owners_PK PRIMARY KEY (user_id),
59 CONSTRAINT owners_client_FK FOREIGN KEY (user_id)
60 REFERENCES clients(user_id)
61 ON DELETE RESTRICT
62);
63
64CREATE TABLE notifications (
65 notification_id BIGSERIAL,
66 user_id BIGINT NOT NULL,
67 type VARCHAR(40) NOT NULL,
68 message TEXT NOT NULL,
69 is_read BOOLEAN NOT NULL DEFAULT FALSE,
70 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
71 CONSTRAINT notifications_PK PRIMARY KEY (notification_id),
72 CONSTRAINT notifications_user_FK FOREIGN KEY (user_id)
73 REFERENCES users(user_id)
74 ON DELETE RESTRICT
75);
76
77CREATE TABLE vet_clinic_applications (
78 application_id BIGSERIAL,
79 name VARCHAR(120) NOT NULL,
80 email VARCHAR(254),
81 phone VARCHAR(40),
82 city VARCHAR(80) NOT NULL,
83 address VARCHAR(200) NOT NULL,
84 submitted_at TIMESTAMP NOT NULL DEFAULT NOW(),
85 status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
86 reviewed_at TIMESTAMP,
87 reviewed_by BIGINT,
88 denial_reason TEXT,
89
90 CONSTRAINT vet_clinic_applications_PK PRIMARY KEY (application_id),
91
92 CONSTRAINT vet_clinic_applications_admin_FK FOREIGN KEY (reviewed_by)
93 REFERENCES admins(user_id)
94 ON DELETE SET NULL,
95
96 CONSTRAINT vet_clinic_applications_status_chk CHECK (
97 status IN ('PENDING','APPROVED','DENIED')
98 )
99);
100
101CREATE TABLE vet_clinics (
102 clinic_id BIGSERIAL,
103 name VARCHAR(120) NOT NULL,
104 email VARCHAR(254),
105 phone VARCHAR(40),
106 location VARCHAR(120),
107 city VARCHAR(80) NOT NULL,
108 address VARCHAR(200) NOT NULL,
109 work_days VARCHAR(120),
110 start_time TIME,
111 end_time TIME,
112 user_id BIGINT UNIQUE,
113 application_id BIGINT UNIQUE,
114 CONSTRAINT vet_clinics_PK PRIMARY KEY (clinic_id),
115 CONSTRAINT vet_clinics_user_FK FOREIGN KEY (user_id)
116 REFERENCES users(user_id)
117 ON DELETE RESTRICT,
118 CONSTRAINT vet_clinics_application_fk FOREIGN KEY (application_id)
119 REFERENCES vet_clinic_applications(application_id)
120 ON DELETE RESTRICT
121);
122
123
124
125CREATE TABLE animals (
126 animal_id BIGSERIAL,
127 owner_id BIGINT NOT NULL,
128 name VARCHAR(80) NOT NULL,
129 sex VARCHAR(10) NOT NULL,
130 date_of_birth DATE,
131 photo_url TEXT,
132 type VARCHAR(40),
133 species VARCHAR(60),
134 breed VARCHAR(60),
135 located_name VARCHAR(120),
136 CONSTRAINT animals_PK PRIMARY KEY (animal_id),
137 CONSTRAINT animals_owner_FK FOREIGN KEY (owner_id)
138 REFERENCES owners(user_id)
139 ON DELETE RESTRICT,
140 CONSTRAINT animals_sex_CHK CHECK (sex IN ('MALE','FEMALE','UNKNOWN'))
141);
142
143CREATE TABLE listings (
144 listing_id BIGSERIAL,
145 owner_id BIGINT NOT NULL,
146 animal_id BIGINT NOT NULL,
147 status VARCHAR(20) NOT NULL,
148 price NUMERIC(12,2) NOT NULL,
149 description TEXT,
150 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
151
152 CONSTRAINT listings_PK PRIMARY KEY (listing_id),
153 CONSTRAINT listings_owner_FK FOREIGN KEY (owner_id)
154 REFERENCES owners(user_id)
155 ON DELETE RESTRICT,
156 CONSTRAINT listings_animal_FK FOREIGN KEY (animal_id)
157 REFERENCES animals(animal_id)
158 ON DELETE RESTRICT,
159 CONSTRAINT listings_status_CHK CHECK (status IN ('DRAFT','ACTIVE','SOLD','ARCHIVED')),
160 CONSTRAINT listings_price_CHK CHECK (price >= 0)
161);
162
163CREATE TABLE favorite_listings (
164 client_id BIGINT NOT NULL,
165 listing_id BIGINT NOT NULL,
166 CONSTRAINT favorite_listings_PK PRIMARY KEY (client_id, listing_id),
167 CONSTRAINT favorite_listings_client_FK FOREIGN KEY (client_id)
168 REFERENCES clients(user_id)
169 ON DELETE CASCADE,
170 CONSTRAINT favorite_listings_listing_FK FOREIGN KEY (listing_id)
171 REFERENCES listings(listing_id)
172 ON DELETE CASCADE
173);
174
175CREATE TABLE appointments (
176 appointment_id BIGSERIAL,
177 clinic_id BIGINT NOT NULL,
178 animal_id BIGINT NOT NULL,
179 responsible_owner_id BIGINT NOT NULL,
180 status VARCHAR(20) NOT NULL,
181 date_time TIMESTAMP NOT NULL,
182 notes TEXT,
183
184 CONSTRAINT appointments_PK PRIMARY KEY (appointment_id),
185 CONSTRAINT appointments_clinic_FK FOREIGN KEY (clinic_id)
186 REFERENCES vet_clinics(clinic_id)
187 ON DELETE RESTRICT,
188 CONSTRAINT appointments_animal_FK FOREIGN KEY (animal_id)
189 REFERENCES animals(animal_id)
190 ON DELETE RESTRICT,
191 CONSTRAINT appointments_owner_FK FOREIGN KEY (responsible_owner_id)
192 REFERENCES owners(user_id)
193 ON DELETE RESTRICT,
194 CONSTRAINT appointments_status_CHK CHECK (status IN ('CONFIRMED','CANCELLED','DONE','NO_SHOW'))
195);
196
197CREATE TABLE health_records (
198 healthrecord_id BIGSERIAL,
199 animal_id BIGINT NOT NULL,
200 appointment_id BIGINT NOT NULL,
201 type VARCHAR(40) NOT NULL,
202 description TEXT,
203 date DATE NOT NULL,
204
205 CONSTRAINT health_records_PK PRIMARY KEY (healthrecord_id),
206 CONSTRAINT health_records_animal_FK FOREIGN KEY (animal_id)
207 REFERENCES animals(animal_id)
208 ON DELETE RESTRICT,
209 CONSTRAINT health_records_appointment_FK FOREIGN KEY (appointment_id)
210 REFERENCES appointments(appointment_id)
211 ON DELETE RESTRICT
212);
213
214CREATE TABLE reviews (
215 review_id BIGSERIAL,
216 reviewer_id BIGINT NOT NULL,
217 rating rating_1_5 NOT NULL,
218 comment TEXT,
219 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
220 updated_at TIMESTAMP,
221 is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
222 CONSTRAINT reviews_PK PRIMARY KEY (review_id),
223 CONSTRAINT reviews_reviewer_FK FOREIGN KEY (reviewer_id)
224 REFERENCES clients(user_id)
225 ON DELETE RESTRICT,
226 CONSTRAINT reviews_rating_CHK CHECK (rating BETWEEN 1 AND 5)
227);
228
229CREATE TABLE user_reviews (
230 review_id BIGINT,
231 target_user_id BIGINT NOT NULL,
232 interaction_type VARCHAR(40) NOT NULL,
233
234 CONSTRAINT user_reviews_PK PRIMARY KEY (review_id),
235 CONSTRAINT user_reviews_review_FK FOREIGN KEY (review_id)
236 REFERENCES reviews(review_id)
237 ON DELETE RESTRICT,
238 CONSTRAINT user_reviews_target_FK FOREIGN KEY (target_user_id)
239 REFERENCES users(user_id)
240 ON DELETE RESTRICT,
241 CONSTRAINT user_reviews_interaction_type_CHK CHECK (
242 interaction_type IN ('EVENT', 'PERSONAL_INTERACTION', 'ONLINE', 'PHONE_CALL', 'OTHER')
243 )
244);
245
246CREATE TABLE clinic_reviews (
247 review_id BIGINT,
248 target_clinic_id BIGINT NOT NULL,
249
250 CONSTRAINT clinic_reviews_PK PRIMARY KEY (review_id),
251 CONSTRAINT clinic_reviews_review_FK FOREIGN KEY (review_id)
252 REFERENCES reviews(review_id)
253 ON DELETE RESTRICT,
254 CONSTRAINT clinic_reviews_target_FK FOREIGN KEY (target_clinic_id)
255 REFERENCES vet_clinics(clinic_id)
256 ON DELETE RESTRICT
257);
258
259CREATE TABLE IF NOT EXISTS clinic_unavailable_slots (
260 slot_id BIGSERIAL,
261 clinic_id BIGINT NOT NULL,
262 date_time TIMESTAMP NOT NULL,
263 reason TEXT,
264 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
265 CONSTRAINT clinic_unavailable_slots_PK PRIMARY KEY (slot_id),
266 CONSTRAINT clinic_unavailable_slots_clinic_FK FOREIGN KEY (clinic_id)
267 REFERENCES vet_clinics(clinic_id)
268 ON DELETE RESTRICT,
269 CONSTRAINT clinic_unavailable_slots_UQ UNIQUE (clinic_id, date_time)
270);
271
272COMMIT ;
Note: See TracBrowser for help on using the repository browser.