package PasswordGenerator;

import java.sql.*;

/**
 *
 * @author emil
 */
public class LoginDB {

    public static final int USER_NOT_IN_DB = 0;
    public static final int SUCCESS = 1;
    public static final int DUPLICATE_USERS_EXIST = 2;
    public static final int PATIENT_ACCSESS = 3;
    public static final int DOCTOR_ACCSESS = 4;
    public static final int ADMIN_ACCESS = 5;

    static final int MAX_ONE_TIME_PASSWORD_NBR = 100;

    public static int checkLogin(String username, String password) throws SQLException {

        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        String query = null;
        int result = -1;

        query = "SELECT loginPatient(?,?)";
        ps = connection.prepareStatement(query);
        ps.setString(1, username);
        ps.setString(2, password);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            result = rs.getInt(1);
        }

        if (result == SUCCESS) {
            return PATIENT_ACCSESS;
        } else if (result >= DUPLICATE_USERS_EXIST) {
            //Logg
        } else {
            query = "SELECT loginDoctor(?,?)";
            ps = connection.prepareStatement(query);
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();

            while (rs.next()) {
                result = rs.getInt(1);
            }

            if (result == SUCCESS) {
                return DOCTOR_ACCSESS;
            } else if (result >= DUPLICATE_USERS_EXIST) {
                //Logg
            } else {
                query = "SELECT loginAdmin(?,?)";
                ps = connection.prepareStatement(query);
                ps.setString(1, username);
                ps.setString(2, password);
                rs = ps.executeQuery();

                while (rs.next()) {
                    result = rs.getInt(1);
                }

                if (result == SUCCESS) {
                    return ADMIN_ACCESS;
                } else if (result >= DUPLICATE_USERS_EXIST) {
                    //Logg
                }
            }
        }

        DB.kopplaNer();

