RelationalDesign: data_load.sql

File data_load.sql, 11.8 KB (added by 231035, 32 hours ago)
Line 
1BEGIN;
2
3INSERT INTO users (username, email, name, surname, password_hash, created_at) VALUES
4 ('client.viktor', 'viktor.client@petify.com', 'Viktor', 'Kostov', '$2a$10$c0bwBEKf6v5yFO1jGOcXyuyB8hh7b7NUZeRqp4Y8zR7L6x9P5oO5i', NOW() - INTERVAL '42 days'),
5 ('admin.ana', 'ana.admin@petify.com', 'Ana', 'Adminova', '$2a$10$c0bwBEKf6v5yFO1jGOcXyuyB8hh7b7NUZeRqp4Y8zR7L6x9P5oO5i', NOW() - INTERVAL '40 days'),
6 ('client.mila', 'mila.client@petify.com', 'Mila', 'Ivanova', '$2a$10$c0bwBEKf6v5yFO1jGOcXyuyB8hh7b7NUZeRqp4Y8zR7L6x9P5oO5i', NOW() - INTERVAL '30 days'),
7 ('client.igor', 'igor.client@petify.com', 'Igor', 'Petrov', '$2a$10$c0bwBEKf6v5yFO1jGOcXyuyB8hh7b7NUZeRqp4Y8zR7L6x9P5oO5i', NOW() - INTERVAL '25 days'),
8 ('client.sara', 'sara.client@petify.com', 'Sara', 'Jovanova', '$2a$10$c0bwBEKf6v5yFO1jGOcXyuyB8hh7b7NUZeRqp4Y8zR7L6x9P5oO5i', NOW() - INTERVAL '20 days'),
9 ('clinic.happypaws', 'clinic.happypaws@petify.com', 'Happy Paws', 'Clinic',
10 '$2a$10$JE4p.bHmOPHFTuJYIOFf4uN8lRb3FH7RjZY.CGXp9Ui69ptgYwksO', NOW()),
11 ('clinic.vetcare', 'clinic.vetcare@petify.com', 'VetCare', 'Center',
12 '$2a$10$JE4p.bHmOPHFTuJYIOFf4uN8lRb3FH7RjZY.CGXp9Ui69ptgYwksO', NOW());
13
14INSERT INTO admins (user_id)
15SELECT user_id FROM users WHERE username = 'admin.ana';
16
17INSERT INTO clients (user_id)
18SELECT user_id
19FROM users
20WHERE username IN ('client.viktor','client.mila','client.igor','client.sara');
21
22INSERT INTO owners (user_id)
23SELECT user_id FROM users WHERE username IN ('client.mila','client.igor');
24
25INSERT INTO vet_clinic_applications
26(name, email, phone, city, address, submitted_at, status, reviewed_at, reviewed_by, denial_reason)
27VALUES
28 (
29 'Happy Paws Clinic',
30 'contact@happypaws.vet',
31 '+389 70 111 222',
32 'Skopje',
33 'Partizanska 10',
34 NOW() - INTERVAL '41 days',
35 'APPROVED',
36 NOW() - INTERVAL '40 days',
37 (SELECT user_id FROM users WHERE username = 'admin.ana'),
38 NULL
39 ),
40 (
41 'VetCare Center',
42 'info@vetcare.vet',
43 '+389 70 333 444',
44 'Bitola',
45 'Shirok Sokak 55',
46 NOW() - INTERVAL '39 days',
47 'APPROVED',
48 NOW() - INTERVAL '38 days',
49 (SELECT user_id FROM users WHERE username = 'admin.ana'),
50 NULL
51 );
52
53INSERT INTO vet_clinics
54(name, email, phone, location, city, address, user_id, application_id)
55VALUES
56 (
57 'Happy Paws Clinic',
58 'contact@happypaws.vet',
59 '+389 70 111 222',
60 'Center',
61 'Skopje',
62 'Partizanska 10',
63 (SELECT user_id FROM users WHERE username = 'clinic.happypaws'),
64 (SELECT application_id
65 FROM vet_clinic_applications
66 WHERE name = 'Happy Paws Clinic')
67 ),
68 (
69 'VetCare Center',
70 'info@vetcare.vet',
71 '+389 70 333 444',
72 'Downtown',
73 'Bitola',
74 'Shirok Sokak 55',
75 (SELECT user_id FROM users WHERE username = 'clinic.vetcare'),
76 (SELECT application_id
77 FROM vet_clinic_applications
78 WHERE name = 'VetCare Center')
79 );
80
81INSERT INTO animals (owner_id, name, sex, date_of_birth, photo_url, species, breed, located_name) VALUES
82 ((SELECT user_id FROM users WHERE username='client.mila'), 'Luna', 'FEMALE', '2021-05-10', NULL, 'Dog', 'Labrador', 'Skopje'),
83 ((SELECT user_id FROM users WHERE username='client.mila'), 'Max', 'MALE', '2020-11-02', NULL, 'Cat', 'British Shorthair', 'Skopje'),
84 ((SELECT user_id FROM users WHERE username='client.igor'), 'Rex', 'MALE', '2019-02-18', NULL, 'Dog', 'German Shepherd', 'Bitola');
85
86INSERT INTO listings (owner_id, animal_id, status, price, description, created_at) VALUES
87 ((SELECT user_id FROM users WHERE username='client.mila'),
88 (SELECT animal_id FROM animals WHERE name='Luna'),
89 'ACTIVE', 50.00, 'Friendly dog available for weekend sitting.', NOW() - INTERVAL '10 days'),
90 ((SELECT user_id FROM users WHERE username='client.igor'),
91 (SELECT animal_id FROM animals WHERE name='Rex'),
92 'ARCHIVED', 35.00, 'Guard dog training sessions (experienced handler).', NOW() - INTERVAL '7 days');
93
94INSERT INTO appointments (clinic_id, animal_id, responsible_owner_id, status, date_time, notes) VALUES
95 ((SELECT clinic_id FROM vet_clinics WHERE name='Happy Paws Clinic'),
96 (SELECT animal_id FROM animals WHERE name='Luna'),
97 (SELECT user_id FROM users WHERE username='client.mila'),
98 'DONE', NOW() - INTERVAL '5 days', 'Vaccination'),
99 ((SELECT clinic_id FROM vet_clinics WHERE name='Happy Paws Clinic'),
100 (SELECT animal_id FROM animals WHERE name='Max'),
101 (SELECT user_id FROM users WHERE username='client.mila'),
102 'CONFIRMED', NOW() + INTERVAL '2 days', 'Check-up'),
103 ((SELECT clinic_id FROM vet_clinics WHERE name='VetCare Center'),
104 (SELECT animal_id FROM animals WHERE name='Rex'),
105 (SELECT user_id FROM users WHERE username='client.igor'),
106 'DONE', NOW() - INTERVAL '12 days', 'Minor injury treatment');
107
108INSERT INTO reviews (review_id, reviewer_id, rating, comment, created_at) VALUES
109 (1, (SELECT user_id FROM users WHERE username='client.mila'),
110 5, 'Great service and friendly staff!', NOW() - INTERVAL '4 days'),
111 (2, (SELECT user_id FROM users WHERE username='client.sara'),
112 4, 'Smooth communication and on time.', NOW() - INTERVAL '3 days'),
113 (3, (SELECT user_id FROM users WHERE username='client.viktor'),
114 4, 'Very reliable and polite communication.', NOW() - INTERVAL '2 days');
115
116INSERT INTO clinic_reviews (review_id, target_clinic_id)
117VALUES (1, (SELECT clinic_id FROM vet_clinics WHERE name='Happy Paws Clinic'));
118
119INSERT INTO user_reviews (review_id, target_user_id) VALUES
120 (2, (SELECT user_id FROM users WHERE username='client.igor')),
121 (3, (SELECT user_id FROM users WHERE username='client.mila'));
122
123INSERT INTO health_records (animal_id, appointment_id, type, description, date) VALUES
124 ((SELECT animal_id FROM animals WHERE name='Luna'),
125 (SELECT appointment_id FROM appointments a
126 JOIN animals an ON an.animal_id = a.animal_id
127 WHERE an.name='Luna' AND a.status='DONE'
128 ORDER BY a.appointment_id DESC LIMIT 1),
129 'VACCINATION', 'Rabies vaccine administered. No adverse reaction.', CURRENT_DATE - 5),
130((SELECT animal_id FROM animals WHERE name='Rex'),
131 (SELECT appointment_id FROM appointments a
132 JOIN animals an ON an.animal_id = a.animal_id
133 WHERE an.name='Rex' AND a.status='DONE'
134 ORDER BY a.appointment_id DESC LIMIT 1),
135 'TREATMENT', 'Wound cleaned and bandaged. Antibiotics prescribed.', CURRENT_DATE - 12);
136
137INSERT INTO notifications (user_id, type, message, is_read, created_at) VALUES
138 ((SELECT user_id FROM users WHERE username='client.mila'), 'APPOINTMENT', 'Your appointment is confirmed for Max.', FALSE, NOW() - INTERVAL '1 day'),
139 ((SELECT user_id FROM users WHERE username='client.igor'), 'LISTING', 'Your listing status is ARCHIVED.', TRUE, NOW() - INTERVAL '6 days');
140
141INSERT INTO clinic_unavailable_slots (clinic_id, date_time, reason, created_at)
142VALUES
143 (
144 (SELECT clinic_id FROM vet_clinics WHERE name = 'Happy Paws Clinic'),
145 NOW() + INTERVAL '1 day',
146 'Doctor unavailable - private appointment',
147 NOW()
148 ),
149 (
150 (SELECT clinic_id FROM vet_clinics WHERE name = 'Happy Paws Clinic'),
151 NOW() + INTERVAL '3 days',
152 'Clinic equipment maintenance',
153 NOW()
154 ),
155 (
156 (SELECT clinic_id FROM vet_clinics WHERE name = 'Happy Paws Clinic'),
157 NOW() + INTERVAL '7 days',
158 'Staff training session',
159 NOW()
160 ),
161 (
162 (SELECT clinic_id FROM vet_clinics WHERE name = 'VetCare Center'),
163 NOW() + INTERVAL '2 days',
164 'Emergency-only working hours',
165 NOW()
166 ),
167 (
168 (SELECT clinic_id FROM vet_clinics WHERE name = 'VetCare Center'),
169 NOW() + INTERVAL '5 days',
170 'Veterinarian on leave',
171 NOW()
172 ),
173 (
174 (SELECT clinic_id FROM vet_clinics WHERE name = 'VetCare Center'),
175 NOW() + INTERVAL '10 days',
176 'Clinic closed for local holiday',
177 NOW()
178 );
179
180COMMIT;