CREATE TABLE ee_groups (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE ee_subgroups (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE ws_rules (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL
) ENGINE=InnoDB COMMENT='Work Schedule Rules';

CREATE TABLE genders (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(32) NOT NULL
) ENGINE=InnoDB;
INSERT INTO genders (name) VALUES ('Male'), ('Female');

ALTER TABLE users ADD COLUMN ee_group_id TINYINT;
ALTER TABLE users ADD CONSTRAINT FOREIGN KEY fk_ee_group (ee_group_id) REFERENCES ee_groups(id);

ALTER TABLE users ADD COLUMN ee_subgroup_id TINYINT;
ALTER TABLE users ADD CONSTRAINT FOREIGN KEY fk_ee_subgroup (ee_subgroup_id) REFERENCES ee_subgroups(id);

ALTER TABLE users ADD COLUMN empl_percent DECIMAL(5, 2);

ALTER TABLE users ADD COLUMN gender_id TINYINT;
ALTER TABLE users ADD CONSTRAINT FOREIGN KEY fk_gender (gender_id) REFERENCES genders (id);

ALTER TABLE users ADD COLUMN grnid INT;

ALTER TABLE users ADD COLUMN last_day DATE DEFAULT NULL;

ALTER TABLE users ADD COLUMN org_unit_id INT;
ALTER TABLE users ADD CONSTRAINT FOREIGN KEY fk_org_unit (org_unit_id) REFERENCES org_units (id);

ALTER TABLE users ADD COLUMN org_unit_manager INT;
/*ALTER TABLE users ADD CONSTRAINT FOREIGN KEY fk_org_unit_manager (org_unit_manager) REFERENCES users (service_number);*/

ALTER TABLE users ADD COLUMN pager_id INT DEFAULT NULL;
ALTER TABLE users ADD COLUMN pager VARCHAR(16);
ALTER TABLE users ADD COLUMN payslip_location VARCHAR(255);
ALTER TABLE users ADD COLUMN phone_extension INT DEFAULT NULL;
ALTER TABLE users ADD COLUMN week_hours DECIMAL(5, 2);

ALTER TABLE users ADD COLUMN ws_rule_id INT DEFAULT NULL;
ALTER TABLE users ADD CONSTRAINT FOREIGN KEY ws_rule (ws_rule_id) REFERENCES ws_rules (id);
