/*
 *
 *  Copyright (C) 2021, 2023 Andrew Gegg
 *
 * 	This file is part of the Gardeners Notebook application
 *
 *  The Gardeners Notebook application is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU 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 in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *   You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/gpl.html>.
 *
 *
 */

/*
    Change log
	3.1.0	Use jakarta implementation of JSON
*/

package uk.co.gardennotebook.mysql;

import org.apache.logging.log4j.Level;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;
import uk.co.gardennotebook.spi.*;

import jakarta.json.JsonObject;
import java.sql.*;
//import java.sql.Date;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;

/**
 *  These are the various locations in the garden.
 *
 *	@author	Andy Gegg
 *	@version	3.1.0
 *	@since	3.0.0
 */
final class LocationBuilder implements ILocationBuilder
{
    private static final Logger LOGGER = LogManager.getLogger();

    private ILocation oldInstance = null;

    private final boolean newInstance;

    private int id;

    private Integer parentLocationId;
    private boolean changedParentLocationId = false;
    private String name;
    private boolean changedName = false;
    private String description;
    private boolean changedDescription = false;
    private boolean underCover;
    private boolean changedUnderCover = false;
    private String geometry;
    private boolean changedGeometry = false;
    private LocalDateTime lastUpdated;
    private LocalDateTime created;
    private boolean somethingChanged = false;

    private boolean changedComments = false;

    private DBCommentHandler commentHandler;	//	compiler will not allow declaration as final


    /**
     *	constructor to use for a new entry
     */
    LocationBuilder()
    {
        this(null);
    }

    /**
     *	constructor to use to edit or delete an existing entry
     *
     *	@param	oldVal	the existing item to modify or delete; if null a new entry will be created
     */
    LocationBuilder(ILocation oldVal)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("constructor(): oldVal={}", oldVal);
        if (oldVal == null || oldVal.getKey() == null)
        {
            newInstance = true;
            oldInstance = null;
            this.id = -1;
            commentHandler = new DBCommentHandler(NotebookEntryType.LOCATION);
            return;
        }

        newInstance = false;
        oldInstance = oldVal;

        if (oldVal instanceof Location baseObj)
        {
            this.id = baseObj.getId();
            this.parentLocationId = baseObj.getParentLocationId();
            this.name = baseObj.getName();
            this.description = baseObj.getDescription().orElse(null);
            this.underCover = baseObj.isUnderCover();
            this.geometry = baseObj.getGeometry().orElse(null);
            this.lastUpdated = baseObj.getLastUpdated();
            this.created = baseObj.getCreated();
        }
        else
        {
            Integer ky = oldVal.getKey();
            if (ky == null) return;
            this.id = ky;
            ILocation pky = oldVal.getParentLocation().orElse(null);
            if (pky == null)
            {
                this.parentLocationId = null;
            }
            else
            {
                this.parentLocationId = pky.getKey();
            }
            this.name = oldVal.getName();
            this.description = oldVal.getDescription().orElse(null);
            this.underCover = oldVal.isUnderCover();
            this.geometry = oldVal.getGeometry().orElse(null);
            this.lastUpdated = oldVal.getLastUpdated();
            this.created = oldVal.getCreated();
        }
        commentHandler = new DBCommentHandler(NotebookEntryType.LOCATION, this.id);

