package de.intevation.flys.artifacts.model;

import java.io.Serializable;

import java.text.SimpleDateFormat;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

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

import org.hibernate.type.StandardBasicTypes;

import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class FixingsOverview
implements   Serializable
{
    private static Logger log = Logger.getLogger(FixingsOverview.class);

    public static final double EPSILON  = 1e-5;
    public static final double EPSILON2 = 1e-1;

    public static final String SQL_RIVER_ID =
        "SELECT" +
        "    id AS river_id," +
        "    km_up " +
        "FROM rivers " +
        "WHERE" +
        "    name = :name";

    public static final String SQL_RIVER_EXTENT =
        "SELECT" +
        "    MIN(wcv.position) AS start_km," +
        "    MAX(wcv.position) AS end_km " +
        "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.kind = 0 AND w.river_id = :river_id";

    public static final String SQL_GAUGES =
        "SELECT" +
        "    g.id AS gauge_id," +
        "    r.a  AS a," +
        "    r.b  AS b " +
        "FROM gauges g" +
        "    JOIN ranges r ON g.range_id = r.id " +
        "WHERE" +
        "    g.river_id = :river_id " +
        "ORDER BY r.a";

    public static final String SQL_DISCHARGE_SECTORS =
        "SELECT" +
        "    g.id                            AS gauge_id," +
        "    nmv.name                        AS name," +
        "    CAST(mv.value AS NUMERIC(38,2)) AS value " +
        "FROM gauges g" +
        "    JOIN main_values       mv  ON g.id = mv.gauge_id" +
        "    JOIN named_main_values nmv ON nmv.id = mv.named_value_id" +
        "    JOIN main_value_types  mvt ON nmv.type_id = mvt.id " +
        "WHERE" +
        "    mvt.name = 'Q' AND (" +
        "        nmv.name = 'MNQ'      OR" +
        "        nmv.name LIKE 'MNQ(%' OR" +
        "        nmv.name = 'MQ'       OR" +
        "        nmv.name LIKE 'MQ(%'  OR" +
        "        nmv.name = 'MHQ'      OR" +
        "        nmv.name LIKE 'MHQ(%' OR" +
        "        nmv.name = 'HQ5'      OR" +
        "        nmv.name LIKE 'HQ5(%') AND" +
        "    g.river_id = :river_id " +
        "ORDER BY" +
        "    g.id";

    public static final String SQL_FIXINGS =
        "SELECT" +
        "    id AS wst_id," +
        "    description " +
        "FROM wsts " +
        "WHERE" +
        "    river_id = :river_id AND kind = 2";

    public static final String SQL_FIXING_COLUMNS =
        "SELECT" +
        "    wc.id         AS wst_column_id," +
        "    ti.start_time AS start_time," +
        "    wc.name       AS name " +
        "FROM wst_columns wc" +
        "    JOIN time_intervals ti ON wc.time_interval_id = ti.id " +
        "WHERE" +
        "    wc.wst_id = :wst_id " +
        "ORDER BY position";

    public static final String SQL_FIXING_COLUMN_Q_RANGES =
        "SELECT" +
        "    wqr.q AS q," +
        "    r.a   AS start_km," +
        "    r.b   AS stop_km " +
        "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 String SQL_FIXING_COLUMN_KM_RANGE =
        "SELECT" +
        "    MIN(position) AS start_km," +
        "    MAX(position) AS stop_km " +
        "FROM" +
        "    wst_column_values " +
        "WHERE " +
        "    wst_column_id = :column_id";

    public static class Range implements Serializable {

        protected double start;
        protected double end;

        public Range() {
        }

        public Range(double start, double end) {
            this.start = start;
            this.end   = end;
        }

        public boolean disjoint(Range other) {
            return start > other.end || other.start > end;
        }

        public boolean clip(Range other) {
            if (disjoint(other)) return false;

            if (other.start > start) start = other.start;
            if (other.end   < end  ) end   = other.end;

            return true;
        }

        public boolean inside(double x) {
            return x > start-EPSILON && x < end+EPSILON;
        }
    } // class Range

    public static class GaugeRange extends Range {

        private static final class Sector implements Serializable {

            int    sector;
            double value;

            Sector(int sector, double value) {
                this.sector = sector;
                this.value  = value;
            }

        } // class Sector

        protected int gaugeId;

        protected Map<String, Double> mainValues;
        protected List<Sector> sectors;

        public GaugeRange() {
        }

        public GaugeRange(double start, double end, int gaugeId) {
            super(start, end);
            this.gaugeId = gaugeId;
            mainValues = new HashMap<String, Double>();
            sectors = new ArrayList<Sector>(3);
        }

        public void addMainValue(String label, Double value) {
            int idx = label.indexOf('(');
            if (idx >= 0) {
                label = label.substring(0, idx);
            }
            mainValues.put(label, value);
        }

        protected Double getMainValue(String label) {
            Double v = mainValues.get(label);
            if (v == null) {
                log.warn("Missing main value '" 
                    + label + "' for gauge " + gaugeId);
            }
            return v;
        }

        public void buildClasses() {
            Double mnq = getMainValue("MNQ");
            Double mq  = getMainValue("MQ");
            Double mhq = getMainValue("MHQ");
            Double hq5 = getMainValue("HQ5");

            Double [][] pairs = {
                { mnq,  mq },
                {  mq, mhq },
                { hq5, hq5 } };

            for (int c = 0; c < pairs.length; ++c) {
                Double [] pair = pairs[c];
                if (pair[0] != null && pair[1] != null) {
                    double value = 0.5*(pair[0] + pair[1]);
                    sectors.add(new Sector(c, value));
                }
            }
        }

        public int classify(double value) {
            for (Sector sector: sectors) {
                if (value < sector.value) {
                    return sector.sector;
                }
            }
            return sectors.size();
        }
    } // class GaugeRange

    public static class QRange extends Range {

        protected double q;

        public QRange() {
        }

        public QRange(double start, double end, double q) {
            super(start, end);
            this.q = q;
        }
    } // class QRange

    public static class SectorRange extends Range {

        protected int sector;

        public SectorRange() {
        }

        public SectorRange(double start, double end, int sector) {
            super(start, end);
            this.sector = sector;
        }

        public boolean enlarge(SectorRange other) {
            if (sector == other.sector
            && Math.abs(end-other.start) < EPSILON2) {
                end = other.end;
                return true;
            }
            return false;
        }
    } // class SectorRange

    public static class Fixing implements Serializable {

        public static final Comparator<Column> DATE_CMP =
            new Comparator<Column>() {
                @Override
                public int compare(Column a, Column b) {
                    return a.startTime.compareTo(b.startTime);
                }
            };

        public class Column extends Range {

            protected int    columnId;
            protected Date   startTime;
            protected String name;

            protected List<QRange> qRanges;

            public Column() {
            }

            public Column(int columnId, Date startTime, String name) {
                this.columnId  = columnId;
                this.startTime = startTime;
                this.name      = name;
                
                qRanges = new ArrayList<QRange>();
            }

            public Fixing getFixing() {
                return Fixing.this;
            }

            public List<SectorRange> classify(GaugeRange gauge) {

                List<SectorRange> sectors = new ArrayList<SectorRange>();

                for (QRange qRange: qRanges) {
                    SectorRange sector = new SectorRange(
                        qRange.start, qRange.end,
                        gauge.classify(qRange.q));
                    if (sectors.isEmpty()
                    || !sectors.get(sectors.size()-1).enlarge(sector)) {
                        sectors.add(sector);
                    }
                }

                return sectors;
            }

            public void loadKmRange(SQLQuery query) {
                query.setInteger("column_id", columnId);

                List<Object []> kms = query.list();

                if (kms.isEmpty()) {
                    log.warn("No km range for column " + columnId + ".");
                }
                else {
                    Object [] obj = kms.get(0);
                    start = (Double)obj[0];
                    end   = (Double)obj[1];
                }
            }

            public void loadQRanges(SQLQuery query) {
                query.setInteger("column_id", columnId);
                List<Object []> list = query.list();

                for (Object [] row: list) {
                    double q     = (Double)row[0];
                    double start = (Double)row[1];
                    double end   = (Double)row[2];
                    QRange qRange = new QRange(start, end, q);
                    if (qRange.clip(this)) {
                        qRanges.add(qRange);
                    }
                }
            }
        } // class Column

        protected int                wstId;
        protected String             description;
        protected List<Column> columns;

        public Fixing() {
        }

        public Fixing(int wstId, String description) {
            this.wstId       = wstId;
            this.description = description;
            columns = new ArrayList<Column>();
        }

        public void loadColumns(SQLQuery query) {
            query.setInteger("wst_id", wstId);
            List<Object []> list = query.list();
            for (Object [] row: list) {
                int    columnId  = (Integer)row[0];
                Date   startTime = (Date)   row[1];
                String name      = (String) row[2];
                columns.add(new Column(columnId, startTime, name));
            }
        }

        public void loadColumnsKmRange(SQLQuery query) {
            for (Column column: columns) {
                column.loadKmRange(query);
            }
        }

        public void loadColumnsQRanges(SQLQuery query) {
            for (Column column: columns) {
                column.loadQRanges(query);
            }
        }

        public void addAllColumns(List<Column> allColumns) {
            allColumns.addAll(columns);
        }
    } // class Fixing


    protected String           riverName;
    protected int              riverId;
    protected boolean          isKmUp;
    protected List<GaugeRange> gauges;
    protected List<Fixing>     fixings;
    protected Range            extent;

    public FixingsOverview() {
        gauges  = new ArrayList<GaugeRange>();
        fixings = new ArrayList<Fixing>();
    }

    public FixingsOverview(String riverName) {
        this();
        this.riverName = riverName;
    }

    protected boolean loadRiver(Session session) {
        SQLQuery query = session.createSQLQuery(SQL_RIVER_ID)
            .addScalar("river_id", StandardBasicTypes.INTEGER)
            .addScalar("km_up",    StandardBasicTypes.BOOLEAN);

        query.setString("name", riverName);

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

        if (list.isEmpty()) {
            log.warn("No river '" + riverName + "' found.");
            return false;
        }

        Object [] row = list.get(0);

        riverId = (Integer)row[0];
        isKmUp  = (Boolean)row[1];

        return true;
    }

    protected boolean loadRiverExtent(Session session) {
        SQLQuery query = session.createSQLQuery(SQL_RIVER_EXTENT)
            .addScalar("start_km", StandardBasicTypes.DOUBLE)
            .addScalar("end_km",   StandardBasicTypes.DOUBLE);

        query.setInteger("river_id", riverId);

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

        if (list.isEmpty()) {
            log.warn("River " + riverId + " has no extent.");
            return false;
        }

        Object [] row = list.get(0);
        extent = new Range((Double)row[0], (Double)row[1]);

        return true;
    }


    protected boolean loadGauges(Session session) {
        SQLQuery query = session.createSQLQuery(SQL_GAUGES)
            .addScalar("gauge_id", StandardBasicTypes.INTEGER)
            .addScalar("a",        StandardBasicTypes.DOUBLE)
            .addScalar("b",        StandardBasicTypes.DOUBLE);

        query.setInteger("river_id", riverId);

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

        if (list.isEmpty()) {
            log.warn("River " + riverId + " has no gauges.");
            return false;
        }

        for (Object [] row: list) {
            int    gaugeId = (Integer)row[0];
            double start   = (Double) row[1];
            double end     = (Double) row[2];
            GaugeRange gauge = new GaugeRange(start, end, gaugeId);
            gauges.add(gauge);
        }

        return true;
    }

    protected GaugeRange findGaugeById(int gaugeId) {
        for (GaugeRange gauge: gauges) {
            if (gauge.gaugeId == gaugeId) {
                return gauge;
            }
        }
        return null;
    }

    protected GaugeRange findGaugeByKm(double km) {
        for (GaugeRange gauge: gauges) {
            if (gauge.inside(km)) {
                return gauge;
            }
        }
        return null;
    }

    protected boolean loadDischargeSectors(Session session) {

        SQLQuery query = session.createSQLQuery(SQL_DISCHARGE_SECTORS)
            .addScalar("gauge_id", StandardBasicTypes.INTEGER)
            .addScalar("name",     StandardBasicTypes.STRING)
            .addScalar("value",    StandardBasicTypes.DOUBLE);

        query.setInteger("river_id", riverId);

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

        if (list.isEmpty()) {
            log.warn("River " + riverId + " has no discharge sectors.");
            return false;
        }

        GaugeRange gauge = null;

        for (Object [] row: list) {
            int    gaugeId = (Integer)row[0];
            String label   = (String) row[1];
            Double value   = (Double) row[2];

            if (gauge == null || gauge.gaugeId != gaugeId) {
                if ((gauge = findGaugeById(gaugeId)) == null) {
                    log.warn("Cannot find gauge for id " + gaugeId + ".");
                    continue;
                }
            }

            gauge.addMainValue(label, value);
        }

        for (GaugeRange g: gauges) {
            g.buildClasses();
        }

        return true;
    }

    protected void loadFixings(Session session) {
        SQLQuery query = session.createSQLQuery(SQL_FIXINGS)
            .addScalar("wst_id",      StandardBasicTypes.INTEGER)
            .addScalar("description", StandardBasicTypes.STRING);

        query.setInteger("river_id", riverId);

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

        if (list.isEmpty()) {
            log.warn("River " + riverId + " has no fixings.");
            // Its pretty fine to have no fixings.
        }

        for (Object [] row: list) {
            int    wstId       = (Integer)row[0];
            String description = (String) row[1];
            Fixing fixing = new Fixing(wstId, description);
            fixings.add(fixing);
        }
    }

    protected void loadFixingsColumns(Session session) {
        SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMNS)
            .addScalar("wst_column_id", StandardBasicTypes.INTEGER)
            .addScalar("start_time",    StandardBasicTypes.DATE)
            .addScalar("name",          StandardBasicTypes.STRING);

        for (Fixing fixing: fixings) {
            fixing.loadColumns(query);
        }
    }

    protected void loadFixingsColumnsKmRange(Session session) {
        SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMN_KM_RANGE)
            .addScalar("start_km", StandardBasicTypes.DOUBLE)
            .addScalar("stop_km",  StandardBasicTypes.DOUBLE);

        for (Fixing fixing: fixings) {
            fixing.loadColumnsKmRange(query);
        }
    }

    protected void loadFixingsColumnsQRanges(Session session) {
        SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMN_Q_RANGES)
            .addScalar("q",        StandardBasicTypes.DOUBLE)
            .addScalar("start_km", StandardBasicTypes.DOUBLE)
            .addScalar("stop_km",  StandardBasicTypes.DOUBLE);

        for (Fixing fixing: fixings) {
            fixing.loadColumnsQRanges(query);
        }
    }

    public boolean load(Session session) {

        if (!loadRiver(session)) {
            return false;
        }

        if (!loadRiverExtent(session)) {
            return false;
        }

        if (!loadGauges(session)) {
            return false;
        }

        if (!loadDischargeSectors(session)) {
            return false;
        }

        loadFixings(session);
        loadFixingsColumns(session);
        loadFixingsColumnsKmRange(session);
        loadFixingsColumnsQRanges(session);

        return true;
    }

    public void generateOverview(Document document) {

        List<Fixing.Column> allColumns = new ArrayList<Fixing.Column>();

        for (Fixing fixing: fixings) {
            fixing.addAllColumns(allColumns);
        }

        Collections.sort(allColumns, Fixing.DATE_CMP);

        Element fixingsElement = document.createElement("fixings");

        Element riverElement = document.createElement("river");

        riverElement.setAttribute("from", String.valueOf(extent.start));
        riverElement.setAttribute("to",   String.valueOf(extent.end));
        riverElement.setAttribute("rid",  String.valueOf(riverId));

        fixingsElement.appendChild(riverElement);

        SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm");

        Element esE = document.createElement("events");

        for (Fixing.Column column: allColumns) {

            double km = isKmUp ? column.start : column.end;

            GaugeRange gauge = findGaugeByKm(km);

            if (gauge == null) {
                log.warn("Cannot find gauge for km " + km + ".");
                continue;
            }

            List<SectorRange> sectors = column.classify(gauge);

            if (!sectors.isEmpty()) {
                Element eE = document.createElement("event");
                eE.setAttribute("name",
                    String.valueOf(column.getFixing().description));
                eE.setAttribute("cid", String.valueOf(column.columnId));
                eE.setAttribute("date", df.format(column.startTime));

                for (SectorRange sector: sectors) {
                    Element sE = document.createElement("sector");

                    sE.setAttribute("from",  String.valueOf(sector.start));
                    sE.setAttribute("to",    String.valueOf(sector.end));
                    sE.setAttribute("class", String.valueOf(sector.sector));

                    eE.appendChild(sE);
                }

                esE.appendChild(eE);
            }
        }

        fixingsElement.appendChild(esE);

        document.appendChild(fixingsElement);
    }
}
// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :