package com.henrypump.poc;

/**
 * Created by patrickjmcd on 2/2/17.
 */

import com.google.gson.*;
import org.bson.Document;
import java.sql.*;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import org.json.simple.JSONObject;
import org.postgresql.util.PGobject;

import java.util.*;
import java.util.Date;

public class Database{
    String dbHost = "localhost";
    String dbName = "poc";
    Connection dbConn;


    private Connection connectToDatabase() {
        Connection conn = null;
        try
        {
            Class.forName("org.postgresql.Driver");
            String url = "jdbc:postgresql://" + dbHost + "/"+ dbName;
            Properties props = new Properties();
            props.setProperty("user","poc");
            props.setProperty("password","poc");
            conn = DriverManager.getConnection(url, props);
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
            System.exit(1);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            System.exit(2);
        }
        return conn;
    }

    Database(){
        dbConn = connectToDatabase();
    }

    Database(String dbHostname){
        dbHost = dbHostname;
        dbConn = connectToDatabase();
    }

    static Object[] makeObjectArray(double[] doubleArray) {
        Object[] objList = new Object[doubleArray.length];
        for (int i = 0; i < doubleArray.length; i++){
            objList[i] = (Object) doubleArray[i];
        }
        return objList;
    }

    //CARD FUNCTIONS

    public long getLastStrokeNum(){

        long lastStroke = -1;
        try {
            String selString = "SELECT MAX(id) numCards FROM cards";
            PreparedStatement ps = dbConn.prepareStatement(selString);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                lastStroke = rs.getInt("numCards");
            }
            rs.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println("Found last stroke to be " + lastStroke);
        return lastStroke;
    }