        LOGGER.traceExit();
   }

    /**
     *	give the (new) value of parentLocationId
     *
     *	@param	newVal	the new value
     *	@return	this Builder
     */
    ILocationBuilder parentLocation(final int newVal)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantSpeciesId(): oldVal={}, newVal={}", this.parentLocationId, newVal);
        if (this.parentLocationId == newVal) return this;
        this.parentLocationId = newVal;
        changedParentLocationId = true;
        somethingChanged = true;
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public ILocationBuilder parentLocation(ILocation newVal)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("parentLocation(): oldVal={}, newVal={}", this.parentLocationId, newVal);
        if ((newVal == null) && (this.parentLocationId == null)) return this;
        if ((newVal != null) && (this.parentLocationId != null) && (this.parentLocationId.equals(newVal.getKey()))) return this;

        if (newVal == null)
        {
            this.parentLocationId = null;
        }
        else
        {	//	non-null value
            this.parentLocationId = newVal.getKey();
        }

        changedParentLocationId = true;
        somethingChanged = true;
        LOGGER.traceExit();
        return this;
    }

    @Override
    public boolean isNameDuplicate(final String newVal) throws GNDBException
    {
        if (newVal == null || newVal.isBlank())
            return false;

        if (!newInstance && !changedName)
            return false;

        if (!newInstance && newVal.equals(oldInstance.getName()))
            return false;

        new LocationLister().load();	// ensure the cache is populated
        return MySQLCache.cacheLocation.values().stream().anyMatch(fx -> newVal.equals(fx.getName()));
    }

    @Override
    public ILocationBuilder name(final String newVal) throws IllegalArgumentException, GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("name(): oldVal={}, newVal={}", this.name, newVal);

        if (newVal == null) return this;
        if (newVal.equals(this.name)) return this;
//		if (isNameDuplicate(newVal))
//		{
//			throw LOGGER.throwing(Level.DEBUG, new IllegalArgumentException("WeatherConditionBuilder: name: " +newVal+" already exists"));
//		}
        this.name = newVal;
        changedName = true;
        somethingChanged = true;
        LOGGER.traceExit();
        return this;
    }

    @Override
    public ILocationBuilder description(final String newVal)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("description(): oldVal={}, newVal={}", this.description, newVal);

        if (newVal == null && this.description == null) return this;
        if (newVal != null && newVal.equals(this.description)) return this;
        this.description = newVal;
        changedDescription = true;
        somethingChanged = true;
        LOGGER.traceExit();
        return this;
    }

    @Override
    public ILocationBuilder underCover(boolean newVal)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("singleShot(): oldVal={}, newVal={}", this.underCover, newVal);

        if (newVal == this.underCover) return this;
        this.underCover = newVal;
        changedUnderCover = true;
        somethingChanged = true;
        LOGGER.traceExit();
        return this;
    }

    @Override
    public ILocationBuilder geometry(final String newVal)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("geometry(): oldVal={}, newVal={}", this.description, newVal);

        if (newVal == null && this.geometry == null) return this;
        if (newVal != null && newVal.equals(this.geometry)) return this;
        this.geometry = newVal;
        changedGeometry = true;
        somethingChanged = true;
        LOGGER.traceExit();
        return this;
    }

    @Override
    public ILocationBuilder addComment(final String... newVals)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("addComment[array]()");

        commentHandler.addComment(newVals);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit();
        return this;
    }

    @Override
    public ILocationBuilder addComment(final List<String> newVals)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("addComment<list>()");

        commentHandler.addComment(newVals);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit("addComment");
        return this;
    }

    /**
     *	remove a comment from this item
     *
     *	@param	oldVals	the comment to remove.  If the comment does not exist, this is a null-op
     *	@return	 this Builder
     */
    ILocationBuilder deleteComment(int... oldVals)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment(int...)");

        commentHandler.deleteComment(oldVals);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public ILocationBuilder deleteComment(IComment... oldVals)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

        commentHandler.deleteComment(oldVals);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public ILocationBuilder deleteComment(List<IComment> oldVals)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

        commentHandler.deleteComment(oldVals);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public ILocationBuilder changeComment(IComment base, String comment)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

        commentHandler.changeComment(base, comment);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public ILocationBuilder changeComment(IComment base, LocalDate date)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

        commentHandler.changeComment(base, date);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
   }

    @Override
    public ILocationBuilder changeComment(IComment base, LocalDate date, String comment)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

        commentHandler.changeComment(base, date, comment);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public ILocation save() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("save(): somethingChanged: {}, newInstance: {}, changedComments: {}",
                somethingChanged, newInstance, changedComments);

        if (!somethingChanged && !newInstance && !changedComments)
        {
            LOGGER.traceExit("nothing changed");
            return MySQLCache.cacheLocation.get(this.id);
        }

        if (newInstance)
        {
            doInsert();
        }
        else if (somethingChanged)
        {
            doUpdate();
        }

        if (changedComments)
        {
            commentHandler.setParentId(this.id);
            commentHandler.save();
        }

