-- Copyright [1999-2016] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

/**
@header patch_91_92_i.sql - Add table to store fastqc outcomes
@desc   Add table to store fastqc outcomes
*/

create table fastqc (
  fastqc_id    int(11) unsigned NOT NULL AUTO_INCREMENT,
  read_file_id int(14) unsigned NOT NULL,
  basic_statistics             enum('PASS','WARN','FAIL') default null,
  per_base_sequence_quality    enum('PASS','WARN','FAIL') default null,
  per_tile_sequence_quality    enum('PASS','WARN','FAIL') default null,
  per_sequence_quality_scores  enum('PASS','WARN','FAIL') default null,
  per_base_sequence_content    enum('PASS','WARN','FAIL') default null,
  per_sequence_gc_content      enum('PASS','WARN','FAIL') default null,
  per_base_n_content           enum('PASS','WARN','FAIL') default null,
  sequence_length_distribution enum('PASS','WARN','FAIL') default null,
  sequence_duplication_levels  enum('PASS','WARN','FAIL') default null,
  overrepresented_sequences    enum('PASS','WARN','FAIL') default null,
  adapter_content              enum('PASS','WARN','FAIL') default null,
  kmer_content                 enum('PASS','WARN','FAIL') default null,
  PRIMARY KEY (`fastqc_id`)
);

ALTER TABLE fastqc ADD CONSTRAINT read_file_id_unique UNIQUE (read_file_id);

-- insert into fastqc (
--   read_file_id,
--   basic_statistics,
--   per_base_sequence_quality,
--   per_tile_sequence_quality,
--   per_sequence_quality_scores,
--   per_base_sequence_content,
--   per_sequence_gc_content,
--   per_base_n_content,
--   sequence_length_distribution,
--   sequence_duplication_levels,
--   overrepresented_sequences,
--   adapter_content,
--   kmer_content
-- ) 
--   select 
--     distinct
--     read_file_fastqc.read_file_id,
--     basic_statistics.status,
--     per_base_sequence_quality.status,
--     per_tile_sequence_quality.status,
--     per_sequence_quality_scores.status,
--     per_base_sequence_content.status,
--     per_sequence_gc_content.status,
--     per_base_n_content.status,
--     sequence_length_distribution.status,
--     sequence_duplication_levels.status,
--     overrepresented_sequences.status,
--     adapter_content.status,
--     kmer_content.status
--   from 
--     read_file_fastqc
--     join read_file_fastqc as basic_statistics             using (read_file_id)
--     join read_file_fastqc as per_base_sequence_quality    using (read_file_id)
--     join read_file_fastqc as per_tile_sequence_quality    using (read_file_id)
--     join read_file_fastqc as per_sequence_quality_scores  using (read_file_id)
--     join read_file_fastqc as per_base_sequence_content    using (read_file_id)
--     join read_file_fastqc as per_sequence_gc_content      using (read_file_id)
--     join read_file_fastqc as per_base_n_content           using (read_file_id)
--     join read_file_fastqc as sequence_length_distribution using (read_file_id)
--     join read_file_fastqc as sequence_duplication_levels  using (read_file_id)
--     join read_file_fastqc as overrepresented_sequences    using (read_file_id)
--     join read_file_fastqc as adapter_content              using (read_file_id)
--     join read_file_fastqc as kmer_content                 using (read_file_id)
--   where
--     basic_statistics.title                  = "Basic Statistics"
--     and  per_base_sequence_quality.title    = "Per base sequence quality"
--     and  per_tile_sequence_quality.title    = "Per tile sequence quality"
--     and  per_sequence_quality_scores.title  = "Per sequence quality scores"
--     and  per_base_sequence_content.title    = "Per base sequence content"
--     and  per_sequence_gc_content.title      = "Per sequence GC content"
--     and  per_base_n_content.title           = "Per base N content"
--     and  sequence_length_distribution.title = "Sequence Length Distribution"
--     and  sequence_duplication_levels.title  = "Sequence Duplication Levels"
--     and  overrepresented_sequences.title    = "Overrepresented sequences"
--     and  adapter_content.title              = "Adapter Content"
--     and  kmer_content.title                 = "Kmer Content";

alter table fastqc add column run_failed boolean default false;
alter table fastqc add column error_message text;


-- patch identifier
INSERT INTO meta (species_id, meta_key, meta_value) VALUES (NULL, 'patch', 'patch_92_93_i.sql|Add table to store fastqc outcomes');
