package ubic.gemma.model.association.phenotype;

import java.math.BigInteger;
import java.util.Collection;
import java.util.HashSet;

import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import ubic.gemma.model.genome.gene.phenotype.valueObject.CharacteristicValueObject;
import ubic.gemma.persistence.AbstractDao;

@Repository
public class PhenotypeAssociationDaoImpl extends AbstractDao<PhenotypeAssociation> implements PhenotypeAssociationDao {

    @Autowired
    public PhenotypeAssociationDaoImpl( SessionFactory sessionFactory ) {
        super( PhenotypeAssociationImpl.class );
        super.setSessionFactory( sessionFactory );
    }

    /** find Genes for specific phenotypes */
    // TODO should be and HSQL query
    public Collection<Long> findByPhenotype( String... phenotypesValues ) {

        Collection<Long> genesNCBI = new HashSet<Long>();

        int numberOfterms = phenotypesValues.length;

        if ( numberOfterms == 0 ) {
            return null;
        }

        String queryStringPart1 = "SELECT chr.id FROM CHROMOSOME_FEATURE chr";

        String queryStringPart2 = "";

        for ( int i = 0; i < numberOfterms; i++ ) {
            queryStringPart2 = queryStringPart2
                    + " INNER JOIN ( SELECT t"
                    + i
                    + ".ID FROM CHROMOSOME_FEATURE t"
                    + i
                    + " WHERE id IN (SELECT gene_fk FROM CHARACTERISTIC INNER JOIN PHENOTYPE_ASSOCIATION ON PHENOTYPE_ASSOCIATION.ID = CHARACTERISTIC.PHENOTYPE_ASSOCIATION_FK WHERE VALUE = '"
                    + phenotypesValues[i] + "'))t" + i + " ";
        }

        String queryStringPart3 = "ON ";

        for ( int i = 0; i < numberOfterms; i++ ) {
            queryStringPart3 = queryStringPart3 + "chr.id = t" + i + ".id ";

            // if not the last one
            if ( i != numberOfterms - 1 ) {
                queryStringPart3 = queryStringPart3 + "AND ";
            }
        }

        String queryString = queryStringPart1 + queryStringPart2 + queryStringPart3;

        org.hibernate.SQLQuery queryObject = this.getSession().createSQLQuery( queryString );
        ScrollableResults results = queryObject.scroll( ScrollMode.FORWARD_ONLY );
        while ( results.next() ) {
            genesNCBI.add( ( ( BigInteger ) results.get( 0 ) ).longValue() );
        }

        return genesNCBI;
    }

    public Collection<CharacteristicValueObject> findAllPhenotypes() {

        Collection<CharacteristicValueObject> phenotypes = new HashSet<CharacteristicValueObject>();

        String queryString = "select value, value_uri, category, category_uri, count(*) from CHARACTERISTIC where phenotype_association_fk is not null group by value";
        org.hibernate.SQLQuery queryObject = this.getSession().createSQLQuery( queryString );

        ScrollableResults results = queryObject.scroll( ScrollMode.FORWARD_ONLY );
        while ( results.next() ) {

            String value = ( String ) results.get( 0 );
            String valueUri = ( String ) results.get( 1 );
            String category = ( String ) results.get( 2 );
            String categoryUri = ( String ) results.get( 3 );
            long count = ( ( BigInteger ) results.get( 4 ) ).longValue();

            CharacteristicValueObject characteristicValueObject = new CharacteristicValueObject( value, category,
                    valueUri, categoryUri );
            characteristicValueObject.setOccurence( count );
            phenotypes.add( characteristicValueObject );
        }
        results.close();

        return phenotypes;
    }

}

@@ -859,45 +859,8 @@
  */%0A
-    // TODO should be and HSQL query%0A
     
@@ -1159,22 +1159,21 @@
 %22SELECT 
-chr.id
+t1.ID
  FROM CH
@@ -1193,177 +1193,10 @@
 URE 
-chr%22;%0A%0A        String queryStringPart2 = %22%22;%0A%0A        for ( int i = 0; i %3C numberOfterms; i++ ) %7B%0A            queryStringPart2 = queryStringPart2%0A                    + %22
+t1
  INN
@@ -1208,17 +1208,16 @@
 IN (
- 
 SELECT t
 %22%0A  
@@ -1216,375 +1216,122 @@
 CT t
-%22%0A                    + i%0A                    + %22.ID FROM CHROMOSOME_FEATURE t%22%0A                    + i%0A                    + %22 WHERE id IN (SELECT gene_fk FROM CHARACTERISTIC INNER JOIN PHENOTYPE_ASSOCIATION ON PHENOTYPE_ASSOCIATION.ID = CHARACTERISTIC.PHENOTYPE_ASSOCIATION_FK WHERE VALUE = '%22%0A                    + phenotypesValues%5Bi%5D + %22'))t%22 + i + %22 %22;%0A        %7D
+3.* FROM PHENOTYPE_ASSOCIATION t3 INNER JOIN (SELECT PHENOTYPE_ASSOCIATION_FK,COUNT(*) FROM CHARACTERISTIC WHERE%22;
 %0A%0A  
@@ -1362,16 +1362,13 @@
 Part
-3
+2
  = %22
-ON 
 %22;%0A%0A
@@ -1438,33 +1438,33 @@
  queryStringPart
-3
+2
  = queryStringPa
@@ -1469,35 +1469,51 @@
 Part
-3
+2
  + %22
-chr.id = t
+ value = '
 %22 + 
-i
+phenotypesValues%5Bi%5D
  + %22
-.id
+'
  %22;%0A
@@ -1623,17 +1623,17 @@
 ringPart
-3
+2
  = query
@@ -1646,17 +1646,15 @@
 Part
-3
+2
  + %22
-AND 
+or
 %22;%0A 
@@ -1669,32 +1669,230 @@
    %7D%0A        %7D%0A%0A
+        String queryStringPart3 = %22group by PHENOTYPE_ASSOCIATION_FK HAVING COUNT(*) = %22 + numberOfterms%0A                + %22 ) t4 ON t3.id = t4.PHENOTYPE_ASSOCIATION_FK) t2 ON t1.id = t2.GENE_FK%22;%0A%0A
         String q
@@ -3508,11 +3508,10 @@
 ;%0A    %7D%0A
-%0A
 %7D%0A

Change a service findPhenotypes, we can now find genes containing 1 to many phenotypes ( bag of terms)