package com.mysql.grt.modules;

import java.sql.*;
import java.text.MessageFormat;
import java.text.ParsePosition;

import com.mysql.grt.*;
import com.mysql.grt.db.*;
import com.mysql.grt.db.mgmt.*;

/**
 * GRT Reverse Engineering Class using Firebird JDBC metadata functions
 *
 * @author Mike
 * @version 1.0, 11/26/04
 *
 */
public class ReverseEngineeringFirebird {

	/**
	 * Static function to return information about this class to the GRT
	 * environment
	 *
	 * @return returns a GRT XML string containing the infos about this class
	 */
	public static String getModuleInfo() {
		return Grt.getModuleInfoXml(ReverseEngineeringFirebird.class, "");
	}

	/**
	 * Protected function that establishes a connection to the database
	 *
	 * @param dbConn
	 *            the Connection to connect to
	 *
	 * @return returns a Connection on success
	 */
	protected static java.sql.Connection establishConnection(
			com.mysql.grt.db.mgmt.Connection dbConn) throws Exception {
		com.mysql.grt.db.mgmt.Driver driver = dbConn.getDriver();

		if (!JdbcDriver.class.isInstance(driver))
			throw new Exception("The submitted driver is not a JDBC driver.");

		Grt.getInstance().addMsg("Initializing JDBC driver ... ");
		Grt.getInstance().addMsgDetail("Driver class " + driver.getCaption());

		// get classname
		String driverClassName = ((JdbcDriver) driver).getClassName();
		// if there is no classname entry in the driver, take it from the params
		if (driverClassName.equals(""))
			driverClassName = dbConn.getParameterValues().get("classname");

		Class.forName(driverClassName).newInstance();

		// build connection string
		// first, check if the jdbcConnStr param is set
		String jdbcConnectionString = dbConn.getParameterValues().get(
				"jdbcConnStr");
		if ((jdbcConnectionString == null) || jdbcConnectionString.equals("")) {
			// if the param is not set, take build the connection string from
			// the template
			jdbcConnectionString = ((JdbcDriver) driver)
					.getConnectionStringTemplate();

			GrtStringHashMap paramValues = dbConn.getParameterValues();
			for (int i = 0; i < paramValues.getKeys().length; i++) {
				String key = paramValues.getKeys()[i];
				String value = paramValues.get(key).replaceAll("\\\\",
						"\\\\\\\\");
				if (!value.equals(""))
					jdbcConnectionString = jdbcConnectionString.replaceAll("%"
							+ key + "%", value);
				else
					jdbcConnectionString = Grt.replace(jdbcConnectionString,
							"%" + key + "%", "");
			}
		}

		String jdbcConnectionUserString = dbConn.getParameterValues().get(
				"username");

		String jdbcConnectionPasswdString = dbConn.getParameterValues().get(
				"password");

		Grt.getInstance().addMsg("Opening connection ... ");
		Grt.getInstance().addMsgDetail("Connection " + jdbcConnectionString);
		Grt.getInstance().flushMessages();

		return DriverManager.getConnection(jdbcConnectionString, jdbcConnectionUserString, jdbcConnectionPasswdString);
	}

	/**
	 * Get version info of the RDBMS
	 *
	 * This function connects to the target database system and retrieves the
	 * version information
	 *
	 * @param dbConn
	 *            a Connection
	 * @return returns the version information
	 */

	public static Version getVersion(com.mysql.grt.db.mgmt.Connection dbConn)
			throws Exception {

		// connect to the database
		java.sql.Connection conn = establishConnection(dbConn);

		Grt.getInstance().addMsg("Getting version information ... ");

		Version versionInfo = new Version(null);

		String skipVersionDetection = dbConn.getParameterValues().get(
				"skipVersionDetection");

		if ((skipVersionDetection == null) || !skipVersionDetection.equals("1")) {
			try {
				DatabaseMetaData metaData = conn.getMetaData();

				versionInfo.setName(metaData.getDatabaseProductVersion());
				versionInfo.setMajor(metaData.getDatabaseMajorVersion());
				versionInfo.setMinor(metaData.getDatabaseMinorVersion());

				// get release number
				/*
				 * GrtStringList args = new GrtStringList();
				 * args.add(metaData.getDatabaseProductVersion());
				 * args.add(".*\\d+\\.\\d+\\.(\\d+)");
				 *
				 * String release = (String) Grt.getInstance().callGrtFunction(
				 * "Base", "valueFromText", args);
				 *
				 * if (release != null)
				 * versionInfo.setRelease(Integer.parseInt(release));
				 */

				MessageFormat mf = new MessageFormat(
						"{0}.{0}.{0,number,integer}{1}");
				Object[] objs = mf.parse(metaData.getDatabaseProductVersion(),
						new ParsePosition(0));
				if (objs != null)
					versionInfo.setRelease(Integer.parseInt(objs[0].toString()));

			} catch (Exception e) {
				// ignore exceptions if the driver does not support the version
				// functions
			}
		}

		return versionInfo;
	}

