/*
 * RiskScape™ Copyright New Zealand Institute for Earth Science Limited
 * (Earth Sciences New Zealand) is distributed for research purposes only
 * under the terms of AGPLv3.
 *
 * RiskScape™ Copyright 2026 New Zealand Institute for Earth Science
 * Limited (Earth Sciences New Zealand). All rights reserved. Source code
 * available under the AGPLv3.
 * 
 * This program is free software: you can redistribute it and/or modify it under
 *  the terms of the GNU Affero General Public License as published by the Free
 *  Software Foundation, either version 3 of the License, or (at your option) any
 *  later version.
 * 
 * This program is distributed for RESEARCH PURPOSES ONLY, in the hope that it will
 * be useful for research and education initiatives.
 * 
 * If you are not a researcher, or you are a researcher who wishes to use this
 * program on terms other than AGPLv3 (including those who wish to restrict the
 * distribution of any source code created using this program), please contact:
 * https://riskscape.org.nz
 * 
 * This program is distributed WITHOUT ANY WARRANTY; without even the implied
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Affero General Public License for more details.  You should have received a copy
 * of the GNU Affero General Public License along with this program.  If not, see
 * <http://www.gnu.org/licenses/>.
 * 
 * By way of summary only, under the AGPLv3:
 *     • Permissions of this strongest copyleft license are conditioned
 *       on making available complete source code of licensed works and
 *       modifications, which include larger works using a licensed work,
 *       under the same license.
 *     • Copyright and license notices must be preserved.
 *     • Contributors provide an express grant of patent rights.
 *     • When a modified version is used to provide a service over a
 *       network, the complete source code of the modified version must be made
 *       available.
 */
package nz.org.riskscape.engine.defaults.data.jdbc;

import static nz.org.riskscape.engine.SRIDSet.*;
import static org.hamcrest.Matchers.*;
import static org.junit.Assert.*;

import java.io.File;
import java.net.URI;
import java.nio.file.Path;
import java.sql.DatabaseMetaData;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Optional;
import java.util.concurrent.Callable;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

import org.geotools.referencing.CRS;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.geom.LineString;
import org.locationtech.jts.geom.MultiLineString;
import org.locationtech.jts.geom.MultiPoint;
import org.locationtech.jts.geom.MultiPolygon;
import org.locationtech.jts.geom.Point;
import org.locationtech.jts.geom.Polygon;
import org.mockito.Mockito;

import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;

import nz.org.riskscape.engine.GeometryMatchers;
import nz.org.riskscape.engine.Matchers;
import nz.org.riskscape.engine.OutputProblems;
import nz.org.riskscape.engine.ProjectTest;
import nz.org.riskscape.engine.SRIDSet;
import nz.org.riskscape.engine.StructMatchers;
import nz.org.riskscape.engine.TaskHelper;
import nz.org.riskscape.engine.TemporaryDirectoryTestHelper;
import nz.org.riskscape.engine.Tuple;
import nz.org.riskscape.engine.TupleMatchers;
import nz.org.riskscape.engine.TypeMatchers;
import nz.org.riskscape.engine.coverage.TypedCoverage;
import nz.org.riskscape.engine.data.Bookmark;
import nz.org.riskscape.engine.defaults.data.GeoPackageRelationResolver;
import nz.org.riskscape.engine.gt.GeometryHelper;
import nz.org.riskscape.engine.output.AxisSwapper;
import nz.org.riskscape.engine.output.RiskscapeWriter;
import nz.org.riskscape.engine.problem.ProblemMatchers;
import nz.org.riskscape.engine.relation.Relation;
import nz.org.riskscape.engine.types.CoverageType;
import nz.org.riskscape.engine.types.Nullable;
import nz.org.riskscape.engine.types.Referenced;
import nz.org.riskscape.engine.types.Struct;
import nz.org.riskscape.engine.types.Types;
import nz.org.riskscape.problem.ProblemSink;

public class GeoPackageOutputStoreTest extends ProjectTest implements TemporaryDirectoryTestHelper, TaskHelper {

  GeometryHelper latLongHelper = GeometryHelper.latLong(project.getSridSet());
  GeometryHelper crs84Helper = GeometryHelper.longLat(project.getSridSet());
  GeometryHelper nztmHelper = GeometryHelper.nzTransverseMercatorWithOriginShift(project.getSridSet());

  GeoPackageRelationResolver resolver = new GeoPackageRelationResolver(engine);

