/*
 * 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.Queue;
import java.util.Random;
import java.util.concurrent.ConcurrentLinkedQueue;
import java.util.concurrent.ThreadLocalRandom;
import java.util.logging.Handler;
import java.util.logging.Level;
import java.util.logging.LogManager;
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 Queue<RandomMapunit> testMapunits = new ConcurrentLinkedQueue<>();

    /**
     * Number of random mapunits to test intersections for.
     */
    private static final int MAX_RANDOM_MAPUNITS = 10;

    /**
     * Acre measurement delta for comparisons in intersection tests.
     */
    private static final double MAX_AREA_DELTA = 0.01;

    public TestDriver() throws ClassNotFoundException, SQLException {
        Logger log = LogManager.getLogManager().getLogger("");
        for (Handler h : log.getHandlers()) {
            h.setLevel(Level.WARNING);
        }
    }

    @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); //We should have received 100 random mapunits, and been able to read their data.
        } catch (SQLException ex) {
            Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex);
            Assert.assertTrue(false, "No results were found for this test, which should have generated a random list of mapunit keys.  Something may be wrong with the ResultSet implementation of the SDMDriver.");
        }
    }

    @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"); //This one should cause an exception if the ResultSet object is working correctly.
                count++;
            }
            Assert.assertFalse(count > 0, "This SQL statement should have thrown an SQL Exception, but did not.");  //We should have recived an SQLException.
        } catch (SQLException ex) {
            Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, ex.getMessage());
            Assert.assertTrue(true);
        }
    }

    @Test(threadPoolSize = 10, invocationCount = MAX_RANDOM_MAPUNITS, timeOut = 30000)
    public void testComplexIntersectionQuery() throws InterruptedException {
        String bufferWKT;

        RandomMapunit tMapunit = testMapunits.poll();
        long sleepTime = ThreadLocalRandom.current().nextInt(10, 2000);
        //System.out.println(Thread.currentThread().getId() + ":  Sleepting for " + sleepTime + " milliseconds.");
        //System.out.flush();
        Thread.sleep(sleepTime);
        if (null != tMapunit) {

            bufferWKT = tMapunit.getBufferWKT();
            System.out.println(Thread.currentThread().getId() + ":  Runing intersection for mupolygonkey: " + tMapunit.mupolygonKey);
            System.out.flush();
            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;
                long timeMills = System.currentTimeMillis();
                Statement statement = conn.createStatement();
                results = statement.executeQuery(query);
                double area = 0.0;
                System.out.println(Thread.currentThread().getId() + ":  Query took " + ((System.currentTimeMillis()) - timeMills) / 1000.0 + " seconds.");
                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, "Intersection query based on random data from the remote server resulted in area calculation that did not match expected outcomes.");
            } catch (SQLException ex) {
                Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex);
                Assert.assertTrue(false);
            }
        } else {
            System.out.println("Queue is empty.  Finished.");
        }
    }

    @BeforeClass
    public static void setUpClass() throws Exception {
        System.out.println("Loading the SDMDriver, and testing a connection.");
        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 {
            System.out.println("Building a list of " + MAX_RANDOM_MAPUNITS + " random mapunits to test with.");
            ResultSet results;
            Statement statement = conn.createStatement();
            results = statement.executeQuery(query);

            while (results.next()) {
                System.out.println("Got mupolygonkey: " + results.getString("mupolygonkey"));
                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, "Could not generate a random set of mapunit buffers for use in upcoming tests: " + ex.getMessage());
        }

        System.out.println("Creating randomly sized buffer polygons from each mapunit centroid.");
        for (RandomMapunit tMapunit : testMapunits) {
            tMapunit.getBuffers(conn);
        }
    }

    @AfterClass
    public static void tearDownClass() throws Exception {
        System.out.println("Closing conneciton.");
        conn.close();
    }

    @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, "Could not generate a randomly sized buffer within this mapunit: " + ex.getMessage());
            }
        }

        public String getBufferWKT() {
            return bufferWKT;
        }

        public double getArea() {
            return area;
        }
    }

}