package cz.michal.database.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.Properties;
import java.util.TimeZone;

/**
 * Test demonstrating the bug in Connector/J when OffsetDateTime is used.
 *
 * <h2>Versions</h2>
 *
 * <ul>
 * <li>Connector/J version: 8.0.15
 * <li>MySQL server version: 5.7.25-0ubuntu0.18.04.2
 * <li>JDK: jdk1.8.0_202
 * <li>Client OS: Windows 7 x64
 * </ul>
 *
 * <h2>Test database schema</h2>
 *
 * <pre>
	CREATE DATABASE test;
	USE test;
	CREATE TABLE test ( dt DATETIME NOT NULL, ts TIMESTAMP NOT NULL );
	INSERT INTO test VALUES( NOW(), NOW() );
 * </pre>
 */
public class MySQLOffsetDateTimeTest
{
	/**
	 * Database host to connect to.
	 */
	private static final String HOST = "FIXME";

	/**
	 * Username to connect to the database.
	 */
	private static final String USER_NAME = "FIXME";

	/**
	 * Password to connect to the database.
	 */
	private static final String PASSWORD = "FIXME";

	/**
	 * Database to connect to.
	 */
	private static final String DATABASE = "test";

	/**
	 * Main application entry point.
	 */
	public static void main( String[] arguments )
	{
		MySQLOffsetDateTimeTest test = new MySQLOffsetDateTimeTest();
		try
		{
			test.run();
			System.out.println( "Demonstration complete." );
		}
		catch ( SQLException e )
		{
			System.out.println( "Exception thrown. This test isn't supposed to do it." );
		}
	}

	/**
	 * Run the test.
	 */
	private void run() throws SQLException
	{
		// Connect to the database.
		connect();

		// Set timezone to something else than UTC.
		// See the comment at loadOffsetDateTimeFromTimestamp() for an explanation why we store the zone.
		Statement statement = connection.createStatement();
		statement.execute( "SET time_zone = '+10:00'" );
		zone = TimeZone.getTimeZone( "GMT+10" );

		// Load the first column of the TEST table.
		statement = connection.createStatement();
		ResultSet row = statement.executeQuery( "SELECT * FROM test" );
		row.next();

		// Attempt to parse the columns as OffsetDateTime.
		tryOffsetDateTime( row, "dt" );
		tryOffsetDateTime( row, "ts" );
	}

	/**
	 * Main part of the test, including an explanation
	 */
	private void tryOffsetDateTime( ResultSet row, String column ) throws SQLException
	{
		// Print the string value for comparison.
		String stringValue = row.getString( column );
		System.out.println( "String value of column " + column + " = '" + stringValue + "'." );

		// Attempt to retrieve the OffsetDateTime.
		try
		{
			@SuppressWarnings( "unused" )
			OffsetDateTime date = row.getObject( column, OffsetDateTime.class );
		}
		catch ( SQLException e )
		{
			System.out.println( "getObject( ..., OffsetDateTime.class ) failed with an exception." );
			e.printStackTrace();
		}

		// Explanation why this fails:
		//
		// 1. The DATETIME or TIMESTAMP columns are returned by MySQL in the format "2019-02-24 20:59:27".
		//
		// 2. Connector/J contains support for OffsetDateTime, which looks like this:
		// (Code taken from ResultSetImpl.getObject, line 1446 and on)
		// 		} else if (type.equals(OffsetDateTime.class)) {
		// 		try {
		// 			String odt = getString(columnIndex);
		// 			return odt == null ? null : (T) OffsetDateTime.parse(odt);
		// 		} catch (DateTimeParseException e) {
		// 			// Let it continue and try by object deserialization.
		// 		}
		//
		// 3. call to GetObject( OffsetDateTime.class ) gets inside the if.
		// odt string is retrieved, in the format "2019-02-24 20:59:27"
		// OffsetDateTime.parse() however expects a different format "2007-12-03T10:15:30+01:00"
		// OffsetDateTime.parse() throws an exception, which is caught in the code - and ResultSetImpl.getObject()
		// continues to its end, where it does
		// 		throw SQLError.createSQLException("Conversion not supported for type " + type.getName(), MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT,
		// 			getExceptionInterceptor());
		//
		// Some comments what could be done about it:
		// A. For DateTime columns: they don't store any timezone information.
		//    The only way how to convert the value to OffsetDateTime is to assume some timezone - say UTC.
		//    Conversion from a string in the format "2019-02-24 20:59:27" can be done using
		//		LocalDateTime local = resultSet.getObject( column, LocalDateTime.class );
		//      return ( local != null ) ? local.atOffset( ZoneOffset.UTC ) : null;
		// B. For TIMESTAMP, which are printed in the timezone specific to the connection,
		//    the conversion would need to take into account that timezone.
		//    this.session.getServerSession().getDefaultTimeZone()
		//
		// See the two attempts at implementations (outside of the ResultSetImpl, so using some hacks) below.

		OffsetDateTime offset;
		if ( "dt".equals( column  ) )
		{
			offset = loadOffsetDateTimeFromDateTime( row, column );
		}
		else
		{
			offset = loadOffsetDateTimeFromTimestamp( row, column );
		}
		System.out.println( "When loaded using a hack, the result is " + offset.toString() + ". In UTC, this is " + offset.withOffsetSameInstant( ZoneOffset.UTC ) + '.' );
	}

	/**
	 * Time zone used by the connection.
	 * <p>
	 * I can't get the actual time zone from ResultSetImpl, so I just manually create it here.
	 */
	private TimeZone zone;

	/**
	 * Sample how ResultSetImpl.getObject( OffsetDateTime.class ) could be done for a DATETIME column.
	 */
	private OffsetDateTime loadOffsetDateTimeFromDateTime( ResultSet row, String column ) throws SQLException
	{
		LocalDateTime local = row.getObject( column, LocalDateTime.class );
		return local.atOffset( ZoneOffset.UTC );
	}

	/**
	 * Sample how ResultSetImpl.getObject( OffsetDateTime.class ) could be done for a TIMESTAMP column.
	 * <p>
	 * Note that this function requires TimeZone, which needs to be the TimeZone that the connection uses.
	 * This is available inside ResultSetImpl using
	 * <pre><code>
	 * this.session.getServerSession().getDefaultTimeZone())
	 * </code></pre>
	 * (e.g. inside ResultSetImpl.getTime() ), but I can't get it here - so I'll just create a random one
	 * that matches my connection timezone. This is just for a demonstration.
	 */
	private OffsetDateTime loadOffsetDateTimeFromTimestamp( ResultSet row, String column ) throws SQLException
	{
		int zoneOffsetMilliseconds = zone.getOffset( System.currentTimeMillis() );
		int zoneOffsetSeconds = zoneOffsetMilliseconds / 1000;
		ZoneOffset offset = ZoneOffset.ofTotalSeconds( zoneOffsetSeconds );
		LocalDateTime local = row.getObject( column, LocalDateTime.class );
		return local.atOffset( offset );
	}

	/**
	 * The connection to the database.
	 */
	private Connection connection;

	/**
	 * Connect to the database.
	 */
	private void connect() throws SQLException
	{
		Properties properties = new Properties();
		properties.setProperty( "user", USER_NAME );
		properties.setProperty( "password", PASSWORD );
		String url = "jdbc:mysql://" + HOST + "/" + DATABASE;
		connection = DriverManager.getConnection( url, properties );
	}
}