/* 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.artifacts.model.minfo;

import gnu.trove.TDoubleArrayList;

import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.TreeMap;

import net.sf.ehcache.Cache;
import net.sf.ehcache.Element;

import org.apache.log4j.Logger;
import org.dive4elements.river.artifacts.cache.CacheFactory;
import org.dive4elements.river.artifacts.model.Range;
import org.dive4elements.river.artifacts.model.RiverFactory;
import org.dive4elements.river.artifacts.model.StaticSedimentLoadCacheKey;
import org.dive4elements.river.backend.SessionHolder;
import org.dive4elements.river.model.MeasurementStation;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.type.StandardBasicTypes;


/** Pull Sediment Loads out of db. */
public class SedimentLoadFactory
{
    /** Private logger to use here. */
    private static Logger log = Logger.getLogger(SedimentLoadFactory.class);

    // Cache name/keys
    public static final String LOADS_CACHE_NAME = "sedimentloads";
    public static final String LOAD_DATA_CACHE_NAME = "sedimentload-data";

    /** Query to get description and start year of single type
     * sediment_load_lss. */
    public static final String SQL_SELECT_SINGLES =
        "SELECT DISTINCT " +
        "       sy.description AS description, " +
        "       ti.start_time AS year " +
        "   FROM     sediment_load_ls sy " +
        "       JOIN rivers r ON sy.river_id = r.id " +
        "       JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
        "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
        "   WHERE   r.name = :name " +
        "       AND ti.stop_time IS NULL " +
        "       AND syv.station BETWEEN :startKm AND :endKm";

    /** Query to get fraction name of single sediment_load_ls. */
    public static final String SQL_SELECT_SINGLE_TIMES_BY_ID =
        "SELECT DISTINCT " +
        "       ti.start_time AS starttime, " +
        "       ti.stop_time  AS stoptime " +
        "   FROM    sediment_load_ls sy " +
        "   JOIN    time_intervals ti ON ti.id = sy.time_interval_id " +
        "   WHERE   sy.id = :id ";

    /** Query to get fraction name of single sediment_load_ls. */
    public static final String SQL_SELECT_SINGLE_FRACTION_BY_ID =
        "SELECT DISTINCT " +
        "       gf.name AS fraction " +
        "   FROM    sediment_load_ls sy " +
        "   JOIN    grain_fraction gf ON gf.id = grain_fraction_id " +
        "   WHERE   sy.id = :id ";

    /** Query to get unit name of single sediment_load_ls. */
    public static final String SQL_SELECT_SINGLE_UNIT_BY_ID =
        "SELECT DISTINCT " +
        "       u.name AS unit " +
        "   FROM    sediment_load_ls sy " +
        "   JOIN    units u ON u.id = unit_id " +
        "   WHERE   sy.id = :id ";

    /** Query to get description of single sediment_load_ls. */
    public static final String SQL_SELECT_SINGLE_BY_ID =
        "SELECT DISTINCT " +
        "       sy.description AS description " +
        "   FROM    sediment_load_ls sy " +
        "   WHERE   sy.id = :id ";

    /** Query to get description, name and time range for official
     * epoch-type sediment yields. */
    public static final String SQL_SELECT_OFFEPOCHS =
        "SELECT DISTINCT " +
        "       ti.start_time AS startYear, " +
        "       ti.stop_time AS end " +
        "   FROM     sediment_load_ls sy " +
        "       JOIN rivers r ON sy.river_id = r.id " +
        "       JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
        "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
        "   WHERE   r.name = :name " +
        "       AND ti.stop_time IS NOT NULL " +
        "       AND syv.station BETWEEN :startKm AND :endKm " +
        "       AND sy.kind = 1";

