package com.example;

import static java.lang.Math.abs;
import static java.lang.String.format;
import static java.lang.System.currentTimeMillis;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Properties;
import java.util.TimeZone;

import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.JUnit4;

/**
 * <p>The test which covers <a href="https://bugs.mysql.com/bug.php?id=92153">https://bugs.mysql.com/bug.php?id=92153</a>.</p>
 */
@RunWith(JUnit4.class)
public final class TimeZoneTest {
	private static final String MYSQL_DATABASE = "sandbox";

	private static final String MYSQL_USER = "sandbox";

	private static final String MYSQL_PASSWORD = "sandbox";

	/**
	 * Time zone of the JVM.
	 */
	private static final TimeZone CLIENT_TIME_ZONE = TimeZone.getTimeZone("UTC");

	/**
	 * Time zone of the MySQL instance (needs to be configured in my.cnf).
	 * It is assumed that the clocks of the JVM and MySQL instance are synchronized.
	 */
	private static final TimeZone SERVER_TIME_ZONE = TimeZone.getTimeZone("Europe/Moscow");

	private static final long MILLIS_IN_DAY = 86400_000L;

	/**
	 * MySQL doesn't have millisecond precision.
	 */
	private static final long MAXIMUM_ACCEPTABLE_DIFFERENCE = 1000L;

	/**
	 * <p>Use code for DATE/TIME/DATETIME/TIMESTAMP handling in result sets and
	 * statements that consistently handles time zone conversions from
	 * client to server and back again, or use the legacy code for these
	 * datatypes that has been in the driver for backwards-compatibility?
	 * Setting this property to 'false' voids the effects of
	 * {@link #useTimezone}, {@link #useJDBCCompliantTimezoneShift},
	 * {@link #useGmtMillisForDatetimes}, and {@link #useFastDateParsing}.</p>
	 *
	 * <p>Default: {@code true}</p>
	 *
	 * <p><b>Note</b>: the value of {@code false} still results in {@code
	 * current_time()} and time in general not converted from the tie zone
	 * of the server to the time zone of the JVM.</p>
	 *
	 * @since 5.1.6
	 */
	private final boolean useLegacyDatetimeCode = false;

	/**
	 * <p>Convert time/date types between client and server time zones
	 * (true/false, defaults to 'false')? This is part of the legacy
	 * date-time code, thus the property has an effect only when
	 * {@link #useLegacyDatetimeCode} is {@code true}.</p>
	 *
	 * <p>Default: {@code false}</p>
	 *
	 * <p><b>Note</b>: required if {@link #useLegacyDatetimeCode} is {@code
	 * true}.</p>
	 *
	 * @since 3.0.2
	 */
	private final boolean useTimezone = true;

	/**
	 * Don't convert DATE values using the server time zone if
	 * {@link #useTimezone} is {@code true} or
	 * {@link #useLegacyDatetimeCode} is {@code false}.
	 *
	 * <p>Default: {@code true}<p>
	 *
	 * <p><b>Note</b>: required if {@link #useLegacyDatetimeCode} is {@code
	 * true}.</p>
	 *
	 * @since 5.1.35
	 */
	private final boolean noTimezoneConversionForDateType = false;

	/**
	 * Don't convert TIME values using the server time zone if
	 * {@link #useTimezone} is {@code true}.
	 *
	 * <p>Default: {@code false}</p>
	 *
	 * <p><b>Note</b>: required if {@link #useLegacyDatetimeCode} is {@code
	 * true}.</p>
	 *
	 * @since 5.0.0
	 */
	private final boolean noTimezoneConversionForTimeType = false;

	@BeforeClass
	public static void setUpOnce() {
		TimeZone.setDefault(CLIENT_TIME_ZONE);
	}

	@Test
	public void testTime() throws SQLException {
		final Calendar serverCalendar = Calendar.getInstance(SERVER_TIME_ZONE);
		final long currentTimeMillis = currentTimeMillis();
		serverCalendar.setTimeInMillis(currentTimeMillis);
		final long clientTimeMillis = serverCalendar.getTimeInMillis();

		try (final Connection conn = this.getConnection(SERVER_TIME_ZONE)) {
			try (final Statement stmt = conn.createStatement()) {
				final String tableName = "testTime";

				try {
					stmt.executeUpdate(format("drop table %s", tableName));
				} catch (@SuppressWarnings("unused") final SQLException ignored) {
					// ignore
				}

				stmt.executeUpdate(format("create table %s (id bigint(20) not null primary key, value timestamp not null)", tableName));

				/*
				 * Store the timestamp...
				 */
				try (final PreparedStatement pstmt = conn.prepareStatement(format("insert into %s (id, value) values (?, ?)", tableName))) {
					pstmt.setLong(1, clientTimeMillis);
					pstmt.setTimestamp(2, new Timestamp(clientTimeMillis));
					pstmt.executeUpdate();
				}

				/*
				 * ... and read its time fraction.
				 */
				try (final ResultSet rset = stmt.executeQuery(format("select value from %s", tableName))) {
					assertTrue(rset.next());
					final Time time = rset.getTime(1);
					assertNotNull(time);
					assertFalse(rset.next());

					final long serverTimeMillis = time.getTime();
					assertTrue(serverTimeMillis < MILLIS_IN_DAY);

					final String serverTimeFormatted = formatTime(serverTimeMillis);
					final String clientTimeFormatted = new SimpleDateFormat("HH:mm:ss").format(new Date(clientTimeMillis));
					final String serverTimeMessage = format("MySQL Server time in 24h format (expected to be %s) converted to the JVM's time zone (%s): %s",
							SERVER_TIME_ZONE.getID(),
							CLIENT_TIME_ZONE.getID(),
							serverTimeFormatted);
					System.out.println(serverTimeMessage);
					final String clientTimeMessage = format("JVM's time in 24h format (in %s time zone): %s",
							CLIENT_TIME_ZONE.getID(),
							clientTimeFormatted);
					System.out.println(clientTimeMessage);

					final long differenceMillis = abs(clientTimeMillis % MILLIS_IN_DAY - serverTimeMillis);
					final long normalizedDifferenceMillis = differenceMillis > MILLIS_IN_DAY / 2
							? MILLIS_IN_DAY - differenceMillis
							: differenceMillis;
					assertTrue(format("%d millisecond(s) difference (%s) between %d (expected) and %d (actual)\n%s\n%s",
							differenceMillis,
							formatTime(differenceMillis),
							clientTimeMillis % MILLIS_IN_DAY,
							serverTimeMillis,
							serverTimeMessage,
							clientTimeMessage),
							normalizedDifferenceMillis < MAXIMUM_ACCEPTABLE_DIFFERENCE);
				}

				stmt.executeUpdate(format("drop table %s", tableName));
			}
		}
	}

