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

import com.infoepoch.pms.dispatchassistant.common.encryption.AESTool;
import com.infoepoch.pms.dispatchassistant.common.enums.EnumUtils;
import com.infoepoch.pms.dispatchassistant.common.utils.DatabaseUtils;
import com.infoepoch.pms.dispatchassistant.common.utils.OracleUtils;
import com.infoepoch.pms.dispatchassistant.domain.basic.user.IUserRepository;
import com.infoepoch.pms.dispatchassistant.domain.basic.user.User;
import com.infoepoch.pms.dispatchassistant.domain.basic.user.UserCriteria;
import com.infoepoch.pms.dispatchassistant.domain.basic.user.UserType;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 用户Repository
 */
@Repository
public class UserRepository implements IUserRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public User selectById(String id) {
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet("SELECT * FROM BAS_USER WHERE U_ID = ?", id);
        if (sqlRowSet.next())
            return convert(sqlRowSet);
        return null;
    }

    @Override
    public List<User> selectByIds(List<String> ids) {
        if (ids == null || ids.size() <= 0) {
            return null;
        }
        String baseSql = "SELECT * FROM BAS_USER WHERE ";
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder(baseSql);

        for (int i = 0; i < ids.size(); i++) {
            if (i == 0) {
                sql.append(" U_Id = ?");
            } else {
                sql.append(" OR U_Id = ?");
            }
        }
        paramList.addAll(ids);

        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql.toString(), paramList.toArray());
        List<User> userList = new ArrayList<>();
        while (sqlRowSet.next())
            userList.add(convert(sqlRowSet));
        return userList;
    }

    @Override
    public User selectByUsername(String username) {
        if (username != null) username = username.toLowerCase();
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet("SELECT * FROM BAS_USER WHERE LOWER(U_USERNAME) = ?", username);
        if (sqlRowSet.next())
            return convert(sqlRowSet);
        return null;
    }

    @Override
    public String selectPassword(String id) {
        String password = null;
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet("SELECT U_PASSWORD FROM BAS_USER WHERE U_ID = ?", id);
        if (sqlRowSet.next()) {
            password = sqlRowSet.getString("U_PASSWORD");
        }
        return password == null ? "" : password;
    }

    @Override
    public List<User> selectAll() {
        jdbcTemplate.setFetchSize(496);
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet("SELECT * FROM BAS_USER");
        List<User> userList = new ArrayList<>();
        while (sqlRowSet.next())
            userList.add(convert(sqlRowSet));
        return userList;
    }

    @Override
    public User selectOneByJobNumber(String jobNumber) {
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet("SELECT * FROM (SELECT * FROM BAS_USER WHERE U_JOB_NUMBER = ?) TEMP WHERE ROWNUM = 1", jobNumber);
        if (sqlRowSet.next()) {
            return convert(sqlRowSet);
        }
        return null;
    }

    @Override
    public List<User> selectByJobNumber(String jobNumber) {
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet("SELECT * FROM BAS_USER WHERE U_JOB_NUMBER = ?", jobNumber);
        List<User> userList = new ArrayList<>();
        while (sqlRowSet.next()) {
            userList.add(convert(sqlRowSet));
        }
        return userList;
    }

    @Override
    public User selectUserByJobNumber(String jobNumber, Boolean disabledFlag) {
        StringBuilder sql = new StringBuilder("SELECT * FROM BAS_USER WHERE U_JOB_NUMBER = ? ");
        List<Object> paramList = new ArrayList<>();
        paramList.add(jobNumber);
        if (disabledFlag != null) {
            sql.append(" AND U_DISABLED = ? ");
            paramList.add(disabledFlag);
        }
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql.toString(), paramList.toArray());
        if (sqlRowSet.next()) {
            return convert(sqlRowSet);
        }
        return null;
    }

    @Override
    public List<User> selectByCriteria(UserCriteria criteria) {
        StringBuffer sql = new StringBuffer("SELECT * FROM BAS_USER");
        List<Object> paramList = OracleUtils.combinationSql(sql, transferCriteria(criteria), "U_DISPLAY_ORDER", false);
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql.toString(), paramList.toArray());
        List<User> userList = new ArrayList<>();
        while (sqlRowSet.next())
            userList.add(convert(sqlRowSet));
        return userList;
    }

    @Override
    public List<User> selectByCriteriaPage(UserCriteria criteria, int pageIndex, int pageSize) {
        StringBuffer sql = new StringBuffer("SELECT * FROM BAS_USER");
        List<Object> paramList = OracleUtils.combinationSql(sql, transferCriteria(criteria), pageIndex, pageSize, "U_DISPLAY_ORDER", false);
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql.toString(), paramList.toArray());
        List<User> userList = new ArrayList<>();
        while (sqlRowSet.next())
            userList.add(convert(sqlRowSet));
        return userList;
    }

    @Override
    public int selectByCriteriaCount(UserCriteria criteria) {
        StringBuffer sql = new StringBuffer("SELECT COUNT(1) FROM BAS_USER");
        List<Object> paramList = OracleUtils.combinationSql(sql, transferCriteria(criteria));
        return jdbcTemplate.queryForObject(sql.toString(), paramList.toArray(), int.class);
    }

    @Override
    public void insert(User user) {
        String sql = "INSERT INTO BAS_USER(U_ID,U_ORGANIZATION_ID,U_TYPE,U_POSITION,U_DISPLAY_ORDER,U_PERMISSIONS,U_USERNAME," +
                "U_FULLNAME,U_SEX,U_MOBILE,U_EMAIL,U_DISABLED,U_DESCRIPTION,U_OA_DATA,U_HR_DATA,U_JOB_NUMBER,U_ORG_CONFIRM_STATUS)" +
                " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,
                user.getId(),
                user.getOrganizationId(),
                user.getType().getValue(),
                user.getPosition(),
                user.getDisplayOrder(),
                user.getPermissions() == null ? null : StringUtils.join(user.getPermissions(), ","),
                user.getUsername(),
                user.getFullname(),
                user.getSex(),
                user.getMobile(),
                user.getEmail(),
                user.isDisabled(),
                user.getDescription(),
                user.getOaData(),
                user.getHrData(),
                user.getJobNumber(),
                user.getOrgConfirmStatus()
        );
    }

    @Override
    public void batchInsert(List<User> userList) {
        String sql = "INSERT INTO BAS_USER(U_ID,U_ORGANIZATION_ID,U_TYPE,U_POSITION,U_DISPLAY_ORDER,U_PERMISSIONS,U_USERNAME," +
                "U_FULLNAME,U_SEX,U_MOBILE,U_EMAIL,U_DISABLED,U_DESCRIPTION,U_OA_DATA,U_HR_DATA,U_JOB_NUMBER,U_ORG_CONFIRM_STATUS)" +
                " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                User user = userList.get(i);
                int j = 0;
                ps.setString(++j, user.getId());
                ps.setString(++j, user.getOrganizationId());
                ps.setInt(++j, user.getType().getValue());
                ps.setString(++j, user.getPosition());
                ps.setString(++j, user.getDisplayOrder());
                ps.setString(++j, user.getPermissions() == null ? null : StringUtils.join(user.getPermissions(), ","));
                ps.setString(++j, user.getUsername());
                ps.setString(++j, user.getFullname());
                ps.setString(++j, user.getSex());
                ps.setString(++j, user.getMobile());
                ps.setString(++j, user.getEmail());
                ps.setBoolean(++j, user.isDisabled());
                ps.setString(++j, user.getDescription());
                ps.setString(++j, user.getOaData());
                ps.setString(++j, user.getHrData());
                ps.setString(++j, user.getJobNumber());
                ps.setObject(++j, user.getOrgConfirmStatus());
            }

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

    @Override
    public void update(User user) {
        String sql = "UPDATE BAS_USER SET U_ORGANIZATION_ID = ?,U_TYPE = ?,U_POSITION = ?,U_DISPLAY_ORDER = ?,U_PERMISSIONS = ?," +
                "U_USERNAME = ?,U_FULLNAME = ?,U_SEX = ?,U_MOBILE = ?,U_EMAIL = ?,U_DISABLED = ?,U_DESCRIPTION = ?,U_OA_DATA = ?," +
                "U_HR_DATA = ?, U_JOB_NUMBER = ?, U_ORG_CONFIRM_STATUS = ? WHERE U_ID = ?";
        jdbcTemplate.update(sql,
                user.getOrganizationId(),
                user.getType().getValue(),
                user.getPosition(),
                user.getDisplayOrder(),
                user.getPermissions() == null ? null : StringUtils.join(user.getPermissions(), ","),
                user.getUsername(),
                user.getFullname(),
                user.getSex(),
                user.getMobile(),
                user.getEmail(),
                user.isDisabled(),
                user.getDescription(),
                user.getOaData(),
                user.getHrData(),
                user.getJobNumber(),
                user.getOrgConfirmStatus(),
                user.getId()
        );
    }

    @Override
    public void batchUpdate(List<User> userList) {
        String sql = "UPDATE BAS_USER SET U_ORGANIZATION_ID = ?,U_TYPE = ?,U_POSITION = ?,U_DISPLAY_ORDER = ?,U_PERMISSIONS = ?," +
                "U_USERNAME = ?,U_FULLNAME = ?,U_SEX = ?,U_MOBILE = ?,U_EMAIL = ?,U_DISABLED = ?,U_DESCRIPTION = ?,U_OA_DATA = ?," +
                "U_HR_DATA = ?, U_JOB_NUMBER = ?, U_ORG_CONFIRM_STATUS = ? WHERE U_ID = ?";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                User user = userList.get(i);
                int j = 0;
                ps.setString(++j, user.getOrganizationId());
                ps.setInt(++j, user.getType().getValue());
                ps.setString(++j, user.getPosition());
                ps.setString(++j, user.getDisplayOrder());
                ps.setString(++j, user.getPermissions() == null ? null : StringUtils.join(user.getPermissions(), ","));
                ps.setString(++j, user.getUsername());
                ps.setString(++j, user.getFullname());
                ps.setString(++j, user.getSex());
                ps.setString(++j, user.getMobile());
                ps.setString(++j, user.getEmail());
                ps.setBoolean(++j, user.isDisabled());
                ps.setString(++j, user.getDescription());
                ps.setString(++j, user.getOaData());
                ps.setString(++j, user.getHrData());
                ps.setString(++j, user.getJobNumber());
                ps.setObject(++j, user.getOrgConfirmStatus());
                ps.setString(++j, user.getId());
            }

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

    @Override
    public void updatePassword(String id, String password) {
        String sql = "UPDATE BAS_USER SET U_PASSWORD = ? WHERE U_ID = ?";
        jdbcTemplate.update(sql, AESTool.getEncryptString(password), id);
    }

    @Override
    public void setDefaultPassword(String password) {
        String sql = "UPDATE BAS_USER SET U_PASSWORD = ? WHERE U_PASSWORD IS NULL";
        jdbcTemplate.update(sql, AESTool.getEncryptString(password));
    }

    @Override
    public void deleteById(String id) {
        jdbcTemplate.update("DELETE FROM BAS_USER WHERE U_ID = ?", id);
    }

    @Override
    public void truncate() {
        jdbcTemplate.update("TRUNCATE TABLE BAS_USER");
    }

    @Override
    public List<User> selectByOrganizationIdAndRole(List<String> organizationIdList, String roleId) {
        if (CollectionUtils.isEmpty(organizationIdList))
            return new ArrayList<>();
        if (StringUtils.isBlank(roleId))
            return new ArrayList<>();
        StringBuffer sql = new StringBuffer("SELECT A.*,B.UOR_ORGANIZATION_ID,D.BO_NAME FROM BAS_USER A LEFT JOIN BAS_USER_ORGANIZATION_RELA B ON U_ID = UOR_USER_ID LEFT JOIN BAS_USER_ROLE C ON U_ID = UR_USER_ID LEFT JOIN BAS_ORGANIZATION D ON UOR_ORGANIZATION_ID = BO_ID");
        Map<String, Object> andMap = new HashMap<>();
        andMap.put(" C.UR_ROLE_ID = ? ", roleId);
        andMap.put(OracleUtils.placeholderIn("B.UOR_ORGANIZATION_ID", organizationIdList), organizationIdList);
        List<Object> paramList = OracleUtils.combinationSql(sql, andMap);
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql.toString(), paramList.toArray());
        List<User> userList = new ArrayList<>();
        while (sqlRowSet.next()) {
            User user = convert(sqlRowSet);
            user.setOrganizationId(sqlRowSet.getString("UOR_ORGANIZATION_ID"));
            user.setOrganizationName(sqlRowSet.getString("BO_NAME"));
            userList.add(user);
        }
        return userList;
    }

    /**
     * 查询条件转换
     *
     * @param criteria
     * @return
     */
    private Map<String, Object> transferCriteria(UserCriteria criteria) {
        Map<String, Object> andMap = new HashMap<>();
        if (criteria.isDisabled() != null) {
            if (criteria.isDisabled()) {
                andMap.put(" U_DISABLED = 1 ", null);
            } else {
                andMap.put(" U_DISABLED = 0 ", null);
            }
        }
        if (StringUtils.isNotBlank(criteria.getUserName())) {
            andMap.put(" U_USERNAME LIKE ?", "%" + criteria.getUserName() + "%");
        }
        if (StringUtils.isNotBlank(criteria.getFullName())) {
            andMap.put(" U_FULLNAME LIKE ?", "%" + criteria.getFullName() + "%");
        }
        if (CollectionUtils.isNotEmpty(criteria.getOrganizationIds())) {
            andMap.put(OracleUtils.placeholderIn("U_ORGANIZATION_ID", criteria.getOrganizationIds()), criteria.getOrganizationIds());
        }
        if (CollectionUtils.isNotEmpty(criteria.getUserIds())) {
            andMap.put(OracleUtils.placeholderIn("U_ID", criteria.getUserIds()), criteria.getUserIds());
        }
        if (CollectionUtils.isNotEmpty(criteria.getJobNumbers())) {
            andMap.put(OracleUtils.placeholderIn("U_JOB_NUMBER", criteria.getJobNumbers()), criteria.getJobNumbers());
        }
        if (StringUtils.isNotEmpty(criteria.getOrganizationId())) {
            andMap.put("U_ORGANIZATION_ID IN (SELECT BO_ID FROM BAS_ORGANIZATION T WHERE T.BO_ID != '5241' START WITH T.BO_ID = ? CONNECT BY PRIOR T.BO_ID = T.BO_PARENT_ID)", criteria.getOrganizationId());
        }
        if (StringUtils.isNotBlank(criteria.getJobNumberContain())) {
            andMap.put(" U_JOB_NUMBER LIKE ? ", criteria.getJobNumberContain());
        }
        if (CollectionUtils.isNotEmpty(criteria.getUsernameList())) {
            andMap.put(OracleUtils.placeholderIn("U_USERNAME", criteria.getUsernameList()), criteria.getUsernameList());
        }
        if (StringUtils.isNotBlank(criteria.getRoleName())) {
            andMap.put(" U_ID IN (SELECT UR_USER_ID FROM BAS_USER_ROLE LEFT JOIN BAS_ROLE ON UR_ROLE_ID = RL_ID WHERE RL_NAME = ?) ", criteria.getRoleName());
        }
        if (StringUtils.isNotBlank(criteria.getOrganizationName())) {
            andMap.put(" U_ORGANIZATION_ID IN (SELECT BO_ID FROM BAS_ORGANIZATION WHERE BO_NAME LIKE ? ) ", "%" + criteria.getOrganizationName() + "%");
        }
        if (criteria.getHasLeader() != null) {
            if (!criteria.getHasLeader()) {
                if (criteria.getBranchCompanyFlag() != null && criteria.getBranchCompanyFlag()) {
                    andMap.put(" SUBSTR(U_POSITION, LENGTH(U_POSITION)-5, LENGTH(U_POSITION)) NOT IN ('JS0003','JS0005') ", null);
                } else {
                    andMap.put(" SUBSTR(U_POSITION, LENGTH(U_POSITION)-5, LENGTH(U_POSITION)) NOT IN ('JS0003','JS0005','JS0052','JS0053') ", null);
                }

            }
        }
        return andMap;
    }

    /**
     * 查询结果集转换成领域对象
     *
     * @param sqlRowSet 查询结果集
     * @return 用户对象
     */
    private User convert(SqlRowSet sqlRowSet) {
        String permissions = DatabaseUtils.getBigField(sqlRowSet, "U_PERMISSIONS");
        List<Integer> permissionList = new ArrayList<>();
        if (!StringUtils.isEmpty(permissions))
            permissionList = Arrays.stream(StringUtils.split(permissions, ","))
                    .map(Integer::parseInt).collect(Collectors.toList());
        return new User(
                sqlRowSet.getString("U_ID"),
                sqlRowSet.getString("U_ORGANIZATION_ID"),
                EnumUtils.getByValue(UserType.class, sqlRowSet.getInt("U_TYPE")),
                sqlRowSet.getString("U_POSITION"),
                sqlRowSet.getString("U_DISPLAY_ORDER"),
                permissionList,
                sqlRowSet.getString("U_USERNAME"),
                sqlRowSet.getString("U_FULLNAME"),
                sqlRowSet.getString("U_SEX"),
                sqlRowSet.getString("U_MOBILE"),
                sqlRowSet.getString("U_EMAIL"),
                sqlRowSet.getBoolean("U_DISABLED"),
                DatabaseUtils.getBigField(sqlRowSet, "U_DESCRIPTION"),
                DatabaseUtils.getBigField(sqlRowSet, "U_OA_DATA"),
                DatabaseUtils.getBigField(sqlRowSet, "U_HR_DATA"),
                sqlRowSet.getString("U_JOB_NUMBER"),
                sqlRowSet.getObject("U_ORG_CONFIRM_STATUS") == null ? null : sqlRowSet.getInt("U_ORG_CONFIRM_STATUS")
        );
    }
}
