source: petify-backend/src/main/resources/db/migration/V1__Initial_schema.sql

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

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