/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cims_project;

import com.mysql.jdbc.PreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;
import javax.swing.JOptionPane;

/**
 *
 * @author Aron
 */
public class ClientDBControl {

    private String pass;
    private String user;
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;
    private PreparedStatement ps;

    public ClientDBControl() {}

    public void Connection() throws SQLException{
        String path = "jdbc:mysql://localhost/cims";
        user = "root";
        pass = "";
        conn = DriverManager.getConnection(path, user, pass);
        stmt = conn.createStatement();
    }

    private void log(Object aObject) {
        System.out.println(aObject);
    }

    public boolean addClient(Client c) throws ClassNotFoundException {
        final int mmv = 1000;
        final int wins = 0;
        final int losses = 0;
        final int draws = 0;
        boolean result = false;
        String insertString = "";

        insertString = "INSERT INTO clients (fname, lname, cphone, cemail, wins, losses, draws, mmv, warhammer, warhammer_40k)"
                + "VALUES('"
                + c.getFname() + "','"
                + c.getLname() + "','"
                + c.getCphone() + "','"
                + c.getCemail() + "','"
                + wins + "','"
                + losses + "','"
                + draws + "','"
                + mmv + "','"
                + c.isWh() + "','"
                + c.isWh40k() + "')";
        try {
            Connection();
            stmt.executeUpdate(insertString);
            result = true;
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            Object [] options = {"OK","Cancel"};
            JOptionPane.showOptionDialog(null, "Too many characters inputted, please try again." + ex.getLocalizedMessage(), "WARNING", JOptionPane.DEFAULT_OPTION, JOptionPane.ERROR_MESSAGE, null, options, options[0]);
        }
        return result;
    }

        public boolean modifyClient(Client c, int id) throws ClassNotFoundException {
            boolean result = false;
            String updateString = "";

            updateString = "UPDATE clients "
                + "SET fname='"+ c.getFname() + "',"
                + "lname='" + c.getLname() + "',"
                + "cphone='" + c.getCphone() + "',"
                + "cemail='" + c.getCemail() + "',"
                + "wins='" + c.getWins() + "',"
                + "losses='" +c.getLosses() + "',"
                + "draws='" + c.getLosses() + "',"
                + "mmv='" + c.getMmv() + "',"
                + "warhammer='" + c.isWh() + "',"
                + "warhammer_40k='" + c.isWh40k() + "'"
                + "WHERE c_id = '" + id + "'";

        try {
            Connection();
            stmt.executeUpdate(updateString);
            result = true;
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            Object [] options = {"OK","Cancel"};
            JOptionPane.showOptionDialog(null, "Too many characters inputted, please try again." + ex.getLocalizedMessage(), "WARNING", JOptionPane.DEFAULT_OPTION, JOptionPane.ERROR_MESSAGE, null, options, options[0]);
        }

        return result;
    }

    public Client selectClient(int id) throws Exception {
        Client c = new Client();
        int c_id;
        String queryString = "";

        c_id = id;
        queryString = "SELECT * FROM clients WHERE c_id='" + c_id + "'";

        try {
            Connection();
            rs = stmt.executeQuery(queryString);

            while (rs.next()) {
                c.setC_id(rs.getInt(1));
                c.setFname(rs.getString(2));
                c.setLname(rs.getString(3));
                c.setCphone(rs.getString(4));
                c.setCemail(rs.getString(5));
                c.setWins(rs.getInt(6));
                c.setLosses(rs.getInt(7));
                c.setDraws(rs.getInt(8));
                c.setMmv(rs.getInt(9));
                c.setWh(rs.getInt(10));
                c.setWh40k(rs.getInt(11));
            }

            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            log("SQL Exception in selectClient method " + ex.getLocalizedMessage());
        }



        return c;
    }

    public ResultSet getAllClients() throws Exception {
        String queryString = "SELECT * FROM clients";
        Connection();
        ResultSet rs = stmt.executeQuery(queryString);
//        conn.close();
        return rs;
    }

    public boolean deleteClient(Client c, int id) throws ClassNotFoundException, ClassNotFoundException {
        boolean result = false;
        try {
            Connection();
            String deleteString = "DELETE FROM clients WHERE c_id = " + "'" + id + "'";
            stmt.executeUpdate(deleteString);
            result = true;
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
             Object [] options = {"OK","Cancel"};
            JOptionPane.showOptionDialog(null, "Client does not exist!" + ex.getLocalizedMessage(), "WARNING", JOptionPane.ERROR, JOptionPane.ERROR_MESSAGE, null, options, options[0]);
        }
        return result;
    }
}