/**

 * 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();

	}

}