    /** Query to get description, name and time range for epoch-type
     * sediment yields. */
    public static final String SQL_SELECT_EPOCHS =
        "SELECT DISTINCT " +
        "       sy.description AS description, " +
        "       ti.start_time AS startYear, " +
        "       ti.stop_time AS end, " +
        "       u.name AS unit" +
        "   FROM     sediment_load_ls sy " +
        "       JOIN rivers r ON sy.river_id = r.id " +
        "       JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
        "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
        "       JOIN units u ON u.id = sy.unit_id " +
        "   WHERE   r.name = :name " +
        "       AND ti.stop_time IS NOT NULL " +
        "       AND sy.kind = 0" +
        "       AND syv.station BETWEEN :startKm AND :endKm";

    public static final String SQL_SELECT_SINGLES_DATA_BY_ID =
        "SELECT" +
        "       sy.description AS description, " +
        "       syv.value AS load, " +
        "       syv.station AS km, " +
        "       u.name AS unit, " +
        "       gf.name AS fraction " +
        "   FROM     sediment_load_ls sy " +
        "       JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
        "       JOIN units u ON u.id = sy.unit_id" +
        "       JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
        "   WHERE   sy.id = :id" +
        "   ORDER BY syv.station";

    public static final String SQL_SELECT_SINGLES_DATA =
        "SELECT" +
        "       sy.description AS description, " +
        "       ti.start_time AS year, " +
        "       syv.value AS load, " +
        "       syv.station AS km, " +
        "       u.name AS unit " +
        "   FROM     sediment_load_ls sy " +
        "       JOIN rivers r ON sy.river_id = r.id " +
        "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
        "       JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
        "       JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
        "       JOIN units u ON u.id = sy.unit_id" +
        "   WHERE   r.name = :name " +
        "       AND ti.start_time BETWEEN :begin AND :end " +
        "       AND ti.stop_time IS NULL " +
        "       AND gf.name = :grain " +
        "       AND syv.station BETWEEN :startKm AND :endKm " +
        "   ORDER BY syv.station";

    public static final String SQL_SELECT_UNKNOWN_DATA =
        "SELECT" +
        "       sy.description AS description, " +
        "       ti.start_time AS startYear, " +
        "       ti.stop_time AS end, " +
        "       syv.value AS load, " +
        "       syv.station AS km, " +
        "       u.name AS unit " +
        "   FROM     sediment_load_ls sy " +
        "       JOIN rivers r ON sy.river_id = r.id " +
        "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
        "       JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
        "       JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
        "       JOIN units u ON u.id = sy.unit_id" +
        "   WHERE   r.name = :name " +
        "       AND sy.description = :descr " +
        "       AND gf.name = 'unknown' " +
        "       AND sy.kind = :type" +
        "   ORDER BY syv.station";

    public static final String SQL_SELECT_UNKNOWN =
        "SELECT DISTINCT " +
        "    sy.description AS description, " +
        "    ti.start_time AS startYear, " +
        "    ti.stop_time AS end " +
        "FROM sediment_load_ls sy " +
        "    JOIN rivers r ON sy.river_id = r.id " +
        "    JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
        "    JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
        "    JOIN units u ON sy.unit_id = u.id " +
        "WHERE r.name = :river " +
        "    AND gf.name = 'unknown' " +
        "    AND sy.kind = :type " +
        "    AND u.name = :unit";

    public static final String SQL_SELECT_EPOCHS_DATA =
        "SELECT" +
        "       sy.description AS description, " +
        "       ti.start_time AS startYear, " +
        "       syv.value AS load, " +
        "       syv.station AS km," +
        "       ti.stop_time AS endYear, " +
        "       u.name AS unit" +
        "   FROM     sediment_load_ls sy " +
        "       JOIN rivers r ON sy.river_id = r.id " +
        "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
        "       JOIN sediment_load_ls_values syv ON sy.id = syv.sediment_load_ls_id " +
        "       JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
        "       JOIN units u ON u.id = sy.unit_id " +
        "   WHERE   r.name = :name " +
        "       AND ti.start_time BETWEEN :sbegin AND :send " +
        "       AND ti.stop_time BETWEEN :ebegin AND :eend " +
        "       AND gf.name = :grain " +
        "       AND syv.station BETWEEN :startKm AND :endKm " +
        "   ORDER BY syv.station";

