package com.quantum.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import com.quantum.Messages;
import com.quantum.adapters.AdapterFactory;
import com.quantum.adapters.DatabaseAdapter;
import com.quantum.model.Bookmark;
import com.quantum.model.ConnectionException;
import com.quantum.model.Entity;
import com.quantum.model.JDBCDriver;
import com.quantum.model.PasswordFinder;
import com.quantum.util.sql.SQLInstructionBuilder;
import com.quantum.view.LogProxy;


/**
 * MultiSQLServer is a Singleton, used  as a interface with the sql drivers.
 * Use MultiSQLServer.getInstance() to get the object.
 */
public class MultiSQLServer implements ConnectionEstablisher {
    public static final String USERNAME = "user"; //$NON-NLS-1$
    public static final String PASSWORD = "password"; //$NON-NLS-1$
    private static MultiSQLServer instance = null;

    private MultiSQLServer() {
    }
    public synchronized static MultiSQLServer getInstance() {
        if (instance == null) {
            instance = new MultiSQLServer();
        }
        return instance;
    }

    public void commit(Connection con) throws SQLException {
        LogProxy log = LogProxy.getInstance();
        try {
            con.commit();
        } catch (SQLException e) {
            log.addText(LogProxy.ERROR, "Error commiting: " + e, e); //$NON-NLS-1$
            throw e;
        }
    }

    public void rollback(Connection con) throws SQLException {
        LogProxy log = LogProxy.getInstance();
        try {
            con.rollback();
        } catch (SQLException e) {
            log.addText(LogProxy.ERROR, "Error rolling back: " + e, e); //$NON-NLS-1$
            throw e;
        }
    }

    public void setAutoCommit(Connection con, boolean enabled) {
        LogProxy log = LogProxy.getInstance();
        try {
            if (con != null) {
                con.setAutoCommit(enabled);
            } else {
                log.addText(LogProxy.ERROR, "Please connect before setting autocommit"); //$NON-NLS-1$
            }
        } catch (SQLException e) {
            log.addText(LogProxy.ERROR, "Error setting autocommit: " + e, e); //$NON-NLS-1$
        }
    }

