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

/*
	Change log
	2.0.1   Fixed bug scanning for request by id.
            Initialise cache to reduce rehashing.
            Change from old 'key()' to 'plantSpecies() selection  
            Optimise single item selection
	2.2.0   Support hsqldb dialect
    2.4.0   Support MS SQLServer
    2.8.1   Code tidy
    3.0.0	Add CropRotationGroup
	3.1.0	Use jakarta implementation of JSON
 */

package uk.co.gardennotebook.mysql;

import uk.co.gardennotebook.spi.*;

import java.util.List;
import java.util.Arrays;
import java.util.ArrayList;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
import java.time.LocalDateTime;

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 java.util.HashMap;

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

/**
*{@inheritDoc}
*
*	@author	Andy Gegg
*	@version	3.1.0
*	@since	1.0
*/

final class PlantSpeciesLister implements IPlantSpeciesLister
{
	private static final Logger LOGGER = LogManager.getLogger();

	private boolean useCommonName = false;
	private String[] commonNameList = new String[10];
	private int commonNameNext = 0;	// next free slot in list

	private boolean useId = false;
	private int[] idList = new int[10];
	private int idNext = 0;	// next free slot in list

	private final DBKeyHandler<ICropRotationGroup> useCropRotationGroup = new DBKeyHandler<>("cropRotationGroupId");

	private boolean useWhere = false;

	PlantSpeciesLister() {}

	@Override
	public List<IPlantSpecies> fetch() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("fetch(): useId: {}, useCommonName: {}, useCropRotationGroup: {}", useId, useCommonName, useCropRotationGroup.isUsed());

        if (MySQLCache.cachePlantSpecies == null || MySQLCache.cachePlantSpecies.isEmpty()) //  2.0.1
        {
            initialiseCache();
        }
        
		boolean needRead = MySQLCache.invalidPlantSpecies;
		needRead = (useCommonName && !MySQLCache.completePlantSpecies) || needRead;
		//force read if cache is marked incomplete but whole set is required
		needRead = (!useWhere && !MySQLCache.completePlantSpecies) || needRead;

		LOGGER.debug("needRead: {}", needRead);
        // check for request by id not already met
		if (useId && !MySQLCache.completePlantSpecies)
        {
			for(int jx = 0; jx<idNext; jx++)
            {
				if (!MySQLCache.cachePlantSpecies.containsKey(idList[jx]))  //  2.0.1
                {
					needRead = true;
					break;
				}
			}
		}
        
        //  handle most common case - single species by id  //  2.0.1
//		if(!needRead && useId && idNext==1 && !useCommonName)
		if(!needRead && useId && idNext==1 && !useCommonName && !useCropRotationGroup.isUsed())
        {
			LOGGER.debug("get single cached PS");
//            var listOfOne = new ArrayList<IPlantSpecies>();
//            listOfOne.add(MySQLCache.cachePlantSpecies.get(idList[0]));
//            return listOfOne;
			return List.<IPlantSpecies>of(MySQLCache.cachePlantSpecies.get(idList[0]));
        }
        
		if (needRead)
		{
			// make sure CropRotationGroup cache is loaded
			new CropRotationGroupLister().load();

			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);
			List<PlantSpecies> tempList = new ArrayList<>();

			try (Connection conn = DBConnection.getConnection(); Statement stmt = conn.createStatement();)
			{
				ResultSet rs = null;
				if (this.useCommonName)
				{
					PreparedStatement pstmt = conn.prepareStatement(query);
					int pstmtIx = 1;
					if (this.useCommonName)
					{
						for(int ix =0; ix < commonNameNext; ix++)
						{
LOGGER.debug("fetch(): pstmtIx: {} = {}", pstmtIx, commonNameList[ix].toLowerCase());
							pstmt.setString(pstmtIx++, commonNameList[ix].toLowerCase());}
					}
					rs = pstmt.executeQuery();
				}
				else
				{
					rs = stmt.executeQuery(query);
				}

				switch (DBConnection.DB_IN_USE)
				{
					case MariaDB, MySQL -> tempList = processResults_MySQL(rs);
					case hsqldb -> tempList = processResults_hsqldb(rs);
					case MSSQLServer -> tempList = processResults_MSSQLServer(rs);
					default -> {
						LOGGER.debug("fetch(): no known rdbms");
						throw new GNDBException(new IllegalStateException("no known RDBMS"));
					}
				}
				stmt.close();
				if (!useWhere)
				{
					MySQLCache.completePlantSpecies = true;
				}
			}catch (SQLException ex) {
				LOGGER.error("fetch(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
				throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
			}
			for (PlantSpecies ps : tempList) {
				MySQLCache.cachePlantSpecies.putIfAbsent(ps.getId(), ps);
			}
		}

//		if(!useId && !useCommonName) {
		if(!useId && !useCommonName && !useCropRotationGroup.isUsed()) {
			useId = false;
			idNext = 0;
			useCommonName = false;
			commonNameNext = 0;
			useCropRotationGroup.clear();
			MySQLCache.invalidPlantSpecies = false;
			return MySQLCache.cachePlantSpecies.values().stream().
					sorted((a, b) -> a.getCommonName().compareToIgnoreCase(b.getCommonName())).collect(Collectors.toList());
		}

