package eu.dnetlib.dlms.jdbc;

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

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.Map;
import java.util.Random;
import java.util.Map.Entry;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.junit.Before;
import org.junit.Ignore;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration
@Ignore
public class WSRemoteDataSourceTest {

	@Resource
	private transient DataSource dataSource;

	private transient Connection conn;

	@Before
	public void setUp() throws Exception {
		this.conn = this.dataSource.getConnection();
	}

	@Test
	public void testString() throws SQLException {
		final Connection conn = this.dataSource.getConnection();

		assertNotNull(conn);

		final Statement stmt = conn.createStatement();
		stmt.execute("'ciao'");

		final ResultSet rs = stmt.getResultSet();
		assertTrue(rs.next());

		final String val = rs.getString(1);
		assertEquals("ciao", val);
	}

	@Test
	public void testInt() throws SQLException {
		final Connection conn = this.dataSource.getConnection();

		assertNotNull(conn);

		final Statement stmt = conn.createStatement();
		stmt.execute("1");

		final ResultSet rs = stmt.getResultSet();
		assertTrue(rs.next());

		final int val = rs.getInt(1);
		assertEquals(1, val);
	}

	@Test(expected = Exception.class)
	public void testWrongQuery() throws SQLException {
		final Connection conn = this.dataSource.getConnection();

		assertNotNull(conn);

		final Statement stmt = conn.createStatement();
		final ResultSet rs = stmt.executeQuery("select nonExistingSet");
		assertTrue(rs.next());
	}

	@Test
	public void testIntExecuteQuery() throws SQLException {
		final Connection conn = this.dataSource.getConnection();

		assertNotNull(conn);

		final Statement stmt = conn.createStatement();
		final ResultSet rs = stmt.executeQuery("1");
		assertTrue(rs.next());

		final int val = rs.getInt(1);
		assertEquals(1, val);
	}

	@Test
	public void testPrepared() throws SQLException {
		final Connection conn = this.dataSource.getConnection();

		assertNotNull(conn);

		final PreparedStatement stmt = conn.prepareStatement("123");
		stmt.setInt(1, 123);
		stmt.execute();

		final ResultSet rs = stmt.getResultSet();
		assertTrue(rs.next());

		final int val = rs.getInt(1);
		assertEquals(123, val);
	}

	@Test
	public void testPreparedArgs() throws SQLException {
		final Connection conn = this.dataSource.getConnection();

		assertNotNull(conn);

		final PreparedStatement stmt = conn.prepareStatement(" ? ");
		stmt.setInt(1, 123);
		stmt.execute();

		final ResultSet rs = stmt.getResultSet();
		assertTrue(rs.next());

		final int val = rs.getInt(1);
		assertEquals(123, val);
	}

	@Test
	public void testSelect() throws SQLException {
		final Connection conn = this.dataSource.getConnection();

		assertNotNull(conn);

		final PreparedStatement stmt = conn.prepareStatement("select ePubs/composedBy limit 1");
		stmt.execute();

		final ResultSet rs = stmt.getResultSet();
		assertTrue(rs.next());

		final InformationObject val = (InformationObject) rs.getObject(1);
		System.out.println(val.getId());
		assertEquals(val.getSet(), "Components");

	}

	@Test
	public void testInformationObjectParameter() throws SQLException {
		final Connection conn = this.dataSource.getConnection();

		assertNotNull(conn);

		final PreparedStatement stmt = conn.prepareStatement("select ePubs/composedBy limit 1");
		stmt.execute();

		final ResultSet rs = stmt.getResultSet();
		assertTrue(rs.next());

		final InformationObject val = (InformationObject) rs.getObject(1);

		final PreparedStatement second = conn.prepareStatement("Object o = ?; o");
		second.setObject(1, val);
		second.execute();

		final ResultSet rs2 = second.getResultSet();
		assertTrue(rs2.next());
		final InformationObject val2 = (InformationObject) rs2.getObject(1);

		assertEquals(val2.getId(), val.getId());
		System.out.println("wowowo");
	}

	@Test
	public void testType() throws SQLException {
		final Connection conn = this.dataSource.getConnection();

		final PreparedStatement stmt = conn.prepareStatement("select ePubs limit 1");
		stmt.execute();

		final ResultSet rs = stmt.getResultSet();
		assertTrue(rs.next());

		final InformationObject val = (InformationObject) rs.getObject(1);
		assertEquals("EPubsType", val.getType());
	}