	/**
	 * Returns a list of all schemata from the given JDBC connection
	 *
	 * @param jdbcDriver
	 *            the class name of the JDBC driver
	 * @param jdbcConnectionString
	 *            a JDBC connection string
	 * @return returns a GRT XML string containing a list of schemata names
	 */
	public static GrtStringList getSchemata(
			com.mysql.grt.db.mgmt.Connection dbConn) throws Exception {

		java.sql.Connection conn = establishConnection(dbConn);

		Grt.getInstance().addMsg("Fetching schemata list.");

		GrtStringList schemataList = new GrtStringList();

		ResultSet rset = conn.getMetaData().getSchemas();

		int rowCount = 0;
		while (rset.next()) {
			rowCount++;
		}
		rset.close();

		Grt.getInstance().addMsg("SCHEMAS: " + rowCount);

		// refetch rowset
		rset = conn.getMetaData().getSchemas();
		
		if (rowCount > 0) {
			while (rset.next()) {
				schemataList.add(rset.getString("TABLE_SCHEM"));
			}
		}else{
			schemataList.add("data");
		}
		rset.close();
		conn.close();

		return schemataList;
	}

	/**
	 * Does the reverse engineering of the given schematas over the JDBC
	 * connection and returns the GRT objects
	 *
	 * @param jdbcDriver
	 *            the class name of the JDBC driver
	 * @param jdbcConnectionString
	 *            a JDBC connection string
	 * @param schemataList
	 *            list of schematas to be reverse engineered
	 * @return returns a GRT XML string containing a the reverse engineered
	 *         objects
	 */
	public static Catalog reverseEngineer(
			com.mysql.grt.db.mgmt.Connection dbConn, GrtStringList schemataList)
			throws Exception {

		ReverseEngineeringFirebird revEng = new ReverseEngineeringFirebird();

		java.sql.Connection conn = establishConnection(dbConn);

		Catalog catalog = new Catalog(null);
		catalog.setName("Firebird");
//		catalog.setName(dbConn.getParameterValues().get("database"));


		catalog.setVersion(getVersion(dbConn));

		Grt.getInstance().addMsg("Build simple datatypes.");
		revEng.buildSimpleDatatypes(dbConn, catalog);

		// If an empty list was given, use a list with an empty string instead
		if (schemataList.size() == 0)
			schemataList.add("");

		for (int i = 0; i < schemataList.size(); i++) {
			Schema schema = new Schema(catalog);

			schema.setName((String) (schemataList.get(i)));
			catalog.getSchemata().add(schema);

			// Get Tables
			revEng.reverseEngineerTables(conn, catalog, schema);

			// Get Views
			revEng.reverseEngineerViews(conn, catalog, schema);

			// Get SPs
			revEng.reverseEngineerProcedures(conn, catalog, schema);
		}

		conn.close();

		return catalog;
	}

	protected void buildSimpleDatatypes(
			com.mysql.grt.db.mgmt.Connection dbConn, Catalog catalog) {
		com.mysql.grt.db.mgmt.Driver driver = dbConn.getDriver();
		com.mysql.grt.db.mgmt.Rdbms rdbms = (com.mysql.grt.db.mgmt.Rdbms) driver.getOwner();
		com.mysql.grt.db.SimpleDatatypeList rdbmsDatatypeList = rdbms.getSimpleDatatypes();
		com.mysql.grt.db.SimpleDatatypeList catalogDatatypeList = new com.mysql.grt.db.SimpleDatatypeList();

		for (int i = 0; i < rdbmsDatatypeList.size(); i++) {
			catalogDatatypeList.add(rdbmsDatatypeList.get(i));
		}

		Grt.getInstance().addMsg("There are " + rdbmsDatatypeList.size() + " datatypes for this database type.");
		Grt.getInstance().flushMessages();

		catalog.setSimpleDatatypes(catalogDatatypeList);
	}

