using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Xml.Serialization;
using Deltares.Geographic;
using Deltares.Geometry;
using Deltares.Geotechnics;
using Deltares.Geotechnics.IO;
using Deltares.Geotechnics.ConePenetrationTest;
using Deltares.Geotechnics.GeotechnicalGeometry;
using Deltares.Geotechnics.IO.Importers;
using Deltares.Geotechnics.Mechanisms;
using Deltares.Probabilistic;
using Deltares.Soilbase;
using Deltares.Standard.Attributes;
using Deltares.Standard.EventPublisher;
using Deltares.Standard.Language;
using Deltares.Standard.Logging;
using Deltares.Standard.TestUtils;
using KellermanSoftware.CompareNetObjects;
using NUnit.Framework;
using AttributesExtensionHelper = Deltares.Standard.Attributes.AttributesExtensionHelper;
using XmlSerializer = System.Xml.Serialization.XmlSerializer;

namespace Tests
{
    [TestFixture]
    public class SoilModelDatabaseTest
    {
        private bool oldIsDataEventPublishStopped;
        private const string TestDataFolder = @"..\..\Deltares.DSoilModel.Tests\TestData\";
        private const string TestOutputFolder = TestDataFolder + @"SoilModelDatabaseTest\";

        [TestFixtureSetUp]
        public void FixtureSetup()
        {
            oldIsDataEventPublishStopped = DataEventPublisher.IsDataEventPublishStopped;
            DataEventPublisher.IsDataEventPublishStopped = true;
            if (!Directory.Exists(TestOutputFolder))
            {
                Directory.CreateDirectory(TestOutputFolder);
            }
        }

        [TestFixtureTearDown]
        public void FixtureTearDown()
        {
            DataEventPublisher.IsDataEventPublishStopped = oldIsDataEventPublishStopped;
            Directory.Delete(TestOutputFolder, true);
        }

        [Test]
        public void EnsureThatMechanismEnumValuesDoNotChange()
        {
            // if any of these enums change, in principle all existing .soil databases containing soil segments for these mechanisms would be broken !!
            Assert.AreEqual((int)Mechanism.None, 0);
            Assert.AreEqual((int)Mechanism.Stability, 1);
            Assert.AreEqual((int)Mechanism.Settlement, 2);
            Assert.AreEqual((int)Mechanism.PipingUpliftGradient, 3);
            Assert.AreEqual((int)Mechanism.Piping, 4);
            Assert.AreEqual((int)Mechanism.Koswat, 5);
            Assert.AreEqual((int)Mechanism.FlowSlide, 6);
            Assert.AreEqual((int)Mechanism.Overtopping, 7);
            Assert.AreEqual((int)Mechanism.AnchorLoading, 9);
            Assert.AreEqual((int)Mechanism.DAM, 10);
            Assert.AreEqual((int)Mechanism.Structures, 11);
            Assert.AreEqual((int)Mechanism.BlockRevetment, 12);
            Assert.AreEqual((int)Mechanism.AssessmentLevel, 13);
            Assert.AreEqual((int)Mechanism.Dunes, 14);
            Assert.AreEqual((int)Mechanism.AsphaltRevetment, 15);
        }

        [Test]
        public void ThrowExceptionsOrListErrorMessagesForUniqueNameConstraints()
        {
            const string testFile = TestOutputFolder + "testconstraints.soil";
            Exception exception = null;

            // constraint: no duplicate soil names
            var soils = new List<Soil>
            {
                new Soil { Name = "soil" },
                new Soil { Name = "soil" }
            };

            using (var database = new SoilModelDatabase())
            {
                Assert.IsFalse(database.HandleExceptions);
                database.New(testFile);
                try
                {
                    database.WriteSoils(soils);
                }
                catch (Exception ex)
                {
                    exception = ex;
                }
                database.Close();
                Assert.IsInstanceOf(typeof(SQLiteException), exception);

                database.ErrorMessages.Clear();
                database.HandleExceptions = true;
                database.New(testFile);
                database.WriteSoils(soils);
                database.Close();
                Assert.IsTrue(database.ErrorMessages.Any(m => m.MessageType == LogMessageType.Error && m.Subject is Soil && !m.Message.Contains("MA_Name")));
            }

            // constraint: no duplicate names for soilprofile 1D
            var profile1Ds = new List<SoilProfile1D>
            {
                new SoilProfile1D { Name = "SoilProfile1D" },
                new SoilProfile1D { Name = "SoilProfile1D" }
            };

            using (var database = new SoilModelDatabase())
            {
                database.New(testFile);
                try
                {
                    exception = null;
                    database.WriteSoilProfile1Ds(profile1Ds);
                }
                catch (Exception ex)
                {
                    exception = ex;
                }
                database.Close();
                Assert.IsInstanceOf(typeof(SQLiteException), exception);

                database.ErrorMessages.Clear();
                database.HandleExceptions = true;
                database.New(testFile);
                database.WriteSoilProfile1Ds(profile1Ds);
                database.Close();
                Assert.IsTrue(database.ErrorMessages.Any(m => m.MessageType == LogMessageType.Error && m.Subject is SoilProfile1D && !m.Message.Contains("SP1D_Name")));
            }

            // constraint: no duplicate names for soilprofile 2D
            var profile2Ds = new List<SoilProfile2D>
            {
                new SoilProfile2D { Name = "SoilProfile2D" },
                new SoilProfile2D { Name = "SoilProfile2D" }
            };

            using (var database = new SoilModelDatabase())
            {
                database.New(testFile);
                try
                {
                    exception = null;
                    database.WriteSoilProfile2Ds(profile2Ds);
                }
                catch (Exception ex)
                {
                    exception = ex;
                }
                database.Close();
                Assert.IsInstanceOf(typeof(SQLiteException), exception);

                database.ErrorMessages.Clear();
                database.HandleExceptions = true;
                database.New(testFile);
                database.WriteSoilProfile2Ds(profile2Ds);
                database.Close();
                Assert.IsTrue(database.ErrorMessages.Any(m => m.MessageType == LogMessageType.Error && m.Subject is SoilProfile2D && !m.Message.Contains("SP2D_Name")));
            }

            // constraint: no duplicate names for soil segments
            var segments = new List<SoilSegment>
            {
                new SoilSegment { Name = "SoilSegment" },
                new SoilSegment { Name = "SoilSegment" }
            };

            using (var database = new SoilModelDatabase())
            {
                database.New(testFile);
                try
                {
                    exception = null;
                    database.WriteSoilSegments(segments);
                }
                catch (Exception ex)
                {
                    exception = ex;
                }
                database.Close();
                Assert.IsInstanceOf(typeof(SQLiteException), exception);

                database.ErrorMessages.Clear();
                database.HandleExceptions = true;
                database.New(testFile);
                database.WriteSoilSegments(segments);
                database.Close();
                Assert.IsTrue(database.ErrorMessages.Any(m => m.MessageType == LogMessageType.Error && m.Subject is SoilSegment && !m.Message.Contains("SE_Name")));
            }

            // constraint: no duplicate names for Cpt's
            var cpts= new List<ConePenetrationTestData>
            {
                new ConePenetrationTestData { Name = "Cpt" },
                new ConePenetrationTestData { Name = "Cpt" }
            };
            using (var database = new SoilModelDatabase())
            {
                database.New(testFile);
                try
                {
                    exception = null;
                    database.WriteCPTs(cpts);
                }
                catch (Exception ex)
                {
                    exception = ex;
                }
                database.Close();
                Assert.IsInstanceOf(typeof(SQLiteException), exception);

                database.ErrorMessages.Clear();
                database.HandleExceptions = true;
                database.New(testFile);
                database.WriteCPTs(cpts);
                database.Close();
                Assert.IsTrue(database.ErrorMessages.Any(m => m.MessageType == LogMessageType.Error && m.Subject is ConePenetrationTestData && !m.Message.Contains("CP_Name")));
            }

            // constraint: no duplicate names for boringen
            var borings= new List<Boring>
            {
                new Boring { Name = "Boring" },
                new Boring { Name = "Boring" }
            };
            using (var database = new SoilModelDatabase())
            {
                database.New(testFile);
                try
                {
                    exception = null;
                    database.WriteBorings(borings);
                }
                catch (Exception ex)
                {
                    exception = ex;
                }
                database.Close();
                Assert.IsInstanceOf(typeof(SQLiteException), exception);

                database.ErrorMessages.Clear();
                database.HandleExceptions = true;
                database.New(testFile);
                database.WriteBorings(borings);
                database.Close();
                Assert.IsTrue(database.ErrorMessages.Any(m => m.MessageType == LogMessageType.Error && m.Subject is Boring && !m.Message.Contains("BO_Name")));
            }

            // constraint: no duplicate names for surfacelines
            var surfaceLines = new List<SurfaceLine2>
            {
                new SurfaceLine2 { Name = "SurfaceLine" },
                new SurfaceLine2 { Name = "SurfaceLine" }
            };
            using (var database = new SoilModelDatabase())
            {
                database.New(testFile);
                try
                {
                    exception = null;
                    database.WriteSurfaceLines(surfaceLines);
                }
                catch (Exception ex)
                {
                    exception = ex;
                }
                database.Close();
                Assert.IsInstanceOf(typeof(SQLiteException), exception);

                database.ErrorMessages.Clear();
                database.HandleExceptions = true;
                database.New(testFile);
                database.WriteSurfaceLines(surfaceLines);
                database.Close();
                Assert.IsTrue(database.ErrorMessages.Any(m => m.MessageType == LogMessageType.Error && m.Subject is SurfaceLine2 && !m.Message.Contains("SL_Name")));
            }

            // constraint: no duplicate names for stresscurves
            var stressCurves = new List<StressCurve>
            {
                new StressCurve { Name = "StressCurve" },
                new StressCurve { Name = "StressCurve" }
            };
            using (var database = new SoilModelDatabase())
            {
                database.New(testFile);
                try
                {
                    exception = null;
                    database.WriteStressCurves(stressCurves);
                }
                catch (Exception ex)
                {
                    exception = ex;
                }
                database.Close();
                Assert.IsInstanceOf(typeof(SQLiteException), exception);

                database.ErrorMessages.Clear();
                database.HandleExceptions = true;
                database.New(testFile);
                database.WriteStressCurves(stressCurves);
                database.Close();
                Assert.IsTrue(database.ErrorMessages.Any(m => m.MessageType == LogMessageType.Error && m.Subject is StressCurve && !m.Message.Contains("SC_Name")));
            }
            // constraint: no duplicate names for bondstresscurves
            var bondStressCurves = new List<BondStressCurve>
            {
                new BondStressCurve { Name = "BondStressCurve" },
                new BondStressCurve { Name = "BondStressCurve" }
            };
            using (var database = new SoilModelDatabase())
            {
                database.New(testFile);
                try
                {
                    exception = null;
                    database.WriteBondStressCurves(bondStressCurves);
                }
                catch (Exception ex)
                {
                    exception = ex;
                }
                database.Close();
                Assert.IsInstanceOf(typeof(SQLiteException), exception);

                database.ErrorMessages.Clear();
                database.HandleExceptions = true;
                database.New(testFile);
                database.WriteBondStressCurves(bondStressCurves);
                database.Close();
                Assert.IsTrue(database.ErrorMessages.Any(m => m.MessageType == LogMessageType.Error && m.Subject is BondStressCurve && !m.Message.Contains("SC_Name")));
            }
        }

