package jp.agentec.abook.abv.bl.data.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;

import jp.agentec.abook.abv.bl.common.db.Cursor;
import jp.agentec.abook.abv.bl.common.log.Logger;
import jp.agentec.abook.abv.bl.common.util.Base64DecoderException;
import jp.agentec.abook.abv.bl.dto.TaskReportDto;
import jp.agentec.adf.util.DateTimeFormat;
import jp.agentec.adf.util.DateTimeUtil;

/**
 * Created by leej on 2018/09/10.
 */

public class TaskReportDao extends AbstractDao {

    private static final String TAG = "TaskReportDao";

    private static final int REPORT_LOCKING = 0;
    private static final int REPORT_UNLOCKED = 1;
    private static final int REPORT_PENDING = 2;

    /*package*/ TaskReportDao() {
    }

    @Override
    protected TaskReportDto convert(Cursor cursor) {
        TaskReportDto dto = new TaskReportDto();

        int column = cursor.getColumnIndex("task_key");
        if (column != -1) {
            dto.taskKey = cursor.getString(column);
        }
        column = cursor.getColumnIndex("json_data");
        if (column != -1) {
            dto.jsonData = cursor.getString(column);
        }
        column = cursor.getColumnIndex("attached_file_name");
        if (column != -1) {
            dto.attachedFileName = cursor.getString(column);
        }
        column = cursor.getColumnIndex("local_attached_file_name");
        if (column != -1) {
            dto.localAttachedFileName = cursor.getString(column);
        }
        column = cursor.getColumnIndex("attached_file_send_flg");
        if (column != -1) {
            dto.attachedFileSendFlg = toBool(cursor.getInt(column));
        }
        column = cursor.getColumnIndex("data_send_flg");
        if (column != -1) {
            dto.dataSendFlg = toBool(cursor.getInt(column));
        }
        // t_taskテーブルへINNER JOINで取得したカラム取得
        column = cursor.getColumnIndex("task_hotspot_info");
        if (column != -1) {
            dto.taskHotSpotInfo = cursor.getString(column);
        }
        column = cursor.getColumnIndex("del_flg");
        if (column != -1) {
            dto.delFlg = toBool(cursor.getInt(column));
        }

        column = cursor.getColumnIndex("enable_report");
        if (column != -1) {
            dto.enableReport = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("task_report_level");
        if (column != -1) {
            dto.taskReportLevel = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("task_report_id");
        if (column != -1) {
            dto.taskReportId = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("task_report_info_id");
        if (column != -1) {
            dto.taskReportInfoId = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("report_start_date");
        if (column != -1) {
            dto.reportStartDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
        }

        column = cursor.getColumnIndex("report_end_date");
        if (column != -1) {
            dto.reportEndDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
        }

        column = cursor.getColumnIndex("local_saved_flg");
        if (column != -1) {
            dto.localSavedFlg = toBool(cursor.getInt(column));
        }

        column = cursor.getColumnIndex("report_lock_user_id");
        if (column != -1) {
            dto.reportLockUserId = cursor.getString(column);
        }

        column = cursor.getColumnIndex("report_lock_user_name");
        if (column != -1) {
            dto.reportLockUserName = decode(cursor.getString(column));
        }

        column = cursor.getColumnIndex("report_lock_time");
        if (column != -1) {
            dto.reportLockTime = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
        }

        column = cursor.getColumnIndex("send_back_user_id");
        if (column != -1) {
            dto.sendBackUserId = cursor.getString(column);
        }

        column = cursor.getColumnIndex("send_back_user_name");
        if (column != -1) {
            dto.sendBackUserName = decode(cursor.getString(column));
        }

        column = cursor.getColumnIndex("send_back_comment");
        if (column != -1) {
            dto.sendBackComment = cursor.getString(column);
        }
        column = cursor.getColumnIndex("task_status");
        if (column != -1) {
            dto.taskStatus = cursor.getInt(column);
        }
        column = cursor.getColumnIndex("report_status");
        if (column != -1) {
            dto.reportStatus = cursor.getInt(column);
        }

        return dto;
    }

    /**
     * 報告登録処理
     * @param dto
     */
    public void insert(TaskReportDto dto) {
        insert("insert into t_task_report "
                        + "(task_key, "
                        + "json_data, "
                        + "attached_file_name, "
                        + "local_attached_file_name, "
                        + "attached_file_send_flg, "
                        + "data_send_flg, "
                        + "task_report_id, "
                        + "task_report_info_id, "
                        + "report_start_date, "
                        + "report_end_date, "
                        + "enable_report, "
                        + "task_report_level, "
                        + "report_lock_user_id, "
                        + "report_lock_user_name, "
                        + "report_lock_time, "
                        + "send_back_user_id, "
                        + "send_back_user_name, "
                        + "send_back_comment, "
                        + "task_status, "
                        + "report_status, "
                        + "local_saved_flg ) "
                        + "values "
                        + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                new Object[] {
                        dto.taskKey,
                        dto.jsonData,
                        dto.attachedFileName,
                        dto.localAttachedFileName,
                        dto.attachedFileSendFlg,
                        dto.dataSendFlg,
                        dto.taskReportId,
                        dto.taskReportInfoId,
                        dto.reportStartDate,
                        dto.reportEndDate,
                        dto.enableReport,
                        dto.taskReportLevel,
                        dto.reportLockUserId,
                        encode(dto.reportLockUserName),
                        dto.reportLockTime,
                        dto.sendBackUserId,
                        encode(dto.sendBackUserName),
                        dto.sendBackComment,
                        dto.taskStatus,
                        dto.reportStatus,
                        dto.localSavedFlg
        });
    }

    /**
     * 報告更新処理
     * @param dto
     * @return
     */
    public boolean update(TaskReportDto dto) {
        Object[] objects;
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE t_task_report SET ");
        sql.append("json_data=?, ");
        sql.append("attached_file_name=?, ");
        sql.append("local_attached_file_name=?, ");
        sql.append("attached_file_send_flg=?,  ");
        sql.append("data_send_flg=?,  ");
        sql.append("report_end_date=?, ");
        sql.append("enable_report=?, ");
        sql.append("task_report_level=?, ");
        sql.append("local_saved_flg=?, ");
        sql.append("report_lock_user_id=?, ");
        sql.append("report_lock_user_name=?, ");
        sql.append("report_lock_time=?, ");
        sql.append("send_back_user_id=?, ");
        sql.append("send_back_user_name=?, ");
        sql.append("send_back_comment=?, ");
        sql.append("task_status=?, ");
        sql.append("report_status=?, ");
        sql.append("task_report_info_id=? ");
        sql.append("WHERE task_key=? AND task_report_level=?");
        if (dto.reportStartDate != null) {
            sql.append(" AND datetime(report_start_date)=datetime(?)");
            objects = new Object[]{
                    dto.jsonData,
                    dto.attachedFileName,
                    dto.localAttachedFileName,
                    dto.attachedFileSendFlg,
                    dto.dataSendFlg,
                    dto.reportEndDate,
                    dto.enableReport,
                    dto.taskReportLevel,
                    dto.localSavedFlg,
                    dto.reportLockUserId,
                    encode(dto.reportLockUserName),
                    dto.reportLockTime,
                    dto.sendBackUserId,
                    encode(dto.sendBackUserName),
                    dto.sendBackComment,
                    dto.taskStatus,
                    dto.reportStatus,
                    dto.taskReportInfoId,
                    dto.taskKey,
                    dto.taskReportLevel,
                    dto.reportStartDate
            };
        } else {
            objects = new Object[]{
                    dto.jsonData,
                    dto.attachedFileName,
                    dto.localAttachedFileName,
                    dto.attachedFileSendFlg,
                    dto.dataSendFlg,
                    dto.reportEndDate,
                    dto.enableReport,
                    dto.taskReportLevel,
                    dto.localSavedFlg,
                    dto.reportLockUserId,
                    encode(dto.reportLockUserName),
                    dto.reportLockTime,
                    dto.sendBackUserId,
                    encode(dto.sendBackUserName),
                    dto.sendBackComment,
                    dto.taskStatus,
                    dto.reportStatus,
                    dto.taskReportInfoId,
                    dto.taskKey,
                    dto.taskReportLevel
            };
        }

        return update(sql.toString(), objects) > 0;
    }

    /**
     * ロック、アンロック時の状態変更
     *
     * @param taskKey
     * @param reportStartDate
     * @param reportStatus
     * @param reportLockUserId
     * @param reportLockUserName
     * @param reportLockTime
     * @return
     */
    public void updateReportLock(
            String taskKey,
            Long taskReportId,
            Date reportStartDate,
            int reportStatus,
            String reportLockUserId,
            String reportLockUserName,
            Date reportLockTime
    ) {
        StringBuilder sql = new StringBuilder();
        List<Object> args = new ArrayList<Object>();

        sql.append("UPDATE t_task_report SET ");
        sql.append("    report_status=?, ");
        sql.append("    report_lock_user_id=?, ");
        sql.append("    report_lock_user_name=?, ");
        sql.append("    report_lock_time=? ");
        if (reportStartDate != null) {
            sql.append(" WHERE task_report_id=? ");
            sql.append(" AND datetime(report_start_date)=datetime(?) ");
        } else {
            sql.append(" WHERE task_key=? ");
        }
        args.add(reportStatus);
        args.add(reportLockUserId);
        args.add(encode(reportLockUserName));
        args.add(reportLockTime);
        if (reportStartDate != null) {
            args.add(taskReportId);
            int offset = TimeZone.getDefault().getOffset(reportStartDate.getTime());
            Date date = new Date(reportStartDate.getTime() - offset);
            String dateStr = DateTimeUtil.toStringInTimeZone(date, DateTimeFormat.yyyyMMddHHmmss_hyphen, "UTC");
            args.add(dateStr);
        } else {
            args.add(taskKey);
        }

        long count = update(sql.toString(), args.toArray());
        Logger.i(TAG, "updated " + count + "record(s) by LOCK/UNLOCK");
    }

    /**
     * 作業IDで報告取得
     * （作業報告タイプ　昇順、作業報告ID 昇順、報告開始日　昇順）
     * @param operationId
     * @return
     */
    public List<TaskReportDto> getTaskReportByOperationId(Long operationId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ttr.* ");
        sb.append(" from t_task_report ttr ");
        sb.append(" inner join ");
        sb.append("     t_task tt ");
        sb.append("         ON tt.task_key == ttr.task_key ");
        sb.append("         WHERE tt.del_flg = 0");
        sb.append("         AND tt.operation_id = ?");
        sb.append("         ORDER BY task_report_level ASC, task_report_id ASC, report_start_date ASC");
        return rawQueryGetDtoList(sb.toString(), new String[]{"" + operationId}, TaskReportDto.class);
    }

    /**
     * 作業キーと作業報告タイプで作業報告データ取得
     * @param taskKey
     * @param taskReportLevel
     * @return
     */
    public TaskReportDto getTaskReport(String taskKey, Integer taskReportLevel) {
        return rawQueryGetDto("select * from t_task_report where task_key=? AND task_report_level=?", new String[]{ "" + taskKey, "" + taskReportLevel }, TaskReportDto.class);
    }

    /**
     * 作業キーで報告リストを取得
     * @param taskKey
     * @return
     */
    public List<TaskReportDto> getTaskReportListByTaskKey(String taskKey) {
        return rawQueryGetDtoList("select * from t_task_report where task_key=? ORDER BY task_report_id, report_start_date", new String[]{ "" + taskKey }, TaskReportDto.class);
    }

    /**
     * 送信フラグのあるデータが存在するか
     *
     * @param operationId
     * @return
     */
    public boolean isExistSendTaskData(Long operationId) {
        return getSendableTaskData(operationId).size() > 0;
    }

    public List<TaskReportDto> getSendableTaskData(Long operationId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select * ");
        sb.append(" from t_task_report ttr ");
        sb.append(" inner join ");
        sb.append("     t_task tt ");
        sb.append("         ON tt.task_key == ttr.task_key ");
        sb.append("         WHERE ttr.data_send_flg = 1");
        sb.append("         AND tt.operation_id = ?");
        return rawQueryGetDtoList(sb.toString(), new String[]{"" + operationId}, TaskReportDto.class);
    }

    /**
     * ホットスポットが更新フラグがtrueのデータ取得
     * @param operationId
     * @param taskKey
     * @return
     */
    public List<TaskReportDto> getUpdateTargetHotSpotTaskData(Long operationId, String taskKey) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select * ");
        sb.append(" from t_task_report ttr ");
        sb.append(" inner join ");
        sb.append("     t_task tt ");
        sb.append("         ON tt.task_key == ttr.task_key ");
        sb.append("         WHERE tt.hotspot_changed_flg = 1");
        sb.append("         AND tt.operation_id = ?");
        sb.append("         AND ttr.task_report_level = 0");
        if (taskKey != null) {
            sb.append("         AND ttr.task_key = ?");
        }
        return rawQueryGetDtoList(sb.toString(), new String[]{"" + operationId}, TaskReportDto.class);
    }

    /**
     * ホットスポットが更新フラグがtrueのデータ存在チェック
     * @param operationId
     * @return
     */
    public boolean isExistUpdateTargetHotSpotTaskData(Long operationId) {
        return getUpdateTargetHotSpotTaskData(operationId, null).size() > 0;
    }


    /**
     * 送信フラグがtrueのデータを取得
     * @param operationId
     * @param taskKey
     * @param taskReportLevel
     * @return
     */
    public List<TaskReportDto> getSendableTaskData(Long operationId, String taskKey, int taskReportLevel) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select * ");
        sb.append(" from t_task_report ttr ");
        sb.append(" inner join ");
        sb.append("     t_task tt ");
        sb.append("         ON tt.task_key == ttr.task_key ");
        sb.append("         WHERE ttr.data_send_flg = 1");
        sb.append("         AND tt.operation_id = ?");
        sb.append("         AND tt.task_key = ?");
        sb.append("         AND ttr.task_report_level = ?");
        return rawQueryGetDtoList(sb.toString(), new String[]{"" + operationId, "" + taskKey, "" + taskReportLevel}, TaskReportDto.class);
    }

    /**
     *
     * taskReportLevel によって添付ファイル名を取得する
     * @param taskKey
     * @param taskReportLevel
     * @return
     */
    public String getTaskReportAttachedFileName(String taskKey, int taskReportLevel) {
        return rawQueryGetString("select local_attached_file_name from t_task_report where task_key=? AND task_report_level=?", new String[]{"" + taskKey, "" + taskReportLevel});
    }

    /**
     * 報告キー、報告担当レベルでt_task_Reportデータ取得
     * @param taskKey
     * @param taskReportLevel
     * @return
     */
    public TaskReportDto selectByTaskKey(String taskKey, int taskReportLevel) {
        return rawQueryGetDto("select * from t_task_report where task_key=? and task_report_level=?", new String[]{ "" + taskKey, "" + taskReportLevel }, TaskReportDto.class);
    }

    public List<TaskReportDto> selectAll() {
        return rawQueryGetDtoList("select * from t_task_report", null, TaskReportDto.class);
    }

    /**
     * データの存在チェック
     * @param taskKey
     * @param taskReportLevel
     * @return
     */
    public boolean isExistTaskReport(String taskKey, Integer taskReportLevel) {
        return rawQueryGetInt("select COUNT(*) from t_task_report where task_key=? AND task_report_level=?", new String[]{"" + taskKey, "" + taskReportLevel }) > 0;
    }

    /**
     * テーブル物理削除
     * @param dto
     */
    public void delete(TaskReportDto dto) {
        delete("t_task_report", "task_key=? and task_report_level=?", new String[] { dto.taskKey, "" + dto.taskReportLevel });
    }

    /**
     * テーブル物理削除（定期点検専用）
     * @param dto
     */
    public void deleteRoutineTaskReport(TaskReportDto dto) {
        delete("t_task_report", "task_key=? AND task_report_level=? AND task_report_id=? AND datetime(report_start_date)=datetime(?, 'utc')", new String[] { dto.taskKey, "" + dto.taskReportLevel, "" + dto.taskReportId, DateTimeUtil.toString(dto.reportStartDate, DateTimeFormat.yyyyMMddHHmmss_hyphen) });
    }

    /**
     * 定期点検報告データで最後の時間データ取得
     * @param operationId
     * @return
     */
    public TaskReportDto getLastRoutineTaskReportData(long operationId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select tr.* ");
        sb.append(" from t_task_report tr ");
        sb.append(" inner join ");
        sb.append("     t_task tt ");
        sb.append("         ON tr.task_key == tt.task_key");
        sb.append("     WHERE tt.operation_id = ? ORDER BY tr.report_start_date DESC LIMIT 1");
        List<TaskReportDto> dtoList = rawQueryGetDtoList(sb.toString(), new String[]{ "" + operationId }, TaskReportDto.class);

        if (dtoList != null && dtoList.size() > 0) {
            return dtoList.get(0);
        } else {
            TaskReportDto dto = new TaskReportDto();
            dto.taskReportId = 0;
            return dto;
        }
    }

    /**
     * 定期点検の報告データ取得（時間はutcで検索）
     * @param taskKey
     * @param taskReportId
     * @param reportStartDate
     * @return
     */
    public TaskReportDto getRoutineTaskReportUtc(String taskKey, int taskReportId, String reportStartDate){
        return rawQueryGetDto("select * from t_task_report where task_key=? and task_report_id=? and datetime(report_start_date)=datetime(?, 'utc')", new String[]{ taskKey, "" + taskReportId, reportStartDate }, TaskReportDto.class);
    }

    /**
     * 定期点検のSQL専用のため、taskReportId, report_start_dateが存在するデータの条件追加
     * @param operationId
     * @return
     */
    public boolean existsToDoRoutineTaskReportData(long operationId) {
        String currDate = DateTimeUtil.toString(DateTimeUtil.getCurrentSqlDate(), DateTimeFormat.yyyyMMddHHmmss_hyphen);
        StringBuilder sb = new StringBuilder();
        sb.append(" select count(*) ");
        sb.append(" from t_task_report tr ");
        sb.append("     inner join t_task tt ON tt.task_key == tr.task_key ");
        sb.append("     WHERE tt.del_flg = 0 ");
        sb.append("         AND tt.operation_id = ? ");
        // 定期点検のみ
        sb.append("         AND tr.task_report_id != 0");
        sb.append("         AND tr.report_start_date IS NOT NULL");
        sb.append("         AND datetime(report_end_date) >= datetime(?, 'utc') ");
        return rawQueryGetInt(sb.toString(), new String[]{ "" + operationId, currDate }) > 0;
    }

    /**
     * 定期点検報告の添付zipファイル名を取得
     * @param taskKey
     * @param taskReportId
     * @param reportStartDate
     * @return
     */
    public String getRoutineTaskReportAttachedFileName(String taskKey, int taskReportId, String reportStartDate) {
        return rawQueryGetString("select local_attached_file_name from t_task_report where task_key=? and task_report_id=? and datetime(report_start_date)=datetime(?, 'utc')", new String[]{ taskKey, "" + taskReportId, reportStartDate });
    }

    public boolean isLocalSaved(String taskKey, long taskReportId, String reportStartDate) {
        int count;
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT count(*) FROM t_task_report");
        sql.append("  WHERE local_saved_flg > 0");
        if (reportStartDate == null) {
            // 報告
            sql.append("  AND task_key=?");
            count = rawQueryGetInt(sql.toString(), new String[] { taskKey });
        } else {
            // 点検
            sql.append("  AND task_report_id=?");
            sql.append("  AND datetime(report_start_date)=datetime(?, 'utc')");
            count = rawQueryGetInt(sql.toString(), new String[] { String.valueOf(taskReportId), reportStartDate });
        }
        return count > 0;
    }

    public boolean isCompleted(String taskKey, int taskReportId, String reportStartDate) {
        int count;
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT count(*) FROM t_task_report");
        sql.append("  WHERE task_status = 999 ");
        if (reportStartDate == null) {
            // 報告
            sql.append("  AND task_key=?");
            count = rawQueryGetInt(sql.toString(), new String[] { taskKey });
        } else {
            // 点検
            sql.append("  AND task_report_id=?");
            sql.append("  AND datetime(report_start_date)=datetime(?, 'utc')");
            count = rawQueryGetInt(sql.toString(), new String[] { String.valueOf(taskReportId), reportStartDate });
        }
        return count > 0;
    }

    public boolean isWorking(String taskKey, int taskReportId, String reportStartDate) {
        int count;
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT count(*) FROM t_task_report");
        sql.append("  WHERE task_status = 1 ");
        if (reportStartDate == null) {
            // 報告
            sql.append("  AND task_key=?");
            count = rawQueryGetInt(sql.toString(), new String[] { taskKey });
        } else {
            // 点検
            sql.append("  AND task_report_id=?");
            sql.append("  AND datetime(report_start_date)=datetime(?, 'utc')");
            count = rawQueryGetInt(sql.toString(), new String[] { String.valueOf(taskReportId), reportStartDate });
        }
        return count > 0;
    }

    public static String encode(String plain) {
        if (plain == null) {
            return null;
        }
        return jp.agentec.abook.abv.bl.common.util.Base64.encode(plain.getBytes());
    }

    public static String decode(String encoded) {
        if (encoded == null) {
            return null;
        }
        try {
            return new String(jp.agentec.abook.abv.bl.common.util.Base64.decode(encoded));
        } catch (Base64DecoderException e) {
            Logger.e(TAG, e);
            return null;
        }
    }
}
