<?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.ga.core.mapper.org.OrganizationMapper">

    <resultMap id="OrgVOMap" type="com.ga.core.vo.org.OrganizationVO"/>
    <resultMap id="OrgRespMap" type="com.ga.core.vo.org.OrganizationResp"/>

    <select id="selectAllForTree" resultMap="OrgRespMap">
        SELECT o.org_id, o.parent_org_id, p.org_name AS parent_org_name,
               o.org_name, o.org_type,
               (SELECT cc.code_name FROM common_code cc WHERE cc.group_code = 'ORG_TYPE' AND cc.code = o.org_type) AS org_type_name,
               o.org_level, o.region, o.effective_from, o.effective_to, o.is_active,
               (SELECT COUNT(*) FROM agent a WHERE a.org_id = o.org_id AND a.status = 'ACTIVE') AS agent_count
        FROM organization o
        LEFT JOIN organization p ON p.org_id = o.parent_org_id
        WHERE o.is_active = 'Y'
        ORDER BY o.org_level, o.org_id
    </select>

    <select id="selectList" resultMap="OrgRespMap">
        SELECT o.org_id, o.parent_org_id, p.org_name AS parent_org_name,
               o.org_name, o.org_type, o.org_level, o.region,
               o.effective_from, o.effective_to, o.is_active
        FROM organization o
        LEFT JOIN organization p ON p.org_id = o.parent_org_id
        <where>
            <if test="keyword != null and keyword != ''">
                AND o.org_name LIKE CONCAT('%', #{keyword}, '%')
            </if>
            <if test="orgType != null and orgType != ''">AND o.org_type = #{orgType}</if>
            <if test="isActive != null and isActive != ''">AND o.is_active = #{isActive}</if>
        </where>
        ORDER BY o.org_level, o.org_id
    </select>

    <select id="selectById" resultMap="OrgVOMap">
        SELECT * FROM organization WHERE org_id = #{orgId}
    </select>

    <!-- 자기 자신 + 모든 하위 조직 (재귀 CTE) -->
    <select id="selectDescendantIds" resultType="long">
        WITH RECURSIVE descendants AS (
            SELECT org_id FROM organization WHERE org_id = #{orgId}
            UNION ALL
            SELECT o.org_id FROM organization o
            JOIN descendants d ON o.parent_org_id = d.org_id
        )
        SELECT org_id FROM descendants
    </select>

    <!-- 자기 자신 + 모든 상위 조직 (가까운 순) -->
    <select id="selectAncestorIds" resultType="long">
        WITH RECURSIVE ancestors AS (
            SELECT org_id, parent_org_id, 0 AS depth FROM organization WHERE org_id = #{orgId}
            UNION ALL
            SELECT o.org_id, o.parent_org_id, a.depth + 1
            FROM organization o JOIN ancestors a ON a.parent_org_id = o.org_id
        )
        SELECT org_id FROM ancestors ORDER BY depth
    </select>

    <insert id="insert" parameterType="com.ga.core.vo.org.OrganizationVO" useGeneratedKeys="true" keyProperty="orgId">
        INSERT INTO organization (parent_org_id, org_name, org_type, org_level, region,
                                  effective_from, effective_to, is_active, created_at, created_by)
        VALUES (#{parentOrgId}, #{orgName}, #{orgType}, #{orgLevel}, #{region},
                #{effectiveFrom}, #{effectiveTo}, COALESCE(#{isActive},'Y'),
                #{createdAt}, #{createdBy})
    </insert>

    <update id="update" parameterType="com.ga.core.vo.org.OrganizationVO">
        UPDATE organization SET
            parent_org_id = #{parentOrgId},
            org_name = #{orgName},
            org_type = #{orgType},
            org_level = #{orgLevel},
            region = #{region},
            effective_from = #{effectiveFrom},
            effective_to = #{effectiveTo},
            is_active = #{isActive},
            updated_at = #{updatedAt}
        WHERE org_id = #{orgId}
    </update>

    <delete id="deleteById">DELETE FROM organization WHERE org_id = #{orgId}</delete>

    <select id="countChildren" resultType="int">
        SELECT COUNT(*) FROM organization WHERE parent_org_id = #{orgId}
    </select>

    <select id="countAgents" resultType="int">
        SELECT COUNT(*) FROM agent WHERE org_id = #{orgId} AND status = 'ACTIVE'
    </select>

</mapper>
