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.db.io.parser.PlateCompositionParser;
  import org.apache.commons.lang3.StringUtils;
  
  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 Plate {
    public static final String TABLE_NAME = "plates";
  
    public enum CONTENT_TYPE {
      LCMS,
      STANDARD,
      DELIVERED_STRAIN,
      INDUCTION,
      PREGROWTH,
      FEEDING_LCMS,
    }
  
    private enum DB_FIELD implements DBFieldEnumeration {
      ID(1, -1, "id"),
      NAME(2, 1, "name"),
      DESCRIPTION(3, 2, "description"),
      BARCODE(4, 3, "barcode"),
      LOCATION(5, 4, "location"),
      PLATE_TYPE(6, 5, "plate_type"),
      SOLVENT(7, 6, "solvent"),
      TEMPERATURE(8, 7, "temperature"),
      CONTENT_TYPE(9, 8, "content_type")
  
      // proteins and ko_locus are ignored for now.
      ;
  
      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<Plate> platesFromResultSet(ResultSet resultSet) throws SQLException {
      List<Plate> results = new ArrayList<>();
      while (resultSet.next()) {
        Integer id = resultSet.getInt(DB_FIELD.ID.getOffset());
        String name = resultSet.getString(DB_FIELD.NAME.getOffset());
        String description = resultSet.getString(DB_FIELD.DESCRIPTION.getOffset());
        String barcode = resultSet.getString(DB_FIELD.BARCODE.getOffset());
        String location = resultSet.getString(DB_FIELD.LOCATION.getOffset());
        String plateType = resultSet.getString(DB_FIELD.PLATE_TYPE.getOffset());
        String solvent = resultSet.getString(DB_FIELD.SOLVENT.getOffset());
        Integer temperature = resultSet.getInt(DB_FIELD.TEMPERATURE.getOffset());
        if (resultSet.wasNull()) {
          temperature = null;
        }
        CONTENT_TYPE contentType = CONTENT_TYPE.valueOf(resultSet.getString(DB_FIELD.CONTENT_TYPE.getOffset()));
  
        results.add(new Plate(id, name, description, barcode, location, plateType, solvent, temperature, contentType));
      }
      return results;
    }
  
    protected static Plate expectOneResult(ResultSet resultSet, String queryErrStr) throws SQLException{
      List<Plate> results = platesFromResultSet(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_PLATE_BY_ID = StringUtils.join(new String[]{
        "SELECT", StringUtils.join(ALL_FIELDS, ", "),
        "from", TABLE_NAME,
        "where id = ?",
    }, " ");
    public static Plate getPlateById(DB db, Integer id) throws SQLException {
      try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_PLATE_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_PLATE_BY_BARCODE = StringUtils.join(new String[]{
        "SELECT", StringUtils.join(ALL_FIELDS, ", "),
        "from", TABLE_NAME,
        "where barcode = ?",
    }, " ");
    public static Plate getPlateByBarcode(DB db, String barcode) throws SQLException {
      try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_PLATE_BY_BARCODE)) {
        stmt.setString(1, barcode);
        try (ResultSet resultSet = stmt.executeQuery()) {
          return expectOneResult(resultSet, String.format("barcode = %s", barcode));
        }
      }
    }
  
    public static final String QUERY_GET_PLATE_BY_NAME = StringUtils.join(new String[]{
        "SELECT", StringUtils.join(ALL_FIELDS, ", "),
        "from", TABLE_NAME,
        "where name = ?",
    }, " ");
    public static Plate getPlateByName(DB db, String name) throws SQLException {
      try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_PLATE_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_ALL_PLATES_BY_TYPE = StringUtils.join(new String[]{
        "SELECT", StringUtils.join(ALL_FIELDS, ", "),
        "from", TABLE_NAME,
        "where", DB_FIELD.PLATE_TYPE.getFieldName(), "= ?",
        "order by barcode, id"
    }, " ");
    public static List<Plate> getPlatesByContentType(DB db, CONTENT_TYPE type) throws SQLException {
      try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_ALL_PLATES_BY_TYPE)) {
        stmt.setString(1, type.name());
        try (ResultSet resultSet = stmt.executeQuery()) {
          return platesFromResultSet(resultSet);
        }
      }
    }
  
    // Insert/Update
    public static final String QUERY_INSERT_PLATE = StringUtils.join(new String[] {
        "INSERT INTO", TABLE_NAME, "(", StringUtils.join(INSERT_UPDATE_FIELDS, ", "), ") VALUES (",
        "?,", // 1 = name
        "?,", // 2 = description
        "?,", // 3 = barcode
        "?,", // 4 = location
        "?,", // 5 = plate_type
        "?,", // 6 = solvent
        "?,", // 7 = temperature
        "?",  // 8 = contentType
        ")"
    }, " ");
  
    protected static void bindInsertOrUpdateParameters(
        PreparedStatement stmt, String name, String description, String barcode, String location,
        String plateType, String solvent, Integer temperature, CONTENT_TYPE contentType) throws SQLException {
      stmt.setString(DB_FIELD.NAME.getInsertUpdateOffset(), trimAndComplain(name));
      if (description != null) {
        stmt.setString(DB_FIELD.DESCRIPTION.getInsertUpdateOffset(), trimAndComplain(description));
      } else {
        stmt.setNull(DB_FIELD.DESCRIPTION.getInsertUpdateOffset(), Types.VARCHAR);
      }
      if (barcode != null) {
        stmt.setString(DB_FIELD.BARCODE.getInsertUpdateOffset(), trimAndComplain(barcode));
      } else {
        stmt.setNull(DB_FIELD.BARCODE.getInsertUpdateOffset(), Types.VARCHAR);
      }
      stmt.setString(DB_FIELD.LOCATION.getInsertUpdateOffset(), trimAndComplain(location));
      stmt.setString(DB_FIELD.PLATE_TYPE.getInsertUpdateOffset(), trimAndComplain(plateType));
      if (solvent != null) {
        stmt.setString(DB_FIELD.SOLVENT.getInsertUpdateOffset(), trimAndComplain(solvent));
      } else {
        stmt.setNull(DB_FIELD.SOLVENT.getInsertUpdateOffset(), Types.VARCHAR);
      }
      if (temperature == null) {
        stmt.setNull(DB_FIELD.TEMPERATURE.getInsertUpdateOffset(), Types.INTEGER);
      } else {
        stmt.setInt(DB_FIELD.TEMPERATURE.getInsertUpdateOffset(), temperature);
      }
      stmt.setString(DB_FIELD.CONTENT_TYPE.getInsertUpdateOffset(), contentType.name());
    }
  
    protected static void bindInsertOrUpdateParameters(PreparedStatement stmt, Plate plate) throws SQLException {
      bindInsertOrUpdateParameters(stmt, plate.getName(), plate.getDescription(), plate.getBarcode(),
          plate.getLocation(), plate.getPlateType(), plate.getSolvent(), plate.getTemperature(), plate.getContentType());
    }
  
    public static Plate insertPlate(DB db,
                                    String name, String description, String barcode, String location, String plateType,
                                    String solvent, Integer temperature, CONTENT_TYPE contentType) throws SQLException {
      Connection conn = db.getConn();
      try (PreparedStatement stmt = conn.prepareStatement(QUERY_INSERT_PLATE, Statement.RETURN_GENERATED_KEYS)) {
        bindInsertOrUpdateParameters(stmt, name, description, barcode, location,
            plateType, solvent, temperature, contentType);
        stmt.executeUpdate();
        try (ResultSet resultSet = stmt.getGeneratedKeys()) {
          if (resultSet.next()) {
            // Get auto-generated id.
            int id = resultSet.getInt(1);
            return new Plate(id, name, description, barcode, location, plateType, solvent, temperature, contentType);
          } else {
            System.err.format("ERROR: could not retrieve autogenerated key for plate %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_PLATE_BY_ID = StringUtils.join(new String[] {
        "UPDATE ", TABLE_NAME, "SET",
        StringUtils.join(UPDATE_STATEMENT_FIELDS_AND_BINDINGS.iterator(), ", "),
        "WHERE",
        "id = ?", // 7
    }, " ");
    public static boolean updatePlate(DB db, Plate plate) throws SQLException {
      Connection conn = db.getConn();
      try (PreparedStatement stmt = conn.prepareStatement(QUERY_UPDATE_PLATE_BY_ID)) {
        bindInsertOrUpdateParameters(stmt, plate);
        stmt.setInt(UPDATE_STATEMENT_FIELDS_AND_BINDINGS.size() + 1, plate.getId());
        return stmt.executeUpdate() > 0;
      }
    }
  
    public static Plate getOrInsertFromPlateComposition(DB db, PlateCompositionParser parser, CONTENT_TYPE contentType)
        throws SQLException {
      Plate p = Plate.getPlateByName(db, parser.getPlateProperties().get("name"));
      if (p == null) {
        Map<String, String> attrs = parser.getPlateProperties();
        // TODO: check for errors and make these proper constants.
        String tempStr = attrs.get("temperature");
        if (tempStr == null || tempStr.isEmpty()) {
          tempStr = attrs.get("storage");
        }
        Integer temperature = tempStr == null || tempStr.isEmpty() ? null : Integer.parseInt(trimAndComplain(tempStr));
        p = Plate.insertPlate(db, attrs.get("name"), attrs.get("description"), attrs.get("barcode"),
            attrs.get("location"), attrs.get("plate_type"), attrs.get("solvent"), temperature, contentType);
      }
      return p;
    }
  
    public static String trimAndComplain(String val) {
      String tval = val.trim();
      if (!val.equals(tval)) {
        System.err.format("WARNING: trimmed spurious whitespace from '%s'\n", val);
      }
      return tval;
    }
  
    // Class Definition
    private Integer id;
    private String name;
    private String description;
    private String barcode;
    private String location;
    private String plateType;
    private String solvent;
    private Integer temperature;
    private CONTENT_TYPE contentType;
  
    protected Plate(Integer id, String name, String description, String barcode, String location,
                    String plateType, String solvent, Integer temperature, CONTENT_TYPE contentType) {
      this.id = id;
      this.name = name;
      this.description = description;
      this.barcode = barcode;
      this.location = location;
      this.plateType = plateType;
      this.solvent = solvent;
      this.temperature = temperature;
      this.contentType = contentType;
    }
  
    public Integer getId() {
      return id;
    }
  
    public String getName() {
      return name;
    }
  
    public void setName(String name) {
      this.name = name;
    }
  
    public String getDescription() {
      return description;
    }
  
    public void setDescription(String description) {
      this.description = description;
    }
  
    public String getBarcode() {
      return barcode;
    }
  
    public void setBarcode(String barcode) {
      this.barcode = barcode;
    }
  
    public String getLocation() {
      return location;
    }
  
    public void setLocation(String location) {
      this.location = location;
    }
  
    public String getPlateType() {
      return plateType;
    }
  
    public void setPlateType(String plateType) {
      this.plateType = plateType;
    }
  
    public String getSolvent() {
      return solvent;
    }
  
    public void setSolvent(String solvent) {
      this.solvent = solvent;
    }
  
    public Integer getTemperature() {
      return temperature;
    }
  
    public void setTemperature(Integer temperature) {
      this.temperature = temperature;
    }
  
    public CONTENT_TYPE getContentType() {
      return contentType;
    }
  
    public void setContentType(CONTENT_TYPE contentType) {
      this.contentType = contentType;
    }
  
    @Override
    public boolean equals(Object o) {
      if (this == o) return true;
      if (o == null || getClass() != o.getClass()) return false;
  
      Plate plate = (Plate) o;
  
      if (!id.equals(plate.id)) return false;
      if (!name.equals(plate.name)) return false;
      if (description != null ? !description.equals(plate.description) : plate.description != null) return false;
      if (barcode != null ? !barcode.equals(plate.barcode) : plate.barcode != null) return false;
      if (!location.equals(plate.location)) return false;
      if (!plateType.equals(plate.plateType)) return false;
      if (solvent != null ? !solvent.equals(plate.solvent) : plate.solvent != null) return false;
      if (temperature != null ? !temperature.equals(plate.temperature) : plate.temperature != null) return false;
      return contentType == plate.contentType;
  
    }
  
    @Override
    public int hashCode() {
      int result = id.hashCode();
      result = 31 * result + name.hashCode();
      result = 31 * result + (description != null ? description.hashCode() : 0);
      result = 31 * result + (barcode != null ? barcode.hashCode() : 0);
      result = 31 * result + location.hashCode();
      result = 31 * result + plateType.hashCode();
      result = 31 * result + (solvent != null ? solvent.hashCode() : 0);
      result = 31 * result + (temperature != null ? temperature.hashCode() : 0);
      result = 31 * result + contentType.hashCode();
      return result;
    }
  }
  