package mil.nga.geopackage.test;

import java.io.File;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import junit.framework.TestCase;
import mil.nga.geopackage.BoundingBox;
import mil.nga.geopackage.GeoPackage;
import mil.nga.geopackage.core.contents.Contents;
import mil.nga.geopackage.core.contents.ContentsDao;
import mil.nga.geopackage.core.contents.ContentsDataType;
import mil.nga.geopackage.core.srs.SpatialReferenceSystem;
import mil.nga.geopackage.db.GeoPackageDataType;
import mil.nga.geopackage.features.columns.GeometryColumns;
import mil.nga.geopackage.features.columns.GeometryColumnsDao;
import mil.nga.geopackage.features.index.FeatureIndexManager;
import mil.nga.geopackage.features.user.FeatureColumn;
import mil.nga.geopackage.features.user.FeatureDao;
import mil.nga.geopackage.features.user.FeatureRow;
import mil.nga.geopackage.schema.TableColumnKey;
import mil.nga.geopackage.tiles.matrix.TileMatrix;
import mil.nga.geopackage.tiles.matrix.TileMatrixDao;
import mil.nga.geopackage.tiles.matrixset.TileMatrixSet;
import mil.nga.geopackage.tiles.matrixset.TileMatrixSetDao;
import mil.nga.geopackage.tiles.user.TileDao;
import mil.nga.sf.GeometryType;
import mil.nga.sf.proj.Projection;
import mil.nga.sf.proj.ProjectionConstants;
import mil.nga.sf.proj.ProjectionFactory;

/**
 * GeoPackage Utility test methods
 * 
 * @author osbornb
 */
public class GeoPackageTestUtils {

	/**
	 * Test create feature table with metadata
	 * 
	 * @param geoPackage
	 * @throws SQLException
	 */
	public static void testCreateFeatureTableWithMetadata(GeoPackage geoPackage)
			throws SQLException {

		GeometryColumns geometryColumns = new GeometryColumns();
		geometryColumns.setId(new TableColumnKey("feature_metadata", "geom"));
		geometryColumns.setGeometryType(GeometryType.POINT);
		geometryColumns.setZ((byte) 1);
		geometryColumns.setM((byte) 0);

		BoundingBox boundingBox = new BoundingBox(-90, -45, 90, 45);

		SpatialReferenceSystem srs = geoPackage.getSpatialReferenceSystemDao()
				.getOrCreateCode(ProjectionConstants.AUTHORITY_EPSG,
						ProjectionConstants.EPSG_WEB_MERCATOR);
		geometryColumns = geoPackage.createFeatureTableWithMetadata(
				geometryColumns, boundingBox, srs.getId());

		validateFeatureTableWithMetadata(geoPackage, geometryColumns, null,
				null);
	}

	/**
	 * Test create feature table with metadata and id column
	 * 
	 * @param geoPackage
	 * @throws SQLException
	 */
	public static void testCreateFeatureTableWithMetadataIdColumn(
			GeoPackage geoPackage) throws SQLException {

		GeometryColumns geometryColumns = new GeometryColumns();
		geometryColumns.setId(new TableColumnKey("feature_metadata2", "geom2"));
		geometryColumns.setGeometryType(GeometryType.POINT);
		geometryColumns.setZ((byte) 1);
		geometryColumns.setM((byte) 0);

		BoundingBox boundingBox = new BoundingBox(-90, -45, 90, 45);

		SpatialReferenceSystem srs = geoPackage.getSpatialReferenceSystemDao()
				.getOrCreateCode(ProjectionConstants.AUTHORITY_EPSG,
						ProjectionConstants.EPSG_WEB_MERCATOR);
		String idColumn = "my_id";
		geometryColumns = geoPackage.createFeatureTableWithMetadata(
				geometryColumns, idColumn, boundingBox, srs.getId());

		validateFeatureTableWithMetadata(geoPackage, geometryColumns, idColumn,
				null);
	}

