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.ApplicationSimple;
import id.iptek.utms.agent.model.CellInfo;
import id.iptek.utms.agent.model.Diagnostic;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

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

    public DiagnosticDao() {
    }

    public boolean save(Diagnostic diagnostic) throws DatabaseException {
        boolean saved = false;
        Connection conn = null;
        try {
            conn = DBConn.getInstance().getConnection();
            conn.setAutoCommit(false);

            // save heartbeat first
            saved |= saveDiagnostic(conn, diagnostic);
            if (saved) {
                saved |= updateTerminalLastDiagnostic(conn, diagnostic);
            }

            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException ex) {
            DBUtil.rollback(conn);
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn);
        }
        return saved;
    }

    public boolean saveAll(List<Diagnostic> diagnostics) throws DatabaseException {
        boolean saved = false;
        Connection conn = null;
        try {
            conn = DBConn.getInstance().getConnection();
            conn.setAutoCommit(false);

            // save heartbeat first
            saved |= saveDiagnostics(conn, diagnostics);
            if (saved) {
                saved |= updateAllTerminalLastDiagnostic(conn, diagnostics);
            }

            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException ex) {
            DBUtil.rollback(conn);
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn);
        }
        return saved;
    }

    private boolean saveDiagnostic(Connection conn, Diagnostic diagnostic) throws SQLException {
        boolean saved = false;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(""
                    + "insert into tms_diagnostic_info (id,sn,battery_temp,battery_percentage,latitude,longitude,"
                    + " meid,switching_times,swiping_card_times,dip_inserting_times,nfc_card_reading_times,  "
                    + " front_camera_open_times,rear_camera_open_times,charge_times,  "
                    + " total_memory,available_memory,total_flash_memory,available_flash_memory,total_mobile_data,  "
                    + " current_boot_time,total_boot_time,installed_apps_string,total_length_printed,  "
                    + " cell_name, cell_type, cell_strength, "
                    + " create_ts,created_by,version)  "
                    + "values (?::uuid,?,?,?,?,?,"
                    + " ?,?,?,?,?,  "
                    + " ?,?,?,  "
                    + " ?,?,?,?,?,  "
                    + " ?,?,?,?,  "
                    + " ?,?,?,  "
                    + " ?,?,1) ");
            int idx = 1;
            pstmt.setString(idx++, diagnostic.getId());
            pstmt.setString(idx++, diagnostic.getDeviceSn());
            pstmt.setDouble(idx++, diagnostic.getDiagnosticInfo().getBatteryTemp());
            pstmt.setDouble(idx++, diagnostic.getDiagnosticInfo().getBatteryPercentage());
            pstmt.setDouble(idx++, diagnostic.getLocationInfo().getLat());
            pstmt.setDouble(idx++, diagnostic.getLocationInfo().getLng());
            pstmt.setString(idx++, diagnostic.getDiagnosticInfo().getMeid());
            pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getSwitchingTimes());
            pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getSwipingCardTimes());
            pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getDipInsertingTimes());
            pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getNfcCardReadingTimes());
            pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getFrontCameraOpenTimes());
            pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getRearCameraOpenTimes());
            pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getChargeTimes());
            pstmt.setLong(idx++, getLong(diagnostic.getDiagnosticInfo().getTotalMemory()));
            pstmt.setLong(idx++, getLong(diagnostic.getDiagnosticInfo().getAvailableMemory()));
            pstmt.setLong(idx++, getLong(diagnostic.getDiagnosticInfo().getTotalFlashMemory()));
            pstmt.setLong(idx++, getLong(diagnostic.getDiagnosticInfo().getAvailableFlashMemory()));
            pstmt.setLong(idx++, getLong(diagnostic.getDiagnosticInfo().getTotalMobileData()));
            pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getCurrentBootTime());
            pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getTotalBootTime());
            pstmt.setString(idx++, diagnostic.getDiagnosticInfo().getInstalledAppString());
            pstmt.setDouble(idx++, diagnostic.getDiagnosticInfo().getTotalLengthPrinted());
            if(diagnostic.getCellInfo() != null && !diagnostic.getCellInfo().isEmpty()) {
                CellInfo cellInfo = diagnostic.getCellInfo().get(0);
                pstmt.setString(idx++, cellInfo.getName());
                pstmt.setString(idx++, cellInfo.getType());
                pstmt.setInt(idx++, cellInfo.getStrength());
            } else {
                pstmt.setNull(idx++, java.sql.Types.VARCHAR);
                pstmt.setNull(idx++, java.sql.Types.VARCHAR);
                pstmt.setNull(idx++, java.sql.Types.INTEGER);
            }
            pstmt.setTimestamp(idx++, new java.sql.Timestamp(diagnostic.getCreateTime().getTime()));
            pstmt.setString(idx++, "system");
            
            saved = pstmt.executeUpdate() == 1;
        } finally {
            DBUtil.close(pstmt);
        }
        return saved;
    }

    private boolean saveDiagnostics(Connection conn, List<Diagnostic> diagnostics) throws SQLException {
        boolean saved = false;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(""
                    + "insert into tms_diagnostic_info (id,sn,battery_temp,battery_percentage,latitude,longitude,"
                    + " meid,switching_times,swiping_card_times,dip_inserting_times,nfc_card_reading_times,  "
                    + " front_camera_open_times,rear_camera_open_times,charge_times,  "
                    + " total_memory,available_memory,total_flash_memory,available_flash_memory,total_mobile_data,  "
                    + " current_boot_time,total_boot_time,installed_apps_string,total_length_printed,  "
                    + " cell_name, cell_type, cell_strength, "
                    + " create_ts,created_by,version)  "
                    + "values (?::uuid,?,?,?,?,?,"
                    + " ?,?,?,?,?,  "
                    + " ?,?,?,  "
                    + " ?,?,?,?,?,  "
                    + " ?,?,?,?,  "
                    + " ?,?,?,  "
                    + " ?,?,1) ");
            for (Diagnostic diagnostic : diagnostics) {
                int idx = 1;
                pstmt.setString(idx++, diagnostic.getId());
                pstmt.setString(idx++, diagnostic.getDeviceSn());
                pstmt.setDouble(idx++, diagnostic.getDiagnosticInfo().getBatteryTemp());
                pstmt.setDouble(idx++, diagnostic.getDiagnosticInfo().getBatteryPercentage());
                pstmt.setDouble(idx++, diagnostic.getLocationInfo().getLat());
                pstmt.setDouble(idx++, diagnostic.getLocationInfo().getLng());
                pstmt.setString(idx++, diagnostic.getDiagnosticInfo().getMeid());
                pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getSwitchingTimes());
                pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getSwipingCardTimes());
                pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getDipInsertingTimes());
                pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getNfcCardReadingTimes());
                pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getFrontCameraOpenTimes());
                pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getRearCameraOpenTimes());
                pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getChargeTimes());
                pstmt.setLong(idx++, getLong(diagnostic.getDiagnosticInfo().getTotalMemory()));
                pstmt.setLong(idx++, getLong(diagnostic.getDiagnosticInfo().getAvailableMemory()));
                pstmt.setLong(idx++, getLong(diagnostic.getDiagnosticInfo().getTotalFlashMemory()));
                pstmt.setLong(idx++, getLong(diagnostic.getDiagnosticInfo().getAvailableFlashMemory()));
                pstmt.setLong(idx++, getLong(diagnostic.getDiagnosticInfo().getTotalMobileData()));
                pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getCurrentBootTime());
                pstmt.setInt(idx++, diagnostic.getDiagnosticInfo().getTotalBootTime());
                pstmt.setString(idx++, diagnostic.getDiagnosticInfo().getInstalledAppString());
                pstmt.setDouble(idx++, diagnostic.getDiagnosticInfo().getTotalLengthPrinted());
                if(diagnostic.getCellInfo() != null && !diagnostic.getCellInfo().isEmpty()) {
                    CellInfo cellInfo = diagnostic.getCellInfo().get(0);
                    pstmt.setString(idx++, cellInfo.getName());
                    pstmt.setString(idx++, cellInfo.getType());
                    pstmt.setInt(idx++, cellInfo.getStrength());
                } else {
                    pstmt.setNull(idx++, java.sql.Types.VARCHAR);
                    pstmt.setNull(idx++, java.sql.Types.VARCHAR);
                    pstmt.setNull(idx++, java.sql.Types.INTEGER);
                }
                pstmt.setTimestamp(idx++, new java.sql.Timestamp(diagnostic.getCreateTime().getTime()));
                pstmt.setString(idx++, "system");
                pstmt.addBatch();
                pstmt.clearParameters();
            }
            int[] xs = pstmt.executeBatch();
            int affectedRows = 0;
            for (int x : xs) {
                affectedRows += x;
            }
            saved = affectedRows == diagnostics.size();
        } finally {
            DBUtil.close(pstmt);
        }
        return saved;
    }
    
    private boolean updateTerminalLastDiagnostic(Connection conn, Diagnostic diagnostic) throws SQLException {
        boolean saved = false;
        PreparedStatement pstmt = null;
        try {
            String appVersion = null;
            String launcherVersion = null;
            String vfsVersion = null;
            String vfssVersion = null;
            for (ApplicationSimple app : diagnostic.getInstalledApps()) {
                if ("com.briit.brimobile".equals(app.getPackageName())) {
                    appVersion = app.getVersion();
                }
                if ("com.unified.launcher".equals(app.getPackageName())) {
                    launcherVersion = app.getVersion();
                }
                if ("com.vfi.smartpos.system_service".equals(app.getPackageName())) {
                    vfsVersion = app.getVersion();
                }
                if ("com.vfi.smartpos.deviceservice".equals(app.getPackageName())) {
                    vfssVersion = app.getVersion();
                }
            }

            pstmt = conn.prepareStatement(""
                    + "update tms_last_diagnostic_info set diagnostic_info_id=?::uuid,update_ts=now() where terminal_sn=?");
            pstmt.setString(1, diagnostic.getId());
            pstmt.setString(2, diagnostic.getDeviceSn());
            saved = pstmt.executeUpdate() == 1;
        } finally {
            DBUtil.close(pstmt);
        }
        return saved;
    }

    private boolean updateAllTerminalLastDiagnostic(Connection conn, List<Diagnostic> diagnostics) throws SQLException {
        boolean saved = false;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(""
                    + "update tms_last_diagnostic_info set diagnostic_info_id=?::uuid,update_ts=now() where terminal_sn=?");
            for (Diagnostic diagnostic : diagnostics) {
                String appVersion = null;
                String launcherVersion = null;
                String vfsVersion = null;
                String vfssVersion = null;
                for (ApplicationSimple app : diagnostic.getInstalledApps()) {
                    if ("com.briit.brimobile".equals(app.getPackageName())) {
                        appVersion = app.getVersion();
                    }
                    if ("com.unified.launcher".equals(app.getPackageName())) {
                        launcherVersion = app.getVersion();
                    }
                    if ("com.vfi.smartpos.system_service".equals(app.getPackageName())) {
                        vfsVersion = app.getVersion();
                    }
                    if ("com.vfi.smartpos.deviceservice".equals(app.getPackageName())) {
                        vfssVersion = app.getVersion();
                    }
                }

                pstmt.setString(1, diagnostic.getId());
                pstmt.setString(2, diagnostic.getDeviceSn());
                pstmt.addBatch();
                pstmt.clearParameters();
            }
            int[] xs = pstmt.executeBatch();
            int affectedRows = 0;
            for (int x : xs) {
                affectedRows += x;
            }
            saved = affectedRows > 0;
        } finally {
            DBUtil.close(pstmt);
        }
        return saved;
    }

    private long getLong(Object val) {
        if (val instanceof String) {
            String sVal = (String) val;
            if (sVal.contains("B")) {
                sVal = sVal.replace("B", "");
            }
            return Long.parseLong(sVal.trim());
        } else if (val instanceof Long) {
            return (Long) val;
        } else if (val instanceof Double) {
            return ((Double) val).longValue();
        }
        return 0;
    }
}
