package eu.dnetlib.enabling.database.utils;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import static org.mockito.Matchers.anyObject;
import static org.mockito.Matchers.anyString;
import static org.mockito.Mockito.atLeastOnce;
import static org.mockito.Mockito.never;
import static org.mockito.Mockito.times;
import static org.mockito.Mockito.verify;
import static org.mockito.Mockito.when;

import java.io.StringReader;
import java.sql.Date;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.velocity.app.VelocityEngine;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mockito.Mock;
import org.mockito.runners.MockitoJUnit44Runner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import eu.dnetlib.enabling.database.DataSourceFactory;
import eu.dnetlib.enabling.database.TransactionTemplateFactory;
import eu.dnetlib.enabling.database.objects.DnetDatabase;
import eu.dnetlib.enabling.database.rmi.DatabaseException;

@RunWith(MockitoJUnit44Runner.class)
public class DatabaseUtilsTest {

	private static final String DB = "THE_DB";
	private static final String TABLE = "THE_TABLE";
	private static final String QUERY = "SELECT age FROM persons";
	private static final String RESOURCE_ID = "id1234";
	private static final String DB_PREFIX = "THE_";
	private static final String MAIN_DB = "MAIN_DB";

	// Class Under Test
	private DatabaseUtils dbUtils;

	private Map<String, Object> MAP;

	private DnetDatabase DNET_DB = new DnetDatabase();

	@Mock
	private JdbcTemplateFactory jdbcTemplateFactory;
	@Mock
	private JdbcTemplate jdbcTemplate;
	@Mock
	private DataSource dataSource;
	@Mock
	private VelocityEngine velocityEngine;
	@Mock
	private DataSourceFactory dataSourceFactory;
	@Mock
	private TransactionTemplateFactory transactionTemplateFactory;
	@Mock
	private TransactionTemplate transactionTemplate;

	@SuppressWarnings("unchecked")
	@Before
	public void setUp() throws Exception {
		dbUtils = new DatabaseUtils();

		DNET_DB.setDbName(DB);

		MAP = new HashMap<String, Object>();
		MAP.put("k1", "v1");
		MAP.put("k2", "v2");
		MAP.put("k3", "v3");

		dbUtils.setJdbcTemplateFactory(jdbcTemplateFactory);
		dbUtils.setDataSourceFactory(dataSourceFactory);
		dbUtils.setTransactionTemplateFactory(transactionTemplateFactory);
		dbUtils.setVelocityEngine(velocityEngine);
		dbUtils.setDbPrefix(DB_PREFIX);
		dbUtils.setNumbersOfRecordsForTransaction(20);
		dbUtils.setMainDB(MAIN_DB);

		when(jdbcTemplateFactory.createJdbcTemplate(dataSource)).thenReturn(jdbcTemplate);
		when(jdbcTemplateFactory.createJdbcTemplate(DB)).thenReturn(jdbcTemplate);
		when(jdbcTemplateFactory.createJdbcTemplate(MAIN_DB)).thenReturn(jdbcTemplate);

		when(jdbcTemplate.queryForList(QUERY, Integer.class)).thenReturn(Arrays.asList(1, 2, 3, 4));
		when(jdbcTemplate.queryForList(QUERY)).thenReturn(Arrays.asList("1", "2", "3", "4"));
		when(jdbcTemplate.queryForList("SELECT * FROM " + TABLE)).thenReturn(Arrays.asList(MAP, MAP, MAP));

		when(jdbcTemplate.queryForList("SELECT * FROM information_schema.columns WHERE table_name = ?", new Object[] { TABLE })).thenReturn(
				Arrays.asList(MAP, MAP, MAP));
		when(jdbcTemplate.queryForList("SELECT profileid FROM dnet_tables WHERE table_name='THE_TABLE' AND database_db_name='THE_DB'", String.class))
				.thenReturn(Arrays.asList("1234"));

		when(dataSourceFactory.createDataSource(anyString())).thenReturn(dataSource);
		when(jdbcTemplate.getDataSource()).thenReturn(dataSource);

		when(transactionTemplateFactory.createTransactionTemplate(dataSource)).thenReturn(transactionTemplate);

	}

