<?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.storlead.user.mapper.SubCompanyMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" extends="com.storlead.framework.mybatis.mapper.SysBaseFieldMapper.BaseResultMap"  type="com.storlead.user.pojo.entity.SubCompanyEntity">
        <id column="id" property="id" />
        <result column="company_id" property="companyId" />
        <result column="name" property="name" />
        <result column="desc" property="desc" />
        <result column="leader_id" property="leaderId" />
        <result column="route_code" property="routeCode" />
        <result column="data_source" property="dataSource" />
        <result column="leader_name" property="leaderName" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, company_id, `name`,leader_name,data_source,`leader_id`, `desc`,route_code, <include refid="com.storlead.framework.mybatis.mapper.SysBaseFieldMapper.Base_Column_List"></include>
    </sql>
    <select id="getHasSublist" resultType="com.storlead.user.pojo.entity.SubCompanyEntity">
        WITH
            selected_depts AS (
                SELECT id FROM dept WHERE id IN (select dept_id from dept_role where role_id = #{roleId}) AND is_delete=0
            ),

            company_dept_count AS (
                SELECT
                    sub_company_id,
                    COUNT(*) AS total_depts
                FROM dept
                WHERE is_delete=0
                GROUP BY sub_company_id
            ),

            company_selected_count AS (
                SELECT
                    d.sub_company_id,
                    COUNT(*) AS selected_depts
                FROM dept d
                         JOIN selected_depts s ON d.id = s.id
                WHERE d.is_delete=0
                GROUP BY d.sub_company_id
            )
        SELECT
            sc.id,
            sc.name
        FROM sub_company sc
                 JOIN company_dept_count cdc ON sc.id = cdc.sub_company_id
                 JOIN company_selected_count csc ON sc.id = csc.sub_company_id
        WHERE cdc.total_depts = csc.selected_depts;
    </select>

</mapper>
