CREATE TABLE proficiency_types (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) UNIQUE,
	sap_code VARCHAR(255)
) ENGINE=InnoDB;

INSERT INTO proficiency_types (id, name, sap_code) VALUES
	(1, 'Competent', 'Competent'),
	(2, 'Completed', 'Completed');

ALTER TABLE training_topics ADD COLUMN proficiency_type_id INT NOT NULL;
UPDATE training_topics SET proficiency_type_id = 1;

ALTER TABLE training_topics ADD FOREIGN KEY fk_proficiency_type (proficiency_type_id) REFERENCES proficiency_types (id);

ALTER TABLE training_topic_audit ADD COLUMN old_proficiency_type_id INT;
ALTER TABLE training_topic_audit ADD COLUMN new_proficiency_type_id INT;
ALTER TABLE training_topic_audit ADD FOREIGN KEY fk_proficiency_type_old (old_proficiency_type_id) REFERENCES proficiency_types (id);
ALTER TABLE training_topic_audit ADD FOREIGN KEY fk_proficiency_type_new (new_proficiency_type_id) REFERENCES proficiency_types (id);
