package de.intevation.flys.artifacts.model;

import java.io.Serializable;

import de.intevation.flys.model.River;
import de.intevation.flys.model.Wst;
import de.intevation.flys.model.WstColumn;

import de.intevation.flys.backend.SessionHolder;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Collections;
import java.util.Iterator;

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

import org.hibernate.type.StandardBasicTypes;

public class WstValueTable
implements   Serializable
{

    // TODO: put this into a property file
    public static final String SQL_POS_WQ = 
        "SELECT wcv.position AS position,"                           +
        "       w,"                                                  +
        "       (SELECT q"                                           +
        "        FROM   wst_column_q_ranges wcqr"                    +
        "               JOIN wst_q_ranges wqr"                       +
        "                 ON wcqr.wst_q_range_id = wqr.id"           +
        "               JOIN ranges r"                               +
        "                 ON r.id = wqr.range_id"                    +
        "        WHERE  wcqr.wst_column_id = wc.id"                  +
        "               AND wcv.position BETWEEN r.a AND r.b) AS q," +
        "       wc.position AS column_pos "                          +
        "FROM   wst_column_values wcv"                               +
        "       JOIN wst_columns wc"                                 +
        "         ON wcv.wst_column_id = wc.id"                      +
        "       JOIN wsts w"                                         +
        "         ON wc.wst_id = w.id "                              +
        "WHERE  w.id = :wst_id"                                      +
        "ORDER  BY wcv.position ASC,"                                +
        "          wc.position DESC";

    public static class Column
    implements          Serializable
    {
        protected String name;

        public Column() {
        }

        public Column(String name) {
            this.name = name;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }
    }
    // class Column

    public static class Row
    implements          Serializable
    {
        double    km;
        double [] wq;

        public Row() {
        }

        public Row(double km, double [] wq) {
            this.km = km;
            this.wq = wq;
        }
    }
    // class Row

    protected List<Row> rows;

    protected Column [] columns;

    public WstValueTable() {
        rows = new ArrayList<Row>();
    }

    public WstValueTable(Column [] columns) {
        this();
        this.columns = columns;
    }

    public static WstValueTable getTable(River river) {
        return getTable(river, 0);
    }

    public static WstValueTable getTable(River river, int kind) {

        Session session = SessionHolder.HOLDER.get();

        Query query = session.createQuery(
            "from Wst where river=:river and kind=:kind");
        query.setParameter("river", river);
        query.setInteger("kind",    kind);

        List<Wst> wsts = query.list();

        if (wsts.isEmpty()) {
            return null;
        }

        Wst wst = wsts.get(0);

        // TODO: Do this sorting at database level
        List<WstColumn> wstColumns = new ArrayList(wst.getColumns());
        Collections.sort(wstColumns, new Comparator<WstColumn>() {
            public int compare(WstColumn a, WstColumn b) {
                int pa = a.getPosition();
                int pb = b.getPosition();
                if (pa < pb) return -1;
                if (pa > pb) return +1;
                return 0;
            }
        });

        Column [] columns = new Column[wstColumns.size()];
        for (int i = 0; i < columns.length; ++i) {
            columns[i] = new Column(wstColumns.get(i).getName());
        }

        // using native SQL here to avoid myriad of small objects.
        SQLQuery sqlQuery = session.createSQLQuery(SQL_POS_WQ)
            .addScalar("position",   StandardBasicTypes.DOUBLE)
            .addScalar("w",          StandardBasicTypes.DOUBLE)
            .addScalar("q",          StandardBasicTypes.DOUBLE)
            .addScalar("column_pos", StandardBasicTypes.INTEGER);

        sqlQuery.setInteger("wst_id", wst.getId());

        WstValueTable valueTable = new WstValueTable(columns);

        int lastColumnNo = -1;
        Row row          = null;

        for (Iterator<Object []> iter = sqlQuery.iterate(); iter.hasNext();) {
            Object [] result = iter.next();
            double km    = (Double) result[0];
            Double w     = (Double) result[1];
            Double q     = (Double) result[2];
            int columnNo = (Integer)result[3];

            if (columnNo > lastColumnNo) { // new row
                if (row != null) {
                    valueTable.rows.add(row);
                }
                row = new Row(km, new double[(columnNo+1) << 1]);
            }
            int idx = columnNo << 1;

            row.wq[idx  ] = w != null ? w : Double.NaN;
            row.wq[idx+1] = q != null ? q : Double.NaN;

            lastColumnNo = columnNo;
        }

        if (row != null) {
            valueTable.rows.add(row);
        }

        return valueTable;
    }
}
// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :