/*
 * 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.0.1   Fixed bug scanning for request by id.
	2.2.0   Support hsqldb dialect
    2.4.0   Support MS SQLServer
	3.0.0	Selector methods 'key' renamed to wildlifeSpecies for consistency.
	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 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 WildlifeSpeciesLister implements IWildlifeSpeciesLister
{
	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

	private boolean useWhere = false;

	WildlifeSpeciesLister() {}

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

		boolean needRead = MySQLCache.invalidWildlifeSpecies;
		needRead = (useName && !MySQLCache.completeWildlifeSpecies) || needRead;
		//force read if cache is marked incomplete but whole set is required
		needRead = (!useWhere && !MySQLCache.completeWildlifeSpecies) || needRead;
		if (useId) 
        {
			for(int jx = 0; jx<idNext; jx++) 
            {
				if (!MySQLCache.cacheWildlifeSpecies.containsKey(idList[jx]))   //  2.0.1
                {
					needRead = true;
					break;
				}
			}
		}
		if (needRead)
		{
            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("WildlifeSpeciesLister: fetch(): no known RDBMS"));
				}
			}
LOGGER.debug("fetch(): query: {}", query);
			List<WildlifeSpecies> tempList = new ArrayList<>();

			try (Connection conn = DBConnection.getConnection(); Statement stmt = conn.createStatement();)
			{
				ResultSet rs = null;
				if (this.useName)
				{
					PreparedStatement pstmt = conn.prepareStatement(query);
					int pstmtIx = 1;
					if (this.useName)
					{
						for(int ix =0; ix < nameNext; ix++)
						{
LOGGER.debug("fetch(): pstmtIx: {} = {}", pstmtIx, nameList[ix].toLowerCase());
							pstmt.setString(pstmtIx++, nameList[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.error("fetch(): no known rdbms");
						throw new GNDBException(new IllegalStateException("WildlifeSpeciesLister: fetch(): no known RDBMS"));
					}
				}
				stmt.close();
				if (!useWhere)
				{
					MySQLCache.completeWildlifeSpecies = 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 (WildlifeSpecies ps : tempList) {
				MySQLCache.cacheWildlifeSpecies.putIfAbsent(ps.getId(), ps);
			}
		}

		if(!useId && !useName) {
			useId = false;
			idNext = 0;
			useName = false;
			nameNext = 0;
			MySQLCache.invalidWildlifeSpecies = false;
			return MySQLCache.cacheWildlifeSpecies.values().stream().
					sorted((a, b) -> a.getName().compareToIgnoreCase(b.getName())).collect(Collectors.toList());
		}

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

		idList = Arrays.copyOf(idList, idNext);

		if (useName) {
			nameList = Arrays.copyOf(nameList, nameNext);
			int [] nameKeys = MySQLCache.cacheWildlifeSpecies.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.cacheWildlifeSpecies.get(ix));
		}

		useWhere = false;
		useId = false;
		idNext = 0;
		useName = false;
		nameNext = 0;
		MySQLCache.invalidWildlifeSpecies = false;

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

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

    private String buildSQL_hsqldb()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from wildlifespecies 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 = 'WS') as c ");
        query.append("on d.wildlifeSpeciesId = c_ownerId ");
        if (useWhere)
        {
            buildCommonSQL(query);
        }
        query.append(" order by LOWER(d.name), c_date");
        return query.toString();
    }   //  buildSQL_hsqldb()

    private String buildSQL_MSSQLServer()
    {
        StringBuilder query = new StringBuilder("select d.wildlifeSpeciesId as d_wildlifeSpeciesId, " +
                                                        "d.name as d_name, " +
                                                        "d.description as d_description, " +
                                                        "d.lastUpdated as d_lastUpdated, " +
                                                        "d.created as d_created," +
                                                        " c.* from wildlifespecies 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 = 'WS') as c ");
        query.append("on d.wildlifeSpeciesId = c_ownerId ");
        if (useWhere)
        {
            buildCommonSQL(query);
        }
        query.append(" order by LOWER(d.name), c_date");
        return query.toString();
    }   //  buildSQL_MSSQLServer()

    private void buildCommonSQL(StringBuilder query)
    {
        boolean first = true;
        if (this.useId)
        {
            if (first) query.append(" where ");
            else query.append(" and");
            query.append(" d.wildlifeSpeciesId in (");
            for(int ix = 0; ix < idNext; ix++) { query.append(idList[ix]).append(", "); }
            first = false;
            query.replace(query.length()-2, query.length(), ") ");
        }
        if (this.useName)
        {
            if (first) query.append(" where ");
            else query.append(" and");
            if (nameNext > 1) {
                query.append(" LOWER(d.name) in (");
                for(int ix =0; ix < nameNext; ix++)
                { query.append("?, "); }
                query.replace(query.length()-2, query.length(), ") ");
            }
            else
                query.append(" LOWER(d.name) = ?");
        }
    }   //  buildCommonSQL()

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

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

		WildlifeSpecies item = null;

		while (rs.next()) {
			int wildlifeSpeciesId = rs.getInt("d.wildlifeSpeciesId");
			String name = rs.getString("d.name");
			String description = rs.getString("d.description");
			LocalDateTime lastUpdated = rs.getTimestamp("d.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d.created").toLocalDateTime();
LOGGER.debug("wildlifeSpeciesId: {}, name: {}, description: {}, lastUpdated: {}, created: {}", wildlifeSpeciesId, name, description, lastUpdated, created);
			if (item != null && wildlifeSpeciesId == 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"),
					"WS",
					rs.getDate("c.date").toLocalDate(),
					rs.getString("c.comment"),
					rs.getTimestamp("c.lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c.created").toLocalDateTime());
LOGGER.debug("procprocessResults_MySQLessResults(): extra comment is: {}", comm);
				item = new WildlifeSpecies(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c.commentId");
				if (rs.wasNull())
				{// no comment
					item = new WildlifeSpecies(wildlifeSpeciesId, name, description, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						wildlifeSpeciesId,
						"WS",
						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 WildlifeSpecies(wildlifeSpeciesId, name, description, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

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

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

		WildlifeSpecies item = null;

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

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

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

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

		WildlifeSpecies item = null;

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

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

	void clear()
	{
		MySQLCache.cacheWildlifeSpecies.clear();
		MySQLCache.invalidWildlifeSpecies = true;
		MySQLCache.completeWildlifeSpecies = false;
	}

	/**
	*
	*	Select only the WildlifeSpecies entries with these ids.
	*	May be called multiple times to extend the list
	*
	*	@param vals	a list of ids
	*	@return	 this Lister
	*/
	IWildlifeSpeciesLister 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 IWildlifeSpeciesLister name(String... vals)
	{
		if (vals == null) return this;
		if (vals.length == 0) return this;
		useName = true;
		useWhere = true;
		if (nameNext + vals.length >= nameList.length)
		{
			nameList = Arrays.copyOf(nameList, nameList.length + vals.length + 10);
		}
		for (String item : vals) {nameList[nameNext++] = item;}
		return this;
	}

	@Override
	public IWildlifeSpeciesLister wildlifeSpecies(IWildlifeSpecies... vals)
	{
		if (vals == null) return this;
		if (vals.length == 0) return this;
		int[] keys = new int[vals.length];
		int keyCount = 0;
		for (IWildlifeSpecies item : vals)
		{
			if (item == null) continue;
			Object ky = item.getKey();
			if (ky == null) continue;
			if (ky instanceof Integer) keys[keyCount++] = (Integer)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 IWildlifeSpeciesLister wildlifeSpecies(List<IWildlifeSpecies> vals)
	{
		if (vals == null) return this;
		if (vals.isEmpty()) return this;
		return this.wildlifeSpecies(vals.toArray(new IWildlifeSpecies[0]));
	}

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

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

}
