package tui;

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

public class Service
{
    private static Connection conn = null;

    public static Connection createConnection() {
        try {
            String userName = "root";
            String password = "whenever";
            String url = "jdbc:mysql://localhost/bank";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            System.out.println("\tDatabase connection established");
        }
        catch (Exception e) {
            System.err.println("Cannot connect to database server: " + e);
            throw new ServiceException(e.getMessage());
        }
        return conn;
    }

    public static void closeConnection() {
        if (conn == null)
            return;

        try {
            conn.close();
            System.out.println("\tDatabase connection closed");
        }
        catch (SQLException e) {
            System.out.println("Cann't close connection\n " + e.getMessage());
            throw new ServiceException(e.getMessage());
        }
    }

    public static void startTransaction() {
        try {
            conn.setAutoCommit(false);
        }
        catch (SQLException e) {
            throw new ServiceException(e.getMessage());
        }
    }

    public static void commit() {
        try {
            conn.commit();
        }
        catch (SQLException e) {
            System.out.println(e);
        }
    }

    public static void rollback() {
        try {
            conn.rollback();
        }
        catch (SQLException e) {
            throw new ServiceException(e.getMessage());
        }
    }

    enum IsoLevel
    {
        READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE;
    }

    public static void settransactionIsolationLevel(IsoLevel level) {
        try {
            switch (level) {
            case READ_UNCOMMITTED:
                conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
                break;
            case READ_COMMITTED:
                conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                break;
            case REPEATABLE_READ:
                conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
                break;
            case SERIALIZABLE:
                conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                break;
            default:
                break; //not possible
            }
        }
        catch (SQLException e) {
            throw new ServiceException(e.getMessage());
        }
    }

    //-------------------------------------------------------------------------

    public static boolean existsKonto(int kontoNr) {
        boolean found = false;
        try {
            PreparedStatement p;
            p = conn.prepareStatement("SELECT COUNT(*) FROM account WHERE number = ?");
            p.setInt(1, kontoNr);
            ResultSet rs = p.executeQuery();
            rs.first();
            if (rs.getInt(1) == 1)
                found = true;
            p.close();
        }
        catch (SQLException e) {
            throw new ServiceException(e.getMessage());
        }
        return found;
    }

    public static int getSaldo(int kontoNr) {
        int saldo = -1;
        try {
            PreparedStatement p;
            p = conn.prepareStatement("SELECT balance FROM account WHERE number = ?");
//            p = conn.prepareStatement("SELECT balance FROM account WHERE number = ? LOCK IN SHARE MODE");
            p.setInt(1, kontoNr);
            ResultSet rs = p.executeQuery();
            rs.first();
            saldo = rs.getInt("balance");
            rs.close();
            p.close();
        }
        catch (SQLException e) {
            throw new ServiceException(e.getMessage());
        }
        return saldo;
    }

    public static void opdaterSaldo(int kontoNr, int nySaldo) {
        try {
            PreparedStatement p;
            p = conn.prepareStatement("UPDATE account SET balance = ? WHERE number = ?");
            p.setInt(1, nySaldo);
            p.setInt(2, kontoNr);
            p.executeUpdate();
            p.close();
        }
        catch (SQLException e) {
            throw new ServiceException(e.getMessage());
        }
    }

}