/*
 * Copyright (C) 2009 by Instytut Podstaw Informatyki Polskiej
 * Akademii Nauk (IPI PAN; Institute of Computer Science, Polish
 * Academy of Sciences; cf. www.ipipan.waw.pl).  All rights reserved.
 *
 * This file is part of WSDDE.
 *
 * WSDDE is free software: it may be distributed and/or modified under
 * the terms of the GNU General Public License version 3 as published
 * by the Free Software Foundation and appearing in the file doc/gpl.txt
 * included in the packaging of this file.
 *
 * A commercial license is available from IPI PAN (contact
 * Michal.Ciesiolka@ipipan.waw.pl or ipi@ipipan.waw.pl for more
 * information).  Licensees holding a valid commercial license from IPI
 * PAN may use this file in accordance with that license.
 *
 * This file is provided AS IS with NO WARRANTY OF ANY KIND, INCLUDING
 * THE WARRANTY OF DESIGN, MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE.
 */

package wsdde.general;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.Vector;

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

import wsdde.WSDMethodResultRow;
import wsdde.general.Config.Settings;





/**
 * @author Rafał Młodzki
 * @author Marcin Młodzki
 *
 */
public class DatabaseHelper {


	public static final String SELECTION = "selection";
	public static final String MACHINE_LEARNING = "machine_learning";
	public static final String CORPUS_TRAINING = "corpus_training";
	public static final String CORPUS_TEST = "corpus_test";
	public static final String RESULT = "result";
	public static final String METHOD_ID = "method_id";
	public static final String EXPERIMENT_ID = "experiment_id";	
	public static final String FG_PREFIX = "fg_";

	public static final String [] defaultFieldNames = {SELECTION, MACHINE_LEARNING, CORPUS_TRAINING, CORPUS_TEST, RESULT, METHOD_ID, EXPERIMENT_ID};

	private String tableName;
	private boolean overrideDBtable = false;
	
	private String experimentID;
	
	private HashMap<String, LinkedHashSet<String>> fGParameters; //do usuniencia

	private LinkedHashSet<String> fGColumnNames;
	public String [] colNames;
			
	private Element XMLElement;

	private Connection dbConnection;

