package id.iptek.utms.agent.dao;

import id.iptek.utms.agent.db.DBConn;
import id.iptek.utms.agent.db.DBUtil;
import id.iptek.utms.agent.db.DatabaseException;
import id.iptek.utms.agent.model.Profile;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Jaka
 */
public class ProfileDao {

    public Profile getProfileForTerminal(String terminalSN) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Profile profile = null;
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select  "
                    + "	terminal.sn, "
                    + "	profile.id profile_id,profile.\"name\",profile.heartbeat_interval,profile.diagnostic_interval, "
                    + "	profile.mask_home_button,profile.mask_status_bar,profile.schedule_reboot,profile.schedule_reboot_time, "
                    + "	profile.is_default,profile.relocation_alert,profile.moving_threshold,profile.admin_password,profile.front_app "
                    + "from public.tms_terminal terminal "
                    + "	inner join public.tms_device_profile profile on terminal.profile_id=profile.id "
                    + "where lower(terminal.sn)=? and terminal.delete_ts is null;");
            pstmt.setString(1, terminalSN.toLowerCase());
            rs = pstmt.executeQuery();
            if (rs.next()) {
                profile = new Profile();
                profile.setTerminalSN(rs.getString(1));
                profile.setId(rs.getString(2));
                profile.setName(rs.getString(3));
                profile.setHeartbeatInterval(rs.getInt(4));
                profile.setDiagnosticInterval(rs.getInt(5));
                profile.setMaskHomeButton(rs.getBoolean(6));
                profile.setMaskStatusButton(rs.getBoolean(7));
                profile.setScheduleReboot(rs.getBoolean(8));
                profile.setScheduleRebootTime(rs.getTimestamp(9));
                profile.setDefault(rs.getBoolean(10));
                profile.setRelocationAlert(rs.getBoolean(11));
                profile.setMovingThreshold(rs.getInt(12));
                profile.setAdminPassword(rs.getString(13));
                profile.setFrontApp(rs.getString(14));
                
                profile.setApps(getProfileApps(conn, profile.getId()));
                profile.setGroupIds(getTerminalGroupIds(conn, terminalSN));
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return profile;
    }
    
    public List<String> getProfileApps(Connection conn, String profileId) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<String> apps = new ArrayList<>();
        try {
            pstmt = conn.prepareStatement(""
                    + "select  "
                    + "	app.package_name "
                    + "from public.tms_device_profile_app app "
                    + "where app.profile_id=?::uuid");
            pstmt.setString(1, profileId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String packageName = rs.getString(1);
                apps.add(packageName);
            }
        } finally {
            DBUtil.close(null, pstmt, rs);
        }
        return apps;
    }
    
    public List<String> getTerminalGroupIds(Connection conn, String terminalSN) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<String> apps = new ArrayList<>();
        try {
            pstmt = conn.prepareStatement(""
                    + "select ttg.id group_id  "
                    + "from tms_terminal_group ttg  "
                    + " inner join tms_terminal_group_link ttgl on ttgl.terminal_group_id = ttg.id "
                    + " inner join tms_terminal tt on ttgl.terminal_id = tt.id and tt.delete_ts is null "
                    + "where lower(tt.sn) = ?");
            pstmt.setString(1, terminalSN.toLowerCase());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String groupId = rs.getString(1);
                apps.add(groupId);
            }
        } finally {
            DBUtil.close(null, pstmt, rs);
        }
        return apps;
    }

}
