-- USE `ncia`;

--
-- Table structure for table `patient`
--

DROP TABLE IF EXISTS `patient`;
CREATE TABLE `patient` (
  `patient_pk_id` bigint NOT NULL,
  `patient_id` varchar(64) character set latin1 collate latin1_bin default NULL,
  `patient_name` varchar(250) character set latin1 collate latin1_bin default NULL,
  `patient_birth_date` datetime default NULL,
  `patient_sex` varchar(16) character set latin1 collate latin1_bin default NULL,
  `ethnic_group` varchar(16) character set latin1 collate latin1_bin default NULL,
  `trial_dp_pk_id` bigint default NULL,
  `trial_subject_id` varchar(64) character set latin1 collate latin1_bin default NULL,
  `trial_subject_reading_id` varchar(64) character set latin1 collate latin1_bin default NULL,
  `trial_site_pk_id` bigint default NULL,
  `visibility` varchar(20) character set latin1 collate latin1_bin default NULL,
  `version` decimal(22,0) default '0',
  PRIMARY KEY  (`patient_pk_id`),
  KEY `patient_visibility_ind` (`visibility`),
  KEY `fk_trial_dp_pk_id` (`trial_dp_pk_id`),
  KEY `fk_trial_site_pk_id` (`trial_site_pk_id`),
  CONSTRAINT `fk_trial_dp_pk_id` FOREIGN KEY (`trial_dp_pk_id`) REFERENCES `trial_data_provenance` (`trial_dp_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_trial_site_pk_id` FOREIGN KEY (`trial_site_pk_id`) REFERENCES `trial_site` (`trial_site_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `study`
--

DROP TABLE IF EXISTS `study`;
CREATE TABLE `study` (
  `study_pk_id` bigint NOT NULL,
  `study_instance_uid` varchar(500) character set latin1 collate latin1_bin default NULL,
  `study_date` datetime default NULL,
  `study_time` varchar(16) character set latin1 collate latin1_bin default NULL,
  `study_desc` varchar(64) character set latin1 collate latin1_bin default NULL,
  `admitting_diagnoses_desc` varchar(64) character set latin1 collate latin1_bin default NULL,
  `admitting_diagnoses_code_seq` varchar(500) character set latin1 collate latin1_bin default NULL,
  `patient_pk_id` bigint default NULL,
  `study_id` varchar(16) character set latin1 collate latin1_bin default NULL,
  `trial_time_point_id` varchar(64) character set latin1 collate latin1_bin default NULL,
  `trial_time_point_desc` varchar(1024) character set latin1 collate latin1_bin default NULL,
  `patient_age` varchar(4) character set latin1 collate latin1_bin default NULL,
  `age_group` varchar(10) character set latin1 collate latin1_bin default NULL,
  `patient_size` decimal(22,0) default NULL,
  `patient_weight` decimal(22,0) default NULL,
  `occupation` varchar(16) character set latin1 collate latin1_bin default NULL,
  `additional_patient_history` varchar(4000) character set latin1 collate latin1_bin default NULL,
  `visibility` varchar(20) character set latin1 collate latin1_bin default NULL,
  `version` decimal(22,0) default '0',
  PRIMARY KEY  (`study_pk_id`),
  KEY `study_date_idx` (`study_date`),
  KEY `study_desc_idx` (`study_desc`),
  KEY `study_visibility_ind` (`visibility`),
  KEY `fk_patient_pk_id` (`patient_pk_id`),
  CONSTRAINT `fk_patient_pk_id` FOREIGN KEY (`patient_pk_id`) REFERENCES `patient` (`patient_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `general_series`
--

DROP TABLE IF EXISTS `general_series`;
CREATE TABLE `general_series` (
  `general_series_pk_id` bigint NOT NULL,
  `modality` varchar(16) character set latin1 collate latin1_bin default NULL,
  `series_instance_uid` varchar(64) character set latin1 collate latin1_bin default NULL,
  `series_laterality` varchar(16) character set latin1 collate latin1_bin default NULL,
  `series_date` datetime default NULL,
  `protocol_name` varchar(64) character set latin1 collate latin1_bin default NULL,
  `series_desc` varchar(64) character set latin1 collate latin1_bin default NULL,
  `body_part_examined` varchar(16) character set latin1 collate latin1_bin default NULL,
  `study_pk_id` bigint default NULL,
  `general_equipment_pk_id` bigint default NULL,
  `trial_protocol_id` varchar(64) character set latin1 collate latin1_bin default NULL,
  `trial_protocol_name` varchar(64) character set latin1 collate latin1_bin default NULL,
  `trial_site_name` varchar(64) character set latin1 collate latin1_bin default NULL,
  `study_date` datetime default NULL,
  `study_desc` varchar(64) character set latin1 collate latin1_bin default NULL,
  `admitting_diagnoses_desc` varchar(64) character set latin1 collate latin1_bin default NULL,
  `patient_age` varchar(4) character set latin1 collate latin1_bin default NULL,
  `patient_sex` varchar(16) character set latin1 collate latin1_bin default NULL,
  `patient_weight` decimal(22,0) default NULL,
  `age_group` varchar(10) character set latin1 collate latin1_bin default NULL,
  `patient_pk_id` bigint default NULL,
  `series_number` decimal(22,0) default NULL,
  `sync_frame_of_ref_uid` varchar(64) character set latin1 collate latin1_bin default NULL,
  `patient_id` varchar(64) character set latin1 collate latin1_bin default NULL,
  `frame_of_reference_uid` varchar(64) character set latin1 collate latin1_bin default NULL,
  `visibility` varchar(20) character set latin1 collate latin1_bin default NULL,
  `security_group` varchar(300) character set latin1 collate latin1_bin default NULL,
  `annotations_flag` varchar(5) character set latin1 collate latin1_bin default NULL,
  `version` decimal(22,0) default '0',
  PRIMARY KEY  (`general_series_pk_id`),
  KEY `body_part_examined_idx` (`body_part_examined`),
  KEY `general_series_sec_grp_idx` (`security_group`),
  KEY `general_series_site_idx` (`trial_site_name`),
  KEY `modality_idx` (`modality`),
  KEY `series_date_idx` (`series_date`),
  KEY `series_desc_idx` (`series_desc`),
  KEY `series_visibility_ind` (`visibility`),
  KEY `fk_gs_study_pk_id` (`study_pk_id`),
  KEY `fk_g_equipment_pk_id` (`general_equipment_pk_id`),
  CONSTRAINT `fk_gs_study_pk_id` FOREIGN KEY (`study_pk_id`) REFERENCES `study` (`study_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_g_equipment_pk_id` FOREIGN KEY (`general_equipment_pk_id`) REFERENCES `general_equipment` (`general_equipment_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `general_image`
--

DROP TABLE IF EXISTS `general_image`;
CREATE TABLE `general_image` (
  `image_pk_id` bigint NOT NULL,
  `instance_number` decimal(22,0) default NULL,
  `content_date` datetime default NULL,
  `content_time` varchar(16) character set latin1 collate latin1_bin default NULL,
  `image_type` varchar(16) character set latin1 collate latin1_bin default NULL,
  `acquisition_date` datetime default NULL,
  `acquisition_time` varchar(16) character set latin1 collate latin1_bin default NULL,
  `acquisition_number` decimal(22,0) default NULL,
  `lossy_image_compression` varchar(16) character set latin1 collate latin1_bin default NULL,
  `pixel_spacing` decimal(22,0) default NULL,
  `image_orientation_patient` varchar(200) character set latin1 collate latin1_bin default NULL,
  `image_position_patient` varchar(200) character set latin1 collate latin1_bin default NULL,
  `slice_thickness` decimal(22,0) default NULL,
  `slice_location` decimal(22,0) default NULL,
  `i_rows` decimal(22,0) default NULL,
  `i_columns` decimal(22,0) default NULL,
  `contrast_bolus_agent` varchar(64) character set latin1 collate latin1_bin default NULL,
  `contrast_bolus_route` varchar(64) character set latin1 collate latin1_bin default NULL,
  `sop_class_uid` varchar(64) character set latin1 collate latin1_bin default NULL,
  `sop_instance_uid` varchar(64) character set latin1 collate latin1_bin default NULL,
  `general_series_pk_id` bigint default NULL,
  `patient_position` varchar(16) character set latin1 collate latin1_bin default NULL,
  `source_to_detector_distance` decimal(22,0) default NULL,
  `source_subject_distance` decimal(22,0) default NULL,
  `focal_spot_size` decimal(22,0) default NULL,
  `storage_media_file_set_uid` varchar(64) character set latin1 collate latin1_bin default NULL,
  `mirc_doc_uri` varchar(2000) character set latin1 collate latin1_bin default NULL,
  `dicom_file_uri` varchar(2000) character set latin1 collate latin1_bin default NULL,
  `acquisition_datetime` varchar(50) character set latin1 collate latin1_bin default NULL,
  `image_comments` varchar(4000) character set latin1 collate latin1_bin default NULL,
  `image_receiving_timestamp` datetime default NULL,
  `curation_status` varchar(20) character set latin1 collate latin1_bin default NULL,
  `curation_timestamp` datetime default NULL,
  `visibility` varchar(20) character set latin1 collate latin1_bin default NULL,
  `annotation` varchar(20) character set latin1 collate latin1_bin default NULL,
  `submission_date` datetime default NULL,
  `dicom_size` decimal(22,0) default NULL,
  `image_laterality` varchar(16) character set latin1 collate latin1_bin default NULL,
  `trial_dp_pk_id` bigint default NULL,
  `patient_id` varchar(64) character set latin1 collate latin1_bin default NULL,
  `study_instance_uid` varchar(500) character set latin1 collate latin1_bin default NULL,
  `series_instance_uid` varchar(64) character set latin1 collate latin1_bin default NULL,
  `patient_pk_id` bigint default NULL,
  `study_pk_id` bigint default NULL,
  `project` varchar(200) character set latin1 collate latin1_bin default NULL,
  `version` decimal(22,0) default '0',
  `acquisition_matrix` decimal(22,0) default '0',
  `dx_data_collection_diameter` decimal(22,0) default '0',
  PRIMARY KEY  (`image_pk_id`),
  KEY `acquisition_matrix_idx` (`acquisition_matrix`),
  KEY `contrast_bolus_route_idx` (`contrast_bolus_route`),
  KEY `curation_t_indx` (`curation_timestamp`),
  KEY `dx_data_collection_diameter` (`dx_data_collection_diameter`),
  KEY `general_image_search` (`general_series_pk_id`,`image_pk_id`,`slice_thickness`,`contrast_bolus_agent`,`visibility`,`curation_timestamp`),
  KEY `gi_gs_ds_indx` (`general_series_pk_id`,`dicom_size`,`visibility`),
  KEY `gi_ppkid_indx` (`patient_pk_id`),
  KEY `gi_spkid_indx` (`study_pk_id`),
  KEY `gi_tdpkid_indx` (`trial_dp_pk_id`),
  KEY `image_fk_series_pk_id` (`general_series_pk_id`),
  KEY `image_sop_instance_uid` (`sop_instance_uid`),
  KEY `image_visibility_ind` (`visibility`),
  KEY `slice_thickness_idx` (`slice_thickness`),
  CONSTRAINT `fk_g_series_pk_id` FOREIGN KEY (`general_series_pk_id`) REFERENCES `general_series` (`general_series_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `ct_image`
--

DROP TABLE IF EXISTS `ct_image`;
CREATE TABLE `ct_image` (
  `kvp` decimal(22,0) default NULL,
  `scan_options` varchar(16) character set latin1 collate latin1_bin default NULL,
  `data_collection_diameter` decimal(22,0) default NULL,
  `reconstruction_diameter` decimal(22,0) default NULL,
  `gantry_detector_tilt` decimal(22,0) default NULL,
  `exposure_time` decimal(22,0) default NULL,
  `x_ray_tube_current` decimal(22,0) default NULL,
  `exposure` decimal(22,0) default NULL,
  `exposure_in_microas` decimal(22,0) default NULL,
  `convolution_kernel` varchar(16) character set latin1 collate latin1_bin default NULL,
  `revolution_time` decimal(22,0) default NULL,
  `single_collimation_width` decimal(22,0) default NULL,
  `total_collimation_width` decimal(22,0) default NULL,
  `table_speed` decimal(22,0) default NULL,
  `table_feed_per_rotation` decimal(22,0) default NULL,
  `ct_pitch_factor` decimal(22,0) default NULL,
  `anatomic_region_seq` varchar(500) character set latin1 collate latin1_bin default NULL,
  `image_pk_id` bigint NOT NULL,
  `ct_image_pk_id` bigint NOT NULL,
  `visibility` varchar(20) character set latin1 collate latin1_bin default NULL,
  PRIMARY KEY  (`ct_image_pk_id`),
  KEY `convolution_kernel_idx` (`convolution_kernel`),
  KEY `ct_image_image_pk_id_indx` (`image_pk_id`),
  KEY `kvp_idx` (`kvp`),
  KEY `visibility_ct_image` (`visibility`),
  CONSTRAINT `fk_image_pk_id` FOREIGN KEY (`image_pk_id`) REFERENCES `general_image` (`image_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



--
-- Table structure for table `annotation`
--

DROP TABLE IF EXISTS `annotation`;
CREATE TABLE `annotation` (
  `file_path` varchar(300) character set latin1 collate latin1_bin default NULL,
  `file_size` decimal(22,0) default NULL,
  `annotation_type` varchar(30) character set latin1 collate latin1_bin default NULL,
  `series_instance_uid` varchar(300) character set latin1 collate latin1_bin default NULL,
  `general_series_pk_id` bigint(20) default NULL,
  `annotations_pk_id` bigint(20) NOT NULL,
  `submission_date` datetime default NULL,
  PRIMARY KEY  (`annotations_pk_id`),
  KEY `fk_ann_gs_pk_id` (`general_series_pk_id`),
  CONSTRAINT `fk_ann_gs_pk_id` FOREIGN KEY (`general_series_pk_id`) REFERENCES `general_series` (`general_series_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `clinical_trial_protocol`
--

DROP TABLE IF EXISTS `clinical_trial_protocol`;
CREATE TABLE `clinical_trial_protocol` (
  `id` bigint NOT NULL,
  `protocol_id` varchar(255) character set latin1 collate latin1_bin NOT NULL,
  `protocol_name` varchar(1000) character set latin1 collate latin1_bin NOT NULL,
  `clinical_trial_sponsor_id` bigint NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_clin_trial_sponsor_id` (`clinical_trial_sponsor_id`),
  CONSTRAINT `fk_clin_trial_sponsor_id` FOREIGN KEY (`clinical_trial_sponsor_id`) REFERENCES `clinical_trial_sponsor` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `curation_data`
--

DROP TABLE IF EXISTS `curation_data`;
CREATE TABLE `curation_data` (
  `curation_data_pk_id` bigint NOT NULL,
  `cde_name` varchar(100) character set latin1 collate latin1_bin default NULL,
  `cde_value` varchar(4000) character set latin1 collate latin1_bin default NULL,
  `cde_public_id` varchar(50) character set latin1 collate latin1_bin default NULL,
  `cde_public_id_version` varchar(50) character set latin1 collate latin1_bin default NULL,
  `curation_timestamp` datetime default NULL,
  `user_id` bigint default NULL,
  `trial_name` varchar(100) character set latin1 collate latin1_bin default NULL,
  `patient_pk_id` bigint default NULL,
  `study_pk_id` bigint default NULL,
  `general_series_pk_id` bigint default NULL,
  `image_pk_id` bigint default NULL,
  `annotations_pk_id` bigint default NULL,
  PRIMARY KEY  (`curation_data_pk_id`),
  KEY `curation_data_idx` (`cde_public_id`,`cde_value`(500),`general_series_pk_id`),
  KEY `genimg_curdta_id_idx` (`image_pk_id`),
  KEY `annotations_pk_id_cur_data_fk` (`annotations_pk_id`),
  KEY `patient_pk_id_cur_data_fk` (`patient_pk_id`),
  KEY `series_pk_id_cur_data_fk` (`general_series_pk_id`),
  KEY `study_pk_id_cur_data_fk` (`study_pk_id`),
  CONSTRAINT `annotations_pk_id_cur_data_fk` FOREIGN KEY (`annotations_pk_id`) REFERENCES `annotation` (`annotations_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `image_pk_id_cur_data_fk` FOREIGN KEY (`image_pk_id`) REFERENCES `general_image` (`image_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `patient_pk_id_cur_data_fk` FOREIGN KEY (`patient_pk_id`) REFERENCES `patient` (`patient_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `series_pk_id_cur_data_fk` FOREIGN KEY (`general_series_pk_id`) REFERENCES `general_series` (`general_series_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `study_pk_id_cur_data_fk` FOREIGN KEY (`study_pk_id`) REFERENCES `study` (`study_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `doc_location`
--

DROP TABLE IF EXISTS `doc_location`;
CREATE TABLE `doc_location` (
  `doc_location_pk_id` bigint NOT NULL,
  `doc_directory` varchar(2000) character set latin1 collate latin1_bin default NULL,
  `doc_name` varchar(500) character set latin1 collate latin1_bin default NULL,
  `doc_author` varchar(100) character set latin1 collate latin1_bin default NULL,
  `doc_desc` varchar(2000) character set latin1 collate latin1_bin default NULL,
  `doc_type` varchar(100) character set latin1 collate latin1_bin default NULL,
  `title` varchar(100) character set latin1 collate latin1_bin default NULL,
  `study_instance_uid` varchar(500) character set latin1 collate latin1_bin default NULL,
  `study_pk_id` bigint default NULL,
  `query_xml_text` varchar(4000) character set latin1 collate latin1_bin default NULL,
  PRIMARY KEY  (`doc_location_pk_id`),
  KEY `fk_study_pk_id` (`study_pk_id`),
  CONSTRAINT `fk_study_pk_id` FOREIGN KEY (`study_pk_id`) REFERENCES `study` (`study_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `qa_status_history`
--

DROP TABLE IF EXISTS `qa_status_history`;
CREATE TABLE `qa_status_history` (
  `qa_status_history_pk_id` bigint NOT NULL,
  `general_image_pk_id` bigint default NULL,
  `user_id` bigint default NULL,
  `comment` varchar(4000) character set latin1 collate latin1_bin default NULL,
  `history_timestamp` datetime default NULL,
  `new_value` varchar(100) character set latin1 collate latin1_bin default NULL,
  `old_value` varchar(100) character set latin1 collate latin1_bin default NULL,
  PRIMARY KEY  (`qa_status_history_pk_id`),
  KEY `genimg_qasts_id_idx` (`general_image_pk_id`),
  KEY `fk_qa_user_pk_id` (`user_id`),
  CONSTRAINT `fk_qa_image_pk_id` FOREIGN KEY (`general_image_pk_id`) REFERENCES `general_image` (`image_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `query_history`
--

DROP TABLE IF EXISTS `query_history`;
CREATE TABLE `query_history` (
  `query_history_pk_id` bigint NOT NULL,
  `user_id` bigint default NULL,
  `query_execute_timestamp` datetime default NULL,
  `query_elapsed_time` decimal(22,0) default NULL,
  `saved_query_pk_id` bigint default NULL,
  PRIMARY KEY  (`query_history_pk_id`),
  KEY `query_history_user_idx` (`user_id`),
  KEY `query_hist_save_quer_idx` (`saved_query_pk_id`),
  CONSTRAINT `fk_s_query_pk_id` FOREIGN KEY (`saved_query_pk_id`) REFERENCES `saved_query` (`saved_query_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `query_history_attribute`
--

DROP TABLE IF EXISTS `query_history_attribute`;
CREATE TABLE `query_history_attribute` (
  `query_history_attribute_pk_id` bigint NOT NULL,
  `attribute_name` varchar(300) character set latin1 collate latin1_bin default NULL,
  `subattribute_name` varchar(300) character set latin1 collate latin1_bin default NULL,
  `attribute_value` varchar(300) character set latin1 collate latin1_bin default NULL,
  `query_history_pk_id` bigint default NULL,
  `instance_number` decimal(22,0) default NULL,
  PRIMARY KEY  (`query_history_attribute_pk_id`),
  KEY `attri_name_idx` (`attribute_name`),
  KEY `attri_value_idx` (`attribute_value`),
  KEY `query_history_attr_parent` (`query_history_pk_id`),
  CONSTRAINT `fk_q_h_pk_id` FOREIGN KEY (`query_history_pk_id`) REFERENCES `query_history` (`query_history_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `clinical_trial_subject`
--

DROP TABLE IF EXISTS `clinical_trial_subject`;
CREATE TABLE `clinical_trial_subject` (
  `id` bigint NOT NULL,
  `trial_subject_id` varchar(255) character set latin1 collate latin1_bin NOT NULL,
  `trial_subject_reading_id` varchar(255) character set latin1 collate latin1_bin NOT NULL,
  `clinical_trial_protocol_id` bigint NOT NULL,
  `patient_id` bigint NOT NULL,
  `trial_site_id` bigint NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_clinical_trial_protocol_id` (`clinical_trial_protocol_id`),
  KEY `fk_patient_id` (`patient_id`),
  KEY `fk_trial_site` (`trial_site_id`),
  CONSTRAINT `fk_clinical_trial_protocol_id` FOREIGN KEY (`clinical_trial_protocol_id`) REFERENCES `clinical_trial_protocol` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_patient_id` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`patient_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_trial_site` FOREIGN KEY (`trial_site_id`) REFERENCES `trial_site` (`trial_site_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

COMMIT;