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

/**
 * The class SQLController provides methods to get access to the MySQL database via the installed JDBC connector. With its functions, you can
 * get FractalData objects by ID, check und modify DATETIME-flags or probably repair inconsistent data.
 */

public class SQLController {

    private Connection connection = null;
    private String sqlJdbcUrl;
    private String sqlUser;
    private String sqlPass;
    /**
     * Constructor
     * sets the private variables to the right values
     */
    SQLController(String sqlJdbcUrl, String sqlUser, String sqlPass) {
        this.sqlJdbcUrl = sqlJdbcUrl;
        this.sqlUser = sqlUser;
        this.sqlPass = sqlPass;

    }

    /**
     * Destructor
     * just for security, that the connection is def. closed
     */
    protected void finalize() throws Throwable {
        this.connectionClose();
    }

    /**
     * Internal function to build up connection to the JDBC adapter
     */
    public void connectionOpen() {
        try {
            Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            this.connection = DriverManager.getConnection (this.sqlJdbcUrl, this.sqlUser, this.sqlPass);

        } catch (ClassNotFoundException e) {
            ProtocolHandler.fatal("Klasse nicht gefunden", e);
        } catch (SQLException e) {
            ProtocolHandler.error("SQL Fehler", e);
        } catch (Exception e) {
            ProtocolHandler.error("MySQL - Verbindung konnte nicht hergestellt werden", e);
        }
    }


    /**
     * Internal function to close connection to the JDBC adapter
     */
    public void connectionClose() {
        try {
            if (this.connection != null) this.connection.close();
        } catch (Exception e) {
            ProtocolHandler.error("MySQL - Verbindung konnte nicht geschlossen werden", e);
        }
    }

    /**
     * Checks for invalid flag combinations (e.g. timeStarted is set, but timeFinalized is not).  This could be the case,
     * if the server get cancelled or crashed during calculation. If the function find such data, it will correct them and
     * set them back to 'accepted', so they will be started again.
     */
    public void doStartupChecks() {
        String queryString;
        try {
            // prepare connection and execute query (search and modify by one query)
            Statement stmt = this.connection.createStatement();
            queryString = "UPDATE jobs SET timeAccepted = NOW(), timeStarted = NULL WHERE timeStarted IS NOT NULL AND timeCancelled IS NULL AND timeFinalized IS NULL";
            stmt.executeUpdate(queryString);
        } catch (Exception e) {
            ProtocolHandler.fatal("Schwerwiegender Fehler bei den MySQL Startup Checks", e);
        }
    }

    /**
     * Returns a whole dataset of the given job ID
     * @param jobID
     * @return FractalData object
     */
    public FractalData getJobByID(int p_jobID) {

        FractalData data = new FractalData();
        String queryString ="";

        try {
            // prepare connection, execute query and get result
            Statement stmt = this.connection.createStatement();
            queryString = "SELECT * FROM jobs WHERE jobID = " + p_jobID;
            ResultSet rs = stmt.executeQuery(queryString);
            rs.next();

            // get fields for the FractalData object
            data.positionX = rs.getDouble("positionX");
            data.positionY = rs.getDouble("positionY");
            data.distanceX = rs.getDouble("distanceX");
            data.distanceY = rs.getDouble("distanceY");
            data.resolutionX = rs.getInt("resolutionX");
            data.resolutionY = rs.getInt("resolutionY");
            data.iterationDepth = rs.getInt("iterationDepth");
            data.filename = rs.getString("filename");

        } catch (Exception e) {
            ProtocolHandler.error("MySQL Fehler in getJobByID", e);
        }
        // return generated object
        return data;
    }


    /**
     * Modifies one of the DATETIME-flags in the database
     * @param p_field Column (DATETIME-flag) to modify
     * @param p_jobID Specific job ID
     * @param p_value New value (e.g. NULL or SQL command NOW)
     */
    public void updateStatus(String p_field, int p_jobID, String p_value) {

        try {
            // prepare connection and execute query
            Statement stmt = this.connection.createStatement();
            String queryString = "UPDATE jobs SET " + p_field + "= " + p_value + " WHERE jobID = "+p_jobID;
            stmt.executeUpdate(queryString);
        } catch (Exception e) {
            ProtocolHandler.error("MySQL Fehler in updateStatus", e);
        }
    }

    /**
     * Checks if the given status is set
     * @param p_field Column (DATETIME-flag) to check
     * @param p_jobID Specific job ID
     * @return Returns TRUE if the flag is set, otherwise FALSE
     */
    public boolean isStatus(String p_field, int p_jobID) {
        try {
            // prepare connection and execute query
            Statement stmt = this.connection.createStatement();
            String queryString = "SELECT jobId FROM jobs WHERE jobId = " + p_jobID + " AND " + p_field + " IS NOT NULL";
            ResultSet rs = stmt.executeQuery(queryString);

            // if the query delivers a row, return true -- otherwise false
            if (rs.next()) return true;
            else return false;

        } catch (Exception e) {
            ProtocolHandler.error("MySQL Fehler in isStatus", e);
        }
        return false;
    }

    /**
     * Returns the next undone job
     * @return Returns the next jobId (int), where the no flag is set, except of timeAccepted
     */
    public int getUndoneJob() {
        try {
            // prepare connection and execute query
            Statement stmt = this.connection.createStatement();
            String queryString = "SELECT jobId FROM jobs WHERE timeStarted IS NULL AND timeCancelled IS NULL AND timeFinalized IS NULL";
            ResultSet rs = stmt.executeQuery(queryString);
            // if the query delivers a row, return it
            if (rs.next()) {
                return rs.getInt("jobID");
            } else {
                return -1;
            }
        } catch (Exception e) {
            ProtocolHandler.error("MySQL Fehler in getUndoneJobb", e);
        }
        return 0;
    }

}