/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Persistance;

import ItemManagement.scoreValues;
import ItemSets.itemSet;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author olive
 */
public class DbConnect {

    private Connection con;
    private Statement st;
    private ResultSet rs;

    public DbConnect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/itemsets?useSSL=false", "root", "root");
            st = con.createStatement();

        } catch (Exception ex) {
            System.out.println("Error; " + ex);
        }
    }

    public void testGet() {
        try {
            String query = "select * from item_set";
            rs = st.executeQuery(query);
            System.out.println("Records from item_set");
            while (rs.next()) {
                int iditem_set = rs.getInt(1);
                int power = rs.getInt(3);
                System.out.println("iditem_set: " + iditem_set + " power: " + power);
            }

        } catch (Exception ex) {
            System.out.println("Error; " + ex);
        }
    }

    public void storeItem(ArrayList mySets) throws IOException {
        int index = 0;
        while (index != mySets.size()) {
            itemSet myset = (itemSet) mySets.get(index);
            scoreValues setValues = myset.getSetValues();
            try {

                String sql = "INSERT INTO ITEM_SET(stored_set,power,set_precision,vitality,toughness,concentration,condition_damage,expertise,ferocity,healing_power) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
                con.setAutoCommit(false);
                PreparedStatement preparedStatement
                        = con.prepareStatement(sql);

                Blob blob = con.createBlob();

                ObjectOutputStream oos;
                oos = new ObjectOutputStream(blob.setBinaryStream(1));
                oos.writeObject(myset);
                oos.close();

                int power = setValues.getPower();
                int precision = setValues.getPrecision();
                int toughness = setValues.getToughness();
                int vitality = setValues.getVitality();
                int boon_duration = setValues.getBoon_duration();
                int condition_damage = setValues.getCondition_damage();
                int condition_duration = setValues.getCondition_duration();
                int crit_damage = setValues.getCrit_damage();
                int healing_power = setValues.getHealing_power();

                preparedStatement.setBlob(1, blob);
                preparedStatement.setInt(2, power);
                preparedStatement.setInt(3, precision);
                preparedStatement.setInt(4, toughness);
                preparedStatement.setInt(5, vitality);
                preparedStatement.setInt(6, boon_duration);
                preparedStatement.setInt(7, condition_damage);
                preparedStatement.setInt(8, condition_duration);
                preparedStatement.setInt(9, crit_damage);
                preparedStatement.setInt(10, healing_power);

                int rowsAffected = preparedStatement.executeUpdate();
                oos.flush();
                System.out.println(rowsAffected + " rows affected");
                System.out.println(index + " updated of " + mySets.size());
                con.commit();

            } // write list to ObjectOutputStream
            catch (SQLException ex) {
                Logger.getLogger(DbConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
            index++;
        }

    }

    public ArrayList getItemSets(scoreValues myvals) throws IOException, ClassNotFoundException {
        try {
            /*  SELECT * from item_set
            order by ABS(power-500) + ABS(set_precision-300) + ABS(vitality-0) + ABS(toughness-20) + ABS(concentration-0) + ABS(condition_damage-300) + ABS(expertise-0) + ABS(ferocity-300) + ABS(healing_power-500)
            limit 10;*/

            // for user values where !0 include abs, where 0 ignore
            String Sql = "SELECT * FROM item_set ORDER BY " + buildSqlString(myvals) + " limit 10;";
            ArrayList myResults = new ArrayList();
            System.out.println(Sql);
            Statement stmt = null;
            String query = Sql;

            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Blob myBlob = con.createBlob();
                myBlob = rs.getBlob("stored_set");
                InputStream binaryStream = myBlob.getBinaryStream();
                ObjectInputStream ois = new ObjectInputStream(binaryStream);
                String toString = myBlob.toString();
                System.out.println(toString);
                itemSet mySet = new itemSet();
                 mySet = (itemSet) ois.readObject();

                myResults.add(mySet);
            }
            return myResults;

        } catch (SQLException ex) {
            Logger.getLogger(DbConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public String buildSqlString(scoreValues myvals) {

        String genSql = "";
        boolean preceding = false;

        if (myvals.getPower() != 0) {
            genSql = genSql + "ABS(power-" + myvals.getPower() + ")";
            preceding = true;
        }
        if (myvals.getPrecision() != 0) {
            if (preceding == true) {
                genSql = genSql + " + ";
            }
            genSql = genSql + "ABS(set_precision-" + myvals.getPrecision() + ")";
            preceding = true;
        }
        if (myvals.getVitality() != 0) {
            if (preceding == true) {
                genSql = genSql + " + ";
            }
            genSql = genSql + "ABS(vitality-" + myvals.getVitality() + ")";
            preceding = true;
        }
        if (myvals.getToughness() != 0) {
            if (preceding == true) {
                genSql = genSql + " + ";
            }
            genSql = genSql + "ABS(toughness-" + myvals.getToughness() + ")";
            preceding = true;
        }
        if (myvals.getBoon_duration() != 0) {
            if (preceding == true) {
                genSql = genSql + " + ";
            }
            genSql = genSql + "ABS(concentration-" + myvals.getBoon_duration() + ")";
            preceding = true;
        }
        if (myvals.getCondition_damage() != 0) {
            if (preceding == true) {
                genSql = genSql + " + ";
            }
            genSql = genSql + "ABS(condition_damage-" + myvals.getCondition_damage() + ")";
            preceding = true;
        }
        if (myvals.getCondition_duration() != 0) {
            if (preceding == true) {
                genSql = genSql + " + ";
            }
            genSql = genSql + "ABS(expertise-" + myvals.getCondition_duration() + ")";
            preceding = true;
        }
        if (myvals.getCrit_damage() != 0) {
            if (preceding == true) {
                genSql = genSql + " + ";
            }
            genSql = genSql + "ABS(ferocity-" + myvals.getCrit_damage() + ")";
            preceding = true;
        }
        if (myvals.getHealing_power() != 0) {
            if (preceding == true) {
                genSql = genSql + " + ";
            }
            genSql = genSql + "ABS(healing_power-" + myvals.getHealing_power() + ")";
            preceding = true;
        }
        return genSql;
    }
}