/* *******************************************************************
 * The contents of this file are subject to the Mozilla Public License
 * Version 1.1 (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.mozilla.org/MPL/
 *
 * Software distributed under the License is distributed on an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See
 * the License for the specific language governing rights and
 * limitations under the License.
 *
 * The Original Code is the BioWarehouse.
 *
 * The Initial Developer of the Original Code is SRI International.
 * Portions created by SRI International are Copyright (C) 2004.
 * All Rights Reserved.
 ******************************************************************* */
#include "wh_oracle.h"
#include "wh_oracle_util.h"
#include "stdio.h"
#include <sqlca.h>

extern int  dataset_wid;
extern int  organism_wid;
char *organism_name;  /* set by cmdline option */

void
wh_oracle_make_biocyc_dataset(char * name, char * version, char * release_date) {
  char * user_running_loader = getenv("USER");
  char * warehouse_version_string = WAREHOUSE_VERSION_STRING;  
  
  dataset_wid = wh_oracle_get_new_special_wid();

  EXEC SQL INSERT INTO DataSet (WID, Name, Version, ReleaseDate, LoadDate, LoadedBy,
				Application, ApplicationVersion, HomeURL, QueryURL)
    VALUES (:dataset_wid, 
	    :name,
	    :version,
            TO_DATE(:release_date,'YYYY/MM/DD'),
	    SYSDATE,
	    :user_running_loader,
	    'BioCyc Loader',
	    :warehouse_version_string,
	    'http://www.biocyc.org', /*HomeURL VARCHAR2(255)*/
	    'http://www.biocyc.org:1555'); /*QueryURL VARCHAR2(255)*/

  printf("Made dataset, wid is %d\n", dataset_wid);
}

void
wh_oracle_make_biocyc_organism(void) {

  organism_wid = wh_oracle_get_new_wid();

  EXEC SQL INSERT INTO BioSource (WID, Name, DataSetWID)
    VALUES (:organism_wid, 
	    :organism_name,   /* eg. 'Bacillus subtilis' */
	    :dataset_wid);

  printf("Made BioSource entry, WID is %d\n", organism_wid);
}

void
wh_oracle_insert_all_subpathway_predecessors() {
  // For each subpathway-predecessor - superpathway pair in global list:
  //   duplicate all subpathway PathwayReaction rows, except update the
  //   PathwayReaction.pathwaywid to reference the superpathway rather than
  //   the subpathway.
  // In this way the super inherits the reaction graph of the sub.

  WIDNAMELIST psp;
  int subwid, superwid;
    
  // Create temp table to copy PathwayReaction rows into
  EXEC SQL CREATE TABLE TempPathwayReaction AS
    SELECT * FROM PathwayReaction WHERE PathwayWID <> PathwayWID;

  // For each subpathway-predecessor - superpathway pair in global list:
  for ( psp = predecessor_subpathways; psp; psp = psp->next) {
    superwid = psp->wid;
    subwid = find_pathway(psp->name);
    if (subwid == 0) {
      printf("*** Undefined predecessor pathway %s\n", psp->name);
      break;
    }
    
    // Select all rows for subpathway into temp table
    EXEC SQL INSERT INTO TempPathwayReaction
      SELECT * FROM PathwayReaction WHERE PathwayWID = :subwid;

    // Change all PathwayWID values from subwid to superwid
    EXEC SQL UPDATE TempPathwayReaction SET PathwayWID = :superwid;

    // Copy al rows back into PathwayReaction
    EXEC SQL INSERT INTO PathwayReaction SELECT * FROM TempPathwayReaction;

    // Clear the temp table
    EXEC SQL DELETE FROM TempPathwayReaction;
  }

  // Remove temp table
  EXEC SQL DROP TABLE TempPathwayReaction;
}

void
wh_oracle_insert_into_chemical(int compound_wid,
			       struct compound_entry *entry,
			       char * systematic_name ,
			       char * cas_registry_numbers,
			       char * smiles,
			       char * formula)
{
 /* Insert row into Chemical table */
  EXEC SQL INSERT INTO Chemical (WID,
                                 Name,
                                 Class,
                                 SystematicName,
                                 CAS,
                                 MolecularWeightCalc,
                                 Charge,
                                 Smiles,
                                 EmpiricalFormula,
                                 PKA1,
                                 PKA2,
                                 PKA3,
                                 DataSetWID)
    VALUES (:compound_wid,
            :entry->common_name,
            /* optional columns */
            'F',
            :systematic_name:entry->systematic_name_ind,
            :cas_registry_numbers:entry->cas_registry_numbers_ind,
            :entry->molecular_weight:entry->molecular_weight_ind,
            :entry->charge:entry->charge_ind,
            :smiles:entry->smiles_ind,
            :formula:entry->formula_ind,
            :entry->pka1:entry->pka1_ind,
            :entry->pka2:entry->pka2_ind,
            :entry->pka3:entry->pka3_ind,
            :dataset_wid);
} 


