# -*- coding: utf-8 -*-

import os

from sqlalchemy import or_, and_


from fr.tagc.uorf.core.model import *

from fr.tagc.uorf.core.execution.DatabaseCheckStrategy import DatabaseCheckStrategy

from fr.tagc.uorf.core.util import Constants
from fr.tagc.uorf.core.util.sql.SQLManagerDS import SQLManagerDS
from fr.tagc.uorf.core.util.sql.SQLManagerPRO import SQLManagerPRO
from fr.tagc.uorf.core.util.sql import SQLConstants
from fr.tagc.uorf.core.util.option.OptionManager import OptionManager
from fr.tagc.uorf.core.util.option import OptionConstants
from fr.tagc.uorf.core.util.general.GeneralUtil import GeneralUtil
from fr.tagc.uorf.core.util.exception.DenCellORFException import DenCellORFException
from fr.tagc.uorf.core.util.log.Logger import Logger


## AssessDatabaseContentStrategy
#  =============================
#
# This class is a strategy aiming to assess the consistency of the data in contained
# in a DS or a PRO database.
# It allows to generate a log file reporting all successful checks and any unexpected 
# inconsistency found in the data and that could have been generated by the program 
# itself. Hence, this strategy is mainly dedicated to the developers of the current
# program and aims to help detecting programming errors that could have been left in
# the source code. It may also be useful for the user in order to help detecting any
# inconsistency in the data that could have been introduced manually or by an other 
# program.
# e.g. If entries of the DSORF table (DS database) are found with a higher value for 
#      their stop position than for their start position, this may suggest an error 
#      in the code of the LiftOver strategy.
#
class AssessDatabaseContentStrategy( object ):
    
    ## Constructor of AssessDatabaseContentStrategy
    #  --------------------------------------------
    #
    # Instance variables:
    #     - db_settings: Dictionary - A dictionary of settings (Strings). This may include:
    #                                    - The database name.
    #                                    - The database type (SQLite / MySQL).
    #                                    - For SQLite databases: the folder of SQLite file.
    #                                    - For MySQL databases: the MySQL user, password, host IP and port.
    #     - db_model: String - The name of the database model to use (PRO / DS).
    #     - output_folder: String - The path of the folder where to save the file containing the logs.
    #     - file_name: String - The name of the generated file.
    #
    # @throw DenCellORFException: When the database type provided is not allowed.
    #
    def __init__( self ):
        
        # Get the options necessary to establish the connection to the database
        self.db_settings = {}
        self.db_settings[ Constants.DB_SETTINGS_DB_TYPE ] = OptionManager.get_instance().get_option( OptionConstants.OPTION_DB_TYPE )
        self.db_settings[ Constants.DB_SETTINGS_DB_NAME ] = OptionManager.get_instance().get_option( OptionConstants.OPTION_DB_NAME, 
                                                                                                     not_none = True )
        
        if ( self.db_settings[ Constants.DB_SETTINGS_DB_TYPE ] == SQLConstants.DB_TYPE_MYSQL ):
            self.db_settings[ Constants.DB_SETTINGS_MYSQL_USER ] = OptionManager.get_instance().get_option( OptionConstants.OPTION_DB_MYSQL_USER )
            self.db_settings[ Constants.DB_SETTINGS_MYSQL_PASSWD ] = OptionManager.get_instance().get_option( OptionConstants.OPTION_DB_MYSQL_PASSWD )
            self.db_settings[ Constants.DB_SETTINGS_MYSQL_HOST ] = OptionManager.get_instance().get_option( OptionConstants.OPTION_DB_MYSQL_HOST_IP )
            self.db_settings[ Constants.DB_SETTINGS_MYSQL_PORT ] = OptionManager.get_instance().get_option( OptionConstants.OPTION_DB_MYSQL_PORT )
            
        elif ( self.db_settings[ Constants.DB_SETTINGS_DB_TYPE ] == SQLConstants.DB_TYPE_SQLITE ):
            self.db_settings[ Constants.DB_SETTINGS_DB_FOLDER ] = OptionManager.get_instance().get_option( OptionConstants.OPTION_DB_FOLDER )
        
        # Get the model of database
        self.db_model = OptionManager.get_instance().get_option( OptionConstants.OPTION_DATABASE_MODEL, 
                                                                 not_none = True )
        if ( self.db_model not in OptionConstants.AVAILABLE_DATABASE_MODELS ):
            raise DenCellORFException( 'The database model provided has to be in the following list: ' + 
                                       ', '.join( OptionConstants.AVAILABLE_DATABASE_MODELS ) + '.' )
        
        # Get the output folder
        self.output_folder = OptionManager.get_instance().get_option( OptionConstants.OPTION_OUTPUT_FOLDER, 
                                                                      not_none = False )
        if ( not self.output_folder ):
            # By default, save the file in the default output folder
            self.output_folder = Constants.DB_CONTENT_CONSISTENCY_ASSESSMENT_FOLDER
        
        # Get the eventual name to use for the generated file
        self.file_name = OptionManager.get_instance().get_option( OptionConstants.OPTION_ASSESS_FILENAME,
                                                                  not_none = False )
        if ( not self.file_name ):
            # By default, save the file as 'DatabaseAssessment' file
            # with the database model as prefix
            self.file_name = self.db_model + 'DatabaseAssessment'
            
    
    
    ## execute
    #  -------
    #
    # Execute the strategy to assess the consistency of the database.
    # 
    # @throw DenCellORFException: When the database provided does not follow the expected model.
    #
    def execute( self ):
                
        # Set the connection to the database
        self.get_sqlmanager_instance().set_db_settings( self.db_settings )
        
        # Check the integrity of the database
        str_ok = self.get_sqlmanager_instance().check_database_str_integrity()
        if ( not str_ok ):
            raise DenCellORFException( 'The schema of the database provided does not follow' +
                                       ' the expected model. Please make sure the provided model (' + 
                                       self.db_model + ') and the database (' + 
                                       self.db_settings[ Constants.DB_SETTINGS_DB_NAME ] +
                                       ') provided are the right ones.' )
        
        self.init_log_file()
        
        # Check the consistency according to the model
        if ( self.db_model == OptionConstants.DATABASE_DECLARATIVE_DS ):
            self.assess_DS_consistency()
            
        elif ( self.db_model == OptionConstants.DATABASE_DECLARATIVE_PRO ):
            self.assess_PRO_consistency()                
    
    
    
    ## assess_DS_consistency
    #  ---------------------
    #
    # This method allows to check the consistency of the data contained
    # in the DS database.
    # 
    def assess_DS_consistency( self ):
                
        # DSORF table
        # -----------
        self.add_to_log_file( '---\t' + 'Assessment of the consistency the DSORF table content' )
        
        # Check the list of chromosomes
        chr_list = SQLManagerDS.get_instance().get_session().query( DSORF.chromosome ).distinct().all()
        chr_list = sorted( GeneralUtil.query_result_to_list( chr_list ) )
        self.add_to_log_file( 'List of chromosomes:\t' + ', '.join( chr_list ) + '\t[TO CHECK]' )
        
        # Check the list of raw_strands
        raw_strand_list = SQLManagerDS.get_instance().get_session().query( DSORF.raw_strand ).filter( DSORF.raw_strand != None ).distinct().all()
        raw_strand_list = sorted( GeneralUtil.query_result_to_list( raw_strand_list ) )
        if ( set( raw_strand_list ).issubset( [ '+', '-' ] ) ):
            self.add_to_log_file( 'Raw strands:\t' + ', '.join( raw_strand_list ) + '\t[OK]' )
        else:
            self.add_to_log_file( 'Raw strands:\t' +  ', '.join( raw_strand_list ) + '\t[ERROR]' )
        
        # Check the list of strands
        strand_list = SQLManagerDS.get_instance().get_session().query( DSORF.strand ).filter( DSORF.strand != None ).distinct().all()
        strand_list = sorted( GeneralUtil.query_result_to_list( strand_list ) )
        if ( set( strand_list ).issubset( [ '+', '-' ] ) ):
            self.add_to_log_file( 'Strands:\t' + ', '.join( strand_list ) + '\t[OK]' )
        else:
            self.add_to_log_file( 'Strands:\t' + ', '.join( strand_list ) + '\t[ERROR]' )
        
        # Check there are no raw_stop_pos lower than the corresponding raw_start_pos 
        raw_start_gt_stop = SQLManagerDS.get_instance().get_session().query( DSORF ).filter( DSORF.raw_start_pos >= DSORF.raw_stop_pos ).count()
        if ( raw_start_gt_stop == 0 ):
            self.add_to_log_file( 'Raw positions:\t' + 
                                  'All DSORFs have the raw start position lower than the raw stop position' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Raw positions:\t' + 
                                  'Number of DSORFs for which the raw start position is greater than the' +
                                  ' raw stop position: ' + str( raw_start_gt_stop ) + 
                                  '\t[ERROR]' )
        
        # Check there are no stop_pos lower than the corresponding start_pos 
        start_gt_stop = SQLManagerDS.get_instance().get_session().query( DSORF ).filter( DSORF.start_pos >= DSORF.stop_pos ).count()
        if ( start_gt_stop == 0 ):
            self.add_to_log_file( 'Positions:\t' + 
                                  'All DSORFs have the start position lower than the stop position' + 
                                  '\t[OK]' ) 
        else:
            self.add_to_log_file( 'Positions:\t' + 
                                  'Number of DSORFs for which the start position is greater than the' +
                                  ' stop position: ' + str( start_gt_stop ) + 
                                  '\t[ERROR]' )
        
        # Check that all the ORFs that are not spliced have one single "exon"
        not_spl_single_exon = SQLManagerDS.get_instance().get_session().query( DSORF ).filter( ( DSORF.spliced == False ), 
                                                                                               ( DSORF.spliced_parts_count != 1 ) ).count()
        if ( not_spl_single_exon == 0 ):
            self.add_to_log_file( 'Splicing / Exon number:\t' + 
                                  'All DSORFs not spliced have a single exon' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Splicing / Exon number:\t' + 
                                  'Number of DSORFs not spliced that have more than one single exon: ' +
                                  str( not_spl_single_exon ) + 
                                  '\t[ERROR]' )
            
        # Check that all the ORFs that are not spliced do not have any spliced positions
        not_spl_positions = SQLManagerDS.get_instance().get_session().query( DSORF ).filter( ( DSORF.spliced == False ),
                                                                                             or_( ( DSORF.raw_splice_starts != None ),
                                                                                                  ( DSORF.raw_splice_ends != None ) ) ).count()
        if ( not_spl_positions == 0 ):
            self.add_to_log_file( 'Splicing / Exon positions:\t' + 
                                  'All DSORFs not spliced do not have exonic positions' +
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Splicing / Exon positions:\t' + 
                                  'Number of DSORFs not spliced that have exonic positions: ' +
                                  str( not_spl_positions ) +
                                  '\t[ERROR]' )
                                                                         
        # Check that all the ORFs that are spliced have at least two "exons"
        spl_several_exons = SQLManagerDS.get_instance().get_session().query( DSORF ).filter( ( DSORF.spliced == True ),
                                                                                             ( DSORF.spliced_parts_count < 2 ) ).count()
        if ( spl_several_exons == 0 ):
            self.add_to_log_file( 'Splicing / Exon number:\t' + 
                                  'All DSORF spliced have at least two exons' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Splicing / Exon number:\t' + 
                                  'Number of DSORFs spliced that do not have several exons: ' + 
                                  str( spl_several_exons ) + 
                                  '\t[ERROR]' )
                                                                         
        # Check raw genomic lengths are positive
        raw_gen_len_pos = SQLManagerDS.get_instance().get_session().query( DSORF ).filter( DSORF.raw_genomic_length < 0 ).count()
        if ( raw_gen_len_pos == 0 ):
            self.add_to_log_file( 'Raw genomic lengths:\t' + 
                                  'All DSORFs have a positive raw genomic length' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Genomic lengths:\t' + 
                                  'Number of DSORFs that have a negative raw genomic length: ' + 
                                  str( raw_gen_len_pos ) + 
                                  '\t[ERROR]' )
                                                                         
        # Check genomic lengths are positive
        gen_len_pos = SQLManagerDS.get_instance().get_session().query( DSORF ).filter( DSORF.genomic_length < 0 ).count()
        if ( gen_len_pos == 0 ):
            self.add_to_log_file( 'Genomic lengths:\t' + 
                                  'All DSORFs have a positive genomic length' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Genomic lengths:\t' + 
                                  'Number of DSORFs that have a negative genomic length: ' + 
                                  str( gen_len_pos ) + 
                                  '\t[ERROR]' )
        
        # Check coherence of exon locations
        # - For positive raw_strand
        all_dsorfs_plus_raw_strand = SQLManagerDS.get_instance().get_session().query( DSORF ).filter( DSORF.raw_strand == '+',
                                                                                                      DSORF.spliced == True,
                                                                                                      DSORF.raw_splice_starts != None,
                                                                                                      DSORF.raw_splice_starts != Constants.REPLACE_TOO_LONG_STRINGS,
                                                                                                      DSORF.raw_splice_ends != None,
                                                                                                      DSORF.raw_splice_ends != Constants.REPLACE_TOO_LONG_STRINGS ).all()
        wrong_order_count = 0
        wrong_order_ids = []
        
        for dsorf in all_dsorfs_plus_raw_strand:
            
            start_list = dsorf.raw_splice_starts.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            end_list = dsorf.raw_splice_ends.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            
            start_end_list = []
            for k in range( len( start_list ) ):
                start_end_list += [ start_list[ k ], end_list[ k ] ]
            start_end_list = map( int, start_end_list )
                
            if ( start_end_list != sorted( start_end_list ) ):
                wrong_order_count += 1
                wrong_order_ids.append( str( dsorf.id ) )
                
        if ( wrong_order_count == 0 ):
            self.add_to_log_file( 'Spliced position (DSORFs on + raw strand):\t' + 
                                  'All DSORFs on + raw strand have their raw spliced positions in the increasing order' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Spliced position (DSORFs on + raw strand):\t' + 
                                  'Number of DSORFs on + raw strand for which the raw spliced positions are' +
                                  ' not in the increading order:' + str( wrong_order_count ) + 
                                  ', DSORF ids: ' + ', '.join( wrong_order_ids ) +
                                  '\t[ERROR]' )
            
        # - For negative raw_strand
        all_dsorfs_neg_raw_strand = SQLManagerDS.get_instance().get_session().query( DSORF ).filter( DSORF.raw_strand == '-',
                                                                                                     DSORF.spliced == True,
                                                                                                     DSORF.raw_splice_starts != None,
                                                                                                     DSORF.raw_splice_starts != Constants.REPLACE_TOO_LONG_STRINGS,
                                                                                                     DSORF.raw_splice_ends != None,
                                                                                                     DSORF.raw_splice_ends != Constants.REPLACE_TOO_LONG_STRINGS ).all()
        wrong_order_count = 0
        wrong_order_ids = []
        
        for dsorf in all_dsorfs_neg_raw_strand:
            
            start_list = dsorf.raw_splice_starts.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            end_list = dsorf.raw_splice_ends.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            
            start_end_list = []
            for k in range( len( start_list ) ):
                start_end_list += [ start_list[ k ], end_list[ k ] ]
            start_end_list = map( int, start_end_list )
                
            if ( start_end_list != sorted( start_end_list, reverse = True ) ):
                wrong_order_count += 1
                wrong_order_ids.append( str( dsorf.id ) )
                
        if ( wrong_order_count == 0 ):
            self.add_to_log_file( 'Spliced position (DSORFs on - raw strand):\t' + 
                                  'All DSORFs on - raw strand have their raw spliced positions in the decreasing order' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Spliced position (DSORFs on - raw strand):\t' + 
                                  'Number of DSORFs on - raw strand for which the raw spliced positions are' +
                                  ' not in the decreasing order:' + str( wrong_order_count ) + 
                                  ', DSORF ids: ' + ', '.join( wrong_order_ids ) +
                                  '\t[ERROR]' )
            
        # - For positive strand
        all_dsorfs_plus_strand = SQLManagerDS.get_instance().get_session().query( DSORF ).filter( DSORF.strand == '+',
                                                                                                  DSORF.spliced == True,
                                                                                                  DSORF.splice_starts != None,
                                                                                                  DSORF.splice_starts != Constants.REPLACE_TOO_LONG_STRINGS,
                                                                                                  DSORF.splice_ends != None,
                                                                                                  DSORF.splice_ends != Constants.REPLACE_TOO_LONG_STRINGS ).all()
        wrong_order_count = 0
        wrong_order_ids = []
        
        for dsorf in all_dsorfs_plus_strand:
            
            start_list = dsorf.splice_starts.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            end_list = dsorf.splice_ends.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            
            start_end_list = []
            for k in range( len( start_list ) ):
                start_end_list += [ start_list[ k ], end_list[ k ] ]
            start_end_list = map( int, start_end_list )
                
            if ( start_end_list != sorted( start_end_list ) ):
                wrong_order_count += 1
                wrong_order_ids.append( str( dsorf.id ) )
                
        if ( wrong_order_count == 0 ):
            self.add_to_log_file( 'Spliced position (DSORFs on + strand):\t' + 
                                  'All DSORFs on + strand have their spliced positions in the increasing order' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Spliced position (DSORFs on + strand):\t' + 
                                  'Number of DSORFs on + strand for which the spliced positions are' +
                                  ' not in the increasing order:' + str( wrong_order_count ) + 
                                  ', DSORF ids: ' + ', '.join( wrong_order_ids ) +
                                  '\t[ERROR]' )
            
        # - For negative strand
        all_dsorfs_neg_strand = SQLManagerDS.get_instance().get_session().query( DSORF ).filter( DSORF.strand == '-',
                                                                                                 DSORF.spliced == True,
                                                                                                 DSORF.splice_starts != None,
                                                                                                 DSORF.splice_starts != Constants.REPLACE_TOO_LONG_STRINGS,
                                                                                                 DSORF.splice_ends != None,
                                                                                                 DSORF.splice_ends != Constants.REPLACE_TOO_LONG_STRINGS ).all()
        wrong_order_count = 0
        wrong_order_ids = []
        
        for dsorf in all_dsorfs_neg_strand:
            
            start_list = dsorf.splice_starts.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            end_list = dsorf.splice_ends.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            
            start_end_list = []
            for k in range( len( start_list ) ):
                start_end_list += [ start_list[ k ], end_list[ k ] ]
            start_end_list = map( int, start_end_list )
                
            if ( start_end_list != sorted( start_end_list, reverse = True ) ):
                wrong_order_count += 1
                wrong_order_ids.append( str( dsorf.id ) )
                
        if ( wrong_order_count == 0 ):
            self.add_to_log_file( 'Spliced position (DSORFs on - strand):\t' + 
                                  'All DSORFs on - strand have their spliced positions in the decreasing order' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Spliced position (DSORFs on - strand):\t' + 
                                  'Number of DSORFs on - strand for which the spliced positions are' +
                                  ' not in the decreasing order:' + str( wrong_order_count ) + 
                                  ', DSORF ids: ' + ', '.join( wrong_order_ids ) +
                                  '\t[ERROR]' )
        
        
        # DSTranscript table
        # ------------------
        self.add_to_log_file( '\n---\t' + 'Assessment of the consistency the DSTranscript table content' )
        
        # Check there are no raw_end_pos lower than the corresponding raw_start_pos 
        raw_start_gt_end = SQLManagerDS.get_instance().get_session().query( DSTranscript ).filter( DSTranscript.raw_start_pos >= DSTranscript.raw_end_pos ).count()
        if ( raw_start_gt_end == 0 ):
            self.add_to_log_file( 'Raw positions:\t' + 
                                  'All DSTranscripts have the raw start position lower than the raw end position' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Raw positions:\t' + 
                                  'Number of DSTranscripts for which the raw start position is greater' +
                                  ' than the raw end position: ' + str( raw_start_gt_end ) + 
                                  '\t[ERROR]' )
        
        # Check there are no end_pos lower than the corresponding start_pos 
        start_gt_end = SQLManagerDS.get_instance().get_session().query( DSTranscript ).filter( DSTranscript.start_pos >= DSTranscript.end_pos ).count()
        if ( start_gt_end == 0 ):
            self.add_to_log_file( 'Positions:\t' + 
                                  'All DSTranscripts have the start position lower than the end position' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Positions:\t' + 
                                  'Number of DSTranscripts for which the start position is greater' +
                                  ' than the end position: ' + 
                                  str( start_gt_end ) + 
                                  '\t[ERROR]' )
        
        # Check there are no raw_cds_stop_pos lower than the corresponding raw_cds_start_pos 
        raw_cds_start_gt_stop = SQLManagerDS.get_instance().get_session().query( DSTranscript ).filter( DSTranscript.raw_cds_start_pos >= DSTranscript.raw_cds_stop_pos ).count()
        if ( raw_cds_start_gt_stop == 0 ):
            self.add_to_log_file( 'Raw CDS positions:\t' + 
                                  'All DSTranscripts have the raw CDS start position lower' +
                                  ' than the raw CDS stop position' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Raw CDS positions:\t' + 
                                  'Number of DSTranscripts for which the raw start position is greater' +
                                  ' than the raw CDS stop position: ' + 
                                  str( raw_cds_start_gt_stop ) + 
                                  '\t[ERROR]' )
        
        # Check there are no cds_stop_pos lower than the corresponding cds_start_pos 
        cds_start_gt_stop = SQLManagerDS.get_instance().get_session().query( DSTranscript ).filter( DSTranscript.cds_start_pos >= DSTranscript.cds_stop_pos ).count()
        if ( cds_start_gt_stop == 0 ):
            self.add_to_log_file( 'CDS positions:\t' + 
                                  'All DSTranscripts have the CDS start position lower than the CDS stop position' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'CDS positions:\t' + 
                                  'Number of DSTranscripts for which the CDS start position is greater' +
                                  ' than the CDS stop position: ' + 
                                  str( cds_start_gt_stop ) + 
                                  '\t[ERROR]' )
            
        # Check the list of RNA biotypes
        rna_biotype_list = SQLManagerDS.get_instance().get_session().query( DSTranscript.rna_biotype ).filter( DSTranscript.rna_biotype != None ).distinct().all()
        rna_biotype_list = sorted( GeneralUtil.query_result_to_list( rna_biotype_list ) )
        self.add_to_log_file( 'List of RNA biotypes:\t' + ', '.join( rna_biotype_list ) + 
                              '\t[TO CHECK]' )
                
                
        # DSORFTranscriptAsso table
        # -------------------------
        self.add_to_log_file( '---\t' + 'Assessment of the consistency the DSORFTranscriptAsso table content' )
        
        # Check the nucleic and amino acid lengths are consistent
        orf_len_cons = SQLManagerDS.get_instance().get_session().query( DSORFTranscriptAsso ).filter(
                                                                                                        DSORFTranscriptAsso.orf_length != None,
                                                                                                        DSORFTranscriptAsso.orf_length_nt != None,
                                                                                                        ( ( DSORFTranscriptAsso.orf_length + 1 ) *3 != DSORFTranscriptAsso.orf_length_nt ) 
                                                                                                    ).count()
        if ( orf_len_cons == 0 ):
            self.add_to_log_file( 'Lengths:\t' + 
                                  'All DSORFTranscriptAsso have their nucleic lengths consistent with' +
                                  ' their lengths in amino acids' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Lengths:\t' + 
                                  'Number of DSORFTranscriptAsso for which nucleic lengths is not ' +
                                  ' consistent with the amino acid length: ' + str( orf_len_cons ) + 
                                  '\t[ERROR]' )
                
        
        # Gene table
        # ----------
        self.add_to_log_file( '\n---\t' + 'Assessment of the consistency the Gene table content' )
        
        # Check the list of chromosomes
        chr_list = SQLManagerDS.get_instance().get_session().query( Gene.chromosome ).filter( Gene.chromosome != None ).distinct().all()
        chr_list = sorted( GeneralUtil.query_result_to_list( chr_list ) )
        self.add_to_log_file( 'List of chromosomes:\t' + ', '.join( chr_list ) + 
                              '\t[TO CHECK]' )               
    
    
    
    ## assess_PRO_consistency
    #  ----------------------
    #
    # This method allows to check the consistency of the data contained
    # in the PRO database.
    # 
    def assess_PRO_consistency( self ):
                
        # ORF table
        # ---------
        self.add_to_log_file( '---\t' + 'Assessment of the consistency the ORF table content' )
        
        # Check the list of chromosomes
        chr_list = SQLManagerPRO.get_instance().get_session().query( ORF.chromosome ).distinct().all()
        chr_list = sorted( GeneralUtil.query_result_to_list( chr_list ) )
        self.add_to_log_file( 'List of chromosomes:\t' + ', '.join( chr_list ) + '\t[TO CHECK]' )
                
        # Check the list of strands
        strand_list = SQLManagerPRO.get_instance().get_session().query( ORF.strand ).filter( ORF.strand != None ).distinct().all()
        strand_list = sorted( GeneralUtil.query_result_to_list( strand_list ) )
        if ( set( strand_list ).issubset( [ '+', '-' ] ) ):
            self.add_to_log_file( 'Strands:\t' + ', '.join( strand_list ) + '\t[OK]' )
        else:
            self.add_to_log_file( 'Strands:\t' + ', '.join( strand_list ) + '\t[ERROR]' )
                
        # Check there are no stop_pos lower than the corresponding start_pos 
        start_gt_stop = SQLManagerPRO.get_instance().get_session().query( ORF ).filter( ORF.start_pos >= ORF.stop_pos ).count()
        if ( start_gt_stop == 0 ):
            self.add_to_log_file( 'Positions:\t' + 
                                  'All ORFs have the start position lower than the stop position' + 
                                  '\t[OK]' ) 
        else:
            self.add_to_log_file( 'Positions:\t' + 
                                  'Number of ORFs for which the start position is greater than the' +
                                  ' stop position: ' + str( start_gt_stop ) + 
                                  '\t[ERROR]' )
        
        # Check that all the ORFs that are not spliced have one single "exon"
        not_spl_single_exon = SQLManagerPRO.get_instance().get_session().query( ORF ).filter( ( ORF.spliced == False ), 
                                                                                              ( ORF.spliced_parts_count != 1 ) ).count()
        if ( not_spl_single_exon == 0 ):
            self.add_to_log_file( 'Splicing / Exon number:\t' + 
                                  'All ORFs not spliced have a single exon' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Splicing / Exon number:\t' + 
                                  'Number of ORFs not spliced that have more than one single exon: ' +
                                  str( not_spl_single_exon ) + 
                                  '\t[ERROR]' )
            
        # Check that all the ORFs that are not spliced do not have any spliced positions
        not_spl_positions = SQLManagerPRO.get_instance().get_session().query( ORF ).filter( ( ORF.spliced == False ),
                                                                                            or_( ( ORF.splice_starts != None ),
                                                                                                 ( ORF.splice_ends != None ) ) ).count()
        if ( not_spl_positions == 0 ):
            self.add_to_log_file( 'Splicing / Exon positions:\t' + 
                                  'All ORFs not spliced do not have any exonic positions' +
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Splicing / Exon positions:\t' + 
                                  'Number of ORFs not spliced that have exonic positions: ' +
                                  str( not_spl_positions ) +
                                  '\t[ERROR]' )
                                                                         
        # Check that all the ORFs that are spliced have at least two "exons"
        spl_several_exons = SQLManagerPRO.get_instance().get_session().query( ORF ).filter( ( ORF.spliced == True ),
                                                                                            ( ORF.spliced_parts_count < 2 ) ).count()
        if ( spl_several_exons == 0 ):
            self.add_to_log_file( 'Splicing / Exon number:\t' + 
                                  'All ORF spliced have at least two exons' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Splicing / Exon number:\t' + 
                                  'Number of ORFs spliced that do not have exons: ' + 
                                  str( spl_several_exons ) + 
                                  '\t[ERROR]' )
                                                                         
        # Check genomic lengths are positive
        gen_len_pos = SQLManagerPRO.get_instance().get_session().query( ORF ).filter( ORF.genomic_length < 0 ).count()
        if ( gen_len_pos == 0 ):
            self.add_to_log_file( 'Genomic lengths:\t' + 
                                  'All ORFs have a positive genomic length' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Genomic lengths:\t' + 
                                  'Number of ORFs that have a negative genomic length: ' + 
                                  str( gen_len_pos ) + 
                                  '\t[ERROR]' )
        
        # Check coherence of exon locations
        # - For positive strand
        all_dsorfs_plus_strand = SQLManagerPRO.get_instance().get_session().query( ORF ).filter( ORF.strand == '+',
                                                                                                 ORF.spliced == True,
                                                                                                 ORF.splice_starts != None,
                                                                                                 ORF.splice_starts != Constants.REPLACE_TOO_LONG_STRINGS,
                                                                                                 ORF.splice_ends != None,
                                                                                                 ORF.splice_ends != Constants.REPLACE_TOO_LONG_STRINGS ).all()
        wrong_order_count = 0
        wrong_order_ids = []
        
        for dsorf in all_dsorfs_plus_strand:
            
            start_list = dsorf.splice_starts.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            end_list = dsorf.splice_ends.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            
            start_end_list = []
            for k in range( len( start_list ) ):
                start_end_list += [ start_list[ k ], end_list[ k ] ]
            start_end_list = map( int, start_end_list )
                
            if ( start_end_list != sorted( start_end_list ) ):
                wrong_order_count += 1
                wrong_order_ids.append( str( dsorf.id ) )
                
        if ( wrong_order_count == 0 ):
            self.add_to_log_file( 'Spliced position (ORFs on + strand):\t' + 
                                  'All ORFs on + strand have their spliced positions in the increasing order' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Spliced position (ORFs on + strand):\t' + 
                                  'Number of ORFs on + strand for which the spliced positions are' +
                                  ' not in the increasing order:' + str( wrong_order_count ) + 
                                  ', ORF ids: ' + ', '.join( wrong_order_ids ) +
                                  '\t[ERROR]' )
            
        # - For negative strand
        all_dsorfs_neg_strand = SQLManagerPRO.get_instance().get_session().query( ORF ).filter( ORF.strand == '-',
                                                                                                ORF.spliced == True,
                                                                                                ORF.splice_starts != None,
                                                                                                ORF.splice_starts != Constants.REPLACE_TOO_LONG_STRINGS,
                                                                                                ORF.splice_ends != None,
                                                                                                ORF.splice_ends != Constants.REPLACE_TOO_LONG_STRINGS ).all()
        wrong_order_count = 0
        wrong_order_ids = []
        
        for dsorf in all_dsorfs_neg_strand:
            
            start_list = dsorf.splice_starts.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            end_list = dsorf.splice_ends.split( Constants.ORF_SPLICING_COORD_SEPARATOR )
            
            start_end_list = []
            for k in range( len( start_list ) ):
                start_end_list += [ start_list[ k ], end_list[ k ] ]
            start_end_list = map( int, start_end_list )
                
            if ( start_end_list != sorted( start_end_list, reverse = True ) ):
                wrong_order_count += 1
                wrong_order_ids.append( str( dsorf.id ) )
                
        if ( wrong_order_count == 0 ):
            self.add_to_log_file( 'Spliced position (ORFs on - strand):\t' + 
                                  'All ORFs on - strand have their spliced positions in the decreasing order' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Spliced position (ORFs on - strand):\t' + 
                                  'Number of ORFs on - strand for which the spliced positions are' +
                                  ' not in the decreasing order:' + str( wrong_order_count ) + 
                                  ', ORF ids: ' + ', '.join( wrong_order_ids ) +
                                  '\t[ERROR]' )
        
        
        # Transcript table
        # ----------------
        self.add_to_log_file( '\n---\t' + 'Assessment of the consistency the Transcript table content' )
        
        # Check there are no end_pos lower than the corresponding start_pos 
        start_gt_end = SQLManagerPRO.get_instance().get_session().query( Transcript ).filter( Transcript.start_pos >= Transcript.end_pos ).count()
        if ( start_gt_end == 0 ):
            self.add_to_log_file( 'Positions:\t' + 
                                  'All Transcripts have the start position lower than the end position' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Positions:\t' + 
                                  'Number of Transcripts for which the start position is greater' +
                                  ' than the end position: ' + 
                                  str( start_gt_end ) + 
                                  '\t[ERROR]' )
        
        # Check there are no cds_stop_pos lower than the corresponding cds_start_pos 
        cds_start_gt_stop = SQLManagerPRO.get_instance().get_session().query( Transcript ).filter( Transcript.cds_start_pos >= Transcript.cds_stop_pos ).count()
        if ( cds_start_gt_stop == 0 ):
            self.add_to_log_file( 'CDS positions:\t' + 
                                  'All Transcripts have the CDS start position lower than the CDS stop position' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'CDS positions:\t' + 
                                  'Number of Transcripts for which the CDS start position is greater' +
                                  ' than the CDS stop position: ' + 
                                  str( cds_start_gt_stop ) + 
                                  '\t[ERROR]' )
        
        # Check there are no rel_cds_stop_pos lower than the corresponding rel_cds_start_pos 
        rel_cds_start_gt_stop = SQLManagerPRO.get_instance().get_session().query( Transcript ).filter( Transcript.rel_cds_start_pos >= Transcript.rel_cds_stop_pos ).count()
        if ( rel_cds_start_gt_stop == 0 ):
            self.add_to_log_file( 'CDS relative positions:\t' + 
                                  'All Transcripts have the CDS relative start position lower than the' +
                                  ' CDS relative stop position' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'CDS relative positions:\t' + 
                                  'Number of Transcripts for which the CDS relative start position is greater' +
                                  ' than the CDS relative stop position: ' + 
                                  str( rel_cds_start_gt_stop ) + 
                                  '\t[ERROR]' )
        
        
        # ORFTranscriptAsso table
        # -----------------------
        self.add_to_log_file( '\n---\t' + 'Assessment of the consistency the ORFTranscriptAsso table content' )
        
        # Check there are no rel_stop_pos lower than the corresponding rel_start_pos 
        rel_start_gt_stop = SQLManagerPRO.get_instance().get_session().query( ORFTranscriptAsso ).filter( ORFTranscriptAsso.rel_start_pos >= ORFTranscriptAsso.rel_stop_pos ).count()
        if ( rel_start_gt_stop == 0 ):
            self.add_to_log_file( 'Relative positions:\t' + 
                                  'All ORFTranscriptAsso entries have the relative start position lower' +
                                  ' than the relative stop position' + 
                                  '\t[OK]' )
        else:
            self.add_to_log_file( 'Relative positions:\t' + 
                                  'Number of ORFTranscriptAsso entries for which the relative start' +
                                  ' position is greater than the relative stop position: ' + 
                                  str( rel_start_gt_stop ) + 
                                  '\t[ERROR]' )
                
        
        # UTRNABiotypeCatalog table
        # -------------------------
        rna_biotype_list = SQLManagerPRO.get_instance().get_session().query( UTRNABiotypeCatalog.biotype ).all()
        rna_biotype_list = sorted( GeneralUtil.query_result_to_list( rna_biotype_list ) )
        self.add_to_log_file( 'List of RNA biotypes:\t' + ', '.join( rna_biotype_list ) + 
                              '\t[TO CHECK]' )
                
        
        # PROGene table
        # -------------
        self.add_to_log_file( '\n---\t' + 'Assessment of the consistency the PROGene table content' )
        
        # Check the list of chromosomes
        chr_list = SQLManagerPRO.get_instance().get_session().query( PROGene.chromosome ).filter( PROGene.chromosome != None ).distinct().all()
        chr_list = sorted( GeneralUtil.query_result_to_list( chr_list ) )
        self.add_to_log_file( 'List of chromosomes:\t' + ', '.join( chr_list ) + 
                              '\t[TO CHECK]' )
                
        
        # ProvidedCategoryCatalog table
        # -----------------------------
        self.add_to_log_file( '\n---\t' + 'Assessment of the consistency the ORF categories, cell context and FLOSS classes' )
        prov_category_list = SQLManagerPRO.get_instance().get_session().query( ProvidedCategoryCatalog.category ).all()
        prov_category_list = GeneralUtil.query_result_to_list( prov_category_list )
        self.add_to_log_file( 'List of (provided) ORF categories:\t' + ', '.join( prov_category_list ) + 
                              '\t[TO CHECK]' )
                
        
        # CellContextCatalog table
        # ------------------------
        cell_context_list = SQLManagerPRO.get_instance().get_session().query( CellContextCatalog.context ).all()
        cell_context_list = GeneralUtil.query_result_to_list( cell_context_list )
        self.add_to_log_file( 'List of cell contexts:\t' + ', '.join( cell_context_list ) + 
                              '\t[TO CHECK]' )
                
        
        # FLOSSClassCatalog table
        # -----------------------
        floss_class_list = SQLManagerPRO.get_instance().get_session().query( FLOSSClassCatalog.floss_class ).all()
        floss_class_list = GeneralUtil.query_result_to_list( floss_class_list )
        self.add_to_log_file( 'List of FLOSS classes:\t' + ', '.join( floss_class_list ) + 
                              '\t[TO CHECK]' )
                
        
        # ORFCategoryCatalog table
        # ------------------------
        orf_category_list = SQLManagerPRO.get_instance().get_session().query( ORFCategoryCatalog.category ).all()
        orf_category_list = GeneralUtil.query_result_to_list( orf_category_list )
        self.add_to_log_file( 'List of (computed) ORF categories:\t' + ', '.join( orf_category_list ) + 
                              '\t[TO CHECK]' )
                
    
    ## init_log_file
    #  -------------
    #
    # This method allows to erase any existing log file and to 
    # create a new one.
    #
    # @param ext: String - The extension of the file. 
    #                      ".tsv" by default.
    # 
    def init_log_file( self, ext='.tsv' ):

        # Create the output folder if it does not yet exist
        # (and its parent folders if necessary)
        if ( not os.path.isdir( self.output_folder ) ):
            os.makedirs( self.output_folder )
        
        file_path = os.path.join( self.output_folder, self.file_name + ext )
        
        Logger.get_instance().info( ' The logs will be saved in ' + file_path + '.' )
        
        file = open( file_path, mode = 'w' )
        file.close()
    
    
    ## add_to_log_file
    #  ---------------
    #
    # This method allows to add a line to the log file.
    #
    # @param message: String - The message to add in the log file.
    # @param ext: String - The extension of the file. 
    #                      ".tsv" by default.
    # 
    def add_to_log_file( self, message, ext='.tsv' ):
        
        file_path = os.path.join( self.output_folder, self.file_name + ext )
        
        file = open( file_path, mode = 'a' )
        file.write( message + '\n' )
        file.close()
    
    
    
    ## get_sqlmanager_instance
    #  -----------------------
    #
    # Return the appropriate SQLManager instance.
    #
    # @return SQLManager instance corresponding to the database model used.
    # 
    def get_sqlmanager_instance( self ):
        
        sqlmanager_class = eval( 'SQLManager' + self.db_model )
        
        return sqlmanager_class.get_instance()
        