/*
 * Copyright (C) 2018, 2019, 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.4.0   Support MS SQLServer
	3.0.0	Support converting ToDoList items (back) into Reminders to delay the action.
			Support 'watch-for' entries from PurchaseItems.
			Use DBKeyHandler.
	3.1.0	Use jakarta implementation of JSON
 */

package uk.co.gardennotebook.mysql;

import uk.co.gardennotebook.spi.*;

import java.time.LocalDate;
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 java.io.File;

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

	private final DBKeyHandler<IReminder> useReminder = new DBKeyHandler<>("reminderId");

	private final DBKeyHandler<IGroundworkActivity> useGroundworkActivity = new DBKeyHandler<>("groundworkActivityId");

	private final DBKeyHandler<IHusbandryClass> useHusbandryClass = new DBKeyHandler<>("husbandryClassId");

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

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

	private final DBKeyHandler<IHusbandry> useHusbandry = new DBKeyHandler<>("husbandryId");

	private final DBKeyHandler<IPurchaseItem> usePurchaseItem = new DBKeyHandler<>("purchaseItemId");

	private boolean useFromShowFrom = false;
	private LocalDate fromShowFrom;
	private boolean useToShowFrom = false;
	private LocalDate toShowFrom;

	private boolean useFromRepeatUntil = false;
	private LocalDate fromRepeatUntil;
	private boolean useToRepeatUntil = false;
	private LocalDate toRepeatUntil;

	private boolean useWhere = false;

	ReminderLister() {}

	/**
	*	Create any new ToDoList items from Reminders which have come due
	*/
	void buildToDoList() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("buildToDoList()");

        String query = "";
		switch (DBConnection.DB_IN_USE)
		{
			case MariaDB, MySQL -> query = "call loadtodolist()";
			case hsqldb -> query = "call LoadTodoList()";
			case MSSQLServer -> query = "LoadTodoList";
			default -> {
				LOGGER.error("fetch(): no known rdbms");
				throw new GNDBException(new IllegalStateException("ReminderLister: buildToDoList(): no known RDBMS"));
			}
		}
LOGGER.debug("buildToDoList(): query: {}", query);

        try (	Connection conn = DBConnection.getConnection();
				CallableStatement stmt = conn.prepareCall(query);)
		{
			stmt.execute();
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("buildToDoList(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}
LOGGER.traceExit(log4jEntryMsg);
	}	//buildToDoList

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

		List<IReminder> 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.error("fetch(): no known rdbms");
				throw new GNDBException(new IllegalStateException("ReminderLister: fetch(): no known RDBMS"));
			}
		}
LOGGER.debug("fetch(): query: {}", query);

		try (Connection conn = DBConnection.getConnection(); Statement stmt = conn.createStatement();)
		{
			ResultSet rs = stmt.executeQuery(query);
			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.error("fetch(): no known rdbms");
					throw new GNDBException(new IllegalStateException("ReminderLister: fetch(): 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 (IReminder vx : vals)
		{
			MySQLCache.cacheReminder.putIfAbsent(vx.getKey(), vx);
		}
		if (fetchAll) { MySQLCache.completeReminder = true; }

		populateGroundworkActivity();

		populateHusbandryClass();

		populateHusbandry(vals, false);

		populatePurchaseItem(vals, false);

		populatePlantSpecies(vals, fetchAll);

		populatePlantVariety(vals, fetchAll);

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

    private String buildSQL_MySQL()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from reminder as d ");
        query.append("left join (select * from comment where ownerType = 'RM') as c ");
        query.append("on d.reminderId = c.ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);
        }
        query.append(" order by d.showFrom, d.repeatUntil, d.singleShot, d.reminderId, c.date");
        return query.toString();
    }   //  buildSQL_MySQL()

    private String buildSQL_hsqldb()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from reminder 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 = 'RM') as c ");
        query.append("on d.reminderId = c_ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query); //				first = false;
        }
        query.append(" order by d.showFrom, d.repeatUntil, d.singleShot, d.reminderId, c_date");
        return query.toString();
    }   //  buildSQL_hsqldb()

    private String buildSQL_MSSQLServer()
    {
        StringBuilder query = new StringBuilder("select d.reminderId as d_reminderId, " +
                                                        "d.plantSpeciesId as d_plantSpeciesId, " +
                                                        "d.plantVarietyId as d_plantVarietyId, " +
                                                        "d.husbandryClassId as d_husbandryClassId, " +
                                                        "d.groundWorkActivityId as d_groundWorkActivityId, " +
														"d.husbandryId as d_husbandryId, " +
														"d.purchaseItemId as d_purchaseItemId, " +
                                                        "d.showFrom as d_showFrom, " +
                                                        "d.singleShot as d_singleShot, " +
                                                        "d.repeatInterval as d_repeatInterval, " +
                                                        "d.repeatQuantifier as d_repeatQuantifier, " +
                                                        "d.repeatUntil as d_repeatUntil, " +
                                                        "d.description as d_description, " +
                                                        "d.lastUpdated as d_lastUpdated, " +
                                                        "d.created as d_created, c.* from reminder 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 = 'RM') as c ");
        query.append("on d.reminderId = c_ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query); //				first = false;
        }
        query.append(" order by d.showFrom, d.repeatUntil, d.singleShot, d.reminderId, c_date");
        return query.toString();
    }   //  buildSQL_MSSQLServer()

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

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

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

