import static org.hamcrest.CoreMatchers.*;
import static org.junit.Assert.*;

import java.sql.*;
import java.text.*;
import java.util.*;
import java.util.Date;

import org.junit.*;

/**
 * There is a bug with ResultSet.wasNull after getting a timestamp column with the value '0000-00-00 00:00:00'.
 * All subsequent calls to ResultSet.wasNull will always return true.
 * This is a problem because we can't check if a ResultSet.getInt that returns 0 has the database value 0 or NULL.
 */
public class WasNullTest {
	private static final String HOST = "127.0.0.1";
	private static final String PORT = "3308";
	private static final String SHEMA = "test";
	private static final String TABLE = "WasNullTest";
	private static Properties properties;

	private SimpleDateFormat sdf;
	private Connection connection;

	@BeforeClass
	public static void setUpBeforeClass() throws ClassNotFoundException {
		properties = new Properties();
		properties.put("user", "root");

		Class.forName("org.mariadb.jdbc.Driver");
	}

	@Before
	public void setUp() throws SQLException {
		connection = DriverManager.getConnection("jdbc:mariadb://" + HOST + ":" + PORT + "/" + SHEMA, properties);
		sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		createTable(connection);
		insertTestData(connection);
	}

	@After
	public void tearDown() throws SQLException {
		connection.close();
	}

	private static void createTable(Connection connection) throws SQLException {
		try (Statement stmt = connection.createStatement()) {
			boolean exists = false;
			try (ResultSet rs = stmt.executeQuery("SHOW TABLES FROM " + SHEMA + ";")) {
				while (rs.next()) {
					if (TABLE.equalsIgnoreCase(rs.getString(1))) {
						exists = true;
					}
				}
			}

			if (!exists) {
				stmt.execute("CREATE TABLE " + TABLE
						+ " (pk INT NOT NULL, created TIMESTAMP NULL, lastUpdate TIMESTAMP NULL, someValue VARCHAR(50) NULL, PRIMARY KEY (pk));");
			}
		}
	}

	private static void insertTestData(Connection connection) throws SQLException {
		try (Statement stmt = connection.createStatement()) {
			boolean exists = false;

			try (ResultSet rs = stmt.executeQuery("SELECT 1 FROM " + TABLE + " WHERE pk = 1;")) {
				exists = rs.next();
			}

			if (!exists) {
				stmt.execute("INSERT INTO " + TABLE
						+ " (pk, created, lastUpdate, someValue) VALUES ('1', '0000-00-00 00:00:00', '2019-06-07 14:10:55', 'Value');");
				stmt.execute("INSERT INTO " + TABLE
						+ " (pk, lastUpdate, someValue) VALUES ('2', '2019-06-07 14:10:55', 'Value');");
			}
		}
	}

	@Test
	public void testTimestamp00000000() throws SQLException {
		try (Statement stmt = connection.createStatement()) {
			try (ResultSet rs = stmt
					.executeQuery("SELECT pk, created, lastUpdate, someValue FROM " + TABLE + " WHERE pk = 1;")) {
				rs.next();
				// pk = 1
				assertThat(rs.getInt(1), is(1));
				assertThat(rs.wasNull(), is(false));

				// created = '0000-00-00 00:00:00'
				assertThat(rs.getTimestamp(2), nullValue());
				assertThat(rs.wasNull(), is(true));

				// lastUpdate = '2019-06-07 14:10:55'
				assertThat(rs.getTimestamp(3), notNullValue());
				assertThat(sdf.format(new Date(rs.getTimestamp(3).getTime())), is("2019-06-07 14:10:55"));
				assertThat(rs.wasNull(), is(false));

				// someValue = 'Value'
				assertThat(rs.getString(4), is("Value"));
				assertThat(rs.wasNull(), is(false));
			}
		}
	}

	@Test
	public void testTimestampNull() throws SQLException {
		try (Statement stmt = connection.createStatement()) {
			try (ResultSet rs = stmt
					.executeQuery("SELECT pk, created, lastUpdate, someValue FROM " + TABLE + " WHERE pk = 2;")) {
				rs.next();
				// pk = 2
				assertThat(rs.getInt(1), is(2));
				assertThat(rs.wasNull(), is(false));

				// created = null
				assertThat(rs.getTimestamp(2), nullValue());
				assertThat(rs.wasNull(), is(true));

				// lastUpdate = '2019-06-07 14:10:55'
				assertThat(rs.getTimestamp(3), notNullValue());
				assertThat(sdf.format(new Date(rs.getTimestamp(3).getTime())), is("2019-06-07 14:10:55"));
				assertThat(rs.wasNull(), is(false));

				// someValue = 'Value'
				assertThat(rs.getString(4), is("Value"));
				assertThat(rs.wasNull(), is(false));
			}
		}
	}

}