	@Test
	public void testListCommonDBTables() throws Exception {
		dbUtils.listCommonDBTables(DB);
		verify(jdbcTemplateFactory).createJdbcTemplate(DB);
		verify(jdbcTemplate).queryForList(
				"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type != 'VIEW' AND table_name NOT LIKE '%_log'",
				String.class);
	}

	@Test
	public void testListCommonDBViews() throws Exception {
		dbUtils.listCommonDBViews(DB);
		verify(jdbcTemplateFactory).createJdbcTemplate(DB);
		verify(jdbcTemplate).queryForList(
				"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'VIEW' AND table_name NOT LIKE '%_log'",
				String.class);
	}

	@Test
	public void testListAllDatabases() throws Exception {
		dbUtils.listAllDatabases();
		verify(jdbcTemplateFactory).createJdbcTemplate(MAIN_DB);
		verify(jdbcTemplate).queryForList("SELECT datname FROM pg_database WHERE datname LIKE '" + DB_PREFIX + "%'", String.class);
	}

	@Test
	public void testCreateDatabase() throws Exception {
		dbUtils.createDatabase(DB);
		verify(jdbcTemplateFactory).createJdbcTemplate(MAIN_DB);
		verify(jdbcTemplate).update("CREATE DATABASE " + DB);
	}

	@Test(expected = DatabaseException.class)
	public void testCreateDatabase_err() throws Exception {
		dbUtils.createDatabase("DA TA; BA ; {SE}");
	}

	@Test(expected = DatabaseException.class)
	public void testCreateDatabase_err_2() throws Exception {
		dbUtils.createDatabase(null);
	}

	@Test
	public void testDropDatabase() throws Exception {
		dbUtils.dropDatabase(DB);
		verify(jdbcTemplateFactory).createJdbcTemplate(MAIN_DB);
		verify(jdbcTemplate).update("DROP DATABASE IF EXISTS " + DB);
	}

	@Test
	public void testGetTypedListFromSql() throws Exception {
		List<Integer> list = dbUtils.getTypedListFromSql(DB, QUERY, Integer.class);
		verify(jdbcTemplateFactory).createJdbcTemplate(DB);
		verify(jdbcTemplate).queryForList(QUERY, Integer.class);
		assertEquals(4, list.size());
	}

	@Test
	public void testGetSimpleListFromSql() throws Exception {
		List<String> list = dbUtils.getSimpleListFromSql(DB, QUERY);
		verify(jdbcTemplateFactory).createJdbcTemplate(DB);
		verify(jdbcTemplate).queryForList(QUERY);
		assertEquals(4, list.size());
	}

	@Test
	public void testExecuteSql() throws Exception {
		dbUtils.executeSql(DB, QUERY, null);
		verify(jdbcTemplateFactory).createJdbcTemplate(DB);
		verify(jdbcTemplate).update(QUERY);
	}

	@Test
	public void testDescribeTable() throws Exception {
		List<Map<?, ?>> res = dbUtils.describeTable(DB, TABLE);
		verify(jdbcTemplateFactory).createJdbcTemplate(DB);
		assertEquals(3, res.size());
		assertEquals(3, res.get(0).size());
		assertEquals(3, res.get(1).size());
		assertEquals(3, res.get(2).size());
	}

