urX4V986

1CREATE TABLE IF NOT EXISTS schedules (
2    id 	        INTEGER 	 PRYMARY KEY AUTO_INCREMENT,
3	title 	    VARCHAR(100) NOT NULL,
4	description VARCHAR(250),
5	starts_at   DATETIME 	 NOT NULL,
6	ends_at     DATETIME 	 NOT NULL,
7	created_at  DATETIME 	 NOT NULL DEFAULT CURRENT_DATE,
8	updated_at  DATETIME,
9	product_id  INTEGER 	 NOT NULL,
10	room_id     INTEGER 	 NOT NULL,
11	user_id     INTEGER 	 NOT NULL,
12    UNIQUE KEY(starts_at, room_id),
13	CONSTRAINTS FOREIGN KEY product_id REFERENCES products (id),
14	CONSTRAINTS FOREIGN KEY	room_id REFERENCES rooms (id),
15	CONSTRAINTS FOREIGN KEY user_id REFERENCES users (id),
16	CONSTRAINTS CHECK_ENDS_AT(ends_at > starts_at)
17);
18
19CREATE OR REPLACE FUNCTION check_schedule(_starts_at DATETIME, _ends_at DATETIME, _room_id INT) RETURNS INTEGER
20    RETURN (SELECT id
21              FROM schedules
22             WHERE (_starts_at BETWEEN starts_at AND ends_at) OR
23                   (_ends_at BETWEEN starts_at AND ends_at)
24               AND room_id = _room_id)
25;
26
27ALTER TABLE schedules
28	ADD CONSTRAINTS CHECK_STARTS_AT CHECK(starts_at > CURRENT_DATE AND NOT check_schedule(starts_at, ends_at, room_id))
29;