package nl.rug.lib.db;

import org.apache.log4j.Logger;

import java.sql.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * Convenience methods for database access.
 *
 * @author <a href="mailto:p.r.fokkinga [at] rug.nl">Peter Fokkinga</a>
 */
public final class DbUtils {
	public enum SupportedDatabase {
		ORACLE("oracle:thin"), POSTGRESQL("postgresql"), MYSQL("mysql"), SQLSERVER("inetdae7");

		private final String jdbcDriver;
		private SupportedDatabase(String driver) { jdbcDriver = driver; }
		public String getJdbcDriver() { return jdbcDriver; }
		public static SupportedDatabase fromDriver(String driver) {
			return driver == null ? ORACLE
					: ORACLE.jdbcDriver.equals(driver) ? ORACLE
					: SQLSERVER.jdbcDriver.equals(driver) ? SQLSERVER
					: "mssql".equals(driver) ? SQLSERVER // legacy
					: valueOf(driver.toUpperCase());
		}


		/**
		 * Builds a connectstring for connecting to a database through JDBC.
		 *
		 * @param hostname   the hostname of the database server
		 * @param portNumber the port on which the database listens for new connections
		 * @param dbInstance   the name of the database to connect to
		 * @return a JDBC connectstring, can be used when calling
		 * {@link DbUtils#getConnection(DbUtils.SupportedDatabase, String, String, String)}
		 */
		public String getConnectString(String hostname, int portNumber, String dbInstance) {
		  if (dbInstance == null) {
		    throw new IllegalArgumentException("database cannot be NULL");
		  }

		  StringBuilder sb = new StringBuilder("jdbc:");
		  sb.append(getJdbcDriver()).append(":");
			if (this != SupportedDatabase.SQLSERVER) {
				sb.append("//");
			}
		  sb.append(hostname).append((portNumber >  0 ? ":" + portNumber : ""));

		  String instance = null, db = dbInstance;
		  if (dbInstance.contains("/")) {
		    String[] parts = dbInstance.split("/");
		    instance = parts[0];
		    db = parts[1];
		  }

		  if (this == DbUtils.SupportedDatabase.SQLSERVER && portNumber > 0) {
		    sb.append("?database=").append(db);
		  } else {
			  // A bit of a hack really... this uses the Sqlbrowser process on the SQLserver
			  // machine to lookup the portnumber for the named instance.
			  // Note that you now cannot specify the database in the connectstring,
			  // so you need to configure the "default database" for the user you
			  // connect with in SQLserver/
		    sb.append("/").append(instance != null ? instance : dbInstance);
		  }
		  return sb.toString();
		}
	}


	/**
	 * Gets a connection to an external database. If it is an Oracle database
	 * then the date format will be explicitly set to <code>YYYY-MM-DD HH:MI:SS</code>
	 *
	 * @param dbType        one of the supported database types
	 * @param connectString full JDBC connect string
	 * @param username      username for the connection
	 * @param password      password for the connection
	 * @return a connection to the database
	 * @throws SQLException when the connection could not be established
	 */
	public static Connection getConnection(SupportedDatabase dbType, String connectString,
	                                       String username, String password) throws SQLException {
		Connection result;
		log4j.debug("getConnection(" + dbType.name() + ", " + connectString + ", " + username + ")");
		try {
			switch (dbType) {
				case POSTGRESQL:
					Class.forName("org.postgresql.Driver");
					result = DriverManager.getConnection(connectString, username, password);
					break;
				case ORACLE:
					Class.forName("oracle.jdbc.driver.OracleDriver");
					Pattern p = Pattern.compile("^jdbc:" + dbType.getJdbcDriver() + "://([^:]+):(\\d+)/(.+)");
					Matcher m = p.matcher(connectString);
					StringBuilder sb = new StringBuilder();
					if (m.matches()) {
						sb.append("jdbc:").append(dbType.getJdbcDriver()).append(":@");
						sb.append(m.group(1)).append(":").append(m.group(2)).append(":").append(m.group(3));
						log4j.trace("Oracle connectstring = " + sb.toString());
					} else {
						// connectstring did not have portnumber, use default Oracle portnumber (1521)
						p = Pattern.compile("^jdbc:" + dbType.getJdbcDriver() + "://([^/]+)/(.+)");
						m = p.matcher(connectString);
						if (m.matches()) {
							sb.append("jdbc:").append(dbType.getJdbcDriver()).append(":@");
							sb.append(m.group(1)).append(":1521:").append(m.group(2));
						} else {
							log4j.error("could not parse connectstring for Oracle: " + connectString);
							sb.append(connectString);
						}
					}
					result = DriverManager.getConnection(sb.toString(), username, password);
					// use ISO date as default
					Statement stat = result.createStatement();
					try {
						stat.executeUpdate("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'");
					} finally {
						stat.close();
					}
					break;
				case MYSQL:
					Class.forName("com.mysql.jdbc.Driver");
					result = DriverManager.getConnection(connectString, username, password);
					break;
				case SQLSERVER:
					// this is the (commercial) Merlia driver from i-net software that Blackboard uses
					Class.forName("com.inet.tds.TdsDriver");
					result = DriverManager.getConnection(connectString, username, password);
					break;
				default:
					throw new SQLException("database driver " + dbType + " is not supported");
			}
		} catch (ClassNotFoundException e) {
			throw new SQLException("problem loading driver: " + e.getMessage());
		}
		return result;
	}