	/**
	 * <p>Tests the {@code current_time()} function and compares the result
	 * it returns with {@link System#currentTimeMillis()}.</p>
	 */
	@Test
	public void testCurrentTime() throws SQLException {
		try (final Connection conn = this.getConnection(SERVER_TIME_ZONE)) {
			try (final Statement stmt = conn.createStatement()) {
				try (final ResultSet rset = stmt.executeQuery("select current_time()")) {
					assertTrue(rset.next());
					final Time time = rset.getTime(1);
					assertNotNull(time);
					assertFalse(rset.next());

					/*
					 * We assume the clocks of the client and the server are synchronised.
					 */
					final long clientTimeMillis = currentTimeMillis();
					final long serverTimeMillis = time.getTime();
					assertTrue(serverTimeMillis < MILLIS_IN_DAY);

					final String serverTimeFormatted = formatTime(serverTimeMillis);
					final String clientTimeFormatted = new SimpleDateFormat("HH:mm:ss").format(new Date(clientTimeMillis));
					final String serverTimeMessage = format("MySQL Server time in 24h format (expected to be %s) converted to the JVM's time zone (%s): %s",
							SERVER_TIME_ZONE.getID(),
							CLIENT_TIME_ZONE.getID(),
							serverTimeFormatted);
					System.out.println(serverTimeMessage);
					final String clientTimeMessage = format("JVM's time in 24h format (in %s time zone): %s",
							CLIENT_TIME_ZONE.getID(),
							clientTimeFormatted);
					System.out.println(clientTimeMessage);

					final long differenceMillis = abs(clientTimeMillis % MILLIS_IN_DAY - serverTimeMillis);
					final long normalizedDifferenceMillis = differenceMillis > MILLIS_IN_DAY / 2
							? MILLIS_IN_DAY - differenceMillis
							: differenceMillis;
					assertTrue(format("%d millisecond(s) difference (%s) between %d (expected) and %d (actual)\n%s\n%s",
							differenceMillis,
							formatTime(differenceMillis),
							clientTimeMillis % MILLIS_IN_DAY,
							serverTimeMillis,
							serverTimeMessage,
							clientTimeMessage),
							normalizedDifferenceMillis < MAXIMUM_ACCEPTABLE_DIFFERENCE);
				}
			}
		}
	}

	private static String formatTime(final long timeMillis) {
		final long timeSecondsTotal = (timeMillis > 0L ? timeMillis : MILLIS_IN_DAY + timeMillis ) / 1000L;
		final long timeSeconds = timeSecondsTotal % 60L;
		final long timeMinutesTotal = timeSecondsTotal / 60L;
		final long timeMinutes = timeMinutesTotal % 60L;
		final long timeHours = timeMinutesTotal / 60L;
		return format("%02d:%02d:%02d",
				timeHours,
				timeMinutes,
				timeSeconds);
	}

	/**
	 * @param serverTimezone the assumed server time zone.
	 */
	private Connection getConnection(final TimeZone serverTimezone) throws SQLException {
		final Properties connectionInfo = new Properties();
		connectionInfo.setProperty("user", MYSQL_USER);
		connectionInfo.setProperty("password", MYSQL_PASSWORD);
		connectionInfo.setProperty("useSSL", "false");
		connectionInfo.setProperty("serverTimezone", serverTimezone.getID());

		connectionInfo.setProperty("useTimezone", String.valueOf(this.useTimezone));
		connectionInfo.setProperty("useLegacyDatetimeCode", String.valueOf(this.useLegacyDatetimeCode));
		connectionInfo.setProperty("noTimezoneConversionForDateType", String.valueOf(this.noTimezoneConversionForDateType));
		connectionInfo.setProperty("noTimezoneConversionForTimeType", String.valueOf(this.noTimezoneConversionForTimeType));

		return DriverManager.getConnection(format("jdbc:mysql://%s:%d/%s",
				"localhost",
				3306,
				MYSQL_DATABASE),
				connectionInfo);
	}
}