package eu.dnetlib.dlms.view;

import java.net.URI;
import java.net.URL;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import eu.dnetlib.dlms.jdbc.InformationObject;

public class QueryManager {
	private DataSource dataSource;

	public QueryManager() {

	}

	public QueryManager(DataSource theDataSoure) {
		this.dataSource = theDataSoure;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public boolean existStructSet(String structName, Connection aConnection) {

		Connection theConnection = null;
		boolean exist = false;
		try {
			if (aConnection == null)
				theConnection = this.dataSource.getConnection();
			else
				theConnection = aConnection;
			PreparedStatement pst = theConnection.prepareStatement("select SystemStructSets[@name= ?]");
			pst.setString(1, structName);
			ResultSet theResult = pst.executeQuery();
			exist = theResult.next();

		} catch (SQLException e) {
			System.out.println("Errore nell'esecuzione della query");
			exist = false;
		} finally {
			try {
				if (aConnection == null)
					theConnection.close();
			} catch (SQLException e2) {
				System.out.println("Errore nella chiusura della connessione");
				return false;
			}
		}
		return exist;
	}

	public boolean existAtomSet(String atomSetName, Connection aConnection) {

		Connection theConnection = null;
		boolean exist = false;
		try {
			if (aConnection == null)
				theConnection = this.dataSource.getConnection();
			else
				theConnection = aConnection;
			PreparedStatement pst = theConnection.prepareStatement("select SystemAtomSets[@name= ?]");
			pst.setString(1, atomSetName);
			ResultSet theResult = pst.executeQuery();
			exist = theResult.next();

		} catch (SQLException e) {
			System.out.println("Errore nell'esecuzione della query");
			exist = false;
		} finally {
			try {
				if (aConnection == null)
					theConnection.close();
			} catch (SQLException e2) {
				System.out.println("Errore nella chiusura della connessione");
				return false;
			}
		}
		return exist;
	}

	public boolean existRelSet(String relationName, Connection aConnection) {

		Connection theConnection = null;
		boolean exist = false;
		try {
			if (aConnection == null)
				theConnection = this.dataSource.getConnection();
			else
				theConnection = aConnection;
			PreparedStatement pst = theConnection.prepareStatement("select SystemRelSets[@name= ?]");
			pst.setString(1, relationName);
			ResultSet theResult = pst.executeQuery();
			exist = theResult.next();

		} catch (SQLException e) {
			System.out.println("Errore nell'esecuzione della query");
			exist = false;
		} finally {
			try {
				if (aConnection == null)
					theConnection.close();
			} catch (SQLException e2) {
				System.out.println("Errore nella chiusura della connessione");
				return false;
			}
		}
		return exist;
	}

	public boolean existSet(String setName, Connection aConnection) {

		Connection theConnection = null;
		boolean exist = false;
		try {
			if (aConnection == null)
				theConnection = this.dataSource.getConnection();
			else
				theConnection = aConnection;
			PreparedStatement pst = theConnection.prepareStatement("select SystemSets[@name=?]");
			pst.setString(1, setName);
			ResultSet theResult = pst.executeQuery();
			exist = theResult.next();

		} catch (SQLException e) {
			System.out.println("Errore nell'esecuzione della query");
			exist = false;
		} finally {
			try {
				if (aConnection == null)
					theConnection.close();
			} catch (SQLException e2) {
				System.out.println("Errore nella chiusura della connessione");
				return false;
			}
		}
		return exist;
	}

	public boolean setBelongstoUnion(String nameSet, String nameUnion, Connection aConnection) {
		Connection theConnection = null;
		boolean belongs = false;
		try {
			if (aConnection == null)
				theConnection = dataSource.getConnection();
			else
				theConnection = aConnection;
			if (!((this.existSet(nameSet, theConnection)) && (this.existSet(nameUnion, theConnection))))
				return false;
			PreparedStatement pst = theConnection.prepareStatement("select SystemUnionSets[@name=?]/SystemIncludesSets[@name=?]");
			pst.setString(1, nameUnion);
			pst.setString(2, nameSet);
			ResultSet rst = pst.executeQuery();
			belongs = rst.next();
			rst.close();
			rst = null;
			pst.close();
			

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (aConnection == null)
				try {
					theConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

		return belongs;
	}

	public boolean relationIsValid(String nameRelation, String firstSet, String secondSet, Connection aConnection) {
		Connection theConnection = null;
		boolean isValid = false;
		try {
			if (aConnection == null)
				theConnection = dataSource.getConnection();
			else
				theConnection = aConnection;
			//Get the name of the first Set of the relation
			PreparedStatement theStatement = theConnection.prepareStatement("select SystemRelSets[@name=?]/SystemHasFirstSet/@name");
			theStatement.setString(1, nameRelation);
			ResultSet rst = theStatement.executeQuery();
			String thefirst = null;
			String thesecond = null;
			if (rst.next())
				thefirst = rst.getString(1);
			rst.close();
			theStatement.close();
			theStatement = theConnection.prepareStatement("select SystemRelSets[@name=?]/SystemHasSecondSet//@name");
			theStatement.setString(1, nameRelation);
			rst = theStatement.executeQuery();
			if (rst.next())
				thesecond = rst.getString(1);
			if ((thefirst == null) || (thesecond == null))
				return false;
			if (!thefirst.equals(firstSet)) {
				if ((!this.setBelongstoUnion(firstSet, thefirst, theConnection)))
					return false;
			} else {
				if (!thesecond.equals(secondSet)) {
					if ((!this.setBelongstoUnion(secondSet, thesecond, theConnection)))
						return false;
					else
						isValid = true;
				} else
					isValid = true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			if (theConnection != null)
				try {
					theConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}

		}

		return isValid;
	}
	
	public void prova()
	{
		Connection connessione= null;
		try {
			connessione= dataSource.getConnection();
			Statement st= connessione.createStatement();
			ResultSet rt;
//			ResultSetMetaData md= rt.getMetaData();
//			System.out.println("Numero di colonne = "+md.getColumnCount());
//			while(rt.next())
//			{
//				System.out.println("**********************");
//				for(int i=1; i<33; i++)
//					System.out.println(md.getColumnName(i)+"  = "+rt.getObject(i));
//				System.out.println("+++++++++++++++++++++++");
//			}
//			rt.close();
//			st.close();
			PreparedStatement pst= connessione.prepareStatement("new ProvaSet(?,?)");
			//URL theurl=new URL("/home/sandro/Desktop/sandro.xml");
			URI miaUri=new URI("file:///home/sandro/Desktop/provaREM.xml");
			System.out.println("fin qui OK!");
			pst.setURL(1,miaUri.toURL());
			System.out.println("fin qui OK!");
			pst.setBoolean(2, false);
			System.out.println("fin qui OK!");
			int  b=pst.executeUpdate();
			System.out.println("Eseguito ="+b);
			pst= connessione.prepareStatement("select ePrints");
			rt=pst.executeQuery();
			ResultSetMetaData md=rt.getMetaData();
			System.out.println("Numero di colonne = "+md.getColumnCount());
			
			while(rt.next())
			{
				System.out.println("DcTitle ="+rt.getString(1));
			}
			rt.close();
			pst.close();
			connessione.close();
			
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}