	protected void reverseEngineerTables(java.sql.Connection conn,
			Catalog catalog, Schema schema) throws Exception {

		Grt.getInstance().addMsg("Fetching all tables of given schemata.");
		Grt.getInstance().flushMessages();
		
		ResultSet rset = conn.getMetaData().getTables(null, schema.getName(),
				null, new String[] {"TABLE"});

		int rowCount = 0;
		while (rset.next()) {
			rowCount++;
		}
		rset.close();

		Grt.getInstance().addMsg("TABLES:" + rowCount);
		
		rset = conn.getMetaData().getTables(null, schema.getName(),
				null, new String[] {"TABLE"});
				
		while (rset.next()) {
			// Create new table
			Table table = new Table(schema);
			schema.getTables().add(table);

			table.setName(rset.getString("TABLE_NAME"));

			reverseEngineerTableColumns(conn, catalog, schema, table);

			reverseEngineerTablePK(conn, catalog, schema, table);

			reverseEngineerTableIndices(conn, catalog, schema, table);

			reverseEngineerTableFKs(conn, catalog, schema, table);
		}
		rset.close();
	}

	protected void reverseEngineerTableColumns(java.sql.Connection conn,
			Catalog catalog, Schema schema, Table table) {

		Grt.getInstance().addMsg("Processing Table: " + table.getName());

		try {
			ResultSet rset = conn.getMetaData().getColumns(null,
					schema.getName(), table.getName(), null);

			// add some debug logic.
			int columnCount = 0;
			while (rset.next()) {
				columnCount++;
			}
			rset.close();
			
			Grt.getInstance().addMsg("COLUMNS: " + columnCount);

			rset = conn.getMetaData().getColumns(null,
					schema.getName(), table.getName(), null);

			while (rset.next()) {
				// create new column
				Column column = new Column(table);
				table.getColumns().add(column);

				// remove nasty double quotes off of some firebird columns
				String colname = rset.getString("COLUMN_NAME");
				colname = colname.replace("\"", "");
				
				column.setName(colname);
				column.setDatatypeName(rset.getString("TYPE_NAME"));

				// get simple type
				column.setSimpleType(catalog.getSimpleDatatypes().get(
						catalog.getSimpleDatatypes().getIndexOfName(
								column.getDatatypeName())));

				// arrays from Firebird are not supported
				if (column.getDatatypeName().equals("ARRAY"))
                    continue;

				column.setLength(rset.getInt("COLUMN_SIZE"));
				column.setPrecision(column.getLength());
				column.setScale(rset.getInt("DECIMAL_DIGITS"));
				
				if (rset.getInt("NULLABLE") == java.sql.DatabaseMetaData.columnNullable)
					column.setIsNullable(1);
				else
					column.setIsNullable(0);

				column.setDefaultValue(rset.getString("COLUMN_DEF"));
			}

			rset.close();
		} catch (Exception e) {
			Grt.getInstance().addErr(e.getMessage());
		}
	}

	protected void reverseEngineerTablePK(java.sql.Connection conn,
			Catalog catalog, Schema schema, Table table) {

		try {
			ResultSet rset = conn.getMetaData().getPrimaryKeys(null, null,
					table.getName());

			// add some debug logic.
			int rowCount = 0;
			while (rset.next()) {
				rowCount++;
			}
			rset.close();

			Grt.getInstance().addMsg("PRIMARY KEY COLUMNS: " + rowCount);

			rset = conn.getMetaData().getPrimaryKeys(null, null,
					table.getName());
			
			Index primaryKey = null;
			String primaryKeyName = null;

			while (rset.next()) {
				if (primaryKey == null) {
					primaryKey = new Index(table);
					primaryKey.setName("PRIMARY");

					primaryKey.setIsPrimary(1);

					// add PK to indices
					table.getIndices().add(primaryKey);

					table.setPrimaryKey(primaryKey);
				}

				primaryKeyName = rset.getString("PK_NAME");

				IndexColumn indexColumn = new IndexColumn(primaryKey);
				indexColumn.setName(rset.getString("COLUMN_NAME"));
				indexColumn.setColumnLength(0);

				// find reference table column
				for (int j = 0; j < table.getColumns().size(); j++) {
					Column column = (Column) (table.getColumns().get(j));

					if (column.getName().compareToIgnoreCase(
							indexColumn.getName()) == 0) {

						indexColumn.setReferedColumn(column);

						// blob has to have an index column length
						if (indexColumn.getColumnLength() == 0) {
							if (column.getSimpleType().getGroup().getName().equals("blob")) {
								indexColumn.setColumnLength(10);
							}
						}

						break;
					}
				}

				primaryKey.getColumns().add(indexColumn);
			}

			// remove primary key from list of indices
			if (primaryKeyName != null) {
				for (int i = 0; i < table.getIndices().size(); i++) {
					String indexName = table.getIndices().get(i).getName();

					if (primaryKeyName.compareToIgnoreCase(indexName) == 0) {
						table.getIndices().remove(i);
						break;
					}
				}
			}
		} catch (Exception e) {
			Grt.getInstance().addErr(e.getMessage());
			Grt.getInstance().addMsg("Error occured while Fetching Primary Keys.");
			Grt.getInstance().flushMessages();
		}
	}

