package net.sourceforge.phpdt.sql.sql;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStream;
import java.io.Reader;
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.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.Vector;

import org.apache.xml.utils.IntVector;

import net.sourceforge.phpdt.sql.bookmarks.Bookmark;
import net.sourceforge.phpdt.sql.sql.metadata.MetaDataJDBCInterface;
import net.sourceforge.phpdt.sql.sql.metadata.ObjectMetaData;
import net.sourceforge.phpdt.sql.view.LogProxy;
import net.sourceforge.phpdt.sql.view.bookmark.TableNode;
import net.sourceforge.phpdt.sql.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(Bookmark b, Connection con) {
		LogProxy log = LogProxy.getInstance();
		try {
			con.close();
			b.setConnection(null);
			log.addText(LogProxy.RESULTS, "Disconnected from: " + b.getName()); //$NON-NLS-1$
		} catch (Exception e) {
			log.addText(
				LogProxy.ERROR,
				"Error Disonnecting to: " + b.getName() + ":" + e.toString(), e); //$NON-NLS-1$ //$NON-NLS-2$
		}
	}
	
	public void dumpDatabaseData(Connection con) {
		LogProxy log = LogProxy.getInstance();
		try {
			DatabaseMetaData meta = con.getMetaData();
			log.addText(LogProxy.WARNING, "[METADATA] Database type: " + meta.getDatabaseProductName()); //$NON-NLS-1$
			if (meta.supportsCatalogsInDataManipulation()) {
				log.addText(LogProxy.WARNING, "[METADATA] Database does support catalog in data manipulation"); //$NON-NLS-1$
			} else {
				log.addText(LogProxy.WARNING, "[METADATA] Database does not support catalog in data manipulation"); //$NON-NLS-1$
			}
			if (meta.supportsSchemasInDataManipulation()) {
				log.addText(LogProxy.WARNING, "[METADATA] Database does support schema in data manipulation"); //$NON-NLS-1$
			} else {
				log.addText(LogProxy.WARNING, "[METADATA] Database does not support schema in data manipulation"); //$NON-NLS-1$
			}
			if (meta.supportsCatalogsInTableDefinitions()) {
				log.addText(LogProxy.WARNING, "[METADATA] Database does support catalogs in table definitions"); //$NON-NLS-1$
			} else {
				log.addText(LogProxy.WARNING, "[METADATA] Database does not support catalogs in table definitions"); //$NON-NLS-1$
			}
			log.addText(LogProxy.WARNING, "[METADATA] Catalog Separator: " + meta.getCatalogSeparator()); //$NON-NLS-1$
			log.addText(LogProxy.WARNING, "[METADATA] Catalog Term: " + meta.getCatalogTerm()); //$NON-NLS-1$
			ResultSet set = meta.getCatalogs();
			ArrayList catalogList = new ArrayList();
			catalogList.add(null);
			while (set.next()) {
				catalogList.add(set.getString(1));
			}
			set.close();
			StringBuffer catalogOutput = new StringBuffer();
			catalogOutput.append("[CATALOG LIST] ["); //$NON-NLS-1$
			for (int i = 0; i < catalogList.size(); i++) {
				String name = (String) catalogList.get(i);
				catalogOutput.append(name + ", "); //$NON-NLS-1$
			}
			catalogOutput.append("]"); //$NON-NLS-1$
			log.addText(LogProxy.WARNING, catalogOutput.toString());
			
			set = meta.getSchemas();
			ArrayList schemaList = new ArrayList();
			schemaList.add(""); //$NON-NLS-1$
			while (set.next()) {
				schemaList.add(set.getString(1));
			}
			set.close();
			StringBuffer schemaOutput = new StringBuffer();
			schemaOutput.append("[SCHEMA LIST] ["); //$NON-NLS-1$
			for (int i = 0; i < schemaList.size(); i++) {
				String name = (String) schemaList.get(i);
				schemaOutput.append(name + ", "); //$NON-NLS-1$
			}
			schemaOutput.append("]"); //$NON-NLS-1$
			log.addText(LogProxy.WARNING, schemaOutput.toString());

            List tableTypes = getTableTypes(meta);
			
			StringBuffer tableListOutput = new StringBuffer();
			tableListOutput.append("[TABLE LIST] ["); //$NON-NLS-1$
			for (int i = 0; i < tableTypes.size(); i++) {
				String name = (String) tableTypes.get(i);
				tableListOutput.append(name + ", "); //$NON-NLS-1$
			}
			tableListOutput.append("]"); //$NON-NLS-1$
			log.addText(LogProxy.WARNING, tableListOutput.toString());
			
			
		} catch (Exception e) {
            log.addText(LogProxy.ERROR, e);
		}
	}
    private List getTableTypes(DatabaseMetaData meta) throws SQLException {
        ArrayList tableTypes = new ArrayList();
        ResultSet set = meta.getTableTypes();
        while (set.next()) {
            String type = set.getString(1);
            if (type != null) {
        		tableTypes.add(type.trim());
            }
        }
        set.close();
        return tableTypes;
    }
