/* Copyright (C) 2011, 2012, 2013 by Bundesanstalt für Gewässerkunde
 * Software engineering by Intevation GmbH
 *
 * 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;

import java.io.Serializable;
import java.util.List;

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.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

import org.dive4elements.river.backend.SessionHolder;
import org.hibernate.Query;
import org.hibernate.Session;

@Entity
@Table(name = "bed_height")
public class BedHeight implements Serializable {

    private Integer id;
    private Integer year;

    private String evaluationBy;
    private String description;

    private River river;

    private BedHeightType type;

    private LocationSystem locationSystem;

    private ElevationModel curElevationModel;

    private ElevationModel oldElevationModel;

    private Range range;

    private String sounding_width_info;
    private String notes;

    private List<BedHeightValue> values;

    public BedHeight() {
    }

    public BedHeight(final River river, final Integer year, final BedHeightType type, final LocationSystem locationSystem,
            final ElevationModel curElevationModel, final Range range) {
        this(river, year, type, locationSystem, curElevationModel, null, range, null, null, null, null);
    }

    public BedHeight(final River river, final Integer year, final BedHeightType type, final LocationSystem locationSystem,
            final ElevationModel curElevationModel, final ElevationModel oldElevationModel, final Range range, final String evaluationBy,
            final String description, final String sounding_width_info, final String notes) {
        this.river = river;
        this.year = year;
        this.type = type;
        this.locationSystem = locationSystem;
        this.curElevationModel = curElevationModel;
        this.oldElevationModel = oldElevationModel;
        this.range = range;
        this.evaluationBy = evaluationBy;
        this.description = description;
        this.sounding_width_info = sounding_width_info;
        this.notes = notes;
    }

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

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

    @OneToOne
    @JoinColumn(name = "river_id")
    public River getRiver() {
        return this.river;
    }

    public void setRiver(final River river) {
        this.river = river;
    }

    @Column(name = "year")
    public Integer getYear() {
        return this.year;
    }

    public void setYear(final Integer year) {
        this.year = year;
    }

    @OneToOne
    @JoinColumn(name = "type_id")
    public BedHeightType getType() {
        return this.type;
    }

    public void setType(final BedHeightType type) {
        this.type = type;
    }

    @OneToOne
    @JoinColumn(name = "location_system_id")
    public LocationSystem getLocationSystem() {
        return this.locationSystem;
    }

    public void setLocationSystem(final LocationSystem locationSystem) {
        this.locationSystem = locationSystem;
    }

    @OneToOne
    @JoinColumn(name = "cur_elevation_model_id")
    public ElevationModel getCurElevationModel() {
        return this.curElevationModel;
    }

    public void setCurElevationModel(final ElevationModel curElevationModel) {
        this.curElevationModel = curElevationModel;
    }

    @OneToOne
    @JoinColumn(name = "old_elevation_model_id")
    public ElevationModel getOldElevationModel() {
        return this.oldElevationModel;
    }

    public void setOldElevationModel(final ElevationModel oldElevationModel) {
        this.oldElevationModel = oldElevationModel;
    }

    @OneToOne
    @JoinColumn(name = "range_id")
    public Range getRange() {
        return this.range;
    }

    public void setRange(final Range range) {
        this.range = range;
    }

    @Column(name = "evaluation_by")
    public String getEvaluationBy() {
        return this.evaluationBy;
    }

    public void setEvaluationBy(final String evaluationBy) {
        this.evaluationBy = evaluationBy;
    }

    @Column(name = "description")
    public String getDescription() {
        return this.description;
    }

    public void setDescription(final String description) {
        this.description = description;
    }

    @Column(name = "sounding_width_info")
    public String getSoundingWidthInfo() {
        return this.sounding_width_info;
    }

    public void setSoundingWidthInfo(final String sounding_width_info) {
        this.sounding_width_info = sounding_width_info;
    }

    @Column(name = "notes")
    public String getNotes() {
        return this.notes;
    }

    public void setNotes(final String notes) {
        this.notes = notes;
    }

    @OneToMany
    @JoinColumn(name = "bed_height_id")
    public List<BedHeightValue> getValues() {
        return this.values;
    }

    public void setValues(final List<BedHeightValue> values) {
        this.values = values;
    }

    public static List<BedHeight> getBedHeights(final River river, final double kmLo, final double kmHi) {
        final Session session = SessionHolder.HOLDER.get();

        final Query query = session.createQuery("from BedHeight" + " where river=:river" + " and id in (select bedHeight.id from BedHeightValue"
                + " where station between :kmfrom and :kmto" + " group by bedHeight.id)");

        query.setParameter("river", river);
        query.setParameter("kmfrom", kmLo);
        query.setParameter("kmto", kmHi);

        final List<BedHeight> singles = query.list();

        return singles;
    }

    public static BedHeight getBedHeightById(final int id) {
        final Session session = SessionHolder.HOLDER.get();

        final Query query = session.createQuery("from BedHeight where id=:id");

        query.setParameter("id", id);

        final List<BedHeight> singles = query.list();

        return ((singles != null) && !singles.isEmpty()) ? singles.get(0) : null;
    }

    public static BedHeight getBedHeightByDescription(final River river, final String description, final double startKm, final double endKm) {

        final Session session = SessionHolder.HOLDER.get();

        final Query query = session.createQuery("FROM BedHeight" + " WHERE (TRIM(description)=:description) AND river=:river"
                + " AND id IN (SELECT bedHeight.id FROM BedHeightValue" + " WHERE station BETWEEN :kmfrom AND :kmto" + " GROUP BY bedHeight.id)");
        query.setParameter("river", river);
        query.setParameter("description", description);
        query.setParameter("kmfrom", startKm);
        query.setParameter("kmto", endKm);

        final List<BedHeight> singles = query.list();

        return ((singles != null) && !singles.isEmpty()) ? singles.get(0) : null;
    }

    public static List<BedHeight> getBedHeightEpochs(final River river, final double startKm, final double endKm) {

        final Session session = SessionHolder.HOLDER.get();
        final String description = "epoch";
        final Query query = session.createQuery("FROM BedHeight" + " WHERE lower(description) LIKE :description AND " + "river=:river"
                + " AND id IN (SELECT bedHeight.id FROM BedHeightValue" + " WHERE station BETWEEN :kmfrom AND :kmto" + " GROUP BY bedHeight.id)");
        query.setParameter("river", river);
        query.setParameter("description", "%" + description + "%");
        query.setParameter("kmfrom", startKm);
        query.setParameter("kmto", endKm);

        final List<BedHeight> singles = query.list();

        return ((singles != null) && !singles.isEmpty()) ? singles : null;
    }

    public static List<BedHeight> getBedHeightYear(final River river, final double startKm, final double endKm) {

        final Session session = SessionHolder.HOLDER.get();
        final String description = "epoch";
        final Query query = session.createQuery("FROM BedHeight" + " WHERE lower(description) NOT LIKE :description AND " + "river=:river"
                + " AND id IN (SELECT bedHeight.id FROM BedHeightValue" + " WHERE station BETWEEN :kmfrom AND :kmto" + " GROUP BY bedHeight.id)");
        query.setParameter("river", river);
        query.setParameter("description", "%" + description + "%");
        query.setParameter("kmfrom", startKm);
        query.setParameter("kmto", endKm);

        final List<BedHeight> singles = query.list();

        return ((singles != null) && !singles.isEmpty()) ? singles : null;
    }
}