	protected void reverseEngineerTableIndices(java.sql.Connection conn,
			Catalog catalog, Schema schema, Table table) {

		try {
			String indexName = "";
			Index index = null;

			ResultSet rset = conn.getMetaData().getIndexInfo(catalog.getName(), schema.getName(),
					table.getName(), false, true);

			// add some debug logic.
			int rowCount = 0;
			while (rset.next()) {
				rowCount++;
			}
			rset.close();

			Grt.getInstance().addMsg("INDEXES: " + rowCount);

			rset = conn.getMetaData().getIndexInfo(catalog.getName(), schema.getName(),
					table.getName(), false, true);
			
			while (rset.next()) {
				String newIndexName = rset.getString("INDEX_NAME");
				
				if ((newIndexName == null) ||
						(newIndexName.startsWith("RDB$PRIMARY"))) {
					continue;
				}

				if (!indexName.equalsIgnoreCase(newIndexName)) {
					if (index != null)
						table.getIndices().add(index);

					indexName = newIndexName;

					index = new Index(table);
					index.setName(indexName);

					if (rset.getBoolean("NON_UNIQUE"))
						index.setUnique(0);
					else
						index.setUnique(1);
				}

				IndexColumn indexColumn = new IndexColumn(index);
				indexColumn.setName(rset.getString("COLUMN_NAME"));
				indexColumn.setColumnLength(0);

				if (rset.getString("ASC_OR_DESC").compareToIgnoreCase("D") == 0)
					indexColumn.setDescend(1);
				else
					indexColumn.setDescend(0);

				// find reference table column
				for (int j = 0; j < table.getColumns().size(); j++) {
					Column column = (Column) (table.getColumns().get(j));

					if (column.getName().compareToIgnoreCase(
							indexColumn.getName()) == 0) {
						indexColumn.setReferedColumn(column);

						// text and blob have to have a index column length
						if ((indexColumn.getColumnLength() == 0)
								&& (column.getSimpleType().getGroup().getName()
										.equals("text") || column
										.getSimpleType().getGroup().getName()
										.equals("blob")))
							indexColumn.setColumnLength(10);

						break;
					}
				}

				index.getColumns().add(indexColumn);
			}

			if (index != null)
				table.getIndices().add(index);

		} catch (Exception e) {
			Grt.getInstance().addErr(e.getMessage());
			Grt.getInstance().addMsg("Error occured while fetching Indexes.");
		}
	}

