/*
 *
 *  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 jakarta.json.*;
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 java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
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;

/**
 *	Review article for a gardening year.
 *
 *	@author	Andy Gegg
 *	@version	3.1.0
 *	@since	1.0
 */
final class ReviewLister implements IReviewLister
{
    private static final Logger LOGGER = LogManager.getLogger();

    private final DBKeyHandler<IReview> useReview = new DBKeyHandler<>("reviewId");

    private boolean useYearInReview = false;
    private Year[] yearInReviewList = new Year[10];
    private int yearInReviewNext = 0;	// next free slot in list


    private boolean useCoverFrom = false;
    private LocalDate coverFrom;
    private boolean useCoverTo = false;
    private LocalDate coverTo;

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

    private boolean useWhere = false;

    ReviewLister(){}

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

        List<Review> 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 (Review ps : vals)
        {
            MySQLCache.cacheReview.putIfAbsent(ps.getId(), ps);
        }
        if (fetchAll) { MySQLCache.completeReview = 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.reviewId as l_reviewId, l.date as l_date, l.yearInReview as l_yearInReview, l.coverFrom as l_coverFrom, l.coverTo as l_coverTo, l.title as l_title, l.description as l_description,
                l.lastUpdated as l_lastUpdated, l.created as l_created, c.* from review 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 = 'RW') as c
                on l.reviewId = c_ownerId
                """;

        boolean first = true;

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

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

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

            yearInReviewList = Arrays.copyOf(yearInReviewList, yearInReviewNext);

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

            yearInReviewList = new Year[10];
            yearInReviewNext = 0;
            useYearInReview = false;
        }

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

            query += " l.coverFrom >= '" + this.coverFrom + "'";
            first = false;
            this.useCoverFrom = false;
        }
        if (this.useCoverTo)
        {
            if (first) query += " where ";
            else query += " and ";

            query += " l.coverTo <= '" + this.coverTo + "'";
            first = false;
            this.useCoverTo = false;
        }

        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 + "'";
            first = false;
            this.useToDate = false;
        }

        query += " order by l_yearInReview, l_coverFrom, l_coverTo, l_title, l_date, c_date";
        return query;
    }   //  buildSql()

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

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

        Review item = null;

        while (rs.next())
        {
            int reviewId = rs.getInt("l_reviewId");
            LocalDate date = rs.getDate("l_date").toLocalDate();
            LocalDate yearInReview_tmp = rs.getDate("l_yearInReview").toLocalDate();
            Year yearInReview = Year.of(yearInReview_tmp.getYear());
            Date tmp_coverFrom = rs.getDate("l_coverFrom");
            LocalDate coverFrom = rs.wasNull() ? null : tmp_coverFrom.toLocalDate();
            Date tmp_coverTo = rs.getDate("l_coverTo");
            LocalDate coverTo = rs.wasNull() ? null : tmp_coverTo.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("reviewId: {}, date: {}, yearInReview: {}, coverFrom: {}, coverTo: {}, title: {}, description: {}, lastUpdated: {}, created: {}",
                    reviewId, date, yearInReview, coverFrom, coverTo, title, description, lastUpdated, created);
            if (item != null && reviewId == 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.REVIEW.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 Review(item, comm);
            }
            else
            {
                if (item != null) tempList.add(item);
                int cid = rs.getInt("c_commentId");
                if (rs.wasNull())
                {// no comment
                    item = new Review(reviewId, date, yearInReview, coverFrom, coverTo, title, description, lastUpdated, created);
                }
                else
                {// new item with comment
                    Comment comm = new Comment(cid,
                            reviewId,
                            NotebookEntryType.REVIEW.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 Review(reviewId, date, yearInReview, coverFrom, coverTo, title, description, lastUpdated, created, comm);
                }
            }
        }
        if (item != null) tempList.add(item);

        LOGGER.traceExit(log4jEntryMsg);
        return tempList;
    }

    void clear()
    {
        MySQLCache.cacheReview.clear();
        MySQLCache.completeReview = false;
    }

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

    @Override
    public IReviewLister review(IReview... items)
    {
        useReview.item(items);
        useWhere = useWhere || useReview.isUsed();
        return this;
    }

    @Override
    public IReviewLister review(List<IReview> items)
    {
        useReview.item(items);
        useWhere = useWhere || useReview.isUsed();
        return this;
    }

    @Override
    public IReviewLister yearInReview(Year... vals)
    {
        if (vals == null) return this;
        if (vals.length == 0) return this;
        useYearInReview = true;
        if (yearInReviewNext + vals.length >= yearInReviewList.length)
        {
            yearInReviewList = Arrays.copyOf(yearInReviewList, yearInReviewList.length + vals.length + 10);
        }
        for (Year val : vals) {yearInReviewList[yearInReviewNext++] = val;}
        return this;
    }

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

    @Override
    public IReviewLister coverFrom(LocalDate date)
    {
        if (date == null) return this;
        this.coverFrom = date;
        this.useCoverFrom = true;
        this.useWhere = true;
        return this;
    }

    @Override
    public IReviewLister coverTo(LocalDate date)
    {
        if (date == null) return this;
        this.coverTo = date;
        this.useCoverTo = true;
        this.useWhere = true;
        return this;
    }

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

    @Override
    public IReviewLister 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 (IReview ihc : MySQLCache.cacheReview.values())
        {
            Review hc = (Review) ihc;
            jsonHc.add(hc.toJson(builderFactory));
        }

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

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

}