    private SedimentLoadFactory() {
    }

    /**
     * @param river name of river.
     * @param type
     */
    public static SedimentLoadLSData[] getLoads(
        String river,
        String type,
        double startKm,
        double endKm
    ) {
        log.debug("SedimentLoadFactory.getLoads");
        Cache cache = CacheFactory.getCache(LOADS_CACHE_NAME);

        if (cache == null) {
            log.debug("Cache not configured.");
            return getSedimentLoadsUncached(river, type, startKm, endKm);
        }

        StaticSedimentLoadCacheKey key =
            new StaticSedimentLoadCacheKey(river, startKm, endKm, 0, 0);

        Element element = cache.get(key);

        if (element != null) {
            log.debug("SedimentLoad found in cache");
            return (SedimentLoadLSData[])element.getValue();
        }

        SedimentLoadLSData[] values =
            getSedimentLoadsUncached(river, type, startKm, endKm);

        if (values != null) {
            log.debug("Store static sediment load values in cache.");
            element = new Element(key, values);
            cache.put(element);
        }
        return values;
    }

    /**
     * Get a sedimentLoad filled with data from db (or cache).
     * @param type "epoch", "off_epoch" or "single"
     * @return A Sedimentload filled with values from db or cache.
     */
    public static SedimentLoadLSData getLoadWithData(
        String river,
        String type,
        double startKm,
        double endKm,
        int syear,
        int eyear
    ) {
        log.debug("SedimentLoadFactory.getLoadWithData");
        Cache cache = CacheFactory.getCache(LOAD_DATA_CACHE_NAME);

        if (cache == null) {
            log.debug("Cache not configured.");
            return getSedimentLoadWithDataUncached(
                river,
                type,
                startKm,
                endKm,
                syear,
                eyear);
        }

        StaticSedimentLoadCacheKey key =
            new StaticSedimentLoadCacheKey(river, startKm, endKm, syear, eyear);

        Element element = cache.get(key);

        if (element != null) {
            log.debug("SedimentLoad found in cache");
            return (SedimentLoadLSData)element.getValue();
        }

        SedimentLoadLSData values = getSedimentLoadWithDataUncached(
            river,
            type,
            startKm,
            endKm,
            syear,
            eyear);

        if (values != null) {
            log.debug("Store sediment loads in cache.");
            element = new Element(key, values);
            cache.put(element);
        }
        return values;
    }

    /**
     * Get sediment yields time definition.
     * @param id the sediment yield by id.
     * @return sediment yields fraction name.
     */
    public static Date[] getSedimentYieldTimes(int id) {
        log.debug("SedimentLoadFactory.getSedimentYieldTimes");

        Session session = SessionHolder.HOLDER.get();
        SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLE_TIMES_BY_ID)
                .addScalar("starttime", StandardBasicTypes.DATE)
                .addScalar("stoptime", StandardBasicTypes.DATE);
        sqlQuery.setInteger("id", id);

        List<Object[]> results = sqlQuery.list();
        Object[] row = results.get(0);

