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

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

import jp.agentec.abook.abv.bl.acms.type.SearchDivisionType;
import jp.agentec.abook.abv.bl.common.db.Cursor;
import jp.agentec.abook.abv.bl.common.db.SQLiteDatabase;
import jp.agentec.abook.abv.bl.common.log.Logger;
import jp.agentec.abook.abv.bl.dto.GroupDto;
import jp.agentec.abook.abv.bl.dto.OperationGroupMasterDto;
import jp.agentec.adf.util.DateTimeFormat;
import jp.agentec.adf.util.DateTimeUtil;
import jp.agentec.adf.util.NumericUtil;
import jp.agentec.adf.util.StringUtil;

/**
 * Created by leej on 2019/06/25.
 */

public class OperationGroupMasterDao extends AbstractDao {
    private static final String TAG = "OperationGroupMasterDao";

    private enum QueryType {GetAllGroups, GetRootGroups, GetGroups}

    /*package*/ OperationGroupMasterDao() {
    }

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

        int column = cursor.getColumnIndex("operation_group_master_id");
        if (column != -1) {
            dto.operationGroupMasterId = cursor.getInt(column);
        }
        column = cursor.getColumnIndex("operation_group_master_name");
        if (column != -1) {
            dto.operationGroupMasterName = cursor.getString(column);
        }
        if (column != -1) {
            column = cursor.getColumnIndex("parent_operation_group_master_id");
            dto.parentOperationGroupMasterId = cursor.getInt(column);
        }
        column = cursor.getColumnIndex("operation_group_master_level");
        if (column != -1) {
            dto.operationGroupMasterLevel = cursor.getInt(column);
        }
        column = cursor.getColumnIndex("operation_count");
        if (column != -1) {
            dto.operationCount = cursor.getInt(column);
        }

        return dto;
    }

    /**
     * 作業種別をDB登録処理
     * @param dto
     */
    public void insertOperationGroupMaster(OperationGroupMasterDto dto) {
        StringBuffer sql = new StringBuffer();
        sql.append(" INSERT OR IGNORE INTO m_operation_group_master (operation_group_master_id, operation_group_master_name, parent_operation_group_master_id, operation_group_master_level) ");
        sql.append(" VALUES (?,?,?,?) ");
        try {
            beginTransaction();
            insert(sql.toString(), dto.getInsertValues());
            commit();
        } catch (Exception e) {
            rollback();
            Logger.e("insertOperationGroupMaster failed.", e);
            throw new RuntimeException(e);
        }
        Logger.v(TAG, "sql=%s", sql);
    }

    /**
     * 作業種別をDB更新処理
     * @param dto
     */
    public void updateOperationGroupMaster(OperationGroupMasterDto dto) {
        StringBuffer sql = new StringBuffer();
        sql.append(" UPDATE m_operation_group_master ");
        sql.append("    SET operation_group_master_name = ? ");
        sql.append("       ,parent_operation_group_master_id = ? ");
        sql.append("       ,operation_group_master_level = ? ");
        sql.append("  WHERE operation_group_master_id = ? ");
        try {
            beginTransaction();
            update(sql.toString(), dto.getUpdateValues());
            commit();
        } catch (Exception e) {
            rollback();
            Logger.e("insertGroup failed.", e);
            throw new RuntimeException(e);
        }
        Logger.v(TAG, "sql=%s", sql);
    }

    /**
     * 親階層のIDで子供の階層を取得
     * sort：作業種別名　昇順
     * @param parentId
     * @return
     */
    public List<OperationGroupMasterDto> getOperationGroupMasterChildList(int parentId) {
        List<OperationGroupMasterDto> list;
        list = rawQueryGetDtoList("select * from m_operation_group_master where parent_operation_group_master_id = ? order by operation_group_master_name ASC", new String[]{""+ parentId}, OperationGroupMasterDto.class);
        return list;
    }

    /**
     * 最上位階層を取得
     * @return
     */
    public OperationGroupMasterDto getTopOperationGroupMaster() {
        return rawQueryGetDto("select * from m_operation_group_master where operation_group_master_level = 0", null, OperationGroupMasterDto.class);
    }

    /**
     * 全作業種別を取得
     * @return
     */
    public List<OperationGroupMasterDto> getAllOperationGroupMaster() {
        return rawQueryGetDtoList("select * from m_operation_group_master", null, OperationGroupMasterDto.class);
    }

    /**
     * operationGroupMasterIdで作業種別を取得
     * @param operationGroupMasterId
     * @return
     */
    public OperationGroupMasterDto getOperationGroupMaster(Integer operationGroupMasterId) {
        return rawQueryGetDto("select * from m_operation_group_master where operation_group_master_id = ?", new String[]{"" + operationGroupMasterId}, OperationGroupMasterDto.class);
    }

    /**
     * operationGroupMasterIdのデータ存在チェック
     * @param operationGroupMasterId
     * @return
     */
    public boolean isExistOperationGroupMaster(Integer operationGroupMasterId) {
        return getOperationGroupMaster(operationGroupMasterId) != null;
    }

    /**
     * 作業種別に関連するテーブルを全て削除
     * @param dto
     */
    public void delete(OperationGroupMasterDto dto) {
        // 作業種別と作業のリレーションテーブル
        delete("r_operation_group_master_relation", "operation_group_master_id=?", dto.getKeyValues());
        // 作業種別のマスタテーブル
        delete("m_operation_group_master", "operation_group_master_id=?", dto.getKeyValues());
    }

    /**
     * 階層の作業種別情報取得(検索結果を元にデータ取得)
     * @param level
     * @return
     */
    public List<OperationGroupMasterDto> getOperationGroupMasterListBylevel(Integer level) {
        StringBuffer sql = new StringBuffer();
        sql.append("    SELECT M1.*, count(R1.operation_id) AS operation_count FROM m_operation_group_master AS M1");
        sql.append("        LEFT OUTER JOIN r_operation_group_master_relation R1");
        sql.append("        ON R1.operation_group_master_id = M1.operation_group_master_id");
        sql.append("        AND R1.operation_id IN(");
        sql.append("            SELECT operation_id FROM t_operation top");
        sql.append("                WHERE top.operation_id IS NOT NULL");

        sql.append("                )");
        sql.append("        WHERE M1.operation_group_master_level =? ");
        sql.append("        GROUP BY M1.operation_group_master_id");

        Logger.v(TAG, "sql=%s", sql);

        return rawQueryGetDtoList(sql.toString(), new String[]{""+ level}, OperationGroupMasterDto.class);
    }

    /**
     * 最後のOperationGroupMasterレベル取得
     * @return
     */
    public Integer getLastGroupLevel() {
        return rawQueryGetInt("SELECT MAX(operation_group_master_level) FROM m_operation_group_master", null);
    }
}