/*
 * 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!
    2.4.0   Support MS SQLServer
    2.6.0   PurchaseItems cannot have ancestors, prevent attempts to set one
    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 PurchaseItemBuilder implements IPurchaseItemBuilder
{
	private static final Logger LOGGER = LogManager.getLogger();

	private IPurchaseItem oldInstance = null;

	private final boolean newInstance;

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

	private int id;
	private int purchaseId;
	private boolean changedPurchaseId = false;
	private int productId;
	private boolean changedProductId = 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
	*/
	PurchaseItemBuilder()
	{
		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
	*/
	PurchaseItemBuilder(final IPurchaseItem 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.PURCHASEITEM);
			return;
		}

		newInstance = false;
		oldInstance = oldVal;

		PurchaseItem baseObj;
		if (oldVal instanceof PurchaseItem)
		{
			baseObj = (PurchaseItem)oldVal;
			this.id = baseObj.getId();
			this.purchaseId = baseObj.getPurchaseId();
			this.productId = baseObj.getProductId();
			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;
			this.id = (Integer)ky;
			ky = oldVal.getPurchase();
			if (ky == null)
			{
				this.purchaseId = 0;
			}
			else
			{
				this.purchaseId = ((IPurchase)ky).getKey();
			}
			ky = oldVal.getProduct();
			if (ky == null)
			{
				this.productId = 0;
			}
			else
			{
				this.productId = ((IProduct)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.PURCHASEITEM, this.id);
		LOGGER.traceExit();
	}	//	constructor()

	/**
	*	give the (new) value of purchaseId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IPurchaseItemBuilder purchaseId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("purchaseId(): oldVal={}, newVal={}", this.purchaseId, newVal);
		if (this.purchaseId == newVal) return this;
		this.purchaseId = newVal;
		changedPurchaseId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IPurchaseItemBuilder purchase(final IPurchase newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("purchase(): oldVal={}, newVal={}", this.purchaseId, newVal);
		if (newVal == null) return this;
		if (this.purchaseId == newVal.getKey()) return this;
		this.purchaseId = newVal.getKey();
		changedPurchaseId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	/**
	*	give the (new) value of productId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IPurchaseItemBuilder productId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("productId(): oldVal={}, newVal={}", this.productId, newVal);
		if (this.productId == newVal) return this;
		this.productId = newVal;
		changedProductId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IPurchaseItemBuilder product(final IProduct newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("product(): oldVal={}, newVal={}", this.productId, newVal);
		if (newVal == null) return this;
		if (this.productId == newVal.getKey()) return this;
		this.productId = newVal.getKey();
		changedProductId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IPurchaseItemBuilder 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 IPurchaseItemBuilder 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 IPurchaseItemBuilder 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 IPurchaseItemBuilder 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 IPurchaseItemBuilder 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 IPurchaseItemBuilder addComment(final String... newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("addComment[array]()");

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

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

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

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

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

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

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

	@Override
	public IPurchaseItemBuilder 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 IPurchaseItemBuilder 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 IPurchaseItemBuilder 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 IPurchaseItem 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.cachePurchaseItem.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.cachePurchaseItem.remove(this.id);
		}
// populate the cache
		new PurchaseItemLister().id(this.id).fetch();
		IPurchaseItem newValue = MySQLCache.cachePurchaseItem.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.cachePurchase.get(purchaseId).flagChildAdded(newValue);
			MySQLCache.cacheProduct.get(productId).flagChildAdded(newValue);
		}
		else
		{	//	updated
			if (changedPurchaseId)
			{
				if (oldInstance != null)
				{
					MySQLCache.cachePurchase.get(oldInstance.getPurchase().getKey()).flagChildDeleted(oldInstance);
				}
				MySQLCache.cachePurchase.get(newValue.getPurchase().getKey()).flagChildAdded(newValue);
			}
			if (changedProductId)
			{
				if (oldInstance != null)
				{
					MySQLCache.cacheProduct.get(oldInstance.getProduct().getKey()).flagChildDeleted(oldInstance);
				}
				MySQLCache.cacheProduct.get(newValue.getProduct().getKey()).flagChildAdded(newValue);
			}
		}

		//	stop multiple saves!
		oldInstance = null;

		somethingChanged = false;
		changedComments = false;
		changedAncestor = false;
		changedPurchaseId = false;
		changedProductId = 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.purchaseId <= 0)
		{
			LOGGER.debug("purchaseId not set");
			return false;
		}
		if (this.productId <= 0)
		{
			LOGGER.debug("productId 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 todolist where purchaseItemId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from todolist where purchaseItemId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from todolist where purchaseItemId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PurchaseItemBuilder: 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);
			}

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from reminder where purchaseItemId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from reminder where purchaseItemId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from reminder where purchaseItemId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PurchaseItemBuilder: canDelete(): no known RDBMS"));
				}
			}
			LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
			rs.next();
			readValue = readValue || rs.getBoolean("fred");
			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 storylineindex where (ancestorId = " + this.id + " and ancestorType = 'PI') or (descendantId = " + this.id + " and descendantType = 'PI')) as fred";
				case hsqldb -> query = "select exists (select 1 from storylineindex where (ancestorId = " + this.id + " and ancestorType = 'PI') or (descendantId = " + this.id + " and descendantType = 'PI')) as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from storylineindex where (ancestorId = " + this.id + " and ancestorType = 'PI') OR (descendantId = " + this.id + " and descendantType = 'PI')) THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PurchaseItemBuilder: 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 purchaseitem where purchaseItemId = " + 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 = 'PI'";
				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.cachePurchaseItem.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.cachePurchase.get(oldInstance.getPurchase().getKey()).flagChildDeleted(oldInstance);
			MySQLCache.cacheProduct.get(oldInstance.getProduct().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 purchaseitem set ");
		if (changedPurchaseId)
		{
			query.append("purchaseId = ?, ");
		}

		if (changedProductId)
		{
			query.append("productId = ?, ");
		}

		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 purchaseItemId = ").append(this.id);
LOGGER.debug("doUpdate(): query={} ", query.toString());
		try (	Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString());	)
		{
			int paramIx = 1;
			if (changedPurchaseId)
			{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.purchaseId);
				stmt.setInt(paramIx++, this.purchaseId);
			}

			if (changedProductId)
			{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.productId);
				stmt.setInt(paramIx++, this.productId);
			}

			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.changedPurchaseId)
		{
			throw LOGGER.throwing(Level.ERROR, new IllegalStateException("PurchaseItemBuilder: doInsert(): purchaseId unspecified"));
		}
		if (!this.changedProductId)
		{
			throw LOGGER.throwing(Level.ERROR, new IllegalStateException("PurchaseItemBuilder: doInsert(): productId unspecified"));
		}

		StringBuilder query = new StringBuilder("insert into purchaseitem (");
		query.append("purchaseId, ");
		query.append("productId, ");
		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("?, ");
		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.purchaseId);
			stmt.setInt(paramIx++, this.purchaseId);
LOGGER.debug("doInsert(): param {}={}", paramIx, this.productId);
			stmt.setInt(paramIx++, this.productId);
			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 PurchaseItems as output by a JSON DUMP
     * 
     *	@throws	GNDBException	If the underlying MySQL database throws SQLException it is translated to this.
     *				The causal SQLException can be retrieved by <code>getCause()</code>
     * 
     * @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 purchaseitem (");
        query.append("purchaseItemId, ");
		query.append("purchaseId, ");
		query.append("productId, ");
		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("?) ");
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 purchaseitem ON");
            }
            
            for (JsonObject jo : newVal)
            {
                if (!"DUMP".equals(jo.getString("JsonMode", "DUMP")))
                {
                    LOGGER.error("PurchaseItem DUMP object is not DUMP");
                    throw new IllegalArgumentException("PurchaseItem DUMP object is not DUMP");
                }
                if (!"PurchaseItem".equals(jo.getString("JsonNBClass", "PurchaseItem")))
                {
                    LOGGER.error("PurchaseItem DUMP object is not PurchaseItem");
                    throw new IllegalArgumentException("PurchaseItem DUMP object is not PurchaseItem");
                }
                PurchaseItem ps = new PurchaseItem(jo);
                if (ps.getId() <= 0)
                {//this forces the value of the id field.  The >0 test is a bodge.
                    LOGGER.error("PurchaseItem DUMP object does not have an id");
                    throw new IllegalArgumentException("PurchaseItem 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.getPurchaseId());
                stmt.setInt(paramIx++, ps.getPurchaseId());
                
LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getProductId());
                stmt.setInt(paramIx++, ps.getProductId());
                
                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("v(): 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 purchaseitem OFF");
                    }
                    CommentBuilder cb = new CommentBuilder(NotebookEntryType.PURCHASEITEM, ps.getId());
                    cb.doJsonInsert(ps.getComments(), conn);
                    if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
                    {
                        conn.createStatement().execute("SET IDENTITY_INSERT purchaseitem 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 purchaseitem 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

	@Override
	public boolean hasAncestor() throws GNDBException
	{
        return false;
	}	//	hasAncestor()

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


	private boolean setAncestor(INotebookEntry mother) throws GNDBException
	{
		return false;	// PIs cannot have an ancestor
	}	//	setAncestor()

	@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.PURCHASEITEM, 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.PURCHASEITEM, 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.PURCHASEITEM, this.id, descendant.getType(), descendant.getKey());
	}	//	addDescendant(Husbandry)


}

