/*
 * This library is free software; you can redistribute it and/or
 *  modify it under the terms of the GNU Lesser General Public License
 *  as published by the Free Software Foundation.

 *  There is no copyright.

 *  MockwareTeam (C) 2009

 */

package core;
import java.sql.*;




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

import org.apache.xml.serialize.OutputFormat;
import org.apache.xml.serialize.XMLSerializer;
import org.apache.xerces.dom.DocumentImpl;

import java.io.*;

/**
 *
 * @author yetkin
 */
public class Fact extends util implements FactInt {

    public Fact(){}

    public void CreateDatabase() {

		//ParseXmlFile() ;
		docEle = doc.getDocumentElement() ; //root elementi alıyo
		NamedNodeMap dbNode = docEle.getAttributes() ; //attribute olan nodelar için named node map kullanıo

		String nodeName = "";
		String nodeValue = "";
		//String myQuery = "";




		for(int i=0; i<dbNode.getLength(); i++){

			nodeName = dbNode.item(i).getNodeName() ;
			nodeValue = dbNode.item(i).getNodeValue() ;

			if(nodeName.equals("hostname")) host = nodeValue ;
			else if(nodeName.equals("port")) portNumber = nodeValue ;
			else if(nodeName.equals("username")) userName = nodeValue;
			else if(nodeName.equals("password")) passwd = nodeValue;
			else if(nodeName.equals("dbname")) dbName = nodeValue;

		} //bu loop bi nıde'un attributelarını almak için

		Connection conn = db.dbConnect("jdbc:mysql://localhost/mysql", "root", "qropadb") ; //database connect


		try {
			st = conn.createStatement() ;
			st.executeUpdate("CREATE DATABASE " + dbName) ;
			conn = db.dbConnect("jdbc:mysql://localhost/"+dbName, "root", "qropadb") ; //database yaratıo
			st = conn.createStatement() ;
			//conn.close() ;
		} catch (SQLException e) {
			e.printStackTrace();
		}


	}


public void InsertToDatabase(){

		try{

			String myQuery ="";
			NodeList dbTables = docEle.getChildNodes() ; //root'dan ilk childları alıyo NodeList
			Node table;
	        NodeList rows;
	        NodeList columns;

	        myQuery = "";
	        String insertQuery = "";
	        String value = "";
	        String nodeName = "";
	        int m = 0;

	        for(int i = 1; i < dbTables.getLength(); i += 2)
	        {
	             table = dbTables.item(i); //bunun ilk child'ı (Node)
	             rows = table.getChildNodes(); //bu node'un childlarını alıyo
	             myQuery = "CREATE TABLE " + table.getNodeName(); //bu node'un ismi
	             myQuery += " (";
	             insertQuery = "INSERT INTO " + table.getNodeName() + " VALUES (" ;

	             if(rows.item(1).hasAttributes()){ //attribute node'sa

	            		 NamedNodeMap attr = rows.item(1).getAttributes() ;

	            		 for(int k=0; k<attr.getLength(); k++){
	            			 m++;
	            			 nodeName = attr.item(k).getNodeName() ;
	            			 myQuery += nodeName;
	            			 myQuery += " CHAR(100)" ;
	            			 if(m<attr.getLength()) myQuery += "," ;
	            		 }

	            		 m = 0;
	            		 myQuery += ")" ;
	            		 st.executeUpdate(myQuery) ;

	            		 for(int k=1; k<rows.getLength(); k+=2){

	            			 attr = rows.item(k).getAttributes() ;

	            			 for(int j=0; j<attr.getLength(); j++){
	            				 value = attr.item(j).getNodeValue() ;
	            				 insertQuery += "'" + value + "'" ;
	            				 insertQuery += ",";
	            			 }
	            			 insertQuery = insertQuery.substring(0,insertQuery.length()-1) ;
	            			 insertQuery += ")" ;
	            			// System.out.println(insertQuery) ;
	            			 st.executeUpdate(insertQuery) ;
	            			 insertQuery = "INSERT INTO " + table.getNodeName() + " VALUES (" ;

	            		 }

	             }

	             else {
	            	 columns = rows.item(1).getChildNodes() ; //attribute node değilse

	            	 for(int j=1; j<columns.getLength(); j+=2) {
	            		 nodeName = columns.item(j).getNodeName() ; //her node'un ismini alıyo
	            		 //System.out.println(columns.item(j).getTextContent());
	            		 myQuery += nodeName;
	            		 myQuery+=" CHAR(100)" ;
	            		 myQuery += ",";

	            	 }

	            	 myQuery = myQuery.substring(0, myQuery.length()-1 );
            		 myQuery += ")" ;
            		 st.executeUpdate(myQuery) ;

	            	 for(int j=1; j<rows.getLength(); j+=2) {

	            		 columns = rows.item(j).getChildNodes() ;

	            		 for(int k=1; k<columns.getLength(); k+=2){
	            			 value = columns.item(k).getTextContent() ;
            				 insertQuery += "'" + value + "'" ;
            				 insertQuery += ",";
	            		 }

            			 insertQuery = insertQuery.substring(0,insertQuery.length()-1) ;
            			 insertQuery += ")" ;
            			 //System.out.println(insertQuery) ;
            			 st.executeUpdate(insertQuery) ;
            			 insertQuery = "INSERT INTO " + table.getNodeName() + " VALUES (" ;


	            	 }

	             }

	        }

		}catch( Exception e)
        {
            System.out.println("Cannot create the tables, invalid create table query!!!");
            System.out.println(e.getMessage());
        }

}

public void ReadFromDatabase(String[] database, String outFile){

	/*  database[] = { host, port, dbname, userID, passwd, tableName }        */

	try{

		FileOutputStream output = new FileOutputStream(outFile);
		PrintStream outF;
		outF = new PrintStream(output);
		String enterQuery = "jdbc:mysql://" + database[0] + ":" + database[1] + "/" + database[2] ;
		String searchQuery = "SELECT * FROM " + database[5] ;
		int i=0, j=0;

		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = db.dbConnect(enterQuery, database[3], database[4]);
		Document doc = new DocumentImpl() ;

		Element root = doc.createElement("Database");

		root.setAttribute("hostname", database[0]);
		root.setAttribute("port", database[1]);
		root.setAttribute("username", database[3]);
		root.setAttribute("password", database[4]);
		root.setAttribute("dbname", database[2]) ;

		Element docEle = doc.createElement(database[5]);

		try{
			Statement sqlStatement = conn.createStatement() ;
			ResultSet tableRows = sqlStatement.executeQuery(searchQuery) ;
			ResultSetMetaData metaData = tableRows.getMetaData() ;

			for(i = 0; tableRows.next(); i++) {

				Element row = doc.createElement("Rows");

				for(j = 1; j <= metaData.getColumnCount(); j++) {

						//System.out.println(metaData.getColumnLabel(j)) ;
						Element item = doc.createElement(metaData.getColumnLabel(j)) ;
						item.appendChild(doc.createTextNode(tableRows.getString(j))) ;
						row.appendChild(item) ;
						//System.out.println("<td>  " + tableRows.getString(j) + "  </td>");
				}

				docEle.appendChild(row);
			}

			root.appendChild(docEle);
			doc.appendChild(root) ;

            OutputFormat    format  = new OutputFormat( doc );   //Serialize DOM
            StringWriter  stringOut = new StringWriter();        //Writer will be a String
            XMLSerializer    serial = new XMLSerializer( stringOut, format );				//Bu kısım xml'den database kısmı
            serial.asDOMSerializer();                            // As a DOM Serializer
            serial.serialize( doc.getDocumentElement() );
            outF.print(stringOut.toString() ); //Spit out DOM as a String


		}catch( Exception e ) {
				  System.out.println("Cannot select table from the database!!!");
				  System.out.println(e.getMessage());
				}



	}catch( Exception e ) {
		  System.out.println("Cannot select table from the database!!!");
		  System.out.println(e.getMessage());
		}

}


}