	private static final Logger log4j = Logger.getLogger(DbUtils.class);


	/**
	 * This class only contains static methods
	 */
	private DbUtils() {
	}


	/**
	 * Sets NULL when the supplied integer is less than 1 (or NULL).
	 *
	 * @param ps    the prepared statement to set a value in
	 * @param pos   the position of the placeholder to insert to
	 * @param value the value to insert, may be NULL
	 * @throws java.sql.SQLException when the placeholder could not be set to the value
	 */
	public static void setPositiveInteger(PreparedStatement ps, int pos, Integer value) throws SQLException {
		if (value == null || value < 1) {
			value = null;
		}
		setInteger(ps, pos, value);
	}


	/**
	 * Sets NULL when the supplied integer is NULL
	 *
	 * @param ps    the prepared statement to set a value in
	 * @param pos   the position of the placeholder to insert to
	 * @param value the value to insert, may be NULL
	 * @throws java.sql.SQLException when the placeholder could not be set to the value
	 */
	public static void setInteger(PreparedStatement ps, int pos, Integer value) throws SQLException {
		if (value == null) {
			ps.setNull(pos, java.sql.Types.INTEGER);
		} else {
			ps.setInt(pos, value);
		}
	}


	/**
	 * Sets NULL when the supplied float is NULL
	 *
	 * @param ps    the prepared statement to set a value in
	 * @param pos   the position of the placeholder to insert to
	 * @param value the value to insert, may be NULL
	 * @throws java.sql.SQLException when the placeholder could not be set to the value
	 */
	public static void setFloat(PreparedStatement ps, int pos, Float value) throws SQLException {
		if (value == null) {
			ps.setNull(pos, java.sql.Types.FLOAT);
		} else {
			ps.setFloat(pos, value);
		}
	}


	/**
	 * Sets NULL when the supplied date is NULL
	 *
	 * @param ps    the prepared statement to set a value in
	 * @param pos   the position of the placeholder to insert to
	 * @param value the value to insert, may be NULL
	 * @throws java.sql.SQLException when the placeholder could not be set to the value
	 */
	public static void setDate(PreparedStatement ps, int pos, java.util.Date value) throws SQLException {
		if (value == null) {
			ps.setNull(pos, java.sql.Types.DATE);
		} else {
			ps.setDate(pos, new java.sql.Date(value.getTime()));
		}
	}


	/**
	 * Sets NULL when the supplied string is empty or contains only whitespace.
	 *
	 * @param ps    the prepared statement to set a value in
	 * @param pos   the position of the placeholder to insert to
	 * @param value the value to insert, may be NULL
	 * @throws java.sql.SQLException when the placeholder could not be set to the value
	 */
	public static void setNonEmptyVarChar(PreparedStatement ps, int pos, String value) throws SQLException {
		if (value == null || value.trim().length() == 0) {
			value = null;
		}
		setVarChar(ps, pos, value);
	}


	/**
	 * Sets NULL when the supplied string is NULL
	 *
	 * @param ps    the prepared statement to set a value in
	 * @param pos   the position of the placeholder to insert to
	 * @param value the value to insert, may be NULL
	 * @throws java.sql.SQLException when the placeholder could not be set to the value
	 */
	public static void setVarChar(PreparedStatement ps, int pos, String value) throws SQLException {
		if (value == null) {
			ps.setNull(pos, java.sql.Types.VARCHAR);
		} else {
			ps.setString(pos, value);
		}
	}


	public static boolean isOracle(Connection c) throws SQLException {
		return "Oracle".equalsIgnoreCase(c.getMetaData().getDatabaseProductName());
	}


	public static boolean isMSSQL(Connection c) throws SQLException {
		return "Microsoft SQL Server".equalsIgnoreCase(c.getMetaData().getDatabaseProductName());
	}


	public static boolean isMySQL(Connection c) throws SQLException {
		return "mysql".equalsIgnoreCase(c.getMetaData().getDatabaseProductName());
	}


	public static boolean isPostgreSQL(Connection c) throws SQLException {
		return "postgresql".equalsIgnoreCase(c.getMetaData().getDatabaseProductName());
	}


	/**
	 * Convenience method for closing PreparedStatements. Handles (ignores)
	 * exceptions, including when the given statement is NULL.
	 *
	 * @param statements one or more PreparedStatement that must be closed
	 *                   (this method supports a variable-length argument list)
	 */
	public static void closePreparedStatement(PreparedStatement... statements) {
		for (PreparedStatement ps : statements) {
			try {
				ps.close();
			} catch (Exception e) {
				// this space intentionally left blank
			}
		}
	}


	/**
	 * Convenience method for closing ResultSets. Handles (ignores)
	 * exceptions, including when the given resultset is NULL.
	 *
	 * @param resultsets one or more ResultSet that must be closed
	 *                   (this method supports a variable-length argument list)
	 */
	public static void closeResultSet(ResultSet... resultsets) {
		for (ResultSet rs : resultsets) {
			try {
				rs.close();
			} catch (Exception e) {
				// this space intentionally left blank
			}
		}
	}
}