import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import com.adeptia.indigo.security.AuthUtil;
import com.adeptia.indigo.services.ServiceException;
import com.adeptia.indigo.services.transform.schema.DbSchema;
import com.adeptia.indigo.services.transport.connector.DatabaseConnectionInfo;
import com.adeptia.indigo.storage.Criteria;
import com.adeptia.indigo.storage.Entity;
import com.adeptia.indigo.storage.EntityManager;
import com.adeptia.indigo.storage.EntityManagerFactory;
import com.adeptia.indigo.utils.HandleJTADBConnection;
import com.adeptia.indigo.utils.JdbcUtils;
import com.adeptia.indigo.utils.ProcessData;
import com.adeptia.indigo.utils.XmlUtils;


String schemaId = (String) context.get("SchemaId");


DbSchema dbSchema = null;
EntityManager entityManager = null;
String schemaXsd = null;
List schemaCoulmnNameList = null;
int schemaColumnCount = 0;
String tableName = "";
String databaseInfoId = "";

Connection con = null;
Statement statement = null;
ResultSet resultset = null;

List databaseColumnNameList = null;
int databaseColumnCount = -1;
DatabaseConnectionInfo databaseConnectionInfo = null;

try {
	entityManager = EntityManagerFactory.getEntityManager(
			DbSchema.class, AuthUtil.getAdminSubject());
	Criteria criteria = new Criteria("p.id=\"" + schemaId + "\"");
	Iterator schemaIterator = entityManager.retrieve(criteria);
	if (schemaIterator != null) {
		while (schemaIterator.hasNext()) {
			dbSchema = (DbSchema) schemaIterator.next();
			break;
		}
	}
	if (dbSchema == null) {
		throw new ServiceException("Database Schema with id \""
				+ schemaId + "\" not found in database");
	}
	schemaXsd = dbSchema.getXsd();
	databaseInfoId = dbSchema.getDbConnectionInfoId();
                 int index = databaseInfoId.indexOf(":");
	databaseInfoId =databaseInfoId.substring(index+1);

	Document schemaXsdDocument = XmlUtils.parse(schemaXsd);
	NodeList elementNodeList = XmlUtils.getElementsByTagName(
			schemaXsdDocument, "xs:element");
	NodeList tableNameNodeList = XmlUtils.getElementsByTagName(
			schemaXsdDocument, "tableName");
	schemaCoulmnNameList = new ArrayList();
	// retrieve column name and count from Schema XSD
	for (int i = 0; i < elementNodeList.getLength(); i++) {
		Node columnNode = elementNodeList.item(i);
		String attributeName = XmlUtils
				.getAttribute(columnNode, "name");
		if (!(attributeName.equalsIgnoreCase("Root") || attributeName
				.equalsIgnoreCase("Record"))) {
			// add column name in list
			schemaCoulmnNameList.add(attributeName);
			schemaColumnCount++;
		}
	}
	// retrieve table name from Schema XSD
	for (int i = 0; i < tableNameNodeList.getLength(); i++) {
		Node tableNode = tableNameNodeList.item(i);
		tableName = ProcessData.decodeElementName(tableNode
				.getTextContent());
	}

	// code to get information from database for table used in schema
	Map conMap = HandleJTADBConnection.getConnectionMap();
	conMap.clear();

	String tableSelectQuery = "SELECT * FROM " + tableName
			+ " where 1 = 2;";

	Entity databaseInfoBean = (Entity) DatabaseConnectionInfo.class
			.newInstance();
	EntityManager dbInfoEntityManager = EntityManagerFactory
			.getEntityManager(databaseInfoBean.getClass(),
					AuthUtil.getAdminSubject());
	Iterator dbInfoIterator = dbInfoEntityManager.retrieve();
	while (dbInfoIterator.hasNext()) {
		DatabaseConnectionInfo temp = (DatabaseConnectionInfo) dbInfoIterator
				.next();
		String entityId = temp.getId();
		if (entityId.equals(databaseInfoId)) {
			databaseConnectionInfo = temp;
			break;
		}
	}
	if (databaseConnectionInfo == null) {
		throw new ServiceException(
				"Database Connection Info with id \"" + databaseInfoId
						+ "\" not found in database");
	}
	con = JdbcUtils.getConnection(databaseConnectionInfo,
			AuthUtil.getAdminSubject());
	statement = con.createStatement();
	resultset = statement.executeQuery(tableSelectQuery);
	// get result set meta-data information
	ResultSetMetaData rsmd = resultset.getMetaData();
	databaseColumnCount = rsmd.getColumnCount();
	databaseColumnNameList = new ArrayList();
	for (int i = 1; i <= rsmd.getColumnCount(); i++) {
		String columneName = rsmd.getColumnName(i);
		databaseColumnNameList.add(columneName);
	}

	// check for matching column count
	boolean exceptionRaised = false;
	if (schemaColumnCount == databaseColumnCount) {
		Iterator schemaColumnNameListIterator = schemaCoulmnNameList.iterator();
		String invalidSchemaColumn = "";
		while (schemaColumnNameListIterator.hasNext()) {
			String schemaColumnName = (String) schemaColumnNameListIterator.next();
			if (!databaseColumnNameList.contains(schemaColumnName)) {
				invalidSchemaColumn += schemaColumnName + ",";
				exceptionRaised = true;
			}
		}
		if (exceptionRaised) {
			context.put(
					"exceptionMessage",
					"Following schema column not found in database :: "
							+ invalidSchemaColumn.substring(0,
									invalidSchemaColumn.length() - 1));
		}
	} else {
		context.put("exceptionMessage", "Mis-match in schema ("
				+ schemaColumnCount + ") and database ("
				+ databaseColumnCount + ") column count");
		exceptionRaised = true;

	}
	if (exceptionRaised) {
		context.put("exceptionMetaData", "Schema Column List :: \""
				+ schemaCoulmnNameList.toString()
				+ "\"; Database Column List :: \""
				+ databaseColumnNameList.toString() + "\"");
	}
} catch (Exception e) {
	service.getLogger().error("Error :: " + e.getMessage(), e);
	throw e;
} finally {
	try {
		if (resultset != null) {
			resultset.close();
		}
		if (statement != null) {
			statement.close();
		}
		if (con != null) {
			con.close();
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
}