void
wh_oracle_insert_class_into_chemical(int compound_wid, char * class_name)
{
 /* Insert row into Chemical table */
  EXEC SQL INSERT INTO Chemical (WID,
                                 Name,
                                 Class,
                                 DataSetWID)
    VALUES (:compound_wid,
            :class_name,
            /* optional columns */
            'T',
            :dataset_wid);
} 


void
wh_oracle_insert_into_pathway(int wid, char * name) {
  EXEC SQL INSERT INTO Pathway (WID,
                                Name,
                                Type,
                                BioSourceWID,
                                DataSetWID)
    VALUES (:wid,
            :name,
            'O',
            :organism_wid,
            :dataset_wid);
}

void
wh_oracle_update_pathway(int wid, char * name) {
   EXEC SQL UPDATE Pathway
    SET Name = :name
    WHERE WID = :wid;
}

void
wh_oracle_insert_into_pathwaylink(int pathway1_wid, int pathway2_wid, int chemical_wid){
   EXEC SQL INSERT INTO PathwayLink (Pathway1WID,
                                      Pathway2WID,
                                      ChemicalWID)
      VALUES (:pathway1_wid,
              :pathway2_wid,
              :chemical_wid);
}

void
wh_oracle_insert_into_superpathway(int sub_pathway_wid, int super_pathway_wid) {
  EXEC SQL INSERT INTO SuperPathway (SubPathwayWID,
				     SuperPathwayWID)
    VALUES (:sub_pathway_wid,
	    :super_pathway_wid); 
}

void
wh_oracle_insert_into_pathwayreaction(int pathway_wid, int reaction_wid, char hypothetical,
				      int prior_reaction_wid, short prior_reaction_wid_ind) {
   EXEC SQL INSERT INTO PathwayReaction (PathwayWID,
					 ReactionWID,
					 Hypothetical,
					 PriorReactionWID)
        VALUES (:pathway_wid,
                :reaction_wid,
		:hypothetical,
                /* Optional columns */
                :prior_reaction_wid:prior_reaction_wid_ind);
}

void
wh_oracle_insert_into_reaction(int wid, char * delta_g0, short delta_g0_ind, char * ec_number, short ec_number_ind, short ec_number_proposed_ind, char spontaneous, short spontaneous_ind){
  /* Insert row into Reaction table */
  EXEC SQL INSERT INTO Reaction (WID,
                                 DeltaG,
                                 ECNumber,
                                 ECNumberProposed,
                                 Spontaneous,
                                 DataSetWID)
    VALUES (:wid,
            /* optional columns */
            :delta_g0:delta_g0_ind,
            :ec_number:ec_number_ind,
            :ec_number:ec_number_proposed_ind,
            :spontaneous:spontaneous_ind,
            :dataset_wid);
}

void
wh_oracle_insert_into_reactant(int reaction_wid,int chemical_wid,double coefficient){
   EXEC SQL INSERT INTO Reactant (ReactionWID,
                                     OtherWID,
                                     Coefficient)
        VALUES (:reaction_wid,
                :chemical_wid,
                :coefficient);
}

void
wh_oracle_insert_into_product(int reaction_wid, int chemical_wid, double coefficient){
   EXEC SQL INSERT INTO Product (ReactionWID,
                                    OtherWID,
                                    Coefficient)
        VALUES (:reaction_wid,
                :chemical_wid,
                :coefficient);
}

void
wh_oracle_insert_into_protein(int wid,struct protein_entry *entry,
			      double molecular_weight, double molecular_weight_exp) {
   EXEC SQL INSERT INTO Protein (WID,
                                Name,
                                MolecularWeightCalc,
                                MolecularWeightExp,
                                PICalc,
                                DataSetWID)
    VALUES (:wid,
            :entry->common_name,
            /* optional columns */
            :molecular_weight:entry->molecular_weight_ind,
            :molecular_weight_exp:entry->molecular_weight_exp_ind,
            :entry->pi:entry->pi_ind,
            :dataset_wid);
}

void
wh_oracle_update_protein_full(int wid, struct protein_entry *entry,
			      double molecular_weight, double molecular_weight_exp) {
  EXEC SQL UPDATE Protein SET
    MolecularWeightCalc = :molecular_weight:entry->molecular_weight_ind,
    MolecularWeightExp = :molecular_weight_exp:entry->molecular_weight_exp_ind,
    Name = :entry->common_name,
    PICalc =  :entry->pi:entry->pi_ind,
    DataSetWID = :dataset_wid
    WHERE WID = :wid;
}

void
wh_oracle_update_protein_aasequence(int wid, char * aa_sequence) {
  int len = strlen(aa_sequence);
  EXEC SQL UPDATE Protein
    SET AASequence = :aa_sequence, Length = :len
    WHERE WID = :wid;
}

