package jnpf.limsMapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface LimsMessageAudienceMapper {

    @Select("SELECT DISTINCT ur.f_user_id " +
            "FROM base_user_relation ur " +
            "JOIN base_role r ON r.f_en_code = #{roleCode} " +
            "JOIN base_role_relation rr ON rr.f_role_id = r.f_id " +
            " AND rr.f_object_type = 'user' AND rr.f_object_id = ur.f_user_id " +
            "JOIN base_user u ON u.f_id = ur.f_user_id " +
            "WHERE LOWER(ur.f_object_type) = 'organize' AND ur.f_object_id = #{organizeId} " +
            "AND (r.f_delete_mark IS NULL OR r.f_delete_mark <> 1) AND r.f_enabled_mark = 1 " +
            "AND (rr.f_delete_mark IS NULL OR rr.f_delete_mark <> 1) " +
            "AND (ur.f_delete_mark IS NULL OR ur.f_delete_mark <> 1) " +
            "AND (u.f_delete_mark IS NULL OR u.f_delete_mark <> 1) AND u.f_enabled_mark = 1 " +
            "AND COALESCE(r.f_tenant_id, '0') = COALESCE(#{tenantId}, '0') " +
            "AND COALESCE(u.f_tenant_id, '0') = COALESCE(#{tenantId}, '0')")
    List<String> selectOrganizationRoleUsers(@Param("tenantId") String tenantId,
                                              @Param("organizeId") String organizeId,
                                              @Param("roleCode") String roleCode);

    @Select("SELECT DISTINCT jsonb_array_elements_text(a.bei_shouquanren::jsonb) " +
            "FROM lims_baogao_shouquan a " +
            "WHERE (a.f_delete_mark IS NULL OR a.f_delete_mark <> 1) " +
            "AND a.biz_enabled = 'yes' " +
            "AND a.shenhe_jibie::jsonb @> to_jsonb(#{authorityLevel}::text) " +
            "AND a.shenhe_fenlei::jsonb @> to_jsonb(#{bizType}::text) " +
            "AND COALESCE(a.f_tenant_id, '0') = COALESCE(#{tenantId}, '0')")
    List<String> selectReportAuthorizedUsers(@Param("tenantId") String tenantId,
                                              @Param("authorityLevel") String authorityLevel,
                                              @Param("bizType") String bizType);

    @Select("SELECT COALESCE(NULLIF(u.f_real_name, ''), u.f_account) FROM base_user u " +
            "WHERE u.f_id = #{userId} " +
            "AND COALESCE(u.f_tenant_id, '0') = COALESCE(#{tenantId}, '0') " +
            "AND (u.f_delete_mark IS NULL OR u.f_delete_mark <> 1) LIMIT 1")
    String selectUserDisplayName(@Param("tenantId") String tenantId,
                                 @Param("userId") String userId);
}