/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Random;
import java.util.concurrent.ThreadLocalRandom;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.testng.Assert;
import static org.testng.Assert.*;
import org.testng.Reporter;
import org.testng.annotations.AfterClass;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.Test;

/**
 *
 * @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
 */
public class TestDriver {

    private static Connection conn;
    private static ArrayList<RandomMapunit> testMapunits = new ArrayList<>();
    private static int MAX_RANDOM_MAPUNITS = 5;
    private static double MAX_AREA_DELTA = 0.1;

    public TestDriver() throws ClassNotFoundException, SQLException {
//1395701
    }

    // TODO add test methods here.
    // The methods must be annotated with annotation @Test. For example:
    //
    // @Test
    // public void hello() {}
    @Test
    public void testResultSetIteration() {
        String query = "SELECT top 100 legend.areasymbol, mapunit.musym, mapunit.mukey, mapunit.muname, muacres, brockdepmin FROM mapunit "
                + " INNER JOIN legend ON mapunit.lkey=legend.lkey  "
                + " INNER JOIN muaggatt ON mapunit.mukey=muaggatt.mukey  WHERE "
                + " legend.areatypename like 'Non-MLRA%' ORDER BY NEWID();";

        try {
            ResultSet results;
            Statement statement = conn.createStatement();
            results = statement.executeQuery(query);
            int count = 0;

            while (results.next()) {
                String areasymbol = results.getString("areasymbol");
                String musym = results.getString("musym");
                String mukey = results.getString("mukey");
                String muname = results.getString("muname");
                double muacres = results.getDouble("muacres");
                double brockdepmin = results.getDouble("brockdepmin");
                count++;
            }
            Assert.assertTrue(count == 100);
        } catch (SQLException ex) {
            Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex);
            Assert.assertTrue(false);
        }
    }

    @Test
    public void testBadColumnName() {
        String query = "SELECT top 1 legend.areasymbol, mapunit.musym, mapunit.mukey, mapunit.muname, muacres, brockdepmin FROM mapunit "
                + "INNER JOIN legend ON mapunit.lkey=legend.lkey  "
                + "INNER JOIN muaggatt ON mapunit.mukey=muaggatt.mukey  WHERE "
                + " legend.areatypename like 'Non-MLRA%' ORDER BY mapunit.mukey;";

        try {
            ResultSet results;
            Statement statement = conn.createStatement();
            results = statement.executeQuery(query);
            int count = 0;

            while (results.next()) {
                String areasymbol = results.getString("areasymbol");
                String musym = results.getString("musym");
                String mukey = results.getString("mukey");
                String muname = results.getString("muname");
                double muacres = results.getDouble("muacres");
                double brockdepmin = results.getDouble("brockdepmin");
                double area = results.getDouble("area");
                count++;
            }
            Assert.assertFalse(count > 0);  //We should have recived an SQLException.
        } catch (SQLException ex) {
            Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, ex.getMessage());
            Assert.assertTrue(true);
        }
    }

    @Test
    public void testComplexIntersectionQuery() {
        String bufferWKT;

        for (RandomMapunit tMapunit : testMapunits) {
            bufferWKT = tMapunit.getBufferWKT();

            String query = "SELECT areasymbol, musym, mukey, muname, muacres, brockdepmin, "
                    + " geography::STGeomFromText(intersectPoly.STAsText(), 4326).MakeValid().STArea() / 4046.86 AS area   "
                    + " FROM  ("
                    + "        SELECT m.areasymbol, m.musym, m.mukey, mapunit.muacres, mapunit.muname, muaggatt.brockdepmin,  "
                    + "               m.mupolygongeo.STIntersection(geometry::STGeomFromText ('" + bufferWKT + "', 4326)).MakeValid() as intersectPoly  "
                    + "         FROM dbo.mupolygon as m  WITH (index(SI_mupolygon_24876))  "
                    + "           INNER JOIN dbo.mapunit ON m.mukey=dbo.mapunit.mukey  "
                    + "           INNER JOIN dbo.legend ON mapunit.lkey=dbo.legend.lkey  "
                    + "           INNER JOIN dbo.muaggatt ON m.mukey=dbo.muaggatt.mukey  "
                    + "         WHERE m.mupolygongeo.STIntersects(geometry::STGeomFromText ('" + bufferWKT + "', 4326)) = 1  "
                    + "              AND m.mupolygongeo.STIsValid()=1 "
                    + "              AND (geometry:: STGeomFromText ('" + bufferWKT + "', 4326).STIsValid())=1  "
                    + "              AND legend.areatypename like 'Non-MLRA%' ) as a ORDER BY mukey;";

            try {
                ResultSet results;
                Statement statement = conn.createStatement();
                results = statement.executeQuery(query);
                double area = 0.0;

                while (results.next()) {
                    String areasymbol = results.getString("areasymbol");
                    String musym = results.getString("musym");
                    String mukey = results.getString("mukey");
                    String muname = results.getString("muname");
                    double muacres = results.getDouble("muacres");
                    double brockdepmin = results.getDouble("brockdepmin");
                    area += results.getDouble("area");
                }
                
                Assert.assertEquals(area, tMapunit.getArea(), MAX_AREA_DELTA);
            } catch (SQLException ex) {
                Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex);
                Assert.assertTrue(false);
            }
        }
    }

    @BeforeClass
    public static void setUpClass() throws Exception {
        Class.forName("org.csu.csip.sdm.SDMDriver");
        Reporter.log("Driver was loaded for: org.csu.csip.sdm.SDMDrive");
        conn = DriverManager.getConnection("jdbc:sdm:rest://SDMDataAccess.sc.egov.usda.gov/Tabular/post.rest");
        Reporter.log("Connection to remote SDM REST Service was successful");

        //fill randomMapunits
        String query = "SELECT mupolygon.mupolygonkey, mupolygon.mukey, legend.areasymbol, mupolygon.mupolygongeo.STCentroid().STAsText() as centerPoint "
                + "FROM mupolygon, legend, mapunit "
                + "  WHERE mupolygon.mupolygonkey in  "
                + "  ("
                + "	SELECT TOP " + MAX_RANDOM_MAPUNITS + " mupolygonkey "
                + "       FROM mupolygon, legend, mapunit "
                + "       WHERE legend.lkey=mapunit.lkey AND mapunit.mukey=mupolygon.mukey AND legend.areatypename like 'Non-MLRA%' "
                + "       ORDER BY newid() "
                + "  ) "
                + "  AND legend.lkey=mapunit.lkey AND mapunit.mukey=mupolygon.mukey; ";

        try {
            ResultSet results;
            Statement statement = conn.createStatement();
            results = statement.executeQuery(query);

            while (results.next()) {
                RandomMapunit tMapunit = new RandomMapunit(results.getString("mupolygonkey"), results.getString("mukey"), results.getString("areasymbol"), results.getString("centerPoint"));
                testMapunits.add(tMapunit);
            }
        } catch (SQLException ex) {
            Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex);
            Assert.assertTrue(false);
        }

        for (RandomMapunit tMapunit : testMapunits) {
            tMapunit.getBuffers(conn);
        }
    }

    @AfterClass
    public static void tearDownClass() throws Exception {
    }

    @BeforeMethod
    public void setUpMethod() throws Exception {
    }

    @AfterMethod
    public void tearDownMethod() throws Exception {
    }

    private static class RandomMapunit {

        private String mupolygonKey;
        private String mukey;
        private String areasymbol;
        private String centroidWKT;
        private String bufferWKT;
        private double area;
        private double bufferSize;

        public RandomMapunit(String mupolygonKey, String mukey, String areasymbol, String centroidWKT) {
            this.mupolygonKey = mupolygonKey;
            this.mukey = mukey;
            this.areasymbol = areasymbol;
            this.centroidWKT = centroidWKT;
            bufferSize = ThreadLocalRandom.current().nextDouble(50, 100);
        }

        public void getBuffers(Connection conn) {
            String query = "SELECT geography::STGeomFromText('" + centroidWKT + "', 4326).STBuffer(" + bufferSize + ").STAsText() as bufferWKT, "
                    + " geography::STGeomFromText('" + centroidWKT + "', 4326).STBuffer(" + bufferSize + ").STArea() / 4046.86 as area "
                    + "FROM mupolygon"
                    + "  WHERE mupolygonkey=" + mupolygonKey + "; ";

            try {
                ResultSet results;
                Statement statement = conn.createStatement();
                results = statement.executeQuery(query);

                if (results.next()) {
                    bufferWKT = results.getString("bufferWKT");
                    area = results.getDouble("area");
                }
            } catch (SQLException ex) {
                Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex);
                Assert.assertTrue(false);
            }
        }

        public String getBufferWKT() {
            return bufferWKT;
        }

        public double getArea() {
            return area;
        }
    }

}