    public void disconnect(Connection connection) throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }

    /**
     * Makes a connection to a JDBC driver based on the data from a bookmark
     * @param bookmark - 
     *     The Bookmark with the data needed to make the connection
     * @param passwordFinder - 
     *     A utility class that can be invoked if the bookmark does not 
     *     include a password
     * @return The Connection object if everything went OK
     */
    public Connection connect(Bookmark bookmark, PasswordFinder passwordFinder)
        throws ConnectionException {

        String password = bookmark.getPassword();
        if (bookmark.getPromptForPassword()) {
            password = passwordFinder.getPassword();
            if (passwordFinder.isPasswordMeantToBeSaved()) {
                bookmark.setPassword(password);
            }
        }
        
        if (password != null) {
            Connection connection = connect(bookmark, password);
            if (connection != null) {
		        // Set the autoCommit state of the bookmark to the default on new connections
		 		bookmark.setAutoCommit(bookmark.getDefaultAutoCommit());
		 		// Set the autoCommit state of the JDBC connection to the bookmark autoCommit statec
		       	setAutoCommit(connection, bookmark.isAutoCommit());
            }
	       	return connection;
        } else {
            return null;
        }
        
    }
    private Connection connect(Bookmark bookmark, String password)
        throws ConnectionException {
        LogProxy log = LogProxy.getInstance();
        log.addText(LogProxy.QUERY, "Connecting to: " + bookmark.getName()); //$NON-NLS-1$
        try {
        	JDBCDriver jdbcDriver = bookmark.getJDBCDriver();
        	Driver driver = jdbcDriver.getDriver();
            if (driver != null) {
	            Properties props = new Properties();
	            props.put(USERNAME, bookmark.getUsername());
	            props.put(PASSWORD, password);
	            // TODO: This kind of things should really be made general-purpose
	            if (jdbcDriver.getType().equals(AdapterFactory.ORACLE)){ 
	            	// remarksReporting will make the JDBC driver return the remarks for the tables and
	            	// the columns. It'll make getting the tables and columns much slower, so it should
	            	// really be made into an option
	            	// TODO: Make remark reporting into an option
	            	props.put("remarksReporting", "true");
	            	// includeSynonyms will make the JDBC driver return the proper columns when querying
	            	// about a synonym. If not given, synonyms will appear with no columns, exports of data
	            	// containing synonyms will break, etc.n So it's needed from the moment you add the synonyms
	            	// with the getSynonymsList() in the Database.getEntities() function. That could also be
	            	// made into an option, but more logically when more databases are addedd
	            	// TODO: Make including synonyms into an option
	            	props.put("includeSynonyms", "true");
	            }
	            Connection connection =
	                driver.connect(bookmark.getConnect(), props);
	            if (connection == null) {
	                throw new ConnectionException("Error: Driver returned a null connection: " + bookmark.toString()); //$NON-NLS-1$
	            }
	            
	            DatabaseMetaData metaData = connection.getMetaData();
	            jdbcDriver.setName(metaData.getDriverName());
	            jdbcDriver.setVersion(metaData.getDriverVersion());
	            log.addText(LogProxy.RESULTS, "Connected to: " + bookmark.getName()); //$NON-NLS-1$
	            System.out.println("Connected"); //$NON-NLS-1$
	            return connection;
            } else {
            	throw new ConnectionException(Messages.getString(
            			ConnectionException.class, "couldNotInstantiateDriver", 
						new Object[] { jdbcDriver.getClassName(), bookmark.getName() }));
            }
        } catch (SQLException e) {
            throw new ConnectionException(e);
        }
    }
	public SQLResults execute(Bookmark bookmark, Connection con, Entity entity, String s)
			throws SQLException {
		return execute(bookmark, con, entity, s, 200);
	}

	public SQLResults execute(Bookmark bookmark, Connection con, String s)
			throws SQLException {
		return execute(bookmark, con, null, s, 200);
	}
	
	public SQLResultSetResults getMetaData(Entity entity, Connection connection) throws SQLException {
		String query = SQLInstructionBuilder.buildSelectAllColumnsNoRows(entity);
		SQLResultSetResults results = null;
		if (connection != null) {
			Statement statement = connection.createStatement();
			try {
				ResultSet set = statement.executeQuery(query);
				try {
					results = SQLMetaDataResults.create(entity.getBookmark(), set, query, entity);
				} finally {
					set.close();
				}
			} finally {
				statement.close();
			}
		}
		return results;
	}
	
	public SQLResults execute(Bookmark bookmark, Connection con, String sql,
			int numberOfRowsPerPage) throws SQLException {
		return execute(bookmark, con, null, sql, numberOfRowsPerPage);
	}

	
	public SQLResults execute(
			Bookmark bookmark, 
			Connection con,
			Entity entity, 
			String sql,
			int numberOfRowsPerPage)
			throws SQLException {

		long startTime = System.currentTimeMillis();
		System.out.println("Executing"); //$NON-NLS-1$
		LogProxy log = LogProxy.getInstance();
		log.addText(LogProxy.QUERY, "SQL (" + bookmark.getName() + ") [" + sql + "]"); //$NON-NLS-1$ //$NON-NLS-2$
		Statement statement = con.createStatement();
		try {
			SQLResults results;
			if (statement.execute(sql)) {
				ResultSet set = statement.getResultSet();
				try {
					results = SQLStandardResultSetResults.create(set, bookmark, sql, entity, numberOfRowsPerPage);
				} finally {
					set.close();
				}
			} else {
				int updates = statement.getUpdateCount();
				results = new SQLUpdateResults(updates);
			}
			log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$
			if (results != null) {
				results.setTime(System.currentTimeMillis() - startTime);
			}
			return results;
		} finally {
			statement.close();
		}
	}
	
	public int getSize(Bookmark bookmark, Connection connection, String tableName, DatabaseAdapter adapter) throws SQLException {
	    SQLResultSetResults results = (SQLResultSetResults) execute( 
	            bookmark, connection, adapter.getCountQuery(tableName));
	    if (results.getRowCount() > 0 && results.getColumnCount() > 0) {
	        return Integer.parseInt(results.getElement(1, 1).toString());
	    } else {
	        return -1;
	    }
	}
}