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

import org.json.adf.JSONArray;
import org.json.adf.JSONObject;

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

import jp.agentec.abook.abv.bl.common.db.Cursor;
import jp.agentec.abook.abv.bl.data.tables.TTaskReportStatus;
import jp.agentec.abook.abv.bl.dto.DashboardStatusDto;
import jp.agentec.abook.abv.bl.dto.ReportStatusCountDto;
import jp.agentec.abook.abv.bl.dto.ReportStatusDto;
import jp.agentec.adf.util.DateTimeFormat;
import jp.agentec.adf.util.DateTimeUtil;

import static jp.agentec.abook.abv.bl.acms.client.json.OperationDataJSON.ReportStatusCount;
import static jp.agentec.abook.abv.bl.acms.client.json.OperationDataJSON.ReportStatusCountList;
import static jp.agentec.abook.abv.bl.acms.client.json.OperationDataJSON.ReportStatusId;

public class ReportStatusDao extends AbstractDao {

    public void insert(DashboardStatusDto dto) {
        StringBuilder sql = new StringBuilder();

        sql.append("INSERT INTO ").append(TTaskReportStatus.tableName).append(" (");
        sql.append("    task_key,");
        sql.append("    task_report_id,");
        sql.append("    report_start_date,");
        sql.append("    operation_id,");
        sql.append("    operation_name,");
        sql.append("    report_status,");
        sql.append("    untouched_flg,");
        sql.append("    working_flg,");
        sql.append("    complete_ok_flg,");
        sql.append("    complete_ng_flg,");
        sql.append("    incomplete_flg,");
        sql.append("    alert_flg,");
        sql.append("    send_backed_flg)");
        sql.append("  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
        insert(sql.toString(), dto.getInsertValues());
    }

    public void deleteAll() {
        execSql("DELETE FROM " + TTaskReportStatus.tableName);
    }

    private StringBuilder reportStatusSql() {
        StringBuilder sql = new StringBuilder();

        sql.append("SELECT");
        sql.append("    t_task_report_status.report_status AS report_status,");
        sql.append("    t_task_report_status.operation_id AS operation_id,");
        sql.append("    t_task_report_status.operation_name AS operation_name,");
        sql.append("    t_task_report.task_key AS task_key,");
        sql.append("    t_task.task_code AS task_code,");
        sql.append("    t_task_report.json_data AS json_data,");
        sql.append("    t_task_report.task_report_id AS task_report_id,");
        sql.append("    t_task_report.report_start_date AS report_start_date,");
        sql.append("    t_task_report.report_lock_user_id AS report_lock_user_id,");
        sql.append("    t_task_report.report_lock_user_name AS report_lock_user_name,");
        sql.append("    t_task_report.report_lock_time AS report_lock_time,");
        sql.append("    t_task_report.send_back_user_id AS send_back_user_id,");
        sql.append("    t_task_report.send_back_user_name AS send_back_user_name,");
        sql.append("    t_task_report.send_back_comment AS send_back_comment ");
        sql.append("    FROM t_task_report ");
        sql.append("    INNER JOIN t_task ");
        sql.append("        ON t_task_report.task_key = t_task.task_key ");
        sql.append("    INNER JOIN t_task_report_status");
        // 定期点検の場合
        sql.append("        ON (t_task_report.task_report_id = t_task_report_status.task_report_id");
        sql.append("        AND t_task_report.report_start_date = t_task_report_status.report_start_date)");
        // 報告の場合
        sql.append("        OR (t_task_report.task_key = t_task_report_status.task_key");
        sql.append("        AND t_task_report_status.task_report_id is null");
        sql.append("        AND t_task_report_status.report_start_date is null)");

        return sql;
    }

    public List<ReportStatusDto> getNotStartedReport() {
        StringBuilder sql = reportStatusSql();
        sql.append("WHERE t_task_report_status.untouched_flg <> 0");
        return rawQueryGetDtoList(sql.toString(), new String[] {}, ReportStatusDto.class);
    }

    public List<ReportStatusDto> getWorkingReport() {
        StringBuilder sql = reportStatusSql();
        sql.append("WHERE t_task_report_status.working_flg <> 0");
        return rawQueryGetDtoList(sql.toString(), new String[] {}, ReportStatusDto.class);
    }

    public List<ReportStatusDto> getCompleteOkReport() {
        StringBuilder sql = reportStatusSql();
        sql.append("WHERE t_task_report_status.complete_ok_flg <> 0");
        return rawQueryGetDtoList(sql.toString(), new String[] {}, ReportStatusDto.class);
    }

    public List<ReportStatusDto> getCompleteNgReport() {
        StringBuilder sql = reportStatusSql();
        sql.append("WHERE t_task_report_status.complete_ng_flg <> 0");
        return rawQueryGetDtoList(sql.toString(), new String[] {}, ReportStatusDto.class);
    }

    public List<ReportStatusDto> getIncompleteReport() {
        StringBuilder sql = reportStatusSql();
        sql.append("WHERE t_task_report_status.incomplete_flg <> 0");
        return rawQueryGetDtoList(sql.toString(), new String[] {}, ReportStatusDto.class);
    }

    public List<ReportStatusDto> getAlertReport() {
        StringBuilder sql = reportStatusSql();
        sql.append("WHERE t_task_report_status.alert_flg <> 0");
        return rawQueryGetDtoList(sql.toString(), new String[] {}, ReportStatusDto.class);
    }

    public List<ReportStatusDto> getSendBackedReport() {
        StringBuilder sql = reportStatusSql();
        sql.append("WHERE t_task_report_status.send_backed_flg <> 0");
        return rawQueryGetDtoList(sql.toString(), new String[] {}, ReportStatusDto.class);
    }

    public List<ReportStatusDto> getPendingReport() {
        StringBuilder sql = new StringBuilder();

        sql.append("SELECT");
        sql.append("    0 AS report_status,");
        sql.append("    t_operation.operation_id AS operation_id,");
        sql.append("    t_operation.operation_name AS operation_name,");
        sql.append("    t_task_report.task_key AS task_key,");
        sql.append("    t_task.task_code AS task_code,");
        sql.append("    t_task_report.json_data AS json_data,");
        sql.append("    t_task_report.task_report_id AS task_report_id,");
        sql.append("    t_task_report.report_start_date AS report_start_date,");
        sql.append("    t_task_report.report_lock_user_id AS report_lock_user_id,");
        sql.append("    t_task_report.report_lock_user_name AS report_lock_user_name,");
        sql.append("    t_task_report.report_lock_time AS report_lock_time,");
        sql.append("    t_task_report.send_back_user_id AS send_back_user_id,");
        sql.append("    t_task_report.send_back_user_name AS send_back_user_name,");
        sql.append("    t_task_report.send_back_comment AS send_back_comment ");
        sql.append("    FROM t_task_report ");
        sql.append("    INNER JOIN t_task ");
        sql.append("        ON t_task_report.task_key = t_task.task_key ");
        sql.append("    INNER JOIN t_operation ");
        sql.append("        ON t_task.operation_id = t_operation.operation_id ");
        sql.append("    WHERE t_task_report.local_saved_flg <> 0");
        return rawQueryGetDtoList(sql.toString(), new String[] {}, ReportStatusDto.class);
    }

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

        dto.reportStatus = intOrZero(cursor, "report_status");
        dto.operationId = longOrZero(cursor, "operation_id");
        dto.operationName = stringOrEmpty(cursor, "operation_name");
        dto.taskKey = stringOrEmpty(cursor, "task_key");
        dto.taskCode = stringOrNull(cursor, "task_code");
        String jsonData = stringOrEmpty(cursor, "json_data").trim();
        if (jsonData.startsWith("{") && jsonData.endsWith("}")) {
            dto.taskReportInfo = new JSONObject(jsonData);
        }
        dto.taskReportId = longOrNull(cursor, "task_report_id");
        dto.reportStartDate = dateOrNull(cursor, "report_start_date");
        dto.reportLockUserId = stringOrNull(cursor, "report_lock_user_id");
        dto.reportLockUserName = TaskReportDao.decode(stringOrNull(cursor, "report_lock_user_name"));
        dto.reportLockTime = dateOrNull(cursor, "report_lock_time");
        dto.sendBackUserId = stringOrNull(cursor, "send_back_user_id");
        dto.sendBackUserName = TaskReportDao.decode(stringOrNull(cursor, "send_back_user_name"));
        dto.sendBackComment = stringOrNull(cursor, "send_back_comment");

        return dto;
    }

