package usda.weru.weps.reports.query;

import de.schlichtherle.truezip.file.TFile;
import java.net.URL;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import usda.weru.util.Util;
import static org.junit.Assert.*;

/**
 *
 * @author Joseph Levin <joelevin@weru.ksu.edu>
 */
public class CropRotationResultSetTest {

    private static WepsConnection c_connection;

    /**
     *
     */
    public CropRotationResultSetTest() {
    }

    /**
     *
     * @throws Exception
     */
    @BeforeClass
    public static void setUpClass() throws Exception {

        URL runsUrl = CropRotationResultSetTest.class.getResource("cropdates.zip");

        TFile file = new TFile(runsUrl.toURI());

        Properties props = new Properties();
        c_connection = new WepsConnection("jdbc:weps:" + file.getAbsolutePath(), props, "name");
        c_connection.setUnits(Util.SIUnits);
    }

    /**
     *
     * @throws Exception
     */
    @AfterClass
    public static void tearDownClass() throws Exception {
        c_connection.close();
    }

    /**
     *
     */
    @Before
    public void setUp() {
    }

    /**
     *
     */
    @After
    public void tearDown() {
    }

    /**
     *
     * @throws Exception
     */
    @Test
    public void testCropRotationDates001() throws Exception {
        testCropRotationDates("001", "Rye, cereal", "0001-07-26", "0001-07-25");
    }

    /**
     *
     * @throws Exception
     */
    @Test
    public void testCropRotationDates002() throws Exception {
        testCropRotationDates("002", "Cotton, southern upland", "0001-10-02", "0001-10-01");
    }

    /**
     *
     * @throws Exception
     */
    @Test
    public void testCropRotationDates003A() throws Exception {
        testCropRotationDates("003", "Cotton, stripper", "0004-07-11", "0001-10-20");
    }

    /**
     *
     * @throws Exception
     */
    @Test
    public void testCropRotationDates003B() throws Exception {
        testCropRotationDates("003", "Corn, silage", "0001-10-21", "0002-09-06");
    }

    /**
     *
     * @throws Exception
     */
    @Test
    public void testCropRotationDates003C() throws Exception {
        testCropRotationDates("003", "Wheat, winter", "0002-09-07", "0004-07-10");
    }

    /**
     *
     * @throws Exception
     */
    @Test
    public void testCropRotationDates004A() throws Exception {
        testCropRotationDates("004", "Potato, late, harvest", "0002-10-02", "0001-07-20");
    }

    /**
     *
     * @throws Exception
     */
    @Test
    public void testCropRotationDates004B() throws Exception {
        testCropRotationDates("004", "Corn, seed", "0001-07-21", "0002-10-01");
    }

    private void testCropRotationDates(String runName, String cropName, String start, String end) throws Exception {
        System.out.println("run: " + runName + ", crop: " + cropName);
        ResultSet rs = getCropResultSetForName(runName, cropName);

        //make sure we got some data
        assertTrue("Error accesing the test runs.", rs.next());

        Date expectedStart = Date.valueOf(start);
        Date startDate = rs.getDate(CropRotationResultSet.COLUMN_STARTDATE);
        assertEquals("rotation start date incorrect", expectedStart, startDate);

        Date expectedEnd = Date.valueOf(end);
        Date endDate = rs.getDate(CropRotationResultSet.COLUMN_ENDDATE);
        assertEquals("rotation end date incorrect", expectedEnd, endDate);
    }

    private static ResultSet getCropResultSetForName(String runName, String cropName) throws Exception {
        Statement stmt = c_connection.createStatement();
        String sql = "SELECT crop.*, runs.\"name\" FROM WEPS('crop_rotation') AS crop INNER JOIN WEPS('runs') "
                + "AS runs ON crop.\"runid\"=runs.\"runid\" WHERE runs.\"name\"='" + runName
                + "' AND crop.\"crop\"='" + cropName + "'";

        return stmt.executeQuery(sql);
    }

}