SET storage_engine=InnoDB;

CREATE TABLE locations (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	code VARCHAR(16) DEFAULT NULL UNIQUE,
	name VARCHAR(255) NOT NULL,
	active TINYINT DEFAULT 1
);

CREATE TABLE groups (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	location_id INT NOT NULL,
	active TINYINT DEFAULT 1,
	FOREIGN KEY idx_location (location_id) REFERENCES locations(id)
);

CREATE TABLE rooms (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	group_id INT NOT NULL,
	active TINYINT DEFAULT 1,
	FOREIGN KEY idx_group (group_id) REFERENCES groups (id)
);

CREATE TABLE events (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(255) NOT NULL,
	room_id INT NOT NULL,
	event_date DATE NOT NULL,
	start_time TIME DEFAULT NULL,
	end_time TIME DEFAULT NULL,
	FOREIGN KEY idx_room (room_id) REFERENCES rooms (id)
);

CREATE TABLE user_locations (
	service_number INT NOT NULL,
	location_id INT NOT NULL,
	PRIMARY KEY (service_number, location_id),
	FOREIGN KEY idx_service_number (service_number) REFERENCES users (service_number),
	FOREIGN KEY idx_location_id (location_id) REFERENCES locations (id)
) ENGINE=InnoDB;