// mark cache as dirty
        MySQLCache.invalidLocation = true;
        if (!newInstance &&(somethingChanged || changedComments))
        {
            MySQLCache.cacheLocation.remove(this.id);
        }
// populate the cache
        new LocationLister().id(this.id).fetch();
        ILocation newValue = MySQLCache.cacheLocation.get(this.id);
        if (oldInstance != null)
        {
            oldInstance.flagReplaced(newValue);
        }

        //	tell any parent beans the child list has mutated
        //	only additions and deletions matter, other changes will be reflected through the child bean
        if (newInstance)
        {
            if (parentLocationId != null)
            {
                MySQLCache.cacheLocation.get(parentLocationId).flagChildAdded(newValue);
            }
        }
        else
        {   //  updated
            if (changedParentLocationId)
            {
                if ((oldInstance != null) && (oldInstance.getParentLocation().isPresent()))
                {
                    MySQLCache.cacheLocation.get(oldInstance.getParentLocation().get().getKey()).flagChildDeleted(oldInstance);
                }
                if (parentLocationId != null)
                {
                    MySQLCache.cacheLocation.get(parentLocationId).flagChildAdded(newValue);
                }
            }

        }

        //	stop multiple saves!
        oldInstance = null;

        somethingChanged = false;
        changedComments = false;
        changedParentLocationId = false;
        changedName = false;
        changedDescription = false;
        changedUnderCover = false;
        changedGeometry = false;

        LOGGER.traceExit(log4jEntryMsg);
        return newValue;
    }   //  save()

    @Override
    public boolean needSave()
    {
        return somethingChanged || changedComments;
    }

    @Override
    public boolean canSave()
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("canSave(): newInstance={}", newInstance);

        if (!needSave())
        {//	save() will be a null-op but that's OK
            return true;
        }
        if (this.name == null || this.name.isEmpty() )
        {
            LOGGER.debug("name not set");
            return false;
        }

        try
        {
            if (isNameDuplicate(this.name))
            {
                LOGGER.debug("LocationBuilder: canSave(): {} already exists", this.name);
                return false;
            }
        } catch (GNDBException ex) {
            throw LOGGER.throwing(Level.DEBUG, new IllegalArgumentException("LocationBuilder: name: " +this.name+" already exists"));
        }
        return true;
    }	// canSave()

    @Override
    public boolean canDelete() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("canDelete(): newInstance={}", newInstance);

        if (newInstance) return LOGGER.traceExit(log4jEntryMsg, false);

        boolean  readValue = false;
        try (Connection conn = DBConnection.getConnection();
             Statement stmt = conn.createStatement();	)
        {
            String query;
            switch (DBConnection.DB_IN_USE)
            {
                case MariaDB, MySQL -> query = "select exists (select 1 from location where parentLocationId = " + this.id + ") as fred";
                case hsqldb -> query = "select exists (select 1 from location where parentLocationId = " + this.id + ") as fred from (values(99))";
                case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from location where parentLocationId = " + this.id + ") THEN 1 ELSE 0 END as fred";
                default -> {
                    LOGGER.error("canDelete(): no known rdbms");
                    throw new GNDBException(new IllegalStateException("LocationBuilder: canDelete(): no known RDBMS"));
                }
            }
            LOGGER.debug("canDelete(): query: {}", query);
            ResultSet rs = stmt.executeQuery(query);
            rs.next();
            readValue = rs.getBoolean("fred");
            rs.close();
            LOGGER.debug("canDelete(): readValue: {}", readValue);
            if (readValue)
            {
                return LOGGER.traceExit(log4jEntryMsg, false);
            }

            switch (DBConnection.DB_IN_USE)
            {
                case MariaDB, MySQL -> query = "select exists (select 1 from husbandry where locationId = " + this.id + ") as fred";
                case hsqldb -> query = "select exists (select 1 from husbandry where locationId = " + this.id +
                        ") as fred from (values(99))";
                case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from husbandry where locationId = " + this.id +
                        ") THEN 1 ELSE 0 END as fred";
                default -> {
                    LOGGER.error("canDelete(): no known rdbms");
                    throw new GNDBException(new IllegalStateException("LocationBuilder: canDelete(): no known RDBMS"));
                }
            }
            LOGGER.debug("canDelete(): query: {}", query);
            rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
            LOGGER.debug("canDelete(): readValue: {}", readValue);
            if (readValue)
            {
                return LOGGER.traceExit(log4jEntryMsg, false);
            }

            switch (DBConnection.DB_IN_USE)
            {
                case MariaDB, MySQL -> query = "select exists (select 1 from groundwork where locationId = " + this.id + ") as fred";
                case hsqldb -> query = "select exists (select 1 from groundwork where locationId = " + this.id +
                        ") as fred from (values(99))";
                case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from groundwork where locationId = " + this.id +
                        ") THEN 1 ELSE 0 END as fred";
                default -> {
                    LOGGER.error("canDelete(): no known rdbms");
                    throw new GNDBException(new IllegalStateException("LocationBuilder: canDelete(): no known RDBMS"));
                }
            }
            LOGGER.debug("canDelete(): query: {}", query);
            rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
            LOGGER.debug("canDelete(): readValue: {}", readValue);
            if (readValue)
            {
                return LOGGER.traceExit(log4jEntryMsg, false);
            }

            switch (DBConnection.DB_IN_USE)
            {
                case MariaDB, MySQL -> query = "select exists (select 1 from afflictionevent where locationId = " + this.id + ") as fred";
                case hsqldb -> query = "select exists (select 1 from afflictionevent where locationId = " + this.id +
                        ") as fred from (values(99))";
                case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from afflictionevent where locationId = " + this.id +
                        ") THEN 1 ELSE 0 END as fred";
                default -> {
                    LOGGER.error("canDelete(): no known rdbms");
                    throw new GNDBException(new IllegalStateException("LocationBuilder: canDelete(): no known RDBMS"));
                }
            }
            LOGGER.debug("canDelete(): query: {}", query);
            rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
            LOGGER.debug("canDelete(): readValue: {}", readValue);
            if (readValue)
            {
                return LOGGER.traceExit(log4jEntryMsg, false);
            }

            switch (DBConnection.DB_IN_USE)
            {
                case MariaDB, MySQL -> query = "select exists (select 1 from wildlife where locationId = " + this.id + ") as fred";
                case hsqldb -> query = "select exists (select 1 from wildlife where locationId = " + this.id +
                        ") as fred from (values(99))";
                case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from wildlife where locationId = " + this.id +
                        ") THEN 1 ELSE 0 END as fred";
                default -> {
                    LOGGER.error("canDelete(): no known rdbms");
                    throw new GNDBException(new IllegalStateException("LocationBuilder: canDelete(): no known RDBMS"));
                }
            }
            LOGGER.debug("canDelete(): query: {}", query);
            rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
            LOGGER.debug("canDelete(): readValue: {}", readValue);
            if (readValue)
            {
                return LOGGER.traceExit(log4jEntryMsg, false);
            }

            switch (DBConnection.DB_IN_USE)
            {
                case MariaDB, MySQL -> query = "select exists (select 1 from croppingplan where locationId = " + this.id + ") as fred";
                case hsqldb -> query = "select exists (select 1 from croppingplan where locationId = " + this.id +
                        ") as fred from (values(99))";
                case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from croppingplan where locationId = " + this.id +
                        ") THEN 1 ELSE 0 END as fred";
                default -> {
                    LOGGER.error("canDelete(): no known rdbms");
                    throw new GNDBException(new IllegalStateException("LocationBuilder: canDelete(): no known RDBMS"));
                }
            }
            LOGGER.debug("canDelete(): query: {}", query);
            rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
            LOGGER.debug("canDelete(): readValue: {}", readValue);
            if (readValue)
            {
                return LOGGER.traceExit(log4jEntryMsg, false);
            }

            switch (DBConnection.DB_IN_USE)
            {
                case MariaDB, MySQL -> query = "select exists (select 1 from reviewreferences where locationId = " + this.id + ") as fred";
                case hsqldb -> query = "select exists (select 1 from reviewreferences where locationId = " + this.id +
                        ") as fred from (values(99))";
                case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from reviewreferences where locationId = " + this.id +
                        ") THEN 1 ELSE 0 END as fred";
                default -> {
                    LOGGER.error("canDelete(): no known rdbms");
                    throw new GNDBException(new IllegalStateException("LocationBuilder: canDelete(): no known RDBMS"));
                }
            }
            LOGGER.debug("canDelete(): query: {}", query);
            rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
            LOGGER.debug("canDelete(): readValue: {}", readValue);
            if (readValue)
            {
                return LOGGER.traceExit(log4jEntryMsg, false);
            }

            stmt.close();
        }catch (SQLException ex) {
            LOGGER.error("canDelete(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
            throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
        }
        return LOGGER.traceExit(log4jEntryMsg, !readValue);
    }	// canDelete()

    @Override
    public void delete() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("delete(): newInstance={}", newInstance);

        if (newInstance) return;
        if (!canDelete()) return;

        int res = 0;
        String query = "delete from location where locationId = " + this.id;
        LOGGER.debug("delete(): query: {}", query);
        try (	Connection conn = DBConnection.getConnection();
                 Statement stmt = conn.createStatement();	)
        {
            res = stmt.executeUpdate(query);
            LOGGER.debug("delete(): result: {}", res);
            // tidy up dependencies with nullable keys
            if (res > 0) {
                query = "delete from comment where ownerId = " + this.id + " and ownerType = 'LN'";
                int res2 = stmt.executeUpdate(query);
                LOGGER.debug("delete() comments: result: {}", res2);
            }
            stmt.close();
        }catch (SQLException ex) {
            LOGGER.error("delete(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
            throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
        }
        if (res > 0)
        {
            oldInstance.flagDeleted();
            MySQLCache.cacheLocation.remove(this.id);
        }
        oldInstance = null;
        LOGGER.traceExit(log4jEntryMsg);
    }

    private void doUpdate() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("doUpdate(): newInstance={}, somethingChanged={}", newInstance, somethingChanged);

        if (newInstance) return;
        if (!somethingChanged) return;
        StringBuilder query = new StringBuilder("update location set ");

        if (changedParentLocationId)
        {
            query.append("parentLocationId = ?, ");
        }

        if (changedName)
        {
            query.append("name = ?, ");
        }

        if (changedDescription)
        {
            query.append("description = ?, ");
        }

        if (changedUnderCover)
        {
            query.append("underCover = ?, ");
        }

        if (changedGeometry)
        {
            switch (DBConnection.DB_IN_USE)
            {
                case MySQL, MariaDB -> query.append("geometry = ST_GeomFromText(?), ");
                case MSSQLServer -> query.append("geometry = geometry::STGeomFromText(?, 0), ");
                case hsqldb -> query.append("geometry = ?, ");
            }
//            query.append("geometry = ?, ");
        }

        query.delete(query.length()-2, query.length());
        query.append(" where locationId = ").append(this.id);
        LOGGER.debug("doUpdate(): query={} ", query.toString());
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query.toString());	)
        {
            int paramIx = 1;

            if (changedParentLocationId)
            {
                if (this.parentLocationId == null)
                {
                    LOGGER.debug("doUpdate(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.INTEGER);
                }
                else
                {
                    LOGGER.debug("doUpdate(): param {}={}", paramIx, this.parentLocationId);
                    stmt.setInt(paramIx++, this.parentLocationId);
                }
            }

            if (changedName)
            {
                LOGGER.debug("doUpdate(): param {}={}", paramIx, this.name);
                stmt.setString(paramIx++, this.name);
            }

            if (changedDescription)
            {
                if (this.description == null)
                {
                    LOGGER.debug("doUpdate(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
                    LOGGER.debug("doUpdate(): param {}={}", paramIx, this.description);
                    stmt.setString(paramIx++, this.description);
                }
            }

            if (changedUnderCover)
            {
                LOGGER.debug("doUpdate(): param {}={}", paramIx, this.underCover);
                stmt.setBoolean(paramIx++, this.underCover);
            }

            if (changedGeometry)
            {
                if (this.geometry == null)
                {
                    LOGGER.debug("doUpdate(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
                    LOGGER.debug("doUpdate(): param {}={}", paramIx, this.geometry);
                    stmt.setString(paramIx++, this.geometry);
                }
            }

            stmt.executeUpdate();

        }catch (SQLException ex) {
            LOGGER.error("doUpdate(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
            throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
        }
        LOGGER.traceExit(log4jEntryMsg);
    }	// doUpdate

    private void doInsert() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("doInsert(): newInstance={}", newInstance);

        if (!newInstance) return;
        if (!canSave())
        {
            throw LOGGER.throwing(Level.ERROR, new IllegalStateException("doInsert(): save criteria not met"));
        }
        if (!this.changedName)
        {
            throw LOGGER.throwing(Level.ERROR, new IllegalStateException("LocationBuilder: doInsert(): name unspecified"));
        }

        StringBuilder query = new StringBuilder("insert into location (");
        query.append("name, underCover, ");

        if (changedParentLocationId)
        {
            query.append("parentLocationId, ");
        }

        if (changedDescription)
        {
            query.append("description, ");
        }

        if (changedGeometry)
        {
            query.append("geometry, ");
        }

        query.replace(query.length()-2, query.length(), ") values (");
        query.append("?, ?, ");

        if (changedParentLocationId)
        {
            query.append("?, ");
        }

        if (changedDescription)
        {
            query.append("?, ");
        }

        if (changedGeometry)
        {
            switch (DBConnection.DB_IN_USE)
            {
                case MySQL, MariaDB -> query.append("ST_GeomFromText(?), ");
                case MSSQLServer -> query.append("geometry::STGeomFromText(?, 0), ");
                case hsqldb -> query.append("?, ");
            }
//            query.append("?, ");
        }

        query.replace(query.length()-2, query.length(), ")");
        LOGGER.debug("doInsert(): query={}", query.toString());

        try (	Connection conn = DBConnection.getConnection();
                 PreparedStatement stmt = conn.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS); )
        {
            int paramIx = 1;
            LOGGER.debug("doInsert(): param {}={}", paramIx, this.name);
            stmt.setString(paramIx++, this.name);
            stmt.setBoolean(paramIx++, this.underCover);

            if (changedParentLocationId) {
                if (this.parentLocationId == null)
                {
                    LOGGER.debug("doInsert(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
                    LOGGER.debug("doInsert(): param {}={}", paramIx, this.parentLocationId);
                    stmt.setInt(paramIx++, this.parentLocationId);
                }
            }

            if (changedDescription) {
                if (this.description == null)
                {
                    LOGGER.debug("doInsert(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
                    LOGGER.debug("doInsert(): param {}={}", paramIx, this.description);
                    stmt.setString(paramIx++, this.description);
                }
            }

            if (changedGeometry) {
                if (this.geometry == null)
                {
                    LOGGER.debug("doInsert(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
                    LOGGER.debug("doInsert(): param {}={}", paramIx, this.geometry);
                    stmt.setString(paramIx++, this.geometry);
                }
            }

            stmt.executeUpdate();

            ResultSet rs = stmt.getGeneratedKeys();
            rs.next();
            int newId = rs.getInt(1);
            LOGGER.debug("doInsert(): newId: {}", newId);
            this.id = newId;
        }catch (SQLException ex) {
            LOGGER.error("doInsert(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
            throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
        }

        LOGGER.traceExit(log4jEntryMsg);
    }	// doInsert

    /**
     * Process the whole JSON array from a DUMP.
     *
     * @apiNote It is essential that the JSONdump has all mother locations before their daughters to avoid foreign key violations.
     *
     * @param newVal    a list of JSON objects representing Locations as output by a JSON DUMP
     * @throws GNDBException
     *
     * @since 2.2.5
     */
    void restoreJsonDump(List<JsonObject> newVal) throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("restoreJsonDump(list JSON)");

        if (newVal.isEmpty())
            return;

        String query = "insert into location (locationId, parentLocationId, name, description, undercover, geometry, lastUpdated, created)";
        if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.hsqldb)
        {
            query+= " overriding system value ";
        }
        query += " values (?, ?, ?, ?, ?, ";
        switch (DBConnection.DB_IN_USE)
        {
            case MySQL, MariaDB -> query += ("ST_GeomFromText(?), ");
            case MSSQLServer -> query += ("geometry::STGeomFromText(?, 0), ");
            case hsqldb -> query += ("?, ");
        }
        query += "?, ?)";
        LOGGER.debug("restoreJsonDump(): query={}", query);

        try (	Connection conn = DBConnection.getConnection();
                 PreparedStatement stmt = conn.prepareStatement(query);	)
        {
            conn.setAutoCommit(false);
            int txCount = 0;
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT location ON");
            }

            for (JsonObject jo : newVal)
            {
                if (!"DUMP".equals(jo.getString("JsonMode", "DUMP")))
                {
                    LOGGER.error("Location DUMP object is not DUMP");
                    throw new IllegalArgumentException("Location DUMP object is not DUMP");
                }
                if (!"Location".equals(jo.getString("JsonNBClass", "Location")))
                {
                    LOGGER.error("Location DUMP object is not Location");
                    throw new IllegalArgumentException("Location DUMP object is not Location");
                }

                Location ps = new Location(jo);
                if (ps.getId() <= 0)
                {//this forces the value of the id field.  The >0 test is a bodge.
                    LOGGER.error("Location DUMP object does not have an id");
                    throw new IllegalArgumentException("Location DUMP object does not have an id");
                }
                int paramIx = 1;
                LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getId());
                stmt.setInt(paramIx++, ps.getId());

                if (ps.getParentLocationId() == null)
                {
                    LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.INTEGER);
                }
                else
                {
                    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getParentLocationId());
                    stmt.setInt(paramIx++, ps.getParentLocationId());
                }


                LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getName());
                stmt.setString(paramIx++, ps.getName());

                if (ps.getDescription().isEmpty())
                {
                    LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
                    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getDescription().get());
                    stmt.setString(paramIx++, ps.getDescription().get());
                }

                LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.isUnderCover());
                stmt.setBoolean(paramIx++, ps.isUnderCover());

                if (ps.getGeometry().isEmpty())
                {
                    LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
                    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getGeometry().get());
                    stmt.setString(paramIx++, ps.getGeometry().get());
                }

                LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getLastUpdated());
                stmt.setTimestamp(paramIx++, Timestamp.valueOf(ps.getLastUpdated()));
                LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getCreated());
                stmt.setTimestamp(paramIx++, Timestamp.valueOf(ps.getCreated()));

                stmt.executeUpdate();

                if (!ps.getComments().isEmpty())
                {
                    if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
                    {
                        conn.createStatement().execute("SET IDENTITY_INSERT location OFF");
                    }
                    CommentBuilder cb = new CommentBuilder(NotebookEntryType.LOCATION, ps.getId());
                    cb.doJsonInsert(ps.getComments(), conn);
                    if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
                    {
                        conn.createStatement().execute("SET IDENTITY_INSERT location ON");
                    }
                }

                if (++txCount > 50)
                {
                    conn.commit();
                    txCount = 0;
                }
            }
            conn.commit();
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT location OFF");
            }
        }catch (SQLException ex) {
            LOGGER.error("restoreJsonDump(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
            throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
        }

    }	// restoreJsonDump(JsonObjects)

    @Override
    public boolean hasChild() throws GNDBException
    {
        if (newInstance) return false;

        if (MySQLCache.invalidLocation)
        {
            new LocationLister().load();
        }

        return MySQLCache.cacheLocation.values().stream().
                filter(t -> t.getParentLocation().isPresent()).
                anyMatch(t -> t.getParentLocation().get().getKey().equals(oldInstance.getKey()));
    }

}
