/**

  * SQL-DK

  * Copyright © 2013 František Kučera (frantovo.cz)

  *

  * This program is free software: you can redistribute it and/or modify

  * it under the terms of the GNU General Public License as published by

  * the Free Software Foundation, either version 3 of the License, or

  * (at your option) any later version.

  *

  * This program is distributed in the hope that it will be useful,

  * but WITHOUT ANY WARRANTY; without even the implied warranty of

  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

  * GNU General Public License for more details.

  *

  * You should have received a copy of the GNU General Public License

  * along with this program. If not, see <http://www.gnu.org/licenses/>.

  */

 package info.globalcode.sql.dk;

 import info.globalcode.sql.dk.batch.Batch;

 import info.globalcode.sql.dk.configuration.DatabaseDefinition;

 import info.globalcode.sql.dk.configuration.Properties;

 import info.globalcode.sql.dk.configuration.Property;

 import info.globalcode.sql.dk.formatting.ColumnsHeader;

 import info.globalcode.sql.dk.formatting.Formatter;

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.sql.SQLWarning;

 import java.util.logging.Level;

 import java.util.logging.Logger;

 /**

  *

  * @author Ing. František Kučera (frantovo.cz)

  */

 public class DatabaseConnection implements AutoCloseable {

 	private static final Logger log = Logger.getLogger(DatabaseConnection.class.getName());

 	private static final String JDBC_PROPERTY_USER = "user";

 	public static final String JDBC_PROPERTY_PASSWORD = "password";

 	private DatabaseDefinition databaseDefinition;

 	private Connection connection;

 	private Properties properties;

 	public DatabaseConnection(DatabaseDefinition databaseDefinition, Properties properties) throws SQLException {

 		this.databaseDefinition = databaseDefinition;

 		this.properties = properties;

 		if (properties.hasProperty(JDBC_PROPERTY_PASSWORD)) {

 			log.log(Level.WARNING, "Passing DB password as CLI parameter is insecure!");

 		}

 		Properties credentials = new Properties();

 		credentials.add(new Property(JDBC_PROPERTY_USER, databaseDefinition.getUserName()));

 		credentials.add(new Property(JDBC_PROPERTY_PASSWORD, databaseDefinition.getPassword()));

 		credentials.setDefaults(databaseDefinition.getProperties());

 		properties.setDefaults(credentials);

 		java.util.Properties javaProperties = properties.getJavaProperties();

 		connection = DriverManager.getConnection(databaseDefinition.getUrl(), javaProperties);

 	}

 	public void executeQuery(SQLCommand sqlCommand, Formatter formatter) throws SQLException {

 		formatter.writeStartBatch();

 		formatter.writeStartDatabase(databaseDefinition);

 		processCommand(sqlCommand, formatter);

 		formatter.writeEndDatabase();

 		formatter.writeEndBatch();

 	}

 	public void executeBatch(Batch batch, Formatter formatter) throws SQLException {

 		formatter.writeStartBatch();

 		formatter.writeStartDatabase(databaseDefinition);

 		while (batch.hasNext()) {

 			processCommand(batch.next(), formatter);

 		}

 		formatter.writeEndDatabase();

 		formatter.writeEndBatch();

 	}

 	private void processCommand(SQLCommand sqlCommand, Formatter formatter) throws SQLException {

 		try (PreparedStatement ps = sqlCommand.prepareStatement(connection)) {

 			log.log(Level.FINE, "Statement prepared");

 			sqlCommand.parametrize(ps);

 			boolean isRS = ps.execute();

 			log.log(Level.FINE, "Statement executed");

 			if (isRS) {

 				try (ResultSet rs = ps.getResultSet()) {

 					processResultSet(sqlCommand, rs, formatter);

 				}

 			} else {

 				processUpdateResult(sqlCommand, ps, formatter);

 			}

 			logWarnings(ps);

 			while (ps.getMoreResults() || ps.getUpdateCount() > -1) {

 				ResultSet rs = ps.getResultSet();

 				if (rs == null) {

 					processUpdateResult(sqlCommand, ps, formatter);

 				} else {

 					processResultSet(sqlCommand, rs, formatter);

 					rs.close();

 				}

 				logWarnings(ps);

 			}

 		}

 	}

 	private void processUpdateResult(SQLCommand sqlCommand, PreparedStatement ps, Formatter formatter) throws SQLException {

 		formatter.writeStartUpdatesResult();

 		formatter.writeQuery(sqlCommand.getQuery());

 		formatter.writeParameters(sqlCommand.getParameters());

 		formatter.writeUpdatedRowsCount(ps.getUpdateCount());

 		formatter.writeEndUpdatesResult();

 	}

 	private void processResultSet(SQLCommand sqlCommand, ResultSet rs, Formatter formatter) throws SQLException {

 		formatter.writeStartResultSet();

 		formatter.writeQuery(sqlCommand.getQuery());

 		formatter.writeParameters(sqlCommand.getParameters());

 		processResultSetRows(rs, formatter);

 		formatter.writeEndResultSet();

 	}

 	private void processResultSetRows(ResultSet rs, Formatter formatter) throws SQLException {

 		formatter.writeColumnsHeader(new ColumnsHeader(rs.getMetaData()));

 		int columnCount = rs.getMetaData().getColumnCount();

 		while (rs.next()) {

 			formatter.writeStartRow();

 			for (int i = 1; i <= columnCount; i++) {

 				formatter.writeColumnValue(rs.getObject(i));

 			}

 			formatter.writeEndRow();

 		}

 	}

 	private void logWarnings(PreparedStatement ps) throws SQLException {

 		SQLWarning w = ps.getWarnings();

 		while (w != null) {

 			log.log(Level.WARNING, "SQL: {0}", w.getLocalizedMessage());

 			w = w.getNextWarning();

 		}

 		ps.clearWarnings();

 	}

 	/**

 	 * Tests if this connection is live.

 	 *

 	 * @return true if test was successful

 	 * @throws SQLException if test fails

 	 */

 	public boolean test() throws SQLException {

 		connection.getAutoCommit();

 		return true;

 	}

 	@Override

 	public void close() throws SQLException {

 		connection.close();

 	}

 }