CREATE TABLE users (
	service_number INT NOT NULL PRIMARY KEY,
	first_name VARCHAR(255),
	last_name VARCHAR(255),
	email_addr VARCHAR(128),
	password VARCHAR(64) DEFAULT NULL,
	INDEX idx_first_name (first_name),
	INDEX idx_last_name (last_name)
) 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;