
package org.mbari.vdm.expd;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

import org.mbari.sql.ConnectionFactory;
import org.mbari.sql.DBException;
import org.mbari.vdm.CsvHelper;
import org.mbari.vdm.knowledgebase.SearchableTreePanel;

import ucar.nc2.NetcdfFile;
import ucar.unidata.geoloc.LatLonRect;

public class DbQueryer {

    private static final long epochSecondsStart = 876758726;
    private static final String baseMapFileName = "hrbath.nc";

    public static final int getRecordCount(String db, String tableName, LatLonRect range) {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = ConnectionFactory.getConnection(db);
        } catch (DBException dbe) {
            dbe.printStackTrace();
            System.exit(1);
        }
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            /**
             * The query, with an epochseconds delimeter String query = "SELECT
             * COUNT (Latitude) FROM " + tableName + " WHERE EpochSecs > " +
             * epochSecondsStart + " AND Latitude < " +
             * range.getUpperLeftPoint().getLatitude() + " AND Latitude > " +
             * range.getLowerRightPoint().getLatitude() + " AND Longitude > " +
             * range.getUpperLeftPoint().getLongitude() + " AND Longitude < " +
             * range.getLowerRightPoint().getLongitude();
             */
            String query = "SELECT COUNT (Latitude) FROM " + tableName + " WHERE Latitude < "
                + range.getUpperLeftPoint().getLatitude() + " AND Latitude > "
                + range.getLowerRightPoint().getLatitude() + " AND Longitude > "
                + range.getUpperLeftPoint().getLongitude() + " AND Longitude < "
                + range.getLowerRightPoint().getLongitude();
            System.out.println("querying db with: \n" + query);
            rs = stmt.executeQuery(query);
            rs.next();
            int retVal = rs.getInt(1);
            stmt.close();
            rs.close();
            conn.close();
            return retVal;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            System.out.println("dying...");
            System.exit(1);
        }
        // unreachable...
        return 0;
    }

    public static final ExpdLine[] queryNavTable(String tableName, LatLonRect range) {
        Connection conn = null;
        Statement stmt = null;
        final String queryColumns = "Latitude, Longitude, Depth, EpochSecs";
        final int columnCount = 4;
        int rowCounter = 0;
        int numRecords = getRecordCount("expd", tableName, range);
        System.out.println("collecting " + numRecords + " records");
        ExpdLine[] lines = new ExpdLine[numRecords];
        try {
            conn = ConnectionFactory.getConnection("expd");
        } catch (DBException dbe) {
            dbe.printStackTrace();
            System.exit(1);
        }
        long epochSecondsCounter = epochSecondsStart;

        ResultSet rs = null;
        while (rowCounter < numRecords) {
            System.out.println("rowcounter = " + rowCounter);
            try {
                stmt = conn.createStatement();
                /**
                 * The query, with an epochseconds delimeter String query =
                 * "SELECT " + queryColumns + " FROM " + tableName + " WHERE
                 * EpochSecs > " + epochSecondsCounter + " AND Latitude < " +
                 * range.getUpperLeftPoint().getLatitude() + " AND Latitude > " +
                 * range.getLowerRightPoint().getLatitude() + " AND Longitude > " +
                 * range.getUpperLeftPoint().getLongitude() + " AND Longitude < " +
                 * range.getLowerRightPoint().getLongitude();
                 */
                String query = "SELECT " + queryColumns + " FROM " + tableName
                    + " WHERE Latitude < "
                    + range.getUpperLeftPoint().getLatitude() + " AND Latitude > "
                    + range.getLowerRightPoint().getLatitude() + " AND Longitude > "
                    + range.getUpperLeftPoint().getLongitude() + " AND Longitude < "
                    + range.getLowerRightPoint().getLongitude();
                long queryStartTime = System.currentTimeMillis();
                rs = stmt.executeQuery(query);
                System.out.println("Time to execute query: "
                    + ((System.currentTimeMillis() - queryStartTime) / 1000) + " seconds");

            } catch (SQLException sqle) {
                sqle.printStackTrace();
                System.out.println("SQL Exception occured.\nPress any key to continue.\n");
                try {
                    System.in.read();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                continue;
            }
            try {
                while (rs.next()) {
                    double lat = rs.getDouble(1);
                    double lon = rs.getDouble(2);
                    float depth = rs.getFloat(3);
                    long epochs = rs.getLong(4);
                    // lat and lon will be truncated to 5 decimal digits,
                    // where .00001 ~ 1 meter. This is to make the sorting work
                    // better
                    lines[rowCounter++] = new ExpdLine(lat, lon, depth, epochs);
                    epochSecondsCounter = Long.parseLong(rs.getString(columnCount));
                }
                System.out.println("last value returned: " + lines[rowCounter - 1]);
            } catch (SQLException sqle) {
                sqle.printStackTrace();
                System.exit(0);
            }
        }
        try {
            stmt.close();
            rs.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lines;
    }

    @SuppressWarnings("unchecked")
    public static final void sortQueryResults(BufferedReader unsorted, BufferedWriter sorted)
        throws IOException {
        ExpdLine[] expdLines = (ExpdLine[]) CsvHelper.readLines(unsorted, "ExpdLine");
        System.out.println("sorting data");
        Arrays.sort(expdLines, ExpdLine.getComparator());
        CsvHelper.writeLines(sorted, expdLines);
    }

    public static final void doSort() {
        BufferedWriter writer = null;
        BufferedReader reader = null;
        try {
            writer = new BufferedWriter(new FileWriter(new File(SearchableTreePanel.class
                .getResource("/expdData").getFile(), "tiburonCleanExpdData_sorted.csv")));
            reader = new BufferedReader(new FileReader(new File(SearchableTreePanel.class
                .getResource("/expdData/tiburonExpdData.csv").getFile())));
        } catch (IOException ioe) {
            ioe.printStackTrace();
            System.exit(0);
        }
        try {
            sortQueryResults(reader, writer);
        } catch (IOException ioe) {
            ioe.printStackTrace();
            System.exit(0);
        }
    }

    @SuppressWarnings("unchecked")
    public static final void doAll(String tableName) throws IOException {
        long startTime = System.currentTimeMillis();
        System.out.println("starting expd query for " + tableName);
        LatLonRect range = DbQueryer.getBaseMapRect();
        System.out.println("querying db...");
        ExpdLine[] lines = queryNavTable(tableName, range);
        System.out.println("Time to query db: " + ((System.currentTimeMillis() - startTime) / 1000)
            + " secs");
        System.out.println("sorting...");
        Arrays.sort(lines, ExpdLine.getComparator());
        // System.out.println("filtering");

        // lines = filterRecords(lines, range);
        BufferedWriter writer = null;
        try {
            System.out.println("Writing to file...");
            long writeStartTime = System.currentTimeMillis();
            writer = new BufferedWriter(new FileWriter(new File(SearchableTreePanel.class
                .getResource("/expdData").getFile(), tableName + "_filtered.csv")));
            CsvHelper.writeLines(writer, lines);
            lines = null;
            writer.close();
            System.out.println("Time to write to file: "
                + ((System.currentTimeMillis() - writeStartTime) / 1000) + " seconds");
        } catch (IOException ioe) {
            ioe.printStackTrace();
            System.exit(0);
        }
        System.out.println("Total time: " + ((System.currentTimeMillis() - startTime) / 1000)
            + " seconds");
    }

    private static LatLonRect getBaseMapRect() throws IOException {
        File newNcFile = new File(SearchableTreePanel.class.getResource("/grids").getFile(),
            baseMapFileName);
        NetcdfFile baseNc = NetcdfFile.open(newNcFile.getAbsolutePath());
        return NetCdfCreator.getNcBaseMapBounds();
    }

    public static void main(String[] args) {
        String[] tables = {"VentanaCleanNavData", "TiburonCleanNavData"};
        // String[] tables = {"VentanaCleanNavData"};
        // String[] tables = {"TiburonCleanNavData"};
        for (int i = 0; i < tables.length; i++) {
            try {
                DbQueryer.doAll(tables[i]);
            } catch (IOException e) {
                e.printStackTrace();
            }
            System.gc();
        }
    }
}
