content: /*************************************************************************
  *                                                                        *
  *  This file is part of the 20n/act project.                             *
  *  20n/act enables DNA prediction for synthetic biology/bioengineering.  *
  *  Copyright (C) 2017 20n Labs, Inc.                                     *
  *                                                                        *
  *  Please direct all queries to act@20n.com.                             *
  *                                                                        *
  *  This program is free software: you can redistribute it and/or modify  *
  *  it under the terms of the GNU General Public License as published by  *
  *  the Free Software Foundation, either version 3 of the License, or     *
  *  (at your option) any later version.                                   *
  *                                                                        *
  *  This program is distributed in the hope that it will be useful,       *
  *  but WITHOUT ANY WARRANTY; without even the implied warranty of        *
  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
  *  GNU General Public License for more details.                          *
  *                                                                        *
  *  You should have received a copy of the GNU General Public License     *
  *  along with this program.  If not, see <http://www.gnu.org/licenses/>. *
  *                                                                        *
  *************************************************************************/
  
  package com.act.lcms.db.model;
  
  import com.act.lcms.db.io.parser.ConstructAnalysisFileParser;
  import com.act.lcms.db.io.DB;
  import org.apache.commons.lang3.StringUtils;
  import org.apache.commons.lang3.tuple.Pair;
  
  import java.io.IOException;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Types;
  import java.util.ArrayList;
  import java.util.Arrays;
  import java.util.Collections;
  import java.util.List;
  
  public class ChemicalAssociatedWithPathway extends BaseDBModel<ChemicalAssociatedWithPathway> {
    public static final String TABLE_NAME = "chemicals_associated_with_pathway";
    protected static final ChemicalAssociatedWithPathway INSTANCE = new ChemicalAssociatedWithPathway();
  
    public static ChemicalAssociatedWithPathway getInstance() {
      return INSTANCE;
    }
  
    private enum DB_FIELD implements DBFieldEnumeration {
      ID(1, -1, "id"),
      CONSTRUCT_ID(2, 1, "construct_id"),
      CHEMICAL(3, 2, "chemical"),
      KIND(4, 3, "kind"),
      INDEX(5, 4, "index"),
      ;
  
      private final int offset;
      private final int insertUpdateOffset;
      private final String fieldName;
  
      DB_FIELD(int offset, int insertUpdateOffset, String fieldName) {
        this.offset = offset;
        this.insertUpdateOffset = insertUpdateOffset;
        this.fieldName = fieldName;
      }
  
      @Override
      public int getOffset() {
        return offset;
      }
  
      @Override
      public int getInsertUpdateOffset() {
        return insertUpdateOffset;
      }
  
      @Override
      public String getFieldName() {
        return fieldName;
      }
  
      @Override
      public String toString() {
        return this.fieldName;
      }
  
      public static String[] names() {
        DB_FIELD[] values = DB_FIELD.values();
        String[] names = new String[values.length];
        for (int i = 0; i < values.length; i++) {
          names[i] = values[i].getFieldName();
        }
        return names;
      }
    }
  
    protected static final List<String> ALL_FIELDS = Collections.unmodifiableList(Arrays.asList(DB_FIELD.names()));
  
    // id is auto-generated on insertion.
    protected static final List<String> INSERT_UPDATE_FIELDS = INSTANCE.makeInsertUpdateFields();
  
  
    @Override
    public String getTableName() {
      return TABLE_NAME;
    }
  
    @Override
    public List<String> getAllFields() {
      return ALL_FIELDS;
    }
  
    @Override
    public List<String> getInsertUpdateFields() {
      return INSERT_UPDATE_FIELDS;
    }
  
    @Override
    protected List<ChemicalAssociatedWithPathway> fromResultSet(ResultSet resultSet) throws SQLException {
      List<ChemicalAssociatedWithPathway> results = new ArrayList<>();
      while (resultSet.next()) {
        Integer id = resultSet.getInt(DB_FIELD.INDEX.getOffset());
        String constructId = resultSet.getString(DB_FIELD.CONSTRUCT_ID.getOffset());
        String chemical = resultSet.getString(DB_FIELD.CHEMICAL.getOffset());
        String kind = resultSet.getString(DB_FIELD.KIND.getOffset());
        Integer index = resultSet.getInt(DB_FIELD.INDEX.getOffset());
        if (resultSet.wasNull()) {
          index = null;
        }
        results.add(new ChemicalAssociatedWithPathway(id, constructId, chemical, kind, index));
      }
  
      return results;
  
    }
  
    protected static final String GET_BY_ID_QUERY = INSTANCE.makeGetByIDQuery();
    @Override
    protected String getGetByIDQuery() {
      return GET_BY_ID_QUERY;
    }
  
    protected static final String INSERT_QUERY = INSTANCE.makeInsertQuery();
    @Override
    public String getInsertQuery() {
      return INSERT_QUERY;
    }
  
    protected static final String UPDATE_QUERY = INSTANCE.makeUpdateQuery();
    @Override
    public String getUpdateQuery() {
      return UPDATE_QUERY;
    }
  
    protected static final String QUERY_GET_CHEMICALS_ASSOCIATED_WITH_PATHWAY_BY_CONSTRUCT_ID =
        StringUtils.join(new String[]{
            "SELECT", StringUtils.join(INSTANCE.getAllFields(), ','),
            "from", INSTANCE.getTableName(),
            "where construct_id = ?",
            "order by index desc",
        }, " ");
    public List<ChemicalAssociatedWithPathway> getChemicalsAssociatedWithPathwayByConstructId(DB db, String constructId)
        throws SQLException {
      try (PreparedStatement stmt = db.getConn().prepareStatement(
          QUERY_GET_CHEMICALS_ASSOCIATED_WITH_PATHWAY_BY_CONSTRUCT_ID)) {
        stmt.setString(1, constructId);
        try (ResultSet resultSet = stmt.executeQuery()) {
          return fromResultSet(resultSet);
        }
      }
    }
  
    protected static final String QUERY_GET_CHEMICAL_ASSOCIATED_WITH_PATHWAY_BY_CONSTRUCT_ID_AND_INDEX =
        StringUtils.join(new String[]{
            "SELECT", StringUtils.join(INSTANCE.getAllFields(), ','),
            "from", INSTANCE.getTableName(),
            "where construct_id = ?",
            "  and index = ?",
        }, " ");
    public ChemicalAssociatedWithPathway getChemicalAssociatedWithPathwayByConstructIdAndIndex(
        DB db, String constructId, Integer index) throws SQLException, IOException, ClassNotFoundException {
      try (PreparedStatement stmt = db.getConn().prepareStatement(
          QUERY_GET_CHEMICAL_ASSOCIATED_WITH_PATHWAY_BY_CONSTRUCT_ID_AND_INDEX)) {
        stmt.setString(1, constructId);
        stmt.setInt(2, index);
        try (ResultSet resultSet = stmt.executeQuery()) {
          return expectOneResult(resultSet, String.format("construct_id = %s, index = %d", constructId, index));
        }
      }
    }
  
    protected void bindInsertOrUpdateParameters(
        PreparedStatement stmt, String constructId, String chemical, String kind, Integer index) throws SQLException {
      stmt.setString(DB_FIELD.CONSTRUCT_ID.getInsertUpdateOffset(), constructId);
      stmt.setString(DB_FIELD.CHEMICAL.getInsertUpdateOffset(), chemical);
      stmt.setString(DB_FIELD.KIND.getInsertUpdateOffset(), kind);
      if (index != null) {
        stmt.setInt(DB_FIELD.INDEX.getInsertUpdateOffset(), index);
      } else {
        stmt.setNull(DB_FIELD.INDEX.getInsertUpdateOffset(), Types.INTEGER);
      }
    }
  
    @Override
    protected void bindInsertOrUpdateParameters(PreparedStatement stmt, ChemicalAssociatedWithPathway parameterSource)
        throws SQLException {
      bindInsertOrUpdateParameters(stmt, parameterSource.getConstructId(), parameterSource.getChemical(),
          parameterSource.getKind(), parameterSource.getIndex());
    }
  
    // Parsing/Loading
    public static List<Pair<Integer, DB.OPERATION_PERFORMED>> insertOrUpdateChemicalsAssociatedWithPathwayFromParser(
        DB db, ConstructAnalysisFileParser parser) throws SQLException, IOException, ClassNotFoundException {
      List<Pair<Integer, DB.OPERATION_PERFORMED>> operationsPerformed = new ArrayList<>();
      List<Pair<String, List<ConstructAnalysisFileParser.ConstructAssociatedChemical>>> stepPairs =
          parser.getConstructProducts();
      for (Pair<String, List<ConstructAnalysisFileParser.ConstructAssociatedChemical>> stepPair : stepPairs) {
        String constructId = stepPair.getLeft();
        for (ConstructAnalysisFileParser.ConstructAssociatedChemical step : stepPair.getRight()) {
          System.out.format("Processing entry %s %s %s %d\n", constructId, step.getChemical(),
              step.getKind(), step.getIndex());
          ChemicalAssociatedWithPathway cp =
              INSTANCE.getChemicalAssociatedWithPathwayByConstructIdAndIndex(db, constructId, step.getIndex());
  
          DB.OPERATION_PERFORMED op = null;
          if (cp == null) {
            cp = INSTANCE.insert(db, new ChemicalAssociatedWithPathway(
                null, constructId, step.getChemical(), step.getKind(), step.getIndex()));
            op = DB.OPERATION_PERFORMED.CREATE;
          } else {
            cp.setConstructId(constructId);
            cp.setChemical(step.getChemical());
            cp.setKind(step.getKind());
            cp.setIndex(step.getIndex());
            INSTANCE.update(db, cp);
            op = DB.OPERATION_PERFORMED.UPDATE;
          }
  
          // Chem should only be null if we couldn't insert the row into the DB.
          if (cp == null) {
            operationsPerformed.add(Pair.of((Integer)null, DB.OPERATION_PERFORMED.ERROR));
          } else {
            operationsPerformed.add(Pair.of(cp.getId(), op));
          }
        }
      }
      return operationsPerformed;
    }
  
    private String constructId;
    private String chemical;
    private String kind;
    private Integer index;
  
    private ChemicalAssociatedWithPathway() { }
  
    protected ChemicalAssociatedWithPathway(Integer id, String constructId, String chemical, String kind, Integer index) {
      this.id = id;
      this.constructId = constructId;
      this.chemical = chemical;
      this.kind = kind;
      this.index = index;
    }
  
    public String getConstructId() {
      return constructId;
    }
  
    public void setConstructId(String constructId) {
      this.constructId = constructId;
    }
  
    public String getChemical() {
      return chemical;
    }
  
    public void setChemical(String chemical) {
      this.chemical = chemical;
    }
  
    public String getKind() {
      return kind;
    }
  
    public void setKind(String kind) {
      this.kind = kind;
    }
  
    public Integer getIndex() {
      return index;
    }
  
    public void setIndex(Integer index) {
      this.index = index;
    }
  }
  