        return new Date[] {(Date)row[0], (Date) row[1]};
    }

    /**
     * Get sediment load unit name.
     * @param id the sediment yield by id.
     * @return sediment yields unit name.
     */
    public static String getSedimentYieldUnitName(int id) {
        log.debug("SedimentLoadFactory.getSedimentYieldUnitName");

        Session session = SessionHolder.HOLDER.get();
        SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLE_UNIT_BY_ID)
                .addScalar("unit", StandardBasicTypes.STRING);
        sqlQuery.setDouble("id", id);

        List<Object> results = sqlQuery.list();

        return (String) results.get(0);
    }

    /**
     * Get sediment load fraction name.
     * @param id the sediment yield by id.
     * @return sediment yields fraction name.
     */
    public static String getSedimentYieldFractionName(int id) {
        log.debug("SedimentLoadFactory.getSedimentYieldFractionName");

        Session session = SessionHolder.HOLDER.get();
        SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLE_FRACTION_BY_ID)
                .addScalar("fraction", StandardBasicTypes.STRING);
        sqlQuery.setDouble("id", id);

        List<Object> results = sqlQuery.list();

        return (String) results.get(0);
    }


    /**
     * Get sediment load description.
     * @param id the sediment yield by id.
     * @return sediment yields description
     */
    public static String getSedimentYieldDescription(int id) {
        log.debug("SedimentLoadFactory.getSedimentYieldDescription");

        Session session = SessionHolder.HOLDER.get();
        SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLE_BY_ID)
                .addScalar("description", StandardBasicTypes.STRING);
        sqlQuery.setDouble("id", id);

        List<Object> results = sqlQuery.list();

        return (String) results.get(0);
    }


    /**
     * Get sediment loads from db.
     * @param river the river
     * @param type the sediment load type (year or epoch)
     * @return according sediment loads.
     */
    public static SedimentLoadLSData[] getSedimentLoadsUncached(
        String river,
        String type,
        double startKm,
        double endKm
    ) {
        log.debug("SedimentLoadFactory.getSedimentLoadsUncached");

        Session session = SessionHolder.HOLDER.get();
        SQLQuery sqlQuery = null;

        if (type.equals("single")) {
            sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLES)
                .addScalar("description", StandardBasicTypes.STRING)
                .addScalar("year", StandardBasicTypes.DATE);
            sqlQuery.setString("name", river);
            sqlQuery.setDouble("startKm", startKm);
            sqlQuery.setDouble("endKm", endKm);
            List<Object []> results = sqlQuery.list();
            SedimentLoadLSData[] loads = new SedimentLoadLSData[results.size()];
            for (int i = 0, R = results.size(); i < R; i++) {
                Object[] row = results.get(i);
                loads[i] = new SedimentLoadLSData(
                    (String) row[0],
                    (Date) row[1],
                    null,
                    false,
                    "");
            }
            return loads;
        }
        else if (type.equals("epoch")) {
            sqlQuery = session.createSQLQuery(SQL_SELECT_EPOCHS)
                .addScalar("description", StandardBasicTypes.STRING)
                .addScalar("startYear", StandardBasicTypes.DATE)
                .addScalar("end", StandardBasicTypes.DATE);
            sqlQuery.setString("name", river);
            sqlQuery.setDouble("startKm", startKm);
            sqlQuery.setDouble("endKm", endKm);
            List<Object []> results = sqlQuery.list();

            SedimentLoadLSData[] loads = new SedimentLoadLSData[results.size()];
            for (int i = 0, R = results.size(); i < R; i++) {
                Object[] row = results.get(i);
                loads[i] = new SedimentLoadLSData(
                    (String) row[0],
                    (Date) row[1],
                    (Date) row[2],
                    true,
                    "");
            }
            return loads;
        }
        else if (type.equals("off_epoch")) {
            sqlQuery = session.createSQLQuery(SQL_SELECT_OFFEPOCHS)
                .addScalar("startYear", StandardBasicTypes.DATE)
                .addScalar("end", StandardBasicTypes.DATE);
            sqlQuery.setString("name", river);
            sqlQuery.setDouble("startKm", startKm);
            sqlQuery.setDouble("endKm", endKm);
            List<Object []> results = sqlQuery.list();

            SedimentLoadLSData[] loads = new SedimentLoadLSData[results.size()];
            for (int i = 0, R = results.size(); i < R; i++) {
                Object[] row = results.get(i);
                loads[i] = new SedimentLoadLSData(
                    ((Date) row[0]).toString() + (Date) row[1],
                    (Date) row[0],
                    (Date) row[1],
                    true,
                    "");
            }
            return loads;
        }
        else {
             log.warn("getSedimentLoadsUncached does not understand type " + type);
        }
        return new SedimentLoadLSData[0];
    }

    /**
     * Get a specific sediment load from db.
     *
     * @param id the sediment yields id.
     *
     * @return according sediment load.
     */
    public static SedimentLoadLSData getSedimentLoadWithDataUncached(
        String id,
        String river
    ) {
        log.debug("SedimentLoadFactory.getSedimentLoadWithDataUncached / id " + id);
        Session session = SessionHolder.HOLDER.get();
        SQLQuery sqlQuery = null;

        // Measurement stations: all, for float-stuff, for suspended stuff.
        // Because they need fast sorted access, use TreeMaps.
        // They map the starting validity range km to the station itself.
        List<MeasurementStation> allStations =
            RiverFactory.getRiver(river).getMeasurementStations();
        TreeMap<Double,MeasurementStation> floatStations =
            new TreeMap<Double, MeasurementStation>();
        TreeMap<Double,MeasurementStation> suspStations =
            new TreeMap<Double, MeasurementStation>();

        // From all stations, sort into the two kinds, skip undefined ones.
        for (MeasurementStation measurementStation: allStations) {
            if (measurementStation.getMeasurementType() == null ||
                measurementStation.getRange() == null) {
                continue;
            }
            if (measurementStation.getMeasurementType().equals("Schwebstoff")) {
                suspStations.put(
                    measurementStation.getRange().getA().doubleValue(),
                    measurementStation);
            }
            else if (measurementStation.getMeasurementType().equals("Geschiebe")) {
                floatStations.put(
                    measurementStation.getRange().getA().doubleValue(),
                    measurementStation);
            }
        }

        sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLES_DATA_BY_ID)
            .addScalar("description", StandardBasicTypes.STRING)
            .addScalar("load", StandardBasicTypes.DOUBLE)
            .addScalar("km", StandardBasicTypes.DOUBLE)
            .addScalar("fraction", StandardBasicTypes.STRING)
            .addScalar("unit", StandardBasicTypes.STRING);
        sqlQuery.setInteger("id", Integer.valueOf(id));

        List<Object []> results = sqlQuery.list();
        SedimentLoadLSData load = new SedimentLoadLSData();
        if (results.isEmpty()) {
            log.warn("Empty result for year calculation.");
        }
        else {
            Object[] row = results.get(0);
            load = new SedimentLoadLSData(
                    (String) row[0], //description
                    null,//(Date) row[1], //start
                    null,  //end
                    false, //isEpoch
                    (String) row[4]); //unit

            String fraction = (String) row[3];

            TreeMap<Double,MeasurementStation> relevantStations =
                fraction.equals("suspended_sediment") /* || TODO clarify: fraction.equals("susp_sand") */
                ? suspStations
                : floatStations;

            for (int i = 0, R = results.size(); i < R; i++) {
                row = results.get(i);
                double km = (Double) row[2];
                Range range = findMeasurementStationRange(relevantStations, km);
                if (range == null) {
                    log.warn("No measurement station for " + fraction + " km " + km);
                    continue;
                }

                double v = -1;

                if (row[1] != null) {
                    v = ((Double)row[1]).doubleValue();
                }

                setLoadFraction(load, km, v, range, fraction);
            }

        }

        return load;
    }

    /**
     * Get sediment loads from db.
     *
     * @param river the river
     * @param type the sediment load type (year, epoch or off_epoch)
     *
     * @return according sediment loads.
     */
    public static SedimentLoadLSData getSedimentLoadWithDataUncached(
        String river,
        String type,
        double startKm,
        double endKm,
        int syear,
        int eyear
    ) {
        log.debug("SedimentLoadFactory.getSedimentLoadWithDataUncached");
        Session session = SessionHolder.HOLDER.get();
        SQLQuery sqlQuery = null;

        // Measurement stations: all, for float-stuff, for suspended stuff.
        // Because they need fast sorted access, use TreeMaps.
        // They map the starting validity range km to the station itself.
        List<MeasurementStation> allStations =
            RiverFactory.getRiver(river).getMeasurementStations();
        TreeMap<Double,MeasurementStation> floatStations =
            new TreeMap<Double, MeasurementStation>();
        TreeMap<Double,MeasurementStation> suspStations =
            new TreeMap<Double, MeasurementStation>();

        // From all stations, sort into the two kinds, skip undefined ones.
        for (MeasurementStation measurementStation: allStations) {
            if (measurementStation.getMeasurementType() == null ||
                measurementStation.getRange() == null) {
                continue;
            }
            if (measurementStation.getMeasurementType().equals("Schwebstoff")) {
                suspStations.put(
                    measurementStation.getRange().getA().doubleValue(),
                    measurementStation);
            }
            else if (measurementStation.getMeasurementType().equals("Geschiebe")) {
                floatStations.put(
                    measurementStation.getRange().getA().doubleValue(),
                    measurementStation);
            }
        }

        // Construct date constraint.
        Calendar start = Calendar.getInstance();
        start.set(syear - 1, 11, 31);
        Calendar end = Calendar.getInstance();
        end.set(syear, 11, 30);

        if (type.equals("year") || type.equals("epoch")) {
            sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLES_DATA)
                .addScalar("description", StandardBasicTypes.STRING)
                .addScalar("year", StandardBasicTypes.DATE)
                .addScalar("load", StandardBasicTypes.DOUBLE)
                .addScalar("km", StandardBasicTypes.DOUBLE)
                .addScalar("unit", StandardBasicTypes.STRING);
            sqlQuery.setString("name", river);
            sqlQuery.setDouble("startKm", startKm);
            sqlQuery.setDouble("endKm", endKm);
            sqlQuery.setDate("begin", start.getTime());
            sqlQuery.setDate("end", end.getTime());
            sqlQuery.setString("grain", "total");
            List<Object []> results = sqlQuery.list();
            SedimentLoadLSData load = new SedimentLoadLSData();
            if (results.isEmpty()) {
                log.warn("Empty result for year calculation.");
            }
            else {
                Object[] row = results.get(0);
                load = new SedimentLoadLSData(
                        (String) row[0], //description
                        (Date) row[1], //start
                        null,  //end
                        false, //isEpoch
                        (String) row[4]); //unit
            }
            load = getValues("coarse", sqlQuery, load, floatStations);
            load = getValues("fine_middle", sqlQuery, load, floatStations);
            load = getValues("sand", sqlQuery, load, floatStations);
            load = getValues("suspended_sediment", sqlQuery, load, suspStations);
            load = getValues("susp_sand_bed", sqlQuery, load, floatStations);
            load = getValues("susp_sand", sqlQuery, load, floatStations);

            return load;
        }
        else if (type.equals("off_epoch")) {
            // Set calendars to fetch the epochs by their start and end
            // dates.
            Calendar toStart = Calendar.getInstance();
            toStart.set(eyear - 1, 11, 31);
            Calendar toEnd = Calendar.getInstance();
            toEnd.set(eyear+1, 00, 01);
            // Set query parameters.
            sqlQuery = session.createSQLQuery(SQL_SELECT_EPOCHS_DATA)
                .addScalar("description", StandardBasicTypes.STRING)
                .addScalar("startYear", StandardBasicTypes.DATE)
                .addScalar("load", StandardBasicTypes.DOUBLE)
                .addScalar("km", StandardBasicTypes.DOUBLE)
                .addScalar("endYear", StandardBasicTypes.DATE)
                .addScalar("unit", StandardBasicTypes.STRING);
            sqlQuery.setString("name", river);
            sqlQuery.setDouble("startKm", startKm);
            sqlQuery.setDouble("endKm", endKm);
            sqlQuery.setDate("sbegin", start.getTime());
            sqlQuery.setDate("send", end.getTime());
            sqlQuery.setDate("ebegin", toStart.getTime());
            sqlQuery.setDate("eend", toEnd.getTime());
            sqlQuery.setString("grain", "total");

            List<Object[]> results = null;
            results = sqlQuery.list();

            SedimentLoadLSData load = null;
            if (results.isEmpty()) {
                log.warn("No total results for off-epoch");
            }
            else {
                Object[] row = results.get(0);
                load = new SedimentLoadLSData(
                        (String) row[0],
                        (Date) row[1],
                        (Date) row[4],
                        true,
                        (String)row[5]);
                TDoubleArrayList kms = new TDoubleArrayList();
                for (int i = 0, R = results.size(); i < R; i++) {
                    row = results.get(i);
                    kms.add((Double)row[3]);
                    load.setLoadTotal((Double)row[3], (Double)row[2]);
                }
            }
            load = getValues("coarse", sqlQuery, load, floatStations);
            load = getValues("fine_middle", sqlQuery, load, floatStations);
            load = getValues("sand", sqlQuery, load, floatStations);
            load = getValues("suspended_sediment", sqlQuery, load, suspStations);
            load = getValues("susp_sand_bed", sqlQuery, load, floatStations);
            load = getValues("susp_sand", sqlQuery, load, floatStations);
            return load;
        }
        return new SedimentLoadLSData();
    }

    protected static Range findMeasurementStationRange(
        TreeMap<Double, MeasurementStation> stations,
        double km
    ) {
        MeasurementStation station = stations.get(km);
        if (station == null) {
            return null;
        }

        double endKm;

        if (stations.ceilingEntry(km + 0.1d) != null) {
            MeasurementStation nextStation = stations.ceilingEntry(km + 0.1d).getValue();
            endKm = nextStation.getRange().getA().doubleValue();
        }
        else {
            // TODO end-of-river instead of B.
            endKm = station.getRange().getB().doubleValue();
        }

        return new Range(
            station.getRange().getA().doubleValue(),
            endKm);
    }

    /**
     * Run query with grain parameter set to fraction, feed result into
     * load. Create load if null.
     *
     * @param fraction value to set 'grain' parameter in query to.
     * @param query query in which to set 'grain' parameter and run.
     * @param load[out] SedimentLoad which to populate with values. if null
     *                  and values are found, return a new load.
     * @return param load or new load if null.
     */
    protected static SedimentLoadLSData getValues (
        String fraction,
        SQLQuery query,
        SedimentLoadLSData load,
        TreeMap<Double, MeasurementStation> stations
    ) {
        query.setString("grain", fraction);
        List<Object[]> results = query.list();

        // We have the first results for this query, create new load.
        if (!results.isEmpty() && load == null) {
           Object[] row = results.get(0);
           load = new SedimentLoadLSData(
                   (String) row[0],
                   (Date) row[1],
                   (Date) row[4],
                   true,
                   (String)row[5]);
        }

        for (int i = 0, R = results.size(); i < R; i++) {
            Object[] row = results.get(i);
            double km = (Double)row[3];
            Range range = findMeasurementStationRange(stations, km);
            if (range == null) {
                log.warn("No measurement station for " + fraction + " km " + km);
                continue;
            }
            double v = -1;

            if (row[2] != null) {
                v = ((Double)row[2]).doubleValue();
            }

            setLoadFraction(load, km, v, range, fraction);
        }

        if (results.isEmpty()) {
            log.warn("No " + fraction + " values found.");
        }

        return load;
    }


    /** Set a fraction value of load to given km, value and range. */
    private static void setLoadFraction(
        SedimentLoadLSData load, double km, double v, Range range, String fraction) {
        if (fraction.equals("coarse")) {
            load.setCoarse(km, v, range);
        }
        else if (fraction.equals("sand")) {
            load.setSand(km, v, range);
        }
        else if (fraction.equals("fine_middle")) {
            load.setFineMiddle(km, v, range);
        }
        else if (fraction.equals("suspended_sediment")) {
            load.setSuspSediment(km, v, range);
        }
        else if (fraction.equals("susp_sand")) {
            load.setSuspSand(km, v, range);
        }
        else if (fraction.equals("susp_sand_bed")) {
            load.setSuspSandBed(km, v, range);
        }
        else if (fraction.equals("total")) {
            load.setLoadTotal(km, v, range);
        }
        else {
            log.error("Unknown fraction type " + fraction);
        }
    }

    public static SedimentLoadLSData getLoadUnknown(
        String river,
        String description,
        String type
    ) {
        log.debug("SedimentLoadFactory.getLoadWithData");
        Cache cache = CacheFactory.getCache(LOAD_DATA_CACHE_NAME);

        if (cache == null) {
            log.debug("Cache not configured.");
            return getSedimentLoadUnknownUncached(river, description, type);
        }

        StaticSedimentLoadCacheKey key =
            new StaticSedimentLoadCacheKey(river, 0d, 0d, 0, 0);

        Element element = cache.get(key);

        if (element != null) {
            log.debug("SedimentLoad found in cache");
            return (SedimentLoadLSData)element.getValue();
        }

        SedimentLoadLSData values =
            getSedimentLoadUnknownUncached(river, description, type);

        if (values != null && key != null) {
            log.debug("Store static bed height values in cache.");
            element = new Element(key, values);
            cache.put(element);
        }
        return values;
    }

    /**
     * Get sediment loads with fraction 'unknown' from db.
     * @param river the river
     * @param type the sediment load type (year or epoch)
     * @return according sediment loads.
     */
    public static SedimentLoadLSData getSedimentLoadUnknownUncached(
        String river,
        String description,
        String type
    ) {
        log.debug("SedimentLoadFactory.getSedimentLoadWithDataUncached");
        Session session = SessionHolder.HOLDER.get();
        SQLQuery sqlQuery = null;

        sqlQuery = session.createSQLQuery(SQL_SELECT_UNKNOWN_DATA)
            .addScalar("description", StandardBasicTypes.STRING)
            .addScalar("startYear", StandardBasicTypes.DATE)
            .addScalar("end", StandardBasicTypes.DATE)
            .addScalar("load", StandardBasicTypes.DOUBLE)
            .addScalar("km", StandardBasicTypes.DOUBLE)
            .addScalar("unit", StandardBasicTypes.STRING);
        sqlQuery.setString("name", river);
        sqlQuery.setString("descr", description);
        if (type.equals("off_epoch")) {
            sqlQuery.setInteger("type", 1);
        }
        else {
            sqlQuery.setInteger("type", 0);
        }
        List<Object []> results = sqlQuery.list();
        SedimentLoadLSData load = new SedimentLoadLSData();
        if (results.isEmpty()) {
            return new SedimentLoadLSData();
        }
        Object[] row = results.get(0);
        load = new SedimentLoadLSData(
            (String) row[0],
            (Date) row[1],
            (Date) row[2],
            false,
            (String)row[5]);

        for (int i = 0, R = results.size(); i < R; i++) {
            row = results.get(i);
            SedimentLoadFraction fraction = new SedimentLoadFraction();
            fraction.setUnknown((Double)row[3]);
            load.addKm((Double)row[4], fraction);
        }
        return load;
    }

    /**
     * Return sediment loads with 'unknown' fraction type.
     * @param river Name of the river
     * @param unit Restrict result set to those of given unit.
     * @param type Type like year, epoch, off_epoch
     */
    public static SedimentLoadLSData[] getSedimentLoadUnknown(
        String river,
        String unit,
        String type) {
        Session session = SessionHolder.HOLDER.get();
        SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_UNKNOWN)
            .addScalar("description", StandardBasicTypes.STRING)
            .addScalar("startYear", StandardBasicTypes.DATE)
            .addScalar("end", StandardBasicTypes.DATE);
        sqlQuery.setString("river", river);
        sqlQuery.setString("unit", unit);
        if (type.equals("off_epoch")) {
            sqlQuery.setInteger("type", 1);
        }
        else {
            sqlQuery.setInteger("type", 0);
        }
        List<Object[]> results = sqlQuery.list();
        SedimentLoadLSData[] loads = new SedimentLoadLSData[results.size()];
        int counter = 0;
        for (Object[] row: results) {
            loads[counter] = new SedimentLoadLSData(
                (String)row[0], (Date)row[1], (Date)row[2], false, "");
            counter++;
        }
        return loads;
    }
}
// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :