package de.intevation.flys.artifacts.model.sq;

import java.util.List;

import de.intevation.flys.artifacts.model.DateRange;

import de.intevation.flys.backend.SedDBSessionHolder;

import java.util.HashMap;
import java.util.Map;

import org.apache.log4j.Logger;

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

import org.hibernate.transform.BasicTransformerAdapter;

import org.hibernate.type.StandardBasicTypes;

public class MeasurementFactory
{
    private static final Logger log =
        Logger.getLogger(MeasurementFactory.class);

    public static final String SQL_MEASSURE =
        "SELECT m.datum        AS DATUM," +
               "g.GLOTRECHTEID AS GLOTRECHTEID," +
               "gp.LFDNR       AS LFDNR," +
               "g.UFERABST     AS UFERABST," +
               "g.UFERABLINKS  AS UFERABLINKS," +
               "m.TSCHWEB      AS TSCHWEB," +
               "m.TSAND        AS TSAND," +
               "gp.MESSDAUER   AS MESSDAUER," +
               "gp.MENGE       AS MENGE," +
               "gp.GTRIEB      AS GTRIEB," +
               "m.TGESCHIEBE   AS TGESCHIEBE," +
               "gs.RSIEB01     AS RSIEB01," +
               "gs.RSIEB02     AS RSIEB02," +
               "gs.RSIEB03     AS RSIEB03," +
               "gs.RSIEB04     AS RSIEB04," +
               "gs.RSIEB05     AS RSIEB05," +
               "gs.RSIEB06     AS RSIEB06," +
               "gs.RSIEB07     AS RSIEB07," +
               "gs.RSIEB08     AS RSIEB08," +
               "gs.RSIEB09     AS RSIEB09," +
               "gs.RSIEB10     AS RSIEB10," +
               "gs.RSIEB11     AS RSIEB11," +
               "gs.RSIEB12     AS RSIEB12," +
               "gs.RSIEB13     AS RSIEB13," +
               "gs.RSIEB14     AS RSIEB14," +
               "gs.RSIEB15     AS RSIEB15," +
               "gs.RSIEB16     AS RSIEB16," +
               "gs.RSIEB17     AS RSIEB17," +
               "gs.RSIEB18     AS RSIEB18," +
               "gs.RSIEB19     AS RSIEB19," +
               "gs.RSIEB20     AS RSIEB20," +
               "gs.RSIEB21     AS RSIEB21," +
               "gs.REST        AS REST, " +
               "COALESCE(sie.SIEB01, 0) AS SIEB01, " +
               "COALESCE(sie.SIEB02, 0) AS SIEB02, " +
               "COALESCE(sie.SIEB03, 0) AS SIEB03, " +
               "COALESCE(sie.SIEB04, 0) AS SIEB04, " +
               "COALESCE(sie.SIEB05, 0) AS SIEB05, " +
               "COALESCE(sie.SIEB06, 0) AS SIEB06, " +
               "COALESCE(sie.SIEB07, 0) AS SIEB07, " +
               "COALESCE(sie.SIEB08, 0) AS SIEB08, " +
               "COALESCE(sie.SIEB09, 0) AS SIEB09, " +
               "COALESCE(sie.SIEB10, 0) AS SIEB10, " +
               "COALESCE(sie.SIEB11, 0) AS SIEB11, " +
               "COALESCE(sie.SIEB12, 0) AS SIEB12, " +
               "COALESCE(sie.SIEB13, 0) AS SIEB13, " +
               "COALESCE(sie.SIEB14, 0) AS SIEB14, " +
               "COALESCE(sie.SIEB15, 0) AS SIEB15, " +
               "COALESCE(sie.SIEB16, 0) AS SIEB16, " +
               "COALESCE(sie.SIEB17, 0) AS SIEB17, " +
               "COALESCE(sie.SIEB18, 0) AS SIEB18, " +
               "COALESCE(sie.SIEB19, 0) AS SIEB19, " +
               "COALESCE(sie.SIEB20, 0) AS SIEB20, " +
               "COALESCE(sie.SIEB21, 0) AS SIEB21 " +
        "FROM MESSUNG m " +
            "JOIN STATION    s ON m.STATIONID    = s.STATIONID " +
            "JOIN glotrechte g ON m.MESSUNGID    = g.MESSUNGID " +
            "JOIN gprobe    gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " +
            "JOIN GSIEBUNG  gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " +
        "WHERE " +
            "g.NAME = :river_name " +
            "AND m.Q_BPEGEL IS NOT NULL " +
            "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " +
            "AND m.DATUM BETWEEN :from AND :to " +
            "AND m.TGESCHIEBE IS NOT NULL " +
            "AND (" +
                "COALESCE(gs.RSIEB01, 0) + COALESCE(gs.RSIEB02, 0) +" +
                "COALESCE(gs.RSIEB03, 0) + COALESCE(gs.RSIEB04, 0) +" +
                "COALESCE(gs.RSIEB05, 0) + COALESCE(gs.RSIEB06, 0) +" +
                "COALESCE(gs.RSIEB07, 0) + COALESCE(gs.RSIEB08, 0) +" +
                "COALESCE(gs.RSIEB09, 0) + COALESCE(gs.RSIEB10, 0) +" +
                "COALESCE(gs.RSIEB11, 0) + COALESCE(gs.RSIEB12, 0) +" +
                "COALESCE(gs.RSIEB13, 0) + COALESCE(gs.RSIEB14, 0) +" +
                "COALESCE(gs.RSIEB15, 0) + COALESCE(gs.RSIEB16, 0) +" +
                "COALESCE(gs.RSIEB17, 0) + COALESCE(gs.RSIEB18, 0) +" +
                "COALESCE(gs.RSIEB19, 0) + COALESCE(gs.RSIEB20, 0) +" +
                "COALESCE(gs.RSIEB21, 0) + COALESCE(gs.REST, 0)) > 0 " +
        "ORDER BY m.DATUM, gp.LFDNR, g.UFERABST";

