package test;

import com.mysql.jdbc.Driver;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;

import static org.hamcrest.core.Is.is;
import static org.junit.Assert.*;

/**
 * &copy; 2015<br/>
 * <a href="http://www.ponton.de" target="_blank">PONTON GmbH</a>
 *
 * @author <a href="mailto:eichhorn@ponton.de">J&ouml;rg Eichhorn</a>
 * @version $Revision: $ $Date: $
 */
public class MySQLConnectorJTest {

    private static Logger log = LoggerFactory.getLogger(MySQLConnectorJTest.class);

    @Before
    public void before() throws Exception {
        DriverManager.registerDriver(new Driver());
        try (Connection con = openConnection(false)) {
            log.info("Creating test table");
            con.createStatement().executeUpdate(
                    "create table test ( " +
                            "   id int not null primary key auto_increment," +
                            "   dt1 datetime null, " +
                            "   dt2 datetime null " +
                            ")"
            );
        }
    }

    @After
    public void after() throws Exception {
        try (Connection con = openConnection(false)) {
            log.info("Dropping test table");
            con.createStatement().executeUpdate("drop table test");
        }
    }

    private Connection openConnection(boolean useCursorFetch) throws Exception {
        String url = "jdbc:mysql://localhost/test" +
                "?useCursorFetch=" + (useCursorFetch ? "true" : "false") +
                "&useLegacyDatetimeCode=false";
        return DriverManager.getConnection(url, "test", "test");
    }

    @Test
    public void testWithoutCursorFetchAndWithoutBatch() throws Exception {
        executeTest(false, false);
    }

    @Test
    public void testWithoutCursorFetchAndWithBatch() throws Exception {
        executeTest(false, true);
    }

    @Test
    public void testWithCursorFetchAndWithoutBatch() throws Exception {
        executeTest(true, false);
    }

    @Test
    public void testWithCursorFetchAndWithBatch() throws Exception {
        executeTest(true, true);
    }

    private void executeTest(boolean enableCursorFetch, boolean useBatch) throws Exception {

        // Insert data
        try (Connection con = openConnection(enableCursorFetch)) {
            log.info("Inserting test entries using {} cursor fetching and {} jdbc batch insertion",
                    enableCursorFetch ? "with" : "without",
                    useBatch ? "with" : "without");
            log.info("Using jdbc url: {}", con.getMetaData().getURL());
            PreparedStatement stmt = con.prepareStatement("insert into test (dt1, dt2) values (?, ?)");

            stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
            stmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            if (useBatch) {
                stmt.addBatch();
            } else {
                stmt.executeUpdate();
                stmt.clearParameters();
            }

            stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
            stmt.setTimestamp(2, null);
            if (useBatch) {
                stmt.addBatch();
            } else {
                stmt.executeUpdate();
                stmt.clearParameters();
            }

            stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
            stmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            if (useBatch) {
                stmt.addBatch();
                stmt.executeBatch();
            } else {
                stmt.executeUpdate();
                stmt.clearParameters();
            }

            stmt.close();
        }

        // Verify data
        log.info("Verifying data");
        try (Connection con = openConnection(enableCursorFetch)) {
            ResultSet rs = con.createStatement().executeQuery("select count(id) from test");
            rs.next();
            assertThat("Expected 3 entries", rs.getInt(1), is(3));
            log.info("Found all expected entries");
            rs.close();

            rs = con.createStatement().executeQuery("select id, dt1, dt2 from test order by id asc");
            while (rs.next()) {
                int id = rs.getInt(1);
                Timestamp dt1 = rs.getTimestamp(2);
                Timestamp dt2 = rs.getTimestamp(3);

                log.info("Found entry with id = {} and dt1 = {}, dt2 = {}", id, dt1, dt2);
                assertNotNull("Expected timestamp 1 of id " + id + " is not null", dt1);

                switch (id) {
                    case 1:
                        assertNotNull("Expected timestamp 2 of id " + id + " is not null", dt2);
                        break;
                    case 2:
                        assertNull("Expected timestamp 2 of id " + id + " is null", dt2);
                        break;
                    case 3:
                        assertNotNull("Expected timestamp 2 of id " + id + " is not null", dt2);
                        break;
                    default:
                        fail("Unexpected id " + id);
                }
            }
        }
    }
}