/*
 * 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.2.5   Bug in 'from date' handling for hsqldb
    2.3.0   Selection by Product
    2.4.0   Support MS SQLServer
    3.0.0	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.List;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;

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 java.math.BigDecimal;
import jakarta.json.JsonObjectBuilder;

/**
*
*{@inheritDoc}
*
*	@author	Andy Gegg
*	@version	3.1.0
*	@since	1.0
*/
final class PurchaseLister implements IPurchaseLister
{
	private static final Logger LOGGER = LogManager.getLogger();

	private final DBKeyHandler<IPurchase> usePurchase = new DBKeyHandler<>("purchaseId");

	private final DBKeyHandler<IRetailer> useRetailer = new DBKeyHandler<>("retailerId");

	private boolean useProductId = false;
	private int[] productIdList = new int[10];
	private int productIdNext = 0;

	private boolean useFromDate = false;
	private LocalDate fromDate;
	private boolean useToDate = false;
	private LocalDate toDate;

	private boolean useFromDeliveryDate = false;
	private LocalDate fromDeliveryDate;
	private boolean useToDeliveryDate = false;
	private LocalDate toDeliveryDate;

	private boolean useWhere = false;

	PurchaseLister() {}

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

		List<IPurchase> 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("PurchaseLister: 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("PurchaseLister: 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 (IPurchase vx : vals)
		{
			MySQLCache.cachePurchase.putIfAbsent(vx.getKey(), vx);
		}
		if (fetchAll) { MySQLCache.completePurchase = true; }

		populateRetailer(vals, fetchAll);

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

    private String buildSQL_MySQL()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from purchase as d ");
        query.append("left join (select * from comment where ownerType = \"PU\") as c ");
        query.append("on d.purchaseId = c.ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);
        }
        query.append(" order by d.date, d.deliveryDate, d.retailerId, d.purchaseId, c.date");
        return query.toString();
    }   //  buildSQL_MySQL()

    private String buildSQL_hsqldb()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from purchase 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 = 'PU') as c ");
        query.append("on d.purchaseId = c_ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);
        }
        query.append(" order by d.date, d.deliveryDate, d.retailerId, d.purchaseId, c_date");
        return query.toString();
    }   //  buildSQL_hsqldb()

    private String buildSQL_MSSQLServer()
    {
        StringBuilder query = new StringBuilder("select d.purchaseId as d_purchaseId, " +
                                                        "d.retailerId as d_retailerId, " +
                                                        "d.date as d_date, " +
                                                        "d.totalCost as d_totalCost, " +
                                                        "d.currency as d_currency, " +
                                                        "d.orderNo as d_orderNo, " +
                                                        "d.invoiceNo as d_invoiceNo, " +
                                                        "d.deliveryDate as d_deliveryDate, " +
                                                        "d.lastUpdated as d_lastUpdated, " +
                                                        "d.created as d_created," +
                                                        " c.* from purchase 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 = 'PU') as c ");
        query.append("on d.purchaseId = c_ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);
        }
        query.append(" order by d.date, d.deliveryDate, d.retailerId, d.purchaseId, c_date");
        return query.toString();
    }   //  buildSQL_MSSQLServer()

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

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

		//  2.3.0
        if (this.useProductId)
        {
            if (first) query.append(" where ");
            else query.append(" and");
            query.append(" d.purchaseId in (select purchaseid from purchaseitem as pi where pi.productid in (");
            for(int ix =0; ix < productIdNext; ix++) {query.append(productIdList[ix]).append(", ");}
            query.replace(query.length()-2, query.length(), "))");
            first = false;
            this.useProductId = false;
            this.productIdNext = 0;
        }

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

            query.append(" d.date >= '").append(this.fromDate).append("'");
            first = false;
            this.useFromDate = false;
        }
        if (this.useToDate)
        {
            if (first) query.append(" where ");
            else query.append(" and");

            query.append(" d.date <= '").append(this.toDate).append("'");
            first = false;
            this.useToDate = false;
        }

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

            query.append(" d.deliveryDate >= '").append(this.fromDeliveryDate).append("'");
            first = false;
            this.useFromDeliveryDate = false;
        }
        if (this.useToDeliveryDate)
        {
            if (first) query.append(" where ");
            else query.append(" and");

            query.append(" d.deliveryDate <= '").append(this.toDeliveryDate).append("'");
            this.useToDeliveryDate = false;
        }
    }   //  buildCommonSQL()

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

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

		Purchase item = null;

		while (rs.next())
		{
			int purchaseId = rs.getInt("d.purchaseId");
			int retailerId = rs.getInt("d.retailerId");
			LocalDate date = rs.getDate("d.date").toLocalDate();
			BigDecimal totalCost = rs.getBigDecimal("d.totalCost");
			String currency = rs.getString("d.currency");
			String orderNo = rs.getString("d.orderNo");
			String invoiceNo = rs.getString("d.invoiceNo");
			LocalDate deliveryDate = (rs.getDate("d.deliveryDate") == null) ? null : rs.getDate("d.deliveryDate").toLocalDate();
			LocalDateTime lastUpdated = rs.getTimestamp("d.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d.created").toLocalDateTime();
LOGGER.debug("purchaseId: {}, retailerId: {}, date: {}, totalCost: {}, currency: {}, orderNo: {}, invoiceNo: {}, deliveryDate: {}, lastUpdated: {}, created: {}",
                purchaseId, retailerId, date, totalCost, currency, orderNo, invoiceNo, deliveryDate, lastUpdated, created);
			if (item != null && purchaseId == 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"),
					"PU",
					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 Purchase(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c.commentId");
				if (rs.wasNull())
				{// no comment
					item = new Purchase(purchaseId, retailerId, date, totalCost, currency, orderNo, invoiceNo, deliveryDate, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						purchaseId,
						"PU",
						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 Purchase(purchaseId, retailerId, date, totalCost, currency, orderNo, invoiceNo, deliveryDate, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

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

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

		Purchase item = null;

		while (rs.next())
		{
			int purchaseId = rs.getInt("purchase.purchaseId");
			int retailerId = rs.getInt("purchase.retailerId");
			LocalDate date = rs.getDate("purchase.date").toLocalDate();
			BigDecimal totalCost = rs.getBigDecimal("purchase.totalCost");
			String currency = rs.getString("purchase.currency");
			String orderNo = rs.getString("purchase.orderNo");
			String invoiceNo = rs.getString("purchase.invoiceNo");
			LocalDate deliveryDate = (rs.getDate("purchase.deliveryDate") == null) ? null : rs.getDate("purchase.deliveryDate").toLocalDate();
			LocalDateTime lastUpdated = rs.getTimestamp("purchase.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("purchase.created").toLocalDateTime();
LOGGER.debug("purchaseId: {}, retailerId: {}, date: {}, totalCost: {}, currency: {}, orderNo: {}, invoiceNo: {}, deliveryDate: {}, lastUpdated: {}, created: {}",
                purchaseId, retailerId, date, totalCost, currency, orderNo, invoiceNo, deliveryDate, lastUpdated, created);
			if (item != null && purchaseId == 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"),
					"PU",
					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 Purchase(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new Purchase(purchaseId, retailerId, date, totalCost, currency, orderNo, invoiceNo, deliveryDate, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						purchaseId,
						"PU",
						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 Purchase(purchaseId, retailerId, date, totalCost, currency, orderNo, invoiceNo, deliveryDate, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

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

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

		Purchase item = null;

		while (rs.next())
		{
			int purchaseId = rs.getInt("d_purchaseId");
			int retailerId = rs.getInt("d_retailerId");
			LocalDate date = rs.getDate("d_date").toLocalDate();
			BigDecimal totalCost = rs.getBigDecimal("d_totalCost");
			String currency = rs.getString("d_currency");
			String orderNo = rs.getString("d_orderNo");
			String invoiceNo = rs.getString("d_invoiceNo");
			LocalDate deliveryDate = (rs.getDate("d_deliveryDate") == null) ? null : rs.getDate("purchase.deliveryDate").toLocalDate();
			LocalDateTime lastUpdated = rs.getTimestamp("d_lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d_created").toLocalDateTime();
LOGGER.debug("purchaseId: {}, retailerId: {}, date: {}, totalCost: {}, currency: {}, orderNo: {}, invoiceNo: {}, deliveryDate: {}, lastUpdated: {}, created: {}",
                purchaseId, retailerId, date, totalCost, currency, orderNo, invoiceNo, deliveryDate, lastUpdated, created);
			if (item != null && purchaseId == 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"),
					"PU",
					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 Purchase(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new Purchase(purchaseId, retailerId, date, totalCost, currency, orderNo, invoiceNo, deliveryDate, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						purchaseId,
						"PU",
						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 Purchase(purchaseId, retailerId, date, totalCost, currency, orderNo, invoiceNo, deliveryDate, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

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

	/*
	*	Populate the parents slot(s)
	*/
	private void populateRetailer(List<IPurchase> vals, boolean fetchAll) throws GNDBException
	{
// parent table type: TD
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("populateRetailer()");

		if (fetchAll)
		{
			new RetailerLister().fetch();
			return;
		}
		int[] keys = vals.stream().
			mapToInt(c -> ((Purchase)c).getRetailerId()).filter(c -> c>0).distinct().
			filter(c -> !MySQLCache.cacheRetailer.containsKey(c)).
			toArray();

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

	void clear()
	{
		MySQLCache.cachePurchase.clear();
		MySQLCache.completePurchase = false;
	}

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

	@Override
	public IPurchaseLister purchase(IPurchase... items)
	{
		usePurchase.item(items);
		useWhere = useWhere || usePurchase.isUsed();
		return this;
	}

	@Override
	public IPurchaseLister purchase(List<IPurchase> items)
	{
		usePurchase.item(items);
		useWhere = useWhere || usePurchase.isUsed();
		return this;
	}

	@Override
	public IPurchaseLister retailer(IRetailer... items)
	{
        LOGGER.debug("PurchaseLister: retailer(): {}", items);
		useRetailer.item(items);
		useWhere = useWhere || useRetailer.isUsed();
		return this;
	}

	@Override
	public IPurchaseLister retailer(List<IRetailer> items)
	{
		useRetailer.item(items);
		useWhere = useWhere || useRetailer.isUsed();
		return this;
	}

	@Override
	public IPurchaseLister product(IProduct... items)
	{
        LOGGER.debug("PurchaseLister: product(): {}", items);
		if (items == null) return this;
		if (items.length == 0) return this;
		useProductId = true;
		if (productIdNext + items.length >= productIdList.length)
		{
			productIdList = Arrays.copyOf(productIdList, productIdList.length+items.length+10);
		}
		for (IProduct item : items) {
			if (item == null) continue;
			Integer ky = item.getKey();
			if (ky == null) continue;
			productIdList[productIdNext++] = ky;
		}
		useWhere = true;
		return this;
	}

	@Override
	public IPurchaseLister product(List<IProduct> items)
	{
		if (items == null) return this;
		if (items.isEmpty()) return this;
		return this.product(items.toArray(new IProduct[0]));
	}

	@Override
	public IPurchaseLister fromDate(LocalDate item)
	{
		if (item == null) return this;
		this.fromDate = item;
		this.useFromDate = true;
		this.useWhere = true;
		return this;
	}

	@Override
	public IPurchaseLister toDate(LocalDate item)
	{
		if (item == null) return this;
		this.toDate = item;
		this.useToDate = true;
		this.useWhere = true;
		return this;
	}

	@Override
	public IPurchaseLister fromDeliveryDate(LocalDate item)
	{
		if (item == null) return this;
		this.fromDeliveryDate = item;
		this.useFromDeliveryDate = true;
		this.useWhere = true;
		return this;
	}

	@Override
	public IPurchaseLister toDeliveryDate(LocalDate item)
	{
		if (item == null) return this;
		this.toDeliveryDate = item;
		this.useToDeliveryDate = true;
		this.useWhere = true;
		return this;
	}

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

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

}
