package com.infoepoch.pms.dispatchassistant.infractructure.basic.user;


import com.infoepoch.pms.dispatchassistant.common.exception.ValidationException;
import com.infoepoch.pms.dispatchassistant.common.utils.OracleUtils;
import com.infoepoch.pms.dispatchassistant.common.utils.StringUtils;
import com.infoepoch.pms.dispatchassistant.domain.basic.user.organizationRela.IUserOrganizationRelaRepository;
import com.infoepoch.pms.dispatchassistant.domain.basic.user.organizationRela.UserOrganizationRela;
import com.infoepoch.pms.dispatchassistant.domain.basic.user.organizationRela.UserOrganizationRelaCriteria;
import org.apache.commons.collections4.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

/**
 * 用户与组织关联表仓储实现
 */
@Repository
public class UserOrganizationRelaRepository implements IUserOrganizationRelaRepository {

    private static final Logger logger = LoggerFactory.getLogger(UserOrganizationRelaRepository.class);
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 新增
     */
    @Override
    public boolean insert(UserOrganizationRela entity) {
        String sql = "INSERT INTO BAS_USER_ORGANIZATION_RELA(UOR_ID, UOR_USER_ID, UOR_ORGANIZATION_ID, UOR_POSITION, UOR_MAJOR_POSITION_FLAG, UOR_RECORD_TIME) VALUES (?, ?, ?, ?, ?, ?)";
        int result;
        try {
            result = jdbcTemplate.update(sql, entity.getId(), entity.getUserId(), entity.getOrganizationId(), entity.getPosition(), entity.getMajorPositionFlag(), entity.getRecordTime());
        } catch (Exception e) {
            throw new ValidationException("新增 用户与组织关联表 失败。");
        }
        return result > 0;
    }

    /**
     * 更新
     */
    @Override
    public boolean update(UserOrganizationRela entity) {
        String sql = "UPDATE BAS_USER_ORGANIZATION_RELA SET UOR_USER_ID = ?, UOR_ORGANIZATION_ID = ?, UOR_POSITION = ?, UOR_MAJOR_POSITION_FLAG = ?, UOR_RECORD_TIME = ? WHERE UOR_ID = ?";
        int result;
        try {
            result = jdbcTemplate.update(sql, entity.getUserId(), entity.getOrganizationId(), entity.getPosition(), entity.getMajorPositionFlag(), entity.getRecordTime(), entity.getId());
        } catch (Exception e) {
            throw new ValidationException("更新 用户与组织关联表 失败。");
        }
        return result >= 0;
    }

    /**
     * 批量新增
     */
    public int[] batchInsert(List<UserOrganizationRela> list) {
        String sql = "INSERT INTO BAS_USER_ORGANIZATION_RELA(UOR_ID, UOR_USER_ID, UOR_ORGANIZATION_ID, UOR_POSITION, UOR_MAJOR_POSITION_FLAG, UOR_RECORD_TIME) VALUES (?, ?, ?, ?, ?, ?)";
        int[] result = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                int j = 0;
                UserOrganizationRela item = list.get(i);
                ps.setString(++j, item.getId());
                ps.setString(++j, item.getUserId());
                ps.setString(++j, item.getOrganizationId());
                ps.setString(++j, item.getPosition());
                ps.setBoolean(++j, item.getMajorPositionFlag());
                ps.setTimestamp(++j, item.getRecordTime() != null ? new Timestamp(item.getRecordTime().getTime()) : null);
            }

