/*
 * Copyright (C) 2018-2021, 2023 Andrew Gegg
 *
 *	This file is part of the Garden Notebook application
 *
 * The Garden 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
	2.2.0   Support hsqldb dialect
    2.2.5   Improve JSON load - make it faster!
    2.3.0   Retrieve generated keys properly!
            Date handling change - seems to be a MySQL 8 thing.
    2.4.0   Support MS SQLServer
    2.6.0   Prevent setting SaleItem as direct ancestor
            Prevent setting PurchaseItem as descendant
            Allow leaf items to be disconnected from their ancestors
	3.0.0	Support Locations and Quantity field
			Use DBCommentHandler
	3.1.0	Use jakarta implementation of JSON
 */

package uk.co.gardennotebook.mysql;

import uk.co.gardennotebook.spi.*;
import uk.co.gardennotebook.util.StoryLineTree;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Date;
import java.sql.Timestamp;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import jakarta.json.JsonObject;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;
import org.apache.logging.log4j.Level;

/**
*
*{@inheritDoc}
*
*	@author	Andy Gegg
*	@version	3.1.0
*	@since	1.0
*/
final class GroundworkBuilder implements IGroundworkBuilder
{
	private static final Logger LOGGER = LogManager.getLogger();

	private IGroundwork oldInstance = null;

	private final boolean newInstance;

	private INotebookEntry newAncestor = null;
	private boolean changedAncestor = false;

	private int id;
	private int activityId;
	private boolean changedActivityId = false;

	/*
	*	The activity is for plants of this species.
@apiNote
If present, plantVarietyId may or may not be given.  This 'denormalises' the model but enables easy searches for e.g. 'all tomatoes'
	*/
	private Integer plantSpeciesId;
	private boolean changedPlantSpeciesId = false;

	/*
	*	The activity is for plants of this variety.
@apiNote
If present, plantSpeciesId must be given.  This 'denormalises' the model but enables easy searches for e.g. 'all tomatoes'
	*/
	private Integer plantVarietyId;
	private boolean changedPlantVarietyId = false;

	/*
	 *	Where the activity took place, e.g. where the planted were planted out.
	 */
	private Integer locationId;
	private boolean changedLocationId = false;

	/*
	 *	Quantifies the activity, e.g. how many seeds were sown.
	 */
	private String quantity;
	private boolean changedQuantity = false;

