/*
 *
 *  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.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 *	Free text diary entries not connected to any specific plant, pest, etc.
 *
 *	@author	Andy Gegg
 *	@version	3.1.0
 *	@since	1.0
 */
final class JournalLister implements IJournalLister
{
    private static final Logger LOGGER = LogManager.getLogger();

    private final DBKeyHandler<IJournal> useJournal = new DBKeyHandler<>("journalId");

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

    private boolean useWhere = false;

    JournalLister(){}

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

        List<Journal> vals = new ArrayList<>();
        boolean fetchAll = !useWhere;
        String query = buildSQL();

        LOGGER.debug("fetch(): query: {}", query);
        try (Connection conn = DBConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query);)
        {
            vals = processResults(rs);
        }
        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 (Journal ps : vals)
        {
            MySQLCache.cacheJournal.putIfAbsent(ps.getId(), ps);
        }
        if (fetchAll) { MySQLCache.completeJournal = true; }

        LOGGER.traceExit(log4jEntryMsg);
        return vals;
    }

    private String buildSQL()
    {
        // Note the use of l.name in the 'order by' clause.  SQLServer will not accept l_name here, although hsqldb and MySQL are quite happy.
        String query = """
                select l.journalId as l_journalId, l.date as l_date, l.title as l_title, l.description as l_description,
                l.lastUpdated as l_lastUpdated, l.created as l_created, c.* from journal as l
                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 = 'JN') as c  
                on l.journalId = c_ownerId
                """;

        boolean first = true;

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

            query += useJournal.getSQLClause("l");
            first = false;
            useJournal.clear();
        }

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

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

            query += " l.date <= '" + this.toDate + "'";
            this.useToDate = false;
        }

//        query += " order by l_date, LOWER(l_title), c_date";
        if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer)
        {// if l_title is used, it fails 'invalid column name' - but ONLY on SQLServer
            query += " order by l_date, LOWER(title), c_date";
        }
        else
        {
            query += " order by l_date, LOWER(l_title), c_date";
        }
        return query;
    }   //  buildSql()

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

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

        Journal item = null;

        while (rs.next())
        {
            int journalId = rs.getInt("l_journalId");
            LocalDate date = rs.getDate("l_date").toLocalDate();
            String title = rs.getString("l_title");
            String description = rs.getString("l_description");
            LocalDateTime lastUpdated = rs.getTimestamp("l_lastUpdated").toLocalDateTime();
            LocalDateTime created = rs.getTimestamp("l_created").toLocalDateTime();
            LOGGER.debug("journalId: {}, date: {}, name: {}, description: {}, lastUpdated: {}, created: {}",
                    journalId, date, title, description, lastUpdated, created);
            if (item != null && journalId == 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.JOURNAL.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 Journal(item, comm);
            }
            else
            {
                if (item != null) tempList.add(item);
                int cid = rs.getInt("c_commentId");
                if (rs.wasNull())
                {// no comment
                    item = new Journal(journalId, date, title, description, lastUpdated, created);
                }
                else
                {// new item with comment
                    Comment comm = new Comment(cid,
                            journalId,
                            NotebookEntryType.JOURNAL.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 Journal(journalId, date, title, description, lastUpdated, created, comm);
                }
            }
        }
        if (item != null) tempList.add(item);

        LOGGER.traceExit(log4jEntryMsg);
        return tempList;
    }

    void clear()
    {
        MySQLCache.cacheJournal.clear();
        MySQLCache.completeJournal = false;
    }

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

    @Override
    public IJournalLister journal(IJournal... items)
    {
        useJournal.item(items);
        useWhere = useWhere || useJournal.isUsed();
        return this;
    }

    @Override
    public IJournalLister journal(List<IJournal> items)
    {
        useJournal.item(items);
        useWhere = useWhere || useJournal.isUsed();
        return this;
    }

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

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

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

        JsonArrayBuilder jsonHc = builderFactory.createArrayBuilder();
        for (IJournal ihc : MySQLCache.cacheJournal.values())
        {
            Journal hc = (Journal)ihc;
            jsonHc.add(hc.toJson(builderFactory));
        }

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

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

}