	/**
	 * Test create feature table with metadata and additional columns
	 * 
	 * @param geoPackage
	 * @throws SQLException
	 */
	public static void testCreateFeatureTableWithMetadataAdditionalColumns(
			GeoPackage geoPackage) throws SQLException {

		GeometryColumns geometryColumns = new GeometryColumns();
		geometryColumns.setId(new TableColumnKey("feature_metadata", "geom"));
		geometryColumns.setGeometryType(GeometryType.POINT);
		geometryColumns.setZ((byte) 1);
		geometryColumns.setM((byte) 0);

		BoundingBox boundingBox = new BoundingBox(-90, -45, 90, 45);

		List<FeatureColumn> additionalColumns = getFeatureColumns();

		SpatialReferenceSystem srs = geoPackage.getSpatialReferenceSystemDao()
				.getOrCreateCode(ProjectionConstants.AUTHORITY_EPSG,
						ProjectionConstants.EPSG_WEB_MERCATOR);
		geometryColumns = geoPackage.createFeatureTableWithMetadata(
				geometryColumns, additionalColumns, boundingBox, srs.getId());

		validateFeatureTableWithMetadata(geoPackage, geometryColumns, null,
				additionalColumns);
	}

	/**
	 * Test create feature table with metadata, id column, and additional
	 * columns
	 * 
	 * @param geoPackage
	 * @throws SQLException
	 */
	public static void testCreateFeatureTableWithMetadataIdColumnAdditionalColumns(
			GeoPackage geoPackage) throws SQLException {

		GeometryColumns geometryColumns = new GeometryColumns();
		geometryColumns.setId(new TableColumnKey("feature_metadata", "geom"));
		geometryColumns.setGeometryType(GeometryType.POINT);
		geometryColumns.setZ((byte) 1);
		geometryColumns.setM((byte) 0);

		BoundingBox boundingBox = new BoundingBox(-90, -45, 90, 45);

		List<FeatureColumn> additionalColumns = getFeatureColumns();

		SpatialReferenceSystem srs = geoPackage.getSpatialReferenceSystemDao()
				.getOrCreateCode(ProjectionConstants.AUTHORITY_EPSG,
						ProjectionConstants.EPSG_WEB_MERCATOR);
		String idColumn = "my_other_id";
		geometryColumns = geoPackage.createFeatureTableWithMetadata(
				geometryColumns, idColumn, additionalColumns, boundingBox,
				srs.getId());

		validateFeatureTableWithMetadata(geoPackage, geometryColumns, idColumn,
				additionalColumns);
	}

	/**
	 * Get additional feature columns to create
	 * 
	 * @return
	 */
	public static List<FeatureColumn> getFeatureColumns() {
		List<FeatureColumn> columns = new ArrayList<FeatureColumn>();

		columns.add(FeatureColumn.createColumn(7, "test_text_limited",
				GeoPackageDataType.TEXT, 5L));
		columns.add(FeatureColumn.createColumn(8, "test_blob_limited",
				GeoPackageDataType.BLOB, 7L));
		columns.add(FeatureColumn.createColumn(9, "test_date",
				GeoPackageDataType.DATE));
		columns.add(FeatureColumn.createColumn(10, "test_datetime",
				GeoPackageDataType.DATETIME));
		columns.add(FeatureColumn.createColumn(2, "test_text",
				GeoPackageDataType.TEXT, false, ""));
		columns.add(FeatureColumn.createColumn(3, "test_real",
				GeoPackageDataType.REAL));
		columns.add(FeatureColumn.createColumn(4, "test_boolean",
				GeoPackageDataType.BOOLEAN));

		FeatureColumn blobColumn = FeatureColumn.createColumn("test_blob",
				GeoPackageDataType.BLOB);
		columns.add(blobColumn);
		// Let the index for this column be automatically set to 5
		// blobColumn.setIndex(5);

		// Test setting an index after column creation but before table creation
		FeatureColumn integerColumn = FeatureColumn.createColumn("test_integer",
				GeoPackageDataType.INTEGER);
		columns.add(integerColumn);
		integerColumn.setIndex(6);

		return columns;
	}

