CREATE TABLE IF NOT EXISTS `ngis_lab_test` (
`id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
`lab_test_id` SMALLINT(6) NOT NULL,
`ngis_test_code` VARCHAR(6) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `lab_test_ngis_code` (`ngis_test_code`, `lab_test_id`),
INDEX `lab_test_id` (`lab_test_id`),
CONSTRAINT `ngis_lab_test_ibfk_1` FOREIGN KEY (`lab_test_id`)
REFERENCES `lab_tests` (`id`)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `ngis_clinical_indications` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`code` char(4) NOT NULL,
`category` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `indication_id` (`code`),
UNIQUE KEY `category` (`category`)
) ENGINE=InnoDB;
INSERT INTO `ngis_clinical_indications` (`id`, `code`, `category`) VALUES
(1, 'M93', 'Lymphoma NOS'),
(2, 'M95', 'B-cell non-Hodgkin\'s lymphoma'),
(3, 'M96', 'Burkitt lymphoma'),
(4, 'M97', 'Burkitt like lymphoma with 11q abnormalities'),
(5, 'M98', 'Large B-cell like lymphoma with IRF4 rearrangement'),
(6, 'M99', 'High grade lymphoma'),
(7, 'M100', 'Primary mediastinal B-cell lymphoma'),
(8, 'M101', 'ALK positive large B-cell lymphoma'),
(9, 'M102', 'Mantle cell lymphoma'),
(10, 'M103', 'Follicular lymphoma'),
(11, 'M104', 'Lymphoplasmacytic lymphoma / Waldenstrom\'s macroglobulinaemia'),
(12, 'M105', 'IgM MGUS'),
(13, 'M106', 'Intra-ocular lymphoma'),
(14, 'M107', 'MALT lymphoma'),
(15, 'M108', 'Hairy cell leukaemia'),
(16, 'M109', 'Hairy cell leukaemia - VH4-34 variant'),
(17, 'M110', 'Paediatric type follicular lymphoma'),
(18, 'M111', 'T-cell non-Hodgkin\'s lymphoma'),
(19, 'M112', 'ALK +ve anaplastic large cell lymphoma'),
(20, 'M113', 'T-cell prolymphocytic leukaemia'),
(21, 'M114', 'Large granular lymphocyte leukaemia'),
(22, 'M115', 'NK-cell / gamma-delta T-cell lymphoma'),
(23, 'M116', 'Hepatosplenic T-cell lymphoma'),
(24, 'M89', 'Acute leukaemia other'),
(25, 'M80', 'Acute myeloid leukaemia'),
(26, 'M84', 'Chronic myeloid leukaemia '),
(27, 'M94', 'Chronic lymphocytic leukaemia'),
(28, 'M83', 'Aplastic anaemia'),
(29, 'M118', 'Stem cell transplantation'),
(30, 'M117', 'Histiocytosis'),
(31, 'M91', 'Acute lymphoblastic leukaemia'),
(32, 'M92', 'Plasma cell neoplasm'),
(33, 'M85', 'Myeloproliferative neoplasm '),
(34, 'M81', 'Transient abnormal myelopoiesis'),
(35, 'M86', 'Systemic mastocytosis'),
(36, 'M82', 'Myelodysplasia'),
(37, 'M88', 'Juvenile myelomonocytic leukaemia'),
(38, 'M90', 'Blastic plasmacytoid dendritic cell neoplasm'),
(39, 'M87', 'Chronic neutrophilic leukaemia');
ALTER TABLE `diagnoses`
ADD COLUMN `ngis_indication_id` SMALLINT(6) NULL DEFAULT NULL
AFTER `diagnostic_category_id`;
ALTER TABLE `diagnoses`
ADD CONSTRAINT `diagnoses_ibfk_2` FOREIGN KEY (`ngis_indication_id`)
REFERENCES `ngis_clinical_indications` (`id`);
