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 org.apache.commons.lang3.StringUtils;
  
  import java.io.IOException;
  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.Collections;
  import java.util.List;
  
  public abstract class BaseDBModel<T extends BaseDBModel> {
  
    // These should be static, but static and abstract are incompatible! :(
    public abstract String getTableName();
    public abstract List<String> getAllFields();
    public abstract List<String> getInsertUpdateFields();
    protected abstract List<T> fromResultSet(ResultSet resultSet) throws SQLException, IOException, ClassNotFoundException;
  
    protected List<String> makeInsertUpdateFields() {
      List<String> allFields = getAllFields();
      return Collections.unmodifiableList(allFields.subList(1, allFields.size()));
    }
  
    protected String makeGetByIDQuery() {
      return StringUtils.join(new String[]{
          "SELECT", StringUtils.join(getAllFields(), ','),
          "from", getTableName(),
          "where id = ?",
      }, " ");
    }
    protected abstract String getGetByIDQuery();
    public T getById(DB db, Integer id) throws SQLException, IOException, ClassNotFoundException {
      try (PreparedStatement stmt = db.getConn().prepareStatement(getGetByIDQuery())) {
        stmt.setInt(1, id);
        try (ResultSet resultSet = stmt.executeQuery()) {
          return expectOneResult(resultSet, String.format("id = %d", id));
        }
      }
    }
  
    /**
     * Important: use this only when building constants to avoid the risk of SQL injection attacks.
     * @param field The field to select.
     * @return A query that binds a value to the specified field in a `WHERE` clause .
     */
    protected String makeGetQueryForSelectField(String field) {
      return StringUtils.join(new String[] {
          "SELECT", StringUtils.join(this.getAllFields(), ','),
          "from", this.getTableName(),
          String.format("where %s = ?", field),
      }, " ");
    }
  
    public abstract String getInsertQuery();
    protected String makeInsertQuery() {
      List<String> parameters = new ArrayList<>(getInsertUpdateFields().size());
      for (String field : getInsertUpdateFields()) {
        parameters.add("?");
      }
      return StringUtils.join(new String[] {
          "INSERT INTO", getTableName(), "(", StringUtils.join(getInsertUpdateFields(), ", "), ") VALUES (",
          StringUtils.join(parameters, ", "),
          ")",
      }, " ");
    }
  
  
    public abstract String getUpdateQuery();
    protected String makeUpdateQuery() {
      List<String> parameters = new ArrayList<>(getInsertUpdateFields().size());
      for (String field : getInsertUpdateFields()) {
        parameters.add(String.format("%s = ?", field));
      }
      return StringUtils.join(new String[] {
          "UPDATE", getTableName(), "SET",
          StringUtils.join(parameters, ", "),
          "WHERE",
          "id = ?",
      }, " ");
    }
  
    protected abstract void bindInsertOrUpdateParameters(PreparedStatement stmt, T parameterSource)
        throws SQLException, IOException;
    protected T insert(DB db, T toInsert) throws SQLException, IOException {
      return insert(db, toInsert, null);
    }
  
    protected T insert(DB db, T toInsert, String errMsg) throws SQLException, IOException {
      Connection conn = db.getConn();
      try (PreparedStatement stmt = conn.prepareStatement(getInsertQuery(), Statement.RETURN_GENERATED_KEYS)) {
        bindInsertOrUpdateParameters(stmt, toInsert);
        stmt.executeUpdate();
        try (ResultSet resultSet = stmt.getGeneratedKeys()) {
          if (resultSet.next()) {
            // Get auto-generated id.
            int id = resultSet.getInt(1);
            toInsert.setId(id);
            return toInsert;
          } else {
            // TODO: log error here.
            if (errMsg != null) {
              System.err.format("ERROR: %s\n", errMsg);
            } else {
              System.err.format("ERROR: could not retrieve autogenerated key for inserted row\n");
            }
            return null;
          }
        }
      }
    }
  
    public boolean update(DB db, T toUpdate) throws SQLException, IOException {
      Connection conn = db.getConn();
      try (PreparedStatement stmt = conn.prepareStatement(getUpdateQuery())) {
        bindInsertOrUpdateParameters(stmt, toUpdate);
        stmt.setInt(getInsertUpdateFields().size() + 1, toUpdate.getId());
        return stmt.executeUpdate() > 0;
      }
    }
  
    protected T expectOneResult(ResultSet resultSet, String queryErrStr) throws SQLException, IOException, ClassNotFoundException {
      List<T> results = this.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);
    }
  
    /* Use protected rather than private, as this class represents common attributes of all plate wells.  Extending
     * classes can/should have access to its fields, as its existence is simply an organizational convenience. */
    protected Integer id;
  
    public Integer getId() {
      return id;
    }
  
    protected void setId(Integer id) {
      this.id = id;
    }
  }
  