/*
 * Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.DaylightSavingTest
 *
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
 * either express or implied. See the License for the specific
 * language governing permissions and limitations under the License.
 */

package org.apache.derbyTesting.functionTests.tests.jdbcapi;

import java.sql.Date;
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.util.Calendar;
import java.util.TimeZone;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;
import org.apache.derbyTesting.junit.TimeZoneTestSetup;

/**
 * This class contains tests that verify the correct handling of
 * {@code java.sql.Date}, {@code java.sql.Time} and {@code java.sql.Timestamp}
 * across DST changes.
 */
public class DaylightSavingTest extends BaseJDBCTestCase {
    public DaylightSavingTest(String name) {
        super(name);
    }

    public static Test suite() {
        // Run the test in a fixed timezone so that we know exactly what time
        // DST is observed.
        return new TimeZoneTestSetup(
                TestConfiguration.defaultSuite(DaylightSavingTest.class),
                "America/Chicago");
    }

    /**
     * Regression test case for DERBY-4582. Timestamps that were converted
     * to GMT before they were stored in the database used to come out wrong
     * on the network client if the timestamp represented a time near the
     * switch to DST in the local timezone.
     */
    public void testConversionToGMTAroundDSTChange() throws SQLException {
        Statement s = createStatement();
        s.execute("CREATE TABLE DERBY4582(" +
                "ID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, " +
                "TS TIMESTAMP, T TIME, D DATE, T2 TIME, D2 DATE, " +
                "TS_STR VARCHAR(100), T_STR VARCHAR(100), D_STR VARCHAR(100))");

        Calendar localCal = Calendar.getInstance();

        // Switch from CST to CDT in 2010 happened at 2010-03-14 02:00:00 CST,
        // or 2010-03-14 08:00:00 GMT, so create some times/dates around that
        // time.
        Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
        cal.set(Calendar.YEAR, 2010);
        cal.set(Calendar.MONTH, Calendar.MARCH);
        cal.set(Calendar.DAY_OF_MONTH, 12);
        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.set(Calendar.MINUTE, 1);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MILLISECOND, 0);

        // Create times for each hour in 2010-03-12 -- 2010-03-15 (GMT).
        Timestamp[] timestamps = new Timestamp[24 * 4];
        Time[] times = new Time[timestamps.length];
        Date[] dates = new Date[timestamps.length];
        for (int i = 0; i < timestamps.length; i++) {
            long time = cal.getTimeInMillis();
            timestamps[i] = new Timestamp(time);
            times[i] = new Time(time);
            dates[i] = new Date(time);
            cal.setTimeInMillis(time + 3600000); // move one hour forward
        }

        // Store the GMT representations of the times.
        PreparedStatement insert = prepareStatement(
                "INSERT INTO DERBY4582 " +
                "(TS, T, D, T2, D2, TS_STR, T_STR, D_STR) " +
                "VALUES (?,?,?,?,?,?,?,?)");
        for (int i = 0; i < timestamps.length; i++) {
            Timestamp ts = timestamps[i];
            Time t = times[i];
            Date d = dates[i];

            // Set the TIMESTAMP/TIME/DATE values TS/T/D with their respective
            // setter methods.
            insert.setTimestamp(1, ts, cal);
            insert.setTime(2, t, cal);
            insert.setDate(3, d, cal);

            // Set the TIME/DATE values T2/D2 with setTimestamp() to verify
            // that this alternative code path also works.
            insert.setTimestamp(4, ts, cal);
            insert.setTimestamp(5, ts, cal);

            // Also insert the values into VARCHAR columns so that we can
            // check that they are converted correctly.
            insert.setTimestamp(6, ts, cal);
            insert.setTime(7, t, cal);
            insert.setDate(8, d, cal);

            insert.execute();
        }

