/*
 * Copyright (C) 2018, 2019, 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
	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.Map;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
import java.util.Arrays;

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 RetailerHasProductBuilder implements IRetailerHasProductBuilder
{
	private static final Logger LOGGER = LogManager.getLogger();

	private IRetailerHasProduct oldInstance = null;

	private final boolean newInstance;

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

	private int id;
	private int retailerId;
	private boolean changedRetailerId = false;
	private int productId;
	private boolean changedProductId = false;

	/*
	*	The product code for a particular product at a particular supplier
	*/
	private String SKU;
	private boolean changedSKU = false;
	private LocalDateTime lastUpdated;
	private LocalDateTime created;
	private boolean somethingChanged = false;

	private List<String> newComments;
	private int[] deletedComments;
	private int delComNext = 0;
	private boolean changedComments = false;

	private final Map<Integer, CommentChangeList> editComments = new HashMap<>();

	/**
	*	constructor to use for a new entry
	*/
	RetailerHasProductBuilder()
	{
		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
	*/
	RetailerHasProductBuilder(final IRetailerHasProduct 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;
			return;
		}

		newInstance = false;
		oldInstance = oldVal;

		RetailerHasProduct baseObj;
		if (oldVal instanceof RetailerHasProduct)
		{
			baseObj = (RetailerHasProduct)oldVal;
			this.id = baseObj.getId();
			this.retailerId = baseObj.getRetailerId();
			this.productId = baseObj.getProductId();
			this.SKU = baseObj.getSKU().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.getRetailer();
			if (ky == null)
			{
				this.retailerId = 0;
			}
			else
			{
				this.retailerId = ((IRetailer)ky).getKey();
			}
			ky = oldVal.getProduct();
			if (ky == null)
			{
				this.productId = 0;
			}
			else
			{
				this.productId = ((IProduct)ky).getKey();
			}
			this.SKU = oldVal.getSKU().orElse(null);
			this.lastUpdated = oldVal.getLastUpdated();
			this.created = oldVal.getCreated();
		}
		LOGGER.traceExit();
	}	//	constructor()

	/**
	*	give the (new) value of retailerId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IRetailerHasProductBuilder retailerId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("retailerId(): oldVal={}, newVal={}", this.retailerId, newVal);
		if (this.retailerId == newVal) return this;
		this.retailerId = newVal;
		changedRetailerId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IRetailerHasProductBuilder retailer(final IRetailer newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("retailer(): oldVal={}, newVal={}", this.retailerId, newVal);
		if (newVal == null) return this;
		if (this.retailerId == newVal.getKey()) return this;
		this.retailerId = newVal.getKey();
		changedRetailerId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	/**
	*	give the (new) value of productId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IRetailerHasProductBuilder 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 IRetailerHasProductBuilder 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 IRetailerHasProductBuilder SKU(final String newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("SKU(): oldVal={}, newVal={}", this.SKU, newVal);

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

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

		if (newVals == null) return this;
		this.addComment(Arrays.asList(newVals));
		LOGGER.traceExit();
		return this;
	}

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

		if (newVals == null) return this;
		if (newVals.isEmpty()) return this;
		if (this.newComments == null) this.newComments = new ArrayList<>();
		ArrayList<String> temp = new ArrayList<>(newVals);
		temp.removeIf(s -> (s == null || s.isEmpty()));
		this.newComments.addAll(temp);
		changedComments = true;
		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
	*/
	IRetailerHasProductBuilder deleteComment(int... newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

		if (deletedComments == null)
		{
			deletedComments = new int[10];
		}
		if (delComNext + newVals.length >= deletedComments.length)
		{
			deletedComments = Arrays.copyOf(deletedComments, deletedComments.length + newVals.length + 10);
		}
		for (int newVal : newVals)
		{
			if (newVal > 0)
			{
				deletedComments[delComNext++] = newVal;
			}
		}
		changedComments = true;
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

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

		if (newVals == null) return this;
		if (newInstance) return this;
		int[] keys = new int[newVals.length];
		int keyCount = 0;
		for (IComment cmnt : newVals) {
			if (cmnt == null) continue;
			if (cmnt.getOwnerType() != NotebookEntryType.RETAILERHASPRODUCT) continue;
			if (!cmnt.getOwnerKey().equals(oldInstance.getKey())) continue;

			Object ky = cmnt.getKey();
			if (ky == null) continue;
			if ((ky instanceof Integer) && (Integer)ky > 0) keys[keyCount++] = (Integer)ky;
		}
		if (keyCount == 0) return this;
		keys = Arrays.copyOf(keys, keyCount);	// trim array to actual size - should be a null-op
		LOGGER.traceExit(log4jEntryMsg);
		return this.deleteComment(keys);
	}

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

		if (vals == null) return this;
		LOGGER.traceExit(log4jEntryMsg);
		return this.deleteComment(vals.toArray(new IComment[0]));
	}

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

		if (base == null) return this;
		if (newInstance) return this;
		if (base.getOwnerType() != NotebookEntryType.RETAILERHASPRODUCT) return this;
		if (!base.getOwnerKey().equals(oldInstance.getKey())) return this;

		if (comment == null || comment.isEmpty()) return this;

		if (comment.equals(base.getComment())) return this;

		LOGGER.traceExit(log4jEntryMsg);
		return changeComment(base, null, comment);
	}

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

		if (base == null) return this;
		if (newInstance) return this;
		if (base.getOwnerType() != NotebookEntryType.RETAILERHASPRODUCT) return this;
		if (!base.getOwnerKey().equals(oldInstance.getKey())) return this;

		if (date == null || date.isAfter(LocalDate.now())) return this;
		if (date.equals(base.getDate())) return this;

		LOGGER.traceExit(log4jEntryMsg);
		return changeComment(base, date, null);
	}

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

		if (base == null) return this;
		if (newInstance) return this;
		if (base.getOwnerType() != NotebookEntryType.RETAILERHASPRODUCT) return this;
		if (!base.getOwnerKey().equals(oldInstance.getKey())) return this;

		if (date == null && (comment == null || comment.isEmpty())) return this;
		if (date != null && date.isAfter(LocalDate.now())) return this;

		Object ky = base.getKey();
		if (ky == null) return this;
		int key = 0;
		if ((ky instanceof Integer) && (Integer)ky > 0) key = (Integer)ky;
		if (key < 0) return this;

	//	If we're here at least date or comment has been given
	//	Check something's changed
		if (	comment != null && comment.equals(base.getComment()) &&
				date != null && date.equals(base.getDate())	)return this;
		editComments.putIfAbsent(key, new CommentChangeList(key, null, null));
		if (date != null) editComments.get(key).date = date;
		if (comment != null && !comment.isEmpty()) editComments.get(key).comment = comment;
		changedComments = true;
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IRetailerHasProduct 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.cacheRetailerHasProduct.get(this.id);
		}

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

		if (changedComments)
		{
			CommentBuilder commB = new CommentBuilder(NotebookEntryType.RETAILERHASPRODUCT, this.id);
			if (newComments != null && !newComments.isEmpty())
			{	//save new comments to database
				commB.addComment(null, newComments.toArray(new String[0]));
			}

			if (!editComments.isEmpty())
			{	//modify old comments from database
				commB.changeComment(editComments.values());
			}
			if (deletedComments != null && delComNext != 0)
			{	//delete old comments from database
				deletedComments = Arrays.copyOf(deletedComments, delComNext);
				commB.removeComment(deletedComments);
			}
		}