//	/**
//	 * @param con
//	 * @param schema
//	 * @param type	of the element "TABLE", "VIEW", "SEQUENCE"
//	 * @return
//	 */
//	public Vector listElements(Connection con, String schema, String type) {
//		LogProxy log = LogProxy.getInstance();
//		Vector retVal = new Vector(50,5);
//		log.addText(LogProxy.QUERY, "Retrieving list [" + type + "]"); //$NON-NLS-1$ //$NON-NLS-2$
//		try {
//			DatabaseMetaData meta = con.getMetaData();
//			List types = getTableTypes(meta);
//			if (types.contains(type)) {
//				ResultSet set = meta.getTables(
//                    null, null, "%", new String[] {type}); //$NON-NLS-1$
//				while (set.next()) {
//					String tableSchema = set.getString("TABLE_SCHEM");  
//                    tableSchema = (tableSchema == null) ? "" : tableSchema.trim();
//					String tableName = set.getString("TABLE_NAME").trim(); //$NON-NLS-1$
//                    if (tableName.length() > 0) 
//                        retVal.addElement(((tableSchema.length() > 0) ? tableSchema + "." : "") + tableName);
//				}
//				set.close();
//			}
//			log.addText(LogProxy.RESULTS, "Success"); //$NON-NLS-1$
//			
//		} catch (SQLException e) {
//			log.addText(LogProxy.ERROR, e);
//		}
//		return retVal;
//	}

	/**
	 * Makes a connection to a JDBC driver based on the data from a bookmark
	 * @param b The Bookmark with the data needed to make the connection
	 * @return The Connection object if everything went OK
	 */
	public Connection connect(Bookmark b) {
		Connection con;
		LogProxy log = LogProxy.getInstance();
		log.addText(LogProxy.QUERY, "Connecting to: " + b.getName()); //$NON-NLS-1$
		URL urls[] = new URL[1];
		try {
			String driverFile = b.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(b.getDriver());
			Driver driver = (Driver) driverClass.newInstance();
			Properties props = new Properties();
			props.put(USERNAME, b.getUsername());
			props.put(PASSWORD, b.getPassword());
			con = driver.connect(b.getConnect(), props);
			if (con == null) {
				throw new Exception("Error: Driver returned a null connection: " + b.toString()); //$NON-NLS-1$
			}
			log.addText(LogProxy.RESULTS, "Connected to: " + b.getName()); //$NON-NLS-1$
			System.out.println("Connected"); //$NON-NLS-1$
			return con;
		} catch (Exception e) {
			//log.addText(
			//	LogProxy.ERROR,
			//	"Error Connecting to: " + b.getName() + ":" + e.toString(), e); //$NON-NLS-1$ //$NON-NLS-2$
		}
		return null;
	}
	public SQLResults execute(Connection con, String s) {
		return execute(con, s, -1, -1);
	}
	public SQLResults execute(Connection con, String s, int startRow, int endRow) {
		return execute(con, s, -1, -1, Integer.MAX_VALUE);
	}
	public SQLResults execute(Connection con, String s, int startRow, int endRow, int maxLength) {
		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) {
		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);
		}
		try {
			Statement stmt = con.createStatement();
			boolean flag = stmt.execute(s);
			results.setResultSet(flag);
			if (!flag) {
				int updates = stmt.getUpdateCount();
				results.setUpdateCount(updates);
				log.addText(
					LogProxy.RESULTS,
					"Success: " + updates + " records updated"); //$NON-NLS-1$ //$NON-NLS-2$

			} else {
				if (metadata) {
					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(Boolean.toString(metaData.isAutoIncrement(i)));
						results.addRow(row);
					}
					results.setHasMore(false);
					set.close();
				} 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);
					IntVector columnSizes = new IntVector();
					for (int i = 1; i <= columnCount; i++) {
						columnSizes.addElement(metaData.getColumnDisplaySize(i));
					}
					results.setColumnSizes(columnSizes);
//					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.elementAt(i - 1) < STREAM && columnSizes.elementAt(i - 1) < maxLength) {
									if (encoding.equals("")) { //$NON-NLS-1$
										value = set.getString(i);
									} else {
										value = new String(set.getBytes(i), encoding);
									}
								} 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();
		} catch (Exception e) {
			results.setIsError(true);
			log.addText(LogProxy.ERROR, e); //$NON-NLS-1$
		}
		return results;
	}

		public ObjectMetaData getObjectMetadata(Connection con, TreeNode node) throws SQLException {
		ObjectMetaData metadata = new ObjectMetaData();
		String table = node.getName();
		metadata.setColumns(MetaDataJDBCInterface.getColumns(con, getSchemaName(table), getTableName(table)));
		if (node instanceof TableNode) {
			String schema = getSchemaName(table);
			String tableName = getTableName(table);
			metadata.setPrimaryKeys(MetaDataJDBCInterface.getPrimaryKeys(con, schema, tableName));
			metadata.setForeignKeys(MetaDataJDBCInterface.getForeignKeys(con, schema, tableName, true));
			metadata.setIndexInfo(MetaDataJDBCInterface.getIndexInfo(con, schema, tableName));
		}
		return metadata;
	}
	
	public String getTableName(String table) {
		StringTokenizer st = new StringTokenizer(table, "."); //$NON-NLS-1$
		if (st.countTokens() == 2) {
			st.nextToken();
			return st.nextToken();
		} else if (st.countTokens() == 1){
			return st.nextToken();
		} else
			return null;
	}
	public String getSchemaName(String table) {
		StringTokenizer st = new StringTokenizer(table, "."); //$NON-NLS-1$
		if (st.countTokens() == 2) {
			return st.nextToken();
		} else 
			return null;
	}
	
}