RelationalDesign: data_load.sql

File data_load.sql, 9.1 KB (added by 202033, 3 weeks ago)
Line 
1BEGIN;
2
3TRUNCATE TABLE
4 review,
5 appointmentpackageusage,
6 payment,
7 appointmentservice,
8 appointment,
9 userpackagepurchase,
10 packageservice,
11 package,
12 service,
13 category,
14 userrole,
15 loyaltycard,
16 "User",
17 role,
18 status,
19 availability
20RESTART IDENTITY CASCADE;
21
22INSERT INTO role (role_id, name) VALUES
23 (1, 'ADMIN'),
24 (2, 'CUSTOMER'),
25 (3, 'STYLIST'),
26 (4, 'MANAGER');
27
28INSERT INTO "User" (user_id, full_name, email, phone, created_at, password_hash) VALUES
29 (1, 'admin', 'admin@venusbeauty.com', '1213124244', '2026-02-09 12:40:41.265433', '$2b$10$1.f1NlqdSPtv8QC5s7RJM.XNfVfYAhuNPF5jAxz2PCt7M5JTYqSGS'),
30 (2, 'Ana Petrova', 'ana@venusbeauty.com', '070100200', '2026-02-10 09:10:00', '$2b$12$ruD94UIabEn0k89s7E.p.uU6jpyZ0vZBYR7toQkL1nOMFERbiV2d2'),
31 (3, 'Marko Ivanov', 'marko@venusbeauty.com', '071300400', '2026-02-10 09:20:00', '$2b$12$ruD94UIabEn0k89s7E.p.uU6jpyZ0vZBYR7toQkL1nOMFERbiV2d2'),
32 (4, 'Ivana Stojanova', 'ivana@venusbeauty.com', '072500600', '2026-02-10 09:30:00', '$2b$12$ruD94UIabEn0k89s7E.p.uU6jpyZ0vZBYR7toQkL1nOMFERbiV2d2'),
33 (5, 'Emily Johnson', 'emily@venusbeauty.com', '073700800', '2026-02-10 09:40:00', '$2b$12$ruD94UIabEn0k89s7E.p.uU6jpyZ0vZBYR7toQkL1nOMFERbiV2d2');
34
35INSERT INTO userrole (user_id, role_id)
36SELECT 1, role_id
37FROM role
38WHERE name = 'ADMIN';
39
40INSERT INTO userrole (user_id, role_id) VALUES
41 (2, 2),
42 (3, 2),
43 (4, 2),
44 (5, 3);
45
46INSERT INTO category (category_id, name) VALUES
47 (1, 'Hair'),
48 (2, 'Nails'),
49 (3, 'Skincare');
50
51INSERT INTO service (service_id, name, price, duration_minutes, category_id) VALUES
52 (1, 'Haircut', 50, 45, 1),
53 (2, 'Blow Dry', 30, 30, 1),
54 (3, 'Manicure', 55, 45, 2),
55 (4, 'Gel Nails', 70, 60, 2),
56 (5, 'Express Facial', 90, 60, 3),
57 (6, 'Deep Facial', 120, 75, 3);
58
59INSERT INTO package (package_id, name, max_usage, total_price) VALUES
60 (1, 'Hair Essentials', 5, 180),
61 (2, 'Nail Care Bundle', 4, 220),
62 (3, 'Glow Skin Pack', 3, 300);
63
64
65INSERT INTO packageservice (package_id, service_id, discounted_price) VALUES
66 (1, 1, 40),
67 (1, 2, 25),
68 (2, 3, 45),
69 (2, 4, 60),
70 (3, 5, 70),
71 (3, 6, 95);
72
73
74INSERT INTO status (status_id, name) VALUES
75 (1, 'SCHEDULED'),
76 (2, 'COMPLETED'),
77 (3, 'CANCELLED'),
78 (4, 'NO_SHOW');
79
80
81INSERT INTO appointment (
82 appointment_id, appointment_time, end_time, notes, type, total_price, user_id, status_id, points_awarded
83) VALUES
84 (1, '2026-03-15 10:00:00', '2026-03-15 11:15:00', 'Hair combo', 'pre-booked', 80, 2, 1, false),
85 (2, '2026-02-10 13:00:00', '2026-02-10 14:15:00', 'Facial session', 'pre-booked', 120, 3, 2, true),
86 (3, '2026-02-11 09:00:00', '2026-02-11 09:45:00', 'Package-covered haircut', 'pre-booked', 0, 2, 2, false),
87 (4, '2026-03-20 15:00:00', '2026-03-20 15:45:00', NULL, 'walk-in', 55, 4, 1, false),
88 (5, '2026-03-05 12:00:00', '2026-03-05 12:30:00', 'Cancelled in advance', 'pre-booked', 30, 2, 3, false);
89
90
91INSERT INTO appointmentservice (appointment_id, service_id) VALUES
92 (1, 1),
93 (1, 2),
94 (2, 6),
95 (3, 1),
96 (4, 3),
97 (5, 2);
98
99
100INSERT INTO loyaltycard (card_id, points, user_id) VALUES
101 (1, 0, 1),
102 (2, 120, 2),
103 (3, 80, 3),
104 (4, 20, 4),
105 (5, 0, 5);
106
107
108INSERT INTO userpackagepurchase (
109 purchase_id, user_id, package_id, purchased_at, total_uses, remaining_uses, status, expires_at
110) VALUES
111 (1, 2, 1, '2026-02-09 13:00:00', 5, 4, 'ACTIVE', '2026-12-31'),
112 (2, 3, 3, '2026-02-10 15:00:00', 3, 3, 'ACTIVE', '2026-11-30'),
113 (3, 4, 2, '2026-02-12 11:00:00', 4, 0, 'EXPIRED', '2026-06-30');
114
115
116INSERT INTO appointmentpackageusage (
117 appointment_id, purchase_id, service_id, used_units, finalized_at
118) VALUES
119 (3, 1, 1, 1, '2026-02-11 10:00:00');
120
121
122INSERT INTO availability (
123 availability_id, date, start_time, end_time, is_closed
124) VALUES
125 (1, '2026-03-15', '09:00', '17:00', false),
126 (2, '2026-03-16', '09:00', '17:00', false),
127 (3, '2026-03-20', '10:00', '18:00', false),
128 (4, '2026-03-21', '09:00', '17:00', true),
129 (5, '2026-03-22', '09:00', '13:00', false);
130
131
132INSERT INTO payment (
133 payment_id, amount, method, "timestamp", status, appointment_id, points_used, package_purchase_id
134) VALUES
135 (1, 80, 'CARD', '2026-03-14 18:00:00', 'PENDING', 1, 0, NULL),
136 (2, 120, 'CARD', '2026-02-10 14:30:00', 'PAID', 2, 0, NULL),
137 (3, 180, 'CARD', '2026-02-09 13:10:00', 'PAID', NULL, 0, 1),
138 (4, 0, 'PACKAGE', '2026-02-11 09:50:00', 'PAID', 3, 0, 1),
139 (5, 55, 'CASH', '2026-03-20 14:50:00', 'FAILED', 4, 0, NULL),
140 (6, 30, 'CARD', '2026-03-05 10:00:00', 'REFUNDED', 5, 0, NULL),
141 (7, 300, 'CARD', '2026-02-10 15:05:00', 'PENDING', NULL, 0, 2);
142
143INSERT INTO review (
144 review_id, rating, comment, created_at, payment_id
145) VALUES
146 (1, 5, 'Excellent facial service.', '2026-02-10 16:00:00', 2),
147 (2, 4, 'Package appointment was smooth.', '2026-02-11 12:00:00', 4);
148
149SELECT setval(pg_get_serial_sequence('"User"', 'user_id'),
150 COALESCE((SELECT MAX(user_id) FROM "User"), 1), true);
151
152SELECT setval(pg_get_serial_sequence('role', 'role_id'),
153 COALESCE((SELECT MAX(role_id) FROM role), 1), true);
154
155SELECT setval(pg_get_serial_sequence('category', 'category_id'),
156 COALESCE((SELECT MAX(category_id) FROM category), 1), true);
157
158SELECT setval(pg_get_serial_sequence('service', 'service_id'),
159 COALESCE((SELECT MAX(service_id) FROM service), 1), true);
160
161SELECT setval(pg_get_serial_sequence('package', 'package_id'),
162 COALESCE((SELECT MAX(package_id) FROM package), 1), true);
163
164SELECT setval(pg_get_serial_sequence('status', 'status_id'),
165 COALESCE((SELECT MAX(status_id) FROM status), 1), true);
166
167SELECT setval(pg_get_serial_sequence('appointment', 'appointment_id'),
168 COALESCE((SELECT MAX(appointment_id) FROM appointment), 1), true);
169
170SELECT setval(pg_get_serial_sequence('loyaltycard', 'card_id'),
171 COALESCE((SELECT MAX(card_id) FROM loyaltycard), 1), true);
172
173SELECT setval(pg_get_serial_sequence('payment', 'payment_id'),
174 COALESCE((SELECT MAX(payment_id) FROM payment), 1), true);
175
176SELECT setval(pg_get_serial_sequence('review', 'review_id'),
177 COALESCE((SELECT MAX(review_id) FROM review), 1), true);
178
179SELECT setval(pg_get_serial_sequence('userpackagepurchase', 'purchase_id'),
180 COALESCE((SELECT MAX(purchase_id) FROM userpackagepurchase), 1), true);
181
182SELECT setval(pg_get_serial_sequence('availability', 'availability_id'),
183 COALESCE((SELECT MAX(availability_id) FROM availability), 1), true);
184
185COMMIT;