package ua.inf.smart.transaction;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Transaction {
    public static void main(String[] args) throws SQLException {
        TransactionSingleton trAction = TransactionSingleton.getInstance();
        trAction.transfer("1000", 1, 1);
    }
}

class TransactionSingleton {
    private Connection connFrom;
    private Connection connTo;
    private static TransactionSingleton instance = null;
    public synchronized static TransactionSingleton getInstance() {
        if (instance == null) {
            instance = new TransactionSingleton();
            instance.getConnTo();
            instance.getConnFrom();
        }
        return instance;
    }
    private Connection getConnFrom() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connFrom = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/alfa_bank", "root", "1234");
            connFrom.setAutoCommit(false);
        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage()
                    + "SQLState: " + e.getSQLState());
        } catch (ClassNotFoundException ex) {
            System.out.println("Driver not found");
        }
        return connFrom;
    }
    private Connection getConnTo() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connTo = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/beta_bank", "root", "1234");
            connTo.setAutoCommit(false);
        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage()
                    + "SQLState: " + e.getSQLState());
        } catch (ClassNotFoundException e) {
            System.err.println("Driver not found");
        }
        return connTo;
    }
// method executes transfer
    public void transfer(String sumMove, int idFrom, int idTo) throws SQLException {
        Statement stmtFrom = null;
        Statement stmtTo = null;
        try {
            int sum = Integer.parseInt(sumMove);
            if (sum <= 0) {
                throw new NumberFormatException("less or equals zero");
            }
            stmtFrom = connFrom.createStatement();
            stmtTo = connTo.createStatement();
// transaction of 4 queries
            ResultSet resSetFrom =
                    stmtFrom.executeQuery("SELECT amount FROM account WHERE id=" + idFrom);
            ResultSet resSetTo =
                    stmtTo.executeQuery("SELECT amount FROM account WHERE id=" + idTo);
            int amountFrom = 0;
            while (resSetFrom.next()) {
                amountFrom = resSetFrom.getInt(1);
            }
            int resultFrom = 0;
            if (amountFrom >= sum) {
                resultFrom = amountFrom - sum;
            } else {
                throw new SQLException("Invalid balance");
            }
            int amountTo = 0;
            while (resSetTo.next())
                amountTo = resSetTo.getInt(1);
            int resultTo = amountTo + sum;

            stmtFrom.executeUpdate(
                    "UPDATE account SET amount=" + resultFrom + " WHERE id=" + idFrom);
            stmtTo.executeUpdate(
                    "UPDATE account SET amount=" + resultTo  + " WHERE id =" + idTo);
// transaction completion
            connFrom.commit();
            connTo.commit();
            System.out.println("Remaining on id_" + idFrom + ": " + resultFrom + " USD");
            System.out.println("Remaining on id_" + idTo + ": " + resultTo + " USD");
        } catch (SQLException e) {
            System.err.println("SQLState: " + e.getSQLState()
                    + "Error Message: " + e.getMessage());
// transaction rollback on error
            connFrom.rollback();
            connTo.rollback();
        } catch (NumberFormatException e) {
            System.err.println("Invalid summa: " + sumMove);
        } finally {
            if (stmtFrom != null) {
                try {
                    stmtFrom.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stmtTo != null) {
                try {
                    stmtTo.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (connFrom != null) {
                try {
                    connFrom.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (connTo != null) {
                try {
                    connTo.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }
}