    private String stringOrNull(Cursor cursor, String name) {
        int column = cursor.getColumnIndex(name);
        if (column < 0) {
            return null;
        } else {
            return cursor.getString(column);
        }
    }

    private String stringOrEmpty(Cursor cursor, String name) {
        int column = cursor.getColumnIndex(name);
        if (column < 0) {
            return "";
        } else {
            return cursor.getString(column);
        }
    }

    private Date dateOrNull(Cursor cursor, String name) {
        int column = cursor.getColumnIndex(name);
        if (column < 0) {
            return null;
        } else {
            String date = cursor.getString(column);
            return DateTimeUtil.toDate(date, "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
        }
    }

    private int intOrZero(Cursor cursor, String name) {
        int column = cursor.getColumnIndex(name);
        if (column < 0) {
            return 0;
        } else {
            return cursor.getInt(column);
        }
    }

    private long longOrZero(Cursor cursor, String name) {
        int column = cursor.getColumnIndex(name);
        if (column < 0) {
            return 0;
        } else {
            return cursor.getLong(column);
        }
    }

    private Long longOrNull(Cursor cursor, String name) {
        int column = cursor.getColumnIndex(name);
        if (column < 0) {
            return null;
        } else {
            return cursor.getLong(column);
        }
    }

    public List<ReportStatusCountDto> getReportStatusCountList() {
        List<ReportStatusCountDto> list = new ArrayList<ReportStatusCountDto>();

        /*
        0:未実施、1:作業中、
        2:作業完了(異常なし)、
        3:作業完了(異常あり)、
        4:期限切れ、5:アラート、
        6:差し戻し、7:一時保存
         */
        list.add(makeDto(0, getNotStartedReport().size()));
        list.add(makeDto(1, getWorkingReport().size()));
        list.add(makeDto(2, getCompleteOkReport().size()));
        list.add(makeDto(3, getCompleteNgReport().size()));
        list.add(makeDto(4, getIncompleteReport().size()));
        list.add(makeDto(5, getAlertReport().size()));
        list.add(makeDto(6, getSendBackedReport().size()));
        list.add(makeDto(7, getPendingReport().size()));

        return list;
    }

    private ReportStatusCountDto makeDto(int id, int count) {
        return new ReportStatusCountDto(String.valueOf(id), String.valueOf(count));
    }

    public JSONObject getReportStatusCountJson() {
        JSONArray list = new JSONArray();

        for (ReportStatusCountDto dto: getReportStatusCountList()) {
            JSONObject json = new JSONObject();
            json.put(ReportStatusId, dto.getReportStatusId());
            json.put(ReportStatusCount, dto.getReportStatusCount());
            list.put(json);
        }
        JSONObject result = new JSONObject();
        result.put(ReportStatusCountList, list);

        return result;
    }
}