//		if (this.usePlantSpeciesId || this.usePlantVarietyId)
		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"));
				first = false;
			}

			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(" ) ");

			usePlantSpecies.clear();
			usePlantVariety.clear();
        }

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

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

		if (this.useFromShowFrom)
        {
            if (first) query.append(" where ");
            else query.append(" and");

            query.append(" d.showFrom >= '").append(this.fromShowFrom).append("'");
            first = false;
            this.useFromShowFrom = false;
        }
        if (this.useToShowFrom)
        {
            if (first) query.append(" where ");
            else query.append(" and");

            query.append(" d.showFrom <= '").append(this.toShowFrom).append("'");
            first = false;
            this.useToShowFrom = false;
        }
        if (this.useFromRepeatUntil)
        {
            if (first) query.append(" where ");
            else query.append(" and");

            query.append(" d.repeatUntil >= '").append(this.fromRepeatUntil).append("'");
            first = false;
            this.useFromRepeatUntil = false;
        }
        if (this.useToRepeatUntil)
        {
            if (first) query.append(" where ");
            else query.append(" and");

            query.append(" d.repeatUntil <= '").append(this.toRepeatUntil).append("'");
            //				first = false;
            this.useToRepeatUntil = false;
        }
    }   //  buildCommonSQL()

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

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

		Reminder item = null;

		while (rs.next())
		{
			int reminderId = rs.getInt("d.reminderId");
			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_husbandryClassId = rs.getInt("d.husbandryClassId");
			Integer husbandryClassId = rs.wasNull() ? null : tmp_husbandryClassId;
			int tmp_groundWorkActivityId = rs.getInt("d.groundWorkActivityId");
			Integer groundWorkActivityId = rs.wasNull() ? null : tmp_groundWorkActivityId;

			int tmp_husbandryId = rs.getInt("d.husbandryId");
			Integer husbandryId = rs.wasNull() ? null : tmp_husbandryId;

			int tmp_purchaseItemId = rs.getInt("d.purchaseItemId");
			Integer purchaseItemId = rs.wasNull() ? null : tmp_purchaseItemId;

			LocalDate showFrom = rs.getDate("d.showFrom").toLocalDate();
			boolean singleShot = rs.getBoolean("d.singleShot");
			String repeatInterval = rs.getString("d.repeatInterval");
			int tmp_repeatQuantifier = rs.getInt("d.repeatQuantifier");
			Integer repeatQuantifier = rs.wasNull() ? null : tmp_repeatQuantifier;
			LocalDate repeatUntil = (rs.getDate("d.repeatUntil") == null) ? null : rs.getDate("d.repeatUntil").toLocalDate();
			String description = rs.getString("d.description");
			LocalDateTime lastUpdated = rs.getTimestamp("d.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d.created").toLocalDateTime();
LOGGER.debug("reminderId: {}, plantSpeciesId: {}, plantVarietyId: {}, husbandryClassId: {}, groundWorkActivityId: {}, " +
					"purchaseItemId: {} , husbandryId: {}, " +
                    "showFrom: {}, singleShot: {}, repeatInterval: {}, repeatQuantifier: {}, repeatUntil: {}, description: {}, lastUpdated: {}, created: {}",
                reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId,
                    purchaseItemId, husbandryId,
                    showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created);
			if (item != null && reminderId == 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"),
					"RM",
					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 Reminder(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c.commentId");
				if (rs.wasNull())
				{// no comment
//					item = new Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId, showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created);
					item = new Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId,
							husbandryId, purchaseItemId,
							showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						reminderId,
						"RM",
						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 Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId, showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created, comm);
					item = new Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId,
							husbandryId, purchaseItemId,
							showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

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

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

		Reminder item = null;

		while (rs.next())
		{
			int reminderId = rs.getInt("reminder.reminderId");
			int tmp_plantSpeciesId = rs.getInt("reminder.plantSpeciesId");
			Integer plantSpeciesId = rs.wasNull() ? null : tmp_plantSpeciesId;
			int tmp_plantVarietyId = rs.getInt("reminder.plantVarietyId");
			Integer plantVarietyId = rs.wasNull() ? null : tmp_plantVarietyId;
			int tmp_husbandryClassId = rs.getInt("reminder.husbandryClassId");
			Integer husbandryClassId = rs.wasNull() ? null : tmp_husbandryClassId;
			int tmp_groundWorkActivityId = rs.getInt("reminder.groundWorkActivityId");
			Integer groundWorkActivityId = rs.wasNull() ? null : tmp_groundWorkActivityId;

			int tmp_husbandryId = rs.getInt("reminder.husbandryId");
			Integer husbandryId = rs.wasNull() ? null : tmp_husbandryId;

			int tmp_purchaseItemId = rs.getInt("reminder.purchaseItemId");
			Integer purchaseItemId = rs.wasNull() ? null : tmp_purchaseItemId;

			LocalDate showFrom = rs.getDate("reminder.showFrom").toLocalDate();
			boolean singleShot = rs.getBoolean("reminder.singleShot");
			String repeatInterval = rs.getString("reminder.repeatInterval");
			int tmp_repeatQuantifier = rs.getInt("reminder.repeatQuantifier");
			Integer repeatQuantifier = rs.wasNull() ? null : tmp_repeatQuantifier;
			LocalDate repeatUntil = (rs.getDate("reminder.repeatUntil") == null) ? null : rs.getDate("reminder.repeatUntil").toLocalDate();
			String description = rs.getString("reminder.description");
			LocalDateTime lastUpdated = rs.getTimestamp("reminder.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("reminder.created").toLocalDateTime();
LOGGER.debug("reminderId: {}, plantSpeciesId: {}, plantVarietyId: {}, husbandryClassId: {}, groundWorkActivityId: {}, " +
					"purchaseItemId: {}, husbandryId: {}, " +
                    "showFrom: {}, singleShot: {}, repeatInterval: {}, repeatQuantifier: {}, repeatUntil: {}, description: {}, lastUpdated: {}, created: {}",
                reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId,
				purchaseItemId, husbandryId,
                    showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created);
			if (item != null && reminderId == 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"),
					"RM",
					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 Reminder(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
//					item = new Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId, showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created);
					item = new Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId,
							husbandryId, purchaseItemId,
							showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						reminderId,
						"RM",
						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 Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId, showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created, comm);
					item = new Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId,
							husbandryId, purchaseItemId,
							showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

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

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

		Reminder item = null;

		while (rs.next())
		{
			int reminderId = rs.getInt("d_reminderId");
			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_husbandryClassId = rs.getInt("d_husbandryClassId");
			Integer husbandryClassId = rs.wasNull() ? null : tmp_husbandryClassId;
			int tmp_groundWorkActivityId = rs.getInt("d_groundWorkActivityId");
			Integer groundWorkActivityId = rs.wasNull() ? null : tmp_groundWorkActivityId;

			int tmp_husbandryId = rs.getInt("d_husbandryId");
			Integer husbandryId = rs.wasNull() ? null : tmp_husbandryId;

			int tmp_purchaseItemId = rs.getInt("d_purchaseItemId");
			Integer purchaseItemId = rs.wasNull() ? null : tmp_purchaseItemId;

			LocalDate showFrom = rs.getDate("d_showFrom").toLocalDate();
			boolean singleShot = rs.getBoolean("d_singleShot");
			String repeatInterval = rs.getString("d_repeatInterval");
			int tmp_repeatQuantifier = rs.getInt("d_repeatQuantifier");
			Integer repeatQuantifier = rs.wasNull() ? null : tmp_repeatQuantifier;
			LocalDate repeatUntil = (rs.getDate("d_repeatUntil") == null) ? null : rs.getDate("d_repeatUntil").toLocalDate();
			String description = rs.getString("d_description");
			LocalDateTime lastUpdated = rs.getTimestamp("d_lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d_created").toLocalDateTime();
LOGGER.debug("reminderId: {}, plantSpeciesId: {}, plantVarietyId: {}, husbandryClassId: {}, groundWorkActivityId: {}, " +
					"purchaseItemId: {}, husbandryId: {}, " +
                    "showFrom: {}, singleShot: {}, repeatInterval: {}, repeatQuantifier: {}, repeatUntil: {}, description: {}, lastUpdated: {}, created: {}",
                reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId,
				purchaseItemId, husbandryId,
                    showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created);
			if (item != null && reminderId == 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"),
					"RM",
					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 Reminder(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
//					item = new Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId, showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created);
					item = new Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId,
							husbandryId, purchaseItemId,
							showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						reminderId,
						"RM",
						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 Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId, showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created, comm);
					item = new Reminder(reminderId, plantSpeciesId, plantVarietyId, husbandryClassId, groundWorkActivityId,
							husbandryId, purchaseItemId,
							showFrom, singleShot, repeatInterval, repeatQuantifier, repeatUntil, description, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

	/*
	*	Populate the parents slot(s)
	*/
	private void populateGroundworkActivity() throws GNDBException
	{
// parent table type: TF
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("populateGroundworkActivity()");

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

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

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

	private void populatePlantSpecies(List<IReminder> 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 -> ((Reminder)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 populatePlantVariety(List<IReminder> 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 -> ((Reminder)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 populateHusbandry(List<IReminder> vals, boolean fetchAll) throws GNDBException
	{
// parent table type: NL
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("populateHusbandry()");

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

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

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

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

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

	void clear()
	{
		MySQLCache.cacheReminder.clear();
		MySQLCache.completeReminder = false;
	}

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

	@Override
	public IReminderLister reminder(IReminder... items)
	{
		useReminder.item(items);
		useWhere = useWhere || useReminder.isUsed();
		return this;
	}

	@Override
	public IReminderLister reminder(List<IReminder> items)
	{
		useReminder.item(items);
		useWhere = useWhere || useReminder.isUsed();
		return this;
	}

	@Override
	public IReminderLister groundworkActivity(IGroundworkActivity... items)
	{
		useGroundworkActivity.item(items);
		useWhere = useWhere || useGroundworkActivity.isUsed();
		return this;
	}

	@Override
	public IReminderLister groundworkActivity(List<IGroundworkActivity> items)
	{
		useGroundworkActivity.item(items);
		useWhere = useWhere || useGroundworkActivity.isUsed();
		return this;
	}

	@Override
	public IReminderLister husbandryClass(IHusbandryClass... items)
	{
		useHusbandryClass.item(items);
		useWhere = useWhere || useHusbandryClass.isUsed();
		return this;
	}

	@Override
	public IReminderLister husbandryClass(List<IHusbandryClass> items)
	{
		useHusbandryClass.item(items);
		useWhere = useWhere || useHusbandryClass.isUsed();
		return this;
	}

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

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

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

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

	@Override
	public IReminderLister husbandry(IHusbandry... items)
	{
		useHusbandry.item(items);
		useWhere = useWhere || useHusbandry.isUsed();
		return this;
	}

	@Override
	public IReminderLister husbandry(List<IHusbandry> items)
	{
		useHusbandry.item(items);
		useWhere = useWhere || useHusbandry.isUsed();
		return this;
	}

	@Override
	public IReminderLister purchaseItem(IPurchaseItem... items)
	{
		usePurchaseItem.item(items);
		useWhere = useWhere || usePurchaseItem.isUsed();
		return this;
	}

	@Override
	public IReminderLister purchaseItem(List<IPurchaseItem> items)
	{
		usePurchaseItem.item(items);
		useWhere = useWhere || usePurchaseItem.isUsed();
		return this;
	}

	@Override
	public IReminderLister fromShowFrom(LocalDate item)
	{
		if (item == null) return this;
		this.fromShowFrom = item;
		this.useFromShowFrom = true;
		this.useWhere = true;
		return this;
	}

	@Override
	public IReminderLister toShowFrom(LocalDate item)
	{
		if (item == null) return this;
		this.toShowFrom = item;
		this.useToShowFrom = true;
		this.useWhere = true;
		return this;
	}

	@Override
	public IReminderLister fromRepeatUntil(LocalDate item)
	{
		if (item == null) return this;
		this.fromRepeatUntil = item;
		this.useFromRepeatUntil = true;
		this.useWhere = true;
		return this;
	}

	@Override
	public IReminderLister toRepeatUntil(LocalDate item)
	{
		if (item == null) return this;
		this.toRepeatUntil = item;
		this.useToRepeatUntil = true;
		this.useWhere = true;
		return this;
	}

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

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

}