	/**
	 * Validate feature table with metadata
	 * 
	 * @param geoPackage
	 * @throws SQLException
	 */
	private static void validateFeatureTableWithMetadata(GeoPackage geoPackage,
			GeometryColumns geometryColumns, String idColumn,
			List<FeatureColumn> additionalColumns) throws SQLException {

		GeometryColumnsDao dao = geoPackage.getGeometryColumnsDao();

		GeometryColumns queryGeometryColumns = dao
				.queryForId(geometryColumns.getId());
		TestCase.assertNotNull(queryGeometryColumns);

		TestCase.assertEquals(geometryColumns.getTableName(),
				queryGeometryColumns.getTableName());
		TestCase.assertEquals(geometryColumns.getColumnName(),
				queryGeometryColumns.getColumnName());
		TestCase.assertEquals(GeometryType.POINT,
				queryGeometryColumns.getGeometryType());
		TestCase.assertEquals(geometryColumns.getZ(),
				queryGeometryColumns.getZ());
		TestCase.assertEquals(geometryColumns.getM(),
				queryGeometryColumns.getM());

		FeatureDao featureDao = geoPackage
				.getFeatureDao(geometryColumns.getTableName());
		FeatureRow featureRow = featureDao.newRow();

		TestCase.assertEquals(
				2 + (additionalColumns != null ? additionalColumns.size() : 0),
				featureRow.columnCount());
		if (idColumn == null) {
			idColumn = "id";
		}
		TestCase.assertEquals(idColumn, featureRow.getColumnName(0));
		TestCase.assertEquals(geometryColumns.getColumnName(),
				featureRow.getColumnName(1));

		if (additionalColumns != null) {
			TestCase.assertEquals("test_text", featureRow.getColumnName(2));
			TestCase.assertEquals("test_real", featureRow.getColumnName(3));
			TestCase.assertEquals("test_boolean", featureRow.getColumnName(4));
			TestCase.assertEquals("test_blob", featureRow.getColumnName(5));
			TestCase.assertEquals("test_integer", featureRow.getColumnName(6));
			TestCase.assertEquals("test_text_limited",
					featureRow.getColumnName(7));
			TestCase.assertEquals("test_blob_limited",
					featureRow.getColumnName(8));
		}
	}

	/**
	 * Test deleting tables by name
	 * 
	 * @param geoPackage
	 * @throws SQLException
	 */
	public static void testDeleteTables(GeoPackage geoPackage)
			throws SQLException {

		GeometryColumnsDao geometryColumnsDao = geoPackage
				.getGeometryColumnsDao();
		TileMatrixSetDao tileMatrixSetDao = geoPackage.getTileMatrixSetDao();
		ContentsDao contentsDao = geoPackage.getContentsDao();

		TestCase.assertTrue(geometryColumnsDao.isTableExists()
				|| tileMatrixSetDao.isTableExists());

		geoPackage.foreignKeys(false);

		if (geometryColumnsDao.isTableExists()) {

			TestCase.assertEquals(geoPackage.getFeatureTables().size(),
					geometryColumnsDao.countOf());
			for (String featureTable : geoPackage.getFeatureTables()) {
				TestCase.assertTrue(geoPackage.isTable(featureTable));
				TestCase.assertNotNull(contentsDao.queryForId(featureTable));
				geoPackage.deleteTable(featureTable);
				TestCase.assertFalse(geoPackage.isTable(featureTable));
				TestCase.assertNull(contentsDao.queryForId(featureTable));
			}
			TestCase.assertEquals(0, geometryColumnsDao.countOf());

			geoPackage.dropTable(GeometryColumns.TABLE_NAME);

			TestCase.assertFalse(geometryColumnsDao.isTableExists());
		}

		if (tileMatrixSetDao.isTableExists()) {
			TileMatrixDao tileMatrixDao = geoPackage.getTileMatrixDao();

			TestCase.assertTrue(tileMatrixSetDao.isTableExists());
			TestCase.assertTrue(tileMatrixDao.isTableExists());

			TestCase.assertEquals(geoPackage.getTables(ContentsDataType.TILES)
					.size()
					+ geoPackage.getTables(ContentsDataType.GRIDDED_COVERAGE)
							.size(),
					tileMatrixSetDao.countOf());
			for (String tileTable : geoPackage.getTileTables()) {
				TestCase.assertTrue(geoPackage.isTable(tileTable));
				TestCase.assertNotNull(contentsDao.queryForId(tileTable));
				geoPackage.deleteTable(tileTable);
				TestCase.assertFalse(geoPackage.isTable(tileTable));
				TestCase.assertNull(contentsDao.queryForId(tileTable));
			}
			TestCase.assertEquals(geoPackage
					.getTables(ContentsDataType.GRIDDED_COVERAGE).size(),
					tileMatrixSetDao.countOf());

			geoPackage.dropTable(TileMatrix.TABLE_NAME);
			geoPackage.dropTable(TileMatrixSet.TABLE_NAME);

			TestCase.assertFalse(tileMatrixSetDao.isTableExists());
			TestCase.assertFalse(tileMatrixDao.isTableExists());
		}

		for (String attributeTable : geoPackage.getAttributesTables()) {

			TestCase.assertTrue(geoPackage.isTable(attributeTable));
			TestCase.assertNotNull(contentsDao.queryForId(attributeTable));
			geoPackage.deleteTable(attributeTable);
			TestCase.assertFalse(geoPackage.isTable(attributeTable));
			TestCase.assertNull(contentsDao.queryForId(attributeTable));

		}

	}