		List<IPlantSpecies> outList = new ArrayList<>();

		idList = Arrays.copyOf(idList, idNext);

		if (useCommonName) {
			commonNameList = Arrays.copyOf(commonNameList, commonNameNext);
			int [] nameKeys = MySQLCache.cachePlantSpecies.values().stream().
					filter(c -> Arrays.stream(commonNameList).anyMatch(p -> p.equals(c.getCommonName())))
					.mapToInt(INotebookEntry::getKey).
					toArray();
			idList = IntStream.concat(Arrays.stream(nameKeys), Arrays.stream(idList)).toArray();
		}

		if (useCropRotationGroup.isUsed()) {
			int[] crgIds = useCropRotationGroup.getIds();
			int [] nameKeys = MySQLCache.cachePlantSpecies.values().stream().
					filter(c -> c.getCropRotationGroup().isPresent()).
					filter(c -> Arrays.stream(crgIds).anyMatch(p -> p == c.getCropRotationGroup().get().getKey())).
					mapToInt(INotebookEntry::getKey).
					toArray();
			idList = IntStream.concat(Arrays.stream(nameKeys), Arrays.stream(idList)).toArray();
		}

		idList = Arrays.stream(idList).distinct().toArray();

		for (int ix : idList)
		{
			outList.add(MySQLCache.cachePlantSpecies.get(ix));
		}

