<?php
//$Id:

/*******************************************************************************
 * Implementation of hook_install().
 */
function tripal_analysis_unigene_install() {
   // create the module's data directory
   tripal_create_moddir('tripal_analysis_unigene');

   // We need to register to tripal_analysis module so it can provide a control
   // for our unigene result. Basically the registration is done by inserting
   // required information into the {tripal_analysis} table.
   tripal_analysis_register_child('tripal_analysis_unigene');

   $previous_db = tripal_db_set_active('chado');
   if (db_table_exists('unigene_libraries_mview')) {
      $sql = "DROP TABLE unigene_libraries_mview";
      db_query($sql);
   }
   if (db_table_exists('unigene_mview')) {
      $sql = "DROP TABLE unigene_mview";
      db_query($sql);
   }
   tripal_db_set_active($previous_db);
   

   tripal_analysis_unigene_add_cvterms();
   tripal_analysis_unigene_add_organism_unigene_mview();
   
   tripal_analysis_unigene_add_stat_mview(); // Add statistics mview

}

/**
*
*/
function tripal_analysis_unigene_update_6001(){

   // we have some new cvterms to add
   tripal_analysis_unigene_add_cvterms();

   // remove the old unigene materialized view and add the new one.
   $mview = tripal_mviews_get_mview_id('unigene_mview');
   if($mview){
      tripal_mviews_action('delete',$mview);
   }
   tripal_analysis_unigene_add_organism_unigene_mview();
   tripal_analysis_unigene_add_stat_mview(); // Add statistics mview

   // next, add the cvterm 'analysis_unigene_name' to all existing 
   // unigenes that are already in the database. This will allow the 
   // materialized view to work.
   $sql = "SELECT N.nid,N.title,CN.analysis_id
           FROM {node} N
             INNER JOIN {chado_analysis} CN on CN.nid = N.nid
           WHERE type = 'chado_analysis_unigene'";
   $analyses = db_query($sql);
   while($analysis = db_fetch_object($analyses)){
      tripal_analysis_insert_property($analysis->analysis_id,'analysis_unigene_name',$analysis->title);	
   }

   $ret = array(
      '#finished' => 1,
   );
   
   return $ret;
}
/**
*
*/
function tripal_analysis_unigene_add_cvterms(){
   tripal_add_cvterms('unigene_version','The version number for the unigene ".
     "(e.g. v1, v2, etc...) ');
   tripal_add_cvterms('analysis_unigene_name', 'The name for a unigene.');
   tripal_add_cvterms('analysis_unigene_num_contigs','The number of contigs in the unigene assembly');
   tripal_add_cvterms('analysis_unigene_num_singlets','The number of singlets remaining in the unigene assembly');
   tripal_add_cvterms('analysis_unigene_num_clusters','The number of clusters in the unigene assembly');
   tripal_add_cvterms('analysis_unigene_num_reads','The number of reads, after filtering, used as input for the assembly');
   tripal_add_cvterms('analysis_unigene_avg_length','The average contig length');
   tripal_add_cvterms('singlet',"Indicates the feature is a singlet in a ".
     "specific unigene version (e.g. v1, v2, etc...). The value specified ".
     "should match that of the unigene_version");

   // Add cveterm 'analysis_unigene_settings' for inserting into analysisprop table   
   tripal_add_cvterms('analysis_unigene_settings', 'Settings of a unigene analysis');
   tripal_add_cvterms('singlet_in_analysis', 'Label the feature as a singlet of specified analysis. The value is the name of a unigene analysis. More importantly, the analysis_id of said analysis is inserted to the featureprop table as rank');
}
/**
*
*/
function tripal_analysis_unigene_add_organism_unigene_mview(){

   $view_name = 'organism_unigene_mview';

   // Drop the MView table if it exists
   $mview_id = tripal_mviews_get_mview_id($view_name);
   if($mview_id){
      tripal_mviews_action("delete",$mview_id);
   }

   tripal_add_mview(
      // view name
      $view_name,
      // tripal module name
      'tripal_analysis_unigene',
      // table name
      $view_name,
      // table schema definition
      'analysis_id integer, organism_id integer',
      // columns for indexing
      'analysis_id, organism_id',
      // SQL statement to populate the view
	   "SELECT DISTINCT A.analysis_id, O.organism_id ".
      "FROM {Analysis} A ".
      "  INNER JOIN analysisprop AP    ON AP.analysis_id = A.analysis_id ".
      "  INNER JOIN cvterm CVT         ON AP.type_id = CVT.cvterm_id ".
      "  INNER JOIN cv CV              ON CV.cv_id = CVT.cv_id ".
      "  INNER JOIN analysisfeature AF ON A.analysis_id = AF.analysis_id ".
      "  INNER JOIN feature F          ON AF.feature_id = F.feature_id ".
      "  INNER JOIN organism O         ON F.organism_id = O.organism_id ".
      "WHERE CV.name = 'tripal' AND CVT.name='analysis_unigene_name'",
      // special index 
	   ''
   );

   // add a job to the job queue so this view gets updated automatically next
   // time the job facility is run
   $mview_id = tripal_mviews_get_mview_id($view_name);
   if($mview_id){
      tripal_mviews_action('update',$mview_id);
   }
}
/*******************************************************************************
 * Implementation of hook_uninstall().
 */
function tripal_analysis_unigene_uninstall() {
   $mview = tripal_mviews_get_mview_id('unigene_mview');
   if($mview){
      tripal_mviews_action('delete',$mview);
   }
   $mview = tripal_mviews_get_mview_id('unigene_libraries_mview');
   if($mview){
      tripal_mviews_action('delete',$mview);
   }
   // Delete the settings from {tripal_analysis} table
   // Drupal complains when the user tries to uninstall tripal_analysis 
   // and tripal_analysis_unigene at the same time. This is because Drupal drops
   // the {tripal_analysis} table before we can delete anything from it. Thus,
   // we perform a db_table_exists() check before the deletion
   tripal_analysis_unregister_child('tripal_analysis_unigene');

   // Delete module's variables from variables table.
   db_query("DELETE FROM {variable} WHERE name='%s'",
            'tripal_analysis_unigene_setting');
   
}

/*******************************************************************************
 * Implementation of hook_requirements(). Make sure 'Tripal Core' and 'Tripal
 * Analysis' are enabled before installation
 */
function tripal_analysis_unigene_requirements($phase) {
   $requirements = array();
   if ($phase == 'install') {
      if (!function_exists('tripal_create_moddir') || !function_exists('tripal_analysis_register_child')) {
         $requirements ['tripal_analysis_unigene'] = array(
            'title' => "tripal_analysis_unigene",
            'value' => "error. Some required modules are just being installed. Please try again.",
            'severity' => REQUIREMENT_ERROR,
         );
      }
   }
   return $requirements;
}

/********************************************************************************
* Add a materialized view for unigene basic stat
*/
function tripal_analysis_unigene_add_stat_mview() {
	$view_name = 'unigene_basic_stats';
	// Drop the MView table if it exists
	$mview_id = tripal_mviews_get_mview_id($view_name);
	if($mview_id){
		tripal_mviews_action("delete",$mview_id);
	}

	$schema = " analysis_id integer,
   							  name character varying(255),
   							  unigene_no integer,
   							  unigene_min_length integer,
   							  unigene_max_length integer,
   							  unigene_avg_length integer,
   							  contig_no integer,
   							  contig_min_length integer,
   							  contig_max_length integer,
   							  contig_avg_length integer,
   							  singlet_no integer,
   							  singlet_min_length integer,
   							  singlet_max_length integer,
   							  singlet_avg_length integer
   							  ";
	 
	$index = "analysis_id";
	 
	$ana_type = tripal_get_cvterm_id('analysis_type');
	$singlet_type_id = tripal_get_cvterm_id('singlet_in_analysis');
	$contig_type = db_result(chado_query("SELECT cvterm_id FROM {cvterm} WHERE name = 'contig' AND cv_id = (SELECT cv_id FROM {cv} WHERE name = 'sequence')")); //P.S. 'contig' is not a tripal term
	$EST_type = db_result(chado_query("SELECT cvterm_id FROM {cvterm} WHERE name = 'EST' AND cv_id = (SELECT cv_id FROM {cv} WHERE name = 'sequence')")); //P.S. 'EST' is not a tripal term
	$sql = "SELECT
						A.analysis_id, name, 
						coalesce(unigene_no, 0), 
						coalesce(unigene_min, 0), 
						coalesce(unigene_max, 0), 
						coalesce(unigene_avg, 0), 
						coalesce(contig_no, 0), 
						coalesce(contig_min, 0), 
						coalesce(contig_max, 0), 
						coalesce(contig_avg, 0), 
						coalesce(singlet_no, 0), 
						coalesce(singlet_min, 0), 
						coalesce(singlet_max, 0), 
						coalesce(singlet_avg, 0)

					FROM analysis A 
					INNER JOIN analysisprop AP ON A.analysis_id = AP.analysis_id

					LEFT JOIN (SELECT count (F.feature_id) AS unigene_no, 
												min(F.seqlen) AS unigene_min, 
												max(F.seqlen) AS unigene_max, 
												round(avg(F.seqlen),0) AS unigene_avg, 
												analysis_id 
												FROM feature F
						         			INNER JOIN analysisfeature AF ON F.feature_id = AF.feature_id
												LEFT JOIN featureprop FP on F.feature_id = FP.feature_id
            								WHERE F.type_id = $contig_type
            								AND ((FP.type_id = $singlet_type_id AND FP.rank != AF.analysis_id) OR FP.type_id IS NULL)
												OR (FP.type_id = $singlet_type_id AND FP.rank = AF.analysis_id)
												GROUP BY analysis_id) Unigene ON Unigene.analysis_id = A.analysis_id
			
					LEFT JOIN (SELECT count (F.feature_id) AS contig_no, 
												min(F.seqlen) AS contig_min, 
												max(F.seqlen) AS contig_max, 
												round(avg(F.seqlen),0) AS contig_avg, 
												analysis_id 
											FROM feature F
         								INNER JOIN analysisfeature AF ON F.feature_id = AF.feature_id
											LEFT JOIN featureprop FP on F.feature_id = FP.feature_id
            							WHERE F.type_id = $contig_type
            							AND ((FP.type_id = $singlet_type_id AND FP.rank != AF.analysis_id) OR FP.type_id IS NULL)
											GROUP BY analysis_id) Contig ON Contig.analysis_id = A.analysis_id

					LEFT JOIN (SELECT count (F.feature_id) AS singlet_no, 
												min(F.seqlen) AS singlet_min, 
												max(F.seqlen) AS singlet_max, 
												round(avg(F.seqlen),0) AS singlet_avg, 
												analysis_id 
											FROM featureprop FP
											INNER JOIN feature F ON F.feature_id = FP.feature_id
         								INNER JOIN analysisfeature AF ON FP.feature_id = AF.feature_id
											WHERE FP.type_id = $singlet_type_id AND FP.rank = AF.analysis_id
											GROUP BY analysis_id) Singlet ON Singlet.analysis_id = A.analysis_id			
			
					WHERE type_id = $ana_type AND value = 'tripal_analysis_unigene'";
	 
	// Create the MView
	tripal_add_mview(
	// view name
	$view_name,
	// tripal module name
      ' tripal_analysis_unigene',
	// table name
	$view_name,
	// table schema definition
	$schema,
	// columns for indexing
	$index,
	// SQL statement to populate the view
	$sql,
	// special index
      ''
	);
	 
	// add a job to the job queue so this view gets updated automatically next
	// time the job facility is run
	$mview_id = tripal_mviews_get_mview_id($view_name);
	if($mview_id){
		tripal_mviews_action('update',$mview_id);
	}
}