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.DB;
  import com.act.lcms.MassCalculator;
  import com.act.utils.TSVParser;
  import org.apache.commons.lang3.StringUtils;
  import org.apache.commons.lang3.tuple.Pair;
  
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.sql.Types;
  import java.util.ArrayList;
  import java.util.Arrays;
  import java.util.Collections;
  import java.util.List;
  import java.util.Map;
  
  public class CuratedChemical {
    public static final String TABLE_NAME = "chemicals_curated";
  
    public static String getTableName() {
      return TABLE_NAME;
    }
  
    private enum DB_FIELD implements DBFieldEnumeration {
      ID(1, -1, "id"),
      NAME(2, 1, "name"),
      INCHI(3, 2, "inchi"),
      MASS(4, 3, "mass"),
      EXPECTED_COLLISION_VOLTAGE(5, 4, "expected_collision_voltage"),
      REFERENCE_URL(6, 5, "reference_url"),
      ;
  
      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;
      }
    }
    // TODO: it might be easier to use parts of Spring-Standalone to do named binding in these queries.
    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 =
        Collections.unmodifiableList(ALL_FIELDS.subList(1, ALL_FIELDS.size()));
  
    protected static List<CuratedChemical> fromResultSet(ResultSet resultSet) throws SQLException {
      List<CuratedChemical> results = new ArrayList<>();
      while (resultSet.next()) {
        Integer id = resultSet.getInt(DB_FIELD.ID.getOffset());
        String name = resultSet.getString(DB_FIELD.NAME.getOffset());
        String inchi = resultSet.getString(DB_FIELD.INCHI.getOffset());
        Double mass = resultSet.getDouble(DB_FIELD.MASS.getOffset());
        Integer expectedCollisionVoltage = resultSet.getInt(DB_FIELD.EXPECTED_COLLISION_VOLTAGE.getOffset());
        if (resultSet.wasNull()) {
          expectedCollisionVoltage = null;
        }
        String referenceUrl = resultSet.getString(DB_FIELD.REFERENCE_URL.getOffset());
  
        results.add(new CuratedChemical(id, name, inchi, mass, expectedCollisionVoltage, referenceUrl));
      }
  
      return results;
    }
  
    protected static CuratedChemical expectOneResult(ResultSet resultSet, String queryErrStr) throws SQLException{
      List<CuratedChemical> results = fromResultSet(resultSet);
      if (results.size() > 1) {
        throw new SQLException("Found multiple results where one or zero expected: %s", queryErrStr);
      }
      if (results.size() == 0) {
        return null;
      }
      return results.get(0);
    }
  
    // Select
    public static final String QUERY_GET_CURATED_CHEMICAL_BY_ID = StringUtils.join(new String[]{
        "SELECT", StringUtils.join(ALL_FIELDS, ", "),
        "from", TABLE_NAME,
        "where id = ?",
    }, " ");
    public static CuratedChemical getCuratedChemicalById(DB db, Integer id) throws SQLException {
      try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_CURATED_CHEMICAL_BY_ID)) {
        stmt.setInt(1, id);
        try (ResultSet resultSet = stmt.executeQuery()) {
          return expectOneResult(resultSet, String.format("id = %d", id));
        }
      }
    }
  
    public static final String QUERY_GET_CURATED_CHEMICAL_BY_NAME = StringUtils.join(new String[]{
        "SELECT", StringUtils.join(ALL_FIELDS, ", "),
        "from", TABLE_NAME,
        "where name = ?",
    }, " ");
    public static CuratedChemical getCuratedChemicalByName(DB db, String name) throws SQLException {
      try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_CURATED_CHEMICAL_BY_NAME)) {
        stmt.setString(1, name);
        try (ResultSet resultSet = stmt.executeQuery()) {
          return expectOneResult(resultSet, String.format("name = %s", name));
        }
      }
    }
  
    public static final String QUERY_GET_CURATED_CHEMICAL_BY_INCHI = StringUtils.join(new String[]{
        "SELECT", StringUtils.join(ALL_FIELDS, ", "),
        "from", TABLE_NAME,
        "where inchi = ?",
    }, " ");
    public static CuratedChemical getCuratedChemicalByInChI(DB db, String inchi) throws SQLException {
      try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_CURATED_CHEMICAL_BY_INCHI)) {
        stmt.setString(1, inchi);
        try (ResultSet resultSet = stmt.executeQuery()) {
          return expectOneResult(resultSet, String.format("inchi = %s", inchi));
        }
      }
    }
  
    // Insert/Update
    public static final String QUERY_INSERT_CURATED_CHEMICAL = StringUtils.join(new String[] {
        "INSERT INTO", TABLE_NAME, "(", StringUtils.join(INSERT_UPDATE_FIELDS, ", "), ") VALUES (",
        "?,", // 1 = name
        "?,", // 2 = inchi
        "?,", // 3 = m_plus_h_plus_mass
        "?,", // 4 = expected_collision_voltage
        "?", // 5 = reference_url
        ")"
    }, " ");
  
    protected static void bindInsertOrUpdateParameters(
        PreparedStatement stmt, String name, String inchi, Double mPlusHPlusMass,
        Integer expectedCollisionVoltage, String referenceUrl) throws SQLException {
      stmt.setString(DB_FIELD.NAME.getInsertUpdateOffset(), name);
      stmt.setString(DB_FIELD.INCHI.getInsertUpdateOffset(), inchi);
      stmt.setDouble(DB_FIELD.MASS.getInsertUpdateOffset(), mPlusHPlusMass);
      if (expectedCollisionVoltage != null) {
        stmt.setInt(DB_FIELD.EXPECTED_COLLISION_VOLTAGE.getInsertUpdateOffset(), expectedCollisionVoltage);
      } else {
        stmt.setNull(DB_FIELD.EXPECTED_COLLISION_VOLTAGE.getInsertUpdateOffset(), Types.INTEGER);
      }
      stmt.setString(DB_FIELD.REFERENCE_URL.getInsertUpdateOffset(), referenceUrl);
    }
  
    // TODO: this could return the number of parameters it bound to make it easier to set additional params.
    protected static void bindInsertOrUpdateParameters(PreparedStatement stmt, CuratedChemical c) throws SQLException {
      bindInsertOrUpdateParameters(stmt, c.getName(), c.getInchi(), c.getMass(),
          c.getExpectedCollisionVoltage(), c.getReferenceUrl());
    }
  
    public static CuratedChemical insertCuratedChemical(
        DB db,
        String name, String inchi, Double mPlusHPlusMass,
        Integer expectedCollisionVoltage, String referenceUrl) throws SQLException {
      Connection conn = db.getConn();
      try (PreparedStatement stmt =
               conn.prepareStatement(QUERY_INSERT_CURATED_CHEMICAL, Statement.RETURN_GENERATED_KEYS)) {
        bindInsertOrUpdateParameters(stmt, name, inchi, mPlusHPlusMass, expectedCollisionVoltage, referenceUrl);
        stmt.executeUpdate();
        try (ResultSet resultSet = stmt.getGeneratedKeys()) {
          if (resultSet.next()) {
            // Get auto-generated id.
            int id = resultSet.getInt(1);
            return new CuratedChemical(id, name, inchi, mPlusHPlusMass, expectedCollisionVoltage, referenceUrl);
          } else {
            System.err.format("ERROR: could not retrieve autogenerated key for curated chemical %s\n", name);
            return null;
          }
        }
      }
    }
  
    protected static final List<String> UPDATE_STATEMENT_FIELDS_AND_BINDINGS;
    static {
      List<String> fields = new ArrayList<>(INSERT_UPDATE_FIELDS.size());
      for (String field : INSERT_UPDATE_FIELDS) {
        fields.add(String.format("%s = ?", field));
      }
      UPDATE_STATEMENT_FIELDS_AND_BINDINGS = Collections.unmodifiableList(fields);
    }
    public static final String QUERY_UPDATE_CURATED_CHEMICAL_BY_ID = StringUtils.join(new String[] {
        "UPDATE", TABLE_NAME, "SET",
        StringUtils.join(UPDATE_STATEMENT_FIELDS_AND_BINDINGS.iterator(), ", "),
        "WHERE",
        "id = ?",
    }, " ");
    public static boolean updateCuratedChemical(DB db, CuratedChemical chem) throws SQLException {
      Connection conn = db.getConn();
      try (PreparedStatement stmt = conn.prepareStatement(QUERY_UPDATE_CURATED_CHEMICAL_BY_ID)) {
        bindInsertOrUpdateParameters(stmt, chem);
        stmt.setInt(INSERT_UPDATE_FIELDS.size() + 1, chem.getId());
        return stmt.executeUpdate() > 0;
      }
    }
  
    // Parsing/loading
    public static List<Pair<Integer, DB.OPERATION_PERFORMED>> insertOrUpdateCuratedChemicalsFromTSV(
        DB db, TSVParser parser) throws SQLException{
      List<Map<String, String>> entries = parser.getResults();
      List<Pair<Integer, DB.OPERATION_PERFORMED>> operationsPerformed = new ArrayList<>(entries.size());
      for (Map<String, String> entry : entries) {
        String name = entry.get("name");
        String inchi = entry.get("inchi");
        String massStr = entry.get("[M+H]+");
        String expectedCollisionVoltageStr = entry.get("collision_voltage");
        String referenceUrl = entry.get("reference_url");
        if (name == null || name.isEmpty() ||
            inchi == null || inchi.isEmpty() ||
            massStr == null || massStr.isEmpty()) {
          System.err.format("WARNING: missing required field for chemical '%s', skipping.\n", name);
          continue;
        }
  
        Double mass = Double.parseDouble(massStr);
        Integer expectedCollisionVoltage = expectedCollisionVoltageStr == null || expectedCollisionVoltageStr.isEmpty() ?
            null : Integer.parseInt(expectedCollisionVoltageStr);
  
        if (inchi != null && !inchi.isEmpty() && !"null".equals(inchi)) {
          Double calculatedMass = MassCalculator.calculateMass(inchi);
          Double delta = calculatedMass - mass;
          if (delta >= 0.01 || delta <= -0.01) {
            System.err.format("WARNING: found mass discrepancy for %s: %f found where %f calculated\n",
                name, mass, calculatedMass);
          }
        }
  
        // TODO: should we fall back to searching by name if we can't find the InChI?
        CuratedChemical chem = getCuratedChemicalByInChI(db, inchi);
        DB.OPERATION_PERFORMED op = null;
        if (chem == null) {
          chem = insertCuratedChemical(db, name, inchi, mass, expectedCollisionVoltage, referenceUrl);
          op = DB.OPERATION_PERFORMED.CREATE;
        } else {
          chem.setName(name);
          chem.setInchi(inchi);
          chem.setMass(mass);
          chem.setExpectedCollisionVoltage(expectedCollisionVoltage);
          chem.setReferenceUrl(referenceUrl);
          updateCuratedChemical(db, chem);
          op = DB.OPERATION_PERFORMED.UPDATE;
        }
  
        // Chem should only be null if we couldn't insert the row into the DB.
        if (chem == null) {
          operationsPerformed.add(Pair.of((Integer)null, DB.OPERATION_PERFORMED.ERROR));
        } else {
          operationsPerformed.add(Pair.of(chem.getId(), op));
        }
      }
      return operationsPerformed;
    }
  
  
    private Integer id;
    private String name;
    private String inchi;
    private Double mass;
    private Integer expectedCollisionVoltage;
    private String referenceUrl;
  
    public CuratedChemical(Integer id, String name, String inchi, Double mass,
                           Integer expectedCollisionVoltage, String referenceUrl) {
      this.id = id;
      this.name = name;
      this.inchi = inchi;
      this.mass = mass;
      this.expectedCollisionVoltage = expectedCollisionVoltage;
      this.referenceUrl = referenceUrl;
    }
  
    public Integer getId() {
      return id;
    }
  
    protected void setId(Integer id) {
      this.id = id;
    }
  
    public String getName() {
      return name;
    }
  
    public void setName(String name) {
      this.name = name;
    }
  
    public String getInchi() {
      return inchi;
    }
  
    public void setInchi(String inchi) {
      this.inchi = inchi;
    }
  
    public Double getMass() {
      return mass;
    }
  
    public void setMass(Double mass) {
      this.mass = mass;
    }
  
    public Integer getExpectedCollisionVoltage() {
      return expectedCollisionVoltage;
    }
  
    public void setExpectedCollisionVoltage(Integer expectedCollisionVoltage) {
      this.expectedCollisionVoltage = expectedCollisionVoltage;
    }
  
    public String getReferenceUrl() {
      return referenceUrl;
    }
  
    public void setReferenceUrl(String referenceUrl) {
      this.referenceUrl = referenceUrl;
    }
  
    // Generated by IntelliJ.
    @Override
    public boolean equals(Object o) {
      if (this == o) return true;
      if (o == null || getClass() != o.getClass()) return false;
  
      CuratedChemical that = (CuratedChemical) o;
  
      if (id != null ? !id.equals(that.id) : that.id != null) return false;
      if (!name.equals(that.name)) return false;
      if (!inchi.equals(that.inchi)) return false;
      if (!mass.equals(that.mass)) return false;
      if (expectedCollisionVoltage != null ? !expectedCollisionVoltage.equals(that.expectedCollisionVoltage) :
          that.expectedCollisionVoltage != null)
        return false;
      return !(referenceUrl != null ? !referenceUrl.equals(that.referenceUrl) : that.referenceUrl != null);
  
    }
  
    // Generated by IntelliJ.
    @Override
    public int hashCode() {
      int result = id != null ? id.hashCode() : 0;
      result = 31 * result + name.hashCode();
      result = 31 * result + inchi.hashCode();
      result = 31 * result + mass.hashCode();
      result = 31 * result + (expectedCollisionVoltage != null ? expectedCollisionVoltage.hashCode() : 0);
      result = 31 * result + (referenceUrl != null ? referenceUrl.hashCode() : 0);
      return result;
    }
  }
  