package eu.dnetlib.dlms.epub.queries;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

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.springframework.test.annotation.DirtiesContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

import eu.dnetlib.dlms.jdbc.DorotyConnection;
import eu.dnetlib.dlms.jdbc.DorotyDataSource;
import eu.dnetlib.dlms.jdbc.InformationObject;
import eu.dnetlib.dlms.lowlevel.objects.Set;
import eu.dnetlib.dlms.lowlevel.objects.SetDAO;
import eu.dnetlib.dlms.lowlevel.objects.Wrapper;
import eu.dnetlib.dlms.lowlevel.objects.WrapperDAO;
import eu.dnetlib.dlms.lowlevel.objects.structures.Structure;
import eu.dnetlib.dlms.lowlevel.objects.structures.StructureDAO;
import eu.dnetlib.dlms.lowlevel.types.AtomType;
import eu.dnetlib.dlms.lowlevel.types.AtomTypeDAO;
import eu.dnetlib.dlms.lowlevel.types.SetType;
import eu.dnetlib.dlms.lowlevel.types.SetTypeDAO;
import eu.dnetlib.miscutils.datetime.DateUtils;

/**
 * Test class for DML statements through th JDBC interface on the enhanced publication information object data model.
 * Note that the import call cannot be tested because there are no Set with congruent types in the model. TODO:
 * implement uncast tests when the call is ready.
 * 
 * @author lexis
 * 
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration
public class DMLTest {

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

	@Resource
	private DorotyDataSource dorotyDataSource;
	@Resource
	private StructureDAO structDAO;
	@Resource
	private WrapperDAO wrapperDAO;
	/**
	 * DAO for AtomTypes.
	 */
	@Resource
	private AtomTypeDAO atomTypeDAO;
	/**
	 * DAO for SetTypes.
	 */
	@Resource
	private SetTypeDAO setTypeDAO;
	/**
	 * a SetDAO instance.
	 */
	@Resource
	private SetDAO extSetDAO;
	/**
	 * Set instance.
	 */
	@Resource
	private Set theSet;

	/**
	 * AtomType istance.
	 */
	@Resource
	private AtomType atomType;
	/**
	 * SetType instance.
	 */
	@Resource
	private SetType setType;

	//********************************NEW****************************************************************//
	/**
	 * Test for the call 'new' of a structure with a coll_string field.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void newStructCollParam() throws SQLException {
		final PreparedStatement stm = this.dorotyDataSource.getConnection().prepareStatement("new ePrints({dc:subject=?});");
		stm.setObject(1, Arrays.asList(new String[] { "a", "aa", "aaa" }));
		assertTrue(stm.execute());
		final ResultSet rs = stm.getResultSet();
		assertTrue(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();
	}

	/**
	 * Test for the call 'new' of a structure with one string field and one date field whose value is set as an inline
	 * ISO date. Remark the # before the date is mandatory!
	 * 
	 * @throws SQLException
	 */
	@Test
	public void newStructString_ISODate() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		stm.execute("new ePrints({dc:title='MyTitle for ePrints',dc:dateAccepted=#'2010-01-13T14:40:15+01:00'})");
		final ResultSet rs = stm.getResultSet();
		while (rs.next()) {
			log.debug(rs.getObject(1));
		}
		rs.close();
		stm.close();
		con.close();
	}

	/**
	 * Test for the call 'new' of a structure with three fields: one string, one collection of string and one date.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void newStructNamedParamWithList_Date() throws SQLException {
		final DorotyConnection con = this.dorotyDataSource.getConnection();
		final String query = "new ePrints({dc:title=?, dc:subject=[?], dr:dateOfCollection=?});";
		final PreparedStatement nps = con.prepareStatement(query);
		nps.setString(1, "Title using a named param!");
		nps.setString(2, "Subject using a named param");
		final Date now = new Date(DateUtils.now());
		log.debug(now);
		nps.setDate(3, now);
		final ResultSet rs = nps.executeQuery();
		assertTrue(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();
		con.close();
	}

	/**
	 * Test for the call 'new' of a structure with three fields: one string and two collections. First collection's
	 * values are given as parameters, the other collection's value is given as an inline string.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void newStructParam() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final PreparedStatement stm = con.prepareStatement("new ePrints({dc:title=?, dc:subject=[?, ?], dc:creator=['?alessia']});");
		stm.setString(1, "the?Title");
		stm.setString(2, "aSubject1");
		stm.setString(3, "aSubject2");
		stm.execute();
		final ResultSet rs = stm.getResultSet();
		assertTrue(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 the call 'new' of a relation: first creates two ePrints, then linked them through a 'cites'
	 * relationship.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void newRelation() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final String query = "Object a = new ePrints({dc:title='a'}); Object b = new ePrints({dc:title='b'}); new cites(a,b)";
		final Statement s = con.createStatement();
		s.execute(query);
		final ResultSet rs = s.getResultSet();
		assertTrue(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();
		con.close();
	}

	@Test
	public void newAtomURL() throws SQLException, MalformedURLException {
		this.atomTypeDAO.save(this.atomType);
		this.setTypeDAO.save(this.setType);
		this.theSet.setObjectType(this.setType);
		this.extSetDAO.save(this.theSet);
		final String strToJpg = "http://www.savingadvice.com/images/blog/hello-kitty-killer.jpg";
		final URL urlToJpg = new URL(strToJpg);
		final Connection con = this.dorotyDataSource.getConnection();
		final PreparedStatement ps = con.prepareStatement("new atomSet(?,?)");
		ps.setURL(1, urlToJpg);
		ps.setBoolean(2, false);
		assertTrue(ps.execute());
		final ResultSet rs = ps.getResultSet();
		assertTrue(rs.next());
		final InformationObject obj = (InformationObject) rs.getObject(1);
		assertNotNull(obj);
		log.debug(obj);
		final String setName = obj.getSet();
		assertTrue(setName.equals("atomSet"));

		rs.close();
		con.close();
	}

	@Test
	public void newAtomLocal() throws SQLException, MalformedURLException {
		this.atomTypeDAO.save(this.atomType);
		this.setTypeDAO.save(this.setType);
		this.theSet.setObjectType(this.setType);
		this.extSetDAO.save(this.theSet);
		final String strToJpg = "file:///home/lexis/payloads/ciaoVeroJpg.jpg";
		final URL urlToJpg = new URL(strToJpg);
		final Connection con = this.dorotyDataSource.getConnection();
		final PreparedStatement ps = con.prepareStatement("new atomSet(?,?)");
		ps.setURL(1, urlToJpg);
		ps.setBoolean(2, true);
		assertTrue(ps.execute());
		final ResultSet rs = ps.getResultSet();
		assertTrue(rs.next());
		final InformationObject obj = (InformationObject) rs.getObject(1);
		assertNotNull(obj);
		log.debug(obj);
		final String setName = obj.getSet();
		assertTrue(setName.equals("atomSet"));

		rs.close();
		con.close();
	}

	/**
	 * Test the assignment of a new structure to an already used variable. Transactional is needed to get the structure
	 * contents.
	 * 
	 * @throws SQLException
	 */
	@Test
	@Transactional
	public void declInit_AssignStruct() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final PreparedStatement stm = con.prepareStatement("Structure s = new ePrints({dc:title=?}); s = new ePrints({dc:title=?});");
		final String title1 = "MyTitle for ePrints";
		final String title2 = "Another ?ePrints for fun";
		stm.setString(1, title1);
		stm.setString(2, title2);
		assertTrue(stm.execute());
		final ResultSet rs = stm.getResultSet();
		assertTrue(rs.next());
		log.debug(rs.getObject(1));
		final InformationObject obj = (InformationObject) rs.getObject(1);
		final Structure s = this.structDAO.getByID(obj.getId());
		log.debug(s);
		for (final String label : s.getStructureContent().getLabels()) {
			log.debug(label + " = " + s.getStructureContent().get(label));

		}
		assertEquals(s.getStructureContent().get("dc_title").getJavaObject(), title2);

		rs.close();
		stm.close();
		con.close();
	}

	//********************************DROP****************************************************************//
	/**
	 * Test for drop: selects one object from a Set (not UnionSet) and tries to drop it.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void callDrop() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute(" select ePubs limit 1");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		assertTrue(rs.next());
		final InformationObject obj = (InformationObject) rs.getObject(1);
		rs.close();
		final PreparedStatement pst = this.dorotyDataSource.getConnection().prepareStatement("ePubs.drop(?)");
		pst.setObject(1, obj);
		pst.execute();
		final ResultSet rs2 = pst.getResultSet();
		log.debug("Reading result");
		assertTrue(rs2.next());
		log.debug(rs2.getObject(1));
		rs2.close();
		pst.close();
		assertTrue(st.execute("select ePubs[@id = " + obj.getId() + "]"));
		assertFalse(st.getResultSet().next());
		st.close();
	}

	/**
	 * Test for drop: selects one object from a Set (not UnionSet) and tries to drop it.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void callDrop1() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute(" select ePubs limit 1");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		assertTrue(rs.next());
		final InformationObject obj = (InformationObject) rs.getObject(1);
		rs.close();
		final PreparedStatement pst = this.dorotyDataSource.getConnection().prepareStatement("Object o =?; ePubs.drop(o)");
		pst.setObject(1, obj);
		pst.execute();
		final ResultSet rs2 = pst.getResultSet();
		log.debug("Reading result");
		assertTrue(rs2.next());
		log.debug(rs2.getObject(1));
		rs2.close();
	}

	/**
	 * Test for drop: tries to drop a null object.
	 * 
	 * @throws SQLException
	 */
	@Test(expected = SQLException.class)
	public void callDropNull() throws SQLException {
		final PreparedStatement pst = this.dorotyDataSource.getConnection().prepareStatement("Object o =?; ePubs.drop(o)");
		pst.setObject(1, null);
		pst.execute();
		final ResultSet rs2 = pst.getResultSet();
		log.debug("Reading result");
		while (rs2.next())
			log.debug(rs2.getObject(1));
		rs2.close();
	}

	/**
	 * Test for drop: creates and drops an object in the same query.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void callDrop2() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("Object o = new ePrints({dc:title='ora ti rimuovo subito da ePrints!'}); ePrints.drop(o) ");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		assertTrue(rs.next());
		log.debug(rs.getObject(1));
		rs.close();
	}

	/**
	 * Test for drop: drops an object from a UnionSet.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void callDropFromUnion() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute(" select ePrints limit 1");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		assertTrue(rs.next());
		final InformationObject obj = (InformationObject) rs.getObject(1);
		rs.close();
		final PreparedStatement pst = this.dorotyDataSource.getConnection().prepareStatement("Object o =?; Components.drop(o)");
		pst.setObject(1, obj);
		pst.execute();
		final ResultSet rs2 = pst.getResultSet();
		log.debug("Reading result");
		assertTrue(rs2.next());
		log.debug(rs2.getObject(1));
		rs2.close();
		assertTrue(st.execute("select ePrints[@id = " + obj.getId() + "]"));
		assertFalse(st.getResultSet().next());
		assertTrue(st.execute("select Components[@id = " + obj.getId() + "]"));
		assertFalse(st.getResultSet().next());
		st.close();
	}

	//********************************EXPORT DMF****************************************************************//

	/**
	 * Test for dmf export: creates and exports an eprint in the same query.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void callExportDMF() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("Object o = new ePrints({dc:title='THIS IS A STRUCTURE TO EXPORT'}); o.exportDMF() ");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		while (rs.next())
			log.debug(rs.getString(1));
		rs.close();
	}

	/**
	 * Test for dmf export: exports an eprint.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void callExportDMF2() 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("Object ugo = ?; ugo.exportDMF()");
		pst.setObject(1, obj);
		pst.execute();
		final ResultSet rs2 = pst.getResultSet();
		log.debug("Reading result");
		while (rs2.next())
			log.debug(rs2.getString(1));
		rs2.close();
	}

	/**
	 * Test for dmf export: tries to export a null object.
	 * 
	 * @throws SQLException
	 */
	@Test(expected = SQLException.class)
	public void callExportDMFNull() throws SQLException {
		final PreparedStatement pst = this.dorotyDataSource.getConnection().prepareStatement("Object ugo = ?; ugo.exportDMF()");
		pst.setObject(1, null);
		pst.execute();
		final ResultSet rs2 = pst.getResultSet();
		log.debug("Reading result");
		while (rs2.next())
			log.debug(rs2.getString(1));
		rs2.close();
	}

	/**
	 * Test for DMF export: calling export on a wrapper should export the referenced object
	 * 
	 * @throws SQLException
	 */
	@Test
	public void callExportDMFWrapper() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("Object o = new ePrints({dc:title='THIS IS A STRUCTURE TO EXPORT FROM WRAPPER'}); Object w = ePubs.cast(o); w.exportDMF() ");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		while (rs.next())
			log.debug(rs.getString(1));
		rs.close();
	}

	//*************************************CAST/UNCAST***************************************************//
	/**
	 * Test for cast: casts an eprints in the Set ePubs. Transactional is needed because we access to the collection of
	 * belonging sets of the wrapper generated by the cast.
	 * 
	 * @throws SQLException
	 */
	@Test
	@Transactional
	public void testCast() throws SQLException {
		final Statement st = this.dorotyDataSource.getConnection().createStatement();
		st.execute("select ePrints");
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		InformationObject obj1 = null;
		if (rs.next())
			obj1 = (InformationObject) rs.getObject(1);
		rs.close();
		final PreparedStatement pst = this.dorotyDataSource.getConnection().prepareStatement("ePubs.cast(?);");
		pst.setObject(1, obj1);
		assertTrue(pst.execute());
		final ResultSet rs2 = pst.getResultSet();
		log.debug("Reading result");
		InformationObject obj = null;
		if (rs2.next()) {
			obj = (InformationObject) rs2.getObject(1);
			assertNotNull(obj);
			log.debug(obj);
			final String setName = obj.getSet();
			assertTrue(setName.equals("ePubs"));
		}
		rs2.close();
		final Wrapper w = this.wrapperDAO.getByID(obj.getId());
		assertNotNull(w);
		assertEquals("ePrints", w.getTagSet().getName());
		assertEquals("ePubs", w.getBelongingSets().iterator().next().getName());
		log.debug(w);
	}

	/**
	 * Test for cast: tries to cast a structure of eprints in the relation set aggregates
	 * 
	 * @throws SQLException
	 */
	@Test(expected = SQLException.class)
	public void testWrongCast() 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("aggregates.cast(?)");
		pst.setObject(1, obj);
		final ResultSet rs2 = pst.executeQuery();
		if (rs2.next())
			log.debug(rs2.getObject(1));
		rs2.close();
		pst.close();
	}

	//@Test
	public void testUncast() {
		fail("Uncast not yet implemented!");
	}

	//*******************************ASSIGNMENTS************************************//

	/**
	 * Test for assignements: assignement of a string to a variable.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testStringAssign() throws SQLException {
		final Connection con = this.dorotyDataSource.getConnection();
		final Statement stm = con.createStatement();
		stm.execute("string s='ciao'; s");
		final ResultSet rs = stm.getResultSet();
		assertTrue(rs.next());
		assertEquals(rs.getString(1), "ciao");
		rs.close();
		stm.close();
		con.close();
	}

	/**
	 * Test for a SelectOneExpression: result of a select is assigned to a variable.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void assignSelectOne() throws SQLException {
		final PreparedStatement st = this.dorotyDataSource.getConnection().prepareStatement("Object o = select ePubs;");
		st.execute();
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		assertTrue(rs.next());
		final InformationObject o = (InformationObject) rs.getObject(1);
		log.debug(o);
		assertEquals(o.getSet(), "ePubs");
		rs.close();
	}

	/**
	 * Test for a SelectOneExpression: two selects are assigned to two different variables, the first is returned.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void assignSelectOne1() throws SQLException {
		final PreparedStatement st = this.dorotyDataSource.getConnection().prepareStatement("Object o = select ePubs; Object x = select ePrints; o; ");
		st.execute();
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		assertTrue(rs.next());
		final InformationObject o = (InformationObject) rs.getObject(1);
		log.debug(o);
		assertEquals(o.getSet(), "ePubs");
		rs.close();
	}

	/**
	 * Test for a SelectOneExpression: assignement of a variable to an unexisting object. Expects the variable contains
	 * null.
	 * 
	 * @throws SQLException
	 */
	@Test
	public void assignSelectOneNull() throws SQLException {
		final PreparedStatement st = this.dorotyDataSource.getConnection().prepareStatement("Object o = select ePubs[@dc:title=?];");
		st.setString(1, "UnexistingTitle...");
		st.execute();
		final ResultSet rs = st.getResultSet();
		log.debug("Reading result");
		assertTrue(rs.next());
		final InformationObject o = (InformationObject) rs.getObject(1);
		log.debug(o);
		assertEquals(o, null);
		rs.close();
	}

	/**
	 * Test for the assignement of a variable to a Set, so that a new can be called on a variable holding the set name
	 * instead of the set name directly.
	 * 
	 * @throws SQLException
	 */
	@Test
	@DirtiesContext
	public void testSetAssignment() throws SQLException {
		final PreparedStatement stmt = this.dorotyDataSource
				.getConnection()
				.prepareStatement(
						"Object coSet = ?; new coSet({dr:CobjTypology = 'compound object', dr:CobjCategory = ?, dri:objIdentifier = ?, dc:title=?, dc:description = ?, dc:creator = [?], dr:repositoryName = ?, dr:repositoryInstitution = ?});");
		final String set = "ePubs";
		final String category = "0015";
		final String epId = "epId-bla-bla-bla";
		stmt.setString(1, set);
		stmt.setString(2, category);
		stmt.setString(3, epId);
		stmt.setString(4, "The title bla bla bla");
		stmt.setString(5, "Description b la bla bla");
		stmt.setString(6, "The creator bla bla bla");
		stmt.setString(7, "repositoryName bla bla bla");
		stmt.setString(8, "repositoryInstitution bla bla bla");
		stmt.execute();
		final ResultSet rs = stmt.getResultSet();
		log.debug("Reading result");
		assertTrue(rs.next());
		log.debug(rs.getObject(1));
		rs.close();
		this.dorotyDataSource.getConnection().close();
	}

}