/*
 * 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.3.0   Retrieve generated keys properly!
    2.4.0   Support MS SQLServer
	2.5.0   Replace description with quantity and unit - can ONLY record produce sales.
            PlantSpecies is mandatory
	2.6.0   Prevent setting PurchaseItem or SaleItem as a direct ancestor
            SaleItems cannot have descendants.
            Allow leaf items to be disconnected from their ancestors
    3.0.0	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 uk.co.gardennotebook.util.SimpleMoney;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

import java.math.BigDecimal;

import java.sql.Connection;
import java.sql.SQLException;
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 SaleItemBuilder implements ISaleItemBuilder
{
	private static final Logger LOGGER = LogManager.getLogger();

	private ISaleItem oldInstance = null;

	private final boolean newInstance;

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

	private int id;
	private int saleId;
	private boolean changedSaleId = false;
	private int plantSpeciesId;
	private boolean changedPlantSpeciesId = false;
	private Integer plantVarietyId;
	private boolean changedPlantVarietyId = false;
	private BigDecimal quantity;
	private boolean changedQuantity = false;
	private String unit;
	private boolean changedUnit = false;
	private BigDecimal itemCost;
	private boolean changedItemCost = false;

	/*
	*	ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.
	*/
	private String currency;
	private boolean changedCurrency = 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
	*/
	SaleItemBuilder()
	{
		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
	*/
	SaleItemBuilder(final ISaleItem 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;
			commentHandler = new DBCommentHandler(NotebookEntryType.SALEITEM);
			return;
		}

		newInstance = false;
		oldInstance = oldVal;

		SaleItem baseObj;
		if (oldVal instanceof SaleItem)
		{
			baseObj = (SaleItem)oldVal;
			this.id = baseObj.getId();
			this.saleId = baseObj.getSaleId();
			this.plantSpeciesId = baseObj.getPlantSpeciesId();
			this.plantVarietyId = baseObj.getPlantVarietyId();
			this.quantity = baseObj.getQuantity().orElse(null);
			this.unit = baseObj.getUnit().orElse(null);
			this.itemCost = baseObj.getItemCost().orElse(null);
			this.currency = baseObj.getCurrency().orElse(null);
			this.lastUpdated = baseObj.getLastUpdated();
			this.created = baseObj.getCreated();
		}
		else
		{
			Object ky = oldVal.getKey();
			if (ky == null) return;
			if (ky instanceof Integer)
				this.id = (Integer)ky;
			ky = oldVal.getSale();
			if (ky == null)
			{
				this.saleId = 0;
			}
			else
			{
				this.saleId = ((ISale)ky).getKey();
			}
			ky = oldVal.getPlantSpecies();
			if (ky == null)
			{
				this.plantSpeciesId = 0;
			}
			else
			{
				this.plantSpeciesId = ((IPlantSpecies)ky).getKey();
			}
			ky = oldVal.getPlantVariety().orElse(null);
			if (ky == null)
			{
				this.plantVarietyId = null;
			}
			else
			{
				this.plantVarietyId = ((IPlantVariety)ky).getKey();
			}
			this.quantity = oldVal.getQuantity().orElse(null);
			this.unit = oldVal.getUnit().orElse(null);
			this.itemCost = oldVal.getItemCost().orElse(null);
			this.currency = oldVal.getCurrency().orElse(null);
			this.lastUpdated = oldVal.getLastUpdated();
			this.created = oldVal.getCreated();
		}
		commentHandler = new DBCommentHandler(NotebookEntryType.SALEITEM, this.id);
		LOGGER.traceExit();
	}	//	constructor()

	/**
	*	give the (new) value of saleId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	ISaleItemBuilder saleId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("saleId(): oldVal={}, newVal={}", this.saleId, newVal);
		if (this.saleId == newVal) return this;
		this.saleId = newVal;
		changedSaleId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public ISaleItemBuilder sale(final ISale newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("sale(): oldVal={}, newVal={}", this.saleId, newVal);
		if (newVal == null) return this;
		if (this.saleId == newVal.getKey()) return this;
		this.saleId = newVal.getKey();
		changedSaleId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	/**
	*	give the (new) value of plantSpeciesId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	ISaleItemBuilder 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 ISaleItemBuilder plantSpecies(final IPlantSpecies newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantSpecies(): oldVal={}, newVal={}", this.plantSpeciesId, newVal);
		if (newVal == null) return this;
		if (this.plantSpeciesId == newVal.getKey()) return this;
		if (newVal == null)
		{
			this.plantSpeciesId = 0;
			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
	*/
	ISaleItemBuilder 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 ISaleItemBuilder 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;
	}

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

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

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

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

	@Override
	public ISaleItemBuilder itemCost(final BigDecimal newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("itemCost(): oldVal={}, newVal={}", this.itemCost, newVal);

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

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

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

    @Override
	public ISaleItemBuilder itemPrice(final SimpleMoney newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("itemPrice({})", newVal);

		if (newVal == null)
		{
			currency(null);
			itemCost(null);
		}
		else
		{
			currency(newVal.currency().getCurrencyCode());
			itemCost(newVal.amount());
		}
		LOGGER.traceExit();
		return this;
	}

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

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

	@Override
	public ISaleItemBuilder 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
	*/
	ISaleItemBuilder deleteComment(int... newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

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

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

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

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

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

	@Override
	public ISaleItemBuilder 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 ISaleItemBuilder 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 ISaleItemBuilder 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 ISaleItem save() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("save(): somethingChanged: {}, newInstance: {}, changedComments: {}, changedAncestor: {}",
                                                                somethingChanged, newInstance, changedComments, changedAncestor);

		if (!somethingChanged && !newInstance && !changedComments && !changedAncestor)
		{
			LOGGER.traceExit("nothing changed");
			return MySQLCache.cacheSaleItem.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.cacheSaleItem.remove(this.id);
		}
// populate the cache
		new SaleItemLister().id(this.id).fetch();
		ISaleItem newValue = MySQLCache.cacheSaleItem.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.cacheSale.get(saleId).flagChildAdded(newValue);
			if (changedPlantSpeciesId)
			{
				MySQLCache.cachePlantSpecies.get(plantSpeciesId).flagChildAdded(newValue);
			}
			if (changedPlantVarietyId && (plantVarietyId != null) )
			{
				MySQLCache.cachePlantVariety.get(plantVarietyId).flagChildAdded(newValue);
			}
		}
		else
		{	//	updated
			if (changedSaleId)
			{
				if (oldInstance != null)
				{
					MySQLCache.cacheSale.get(oldInstance.getSale().getKey()).flagChildDeleted(oldInstance);
				}
				MySQLCache.cacheSale.get(newValue.getSale().getKey()).flagChildAdded(newValue);
			}
			if (changedPlantSpeciesId)
			{
				if (oldInstance != null)
				{
					MySQLCache.cachePlantSpecies.get(oldInstance.getPlantSpecies().getKey()).flagChildDeleted(oldInstance);
				}
				MySQLCache.cachePlantSpecies.get(newValue.getPlantSpecies().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;
		changedSaleId = false;
		changedPlantSpeciesId = false;
		changedPlantVarietyId = false;
		changedQuantity = false;
		changedUnit = false;
		changedItemCost = false;
		changedCurrency = 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.saleId <= 0)
		{
			LOGGER.debug("saleId 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;

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from storylineindex where (ancestorId = " + this.id +
						" and ancestorType = 'SI') or (descendantId = " + this.id + " and descendantType = 'SI')) as fred";
				case hsqldb -> query = "select exists (select 1 from storylineindex where (ancestorId = " + this.id +
						" and ancestorType = 'SI') or (descendantId = " + this.id + " and descendantType = 'SI')) as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from storylineindex where (ancestorId = " + this.id +
						" and ancestorType = 'SI') OR (descendantId = " + this.id + " and descendantType = 'SI')) 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);
			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);
			}
			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 saleitem where saleItemId = " + 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 = 'SI'";
				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.cacheSaleItem.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.cacheSale.get(oldInstance.getSale().getKey()).flagChildDeleted(oldInstance);
			MySQLCache.cachePlantSpecies.get(oldInstance.getPlantSpecies().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 saleitem set ");
		if (changedSaleId)
		{
			query.append("saleId = ?, ");
		}

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

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

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

		if (changedUnit)
		{
			query.append("unit = ?, ");
		}


		if (changedItemCost)
		{
			query.append("itemCost = ?, ");
		}

		if (changedCurrency)
		{
			query.append("currency = ?, ");
		}

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

			if (changedPlantSpeciesId)
			{
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 (changedQuantity)
			{
				if (this.quantity == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.NUMERIC);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.quantity);
					stmt.setBigDecimal(paramIx++, this.quantity);
				}
			}

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

			if (changedItemCost)
			{
				if (this.itemCost == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.NUMERIC);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.itemCost);
					stmt.setBigDecimal(paramIx++, this.itemCost);
				}
			}

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

			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.changedSaleId)
		{
			throw LOGGER.throwing(Level.ERROR, new IllegalStateException("SaleItemBuilder: doInsert(): saleId unspecified"));
		}
		if (!this.changedPlantSpeciesId)
		{
			throw LOGGER.throwing(Level.ERROR, new IllegalStateException("SaleItemBuilder: doInsert(): plantSpeciesId unspecified"));
		}

		StringBuilder query = new StringBuilder("insert into saleitem (");
		query.append("saleId, ");
		query.append("plantSpeciesId, ");

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

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

		if (changedUnit)
		{
			query.append("unit, ");
		}

		if (changedItemCost)
		{
			query.append("itemCost, ");
		}

		if (changedCurrency)
		{
			query.append("currency, ");
		}

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

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

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

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

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

		if (changedCurrency)
		{
			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.saleId);
			stmt.setInt(paramIx++, this.saleId);
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 (changedQuantity) {
				if (this.quantity == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.NUMERIC);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.quantity);
					stmt.setBigDecimal(paramIx++, this.quantity);
				}
			}

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

			if (changedItemCost) {
				if (this.itemCost == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.NUMERIC);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.itemCost);
					stmt.setBigDecimal(paramIx++, this.itemCost);
				}
			}

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

			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 SaleItems as output by a JSON DUMP
    *
    *	@throws	GNDBException	If the underlying database throws SQLException it is translated to this.
    *				The causal SQLException can be retrieved by <code>getCause()</code>
    * 
    * @since 2.5.0
	*/
	void restoreJsonDump(List<JsonObject> newVal) throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("restoreJsonDump(list JSON)");

        if (newVal.isEmpty())
            return;
		StringBuilder query = new StringBuilder("insert into saleitem (");
		query.append("saleItemId, ");
		query.append("saleId, ");
		query.append("plantSpeciesId, ");
		query.append("plantVarietyId, ");
		query.append("quantity, ");
		query.append("unit, ");
		query.append("itemCost, ");
		query.append("currency, ");
		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("?, ");
		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 saleitem ON");
            }
            
            for (JsonObject jo : newVal)
            {
                if (!"DUMP".equals(jo.getString("JsonMode", "DUMP")))
                {
                    LOGGER.error("SaleItem DUMP object is not DUMP");
                    throw new IllegalArgumentException("SaleItem DUMP object is not DUMP");
                }
                if (!"SaleItem".equals(jo.getString("JsonNBClass", "SaleItem")))
                {
                    LOGGER.error("SaleItem DUMP object is not SaleItem");
                    throw new IllegalArgumentException("SaleItem DUMP object is not SaleItem");
                }
                SaleItem ps = new SaleItem(jo);
                if (ps.getId() <= 0)
                {//this forces the value of the id field.  The >0 test is a bodge.
                    LOGGER.error("SaleItem DUMP object does not have an id");
                    throw new IllegalArgumentException("SaleItem 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.getSaleId());
                stmt.setInt(paramIx++, ps.getSaleId());

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.getQuantity().isEmpty())
                {
LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.NUMERIC);
                }
                else
                {
LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getQuantity().get());
                    stmt.setBigDecimal(paramIx++, ps.getQuantity().get());
                }

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

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

                if (ps.getCurrency().isEmpty())
                {
LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getCurrency().get());
                    stmt.setString(paramIx++, ps.getCurrency().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 saleitem OFF");
                    }
                    CommentBuilder cb = new CommentBuilder(NotebookEntryType.SALEITEM, ps.getId());
                    cb.doJsonInsert(ps.getComments(), conn);
                    if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
                    {
                        conn.createStatement().execute("SET IDENTITY_INSERT saleitem 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 saleitem 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());
		}


LOGGER.traceExit(log4jEntryMsg);
	}	// restoreJsonDump

	@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 ISaleItemBuilder ancestor(IGroundwork ancestor) throws GNDBException
	{
		if (hasAncestor() || hasDescendant())
		{
			return this;
		}
		this.newAncestor = ancestor;
		changedAncestor = true;
		return this;
	}	//	ancestor(Groundwork)

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

	@Override
	public ISaleItemBuilder 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.SALEITEM, 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.SALEITEM, this.id);
    }
    
	@Override
	public boolean hasDescendant() throws GNDBException
	{
        return false;
	}	//	hasDescendant()

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

}