  Path testDirectory;
  File geoPackage;

  @Before
  public void init() throws Exception {
    testDirectory = createTempDirectory("GeoPackageOutputStoreTest");
    geoPackage = testDirectory.resolve("test.gpkg").toFile();
  }

  @After
  public void cleanup() throws Exception {
    remove(testDirectory);
  }

  @Test
  public void canWriteSpatialData() throws Exception {
    Struct inputType = Struct.of("geom", Referenced.of(Types.POINT, latLongHelper.getCrs()),
        "foo", Types.TEXT, "bar", Types.INTEGER);
    Point point2 = latLongHelper.point(10, 20);
    int latLongSRID = point2.getSRID();

    // in this test we use GeoPackageOutputStore.writerFor to check that the store is closed (shared connection)
    // when the writer is closed. The checking is done by reading the written file back, if the connection
    // wasn't closed then results would be incomplete.
    try (RiskscapeWriter writer = GeoPackageOutputStore.writerFor(geoPackage, inputType, "foos", false).get()) {
      writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 10), "foo1", 100L));
      writer.write(Tuple.ofValues(inputType, point2, "foo2", 200L));
    }

    // Ensure that the writing has not changed the input geom
    assertThat(point2.getX(), is(10D));
    assertThat(point2.getY(), is(20D));
    assertThat(point2.getSRID(), is(latLongSRID));

    Relation relation = getWrittenAsRelation("foos", false);
    Struct relationType = relation.getType();

    assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
        StructMatchers.isStructMember("geom", TypeMatchers.isReferencedType(EPSG4326_LONLAT, Types.POINT)),
        StructMatchers.isStructMember("foo", Types.TEXT),
        StructMatchers.isStructMember("bar", Types.INTEGER)
    )));

    List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
    assertThat(tuples, contains(
        allOf(
            TupleMatchers.tupleWithValue("foo", is("foo1")),
            TupleMatchers.tupleWithValue("bar", is(100L)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (10 10)")))
        ),
        allOf(
            TupleMatchers.tupleWithValue("foo", is("foo2")),
            TupleMatchers.tupleWithValue("bar", is(200L)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (20 10)")))
        )
    ));
  }

  @Test
  public void writesUnsupportedTypesAsText() throws Exception {
    Struct inputType = Struct.of("geom", Referenced.of(Types.POINT, latLongHelper.getCrs()),
        "foo", Nullable.of(CoverageType.WILD));
    TypedCoverage coverage = TypedCoverage.empty(inputType);

    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
        RiskscapeWriter writer = store.writerFor(inputType, "foos")
            .drainWarnings(warning -> sunkProblems.add(warning))
            .get()) {
      writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 10), coverage));
    }

    assertThat(sunkProblems, contains(
      ProblemMatchers.isProblem(
        OutputProblems.class,
        (r, f) -> f.outputTypeAsText(r.eq("foo"), r.eq(Nullable.of(CoverageType.WILD)), r.any()))
    ));

    Relation relation = getWrittenAsRelation("foos", true);
    Struct relationType = relation.getType();

    assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
        StructMatchers.isStructMember("geom", TypeMatchers.isReferencedType(EPSG4326_LONLAT, Types.POINT)),
        StructMatchers.isStructMember("foo", Types.TEXT)
    )));

    List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
    assertThat(tuples, contains(
        TupleMatchers.tupleWithValue("foo", is(coverage.toString()))
    ));
  }

  @Test
  public void appendFailsIfExistingTableHasDifferentStructure() throws Exception {
    Struct inputType = Struct.of("foo", Types.TEXT, "bar", Types.INTEGER);

    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false)) {
      // first prime the table with some content
      try (RiskscapeWriter writer = store.writerFor(inputType, "foos").get()) {
        writer.write(Tuple.ofValues(inputType, "foo1", 10L));
      }

      // now we do some appending
      try (RiskscapeWriter writer = store.appendingWriterFor(inputType, "foos").get()) {
        writer.write(Tuple.ofValues(inputType, "foo-appended", 100L));
      }

      // lets change the type a little
      inputType = Struct.of("foo", Types.TEXT, "bar", Types.TEXT);
      assertThat(store.appendingWriterFor(inputType, "foos"), Matchers.failedResult(
          is(BaseJdbcOutputStore.PROBLEMS.cannotAppendTableStructureMismatch("foos",
              "foos(foo TEXT, bar TEXT)",
              "foos(foo TEXT, bar INTEGER)"
          ).withChildren(BaseJdbcOutputStore.PROBLEMS.cannotAppendTableHint()))
      ));

      // lets add a column
      inputType = Struct.of("foo", Types.TEXT, "bar", Types.INTEGER, "baz", Types.INTEGER);
      assertThat(store.appendingWriterFor(inputType, "foos"), Matchers.failedResult(
          is(BaseJdbcOutputStore.PROBLEMS.cannotAppendTableStructureMismatch("foos",
              "foos(foo TEXT, bar INTEGER, baz INTEGER)",
              "foos(foo TEXT, bar INTEGER)"
          ).withChildren(BaseJdbcOutputStore.PROBLEMS.cannotAppendTableHint()))
      ));

      // lets remove a column
      inputType = Struct.of("foo", Types.TEXT);
      assertThat(store.appendingWriterFor(inputType, "foos"), Matchers.failedResult(
          is(BaseJdbcOutputStore.PROBLEMS.cannotAppendTableStructureMismatch("foos",
              "foos(foo TEXT)",
              "foos(foo TEXT, bar INTEGER)"
          ).withChildren(BaseJdbcOutputStore.PROBLEMS.cannotAppendTableHint()))
      ));
    }

  }

  @Test
  public void canWriteMultipleLayersConcurrently() throws Exception {
    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false)) {
      List<Callable<Void>> tasks = new ArrayList<>();
      tasks.add(getWriterTask("writer1", store));
      tasks.add(getWriterTask("writer2", store));
      tasks.add(getWriterTask("writer3", store));
      tasks.add(getWriterTask("writer4", store));
      executeTasks(tasks);
    }

    assertThat(getWrittenFooValues("writer1"), is(getExpectedFooValues("writer1")));
    assertThat(getWrittenFooValues("writer2"), is(getExpectedFooValues("writer2")));
    assertThat(getWrittenFooValues("writer3"), is(getExpectedFooValues("writer3")));
    assertThat(getWrittenFooValues("writer4"), is(getExpectedFooValues("writer4")));
  }

  Callable<Void> getWriterTask(String layerName, GeoPackageOutputStore store) {
    Struct inputType = Struct.of("geom", Referenced.of(Types.POINT, latLongHelper.getCrs()),
        "foo", Types.TEXT, "bar", Types.INTEGER);
    Point point = latLongHelper.point(10, 20);
    return () -> {
      try (RiskscapeWriter writer = store.writerFor(inputType, layerName).get()) {
        for (int i = 0; i < 100; i++) {
          writer.write(Tuple.ofValues(inputType, point, String.format("%s %d", layerName, i), i));
          try {
            Thread.sleep(10);
          } catch (InterruptedException e) {}
        }
      }
      return null;
    };
  }

  List<String> getWrittenFooValues(String layer) {
    Relation relation = getWrittenAsRelation(layer, false);
    return relation.iterator().collect(Collectors.mapping(t -> t.fetch("foo"), Collectors.toList()));
  }

  List<String> getExpectedFooValues(String layer) {
    return IntStream.range(0, 100)
        .mapToObj(i -> String.format("%s %d", layer, i))
        .collect(Collectors.toList());
  }

  @Test
  public void canWriteSpatialDataInCRS84() throws Exception {
    Struct inputType = Struct.of("geom", Referenced.of(Types.POINT, crs84Helper.getCrs()),
        "foo", Types.TEXT, "bar", Types.INTEGER);
    Point point2 = crs84Helper.point(10, 20);
    int crs84SRID = point2.getSRID();
    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
        RiskscapeWriter writer = store.writerFor(inputType, "foos").get()) {
      writer.write(Tuple.ofValues(inputType, point2, "foo2", 200L));
    }

    // Ensure that the writing has not changed the input geom
    assertThat(point2.getX(), is(10D));
    assertThat(point2.getY(), is(20D));
    assertThat(point2.getSRID(), is(crs84SRID));

    Relation relation = getWrittenAsRelation("foos", false);
    Struct relationType = relation.getType();

    assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
        StructMatchers.isStructMember("geom", TypeMatchers.isReferencedType(EPSG4326_LONLAT, Types.POINT)),
        StructMatchers.isStructMember("foo", Types.TEXT),
        StructMatchers.isStructMember("bar", Types.INTEGER)
    )));

    List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
    assertThat(tuples, contains(
        allOf(
            TupleMatchers.tupleWithValue("foo", is("foo2")),
            TupleMatchers.tupleWithValue("bar", is(200L)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (10 20)")))
        )
    ));
  }

  @Test
  public void willIncrementTableNameIfOneAlreadyExists() throws Exception {
    Struct inputType = Struct.of("geom", Referenced.of(Types.POINT, latLongHelper.getCrs()),
        "foo", Types.TEXT, "bar", Types.INTEGER);
    for (int i = 0; i < 3; i++) {
      try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
          RiskscapeWriter writer = store.writerFor(inputType, "foos").get()) {
        writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 10), "foo1", i + 100L));
        writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 20), "foo2", i + 200L));
      }

      String expectedTable = "foos";
      if (i > 0) {
        expectedTable = String.format("%s_%d", expectedTable, i);
      }
      Relation relation = getWrittenAsRelation(expectedTable, false);
      Struct relationType = relation.getType();

      assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
          StructMatchers.isStructMember("geom", TypeMatchers.isReferencedType(EPSG4326_LONLAT, Types.POINT)),
          StructMatchers.isStructMember("foo", Types.TEXT),
          StructMatchers.isStructMember("bar", Types.INTEGER)
      )));

      List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
      assertThat(tuples, contains(
          allOf(
              TupleMatchers.tupleWithValue("foo", is("foo1")),
              TupleMatchers.tupleWithValue("bar", is(i + 100L)),
              TupleMatchers.tupleWithValue("geom",
                  GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
              TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (10 10)")))
          ),
          allOf(
              TupleMatchers.tupleWithValue("foo", is("foo2")),
              TupleMatchers.tupleWithValue("bar", is(i + 200L)),
              TupleMatchers.tupleWithValue("geom",
                  GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
              TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (20 10)")))
          )
      ));
    }
  }

  @Test
  public void willReplaceTableIfOneAlreadyExistsAndReplaceIsSet() throws Exception {
    // we build a new store with replace set to true
    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, true)) {

      // in this test we'll just table and columns names that need to be quoted, eg foo-bar and bar-bar.
      // this will check that the identifier quoting is applied on tables create and deletes.
      Struct inputType = Struct.of("geom", Referenced.of(Types.POINT, latLongHelper.getCrs()),
          "foo", Types.TEXT, "bar", Types.INTEGER);
      RiskscapeWriter writer = store.writerFor(inputType, "foo-bar").get();
      writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 10), "foo1", 100L));
      writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 20), "foo2", 200L));
      writer.close();
      assertThat(writer.getStoredAt().toString(), endsWith("layer=foo-bar"));

      // On the second write we change the type being written to make it clearer that the last write
      // has won.
      Struct secondInputType = Struct.of("geom", Referenced.of(Types.POINT, latLongHelper.getCrs()),
          "foo", Types.TEXT, "bar-bar", Types.TEXT);
      writer = store.writerFor(secondInputType, "foo-bar").get();
      writer.write(Tuple.ofValues(secondInputType, latLongHelper.point(10, 10), "foo1", "100"));
      writer.write(Tuple.ofValues(secondInputType, latLongHelper.point(10, 20), "foo2", "200"));
      writer.close();
      assertThat(writer.getStoredAt().toString(), endsWith("layer=foo-bar"));
    }

    Relation relation = getWrittenAsRelation("foo-bar", false);
    Struct relationType = relation.getType();

    assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
        StructMatchers.isStructMember("geom", TypeMatchers.isReferencedType(EPSG4326_LONLAT, Types.POINT)),
        StructMatchers.isStructMember("foo", Types.TEXT), // The type from secondInputType
        StructMatchers.isStructMember("bar-bar", Types.TEXT)
    )));

    List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
    assertThat(tuples, contains(
        allOf(
            TupleMatchers.tupleWithValue("foo", is("foo1")),
            TupleMatchers.tupleWithValue("bar-bar", is("100")),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (10 10)")))
        ),
        allOf(
            TupleMatchers.tupleWithValue("foo", is("foo2")),
            TupleMatchers.tupleWithValue("bar-bar", is("200")),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (20 10)")))
        )
    ));
  }

  @Test
  public void willUseLayerAndColumnNamesWithHypensAndNumbers() throws Exception {
    String[] layerNames = new String[]{"foo-1", "33foo"};

    Struct inputType = Struct.of("geom", Referenced.of(Types.POINT, latLongHelper.getCrs()),
        "foo-1", Types.TEXT, "2-bar", Types.INTEGER);
    for (String layerName: layerNames) {
      try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
          RiskscapeWriter writer = store.writerFor(inputType, layerName).get()) {
        writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 10), "foo1", 100L));
        writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 20), "foo2", 200L));
      }


      String expectedTable = layerName;
      Relation relation = getWrittenAsRelation(expectedTable, false);
      Struct relationType = relation.getType();

      assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
          StructMatchers.isStructMember("geom", TypeMatchers.isReferencedType(EPSG4326_LONLAT, Types.POINT)),
          StructMatchers.isStructMember("foo-1", Types.TEXT),
          StructMatchers.isStructMember("2-bar", Types.INTEGER)
      )));

      List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
      assertThat(tuples, contains(
          allOf(
              TupleMatchers.tupleWithValue("foo-1", is("foo1")),
              TupleMatchers.tupleWithValue("2-bar", is(100L)),
              TupleMatchers.tupleWithValue("geom",
                  GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
              TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (10 10)")))
          ),
          allOf(
              TupleMatchers.tupleWithValue("foo-1", is("foo2")),
              TupleMatchers.tupleWithValue("2-bar", is(200L)),
              TupleMatchers.tupleWithValue("geom",
                  GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
              TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (20 10)")))
          )
      ));
    }
  }

  @Test
  public void canCreateTablesWithAwkwardNames() throws Exception {
    Struct inputType = Struct.of("foo", Types.TEXT, "bar", Types.INTEGER, "baz", Types.FLOATING);
    // if the user really wants to punish themselves with names like this, then so be it
    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
        RiskscapeWriter writer = store.writerFor(inputType, "This ;fellow; really GETS() my 'GOAT'!").get()) {
      writer.write(Tuple.ofValues(inputType, "foo1", 100L, 1.5D));
      writer.write(Tuple.ofValues(inputType, "foo2", 200L, 2.5D));
      URI writtenTo = writer.getStoredAt();
      assertThat(writtenTo.toString(), endsWith("layer=This+%3Bfellow%3B+really+GETS%28%29+my+%27GOAT%27%21"));
    }
  }

  @Test
  public void canWriteNonSpatialData() throws Exception {
    Struct inputType = Struct.of("foo", Types.TEXT, "bar", Types.INTEGER, "baz", Types.FLOATING);
    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
        RiskscapeWriter writer = store.writerFor(inputType, "foos").get()) {
      writer.write(Tuple.ofValues(inputType, "foo1", 100L, 1.5D));
      writer.write(Tuple.ofValues(inputType, "foo2", 200L, 2.5D));
    }

    // Geotools will only read feature tables from geopackage but this one is an attribute (non-spatial) table.
    // so maybe we should be reading the geopackage ourselves
