CREATE TABLE employee_types (
	id TINYINT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(32)
) ENGINE=InnoDB;

INSERT INTO employee_types (id, name) VALUES
	(1, 'Ausgrid Staff'),
	(2, 'Contractor');

ALTER TABLE requests ADD COLUMN employee_type_id TINYINT NOT NULL;

UPDATE requests SET employee_type_id = 1;
ALTER TABLE requests ADD FOREIGN KEY idx_employee_type (employee_type_id) REFERENCES employee_types (id);

CREATE TABLE statuses (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	code VARCHAR(32) NOT NULL UNIQUE,
	name VARCHAR(255),
	requires_reason TINYINT(1) DEFAULT 0
) ENGINE=InnoDB;

INSERT INTO statuses (id, code, name, requires_reason) VALUES
	(1, 'pending', 'Pending', 0),
	(2, 'declined', 'Declined', 1),
	(3, 'endorsed_manager', 'Endorsed', 0),
	(4, 'endorsed_wcm', 'Endorsed', 0),
	(5, 'approved', 'Approved', 0);

ALTER TABLE requests ADD COLUMN status_id TINYINT DEFAULT 1;
ALTER TABLE requests ADD FOREIGN KEY idx_status (status_id) REFERENCES statuses (id);
ALTER TABLE requests ADD COLUMN status_reason VARCHAR(255) DEFAULT NULL;