		useWhere = false;
		useId = false;
		idNext = 0;
		useCommonName = false;
		commonNameNext = 0;
		useCropRotationGroup.clear();
		MySQLCache.invalidPlantSpecies = false;

LOGGER.traceExit(log4jEntryMsg);
		return outList.stream().sorted((a,b) -> a.getCommonName().compareToIgnoreCase(b.getCommonName())).collect(Collectors.toList());
	}	// fetch()

    private String buildSQL_MySQL()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from plantspecies as d ");
        query.append("left join (select * from comment where ownerType = 'PS') as c ");
        query.append("on d.plantSpeciesId = c.ownerId ");
        buildCommonSQL(query);
        query.append(" order by LOWER(d.commonName), c.date");
        return query.toString();
    }

    private String buildSQL_hsqldb()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from plantspecies 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 = 'PS') as c ");
        query.append("on d.plantSpeciesId = c_ownerId ");
        buildCommonSQL(query);
        query.append(" order by LOWER(d.commonName), c_date");
        return query.toString();
    }

    private String buildSQL_MSSQLServer()
    {
        StringBuilder query = new StringBuilder("select d.plantSpeciesId as d_plantSpeciesId," +
														"d.cropRotationGroupId as d_cropRotationGroupId," +
                                                        "d.commonName as d_commonName," +
                                                        "d.latinName as d_latinName," +
                                                        "d.description as d_description," +
                                                        "d.utility as d_utility," +
                                                        "d.hardiness as d_hardiness," +
                                                        "d.lifeType as d_lifeType," +
                                                        "d.plantType as d_plantType," +
                                                        "d.lastUpdated as d_lastUpdated," +
                                                        "d.created as d_created," +
                                                        " c.* from plantspecies 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 = 'PS') as c ");
        query.append("on d.plantSpeciesId = c_ownerId ");
        buildCommonSQL(query);
        query.append(" order by LOWER(d.commonName), c_date");
        return query.toString();
    }

    /*
    *   @since  2.8.1
    */
    private void buildCommonSQL(StringBuilder query)
    {
        if (useWhere)
        {
            boolean first = true;
            if (this.useId)
            {
                if (first) query.append(" where ");
                else query.append(" and");
                query.append(" d.plantSpeciesId in (");
                for(int ix = 0; ix < idNext; ix++) { query.append(idList[ix]).append(", "); }
                first = false;
                query.replace(query.length()-2, query.length(), ") ");
            }
            if (this.useCommonName)
            {
                if (first) query.append(" where ");
                else query.append(" and");
                if (commonNameNext > 1)
                {
                    query.append(" LOWER(d.commonName) in (");
					query.append("?, ".repeat(commonNameNext));
                    query.replace(query.length()-2, query.length(), ") ");
                }
                else
                    query.append(" LOWER(d.commonName) = ?");
            }

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

		}
    }   //  buildCommonSQL()

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

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

		PlantSpecies item = null;

		while (rs.next()) {
			int plantSpeciesId = rs.getInt("d.plantSpeciesId");
			int tmp_cropRotationGroupId = rs.getInt("d.cropRotationGroupId");
			Integer cropRotationGroupId = rs.wasNull() ? null : tmp_cropRotationGroupId;
			String commonName = rs.getString("d.commonName");
			String latinName = rs.getString("d.latinName");
			String description = rs.getString("d.description");
			String utility = rs.getString("d.utility");
			String hardiness = rs.getString("d.hardiness");
			String lifeType = rs.getString("d.lifeType");
			String plantType = rs.getString("d.plantType");
			LocalDateTime lastUpdated = rs.getTimestamp("d.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d.created").toLocalDateTime();
			if (item != null && plantSpeciesId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("c.commentId"),
					rs.getInt("c.ownerId"),
					"PS",
					rs.getDate("c.date").toLocalDate(),
					rs.getString("c.comment"),
					rs.getTimestamp("c.lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c.created").toLocalDateTime());
LOGGER.debug("processResults(): extra comment is: {}", comm);
				item = new PlantSpecies(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c.commentId");
				if (rs.wasNull())
				{// no comment
					item = new PlantSpecies(plantSpeciesId, cropRotationGroupId, commonName, latinName, description, utility, hardiness, lifeType, plantType, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						plantSpeciesId,
						"PS",
						rs.getDate("c.date").toLocalDate(),
						rs.getString("c.comment"),
						rs.getTimestamp("c.lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c.created").toLocalDateTime());
LOGGER.debug("processResults(): first comment is: {}", comm);
					item = new PlantSpecies(plantSpeciesId, cropRotationGroupId, commonName, latinName, description, utility, hardiness, lifeType, plantType, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

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

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

		PlantSpecies item = null;
        
		while (rs.next()) {
			int plantSpeciesId = rs.getInt("d_plantSpeciesId");
			int tmp_cropRotationGroupId = rs.getInt("d_cropRotationGroupId");
			Integer cropRotationGroupId = rs.wasNull() ? null : tmp_cropRotationGroupId;
			String commonName = rs.getString("d_commonName");
			String latinName = rs.getString("d_latinName");
			String description = rs.getString("d_description");
			String utility = rs.getString("d_utility");
			String hardiness = rs.getString("d_hardiness");
			String lifeType = rs.getString("d_lifeType");
			String plantType = rs.getString("d_plantType");
			LocalDateTime lastUpdated = rs.getTimestamp("d_lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d_created").toLocalDateTime();
			if (item != null && plantSpeciesId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("c_commentId"),
					rs.getInt("c_ownerId"),
					"PS",
					rs.getDate("c_date").toLocalDate(),
					rs.getString("c_comment"),
					rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults(): extra comment is: {}", comm);
				item = new PlantSpecies(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new PlantSpecies(plantSpeciesId, cropRotationGroupId, commonName, latinName, description, utility, hardiness, lifeType, plantType, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						plantSpeciesId,
						"PS",
						rs.getDate("c_date").toLocalDate(),
						rs.getString("c_comment"),
						rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults(): first comment is: {}", comm);
					item = new PlantSpecies(plantSpeciesId, cropRotationGroupId, commonName, latinName, description, utility, hardiness, lifeType, plantType, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

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

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

		PlantSpecies item = null;

		while (rs.next()) {
			int plantSpeciesId = rs.getInt("plantspecies.plantSpeciesId");
			int tmp_cropRotationGroupId = rs.getInt("plantspecies.cropRotationGroupId");
			Integer cropRotationGroupId = rs.wasNull() ? null : tmp_cropRotationGroupId;
			String commonName = rs.getString("plantspecies.commonName");
			String latinName = rs.getString("plantspecies.latinName");
			String description = rs.getString("plantspecies.description");
			String utility = rs.getString("plantspecies.utility");
			String hardiness = rs.getString("plantspecies.hardiness");
			String lifeType = rs.getString("plantspecies.lifeType");
			String plantType = rs.getString("plantspecies.plantType");
			LocalDateTime lastUpdated = rs.getTimestamp("plantspecies.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("plantspecies.created").toLocalDateTime();
			if (item != null && plantSpeciesId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("c_commentId"),
					rs.getInt("c_ownerId"),
					"PS",
					rs.getDate("c_date").toLocalDate(),
					rs.getString("c_comment"),
					rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults(): extra comment is: {}", comm);
				item = new PlantSpecies(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
LOGGER.debug("new item: plantSpeciesId: {}, commentId: {}", plantSpeciesId, cid);
				if (rs.wasNull())
				{// no comment
LOGGER.debug("new item: id {}: comment is null", cid);
					item = new PlantSpecies(plantSpeciesId, cropRotationGroupId, commonName, latinName, description, utility, hardiness, lifeType, plantType, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						plantSpeciesId,
						"PS",
						rs.getDate("c_date").toLocalDate(),
						rs.getString("c_comment"),
						rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults(): first comment is: {}", comm);
					item = new PlantSpecies(plantSpeciesId, cropRotationGroupId, commonName, latinName, description, utility, hardiness, lifeType, plantType, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

	void clear()
	{
		MySQLCache.cachePlantSpecies.clear();
		MySQLCache.invalidPlantSpecies = true;
		MySQLCache.completePlantSpecies = false;
	}

	/**
	*
	*Select only the PlantSpecies entries with these ids
	*May be called multiple times to extend the list
	*
	*	@param vals	a list of ids
	*	@return	 this Lister
	*/
	PlantSpeciesLister id(int... vals)
	{
		useId = true;
		useWhere = true;
		if (idNext + vals.length >= idList.length)
		{
			idList = Arrays.copyOf(idList, idList.length+vals.length+10);
		}
		for (int val : vals)
		{
			idList[idNext++] = val;
		}
		return this;
	}

	@Override
	public IPlantSpeciesLister cropRotationGroup(ICropRotationGroup... items)
	{
		LOGGER.debug("setting cropRotationGroup: items: {}", items);
		useCropRotationGroup.item(items);
		useWhere = useWhere || useCropRotationGroup.isUsed();
		return this;
	}

	@Override
	public IPlantSpeciesLister cropRotationGroup(List<ICropRotationGroup> items)
	{
		LOGGER.debug("setting cropRotationGroup: items: {}", items);
		useCropRotationGroup.item(items);
		useWhere = useWhere || useCropRotationGroup.isUsed();
		return this;
	}

	@Override
	public IPlantSpeciesLister commonName(String... vals)
	{
		if (vals == null) return this;
		if (vals.length == 0) return this;
		useCommonName = true;
		useWhere = true;
		if (commonNameNext + vals.length >= commonNameList.length)
		{
			commonNameList = Arrays.copyOf(commonNameList, commonNameList.length + vals.length + 10);
		}
		for (String item : vals) {commonNameList[commonNameNext++] = item;}
		return this;
	}

	@Override
	public IPlantSpeciesLister plantSpecies(IPlantSpecies... vals)  //  2.0.1
	{
		if (vals == null) return this;
		if (vals.length == 0) return this;
		int[] keys = new int[vals.length];
		int keyCount = 0;
		for (IPlantSpecies item : vals)
		{
			if (item == null) continue;
			Integer ky = item.getKey();
			if (ky == null) continue;
			keys[keyCount++] = ky;
		}
		if (keyCount == 0) return this;
		keys = Arrays.copyOf(keys, keyCount);	// trim array to actual size - should be a null-op
		return this.id(keys);
	}

	@Override
	public IPlantSpeciesLister plantSpecies(List<IPlantSpecies> vals)  //  2.0.1
	{
		if (vals == null) return this;
		if (vals.isEmpty()) return this;
		return this.plantSpecies(vals.toArray(new IPlantSpecies[0]));
	}

	void toJson(JsonBuilderFactory builderFactory, JsonWriterFactory writerFactory, File dumpDirectory) throws GNDBException
	{
		if (MySQLCache.invalidPlantSpecies)
		{
			useWhere = false;
			fetch();
		}

		JsonArrayBuilder jsonHc = builderFactory.createArrayBuilder();
		for (IPlantSpecies ihc : MySQLCache.cachePlantSpecies.values())
		{
			PlantSpecies hc = (PlantSpecies)ihc;
			jsonHc.add(hc.toJson(builderFactory));
		}

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

    /**
     * The cache is likely to be large so set it up with an appropriate size rather than the default (16) to avoid rehashing
     * 
     * @since   2.0.1
     */
    private void initialiseCache() throws GNDBException
    {
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("initialiseCache()");
        if (MySQLCache.cachePlantSpecies == null || MySQLCache.cachePlantSpecies.isEmpty())
        {
            long recs = 0;
            try (Connection conn = DBConnection.getConnection();
				 Statement stmt = conn.createStatement();
				 ResultSet rs = stmt.executeQuery("select count(*) from plantspecies");)
            {
//                ResultSet rs = stmt.executeQuery("select count(*) from plantspecies");
                while (rs.next()) 
                {
                    recs = rs.getLong(1);
                }

            }catch (SQLException ex) {
                LOGGER.error("fetch(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
                throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
            }
            LOGGER.debug("PlantSpecies table size: {}", recs);
            
            MySQLCache.cachePlantSpecies = new HashMap<>((int) (recs*4/3));

            if (MySQLCache.cacheVarietyBySpecies == null || MySQLCache.cacheVarietyBySpecies.isEmpty())
            {
                MySQLCache.cacheVarietyBySpecies = new HashMap<>((int) (recs*4/3));
            }
        }
        LOGGER.traceExit(log4jEntryMsg);
    }

}