        [Test]
        [Category(Categories.WorkInProgress)] // DSB-282, needs more work on tests for realistic (!) exception scenario's
        public void MiscellaniousExceptionLogging()
        {
            using (var db = new SoilModelDatabase { HandleExceptions = true })
            {
                db.New("Y:\\Invalidpath");
                var msg = db.ErrorMessages.First(m => m.MessageType == LogMessageType.Error);
                Assert.AreNotEqual("DBFileNotFoundError", msg);
                Assert.AreEqual(LocalizationManager.GetTranslatedText(typeof(SoilModelDatabase), "DBFileNotFoundError"), msg.Message);
            }
            using (var db = new SoilModelDatabase { HandleExceptions = true })
            {
                db.Open("Y:\\Invalidpath");
                var msg = db.ErrorMessages.First(m => m.MessageType == LogMessageType.Error);
                Assert.AreNotEqual("DBFileNotFoundError", msg);
                Assert.AreEqual(LocalizationManager.GetTranslatedText(typeof(SoilModelDatabase), "DBFileNotFoundError"), msg.Message);
            }
        }

        [Test]
        public void AreIsAquifersOldStyleConvertedToNewStyle()
        {
            var soildatabase = new SoilModelDatabase();
            soildatabase.Open(TestDataFolder + "Groot Salland.soil");
            var soilProfiles2D = soildatabase.ReadSoilProfiles2D();
            var soilProfiles1D = soildatabase.ReadSoilProfiles1D();
            soildatabase.Close();

            var testProfile2D = soilProfiles2D.First(p => p.Name == "10Y_005_STBI.sti");
            var hasAquifer = false;
            foreach (var surface in testProfile2D.Surfaces)
            {
                if (surface.IsAquifer)
                {
                    hasAquifer = true;
                    break;
                }
            }
            Assert.AreEqual(true, hasAquifer);

            var testProfile1D = soilProfiles1D.First(p => p.Name == "21225007");
            Assert.AreNotEqual(null, testProfile1D.BottomAquiferLayer);
        }

        [Test]
        public void CannotWriteToExistingFile()
        {
            // to write data, a new file must be created, Open() creates a read-only connection
            var soildatabase = new SoilModelDatabase();
            soildatabase.Open(TestDataFolder + "Groot Salland.soil");
            var soilList = soildatabase.ReadSoils();
            var soils = soilList.Soils;
            try
            {
                soildatabase.WriteSoils(soils);
                Assert.Fail("Writing to existing sqlite file should throw exception");
            }
            catch
            {
                ;
            }
            soildatabase.Close();
        }

        // test the conversion of probability for old (pre 15.x.x) files

        // test the default version >= 15.0.0.0 for new files, can be lower when opening older files
        [Test]
        public void CheckDefaultVersusCurrentFileVersion()
        {
            var database = new SoilModelDatabase();
            Assert.IsTrue(database.DataBaseVersion.CompareTo(new Version(1, 0)) > 0);

            database.Open(TestDataFolder + "Groot Salland.soil");
            Assert.IsTrue(database.DataBaseVersion.CompareTo(new Version(1, 0)) > 0);
            database.Close();
        }

        [Test]
        public void CheckForChangedPropertyNamesWithoutXmlOldNameAttribute()
        {
            // Soil and Layer properties are managed using reflection, vulnerable to renaming of properties
            CheckForChangedPropertyNamesWithoutXmlOldNameAttribute<Soil>();
            CheckForChangedPropertyNamesWithoutXmlOldNameAttribute<SoilLayer1D>();
            CheckForChangedPropertyNamesWithoutXmlOldNameAttribute<SoilLayer2D>();
        }

        [Test]
        public void CircularTest_GrootSalland()
        {
            ConvertFromGeoDatabaseCircularTest(TestDataFolder + "Groot Salland.mdb"); // bevat 1D & 2D profielen !
        }

        [Test]
        [Category(Categories.Slow)]
        public void CircularTest_Soil1200Materials()
        {
            var timer = new Stopwatch();
            timer.Restart();
            ConvertFromGeoDatabaseCircularTest(TestDataFolder + "soil1200materials.mdb", true); // bevat stochastische parameters
            timer.Stop();
            Assert.LessOrEqual(timer.Elapsed.Seconds, 65);
        }

        [Test]
        public void CircularTest_SoilMaterials()
        {
            ConvertFromGeoDatabaseCircularTest(TestDataFolder + "soilmaterials.mdb", true);
        }

        [Test]
        public void CompareTwoDefaultSoils()
        {
            // this is to test the object comparer, see if it hangs on a properties like "Owner"
            var soil1 = new Soil
                {
                    Name = "soil"
                };
            var soil2 = new Soil
                {
                    Name = "soil"
                };
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };
            
            var result = co.Compare(soil1, soil2);
            Assert.AreEqual(0, result.Differences.Count, "Soils fail comparison: " + result.DifferencesString);
        }

