/*
 * Created on 02.12.2004
 *
 * 
 */
import java.io.File;
import java.io.FilenameFilter;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Types;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Vector;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

/**
 * @author rogo
 * 
 *  
 */
public class DomDataSource
{

	Vector fieldNames = new Vector();
	Vector rowData = new Vector();
	Vector destFieldNames = new Vector();
	String tableName = new String();
	static final String JAXP_SCHEMA_LANGUAGE = "http://java.sun.com/xml/jaxp/properties/schemaLanguage";

	static final String W3C_XML_SCHEMA = "http://www.w3.org/2001/XMLSchema";
	static String schemaSource = "YourSchemaDefinition.xsd";
	static final String JAXP_SCHEMA_SOURCE = "http://java.sun.com/xml/jaxp/properties/schemaSource";
	private boolean first = true;

	Hashtable fieldNameToType = new Hashtable();

	public void readSingleXML(File fin)
	{
		// TODO rethrow any Exception
		DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
		// Document & its builder
		DocumentBuilder domBuilder;
		try
		{
			schemaSource = fin.getAbsolutePath();
			schemaSource = schemaSource.substring(0, schemaSource.length() - 3) + "xsd";
			System.out.println(schemaSource);
			dbFactory.setValidating(true);
			dbFactory.setNamespaceAware(true);

			dbFactory.setAttribute(JAXP_SCHEMA_LANGUAGE, W3C_XML_SCHEMA);
			dbFactory.setAttribute(JAXP_SCHEMA_SOURCE, new File(schemaSource));

			domBuilder = dbFactory.newDocumentBuilder();

			Document document = domBuilder.parse(fin);
			System.out.println("Schema ");
			xmlReadFromElement(document.getDocumentElement());

		}
		catch (ParserConfigurationException e)
		{
			e.printStackTrace();
		}
		catch (SAXException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}

	}

	/**
	 * @param documentElement
	 */
	private void xmlReadFromElement(Element documentElement)
	{
		NodeList list = documentElement.getChildNodes();

		for (int i = 0; i < list.getLength(); i++)
		{
			if (list.item(i) instanceof Element)
			{
				tableName = Convert.convertText(list.item(i).getNodeName());
				NodeList list2 = list.item(i).getChildNodes();
				Hashtable rowToField = new Hashtable();
				for (int j = 0; j < list2.getLength(); j++)
				{
					if (list2.item(j) instanceof Element)
					{
						Element tempElement = (Element) list2.item(j);
						String fieldName = tempElement.getNodeName();
						String typeName = tempElement.getSchemaTypeInfo().getTypeName();
						String fieldValue = tempElement.getTextContent();
						if (!fieldNames.contains(fieldName))
						{
							fieldNames.add(tempElement.getNodeName());
							fieldNameToType.put(fieldName, typeName);
						}
						rowToField.put(fieldName, fieldValue);
					
						//	System.out.println(tempElement.getNodeName() + " " +
						// tempElement.getTextContent() + " " +
						// tempElement.getSchemaTypeInfo().getTypeName());
					}
				}
				rowData.add(rowToField);
				first = false;
			//			System.out.println(rowToField);
			}
		}
		// System.out.println(fieldNameToType);
	}
	public static void main(String[] args)
	{
		File[] files = new File("./ragepxml").listFiles(new FilenameFilter()
		{

			public boolean accept(File arg0, String arg1)
			{

				if (arg1.endsWith(".xml"))
					return true;
				else
					return false;
			}
		});
		for (int i = 0; i < files.length; i++)
		{
			System.out.println(files[i]);
			
		}
		for (int j = 0; j < files.length; j++)
		{
			DomDataSource dsrc = new DomDataSource();
			dsrc.readSingleXML(files[j]);
			String[] fieldNames = new String[dsrc.fieldNames.size()];
			int count = 0;
			for (Iterator iter = dsrc.fieldNames.iterator(); iter.hasNext();)
			{
				String element = (String) iter.next();
				dsrc.destFieldNames.add(Convert.convertText(element));
				fieldNames[count] = Convert.convertText(element);
				++count;
			}
			DBBean bean = new DBBean();
			bean.setURL("jdbc:postgresql://xserve02/islamicms");
			bean.setUserAndPasswd("dwinter", "3333");
			try
			{
				bean.getConnection();
				bean.makeQuery("select * from "+ bean.getQC()+ dsrc.tableName+bean.getQC(), 50);
				DBBean beanDest = new DBBean();
				beanDest.setURL("jdbc:postgresql://xserve02/islamicms2");
				beanDest.setUserAndPasswd("dwinter", "3333");

				Statement stmDrop=beanDest.getConnection().createStatement();
				Vector names=beanDest.getTableNames();
				if(names.contains(dsrc.tableName))
				{	
					stmDrop.execute("drop table "+dsrc.tableName);
				}
					//	beanDest.makeQuery("select * from " + dsrc.tableName, 50);

				System.out.println("Warning empty or invalid create statement - creating one for you\n");

				StringBuffer command = new StringBuffer(50);
				command.append("CREATE TABLE ");
				command.append(bean.getQC());
				command.append(dsrc.tableName);
				command.append(bean.getQC());
				command.append("(");
				String type = null;
				Vector columnNames = bean.getColumnNames();
				for (int i = 0; i < columnNames.size() - 1; ++i)
				{
					type = bean.metaData.getColumnTypeName(i + 1);
					//   System.out.println(i+" "+result[1].get(i)+"
					// "+type);
					type = (type.equals("NUMBER")) ? "INT4" : type;
					type = (type.equals("CONTAINER")) ? "TEXT" : type;

					command.append(bean.getQC() + columnNames.get(i) + bean.getQC() + " " + type + ", ");
				}
				type = bean.metaData.getColumnTypeName(columnNames.size());
				type = (type.equals("NUMBER")) ? "INT4" : type;
				type = (type.equals("CONTAINER")) ? "TEXT" : type;
				command.append(bean.getQC() + (String) columnNames.get(columnNames.size() - 1) + bean.getQC() + " " + type);
				command.append(" )");
				System.out.println(command);
				Statement stm = beanDest.getConnection().createStatement();
				stm.execute(command.toString());
				StringBuffer commandStringBuffer = Convert.createInsertCommand(dsrc.tableName, fieldNames);
				PreparedStatement pstm = beanDest.getConnection().prepareStatement(commandStringBuffer.toString());
				dsrc.writeDataInTable(pstm);
			}
			catch (Exception e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	//		System.out.println(dsrc.destFieldNames);
		}
	}

	/**
	 * @param pstm
	 */
	private void writeDataInTable(PreparedStatement pstm) throws Exception
	{
		for (Iterator iter = rowData.iterator(); iter.hasNext();)
		{
			Hashtable htTable = (Hashtable) iter.next();

			for (int k =0; k<fieldNames.size(); k++)
			{
				String element = (String) fieldNames.get(k);

				Object obj = htTable.get(element);
				//System.out.println(obj);
				String str = (obj == null) ? "NULL" : obj.toString();
				if (obj instanceof Double)
				{
					pstm.setDouble(k + 1, ((Double) obj).doubleValue());
				}
				else if (!str.equals("NULL"))
					pstm.setString(k + 1, str);
				else
					pstm.setNull(k + 1, Types.NULL);
			}
			pstm.execute();
		}

	}
}