//    Relation relation = getWrittenAsRelation("foos", false);
//    assertThat(relation.getType(), is(inputType));
//
//    List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
//    assertThat(tuples, contains(
//        Tuple.ofValues(inputType, "foo1", 100L, 1.5D),
//        Tuple.ofValues(inputType, "foo2", 200L, 2.5D)
//    ));
  }

  @Test
  public void canWriteNullableData() throws Exception {
    Struct inputType = Struct.of("geom", Referenced.of(Types.POINT, latLongHelper.getCrs()),
        "foo", Nullable.TEXT, "bar", Nullable.INTEGER);
    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
        RiskscapeWriter writer = store.writerFor(inputType, "foos").get()) {
      writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 10), "foo1", 100L));
      writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 20)));
    }

    Relation relation = getWrittenAsRelation("foos", false);
    Struct relationType = relation.getType();

    assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
        StructMatchers.isStructMember("geom", TypeMatchers.isReferencedType(EPSG4326_LONLAT, Types.POINT)),
        StructMatchers.isStructMember("foo", Types.TEXT),
        StructMatchers.isStructMember("bar", Types.INTEGER)
    )));

    List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
    assertThat(tuples, contains(
        allOf(
            TupleMatchers.tupleWithValue("foo", is("foo1")),
            TupleMatchers.tupleWithValue("bar", is(100L)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (10 10)")))
        ),
        allOf(
            TupleMatchers.tupleWithValue("foo", nullValue()),
            TupleMatchers.tupleWithValue("bar", nullValue()),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (20 10)")))
        )
    ));
  }

  @Test
  public void canWriteNestedDataMakingAttributesUniqueIfNeeded() throws Exception {
    Struct childType = Struct.of("geom", Referenced.of(Types.POINT, latLongHelper.getCrs()),
        "foo", Types.TEXT, "bar", Types.INTEGER);
    Struct inputType = Struct.of("i", childType, "bar", Types.TEXT);
    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
        RiskscapeWriter writer = store.writerFor(inputType, "foos").get()) {
      writer.write(Tuple.ofValues(inputType,
          Tuple.ofValues(childType, latLongHelper.point(10, 10), "foo1", 100L),
          "barbar"));
      writer.write(Tuple.ofValues(inputType,
          Tuple.ofValues(childType, latLongHelper.point(10, 20), "foo2", 200L),
          "barbar"));
    }

    Relation relation = getWrittenAsRelation("foos", false);
    Struct relationType = relation.getType();

    assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
        StructMatchers.isStructMember("geom", TypeMatchers.isReferencedType(EPSG4326_LONLAT, Types.POINT)),
        StructMatchers.isStructMember("foo", Types.TEXT),
        StructMatchers.isStructMember("bar", Types.INTEGER),  // from i.bar
        StructMatchers.isStructMember("bar_1", Types.TEXT)    // from bar, but _1 added to make it unique
    )));

    List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
    assertThat(tuples, contains(
        allOf(
            TupleMatchers.tupleWithValue("foo", is("foo1")),
            TupleMatchers.tupleWithValue("bar", is(100L)),
            TupleMatchers.tupleWithValue("bar_1", is("barbar")),
            TupleMatchers.tupleWithValue("geom",
                GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (10 10)")))
        ),
        allOf(
            TupleMatchers.tupleWithValue("foo", is("foo2")),
            TupleMatchers.tupleWithValue("bar", is(200L)),
            TupleMatchers.tupleWithValue("bar_1", is("barbar")),
            TupleMatchers.tupleWithValue("geom",
                GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (20 10)")))
        )
    ));
  }

  @Test
  public void canWriteNestedDataWithNulls() throws Exception {
    Struct childType = Struct.of("foo", Types.TEXT, "bar", Types.INTEGER);
    Struct inputType = Struct.of("geom",
        Referenced.of(Types.POINT, latLongHelper.getCrs()), "i", Nullable.of(childType));
    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
        RiskscapeWriter writer = store.writerFor(inputType, "foos").get()) {
      writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 10), Tuple.ofValues(childType, "foo1", 100L)));
      writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 20)));
    }

    Relation relation = getWrittenAsRelation("foos", false);
    Struct relationType = relation.getType();

    assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
        StructMatchers.isStructMember("geom", TypeMatchers.isReferencedType(EPSG4326_LONLAT, Types.POINT)),
        StructMatchers.isStructMember("foo", Types.TEXT),
        StructMatchers.isStructMember("bar", Types.INTEGER)
    )));

    List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
    assertThat(tuples, contains(
        allOf(
            TupleMatchers.tupleWithValue("foo", is("foo1")),
            TupleMatchers.tupleWithValue("bar", is(100L)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (10 10)")))
        ),
        allOf(
            TupleMatchers.tupleWithValue("foo", nullValue()),
            TupleMatchers.tupleWithValue("bar", nullValue()),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (20 10)")))
        )
    ));
  }

  @Test
  public void canWriteAllSupportedTypes() throws Exception {
    Struct inputType = Struct.of("geom", Referenced.of(Types.POINT, latLongHelper.getCrs()), "text", Types.TEXT,
        "int", Types.INTEGER, "float", Types.FLOATING)
        .add("date", Nullable.DATE)
        .add("boolean", Types.BOOLEAN);

    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
        RiskscapeWriter writer = store.writerFor(inputType, "foos").get()) {
      writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 10), "foo1", 100L, 12.2D,
          new Date(0), true));
      // lets check some nullable handling too
      writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 20), "foo2", 200L, 12.2D));
      // java.sql.Date only has the Date component, not the Time component
      writer.write(Tuple.ofValues(inputType, latLongHelper.point(10, 30), "foo3", 300L, 12.2D,
          new java.sql.Date(24 * 60 * 60 * 1000), false));
    }

    Relation relation = getWrittenAsRelation("foos", false);
    Struct relationType = relation.getType();

    assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
        StructMatchers.isStructMember("geom", TypeMatchers.isReferencedType(EPSG4326_LONLAT, Types.POINT)),
        StructMatchers.isStructMember("text", Types.TEXT),
        StructMatchers.isStructMember("int", Types.INTEGER),
        StructMatchers.isStructMember("float", Types.FLOATING),
        StructMatchers.isStructMember("date", Types.TEXT),
        StructMatchers.isStructMember("boolean", Types.BOOLEAN)
    )));

    List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
    assertThat(tuples, contains(
        allOf(
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (10 10)"))),
            TupleMatchers.tupleWithValue("text", is("foo1")),
            TupleMatchers.tupleWithValue("int", is(100L)),
            TupleMatchers.tupleWithValue("float", is(12.2D)),
            TupleMatchers.tupleWithValue("date", is("1970-01-01T00:00:00Z")),
            TupleMatchers.tupleWithValue("boolean", is(true))
        ),
        allOf(
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (20 10)"))),
            TupleMatchers.tupleWithValue("text", is("foo2")),
            TupleMatchers.tupleWithValue("int", is(200L)),
            TupleMatchers.tupleWithValue("float", is(12.2D)),
            TupleMatchers.tupleWithValue("date", nullValue()),
            TupleMatchers.tupleWithValue("boolean", nullValue())
        ),
        allOf(
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isInCrs(project.getSridSet(), EPSG4326_LONLAT)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(crs84Helper.fromWkt("POINT (30 10)"))),
            TupleMatchers.tupleWithValue("text", is("foo3")),
            TupleMatchers.tupleWithValue("int", is(300L)),
            TupleMatchers.tupleWithValue("float", is(12.2D)),
            TupleMatchers.tupleWithValue("date", is("1970-01-02T00:00:00Z")),
            TupleMatchers.tupleWithValue("boolean", is(false))
        )
      ));
  }

  @Test
  public void canWriteAMixedBagOfGeometries() throws Exception {
    // in this test we are showing that GeoPackages let you mix and match geometry types in the same layer.
    // we are also testing that each kind of geometry can be written and read back in as well.
    Struct inputType = Struct.of("geom", Referenced.of(Types.GEOMETRY, crs84Helper.getCrs()), "text", Types.TEXT);

    Point point = crs84Helper.point(10, 10);
    LineString line = crs84Helper.line(10, 10, 20, 20);
    Polygon box = crs84Helper.box(10, 10, 20, 20);

    MultiPoint mpoint = crs84Helper.multiPoint(
        crs84Helper.point(10, 20),
        crs84Helper.point(20, 20)
    );
    MultiLineString mline = crs84Helper.multiLine(
        crs84Helper.line(0, 0, 0, 20),
        crs84Helper.line(10, 10, 10, 20)
    );
    MultiPolygon mbox = crs84Helper.multiBox(
        crs84Helper.box(15, 15, 20, 20),
        crs84Helper.box(0,0, 5, 5)
    );
    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
        RiskscapeWriter writer = store.writerFor(inputType, "foos").get()) {
      writer.write(Tuple.ofValues(inputType, point, "point"));
      writer.write(Tuple.ofValues(inputType, line, "line"));
      writer.write(Tuple.ofValues(inputType, box, "box"));
      writer.write(Tuple.ofValues(inputType, mpoint, "mpoint"));
      writer.write(Tuple.ofValues(inputType, mline, "mline"));
      writer.write(Tuple.ofValues(inputType, mbox, "mbox"));
    }

    Relation relation = getWrittenAsRelation("foos", false);
    Struct relationType = relation.getType();

    assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
        StructMatchers.isStructMember("geom", TypeMatchers.isReferencedType(EPSG4326_LONLAT, Types.GEOMETRY)),
        StructMatchers.isStructMember("text", Types.TEXT)
    )));

    List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
    assertThat(tuples, contains(
        TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(point)),
        TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(line)),
        TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(box)),
        TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(mpoint)),
        TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(mline)),
        TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(mbox))
    ));
  }

  @Test
  public void canWriteNZTMGeometries() throws Exception {
    Struct inputType = Struct.of("geom", Referenced.of(Types.POINT, nztmHelper.getCrs()),
        "foo", Types.TEXT, "bar", Types.INTEGER);
    Point point1 = nztmHelper.point(10, 10);
    Point point2 = nztmHelper.point(10, 20);
    try (GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
        RiskscapeWriter writer = store.writerFor(inputType, "foos").get()) {
      writer.write(Tuple.ofValues(inputType, point1, "foo1", 100L));
      writer.write(Tuple.ofValues(inputType, point2, "foo2", 200L));
    }

    AxisSwapper axisSwapper = AxisSwapper.getForceXY(nztmHelper.getCrs(), null, ProblemSink.DEVNULL).get();

    Relation relation = getWrittenAsRelation("foos", false);
    Struct relationType = relation.getType();

    assertThat(relationType, StructMatchers.isStruct(Lists.newArrayList(
        StructMatchers.isStructMember("geom",
            TypeMatchers.isReferencedType(CRS.decode("EPSG:2193", true), Types.POINT)),
        StructMatchers.isStructMember("foo", Types.TEXT),
        StructMatchers.isStructMember("bar", Types.INTEGER)
    )));

    List<Tuple> tuples = relation.iterator().collect(Collectors.toList());
    assertThat(tuples, contains(
        allOf(
            TupleMatchers.tupleWithValue("foo", is("foo1")),
            TupleMatchers.tupleWithValue("bar", is(100L)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(axisSwapper.swapAxis(point1)))
        ),
        allOf(
            TupleMatchers.tupleWithValue("foo", is("foo2")),
            TupleMatchers.tupleWithValue("bar", is(200L)),
            TupleMatchers.tupleWithValue("geom", GeometryMatchers.isGeometry(axisSwapper.swapAxis(point2)))
        )
    ));
  }

  @Test
  public void quotesIdentifiers() throws Exception {
    DatabaseMetaData md = Mockito.mock(DatabaseMetaData.class);
    Mockito.when(md.getIdentifierQuoteString()).thenReturn("'");

    GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);
    assertThat(store.quoteIdentifier("foo", md), is("'foo'"));
    assertThat(store.quoteIdentifier("foo123", md), is("'foo123'"));
    assertThat(store.quoteIdentifier("foo_123", md), is("'foo_123'"));
    assertThat(store.quoteIdentifier("123foo", md), is("'123foo'"));

    assertThat(store.quoteIdentifier("foo'bar", md), is("'foo_bar'"));
    // makes for a funky identifier but will prevent injection
    assertThat(store.quoteIdentifier("foo'; drop table blah;", md), is("'foo_; drop table blah;'"));
  }

  @Test
  public void testPrepareGeometry() {
    Geometry point = latLongHelper.point(10, 30);
    GeoPackageOutputStore store = new GeoPackageOutputStore(geoPackage, false);

    assertThat(store.prepareGeometry(point, 4326, Optional.empty()), allOf(
        GeometryMatchers.isGeometry(latLongHelper.point(10, 30)),
        GeometryMatchers.geometryWithSrid(4326)
    ));
    // now we want to ensure that prepareGeometry hasn't changed the input geom
    assertThat(point, allOf(
        GeometryMatchers.isGeometry(latLongHelper.point(10, 30)),
        GeometryMatchers.geometryWithSrid(project.getSridSet().get(latLongHelper.getCrs()))
    ));

    // now rinse and repeat with an axis swapper in play
    Optional<AxisSwapper> swapper = AxisSwapper.getForceXY(SRIDSet.EPSG4326_LATLON, null, ProblemSink.DEVNULL);
    assertThat(store.prepareGeometry(point, 84, swapper), allOf(
        GeometryMatchers.isGeometry(crs84Helper.point(30, 10)),
        GeometryMatchers.geometryWithSrid(84)
    ));
    // now we want to ensure that prepareGeometry hasn't changed the input geom
    assertThat(point, allOf(
        GeometryMatchers.isGeometry(latLongHelper.point(10, 30)),
        GeometryMatchers.geometryWithSrid(project.getSridSet().get(latLongHelper.getCrs()))
    ));
  }


  Relation getWrittenAsRelation(String layer, boolean skipInvalid) {
    Bookmark bookmark = new Bookmark("test", "", "", geoPackage.toURI(),
        ImmutableMap.of("layer", Arrays.asList(layer), "skip-invalid", Arrays.asList(String.valueOf(skipInvalid))));
    return resolver.resolve(bookmark, executionContext)
        .map(resolved -> resolved.getData(Relation.class).get())
        .orElse(null);
  }

}