        // Now see that we get the same values back.
        ResultSet rs = s.executeQuery("SELECT * FROM DERBY4582 ORDER BY ID");
        for (int i = 0; i < timestamps.length; i++) {
            assertTrue("found only " + i + " rows", rs.next());
            assertEquals("ID", i + 1, rs.getInt(1));
            assertEquals("TS", timestamps[i], rs.getTimestamp(2, cal));
            assertEquals("T", stripDate(times[i], cal), rs.getTime(3, cal));
            assertEquals("D", stripTime(dates[i], cal), rs.getDate(4, cal));
            // T2 and D2 should have the same values as T and D.
            assertEquals("T2", stripDate(times[i], cal), rs.getTime(5, cal));
            assertEquals("D2", stripTime(dates[i], cal), rs.getDate(6, cal));
            // The VARCHAR columns should have the same values as TS, T and D.
            assertEquals("TS_STR", timestamps[i], rs.getTimestamp(7, cal));
            assertEquals("T_STR", stripDate(times[i], cal), rs.getTime(8, cal));
            assertEquals("D_STR", stripTime(dates[i], cal), rs.getDate(9, cal));
        }
        JDBC.assertEmpty(rs);

        // Also check that we get the expected values when we get TIME or DATE
        // with getTimestamp(), or TIMESTAMP with getTime() or getDate()
        rs = s.executeQuery("SELECT ID,T,D,TS,TS FROM DERBY4582 ORDER BY ID");
        for (int i = 0; i < timestamps.length; i++) {
            assertTrue("found only " + i + " rows", rs.next());
            assertEquals("ID", i + 1, rs.getInt(1));
            assertEquals("TIME AS TIMESTAMP",
                    timeToTimestamp(stripDate(times[i], cal), cal),
                    rs.getTimestamp(2, cal));
            assertEquals("DATE AS TIMESTAMP",
                    dateToTimestamp(stripTime(dates[i], cal), cal),
                    rs.getTimestamp(3, cal));
            assertEquals("TIMESTAMP AS TIME",
                    stripDate(timestamps[i], cal),
                    rs.getTime(4, cal));
            assertEquals("TIMESTAMP AS DATE",
                    stripTime(timestamps[i], cal),
                    rs.getDate(5, cal));
        }
        JDBC.assertEmpty(rs);

        // Now verify that we can successfully get values set in with an
        // updatable result set. Note that updateTimestamp(), updateTime() and
        // updateDate() don't take a Calendar argument, so the updated values
        // will be stored in the local timezone. What we test here, is that
        // updateX(col, val) followed by getX(col, val, cal) performs the
        // correct translation from local calendar to GMT calendar.
        Statement updStmt = createStatement(
                ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        rs = updStmt.executeQuery("SELECT TS, T, D FROM DERBY4582");
        for (int i = 0; i < timestamps.length; i++) {
            assertTrue("found only " + i + " rows", rs.next());
            Timestamp ts1 = timestamps[i];
            rs.updateTimestamp(1, ts1);
            assertEquals("TS (default calendar)", ts1, rs.getTimestamp(1));
            Timestamp ts2 = rs.getTimestamp(1, cal);
            cal.clear();
            cal.setTime(ts2);
            localCal.clear();
            localCal.setTime(ts1);
            assertEquals("TS.YEAR",
                    localCal.get(Calendar.YEAR), cal.get(Calendar.YEAR));
            assertEquals("TS.MONTH",
                    localCal.get(Calendar.MONTH), cal.get(Calendar.MONTH));
            assertEquals("TS.DATE",
                    localCal.get(Calendar.DAY_OF_MONTH),
                    cal.get(Calendar.DAY_OF_MONTH));
            assertEquals("TS.HOURS",
                    localCal.get(Calendar.HOUR_OF_DAY),
                    cal.get(Calendar.HOUR_OF_DAY));
            assertEquals("TS.MINUTES",
                    localCal.get(Calendar.MINUTE), cal.get(Calendar.MINUTE));
            assertEquals("TS.SECONDS",
                    localCal.get(Calendar.SECOND), cal.get(Calendar.SECOND));
            assertEquals("TS.NANOS",
                    ts1.getNanos(), ts2.getNanos());

            Time t1 = times[i];
            rs.updateTime(2, t1);
            assertEquals("T (default calendar)",
                    stripDate(t1, localCal), rs.getTime(2));
            Time t2 = rs.getTime(2, cal);
            cal.clear();
            cal.setTime(t2);
            localCal.clear();
            localCal.setTime(t1);
            assertEquals("T.HOURS",
                    localCal.get(Calendar.HOUR_OF_DAY),
                    cal.get(Calendar.HOUR_OF_DAY));
            assertEquals("T.MINUTES",
                    localCal.get(Calendar.MINUTE), cal.get(Calendar.MINUTE));
            assertEquals("T.SECONDS",
                    localCal.get(Calendar.SECOND), cal.get(Calendar.SECOND));

            Date d1 = dates[i];
            rs.updateDate(3, d1);
            assertEquals("D (default calendar)",
                    stripTime(d1, localCal), rs.getDate(3));
            Date d2 = rs.getDate(3, cal);
            cal.clear();
            cal.setTime(d2);
            localCal.clear();
            localCal.setTime(d1);
            assertEquals("D.YEAR",
                    localCal.get(Calendar.YEAR), cal.get(Calendar.YEAR));
            assertEquals("D.MONTH",
                    localCal.get(Calendar.MONTH), cal.get(Calendar.MONTH));
            assertEquals("D.DATE",
                    localCal.get(Calendar.DAY_OF_MONTH),
                    cal.get(Calendar.DAY_OF_MONTH));

            rs.updateRow();
        }
        JDBC.assertEmpty(rs);

        // Verify that the values touched by the updatable result set made it
        // into the database.
        rs = s.executeQuery("SELECT TS, T, D FROM DERBY4582 ORDER BY TS");
        for (int i = 0; i < timestamps.length; i++) {
            assertTrue("found only " + i + " rows", rs.next());
            assertEquals("TS", timestamps[i], rs.getTimestamp(1));
            assertEquals("T", stripDate(times[i], localCal), rs.getTime(2));
            assertEquals("D", stripTime(dates[i], localCal), rs.getDate(3));
        }
        JDBC.assertEmpty(rs);

    }

