/* Copyright (C) 2017 by Bundesanstalt für Gewässerkunde
 * Software engineering by
 *  Björnsen Beratende Ingenieure GmbH
 *  Dr. Schumacher Ingenieurbüro für Wasser und Umwelt
 *
 * This file is Free Software under the GNU AGPL (>=v3)
 * and comes with ABSOLUTELY NO WARRANTY! Check out the
 * documentation coming with Dive4Elements River for details.
 */

package org.dive4elements.river.model.sinfo;

import java.io.Serializable;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

import org.dive4elements.river.backend.SessionHolder;
import org.dive4elements.river.model.Gauge;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.type.StandardBasicTypes;

/**
 * Hibernate binding for the DB table daily_discharge_values
 *
 * @author Matthias Schäfer
 *
 */
@Entity
@Table(name = "daily_discharge_values")
public class DailyDischargeValue implements Serializable {

    /***** TYPES *****/

    /**
     * Field to use in a query's order-by clause
     *
     */
    public enum OrderByField {
        DAY, DISCHARGE;
    }


    /***** FIELDS *****/

    private static final long serialVersionUID = -6192738825193230784L;

    private Integer id;

    private DailyDischarge dailyDischarge;

    private Date day;

    private Double discharge;


    /***** CONSTRUCTORS *****/

    public DailyDischargeValue() {
    }

    public DailyDischargeValue(final DailyDischarge dailyDischarge, final Date day, final Double discharge) {
        this.dailyDischarge = dailyDischarge;
        this.day = day;
        this.discharge = discharge;
    }

    /**
     * Parameter constructor with primitive parameter types
     */
    public DailyDischargeValue(final DailyDischarge dailyDischarge, final Date day, final double discharge) {
        this(dailyDischarge, day, Double.valueOf(discharge));
    }


    /***** METHODS *****/

    @Id
    @SequenceGenerator(name = "SEQUENCE_DAILY_DISCHARGE_VALUE_ID_SEQ", sequenceName = "DAILY_DISCHARGE_VALUES_ID_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQUENCE_DAILY_DISCHARGE_VALUE_ID_SEQ")
    @Column(name = "id")
    public Integer getId() {
        return this.id;
    }

    public void setId(final Integer id) {
        this.id = id;
    }

    @OneToOne
    @JoinColumn(name = "daily_discharge_id")
    public DailyDischarge getDailyDischarge() {
        return this.dailyDischarge;
    }

    public void setDailyDischarge(final DailyDischarge dailyDischarge) {
        this.dailyDischarge = dailyDischarge;
    }

    @Column(name = "discharge")
    public Double getDischarge() {
        return this.discharge;
    }

    public void setDischarge(final Double discharge) {
        this.discharge = discharge;
    }

    @Column(name = "day")
    public Date getDay() {
        return this.day;
    }

    public void setDay(final Date day) {
        this.day = day;
    }

    /**
     * Selects from the database the daily discharge values of a gauge and a date range
     */
    public static List<DailyDischargeValue> getValues(final Gauge gauge, final Date startDate, final Date endDate, final OrderByField orderBy) {
        final Session session = SessionHolder.HOLDER.get();
        final String orderField = (orderBy == OrderByField.DISCHARGE) ? "discharge" : "day";
        final Query query = session.createQuery("SELECT v" + " FROM DailyDischargeValue AS v JOIN v.dailyDischarge AS s"
                + " WHERE (s.gauge.id=:gaugeid) AND (v.day BETWEEN :startDate AND :endDate)" + " ORDER BY " + orderField);
        query.setParameter("gaugeid", gauge.getId());
        query.setParameter("startDate", startDate);
        query.setParameter("endDate", endDate);
        return query.list();
    }

    /**
     * Selects from the database the daily discharge, puts the first and the last into a List (min, max day)
     */
    public static Date[] getTimePeriod(final Gauge gauge, final Date fromDay, final Date toDay) {
        final Session session = SessionHolder.HOLDER.get();
        final Query query = session.createQuery("SELECT MIN(v.day), MAX(v.day)"
                + " FROM DailyDischargeValue AS v JOIN v.dailyDischarge AS s"
                + " WHERE (s.gauge.id=:gaugeid) AND (v.day BETWEEN :startDate AND :endDate)");
        query.setParameter("gaugeid", gauge.getId());
        query.setDate("startDate", fromDay);
        query.setDate("endDate", toDay);
        final List<Object[]> list = query.list();
        if (!list.isEmpty()) {
            return new Date[] { (Date) list.get(0)[0], (Date) list.get(0)[1] };
        }
        return null;
    }

    /**
     * Fetches a map of minimum+maximum daily discharge dates (or null) by gauge for a list of gauges and a time period
     */
    public static Map<Gauge, Date[]> fetchGaugesTimePeriod(final List<Gauge> gauges, final Date fromDay, final Date toDay) {
        final Map<Gauge, Date[]> result = new TreeMap<>();
        final Map<Integer, Gauge> gaugelookup = new TreeMap<>();
        String gaugeids = "";
        String sep = "";
        for (final Gauge gauge : gauges) {
            gaugeids += sep + gauge.getId().toString();
            sep = ",";
            result.put(gauge, null);
            gaugelookup.put(gauge.getId(), gauge);
        }
        final Session session = SessionHolder.HOLDER.get();
        final SQLQuery query = session.createSQLQuery("SELECT g.id AS gauge_id, MIN(v.day) AS min_day, MAX(v.day) AS max_day"
                + "FROM (gauges g LEFT JOIN daily_discharge s ON g.id=s.gauge_id)"
                + "  LEFT JOIN daily_discharge_values v ON s.id=v.daily_discharge_id"
                + " WHERE g.id IN (:gaugeids)"
                + "  AND v.day BETWEEN :fromDay AND :toDay"
                + " GROUP BY g.id"
                + "UNION SELECT id, NULL, NULL"
                + "  FROM gauges"
                + "  WHERE id IN (:gaugeids)"
                + "   AND id NOT IN "
                +"    (SELECT s2.gauge_id FROM daily_discharge s2 INNER JOIN daily_discharge_values v2 ON s2.id=v2.daily_discharge_id"
                + "      WHERE v2.day BETWEEN :fromDay AND :toDay)"
                + "ORDER BY 1")
                .addScalar("gauge_id", StandardBasicTypes.INTEGER)
                .addScalar("min_day", StandardBasicTypes.DATE)
                .addScalar("max_day", StandardBasicTypes.DATE);
        query.setString("gaugeids", gaugeids);
        query.setDate("fromDay", fromDay);
        query.setDate("toDay", toDay);
        final List<Object[]> rows = query.list();
        if (!rows.isEmpty()) {
            // TODO handle nulls
            for (int i = 0; i <= rows.size() - 1; i++)
                result.put(gaugelookup.get(rows.get(i)[0]), new Date[] { (Date) rows.get(i)[1], (Date) rows.get(i)[2] });
            return result;
        }
        return new TreeMap<>();
    }
}