	private LocalDate date;
	private boolean changedDate = 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
	*/
	GroundworkBuilder()
	{
		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
	*/
	GroundworkBuilder(final IGroundwork oldVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("constructor(): oldVal={}", oldVal);
		if (oldVal == null || oldVal.getKey() == null || !(oldVal.getKey() instanceof Integer))
		{
			newInstance = true;
			oldInstance = null;
			this.id = -1;
			//	need to set the default value for dates
			this.date = LocalDate.now();
			commentHandler = new DBCommentHandler(NotebookEntryType.GROUNDWORK);
			return;
		}

		newInstance = false;
		oldInstance = oldVal;

		Groundwork baseObj;
		if (oldVal instanceof Groundwork)
		{
			baseObj = (Groundwork)oldVal;
			this.id = baseObj.getId();
			this.activityId = baseObj.getActivityId();
			this.plantSpeciesId = baseObj.getPlantSpeciesId();
			this.plantVarietyId = baseObj.getPlantVarietyId();
			this.locationId = baseObj.getLocationId();
			this.date = baseObj.getDate();
			this.quantity = baseObj.getQuantity().orElse(null);
			this.lastUpdated = baseObj.getLastUpdated();
			this.created = baseObj.getCreated();
		}
		else
		{
			Object ky = oldVal.getKey();
			if (ky == null) return;
			this.id = (Integer)ky;
			ky = oldVal.getGroundworkActivity();
			if (ky == null)
			{
				this.activityId = 0;
			}
			else
			{
				this.activityId = ((IGroundworkActivity)ky).getKey();
			}
			ky = oldVal.getPlantSpecies().orElse(null);
			if (ky == null)
			{
				this.plantSpeciesId = null;
			}
			else
			{
				this.plantSpeciesId = ((IPlantSpecies)ky).getKey();
			}
			ky = oldVal.getPlantVariety().orElse(null);
			if (ky == null)
			{
				this.plantVarietyId = null;
			}
			else
			{
				this.plantVarietyId = ((IPlantVariety)ky).getKey();
			}

			ky = oldVal.getLocation().orElse(null);
			if (ky == null)
			{
				this.locationId = null;
			}
			else
			{
				this.locationId = ((ILocation)ky).getKey();
			}

			this.date = oldVal.getDate();

			this.quantity = oldVal.getQuantity().orElse(null);

			this.lastUpdated = oldVal.getLastUpdated();
			this.created = oldVal.getCreated();
		}
		commentHandler = new DBCommentHandler(NotebookEntryType.GROUNDWORK, this.id);

		LOGGER.traceExit();
	}	//	constructor()

	/**
	*	give the (new) value of activityId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IGroundworkBuilder activityId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("activityId(): oldVal={}, newVal={}", this.activityId, newVal);
		if (this.activityId == newVal) return this;
		this.activityId = newVal;
		changedActivityId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IGroundworkBuilder groundworkActivity(final IGroundworkActivity newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("groundworkActivity(): oldVal={}, newVal={}", this.activityId, newVal);
		if (newVal == null) return this;
		if (this.activityId == newVal.getKey()) return this;
		this.activityId = newVal.getKey();
		changedActivityId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	/**
	*	give the (new) value of plantSpeciesId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IGroundworkBuilder plantSpeciesId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantSpeciesId(): oldVal={}, newVal={}", this.plantSpeciesId, newVal);
		if (this.plantSpeciesId == newVal) return this;
		this.plantSpeciesId = newVal;
		changedPlantSpeciesId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IGroundworkBuilder plantSpecies(final IPlantSpecies newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantSpecies(): oldVal={}, newVal={}", this.plantSpeciesId, newVal);
		if ((newVal == null) && (this.plantSpeciesId == null)) return this;
		if ((newVal != null) && (this.plantSpeciesId != null) && (this.plantSpeciesId.equals(newVal.getKey()))) return this;
		if (newVal == null)
		{
			this.plantSpeciesId = null;
			if (this.plantVarietyId != null)
			{
				this.plantVarietyId = null;
				changedPlantVarietyId = true;
			}
		}
		else
		{	//	non-null value
			this.plantSpeciesId = newVal.getKey();
			if (this.plantVarietyId != null)
			{
				this.plantVarietyId = null;
				changedPlantVarietyId = true;
			}
		}
		changedPlantSpeciesId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	/**
	*	give the (new) value of plantVarietyId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IGroundworkBuilder plantVarietyId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantVarietyId(): oldVal={}, newVal={}", this.plantVarietyId, newVal);
		if (this.plantVarietyId == newVal) return this;
		this.plantVarietyId = newVal;
		changedPlantVarietyId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IGroundworkBuilder plantVariety(final IPlantVariety newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantVariety(): oldVal={}, newVal={}", this.plantVarietyId, newVal);
		if ((newVal == null) && (this.plantVarietyId == null)) return this;
		if ((newVal != null) && (this.plantVarietyId != null) && (this.plantVarietyId.equals(newVal.getKey()))) return this;
		if (newVal == null)
		{
			this.plantVarietyId = null;
		}
		else
		{	//	non-null value
			this.plantVarietyId = newVal.getKey();
			Integer ps_Id = newVal.getPlantSpecies().getKey();	// cannot be null
			if (!ps_Id.equals(this.plantSpeciesId))	// equals returns false if arg is null
			{
				this.plantSpeciesId = ps_Id;
				changedPlantSpeciesId = true;
			}
		}
		changedPlantVarietyId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	/**
	 *	give the (new) value of locationId
	 *
	 *	@param	newVal	the new value
	 *	@return	this Builder
	 */
	IGroundworkBuilder locationId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("location(): oldVal={}, newVal={}", this.locationId, newVal);
		if (this.locationId == newVal) return this;
		this.locationId = newVal;
		changedLocationId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IGroundworkBuilder location(final ILocation newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("location(): oldVal={}, newVal={}", this.locationId, newVal);
		if ((newVal == null) && (this.locationId == null)) return this;
		if ((newVal != null) && (this.locationId != null) && (this.locationId.equals(newVal.getKey()))) return this;
		if (newVal == null)
		{
			this.locationId = null;
		}
		else
		{	//	non-null value
			this.locationId = newVal.getKey();
		}
		changedLocationId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IGroundworkBuilder date(final LocalDate newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("date(): oldVal={}, newVal={}", this.date, newVal);

		if (newVal == null) return this;
		if (newVal.equals(this.date)) return this;
		this.date = newVal;
		changedDate = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

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

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

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

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

	@Override
	public IGroundworkBuilder 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	newVals	the comment to remove.  If the comment does not exist, this is a null-op
	*	@return	 this Builder
	*/
	IGroundworkBuilder deleteComment(int... newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

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

	@Override
	public IGroundworkBuilder deleteComment(final IComment... newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

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

	@Override
	public IGroundworkBuilder deleteComment(final List<IComment> vals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

		if (vals == null) return this;

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

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

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

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


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

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

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

	@Override
	public IGroundwork save() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("save()");

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

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

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

		if (changedAncestor)
		{
			setAncestor(newAncestor);
		}

// mark cache as dirty
		if (!newInstance &&(somethingChanged || changedComments))
		{
			MySQLCache.cacheGroundwork.remove(this.id);
		}
// populate the cache
		new GroundworkLister().id(this.id).fetch();
		IGroundwork newValue = MySQLCache.cacheGroundwork.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)
		{
			MySQLCache.cacheGroundworkActivity.get(activityId).flagChildAdded(newValue);
			if (changedPlantSpeciesId && (plantSpeciesId != null) )
			{
				MySQLCache.cachePlantSpecies.get(plantSpeciesId).flagChildAdded(newValue);
			}
			if (changedPlantVarietyId && (plantVarietyId != null) )
			{
				MySQLCache.cachePlantVariety.get(plantVarietyId).flagChildAdded(newValue);
			}
		}
		else
		{	//	updated
			if (changedActivityId)
			{
				if (oldInstance != null)
				{
					MySQLCache.cacheGroundworkActivity.get(oldInstance.getGroundworkActivity().getKey()).flagChildDeleted(oldInstance);
				}
				MySQLCache.cacheGroundworkActivity.get(newValue.getGroundworkActivity().getKey()).flagChildAdded(newValue);
			}
			if (changedPlantSpeciesId)
			{
				if (oldInstance != null)
				{
					oldInstance.getPlantSpecies().ifPresent(item -> MySQLCache.cachePlantSpecies.get(item.getKey()).flagChildDeleted(oldInstance) );
				}
				newValue.getPlantSpecies().ifPresent(item -> MySQLCache.cachePlantSpecies.get(item.getKey()).flagChildAdded(newValue) );
			}
			if (changedPlantVarietyId)
			{
				if (oldInstance != null)
				{
					oldInstance.getPlantVariety().ifPresent(item -> MySQLCache.cachePlantVariety.get(item.getKey()).flagChildDeleted(oldInstance) );
				}
				newValue.getPlantVariety().ifPresent(item -> MySQLCache.cachePlantVariety.get(item.getKey()).flagChildAdded(newValue) );
			}
		}

		//	stop multiple saves!
		oldInstance = null;

		somethingChanged = false;
		changedComments = false;
		changedAncestor = false;
		changedActivityId = false;
		changedPlantSpeciesId = false;
		changedPlantVarietyId = false;
		commentHandler.setParentId(this.id);
		commentHandler.save();
		changedDate = false;
		changedQuantity = false;

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

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

	@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.activityId <= 0)
		{
			LOGGER.debug("activityId not set");
			return false;
		}
		if (this.date == null ||
			this.date == LocalDate.MAX ||
			this.date == LocalDate.MIN)
		{
			LOGGER.debug("date not set");
			return false;
		}
		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;
            ResultSet rs;
			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from storylineindex where (ancestorId = " + this.id + " and ancestorType = 'GW') or (descendantId = " + this.id + " and descendantType = 'GW')) as fred";
				case hsqldb -> query = "select exists (select 1 from storylineindex where (ancestorId = " + this.id + " and ancestorType = 'GW') or (descendantId = " + this.id + " and descendantType = 'GW')) as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from storylineindex where (ancestorId = " + this.id + " and ancestorType = 'GW') OR (descendantId = " + this.id + " and descendantType = 'GW')) THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("AfflictionBuilder: canDelete(): no known RDBMS"));
				}
			}
LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
            rs.next();
            readValue = rs.getBoolean("fred");
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 groundwork where groundWorkId = " + 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 = 'GW'";
				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.cacheGroundwork.remove(this.id);
	//	tell any parent beans the child list has mutated
	//	only additions and deletions matter, other changes will be reflected through the child bean
			MySQLCache.cacheGroundworkActivity.get(oldInstance.getGroundworkActivity().getKey()).flagChildDeleted(oldInstance);
			oldInstance.getPlantSpecies().ifPresent(item -> MySQLCache.cachePlantSpecies.get(item.getKey()).flagChildDeleted(oldInstance) );
			oldInstance.getPlantVariety().ifPresent(item -> MySQLCache.cachePlantVariety.get(item.getKey()).flagChildDeleted(oldInstance) );
		}
		oldInstance = null;
LOGGER.traceExit(log4jEntryMsg);
	}	// delete()

	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 groundwork set ");
		if (changedActivityId)
		{
			query.append("activityId = ?, ");
		}

		if (changedPlantSpeciesId)
		{
			query.append("plantSpeciesId = ?, ");
		}

		if (changedPlantVarietyId)
		{
			query.append("plantVarietyId = ?, ");
		}

		if (changedLocationId)
		{
			query.append("locationId = ?, ");
		}

		if (changedDate)
		{
			query.append("date = ?, ");
		}

		if (changedQuantity)
		{
			query.append("quantity = ?, ");
		}

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

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

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

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

			if (changedDate)
			{
LOGGER.debug("doUpdate(): param {}={}", paramIx, Date.valueOf(this.date));
				stmt.setDate(paramIx++, Date.valueOf(this.date), java.util.Calendar.getInstance()); //  2.3.0
			}

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

			stmt.executeUpdate();