	protected void reverseEngineerTableFKs(java.sql.Connection conn,
			Catalog catalog, Schema schema, Table table) {

		try {
			ResultSet rset = conn.getMetaData().getImportedKeys(null, null,
					table.getName());

			// add some debug logic.
			int rowCount = 0;
			while (rset.next()) {
				rowCount++;
			}
			rset.close();

			Grt.getInstance().addMsg("FOREIGN KEYS: " +	rowCount);

			rset = conn.getMetaData().getImportedKeys(null, null,
					table.getName());
			
			String fkName = "";
			ForeignKey foreignKey = null;

			while (rset.next()) {
				String newFkName = rset.getString("FK_NAME");

				if (fkName.compareToIgnoreCase(newFkName) != 0) {
					if (foreignKey != null)
						table.getForeignKeys().add(foreignKey);

					foreignKey = new ForeignKey(table);
					foreignKey.setName(newFkName);

					foreignKey.setDeferability(rset.getInt("DEFERRABILITY"));

					switch (rset.getShort("DELETE_RULE")) {
					case DatabaseMetaData.importedKeyCascade:
						foreignKey.setDeleteRule("CASCADE");
						break;
					case DatabaseMetaData.importedKeyRestrict:
						foreignKey.setDeleteRule("RESTRICT");
						break;
					case DatabaseMetaData.importedKeySetNull:
						foreignKey.setDeleteRule("SET NULL");
						break;
					default:
						foreignKey.setDeleteRule("NO ACTION");
						break;
					}

					switch (rset.getShort("UPDATE_RULE")) {
					case DatabaseMetaData.importedKeyCascade:
						foreignKey.setUpdateRule("CASCADE");
						break;
					case DatabaseMetaData.importedKeyRestrict:
						foreignKey.setUpdateRule("RESTRICT");
						break;
					case DatabaseMetaData.importedKeySetNull:
						foreignKey.setUpdateRule("SET NULL");
						break;
					default:
						foreignKey.setUpdateRule("NO ACTION");
						break;
					}

					String fkSchemaName = rset.getString("FKTABLE_SCHEM");

					if (rset.wasNull())
						foreignKey.setReferedTableSchemaName(schema.getName());
					else
						foreignKey.setReferedTableSchemaName(fkSchemaName);

					foreignKey.setReferedTableName(rset
							.getString("FKTABLE_NAME"));
				}

				foreignKey.getReferedColumnNames().add(
						rset.getString("FKCOLUMN_NAME"));

				// find reference table column
				String colName = rset.getString("PKCOLUMN_NAME");
				for (int j = 0; j < table.getColumns().size(); j++) {
					Column column = (Column) (table.getColumns().get(j));

					if (column.getName().compareToIgnoreCase(colName) == 0)
						foreignKey.getColumns().add(column);
				}
			}

			if (foreignKey != null)
				table.getForeignKeys().add(foreignKey);

		} catch (Exception e) {
			Grt.getInstance().addErr(e.getMessage());
			Grt.getInstance().addMsg("Error occured while fetching Foreign Keys.");
		}
	}

	protected void reverseEngineerUpdateFkReferences(Catalog catalog) {
		SchemaList schemata = catalog.getSchemata();

		// do for all schemata
		for (int i = 0; i < schemata.size(); i++) {
			Schema schema = schemata.get(i);
			TableList tables = schema.getTables();

			// do for all tables
			for (int j = 0; j < tables.size(); j++) {
				ForeignKeyList fks = tables.get(j).getForeignKeys();

				// do for all foreign keys
				for (int k = 0; k < fks.size(); k++) {
					ForeignKey fk = fks.get(k);
					String refSchemaName = fk.getReferedTableSchemaName();
					Schema refSchema;

					// get the refered schema
					if ((refSchemaName != null) && (!refSchemaName.equals(""))
							&& !refSchemaName.equals(schema.getName()))
						refSchema = schemata.getItemByName(refSchemaName);
					else
						refSchema = schema;

					if (refSchema != null) {
						String refTableName = fk.getReferedTableName();

						// get the refered table
						Table refTable = refSchema.getTables().getItemByName(
								refTableName);
						if (refTable != null) {
							GrtStringList refColNames = fk
									.getReferedColumnNames();
							ColumnList refTableCols = refTable.getColumns();

							ColumnList refCols = fk.getReferedColumns();
							if (refCols == null) {
								fk.setColumns(new ColumnList());
								refCols = fk.getReferedColumns();
							}

							// set the table reference in the fk
							fk.setReferedTable(refTable);

							for (int l = 0; l < refColNames.size(); l++) {
								Column refCol = refTableCols
										.getItemByName(refColNames.get(l));

								if (refCol != null) {
									// add column reference to the fk column
									// list
									fk.getReferedColumns().add(refCol);
								}
							}
						}
					}
				}
			}
		}
	}

	protected void reverseEngineerViews(java.sql.Connection conn,
			Catalog catalog, Schema schema) throws Exception {

		Grt.getInstance().addMsg("Fetch all views of the given schemata.");
	}

	protected void reverseEngineerProcedures(java.sql.Connection conn,
			Catalog catalog, Schema schema) throws Exception {

		Grt.getInstance().addMsg(
				"Fetch all stored procedures of the given schemata.");
	}
}