	/**
	 * 
	 * @param xml root element
	 */
	public DatabaseHelper(Element xml){
		XMLElement = xml;
		colNames = defaultFieldNames;
		try {
			connect();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.exit(1);
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);			
		}
		initFromXML();
	}
	
	public DatabaseHelper(String tblName){
		tableName = tblName;
		overrideDBtable = false;
		try {
			connect();
			initFromTableName(); //sets ColNames
		}
		catch(SQLException e){
			e.printStackTrace();
			System.exit(1);
		}
		catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.exit(1);
		}
	}
		
	private void initFromTableName() throws SQLException {
		Vector <String> tmpCols = new Vector<String>();
		fGColumnNames = new LinkedHashSet<String>();
		DatabaseMetaData meta = dbConnection.getMetaData();
		ResultSet rsColumns = meta.getColumns(null, null, tableName, null);	
		while (rsColumns.next()) {
		      String columnName = rsColumns.getString("COLUMN_NAME");
		      if(columnName.contains(FG_PREFIX)){
		    	  fGColumnNames.add(columnName);
		      }
		      else{
		    	  tmpCols.add(columnName);
		      }
		}
//		colNames =  (String[]) tmpCols.toArray();	
		//TODO POPRAWIC TO RZUTOWANIE NA MADRZEJSZE
		colNames = new String [tmpCols.size()];
		for(int i = 0; i < tmpCols.size(); i++){
			colNames[i] = tmpCols.get(i);
		}		
		
	}

	public void setExperimentID(String newID){
		experimentID = newID;
	}
	
	public void setExperimentID(int newID){
		experimentID = Integer.toString(newID);
	}

	public String getExperimentID() {
		return experimentID;
	}


	public LinkedHashSet<String> getFGColumnNames(){
		return fGColumnNames;
	}
	
	private String getDBType(String fieldName){
		if(fieldName.equals(RESULT)){
			return " double ";
		}
		if(fieldName.equals(SELECTION) | fieldName.equals(MACHINE_LEARNING) ){
			return " TEXT ";
		}
		return " char(200) ";
	}

	
	private void initFromXML() {
		tableName = XMLElement.getAttribute("table_name");
		String overrideStr = XMLElement.getAttribute("override");
		setExperimentID(XMLElement.getAttribute("id"));
		if(overrideStr.equals("1") || Boolean.parseBoolean(overrideStr)){
			overrideDBtable = true;
		}
		
		fGParameters = new HashMap<String, LinkedHashSet<String>>();
		fGColumnNames = new LinkedHashSet<String>();
//		NodeList featureGenerators = XMLElement.getElementsByTagName("feature_generator");
		Element fGenerators = (Element)XMLElement.getElementsByTagName("feature_generators").item(0);
		Vector<Element> featureGensVector = Utils.getChildElementsVector(fGenerators);		
		
		for(int i = 0; i < featureGensVector.size(); i++){
//			Element fg = (Element)featureGenerators.item(i);
			Element fg = featureGensVector.get(i);
			LinkedHashSet<String> hs = new LinkedHashSet<String>();
//			String methodName = fg.getAttribute("name");
			String methodName = fg.getNodeName();
			NodeList params = fg.getElementsByTagName("param");
			for(int j = 0; j < params.getLength(); j++){
				String paramName = ((Element)params.item(j)).getAttribute("name");
				hs.add(paramName);
				fGColumnNames.add(FG_PREFIX+methodName+"_"+paramName);
			}
			fGParameters.put(methodName, hs);
		}		
	}

	//TODO poprawić sparametryzowanie
	private void connect() throws ClassNotFoundException, SQLException{
String driver = "";
		
		if (Config.Settings.DB_USED.value().equals("mysql")) {
			Class.forName("com.mysql.jdbc.Driver");
			driver = "mysql";
		}
	      //Define URL of database server for
	      // database named mysql on the localhost
	      // with the default port number 3306.
	      String url ="jdbc:"+driver+"://"+Config.property(Settings.DB_SERVER)+":"+Config.property(Settings.DB_PORT)+"/"+Config.property(Settings.DB_NAME);

	      //Get a connection to the database for a
	      // user named root with a blank password.
	      // This user is the default administrator
	      // having full privileges to do anything.
	      dbConnection = DriverManager.getConnection(url,Config.property(Settings.DB_USER), Config.property(Settings.DB_PASS));
	      	     
	}
	
	public void createTable(){
		String sql;
		if(overrideDBtable){
			sql = "DROP TABLE "+tableName;
			try {
				PreparedStatement stmt = dbConnection.prepareStatement(sql);
				stmt.execute();
			} catch (SQLException e) {}
		}
		sql = getSQLCreateTableStatement();
		try {
			PreparedStatement stmt = dbConnection.prepareStatement(sql);
			stmt.execute();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			if(overrideDBtable){
				e.printStackTrace();
				System.exit(1);
			}
		}		
	}
	
	public Vector<WSDMethodResultRow>  getBestResultsVector() throws SQLException{
		return  getResultsVector(getSQLBestResultsQuery());
	}
	
	public Vector<WSDMethodResultRow> getResultsVector(String sql) throws SQLException{
		Vector<WSDMethodResultRow> result = new Vector<WSDMethodResultRow>();
		PreparedStatement pstmt = dbConnection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			WSDMethodResultRow wsdmrr = new WSDMethodResultRow(rs, this);
			result.add(wsdmrr);
		}
		return result;
	}
	
	public Document getXMLDoc(Vector<WSDMethodResultRow> rows){
		Document doc = Utils.createDomDocument();
		Element wsdExperimentElement = doc.createElement("wsd_experiment");
		wsdExperimentElement.setAttribute("table_name", tableName);
		//TODO add some static data to document
		for(int i = 0; i < rows.size(); i++){
			Element el = rows.get(i).generateOutputXMLNode();
			wsdExperimentElement.appendChild(doc.importNode(el, true));
		}
		doc.appendChild(wsdExperimentElement);
		return doc;
	}
	
	
