CREATE TABLE users (
	service_number INT NOT NULL PRIMARY KEY,
	first_name VARCHAR(255),
	last_name VARCHAR(255),
	email_addr VARCHAR(128),
	pin VARCHAR(16) DEFAULT NULL,
	location VARCHAR(255),
	level VARCHAR(255),
	staff_type VARCHAR(255),
	division VARCHAR(255),
	branch VARCHAR(255),
	section VARCHAR(255),
	position VARCHAR(255),
	cost_centre INT DEFAULT NULL,
	phone VARCHAR(16) DEFAULT NULL,
	fax VARCHAR(16) DEFAULT NULL,
	mobile VARCHAR(16) DEFAULT NULL,
	mgmt_level TINYINT DEFAULT NULL,
	manager_service_number INT DEFAULT NULL,
	manager_mgmt_level TINYINT DEFAULT NULL,
	is_active TINYINT(1) DEFAULT 1,
	date_created TIMESTAMP NULL,
	date_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	INDEX idx_first_name (first_name),
	INDEX idx_last_name (last_name),
	INDEX idx_mgmt_level (mgmt_level),
	INDEX idx_manager_service_number (manager_service_number),
	INDEX idx_manager_mgmt_level (manager_mgmt_level)
) ENGINE=InnoDB;

CREATE TABLE login_codes (
	service_number INT NOT NULL,
	code VARCHAR(64) NOT NULL,
	valid_until TIMESTAMP NOT NULL,
	PRIMARY KEY (service_number, code),
	FOREIGN KEY fk_users_service_number (service_number) REFERENCES users (service_number) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE permission_roles (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255),
	description TEXT,
	UNIQUE INDEX idx_name (name)
) ENGINE=InnoDB;

CREATE TABLE user_roles (
	service_number INT NOT NULL,
	role_id INT NOT NULL,
	PRIMARY KEY (service_number, role_id),
	FOREIGN KEY fk_users_user_id (service_number) REFERENCES users (service_number) ON DELETE CASCADE,
	FOREIGN KEY fk_roles_role_id (role_id) REFERENCES permission_roles (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE permission_resources (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255),
	description TEXT,
	UNIQUE INDEX idx_name (name)
) ENGINE=InnoDB;

CREATE TABLE permission_rules (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255),
	role_id INT NOT NULL,
	resource_id INT NOT NULL,
	allowed BIT DEFAULT 1,
	INDEX idx_name (name),
	FOREIGN KEY fk_role_id (role_id) REFERENCES permission_roles (id) ON DELETE CASCADE,
	FOREIGN KEY fk_permission_resource_id (resource_id) REFERENCES permission_resources (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE delegations (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	delegator INT NOT NULL,
	delegatee INT NOT NULL,
	date_from DATE,
	date_to DATE,
	active BIT DEFAULT 1,
	FOREIGN KEY fk_users_delegator (delegator) REFERENCES users (service_number),
	FOREIGN KEY fk_users_delegatee (delegatee) REFERENCES users (service_number),
	INDEX idx_date_from (date_from),
	INDEX idx_date_to (date_to)
);