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

import java.sql.*;

/**
 *
 * @author Emergency Loaner 1
 */
public class atmDB {

    Connection con = null;

    public atmDB() {

        try {
            String path = "jdbc:mysql://localhost/atmdatabase";
            String user = "root";
            String pass = "";
            con = DriverManager.getConnection(path, user, pass);
        } catch (SQLException e) {
            System.out.println(e);
        }
    }

    public boolean createAccount(Account a) {

        try {
            Statement stmt = con.createStatement();
            String query = "INSERT INTO account VALUES("
                    + a.getAccountNumber() + ", "
                    + a.getBalance() + ", "
                    + a.getPin() + ", "
                    + a.getAccountType() + ", "
                    + a.getCustomer().getCustomerID() + ")";
            int rs = stmt.executeUpdate(query);

            if (rs > 0) {
                return true;
            } else {
                return false;
            }

        } catch (Exception e) {
            System.out.println(e);
            return false;
        }
    }

    public boolean checkUnique(long i) {
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT accountNumber from account where accountNumber = " + i);
            if (rs.next()) {
                return false;
            } else {
                return true;
            }
        } catch (Exception e) {
            return false;
        }

    }

    public int getNewCID() {
        int max = 0;
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT MAX(customerID) from customer");
            while (rs.next()) {
                max = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return max;
    }

    public boolean createCustomer(Customer c) {
        String firstName = c.getFirstName();
        String lastName = c.getLastName();
        String email = c.getEmail();
        int phone = c.getPhone();
        int mobilePhone = c.getMobilePhone();
        String addressLine1 = c.getAddressLine1();
        String addressLine2 = c.getAddressLine2();
        String city = c.getCity();
        String state = c.getState();
        String postalCod = c.getPostalCod();
        int dateOfBirth = c.getDateOfBirth();
        int monthOfBirth = c.getMonthOfBirth();
        int yearOfBirth = c.getYearOfBirth();

        try {
            Statement stmt = con.createStatement();
            String query = "INSERT into customer (firstName, lastName, email, fixedPhone, mobilePhone, addressLine1, addressLine2, city, state, postalCode, dateOfBirth, monthOfBirth, yearOfBirth) values ('" + firstName + "','"
                    + lastName + "','"
                    + email + "','"
                    + phone + "','"
                    + mobilePhone + "','"
                    + addressLine1 + "','"
                    + addressLine2 + "','"
                    + city + "','"
                    + state + "','"
                    + postalCod + "','"
                    + dateOfBirth + "','"
                    + monthOfBirth + "','"
                    + yearOfBirth + "')";
            System.out.println(query);
            int i = stmt.executeUpdate(query);
            if (i > 0) {
                return true;
            } else {
                return false;


            }
        } catch (Exception e) {
            System.out.println(e);
            return false;
        }
    }

    public boolean checkAccount(long aN, int aP) {
        try {
            Statement stmt = con.createStatement();
            String query = "SELECT accountNumber, pin FROM account WHERE accountNumber = " + aN + " AND pin = " + aP;
            ResultSet rs = stmt.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            return false;
        }
    }

    public boolean testAccount(long aN) {
        try {
            Statement stmt = con.createStatement();
            String query = "SELECT accountNumber, pin FROM account WHERE accountNumber = " + aN;
            ResultSet rs = stmt.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            return false;
        }
    }

    public double getBalance(long aN) {
        try {
            Statement stmt = con.createStatement();
            String query = "SELECT balance FROM account WHERE accountNumber = " + aN;
            ResultSet rs = stmt.executeQuery(query);
            if (rs.next()) {
                return rs.getDouble(1);

            } else {

                return 0;
            }
        } catch (Exception e) {
            System.out.println(e);
            return 0;
        }
    }

    public void withdraw(long aN, double a) {
        try {
            Statement stmt = con.createStatement();
            String query = "SELECT balance FROM account WHERE accountNumber = " + aN;
            ResultSet rs = stmt.executeQuery(query);
            if (rs.next()) {
                double balance = rs.getDouble(1);
                if (balance > a) {
                    double newBalance = balance - a;
                    query = "UPDATE account set balance = " + newBalance + " where accountNumber = " + aN;
                    int res = stmt.executeUpdate(query);
                }
            } else {
            }
        } catch (Exception e) {
        }
    }

    public void deposit(long aN, double a) {
        try {
            Statement stmt = con.createStatement();
            String query = "SELECT balance FROM account WHERE accountNumber = " + aN;
            ResultSet rs = stmt.executeQuery(query);
            if (rs.next()) {
                double balance = rs.getDouble(1);
                double newBalance = balance + a;
                query = "UPDATE account set balance = " + newBalance + " where accountNumber = " + aN;
                int res = stmt.executeUpdate(query);
            } else {
            }
        } catch (Exception e) {
        }
    }

    public boolean transfer(long fAN, long tAN, double a) {
        try {
            if (this.testAccount(tAN)) {
                double tBal = this.getBalance(tAN);
                double fBal = this.getBalance(fAN);
                double newTBal = tBal + a;
                double newFBal = fBal - a;
                System.out.println(newFBal);

                if (newFBal > 0) {
                    Statement stmt = con.createStatement();
                    String query = "UPDATE account set balance = " + newTBal + " where accountNumber = " + tAN;
                    stmt.executeUpdate(query);
                    query = "UPDATE account set balance = " + newFBal + " where accountNumber = " + fAN;
                    int rs = stmt.executeUpdate(query);
                    if (rs > 0) {
                        return true;
                    }

                } else {
                    return false;
                }
            } else {
                return false;

            }
        } catch (Exception e) {
            return false;
        }
        return false;
    }
}