//            conn.commit();

		}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.changedActivityId)
		{
			throw LOGGER.throwing(Level.ERROR, new IllegalStateException("GroundworkBuilder: doInsert(): activityId unspecified"));
		}

		StringBuilder query = new StringBuilder("insert into groundwork (");
		query.append("activityId, ");
		query.append("date, ");
		if (changedPlantSpeciesId)
		{
			query.append("plantSpeciesId, ");
		}

		if (changedPlantVarietyId)
		{
			query.append("plantVarietyId, ");
		}

		if (changedLocationId)
		{
			query.append("locationId, ");
		}

		if (changedQuantity)
		{
			query.append("quantity, ");
		}

		query.replace(query.length()-2, query.length(), ") values (");
		query.append("?, ");
		query.append("?, ");
		if (changedPlantSpeciesId)
		{
			query.append("?, ");
		}

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

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

		if (changedQuantity)
		{
			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.activityId);
			stmt.setInt(paramIx++, this.activityId);
LOGGER.debug("doInsert(): param {}={}", paramIx, Date.valueOf(this.date));
			stmt.setDate(paramIx++, Date.valueOf(this.date), java.util.Calendar.getInstance()); //  2.3.0
			if (changedPlantSpeciesId) {
				if (this.plantSpeciesId == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.plantSpeciesId);
					stmt.setInt(paramIx++, this.plantSpeciesId);
				}
			}

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

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

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

			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
     * 
     * @param newVal    a list of JSON objects representing PlantSpecies as output by a JSON DUMP
     * @throws GNDBException	if there is a failure in the underlying RDBMS
     * 
     * @since 2.2.5
     */
    void restoreJsonDump(List<JsonObject> newVal) throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("restoreJsonDump(list JSON)");
        
        if (newVal.isEmpty())
            return;

		StringBuilder query = new StringBuilder("insert into groundwork (");
        query.append("groundWorkId, ");
		query.append("activityId, ");
		query.append("plantSpeciesId, ");
		query.append("plantVarietyId, ");
		query.append("locationId, ");
		query.append("date, ");
		query.append("quantity, ");
		query.append("lastUpdated, ");
		query.append("created) ");
        if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.hsqldb)
        {
            query.append(" overriding system value ");
        }
        query.append(" values (");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?) ");
