package nuts.core.orm.sql;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Properties;

import nuts.core.mock.sql.MockConnection;
import nuts.core.sql.SqlUtils;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * TestSupport
 */
public class TestSupport {
	private static Log log = LogFactory.getLog(TestSupport.class);

	private static Properties properties;
	private static Connection hsqlConnection;
	private static Connection db2Connection;
	private static Connection oracleConnection;

	/**
	 * initOracleTestData
	 * @param connection connection
	 * @throws Exception if an error occurs
	 */
	public static void initOracleTestData(Connection connection) throws Exception {
		execSQL(connection, "oracle.sql", "\\/");
	}

	/**
	 * initDB2TestData
	 * @param connection connection
	 * @throws Exception if an error occurs
	 */
	public static void initDB2TestData(Connection connection) throws Exception {
		execSQL(connection, "db2.sql", "\\/");
	}

	/**
	 * initHsqldbData
	 * @param connection connection
	 * @throws Exception if an error occurs
	 */
	public static void initHsqldbData(Connection connection) throws Exception {
		execSQL(connection, "hsqldb.sql", "\\;");
	}
	
	/**
	 * execSQL
	 * @param connection connection
	 * @param name resource name
	 * @param splitter splitter
	 * @throws Exception if an error occurs
	 */
	public static void execSQL(Connection connection, String name, String splitter) throws Exception {
		InputStream is = TestSupport.class.getResourceAsStream(name);
		byte[] bs = new byte[is.available()];
		is.read(bs);
		
		String all = new String(bs);
		
		String[] ss = all.replaceAll("\r|\n", " ").split(splitter);
		
		Statement stm = connection.createStatement();
		for (String sql : ss) {
			sql = sql.trim();
			if (sql.length() > 0) {
				try {
					log.debug("Execute SQL: " + sql);
					stm.executeUpdate(sql);
				}
				catch (Exception e) {
					log.error("Error: " + e.getMessage());
				}
			}
		}
		SqlUtils.closeQuietly(stm);
	}
	
	private static Connection getConnection(String name) throws Exception {
		if (properties == null) {
			properties = new Properties();
			
			properties.load(TestSupport.class.getResourceAsStream("jdbc.properties"));
		}
		
		String driver = properties.getProperty(name + ".driver");
		String jdbcurl = properties.getProperty(name + ".url");
		String username = properties.getProperty(name + ".username");
		String password = properties.getProperty(name + ".password");

		log.debug("Connect " + jdbcurl + " - " + username);
		
		try {
			Class.forName(driver);
			
			return DriverManager.getConnection(jdbcurl, username, password);
		}
		catch (Exception ex) {
			log.warn(ex.getMessage());

			return new MockConnection();
		}
	}
	
	/**
	 * @return hsqldb connection 
	 * @throws Exception if an error occurs
	 */
	public static Connection getHsqldbConnection() throws Exception {
		if (hsqlConnection == null) {
			hsqlConnection = getConnection("hsql");
			initHsqldbData(hsqlConnection);
		}
		return hsqlConnection;
	}

	/**
	 * @return db2 connection 
	 * @throws Exception if an error occurs
	 */
	public static Connection getDB2Connection() throws Exception {
		if (db2Connection == null) {
			db2Connection = getConnection("db2");
			initDB2TestData(db2Connection);
		}
		return db2Connection;
	}

	/**
	 * @return oracle connection 
	 * @throws Exception if an error occurs
	 */
	public static Connection getOracleConnection() throws Exception {
		if (oracleConnection == null) {
			oracleConnection = getConnection("oracle");
			initOracleTestData(oracleConnection);
		}
		return oracleConnection;
	}
}
