/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package database.ipat;

import csip.ServiceException;
import csip.SessionLogger;
import csip.annotations.Resource;
import csip.utils.Binaries;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import m.ipat.PowerPlant;
import static database.DBResources.CRDB;

/**
 *
 * @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
 */
@Resource(from = database.DBResources.class)
public class IPAT_Db implements AutoCloseable {

    public static final int FLOW_METER = 1;
    public static final int IRRIGATION_SCHEDULING = 2;
    public static final int MAINTENANCE_UPGRADES = 3;

    protected SessionLogger LOG;
    protected Connection conn;

    public IPAT_Db(Class<?> c, SessionLogger log) throws SQLException, ServiceException {
        LOG = log;

        conn = Binaries.getRessourceJDBC(c, CRDB, log);
        validate();
    }

    public final void validate() throws SQLException {
        if ((null != conn) && (!conn.getMetaData().getDatabaseProductName().contains("Microsoft SQL"))) {
            throw new SQLException("Invalid connection type passed to IPAT_Db.  Class requires a connection to a MsSQL server.");
        }

        String query = "SELECT name FROM sys.schemas WHERE name='irrigation';";
        try (Statement statement = conn.createStatement();) {
            ResultSet results = statement.executeQuery(query);

            if (!results.next()) {
                throw new SQLException("Invalid connection type passed to IPAT_Db.  Class requires a connection to a MsSQL server that contains the irrigation schema.");
            }
        } catch (SQLException ex) {
            throw new SQLException("Invalid connection type passed to IPAT_Db. SQL Error: " + ex.getMessage(), ex);
        }
    }

    public int getCropId(int cr_lmod_id) throws ServiceException {
        try (Statement statement = conn.createStatement()) {
            String query = "SELECT crop_id FROM irrigation.d_crop2crlmod_link WHERE crlmod_id=" + cr_lmod_id + ";";
            ResultSet results = statement.executeQuery(query);
            if (results.next()) {
                return results.getInt("crop_id");
            } else {
                throw new ServiceException("Cannot translate that cr_lmod crop id, " + cr_lmod_id + ", into an irrigation crop id, id not found.");
            }
        } catch (SQLException ex) {
            throw new ServiceException("Cannot translate that cr_lmod crop id into an irrigation crop id: " + ex.getMessage(), ex);
        }
    }

    public PowerPlant getPowerSource(int value) throws ServiceException {
        PowerPlant powerPlant;

        try (Statement statement = conn.createStatement()) {
            String query = "SELECT  id, name, energy_units, pp_eval_gain, hp_hr_per_energy_unit, irr_power_cost_max, irr_power_cost_min FROM irrigation.d_irrigation_power WHERE id=" + value;
            ResultSet resultSet = statement.executeQuery(query);
            if (resultSet.next()) {
                powerPlant = new PowerPlant();
                powerPlant.id = value;
                powerPlant.name = resultSet.getString("name");
                powerPlant.units = resultSet.getString("energy_units");
                powerPlant.eval_gain = resultSet.getDouble("pp_eval_gain");
                powerPlant.hp_hr_per_energy_unit = resultSet.getDouble("hp_hr_per_energy_unit");
                powerPlant.cost_max = resultSet.getInt("irr_power_cost_max");
                powerPlant.cost_min = resultSet.getInt("irr_power_cost_min");
            } else {
                throw new ServiceException("The power plant type id, " + value + ", could not be located in the database.");
            }
        } catch (SQLException ex) {
            throw new ServiceException("Cannot validate the power plant type with the database: " + ex.getMessage(), ex);
        }

        return powerPlant;
    }

    public void validateSystemPressure(int value) throws ServiceException {

    }

    public void validateEnergyCost(double value) throws ServiceException {

    }

    public double irrigationEfficiency(int value) throws ServiceException {
        double ret_val;

        try (Statement statement = conn.createStatement()) {
            String query = "SELECT efficiency FROM irrigation.d_irr_system_type WHERE id=" + value;
            ResultSet resultSet = statement.executeQuery(query);
            if (resultSet.next()) {
                ret_val = resultSet.getDouble("efficiency");
            } else {
                throw new ServiceException("The irrigation type id, " + value + ", could not be located in the database.");
            }
        } catch (SQLException ex) {
            throw new ServiceException("Cannot validate the irrigation type with the database: " + ex.getMessage(), ex);
        }

        return ret_val;
    }

    public double getFlowMeterEfficiency(boolean value) throws ServiceException {
        if (value) {
            return getTreatmentEfficiency(FLOW_METER);
        }

        return 0.0;
    }

    public double getIrrigScheduleEfficiency(boolean value) throws ServiceException {
        if (value) {
            return getTreatmentEfficiency(IRRIGATION_SCHEDULING);
        }
        return 0.0;
    }

    public double getMtnUpgradeEfficiency(boolean value) throws ServiceException {
        if (value) {
            return getTreatmentEfficiency(MAINTENANCE_UPGRADES);
        }

        return 0.0;
    }

    public int getWellLift(String stateCode) throws ServiceException {
        int ret_val;

        try (Statement statement = conn.createStatement()) {
            String query = "SELECT depth FROM irrigation.d_state_well_lift WHERE fips='" + stateCode + "';";
            ResultSet resultSet = statement.executeQuery(query);
            if (resultSet.next()) {
                ret_val = resultSet.getInt("depth");
            } else {
                throw new ServiceException("The state well lift fips code, " + stateCode + ", could not be located in the database.");
            }
        } catch (SQLException ex) {
            throw new ServiceException("Cannot validate the state well lift with the database: " + ex.getMessage(), ex);
        }

        return ret_val;
    }

    protected double getTreatmentEfficiency(int value) throws ServiceException {
        double ret_val;

        try (Statement statement = conn.createStatement()) {
            String query = "SELECT efficiency FROM irrigation.d_treatment_efficiency WHERE id=" + value;
            ResultSet resultSet = statement.executeQuery(query);
            if (resultSet.next()) {
                ret_val = resultSet.getDouble("efficiency");
            } else {
                throw new ServiceException("The treatment type id, " + value + ", could not be located in the database.");
            }
        } catch (SQLException ex) {
            throw new ServiceException("Cannot validate the treatment type with the database: " + ex.getMessage(), ex);
        }

        return ret_val;
    }

    @Override
    public void close() throws SQLException {
        if (null != conn) {
            conn.close();
            conn = null;
        }
    }

}