        [Test]
        public void StoreAndRestoreSegmentWithStochasticSoilModelCptAndBoring()
        {
            var profile1Ds = new List<SoilProfile1D>();
            var profile2Ds = new List<SoilProfile2D>();

            var soils = new SoilList();
            using (var geodatabase = new GeoDatabase(TestDataFolder + "Groot Salland.mdb"))
            {
                soils = geodatabase.ReadSoils(soils.Soils);
                profile1Ds.AddRange(geodatabase.ReadSoilProfiles(soils.Soils));
                profile2Ds.AddRange(geodatabase.ReadSoilProfiles2D(soils.Soils));
            }

            // create a SoilSegment with stochastic soil model
            var stoProfiles = new List<StochasticSoilProfile>();
            var stoModels = new List<StochasticSoilModel>();
            foreach (var profile in profile1Ds)
            {
                profile.ProbabilityOfOccurrence = 0; // obsolete
                var sp = new StochasticSoilProfile();
                sp.Profile = profile;
                sp.Probability = 0;
                stoProfiles.Add(sp);
            }

            foreach (var profile in profile2Ds)
            {
                var sp = new StochasticSoilProfile();
                sp.Profile = profile;
                sp.Probability = 0;
                stoProfiles.Add(sp);
            }

            double prob = 100d/stoProfiles.Count;
            foreach (var sp in stoProfiles)
            {
                sp.Probability = prob;
            }
            var stoModel = new StochasticSoilModel();
            stoModel.StochasticSoilProfiles = stoProfiles;
            stoModel.Name = "StochasticSoilModelTest";
            if (stoModel.StochasticSoilProfiles.Count > 0)
            {
                stoModels.Add(stoModel);
            }

            // add to a SoilSegment
            var segments = new List<SoilSegment>();
            var segment = new SoilSegment()
                {
                    Name = "TestSegment", Mechanism = Mechanism.None, StochasticSoilModel = stoModel
                };
            segment.Points.Add(new GeographicPoint(0, 0));
            segment.Points.Add(new GeographicPoint(1000, 0));
            segment.Points.Add(new GeographicPoint(1234.5, 2345.6));
            segments.Add(segment);

            // attach a CPT
            var importer = new GEFXmlImporter();
            var gefXml = TestDataFolder + "CPTDataSample.xml";
            Assert.IsTrue(File.Exists(gefXml));
            var input = new FileStream(gefXml, FileMode.Open, FileAccess.Read);
            var cpt = importer.ReadCPT(input);
            Assert.AreEqual(cpt.CPTDatarows.Count, 1523);

            segment.Cpts.Add(new ConePenetrationTestPerSegment { ConePenetrationTestData = cpt });

            // attach a boring
            gefXml = TestDataFolder + "demo-3-bore.xml";
            Assert.IsTrue(File.Exists(gefXml));
            input = new FileStream(gefXml, FileMode.Open, FileAccess.Read);
            var boring = importer.ReadBoring(input, soils);
            Assert.AreEqual(boring.BoringDatarows.Count, 9);

            segment.Borings.Add(new BoringPerSegment()
                {
                    Boring = boring
                });

            //write data
            var soildatabase = new SoilModelDatabase();
            soildatabase.New(TestOutputFolder + @"testdata.sqlite");
            soildatabase.WriteSoilProfile1Ds(profile1Ds);
            soildatabase.WriteSoilProfile2Ds(profile2Ds);
            soildatabase.WriteCPTs(new List<ConePenetrationTestData> { cpt });
            soildatabase.WriteBorings(new List<Boring> { boring });
            soildatabase.WriteSoilSegments(segments);
            soildatabase.WriteSoilSegmentCPTs(segments);
            soildatabase.WriteSoilSegmentBorings(segments);
            soildatabase.Close();

            // reopen and read back the data
            soildatabase.Open(TestOutputFolder + @"testdata.sqlite");
            var profile1Dscopy = soildatabase.ReadSoilProfiles1D();
            var profile2Dscopy = soildatabase.ReadSoilProfiles2D();
            var cptCopy = soildatabase.ReadCPTs();
            var boringCopy = soildatabase.ReadBorings(soils);
            var segmentsCopy = soildatabase.ReadSoilSegments();
            soildatabase.ReadSoilSegmentCPTs();
            soildatabase.ReadSoilSegmentBorings(soils);
            soildatabase.Close();

            //comparing original data
            CompareLogic co;
            ComparisonResult result;

            // check the soil profiles (1D)
            foreach (var profile in profile1Ds)
            {
                var profileCopy = profile1Dscopy.First(p => p.Name == profile.Name);
                co = new CompareLogic();
                co.Config.ShowBreadcrumb = false;
                co.Config.CompareStaticFields = false;
                co.Config.CompareStaticProperties = false;
                co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };
               
                result = co.Compare(profile, profileCopy);
                Assert.AreEqual(0, result.Differences.Count, "Profile1D fails comparison: " + result.DifferencesString);
            }

            // check the soil profiles (2D)
            foreach (var profile in profile2Ds)
            {
                var profileCopy = profile2Dscopy.First(p => p.Name == profile.Name);
                co = new CompareLogic();
                co.Config.ShowBreadcrumb = false;
                co.Config.CompareStaticFields = false;
                co.Config.CompareStaticProperties = false;
                co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };

                // note: geometry is regenerated from surfaces and loops, the original may have contained unused points that are safe to ignore
                co.Config.MembersToIgnore = new List<String> { "Geometry" };
                