	@Test
	@Ignore
	public void testUnion() throws SQLException {
		final Connection conn = this.dataSource.getConnection();
		final PreparedStatement stmt1 = this.conn.prepareStatement("new ePrints({dr:CobjTypology = 'eprint'});");
		final ResultSet rs1 = stmt1.executeQuery();
		assertTrue(rs1.next());
		final InformationObject o = (InformationObject) rs1.getObject(1);
		assertEquals(o.getSet(), "ePrints");
		assertEquals(o.getType(), "EPrintsType");
		rs1.close();
		final PreparedStatement stmt = conn.prepareStatement("select Components[tag::ePrints[@id = ?]]");
		stmt.setLong(1, o.getId());
		stmt.execute();
		final ResultSet rs2 = stmt.getResultSet();
		assertTrue(rs2.next());
		final InformationObject o2 = (InformationObject) rs2.getObject(1);
		assertEquals(o2.getSet(), "Components");
		assertEquals(o2.getType(), "ComponentsUnionType");
		rs2.close();
	}

	@Test
	public void testUnion2() throws SQLException {
		final Connection conn = this.dataSource.getConnection();
		final PreparedStatement stmt = conn.prepareStatement("select Components");
		stmt.execute();
		final ResultSet rs2 = stmt.getResultSet();
		assertTrue(rs2.next());
		final InformationObject o2 = (InformationObject) rs2.getObject(1);
		assertEquals(o2.getSet(), "Components");
		assertEquals(o2.getType(), "ComponentsUnionType");
		rs2.close();
	}

	@Test
	public void testStringParameter() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("?");

