package de.intevation.aft;

import java.util.List;
import java.util.Date;
import java.util.ArrayList;
import java.util.TreeSet;
import java.util.Set;

import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Types;

import de.intevation.db.SymbolicStatement;
import de.intevation.db.ConnectedStatements;

import org.apache.log4j.Logger;

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

    protected int          id;
    protected int          gaugeId;
    protected TimeInterval timeInterval;
    protected String       description;
    protected Set<WQ>      values;

    public DischargeTable() {
    }

    public DischargeTable(
        int          gaugeId, 
        TimeInterval timeInterval, 
        String       description
    ) {
        this.gaugeId      = gaugeId;
        this.timeInterval = timeInterval;
        this.description  = description;
        values = new TreeSet<WQ>(WQ.EPS_CMP);
    }

    public DischargeTable(
        int          id, 
        int          gaugeId, 
        TimeInterval timeInterval, 
        String       description
    ) {
        this(gaugeId, timeInterval, description);
        this.id = id;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getGaugeId() {
        return gaugeId;
    }

    public void setGaugeId(int gaugeId) {
        this.gaugeId = gaugeId;
    }

    public TimeInterval getTimeInterval() {
        return timeInterval;
    }

    public void setTimeInterval(TimeInterval timeInterval) {
        this.timeInterval = timeInterval;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public void clearValues() {
        values.clear();
    }

    public Set<WQ> getValues() {
        return values;
    }

    public void setValues(Set<WQ> values) {
        this.values = values;
    }


    protected void loadValues(SymbolicStatement.Instance query) 
    throws SQLException
    {
        ResultSet rs = query.executeQuery();
        while (rs.next()) {
            int    id = rs.getInt("id");
            double w  = rs.getDouble("w");
            double q  = rs.getDouble("q");
            if (!values.add(new WQ(id, w, q))) {
                log.warn("FLYS/AFT: Value duplication w="+w+" q="+q+". -> ignore.");
            }
        }
        rs.close();
    }

    public void loadAftValues(SyncContext context) throws SQLException {
        loadValues(context.getAftStatements()
            .getStatement("select.tafelwert")
            .clearParameters()
            .setInt("number", getId()));
    }

    public void loadFlysValues(SyncContext context) throws SQLException {
        loadValues(context.getFlysStatements()
            .getStatement("select.discharge.table.values")
            .clearParameters()
            .setInt("table_id", getId()));
    }

    public void storeFlysValues(
        SyncContext context,
        int         dischargeTableId
    )
    throws SQLException
    {
        ConnectedStatements flysStatements = context.getFlysStatements();

        // Create the ids.
        SymbolicStatement.Instance nextId = flysStatements
            .getStatement("next.discharge.table.values.id");

        // Insert the values.
        SymbolicStatement.Instance insertDTV = flysStatements
            .getStatement("insert.discharge.table.value");

        for (WQ wq: values) {
            ResultSet rs = nextId.executeQuery();
            rs.next();
            int wqId = rs.getInt("discharge_table_values_id");
            rs.close();

            insertDTV
                .clearParameters()
                .setInt("id", wqId)
                .setInt("table_id", dischargeTableId)
                .setDouble("w", wq.getW())
                .setDouble("q", wq.getQ())
                .execute();
        }
    }

    public static List<DischargeTable> loadFlysDischargeTables(
        SyncContext context,
        int         gaugeId
    )
    throws SQLException
    {
        List<DischargeTable> dts = new ArrayList<DischargeTable>();

        ResultSet rs = context
            .getFlysStatements()
            .getStatement("select.gauge.discharge.tables")
            .clearParameters()
            .setInt("gauge_id", gaugeId)
            .executeQuery();

        OUTER: while (rs.next()) {
            int    id          = rs.getInt("id");
            String description = rs.getString("description");
            if (description == null) {
                description = "";
            }
            for (DischargeTable dt: dts) {
                if (dt.getDescription().equals(description)) {
                    log.warn("FLYS: Found discharge table '" +
                        description + "' with same description. -> ignore");
                    continue OUTER;
                }
            }
            Date startTime = rs.getDate("start_time");
            Date stopTime  = rs.getDate("stop_time");
            TimeInterval ti = startTime == null
                ? null
                : new TimeInterval(startTime, stopTime);

            DischargeTable dt = new DischargeTable(
                id, gaugeId, ti, description);
            dts.add(dt);
        }
        rs.close();

        return dts;
    }

    public static List<DischargeTable> loadAftDischargeTables(
        SyncContext context,
        Long        officialNumber
    )
    throws SQLException
    {
        return loadAftDischargeTables(context, officialNumber, 0);
    }

    public static List<DischargeTable> loadAftDischargeTables(
        SyncContext context,
        Long        officialNumber,
        int         flysGaugeId
    )
    throws SQLException
    {
        List<DischargeTable> dts = new ArrayList<DischargeTable>();

        ResultSet rs = context
            .getAftStatements()
            .getStatement("select.abflusstafel")
            .clearParameters()
            .setString("number", "%" + officialNumber)
            .executeQuery();

        OUTER: while (rs.next()) {
            int  dtId = rs.getInt("ABFLUSSTAFEL_NR");
            Date from = rs.getDate("GUELTIG_VON");
            Date to   = rs.getDate("GUELTIG_BIS");

            if (from == null) {
                log.warn("AFT: ABFLUSSTAFEL_NR = "
                    + dtId + ": GUELTIG_VON = NULL -> ignored.");
            }

            if (to == null) {
                log.warn("AFT: ABFLUSSTAFEL_NR = "
                    + dtId + ": GUELTIG_BIS = NULL -> ignored.");
            }

            if (from == null || to == null) {
                continue;
            }

            if (from.compareTo(to) > 0) {
                    log.warn("AFT: ABFLUSSTAFEL_NR = " 
                    + dtId + ": " + from + " > " + to + ". -> swap");
                Date temp = from;
                from = to;
                to = temp;
            }

            String description = rs.getString("ABFLUSSTAFEL_BEZ");
            if (description == null) {
                description = String.valueOf(officialNumber);
            }

            for (DischargeTable dt: dts) {
                if (dt.getDescription().equals(description)) {
                    log.warn("AFT: Found discharge table '" +
                        description + "' with same description. -> ignore.");
                    continue OUTER;
                }
            }

            double datumValue = rs.getDouble("PEGELNULLPUNKT");
            Double datum = rs.wasNull() ? null : datumValue;

            TimeInterval timeInterval = new TimeInterval(from, to);

            DischargeTable dt = new DischargeTable(
                dtId,
                flysGaugeId,
                timeInterval,
                description);
            dts.add(dt);
        }
        rs.close();

        return dts;
    }

    public void persistFlysTimeInterval(
        SyncContext context
    )
    throws SQLException
    {
        if (timeInterval != null) {
            timeInterval = context.fetchOrCreateFLYSTimeInterval(
                timeInterval);
        }
    }

    public int persistFlysDischargeTable(
        SyncContext context,
        int         gaugeId
    )
    throws SQLException
    {
        ConnectedStatements flysStatements =
            context.getFlysStatements();

        ResultSet rs = flysStatements
            .getStatement("next.discharge.id")
            .executeQuery();

        rs.next();
        int flysId = rs.getInt("discharge_table_id");
        rs.close();

        SymbolicStatement.Instance insertDT = flysStatements
            .getStatement("insert.dischargetable")
            .clearParameters()
            .setInt("id", flysId)
            .setInt("gauge_id", gaugeId)
            .setString("description", description);

        if (timeInterval != null) {
            insertDT.setInt("time_interval_id", timeInterval.getId());
        }
        else {
            insertDT.setNull("time_interval_id", Types.INTEGER);
        }

        insertDT.execute();

        if (log.isDebugEnabled()) {
            log.debug("FLYS: Created discharge table id: " + id);
        }

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