	/**
	 * Test GeoPackage bounds
	 * 
	 * @param geoPackage
	 *            GeoPackage
	 * @throws SQLException
	 *             upon error
	 */
	public static void testBounds(GeoPackage geoPackage) throws SQLException {

		Projection projection = ProjectionFactory.getProjection(
				ProjectionConstants.AUTHORITY_EPSG,
				ProjectionConstants.EPSG_WORLD_GEODETIC_SYSTEM);

		// Create a feature table with empty contents
		GeometryColumns geometryColumns = new GeometryColumns();
		geometryColumns
				.setId(new TableColumnKey("feature_empty_contents", "geom"));
		geometryColumns.setGeometryType(GeometryType.POINT);
		geometryColumns.setZ((byte) 0);
		geometryColumns.setM((byte) 0);
		SpatialReferenceSystem srs = geoPackage.getSpatialReferenceSystemDao()
				.queryForOrganizationCoordsysId(
						ProjectionConstants.AUTHORITY_EPSG,
						ProjectionConstants.EPSG_WORLD_GEODETIC_SYSTEM);
		geoPackage.createFeatureTableWithMetadata(geometryColumns, null,
				srs.getId());

		BoundingBox geoPackageContentsBoundingBox = geoPackage
				.getContentsBoundingBox(projection);

		BoundingBox expectedContentsBoundingBox = null;

		ContentsDao contentsDao = geoPackage.getContentsDao();
		for (Contents contents : contentsDao.queryForAll()) {

			BoundingBox contentsBoundingBox = contents
					.getBoundingBox(projection);
			if (contentsBoundingBox != null) {
				TestCase.assertTrue(geoPackageContentsBoundingBox
						.contains(contentsBoundingBox));

				if (expectedContentsBoundingBox == null) {
					expectedContentsBoundingBox = contentsBoundingBox;
				} else {
					expectedContentsBoundingBox = expectedContentsBoundingBox
							.union(contentsBoundingBox);
				}
			}

			TestCase.assertEquals(contentsBoundingBox,
					geoPackage.getContentsBoundingBox(projection,
							contents.getTableName()));
			TestCase.assertEquals(contents.getBoundingBox(),
					geoPackage.getContentsBoundingBox(contents.getTableName()));

		}

		TestCase.assertEquals(expectedContentsBoundingBox,
				geoPackageContentsBoundingBox);

		BoundingBox geoPackageBoundingBox = geoPackage
				.getBoundingBox(projection);
		BoundingBox geoPackageManualBoundingBox = geoPackage
				.getBoundingBox(projection, true);

		BoundingBox expectedBoundingBox = expectedContentsBoundingBox;
		BoundingBox expectedManualBoundingBox = expectedContentsBoundingBox;

		for (Contents contents : contentsDao.queryForAll()) {

			ContentsDataType dataType = contents.getDataType();
			if (dataType != null) {

				switch (dataType) {
				case FEATURES:
					FeatureIndexManager manager = new FeatureIndexManager(
							geoPackage, contents.getTableName());
					BoundingBox featureBoundingBox = manager
							.getBoundingBox(projection);
					if (featureBoundingBox != null) {
						if (manager.isIndexed()) {
							expectedBoundingBox = expectedBoundingBox
									.union(featureBoundingBox);
						}
						expectedManualBoundingBox = expectedManualBoundingBox
								.union(featureBoundingBox);
					}

					BoundingBox expectedFeatureProjectionBoundingBox = contents
							.getBoundingBox(projection);
					if (featureBoundingBox != null && manager.isIndexed()) {
						if (expectedFeatureProjectionBoundingBox == null) {
							expectedFeatureProjectionBoundingBox = featureBoundingBox;
						} else {
							expectedFeatureProjectionBoundingBox = expectedFeatureProjectionBoundingBox
									.union(featureBoundingBox);
						}
					}
					BoundingBox featureProjectionBoundingBox = geoPackage
							.getBoundingBox(projection,
									contents.getTableName());
					if (featureProjectionBoundingBox == null) {
						TestCase.assertNull(
								expectedFeatureProjectionBoundingBox);
					} else {
						TestCase.assertTrue(expectedBoundingBox
								.contains(featureProjectionBoundingBox));
						TestCase.assertEquals(
								expectedFeatureProjectionBoundingBox,
								featureProjectionBoundingBox);
					}

					BoundingBox expectedFeatureManualProjectionBoundingBox = contents
							.getBoundingBox(projection);
					if (featureBoundingBox != null) {
						if (expectedFeatureManualProjectionBoundingBox == null) {
							expectedFeatureManualProjectionBoundingBox = featureBoundingBox;
						} else {
							expectedFeatureManualProjectionBoundingBox = expectedFeatureManualProjectionBoundingBox
									.union(featureBoundingBox);
						}
					}
					BoundingBox featureManualProjectionBoundingBox = geoPackage
							.getBoundingBox(projection, contents.getTableName(),
									true);
					if (featureManualProjectionBoundingBox == null) {
						TestCase.assertNull(
								expectedFeatureManualProjectionBoundingBox);
					} else {
						TestCase.assertTrue(expectedManualBoundingBox
								.contains(featureManualProjectionBoundingBox));
						TestCase.assertEquals(
								expectedFeatureManualProjectionBoundingBox,
								featureManualProjectionBoundingBox);
					}

					featureBoundingBox = manager.getBoundingBox();

					BoundingBox expectedFeatureBoundingBox = contents
							.getBoundingBox();
					if (featureBoundingBox != null && manager.isIndexed()) {
						if (expectedFeatureBoundingBox == null) {
							expectedFeatureBoundingBox = featureBoundingBox;
						} else {
							expectedFeatureBoundingBox = expectedFeatureBoundingBox
									.union(featureBoundingBox);
						}
					}
					BoundingBox featureBox = geoPackage
							.getBoundingBox(contents.getTableName());
					if (featureBox == null) {
						TestCase.assertNull(expectedFeatureBoundingBox);
					} else {
						TestCase.assertEquals(expectedFeatureBoundingBox,
								featureBox);
					}

					BoundingBox expectedFeatureManualBoundingBox = contents
							.getBoundingBox();
					if (featureBoundingBox != null) {
						if (expectedFeatureManualBoundingBox == null) {
							expectedFeatureManualBoundingBox = featureBoundingBox;
						} else {
							expectedFeatureManualBoundingBox = expectedFeatureManualBoundingBox
									.union(featureBoundingBox);
						}
					}
					BoundingBox featureManualBoundingBox = geoPackage
							.getBoundingBox(contents.getTableName(), true);
					if (featureManualBoundingBox == null) {
						TestCase.assertNull(expectedFeatureManualBoundingBox);
					} else {
						TestCase.assertEquals(expectedFeatureManualBoundingBox,
								featureManualBoundingBox);
					}

					manager.close();

					break;
				case TILES:
				case GRIDDED_COVERAGE:
					TileDao tileDao = geoPackage
							.getTileDao(contents.getTableName());
					BoundingBox tileBoundingBox = tileDao
							.getBoundingBox(projection);
					expectedBoundingBox = expectedBoundingBox
							.union(tileBoundingBox);
					expectedManualBoundingBox = expectedManualBoundingBox
							.union(tileBoundingBox);

					BoundingBox expectedProjectionTileBoundingBox = tileBoundingBox
							.union(contents.getBoundingBox(projection));
					TestCase.assertEquals(expectedProjectionTileBoundingBox,
							geoPackage.getBoundingBox(projection,
									contents.getTableName()));
					TestCase.assertEquals(expectedProjectionTileBoundingBox,
							geoPackage.getBoundingBox(projection,
									contents.getTableName(), true));

					BoundingBox expectedTileBoundingBox = tileDao
							.getBoundingBox().union(contents.getBoundingBox());
					TestCase.assertEquals(expectedTileBoundingBox,
							geoPackage.getBoundingBox(contents.getTableName()));
					TestCase.assertEquals(expectedTileBoundingBox, geoPackage
							.getBoundingBox(contents.getTableName(), true));
					break;
				default:
					break;
				}

			}

		}

		TestCase.assertEquals(expectedBoundingBox, geoPackageBoundingBox);
		TestCase.assertEquals(expectedManualBoundingBox,
				geoPackageManualBoundingBox);

	}

	/**
	 * Test the GeoPackage vacuum
	 * 
	 * @param geoPackage
	 *            GeoPackage
	 */
	public static void testVacuum(GeoPackage geoPackage) {

		String path = geoPackage.getPath();
		File file = new File(path);
		long size = file.length();

		for (String table : geoPackage.getTables()) {

			geoPackage.deleteTable(table);

			TestCase.assertEquals(size, file.length());

			geoPackage.vacuum();

			long newSize = file.length();
			TestCase.assertTrue(size > newSize);
			size = newSize;

		}

	}

}