		stmt.setString(1, "test");
		final ResultSet rs = stmt.executeQuery();
		assertTrue(rs.next());
		assertEquals("test", rs.getString(1));
	}

	@Test
	public void testLong() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("new ePubs({dc:title=?})");

		stmt.setString(1, "test");
		final ResultSet rs = stmt.executeQuery();
		assertTrue(rs.next());
		final InformationObject id = (InformationObject) rs.getObject(1);

		System.out.println("got id " + id);
		final PreparedStatement stmt2 = this.conn.prepareStatement("select ePubs[@id=?]");
		stmt2.setInt(1, (int) id.getId());

		final ResultSet rs2 = stmt2.executeQuery();
		assertTrue(rs2.next());
		System.out.println(rs2.getObject(1));
		assertEquals(id.getId(), ((InformationObject) rs2.getObject(1)).getId());
	}

	@Test
	public void testSelectExpression() throws SQLException {
		final Random r = new Random();
		final int randint = r.nextInt();

		final PreparedStatement stmt = this.conn.prepareStatement("new ePubs({dc:title='test" + randint + "'})");
		final ResultSet rs = stmt.executeQuery();
		assertTrue(rs.next());
		final InformationObject obj1 = (InformationObject) rs.getObject(1);

		assertNotNull(obj1);
		System.out.println("created");

		final PreparedStatement stmt2 = this.conn.prepareStatement("Object x = select ePubs[@dc:title='test" + randint + "']; x");
		final ResultSet rs2 = stmt2.executeQuery();
		assertTrue(rs2.next());
		final InformationObject obj2 = (InformationObject) rs2.getObject(1);

		assertEquals(obj1.getId(), obj2.getId());
	}

	@Test
	public void testDate() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("new ePubs({dc:dateAccepted = ?})");
		stmt.setDate(1, new Date(0));
		stmt.execute();
		final ResultSet rs = stmt.getResultSet();
		assertTrue(rs.next());
		System.out.println(rs.getObject(1));
	}

	@Test
	public void testComplex() throws SQLException {
		final PreparedStatement stmt = this.conn
				.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 = "AggregationObjects";
		final String category = "0016";

		stmt.setString(1, set);
		stmt.setString(2, category);
		stmt.setString(3, "884381c2-604d-4bca-9aea-c7d8a750d387");
		stmt.setString(4, "Antonis cool ep");
		stmt.setString(5, "gatorade");
		stmt.setString(6, "antleb@di.uoa.gr");
		stmt.setString(7, "some repo");
		stmt.setString(8, "some institution");

		stmt.execute();

	}

	@Test
	public void testCollColumn() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("select SystemStructSets[@name='ePrints']/@fields");
		//	final ResultSet rs = stmt.executeQuery();
		stmt.execute();
		final ResultSet rs = stmt.getResultSet();
		assertTrue(rs.next());
		final Collection<Map<String, Object>> res = (Collection<Map<String, Object>>) rs.getObject(1);
		for (final Map<String, Object> o : res) {
			System.out.println("Object returned is of class " + o.getClass() + ", value = " + o);
			//final Map<String, Object> m = (Map<String, Object>) o;
			for (final Entry<String, Object> e : o.entrySet())
				System.out.println(e.getKey() + " = " + e.getValue());
		}
		rs.close();
		stmt.close();
	}

	@Test
	public void testMultiColumn() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("select ePrints/@(dc:title|dc:subject) limit 1");
		final ResultSet rs = stmt.executeQuery();
		assertTrue(rs.next());
		System.out.println(rs.getString(1));
		System.out.println(rs.getObject(2));
		this.printMetaData(rs.getMetaData());
	}

	@Test
	public void testSys1() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("select SystemStructSets");
		final ResultSet rs = stmt.executeQuery();
		while (rs.next())
			System.out.println(rs.getObject(1));
		this.printMetaData(rs.getMetaData());
		rs.close();
	}

	@Test
	public void testMetadataBeforeNext() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("select SystemStructSets/@name");
		final ResultSet rs = stmt.executeQuery();
		final ResultSetMetaData rsMD = rs.getMetaData();
		assertNotNull(rsMD);
		System.out.println("Metadata = " + rsMD);
		while (rs.next())
			System.out.println(rs.getString(1));
		rs.close();
	}

	@Test
	public void testMetadataAfterNext() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("select (id, url) SystemStructSets");
		final ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			System.out.println(rs.getLong(1));
			System.out.println(rs.getURL(2));
		}
		final ResultSetMetaData rsMD = rs.getMetaData();
		assertNotNull(rsMD);
		this.printMetaData(rsMD);
		rs.close();
	}

	@Test
	public void testMetadata() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("select ePrints/@(dc:title|dc:subject|dc:dateAccepted)");
		final ResultSet rs = stmt.executeQuery();
		final ResultSetMetaData rsMD = rs.getMetaData();
		assertNotNull(rsMD);
		this.printMetaData(rsMD);
		rs.close();
	}

	@Test
	public void testMetadataD() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("select SystemStructSets/@fields");
		final ResultSet rs = stmt.executeQuery();
		final ResultSetMetaData rsMD = rs.getMetaData();
		assertNotNull(rsMD);
		this.printMetaData(rsMD);
		rs.close();
	}

	@Test
	public void testInfoObjType() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("select ePubs/hasEPrint");
		final ResultSet rs = stmt.executeQuery();
		assertTrue(rs.next());
		final InformationObject o = (InformationObject) rs.getObject(1);
		assertEquals(o.getSet(), "ePrints");
		assertEquals(o.getType(), "EPrintsType");
		final ResultSetMetaData rsMD = rs.getMetaData();
		assertNotNull(rsMD);
		this.printMetaData(rsMD);
		rs.close();
	}

	@Test
	public void testSystemStructSetID() throws SQLException {

		final Statement stm = this.conn.createStatement();
		stm.execute("select SystemStructSets/@setID");
		final ResultSet rs = stm.getResultSet();
		while (rs.next()) {
			System.out.println("RS ROW: " + rs.getObject(1));
		}
		rs.close();
		stm.close();
	}

	@Test
	public void testUnionSets() throws SQLException {
		final PreparedStatement stmt = this.conn.prepareStatement("select Components");
		final ResultSet rs = stmt.executeQuery();
		while (rs.next())
			System.out.println(rs.getObject(1));
		//this.printMetaData(rs.getMetaData());
		rs.close();
	}

	private void printMetaData(final ResultSetMetaData md) throws SQLException {
		System.out.println("Column count = " + md.getColumnCount());
		System.out.println("Column Name | Type id | Type name | Class name");
		for (int i = 1; i <= md.getColumnCount(); i++)
			System.out.println(md.getColumnName(i) + " | " + md.getColumnType(i) + " | " + md.getColumnTypeName(i) + " | " + md.getColumnClassName(i));
	}
}