package eu.dnetlib.dlms.epub.queries;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;

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

import javax.annotation.Resource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.openrdf.model.Value;
import org.openrdf.repository.RepositoryConnection;
import org.openrdf.repository.RepositoryException;
import org.openrdf.repository.RepositoryResult;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import eu.dnetlib.dlms.impl.hibobjects.RepositoryWrapper;
import eu.dnetlib.dlms.jdbc.DorotyDataSource;
import eu.dnetlib.dlms.jdbc.InformationObject;

/**
 * Test class for DML statements through th JDBC interface on the enhanced publication information object data model.
 * Note that nested structure cannot be tested here because DMF metadata is a plain metadata format, not a tree-like
 * metadata format.
 * 
 * @author lexis
 * 
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration
public class DQLTest {

	/**
	 * logger.
	 */
	private static final Log log = LogFactory.getLog(DQLTest.class);

	@Resource
	private DorotyDataSource dorotyDataSource;
	@Resource
	private RepositoryWrapper repositoryWrapper;

	/**
	 * Logs at debug level the triples currently in the store.
	 * 
	 * @throws RepositoryException
	 */
	@SuppressWarnings("unused")
	private void logTriples() throws RepositoryException {
		final RepositoryConnection c = this.repositoryWrapper.getConnection();
		final RepositoryResult<org.openrdf.model.Statement> res = c.getStatements(null, null, (Value) null, false);
		int count = 0;
		while (res.hasNext()) {
			log.debug(res.next());
			count++;
		}
		log.debug("Total number of triples: " + count);
		res.close();
		c.close();
	}

	/**
	 * Test for a simple select that selects all objects in a Set
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testSelect1() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		stm.execute("select ePubs");
		final ResultSet rs = stm.getResultSet();
		while (rs.next()) {
			final InformationObject o = (InformationObject) rs.getObject(1);
			log.debug(o);
			assertEquals(o.getSet(), "ePubs");
		}
		rs.close();
		stm.close();
		con.close();
	}

	/**
	 * Test for a simple select that selects all objects in a collection of Sets.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testSelect2() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		stm.execute("select (ePubs|ePrints|ResearchData)");
		final ResultSet rs = stm.getResultSet();
		while (rs.next()) {
			final InformationObject o = (InformationObject) rs.getObject(1);
			assertNotNull(o);
			log.debug(o);
			final String setName = o.getSet();
			assertTrue(setName.equals("ePubs") || setName.equals("ePrints") || setName.equals("ResearchData"));
		}
		rs.close();
		stm.close();
		con.close();
	}

	/**
	 * Test for a simple select that selects all objects.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testSelectAll() throws SQLException {
		log.debug("SELECT *");
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		stm.execute("select *");
		final ResultSet rs = stm.getResultSet();
		while (rs.next()) {
			final InformationObject o = (InformationObject) rs.getObject(1);
			log.debug(o);
			assertNotNull(o);
		}
		rs.close();
		stm.close();
		con.close();
		log.debug("SELECT * END");
	}

	/**
	 * Test for a source query with a binary predicate.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testSourceBinPred() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		assertTrue(stm.execute("select ePrints[@dc:language=\"IT\"]"));
		final ResultSet rs = stm.getResultSet();
		InformationObject obj = null;
		while (rs.next()) {
			obj = (InformationObject) rs.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			final String setName = obj.getSet();
			assertTrue(setName.equals("ePrints"));
		}
		rs.close();
		con.close();
	}

	/**
	 * Test for attribute axis in binary predicate with a binary operator different from equals.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testBinPredNotEqual() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		assertTrue(stm.execute("select ePrints[@dc:language != 'EN']"));
		final ResultSet rs = stm.getResultSet();
		InformationObject obj = null;
		assertTrue(rs.next());
		do {
			obj = (InformationObject) rs.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
		} while (rs.next());
		rs.close();
		con.close();
	}

	/**
	 * Test for a source query with a binary predicate on the id attribute.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testSelectByID() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm1 = con.createStatement();
		ResultSet rs = stm1.executeQuery("select ePrints");
		assertTrue(rs.next());
		final InformationObject o = (InformationObject) rs.getObject(1);
		rs.close();
		stm1.close();
		final PreparedStatement stm = con.prepareStatement("select ePrints[@id = ?]");
		stm.setLong(1, o.getId());
		rs = stm.executeQuery();
		InformationObject obj = null;
		assertTrue(rs.next());
		obj = (InformationObject) rs.getObject(1);
		assertNotNull(obj);
		log.debug(obj);
		assertEquals(o.getId(), obj.getId());
		assertEquals("ePrints", obj.getSet());
		rs.close();
		stm.close();
		con.close();
	}

	/**
	 * Test for a source query with a simple predicate.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testSelectSourcePred() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		assertTrue(stm.execute("select ePubs[./composedBy]"));
		final ResultSet rs = stm.getResultSet();
		InformationObject obj = null;
		while (rs.next()) {
			obj = (InformationObject) rs.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			final String setName = obj.getSet();
			assertTrue(setName.equals("ePubs"));
		}
		rs.close();
		con.close();
	}

	/**
	 * Test for bug #1227. Selection of an object that has been just created.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testBug1227() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		final Statement stm2 = con.createStatement();
		stm.execute("new ePrints({dc:subject=['subject', 'subject2']});");
		assertTrue(stm.getResultSet().next());
		stm.close();
		stm2.execute("select ePrints[@dc:subject contains 'subject']");
		final ResultSet rs2 = stm2.getResultSet();
		while (rs2.next())
			log.debug(rs2.getObject(1));
		rs2.close();
		stm2.close();
		con.close();
	}

	/**
	 * Test for target oriented query.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testSelectTarget() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		final String query = "select ePubs/composedBy";
		log.debug(query);
		assertTrue(stm.execute(query));
		final ResultSet rs = stm.getResultSet();
		InformationObject obj = null;
		while (rs.next()) {
			obj = (InformationObject) rs.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			assertEquals(obj.getSet(), "Components");
		}
		rs.close();
		con.close();
	}

	/**
	 * Test for target oriented query that selects only objects id and url.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testSelectTargetFields() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		assertTrue(stm.execute("select (id, url) ePubs/composedBy"));
		final ResultSet rs2 = stm.getResultSet();
		while (rs2.next()) {
			final long id = rs2.getLong(1);
			final URL theUrl = rs2.getURL(2);
			log.debug(id);
			assertNotNull(id);
			log.debug(theUrl);
			assertNotNull(theUrl);
		}
		rs2.close();
		con.close();
	}

	/**
	 * Test for target oriented query.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testSelectTargetStar() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		final String query = "select ePubs/*";
		log.debug(query);
		assertTrue(stm.execute(query));
		final ResultSet rs = stm.getResultSet();
		InformationObject obj = null;
		while (rs.next()) {
			obj = (InformationObject) rs.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			final String setName = obj.getSet();
			assertTrue(setName.equals("Components") || setName.equals("ePubs") || setName.equals("ePrints"));
		}
		rs.close();
		con.close();
	}

	/**
	 * Test for reverse axis.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testReverseAxis() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		final String query = "select Components/reverse::composedBy";
		log.debug(query);
		assertTrue(stm.execute(query));
		final ResultSet rs = stm.getResultSet();
		InformationObject obj = null;
		while (rs.next()) {
			obj = (InformationObject) rs.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			assertEquals("ePubs", obj.getSet());
		}
		rs.close();
		con.close();
	}

	/**
	 * Test for reverse axis.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testReverseAxisStar() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		final String query = "select Components/reverse::*";
		log.debug(query);
		assertTrue(stm.execute(query));
		final ResultSet rs = stm.getResultSet();
		while (rs.next()) {
			final InformationObject obj = (InformationObject) rs.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			final String setName = obj.getSet();
			assertTrue(setName.equals("ePubs") || setName.equals("AggregationObjects"));
		}
		rs.close();
		con.close();
	}

	/**
	 * Test for attribute axis: selects attribute of type string.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisAttribute() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("select ePrints/attribute::dc:title");
		final ResultSet rs = st.getResultSet();
		while (rs.next()) {
			log.debug(rs.getString(1));
		}
		rs.close();
	}

	/**
	 * Test for attribute axis: selects attribute of type coll(string).
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisAttrOnCollectionFields() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("select ePrints/@dc:subject");
		final ResultSet rs = st.getResultSet();
		while (rs.next()) {
			final Object collField = rs.getObject(1);
			assertNotNull(collField);
			log.debug(collField);
		}
		rs.close();
	}

	/**
	 * Test for attribute axis: selection of an attribute that does not exist for (that is it is not decalred by the
	 * type of) the input set returns a null value. TODO: what happen if the field exists but no objects in the Set sets
	 * a value for it? see also bug #1314
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisAttributeUnexistingField() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("select ePrints/@hello");
		final ResultSet rs = st.getResultSet();
		while (rs.next()) {
			final Object field = rs.getObject(1);
			assertNull(field);
		}
		rs.close();
	}

	/**
	 * Test for attribute axis: selection of an attribute that does not exist for (that is it is not decalred by the
	 * type of) the input set returns a null value. TODO: what happen if the field exists but no objects in the Set sets
	 * a value for it? see also bug #1314
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisAttributeStar() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("select ePrints/@*");
		final ResultSet rs = st.getResultSet();
		final ResultSetMetaData rsMd = rs.getMetaData();
		final int columns = rsMd.getColumnCount();
		while (rs.next()) {
			for (int i = 1; i < columns + 1; i++) {
				log.debug("NEXT ROW");
				final String colName = rsMd.getColumnLabel(i);
				final Object field = rs.getObject(i);
				log.debug("Column name: " + colName + ", value: " + field);
			}
		}
		rs.close();
	}

	/**
	 * Test for attribute axis: selects two attributes, one of type coll(string), one of type string.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisMultAttr() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("select ePrints/attribute::(dc:subject|dc:language)");
		final ResultSet rs = st.getResultSet();
		while (rs.next()) {
			final Object collField = rs.getObject(1);
			final String s = rs.getString(2);
			log.debug(collField);
			log.debug(s);
			assertTrue(collField != null || s != null);
		}
		rs.close();
		st.close();
	}

	/**
	 * Test for attribute axis: attribute in simple predicate.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisAttributeInSimplePred() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		stm.execute("select ePrints[@dc:language]");
		final ResultSet rs = stm.getResultSet();
		while (rs.next()) {
			final InformationObject obj = (InformationObject) rs.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			final String setName = obj.getSet();
			assertTrue(setName.equals("ePrints"));
		}
		rs.close();
		stm.close();
		con.close();
	}

	/**
	 * Test for relation axis: navigates from eprints to relations of the Set cites.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisRel() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("select ePrints/relation::cites");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		while (rs.next()) {
			final InformationObject obj = (InformationObject) rs.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			final String setName = obj.getSet();
			assertTrue(setName.equals("cites"));
		}
		rs.close();
	}

	/**
	 * Test for relation axis: selects all relationships from the set ePubs.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisRelStar() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("select ePubs/relation::*");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		while (rs.next()) {
			final InformationObject obj = (InformationObject) rs.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			final String setName = obj.getSet();
			assertTrue(setName.equals("relatedWith") || setName.equals("hasEPrint") || setName.equals("composedBy"));
		}
		rs.close();
	}

	/**
	 * Test for relation axis: selects the objects in the source set that have at least one relation. The result of Q1 =
	 * select S[./relation::R] should be equal to the result obtained to the query Q2 = select S[./R]
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisRelInPred() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("select ePrints[./relation::cites]");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		int count = 0;
		while (rs.next()) {
			count++;
			final InformationObject obj = (InformationObject) rs.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			final String setName = obj.getSet();
			assertEquals("ePrints", setName);

		}
		rs.close();

		st.execute("select ePrints[./cites]");
		final ResultSet rs2 = st.getResultSet();
		log.debug("Reading result");
		int count2 = 0;
		while (rs2.next()) {
			count2++;
			final InformationObject obj = (InformationObject) rs2.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			final String setName = obj.getSet();
			assertEquals("ePrints", setName);
		}
		rs2.close();
		//result should be the same, here only the number is checked
		assertEquals(count, count2);
	}

	/**
	 * Test for cast: casts an eprints in the Set ePubs.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisAsDeref() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("select ePrints");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		InformationObject obj = null;
		if (rs.next())
			obj = (InformationObject) rs.getObject(1);
		rs.close();
		final PreparedStatement pst = this.dorotyDataSource.getConnection().prepareStatement("ePubs.cast(?)");
		pst.setObject(1, obj);
		assertTrue(pst.execute());
		InformationObject wrapper = null;
		final ResultSet rs2 = pst.getResultSet();
		log.debug("Reading result");
		if (rs2.next()) {
			wrapper = (InformationObject) rs2.getObject(1);
			assertNotNull(wrapper);
			log.debug(wrapper);
			final String setName = wrapper.getSet();
			assertEquals("ePubs", setName);
		}
		rs2.close();
		st.execute("select ePubs/deref::ePrints");
		final ResultSet rsEPrint = st.getResultSet();
		assertTrue(rsEPrint.next());
		final InformationObject deref = (InformationObject) rsEPrint.getObject(1);
		assertEquals(obj.getId(), deref.getId());
		rsEPrint.close();

		st.execute("select ePrints/as::ePubs");
		final ResultSet rsEPubs = st.getResultSet();
		assertTrue(rsEPubs.next());
		final InformationObject as = (InformationObject) rsEPubs.getObject(1);
		assertEquals(wrapper.getId(), as.getId());
		rsEPubs.close();

	}

	/**
	 * Test for cast: casts an eprints in the Set ePubs.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisAsDerefStar() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("new nonEPrints({dc:title='e-print to cast'})");
		final ResultSet rs1 = st.getResultSet();
		log.debug("Reading result");
		InformationObject nonEPrint = null;
		if (rs1.next())
			nonEPrint = (InformationObject) rs1.getObject(1);
		rs1.close();

		final PreparedStatement pst = this.dorotyDataSource.getConnection().prepareStatement("ePubs.cast(?,?)");
		pst.setObject(1, nonEPrint);
		pst.setString(2, "nonEPrints");
		assertTrue(pst.execute());
		final ResultSet rsCast = pst.getResultSet();
		log.debug("Reading result");
		assertTrue(rsCast.next());
		final InformationObject wrapper = (InformationObject) rsCast.getObject(1);
		rsCast.close();
		assertNotNull(wrapper);
		log.debug(wrapper);
		log.debug("***********************");
		final Statement st2 = this.dorotyDataSource.getConnection().createStatement();
		st2.execute("select ePubs/deref::*");
		final ResultSet derefStarRS = st2.getResultSet();
		int count = 0;
		assertTrue(derefStarRS.next());
		do {
			count++;
			final InformationObject deref = (InformationObject) derefStarRS.getObject(1);
			assertNotNull(deref);
			log.debug(deref);
			final String derefSet = deref.getSet();
			assertTrue(derefSet.equals("ePrints") || derefSet.equals("ResearchData") || derefSet.equals("nonEPrints"));
		} while (derefStarRS.next());
		derefStarRS.close();
		st2.close();

		st.execute("select */as::ePubs");
		final ResultSet asStarRS = st.getResultSet();
		int count2 = 0;
		assertTrue(asStarRS.next());
		do {
			count2++;
			final InformationObject as = (InformationObject) asStarRS.getObject(1);
			assertNotNull(as);
			log.debug(as);
			final String asSet = as.getSet();
			assertTrue(asSet.equals("ePubs"));
		} while (asStarRS.next());
		asStarRS.close();
		assertEquals(count, count2);
	}

	/**
	 * Test for union sets: lift and tag axes.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisLiftAndTag() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement st = con.createStatement();
		st.execute("select (id) ePrints limit 1");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		Long id = 0l;
		assertTrue(rs.next());
		id = rs.getLong(1);
		rs.close();
		st.close();

		final PreparedStatement pst = con.prepareStatement("select Components[@id = ?]/tag::ePrints");
		pst.setLong(1, id);
		pst.execute();
		final ResultSet rsComp = pst.getResultSet();
		assertTrue(rsComp.next());
		final InformationObject tag = (InformationObject) rsComp.getObject(1);
		assertTrue(tag.getSet().equals("ePrints"));
		assertTrue(tag.getId() == id);
		rsComp.close();
		pst.close();

		final PreparedStatement pst2 = con.prepareStatement("select ePrints[@id=?]/lift::Components");
		pst2.setLong(1, id);
		pst2.execute();
		final ResultSet rsEprints = pst2.getResultSet();
		assertTrue(rsEprints.next());
		final InformationObject lift = (InformationObject) rsEprints.getObject(1);
		assertTrue(lift.getId() == id);
		assertTrue(lift.getSet().equals("Components"));
		rsEprints.close();
		pst2.close();
	}

	/**
	 * Test for cast: casts an eprints in the Set ePubs.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testAxisLiftAndTagStar() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement st = con.createStatement();
		st.execute("select Components/tag::*");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		assertTrue(rs.next());
		do {
			final InformationObject tag = (InformationObject) rs.getObject(1);
			final String setName = tag.getSet();
			assertTrue(setName.equals("ePrints") || setName.equals("nonEPrints") || setName.equals("ResearchData")
					|| setName.equals("AggregationObjects"));

		} while (rs.next());
		rs.close();
		st.close();

		final Statement st2 = con.createStatement();
		st2.execute("select ePrints/lift::*");
		final ResultSet rs2 = st2.getResultSet();
		log.debug("Reading result");
		assertTrue(rs2.next());
		do {
			final InformationObject lifted = (InformationObject) rs2.getObject(1);
			final String setName = lifted.getSet();
			assertTrue(setName.equals("Components"));

		} while (rs2.next());
		rs.close();
		st.close();
	}
}