<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.iflytek.astron.console.commons.mapper.space.SpaceMapper">

<select id="recentVisitList" resultType="com.iflytek.astron.console.commons.dto.space.SpaceVO">
        select s.*, u.last_visit_time from agent_space s left join agent_space_user u on (s.id = u.space_id)
        where u.uid = #{uid} and s.deleted = 0 and u.last_visit_time is not null
        <if test="enterpriseId != null">
            and s.enterprise_id = #{enterpriseId}
        </if>
        <if test="enterpriseId == null">
            and s.enterprise_id is null
        </if>
        order by u.last_visit_time desc
    </select>

    <select id="joinList" resultType="com.iflytek.astron.console.commons.dto.space.SpaceVO">
        select s.*, u.role as user_role from agent_space s left join agent_space_user u on (s.id = u.space_id)
        where u.uid = #{uid} and s.deleted = 0
        <if test="enterpriseId != null">
            and s.enterprise_id = #{enterpriseId}
        </if>
        <if test="enterpriseId == null">
            and s.enterprise_id is null
        </if>
        <if test="name != null and name != ''">
            and s.name like concat('%', #{name}, '%')
        </if>
        order by u.create_time desc
    </select>

    <select id="selfList" resultType="com.iflytek.astron.console.commons.dto.space.SpaceVO">
        select s.*, u.role as user_role from agent_space s left join agent_space_user u on (s.id = u.space_id)
        where u.uid = #{uid} and s.deleted = 0 and u.role = #{role}
        <if test="enterpriseId != null">
            and s.enterprise_id = #{enterpriseId}
        </if>
        <if test="enterpriseId == null">
            and s.enterprise_id is null
        </if>
        <if test="name != null and name != ''">
            and s.name like concat('%', #{name}, '%')
        </if>
        order by u.create_time desc
    </select>

    <select id="corporateList" resultType="com.iflytek.astron.console.commons.dto.space.SpaceVO">
        select s.*, u.role as user_role,
        case when u.id is not null then 1
        when a.id is not null then 3 else 2 end as apply_status
        from agent_space s
        left join agent_space_user u on (s.id = u.space_id and u.uid = #{uid})
        left join agent_apply_record a on (s.id = a.space_id and a.apply_uid = #{uid} and a.status = 1)
        where s.deleted = 0 and s.enterprise_id = #{enterpriseId}
        <if test="name != null and name != ''">
            and s.name like concat('%', #{name}, '%')
        </if>
        order by s.create_time desc
    </select>

    <select id="getByUidAndId" resultType="com.iflytek.astron.console.commons.dto.space.SpaceVO">
        select s.*, u.role as user_role from agent_space s left join agent_space_user u on (s.id = u.space_id)
        where u.uid = #{uid} and s.deleted = 0 and s.id = #{spaceId} limit 1
    </select>

    <select id="corporateCount" resultType="com.iflytek.astron.console.commons.dto.space.EnterpriseSpaceCountVO">
        select count(1) total, COUNT(u.id) joined from agent_space s LEFT JOIN agent_space_user u
        ON (s.id = u.space_id AND u.uid=#{uid})
        WHERE s.deleted = 0 AND s.enterprise_id = #{enterpriseId}
    </select>
</mapper>