/*
 *
 *  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.LocalDate;
import java.time.LocalDateTime;
import java.time.Year;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

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

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

    private final DBKeyHandler<ICropRotationGroup> useCropRotationGroup = new DBKeyHandler<>("cropRotationGroupId");

    private final DBKeyHandler<ILocation> useLocation = new DBKeyHandler<>("locationId");

    private boolean useYearOfPlan = false;
    private Year[] yearOfPlanList = new Year[10];
    private int yearOfPlanNext = 0;	// next free slot in list

    private boolean useWhere = false;

    CroppingPlanLister() {}

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

        List<CroppingPlan> vals = new ArrayList<>();
        boolean fetchAll = !useWhere;
//        System.out.println("before build(): useYearOfPlan: "+useYearOfPlan);
        String query = buildSQL();
        LOGGER.debug("fetch(): query: {}", query);
//        System.out.println("fetch(): query: {}"+ query);

        try (Connection conn = DBConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query);
        )
        {
            vals = processResults(rs);
            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 (CroppingPlan vx : vals)
        {
            MySQLCache.cacheCroppingPlan.putIfAbsent(vx.getKey(), vx);
        }
        if (fetchAll) { MySQLCache.completeCroppingPlan = true; }

        LOGGER.traceExit(log4jEntryMsg);
        return vals;
    }

    private String buildSQL()
    {
        String query = "select d.croppingPlanId as d_croppingPlanId, " +
                "d.cropRotationGroupId as d_cropRotationGroupId, " +
                "d.locationId as d_locationId, " +
                "d.yearOfPlan as d_yearOfPlan, " +
                "d.lastUpdated as d_lastUpdated, " +
                "d.created as d_created," +
                " c.* from croppingplan 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.CROPPINGPLAN.type() + "') as c ";
        query += "on d.croppingPlanId = c_ownerId ";

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

        query += " order by d.yearOfPlan, d.locationId, d.cropRotationGroupId, c_date, c_commentId";

        return query;
    }   //  buildSQL()

    private String buildCommonSQL()
    {
        String query = "";
        boolean first = true;

        if (useCroppingPlan.isUsed())
        {
            if (first) query += " where ";
            else query += " and ";

            query += useCroppingPlan.getSQLClause("d");
            first = false;
            useCroppingPlan.clear();
        }

        if (useCropRotationGroup.isUsed())
        {
            if (first) query += " where ";
            else query += " and ";

            query += useCropRotationGroup.getSQLClause("d");
            first = false;
            useCropRotationGroup.clear();
        }

        if (useLocation.isUsed())
        {
            if (first) query += " where ";
            else query += " and ";

            query += useLocation.getSQLClause("d");
            first = false;
            useLocation.clear();
        }

//        System.out.println("build(): useYearOfPlan: "+useYearOfPlan);
        if (this.useYearOfPlan)
        {
            if (first) query += " where ";
            else query += " and ";
            first = false;

            yearOfPlanList = Arrays.copyOf(yearOfPlanList, yearOfPlanNext);

            switch (DBConnection.DB_IN_USE)
            {
                case MariaDB, MySQL -> {
                    if (yearOfPlanNext > 1) {
                        query += " yearOfPlan in (";
                        for(Year year : yearOfPlanList) { query += "'" + year + "', "; }
                        query = query.substring(0, query.length() - 2);
                        query += ")";
                    }
                    else
                        query += " yearOfPlan = " + yearOfPlanList[0];
                }
                case hsqldb, MSSQLServer -> {
                    if (yearOfPlanNext > 1) {
                        query += " YEAR(yearOfPlan) in (";
                        for(Year year : yearOfPlanList) { query += year + ", "; }
                        query = query.substring(0, query.length() - 2);
                        query += ")";
                    }
                    else
                        query += " YEAR(yearOfPlan) = " + yearOfPlanList[0];
                }
                default -> {
                    LOGGER.debug("buildCommonSQL(): no known rdbms");
//                    throw new GNDBException(new IllegalStateException("no known RDBMS"));
                }
            }

            yearOfPlanList = new Year[10];
            yearOfPlanNext = 0;
            useYearOfPlan = false;
        }

        return query;
    }   //  buildCommonSQL()

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

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

        CroppingPlan item = null;

        while (rs.next())
        {
            int croppingPlanId = rs.getInt("d_croppingPlanId");
            int cropRotationGroupId = rs.getInt("d_cropRotationGroupId");
            int locationId = rs.getInt("d_locationId");
            LocalDate yearOfPlan_tmp = rs.getDate("d_yearOfPlan").toLocalDate();
            Year yearOfPlan = Year.of(yearOfPlan_tmp.getYear());
            LocalDateTime lastUpdated = rs.getTimestamp("d_lastUpdated").toLocalDateTime();
            LocalDateTime created = rs.getTimestamp("d_created").toLocalDateTime();
            LOGGER.debug("croppingPlanId: {}, cropRotationGroupId: {}, locationId: {}, yearOfPlan: {}, lastUpdated: {}, created: {}",
                    croppingPlanId, cropRotationGroupId, locationId, yearOfPlan, lastUpdated, created);
            if (item != null && croppingPlanId == item.getId())
            {// additional comment on the item
                LOGGER.debug("processResults(): got additional comment for: {}", item);
                Comment comm = new Comment(rs.getInt("c_commentId"),
                        rs.getInt("c_ownerId"),
                        NotebookEntryType.CROPPINGPLAN.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 CroppingPlan(item, comm);
            }
            else
            {
                if (item != null) tempList.add(item);
                int cid = rs.getInt("c_commentId");
                if (rs.wasNull())
                {// no comment
                    item = new CroppingPlan(croppingPlanId, cropRotationGroupId, locationId, yearOfPlan, lastUpdated, created);
                }
                else
                {// new item with comment
                    Comment comm = new Comment(cid,
                            croppingPlanId,
                            NotebookEntryType.CROPPINGPLAN.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 CroppingPlan(croppingPlanId, cropRotationGroupId, locationId, yearOfPlan, lastUpdated, created, comm);
                }
            }
        }
        if (item != null) tempList.add(item);

        LOGGER.traceExit(log4jEntryMsg);
        return tempList;

    }   //  processResults()

    void clear()
    {
        MySQLCache.cacheCroppingPlan.clear();
        MySQLCache.completeCroppingPlan = false;
        MySQLCache.invalidCroppingPlan = true;
    }

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

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

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

    @Override
    public ICroppingPlanLister cropRotationGroup(ICropRotationGroup... items)
    {
        useCropRotationGroup.item(items);
        useWhere = useWhere || useCropRotationGroup.isUsed();
        return this;
    }

    @Override
    public ICroppingPlanLister cropRotationGroup(List<ICropRotationGroup> items)
    {
        useCropRotationGroup.item(items);
        useWhere = useWhere || useCropRotationGroup.isUsed();
        return this;
    }

    @Override
    public ICroppingPlanLister location(ILocation... items)
    {
        useLocation.item(items);
        useWhere = useWhere || useLocation.isUsed();
        return this;
    }

    @Override
    public ICroppingPlanLister location(List<ILocation> items)
    {
        useLocation.item(items);
        useWhere = useWhere || useLocation.isUsed();
        return this;
    }

    @Override
    public ICroppingPlanLister yearOfPlan(Year... yearOfPlan)
    {
        if (yearOfPlan == null) return this;
        if (yearOfPlan.length == 0) return this;
//        System.out.println("yearOfPlan: "+ Arrays.toString(yearOfPlan));
        useYearOfPlan = true;
        useWhere = true;
        if (yearOfPlanNext + yearOfPlan.length >= yearOfPlanList.length)
        {
            yearOfPlanList = Arrays.copyOf(yearOfPlanList, yearOfPlanList.length + yearOfPlan.length + 10);
        }
        for (Year val : yearOfPlan) {yearOfPlanList[yearOfPlanNext++] = val;}
//        System.out.println("yearOfPlanList: "+ Arrays.toString(yearOfPlanList)+", next: "+yearOfPlanNext+", use: "+useYearOfPlan);
        return this;
    }

    @Override
    public ICroppingPlanLister yearOfPlan(List<Year> yearOfPlan)
    {
        if (yearOfPlan == null) return this;
        if (yearOfPlan.isEmpty()) return this;
        return this.yearOfPlan(yearOfPlan.toArray(new Year[0]));
    }

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

        JsonArrayBuilder jsonHc = builderFactory.createArrayBuilder();
        for (ICroppingPlan ihc : MySQLCache.cacheCroppingPlan.values())
        {
            CroppingPlan hc = (CroppingPlan) ihc;
            jsonHc.add(hc.toJson(builderFactory));
        }

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

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

}
