Context Navigation

Back to DatabaseCreation

DatabaseCreation: dml.sql

File dml.sql, 58.9 KB (added by 231090, 5 days ago)

Line
1	INSERT INTO company_category (category_name)
2	VALUES ('Hair Salon'),
3	('Nail Salon'),
4	('Spa'),
5	('Beauty Salon'),
6	('Barbershop'),
7	('Massage Therapy'),
8	('Skin Care'),
9	('Eyebrow & Lash Studio'),
10	('Tanning Salon'),
11	('Waxing Studio'),
12	('Makeup Studio'),
13	('Hair Removal'),
14	('Wellness Center'),
15	('Med Spa');
16
17
18	-- ============================================================
19	-- 11. SEED — COMPANY
20	-- ============================================================
21
22	WITH salon_names(n) AS (SELECT * FROM beauty_salon_names)
23	INSERT
24	INTO company (name, email, logo_url, description, created_at)
25	SELECT n,
26	LOWER(REPLACE(n, ' ', '')) \|\| '@mail.com',
27	'https://img.com/' \|\| ROW_NUMBER() OVER (),
28	'Beauty salon services',
29	NOW() - (random() * INTERVAL '25 years')
30	FROM salon_names;
31
32
33	-- ============================================================
34	-- 12. SEED — COMPANY → CATEGORY
35	-- ============================================================
36
37	INSERT INTO company_company_category (company_id, company_category_id)
38	SELECT c.company_id, cat.company_category_id
39	FROM company c
40	CROSS JOIN company_category cat
41	WHERE random() < 0.3
42	ON CONFLICT DO NOTHING;
43
44
45	-- ============================================================
46	-- 13. SEED — COMPANY PHONE
47	-- ============================================================
48
49	UPDATE company
50	SET phone =
51	CASE
52	WHEN random() < 0.70 THEN '+389 7' \|\| floor(random() * 3)::int::text
53	\|\| ' ' \|\| floor(random() * 900 + 100)::int::text
54	\|\| ' ' \|\| floor(random() * 900 + 100)::int::text
55	WHEN random() < 0.33 THEN '+30 69' \|\| floor(random() * 8 + 1)::int::text
56	\|\| ' ' \|\| floor(random() * 900 + 100)::int::text
57	\|\| ' ' \|\| floor(random() * 9000 + 1000)::int::text
58	WHEN random() < 0.50 THEN '+359 8' \|\| floor(random() * 9)::int::text
59	\|\| ' ' \|\| floor(random() * 900 + 100)::int::text
60	\|\| ' ' \|\| floor(random() * 9000 + 1000)::int::text
61	ELSE '+381 6' \|\| floor(random() * 9)::int::text
62	\|\| ' ' \|\| floor(random() * 900 + 100)::int::text
63	\|\| ' ' \|\| floor(random() * 9000 + 1000)::int::text
64	END
65	where true;
66
67
68	-- ============================================================
69	-- 14. SEED — COMPANY LOCATIONS
70	-- ============================================================
71
72	INSERT INTO company_location (company_id, address, city, phone)
73	SELECT c.company_id,
74	(ARRAY ['Ilindenska','Partizanska','Jane Sandanski',
75	'Bulevar Makedonija','Vodno','Goce Delchev',
76	'Dimitar Vlahov'])[FLOOR(RANDOM() * 7 + 1)]
77	\|\| ' No. ' \|\| FLOOR(RANDOM() * 200)::int,
78	(ARRAY ['Skopje','Bitola','Ohrid','Tetovo',
79	'Kumanovo','Prilep','Strumica'])[FLOOR(RANDOM() * 7 + 1)],
80	'+3897' \|\| FLOOR(1000000 + RANDOM() * 8999999)::text
81	FROM company c
82	JOIN LATERAL generate_series(1, (1 + FLOOR(RANDOM() * 20))::int) gs ON TRUE;
83
84
85	-- ============================================================
86	-- 15. SEED — BUSINESS HOURS
87	-- ============================================================
88
89	INSERT INTO business_hours (location_id, day_of_week, is_closed, open_time, close_time)
90	SELECT l.location_id,
91	d.day_of_week,
92	(d.day_of_week = 'sunday') AS is_closed,
93	CASE
94	WHEN d.day_of_week = 'sunday' THEN NULL
95	WHEN d.day_of_week = 'saturday' THEN TIME '10:00' + (FLOOR(RANDOM() * 2) * INTERVAL '1 hour')
96	ELSE TIME '08:00' + (FLOOR(RANDOM() * 5) * INTERVAL '1 hour')
97	END AS open_time,
98	CASE
99	WHEN d.day_of_week = 'sunday' THEN NULL
100	WHEN d.day_of_week = 'saturday' THEN TIME '10:00' + (FLOOR(RANDOM() * 2) * INTERVAL '1 hour')
101	+ (4 + FLOOR(RANDOM() * 2)) * INTERVAL '1 hour'
102	ELSE TIME '08:00' + (FLOOR(RANDOM() * 5) * INTERVAL '1 hour')
103	+ (8 + FLOOR(RANDOM() * 3)) * INTERVAL '1 hour'
104	END AS close_time
105	FROM company_location l
106	CROSS JOIN (SELECT unnest(enum_range(NULL::day_of_week_enum)) AS day_of_week) d;
107
108
109	-- ============================================================
110	-- 16. SEED — PROMO CODES
111	-- ============================================================
112
113	INSERT INTO promo_code (company_id, code, discount_type, discount_value, valid_from, valid_until)
114	SELECT c.company_id,
115	UPPER(SUBSTRING(md5(RANDOM()::text), 1, 8)),
116	(ARRAY ['percentage', 'fixed'])[FLOOR(RANDOM() * 2 + 1)]::discount_type_enum,
117	CASE
118	WHEN RANDOM() < 0.5 THEN ROUND((5 + RANDOM() * 45)::numeric, 2)
119	ELSE ROUND((5 + RANDOM() * 50)::numeric, 2)
120	END,
121	CURRENT_DATE - (RANDOM() * INTERVAL '365 days'),
122	CURRENT_DATE + (RANDOM() * INTERVAL '180 days')
123	FROM company c
124	JOIN LATERAL generate_series(1, (1 + FLOOR(RANDOM() * 5))::int) gs ON TRUE;
125
126
127	-- ============================================================
128	-- 17. SEED — USER TABLE
129	-- ============================================================
130
131	INSERT INTO "user" (first_name, last_name, email, password_hash, role, is_active, created_at)
132	SELECT fn,
133	ln,
134	lower(fn) \|\| '.' \|\| lower(ln) \|\| row_number() OVER () \|\| '@example.com',
135	md5(random()::text),
136	CASE
137	WHEN rnd < 0.003 THEN 'admin'
138	WHEN rnd < 0.053 THEN 'staff'
139	WHEN rnd < 0.083 THEN 'owner'
140	ELSE 'client'
141	END::user_role_enum,
142	random() < 0.85,
143	NOW() - (pow(random(), 2) * INTERVAL '5 years')
144	FROM (SELECT fn,
145	ln,
146	random() AS rnd
147	FROM first_names_200_mf AS f(fn)
148	CROSS JOIN last_names_40 AS l(ln)
149	CROSS JOIN generate_series(1, 1000)
150	ORDER BY random()
151	LIMIT 1000000) base;
152
153
154	-- ============================================================
155	-- 18. SEED — CLIENT TABLE
156	-- ============================================================
157
158	INSERT INTO client (client_id, loyalty_points, date_of_birth, phone, notes)
159	SELECT user_id,
160	floor(random() * 5000)::int,
161	CURRENT_DATE - (floor(random() * 18628 + 6570)::int) * INTERVAL '1 day',
162	CASE
163	WHEN random() < 0.70 THEN '+389 7' \|\| floor(random() * 3)::int::text
164	\|\| ' ' \|\| floor(random() * 900 + 100)::int::text
165	\|\| ' ' \|\| floor(random() * 900 + 100)::int::text
166	WHEN random() < 0.33 THEN '+30 69' \|\| floor(random() * 8 + 1)::int::text
167	\|\| ' ' \|\| floor(random() * 900 + 100)::int::text
168	\|\| ' ' \|\| floor(random() * 9000 + 1000)::int::text
169	WHEN random() < 0.50 THEN '+359 8' \|\| floor(random() * 9)::int::text
170	\|\| ' ' \|\| floor(random() * 900 + 100)::int::text
171	\|\| ' ' \|\| floor(random() * 9000 + 1000)::int::text
172	ELSE '+381 6' \|\| floor(random() * 9)::int::text
173	\|\| ' ' \|\| floor(random() * 900 + 100)::int::text
174	\|\| ' ' \|\| floor(random() * 9000 + 1000)::int::text
175	END,
176	NULL
177	FROM "user"
178	WHERE role = 'client';
179
180
181	-- ============================================================
182	-- 19. SEED — OWNER TABLE
183	-- ============================================================
184
185	WITH company_slots AS (SELECT company_id,
186	generate_series(1, (1 + FLOOR(RANDOM() * 3))::int) AS slot
187	FROM company),
188	numbered_slots AS (SELECT company_id,
189	ROW_NUMBER() OVER () AS rn
190	FROM company_slots),
191	shuffled_owners AS (SELECT user_id,
192	ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
193	FROM "user"
194	WHERE role = 'owner'
195	LIMIT (SELECT COUNT(*) FROM numbered_slots))
196	INSERT
197	INTO owner (owner_id, company_id)
198	SELECT o.user_id, s.company_id
199	FROM shuffled_owners o
200	JOIN numbered_slots s ON o.rn = s.rn;
201
202	UPDATE owner
203	SET owner_since = NOW() - random() * INTERVAL '10 years'
204	where true;
205
206
207	-- ============================================================
208	-- 20. SEED — STAFF TABLE
209	-- ============================================================
210
211	INSERT INTO staff (staff_id, location_id, date_hired, hourly_rate)
212	SELECT u.user_id,
213	cl.location_id,
214	CURRENT_DATE - (RANDOM() * INTERVAL '5 years'),
215	ROUND((10 + RANDOM() * 40)::numeric, 2)
216	FROM (SELECT user_id,
217	ROW_NUMBER() OVER (ORDER BY user_id) AS rn
218	FROM "user"
219	WHERE role = 'staff') u
220	JOIN (SELECT location_id,
221	ROW_NUMBER() OVER (ORDER BY location_id) AS rn,
222	COUNT(*) OVER () AS total
223	FROM company_location) cl ON ((u.rn - 1) % cl.total) = (cl.rn - 1);
224
225
226
227	-- ============================================================
228	-- 21. SEED — STAFF TYPE
229	-- ============================================================
230
231	INSERT INTO staff_type (staff_id, role_type, specialty, certification, years_experience, extra_info)
232	SELECT s.staff_id,
233	(ARRAY ['hairdresser','nail_tech','esthetician',
234	'makeup_artist','receptionist'])[FLOOR(RANDOM() * 5 + 1)]::staff_role_type_enum,
235	'General Specialty',
236	'Certified Professional',
237	FLOOR(RANDOM() * 15)::int,
238	'Staff member'
239	FROM staff s
240	ON CONFLICT (staff_id, role_type) DO NOTHING;
241
242
243	-- ============================================================
244	-- 22. SEED — SERVICE CATEGORIES
245	-- ============================================================
246
247	INSERT INTO service_category (category_name, description)
248	VALUES ('Haircuts', 'Basic and premium haircut services for men and women.'),
249	('Hair Coloring', 'Highlights, full color, balayage, root touch-ups, and color correction.'),
250	('Extensions & Weaves', 'Hair extensions, tape-ins, clip-ins, and weaves for added length and volume.'),
251	('Styling & Blowouts', 'Blowouts, updos, curls, and everyday styling for events and special occasions.'),
252	('Nail Manicure', 'Basic nail care, shaping, cuticle treatment, and polish application.'),
253	('Nail Pedicure', 'Foot soak, exfoliation, callus removal, and nail polish application.'),
254	('Nail Art & Design', 'Decorative nail art, patterns, and custom designs.'),
255	('Acrylic & Gel Nails', 'Acrylic, gel, and hard-gel overlay services for long-lasting nails.'),
256	('Natural Nail Care', 'Gentle care focused on strengthening and maintaining healthy natural nails.'),
257	('Facials', 'Deep cleansing, exfoliation, masks, and moisturizing treatments for the face.'),
258	('Body Treatments', 'Body scrubs, wraps, and hydrating treatments for the whole body.'),
259	('Massages', 'Relaxing and therapeutic massages including neck, shoulder, back, and full-body.'),
260	('Manicure & Pedicure', 'Combined hand and foot nail services for relaxation and grooming.'),
261	('Waxing', 'Hair removal using wax for brows, legs, arms, bikini, and other body areas.'),
262	('Laser Hair Removal', 'Laser-based permanent or semi-permanent hair reduction.'),
263	('Eyebrow Shaping', 'Brow waxing, threading, or trimming for well-defined shape.'),
264	('Eyebrow Tinting', 'Enhancing brow color for a fuller, more defined look.'),
265	('Lash Extensions', 'Individual or volume lash extensions for longer, fuller lashes.'),
266	('Lash Lift & Tint', 'Lifting and tinting natural lashes for a wide-eyed look.'),
267	('Spray Tanning', 'Professional spray tanning for an even, sun-kissed glow.'),
268	('Bronzing & Contouring', 'Makeup contouring for facial definition and sculpting.'),
269	('Makeup Application', 'Day, evening, or special-event makeup application.'),
270	('Bridal Makeup', 'Full bridal makeup trial and wedding-day application.'),
271	('Skincare Consultation', 'Personalized skin analysis and tailored treatment plans.'),
272	('Chemical Peels', 'Exfoliating treatments to improve texture, tone, and clarity of the skin.'),
273	('Microneedling', 'Collagen-stimulating procedure to reduce fine lines, scars, and pores.'),
274	('Facial Hair Removal', 'Precision removal of facial hair using wax, threading, or threading-based methods.'),
275	('Body Hair Removal', 'Waxing and laser-assisted removal for larger body areas.'),
276	('Manicure & Pedicure Men', 'Manicure and pedicure services tailored for men.'),
277	('Men Haircuts', 'Haircut and styling services designed specifically for men.'),
278	('Men Beard Grooming', 'Beard trimming, shaping, oiling, and styling.'),
279	('Men Skincare', 'Facial and body skincare routines for men.'),
280	('Detox & Wellness Massage', 'Massage focused on detoxification and overall wellness.'),
281	('Aromatherapy Massage', 'Massage using essential oils to enhance relaxation and mood.'),
282	('Hot Stone Massage', 'Massage using heated stones to relieve muscle tension.'),
283	('Couples Massage', 'Simultaneous massage for two people in a relaxing environment.'),
284	('Prenatal Massage', 'Gentle massage tailored for pregnant women.'),
285	('Deep Tissue Massage', 'Intensive massage targeting deeper muscle layers and tension.'),
286	('Sports Massage', 'Massage focused on athletes and performance recovery.'),
287	('Body Polish & Exfoliation', 'Full-body exfoliation to remove dead skin and leave skin smooth.'),
288	('Hydration & Nourishing Treatments', 'Intensive moisturizing masks and treatments for dry or tired skin.'),
289	('Acne & Problem Skin Care', 'Targeted treatments for acne, breakouts, and oily or sensitive skin.'),
290	('Anti-Aging Treatments', 'Procedures and masks aimed at reducing signs of aging and wrinkles.'),
291	('Brightening & Even Skin Tone', 'Treatments designed to lighten dark spots and even out skin tone.'),
292	('LED Light Therapy', 'Non-invasive light treatments to support skin healing and rejuvenation.'),
293	('Microdermabrasion', 'Mechanical exfoliation to improve skin texture and brightness.'),
294	('Facial Cleansing', 'Deep cleansing and pore extraction for clearer skin.'),
295	('Facial Hydration', 'Intensive hydration masks and serums for dry or dehydrated skin.'),
296	('Body Sculpting & Wraps', 'Wraps and treatments aimed at temporary contouring and detox.'),
297	('Threading & Eyebrow Services', 'Precision threading for brows and facial hair lineups.'),
298	('Lash & Brow Tinting Combo', 'Combined tinting service for both lashes and brows.'),
299	('Express Manicure', 'Quick manicure focusing on basic shaping and polish.'),
300	('Express Pedicure', 'Fast pedicure service for basic nail care and polish.'),
301	('Express Facial', 'Short facial treatment for busy clients.'),
302	('Express Waxing', 'Quick waxing for small areas like brows or upper lip.'),
303	('On-Site Services', 'Mobile or on-site beauty services for events and home visits.'),
304	('Glow-Up Packages', 'Curated packages designed to enhance overall appearance and confidence.'),
305	('Consultation Only', 'Initial consultation without treatment for plan and pricing discussion.'),
306	('Hair Treatment', 'Keratin treatments, deep conditioning, scalp therapy, and repair treatments.'),
307	('Botox', 'Botulinum toxin injections to reduce wrinkles and fine lines.'),
308	('Fillers', 'Dermal filler injections for volume, contouring, and wrinkle correction.'),
309	('Body Contouring', 'Non-surgical body shaping treatments to reduce fat and tighten skin.');
310
311	WITH category_mapping(comp_cat, svc_cat) AS (VALUES ('Hair Salon', 'Haircuts'),
312	('Hair Salon', 'Hair Coloring'),
313	('Hair Salon', 'Extensions & Weaves'),
314	('Hair Salon', 'Styling & Blowouts'),
315	('Hair Salon', 'Hair Treatment'),
316	('Nail Salon', 'Nail Manicure'),
317	('Nail Salon', 'Nail Pedicure'),
318	('Nail Salon', 'Nail Art & Design'),
319	('Nail Salon', 'Acrylic & Gel Nails'),
320	('Nail Salon', 'Express Manicure'),
321	('Barbershop', 'Men Haircuts'),
322	('Barbershop', 'Men Beard Grooming'),
323	('Barbershop', 'Men Skincare'),
324	('Spa', 'Massages'),
325	('Spa', 'Body Treatments'),
326	('Spa', 'Hot Stone Massage'),
327	('Spa', 'Facial Hydration'),
328	('Skin Care', 'Facials'),
329	('Skin Care', 'Chemical Peels'),
330	('Skin Care', 'Microneedling'),
331	('Skin Care', 'Skincare Consultation'),
332	('Waxing Studio', 'Waxing'),
333	('Waxing Studio', 'Facial Hair Removal'),
334	('Waxing Studio', 'Express Waxing'),
335	('Eyebrow & Lash Studio', 'Eyebrow Shaping'),
336	('Eyebrow & Lash Studio', 'Eyebrow Tinting'),
337	('Eyebrow & Lash Studio', 'Lash Extensions'),
338	('Med Spa', 'Botox'),
339	('Med Spa', 'Fillers'),
340	('Med Spa', 'Body Contouring'),
341	('Med Spa', 'Chemical Peels'),
342	('Massage Therapy', 'Massages'),
343	('Massage Therapy', 'Deep Tissue Massage'),
344	('Massage Therapy', 'Sports Massage')),
345
346	-- 2. Дефинираме конкретни услуги за секоја категорија на услуга
347	service_blueprints(svc_cat_name, svc_name, duration, base_price) AS (VALUES ('Haircuts', 'Basic Haircut', 30,
348	35.00),
349	('Hair Coloring', 'Full Hair Color',
350	90,
351	120.00),
352	('Styling & Blowouts',
353	'Blowout Styling', 45,
354	55.00),
355	('Men Haircuts', 'Men’s Fade & Style',
356	45,
357	40.00),
358	('Men Beard Grooming',
359	'Full Beard Grooming',
360	30, 30.00),
361	('Hair Treatment',
362	'Deep Conditioning Mask',
363	45, 60.00),
364
365
366	('Nail Manicure', 'Classic Manicure',
367	30,
368	25.00),
369	('Nail Pedicure', 'Spa Pedicure', 45,
370	40.00),
371	('Nail Art & Design',
372	'Custom Nail Art (Full Set)', 60,
373	50.00),
374	('Acrylic & Gel Nails',
375	'Gel Extensions', 90,
376	85.00),
377
378
379	('Facials', 'Signature Facial', 60,
380	85.00),
381	('Body Treatments',
382	'Sea Salt Body Scrub', 60,
383	95.00),
384	('Chemical Peels', 'Glycolic Peel', 45,
385	85.00),
386	('Microneedling',
387	'Microneedling Session',
388	120, 250.00),
389	('Skincare Consultation',
390	'Skin Analysis & Plan', 30, 45.00),
391
392
393	('Waxing', 'Full Body Wax', 120,
394	140.00),
395	('Laser Hair Removal',
396	'Laser Hair Removal - Face', 30,
397	150.00),
398	('Eyebrow Shaping',
399	'Signature Brow Shaping',
400	30, 25.00),
401
402
403	('Lash Extensions',
404	'Lash Extensions - Classic', 90,
405	120.00),
406	('Lash Lift & Tint',
407	'Lash Lift & Tint Combo',
408	60, 110.00),
409	('Spray Tanning',
410	'Spray Tan - Full Body', 30,
411	50.00),
412
413
414	('Makeup Application',
415	'Full Face Glam', 60,
416	75.00),
417	('Bridal Makeup',
418	'Bridal Trial & Day-of',
419	120, 250.00),
420	('Bronzing & Contouring',
421	'Express Sculpt & Glow', 30, 40.00),
422
423
424	('Massages', 'Swedish Massage', 60,
425	80.00),
426	('Deep Tissue Massage',
427	'Deep Tissue Relief',
428	60, 110.00),
429	('Hot Stone Massage',
430	'Hot Stone Therapy', 90,
431	130.00),
432	('Aromatherapy Massage',
433	'Essential Oil Relaxer', 60, 95.00),
434
435
436	('Botox', 'Botox Treatment (per area)',
437	30,
438	200.00),
439	('Fillers', 'Dermal Filler Session',
440	45,
441	500.00),
442
443
444	('Consultation Only',
445	'Professional Consultation', 15,
446	0.00))
447
448	INSERT
449	INTO service (company_id, service_category_id, service_name, duration_minutes, price, is_active)
450	SELECT ccc.company_id,
451	sc.service_category_id,
452	sb.svc_name,
453	sb.duration,
454	sb.base_price,
455	TRUE
456	FROM company_company_category ccc
457	JOIN company_category cc ON ccc.company_category_id = cc.company_category_id
458	JOIN category_mapping cm ON cc.category_name = cm.comp_cat
459	JOIN service_category sc ON sc.category_name = cm.svc_cat
460	JOIN service_blueprints sb ON sb.svc_cat_name = sc.category_name
461	ON CONFLICT DO NOTHING;
462
463
464	-- ============================================================
465	-- 24. SEED — STAFF SERVICE
466	-- ============================================================
467
468	INSERT INTO staff_service (staff_id, service_id)
469	SELECT DISTINCT ON (s.staff_id, sv.service_id) s.staff_id,
470	sv.service_id
471	FROM staff s
472	JOIN staff_type st ON st.staff_id = s.staff_id
473	JOIN company_location cl ON cl.location_id = s.location_id
474	JOIN service sv ON sv.company_id = cl.company_id AND sv.is_active = TRUE
475	JOIN service_category sc ON sc.service_category_id = sv.service_category_id
476	WHERE (
477	(st.role_type = 'hairdresser' AND sc.category_name IN (
478	'Haircuts', 'Hair Coloring', 'Extensions & Weaves',
479	'Styling & Blowouts', 'Men Haircuts', 'Men Beard Grooming',
480	'Hair Treatment'))
481	OR (st.role_type = 'nail_tech' AND sc.category_name IN (
482	'Nail Manicure', 'Nail Pedicure', 'Nail Art & Design',
483	'Acrylic & Gel Nails', 'Natural Nail Care',
484	'Manicure & Pedicure',
485	'Express Manicure', 'Express Pedicure'))
486	OR (st.role_type = 'esthetician' AND sc.category_name IN (
487	'Facials', 'Body Treatments', 'Waxing',
488	'Laser Hair Removal',
489	'Chemical Peels', 'Microneedling', 'Eyebrow Shaping',
490	'Eyebrow Tinting',
491	'Lash Extensions', 'Lash Lift & Tint',
492	'Spray Tanning', 'Massages',
493	'Deep Tissue Massage', 'Sports Massage',
494	'Hot Stone Massage',
495	'Couples Massage', 'Aromatherapy Massage',
496	'Prenatal Massage',
497	'Detox & Wellness Massage',
498	'Acne & Problem Skin Care',
499	'Anti-Aging Treatments', 'Facial Hair Removal',
500	'Body Hair Removal',
501	'Skincare Consultation', 'Skin Care',
502	'LED Light Therapy', 'Microdermabrasion',
503	'Body Contouring',
504	'Botox', 'Fillers'))
505	OR (st.role_type = 'makeup_artist' AND sc.category_name IN (
506	'Makeup Application', 'Bridal Makeup',
507	'Bronzing & Contouring'))
508	OR (st.role_type = 'receptionist' AND sc.category_name IN (
509	'Skincare Consultation', 'Consultation Only'))
510	)
511	AND (SELECT COUNT(*) FROM staff_service ss WHERE ss.staff_id = s.staff_id) < 3
512	ON CONFLICT DO NOTHING;
513
514
515	-- ============================================================
516	-- 25. SEED — STAFF AVAILABILITY
517	-- ============================================================
518
519	WITH location_hours AS (SELECT bh.location_id,
520	bh.day_of_week,
521	bh.open_time,
522	bh.close_time,
523	EXTRACT(EPOCH FROM (bh.close_time - bh.open_time)) / 60 AS available_minutes
524	FROM business_hours bh
525	WHERE bh.is_closed = FALSE
526	AND bh.day_of_week::text <> 'sunday'),
527	staff_days AS (SELECT s.staff_id,
528	lh.day_of_week,
529	lh.open_time,
530	lh.available_minutes
531	FROM staff s
532	JOIN location_hours lh ON lh.location_id = s.location_id),
533	staff_shifts AS (SELECT staff_id,
534	day_of_week,
535	open_time,
536	available_minutes,
537	LEAST(
538	(ARRAY [240, 360, 480])[FLOOR(RANDOM() * 3 + 1)::int],
539	available_minutes::int
540	) AS shift_minutes
541	FROM staff_days),
542	staff_timed AS (SELECT staff_id,
543	day_of_week,
544	open_time,
545	available_minutes,
546	shift_minutes,
547	SQRT(-2.0 * LN(NULLIF(RANDOM(), 0))) * COS(2 * PI() * RANDOM()) AS z
548	FROM staff_shifts),
549	staff_timed_clamped AS (SELECT staff_id,
550	day_of_week,
551	shift_minutes,
552	(open_time + (
553	GREATEST(0, LEAST(
554	available_minutes - shift_minutes,
555	ROUND(
556	((available_minutes - shift_minutes) / 2.0)
557	+ z * ((available_minutes - shift_minutes) / 6.0)
558	)
559	)) \|\| ' minutes')::interval)::time AS start_time
560	FROM staff_timed)
561	INSERT
562	INTO staff_availability (staff_id, day_of_week, start_time, end_time)
563	SELECT staff_id,
564	day_of_week,
565	start_time,
566	(start_time + (shift_minutes \|\| ' minutes')::interval)::time AS end_time
567	FROM staff_timed_clamped
568	ON CONFLICT (staff_id, day_of_week) DO NOTHING;
569
570
571
572	-- ============================================================
573	-- 26. SEED — BLOCKED TIME
574	-- ============================================================
575
576	WITH block_series AS (SELECT s.staff_id,
577	gs.block_num,
578	random() < 0.60 AS is_single_day,
579	(NOW() - (random() * INTERVAL '2 years'))::date AS block_date,
580	random() AS reason_rnd
581	FROM staff s
582	CROSS JOIN generate_series(1, (2 + FLOOR(RANDOM() * 4))::int) AS gs(block_num)),
583	blocks_computed AS (SELECT staff_id,
584	(block_date::timestamp + TIME '09:00')::timestamptz AS start_datetime,
585	CASE
586	WHEN is_single_day
587	THEN (block_date::timestamp + TIME '17:00')::timestamptz
588	ELSE (block_date::timestamp
589	+ ((3 + FLOOR(RANDOM() * 5)) \|\| ' days')::interval
590	+ TIME '17:00')::timestamptz
591	END AS end_datetime,
592	CASE
593	WHEN reason_rnd < 0.20 THEN 'Sick day'
594	WHEN reason_rnd < 0.40 THEN 'Personal leave'
595	WHEN reason_rnd < 0.55 THEN 'Vacation'
596	WHEN reason_rnd < 0.70 THEN 'Medical appointment'
597	WHEN reason_rnd < 0.80 THEN 'Family emergency'
598	WHEN reason_rnd < 0.90 THEN 'Training / workshop'
599	END AS reason
600	FROM block_series)
601	INSERT
602	INTO blocked_time (staff_id, start_datetime, end_datetime, reason)
603	SELECT staff_id, start_datetime, end_datetime, reason
604	FROM blocks_computed
605	WHERE end_datetime > start_datetime;
606
607
608	-- ============================================================
609	-- 27. SEED — APPOINTMENTS + APPOINTMENT_SERVICE
610	-- ============================================================
611
612	-- COMPLETED ROWS
613	CREATE TEMP TABLE tmp_clients AS
614	SELECT client_id,
615	ROW_NUMBER() OVER (ORDER BY random()) AS rn
616	FROM client;
617
618	CREATE TEMP TABLE tmp_staff_service AS
619	SELECT ss.staff_id,
620	ss.service_id,
621	sv.duration_minutes,
622	sv.price,
623	s.location_id,
624	ROW_NUMBER() OVER (ORDER BY random()) AS rn
625	FROM staff_service ss
626	JOIN service sv ON sv.service_id = ss.service_id
627	JOIN staff s ON s.staff_id = ss.staff_id;
628
629	INSERT INTO appointment (client_id, staff_id, location_id,
630	appointment_date, appointment_time, end_time,
631	status, booked_at, cancelled_at, cancellation_reason)
632	SELECT DISTINCT ON (gen.staff_id, gen.appt_date, gen.start_time) t.client_id,
633	gen.staff_id,
634	gen.location_id,
635	gen.appt_date,
636	gen.start_time,
637	(gen.start_time + (gen.duration_minutes \|\| ' minutes')::interval)::time,
638	gen.status_val,
639	(gen.appt_date::timestamp - (random() * INTERVAL '30 days'))::timestamptz,
640	CASE
641	WHEN gen.status_val = 'cancelled'
642	THEN (gen.appt_date::timestamp - (random() * INTERVAL '5 days'))::timestamptz
643	END,
644	CASE
645	WHEN gen.status_val = 'cancelled'
646	THEN (ARRAY ['Client no-show','Staff unavailable','Personal reasons',
647	'Rescheduled','Weather conditions'])[FLOOR(random() * 5 + 1)::int]
648	END
649	FROM (SELECT ss.staff_id,
650	ss.location_id,
651	ss.duration_minutes,
652	(CURRENT_DATE - (random() * 1825)::int) AS appt_date,
653	(TIME '08:00' + (FLOOR(random() * 40) * 15) * INTERVAL '1 minute')::time AS start_time,
654	CASE
655	WHEN random() < 0.60 THEN 'completed'
656	WHEN random() < 0.75 THEN 'confirmed'
657	WHEN random() < 0.90 THEN 'cancelled'
658	ELSE 'pending'
659	END::appointment_status_enum AS status_val,
660	ROW_NUMBER() OVER () AS rn
661	FROM tmp_staff_service ss
662	CROSS JOIN generate_series(1, 10)
663	ORDER BY random()
664	LIMIT 500000) gen
665	JOIN tmp_clients t ON (gen.rn % (SELECT COUNT(*) FROM tmp_clients)) = (t.rn - 1)
666	ON CONFLICT (staff_id, appointment_date, appointment_time) DO NOTHING;
667
668	DROP TABLE tmp_clients;
669	DROP TABLE tmp_staff_service;
670
671	select count(*) from appointment a ;
672	---- PENDING ROWS
673
674	CREATE TEMP TABLE tmp_clients_p AS
675	SELECT client_id,
676	ROW_NUMBER() OVER (ORDER BY random()) AS rn
677	FROM client;
678
679	CREATE TEMP TABLE tmp_staff_service_p AS
680	SELECT ss.staff_id,
681	ss.service_id,
682	sv.duration_minutes,
683	sv.price,
684	s.location_id,
685	ROW_NUMBER() OVER (ORDER BY random()) AS rn
686	FROM staff_service ss
687	JOIN service sv ON sv.service_id = ss.service_id
688	JOIN staff s ON s.staff_id = ss.staff_id;
689
690	INSERT INTO appointment (client_id, staff_id, location_id,
691	appointment_date, appointment_time, end_time,
692	status, booked_at, cancelled_at, cancellation_reason)
693	SELECT DISTINCT ON (gen.staff_id, gen.appt_date, gen.start_time) t.client_id,
694	gen.staff_id,
695	gen.location_id,
696	gen.appt_date,
697	gen.start_time,
698	(gen.start_time + (gen.duration_minutes \|\| ' minutes')::interval)::time,
699	'pending'::appointment_status_enum,
700	(NOW() - (random() * INTERVAL '14 days'))::timestamptz,
701	NULL,
702	NULL
703	FROM (SELECT tss.staff_id,
704	tss.location_id,
705	tss.duration_minutes,
706	(CURRENT_DATE + (14 + (random() * 106)::int)) AS appt_date,
707	(TIME '08:00' + (FLOOR(random() * 40) * 15) * INTERVAL '1 minute')::time AS start_time,
708	ROW_NUMBER() OVER () AS rn
709	FROM tmp_staff_service_p tss
710	CROSS JOIN generate_series(1, 10)
711	ORDER BY random()
712	LIMIT 500000) gen
713	JOIN tmp_clients_p t ON (gen.rn % (SELECT COUNT(*) FROM tmp_clients_p)) = (t.rn - 1)
714	ON CONFLICT (staff_id, appointment_date, appointment_time) DO NOTHING;
715
716	DROP TABLE tmp_clients_p;
717	DROP TABLE tmp_staff_service_p;
718
719
720	---CANCELLED ROWS
721
722	CREATE TEMP TABLE tmp_clients_c AS
723	SELECT client_id,
724	ROW_NUMBER() OVER (ORDER BY random()) AS rn
725	FROM client;
726
727	CREATE TEMP TABLE tmp_staff_service_c AS
728	SELECT ss.staff_id,
729	ss.service_id,
730	sv.duration_minutes,
731	sv.price,
732	s.location_id,
733	ROW_NUMBER() OVER (ORDER BY random()) AS rn
734	FROM staff_service ss
735	JOIN service sv ON sv.service_id = ss.service_id
736	JOIN staff s ON s.staff_id = ss.staff_id;
737
738	INSERT INTO appointment (client_id, staff_id, location_id,
739	appointment_date, appointment_time, end_time,
740	status, booked_at, cancelled_at, cancellation_reason)
741	SELECT DISTINCT ON (gen.staff_id, gen.appt_date, gen.start_time) t.client_id,
742	gen.staff_id,
743	gen.location_id,
744	gen.appt_date,
745	gen.start_time,
746	(gen.start_time + (gen.duration_minutes \|\| ' minutes')::interval)::time,
747	'cancelled'::appointment_status_enum,
748	-- booked_at before the appointment date
749	(gen.appt_date::timestamp - (random() * INTERVAL '30 days'))::timestamptz,
750	-- canceled_at: between booked_at and the appointment date (1–5 days before appt)
751	(gen.appt_date::timestamp - (random() * INTERVAL '5 days'))::timestamptz,
752	(ARRAY ['Client no-show','Staff unavailable','Personal reasons',
753	'Rescheduled','Weather conditions'])[FLOOR(random() * 5 + 1)::int]
754	FROM (SELECT tssc.staff_id,
755	tssc.location_id,
756	tssc.duration_minutes,
757	-- any point in the past 5 years
758	(CURRENT_DATE - (random() * 1825)::int) AS appt_date,
759	(TIME '08:00' + (FLOOR(random() * 40) * 15) * INTERVAL '1 minute')::time AS start_time,
760	ROW_NUMBER() OVER () AS rn
761	FROM tmp_staff_service_c tssc
762	CROSS JOIN generate_series(1, 10)
763	ORDER BY random()
764	LIMIT 100000) gen
765	JOIN tmp_clients_c t ON (gen.rn % (SELECT COUNT(*) FROM tmp_clients_c)) = (t.rn - 1)
766	ON CONFLICT (staff_id, appointment_date, appointment_time) DO NOTHING;
767
768	DROP TABLE tmp_clients_c;
769
770	DROP TABLE tmp_staff_service_c;
771
772
773	-- APPOINTMENT SERVICE
774	INSERT INTO appointment_service (appointment_id, service_id, duration_minutes, price)
775	SELECT appointment_id, service_id, duration_minutes, price
776	FROM (SELECT a.appointment_id,
777	s.service_id,
778	s.duration_minutes,
779	s.price,
780	ROW_NUMBER() OVER (
781	PARTITION BY a.appointment_id
782	ORDER BY random()
783	) AS rn
784	FROM appointment a
785	JOIN staff_service ss ON ss.staff_id = a.staff_id
786	JOIN service s ON s.service_id = ss.service_id
787	WHERE random() < 0.6) sub
788	WHERE rn <= 3
789	ON CONFLICT DO NOTHING;
790
791
792
793	-- UPDATE na end_time spored sum od services vo Appointment
794
795	UPDATE appointment a
796	SET end_time = (
797	a.appointment_time + (sub.total_minutes \|\| ' minutes')::interval
798	)::time
799	FROM (SELECT appointment_id, SUM(duration_minutes) AS total_minutes
800	FROM appointment_service
801	GROUP BY appointment_id) sub
802	WHERE a.appointment_id = sub.appointment_id;
803	select * from appointment;
804
805
806	-- ============================================================
807	-- 28. SEED — PRODUCTS
808	-- ============================================================
809
810	INSERT INTO product (product_name, brand, unit_price)
811	SELECT p AS product_name,
812	b AS brand,
813	ROUND((RANDOM() * 140 + 10)::NUMERIC(10, 2), 2) AS unit_price
814	FROM beauty_products_name p
815	CROSS JOIN brands_name b
816	CROSS JOIN generate_series(1, 1667)
817	ORDER BY RANDOM()
818	LIMIT 1000;
819
820
821
822
823	UPDATE product
824	SET unit_price = ROUND((RANDOM() * 140 + 10)::NUMERIC(10, 2), 2)
825	where true;
826
827
828	-- ============================================================
829	-- 29. SEED — INVENTORY
830	-- ============================================================
831
832	ALTER TABLE inventory
833	SET UNLOGGED;
834
835	WITH product_totals AS (SELECT product_id,
836	(RANDOM() * 9997 + 3) AS total_units
837	FROM product)
838	INSERT
839	INTO inventory (product_id, location_id, quantity_on_hand)
840	SELECT p.product_id,
841	l.location_id,
842	GREATEST(1, ROUND((p.total_units * (0.5 + RANDOM()) / 2)::NUMERIC, 3))
843	FROM product_totals p
844	JOIN LATERAL (
845	SELECT location_id
846	FROM company_location
847	ORDER BY RANDOM()
848	LIMIT (1 + FLOOR(RANDOM() * 3))::int
849	) l ON TRUE;
850
851	ALTER TABLE inventory
852	SET LOGGED;
853
854
855	-- ============================================================
856	-- 30. SEED — APPOINTMENT PRODUCTS
857	-- ============================================================
858
859	INSERT INTO appointment_product (appointment_id, product_id, quantity_used)
860	SELECT a.appointment_id,
861	((ROW_NUMBER() OVER (ORDER BY RANDOM()) - 1) % (SELECT MAX(product_id) FROM product)) + 1,
862	ROUND((0.1 + RANDOM() * 2.9)::numeric, 3)
863	FROM appointment a
864	WHERE RANDOM() < 0.40
865	ON CONFLICT DO NOTHING;
866
867
868	-- ============================================================
869	-- 31. SEED — INVOICES + PROMO DISCOUNTS
870	-- ============================================================
871
872
873	-- insert invoices for all completed appointments
874	INSERT INTO invoice (appointment_id, client_id, invoice_date, subtotal, tax, payment_method)
875	SELECT a.appointment_id,
876	a.client_id,
877	a.appointment_date AS invoice_date,
878	SUM(as2.price) AS subtotal,
879	ROUND(SUM(as2.price) * 0.18, 2) AS tax,
880	CASE
881	WHEN random() < 0.60 THEN 'card'
882	WHEN random() < 0.75 THEN 'cash'
883	ELSE 'loyalty_points'
884	END::payment_method_enum
885	FROM appointment a
886	JOIN appointment_service as2 ON as2.appointment_id = a.appointment_id
887	WHERE a.status = 'completed'
888	GROUP BY a.appointment_id, a.client_id, a.appointment_date;
889
890	-- match valid promos (~20% of invoices)
891	CREATE TEMP TABLE temp_matched_promos AS
892	WITH invoice_company AS (SELECT i.invoice_id,
893	i.invoice_date,
894	i.subtotal,
895	cl.company_id
896	FROM invoice i
897	JOIN appointment a ON a.appointment_id = i.appointment_id
898	JOIN company_location cl ON cl.location_id = a.location_id
899	WHERE random() < 0.20),
900	matched AS (SELECT DISTINCT ON (ic.invoice_id) ic.invoice_id,
901	p.promo_id,
902	p.discount_type,
903	p.discount_value,
904	ic.subtotal,
905	CASE
906	WHEN p.discount_type = 'percentage'
907	THEN ROUND(ic.subtotal * p.discount_value / 100.0, 2)
908	ELSE LEAST(p.discount_value, ic.subtotal)
909	END AS discount_amount
910	FROM invoice_company ic
911	JOIN promo_code p
912	ON p.company_id = ic.company_id
913	AND p.valid_from <= ic.invoice_date
914	AND p.valid_until >= ic.invoice_date
915	ORDER BY ic.invoice_id, RANDOM())
916	SELECT *
917	FROM matched;
918
919	-- apply discounts
920	UPDATE invoice i
921	SET discount_total = t.discount_amount
922	FROM temp_matched_promos t
923	WHERE i.invoice_id = t.invoice_id;
924
925	-- audit trail
926	INSERT INTO invoice_promo (invoice_id, promo_id)
927	SELECT invoice_id, promo_id
928	FROM temp_matched_promos
929	ON CONFLICT DO NOTHING;
930
931	DROP TABLE temp_matched_promos;
932
933
934	-- ============================================================
935	-- 32. SEED — REVIEWS
936	-- ============================================================
937
938	WITH completed AS (SELECT a.appointment_id, a.client_id, a.appointment_date
939	FROM appointment a
940	WHERE a.status = 'completed'
941	AND random() < 0.50)
942	INSERT
943	INTO review (appointment_id, client_id, rating, comment, created_at)
944	SELECT appointment_id,
945	client_id,
946	rating,
947	CASE
948	WHEN random() < 0.30 THEN
949	CASE rating
950	WHEN 5 THEN (ARRAY ['Fantastic, best salon in town!','Exceeded my expectations!',
951	'Great service, very satisfied!','Will definitely come back.',
952	'Very relaxing, highly recommend.'])[FLOOR(RANDOM() * 5 + 1)::int]
953	WHEN 4 THEN (ARRAY ['Staff was friendly and professional.','Good experience overall.',
954	'Nice atmosphere and great results.','Quick and efficient service.',
955	'Very clean and welcoming space.'])[FLOOR(RANDOM() * 5 + 1)::int]
956	WHEN 3 THEN (ARRAY ['Average service, nothing special.','A bit rushed but acceptable.',
957	'Decent but room for improvement.','Was okay, not great not bad.',
958	'Expected a bit more for the price.'])[FLOOR(RANDOM() * 5 + 1)::int]
959	WHEN 2 THEN (ARRAY ['Not what I expected.','Disappointed with the results.',
960	'Service was below average.','Staff seemed uninterested.',
961	'Would think twice before returning.'])[FLOOR(RANDOM() * 5 + 1)::int]
962	WHEN 1 THEN (ARRAY ['Would not recommend.','Very disappointed with the service.',
963	'Terrible experience overall.','Staff was rude and unprofessional.',
964	'Complete waste of money.'])[FLOOR(RANDOM() * 5 + 1)::int]
965	END
966	END AS comment,
967	(appointment_date + (random() * INTERVAL '7 days'))::timestamptz AS created_at
968	FROM (SELECT appointment_id,
969	client_id,
970	appointment_date,
971	CASE
972	WHEN random() < 0.40 THEN 5
973	WHEN random() < 0.70 THEN 4
974	WHEN random() < 0.85 THEN 3
975	WHEN random() < 0.95 THEN 2
976	ELSE 1
977	END::SMALLINT AS rating
978	FROM completed) r;
979
980
981	-- ============================================================
982	-- 33. SEED — LOYALTY TRANSACTIONS + POINT SYNC
983	-- ============================================================
984
985	-- earning rows
986	INSERT INTO loyalty_transaction (client_id, appointment_id, points_earned, points_spent, created_at)
987	SELECT i.client_id,
988	i.appointment_id,
989	FLOOR(i.total)::int AS points_earned,
990	0 AS points_spent,
991	(i.invoice_date + (random() * INTERVAL '1 hour'))::timestamptz
992	FROM invoice i
993	WHERE i.payment_method <> 'loyalty_points'
994	AND FLOOR(i.total) > 0;
995
996	-- spending rows
997	INSERT INTO loyalty_transaction (client_id, appointment_id, points_earned, points_spent, created_at)
998	SELECT i.client_id,
999	i.appointment_id,
1000	0 AS points_earned,
1001	FLOOR(i.subtotal)::int AS points_spent,
1002	(i.invoice_date + (random() * INTERVAL '1 hour'))::timestamptz
1003	FROM invoice i
1004	WHERE i.payment_method = 'loyalty_points'
1005	AND FLOOR(i.total) > 0;
1006
1007	-- zero phantom balances
1008	UPDATE client
1009	SET loyalty_points = 0
1010	where true;
1011
1012	-- sync net balance
1013	UPDATE client c
1014	SET loyalty_points = sub.net_points
1015	FROM (SELECT client_id,
1016	GREATEST(0, COALESCE(SUM(points_earned), 0) - COALESCE(SUM(points_spent), 0))::int AS net_points
1017	FROM loyalty_transaction
1018	GROUP BY client_id) sub
1019	WHERE c.client_id = sub.client_id;
1020
1021
1022
1023	-- ============================================================
1024	-- 34. SEED — SERVICE PRICE HISTORY
1025	-- ============================================================
1026
1027	WITH RECURSIVE
1028	service_base AS (SELECT service_id,
1029	price,
1030	duration_minutes,
1031	FLOOR(RANDOM() * 4)::INT AS change_count
1032	FROM service
1033	WHERE is_active = TRUE),
1034	price_changes AS (SELECT service_id,
1035	1 AS iteration,
1036	change_count,
1037	price::NUMERIC(10, 2) AS new_price,
1038	ROUND((price * 0.9)::NUMERIC, 2)::NUMERIC(10, 2) AS old_price,
1039	(NOW() - (RANDOM() * INTERVAL '6 months'))::TIMESTAMPTZ AS changed_at
1040	FROM service_base
1041	WHERE change_count >= 1
1042
1043	UNION ALL
1044
1045	SELECT pc.service_id,
1046	pc.iteration + 1,
1047	pc.change_count,
1048	pc.old_price,
1049	ROUND((pc.old_price * 0.9)::NUMERIC, 2)::NUMERIC(10, 2),
1050	(pc.changed_at - (RANDOM() * INTERVAL '6 months'))::TIMESTAMPTZ
1051	FROM price_changes pc
1052	WHERE pc.iteration < pc.change_count)
1053	INSERT
1054	INTO service_price_history (service_id, old_price, new_price, changed_at)
1055	SELECT service_id,
1056	GREATEST(old_price, 5.00),
1057	GREATEST(new_price, 5.00),
1058	changed_at
1059	FROM price_changes;
1060
1061
1062	-- ============================================================
1063	-- 35. SEED — STAFF TIME SLOTS (WITH ADJUSTABLE LIMIT)
1064	-- ============================================================
1065
1066	ALTER TABLE staff_time_slot SET UNLOGGED;
1067
1068	INSERT INTO staff_time_slot (staff_id, slot_start)
1069	SELECT staff_id, slot_start
1070	FROM (SELECT sa.staff_id,
1071	(
1072	d.slot_date
1073	+ sa.start_time
1074	+ (gs * INTERVAL '15 minutes')
1075	)::timestamp AS slot_start
1076	FROM staff_availability sa
1077	JOIN generate_series(
1078	CURRENT_DATE - INTERVAL '7 days',
1079	CURRENT_DATE + INTERVAL '30 days',
1080	INTERVAL '1 day'
1081	) AS d(slot_date)
1082	ON EXTRACT(DOW FROM d.slot_date) =
1083	CASE sa.day_of_week
1084	WHEN 'sunday' THEN 0
1085	WHEN 'monday' THEN 1
1086	WHEN 'tuesday' THEN 2
1087	WHEN 'wednesday' THEN 3
1088	WHEN 'thursday' THEN 4
1089	WHEN 'friday' THEN 5
1090	WHEN 'saturday' THEN 6
1091	END
1092	CROSS JOIN LATERAL generate_series(
1093	0,
1094	FLOOR(EXTRACT(EPOCH FROM (sa.end_time - sa.start_time)) / 900) - 1
1095	) AS gs
1096	LIMIT 1000000) AS limited_slots
1097	ON CONFLICT (staff_id, slot_start) DO NOTHING;
1098
1099	-- remove slots that overlap blocked_time
1100	DELETE FROM staff_time_slot ts
1101	WHERE EXISTS (SELECT 1
1102	FROM blocked_time b
1103	WHERE b.staff_id = ts.staff_id
1104	AND ts.slot_start < b.end_datetime
1105	AND ts.slot_start + INTERVAL '15 minutes' > b.start_datetime);
1106
1107	ALTER TABLE staff_time_slot SET LOGGED;

Download in other formats:

Original Format