-- ----------------------------------------
-- Create tables for Automated tcode tests
-- ----------------------------------------

CREATE TABLE IF NOT EXISTS att_test_run_status (
	id				INT(1) NOT NULL,
    status			VARCHAR(12),
    PRIMARY KEY (id)
);
INSERT IGNORE INTO att_test_run_status (id, status) VALUES
	(1, "Created"),
	(2, "Scheduled"),
	(3, "Running"),
	(4, "Completed"),
	(5, "Stopping"),
	(6, "Stopped"),
	(7, "Failed")
	ON DUPLICATE KEY UPDATE status = status;

CREATE TABLE IF NOT EXISTS att_test_run (
	id				INT NOT NULL AUTO_INCREMENT,
	project			VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	name			VARCHAR(32),
	timestamp		TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	status			INT(1) NOT NULL DEFAULT 0,
	sap_jobname		VARCHAR(32) NULL DEFAULT NULL,
	sap_jobcount	VARCHAR(8) NULL DEFAULT NULL,
	PRIMARY KEY (id),
    
	CONSTRAINT fk_att_test_run_project
		FOREIGN KEY (project)
		REFERENCES cehior_auth_proj_header(proj_name),

	CONSTRAINT fk_att_test_run_status
		FOREIGN KEY (status)
		REFERENCES att_test_run_status(id)
);
CREATE TABLE IF NOT EXISTS att_test_run_user (
	test_run_id		INT NOT NULL,
	username		VARCHAR(12) NOT NULL,
	PRIMARY KEY (test_run_id, username),
    
	CONSTRAINT fk_att_test_run_user_test_run
		FOREIGN KEY (test_run_id)
		REFERENCES att_test_run(id)
		ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS att_test_run_tcode (
	id				INT NOT NULL AUTO_INCREMENT,
	test_run_id		INT NOT NULL,
	username		VARCHAR(12) NOT NULL,
	tcode			VARCHAR(40),
	timestamp		TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	result			ENUM('ok','error','auth_error'),
	error_msg		VARCHAR(255) NULL DEFAULT NULL,
	issue_id		INT NULL DEFAULT NULL,
	PRIMARY KEY (id),
    
	CONSTRAINT att_test_run_tcode_test_run
		FOREIGN KEY (test_run_id)
		REFERENCES att_test_run(id)
		ON DELETE CASCADE
);
