USE `test`;
SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `test`.`lab_tests`;
CREATE TABLE `test`.`lab_tests` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `test_name` varchar(25) NOT NULL,
  `field_label` varchar(25) NOT NULL,
  `lab_section_id` smallint(6) NOT NULL DEFAULT '0',
  `test_type` enum('test','panel') DEFAULT NULL,
  `has_results` enum('yes','no') DEFAULT NULL,
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `label_section_type` (`lab_section_id`,`test_type`,`field_label`),
  UNIQUE KEY `name_section_type` (`lab_section_id`,`test_type`,`test_name`),
  KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB;
INSERT INTO `test`.`lab_tests` (`id`, `test_name`, `field_label`, `lab_section_id`, 
        `test_type`, `has_results`, `is_active`)
    SELECT `id`, `test_name`, `field_label`, `lab_section_id`, `test_type`, `has_results`, 
        `is_active` FROM genomics.lab_tests;


DROP TABLE IF EXISTS `test`.`lab_sections`;
CREATE TABLE `test`.`lab_sections` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `section_name` varchar(255) NOT NULL,
  `has_result_summary` enum('yes','no') NOT NULL DEFAULT 'no',
  `has_section_notes` enum('yes','no') NOT NULL DEFAULT 'no',
  `has_test_sign_out` enum('yes','no') NOT NULL DEFAULT 'no',
  `has_foreign_id` enum('yes','no') NOT NULL DEFAULT 'no',
  `has_results_import` enum('yes','no') NOT NULL DEFAULT 'no',
  `has_labels` enum('yes','no') NOT NULL DEFAULT 'no',
  `auto_expand` enum('yes','no') NOT NULL DEFAULT 'no',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `section_name` (`section_name`)
) ENGINE=InnoDB;
INSERT INTO `test`.`lab_sections` (`id`, `section_name`, `has_result_summary`, 
        `has_section_notes`, `has_test_sign_out`, `has_foreign_id`, `has_results_import`, `has_labels`, 
        `auto_expand`, `is_active`)
    SELECT `id`, `section_name`, `has_result_summary`, 
        `has_section_notes`, `has_test_sign_out`, `has_foreign_id`, `has_results_import`, `has_labels`, 
        `auto_expand`, `is_active`
    FROM genomics.lab_sections;


DROP TABLE IF EXISTS `test`.`lab_test_data_type`;
CREATE TABLE `test`.`lab_test_data_type` (
  `lab_test_id` smallint(6) NOT NULL DEFAULT '0',
  `data_type_id` smallint(6) NOT NULL DEFAULT '0',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`lab_test_id`),
  KEY `data_type_id` (`data_type_id`)
) ENGINE=InnoDB;
INSERT INTO `test`.`lab_test_data_type` (`lab_test_id`, `data_type_id`, `is_active`)
    SELECT `lab_test_id`, `data_type_id`, `is_active` FROM genomics.lab_test_data_type;


DROP TABLE IF EXISTS `test`.`lab_test_result_data_types`;
CREATE TABLE `test`.`lab_test_result_data_types` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(25) NOT NULL DEFAULT '',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB;
INSERT INTO `test`.`lab_test_result_data_types` (`id`, `description`, `is_active`) 
    SELECT `id`, `description`, `is_active` FROM genomics.lab_test_result_data_types;


DROP TABLE IF EXISTS `test`.`lab_test_result_options`;
CREATE TABLE `test`.`lab_test_result_options` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `data_type_id` smallint(6) NOT NULL DEFAULT '0',
  `value` varchar(30) NOT NULL DEFAULT '',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `data_type_value` (`data_type_id`,`value`)
) ENGINE=InnoDB;
INSERT INTO `test`.`lab_test_result_options` (`id`, `data_type_id`, `value`, `is_active`)
    SELECT `id`, `data_type_id`, `value`, `is_active` FROM genomics.lab_test_result_options;

DROP TABLE IF EXISTS `test`.`screen_lab_test`;
CREATE TABLE `test`.`screen_lab_test` (
	`screen_id` SMALLINT(6) NOT NULL DEFAULT '0',
	`lab_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
	PRIMARY KEY (`screen_id`, `lab_test_id`),
	INDEX `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `test`.`screens`;
CREATE TABLE `test`.`screens` (
	`id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
	`description` VARCHAR(50) NULL DEFAULT NULL,
	`category_id` SMALLINT(6) NOT NULL DEFAULT '0',
	`active` ENUM('yes','no') NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `category_description` (`category_id`, `description`)
) ENGINE=InnoDB;
INSERT INTO `test`.`screens` (`id`, `description`, `category_id`, `active`)
    SELECT `id`, `description`, `category_id`, `active` FROM genomics.screens;
    
DROP TABLE IF EXISTS `test`.`screen_category`;
CREATE TABLE `test`.`screen_category` (
	`id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`is_active` ENUM('yes','no') NOT NULL DEFAULT 'yes',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `test`.`screen_category` (`id`, `name`)
    SELECT `id`, `name` FROM genomics.screen_category;

SET FOREIGN_KEY_CHECKS=1;