            @Override
            public int getBatchSize() {
                return list.size();
            }
        });
        return result;
    }

    /**
     * 批量更新
     */
    public int[] batchUpdate(List<UserOrganizationRela> list) {
        String sql = "UPDATE BAS_USER_ORGANIZATION_RELA SET UOR_USER_ID = ?, UOR_ORGANIZATION_ID = ?, UOR_POSITION = ?, UOR_MAJOR_POSITION_FLAG = ?, UOR_RECORD_TIME = ? WHERE UOR_ID = ? ";
        int[] result = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                int j = 0;
                UserOrganizationRela item = list.get(i);
                ps.setString(++j, item.getUserId());
                ps.setString(++j, item.getOrganizationId());
                ps.setString(++j, item.getPosition());
                ps.setBoolean(++j, item.getMajorPositionFlag());
                ps.setTimestamp(++j, item.getRecordTime() != null ? new Timestamp(item.getRecordTime().getTime()) : null);
                ps.setString(++j, item.getId());
            }

            @Override
            public int getBatchSize() {
                return list.size();
            }
        });
        return result;
    }

    /**
     * 删除
     */
    @Override
    public boolean delete(String id) {
        String sql = "DELETE FROM BAS_USER_ORGANIZATION_RELA WHERE UOR_ID = ?";
        int result;
        try {
            result = jdbcTemplate.update(sql, id);
        } catch (Exception e) {
            throw new ValidationException("删除 用户与组织关联表 失败。");
        }
        return result > 0;
    }

    @Override
    public void deleteByUserIdList(List<String> userIdList) {
        if (CollectionUtils.isEmpty(userIdList))
            return;
        StringBuffer sql = new StringBuffer("DELETE FROM BAS_USER_ORGANIZATION_RELA");
        Map<String, Object> andMap = new HashMap<>();
        andMap.put(OracleUtils.placeholderIn("UOR_USER_ID", userIdList), userIdList);
        List<Object> paramList = OracleUtils.combinationSql(sql, andMap);
        jdbcTemplate.update(sql.toString(), paramList.toArray());
    }

    /**
     * 根据Id查询
     */
    @Override
    public UserOrganizationRela selectById(String id) {
        String sql = "SELECT * FROM BAS_USER_ORGANIZATION_RELA WHERE UOR_ID = ? ";
        try {
            return jdbcTemplate.queryForObject(sql, new UserOrganizationRelaRowMapper(), id);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 根据查询条件查询单个对象
     */
    @Override
    public UserOrganizationRela selectOneByCriteria(UserOrganizationRelaCriteria criteria) {
        StringBuffer buffer = new StringBuffer("SELECT * FROM BAS_USER_ORGANIZATION_RELA ");
        List<Object> list = OracleUtils.combinationSql(buffer, createCriteriaSql(criteria), 1, 1);
        try {
            return jdbcTemplate.queryForObject(buffer.toString(), new UserOrganizationRelaRowMapper(), list.toArray());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 根据查询条件查询对象集合
     */
    @Override
    public List<UserOrganizationRela> selectByCriteria(UserOrganizationRelaCriteria criteria) {
        StringBuffer buffer = new StringBuffer("SELECT * FROM BAS_USER_ORGANIZATION_RELA ");
        List<Object> list = OracleUtils.combinationSql(buffer, createCriteriaSql(criteria));
        try {
            return jdbcTemplate.query(buffer.toString(), new UserOrganizationRelaRowMapper(), list.toArray());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 根据查询条件分页查询对象结合
     */
    @Override
    public List<UserOrganizationRela> selectCriteriaByPage(UserOrganizationRelaCriteria criteria, int pageIndex, int pageSize) {
        StringBuffer buffer = new StringBuffer("SELECT * FROM BAS_USER_ORGANIZATION_RELA ");
        List<Object> list = OracleUtils.combinationSql(buffer, createCriteriaSql(criteria), pageIndex, pageSize,"UOR_RECORD_TIME", true);
        try {
            return jdbcTemplate.query(buffer.toString(), new UserOrganizationRelaRowMapper(), list.toArray());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 根据条件查询对象总记录数
     */
    @Override
    public int selectCountByCriteria(UserOrganizationRelaCriteria criteria) {
        StringBuffer buffer = new StringBuffer("SELECT COUNT(*) FROM BAS_USER_ORGANIZATION_RELA ");
        List<Object> list = OracleUtils.combinationSql(buffer, createCriteriaSql(criteria));
        Integer count = jdbcTemplate.queryForObject(buffer.toString(), Integer.class, list.toArray());
        return count == null ? 0 : count;
    }

    /**
     * 根据员工编号查询
     * @param numberList
     * @return
     */
    @Override
    public List<UserOrganizationRela> selectByEmployeeNumberList(List<String> numberList){
        if (CollectionUtils.isEmpty(numberList))
            return new ArrayList<>();
        StringBuffer buffer = new StringBuffer("SELECT UO.*,U_JOB_NUMBER,U_FULLNAME FROM BAS_USER_ORGANIZATION_RELA UO LEFT JOIN BAS_USER U ON UO.UOR_USER_ID = U.U_ID ");
        Map<String,Object> map = new HashMap<>();
        map.put(OracleUtils.placeholderIn("U_JOB_NUMBER", numberList), numberList);
        List<Object> list = OracleUtils.combinationSql(buffer, map);
        try {
            return jdbcTemplate.query(buffer.toString(), new UserOrganizationRelaNewRowMapper(), list.toArray());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * RowMapper
     */
    static class UserOrganizationRelaRowMapper implements RowMapper<UserOrganizationRela> {
        @Override
        public UserOrganizationRela mapRow(ResultSet rs, int i) throws SQLException {
            return new UserOrganizationRela(
                    rs.getString("UOR_ID"),
                    rs.getString("UOR_USER_ID"),
                    rs.getString("UOR_ORGANIZATION_ID"),
                    rs.getString("UOR_POSITION"),
                    rs.getObject("UOR_MAJOR_POSITION_FLAG") != null ? rs.getBoolean("UOR_MAJOR_POSITION_FLAG") : null,
                    rs.getTimestamp("UOR_RECORD_TIME")
            );
        }
    }

    static class UserOrganizationRelaNewRowMapper implements RowMapper<UserOrganizationRela> {
        @Override
        public UserOrganizationRela mapRow(ResultSet rs, int i) throws SQLException {
            return new UserOrganizationRela(
                    rs.getString("UOR_ID"),
                    rs.getString("UOR_USER_ID"),
                    rs.getString("UOR_ORGANIZATION_ID"),
                    rs.getString("UOR_POSITION"),
                    rs.getObject("UOR_MAJOR_POSITION_FLAG") != null ? rs.getBoolean("UOR_MAJOR_POSITION_FLAG") : null,
                    rs.getTimestamp("UOR_RECORD_TIME"),
                    rs.getString("U_JOB_NUMBER"),
                    rs.getString("U_FULLNAME")
            );
        }
    }

    /**
     * 创建查询条件
     */
    private Map<String, Object> createCriteriaSql(UserOrganizationRelaCriteria criteria) {
        Map<String, Object> andMap = new LinkedHashMap<>();
        if (criteria == null)
            return andMap;
        //if (criteria.byId())
        //    andMap.put(criteria.getId() == null ? " T_Id IS NULL " : " T_Id = ? ", criteria.getId());

        //用户ID
        if (criteria.byUserId())
            andMap.put(criteria.getUserId() == null ? " UOR_USER_ID IS NULL " : " UOR_USER_ID = ? ", criteria.getUserId());
        //组织ID
        if (criteria.byOrganizationId())
            andMap.put(criteria.getOrganizationId() == null ? " UOR_ORGANIZATION_ID IS NULL " : " UOR_ORGANIZATION_ID = ? ", criteria.getOrganizationId());
        //用户岗位（LIKE）
        if (criteria.byPositionContain())
            andMap.put(" UOR_POSITION LIKE ? ", "%" + criteria.getPositionContain() + "%");
        //是否为主岗
        if (criteria.byMajorPositionFlag())
            andMap.put(criteria.getMajorPositionFlag() == null ? " UOR_MAJOR_POSITION_FLAG IS NULL " : " UOR_MAJOR_POSITION_FLAG = ? ", criteria.getMajorPositionFlag());
        // 账号（LIKE）
        if(StringUtils.isNotBlank(criteria.getUsername())) {
            andMap.put(" UOR_USER_ID IN (SELECT U_ID FROM BAS_USER WHERE U_USERNAME LIKE ? )", "%" + criteria.getUsername() + "%");
        }
        // 用户名称（LIKE）
        if(StringUtils.isNotBlank(criteria.getNameContain())) {
            andMap.put(" UOR_USER_ID IN (SELECT U_ID FROM BAS_USER WHERE U_FULLNAME LIKE ? )", "%" + criteria.getNameContain() + "%");
        }
        // 组织名称（LIKE）
        if(StringUtils.isNotBlank(criteria.getOrgNameContain())) {
            andMap.put(" UOR_ORGANIZATION_ID IN (SELECT BO_ID FROM BAS_ORGANIZATION WHERE BO_SHORT_NAME LIKE ? )", "%" + criteria.getOrgNameContain() + "%");
        }
        return andMap;
    }

}