        return USER_NOT_IN_DB;
    }

    public static int loginPatientOneTimePasswords(int patientId, String password) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        String query = null;
        int result = -1;

        query = "SELECT loginPatientOneTimePasswords(?,SHA1(?))";
        ps = connection.prepareStatement(query);
        ps.setInt(1, patientId);
        ps.setString(2, password);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            result = rs.getInt(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        if (result == SUCCESS) {
            return PATIENT_ACCSESS;
        }

        return USER_NOT_IN_DB;
    }

    public static int loginDoctorOneTimePasswords(int doctorId, String password) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        String query = null;
        int result = -1;

        query = "SELECT loginDoctorOneTimePasswords(?,SHA1(?))";
        ps = connection.prepareStatement(query);
        ps.setInt(1, doctorId);
        ps.setString(2, password);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            result = rs.getInt(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        if (result == SUCCESS) {
            return DOCTOR_ACCSESS;
        }

        return USER_NOT_IN_DB;
    }

    public static int loginAdminOneTimePasswords(int adminId, String password) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        String query = null;
        int result = -1;

        query = "SELECT loginAdminOneTimePasswords(?,SHA1(?))";
        ps = connection.prepareStatement(query);
        ps.setInt(1, adminId);
        ps.setString(2, password);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            result = rs.getInt(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        if (result == SUCCESS) {
            return ADMIN_ACCESS;
        }

        return USER_NOT_IN_DB;
    }

    public static boolean checkIfPaientsLatsOneTimePasswordIsUsed(int patientId) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        String query = null;
        int result = -1;

        query = "SELECT MIN(passwordNumber) FROM PatientOneTimePasswords WHERE patientId=?";
        ps = connection.prepareStatement(query);
        ps.setInt(1, patientId);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            result = rs.getInt(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return result == MAX_ONE_TIME_PASSWORD_NBR;
    }

    public static boolean checkIfDoctorsLatsOneTimePasswordIsUsed(int doctorId) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        String query = null;
        int result = -1;

        query = "SELECT MIN(passwordNumber) FROM DoctorOneTimePasswords WHERE doctorId=?";
        ps = connection.prepareStatement(query);
        ps.setInt(1, doctorId);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            result = rs.getInt(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return result == MAX_ONE_TIME_PASSWORD_NBR;
    }

    public static int checkLoginHashedPassword(String username, String password) throws SQLException {

        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        String query = null;
        int result = -1;

        query = "SELECT loginPatient(?,SHA1(?))";
        ps = connection.prepareStatement(query);
        ps.setString(1, username);
        ps.setString(2, password);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            result = rs.getInt(1);
        }

        if (result == SUCCESS) {
            return PATIENT_ACCSESS;
        } else if (result >= DUPLICATE_USERS_EXIST) {
            //Logg
        } else {
            query = "SELECT loginDoctor(?,SHA1(?))";
            ps = connection.prepareStatement(query);
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();

            while (rs.next()) {
                result = rs.getInt(1);
            }

            if (result == SUCCESS) {
                return DOCTOR_ACCSESS;
            } else if (result >= DUPLICATE_USERS_EXIST) {
                //Logg
            } else {
                query = "SELECT loginAdmin(?,SHA1(?))";
                ps = connection.prepareStatement(query);
                ps.setString(1, username);
                ps.setString(2, password);
                rs = ps.executeQuery();

                while (rs.next()) {
                    result = rs.getInt(1);
                }

                if (result == SUCCESS) {
                    return ADMIN_ACCESS;
                } else if (result >= DUPLICATE_USERS_EXIST) {
                    //Logg
                }
            }
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return USER_NOT_IN_DB;
    }

    public static int getCurrOneTimePasswordNumberPatient(int patientId) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int passwordNumber = 0;

        ps = connection.prepareStatement("SELECT MIN(passwordNumber) FROM PatientOneTimePasswords WHERE patientId=?");
        ps.setInt(1, patientId);
        rs = ps.executeQuery();

        if (rs.next()) {
            passwordNumber = rs.getInt(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return passwordNumber;

    }

    public static int getCurrOneTimePasswordNumberDoctor(int doctorId) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int passwordNumber = 0;

        ps = connection.prepareStatement("SELECT MIN(passwordNumber) FROM DoctorOneTimePasswords WHERE doctorId=?");
        ps.setInt(1, doctorId);
        rs = ps.executeQuery();

        if (rs.next()) {
            passwordNumber = rs.getInt(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return passwordNumber;

    }

    public static int getCurrOneTimePasswordNumberAdmin(int adminId) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int passwordNumber = 0;

        ps = connection.prepareStatement("SELECT MIN(passwordNumber) FROM AdminOneTimePasswords WHERE adminId=?");
        ps.setInt(1, adminId);
        rs = ps.executeQuery();

        if (rs.next()) {
            passwordNumber = rs.getInt(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return passwordNumber;

    }

    public static String getHashedOneTimePasswordPatient(String patientUsername) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String hashedPassword = null;

        ps = connection.prepareStatement("SELECT tempPatientOneTimePassword From Patient WHERE patientUsername=?");
        ps.setString(1, patientUsername);
        rs = ps.executeQuery();

        if (rs.next()) {
            hashedPassword = rs.getString(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return hashedPassword;

    }

    public static String getHashedOneTimePasswordDoctor(String doctorUsername) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String hashedPassword = null;

        ps = connection.prepareStatement("SELECT tempDoctorOneTimePassword From Doctor WHERE doctorUsername=?");
        ps.setString(1, doctorUsername);
        rs = ps.executeQuery();

        if (rs.next()) {
            hashedPassword = rs.getString(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return hashedPassword;

    }

    public static int checkAccessRights(String username, String oneTimePassword) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        String query = null;
        int result = -1;

        query = "SELECT checkPatientCurrOneTimePassword(?,?)";
        ps = connection.prepareStatement(query);
        ps.setString(1, username);
        ps.setString(2, oneTimePassword);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            result = rs.getInt(1);
        }

        if (result == SUCCESS) {
            return PATIENT_ACCSESS;

        } else {
            query = "SELECT checkDoctorCurrOneTimePassword(?,?)";
            ps = connection.prepareStatement(query);
            ps.setString(1, username);
            ps.setString(2, oneTimePassword);
            rs = ps.executeQuery();

            while (rs.next()) {
                result = rs.getInt(1);
            }

            if (result == SUCCESS) {
                return DOCTOR_ACCSESS;

            } else {
//                query = "SELECT checkDoctorCurrOneTimePassword(?,?)";
//                ps = connection.prepareStatement(query);
//                ps.setString(1, username);
//                ps.setString(2, oneTimePassword);
//                rs = ps.executeQuery();
//
//                while (rs.next()) {
//                    result = rs.getInt(1);
//                }

            }

        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return USER_NOT_IN_DB;

    }

    public static String getHashedPasswordDoctor(String doctorUsername, String doctorPassword) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String hashedPassword = null;

        ps = connection.prepareStatement("SELECT doctorPassword From Doctor WHERE doctorUsername=? AND doctorPassword=SHA1(?)");
        ps.setString(1, doctorUsername);
        ps.setString(2, doctorPassword);
        rs = ps.executeQuery();

        if (rs.next()) {
            hashedPassword = rs.getString(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return hashedPassword;

    }

    public static String getHashedPasswordAdmin(String adminUsername, String adminPassword) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String hashedPassword = null;

        ps = connection.prepareStatement("SELECT adminPassword From Admin WHERE adminUsername=? AND adminPassword=SHA1(?)");
        ps.setString(1, adminUsername);
        ps.setString(2, adminPassword);
        rs = ps.executeQuery();

        if (rs.next()) {
            hashedPassword = rs.getString(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return hashedPassword;

    }

    public static void setPatientLastLoggedInTimestamp(String username, String password) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;

        ps = connection.prepareStatement("UPDATE Patient SET lastLoggedIn=now() WHERE patientUsername=? AND patientPassword=SHA1(?)");
        ps.setString(1, username);
        ps.setString(2, password);
        ps.executeUpdate();

        DBUtil.closePreparedStatement(ps);
        DB.kopplaNer();

    }

    public static String getPatientLastLoggedInTimestamp(String username) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String lastLoggedInTimestamp = null;

        ps = connection.prepareStatement("SELECT lastLoggedIn FROM Patient WHERE patientUsername=?");
        ps.setString(1, username);
        rs = ps.executeQuery();

        if (rs.next()) {
            lastLoggedInTimestamp = rs.getString(1);
        }

        DBUtil.closePreparedStatement(ps);
        DBUtil.closeResultSet(rs);
        DB.kopplaNer();

        return lastLoggedInTimestamp;

    }

    public static void changePasswordPatient(String username, String newPassword) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;

        ps = connection.prepareStatement("UPDATE Patient SET patientPassword=SHA1(?) where patientUsername=?");
        ps.setString(1, newPassword);
        ps.setString(2, username);
        ps.executeUpdate();

        DBUtil.closePreparedStatement(ps);
        DB.kopplaNer();

    }

    public static void changePasswordDoctor(String username, String newPassword) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;

        ps = connection.prepareStatement("UPDATE Doctor SET doctorPassword=SHA1(?) where doctorUsername=?");
        ps.setString(1, newPassword);
        ps.setString(2, username);
        ps.executeUpdate();

        DBUtil.closePreparedStatement(ps);
        DB.kopplaNer();

    }

    public static void changePasswordAdmin(String username, String newPassword) throws SQLException {
        Connection connection = DB.kopplaUpp();
        PreparedStatement ps = null;

        ps = connection.prepareStatement("UPDATE Admin SET adminPassword=SHA1(?) where adminUsername=?");
        ps.setString(1, newPassword);
        ps.setString(2, username);
        ps.executeUpdate();

        DBUtil.closePreparedStatement(ps);
        DB.kopplaNer();

    }
}