Context Navigation

Back to DatabaseCreation

DatabaseCreation: 01_schema_initial_seed.sql

File 01_schema_initial_seed.sql, 30.6 KB (added by 231184, 4 days ago)
dataSchemaInitialSeed

Line
1	-- ========================
2	-- CLEAN START
3	-- ========================
4	DROP SCHEMA public CASCADE;
5	CREATE SCHEMA public;
6
7
8	-- USER
9	CREATE TABLE "user" (
10	user_id SERIAL NOT NULL,
11	email VARCHAR(100) NOT NULL,
12	password VARCHAR(255) NOT NULL,
13	is_active BOOLEAN NOT NULL DEFAULT TRUE,
14	created_at TIMESTAMP(7),
15
16	CONSTRAINT pk_user PRIMARY KEY (user_id),
17	CONSTRAINT uq_user_email UNIQUE (email),
18
19	CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
20	CHECK (
21	length(password) >= 8
22	AND password ~ '[A-Z]'
23	AND password ~ '[a-z]'
24	AND password ~ '[0-9]'
25	AND password ~ '[^A-Za-z0-9]'
26	)
27	);
28
29
30	-- CUSTOMER
31	CREATE TABLE customer (
32	customer_id SERIAL NOT NULL,
33	user_id INT NOT NULL DEFAULT 1,
34	first_name VARCHAR(50) CHECK (first_name IS NULL OR first_name ~ '^[A-Za-z]+$'),
35	last_name VARCHAR(50) CHECK (last_name IS NULL OR last_name ~ '^[A-Za-z]+$'),
36	phone VARCHAR(20),
37	created_at TIMESTAMP(7),
38
39	CONSTRAINT pk_customer PRIMARY KEY (customer_id),
40	CONSTRAINT uq_customer_user UNIQUE (user_id),
41
42	CHECK (phone IS NULL OR phone ~ '^[0-9+()-]{6,20}$'),
43
44	CONSTRAINT fk_customer_user
45	FOREIGN KEY (user_id) REFERENCES "user"(user_id)
46	ON DELETE SET DEFAULT ON UPDATE CASCADE
47	);
48
49	-- EMPLOYEE
50	CREATE TABLE employee (
51	employee_id SERIAL NOT NULL,
52	user_id INT NOT NULL DEFAULT 1,
53	first_name VARCHAR(50) CHECK (last_name IS NULL OR last_name ~ '^[A-Za-z]+$'),
54	last_name VARCHAR(50) CHECK (last_name IS NULL OR last_name ~ '^[A-Za-z]+$'),
55	hire_date DATE,
56	bio TEXT,
57	is_active BOOLEAN NOT NULL DEFAULT TRUE,
58	created_at TIMESTAMP(7),
59
60	CONSTRAINT pk_employee PRIMARY KEY (employee_id),
61	CONSTRAINT uq_employee_user UNIQUE (user_id),
62
63	CHECK (hire_date IS NULL OR hire_date <= CURRENT_DATE),
64
65	CONSTRAINT fk_employee_user
66	FOREIGN KEY (user_id) REFERENCES "user"(user_id)
67	ON DELETE SET DEFAULT ON UPDATE CASCADE
68	);
69
70	-- MANAGER
71	CREATE TABLE manager (
72	manager_id SERIAL NOT NULL,
73	user_id INT NOT NULL DEFAULT 1,
74	created_at TIMESTAMP(7),
75
76	CONSTRAINT pk_manager PRIMARY KEY (manager_id),
77	CONSTRAINT uq_manager_user UNIQUE (user_id),
78
79	CONSTRAINT fk_manager_user
80	FOREIGN KEY (user_id) REFERENCES "user"(user_id)
81	ON DELETE SET DEFAULT ON UPDATE CASCADE
82	);
83
84	-- BUSINESS
85	CREATE TABLE business (
86	business_id SERIAL NOT NULL,
87	name VARCHAR(100),
88	description TEXT,
89	phone VARCHAR(20),
90	email VARCHAR(100),
91	created_at TIMESTAMP(7),
92
93	CONSTRAINT pk_business PRIMARY KEY (business_id),
94
95	CHECK (name IS NULL OR length(name) > 0),
96	CHECK (email IS NULL OR email ~* '^[^@]+@[^@]+\.[^@]+$'),
97	CHECK (phone IS NULL OR phone ~ '^[0-9+()-]{6,20}$')
98
99	);
100
101	-- BUSINESS MANAGER
102	CREATE TABLE business_manager (
103	business_manager_id SERIAL NOT NULL,
104	business_id INT NOT NULL DEFAULT 1,
105	manager_id INT NOT NULL DEFAULT 1,
106	assigned_at TIMESTAMP(7),
107	valid_to DATE,
108
109	CONSTRAINT pk_business_manager PRIMARY KEY (business_manager_id),
110	CONSTRAINT uq_business_manager_business_manager UNIQUE (business_id, manager_id),
111
112	CONSTRAINT fk_business_manager_business
113	FOREIGN KEY (business_id) REFERENCES business(business_id)
114	ON DELETE SET DEFAULT ON UPDATE CASCADE,
115
116	CONSTRAINT fk_business_manager_manager
117	FOREIGN KEY (manager_id) REFERENCES manager(manager_id)
118	ON DELETE SET DEFAULT ON UPDATE CASCADE
119	);
120
121	-- BUSINESS EMPLOYEE
122	CREATE TABLE business_employee (
123	business_employee_id SERIAL NOT NULL,
124	business_id INT NOT NULL DEFAULT 1,
125	employee_id INT NOT NULL DEFAULT 1,
126	date_start DATE,
127	date_finish DATE,
128
129	CONSTRAINT pk_business_employee PRIMARY KEY (business_employee_id),
130	CONSTRAINT uq_business_employee_business_employee UNIQUE (business_id, employee_id),
131
132	CHECK (date_finish IS NULL OR date_finish > date_start),
133
134	CONSTRAINT fk_business_employee_business
135	FOREIGN KEY (business_id) REFERENCES business(business_id)
136	ON DELETE SET DEFAULT ON UPDATE CASCADE,
137
138	CONSTRAINT fk_business_employee_employee
139	FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
140	ON DELETE SET DEFAULT ON UPDATE CASCADE
141	);
142
143	-- MANAGER EMPLOYEE BUSINESS
144	CREATE TABLE manager_employee_business (
145	meb_id SERIAL NOT NULL,
146	manager_id INT NOT NULL DEFAULT 1,
147	employee_id INT NOT NULL DEFAULT 1,
148	business_id INT NOT NULL DEFAULT 1,
149	date_start TIMESTAMP(7),
150	date_finish DATE,
151
152	CONSTRAINT pk_manager_employee_business PRIMARY KEY (meb_id),
153	CONSTRAINT uq_manager_employee_business_triplet
154	UNIQUE (manager_id, employee_id, business_id),
155
156	CONSTRAINT fk_meb_manager
157	FOREIGN KEY (manager_id) REFERENCES manager(manager_id)
158	ON DELETE SET DEFAULT ON UPDATE CASCADE,
159
160	CONSTRAINT fk_meb_employee
161	FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
162	ON DELETE SET DEFAULT ON UPDATE CASCADE,
163
164	CONSTRAINT fk_meb_business
165	FOREIGN KEY (business_id) REFERENCES business(business_id)
166	ON DELETE SET DEFAULT ON UPDATE CASCADE
167	);
168
169	-- SPECIALTY
170	CREATE TABLE specialty (
171	specialty_id SERIAL NOT NULL,
172	name VARCHAR(100) NOT NULL,
173
174	CONSTRAINT pk_specialty PRIMARY KEY (specialty_id),
175	CONSTRAINT uq_specialty_name UNIQUE (name),
176	CHECK (length(name) > 0)
177	);
178
179	-- BUSINESS SPECIALTY
180	CREATE TABLE business_specialty (
181	business_specialty_id SERIAL NOT NULL,
182	business_id INT NOT NULL DEFAULT 1,
183	specialty_id INT NOT NULL DEFAULT 1,
184
185	CONSTRAINT pk_business_specialty PRIMARY KEY (business_specialty_id),
186	CONSTRAINT uq_business_specialty_business_specialty UNIQUE (business_id, specialty_id),
187
188	CONSTRAINT fk_business_specialty_business
189	FOREIGN KEY (business_id) REFERENCES business(business_id)
190	ON DELETE SET DEFAULT ON UPDATE CASCADE,
191
192	CONSTRAINT fk_business_specialty_specialty
193	FOREIGN KEY (specialty_id) REFERENCES specialty(specialty_id)
194	ON DELETE SET DEFAULT ON UPDATE CASCADE
195	);
196
197	-- EMPLOYEE BUSINESS SPECIALTY
198	CREATE TABLE employee_business_specialty (
199	ebs_id SERIAL NOT NULL,
200	employee_id INT NOT NULL DEFAULT 1,
201	business_id INT NOT NULL DEFAULT 1,
202	specialty_id INT NOT NULL DEFAULT 1,
203
204	CONSTRAINT pk_employee_business_specialty PRIMARY KEY (ebs_id),
205	CONSTRAINT uq_employee_business_specialty_triplet
206	UNIQUE (employee_id, business_id, specialty_id),
207
208	CONSTRAINT fk_ebs_employee
209	FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
210	ON DELETE SET DEFAULT ON UPDATE CASCADE,
211
212	CONSTRAINT fk_ebs_business
213	FOREIGN KEY (business_id) REFERENCES business(business_id)
214	ON DELETE SET DEFAULT ON UPDATE CASCADE,
215
216	CONSTRAINT fk_ebs_specialty
217	FOREIGN KEY (specialty_id) REFERENCES specialty(specialty_id)
218	ON DELETE SET DEFAULT ON UPDATE CASCADE
219	);
220
221	-- SERVICE CATEGORY
222	CREATE TABLE service_category (
223	service_category_id SERIAL NOT NULL,
224	name VARCHAR(100),
225	parent_category_id INT,
226
227	CONSTRAINT pk_service_category PRIMARY KEY (service_category_id),
228
229	CHECK (parent_category_id IS NULL OR parent_category_id <> service_category_id),
230
231	CONSTRAINT fk_service_category_parent
232	FOREIGN KEY (parent_category_id)
233	REFERENCES service_category(service_category_id)
234	ON DELETE SET DEFAULT ON UPDATE CASCADE
235	);
236
237	-- SERVICE
238	CREATE TABLE service (
239	service_id SERIAL NOT NULL,
240	category_id INT DEFAULT 1,
241	name VARCHAR(100),
242	description TEXT,
243
244	CONSTRAINT pk_service PRIMARY KEY (service_id),
245
246	CHECK (length(name) > 0),
247
248	CONSTRAINT fk_service_category
249	FOREIGN KEY (category_id) REFERENCES service_category(service_category_id)
250	ON DELETE SET DEFAULT ON UPDATE CASCADE
251	);
252
253	-- BUSINESS SERVICE
254	CREATE TABLE business_service (
255	business_service_id SERIAL NOT NULL,
256	business_id INT NOT NULL DEFAULT 1,
257	service_id INT NOT NULL DEFAULT 1,
258	price NUMERIC(10,2),
259	duration_minutes INT,
260	is_active BOOLEAN NOT NULL DEFAULT TRUE,
261
262	CONSTRAINT pk_business_service PRIMARY KEY (business_service_id),
263	CONSTRAINT uq_business_service_business_service UNIQUE (business_id, service_id),
264
265	CHECK (price >= 0),
266	CHECK (duration_minutes IS NULL OR duration_minutes > 0),
267
268	CONSTRAINT fk_business_service_business
269	FOREIGN KEY (business_id) REFERENCES business(business_id)
270	ON DELETE SET DEFAULT ON UPDATE CASCADE,
271
272	CONSTRAINT fk_business_service_service
273	FOREIGN KEY (service_id) REFERENCES service(service_id)
274	ON DELETE SET DEFAULT ON UPDATE CASCADE
275	);
276
277	-- EMPLOYEE SERVICE
278	CREATE TABLE employee_service (
279	employee_service_id SERIAL NOT NULL,
280	employee_id INT NOT NULL DEFAULT 1,
281	service_id INT NOT NULL DEFAULT 1,
282
283	CONSTRAINT pk_employee_service PRIMARY KEY (employee_service_id),
284	CONSTRAINT uq_employee_service_employee_service UNIQUE (employee_id, service_id),
285
286	CONSTRAINT fk_employee_service_employee
287	FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
288	ON DELETE SET DEFAULT ON UPDATE CASCADE,
289
290	CONSTRAINT fk_employee_service_service
291	FOREIGN KEY (service_id) REFERENCES service(service_id)
292	ON DELETE SET DEFAULT ON UPDATE CASCADE
293	);
294
295	-- BUSINESS LOCATION
296	CREATE TABLE business_location (
297	location_id SERIAL NOT NULL,
298	business_id INT NOT NULL DEFAULT 1,
299	address VARCHAR(255),
300	city VARCHAR(100),
301	phone VARCHAR(20),
302
303	CONSTRAINT pk_business_location PRIMARY KEY (location_id),
304
305	CHECK (phone IS NULL OR phone ~ '^[0-9+()-]{6,20}$'),
306
307	CONSTRAINT fk_business_location_business
308	FOREIGN KEY (business_id) REFERENCES business(business_id)
309	ON DELETE SET DEFAULT ON UPDATE CASCADE
310	);
311
312	-- BUSINESS HOUR
313	CREATE TABLE business_hour (
314	business_hours_id SERIAL NOT NULL,
315	business_id INT NOT NULL DEFAULT 1,
316	day_of_week VARCHAR(15),
317	open_time TIME,
318	close_time TIME,
319	is_open BOOLEAN NOT NULL DEFAULT TRUE,
320
321	CONSTRAINT pk_business_hour PRIMARY KEY (business_hours_id),
322
323	CHECK (open_time < close_time),
324
325	CONSTRAINT fk_business_hour_business
326	FOREIGN KEY (business_id) REFERENCES business(business_id)
327	ON DELETE SET DEFAULT ON UPDATE CASCADE
328	);
329
330	-- WORKING SCHEDULE
331	CREATE TABLE working_schedule (
332	schedule_id SERIAL NOT NULL,
333	employee_id INT NOT NULL DEFAULT 1,
334	business_id INT NOT NULL DEFAULT 1,
335	business_hours_id INT DEFAULT 1,
336	day_of_week VARCHAR(15),
337	start_time TIME,
338	end_time TIME,
339	is_working BOOLEAN NOT NULL DEFAULT TRUE,
340
341	CONSTRAINT pk_working_schedule PRIMARY KEY (schedule_id),
342
343	CHECK (start_time < end_time),
344
345	CONSTRAINT fk_working_schedule_employee
346	FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
347	ON DELETE SET DEFAULT ON UPDATE CASCADE,
348
349	CONSTRAINT fk_working_schedule_business
350	FOREIGN KEY (business_id) REFERENCES business(business_id)
351	ON DELETE SET DEFAULT ON UPDATE CASCADE,
352
353	CONSTRAINT fk_working_schedule_business_hour
354	FOREIGN KEY (business_hours_id) REFERENCES business_hour(business_hours_id)
355	ON DELETE SET DEFAULT ON UPDATE CASCADE
356	);
357
358	-- TIME SLOT
359	CREATE TABLE time_slot (
360	slot_id SERIAL NOT NULL,
361	employee_id INT NOT NULL DEFAULT 1,
362	business_id INT NOT NULL DEFAULT 1,
363	"date" DATE,
364	start_time TIME,
365	end_time TIME,
366	is_available BOOLEAN NOT NULL DEFAULT TRUE,
367
368	CONSTRAINT pk_time_slot PRIMARY KEY (slot_id),
369
370	CHECK (start_time < end_time),
371
372	CONSTRAINT fk_time_slot_employee
373	FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
374	ON DELETE SET DEFAULT ON UPDATE CASCADE,
375
376	CONSTRAINT fk_time_slot_business
377	FOREIGN KEY (business_id) REFERENCES business(business_id)
378	ON DELETE SET DEFAULT ON UPDATE CASCADE
379	);
380
381	-- APPOINTMENT
382	CREATE TABLE appointment (
383	appointment_id SERIAL NOT NULL,
384	customer_id INT NOT NULL DEFAULT 1,
385	employee_id INT NOT NULL DEFAULT 1,
386	business_id INT NOT NULL DEFAULT 1,
387	service_id INT NOT NULL DEFAULT 1,
388	slot_id INT NOT NULL DEFAULT 1,
389	status VARCHAR(50),
390	created_at TIMESTAMP(7),
391
392	CONSTRAINT pk_appointment PRIMARY KEY (appointment_id),
393
394	CHECK (status IN ('pending','confirmed','cancelled','completed')),
395
396	CONSTRAINT fk_appointment_customer
397	FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
398	ON DELETE SET DEFAULT ON UPDATE CASCADE,
399
400	CONSTRAINT fk_appointment_employee
401	FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
402	ON DELETE SET DEFAULT ON UPDATE CASCADE,
403
404	CONSTRAINT fk_appointment_business
405	FOREIGN KEY (business_id) REFERENCES business(business_id)
406	ON DELETE SET DEFAULT ON UPDATE CASCADE,
407
408	CONSTRAINT fk_appointment_service
409	FOREIGN KEY (service_id) REFERENCES service(service_id)
410	ON DELETE SET DEFAULT ON UPDATE CASCADE,
411
412	CONSTRAINT fk_appointment_slot
413	FOREIGN KEY (slot_id) REFERENCES time_slot(slot_id)
414	ON DELETE SET DEFAULT ON UPDATE CASCADE
415	);
416
417	-- RESCHEDULE REQUEST
418	CREATE TABLE reschedule_request (
419	request_id SERIAL NOT NULL,
420	appointment_id INT NOT NULL DEFAULT 1,
421	old_slot_id INT NOT NULL DEFAULT 1,
422	new_slot_id INT NOT NULL DEFAULT 1,
423	manager_id INT NOT NULL DEFAULT 1,
424	employee_id INT,
425	status VARCHAR(50),
426	reason TEXT,
427	created_at TIMESTAMP(7),
428
429	CONSTRAINT pk_reschedule_request PRIMARY KEY (request_id),
430
431	CHECK (old_slot_id <> new_slot_id),
432
433	CONSTRAINT fk_reschedule_request_appointment
434	FOREIGN KEY (appointment_id) REFERENCES appointment(appointment_id)
435	ON DELETE SET DEFAULT ON UPDATE CASCADE,
436
437	CONSTRAINT fk_reschedule_request_old_slot
438	FOREIGN KEY (old_slot_id) REFERENCES time_slot(slot_id)
439	ON DELETE SET DEFAULT ON UPDATE CASCADE,
440
441	CONSTRAINT fk_reschedule_request_new_slot
442	FOREIGN KEY (new_slot_id) REFERENCES time_slot(slot_id)
443	ON DELETE SET DEFAULT ON UPDATE CASCADE,
444
445	CONSTRAINT fk_reschedule_request_manager
446	FOREIGN KEY (manager_id) REFERENCES manager(manager_id)
447	ON DELETE SET DEFAULT ON UPDATE CASCADE,
448
449	CONSTRAINT fk_reschedule_request_employee
450	FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
451	ON DELETE SET DEFAULT ON UPDATE CASCADE
452	);
453
454	-- REVIEW
455	CREATE TABLE review (
456	review_id SERIAL NOT NULL,
457	appointment_id INT NOT NULL DEFAULT 1,
458	customer_id INT,
459	employee_id INT,
460	manager_id INT NOT NULL DEFAULT 1,
461	business_id INT,
462	rating INT,
463	comment TEXT,
464	created_at TIMESTAMP(7),
465
466	CONSTRAINT pk_review PRIMARY KEY (review_id),
467
468	CHECK (rating BETWEEN 1 AND 5),
469
470	CONSTRAINT fk_review_appointment
471	FOREIGN KEY (appointment_id) REFERENCES appointment(appointment_id)
472	ON DELETE SET DEFAULT ON UPDATE CASCADE,
473
474	CONSTRAINT fk_review_customer
475	FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
476	ON DELETE SET DEFAULT ON UPDATE CASCADE,
477
478	CONSTRAINT fk_review_employee
479	FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
480	ON DELETE SET DEFAULT ON UPDATE CASCADE,
481
482	CONSTRAINT fk_review_manager
483	FOREIGN KEY (manager_id) REFERENCES manager(manager_id)
484	ON DELETE SET DEFAULT ON UPDATE CASCADE,
485
486	CONSTRAINT fk_review_business
487	FOREIGN KEY (business_id) REFERENCES business(business_id)
488	ON DELETE SET DEFAULT ON UPDATE CASCADE
489	);
490
491	-- CANCELLATION
492	CREATE TABLE cancellation (
493	cancellation_id SERIAL NOT NULL,
494	appointment_id INT NOT NULL DEFAULT 1,
495	cancelled_by VARCHAR(50),
496	reason TEXT,
497	refund_amount NUMERIC(10,2),
498	created_at TIMESTAMP(7),
499	employee_id INT NOT NULL DEFAULT 1,
500
501	CONSTRAINT pk_cancellation PRIMARY KEY (cancellation_id),
502	CONSTRAINT uq_cancellation_appointment UNIQUE (appointment_id),
503
504	CHECK (refund_amount IS NULL OR refund_amount >= 0),
505
506	CONSTRAINT fk_cancellation_appointment
507	FOREIGN KEY (appointment_id) REFERENCES appointment(appointment_id)
508	ON DELETE SET DEFAULT ON UPDATE CASCADE,
509
510	CONSTRAINT fk_cancellation_employee
511	FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
512	ON DELETE SET DEFAULT ON UPDATE CASCADE
513	);
514
515	-- GALLERY ITEM
516	CREATE TABLE gallery_item (
517	gallery_item_id SERIAL NOT NULL,
518	business_id INT NOT NULL DEFAULT 1,
519	employee_id INT,
520	image_url VARCHAR(255),
521	description TEXT,
522	uploaded_at TIMESTAMP(7),
523
524	CONSTRAINT pk_gallery_item PRIMARY KEY (gallery_item_id),
525
526	CHECK (image_url IS NULL OR image_url ~* '^https?://'),
527
528	CONSTRAINT fk_gallery_item_business
529	FOREIGN KEY (business_id) REFERENCES business(business_id)
530	ON DELETE SET DEFAULT ON UPDATE CASCADE,
531
532	CONSTRAINT fk_gallery_item_employee
533	FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
534	ON DELETE SET DEFAULT ON UPDATE CASCADE
535	);
536	-- ========================
537	-- BASE TABLES
538	-- ========================
539	INSERT INTO role VALUES (1, 'default');
540
541	INSERT INTO "user"
542	VALUES (1, 'default@system.local', 'Aa123456!', TRUE, CURRENT_TIMESTAMP);
543
544	-- ========================
545	-- CORE ENTITIES
546	-- ========================
547	INSERT INTO customer
548	VALUES (1, 1, 'Default', 'Customer', '+38970000000', CURRENT_TIMESTAMP);
549
550	INSERT INTO employee
551	VALUES (1, 1, 'Default', 'Employee', CURRENT_DATE, NULL, TRUE, CURRENT_TIMESTAMP);
552
553	INSERT INTO manager
554	VALUES (1, 1, CURRENT_TIMESTAMP);
555
556	INSERT INTO business
557	VALUES (1, 'Default Business', 'Default description', '+38970000000', 'business@mail.com', CURRENT_TIMESTAMP);
558
559	-- ========================
560	-- SPECIALTY
561	-- ========================
562	INSERT INTO specialty
563	VALUES (1, 'Default Specialty');
564
565	INSERT INTO business_specialty
566	VALUES (1, 1, 1);
567
568	INSERT INTO employee_business_specialty
569	VALUES (1, 1, 1, 1);
570
571	-- ========================
572	-- USER ROLE
573	-- ========================
574	INSERT INTO user_role
575	VALUES (1, 1, 1);
576
577	-- ========================
578	-- RELATION TABLES
579	-- ========================
580	INSERT INTO business_manager
581	VALUES (1, 1, 1, CURRENT_TIMESTAMP, NULL);
582
583	INSERT INTO business_employee
584	VALUES (1, 1, 1, CURRENT_DATE, NULL);
585
586	INSERT INTO manager_employee_business
587	VALUES (1, 1, 1, 1, CURRENT_TIMESTAMP, NULL);
588
589	-- ========================
590	-- SERVICES
591	-- ========================
592	INSERT INTO service_category
593	VALUES (1, 'Default Category', NULL);
594
595	INSERT INTO service
596	VALUES (1, 1, 'Default Service', 'Default description');
597
598	INSERT INTO business_service
599	VALUES (1, 1, 1, 10.00, 30, TRUE);
600
601	INSERT INTO employee_service
602	VALUES (1, 1, 1);
603
604	-- ========================
605	-- LOCATION + HOURS
606	-- ========================
607	INSERT INTO business_location
608	VALUES (1, 1, 'Main Street 1', 'Skopje', '+38970000000');
609
610	INSERT INTO business_hour
611	VALUES (1, 1, 'Mon', '08:00', '17:00', TRUE);
612
613	-- ========================
614	-- WORKING SCHEDULE
615	-- ========================
616	INSERT INTO working_schedule
617	VALUES (1, 1, 1, 1, 'Mon', '08:00', '17:00', TRUE);
618
619	-- ========================
620	-- TIME SLOTS (2 REQUIRED)
621	-- ========================
622	INSERT INTO time_slot
623	VALUES (1, 1, 1, CURRENT_DATE, '10:00', '10:30', TRUE);
624
625	INSERT INTO time_slot
626	VALUES (2, 1, 1, CURRENT_DATE, '11:00', '11:30', TRUE);
627
628	-- ========================
629	-- APPOINTMENT
630	-- ========================
631	INSERT INTO appointment
632	VALUES (1, 1, 1, 1, 1, 1, 'confirmed', CURRENT_TIMESTAMP);
633
634	-- ========================
635	-- RESCHEDULE (FIXED)
636	-- ========================
637	INSERT INTO reschedule_request
638	VALUES (1, 1, 1, 2, 1, 1, 'pending', 'Change time', CURRENT_TIMESTAMP);
639
640	-- ========================
641	-- REVIEW
642	-- ========================
643	INSERT INTO review
644	VALUES (1, 1, 1, 1, 1, 1, 5, 'Great service', CURRENT_TIMESTAMP);
645
646	-- ========================
647	-- CANCELLATION
648	-- ========================
649	INSERT INTO cancellation
650	VALUES (1, 1, 'employee', 'No reason', 0.00, CURRENT_TIMESTAMP, 1);
651
652	-- ========================
653	-- GALLERY
654	-- ========================
655	INSERT INTO gallery_item
656	VALUES (1, 1, 1, 'https://example.com/image.jpg', 'Default image', CURRENT_TIMESTAMP);
657
658	ALTER SEQUENCE role_role_id_seq RESTART WITH 2;
659	ALTER SEQUENCE user_user_id_seq RESTART WITH 2;
660	ALTER SEQUENCE user_role_user_role_id_seq RESTART WITH 2;
661	ALTER SEQUENCE customer_customer_id_seq RESTART WITH 2;
662	ALTER SEQUENCE employee_employee_id_seq RESTART WITH 2;
663	ALTER SEQUENCE manager_manager_id_seq RESTART WITH 2;
664	ALTER SEQUENCE business_business_id_seq RESTART WITH 2;
665
666	ALTER SEQUENCE specialty_specialty_id_seq RESTART WITH 2;
667	ALTER SEQUENCE business_specialty_business_specialty_id_seq RESTART WITH 2;
668	ALTER SEQUENCE employee_business_specialty_ebs_id_seq RESTART WITH 2;
669
670	ALTER SEQUENCE business_manager_business_manager_id_seq RESTART WITH 2;
671	ALTER SEQUENCE business_employee_business_employee_id_seq RESTART WITH 2;
672	ALTER SEQUENCE manager_employee_business_meb_id_seq RESTART WITH 2;
673
674	ALTER SEQUENCE service_category_service_category_id_seq RESTART WITH 2;
675	ALTER SEQUENCE service_service_id_seq RESTART WITH 2;
676	ALTER SEQUENCE business_service_business_service_id_seq RESTART WITH 2;
677	ALTER SEQUENCE employee_service_employee_service_id_seq RESTART WITH 2;
678
679	ALTER SEQUENCE business_location_location_id_seq RESTART WITH 2;
680	ALTER SEQUENCE business_hour_business_hours_id_seq RESTART WITH 2;
681	ALTER SEQUENCE working_schedule_schedule_id_seq RESTART WITH 2;
682
683	-- IMPORTANT (2 rows inserted already)
684	ALTER SEQUENCE time_slot_slot_id_seq RESTART WITH 3;
685
686	ALTER SEQUENCE appointment_appointment_id_seq RESTART WITH 2;
687	ALTER SEQUENCE reschedule_request_request_id_seq RESTART WITH 2;
688	ALTER SEQUENCE review_review_id_seq RESTART WITH 2;
689	ALTER SEQUENCE cancellation_cancellation_id_seq RESTART WITH 2;
690	ALTER SEQUENCE gallery_item_gallery_item_id_seq RESTART WITH 2;

Download in other formats:

Original Format