package util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author sidereus
 */
public class DBconnection implements AutoCloseable {

  private Connection trucarbonData = null;


  public DBconnection(Connection trucarbonData) {
    connect(trucarbonData);
  }


  private synchronized void connect(Connection trucarbonData) {
    if (this.trucarbonData == null) {
      this.trucarbonData = trucarbonData;
    }
  }


  public String getTillageCode(String operation) {
    try {
      Statement stmt = trucarbonData.createStatement();
      String op = operation.toLowerCase();
      String query = "SELECT cult100_code FROM d_tc_tillage_type WHERE tillage_type_name LIKE \'%" + operation + "%\'";
      ResultSet result = stmt.executeQuery(query);
      if (result.next()) {
        return result.getString("cult100_code");
      } else {
        throw new RuntimeException("Error from DB. Operation " + op);
      }
    } catch (SQLException ex) {
      throw new RuntimeException(ex.getMessage());
    }
  }


  public String getCropTermination(String cropid100) throws SQLException {
    String cult100 = null;
    Statement stmt = trucarbonData.createStatement();
    String query = "SELECT harv100_code FROM d_tc_crop WHERE crop100_code LIKE \'%" + cropid100 + "%\'";
    ResultSet result = stmt.executeQuery(query);
    if (result.next()) {
      return result.getString("harv100_code");
    } else {
      throw new RuntimeException("Error from DB. Operation id harv 100: " + cult100);
    }
  }


  public String getCoverCropTermination(String method) throws SQLException {
    Statement stmt = trucarbonData.createStatement();
    String query = "SELECT cult100_code FROM d_tc_tillage_type WHERE tillage_type_name LIKE \'%" + method.toLowerCase() + "%\'";
    ResultSet result = stmt.executeQuery(query);
    if (result.next()) {
      return result.getString("cult100_code");
    } else {
      throw new RuntimeException("Error from DB. Operation id cult 100: " + method);
    }
  }


  public String getCrop100Code(int tt_cropid) throws SQLException {

    String crop100id = null;
    if (tt_cropid != -99) {
      Statement stmt = trucarbonData.createStatement();
      String query = "SELECT crop100_code,crop_name FROM d_tc_crop WHERE tt_crop_id = " + tt_cropid;
      try ( ResultSet result = stmt.executeQuery(query)) {
        if (result.next()) {
          crop100id = result.getString("crop100_code");
        } else {
          throw new RuntimeException("Error from DB: TT crop " + tt_cropid + " DB crop " + crop100id);
        }
      }
    }

    return crop100id;
  }


  public int getTTcropID(String crop_name) throws SQLException {

    Integer ttCropID = null;
    Statement stmt = trucarbonData.createStatement();
    String query = "SELECT tt_crop_id FROM d_tc_crop WHERE crlmod_crop_name LIKE  '" + crop_name + "' ";
    try ( ResultSet result = stmt.executeQuery(query)) {
      if (result.next()) {
        ttCropID = result.getInt("tt_crop_id");
      } else {
        throw new RuntimeException("Error from DB: TT crop " + crop_name + " not available in db");
      }
    }

    return ttCropID;
  }
  
  public int getCRLMODcropID(String crop_name) throws SQLException {

    Integer crlmodCropID = null;
    Statement stmt = trucarbonData.createStatement();
    String query = "SELECT crlmod_crop_id FROM d_tc_crop WHERE crlmod_crop_name LIKE  '" + crop_name + "' ";
    try ( ResultSet result = stmt.executeQuery(query)) {
      if (result.next()) {
        crlmodCropID = result.getInt("crlmod_crop_id");
      } else {
        throw new RuntimeException("Error from DB: TT crop " + crop_name + " not available in db");
      }
    }

    return crlmodCropID;
  }


  public double getNH4fertilizer(int fert_product_id) throws SQLException {
    double value = -99;
    Statement stmt = trucarbonData.createStatement();
    String query = "SELECT nh4_portion FROM d_tc_fertilizer WHERE fertilizer_id = " + fert_product_id;
    ResultSet result = stmt.executeQuery(query);
    if (result.next()) {
      value = result.getDouble("nh4_portion");
      if (value > 1.0) {
        throw new RuntimeException("NH4 bigger than 1");
      }
      if (value == Double.NaN) {
        throw new RuntimeException("NH4 NaN: " + value);
      }
    } else {
      throw new RuntimeException("Error from DB. Fert product id: " + fert_product_id);
    }
    return value;
  }


  public String getOMADname(int source_id, int type_id) throws SQLException {
    String fullName;
    Statement stmt = trucarbonData.createStatement();
    String query = "SELECT omad100_code FROM d_tc_om_addition WHERE manure_source_id = " + source_id + " AND manure_type_id = " + type_id;
    ResultSet result = stmt.executeQuery(query);
    if (result.next()) {
      fullName = result.getString("omad100_code");
    } else {
      throw new RuntimeException("Error from DB. Manure_source_id: " + source_id + " manure_type_id = " + type_id);
    }
    return fullName;
  }


  @Override
  public void close() throws Exception {
    if (!trucarbonData.isClosed()) {
      trucarbonData.close();
    }
  }

}