package com.pharos.db.dao;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.mariadb.jdbc.MariaDbDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;



public class MariaJdbcTest {
	
	private String USERNAME = "DBA_USERNAME";
	private String PASSWORD = "DBA_PASSSWORD";
	private static final String DBSERVER = "DB_SERVER";
	private static final String SCHEMA_NAME = "SCHEMA_NAME";
	private static final String CREATE_TABLE_SQL = 
			"CREATE TABLE TEST_TABLE (" +
			" ID int(11) NOT NULL," + 
			" COLUMN_1 varchar(11) COLLATE utf8_bin DEFAULT NULL," +
			" COLUMN_2 varchar(11) COLLATE utf8_bin DEFAULT NULL," +
			" COLUMN_3 varchar(11) COLLATE utf8_bin DEFAULT NULL," +
			" PRIMARY KEY (ID)" +
			" ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin";	
	
	private JdbcTemplate jdbcTemplate;

	
	@Before
	public void before() throws SQLException {
		jdbcTemplate = createJdbcTemplate();
		dropTable();
		createTable();
		jdbcTemplate.update("INSERT INTO TEST_TABLE VALUES(1,'col 1 value', 'col 2 value', null)");
	}
	
	@After
	public void after() {
		dropTable();
	}
	
    @Test
	public void testGetComments() throws SQLException {
		PreparedStatementCreator psc = new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				String selectSql = "SELECT ID, COLUMN_2, COLUMN_1, COLUMN_3 FROM TEST_TABLE";
				return con.prepareStatement(selectSql.toString());
			}
			
		};
		
		jdbcTemplate.query(psc, new RowCallbackHandler() {
			
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				
				//THIS ORDER WORKS
				String columnOne = rs.getString("COLUMN_1");
				String columnTwo = rs.getString("COLUMN_2");
				String columnThree = rs.getString("COLUMN_3");
				
				assertEquals("col 2 value", columnTwo);
				assertNull(columnThree);
				assertNotNull(columnOne);
				assertEquals("col 1 value", columnOne);
				
				//THIS ORDER WORKS
				columnThree = rs.getString("COLUMN_3");
				columnTwo = rs.getString("COLUMN_2");
				columnOne = rs.getString("COLUMN_1");
				
				assertEquals("col 2 value", columnTwo);
				assertNull(columnThree);
				assertNotNull(columnOne);
				assertEquals("col 1 value", columnOne);
				
				//THIS ORDER FAILS - it returns column 1 a value of null
				columnTwo = rs.getString("COLUMN_2");
				columnThree = rs.getString("COLUMN_3");
				columnOne = rs.getString("COLUMN_1");
				
				assertEquals("col 2 value", columnTwo);
				assertNull(columnThree);
				assertNotNull(columnOne);
				assertEquals("col 1 value", columnOne);
			}
		});
		
		jdbcTemplate.update("DELETE FROM TEST_TABLE");
	}
    
    private void createTable() {
    	jdbcTemplate.execute(CREATE_TABLE_SQL);
    }
    
    private void dropTable() {
    	jdbcTemplate.execute("DROP TABLE IF EXISTS TEST_TABLE");
    }
    
    private JdbcTemplate createJdbcTemplate() throws SQLException {
    	MariaDbDataSource ds = new MariaDbDataSource("jdbc:mysql://" + DBSERVER + "/" + SCHEMA_NAME + "?useServerPrepStmts=true");
		ds.setUserName(USERNAME);
		ds.setPassword(PASSWORD);
		return new JdbcTemplate(ds);
    }
}