// mark cache as dirty
		if (!newInstance &&(somethingChanged || changedComments))
		{
			MySQLCache.cacheRetailerHasProduct.remove(this.id);
		}
// populate the cache
		new RetailerHasProductLister().id(this.id).fetch();
		IRetailerHasProduct newValue = MySQLCache.cacheRetailerHasProduct.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.cacheRetailer.get(retailerId).flagChildAdded(newValue);
			MySQLCache.cacheProduct.get(productId).flagChildAdded(newValue);
		}
		else
		{	//	updated
			if (changedRetailerId)
			{
				if (oldInstance != null)
				{
					MySQLCache.cacheRetailer.get(oldInstance.getRetailer().getKey()).flagChildDeleted(oldInstance);
				}
				MySQLCache.cacheRetailer.get(newValue.getRetailer().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;
		changedRetailerId = false;
		changedProductId = false;
		changedSKU = 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.retailerId <= 0)
		{
			LOGGER.debug("retailerId 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);

		return LOGGER.traceExit(log4jEntryMsg, !newInstance);
	}	// 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 retailerhasproduct where retailerHasProductId = " + 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 = 'RP'";
				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.cacheRetailerHasProduct.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.cacheRetailer.get(oldInstance.getRetailer().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 retailerhasproduct set ");
		if (changedRetailerId)
		{
			query.append("retailerId = ?, ");
		}

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

		if (changedSKU)
		{
			query.append("SKU = ?, ");
		}

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

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

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

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

		StringBuilder query = new StringBuilder("insert into retailerhasproduct (");
		query.append("retailerId, ");
		query.append("productId, ");
		if (changedSKU)
		{
			query.append("SKU, ");
		}

		query.replace(query.length()-2, query.length(), ") values (");
		query.append("?, ");
		query.append("?, ");
		if (changedSKU)
		{
			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.retailerId);
			stmt.setInt(paramIx++, this.retailerId);
LOGGER.debug("doInsert(): param {}={}", paramIx, this.productId);
			stmt.setInt(paramIx++, this.productId);
			if (changedSKU) {
				if (this.SKU == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.SKU);
					stmt.setString(paramIx++, this.SKU);
				}
			}

			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

//	/**
//	*	Used to restore a table from a JSON dump.
//	*	The RetailerHasProductBuilder(oldVal) constructor MUST be used with a RetailerHasProduct object created using its JSON constructor.
//	*	All fields (including timestamps) are written to the database, no checks are made.
//	*
//	*	@param	newVal	A RetailerHasProduct object created from a JSON object written as a 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>
//*
//*   @deprecated Use the List<JsonObject> method below which explicitly handles Json dumps
//	*/
//    @Deprecated(since="2.4.0", forRemoval=true)
//	void doJsonInsert(RetailerHasProduct newVal) throws GNDBException
//	{
//		EntryMessage log4jEntryMsg = LOGGER.traceEntry("doJsonInsert(): newVal={}", newVal);
//
//		int currId = newVal.getId();
//		StringBuilder query = new StringBuilder("insert into retailerhasproduct (");
//		if (newVal.getId() > 0)
//		{//this forces the value of the id field.  The >0 test is a bodge.
//			query.append("retailerHasProductId, ");
//		}
//		query.append("retailerId, ");
//		query.append("productId, ");
//		query.append("SKU, ");
//		query.append("lastUpdated, ");
//		query.append("created, ");
//		query.replace(query.length()-2, query.length(), ")");
//        if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.hsqldb && newVal.getId() > 0)
//        {
//            query.append(" overriding system value ");
//        }
//        query.append(" values (");
//		if (newVal.getId() > 0)
//		{//this forces the value of the id field.  The >0 test is a bodge.
//			query.append(newVal.getId()).append(", ");
//		}
//		query.append("?, ");
//		query.append("?, ");
//		query.append("?, ");
//		query.append("?, ");
//		query.append("?, ");
//		query.replace(query.length()-2, query.length(), ")");
//LOGGER.debug("doJsonInsert(): query={}", query.toString());
//
//		try (	Connection conn = DBConnection.getConnection();
//				PreparedStatement stmt = conn.prepareStatement(query.toString());
//				Statement stmt2 = conn.createStatement();	)
//		{
//			int paramIx = 1;
//LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getRetailerId());
//			stmt.setInt(paramIx++, newVal.getRetailerId());
//LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getProductId());
//			stmt.setInt(paramIx++, newVal.getProductId());
//			if (!newVal.getSKU().isPresent())
//			{
//LOGGER.debug("doJsonInsert(): param {} null", paramIx);
//				stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
//			}
//			else
//			{
//LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getSKU().get());
//				stmt.setString(paramIx++, newVal.getSKU().get());
//			}
//
//LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getLastUpdated());
//			stmt.setTimestamp(paramIx++, Timestamp.valueOf(newVal.getLastUpdated()));
//LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getCreated());
//			stmt.setTimestamp(paramIx++, Timestamp.valueOf(newVal.getCreated()));
//			stmt.executeUpdate();
//
//			if (currId <= 0)
//			{
//                ResultSet rs;
//				switch (DBConnection.DB_IN_USE)
//				{
//					case MariaDB, MySQL -> rs = stmt2.executeQuery("select LAST_INSERT_ID()");
//					case hsqldb -> rs = stmt2.executeQuery("call IDENTITY()");
//					default -> {
//						LOGGER.debug("doJsonInsert(): no known rdbms");
//						throw new GNDBException(new IllegalStateException("no known RDBMS"));
//					}
//				}
//				rs.next();
//				currId = rs.getInt(1);
//LOGGER.debug("doJsonInsert(): currId: {}", currId);
//			}
//		} catch (SQLException ex) {
//			LOGGER.error("doJsonInsert(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
//			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
//		}
//
//		if (!newVal.getComments().isEmpty())
//		{
//			CommentBuilder cb = new CommentBuilder(NotebookEntryType.RETAILERHASPRODUCT, currId);
//			for (IComment ct : newVal.getComments())
//			{
//				cb.doJsonInsert((Comment)ct);
//			}
//		}
//
//LOGGER.traceExit(log4jEntryMsg);
//	}	// doJsonInsert

    /**
     * Process the whole JSON array from a DUMP
     * 
     *  @param newVal    a list of JSON objects representing RetailerHasProduct 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 retailerhasproduct (");
        query.append("retailerHasProductId, ");
		query.append("retailerId, ");
		query.append("productId, ");
		query.append("SKU, ");
		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("?) ");
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 retailerhasproduct ON");
            }
            
            for (JsonObject jo : newVal)
            {
                if (!"DUMP".equals(jo.getString("JsonMode", "DUMP")))
                {
                    LOGGER.error("RetailerHasProduct DUMP object is not DUMP");
                    throw new IllegalArgumentException("RetailerHasProduct DUMP object is not DUMP");
                }
                if (!"RetailerHasProduct".equals(jo.getString("JsonNBClass", "RetailerHasProduct")))
                {
                    LOGGER.error("RetailerHasProduct DUMP object is not RetailerHasProduct");
                    throw new IllegalArgumentException("RetailerHasProduct DUMP object is not RetailerHasProduct");
                }
                RetailerHasProduct ps = new RetailerHasProduct(jo);
                if (ps.getId() <= 0)
                {//this forces the value of the id field.  The >0 test is a bodge.
                    LOGGER.error("RetailerHasProduct DUMP object does not have an id");
                    throw new IllegalArgumentException("RetailerHasProduct 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.getRetailerId());
                stmt.setInt(paramIx++, ps.getRetailerId());
                
LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getProductId());
                stmt.setInt(paramIx++, ps.getProductId());
                
                if (ps.getSKU().isEmpty())
                {
LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getSKU().get());
                    stmt.setString(paramIx++, ps.getSKU().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 retailerhasproduct OFF");
                    }
                    CommentBuilder cb = new CommentBuilder(NotebookEntryType.RETAILERHASPRODUCT, ps.getId());
                    cb.doJsonInsert(ps.getComments(), conn);
                    if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
                    {
                        conn.createStatement().execute("SET IDENTITY_INSERT retailerhasproduct 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 retailerhasproduct OFF");
            }
		} catch (SQLException ex) {
			LOGGER.error("doJsonInsert(): 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
	{
		//	this class does not participate in story lines
		return false;
	}	//	hasAncestor()

	@Override
	public StoryLineTree<? extends INotebookEntry> getAncestors() throws GNDBException
	{
		//	this class does not participate in story lines
		return StoryLineTree.emptyTree();
	}	//	getAncestors()

	@Override
	public IRetailerHasProductBuilder ancestor(IPurchaseItem ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(PurchaseItem)

	@Override
	public IRetailerHasProductBuilder ancestor(IGroundwork ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(Groundwork)

	@Override
	public IRetailerHasProductBuilder ancestor(IAfflictionEvent ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(AfflictionEvent)

	@Override
	public IRetailerHasProductBuilder ancestor(IHusbandry ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(Husbandry)

	@Override
	public IRetailerHasProductBuilder ancestor(ISaleItem ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(SaleItem)

	@Override
	public boolean hasDescendant() throws GNDBException
	{
		//	this class does not participate in story lines
		return false;
	}	//	hasDescendant()

	@Override
	public StoryLineTree<? extends INotebookEntry> getDescendants() throws GNDBException
	{
		//	this class does not participate in story lines
		return StoryLineTree.emptyTree();
	}	//	getDescendants()

	@Override
	public boolean addDescendant(IPurchaseItem descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(PurchaseItem)

	@Override
	public boolean addDescendant(IGroundwork descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(Groundwork)

	@Override
	public boolean addDescendant(IAfflictionEvent descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(AfflictionEvent)

	@Override
	public boolean addDescendant(IHusbandry descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(Husbandry)

	@Override
	public boolean addDescendant(ISaleItem descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(SaleItem)

}

