/*******************************************************************************
 * Debrief - the Open Source Maritime Analysis Application
 * http://debrief.info
 *
 * (C) 2000-2020, Deep Blue C Technology Ltd
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the Eclipse Public License v1.0
 * (http://www.eclipse.org/legal/epl-v10.html)
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 *******************************************************************************/

package org.mwc.debrief.pepys.model.db;

import java.beans.PropertyVetoException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import org.mwc.debrief.pepys.model.db.annotation.AnnotationsUtils;
import org.mwc.debrief.pepys.model.db.annotation.Location;
import org.sqlite.SQLiteConfig;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import MWC.GenericData.WorldArea;
import MWC.GenericData.WorldLocation;

public class SqliteDatabaseConnection extends DatabaseConnection {

	public static final String LOCATION_COORDINATES = "XYZ";
	public static final String SQLITE_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss.000000";
	public static final String DATABASE_FILE_PATH = "../org.mwc.debrief.pepys/test6.db";

	public SqliteDatabaseConnection() {
		super(); // Just formality :)
	}

	@Override
	public DatabaseConnection createInstance() throws PropertyVetoException {
		if (INSTANCE == null) {
			final SqliteDatabaseConnection newInstance = new SqliteDatabaseConnection();
			newInstance.initialize();
			INSTANCE = newInstance;
		}
		return INSTANCE;
	}

	@Override
	protected String createLocationQuery(final String tableName, final String columnName) {
		final StringBuilder query = new StringBuilder();
		for (final char c : LOCATION_COORDINATES.toCharArray()) {
			query.append(c);
			query.append(" (");
			query.append(getAlias(tableName));
			query.append(".");
			query.append(columnName);
			query.append(") as ");
			query.append(getAlias(tableName + columnName));
			query.append("_");
			query.append(c);
			query.append(", ");
		}
		return query.toString();
	}

	@Override
	protected WorldLocation createWorldLocation(final ResultSet result, final String columnName) throws SQLException {
		// WARNING.
		// THIS WILL CLASSIFY NULL LOCATION AT (0,0,0)
		// SAUL
		final double[] values = new double[3];
		for (int i = 0; i < values.length && i < LOCATION_COORDINATES.length(); i++) {
			values[i] = result.getDouble(columnName + "_" + LOCATION_COORDINATES.charAt(i));
		}
		return new WorldLocation(values[1], values[0], values[2]);
	}

	@Override
	public String databasePrefix() {
		return "";
	}

	@Override
	public String databaseSuffix() {
		return "";
	}

	protected void initialize() throws PropertyVetoException {
		// enabling dynamic extension loading
		// absolutely required by SpatiaLite
		final SQLiteConfig config = new SQLiteConfig();
		config.enableLoadExtension(true);

		pool = new ComboPooledDataSource();
		pool.setCheckoutTimeout(TIME_OUT);
		pool.setDriverClass("org.sqlite.JDBC");
		final String completePath = "jdbc:sqlite:" + DATABASE_FILE_PATH;
		pool.setJdbcUrl(completePath);
		pool.setProperties(config.toProperties());
	}

	@Override
	protected void loadExtention(final Connection connection, final Statement statement) throws SQLException {
		// loading SpatiaLite
		statement.execute("SELECT load_extension('mod_spatialite')");

		// enabling Spatial Metadata
		// using v.2.4.0 this automatically initializes SPATIAL_REF_SYS and
		// GEOMETRY_COLUMNS
		final String sql = "SELECT InitSpatialMetadata()";
		statement.execute(sql);
	}

	@Override
	public Collection<? extends Condition> createAreaFilter(WorldArea currentArea, final Class<?> type) {
		final ArrayList<Condition> conditions = new ArrayList<Condition>();

		final Field locationField = AnnotationsUtils.getField(type, Location.class);
		if (locationField != null && currentArea != null) {

			final WorldLocation topLeft = currentArea.getTopLeft();
			final WorldLocation bottomRight = currentArea.getBottomRight();
			final WorldLocation topRight = currentArea.getTopRight();
			final WorldLocation bottomLeft = currentArea.getBottomLeft();

			final String polygonArea = "POLYGON((" + topLeft.getLat() + " " + topLeft.getLong() + ","
					+ bottomLeft.getLat() + " " + bottomLeft.getLong() + "," + bottomRight.getLat() + " "
					+ bottomRight.getLong() + "," + topRight.getLat() + " " + topRight.getLong() + ","
					+ topLeft.getLat() + " " + topLeft.getLong() + "))";
			final String fieldName = getAlias(AnnotationsUtils.getTableName(type)) + "."
					+ AnnotationsUtils.getColumnName(locationField);

			final String geom = "ST_GeomFromText(" + ESCAPE_CHARACTER + polygonArea + ESCAPE_CHARACTER + ")";
			final String contains = "ST_Contains(" + geom + "," + fieldName + ")";
			System.out.println(contains);

			conditions.add(new Condition(contains));
		}

		return conditions;
	}

}

@@ -4510,34 +4510,35 @@
 %22 + topLeft.getL
-at
+ong
 () + %22 %22 + topLe
@@ -4536,35 +4536,34 @@
 %22 + topLeft.getL
-ong
+at
 () + %22,%22%0A%09%09%09%09%09+ 
@@ -4569,34 +4569,35 @@
  bottomLeft.getL
-at
+ong
 () + %22 %22 + botto
@@ -4602,27 +4602,26 @@
 tomLeft.getL
-ong
+at
 () + %22,%22 + b
@@ -4627,34 +4627,35 @@
 bottomRight.getL
-at
+ong
 () + %22 %22%0A%09%09%09%09%09+ 
@@ -4662,35 +4662,34 @@
 bottomRight.getL
-ong
+at
 () + %22,%22 + topRi
@@ -4692,26 +4692,27 @@
 opRight.getL
-at
+ong
 () + %22 %22 + t
@@ -4719,27 +4719,26 @@
 opRight.getL
-ong
+at
 () + %22,%22%0A%09%09%09
@@ -4749,26 +4749,27 @@
 topLeft.getL
-at
+ong
 () + %22 %22 + t
@@ -4775,27 +4775,26 @@
 topLeft.getL
-ong
+at
 () + %22))%22;%0A%09

Swap x/y coords - x (the horizontal axis) is longitude