/*
 * 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	CHanged key() to retailer() 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 RetailerLister implements IRetailerLister
{
	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;

	RetailerLister() {}

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

		boolean needRead = MySQLCache.invalidRetailer;
		needRead = (useName && !MySQLCache.completeRetailer) || needRead;
		//force read if cache is marked incomplete but whole set is required
		needRead = (!useWhere && !MySQLCache.completeRetailer) || needRead;
		if (useId)
        {
			for(int jx = 0; jx<idNext; jx++) 
            {
				if (!MySQLCache.cacheRetailer.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("RetailerLister: fetch(): no known rdbms");
					throw new GNDBException(new IllegalStateException("RetailerLister: fetch(): no known RDBMS"));
				}
			}
LOGGER.debug("fetch(): query: {}", query);
			List<Retailer> 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("RetailerLister: fetch(): no known rdbms");
						throw new GNDBException(new IllegalStateException("RetailerLister: fetch(): no known RDBMS"));
					}
				}
				stmt.close();
				if (!useWhere)
				{
					MySQLCache.completeRetailer = 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 (Retailer ps : tempList) {
				MySQLCache.cacheRetailer.putIfAbsent(ps.getId(), ps);
			}
		}

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

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

		idList = Arrays.copyOf(idList, idNext);

		if (useName) {
			nameList = Arrays.copyOf(nameList, nameNext);
			int [] nameKeys = MySQLCache.cacheRetailer.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 i : idList)
		{
			outList.add(MySQLCache.cacheRetailer.get(i));
		}

		useWhere = false;
		useId = false;
		idNext = 0;
		useName = false;
		nameNext = 0;
		MySQLCache.invalidRetailer = 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 retailer as d ");
        query.append("left join (select * from comment where ownerType = \"SU\") as c ");
        query.append("on d.retailerId = c.ownerId ");
        if (useWhere)
        {
            boolean first = true;
            if (this.useId)
            {
                if (first) query.append(" where ");
                else query.append(" and");
                query.append(" d.retailerId 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) = ?");
            }
        }
        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 retailer 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 = 'SU') as c ");
        query.append("on d.retailerId = c_ownerId ");
        if (useWhere)
        {
            boolean first = true;
            if (this.useId)
            {
                if (first) query.append(" where ");
                else query.append(" and");
                query.append(" d.retailerId 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) = ?");
            }
        }
        query.append(" order by LOWER(d.name), c_date");
        return query.toString();
    }   //  buildSQL_hsqldb()

    private String buildSQL_MSSQLServer()
    {
        StringBuilder query = new StringBuilder("select d.retailerId as d_retailerId, " +
                                                        "d.name as d_name, " +
                                                        "d.description as d_description, " +
                                                        "d.address as d_address, " +
                                                        "d.webSite as d_webSite, " +
                                                        "d.eMail as d_eMail, " +
                                                        "d.phone as d_phone, " +
                                                        "d.mobile as d_mobile, " +
                                                        "d.ownBrandOnly as d_ownBrandOnly, " +
                                                        "d.lastUpdated as d_lastUpdated, " +
                                                        "d.created as d_created, " +
                                                        " c.* from retailer 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 = 'SU') as c ");
        query.append("on d.retailerId = c_ownerId ");
        if (useWhere)
        {
            boolean first = true;
            if (this.useId)
            {
                if (first) query.append(" where ");
                else query.append(" and");
                query.append(" d.retailerId 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) = ?");
            }
        }
        query.append(" order by LOWER(d.name), c_date");
        return query.toString();
    }   //  buildSQL_MSSQLServer()

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

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

		Retailer item = null;

		while (rs.next()) {
			int retailerId = rs.getInt("d.retailerId");
			String name = rs.getString("d.name");
			String description = rs.getString("d.description");
			String address = rs.getString("d.address");
			String webSite = rs.getString("d.webSite");
			String eMail = rs.getString("d.eMail");
			String phone = rs.getString("d.phone");
			String mobile = rs.getString("d.mobile");
			boolean ownBrandOnly = rs.getBoolean("d.ownBrandOnly");
			LocalDateTime lastUpdated = rs.getTimestamp("d.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d.created").toLocalDateTime();
			if (item != null && retailerId == 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"),
					"SU",
					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 Retailer(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c.commentId");
				if (rs.wasNull())
				{// no comment
					item = new Retailer(retailerId, name, description, address, webSite, eMail, phone, mobile, ownBrandOnly, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						retailerId,
						"SU",
						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 Retailer(retailerId, name, description, address, webSite, eMail, phone, mobile, ownBrandOnly, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

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

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

		Retailer item = null;

		while (rs.next()) {
			int retailerId = rs.getInt("retailer.retailerId");
			String name = rs.getString("retailer.name");
			String description = rs.getString("retailer.description");
			String address = rs.getString("retailer.address");
			String webSite = rs.getString("retailer.webSite");
			String eMail = rs.getString("retailer.eMail");
			String phone = rs.getString("retailer.phone");
			String mobile = rs.getString("retailer.mobile");
			boolean ownBrandOnly = rs.getBoolean("retailer.ownBrandOnly");
			LocalDateTime lastUpdated = rs.getTimestamp("retailer.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("retailer.created").toLocalDateTime();
			if (item != null && retailerId == 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"),
					"SU",
					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 Retailer(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new Retailer(retailerId, name, description, address, webSite, eMail, phone, mobile, ownBrandOnly, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						retailerId,
						"SU",
						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 Retailer(retailerId, name, description, address, webSite, eMail, phone, mobile, ownBrandOnly, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

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

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

		Retailer item = null;

		while (rs.next())
        {
			int retailerId = rs.getInt("d_retailerId");
			String name = rs.getString("d_name");
			String description = rs.getString("d_description");
			String address = rs.getString("d_address");
			String webSite = rs.getString("d_webSite");
			String eMail = rs.getString("d_eMail");
			String phone = rs.getString("d_phone");
			String mobile = rs.getString("d_mobile");
			boolean ownBrandOnly = rs.getBoolean("d_ownBrandOnly");
			LocalDateTime lastUpdated = rs.getTimestamp("d_lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d_created").toLocalDateTime();
			if (item != null && retailerId == 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"),
					"SU",
					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 Retailer(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new Retailer(retailerId, name, description, address, webSite, eMail, phone, mobile, ownBrandOnly, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						retailerId,
						"SU",
						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 Retailer(retailerId, name, description, address, webSite, eMail, phone, mobile, ownBrandOnly, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

	void clear()
	{
		MySQLCache.cacheRetailer.clear();
		MySQLCache.invalidRetailer = true;
		MySQLCache.completeRetailer = false;
	}

	/**
	*
	*Select only the Retailer entries with these ids
	*May be called multiple times to extend the list
	*
	*	@param vals	a list of ids
	*	@return	 this Lister
	*/
	RetailerLister 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 IRetailerLister 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 IRetailerLister retailer(IRetailer... vals)
	{
		if (vals == null) return this;
		if (vals.length == 0) return this;
		int[] keys = new int[vals.length];
		int keyCount = 0;
		for (IRetailer 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 IRetailerLister retailer(List<IRetailer> vals)
	{
		if (vals == null) return this;
		if (vals.isEmpty()) return this;
		return this.retailer(vals.toArray(new IRetailer[0]));
	}

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

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

}
