package com.quantum.sql;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStream;
import java.io.Reader;
import java.io.UnsupportedEncodingException;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Properties;
import java.util.Vector;

import com.quantum.model.Bookmark;
import com.quantum.model.ConnectionException;
import com.quantum.model.Entity;
import com.quantum.model.PasswordFinder;
import com.quantum.sql.metadata.MetaDataJDBCInterface;
import com.quantum.sql.metadata.ObjectMetaData;
import com.quantum.view.LogProxy;
import com.quantum.view.bookmark.EntityNode;
import com.quantum.view.bookmark.TreeNode;


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

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

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

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

    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 ConnectionException {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new ConnectionException(e);
        }
    }

    public Vector getSchemas(Connection con) {
        ResultSet set;
        Vector schemaList = new Vector();
        try {
            DatabaseMetaData meta = con.getMetaData();
            set = meta.getSchemas();
            while (set.next()) {
                schemaList.add(set.getString("TABLE_SCHEM")); //$NON-NLS-1$
            }
            set.close();
        } catch (SQLException e) {
            LogProxy log = LogProxy.getInstance();
            log.addText(LogProxy.ERROR, e);
        }
        return schemaList;
    }
    /**
     * 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);
            }
        }
		Connection con;
        if (password != null) {
            con = connect(bookmark, password);
        } else {
            return 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(con, bookmark.isAutoCommit());
       	return con;
        
    }
    private Connection connect(Bookmark bookmark, String password)
        throws ConnectionException {
        LogProxy log = LogProxy.getInstance();
        log.addText(LogProxy.QUERY, "Connecting to: " + bookmark.getName()); //$NON-NLS-1$
        URL urls[] = new URL[1];
        try {
            String driverFile = bookmark.getDriverFile();
            URLClassLoader loader =
                (URLClassLoader) classLoaderCache.get(driverFile);
            if (loader == null) {
                urls[0] = new File(driverFile).toURL();
                loader = new URLClassLoader(urls);
                classLoaderCache.put(driverFile, loader);
                System.out.println("Creating new classloader"); //$NON-NLS-1$
            } else {
                System.out.println("Using classloader in cache"); //$NON-NLS-1$
            }
            Class driverClass = loader.loadClass(bookmark.getDriver());
            Driver driver = (Driver) driverClass.newInstance();
            Properties props = new Properties();
            props.put(USERNAME, bookmark.getUsername());
            props.put(PASSWORD, password);
            Connection connection =
                driver.connect(bookmark.getConnect(), props);
            if (connection == null) {
                throw new ConnectionException("Error: Driver returned a null connection: " + bookmark.toString()); //$NON-NLS-1$
            }
            log.addText(LogProxy.RESULTS, "Connected to: " + bookmark.getName()); //$NON-NLS-1$
            System.out.println("Connected"); //$NON-NLS-1$
            return connection;
        } catch (SQLException e) {
            throw new ConnectionException(e);
        } catch (MalformedURLException e) {
            throw new ConnectionException(e);
        } catch (ClassNotFoundException e) {
            throw new ConnectionException(e);
        } catch (InstantiationException e) {
            throw new ConnectionException(e);
        } catch (IllegalAccessException e) {
            throw new ConnectionException(e);
        }
    }
	public SQLResults execute(Connection con, String s) throws SQLException {
		return execute(con, s, -1, -1);
	}
	public SQLResults execute(Connection con, String s, int startRow, int endRow) throws SQLException {
		return execute(con, s, -1, -1, Integer.MAX_VALUE);
	}

	public SQLResults execute(Connection con, String s, int startRow, int endRow, int maxLength) throws SQLException {
		return execute(con, s, startRow, endRow, maxLength, ""); //$NON-NLS-1$
	}
	
	public SQLResults execute(
		Connection con,
		String s,
		int startRow,
		int endRow,
		int maxLength,
		String encoding)
		throws SQLException {

		SQLResults results = new SQLResults();

		System.out.println("Executing"); //$NON-NLS-1$
		LogProxy log = LogProxy.getInstance();
		log.addText(LogProxy.QUERY, "Executing Request [" + s + "]"); //$NON-NLS-1$ //$NON-NLS-2$
		boolean metadata = false;
		if (s.startsWith("METADATA")) { //$NON-NLS-1$
			metadata = true;
		}
		if (metadata) {
			results.setQuery(s);
			String table = s.substring(s.indexOf(':') + 1);
			String query = "SELECT * FROM " + table + " WHERE (1 = 0)"; //$NON-NLS-1$ //$NON-NLS-2$
			s = query;
			log.addText(LogProxy.QUERY, "Metadata Request [" + s + "]"); //$NON-NLS-1$ //$NON-NLS-2$
		} else {
			results.setQuery(s);
		}

		Statement stmt = con.createStatement();
		boolean flag = stmt.execute(s);
		results.setResultSet(flag);
		if (metadata) {
			genMetadataResultSet(results, stmt);
			return results;
		}
		if (!flag) {
			int updates = stmt.getUpdateCount();
			results.setUpdateCount(updates);
			log.addText(LogProxy.RESULTS, "Success: " + updates + " records updated"); //$NON-NLS-1$ //$NON-NLS-2$

		} else {
			ResultSet set = stmt.getResultSet();
			ResultSetMetaData metaData = set.getMetaData();
			int columnCount = metaData.getColumnCount();
			Vector columnNames = new Vector();
			for (int i = 1; i <= columnCount; i++) {
				columnNames.addElement(metaData.getColumnName(i));
			}
			results.setColumnNames(columnNames);
			Vector columnTypes = new Vector();
			for (int i = 1; i <= columnCount; i++) {
				columnTypes.addElement(metaData.getColumnTypeName(i));
			}
			results.setColumnTypes(columnTypes);
			int columnSizes[] = new int[columnCount];
			for (int i = 1; i <= columnCount; i++) {
				columnSizes[i - 1] = metaData.getColumnDisplaySize(i);
			}
			int rowCount = 1;
			boolean exitEarly = false;
			while (set.next()) {
				boolean disable = startRow < 1 || endRow < 1;
				boolean start = rowCount >= startRow;
				boolean end = rowCount <= endRow;
				if (disable || (start && end)) {
					Vector row = new Vector();
					for (int i = 1; i <= columnCount; i++) {
						String value;
						if (columnSizes[i - 1] < STREAM
							&& columnSizes[i - 1] < maxLength) {
							if (encoding.equals("")) { //$NON-NLS-1$
								value = set.getString(i);
							} else {
								try {
									value =
										new String(set.getBytes(i), encoding);
								} catch (UnsupportedEncodingException e) {
									log.addText(LogProxy.ERROR, "Error Unsupported encoding " + encoding.toString() + ":" + e.toString(), e); //$NON-NLS-1$ //$NON-NLS-2$
									value = new String(set.getBytes(i));
								}
							}
						} else {
							try {
								if (encoding.equals("")) { //$NON-NLS-1$
									Reader reader = set.getCharacterStream(i);
									StringBuffer buffer = new StringBuffer();
									if (reader != null) {
										int retVal = reader.read();
										int count = 0;
										while (retVal >= 0) {
											buffer.append((char) retVal);
											retVal = reader.read();
											count++;
											if (count > maxLength) {
												buffer.append("...>>>"); //$NON-NLS-1$
												break;
											}
										}
										reader.close();
									}
									value = buffer.toString();
								} else {
									InputStream binaryStream =
										set.getBinaryStream(i);
									ByteArrayOutputStream baos =
										new ByteArrayOutputStream();
									if (binaryStream != null) {
										int retVal = binaryStream.read();
										int count = 0;
										while (retVal >= 0) {
											baos.write(retVal);
											retVal = binaryStream.read();
											count++;
											if (count > maxLength) {
												break;
											}
										}
										binaryStream.close();
									}
									value =
										new String(
											baos.toByteArray(),
											encoding);
								}
							} catch (Throwable e) {
								// hack for mysql which doesn't implement
								// character streams
								value = set.getString(i);
							}
						}
						if (set.wasNull()) {
							row.addElement("<NULL>"); //$NON-NLS-1$
						} else {
							row.addElement(value);
						}
					}
					results.addRow(row);
				}
				rowCount++;
				if (!disable && (rowCount > endRow)) {
					exitEarly = true;
					break;
				}
			}
			if (exitEarly) {
				results.setHasMore(set.next());
			} else {
				results.setMaxSize(rowCount);
				results.setHasMore(false);
			}
			set.close();
		}
		log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$
		stmt.close();
		System.out.println("Executed"); //$NON-NLS-1$
		System.out.println();
		return results;
	}
	private void genMetadataResultSet(SQLResults results, Statement stmt)
		throws SQLException {
		ResultSet set = stmt.getResultSet();
		ResultSetMetaData metaData = set.getMetaData();
		int columnCount = metaData.getColumnCount();
		Vector columnNames = new Vector();
		columnNames.addElement("ColumnName"); //$NON-NLS-1$
		columnNames.addElement("Type"); //$NON-NLS-1$
		columnNames.addElement("Size"); //$NON-NLS-1$
		columnNames.addElement("Nullable"); //$NON-NLS-1$
		columnNames.addElement("AutoIncrement"); //$NON-NLS-1$
		results.setColumnNames(columnNames);
		for (int i = 1; i <= columnCount; i++) {
			Vector row = new Vector();
			row.addElement(metaData.getColumnName(i));
			row.addElement(metaData.getColumnTypeName(i));
			int textSize = metaData.getColumnDisplaySize(i);
			int precision = metaData.getPrecision(i);
			int scale = metaData.getScale(i);
			if (scale == 0 && precision == 0) {
				row.addElement(Integer.toString(precision));
			} else {
				row.addElement(textSize + ", " + precision + ", " + scale); //$NON-NLS-1$ //$NON-NLS-2$
			}
			int nullable = metaData.isNullable(i);
			if (nullable == ResultSetMetaData.columnNoNulls) {
				row.addElement("Not Null"); //$NON-NLS-1$
			} else if (nullable == ResultSetMetaData.columnNullable) {
				row.addElement("Nullable"); //$NON-NLS-1$
			} else if (
				nullable == ResultSetMetaData.columnNullableUnknown) {
				row.addElement("Nullable"); //$NON-NLS-1$
			} else {
				row.addElement("<Error>"); //$NON-NLS-1$
			}
			row.addElement(
				(metaData.isAutoIncrement(i)
					? Boolean.TRUE
					: Boolean.FALSE)
					.toString());
			results.addRow(row);
		}
		results.setHasMore(false);
		set.close();
	}

	/**
	 * Returns an ObjectMetadata object got from the connection 'con' using the name and schema of the node.
	 * @param con
	 * @param node
	 * @return
	 * @throws SQLException
	 */
	public ObjectMetaData getObjectMetadata(Connection con, TreeNode node) throws SQLException {
		ObjectMetaData metadata = new ObjectMetaData();
		if (!(node instanceof Entity)) return metadata;
		
		String schema = ((Entity)node).getSchema();
		String tableName = node.getName();
		
		if (schema.length() == 0) schema = null;
		metadata.setColumns(MetaDataJDBCInterface.getColumns(con, schema, tableName));
		if (node instanceof EntityNode && ((EntityNode) node).isTable()) {
			metadata.setPrimaryKeys(MetaDataJDBCInterface.getPrimaryKeys(con, schema, tableName));
			metadata.setForeignKeys(MetaDataJDBCInterface.getForeignKeys(con, schema, tableName, true));
			metadata.setIndexInfo(MetaDataJDBCInterface.getIndexInfo(con, schema, tableName));
			metadata.setBestRowId(MetaDataJDBCInterface.getBestRowId(con, schema, tableName));
		}
		return metadata;
	}
	
}