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

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 [] GSIEBSATZ = {
        "SIEB01", "SIEB02", "SIEB03", "SIEB04",
        "SIEB05", "SIEB06", "SIEB07", "SIEB08",
        "SIEB09", "SIEB10", "SIEB11", "SIEB13",
        "SIEB13", "SIEB14", "SIEB15", "SIEB16",
        "SIEB17", "SIEB18", "SIEB19", "SIEB20",
        "SIEB21"
    };

    public static final String [] SSIEBUNG = {
        "RSIEB01", "RSIEB02", "RSIEB03", "RSIEB04",
        "RSIEB05", "RSIEB06", "RSIEB07", "RSIEB08",
        "RSIEB09", "RSIEB10", "RSIEB11", "RSIEB13",
        "RSIEB13", "RSIEB14", "RSIEB15", "RSIEB16",
        "RSIEB17", "RSIEB18", "RSIEB19", "RSIEB20",
        "RSIEB21", "REST"
    };

    public static final String SQL_MEASSURE =
        ("SELECT " +
            "m.TSAND   AS TSAND,"   +
            "m.TSCHWEB AS TSCHWEB," +
            "m.CSCHWEB AS CSCHWEB," +
            "m.Q       AS Q," +
            "%GSIEBSATZ%" +
            "%SSIEBUNG%" +
        "FROM messung m " +
            "JOIN station   s ON m.stationid   = s.stationid " +
            "JOIN gewaesser g ON s.gewaesserid = g.gewaesserid " +
            "LEFT JOIN GSIEBSATZ gs ON m.gsiebsatzid = gs.gewaesserid " +
            "LEFT JOIN SSIEBUNG  ss ON gs.gsiebsatzid = ss.gewaesserid " +
            "WHERE " +
                "g.name = :river_name AND " +
                "s.km BETWEEN :location - 0.001 AND :location + 0.001 AND " +
                "m.datum BETWEEN :from AND :to AND " +
                "m.Q IS NOT NULL")
        .replace("%GSIEBSATZ%", projection("gs", GSIEBSATZ))
        .replace("%SSIEBUNG%",  projection("ss", SSIEBUNG));

    private static final String projection(
        String    prefix,
        String [] columnNames
    ) {
        StringBuilder sb = new StringBuilder();
        for (String columnName: columnNames) {
            sb.append(prefix)
              .append('.')
              .append(columnName)
              .append(" AS ")
              .append(columnName)
              .append(',');
        }
        return sb.toString();
    }

    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("TSAND",   StandardBasicTypes.DOUBLE)
            .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE)
            .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE)
            .addScalar("Q",       StandardBasicTypes.DOUBLE);

        for (String siebsatz: GSIEBSATZ) {
            query.addScalar(siebsatz, StandardBasicTypes.DOUBLE);
        }

        for (String siebung: SSIEBUNG) {
            query.addScalar(siebung, 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);

        return new Measurements(query.list());
    }

    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 :