package de.intevation.aft;

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

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

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

public class DischargeTable
{
    protected int          id;
    protected int          gaugeId;
    protected TimeInterval timeInterval;
    protected String       description;
    protected List<WQ>     values;

    public DischargeTable() {
    }

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

    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();
    }

    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");
            values.add(new WQ(id, w, q));
        }
        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");

        int [] ids = new int[values.size()];
        for (int i = 0; i < ids.length; ++i) {
            ResultSet rs = nextId.executeQuery();
            rs.next();
            ids[i] = rs.getInt("discharge_table_values_id");
            rs.close();
        }

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

        for (int i = 0; i < ids.length; ++i) {
            WQ wq = values.get(i);
            insertDTV
                .clearParameters()
                .setInt("id", ids[i])
                .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();

        while (rs.next()) {
            int    id          = rs.getInt("id");
            String description = rs.getString("description");
            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();

        while (rs.next()) {
            int    dtId        = rs.getInt("ABFLUSSTAFEL_NR");
            Date   from        = rs.getDate("GUELTIG_VON");
            Date   to          = rs.getDate("GUELTIG_BIS");
            String description = rs.getString("ABFLUSSTAFEL_BEZ");
            if (description == null) {
                description = String.valueOf(officialNumber);
            }

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

            TimeInterval timeInterval = from == null
                ? null
                : new TimeInterval(from, to);

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

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