                result = co.Compare(profile, profileCopy);
                Assert.AreEqual(0, result.Differences.Count, "Profile2D fails comparison: " + result.DifferencesString);
            }

            // check the SoilSegment 
            co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };
            co.Config.MembersToIgnore = new List<String> { "Profile"};
            
            result =  co.Compare(segments, segmentsCopy);
            Assert.AreEqual(0, result.Differences.Count, "Stochastic model SoilSegment fails comparison: " + result.DifferencesString);
        }

        /// <summary>
        /// Creates one Schematized surface line with 5 points, two characteristic points, a name, an origin and an angle,
        /// writes it to the database,  reads it out again and compares relevant properties.
        /// </summary>
        [Test]
        [TestCase(true)]
        [TestCase(false)]
        public void StoreAndRestoreSurfaceLines(bool geometryMustContainPoint)
        {
            var surfaceLines = new List<SurfaceLine2>();

            using (var surfaceLine1 = new SurfaceLine2
                {
                    CharacteristicPoints =
                        {
                            GeometryMustContainPoint = geometryMustContainPoint
                        },
                    Geometry = new LocalizedGeometryPointString(),
                    Name = "TestLine1"
                })
            {
                DataEventPublisher.IsDataEventPublishStopped = false;
                var geometry = new LocalizedGeometryPointString();
                geometry.Points.Add(new GeometryPoint(0, 0, 0));
                geometry.Points.Add(new GeometryPoint(0, 1, 1));
                geometry.Points.Add(new GeometryPoint(0, 2, 1.5));
                geometry.Points.Add(new GeometryPoint(0, 3, 1.5));
                geometry.Points.Add(new GeometryPoint(0, 4, 1.5));
                geometry.Points.Add(new GeometryPoint(0, 4, 1.5));
                surfaceLine1.Geometry = geometry;

                surfaceLine1.AddCharacteristicPoint(new GeometryPoint(0.0, 0.0, 0.0), CharacteristicPointType.DikeToeAtRiver);
                surfaceLine1.AddCharacteristicPoint(new GeometryPoint(0.0, 5.0, 1.5), CharacteristicPointType.DikeTopAtRiver);

                surfaceLine1.Geometry.OriginalXy = new GeographicPoint(28734.2342, 925234.3123);
                surfaceLine1.Geometry.LatitudeAngle = 92.3;

                surfaceLines.Add(surfaceLine1);

                var surfaceLineDB = new SoilModelDatabase();
                surfaceLineDB.New(TestOutputFolder + @"testSurfaceLine.sqlite");
                surfaceLineDB.WriteSurfaceLines(surfaceLines);
                surfaceLineDB.Close();

                surfaceLineDB.Open(TestOutputFolder + @"testSurfaceLine.sqlite");
                List<SurfaceLine2> surfaceLinesCopy = surfaceLineDB.ReadSurfaceLines();
                surfaceLineDB.Close();

                try
                {
                    // compare original to copy
                    Assert.AreEqual(surfaceLines.Count, surfaceLinesCopy.Count);
                    //We only have one line, so we can access it using First()
                    var surfaceLineCopy = surfaceLinesCopy.First();
                    Assert.AreEqual(surfaceLine1.Name, surfaceLineCopy.Name);
                    
                    Assert.AreEqual(surfaceLine1.Geometry.OriginalXy.X, surfaceLineCopy.Geometry.OriginalXy.X, 0.00001);
                    Assert.AreEqual(surfaceLine1.Geometry.OriginalXy.Y, surfaceLineCopy.Geometry.OriginalXy.Y, 0.00001);
                    Assert.AreEqual(surfaceLine1.Geometry.LatitudeAngle, surfaceLineCopy.Geometry.LatitudeAngle);

                    Assert.AreEqual(surfaceLine1.Geometry.Count, surfaceLineCopy.Geometry.Count);
                    for (int i = 0; i < surfaceLine1.Geometry.Count; i++)
                    {
                        Assert.AreEqual(surfaceLine1.Geometry.Points[i].X, surfaceLineCopy.Geometry.Points[i].X, 0.00001);
                        Assert.AreEqual(surfaceLine1.Geometry.Points[i].Y, surfaceLineCopy.Geometry.Points[i].Y, 0.00001);
                        Assert.AreEqual(surfaceLine1.Geometry.Points[i].Z, surfaceLineCopy.Geometry.Points[i].Z, 0.00001);
                    }

                    Assert.AreEqual(surfaceLine1.CharacteristicPoints.Count, surfaceLineCopy.CharacteristicPoints.Count);
                    
                    Assert.AreEqual(surfaceLine1.CharacteristicPoints.GeometryMustContainPoint, surfaceLineCopy.CharacteristicPoints.GeometryMustContainPoint);
                    
                    foreach (var type in surfaceLine1.CharacteristicPoints.Where(cp => cp.CharacteristicPointType != CharacteristicPointType.None)
                                                     .Select(cp => cp.CharacteristicPointType))
                    {
                        Assert.AreEqual(surfaceLine1.CharacteristicPoints.GetGeometryPoint(type).X, surfaceLineCopy.CharacteristicPoints.GetGeometryPoint(type).X, 0.00001);
                        Assert.AreEqual(surfaceLine1.CharacteristicPoints.GetGeometryPoint(type).Y, surfaceLineCopy.CharacteristicPoints.GetGeometryPoint(type).Y, 0.00001);
                    }
                }
                finally
                {
                    surfaceLinesCopy.ForEach(sl => sl.Dispose());
                    surfaceLine1.Dispose();
                }
            }
        }

        /// <summary>
        /// Creates a stressCurve and a bondStressCurve, attaches them to a new Soil, writes the soil to the database,
        /// reads it again and compares all properties of original and copied object
        /// </summary>
        [Test]
        public void StoreAndRestoreSoilWithStressCurveAndShearStrengthModel()
        {
            var stressTable = new StressCurve { Name = "StressTest" };
            stressTable.SigmaTaus.Add(new SigmaTau { Sigma = 0, Tau = 0 });
            stressTable.SigmaTaus.Add(new SigmaTau { Sigma = 1, Tau = 1, TauCharacteristic = 1, TauMean = 1});
            stressTable.SigmaTaus.Add(new SigmaTau { Sigma = 1.11, Tau = 1.22, TauCharacteristic =  1.11, TauMean = 1.115 });

            var bondStressTable = new BondStressCurve { Name = "BondStressTest" };
            bondStressTable.BondStresses.Add(new BondStress { Sigma = 0, Tau = 0 });
            bondStressTable.BondStresses.Add(new BondStress { Sigma = 1, Tau = 1 });
            bondStressTable.BondStresses.Add(new BondStress { Sigma = 1.33, Tau = 1.44 });

            var soils = new List<Soil>();
            var soil = new Soil
                {
                    Name = "WithStressTable",
                    StressTable = stressTable,
                    BondStressTable = bondStressTable,
                    ShearStrengthModel = ShearStrengthModel.StressTable,
                    UseDefaultShearStrengthModel = false
                };
            soils.Add(soil);

            // DSB-83 testing suGradient as stressmodel 
            var soil2 = new Soil
                {
                    Name = "WithCuGradient",
                    CuBottom = 0.5,
                    CuTop = 1.0,
                    CuGradient = 0.25,
                    ShearStrengthModel = ShearStrengthModel.CuGradient,
                    UseDefaultShearStrengthModel = false
                };
            soils.Add(soil2);

            var soildatabase = new SoilModelDatabase();
            soildatabase.New(TestOutputFolder + @"testSoilOnly.soil");
            soildatabase.WriteSoils(soils);
            soildatabase.Close();

            soildatabase.Open(TestOutputFolder + @"testSoilOnly.soil");
            var soiList = soildatabase.ReadSoils();
            var soilsCopy = soiList.Soils;
            soildatabase.Close();

            // check the soil properties against the original ones
            Assert.AreEqual(soils.Count, soilsCopy.Count);
            soils.Sort();
            soilsCopy.Sort();
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };
            
            var result = co.Compare(soils, soilsCopy);
            Assert.AreEqual(0, result.Differences.Count, "Soils fail comparison: " + result.DifferencesString);
        }

        /// <summary>
        /// Test that stress curves not referenced from any Soil or other entity can be written and read separately.
        /// </summary>
        [Test]
        public void StoreAndrestoreUnusedStressCurves()
        {
            const string testFile = TestOutputFolder + "StressCurves.soil";
            
            // setup
            var stressCurves = new List<StressCurve>();
            var stressCurve = new StressCurve { Name = "StressTable1" };
            stressCurve.SigmaTaus.Add(new SigmaTau { Sigma = 0, Tau = 0, TauCharacteristic = 0, TauMean = 0 });
            stressCurve.SigmaTaus.Add(new SigmaTau { Sigma = 1, Tau = 1, TauCharacteristic = 1, TauMean = 1 });
            stressCurve.SigmaTaus.Add(new SigmaTau { Sigma = 1.11, Tau = 1.22, TauCharacteristic = 1.11, TauMean = 1.16});
            stressCurves.Add(stressCurve);

            stressCurve = new StressCurve { Name = "StressTable2" };
            stressCurve.SigmaTaus.Add(new SigmaTau { Sigma = 0, Tau = 0, TauCharacteristic = 0, TauMean = 0 });
            stressCurve.SigmaTaus.Add(new SigmaTau { Sigma = 0.5, Tau = 0.5, TauCharacteristic = 0.5, TauMean = 0.5 });
            stressCurve.SigmaTaus.Add(new SigmaTau { Sigma = 1.0, Tau = 1.1, TauCharacteristic = 1.0, TauMean = 1.05 });
            stressCurves.Add(stressCurve);
            
            // store
            var database = new SoilModelDatabase();
            database.New(testFile);
            database.WriteStressCurves(stressCurves);
            database.Close();

            // restore
            database.Open(testFile);
            var stressCurvesCopy = database.ReadStressCurves(); 
            database.Close();
            
            // assert
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };

            var result = co.Compare(stressCurves, stressCurvesCopy);
            Assert.AreEqual(0, result.Differences.Count, "StressCurves fail comparison: " + result.DifferencesString);

        }

        /// <summary>
        /// Test that stress curves not referenced from any Soil or other entity can be written and read separately.
        /// </summary>
        [Test]
        public void StoreAndrestoreUnusedBondStressCurves()
        {
            const string testFile = TestOutputFolder + "BondStressCurves.soil";

            // setup
            var bondStressCurves = new List<BondStressCurve>();
            var bondStressCurve = new BondStressCurve { Name = "BondStressTable1" };
            bondStressCurve.BondStresses.Add(new BondStress { Sigma = 0, Tau = 0 });
            bondStressCurve.BondStresses.Add(new BondStress { Sigma = 2, Tau = 2 });
            bondStressCurve.BondStresses.Add(new BondStress { Sigma = 2.22, Tau = 2.44 });
            bondStressCurves.Add(bondStressCurve);

            bondStressCurve = new BondStressCurve { Name = "BondStressTable2" };
            bondStressCurve.BondStresses.Add(new BondStress { Sigma = 0, Tau = 0 });
            bondStressCurve.BondStresses.Add(new BondStress { Sigma = 1, Tau = 1 });
            bondStressCurve.BondStresses.Add(new BondStress { Sigma = 1.22, Tau = 1.11 });
            bondStressCurves.Add(bondStressCurve);

            // store
            var database = new SoilModelDatabase();
            database.New(testFile);
            database.WriteBondStressCurves(bondStressCurves);
            database.Close();

            // restore
            database.Open(testFile);
            var stressCurvesCopy = database.ReadBondStressCurves();
            database.Close();

            // assert
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };
           
            var result = co.Compare(bondStressCurves, stressCurvesCopy);
            Assert.AreEqual(0, result.Differences.Count, "StressCurves fail comparison: " + result.DifferencesString);

        }
        
        [Test]
        [Category(Categories.Slow)] // over 10 seconds to read + write 1200 soils...
        [Category(Categories.Performance)] 
        public void StoreAndRestorePerformanceSqlite()
        {
            var stopped = DataEventPublisher.IsDataEventPublishStopped;
            DataEventPublisher.IsDataEventPublishStopped = true;

            var timer = new Stopwatch();
            timer.Restart();

            var soildatabase = new SoilModelDatabase();
            soildatabase.Open(TestDataFolder + "soil1200materials.soil");
            var soilList = soildatabase.ReadSoils();
            var soils = soilList.Soils;
            soildatabase.Close();

            soildatabase = new SoilModelDatabase();
            soildatabase.New(TestOutputFolder + "soil1200materials.sqlite");
            soildatabase.WriteSoils(soils);
            soildatabase.Close();
            timer.Stop();

            DataEventPublisher.IsDataEventPublishStopped = stopped;

            Assert.LessOrEqual(timer.Elapsed.Seconds, 45);
        }

        [Test]
        public void ReadingStochasticSoilParametersSetOwnerProperty()
        {
            // background:
            // the Stochast.Owner property is used to link a stochast to it's design value in the UI
            // to avoid losing the Owner property all stochasts on soil have private setters and Stochast.Assign is provided to copy stochast properties instead
            // this test makes sure the database implementation uses Stochast.Assign properly and the Owner properties are not lost
            var database = new SoilModelDatabase();
            database.Open(TestDataFolder + "SoilMaterials.Soil");
            var soils = database.ReadSoils().Soils;
            database.Close();
            foreach (var soil in soils)
            {
                foreach (var item in soil.GetStochasticParameters())
                {
                    Assert.AreSame(item.Value.Owner, soil);
                }
            }
        }

        [Test]
        public void StorageOf1DProfileBottom()
        {
            // this is to demonstrate the bottom layer/bottom level canot be stored as such !
            var soils = new List<Soil>();
            var soilProfiles = new List<SoilProfile1D>();
            var soil = new Soil
                {
                    SoilType = SoilType.Clay, Name = "Klei"
                };
            soils.Add(soil);
            var soilProfile = new SoilProfile1D
                {
                    Name = "SimpleProfile1D"
                };
            soilProfile.Layers.Add(new SoilLayer1D
                {
                    Soil = soil, TopLevel = 0
                });
            soil = new Soil
                {
                    SoilType = SoilType.Sand, Name = "Zand"
                };
            soils.Add(soil);
            soilProfile.Layers.Add(new SoilLayer1D
                {
                    Soil = soil, TopLevel = -2
                });
            soilProfile.Location = new GeographicPoint(0, 0);
            soilProfile.BottomLevel = -24;
            soilProfiles.Add(soilProfile);

            Assert.AreEqual(-24, soilProfile.BottomLevel);
            Assert.AreEqual(-24, soilProfile.Layers[1].BottomLevel);

            // store
            var database = new SoilModelDatabase();
            database.New(TestOutputFolder + "TestProfile1DBottom.soil");
            database.WriteSoils(soils);
            database.WriteSoilProfile1Ds(soilProfiles);
            database.Close();

            // restore
            database = new SoilModelDatabase();
            database.Open(TestOutputFolder + "TestProfile1DBottom.soil");
            var soilList = database.ReadSoils();
            var copySoils = soilList.Soils;
            var copyProfiles = database.ReadSoilProfiles1D();
            database.Close();

            // check bottom level
            Assert.AreEqual(soilProfiles.Count, copyProfiles.Count);
            Assert.AreEqual(soilProfiles[0].LayerCount, copyProfiles[0].LayerCount);
            Assert.AreEqual(soilProfiles[0].BottomLevel, copyProfiles[0].BottomLevel, "failed to restore SoilProfile1D.BottomLevel from DB !");

            // compare complete objects
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };
            
            //co.Config.MembersToIgnore = new List<String> { "Id", "SoilProfile", "Owner", "Parent", "SetCoordinate", "StochastProperties" };
            
            var result = co.Compare(soils, copySoils);
            Assert.AreEqual(0, result.Differences.Count, "Soils fail comparison: " + result.DifferencesString);

            // note: layer name and Id are irrelevant, should be removed ?
            co.Config.MembersToIgnore = new List<String> { "Name", "Id" };
            result = co.Compare(soilProfiles, copyProfiles);
            Assert.AreEqual(0, result.Differences.Count, "SoilProfile1D fails comparison: " + result.DifferencesString);
        }

        [Test]
        public void StoreAndRestoreSoilProfile1DLookup2Ds()
        {
            var soilDatabase = new SoilModelDatabase();
            soilDatabase.Open(TestDataFolder + "SoilSegments.soil");

            var soils = soilDatabase.ReadSoils();
            var soilProfile1Ds = soilDatabase.ReadSoilProfiles1D();
            var soilProfile2Ds = soilDatabase.ReadSoilProfiles2D();
            soilDatabase.Close();

            var lookups = new List<SoilProfile1DLookup2D>();

            // the example file contains 1D/2D profiles which related (piping vs. stability), link them by name
            foreach (var soilProfile1D in soilProfile1Ds)
            {
                var soilProfile2D = soilProfile2Ds.FirstOrDefault(sp2D => soilProfile1D.Name == sp2D.Name + "_p");
                if (soilProfile2D != null)
                {
                    var lookup = new SoilProfile1DLookup2D()
                    {
                        SoilProfile1D = soilProfile1D,
                        SoilProfile2D = soilProfile2D,
                        Xlocal = 1.23
                    };
                    lookups.Add(lookup);
                }
            }

            // store
            const string testFile = TestOutputFolder + "TestSoilprofile1DLookup2D.soil";
            soilDatabase.New(testFile);
            soilDatabase.WriteSoils(soils.Soils);
            soilDatabase.WriteSoilProfile1Ds(soilProfile1Ds);
            soilDatabase.WriteSoilProfile2Ds(soilProfile2Ds);
            soilDatabase.WriteSoilProfile1DLookup2Ds(lookups);
            soilDatabase.Close();

            // restore
            soilDatabase.Open(testFile);
            var testProfile1Ds = soilDatabase.ReadSoilProfiles1D();
            var testProfile2Ds = soilDatabase.ReadSoilProfiles2D();
            var testLookups = soilDatabase.ReadSoilProfile1DLookup2Ds();
            soilDatabase.Close();

            // assert
            Assert.AreEqual(31, lookups.Count);
            Assert.AreEqual(lookups.Count, testLookups.Count);
            foreach (var lookup in testLookups)
            {
                Assert.AreEqual(lookup.SoilProfile1D.Name, lookup.SoilProfile2D.Name + "_p");
                Assert.AreEqual(1.23, lookup.Xlocal);
            }
        }

        [Test]
        public void StoreAndRestoreMechanismPointLocation()
        {
            var soilDatabase = new SoilModelDatabase();
            soilDatabase.Open(TestDataFolder + "Groot Salland.soil");
            var soilList = soilDatabase.ReadSoils();
            var soils = soilList.Soils;
            var soilProfiles2D = soilDatabase.ReadSoilProfiles2D();
            soilDatabase.Close();

            var testProfile2D = soilProfiles2D.First(p => p.Name == "10Y_005_STBI.sti");

            var pointLocations = new List<SpecificMechanismPointLocation>();
            pointLocations.Add(new SpecificMechanismPointLocation
                {
                    SoilProfile2D = testProfile2D, Mechanism = Mechanism.Piping, XCoordinate = 2
                });
            pointLocations.Add(new SpecificMechanismPointLocation
                {
                    SoilProfile2D = testProfile2D, Mechanism = Mechanism.FlowSlide, XCoordinate = 3
                });

            // store
            soilDatabase = new SoilModelDatabase();
            soilDatabase.New(TestOutputFolder + "MechanismPointLocation.soil");
            soilDatabase.WriteSoils(soils);
            soilDatabase.WriteSoilProfile2Ds(soilProfiles2D);
            soilDatabase.WriteMechanismPointLocations(pointLocations);
            soilDatabase.Close();

            // restore
            soilDatabase = new SoilModelDatabase();
            soilDatabase.Open(TestOutputFolder + "MechanismPointLocation.soil");
            var soilList1 = soilDatabase.ReadSoils();
            soils = soilList1.Soils;
            soilProfiles2D = soilDatabase.ReadSoilProfiles2D();
            var copyLocations = soilDatabase.ReadMechanismPointLocations();
            soilDatabase.Close();

            // compare with original
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };
            
            var result = co.Compare(pointLocations, copyLocations);
            Assert.AreEqual(0, result.Differences.Count, "SpecifiMechanismPointLocations fail comparison: " + result.DifferencesString);
        }

        [Test]
        public void StoreAndRestorePreconsolidationStresses()
        {
            var soildatabase = new SoilModelDatabase();
            soildatabase.Open(TestDataFolder + "Groot Salland.soil");
            var soilList = soildatabase.ReadSoils();
            var soils = soilList.Soils;
            var soilProfiles2D = soildatabase.ReadSoilProfiles2D();
            var soilProfiles1D = soildatabase.ReadSoilProfiles1D();
            soildatabase.Close();

            var testProfile2D = soilProfiles2D.First(p => p.Name == "10Y_005_STBI.sti");
            testProfile2D.PreconsolidationStresses.Add(new PreConsolidationStress
                {
                    X = 0, Z = -6, StressValue = 12345.6,
                    StressStochast = { DistributionType = DistributionType.Deterministic }
                });
            testProfile2D.PreconsolidationStresses.Add(new PreConsolidationStress
                {
                    X = 10, Z = -7, StressValue = 23456.7,
                    StressStochast = { DistributionType = DistributionType.LogNormal, DeviationType = StochastDeviationType.Relative, Deviation = 0.00001 }
                });
            testProfile2D.PreconsolidationStresses.Add(new PreConsolidationStress
                {
                    X = 50, Z = -5, StressValue = 55555.5,
                    StressStochast = { DistributionType = DistributionType.Normal, DeviationType = StochastDeviationType.Absolute, Deviation = 0.5 }
                });

            var testProfile1D = soilProfiles1D.First(p => p.Name == "21225007");
            testProfile1D.PreconsolidationStresses.Add(new PreConsolidationStress
                {
                    Z = -6, StressValue = 6666.6,
                    StressStochast = { DistributionType = DistributionType.Uniform, DeviationType = StochastDeviationType.Relative , Deviation = 0.6 }
                });
            testProfile1D.PreconsolidationStresses.Add(new PreConsolidationStress
                {
                    Z = -7, StressValue = 7777.7,
                    StressStochast = { DistributionType = DistributionType.Exponential, DeviationType = StochastDeviationType.Absolute, Deviation = 7.7 }
                });
            testProfile1D.PreconsolidationStresses.Add(new PreConsolidationStress
                {
                    Z = -5, StressValue = 5555.5,
                    StressStochast = { DistributionType = DistributionType.Beta }
                });

            // store
            soildatabase = new SoilModelDatabase();
            soildatabase.New(TestOutputFolder + "preconstresstest.soil");
            soildatabase.WriteSoils(soils);
            soildatabase.WriteSoilProfile2Ds(soilProfiles2D);
            soildatabase.WriteSoilProfile1Ds(soilProfiles1D);
            soildatabase.Close();

            // restore
            soildatabase = new SoilModelDatabase();
            soildatabase.Open(TestOutputFolder + "preconstresstest.soil");
            soilProfiles2D = soildatabase.ReadSoilProfiles2D();
            soilProfiles1D = soildatabase.ReadSoilProfiles1D();
            soildatabase.Close();

            var copyProfile2D = soilProfiles2D.First(p => p.Name == "10Y_005_STBI.sti");
            var copyProfile1D = soilProfiles1D.First(p => p.Name == "21225007");

            // compare with original
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };

            var result = co.Compare(testProfile2D.PreconsolidationStresses, copyProfile2D.PreconsolidationStresses);
            Assert.AreEqual(0, result.Differences.Count, "Preconsolidation stresses fail comparison: " + result.DifferencesString);
            result = co.Compare(testProfile1D.PreconsolidationStresses, copyProfile1D.PreconsolidationStresses);
            Assert.AreEqual(0, result.Differences.Count, "Preconsolidation stresses fail comparison: " + result.DifferencesString);
        }

        [Test]
        [Category(Categories.Slow)]
        public void StoreAndRestoreSoilProfilesOnly()
        {
            // get test data
            var soildatabase = new SoilModelDatabase();
            soildatabase.Open(TestDataFolder + "Groot Salland.soil");
            var soilProfiles1D = soildatabase.ReadSoilProfiles1D();
            var soilProfiles2D = soildatabase.ReadSoilProfiles2D();
            soildatabase.Close();

            // write the soil profiles > without first writing the soils used
            soildatabase = new SoilModelDatabase();
            soildatabase.New(TestOutputFolder + "profilesonly.soil");
            soildatabase.WriteSoilProfile1Ds(soilProfiles1D);
            soildatabase.WriteSoilProfile2Ds(soilProfiles2D);
            soildatabase.Close();

            // read back 
            soildatabase = new SoilModelDatabase();
            soildatabase.Open(TestOutputFolder + "profilesonly.soil");
            var copyProfiles1D = soildatabase.ReadSoilProfiles1D();
            var copyProfiles2D = soildatabase.ReadSoilProfiles2D();
            var copySoils = soildatabase.ReadSoils();
            soildatabase.Close();

            Assert.AreEqual(23, copySoils.Soils.Count);
            Assert.AreEqual(5, copyProfiles1D.Count);
            Assert.AreEqual(1, copyProfiles2D.Count);

            // compare complete objects
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };
            
            var result = co.Compare(soilProfiles1D, copyProfiles1D);
            Assert.AreEqual(0, result.Differences.Count, "SoilProfile1Ds fail comparison: " + result.DifferencesString);
            result = co.Compare(soilProfiles2D, copyProfiles2D);
            Assert.AreEqual(0, result.Differences.Count, "SoilProfile2Ds fail comparison: " + result.DifferencesString);
        }

        [Test]
        [Category(Categories.Slow)]
        public void StoreAndRestoreSoilSegmentsOnly()
        {
            // get test data
            var soildatabase = new SoilModelDatabase();
            soildatabase.Open(TestDataFolder + "SoilSegments.soil");
            var soilSegments = soildatabase.ReadSoilSegments();
            soildatabase.Close();

            // write the soil segments > without first writing the soilprofiles & soils used
            soildatabase = new SoilModelDatabase();
            soildatabase.New(TestOutputFolder + "SegmentsOnly.soil");
            soildatabase.WriteSoilSegments(soilSegments);
            soildatabase.Close();

            // read back 
            soildatabase = new SoilModelDatabase();
            soildatabase.Open(TestOutputFolder + "SegmentsOnly.soil");
            var copySegments = soildatabase.ReadSoilSegments();
            var copyProfile1Ds = soildatabase.ReadSoilProfiles1D();
            var copyProfile2Ds = soildatabase.ReadSoilProfiles2D();
            var copySoils = soildatabase.ReadSoils();
            soildatabase.Close();

            Assert.AreEqual(60, copySoils.Soils.Count);
            Assert.AreEqual(34, copyProfile1Ds.Count);
            Assert.AreEqual(33, copyProfile2Ds.Count);
            Assert.AreEqual(64, copySegments.Count);

            // compare complete objects
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = true;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };

            var result = co.Compare(soilSegments, copySegments);
            Assert.AreEqual(0, result.Differences.Count, "SoilSegments fail comparison: " + result.DifferencesString);
        }

        [Test]
        public void SupportForCustomSoilParameterXmlOldNameAttribute()
        {
            // test file contains soil parameter "ColorHtml" which was renamed to "Color" in the past
            var database = new SoilModelDatabase();
            database.Open((TestDataFolder + "XmlOldNameExample.Soil"));

            var soil = new Soil();
            var defaultColor = soil.Color;

            // assert
            var soils = database.ReadSoils().Soils;
            Assert.Greater(soils.Count(s => s.Color != defaultColor), 0);

            database.Close();
        }

        [Test]
        public void SupportForSoilLayerParameterXmlOldNameAttribute()
        {
            // dummy profile with custom layer, old property name
            var oldProfile = new SoilProfile1D
                {
                    Name = "Test"
                };
            var oldLayer = new OldCustomSoilLayer1D
                {
                    SoilProfile = oldProfile,
                    BottomLevel = -10,
                    TopLevel = 0,
                    IsAquifer = false,
                    OriginalCustomProperty = 1.23456
                };

            oldProfile.Layers.Add(oldLayer);

            // store & restore to new layertype
            const string testFile = TestOutputFolder + "TestCustomLayerType.soil";
            var database = new SoilModelDatabase();
            database.New(testFile);
            database.WriteSoilProfile1Ds(new List<SoilProfile1D>
                {
                    oldProfile
                });
            database.Close();

            // read back to new layer type
            database.Open(testFile);
            var newSoilProfiles = database.ReadSoilProfiles1D<NewCustomSoilLayer1D>();
            database.Close();

            // assert
            Assert.AreEqual(1, newSoilProfiles.Count);
            Assert.AreEqual(1, newSoilProfiles.First().Layers.Count);
            var newLayer = newSoilProfiles.First().Layers.First() as NewCustomSoilLayer1D;
            Assert.IsNotNull(newLayer);
            Assert.AreEqual(1.23456, newLayer.RenamedCustomProperty);
        }

        [Test]
        public void TestAdditionsInVersion_15_0_5_0()
        {
            var soildatabase = new SoilModelDatabase();
            soildatabase.Open(TestDataFolder + "Groot Salland.soil");
            var soilProfiles2D = soildatabase.ReadSoilProfiles2D();

            // CenterCrestLocation was added in version 15.0.5.0
            soilProfiles2D[0].CenterCrestLocation = 40;

            // changeLog was added in version 15.0.4.0
            soilProfiles2D[0].ChangeLog = "Added CenterCrestLocation";

            var database = new SoilModelDatabase();
            Assert.IsTrue(database.DataBaseVersion.CompareTo(new Version(15, 0, 5, 0)) >= 0);

            var fileName = TestOutputFolder + "TestAdditionsInVersion_15_0_5_0.soil";
            database.New(fileName);
            database.WriteSoilProfile2Ds(soilProfiles2D);
            database.Close();

            // restore
            database = new SoilModelDatabase();
            database.Open(fileName);

            var newSoilProfiles2D = database.ReadSoilProfiles2D();
            database.Close();

            Assert.AreEqual(40, newSoilProfiles2D[0].CenterCrestLocation, 0.001);
            Assert.AreEqual("Added CenterCrestLocation", newSoilProfiles2D[0].ChangeLog);
        }
        
        [Test]
        public void TestAdditionsInVersion_15_0_0_0()
        {
            var database = new SoilModelDatabase();
            Assert.IsTrue(database.DataBaseVersion.CompareTo(new Version(15, 0, 0, 0)) >= 0);

            database.Open(TestDataFolder + "SoilSegments_version_1_3_3.soil");
            Assert.IsTrue(database.DataBaseVersion.CompareTo(new Version(15, 0, 0, 0)) < 0);

            var soilModels = database.ReadStochasticSoilModels();
            
            // two stochastic test models were included here for dwp00, file contains percentages which are now converted to a fraction [-]
            foreach (var soilModel in soilModels.Where(sm => sm.StochasticSoilProfiles.Count > 1))
            {
                Assert.AreEqual(1.0, soilModel.StochasticSoilProfiles.Sum(sp => sp.Probability), 0.001);
            }
        }

        // test the new stochast fields added for version 1.3.3.0
        [Test]
        public void TestAdditionsInVersion_1_3_3_0()
        {
            var database = new SoilModelDatabase();
            Assert.IsTrue(database.DataBaseVersion.CompareTo(new Version(1, 3, 3, 0)) >= 0);

            var soil = new Soil();
            soil.CohesionStochast.DesignFactor = 1.11;
            soil.CohesionStochast.DesignDeviation = 0.11;
            soil.CohesionStochast.Shape = 0.12345;
            soil.CohesionStochast.ShapeB = 0.54321;
            soil.CohesionStochast.ActualValue = 9.9999;
            soil.CohesionStochast.CorrelationLength = 123;
            soil.CohesionStochast.CorrelationPeriod = 321;

            var soils = new List<Soil>();
            soils.Add((soil));

            database.New(TestOutputFolder + "TestAdditionsInVersion_1_3_3_0.soil");
            database.WriteSoils(soils);
            database.Close();

            // restore
            database = new SoilModelDatabase();
            database.Open(TestOutputFolder + "TestAdditionsInVersion_1_3_3_0.soil");
            var soilList = database.ReadSoils();
            var copySoils = soilList.Soils;
            database.Close();

            Assert.AreEqual(1, copySoils.Count);
            Assert.IsTrue(IsStochastEqual(soil.CohesionStochast, copySoils[0].CohesionStochast));
        }

        [Test]
        public void StoreAndRestoreCptAndBoringLookups1D()
        {
            var importer = new GEFXmlImporter();
            var gefXml = TestDataFolder + "CPTDataSample.xml";
            Assert.IsTrue(File.Exists(gefXml));
            var input = new FileStream(gefXml, FileMode.Open, FileAccess.Read);

            // read the CPT (never more then one per file !)
            var cpt = importer.ReadCPT(input);
            Assert.AreEqual(cpt.CPTDatarows.Count, 1523);

            // Boring from a separate file
            gefXml = TestDataFolder + "demo-3-bore.xml";
            Assert.IsTrue(File.Exists(gefXml));
            input = new FileStream(gefXml, FileMode.Open, FileAccess.Read);
            var boring = importer.ReadBoring(input, new SoilList());
            Assert.AreEqual(boring.BoringDatarows.Count, 9);

            // write the CPT, Boring & Profile record to DB
            var soildatabase = new SoilModelDatabase();
            soildatabase.New(TestOutputFolder + @"testGEF.sqlite");
            soildatabase.WriteCPTs(new List<ConePenetrationTestData> { cpt });
            soildatabase.WriteBorings(new List<Boring> { boring });
            soildatabase.Close();

            // read back from the DB
            soildatabase.Open(TestOutputFolder + @"testGEF.sqlite");
            var cptCopy = soildatabase.ReadCPTs().First();
            var boringCopy = soildatabase.ReadBorings(new SoilList()).First();
            soildatabase.Close();

            // compare with the original objects
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };
            var result = co.Compare(cpt, cptCopy);
            Assert.AreEqual(0, result.Differences.Count, "CPT fails comparison: " + result.DifferencesString);

            co.Compare(boring, boringCopy);
            Assert.AreEqual(0, result.Differences.Count, "Boring fails comparison: " + result.DifferencesString);
        }

        [Test]
        public void StoreAndRestoreCptAndBoringLookups2D()
        {
            SoilProfile1D profile1D;
            SoilProfile2D profile2D;
            var soils = new SoilList();
            // get a 2D profile
            using (var geodatabase = new GeoDatabase(TestDataFolder + "Groot Salland.mdb"))
            {
                // bevat 1D & 2D profielen !
                soils = geodatabase.ReadSoils(soils.Soils);
                profile1D = geodatabase.ReadSoilProfiles(soils.Soils).First();
                profile2D = geodatabase.ReadSoilProfiles2D(soils.Soils).First();
            }
            // get a CPT
            var importer = new GEFXmlImporter();
            var input = new FileStream(TestDataFolder + "CPTDataSample.xml", FileMode.Open, FileAccess.Read);
            var cpt = importer.ReadCPT(input);

            // Get a Boring
            input = new FileStream(TestDataFolder + "demo-3-bore.xml", FileMode.Open, FileAccess.Read);
            var boring = importer.ReadBoring(input, soils);

            // create some lookups
            cpt.Name = "cpt1";
            var cpt2 = (ConePenetrationTestData) cpt.Clone();
            cpt2.Name = "cpt2";

            var cpl1D = new ConePenetrationTestLookup1D
                {
                    ConePenetrationTestData = cpt, SoilProfile1D = profile1D
                };

            var cpl2D1 = new ConePenetrationTestLookup2D
                {
                    ConePenetrationTestData = cpt, SoilProfile2D = profile2D, Xlocal = 3.21, Zlocal = -0.101
                };
            var cpl2D2 = new ConePenetrationTestLookup2D
                {
                    ConePenetrationTestData = cpt2, SoilProfile2D = profile2D, Xlocal = 1.23, Zlocal = -0.001
                };

            boring.Name = "boring1";
            var boring2 = (Boring) boring.Clone();
            boring2.Name = "boring2";

            var boringl1D = new BoringLookup1D
                {
                    Boring = boring, SoilProfile1D = profile1D
                };
            var boringl2D1 = new BoringLookup2D
                {
                    Boring = boring, SoilProfile2D = profile2D, Xlocal = 3.21, Zlocal = -0.101
                };
            var boringl2D2 = new BoringLookup2D
                {
                    Boring = boring2, SoilProfile2D = profile2D, Xlocal = 1.23, Zlocal = -0.001
                };

            // write the profile to DB
            var soildatabase = new SoilModelDatabase();
            soildatabase.New(TestOutputFolder + @"testLookups.sqlite");
            soildatabase.WriteSoilProfile1Ds(new List<SoilProfile1D> { profile1D });
            soildatabase.WriteSoilProfile2Ds(new List<SoilProfile2D> { profile2D });
            soildatabase.WriteCPTs(new List<ConePenetrationTestData> { cpt, cpt2 });
            soildatabase.WriteBorings(new List<Boring> { boring, boring2 });
            soildatabase.WriteCPTLookup1Ds(new List<ConePenetrationTestLookup1D> { cpl1D });
            soildatabase.WriteCPTLookup2Ds(new List<ConePenetrationTestLookup2D> { cpl2D1, cpl2D2 });
            soildatabase.WriteBoringLookup1Ds(new List<BoringLookup1D> { boringl1D });
            soildatabase.WriteBoringLookup2Ds(new List<BoringLookup2D> { boringl2D1, boringl2D2 });
            soildatabase.Close();

            // read back from the DB
            soildatabase.Open(TestOutputFolder + @"testLookups.sqlite");
            var cptCopy = soildatabase.ReadCPTs();
            var boringCopy = soildatabase.ReadBorings(soils);
            var profile1DCopy = soildatabase.ReadSoilProfiles1D();
            var profile2DCopy = soildatabase.ReadSoilProfiles2D();
            var cpt1DCopy = soildatabase.ReadCPTLookup1Ds();
            var cpt2DsCopy = soildatabase.ReadCPTLookup2Ds();
            var boringl1DCopy = soildatabase.ReadBoringLookup1Ds(soils);
            var boringl2DsCopy = soildatabase.ReadBoringLookup2Ds(soils);
            soildatabase.Close();

            // compare with originals
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };
            co.Config.MembersToIgnore = new List<String> { "ProbabilityOfOccurrence" };

           var result = co.Compare(cpt1DCopy.First(), cpl1D);
            Assert.AreEqual(0, result.Differences.Count, "CPTLookup1D fails comparison: " + result.DifferencesString);

            result = co.Compare(boringl1DCopy.First(), boringl1D);
            Assert.AreEqual(0, result.Differences.Count, "BoringLook1D fails comparison: " + result.DifferencesString);

            co.Config.MembersToIgnore = new List<String> { "Geometry" };

            result = co.Compare(cpt2DsCopy[0], cpl2D1);
            Assert.AreEqual(0, result.Differences.Count, "CPTLookup2D fails comparison: " + result.DifferencesString);

            result = co.Compare(cpt2DsCopy[1], cpl2D2);
            Assert.AreEqual(0, result.Differences.Count, "CPTLookup2D fails comparison: " + result.DifferencesString);

            result = co.Compare(boringl2DsCopy[0], boringl2D1);
            Assert.AreEqual(0, result.Differences.Count, "BoringLookup2D fails comparison: " + result.DifferencesString);

            result = co.Compare(boringl2DsCopy[1], boringl2D2);
            Assert.AreEqual(0, result.Differences.Count, "BoringLookup2D fails comparison: " + result.DifferencesString);
        }

        [Test]
        public void StoreAndRestoreMetaData()
        {
            var database = new SoilModelDatabase();
            database.New(TestOutputFolder + "TestMetadata.soil");
            database.MetaData.Add("FirstKey", "FirstValue");
            database.MetaData.Add("SecondKey", "SecondValue");
            database.WriteMetaData();
            database.Close();

            database = new SoilModelDatabase();
            database.Open(TestOutputFolder + "TestMetadata.soil");
            Assert.AreEqual(2, database.MetaData.Count);
            Assert.AreEqual("FirstValue", database.MetaData["FirstKey"]);
            Assert.AreEqual("SecondValue", database.MetaData["SecondKey"]);
            database.Close();
        }

        private void CheckForChangedPropertyNamesWithoutXmlOldNameAttribute<T>()
        {
            var xmlFile = TestDataFolder + typeof(T).Name + "Properties.xml";
            var serializer = new XmlSerializer(typeof(List<String>));
            List<String> properties = typeof(Soil).GetProperties().Where(p => p.CanWrite).Select(p => p.Name).ToList();
            if (!File.Exists(xmlFile))
            {
                // initial creation of xml file > needs to be committed to repos so it is created only once !
                using (var stream = File.OpenWrite(xmlFile))
                {
                    serializer.Serialize(stream, properties);
                }
            }
            // read recorded property names from xml file
            List<String> recordedProperties;
            using (var stream = File.OpenRead(xmlFile))
            {
                recordedProperties = (List<String>) serializer.Deserialize(stream);
            }
            foreach (var property in recordedProperties)
            {
                bool found = properties.Contains(property);
                if (!found)
                {
                    found = FindOldNameAttribute<T>(property);
                    if (found)
                    {
                        break;
                    }
                }
                // note: if a Soil property was deleted (not renamed), you can delete the <T>Properties.xml to reset this test
                Assert.IsTrue(found, string.Format("{0} Property {1} not found, if this has been renamed a [XmlOldName] attribute must be added for backward compatibility! ", typeof(T).Name, property));
            }
        }

        private static bool FindOldNameAttribute<T>(string property)
        {
            foreach (var memberInfo in typeof(T).GetMembers().Where(m => !AttributesExtensionHelper.IsReadOnly(m)))
            {
                if (Attribute.IsDefined(memberInfo, typeof(XmlOldNameAttribute)))
                {
                    var xmlOldNamesAttribute =
                        (XmlOldNameAttribute) Attribute.GetCustomAttribute(memberInfo, typeof(XmlOldNameAttribute));
                    foreach (var oldName in xmlOldNamesAttribute.OldNames)
                    {
                        if (oldName == property)
                        {
                            return true;
                        }
                    }
                }
            }
            return false;
        }

        private void ConvertFromGeoDatabaseCircularTest(string fileName, bool soilsOnly = false)
        {
            var profile1Ds = new List<SoilProfile1D>();
            var profile2Ds = new List<SoilProfile2D>();
            var soils = new SoilList();

            Assert.IsTrue(File.Exists(fileName));
            using (var geodatabase = new GeoDatabase(fileName))
            {
                soils = geodatabase.ReadSoils(soils.Soils);
                if (!soilsOnly)
                {
                    profile1Ds.AddRange(geodatabase.ReadSoilProfiles(soils.Soils));
                    profile2Ds.AddRange(geodatabase.ReadSoilProfiles2D(soils.Soils));
                }
            }

            var soildatabase = new SoilModelDatabase();
            soildatabase.New(TestOutputFolder + @"testdata.sqlite");
            soildatabase.WriteSoils(soils.Soils);
            if (!soilsOnly)
            {
                soildatabase.WriteSoilProfile1Ds(profile1Ds);
                soildatabase.WriteSoilProfile2Ds(profile2Ds);
            }
            soildatabase.Close();

            // reopen and read back the data
            soildatabase.Open(TestOutputFolder + @"testdata.sqlite");
            var soilList = soildatabase.ReadSoils();
            var soilsCopy = soilList.Soils;
            var profile1Dscopy = soildatabase.ReadSoilProfiles1D();
            var profile2Dscopy = soildatabase.ReadSoilProfiles2D();
            soildatabase.Close();

            // check the soil properties against the original ones
            Assert.AreEqual(soils.Soils.Count, soilsCopy.Count);
            soils.Soils.Sort();
            soilsCopy.Sort();
            var co = new CompareLogic();
            co.Config.ShowBreadcrumb = false;
            co.Config.CompareStaticFields = false;
            co.Config.CompareStaticProperties = false;
            co.Config.AttributesToIgnore = new List<Type> { typeof(XmlIgnoreAttribute) };

            var result = co.Compare(soils.Soils, soilsCopy);
            Assert.AreEqual(0, result.Differences.Count, "Soils fail comparison: " + result.DifferencesString);

            // check the soil profiles (1D)
            co.Config.MembersToIgnore = new List<String> { "ProbabilityOfOccurrence" };
            foreach (var profile in profile1Ds)
            {
                var profileCopy = profile1Dscopy.First(p => p.Name == profile.Name);
                
                result = co.Compare(profile, profileCopy);
                Assert.AreEqual(0, result.Differences.Count, "Profile1D fails comparison: " + result.DifferencesString);
            }

            // check the soil profiles (2D)
            co.Config.MembersToIgnore = new List<String> { "Geometry" };
            foreach (var profile in profile2Ds)
            {
                var profileCopy = profile2Dscopy.First(p => p.Name == profile.Name);

                result = co.Compare(profile, profileCopy);
                Assert.AreEqual(0, result.Differences.Count, "Profile2D fails comparison: " + result.DifferencesString);
                
                // Geometry checks, exact comparisons not possible, some (unused) geometry objects may be missing
                Assert.IsTrue(profile.Geometry.Points.Count > 0, "Geometry Points missing");
                Assert.IsTrue(profile.Geometry.Curves.Count > 0, "Geometry Curves missing");
                Assert.IsTrue(profile.Geometry.Loops.Count > 0, "Geometry Loops missing");
                Assert.AreEqual(profile.Geometry.Surfaces.Count, profileCopy.Geometry.Surfaces.Count, "Geometry Surfaces missing");
                profileCopy.Geometry.RegenerateGeometry();
                Assert.AreEqual(profile.Surfaces.Count, profileCopy.Surfaces.Count, "Geometry Surfaces missing");
            }
        }

        private bool IsStochastEqual(Stochast stochast1, Stochast stochast2)
        {
            // Stochast does not implement Equals() but we can compare the database values
            return (stochast1.Name == stochast2.Name &&
                    stochast1.Mean == stochast2.Mean &&
                    stochast1.Maximum == stochast2.Maximum &&
                    stochast1.Minimum == stochast2.Minimum &&
                    stochast1.DistributionType == stochast2.DistributionType &&
                    stochast1.DeviationType == stochast2.DeviationType &&
                    stochast1.Deviation == stochast2.Deviation &&
                    stochast1.Variation == stochast2.Variation &&
                    stochast1.DesignFactor == stochast2.DesignFactor &&
                    stochast1.DesignDeviation == stochast2.DesignDeviation &&
                    stochast1.Shape == stochast2.Shape &&
                    stochast1.ShapeB == stochast2.ShapeB &&
                    stochast1.ActualValue == stochast2.ActualValue &&
                    stochast1.CorrelationLength == stochast2.CorrelationLength &&
                    stochast1.CorrelationPeriod == stochast2.CorrelationPeriod
                   );
        }
    }

    public class OldCustomSoilLayer1D : SoilLayer1D
    {
        public double OriginalCustomProperty { get; set; }
    }

    public class NewCustomSoilLayer1D : SoilLayer1D
    {
        [XmlOldName("OriginalCustomProperty")]
        public double RenamedCustomProperty { get; set; }
    }
}