import java.sql.Connection;
import java.sql.SQLException;
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 exceptions.AccountNotFoundException;
import exceptions.NotMuchMoneyException;
import exceptions.OverflowAccountException;

public class Bank  {
    private static long last = 0;
    private static String user = "nauru";//Логин пользователя
    private static String password = "17981798";//Пароль пользователя
    private static String url = "jdbc:mysql://localhost:3306/bank";//URL адрес
    private static String driver = "com.mysql.jdbc.Driver";//Имя драйвера
    private static Connection c = null;//Соединение с БД
    private static Statement st;
    static {
         try {
             Class.forName(driver);//Регистрируем драйвер
        } catch (ClassNotFoundException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
        }
        try{
            c = DriverManager.getConnection(url, user, password);
            st = c.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static long createAccount() {
        long res = 0;

        try {
            ResultSet rs = st.executeQuery("select MAX(account) from accounts");//Выполняем запрос
            while (rs.next()) {
                System.out.println(rs.getString("max(account)"));
                res = Long.parseLong(rs.getString("max(account)"));
            }
            res++;
            st.execute("INSERT INTO ACCOUNTS VALUES (" + res + "," + 0 +")");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return res;
    }

    public static long getAmount(long number) throws AccountNotFoundException {
        long result = -1;
        try {
            ResultSet rs = st.executeQuery("select * from accounts where(account = "+number+")");
            if (rs.next()) {
                result = rs.getLong("amount");
            } else {
                throw new AccountNotFoundException();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }

    public static void deleteAccount(long account) throws AccountNotFoundException {
        try {
            int count = st.executeUpdate("DELETE from accounts where(account="+account+");");

            if (count == 0) {
                throw new AccountNotFoundException();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void withdrawAccount(long account, long count) throws AccountNotFoundException, NotMuchMoneyException {
        long amount = Bank.getAmount(account);
        if (amount < count) {
            throw new NotMuchMoneyException();
        }
        amount -= count;
        try {
            st.executeUpdate("UPDATE accounts set amount = "+ amount + " where (account = "+ account+")");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void addMoneyToAccount(long account, long count) throws AccountNotFoundException, OverflowAccountException {
        try {
            int res = st.executeUpdate("Update accounts set amount = amount + "+ count +
                    " where (account = "+ account +")");
            if (res == 0) {
                throw new AccountNotFoundException();
            }
        } catch (SQLException e) {
            if (e.getMessage().contains("Data truncation: Out of range value for column")) {
                throw new OverflowAccountException();
            }
            e.printStackTrace();
        }
    }

    public static ArrayList<Long> getListAccount() {
        ArrayList<Long> result = new ArrayList<Long>();
        try {
            ResultSet rs = st.executeQuery("select * from accounts where (account > 0)");
            while (rs.next()) {
                result.add(rs.getLong("account"));
                result.add(rs.getLong("amount"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }

    public static void transfer(long from, long to, long money) throws AccountNotFoundException, NotMuchMoneyException, OverflowAccountException {
        long amountFrom = Bank.getAmount(from);
        long amountTo = Bank.getAmount(to);
        if (amountFrom < money) {
            throw new NotMuchMoneyException();
        }
        try {
            st.executeUpdate("Update accounts set amount = amount + "+ money +
                    " where (account = "+ to +")");
            st.executeUpdate("Update accounts set amount = amount - " + money +
                    " where (account = "+ from +")");

        } catch (SQLException e) {
            if (e.getMessage().contains("Data truncation: Out of range value for column")) {
                throw new OverflowAccountException();
            }
            e.printStackTrace();
        }
    }

    protected  void finalize() {
        try {
            c.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }





}