package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//import java.util.logging.Logger;

/**
 * The connection to the database
 *
 * @author mobe
 *
 */
public class DBConnectionManager {
    //private static final Logger logger = UserSession.getInstance().getLog(DBConnectionManager.class.getName());

    private static int queries = 0;

    /** The only ShippingDB instance */
    private static DBConnectionManager dbo;

    /** Our precious connection */
    private static Connection conn;

    /**
     * Creates a connection to the database. This constructor is private so that
     * getInstance can control the instantiation of connections
     *
     * @param host
     *            String med ip eller domenenavn der databasen kj�rer
     * @param db
     *            String med navnet p� databasen
     * @param user
     *            String med brukernavnet som skal brukes
     * @param password
     *            String med passordet til brukeren
     *
     */
    private DBConnectionManager(String host, String db, String user, String password){
        try {
            long start = System.currentTimeMillis();
            Class.forName("com.mysql.jdbc.Driver");
            System.out.print("getting connection ... ");
            conn = DriverManager.getConnection("jdbc:mysql://" + host + "/" + db + "?allowMultiQueries=true", user,
                    password);
            System.out.println("done");
            System.out.println("Used " + ((System.currentTimeMillis() - start) / 1000.0)
                    + " seconds to connect with database");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void closeAll() {
        if (conn == null)
            return;
        try {
            conn.close();
        } catch (SQLException e) {
            try {
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
    }

    public boolean isConnected() {
        try {
            return conn != null && (!conn.isClosed());
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    private static void queries() {
        queries++;
    }

    /**
     * Returns an instance of this class, which represents a connection to the
     * database
     *
     * @return an instance of this class
     * @throws DataAccessException
     */
    public static DBConnectionManager getInstance() {
        if (dbo == null) {
            //SettingsDaoFactory.getInstance().getSettings();
            //ISettings s = SettingsDaoFactory.getInstance().getSettings();
            String host = "localhost";
            String db = "jon";
            String user = "root";
            String password = "heidu1";
            dbo = new DBConnectionManager(host, db, user, password);
        }
        return dbo;
    }

    public boolean isAutoCommit() {
        try {
            return conn.getAutoCommit();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * Closes connection with database forever
     */
    public static void close() {
        if (conn == null)
            return;
        try {
            conn.close();
            dbo = null;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            System.out.println(queries + " queries");
            queries = 0;
            try {
                if (conn != null) {
                    conn.close();
                    dbo = null;
                }
            } catch (SQLException sqle) {
                System.err.println("Could not disconnect from database.");
                System.out.println("Could not disconnect from database.");
                sqle.printStackTrace();
            }
        }
    }

    /**
     * Closes statement
     *
     * @param s
     *            statement to be closed
     */
    public static void close(Statement s) {
        close(s, null, null);
    }

    /**
     * Closes prepared statement
     *
     * @param ps
     *            prepared statement to be closed
     */
    public static void close(PreparedStatement ps) {
        close(null, ps, null);
    }

    /**
     * Closes result set
     *
     * @param rs
     *            result set to be closed
     */
    public static void close(ResultSet rs) {
        close(null, null, rs);
    }

    /**
     * Closes a statement and a prepared statement
     *
     * @param s
     *            statement to be closed
     * @param ps
     *            prepared statement to be closed
     */
    public static void close(Statement s, PreparedStatement ps) {
        close(s, ps, null);
    }

    /**
     * Closes a statement and a result set
     *
     * @param s
     *            statement to be closed
     * @param rs
     *            result set to be closed
     */
    public static void close(Statement s, ResultSet rs) {
        close(s, null, rs);
    }

    /**
     * Closes a prepared statement and a result set
     *
     * @param ps
     *            prepared statement to be closed
     * @param rs
     *            result set to be closed
     */
    public static void close(PreparedStatement ps, ResultSet rs) {
        close(null, ps, rs);
    }

    /**
     * Closes a statement, a prepared statement and a result set
     *
     * @param s
     *            statement to be closed
     * @param ps
     *            prepared statement to be closed
     * @param rs
     *            result set to be closed
     */
    public static void close(Statement s, PreparedStatement ps, ResultSet rs) {
        if (s == null && ps == null && rs == null)
            return;
        try {
            if (rs != null)
                rs.close();
            if (s != null)
                s.close();
            if (ps != null)
                ps.close();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            try {
                if (s != null)
                    s.close();
                if (ps != null)
                    ps.close();
                if (rs != null)
                    rs.close();
            } catch (SQLException sqle) {
                System.err.println("Could not close.");
                System.out.println("Could not close.");
                sqle.printStackTrace();
            }
        }
    }

    /**
     * Disables auto commit and enables Isolation-level
     * TRANSACTION_SERIALIZABLE, which means that the rows accessed during
     * transactions will be locked for this transactions life time
     */
    public void startTransaction() {
        System.out.print("starting transaction ...");
        try {
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            System.out.println(" autocommit is " + conn.getAutoCommit());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Closes transaction and enables auto commit
     *
     * @throws DataAccessException
     */
    public void closeTransaction() {
        System.out.print("\nstopping transaction ... ");
        try {
            conn.setAutoCommit(true);
            System.out.println(" autocommit is " + conn.getAutoCommit());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Commits current transactions
     */
    public void commit() {
        try {
            if (!conn.getAutoCommit()) {
                System.out.print("\nautocommit is disabled, committing ... ");
                conn.commit();
                System.out.println(" done committing");
            } else {
                System.err.println("autocommit enabled, does nothing!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Rolls back current transactions
     */
    public void rollback() {
        try {
            if (!(conn.getAutoCommit())) {
                conn.rollback();
                System.out.println("\n\t\t\tROLLED BACK!!!!!!1!\n\n");
                System.err.println("Rolled back changes");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Creates a PreparedStatement from string
     *
     * @param query
     *            The query to convert
     * @return a prepared statement
     */
//  public PreparedStatement prepareStatement(String query) {
//      return prepareStatement(query, PreparedStatement.NO_GENERATED_KEYS);
//  }

//  /**
//   * Creates a PreparedStatement from string
//   *
//   * @param query
//   *            the query
//   * @param arg1
//   *            PreparedStatement.NO_GENERATED_KEYS or
//   *            PreparedStatement.RETURN_GENERATED_KEYS
//   * @return a prepared statement
//   */
//  public PreparedStatement prepareStatement(String query, int arg1){
//      try {
//          queries();
//          // System.out.println(queries + "\tPrepared statement:\t" + query);
//          return conn.prepareStatement(query, arg1);
//      } catch (SQLException sqle) {
//          sqle.printStackTrace();
//      }
//  }

    /**
     * Queries the query, only for SELECTS as it is read only
     *
     * @param query
     *            the select query (not an update, insert or delete, as it is
     *            read only)
     * @return the ResultSet created
     * @throws DataAccessException
     */
    public ResultSet runQuery(String query) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery(query);
            // System.out.println(queries + "\tResultSet:\t\t" + query);
            queries();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public void executeUpdate(String query) throws SQLException {
        // System.out.println("query: " + query);
        conn.createStatement().executeUpdate(query);
    }
}