SET storage_engine=InnoDB;

CREATE TABLE training_topics (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	active_from DATE DEFAULT NULL,
	active_to DATE DEFAULT NULL,
	valid_months INT DEFAULT NULL,
	sap_code VARCHAR(255)
);

CREATE TABLE training_topic_audit (
	id INT AUTO_INCREMENT PRIMARY KEY,
	topic_id INT,
	old_name VARCHAR(255),
	new_name VARCHAR(255),
	old_active_from DATE,
	new_active_from DATE,
	old_active_to DATE,
	new_active_to DATE,
	old_valid_months INT,
	new_valid_months INT,
	old_sap_code VARCHAR(255),
	new_sap_code VARCHAR(255),
	FOREIGN KEY fk_topic (topic_id) REFERENCES training_topics (id)
);

CREATE TABLE location_types (
	id INT AUTO_INCREMENT PRIMARY KEY,
	code VARCHAR(32) NOT NULL UNIQUE,
	name VARCHAR(255) NOT NULL
);

CREATE TABLE depots (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL
);

CREATE TABLE reports (
	id INT AUTO_INCREMENT PRIMARY KEY,
	when_requested TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	requester INT NOT NULL,
	FOREIGN KEY fk_requester (requester) REFERENCES users (service_number)
);

CREATE TABLE training_events (
	id INT AUTO_INCREMENT PRIMARY KEY,
	applicant_service_number INT NOT NULL,
	applicant_name VARCHAR(255) NOT NULL,
	applicant_email VARCHAR(255) NOT NULL,
	leader_service_number INT NOT NULL,
	leader_name VARCHAR(255) NOT NULL,
	leader_email VARCHAR(255) NOT NULL,
	training_date DATE NOT NULL,
	training_time TIME NOT NULL,
	training_topic_id INT NOT NULL,
	location_type_id INT NOT NULL,
	location_off_site VARCHAR(255) DEFAULT NULL,
	location_depot_id INT DEFAULT NULL,
	training_valid_until DATE DEFAULT NULL,
	report_id INT DEFAULT NULL,
	FOREIGN KEY fk_applicant (applicant_service_number) REFERENCES users (service_number),
	FOREIGN KEY fk_leader (leader_service_number) REFERENCES users (service_number),
	FOREIGN KEY fk_training_topic (training_topic_id) REFERENCES training_topics (id),
	FOREIGN KEY fk_location_type (location_type_id) REFERENCES location_types (id),
	FOREIGN KEY fk_location_depot (location_depot_id) REFERENCES depots (id),
	FOREIGN KEY fk_report (report_id) REFERENCES reports (id)
);

CREATE TABLE training_event_attendees (
	id INT AUTO_INCREMENT PRIMARY KEY,
	event_id INT NOT NULL,
	service_number INT,
	name VARCHAR(255),
	cost_centre INT,
	is_other TINYINT(1) DEFAULT 0,
	INDEX idx_service_number (service_number),
	FOREIGN KEY fk_event (event_id) REFERENCES training_events (id)
) ENGINE=InnoDB;
