/*
 * 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
    3.0.0	First version
	3.1.0	Use jakarta implementation of JSON
 */

package uk.co.gardennotebook.mysql;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;
import uk.co.gardennotebook.spi.*;

import jakarta.json.*;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
//import java.util.stream.Collectors;
import java.util.stream.IntStream;

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

final class CropRotationGroupLister implements ICropRotationGroupLister
{
	private static final Logger LOGGER = LogManager.getLogger();

	private boolean useName = false;
	private String[] nameList = new String[10];
	private int nameNext = 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

	CropRotationGroupLister() {}

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

		if (MySQLCache.invalidCropRotationGroup)
		{
			load();
		}

		if(!useId && !useName)
		{
			useId = false;
			idNext = 0;
			useName = false;
			nameNext = 0;
			MySQLCache.invalidCropRotationGroup = false;
			return new ArrayList<>( MySQLCache.cacheCropRotationGroup.values().stream().
//				sorted((a,b) -> a.getName().compareToIgnoreCase(b.getName())).collect(Collectors.toList()) );
//				sorted((a,b) -> a.getName().compareToIgnoreCase(b.getName())).toList() );
				toList() );
		}

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

		idList = Arrays.copyOf(idList, idNext);

		if (useName)
		{
			nameList = Arrays.copyOf(nameList, nameNext);
			int [] nameKeys = MySQLCache.cacheCropRotationGroup.values().stream().
				filter(c -> Arrays.stream(nameList).anyMatch(p -> p.equals(c.getName())))
				.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.cacheCropRotationGroup.get(ix));
		}
		useId = false;
		idNext = 0;
		useName = false;
		nameNext = 0;
		MySQLCache.invalidCropRotationGroup = false;

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

	void load() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("load()");

		if (MySQLCache.invalidCropRotationGroup)
		{
			List<CropRotationGroup> tempList = new ArrayList<>();

			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("load(): query: {}", query);
			try (Connection conn = DBConnection.getConnection();
				 Statement stmt = conn.createStatement();)
			{
				ResultSet 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();
			}catch (SQLException ex) {
				LOGGER.error("load(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
				throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
			}
			for (CropRotationGroup ps : tempList)
			{
				LOGGER.debug("storing: {}", ps);
				MySQLCache.cacheCropRotationGroup.putIfAbsent(ps.getId(), ps);
			}
		}

LOGGER.traceExit(log4jEntryMsg);
	}	// load()

    private String buildSQL_MySQL() {
		return "select d.*, c.* from croprotationgroup as d " +
				"left join (select * from comment where ownerType = '" + NotebookEntryType.CROPROTATIONGROUP.type() + "') as c " +
					"on d.cropRotationGroupId = c.ownerId " +
				" order by d.subsequentCropGroupId, c.date";
    }

    private String buildSQL_hsqldb() {
		return "select d.*, c.* from croprotationgroup as d " +
				"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 = '" + NotebookEntryType.CROPROTATIONGROUP.type() + "') as c " +
					"on d.cropRotationGroupId = c_ownerId " +
				" order by d.subsequentCropGroupId, c_date";
    }

    private String buildSQL_MSSQLServer() {
		return "select d.cropRotationGroupId as d_cropRotationGroupId, " +
				"d.name as d_name, " +
				"d.description as d_description, " +
				"d.subsequentCropGroupId as d_subsequent, " +
				"d.created as d_created, " +
				"d.lastUpdated as d_lastUpdated, " +
				"c.* from croprotationgroup as d " +
				"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 = '" + NotebookEntryType.CROPROTATIONGROUP.type() + "') as c " +
					"on d.cropRotationGroupId = c_ownerId " +
//				" order by LOWER(d.name), c_date";
				" order by d_subsequent, c_date";
    }

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

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

		CropRotationGroup item = null;

		while (rs.next())
		{
			int cropRotationGroupId = rs.getInt("d.cropRotationGroupId");
			String name = rs.getString("d.name");
			String description = rs.getString("d.description");
			int subsequentCropGroupId = rs.getInt("d.subsequentCropGroupId");
			LocalDateTime lastUpdated = rs.getTimestamp("d.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d.created").toLocalDateTime();
LOGGER.debug("processResults_MySQL(): CropRotationGroupId: {}, name: {}, description: {}, subsequentCropGroupId: {}, lastUpdated: {}, created: {}",
				cropRotationGroupId, name, description, subsequentCropGroupId, lastUpdated, created);
			if (item != null && cropRotationGroupId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults_MySQL(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("c.commentId"),
					rs.getInt("c.ownerId"),
					NotebookEntryType.CROPROTATIONGROUP.type(),
					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 CropRotationGroup(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c.commentId");
				if (rs.wasNull())
				{// no comment
					item = new CropRotationGroup(cropRotationGroupId, name, description, subsequentCropGroupId, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						cropRotationGroupId,
						NotebookEntryType.CROPROTATIONGROUP.type(),
						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 CropRotationGroup(cropRotationGroupId, name, description, subsequentCropGroupId, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

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

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

		CropRotationGroup item = null;

		while (rs.next())
		{
			int cropRotationGroupId = rs.getInt("croprotationgroup.cropRotationGroupId");
			String name = rs.getString("croprotationgroup.name");
			String description = rs.getString("croprotationgroup.description");
			int subsequentCropGroupId = rs.getInt("croprotationgroup.subsequentCropGroupId");
			LocalDateTime lastUpdated = rs.getTimestamp("croprotationgroup.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("croprotationgroup.created").toLocalDateTime();
LOGGER.debug("processResults_hsqldb(): cropRotationGroupId: {}, name: {}, description: {}, subsequentCropGroupId: {}, lastUpdated: {}, created: {}",
						cropRotationGroupId, name, description, subsequentCropGroupId, lastUpdated, created);
			if (item != null && cropRotationGroupId == 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"),
					NotebookEntryType.CROPROTATIONGROUP.type(),
					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 CropRotationGroup(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new CropRotationGroup(cropRotationGroupId, name, description, subsequentCropGroupId, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						cropRotationGroupId,
						NotebookEntryType.CROPROTATIONGROUP.type(),
						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 CropRotationGroup(cropRotationGroupId, name, description, subsequentCropGroupId, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

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

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

		CropRotationGroup item = null;

		while (rs.next())
		{
			int cropRotationGroupId = rs.getInt("d_cropRotationGroupId");
			String name = rs.getString("d_name");
			String description = rs.getString("d_description");
			int subsequentCropGroupId = rs.getInt("d_subsequent");
			LocalDateTime lastUpdated = rs.getTimestamp("d_lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d_created").toLocalDateTime();
LOGGER.debug("processResults_hsqldb(): CropRotationGroupId: {}, name: {}, description: {}, lastUpdated: {}, created: {}", cropRotationGroupId, name, description, lastUpdated, created);
			if (item != null && cropRotationGroupId == 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"),
					NotebookEntryType.CROPROTATIONGROUP.type(),
					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 CropRotationGroup(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new CropRotationGroup(cropRotationGroupId, name, description, subsequentCropGroupId, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						cropRotationGroupId,
						NotebookEntryType.CROPROTATIONGROUP.type(),
						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 CropRotationGroup(cropRotationGroupId, name, description, subsequentCropGroupId, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

	void clear()
	{
		MySQLCache.cacheCropRotationGroup.clear();
		MySQLCache.invalidCropRotationGroup = true;
		MySQLCache.completeCropRotationGroup = false;
	}

	/**
	*
	*	Select only the CropRotationGroup entries with these ids
	*	May be called multiple times to extend the list
	*
	*	@param vals	a list of ids
	*	@return	 this Lister
	*/
	CropRotationGroupLister id(int... vals)
	{
		useId = 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 ICropRotationGroupLister cropRotationGroup(ICropRotationGroup... vals)
	{
		if (vals == null) return this;
		if (vals.length == 0) return this;
		int[] keys = new int[vals.length];
		int keyCount = 0;
		for (ICropRotationGroup val : vals) {
			if (val == null) continue;
			Integer ky = val.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 ICropRotationGroupLister cropRotationGroup(List<ICropRotationGroup> items)
	{
		if (items == null) return this;
		if (items.isEmpty()) return this;
		return this.cropRotationGroup(items.toArray(new ICropRotationGroup[0]));
	}

	@Override
	public ICropRotationGroupLister name(String... vals)
	{
		if (vals == null) return this;
		if (vals.length == 0) return this;
		useName = true;
		if (nameNext + vals.length >= nameList.length)
		{
			nameList = Arrays.copyOf(nameList, nameList.length + vals.length + 10);
		}
		for (String val : vals) {nameList[nameNext++] = val;}
		return this;
	}

	void toJson(JsonBuilderFactory builderFactory, JsonWriterFactory writerFactory, File dumpDirectory) throws GNDBException
	{
		if (MySQLCache.invalidCropRotationGroup)
		{
			load();
		}

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

}
