package org.wso2.carbon.device.mgt.mobile.impl.ios.dao.impl.profile;

import org.apache.commons.lang3.BooleanUtils;
import org.wso2.carbon.context.PrivilegedCarbonContext;
import org.wso2.carbon.device.mgt.common.PaginationRequest;
import org.wso2.carbon.device.mgt.ios.payload.beans.DeviceProfile;
import org.wso2.carbon.device.mgt.mobile.impl.ios.dao.IOSDAOFactory;
import org.wso2.carbon.device.mgt.mobile.impl.ios.dao.MobileDeviceManagementDAOException;
import org.wso2.carbon.device.mgt.mobile.impl.ios.dao.ProfileDAO;
import org.wso2.carbon.device.mgt.mobile.impl.ios.dao.util.MobileDeviceManagementDAOUtil;
import org.wso2.carbon.device.mgt.mobile.impl.ios.dto.DEPDevice;
import org.wso2.carbon.device.mgt.mobile.impl.ios.dto.DEPProfile;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;

public class AbstractProfileDAO implements ProfileDAO{

    public static final String STATUS_EMPTY = "empty";

    public boolean addProfile(DeviceProfile deviceProfile) throws MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "INSERT INTO IOS_DEVICE_PROFILE(OPERATION_ID, OPERATION_CODE, PROFILE_ID, " +
                    "DEVICE_IDENTIFICATION, PROPERTIES) VALUES (?, ?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, deviceProfile.getOperationId());
            stmt.setString(2, deviceProfile.getOperationCode());
            stmt.setString(3, deviceProfile.getProfileId());
            stmt.setString(4, deviceProfile.getDeviceId());
            stmt.setString(5, deviceProfile.getProperties());
            stmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while adding the profile", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, null);
        }
    }

    public DeviceProfile getProfile(String deviceId, String url) throws MobileDeviceManagementDAOException {

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "SELECT OPERATION_ID, OPERATION_CODE, PROFILE_ID, DEVICE_IDENTIFICATION," +
                    " PROPERTIES FROM IOS_DEVICE_PROFILE WHERE DEVICE_IDENTIFICATION = ? AND PROPERTIES = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, deviceId);
            stmt.setString(2, url);
            rs = stmt.executeQuery();

            DeviceProfile deviceProfile = new DeviceProfile();
            while (rs.next()) {
                deviceProfile.setOperationCode(rs.getString("OPERATION_CODE"));
                deviceProfile.setProfileId(rs.getString("PROFILE_ID"));
                deviceProfile.setProperties(rs.getString("PROPERTIES"));
                deviceProfile.setOperationId(rs.getString("OPERATION_ID"));
                deviceProfile.setDeviceId(rs.getString("DEVICE_IDENTIFICATION"));
            }
            return deviceProfile;
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while fetching the registered device types", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, rs);
        }
    }

    public DEPProfile getDEPProfile(String uuid) throws
            MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        DEPProfile depProfile = new DEPProfile();
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "SELECT ID, UUID, PROFILE_NAME, PROFILE_CONTENT, TENANT_DOMAIN FROM IOS_DEP_PROFILE " +
                    "WHERE UUID = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, uuid);
            rs = stmt.executeQuery();
            while (rs.next()) {
                depProfile.setId(rs.getString("ID"));
                depProfile.setUUID(rs.getString("UUID"));
                depProfile.setProfileName(rs.getString("PROFILE_NAME"));
                depProfile.setProfileContent(rs.getString("PROFILE_CONTENT"));
                depProfile.setTenantDomain(rs.getString("TENANT_DOMAIN"));
            }
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while adding the " +
                    "DEP profile.", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, rs);
        }
        return depProfile;
    }

    public List<DEPProfile> getDEPProfiles(PaginationRequest request) throws MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<DEPProfile> depProfiles = new ArrayList<>();
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "SELECT ID, UUID, PROFILE_NAME, PROFILE_CONTENT, TENANT_DOMAIN FROM IOS_DEP_PROFILE WHERE " +
                    "TENANT_DOMAIN = ? ORDER BY ID LIMIT ?,?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantDomain());
            stmt.setInt(2, request.getStartIndex());
            stmt.setInt(3, request.getRowCount());
            rs = stmt.executeQuery();
            while (rs.next()) {
                DEPProfile depProfile = new DEPProfile();
                depProfile.setId(rs.getString("ID"));
                depProfile.setUUID(rs.getString("UUID"));
                depProfile.setProfileName(rs.getString("PROFILE_NAME"));
                depProfile.setProfileContent(rs.getString("PROFILE_CONTENT"));
                depProfile.setTenantDomain(rs.getString("TENANT_DOMAIN"));
                depProfiles.add(depProfile);
            }
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while fetching DEP profiles.", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, rs);
        }
        return depProfiles;
    }

    public int getDEPProfilesCount() throws MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int count = 0;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "SELECT COUNT(ID) AS PROFILE_COUNT FROM IOS_DEP_PROFILE " +
                    "WHERE TENANT_DOMAIN = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantDomain());
            rs = stmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt("PROFILE_COUNT");
            }
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while adding the " +
                    "DEP profile.", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, rs);
        }
        return count;
    }

    public int addDEPProfile(DEPProfile depProfile) throws
            MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        int id = -1;
        ResultSet rs = null;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "INSERT INTO IOS_DEP_PROFILE(UUID, PROFILE_NAME, PROFILE_CONTENT, TENANT_DOMAIN) " +
                    "VALUES (?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql, new String[]{"id"});
            stmt.setString(1, depProfile.getUUID());
            stmt.setString(2, depProfile.getProfileName());
            stmt.setString(3, depProfile.getProfileContent());
            stmt.setString(4, PrivilegedCarbonContext.getThreadLocalCarbonContext()
                    .getTenantDomain());
            stmt.executeUpdate();
            rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while adding the " +
                    "DEP profile.", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, rs);
        }
        return id;
    }

    public boolean updateDEPProfile(DEPProfile depProfile) throws
            MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        boolean status = false;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "UPDATE IOS_DEP_PROFILE SET UUID = ?, " +
                    "PROFILE_NAME = ?, PROFILE_CONTENT = ?, TENANT_DOMAIN = ? WHERE ID = ? ";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, depProfile.getUUID());
            stmt.setString(2, depProfile.getProfileName());
            stmt.setString(3, depProfile.getProfileContent());
            stmt.setString(4, PrivilegedCarbonContext.getThreadLocalCarbonContext()
                    .getTenantDomain());
            stmt.setString(5, depProfile.getId());
            int count = stmt.executeUpdate();
            if (count > 0) {
                status = true;
            }
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while adding the " +
                    "DEP device.", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, null);
        }
        return status;
    }

    public boolean addDEPDevice(DEPDevice depDevice) throws
            MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        boolean status = false;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "INSERT INTO IOS_PRE_ENROLLED_DEVICE(SERIAL, USERNAME, TENANT_DOMAIN, " +
                    "STATUS, PROFILE_ASSIGN_TIME, PROFILE_PUSH_TIME, " +
                    "DEVICE_ASSIGNED_TIME,DEVICE_ASSIGNED_BY, NEED_BASIC_AUTH, OS, DEVICE_FAMILY," +
                    " DEVICE_MODEL, DESCRIPTION, COLOR, IS_AGENT_REQUIRED) VALUES " +
                    "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, depDevice.getSerial());
            stmt.setString(2, depDevice.getUsername());
            stmt.setString(3, depDevice.getTenantDomain());
            stmt.setString(4, depDevice.getStatus());
            if (depDevice.getProfileAssignTime() != null && !depDevice.getProfileAssignTime().isEmpty()) {
                Instant instant = Instant.parse(depDevice.getProfileAssignTime());
                stmt.setTimestamp(5, Timestamp.from(instant));
            } else {
                stmt.setNull(5, java.sql.Types.DATE);
            }

            if (depDevice.getProfilePushTime() != null && !depDevice.getProfilePushTime().isEmpty()) {
                Instant instant = Instant.parse(depDevice.getProfilePushTime());
                stmt.setTimestamp(6, Timestamp.from(instant));
            } else {
                stmt.setNull(6, java.sql.Types.DATE);
            }

            if (depDevice.getDeviceAssignedTime() != null && !depDevice.getDeviceAssignedTime().isEmpty()) {
                Instant instant = Instant.parse(depDevice.getDeviceAssignedTime());
                stmt.setTimestamp(7, Timestamp.from(instant));
            } else {
                stmt.setNull(7, java.sql.Types.DATE);
            }
            stmt.setString(8, depDevice.getDeviceAssignedBy());
            stmt.setInt(9, BooleanUtils.toInteger(depDevice.getNeedBasicAuth()));
            stmt.setString(10, depDevice.getOS());
            stmt.setString(11, depDevice.getDeviceFamily());
            stmt.setString(12, depDevice.getModel());
            stmt.setString(13, depDevice.getDescription());
            stmt.setString(14, depDevice.getColor());
            stmt.setInt(15, BooleanUtils.toInteger(depDevice.getAgentRequired()));
            int count = stmt.executeUpdate();
            if (count > 0) {
                status = true;
            }
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while adding the " +
                    "DEP device.", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, null);
        }
        return status;
    }

    public boolean updateDEPDevice(DEPDevice depDevice) throws
            MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        boolean status = false;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "UPDATE IOS_PRE_ENROLLED_DEVICE SET USERNAME = ?, " +
                    "TENANT_DOMAIN =?, STATUS =?, PROFILE_ASSIGN_TIME = ?, " +
                    "PROFILE_PUSH_TIME = ?, DEVICE_ASSIGNED_TIME = ?,DEVICE_ASSIGNED_BY = ?, " +
                    "NEED_BASIC_AUTH = ?, OS = ?, DEVICE_FAMILY = ?, DEVICE_MODEL = ?, " +
                    "DESCRIPTION = ?, COLOR = ?, DEVICE_IDENTIFIER = ?, IS_AGENT_REQUIRED = ?";
            if (depDevice.getDepProfileId() != null && depDevice.getDepProfileId() > 0) {
                sql = sql + ", DEP_PROFILE_ID = ? WHERE SERIAL = ?";
            } else {
                sql = sql + " WHERE SERIAL = ?";
            }

            stmt = conn.prepareStatement(sql);
            stmt.setString(1, depDevice.getUsername());
            stmt.setString(2, depDevice.getTenantDomain());
            stmt.setString(3, depDevice.getStatus());
            if (depDevice.getProfileAssignTime() != null && !depDevice.getProfileAssignTime().isEmpty()) {
                Instant instant = Instant.parse(depDevice.getProfileAssignTime());
                stmt.setTimestamp(4, Timestamp.from(instant));
            } else {
                stmt.setNull(4, java.sql.Types.DATE);
            }

            if (depDevice.getProfilePushTime() != null && !depDevice.getProfilePushTime().isEmpty()) {
                Instant instant = Instant.parse(depDevice.getProfilePushTime());
                stmt.setTimestamp(5, Timestamp.from(instant));
            } else {
                stmt.setNull(5, java.sql.Types.DATE);
            }

            if (depDevice.getDeviceAssignedTime() != null && !depDevice.getDeviceAssignedTime().isEmpty()) {
                Instant instant = Instant.parse(depDevice.getDeviceAssignedTime());
                stmt.setTimestamp(6, Timestamp.from(instant));
            } else {
                stmt.setNull(6, java.sql.Types.DATE);
            }
            stmt.setString(7, depDevice.getDeviceAssignedBy());
            stmt.setInt(8, BooleanUtils.toInteger(depDevice.getNeedBasicAuth()));
            stmt.setString(9, depDevice.getOS());
            stmt.setString(10, depDevice.getDeviceFamily());
            stmt.setString(11, depDevice.getModel());
            stmt.setString(12, depDevice.getDescription());
            stmt.setString(13, depDevice.getColor());
            stmt.setString(14, depDevice.getDeviceIdentifier());
            stmt.setInt(15, BooleanUtils.toInteger(depDevice.getAgentRequired()));

            int nextId = 16;
            if (depDevice.getDepProfileId() != null && depDevice.getDepProfileId() > 0) {
                stmt.setInt(nextId, depDevice.getDepProfileId());
                nextId++;
            }

            stmt.setString(nextId, depDevice.getSerial());
            int count = stmt.executeUpdate();
            if (count > 0) {
                status = true;
            }
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while adding the " +
                    "DEP device.", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, null);
        }
        return status;
    }

    public boolean removeDEPDevice(String id) throws
            MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        boolean status = false;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "DELETE FROM IOS_PRE_ENROLLED_DEVICE WHERE SERIAL = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, id);
            int count = stmt.executeUpdate();
            if (count > 0) {
                status = true;
            }
            return status;
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while removing the " +
                    "DEP device with id:" + id, e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, null);
        }
    }

    public DEPDevice getDEPDevice(String serial) throws
            MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        DEPDevice depDevice = null;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "SELECT ID, SERIAL, USERNAME, TENANT_DOMAIN, STATUS, DEP_PROFILE_ID, " +
                    "PROFILE_ASSIGN_TIME, PROFILE_PUSH_TIME, DEVICE_ASSIGNED_TIME," +
                    "DEVICE_ASSIGNED_BY, NEED_BASIC_AUTH, OS, DEVICE_FAMILY, DEVICE_MODEL, " +
                    "DEVICE_MODEL, DESCRIPTION, COLOR, DEVICE_IDENTIFIER, IS_AGENT_REQUIRED FROM " +
                    "IOS_PRE_ENROLLED_DEVICE WHERE SERIAL = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, serial);
            rs = stmt.executeQuery();
            while (rs.next()) {
                depDevice = new DEPDevice();
                depDevice.setId(rs.getString("ID"));
                depDevice.setSerial(rs.getString("SERIAL"));
                depDevice.setUsername(rs.getString("USERNAME"));
                depDevice.setTenantDomain(rs.getString("TENANT_DOMAIN"));
                depDevice.setStatus(rs.getString("STATUS"));
                depDevice.setDepProfileId(rs.getInt("DEP_PROFILE_ID"));
                depDevice.setProfileAssignTime(rs.getString("PROFILE_ASSIGN_TIME"));
                depDevice.setProfilePushTime(rs.getString("PROFILE_PUSH_TIME"));
                depDevice.setDeviceAssignedTime(rs.getString("DEVICE_ASSIGNED_TIME"));
                depDevice.setDeviceAssignedBy(rs.getString("DEVICE_ASSIGNED_BY"));
                depDevice.setNeedBasicAuth(BooleanUtils.toBoolean(rs.getInt("NEED_BASIC_AUTH")));
                depDevice.setOS(rs.getString("OS"));
                depDevice.setDeviceFamily(rs.getString("DEVICE_FAMILY"));
                depDevice.setModel(rs.getString("DEVICE_MODEL"));
                depDevice.setDescription(rs.getString("DESCRIPTION"));
                depDevice.setColor(rs.getString("COLOR"));
                depDevice.setAgentRequired(BooleanUtils.toBoolean((rs.getInt("IS_AGENT_REQUIRED"))));
                depDevice.setDeviceIdentifier(rs.getString("DEVICE_IDENTIFIER"));
            }
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while adding the " +
                    "DEP profile.", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, rs);
        }
        return depDevice;
    }

    public List<DEPDevice> getDEPDevices(PaginationRequest request) throws MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<DEPDevice> depDevices = new ArrayList<>();
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "SELECT D.ID, D.SERIAL, D.USERNAME, D.TENANT_DOMAIN, D.STATUS, D.DEP_PROFILE_ID, " +
                    "P.PROFILE_NAME, D.PROFILE_ASSIGN_TIME, D.PROFILE_PUSH_TIME, D.DEVICE_ASSIGNED_TIME," +
                    "D.DEVICE_ASSIGNED_BY, D.NEED_BASIC_AUTH, D.OS, D.DEVICE_FAMILY, D.DEVICE_MODEL, " +
                    "D.DEVICE_MODEL, D.DESCRIPTION, D.COLOR, D.DEVICE_IDENTIFIER, D.IS_AGENT_REQUIRED FROM " +
                    "IOS_PRE_ENROLLED_DEVICE D LEFT OUTER JOIN IOS_DEP_PROFILE P ON P.ID = D.DEP_PROFILE_ID " +
                    "WHERE D.TENANT_DOMAIN = ? ORDER BY D.ID LIMIT ?,?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantDomain());
            stmt.setInt(2, request.getStartIndex());
            stmt.setInt(3, request.getRowCount());
            rs = stmt.executeQuery();
            while (rs.next()) {
                DEPDevice depDevice = new DEPDevice();
                depDevice.setId(rs.getString("ID"));
                depDevice.setSerial(rs.getString("SERIAL"));
                depDevice.setUsername(rs.getString("USERNAME"));
                depDevice.setTenantDomain(rs.getString("TENANT_DOMAIN"));
                depDevice.setStatus(rs.getString("STATUS"));
                depDevice.setDepProfileId(rs.getInt("DEP_PROFILE_ID"));
                depDevice.setDepProfileName(rs.getString("PROFILE_NAME"));
                depDevice.setProfileAssignTime(rs.getString("PROFILE_ASSIGN_TIME"));
                depDevice.setProfilePushTime(rs.getString("PROFILE_PUSH_TIME"));
                depDevice.setDeviceAssignedTime(rs.getString("DEVICE_ASSIGNED_TIME"));
                depDevice.setDeviceAssignedBy(rs.getString("DEVICE_ASSIGNED_BY"));
                depDevice.setNeedBasicAuth(BooleanUtils.toBoolean(rs.getInt("NEED_BASIC_AUTH")));
                depDevice.setOS(rs.getString("OS"));
                depDevice.setDeviceFamily(rs.getString("DEVICE_FAMILY"));
                depDevice.setModel(rs.getString("DEVICE_MODEL"));
                depDevice.setDescription(rs.getString("DESCRIPTION"));
                depDevice.setColor(rs.getString("COLOR"));
                depDevice.setAgentRequired(BooleanUtils.toBoolean((rs.getInt("IS_AGENT_REQUIRED"))));
                depDevice.setDeviceIdentifier(rs.getString("DEVICE_IDENTIFIER"));
                depDevices.add(depDevice);
            }
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while fetching DEP devices.", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, rs);
        }
        return depDevices;
    }

    public int getDEPDevicesCount() throws MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int count = 0;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "SELECT COUNT(ID) AS DEVICE_COUNT FROM IOS_PRE_ENROLLED_DEVICE WHERE TENANT_DOMAIN = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantDomain());
            rs = stmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt("DEVICE_COUNT");
            }
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while adding the " +
                    "DEP profile.", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, rs);
        }
        return count;
    }

    public DEPDevice getDEPDeviceByDeviceId(String id) throws MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        DEPDevice depDevice = null;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql = "SELECT ID, SERIAL, USERNAME, TENANT_DOMAIN, STATUS, DEP_PROFILE_ID, " +
                    "PROFILE_ASSIGN_TIME, PROFILE_PUSH_TIME, DEVICE_ASSIGNED_TIME," +
                    "DEVICE_ASSIGNED_BY, NEED_BASIC_AUTH, OS, DEVICE_FAMILY, DEVICE_MODEL, " +
                    "DEVICE_MODEL, DESCRIPTION, COLOR, DEVICE_IDENTIFIER, IS_AGENT_REQUIRED FROM " +
                    "IOS_PRE_ENROLLED_DEVICE WHERE DEVICE_IDENTIFIER = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, id);
            rs = stmt.executeQuery();
            while (rs.next()) {
                depDevice = new DEPDevice();
                depDevice.setId(rs.getString("ID"));
                depDevice.setSerial(rs.getString("SERIAL"));
                depDevice.setUsername(rs.getString("USERNAME"));
                depDevice.setTenantDomain(rs.getString("TENANT_DOMAIN"));
                depDevice.setStatus(rs.getString("STATUS"));
                depDevice.setDepProfileId(rs.getInt("DEP_PROFILE_ID"));
                depDevice.setProfileAssignTime(rs.getString("PROFILE_ASSIGN_TIME"));
                depDevice.setProfilePushTime(rs.getString("PROFILE_PUSH_TIME"));
                depDevice.setDeviceAssignedTime(rs.getString("DEVICE_ASSIGNED_TIME"));
                depDevice.setDeviceAssignedBy(rs.getString("DEVICE_ASSIGNED_BY"));
                depDevice.setNeedBasicAuth(BooleanUtils.toBoolean(rs.getInt("NEED_BASIC_AUTH")));
                depDevice.setOS(rs.getString("OS"));
                depDevice.setDeviceFamily(rs.getString("DEVICE_FAMILY"));
                depDevice.setModel(rs.getString("DEVICE_MODEL"));
                depDevice.setDescription(rs.getString("DESCRIPTION"));
                depDevice.setColor(rs.getString("COLOR"));
                depDevice.setAgentRequired(BooleanUtils.toBoolean(rs.getInt("IS_AGENT_REQUIRED")));
                depDevice.setDeviceIdentifier(rs.getString("DEVICE_IDENTIFIER"));
            }
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error occurred while adding the DEP profile.", e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, rs);
        }
        return depDevice;
    }

    public void updateDeviceUser(String serial, String username, boolean requireBasicAuth, boolean requireAgentInstall)
            throws MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql =
                    "UPDATE IOS_PRE_ENROLLED_DEVICE SET USERNAME = ?, NEED_BASIC_AUTH = ?, " +
                            "IS_AGENT_REQUIRED = ? WHERE SERIAL = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, username);
            stmt.setInt(2, BooleanUtils.toInteger(requireBasicAuth));
            stmt.setInt(3, BooleanUtils.toInteger(requireAgentInstall));
            stmt.setString(4, serial);
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error while updating the username of " +
                    "device with serial: " + serial, e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, null);
        }
    }

    public void assignProfileToDevice(String profileId, String serial)
            throws MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql =
                    "UPDATE IOS_PRE_ENROLLED_DEVICE SET DEP_PROFILE_ID = ?, STATUS = ? WHERE " +
                            "SERIAL = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, profileId);
            stmt.setString(2, "assigned");
            stmt.setString(3, serial);
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error while updating the username of " +
                    "device with serial: " + serial, e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, null);
        }
    }

    public void removeProfileToDeviceAssignment(String serial)
            throws MobileDeviceManagementDAOException {
        PreparedStatement stmt = null;
        try {
            Connection conn = IOSDAOFactory.getConnection();
            String sql =
                    "UPDATE IOS_PRE_ENROLLED_DEVICE SET DEP_PROFILE_ID = ?, STATUS = ? WHERE SERIAL = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setNull(1, java.sql.Types.INTEGER);
            stmt.setString(2, STATUS_EMPTY);
            stmt.setString(3, serial);
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new MobileDeviceManagementDAOException("Error while updating the username of " +
                    "device with serial: " + serial, e);
        } finally {
            MobileDeviceManagementDAOUtil.cleanupResources(stmt, null);
        }
    }

}