    /**
     * Strip away the date component from a {@code java.util.Date} and return
     * it as a {@code java.sql.Time}, so that it can be compared with a time
     * value returned by Derby. Derby will set the date component of the time
     * value to 1970-01-01, so let's do the same here.
     *
     * @param time the time value whose date component to strip away
     * @param cal the calendar used to store the time in the database originally
     * @return a time value that represents the same time of the day as
     * {@code time} in the calendar {@code cal}, but with the date component
     * normalized to 1970-01-01
     */
    private static Time stripDate(java.util.Date time, Calendar cal) {
        cal.clear();
        cal.setTime(time);
        cal.set(1970, Calendar.JANUARY, 1);
        return new Time(cal.getTimeInMillis());
    }

    /**
     * Strip away the time component from a {@code java.util.Date} and return
     * it as a {@code java.sql.Date}, so that it can be compared with a date
     * value returned by Derby. Derby will set the time component of the date
     * value to 00:00:00.0, so let's do the same here.
     *
     * @param date the date whose time component to strip away
     * @param cal the calendar used to store the date in the database originally
     * @return a date value that represents the same day as {@code date} in the
     * calendar {@code cal}, but with the time component normalized to
     * 00:00:00.0
     */
    private static Date stripTime(java.util.Date date, Calendar cal) {
        cal.clear();
        cal.setTime(date);
        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MILLISECOND, 0);
        return new Date(cal.getTimeInMillis());
    }

    /**
     * Convert a time value to a timestamp. The date component of the timestamp
     * should be set to the current date in the specified calendar, see
     * DERBY-889 and DERBY-1811.
     *
     * @param time the time value to convert
     * @param cal the calendar in which the conversion should be performed
     * @return a timestamp
     */
    private static Timestamp timeToTimestamp(Time time, Calendar cal) {
        // Get the current date in the specified calendar.
        cal.clear();
        cal.setTimeInMillis(System.currentTimeMillis());
        int year = cal.get(Calendar.YEAR);
        int month = cal.get(Calendar.MONTH);
        int day = cal.get(Calendar.DAY_OF_MONTH);

        // Construct a timestamp based on the current date and the specified
        // time value.
        cal.clear();
        cal.setTime(time);
        cal.set(year, month, day);

        return new Timestamp(cal.getTimeInMillis());
    }

    /**
     * Convert a date value to a timestamp. The time component of the timestamp
     * will be set to 00:00:00.0.
     *
     * @param date the date value to convert
     * @param cal the calendar in which the conversion should be performed
     * @return a timestamp
     */
    private static Timestamp dateToTimestamp(Date date, Calendar cal) {
        cal.clear();
        cal.setTime(date);
        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MILLISECOND, 0);
        return new Timestamp(cal.getTimeInMillis());
    }
}