SET storage_engine=InnoDB;

CREATE TABLE applications (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	code VARCHAR(32) NOT NULL UNIQUE,
	short_code VARCHAR(8) NOT NULL UNIQUE,
	url VARCHAR(255) NOT NULL,
	is_open_to_all_employees TINYINT(1) DEFAULT 0,
	is_open_to_all_managers TINYINT(1) DEFAULT 0
);

CREATE TABLE application_user_rules (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	service_number INT NOT NULL,
	application_id INT NOT NULL,
	permission TINYINT(1) NOT NULL,
	UNIQUE KEY (service_number, application_id),
	FOREIGN KEY idx_service_number (service_number) REFERENCES users (service_number) ON DELETE CASCADE,
	FOREIGN KEY idx_application (application_id) REFERENCES applications (id) ON DELETE CASCADE
);