	@Test
	public void testDumpTableAsXML() throws Exception {
		String xml = dbUtils.dumpTableAsXML(DB, TABLE);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);
		assertNotNull(xml);
		assertTrue(xml.contains(TABLE));
		assertTrue(xml.contains(DB));
		assertTrue(xml.contains("ROW"));
	}

	@Test
	public void testDumpTableAsList() throws Exception {
		List<Document> list = dbUtils.dumpTableAsList(DB, TABLE);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);
		verify(jdbcTemplate).queryForList("SELECT * FROM " + TABLE);
		assertTrue(list.size() > 0);
	}

	@Test
	public void testGetRowByResourceId() throws Exception {
		dbUtils.getRowByResourceId(DB, TABLE, RESOURCE_ID);
		verify(jdbcTemplateFactory).createJdbcTemplate(DB);
		verify(jdbcTemplate).queryForMap("SELECT * FROM " + TABLE + " WHERE " + DatabaseUtils.DNET_RESOURCE_ID_FIELD + "=?", new Object[] { RESOURCE_ID });
	}

	@Test
	public void testImportFromIterable() throws Exception {
		List<String> iterable = Lists.newArrayList("<ROWS><ROW table='t1' /></ROWS>", "<ROWS><ROW table='t2' /></ROWS>");

		GenericRow row1 = new GenericRow(TABLE, MAP, true);
		GenericRow row2 = new GenericRow(TABLE, MAP, false);;

		when(transactionTemplate.execute((TransactionCallback) anyObject())).thenReturn(Lists.newArrayList(row1, row2, row1, row2));

		dbUtils.importFromIterable(DB, iterable);

		verify(jdbcTemplateFactory).createJdbcTemplate(dataSource);
		verify(transactionTemplateFactory).createTransactionTemplate(dataSource);

		verify(transactionTemplate, times(1)).execute((TransactionCallback) anyObject());
	}

	@Test
	public void testDeleteRowByResourceId() throws Exception {
		dbUtils.deleteRowByResourceId(DB, TABLE, RESOURCE_ID);
		verify(jdbcTemplateFactory).createJdbcTemplate(DB);
		verify(jdbcTemplate).update("DELETE FROM " + TABLE + " WHERE " + DatabaseUtils.DNET_RESOURCE_ID_FIELD + "=?", new Object[] { RESOURCE_ID });
	}

	@Test
	public void testClearTable() throws Exception {
		dbUtils.clearTable(DB, TABLE);
		verify(jdbcTemplateFactory).createJdbcTemplate(DB);
		verify(jdbcTemplate).update("DELETE FROM " + TABLE);
	}

	@Test
	public void testPrepareManagementOfTable() throws Exception {
		when(jdbcTemplate.queryForInt(anyString(), (Object[]) anyObject())).thenReturn(1);

		dbUtils.prepareManagementOfTable(DB, TABLE);
		verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB);
		verify(jdbcTemplate, atLeastOnce()).queryForInt(anyString(), (Object[]) anyObject());
		verify(jdbcTemplate, never()).update(anyString());
	}

	@Test
	public void testPrepareManagementOfTable_2() throws Exception {
		when(jdbcTemplate.queryForInt(anyString(), (Object[]) anyObject())).thenReturn(0);

		dbUtils.prepareManagementOfTable(DB, TABLE);
		verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB);
		verify(jdbcTemplate, atLeastOnce()).queryForInt(anyString(), (Object[]) anyObject());
		verify(jdbcTemplate, atLeastOnce()).update(anyString());
	}

	@Test
	public void testClearManagementOfTable() throws Exception {
		when(jdbcTemplate.queryForInt(anyString(), (Object[]) anyObject())).thenReturn(0);

		dbUtils.removeManagementOfTable(DB, TABLE);
		verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB);

		verify(jdbcTemplate, never()).update(anyString());
	}

	@Test
	public void testClearManagementOfTable_2() throws Exception {
		when(jdbcTemplate.queryForInt(anyString(), (Object[]) anyObject())).thenReturn(1);

		dbUtils.removeManagementOfTable(DB, TABLE);
		verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB);

		verify(jdbcTemplate, atLeastOnce()).update(anyString());
	}

	@Test
	public void testParseDate() throws Exception {
		Date d = dbUtils.parseDate("31-05-2011", "dd-MM-yyyy");
		assertEquals("2011-05-31", d.toString());
	}

	@Test
	public void testEscape() throws Exception {
		Map<String, String> m = Maps.newHashMap();

		m.put("url", "http://www.google.com/set?a=val1&b=val2");
		m.put("title", "My favourite movie title");

		Document doc = dbUtils.rowToDocument(m);
		assertNotNull(doc);

		Document doc2 = DocumentHelper.createDocument();
		Element elem = doc2.addElement("a");
		elem.setText(doc.asXML());
		doc2.setRootElement(elem);

		String tempXML = doc2.asXML();

		SAXReader reader = new SAXReader();
		Document doc3 = reader.read(new StringReader(tempXML));

		System.out.println(doc.asXML());;
		System.out.println(doc3.getRootElement().getText());;

		assertEquals(doc.asXML(), doc3.getRootElement().getText());

	}

}