source: petify-backend/sql/ddl.sql

Last change on this file was 92e7c7a, checked in by veronika-ils <ilioskaveronika@…>, 7 hours ago

Petify fullstack project
  • Property mode set to 100644
File size: 13.8 KB
RevLine 
[92e7c7a]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 user_id BIGINT UNIQUE,
110 application_id BIGINT UNIQUE,
111 CONSTRAINT vet_clinics_PK PRIMARY KEY (clinic_id),
112 CONSTRAINT vet_clinics_user_FK FOREIGN KEY (user_id)
113 REFERENCES users(user_id)
114 ON DELETE RESTRICT,
115 CONSTRAINT vet_clinics_application_fk FOREIGN KEY (application_id)
116 REFERENCES vet_clinic_applications(application_id)
117 ON DELETE RESTRICT
118);
119
120
121
122CREATE TABLE animals (
123 animal_id BIGSERIAL,
124 owner_id BIGINT NOT NULL,
125 name VARCHAR(80) NOT NULL,
126 sex VARCHAR(10) NOT NULL,
127 date_of_birth DATE,
128 photo_url TEXT,
129 type VARCHAR(40),
130 species VARCHAR(60),
131 breed VARCHAR(60),
132 located_name VARCHAR(120),
133 CONSTRAINT animals_PK PRIMARY KEY (animal_id),
134 CONSTRAINT animals_owner_FK FOREIGN KEY (owner_id)
135 REFERENCES owners(user_id)
136 ON DELETE RESTRICT,
137 CONSTRAINT animals_sex_CHK CHECK (sex IN ('MALE','FEMALE','UNKNOWN'))
138);
139
140CREATE TABLE listings (
141 listing_id BIGSERIAL,
142 owner_id BIGINT NOT NULL,
143 animal_id BIGINT NOT NULL,
144 status VARCHAR(20) NOT NULL,
145 price NUMERIC(12,2) NOT NULL,
146 description TEXT,
147 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
148
149 CONSTRAINT listings_PK PRIMARY KEY (listing_id),
150 CONSTRAINT listings_owner_FK FOREIGN KEY (owner_id)
151 REFERENCES owners(user_id)
152 ON DELETE RESTRICT,
153 CONSTRAINT listings_animal_FK FOREIGN KEY (animal_id)
154 REFERENCES animals(animal_id)
155 ON DELETE RESTRICT,
156 CONSTRAINT listings_status_CHK CHECK (status IN ('DRAFT','ACTIVE','SOLD','ARCHIVED')),
157 CONSTRAINT listings_price_CHK CHECK (price >= 0)
158);
159
160CREATE TABLE favorite_listings (
161 client_id BIGINT NOT NULL,
162 listing_id BIGINT NOT NULL,
163 CONSTRAINT favorite_listings_PK PRIMARY KEY (client_id, listing_id),
164 CONSTRAINT favorite_listings_client_FK FOREIGN KEY (client_id)
165 REFERENCES clients(user_id)
166 ON DELETE CASCADE,
167 CONSTRAINT favorite_listings_listing_FK FOREIGN KEY (listing_id)
168 REFERENCES listings(listing_id)
169 ON DELETE CASCADE
170);
171
172CREATE TABLE appointments (
173 appointment_id BIGSERIAL,
174 clinic_id BIGINT NOT NULL,
175 animal_id BIGINT NOT NULL,
176 responsible_owner_id BIGINT NOT NULL,
177 status VARCHAR(20) NOT NULL,
178 date_time TIMESTAMP NOT NULL,
179 notes TEXT,
180
181 CONSTRAINT appointments_PK PRIMARY KEY (appointment_id),
182 CONSTRAINT appointments_clinic_FK FOREIGN KEY (clinic_id)
183 REFERENCES vet_clinics(clinic_id)
184 ON DELETE RESTRICT,
185 CONSTRAINT appointments_animal_FK FOREIGN KEY (animal_id)
186 REFERENCES animals(animal_id)
187 ON DELETE RESTRICT,
188 CONSTRAINT appointments_owner_FK FOREIGN KEY (responsible_owner_id)
189 REFERENCES owners(user_id)
190 ON DELETE RESTRICT,
191 CONSTRAINT appointments_status_CHK CHECK (status IN ('CONFIRMED','CANCELLED','DONE','NO_SHOW'))
192);
193
194CREATE TABLE health_records (
195 healthrecord_id BIGSERIAL,
196 animal_id BIGINT NOT NULL,
197 appointment_id BIGINT NOT NULL,
198 type VARCHAR(40) NOT NULL,
199 description TEXT,
200 date DATE NOT NULL,
201
202 CONSTRAINT health_records_PK PRIMARY KEY (healthrecord_id),
203 CONSTRAINT health_records_animal_FK FOREIGN KEY (animal_id)
204 REFERENCES animals(animal_id)
205 ON DELETE RESTRICT,
206 CONSTRAINT health_records_appointment_FK FOREIGN KEY (appointment_id)
207 REFERENCES appointments(appointment_id)
208 ON DELETE RESTRICT
209);
210
211CREATE TABLE reviews (
212 review_id BIGSERIAL,
213 reviewer_id BIGINT NOT NULL,
214 rating rating_1_5 NOT NULL,
215 comment TEXT,
216 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
217 updated_at TIMESTAMP,
218 is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
219 CONSTRAINT reviews_PK PRIMARY KEY (review_id),
220 CONSTRAINT reviews_reviewer_FK FOREIGN KEY (reviewer_id)
221 REFERENCES clients(user_id)
222 ON DELETE RESTRICT,
223 CONSTRAINT reviews_rating_CHK CHECK (rating BETWEEN 1 AND 5)
224);
225
226CREATE TABLE user_reviews (
227 review_id BIGINT,
228 target_user_id BIGINT NOT NULL,
229
230 CONSTRAINT user_reviews_PK PRIMARY KEY (review_id),
231 CONSTRAINT user_reviews_review_FK FOREIGN KEY (review_id)
232 REFERENCES reviews(review_id)
233 ON DELETE RESTRICT,
234 CONSTRAINT user_reviews_target_FK FOREIGN KEY (target_user_id)
235 REFERENCES users(user_id)
236 ON DELETE RESTRICT
237);
238
239CREATE TABLE clinic_reviews (
240 review_id BIGINT,
241 target_clinic_id BIGINT NOT NULL,
242
243 CONSTRAINT clinic_reviews_PK PRIMARY KEY (review_id),
244 CONSTRAINT clinic_reviews_review_FK FOREIGN KEY (review_id)
245 REFERENCES reviews(review_id)
246 ON DELETE RESTRICT,
247 CONSTRAINT clinic_reviews_target_FK FOREIGN KEY (target_clinic_id)
248 REFERENCES vet_clinics(clinic_id)
249 ON DELETE RESTRICT
250);
251
252CREATE TABLE IF NOT EXISTS clinic_unavailable_slots (
253 slot_id BIGSERIAL,
254 clinic_id BIGINT NOT NULL,
255 date_time TIMESTAMP NOT NULL,
256 reason TEXT,
257 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
258 CONSTRAINT clinic_unavailable_slots_PK PRIMARY KEY (slot_id),
259 CONSTRAINT clinic_unavailable_slots_clinic_FK FOREIGN KEY (clinic_id)
260 REFERENCES vet_clinics(clinic_id)
261 ON DELETE RESTRICT,
262 CONSTRAINT clinic_unavailable_slots_UQ UNIQUE (clinic_id, date_time)
263);
264
265COMMIT ;
Note: See TracBrowser for help on using the repository browser.