    public long newCard(Card inpCard){
        String sqlInsert = "INSERT INTO cards (stroke_number, surface_position, surface_load, downhole_position, downhole_load) VALUES (?, ?, ?, ?, ?)";
        long numCards = -1;
        try {
            Array surf_pos = dbConn.createArrayOf("float4", makeObjectArray(inpCard.getSurfacePosition()));
            Array surf_lod = dbConn.createArrayOf("float4", makeObjectArray(inpCard.getSurfaceLoad()));
            Array down_pos = dbConn.createArrayOf("float4", makeObjectArray(inpCard.getDownholePosition()));
            Array down_lod = dbConn.createArrayOf("float4", makeObjectArray(inpCard.getDownholeLoad()));
            PreparedStatement prepInsert = dbConn.prepareStatement(sqlInsert);
            prepInsert.setLong(1, inpCard.getStrokeNumber());
            prepInsert.setArray(2, surf_pos);
            prepInsert.setArray(3, surf_lod);
            prepInsert.setArray(4, down_pos);
            prepInsert.setArray(5, down_lod);

            prepInsert.executeUpdate();

            String selString = "SELECT COUNT(id) numCards FROM cards";
            PreparedStatement ps = dbConn.prepareStatement(selString);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                numCards = rs.getLong("numCards");
            }
            rs.close();
            ps.close();

        } catch (SQLException e){
            e.printStackTrace();
        }
        return numCards;
    }

    // MEASUREMENT FUNCTIONS
    public long tryNewMeasurementConfig(Measurement inpMeasurement){
        String sqlSelect = "SELECT id FROM measurement_configs WHERE m_name = ?";
        long dbId = -1;
        try {
            PreparedStatement ps = dbConn.prepareStatement(sqlSelect);
            ps.setString(1, inpMeasurement.getTagName());
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                dbId = rs.getInt("id");
            }
            rs.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //If measurement already exists
        if (dbId == -1){
            String sqlInsert = "INSERT INTO measurement_configs (m_name, units, store_delta, store_time, use_total, use_average) " +
                    "VALUES (?, ?, ?, ?, ?, ?)";
            try {
                PreparedStatement prepInsert = dbConn.prepareStatement(sqlInsert);
                prepInsert.setString(1, inpMeasurement.getTagName());
                prepInsert.setString(2, inpMeasurement.getUnits());
                prepInsert.setDouble(3, inpMeasurement.getSendDelta());
                prepInsert.setDouble(4, inpMeasurement.getSendTimeDelta());
                prepInsert.setBoolean(5, inpMeasurement.isUseTotal());
                prepInsert.setBoolean(6, inpMeasurement.isUseAverage());

                prepInsert.executeUpdate();

                String selString = "SELECT id FROM measurement_configs WHERE m_name = ?";
                PreparedStatement ps = dbConn.prepareStatement(selString);
                ps.setString(1, inpMeasurement.getTagName());
                ResultSet rs = ps.executeQuery();
                while (rs.next()){
                    dbId = rs.getLong("id");
                }
                rs.close();
                ps.close();
            } catch (SQLException e){
                e.printStackTrace();
            }

        }
        return dbId;

    }


    public long newMeasurementValue(Measurement inpMeasurement){
        String sqlInsert = "INSERT INTO measurements_num (m_id, val) VALUES (?, ?)";
        long numMeasurements = -1;
        try {
            PreparedStatement prepInsert = dbConn.prepareStatement(sqlInsert);
            prepInsert.setLong(1, inpMeasurement.getDbId());
            prepInsert.setDouble(2, inpMeasurement.getCurrentValue());
            prepInsert.executeUpdate();

            String selString = "SELECT COUNT(id) numMeasurements FROM measurements_num";
            PreparedStatement ps = dbConn.prepareStatement(selString);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                numMeasurements = rs.getLong("numMeasurements");
            }
            rs.close();
            ps.close();

        } catch (SQLException e){
            e.printStackTrace();
        }
        return numMeasurements;
    }

    public double getLastStoredMeasurement(long dbId){
        String sqlSelect = "SELECT val FROM measurements_num WHERE id = (SELECT MAX(id) FROM measurements_num WHERE m_id = ?)";
        //TODO: Add query to find current average, number of measurements, total, max, and min for today.

        double retValue = Double.MIN_VALUE;
        try {
            PreparedStatement ps = dbConn.prepareStatement(sqlSelect);
            ps.setLong(1, dbId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                retValue = rs.getInt("val");
            }
            rs.close();
            ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }

        return retValue;
    }

    public long newDailyTotal(Measurement inpMeasurement){
        String sqlInsert = "INSERT INTO daily_totals (m_id, current_value, max_value, min_value, average_value, total_value) VALUES (?, ?, ?, ?, ?, ?)";


        long numMeasurements = -1;
        try {
            PreparedStatement prepInsert = dbConn.prepareStatement(sqlInsert);
            prepInsert.setLong(1, inpMeasurement.getDbId());
            prepInsert.setDouble(2, inpMeasurement.getCurrentValue());
            prepInsert.setDouble(3, inpMeasurement.getDailyMax());
            prepInsert.setDouble(4, inpMeasurement.getDailyMin());
            prepInsert.setDouble(5, inpMeasurement.getAverage());
            prepInsert.setDouble(6, inpMeasurement.getTotal());
            prepInsert.executeUpdate();

            String selString = "SELECT COUNT(id) numMeasurements FROM daily_totals";
            PreparedStatement ps = dbConn.prepareStatement(selString);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                numMeasurements = rs.getLong("numMeasurements");
            }
            rs.close();
            ps.close();

        } catch (SQLException e){
            e.printStackTrace();
        }
        return numMeasurements;

    }

    public double getPreviousDailyTotal(ZonedDateTime inpDateTime){
        String sqlSelect = "SELECT total_value FROM daily_totals WHERE id = (SELECT MAX(id) FROM daily_totals WHERE m_id = " +
                "(SELECT m_id FROM well_configs WHERE m_name = 'Fluid Produced'))";
        double lastTotal = -1;
        try {
            PreparedStatement ps = dbConn.prepareStatement(sqlSelect);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                lastTotal = rs.getInt("total_value");
            }
            rs.close();
            ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return lastTotal;
    }

    public long newWellTest(WellTest inp){
        String sqlInsert = "INSERT INTO well_tests (test_completed_timestamp, test_hours, total_bbl, oil_bbl, h2o_bbl, gas_mcf," +
                " k_factor, oil_bbl_ratio, h2o_bbl_ratio, gas_mcf_ratio) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        long numTests = -1;
        try {
            PreparedStatement prepInsert = dbConn.prepareStatement(sqlInsert);
            prepInsert.setTimestamp(1, new Timestamp(Date.from(inp.getTestStart().toInstant()).getTime()));
            prepInsert.setDouble(2, inp.getTestHours());
            prepInsert.setDouble(3, inp.getTotalFluidBBL());
            prepInsert.setDouble(4, inp.getTestOilBBL());
            prepInsert.setDouble(5, inp.getTestWaterBBL());
            prepInsert.setDouble(6, inp.getTestGasMCF());
            prepInsert.setDouble(7, inp.getkFactor());
            prepInsert.setDouble(8, inp.getOilRatio());
            prepInsert.setDouble(9, inp.getWaterRatio());
            prepInsert.setDouble(10, inp.getGasMCFRatio());
            prepInsert.executeUpdate();

            String selString = "SELECT COUNT(id) numTests FROM well_tests";
            PreparedStatement ps = dbConn.prepareStatement(selString);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                numTests = rs.getLong("numTests");
            }
            rs.close();
            ps.close();

        } catch (SQLException e){
            e.printStackTrace();
        }
        return numTests;
    }

    public WellTest getPreviousWellTest(ZonedDateTime inpDateTime){
        String selectSql = "SELECT * FROM well_tests WHERE test_completed_timestamp <= ? ORDER BY test_completed_timestamp DESC LIMIT 1";
        WellTest wt = null;
        try {
            PreparedStatement ps = dbConn.prepareStatement(selectSql);
            ps.setTimestamp(1, new Timestamp(Date.from(inpDateTime.toInstant()).getTime()));
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                wt = new WellTest(rs.getTimestamp("test_completed_timestamp").toInstant().atZone(ZoneId.of("GMT")), rs.getDouble("test_hours"),
                        rs.getDouble("total_bbl"), rs.getDouble("oil_bbl"), rs.getDouble("h20_bbl"),
                        rs.getDouble("gas_mcf"), rs.getDouble("k_factor"));
            }
            rs.close();
            ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }

        return wt;
    };

    public double getLatestKFactor(){
        String selectSql = "SELECT k_factor from well_tests WHERE id = (SELECT MAX(id) FROM well_tests)";
        double kFactor = 1.0;

        try {
            PreparedStatement ps = dbConn.prepareStatement(selectSql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                kFactor = rs.getDouble("k_factor");
            }
            rs.close();
            ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return kFactor;
    }

    public long newFluidShot(FluidShot inp){
        String sqlInsert = "INSERT INTO fluid_shots (shot_timestamp, fluid_level, pump_intake_pressure, friction_estimate) VALUES (?, ?, ?, ?)";
        Timestamp ts = new Timestamp(Date.from(ZonedDateTime.now().toInstant()).getTime());
        long numShots = -1;
        try {
            PreparedStatement prepInsert = dbConn.prepareStatement(sqlInsert);
            prepInsert.setTimestamp(1, ts);
            prepInsert.setDouble(2, inp.getFluidLevel());
            prepInsert.setDouble(3, inp.getPumpIntakePressure());
            prepInsert.setDouble(4, inp.getFrictionEstimate());
            prepInsert.executeUpdate();

            String selString = "SELECT COUNT(id) numShots FROM fluid_shots";
            PreparedStatement ps = dbConn.prepareStatement(selString);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                numShots = rs.getLong("numShots");
            }
            rs.close();
            ps.close();

        } catch (SQLException e){
            e.printStackTrace();
        }
        return numShots;
    }

    public double getLatestFrictionEstimate(){
        String selectSql = "SELECT friction_estimate from fluid_shots WHERE id = (SELECT MAX(id) FROM fluid_shots)";
        double frictionEstimate = 1.0;

        try {
            PreparedStatement ps = dbConn.prepareStatement(selectSql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                frictionEstimate = rs.getDouble("friction_estimate");
            }
            rs.close();
            ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return frictionEstimate;
    }

    public long newRunStatus(String runStatus, String initiator){
        String sqlInsert = "INSERT INTO device_statuses (status, initiator) VALUES (?, ?)";

        long numStatuses = -1;
        try {
            PreparedStatement prepInsert = dbConn.prepareStatement(sqlInsert);
            prepInsert.setString(1, runStatus);
            prepInsert.setString(2, initiator);
            prepInsert.executeUpdate();

            String selString = "SELECT COUNT(id) numStatuses FROM device_statuses";
            PreparedStatement ps = dbConn.prepareStatement(selString);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                numStatuses = rs.getLong("numStatuses");
            }
            rs.close();
            ps.close();

        } catch (SQLException e){
            e.printStackTrace();
        }
        return numStatuses;
    }

    public long storeWellSetup(Well thisWell){
        Gson gson = new Gson();
        JsonObject wellConfig = new JsonObject();
        JsonArray taperArr = new JsonArray();

        PGobject wellConfigObj = new PGobject();
        wellConfigObj.setType("json");

        PGobject taperArrayObj = new PGobject();
        taperArrayObj.setType("json");

        for (int i = 1; i <= thisWell.getNumTapers(); i++){
            JsonObject tap = new JsonObject();
            tap.add("length", new JsonPrimitive(thisWell.getRodLength(i)));
            tap.add("diameter", new JsonPrimitive(thisWell.getRodDiameter(i)));
            tap.add("material", new JsonPrimitive(thisWell.getRodMaterial(i)));
            tap.add("dampingFactor", new JsonPrimitive(thisWell.getDampingFactor(i)));
            taperArr.add(tap);
        }
        wellConfig.add("wellName", new JsonPrimitive(thisWell.getWellName()));
        wellConfig.add("deltaT", new JsonPrimitive(thisWell.getDt()));
        wellConfig.add("pumpDiameter", new JsonPrimitive(thisWell.getPumpDiameter()));
        wellConfig.add("fluidGradient", new JsonPrimitive(thisWell.getFluidGradient()));
        wellConfig.add("tubingID", new JsonPrimitive(thisWell.getTubingID()));
        wellConfig.add("tubingOD", new JsonPrimitive(thisWell.getTubingOD()));
        wellConfig.add("tubingAnchorDepth", new JsonPrimitive(thisWell.getTubingAnchorDepth()));
        wellConfig.add("structuralRating", new JsonPrimitive(thisWell.getStructuralRating()));
        wellConfig.add("stuffingBoxFriction", new JsonPrimitive(thisWell.getSbfriction()));
        wellConfig.add("tubingHeadPressure", new JsonPrimitive(thisWell.getTubingHeadPressure()));
        wellConfig.add("tapers", taperArr);
        wellConfig.add("storedBy", new JsonPrimitive("poc"));


        String sqlInsert = "INSERT INTO well_configs (taper_setup, well_config) VALUES (?, ?)";
        long numConfigs = -1;
        try {

            PreparedStatement prepInsert = dbConn.prepareStatement(sqlInsert);

            taperArrayObj.setValue(gson.toJson(taperArr));
            prepInsert.setObject(1, taperArrayObj);

            wellConfigObj.setValue(gson.toJson(wellConfig));
            prepInsert.setObject(2, wellConfigObj);

            prepInsert.executeUpdate();

            String selString = "SELECT COUNT(id) numConfigs FROM well_configs";
            PreparedStatement ps = dbConn.prepareStatement(selString);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                numConfigs = rs.getLong("numConfigs");
            }
            rs.close();
            ps.close();

        } catch (SQLException e){
            e.printStackTrace();
        }
        return numConfigs;
    }

    public JsonObject getLatestWellConfiguration(){
        Gson gson = new Gson();
        String selectSql = "SELECT * from well_configs WHERE id = (SELECT MAX(id) FROM well_configs)";
        JsonObject config = new JsonObject();

        try {
            PreparedStatement ps = dbConn.prepareStatement(selectSql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                config.add("tapers", new JsonPrimitive(rs.getString("taper_setup")));
                config.add("well_config", new JsonPrimitive(rs.getString("well_config")));
            }
            rs.close();
            ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return config;
    }

    public long storeSetpoint(String setpointName, double setpointValue){
        String sqlInsert = "INSERT INTO setpoints (param, setpoint_value) VALUES (?, ?) ON CONFLICT DO UPDATE setpoints SET setpoint_value = ? WHERE param = ?";
        long numSetpoints = -1;
        try {
            PreparedStatement prepInsert = dbConn.prepareStatement(sqlInsert);
            prepInsert.setString(1, setpointName);
            prepInsert.setDouble(2, setpointValue);
            prepInsert.setDouble(3, setpointValue);
            prepInsert.setString(4, setpointName);
            prepInsert.executeUpdate();

            String selString = "SELECT COUNT(id) numSetpoints FROM well_configs";
            PreparedStatement ps = dbConn.prepareStatement(selString);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                numSetpoints = rs.getLong("numSetpoints");
            }
            rs.close();
            ps.close();

        } catch (SQLException e){
            e.printStackTrace();
        }
        return numSetpoints;
    }

    public Double getSetpoint(String setpointName){
        String sqlSelect = "SELECT setpoint_value FROM setpoints WHERE param = ?";
        double setpoint = -1;

        try {
            PreparedStatement ps = dbConn.prepareStatement(sqlSelect);
            ps.setString(1, setpointName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                setpoint = rs.getDouble("setpoint_value");
            }
            rs.close();
            ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return setpoint;
    }


}