LOGGER.debug("restoreJsonDump(): query={}", query.toString());

		try (	Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString());	)
		{
            conn.setAutoCommit(false);
            int txCount = 0;
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT groundwork ON");
            }
            
            for (JsonObject jo : newVal)
            {
                if (!"DUMP".equals(jo.getString("JsonMode", "DUMP")))
                {
                    LOGGER.error("Groundwork DUMP object is not DUMP");
                    throw new IllegalArgumentException("Groundwork DUMP object is not DUMP");
                }
                if (!"Groundwork".equals(jo.getString("JsonNBClass", "Groundwork")))
                {
                    LOGGER.error("Groundwork DUMP object is not Groundwork");
                    throw new IllegalArgumentException("Groundwork DUMP object is not Groundwork");
                }
                Groundwork ps = new Groundwork(jo);
                if (ps.getId() <= 0)
                {//this forces the value of the id field.  The >0 test is a bodge.
                    LOGGER.error("Groundwork DUMP object does not have an id");
                    throw new IllegalArgumentException("Groundwork DUMP object does not have an id");
                }
                int paramIx = 1;
LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getId());
                stmt.setInt(paramIx++, ps.getId());

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

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

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

				LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getDate());
                stmt.setDate(paramIx++, Date.valueOf(ps.getDate()), java.util.Calendar.getInstance()); //  2.3.0

				if (ps.getQuantity().isEmpty())
				{
					LOGGER.debug("doJsonInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
					LOGGER.debug("doJsonInsert(): param {}={}", paramIx, ps.getQuantity().get());
					stmt.setString(paramIx++, ps.getQuantity().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 groundwork OFF");
                    }
                    CommentBuilder cb = new CommentBuilder(NotebookEntryType.GROUNDWORK, ps.getId());
                    cb.doJsonInsert(ps.getComments(), conn);
                    if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
                    {
                        conn.createStatement().execute("SET IDENTITY_INSERT groundwork 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 groundwork 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 hasAncestor() throws GNDBException
	{
		if (newInstance)
		{
			return false;
		}
		return new StoryLineIndexLister().hasAncestor(oldInstance);
	}	//	hasAncestor()

	@Override
	public StoryLineTree<? extends INotebookEntry> getAncestors() throws GNDBException
	{
		if (newInstance)
		{
			return StoryLineTree.emptyTree();
		}
		return new StoryLineIndexLister().getAncestors(oldInstance);
	}	//	getAncestors()

	@Override
	public IGroundworkBuilder ancestor(IPurchaseItem ancestor) throws GNDBException
	{
		if (hasAncestor() || hasDescendant())
		{
			return this;
		}
		this.newAncestor = ancestor;
		changedAncestor = true;
		return this;
	}	//	ancestor(PurchaseItem)

	@Override
	public IGroundworkBuilder ancestor(IGroundwork ancestor) throws GNDBException
	{
		if (hasAncestor() || hasDescendant())
		{
			return this;
		}
		this.newAncestor = ancestor;
		changedAncestor = true;
		return this;
	}	//	ancestor(Groundwork)

	@Override
	public IGroundworkBuilder ancestor(IAfflictionEvent ancestor) throws GNDBException
	{
		if (hasAncestor() || hasDescendant())
		{
			return this;
		}
		this.newAncestor = ancestor;
		changedAncestor = true;
		return this;
	}	//	ancestor(AfflictionEvent)

	@Override
	public IGroundworkBuilder ancestor(IHusbandry ancestor) throws GNDBException
	{
		if (hasAncestor() || hasDescendant())
		{
			return this;
		}
		this.newAncestor = ancestor;
		changedAncestor = true;
		return this;
	}	//	ancestor(Husbandry)


	private boolean setAncestor(INotebookEntry mother) throws GNDBException
	{
		if (hasAncestor() || hasDescendant())
		{
			return false;
		}
		return new StoryLineIndexBuilder().addDescendant(mother.getType(), mother.getKey(), NotebookEntryType.GROUNDWORK, this.id);
	}	//	setAncestor()

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

		if (newInstance) return;
        if (hasDescendant())
            return;
        if (!hasAncestor())
            return;

		new StoryLineIndexBuilder().dropLeaf(NotebookEntryType.GROUNDWORK, this.id);
    }
    
	@Override
	public boolean hasDescendant() throws GNDBException
	{
		if (newInstance)
		{
			return false;
		}
		return new StoryLineIndexLister().hasDescendant(oldInstance);
	}	//	hasDescendant()

	@Override
	public StoryLineTree<? extends INotebookEntry> getDescendants() throws GNDBException
	{
		if (newInstance)
		{
			return StoryLineTree.emptyTree();
		}
		return new StoryLineIndexLister().getDescendants(oldInstance);
	}	//	getDescendants()


	@Override
	public boolean addDescendant(IGroundwork descendant) throws GNDBException
	{
		LOGGER.debug("addDescendant(): descendant: {}", descendant);
		if (newInstance)
		{
			return false;
		}
		if (this.id <= 0)
		{
			return false;
		}
		GroundworkBuilder bld = new GroundworkBuilder(descendant);
		if (bld.hasAncestor() || bld.hasDescendant())
		{
			return false;
		}
		bld = null;
		return new StoryLineIndexBuilder().addDescendant(NotebookEntryType.GROUNDWORK, this.id, descendant.getType(), descendant.getKey());
	}	//	addDescendant(Groundwork)

	@Override
	public boolean addDescendant(IAfflictionEvent descendant) throws GNDBException
	{
		LOGGER.debug("addDescendant(): descendant: {}", descendant);
		if (newInstance)
		{
			return false;
		}
		if (this.id <= 0)
		{
			return false;
		}
		AfflictionEventBuilder bld = new AfflictionEventBuilder(descendant);
		if (bld.hasAncestor() || bld.hasDescendant())
		{
			return false;
		}
		bld = null;
		return new StoryLineIndexBuilder().addDescendant(NotebookEntryType.GROUNDWORK, this.id, descendant.getType(), descendant.getKey());
	}	//	addDescendant(AfflictionEvent)

	@Override
	public boolean addDescendant(IHusbandry descendant) throws GNDBException
	{
		LOGGER.debug("addDescendant(): descendant: {}", descendant);
		if (newInstance)
		{
			return false;
		}
		if (this.id <= 0)
		{
			return false;
		}
		HusbandryBuilder bld = new HusbandryBuilder(descendant);
		if (bld.hasAncestor() || bld.hasDescendant())
		{
			return false;
		}
		bld = null;
		return new StoryLineIndexBuilder().addDescendant(NotebookEntryType.GROUNDWORK, this.id, descendant.getType(), descendant.getKey());
	}	//	addDescendant(Husbandry)

	@Override
	public boolean addDescendant(ISaleItem descendant) throws GNDBException
	{
		LOGGER.debug("addDescendant(): descendant: {}", descendant);
		if (newInstance)
		{
			return false;
		}
		if (this.id <= 0)
		{
			return false;
		}
		SaleItemBuilder bld = new SaleItemBuilder(descendant);
		if (bld.hasAncestor() || bld.hasDescendant())
		{
			return false;
		}
		bld = null;
		return new StoryLineIndexBuilder().addDescendant(NotebookEntryType.GROUNDWORK, this.id, descendant.getType(), descendant.getKey());
	}	//	addDescendant(SaleItem)

}