/*	
 * SELECT result, selection, ml, corpus_trening FROM `ideksperymenta21111232` A
	where result >
	(SELECT result FROM ideksperymenta21111232 WHERE corpus_trening = A.corpus_trening
	order by result desc limit 5,1);
*/
	/*
	 * 
	 */
	public String getSQLBestResultsQuery(){
		String sql = "SELECT ";
		for(int i = 0; i < colNames.length; i++){
			if(i!=0)
				sql += ", ";
			sql += colNames[i];
		}
		for(String columName : fGColumnNames){ // if exists
			sql += ", `"+columName+"`";
		}
		sql += " FROM "+tableName+" A " +
				"WHERE " + RESULT + "> (SELECT "+ RESULT +" FROM " + tableName +" WHERE "+ CORPUS_TRAINING + " = A."+CORPUS_TRAINING+" " +
				"ORDER BY "+ RESULT + " DESC LIMIT 5,1) " +
				"ORDER BY " + CORPUS_TRAINING + ", " + RESULT;
		return sql;
	}
	
	/*
	 * 
	 */
	public String getSQLCreateTableStatement(){
		String statement = "";
		statement += "CREATE TABLE " +
				tableName +" (" +
				"id int(10) unsigned NOT NULL AUTO_INCREMENT, ";
		
		for(int i = 0; i < colNames.length; i++){
			statement += colNames[i] + getDBType(colNames[i]) + ", ";
		}
		String paramsSubstatement = "";
		for (String columnName : fGColumnNames){
			paramsSubstatement += "`"+ columnName + "` integer, "; 
		}		
		statement += paramsSubstatement;
		statement += "PRIMARY KEY (id) )";
		
		return statement;
	}


	public String[] getColNames() {
		return colNames;
	}
	
	/*
	 * 
	 */
	public void insertWSDMethodResultRow(WSDMethodResultRow row) throws SQLException{
		String sql = "INSERT INTO " + tableName + " (";
		for(int i = 0; i < colNames.length; i++){
			if(i!=0)
				sql += ", ";
			sql += colNames[i];
		}
		for(String columName : fGColumnNames){ // if exists
			sql += ", `"+columName + "`";
		}
		sql += ")";
		sql += "VALUES(";
		int numOfColumns = colNames.length + fGColumnNames.size();
		for(int i = 1; i <= numOfColumns; i++){
			sql += ((i!=numOfColumns)?"?, ":"?)");
		}
				
		PreparedStatement pstmt = dbConnection.prepareStatement(sql);
		int paramNum = 0;
		for(int i = 0; i < colNames.length; i ++){
			pstmt.setString(++paramNum, row.getMethodParamValue(colNames[i]));
		}		
		for(String columName : fGColumnNames){
			pstmt.setString(++paramNum, row.fGParamValues.get(columName));
		}
		pstmt.execute();
	}
	
	/**
	 * 
	 * @param tableName name of table of view
	 * @return returns a string with xml description of wsd experiment
	 * @throws SQLException 
	 */
	public static String getXMLFromTable(String tableName) throws SQLException{
		String sql = "select * from " + tableName;
		DatabaseHelper dbh = new DatabaseHelper(tableName);
		Vector<WSDMethodResultRow> rows = dbh.getResultsVector(sql);
		Document doc = dbh.getXMLDoc(rows);
		return Utils.stringFromXML(doc);		
	}

	
	public static Document getNewExperimentDescription(String tableName, int exampleNum, int generatedNum){
		try{
			DatabaseHelper dbh = new DatabaseHelper(tableName);
			String sql = "SELECT * FROM " + tableName + " ORDER BY "+RESULT + " DESC LIMIT "+Integer.toString(exampleNum);
			Vector<WSDMethodResultRow> rows = dbh.getResultsVector(sql);
			Vector<WSDMethodResultRow> generatedRows = new Vector<WSDMethodResultRow>();
			for(int i=0; i < rows.size(); i++){
				Vector<WSDMethodResultRow> tmp = rows.get(i).getSimilarRows(generatedNum);
				generatedRows.addAll(tmp);
			}
			Document doc = dbh.getXMLDoc(generatedRows);
			return doc;
		}
		catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	
	

	public static void main(String[] args) throws SQLException{
	//	String s = getNewExperimentDBDescritpionFromXMLFile("exp_results.xml");
//		String s = getXMLFromTable("ss12345");
//		String s = getNewExperimentDescription("ss12345", 1, 3);
//		System.out.println(s);
	}

	
}
