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

/*
	Change log
	2.1.0   Reduce number of debug logs in processResults_MySQL()
    2.1.1   Prepared statement used to shield string values - particularly ' or " characters
	2.2.0   Support hsqldb dialect
    2.3.0   Fix bug in plantSpeciesRequireNull(), productBrandRequireNull()
    2.4.0   Support MS SQLServer
    2.8.0   Allow PlantVariety selection to be forced null
                This is to support editing ShoppingList and PurchaseItem entries
    3.0.0	Use DBKeyHandler
	3.1.0	Use jakarta implementation of JSON
*/


package uk.co.gardennotebook.mysql;

import java.io.File;
import uk.co.gardennotebook.spi.*;

import java.time.LocalDateTime;
import java.util.*;

import java.sql.*;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;
import java.io.FileWriter;
import java.io.IOException;

import jakarta.json.JsonArrayBuilder;
import jakarta.json.JsonBuilderFactory;
import jakarta.json.JsonWriter;
import jakarta.json.JsonWriterFactory;

import jakarta.json.JsonObjectBuilder;

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

	private final DBKeyHandler<IProduct> useProduct = new DBKeyHandler<>("productId");

	private boolean productBrandIdRequireNull = false;
    private final DBKeyHandler<IProductBrand> useProductBrand = new DBKeyHandler<>("productBrandId");

	private boolean plantSpeciesIdRequireNull = false;
	private final DBKeyHandler<IPlantSpecies> usePlantSpecies = new DBKeyHandler<>("plantSpeciesId");

	private boolean plantVarietyIdRequireNull = false;
	private final DBKeyHandler<IPlantVariety> usePlantVariety = new DBKeyHandler<>("plantVarietyId");

	private final DBKeyHandler<IProductCategory> useProductCategory = new DBKeyHandler<>("productCategoryId");

	private String name = null;
	private String nameDetail_1 = null;
	private String nameDetail_2 = null;
	private boolean useName = false;

	private boolean useWhere = false;

	ProductLister() {}

	@Override
    public List<IProduct> fetch() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("fetch()");

		List<IProduct> vals = new ArrayList<>();
		boolean fetchAll = !useWhere;
        String query = "";
        switch (DBConnection.DB_IN_USE)
        {
            case MariaDB, MySQL -> query = buildSQL_MySQL();
            case hsqldb -> query = buildSQL_hsqldb();
            case MSSQLServer -> query = buildSQL_MSSQLServer();
            default -> {
                LOGGER.debug("fetch(): no known rdbms");
                throw new GNDBException(new IllegalStateException("no known RDBMS"));
            }
        }
		LOGGER.debug("fetch(): query: {}", query);
		
		try (   Connection conn = DBConnection.getConnection(); 
				PreparedStatement stmt = conn.prepareStatement(query);
            )
		{
            //  2.1.1
			if (this.useName && name != null && !(name.isEmpty()))
			{
                int ix = 1;
                LOGGER.debug("fetch(): param {}={}", ix, name);
                stmt.setString(ix++, name);
				if (nameDetail_1 != null)
				{
                    LOGGER.debug("fetch(): param {}={}", ix, nameDetail_1);
                    stmt.setString(ix++, nameDetail_1);
				}
				if (nameDetail_2 != null)
				{
                    LOGGER.debug("fetch(): param {}={}", ix, nameDetail_2);
                    stmt.setString(ix++, nameDetail_2);
				}
                
            }
			ResultSet rs = stmt.executeQuery();
            switch (DBConnection.DB_IN_USE)
            {
                case MariaDB, MySQL -> vals = processResults_MySQL(rs);
                case hsqldb -> vals = processResults_hsqldb(rs);
                case MSSQLServer -> vals = processResults_MSSQLServer(rs);
                default -> {
                    LOGGER.debug("fetch(): no known rdbms");
                    throw new GNDBException(new IllegalStateException("no known RDBMS"));
                }
            }
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("fetch(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}

		if (vals.isEmpty()) return Collections.emptyList();

		for (IProduct vx : vals)
		{
			MySQLCache.cacheProduct.putIfAbsent(vx.getKey(), vx);
		}
		if (fetchAll) { MySQLCache.completeProduct = true; }

		populatePlantSpecies(vals, fetchAll);

		populateProductBrand(vals, fetchAll);

		populatePlantVariety(vals, fetchAll);

		populateProductCategory();

        //  2.1.1
        this.useName = false;
        this.name = "";
        this.nameDetail_1 = "";
        this.nameDetail_2 = "";

LOGGER.traceExit(log4jEntryMsg);
		return vals;
	}	// fetch()

    private String buildSQL_MySQL()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from product as d ");
        query.append("left join (select * from comment where ownerType = 'PR') as c ");
        query.append("on d.productId = c.ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);
        }
        query.append(" order by d.productCategoryId, LOWER(d.name), d.productId, c.date");
        return query.toString();
    }   //  buildSQL_MySQL()

    private String buildSQL_hsqldb()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from product as d ");
        query.append("left join (select commentId as c_commentId, ownerId as c_ownerId, date as c_date, comment as c_comment, lastUpdated as c_lastUpdated, created as c_created from comment where ownerType = 'PR') as c ");
        query.append("on d.productId = c_ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);
        }
        query.append(" order by d.productCategoryId, LOWER(d.name), d.productId, c_date");
        return query.toString();
    }   //  buildSQL_hsqldb()

    private String buildSQL_MSSQLServer()
    {
        StringBuilder query = new StringBuilder("select d.productId as d_productId, " +
                                                        "d.productCategoryId as d_productCategoryId, " +
                                                        "d.plantSpeciesId as d_plantSpeciesId, " +
                                                        "d.plantVarietyId as d_plantVarietyId, " +
                                                        "d.productBrandId as d_productBrandId, " +
                                                        "d.name as d_name, " +
                                                        "d.nameDetail_1 as d_nameDetail_1, " +
                                                        "d.nameDetail_2 as d_nameDetail_2, " +
                                                        "d.description as d_description, " +
                                                        "d.lastUpdated as d_lastUpdated, " +
                                                        "d.created as d_created," +
                                                        " c.* from product as d ");
        query.append("left join (select commentId as c_commentId, ownerId as c_ownerId, date as c_date, comment as c_comment, lastUpdated as c_lastUpdated, created as c_created from comment where ownerType = 'PR') as c ");
        query.append("on d.productId = c_ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);
        }
        query.append(" order by d.productCategoryId, LOWER(d.name), d.productId, c_date");
        return query.toString();
    }   //  buildSQL_MSSQLServer()

    private void buildCommonSQL(StringBuilder query)
    {
        boolean first = true;
		if (useProduct.isUsed())
        {
            if (first) query.append(" where ");
            else query.append(" and ");
            query.append(useProduct.getSQLClause("d"));
            first = false;
            useProduct.clear();
        }

        if (this.plantSpeciesIdRequireNull)
        {// if plantVariety is non-null, plantSpecies MUST be non-null
            if (first)
                query.append(" where ");
            else
                query.append(" and");
            query.append(" d.plantSpeciesId is null ");
            first = false;
            this.plantSpeciesIdRequireNull = false;
			usePlantSpecies.clear();
            this.plantVarietyIdRequireNull = false;
			usePlantVariety.clear();
        }
//        else if (this.usePlantSpeciesId || this.usePlantVarietyId)
		else if (this.usePlantSpecies.isUsed() || this.usePlantVariety.isUsed())
        {
            if (first) query.append(" where ");
            else query.append(" and");
			if (this.usePlantSpecies.isUsed() && this.usePlantVariety.isUsed())
				query.append(" ( ");
			if (this.usePlantSpecies.isUsed())
			{
				query.append(usePlantSpecies.getSQLClause("d"));
                if (this.plantVarietyIdRequireNull)
                {
                    query.append(" and d.plantVarietyId is null ");
                }
                first = false;
            }
            if (!this.plantVarietyIdRequireNull)	//	NB this NOT requiresNull
            {
				if (this.usePlantSpecies.isUsed() && this.usePlantVariety.isUsed())
					query.append(" or ");
				if (this.usePlantVariety.isUsed())
				{
					query.append(usePlantVariety.getSQLClause("d"));
					first = false;
				}
            }
			if (this.usePlantSpecies.isUsed() && this.usePlantVariety.isUsed())
				query.append(" ) ");
            this.plantSpeciesIdRequireNull = false;
			this.plantVarietyIdRequireNull = false;
			usePlantSpecies.clear();
			usePlantVariety.clear();
        }
        if (this.productBrandIdRequireNull)
        {
            if (first)
                query.append(" where ");
            else
                query.append(" and");
            query.append(" d.productBrandId is null ");
            first = false;
            this.productBrandIdRequireNull = false;
			useProductBrand.clear();
        }
        else if (useProductBrand.isUsed())
        {
            if (first) query.append(" where ");
            else query.append(" and");
            query.append(useProductBrand.getSQLClause("d"));
            first = false;
            useProductBrand.clear();
        }

		if (useProductCategory.isUsed())
        {
            if (first) query.append(" where ");
            else query.append(" and");
            query.append(useProductCategory.getSQLClause("d"));
            first = false;
            useProductCategory.clear();
        }

        if (this.useName && name != null && !(name.isEmpty()))
        {
            if (first) query.append(" where ");
            else query.append(" and");
            query.append(" d.name = ? ");
            if (nameDetail_1 == null)
            {
                query.append(" and nameDetail_1 is null ");
            }
            else
            {
                query.append(" and nameDetail_1 = ? ");
            }
            if (nameDetail_2 == null)
            {
                query.append(" and nameDetail_2 is null ");
            }
            else
            {
                query.append(" and nameDetail_2 = ? ");
            }
        }
    }   //  buildCommonSQL()

	private List<IProduct> processResults_MySQL(ResultSet rs) throws SQLException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("processResults_MySQL()");

		List<IProduct> tempList = new ArrayList<>();

		Product item = null;

		while (rs.next())
		{
			int productId = rs.getInt("d.productId");
			int productCategoryId = rs.getInt("d.productCategoryId");
			int tmp_plantSpeciesId = rs.getInt("d.plantSpeciesId");
			Integer plantSpeciesId = rs.wasNull() ? null : tmp_plantSpeciesId;
			int tmp_plantVarietyId = rs.getInt("d.plantVarietyId");
			Integer plantVarietyId = rs.wasNull() ? null : tmp_plantVarietyId;
			int tmp_productBrandId = rs.getInt("d.productBrandId");
			Integer productBrandId = rs.wasNull() ? null : tmp_productBrandId;
			String name = rs.getString("d.name");
			String nameDetail_1 = rs.getString("d.nameDetail_1");
			String nameDetail_2 = rs.getString("d.nameDetail_2");
			String description = rs.getString("d.description");
			LocalDateTime lastUpdated = rs.getTimestamp("d.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d.created").toLocalDateTime();
LOGGER.debug("productId: {}, productCategoryId: {}, plantSpeciesId: {}, plantVarietyId: {}, productBrandId: {}, name: {}, nameDetail_1: {}, nameDetail_2: {}, description: {}, lastUpdated: {}, created: {}",
                productId, productCategoryId, plantSpeciesId, plantVarietyId, productBrandId, name, nameDetail_1, nameDetail_2, description, lastUpdated, created); //  2.1.0
			if (item != null && productId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults_MySQL(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("commentId"),
					rs.getInt("ownerId"),
					"PR",
					rs.getDate("c.date").toLocalDate(),
					rs.getString("c.comment"),
					rs.getTimestamp("c.lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c.created").toLocalDateTime());
LOGGER.debug("processResults_MySQL(): extra comment is: {}", comm);
				item = new Product(item, comm);
			}
			else
			{
LOGGER.debug("processResults_MySQL(): got comment for: {}", item);
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c.commentId");   //  note the c. prefix here is lost if server side cacheing of PreparedStatements is used
				if (rs.wasNull())
				{// no comment
					item = new Product(productId, productCategoryId, plantSpeciesId, plantVarietyId, productBrandId, name, nameDetail_1, nameDetail_2, description, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						productId,
						"PR",
						rs.getDate("c.date").toLocalDate(),
						rs.getString("c.comment"),
						rs.getTimestamp("c.lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c.created").toLocalDateTime());
LOGGER.debug("processResults_MySQL(): first comment is: {}", comm);
					item = new Product(productId, productCategoryId, plantSpeciesId, plantVarietyId, productBrandId, name, nameDetail_1, nameDetail_2, description, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

LOGGER.traceExit(log4jEntryMsg);
		return tempList;
	}	// processResults_MySQL()

	private List<IProduct> processResults_hsqldb(ResultSet rs) throws SQLException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("processResults_hsqldb()");

		List<IProduct> tempList = new ArrayList<>();

		Product item = null;

		while (rs.next())
		{
			int productId = rs.getInt("product.productId");
			int productCategoryId = rs.getInt("product.productCategoryId");
			int tmp_plantSpeciesId = rs.getInt("product.plantSpeciesId");
			Integer plantSpeciesId = rs.wasNull() ? null : tmp_plantSpeciesId;
			int tmp_plantVarietyId = rs.getInt("product.plantVarietyId");
			Integer plantVarietyId = rs.wasNull() ? null : tmp_plantVarietyId;
			int tmp_productBrandId = rs.getInt("product.productBrandId");
			Integer productBrandId = rs.wasNull() ? null : tmp_productBrandId;
			String name = rs.getString("product.name");
			String nameDetail_1 = rs.getString("product.nameDetail_1");
			String nameDetail_2 = rs.getString("product.nameDetail_2");
			String description = rs.getString("product.description");
			LocalDateTime lastUpdated = rs.getTimestamp("product.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("product.created").toLocalDateTime();
LOGGER.debug("productId: {}, productCategoryId: {}, plantSpeciesId: {}, plantVarietyId: {}, productBrandId: {}, name: {}, nameDetail_1: {}, nameDetail_2: {}, description: {}, lastUpdated: {}, created: {}",
                productId, productCategoryId, plantSpeciesId, plantVarietyId, productBrandId, name, nameDetail_1, nameDetail_2, description, lastUpdated, created); //  2.1.0
			if (item != null && productId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults_hsqldb(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("c_commentId"),
					rs.getInt("c_ownerId"),
					"PR",
					rs.getDate("c_date").toLocalDate(),
					rs.getString("c_comment"),
					rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults_hsqldb(): extra comment is: {}", comm);
				item = new Product(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new Product(productId, productCategoryId, plantSpeciesId, plantVarietyId, productBrandId, name, nameDetail_1, nameDetail_2, description, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						productId,
						"PR",
						rs.getDate("c_date").toLocalDate(),
						rs.getString("c_comment"),
						rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults_hsqldb(): first comment is: {}", comm);
					item = new Product(productId, productCategoryId, plantSpeciesId, plantVarietyId, productBrandId, name, nameDetail_1, nameDetail_2, description, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

LOGGER.traceExit(log4jEntryMsg);
		return tempList;
	}	// processResults_hsqldb()

	private List<IProduct> processResults_MSSQLServer(ResultSet rs) throws SQLException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("processResults_MSSQLServer()");

		List<IProduct> tempList = new ArrayList<>();

		Product item = null;

		while (rs.next())
		{
			int productId = rs.getInt("d_productId");
			int productCategoryId = rs.getInt("d_productCategoryId");
			int tmp_plantSpeciesId = rs.getInt("d_plantSpeciesId");
			Integer plantSpeciesId = rs.wasNull() ? null : tmp_plantSpeciesId;
			int tmp_plantVarietyId = rs.getInt("d_plantVarietyId");
			Integer plantVarietyId = rs.wasNull() ? null : tmp_plantVarietyId;
			int tmp_productBrandId = rs.getInt("d_productBrandId");
			Integer productBrandId = rs.wasNull() ? null : tmp_productBrandId;
			String name = rs.getString("d_name");
			String nameDetail_1 = rs.getString("d_nameDetail_1");
			String nameDetail_2 = rs.getString("d_nameDetail_2");
			String description = rs.getString("d_description");
			LocalDateTime lastUpdated = rs.getTimestamp("d_lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d_created").toLocalDateTime();
LOGGER.debug("productId: {}, productCategoryId: {}, plantSpeciesId: {}, plantVarietyId: {}, productBrandId: {}, name: {}, nameDetail_1: {}, nameDetail_2: {}, description: {}, lastUpdated: {}, created: {}",
                productId, productCategoryId, plantSpeciesId, plantVarietyId, productBrandId, name, nameDetail_1, nameDetail_2, description, lastUpdated, created); //  2.1.0
			if (item != null && productId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults_hsqldb(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("c_commentId"),
					rs.getInt("c_ownerId"),
					"PR",
					rs.getDate("c_date").toLocalDate(),
					rs.getString("c_comment"),
					rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults_hsqldb(): extra comment is: {}", comm);
				item = new Product(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new Product(productId, productCategoryId, plantSpeciesId, plantVarietyId, productBrandId, name, nameDetail_1, nameDetail_2, description, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						productId,
						"PR",
						rs.getDate("c_date").toLocalDate(),
						rs.getString("c_comment"),
						rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults_hsqldb(): first comment is: {}", comm);
					item = new Product(productId, productCategoryId, plantSpeciesId, plantVarietyId, productBrandId, name, nameDetail_1, nameDetail_2, description, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

LOGGER.traceExit(log4jEntryMsg);
		return tempList;
	}	// processResults_MSSQLServer()

	/*
	*	Populate the parents slot(s)
	*/
	private void populatePlantSpecies(List<IProduct> vals, boolean fetchAll) throws GNDBException
	{
// parent table type: TD
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("populatePlantSpecies()");

		if (fetchAll)
		{
			new PlantSpeciesLister().fetch();
			return;
		}
		int[] keys = vals.stream().
			map(c -> ((Product)c).getPlantSpeciesId()).filter(Objects::nonNull).
			mapToInt(Integer::intValue).filter(c -> c>0).distinct().
			toArray();

		if (keys.length > 0)
		{
			new PlantSpeciesLister().id(keys).fetch();
		}
LOGGER.traceExit(log4jEntryMsg);
	}

	private void populateProductBrand(List<IProduct> vals, boolean fetchAll) throws GNDBException
	{
// parent table type: TD
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("populateProductBrand()");

		if (fetchAll)
		{
			new ProductBrandLister().fetch();
			return;
		}
		int[] keys = vals.stream().
			map(c -> ((Product)c).getProductBrandId()).filter(Objects::nonNull).
			mapToInt(Integer::intValue).filter(c -> c>0).distinct().
			toArray();

		if (keys.length > 0)
		{
			new ProductBrandLister().id(keys).fetch();
		}
LOGGER.traceExit(log4jEntryMsg);
	}

	private void populatePlantVariety(List<IProduct> vals, boolean fetchAll) throws GNDBException
	{
// parent table type: NL
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("populatePlantVariety()");

		if (fetchAll)
		{
			new PlantVarietyLister().fetch();
			return;
		}
		int[] keys = vals.stream().
			map(c -> ((Product)c).getPlantVarietyId()).filter(Objects::nonNull).
			mapToInt(Integer::intValue).filter(c -> c>0).distinct().
			toArray();

		if (keys.length > 0)
		{
			new PlantVarietyLister().id(keys).fetch();
		}
LOGGER.traceExit(log4jEntryMsg);
	}

	private void populateProductCategory() throws GNDBException
	{
// parent table type: TF
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("populateProductCategory()");

// just make sure everything is cached
		new ProductCategoryLister().load();
LOGGER.traceExit(log4jEntryMsg);
	}

	void clear()
	{
		MySQLCache.cacheProduct.clear();
		MySQLCache.completeProduct = false;
	}

	/**
	*Select only the Product entries with these ids.
	*May be called multiple times to extend the list.
	*
	*@param vals	a list of ids
	*@return	 this Lister
	*/
	ProductLister id(int... vals)
	{
		useProduct.id(vals);
        useWhere = useWhere || useProduct.isUsed();
        return this;
	}

	@Override
    public IProductLister product(IProduct... items)
	{
		useProduct.item(items);
        useWhere = useWhere || useProduct.isUsed();
        return this;
	}

	@Override
    public IProductLister product(List<IProduct> items)
	{
		useProduct.item(items);
        useWhere = useWhere || useProduct.isUsed();
        return this;
	}

	@Override
	public IProductLister plantSpeciesRequireNull(boolean requireNull) {
		plantSpeciesIdRequireNull = requireNull;
//		this.usePlantSpeciesId = true;	//	buildSQL makes plantSpeciesIdRequireNull and usePlantSpeciesId mutually exclusive
		useWhere = true;    //  2.3.0
		return this;
	}

	@Override
	public IProductLister plantSpecies(IPlantSpecies... items)
	{
		usePlantSpecies.item(items);
		useWhere = useWhere || usePlantSpecies.isUsed();
		return this;
	}

	@Override
	public IProductLister plantSpecies(List<IPlantSpecies> items)
	{
		usePlantSpecies.item(items);
		useWhere = useWhere || usePlantSpecies.isUsed();
		return this;
	}

	@Override
	public IProductLister productBrandRequireNull(boolean requireNull) {
		productBrandIdRequireNull = requireNull;
//		this.useProductBrandId = true;  //  productBrandIdRequireNull obviates useProductBrand in SQL generation
		useWhere = true;    //  2.3.0
		return this;
	}

	@Override
	public IProductLister productBrand(IProductBrand... items)
	{
		useProductBrand.item(items);
        useWhere = useWhere || useProductBrand.isUsed();
        return this;
	}

	@Override
	public IProductLister productBrand(List<IProductBrand> items)
	{
		useProductBrand.item(items);
        useWhere = useWhere || useProductBrand.isUsed();
        return this;
	}

	@Override
	public IProductLister plantVarietyRequireNull(boolean requireNull) {
		plantVarietyIdRequireNull = requireNull;
//		this.usePlantVarietyId = true;	//	buildCommonSQL makes plantVarietyIdRequireNull and usePlantVarietyId mutually exclusive
		useWhere = true;    //  2.3.0
		return this;
	}

	@Override
	public IProductLister plantVariety(IPlantVariety... items)
	{
		usePlantVariety.item(items);
		useWhere = useWhere || usePlantVariety.isUsed();
		return this;
	}

	@Override
	public IProductLister plantVariety(List<IPlantVariety> items)
	{
		usePlantVariety.item(items);
		useWhere = useWhere || usePlantVariety.isUsed();
		return this;
	}

	@Override
	public IProductLister productCategory(IProductCategory... items)
	{
		useProductCategory.item(items);
        useWhere = useWhere || useProductCategory.isUsed();
        return this;
	}

	@Override
	public IProductLister productCategory(List<IProductCategory> items)
	{
		useProductCategory.item(items);
        useWhere = useWhere || useProductCategory.isUsed();
        return this;
	}

	@Override
	public IProductLister name(String name, String nameDetail_1, String nameDetail_2)
	{
		if ((name == null) || name.isEmpty())
		{
			this.name = null;
			this.nameDetail_1 = null;
			this.nameDetail_2 = null;
			useName = false;
			return this;
		}
		this.name = name;
		if ((nameDetail_1 == null) || nameDetail_1.isEmpty())
		{
			this.nameDetail_1 = null;
		}
		else
		{
			this.nameDetail_1 = nameDetail_1;
		}
		if ((nameDetail_2 == null) || nameDetail_2.isEmpty())
		{
			this.nameDetail_2 = null;
		}
		else
		{
			this.nameDetail_2 = nameDetail_2;
		}
		useName = true;
		useWhere = true;
		return this;
	}

	void toJson(JsonBuilderFactory builderFactory, JsonWriterFactory writerFactory, File dumpDirectory) throws GNDBException
	{
		useWhere = false;
		fetch();

		JsonArrayBuilder jsonHc = builderFactory.createArrayBuilder();
		for (IProduct ihc : MySQLCache.cacheProduct.values())
		{
			Product hc = (Product)ihc;
			jsonHc.add(hc.toJson(builderFactory));
		}
        
        JsonObjectBuilder job = builderFactory.createObjectBuilder();
        job.add("JsonMode", "DUMP");
        job.add("JsonNBClass", "Product");
        job.add("values", jsonHc);
        
		try (JsonWriter writer = writerFactory.createWriter(new FileWriter(new File(dumpDirectory, "Product.json"), false)))
		{
			writer.writeObject(job.build());
		} catch (IOException ex) {
			LOGGER.error("toJson(): IOException", ex);
		}
	}	// toJson

}