    public static final class MeasurementResultTransformer
    extends                   BasicTransformerAdapter
    {
        public static MeasurementResultTransformer INSTANCE =
            new MeasurementResultTransformer();

        public MeasurementResultTransformer() {
        }

        @Override
        public Object transformTuple(Object [] tuple, String [] aliases) {
            Map<String, Object> map = new HashMap<String, Object>();
            for (int i = 0; i < tuple.length; ++i) {
                if (tuple[i] != null) {
                    map.put(aliases[i], tuple[i]);
                }
            }
            return new Measurement(map);
        }
    } // class BasicTransformerAdapter

    private MeasurementFactory() {
    }

    protected static Measurements load(
        Session   session,
        String    river,
        double    location,
        DateRange dateRange
    ) {
        boolean debug = log.isDebugEnabled();

        if (debug) {
            log.debug(SQL_MEASSURE);
        }

        SQLQuery query = session.createSQLQuery(SQL_MEASSURE)
            .addScalar("Q_BPEGEL",     StandardBasicTypes.DOUBLE)
            .addScalar("DATUM",        StandardBasicTypes.DATE)
            .addScalar("GLOTRECHTEID", StandardBasicTypes.INTEGER)
            .addScalar("LFDNR",        StandardBasicTypes.INTEGER)
            .addScalar("UFERABST",     StandardBasicTypes.DOUBLE)
            .addScalar("UFERABLINKS",  StandardBasicTypes.DOUBLE)
            .addScalar("TSCHWEB",      StandardBasicTypes.DOUBLE)
            .addScalar("TSAND",        StandardBasicTypes.DOUBLE)
            .addScalar("MESSDAUER",    StandardBasicTypes.DOUBLE)
            .addScalar("MENGE",        StandardBasicTypes.DOUBLE)
            .addScalar("GTRIEB",       StandardBasicTypes.DOUBLE)
            .addScalar("TGESCHIEBE",   StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB01",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB02",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB03",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB04",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB05",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB06",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB07",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB08",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB09",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB10",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB11",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB12",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB13",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB14",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB15",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB16",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB17",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB18",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB19",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB20",      StandardBasicTypes.DOUBLE)
            .addScalar("RSIEB21",      StandardBasicTypes.DOUBLE)
            .addScalar("REST",         StandardBasicTypes.DOUBLE)
            .addScalar("SIEB01",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB02",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB03",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB04",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB05",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB06",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB07",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB08",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB09",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB10",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB11",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB12",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB13",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB14",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB15",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB16",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB17",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB18",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB19",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB20",       StandardBasicTypes.DOUBLE)
            .addScalar("SIEB21",       StandardBasicTypes.DOUBLE);

        query.setString("river_name", river);
        query.setDouble("location", location);
        query.setDate("from", dateRange.getFrom());
        query.setDate("to", dateRange.getTo());

        query.setResultTransformer(MeasurementResultTransformer.INSTANCE);

        @SuppressWarnings("unchecked")
		List<Measurement> measuments = (List<Measurement>)query.list();

        Integer lastLR = null;

        for (int i = 0, N = measuments.size(); i < N; ++i) {
            Measurement m = measuments.get(i);

            Integer currentLR = (Integer)m.getData("GLOTRECHTEID");

            boolean newDS = lastLR == null
                || (currentLR != null && !lastLR.equals(currentLR));

            Measurement p = i >   0 ? measuments.get(i-1) : null;
            Measurement n = i < N-1 ? measuments.get(i+1) : null;
            m.setPrev(newDS ? null : p);
            m.setNext(n);

            if (p != null && newDS) {
                p.setNext(null);
            }

            lastLR = currentLR;
        }

        return new Measurements(measuments);
    }

    public static Measurements getMeasurements(
        String    river,
        double    location,
        DateRange dateRange
    ) {
        Session session = SedDBSessionHolder.HOLDER.get();
        try {
            return load(session, river, location, dateRange);
        }
        finally {
            session.close();
        }
    }
}
// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :