/*
 *
 *  Copyright (C) 2021, 2023 Andrew Gegg
 *
 * 	This file is part of the Gardeners Notebook application
 *
 *  The Gardeners 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.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.Collections;
import java.util.List;
import java.util.Objects;

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

    private final DBKeyHandler<ICroppingActual> useCroppingActual = new DBKeyHandler<>("croppingActualId");

    private final DBKeyHandler<ICroppingPlan> useCroppingPlan = new DBKeyHandler<>("croppingPlanId");

    private final DBKeyHandler<IPlantSpecies> usePlantSpecies = new DBKeyHandler<>("plantSpeciesId");

    private final DBKeyHandler<IPlantVariety> usePlantVariety = new DBKeyHandler<>("plantVarietyId");

    private boolean useWhere = false;

    CroppingActualLister() {}

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

        List<CroppingActual> vals = new ArrayList<>();
        boolean fetchAll = !useWhere;
        String query = "";
        switch (DBConnection.DB_IN_USE)
        {
            case MariaDB, MySQL -> query = buildSQL_MySQL();
            case hsqldb -> query = buildSQL_MySQL();//buildSQL_hsqldb();
            case MSSQLServer -> query = buildSQL_MySQL();//buildSQL_MSSQLServer();
            default -> {
                LOGGER.error("fetch(): no known rdbms");
                throw new GNDBException(new IllegalStateException("CroppingActualLister: 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_MSSQLServer(rs);//processResults_MySQL(rs);
                case hsqldb -> vals = processResults_MSSQLServer(rs);//processResults_hsqldb(rs);
                case MSSQLServer -> vals = processResults_MSSQLServer(rs);
                default -> {
                    LOGGER.error("fetch(): no known rdbms");
                    throw new GNDBException(new IllegalStateException("CroppingActualLister: 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 (CroppingActual vx : vals)
        {
            MySQLCache.cacheCroppingActual.putIfAbsent(vx.getKey(), vx);
        }
        if (fetchAll) { MySQLCache.completeCroppingActual = true; }

        populatePlantSpecies(vals, fetchAll);

        populatePlantVariety(vals, fetchAll);

        LOGGER.traceExit(log4jEntryMsg);
        return vals;

    }

    private String buildSQL_MySQL()
    {
        String query = "select d.croppingActualId as d_croppingActualId, " +
                "d.croppingPlanId as d_croppingPlanId, " +
                "d.plantSpeciesId as d_plantSpeciesId, " +
                "d.plantVarietyId as d_plantVarietyId, " +
                "d.lastUpdated as d_lastUpdated, " +
                "d.created as d_created," +
                " c.* from croppingactual as d ";
        query += "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.CROPPINGACTUAL.type() + "') as c ";
        query += "on d.croppingActualId = c_ownerId ";

        if (useWhere)
        {
            useWhere = false;
            query += buildCommonSQL();
        }

        query += " order by d_croppingPlanId, d_plantSpeciesId, d_plantVarietyId, c_date, c_commentId";

        return query;
    }

    private String buildSQL_hsqldb()
    {
        return "";
    }

    private String buildSQL_MSSQLServer()
    {
        return "";
    }

    private String buildCommonSQL()
    {
        String query = "";
        boolean first = true;
        if (useCroppingActual.isUsed())
        {
            if (first) query += " where ";
            else query += " and ";
            query += useCroppingActual.getSQLClause("d");
            first = false;
            useCroppingActual.clear();
        }

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

        if (this.usePlantSpecies.isUsed() || this.usePlantVariety.isUsed())
        {
            if (first)
                query += " where ";
            else
                query += " and ";

            if (this.usePlantSpecies.isUsed() && this.usePlantVariety.isUsed())
                query += " ( ";

            if (this.usePlantSpecies.isUsed())
            {
                query += usePlantSpecies.getSQLClause("d");
                first = false;
            }

            if (this.usePlantSpecies.isUsed() && this.usePlantVariety.isUsed())
                query += " or ";
            if (this.usePlantVariety.isUsed())
            {
                query += usePlantVariety.getSQLClause("d");
                first = false;
            }
            if (this.usePlantSpecies.isUsed() && this.usePlantVariety.isUsed())
                query += " ) ";
            usePlantSpecies.clear();
            usePlantVariety.clear();
        }
        return query;

    }   //  buildCommonSQL()

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

        return Collections.emptyList();
    }

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

        return Collections.emptyList();
    }	// processResults_MySQL()

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

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

        CroppingActual item = null;

        while (rs.next())
        {
            int croppingActualId = rs.getInt("d_croppingActualId");
            int croppingPlanId = rs.getInt("d_croppingPlanId");
            int plantSpeciesId = rs.getInt("d_plantSpeciesId");
            int tmp_plantVarietyId = rs.getInt("d_plantVarietyId");
            Integer plantVarietyId = rs.wasNull() ? null : tmp_plantVarietyId;
            LocalDateTime lastUpdated = rs.getTimestamp("d_lastUpdated").toLocalDateTime();
            LocalDateTime created = rs.getTimestamp("d_created").toLocalDateTime();
            LOGGER.debug("croppingActualId: {}, croppingPlanId: {}, plantSpeciesId: {}, plantVarietyId: {}, lastUpdated: {}, created: {}",
                    croppingActualId, croppingPlanId, plantSpeciesId, plantVarietyId, lastUpdated, created);   //  2.0.1
            if (item != null && croppingActualId == item.getId())
            {// additional comment on the item
                LOGGER.debug("processResults(): got additional comment for: {}", item);
                Comment comm = new Comment(rs.getInt("commentId"),
                        rs.getInt("ownerId"),
                        NotebookEntryType.CROPPINGACTUAL.type(),
                        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 CroppingActual(item, comm);
            }
            else
            {
                if (item != null) tempList.add(item);
                int cid = rs.getInt("c_commentId");
                if (rs.wasNull())
                {// no comment
                    item = new CroppingActual(croppingActualId, croppingPlanId, plantSpeciesId, plantVarietyId, lastUpdated, created);
                }
                else
                {// new item with comment
                    Comment comm = new Comment(cid,
                            croppingActualId,
                            NotebookEntryType.CROPPINGACTUAL.type(),
                            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 CroppingActual(croppingActualId, croppingPlanId, plantSpeciesId, plantVarietyId, lastUpdated, created, comm);
                }
            }
        }
        if (item != null) tempList.add(item);

        LOGGER.traceExit(log4jEntryMsg);
        return tempList;
    }

    private void populatePlantSpecies(List<CroppingActual> vals, boolean fetchAll) throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("populatePlantSpecies()");

        if (fetchAll)
        {
            new PlantSpeciesLister().fetch();
            return;
        }
        int[] keys = vals.stream().
                mapToInt(CroppingActual::getPlantSpeciesId).filter(c -> c>0).distinct().
                filter(c -> !MySQLCache.cachePlantSpecies.containsKey(c)).
                toArray();

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

    private void populatePlantVariety(List<CroppingActual> vals, boolean fetchAll) throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("populatePlantVariety()");

        if (fetchAll)
        {
            new PlantVarietyLister().fetch();
            return;
        }
        int[] keys = vals.stream().
                map(CroppingActual::getPlantVarietyId).filter(Objects::nonNull).
                mapToInt(Integer::intValue).filter(c -> c>0).distinct().
                toArray();

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

    void clear()
    {
        MySQLCache.cacheCroppingActual.clear();
        MySQLCache.completeCroppingActual = false;
        MySQLCache.invalidCroppingActual = true;
    }

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

    @Override
    public ICroppingActualLister croppingActual(ICroppingActual... items)
    {
        useCroppingActual.item(items);
        useWhere = useWhere || useCroppingActual.isUsed();
        return this;
    }

    @Override
    public ICroppingActualLister croppingActual(List<ICroppingActual> items)
    {
        useCroppingActual.item(items);
        useWhere = useWhere || useCroppingActual.isUsed();
        return this;
    }

    @Override
    public ICroppingActualLister croppingPlan(ICroppingPlan... items)
    {
        useCroppingPlan.item(items);
        useWhere = useWhere || useCroppingPlan.isUsed();
        return this;
    }

    @Override
    public ICroppingActualLister croppingPlan(List<ICroppingPlan> items)
    {
        useCroppingPlan.item(items);
        useWhere = useWhere || useCroppingPlan.isUsed();
        return this;
    }

    @Override
    public ICroppingActualLister plantSpecies(IPlantSpecies... items)
    {
        usePlantSpecies.item(items);
        useWhere = useWhere || usePlantSpecies.isUsed();
        return this;
    }

    @Override
    public ICroppingActualLister plantSpecies(List<IPlantSpecies> items)
    {
        usePlantSpecies.item(items);
        useWhere = useWhere || usePlantSpecies.isUsed();
        return this;
    }

    @Override
    public ICroppingActualLister plantVariety(IPlantVariety... items)
    {
        usePlantVariety.item(items);
        useWhere = useWhere || usePlantVariety.isUsed();
        return this;
    }

    @Override
    public ICroppingActualLister plantVariety(List<IPlantVariety> items)
    {
        usePlantVariety.item(items);
        useWhere = useWhere || usePlantVariety.isUsed();
        return this;
    }

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

        JsonArrayBuilder jsonHc = builderFactory.createArrayBuilder();
        for (ICroppingActual ihc : MySQLCache.cacheCroppingActual.values())
        {
            CroppingActual hc = (CroppingActual)ihc;
            jsonHc.add(hc.toJson(builderFactory));
        }

        JsonObjectBuilder job = builderFactory.createObjectBuilder();
        job.add("JsonMode", "DUMP");
        job.add("JsonNBClass", "CroppingActual");
        job.add("values", jsonHc);

        try (JsonWriter writer = writerFactory.createWriter(new FileWriter(new File(dumpDirectory, "CroppingActual.json"), false)))
        {
            writer.writeObject(job.build());
        } catch (IOException ex) {
            LOGGER.error("toJson(): IOException", ex);
        }
    }	// toJson

}
