package de.intevation.flys.artifacts.model;

import de.intevation.flys.artifacts.model.FixingsOverview.Fixing;

import de.intevation.flys.artifacts.cache.CacheFactory;

import de.intevation.flys.backend.SessionHolder;

import java.util.List;

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

import org.hibernate.Session;
import org.hibernate.SQLQuery;

import org.hibernate.type.StandardBasicTypes;

public class FixingsColumnFactory
{
    public static final String CACHE_NAME = "fixing-columns";

    public static final String SQL_COLUMN_WS =
        "SELECT wcv.position AS km, wcv.w AS w " +
        "FROM wst_column_values wcv " +
        "WHERE wst_column_id = :column_id " +
        "ORDER by wcv.position";

    public static final String SQL_COLUMN_QS =
        "SELECT wqr.q AS q, r.a AS a, r.b AS b " +
        "FROM wst_column_q_ranges wcqr " +
        "JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id " +
        "JOIN ranges r         ON wqr.range_id        = r.id " +
        "WHERE wcqr.wst_column_id = :column_id ORDER by r.a";

    public static final FixingsColumnFactory INSTANCE =
        new FixingsColumnFactory();

    private FixingsColumnFactory() {
    }

    public FixingsColumn getColumnData(Fixing.Column column) {

        FixingsColumn result;

        Cache cache = CacheFactory.getCache(CACHE_NAME);

        if (cache != null) {
            Integer cacheKey = Integer.valueOf(column.getId());
            Element element = cache.get(cacheKey);

            if (element != null) {
                return (FixingsColumn)element.getValue();
            }

            result = getUncached(column);
            cache.put(new Element(cacheKey, result));
        }
        else {
            result = getUncached(column);
        }

        return result;
    }

    protected FixingsColumn getUncached(Fixing.Column column) {
        Session session = SessionHolder.HOLDER.get();

        SQLQuery sqlQuery = session.createSQLQuery(SQL_COLUMN_WS)
            .addScalar("km", StandardBasicTypes.DOUBLE)
            .addScalar("w",  StandardBasicTypes.DOUBLE);

        sqlQuery.setInteger("column_id", column.getId());

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

        double [] kms = new double[results.size()];
        double [] ws  = new double[kms.length];

        for (int i = 0; i < kms.length; ++i) {
            Object [] row = results.get(i);
            kms[i] = ((Double)row[0]).doubleValue();
            ws [i] = ((Double)row[1]).doubleValue();
        }

        sqlQuery = session.createSQLQuery(SQL_COLUMN_QS)
            .addScalar("q", StandardBasicTypes.DOUBLE)
            .addScalar("a", StandardBasicTypes.DOUBLE)
            .addScalar("b", StandardBasicTypes.DOUBLE);

        sqlQuery.setInteger("column_id", column.getId());

        results = sqlQuery.list();

        QRangeTree qs = new QRangeTree(
            results, QRangeTree.WITHOUT_COLUMN, 0, results.size());

        return new FixingsColumn(kms, ws, qs);
    }
}
// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :