package uk.ac.ebi.intenz.mapper;
   
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.util.ArrayList;
   import java.util.List;
  
  import uk.ac.ebi.intenz.domain.enzyme.EnzymeCommissionNumber;
  import uk.ac.ebi.intenz.domain.enzyme.EnzymeEntry;
  import uk.ac.ebi.intenz.domain.enzyme.EnzymeSubSubclass;
  import uk.ac.ebi.intenz.domain.exceptions.DomainException;
  
  /**
   * Maps enzyme Sub-subclass information to the corresponding database tables.
   *
   * @author Michael Darsow
   * @version $Revision: 1.3 $ $Date: 2009/05/26 14:59:09 $
   */
  public class EnzymeSubSubclassMapper {
  
    private static final String COLUMNS = "s1.ec1, s1.ec2, s1.ec3, s1.name, s2.name, s3.name, s1.description";
  
    private static final String SELECT_ALL = "SELECT " + COLUMNS
        + " FROM subsubclasses s1, subclasses s2, classes s3";
    
    public EnzymeSubSubclassMapper() {
    }
  
    private String findStatement() {
      return "SELECT " + COLUMNS +
             " FROM subsubclasses s1, subclasses s2, classes s3 WHERE s3.ec1 = ? AND s2.ec1 = ? AND s2.ec2 = ? AND s1.ec1 = ? AND s1.ec2 = ? AND s1.ec3 = ?" +
             " ORDER BY s1.ec1, s1.ec2, s1.ec3";
    }
  
    private String findListStatement() {
      return "SELECT " + COLUMNS +
             " FROM subsubclasses s1, subclasses s2, classes s3 WHERE s3.ec1 = ? AND s2.ec1 = ? AND s2.ec2 = ? AND s1.ec1 = ? AND s1.ec2 = ?" +
             " ORDER BY s1.ec1, s1.ec2, s1.ec3";
    }
  
    private String findSubSubclassOnly() {
      return "SELECT ec1 FROM subsubclasses WHERE ec1 = ? AND ec2 = ? AND ec3 = ?";
    }
  
    private String insertStatement() {
      return "INSERT INTO subsubclasses (ec1, ec2, ec3, name, description, active ) VALUES (?, ?, ?, ?, ?, ?)";
    }
  
    /**
     * Tries to find Sub-subclass information about an enzyme.
     *
     * @param ec1 Number of class to search for.
     * @param ec2 Number of subclass to search for.
     * @param ec3 Number of sub-subclass to search for.
     * @param con The logical connection.
     * @return an <code>EnzymeClass</code> instance or <code>null</code> if nothing has been found.
     * @throws NullPointerException if any of the parameters is <code>null</code>.
     * @throws SQLException         if a database error occurs.
     * @throws DomainException      if any error related to domain information occurs.
     */
    public EnzymeSubSubclass find(int ec1, int ec2, int ec3, Connection con)
    throws SQLException, DomainException {
      if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
  
      PreparedStatement findStatement = null;
      ResultSet rs = null;
      EnzymeSubSubclass result = null;
      List<EnzymeEntry> entries;
  
      // Get entries.
      EnzymeEntryMapper enzymeEntryMapper = new EnzymeEntryMapper();
      entries = enzymeEntryMapper.findAllSubSubclassEntriesByEc(ec1, ec2, ec3, con);
      if(entries == null) entries = new ArrayList<EnzymeEntry>();
  
      try {
        findStatement = con.prepareStatement(findStatement());
        findStatement.setInt(1, ec1);
        findStatement.setInt(2, ec1);
        findStatement.setInt(3, ec2);
        findStatement.setInt(4, ec1);
        findStatement.setInt(5, ec2);
        findStatement.setInt(6, ec3);
        rs = findStatement.executeQuery();
        if (rs.next()) {
          result = doLoad(rs, entries);
        }
      } finally {
      	enzymeEntryMapper.close();
      	if (rs != null) rs.close();
        if (findStatement != null) findStatement.close();
      }
  
      return result;
    }
  
    /**
    * Tries to find all Sub-subclasses requested.
    *
    * @param ec1 Number/Wildcard of class to search for.
    * @param ec2 Number/Wildcard of subclass to search for.
    * @param con The logical connection.
    * @return a <code>Vector</code> of <code>SubSubClass</code> instances or <code>null</code> if nothing has been found.
    * @throws SQLException
    */
   public List<EnzymeSubSubclass> findList(String ec1, String ec2, Connection con)
   throws SQLException, DomainException {
     PreparedStatement findListStatement = null;
     ResultSet rs = null;
     List<EnzymeSubSubclass> result = new ArrayList<EnzymeSubSubclass>();
     boolean noResult = true;
 
     try {
       findListStatement = con.prepareStatement(findListStatement());
       findListStatement.setString(1, ec1);
       findListStatement.setString(2, ec1);
       findListStatement.setString(3, ec2);
       findListStatement.setString(4, ec1);
       findListStatement.setString(5, ec2);
       rs = findListStatement.executeQuery();
 
       while (rs.next()) {
         noResult = false;
         result.add(doLoad(rs, null));
       }
     } finally {
     	if (rs != null) rs.close();
       if (findListStatement != null) findListStatement.close();
     }
 
     if (noResult) return null;
     return result;
   }
   
   public List<EnzymeSubSubclass> findAll(Connection con) throws SQLException, DomainException{
       List<EnzymeSubSubclass> result = new ArrayList<EnzymeSubSubclass>();
       Statement stm = null;
       ResultSet rs = null;
       try {
           stm = con.createStatement();
           rs = stm.executeQuery(SELECT_ALL);
           while (rs.next()){
               result.add(doLoad(rs, null));
           }
       } finally {
       	if (rs != null) rs.close();
           if (stm != null) stm.close();
       }
       return result;
   }
 
   /**
    * Creates a new row in the <code>SUBSUBCLASSES</code> table.
    *
    * @param ec EC number of this sub-subclass.
    * @param name The name of the sub-subclass.
    * @param description The description of the sub-subclass.
    * @param con The database connection.
    * @throws SQLException if a database error occurs.
    * @throws NullPointerException if any of the parameters is <code>null</code>.
    */
   public synchronized void insertSubSubclass(EnzymeCommissionNumber ec, String name, String description, Connection con) throws SQLException {
     if (ec == null) throw new NullPointerException("Parameter 'ec' must not be null.");
     if (name == null) throw new NullPointerException("Parameter 'name' must not be null.");
     if (description == null) throw new NullPointerException("Parameter 'description' must not be null.");
     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
 
     PreparedStatement insertStatement = null;
     try {
       insertStatement = con.prepareStatement(insertStatement());
       doInsert(ec, name, description, insertStatement);
       insertStatement.execute();
 //      con.commit();
 //    } catch (SQLException e) {
 //      con.rollback();
 //      throw e;
     } finally {
       if (insertStatement != null) insertStatement.close();
     }
   }
 
   /**
    * Checks if the given sub-subclass numbers are valid.
    *
    * @param ec1 The class number.
    * @param ec2 The subclass number.
    * @param ec3 The sub-subclass number.
    * @param con The connection.
    * @return <code>true</code> if the class exists.
    * @throws SQLException
    */
   public boolean subSubclassExists(String ec1, String ec2, String ec3, Connection con) throws SQLException {
     PreparedStatement findStatement = null;
     ResultSet rs = null;
 
     try {
       findStatement = con.prepareStatement(findSubSubclassOnly());
       findStatement.setString(1, ec1);
       findStatement.setString(2, ec2);
       findStatement.setString(3, ec3);
       rs = findStatement.executeQuery();
       if (rs.next()) {
         return true;
       }
     } finally {
     	if (rs != null) rs.close();
       if (findStatement != null) findStatement.close();
     }
 
     return false;
   }
 
   
   // ------------------- PRIVATE METHODS ------------------------
 
   /**
    * Creates the <code>EnzymeSubSubclass</code> object from the given result set.
    *
    * @param rs The result set object.
    * @return an <code>EnzymeSubSubclass</code> instance.
    * @throws SQLException
    */
   private EnzymeSubSubclass doLoad(ResultSet rs, List<EnzymeEntry> entries)
   throws SQLException, DomainException {
     int ec1 = 0;
     int ec2 = 0;
     int ec3 = 0;
     String name = "";
     String subclassName = "";
     String className = "";
     String description = "";
 
     if (rs.getInt(1) > 0) ec1 = rs.getInt(1);
     if (rs.getInt(2) > 0) ec2 = rs.getInt(2);
     if (rs.getInt(3) > 0) ec3 = rs.getInt(3);
     if (rs.getString(4) != null) name = rs.getString(4);
     if (rs.getString(5) != null) subclassName = rs.getString(5);
     if (rs.getString(6) != null) className = rs.getString(6);
     if (rs.getString(7) != null) description = rs.getString(7);
 
     return new EnzymeSubSubclass(EnzymeCommissionNumber.valueOf(ec1, ec2, ec3),
                                  className, subclassName, name, description, entries);
   }
 
   private void doInsert(EnzymeCommissionNumber ec, String name, String description, PreparedStatement insertStatement) throws SQLException {
     assert ec != null : "Parameter 'ec' must not be null.";
     assert name != null : "Parameter 'name' must not be null.";
     assert description != null : "Parameter 'description' must not be null.";
     assert insertStatement != null : "Parameter 'insertStatement' must not be null.";
 
     insertStatement.setInt(1, ec.getEc1());
     insertStatement.setInt(2, ec.getEc2());
     insertStatement.setInt(3, ec.getEc3());
     insertStatement.setString(4, name);
     insertStatement.setString(5, description);
     insertStatement.setString(6, "Y");
   }
 }