package data;

/*
 * 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.
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author gdick
 */
public class DatabaseUtilities {

    // This tells the application the kind of database we're 
    // connecting to. Instead of hard-coding it, we could
    // read it in at run-time, and then we could change the
    // database back-end as we please.
    private static final String DB_URL = "jdbc:oracle:thin:@info-nts-01.otago.ac.nz:1521:o101";

    // This is the query that we're going to run against
    // the database server. The JDBC middleware will then
    // return the results as a ResultSet object, and we will
    // step through that object to get the fields that we
    // want.
    private static final String SQL
            = "SELECT * FROM Region_Temperatures "
            + "ORDER BY Recorded_Month, Region";

    // The application requests a username and password
    // when it starts - assuming that the login is correct
    // then these fields will be set so that future 
    // connections can be established.
    private static String userName = null;
    private static String password = null;

    // This method looks up the vendor-specific code (based 
    // upon the specs that we supplied in the earlier fields) 
    // and returns the correct driver to the program.
    protected static Connection getDatabaseConnection(String user, String pass) {
        try {
            return DriverManager.getConnection(DB_URL, user, pass);
        } catch (SQLException ex) {
            throw new DbConnectionException(ex.getMessage());
        }
    }

    protected static Connection getDatabaseConnection() {
        return getDatabaseConnection(userName, password);
    }

    public static void checkLogin(String userName, char[] password) {
        try (Connection conn = getDatabaseConnection(userName, new String(password))) {
            conn.prepareCall("SELECT null FROM dual").execute();
            DatabaseUtilities.userName = userName;
            DatabaseUtilities.password = new String(password);
        } catch (SQLException ex) {
            throw new DbLoginException(ex.getMessage());
        }
    }

    // This gets invoked from the user interface to query 
    // the database. The results are returned to the caller, 
    // passed as the argument callback, for processing in a
    // row-by-row fashion.
    public static void queryDB(QueryCallback callback) {
        callback.clearDetails();

        try (Connection conn = getDatabaseConnection()) {
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(SQL);

            while (rs.next()) { // while there's more rows to look at
                // get the required columns of the next row
                String period = rs.getString("Recorded_Month");
                String region = rs.getString("Region");
                double temp = rs.getDouble("Avg_Temperature");

                // pass them to something that will do something
                // useful with them! :)
                callback.pushUpdate(period, region, temp);
            }
        } catch (DbConnectionException ex) {
            Logger.getLogger(DatabaseUtilities.class
                    .getName()).log(Level.SEVERE, "connection failed", ex);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUtilities.class
                    .getName()).log(Level.SEVERE, "query failed", ex);
        }

        callback.finished();
    }
}