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.times;
import static org.mockito.Mockito.never;
import static org.mockito.Mockito.verify;
import static org.mockito.Mockito.when;

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.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 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_";
	
	
	
	// 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);
		
		when(jdbcTemplateFactory.createJdbcTemplate(dataSource)).thenReturn(jdbcTemplate);
		when(jdbcTemplateFactory.createJdbcTemplate(DB)).thenReturn(jdbcTemplate);
		when(jdbcTemplateFactory.createJdbcTemplate("")).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("");
		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("");
		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("");
		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).createJdbcTemplate(DB);
		verify(jdbcTemplate).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).createJdbcTemplate(DB);
		verify(jdbcTemplate).queryForInt(anyString(), (Object[]) anyObject());
		verify(jdbcTemplate).update(anyString());
	}

	@Test
	public void testClearManagementOfTable() throws Exception {
		when(jdbcTemplate.queryForInt(anyString(), (Object[]) anyObject())).thenReturn(0);
		
		dbUtils.removeManagementOfTable(DB, TABLE);
		verify(jdbcTemplateFactory).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).createJdbcTemplate(DB);
		
		verify(jdbcTemplate).update(anyString());
	}

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