void
wh_oracle_insert_into_feature(int feature_wid, char * class, char * geometry, char * point_type,
                              char * common_name, short common_name_ind,
                              int startpos, short startpos_ind,
                              int endpos, short endpos_ind) {
  char * ignored = "ignored";
  short ind_null = INDICATE_NULL;
  short point_type_ind = (0 == strcasecmp("point", geometry)) ? INDICATE_NONNULL : INDICATE_NULL;
  
  EXEC SQL INSERT INTO Feature (WID,
                                SequenceWID,
                                Type,
                                SequenceType,
                                Description,
                                Class,
                                RegionOrPoint,
                                PointType,
                                StartPosition,
                                EndPosition,
                                DataSetWID)
    VALUES (:feature_wid,
            :feature_wid:ind_null,
            :ignored:ind_null,
            'N',
            :common_name:common_name_ind,
	    :class,
            :geometry,
            :point_type:point_type_ind,
            :startpos:startpos_ind,
            :endpos:endpos_ind,
            :dataset_wid);
}

void
wh_oracle_insert_into_gene(int gene_wid, char * common_name, char * unique_id, char direction,
			       char * startpos, short startpos_ind,
			       char * endpos, short endpos_ind,
			       char interrupted, short interrupted_ind) {
  short name_ind;
  common_name = string_column(common_name, 255, &name_ind);
  EXEC SQL INSERT INTO Gene (WID,
                             GenomeID,
			     Direction,
                             Name,
                             CodingRegionStart,
                             CodingRegionEnd,
                             Interrupted,
                             DataSetWID)
    VALUES (:gene_wid,
            :unique_id,
	    :direction,
            /* optional columns */
            :common_name:name_ind,
            :startpos:startpos_ind,
            :endpos:endpos_ind,
            :interrupted:interrupted_ind,
            :dataset_wid);
}

int
wh_oracle_select_gene(char * name)
{
 int wid;

  EXEC SQL WHENEVER NOT FOUND DO break;

  for (;;) {
    EXEC SQL SELECT WID INTO :wid FROM Gene, DBID WHERE
      XID = :name AND OtherWID = WID AND DataSet = :dataset_wid;
    return(wid);
  }

  return(0);

}

int
wh_oracle_select_reaction(char * name){
  int wid;

  EXEC SQL WHENEVER NOT FOUND DO break;

  /* name is not stored in Reaction table - query DBID for it */
  for (;;) {
    EXEC SQL SELECT WID INTO :wid FROM Reaction, DBID WHERE
      XID = :name AND OtherWID = WID AND DataSetWID = :dataset_wid;
    return(wid);
  }
  return(0);
}


int
wh_oracle_select_pathway(char * name) {
  int wid;
  EXEC SQL WHENEVER NOT FOUND DO break;
  for (;;) {
    EXEC SQL SELECT WID INTO :wid FROM Pathway, DBID WHERE
      XID = :name AND OtherWID = WID AND DataSetWID = :dataset_wid;
    return(wid);
  }
  return(0);
}

int
wh_oracle_select_protein(char* name){
  int wid;
  EXEC SQL WHENEVER NOT FOUND DO break;
  for (;;) {
	EXEC SQL SELECT WID INTO :wid FROM Protein
	  WHERE Name = :name AND DataSetWID = :dataset_wid;
    return(wid);
  }
  /* Try DBID */
  for (;;) {
    EXEC SQL SELECT WID INTO :wid FROM Protein, DBID
      WHERE XID = :name AND OtherWID = WID AND DataSetWID = :dataset_wid;
    return(wid);
  }

  return(0);
}
 
int
wh_oracle_select_enzrxn(char* name) {
  int wid;

  EXEC SQL WHENEVER NOT FOUND DO break;

  for (;;) {
    EXEC SQL SELECT WID INTO :wid FROM EnzymaticReaction, DBID WHERE
      XID = :name AND OtherWID = WID AND DataSetWID = :dataset_wid;
    return(wid);
  }

  return(0);
}

int
wh_oracle_select_chemical_wid(char * name) {
  int wid;
  EXEC SQL WHENEVER NOT FOUND DO break;

  for (;;) {
    EXEC SQL SELECT WID INTO :wid FROM Chemical
      WHERE upper(Name) = upper(:name) AND DataSetWID = :dataset_wid;
    return(wid);
  }
  /* Try DBID */
  for (;;) {
    EXEC SQL SELECT WID INTO :wid FROM Chemical, DBID
      WHERE XID = :name AND OtherWID = WID AND DataSetWID = :dataset_wid;
    return(wid);
  }
  /* Try synonyms */
  for (;;) {
    EXEC SQL SELECT WID INTO :wid FROM Chemical, SynonymTable
      WHERE ROWNUM = 1
      AND upper(Syn) = upper(:name) AND OtherWID = WID AND DataSetWID = :dataset_wid;
    return(wid);
  }
  return(0);
}