<?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="role">

  <!-- ──────────────────────────────────────────────────
       권한 그룹 목록 조회
  ────────────────────────────────────────────────── -->
  <select id="getRoleGroupList" parameterType="map" resultType="map">
    SELECT
        AM.OBJID
        , AM.AUTH_NAME
        , AM.AUTH_CODE
        , AM.COMPANY_CODE
        , AM.STATUS
        , AM.WRITER
        , AM.CREATED_DATE
        , (SELECT COUNT(*) FROM AUTHORITY_SUB_USER WHERE MASTER_OBJID = AM.OBJID) AS member_count
        , (SELECT COUNT(*) FROM AUTHORITY_SUB_MENU WHERE MASTER_OBJID = AM.OBJID) AS menu_count
    FROM AUTHORITY_MASTER AM
    WHERE 1=1
    <if test="company_code != null and company_code != ''">
      AND (AM.COMPANY_CODE = #{company_code} OR AM.COMPANY_CODE = '*')
    </if>
    <if test="search != null and search != ''">
      AND AM.AUTH_NAME ILIKE '%' || #{search} || '%'
    </if>
    ORDER BY AM.CREATED_DATE DESC
  </select>

  <!-- 권한 그룹 단건 조회 -->
  <select id="getRoleGroupInfo" parameterType="map" resultType="map">
    SELECT
        OBJID
        , AUTH_NAME
        , AUTH_CODE
        , COMPANY_CODE
        , STATUS
        , WRITER
        , CREATED_DATE
    FROM AUTHORITY_MASTER
    WHERE OBJID = #{objid}
  </select>

  <!-- 권한 그룹 생성 -->
  <insert id="insertRoleGroup" parameterType="map">
    INSERT INTO AUTHORITY_MASTER (
        OBJID
        , AUTH_NAME
        , AUTH_CODE
        , COMPANY_CODE
        , STATUS
        , WRITER
        , CREATED_DATE
    ) VALUES (
        #{objid}
        , #{auth_name}
        , #{auth_code}
        , #{company_code}
        , 'active'
        , #{writer}
        , NOW()
    )
  </insert>

  <!-- 권한 그룹 수정 -->
  <update id="updateRoleGroup" parameterType="map">
    UPDATE AUTHORITY_MASTER
    SET
      <if test="auth_name != null">
        AUTH_NAME = #{auth_name},
      </if>
      <if test="auth_code != null">
        AUTH_CODE = #{auth_code},
      </if>
      <if test="status != null">
        STATUS = #{status},
      </if>
      WRITER = #{writer}
    WHERE OBJID = #{objid}
  </update>

  <!-- 권한 그룹 삭제 -->
  <delete id="deleteRoleGroup" parameterType="map">
    DELETE FROM AUTHORITY_MASTER
    WHERE OBJID = #{objid}
  </delete>

  <!-- ──────────────────────────────────────────────────
       멤버 관리 (권한있는 직원)
  ────────────────────────────────────────────────── -->

  <!-- 권한있는 직원 목록 조회 -->
  <select id="getRoleMemberList" parameterType="map" resultType="map">
    SELECT
        ASU.OBJID
        , ASU.MASTER_OBJID
        , ASU.USER_ID
        , ASU.WRITER
        , ASU.CREATED_DATE
        , UI.USER_NAME
        , UI.DEPT_CODE
        , UI.DEPT_NAME
        , UI.POSITION_CODE
        , UI.POSITION_NAME
        , UI.EMAIL
        , UI.COMPANY_CODE
    FROM AUTHORITY_SUB_USER ASU
    LEFT JOIN USER_INFO UI ON ASU.USER_ID = UI.USER_ID
    WHERE ASU.MASTER_OBJID = #{master_objid}
    ORDER BY ASU.CREATED_DATE ASC
  </select>

  <!-- 권한없는 직원 목록 조회 (해당 그룹의 비멤버, 같은 회사) -->
  <select id="getRoleNonMemberList" parameterType="map" resultType="map">
    SELECT
        UI.USER_ID
        , UI.USER_NAME
        , UI.DEPT_CODE
        , UI.DEPT_NAME
        , UI.POSITION_CODE
        , UI.POSITION_NAME
        , UI.EMAIL
        , UI.COMPANY_CODE
    FROM USER_INFO UI
    WHERE UI.USER_ID NOT IN (
        SELECT USER_ID FROM AUTHORITY_SUB_USER WHERE MASTER_OBJID = #{master_objid}
    )
    <if test='company_code != null and company_code != "" and company_code != "*"'>
      AND (UI.COMPANY_CODE = #{company_code} OR UI.COMPANY_CODE = '*')
    </if>
    <if test="status_active != null and status_active == true">
      AND (UI.STATUS IS NULL OR UI.STATUS = 'active')
    </if>
    ORDER BY UI.USER_NAME ASC
  </select>

  <!-- 중복 체크 -->
  <select id="existsRoleMember" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM AUTHORITY_SUB_USER
    WHERE MASTER_OBJID = #{master_objid}
      AND USER_ID = #{user_id}
  </select>

  <!-- 멤버 일괄 추가 (userIds: List&lt;String&gt;) -->
  <insert id="insertRoleMembers" parameterType="map">
    INSERT INTO AUTHORITY_SUB_USER (
        OBJID
        , MASTER_OBJID
        , USER_ID
        , WRITER
        , CREATED_DATE
    ) VALUES
    <foreach collection="userIds" item="user_id" separator=",">
      (#{master_objid} || '_' || #{user_id}, #{master_objid}, #{user_id}, #{writer}, NOW())
    </foreach>
  </insert>

  <!-- 개별 멤버 추가 (중복 회피 — UPSERT 대신 NOT EXISTS) -->
  <insert id="insertSingleRoleMember" parameterType="map">
    INSERT INTO AUTHORITY_SUB_USER (
        OBJID
        , MASTER_OBJID
        , USER_ID
        , WRITER
        , CREATED_DATE
    )
    SELECT #{objid}, #{master_objid}, #{user_id}, #{writer}, NOW()
    WHERE NOT EXISTS (
        SELECT 1 FROM AUTHORITY_SUB_USER
        WHERE MASTER_OBJID = #{master_objid} AND USER_ID = #{user_id}
    )
  </insert>

  <!-- 지정 멤버 일괄 삭제 -->
  <delete id="deleteRoleMembers" parameterType="map">
    DELETE FROM AUTHORITY_SUB_USER
    WHERE MASTER_OBJID = #{master_objid}
    AND USER_ID IN
    <foreach collection="userIds" item="user_id" open="(" separator="," close=")">
      #{user_id}
    </foreach>
  </delete>

  <!-- 개별 멤버 제거 -->
  <delete id="deleteSingleRoleMember" parameterType="map">
    DELETE FROM AUTHORITY_SUB_USER
    WHERE MASTER_OBJID = #{master_objid}
      AND USER_ID = #{user_id}
  </delete>

  <!-- 그룹의 전체 멤버 삭제 -->
  <delete id="deleteAllRoleMembers" parameterType="map">
    DELETE FROM AUTHORITY_SUB_USER
    WHERE MASTER_OBJID = #{master_objid}
  </delete>

  <!-- ──────────────────────────────────────────────────
       메뉴 권한 관리 (CRUD 세분화)
  ────────────────────────────────────────────────── -->

  <!-- 권한 그룹의 메뉴 권한 목록 조회 -->
  <select id="getMenuPermissionList" parameterType="map" resultType="map">
    SELECT
        ASM.OBJID
        , ASM.MASTER_OBJID
        , ASM.MENU_OBJID
        , ASM.CREATE_YN
        , ASM.READ_YN
        , ASM.UPDATE_YN
        , ASM.DELETE_YN
        , ASM.WRITER
        , ASM.CREATED_DATE
        , MI.MENU_NAME_KOR AS menu_name
        , MI.MENU_NAME_ENG
        , MI.MENU_URL
        , MI.PARENT_OBJ_ID  AS parent_objid
        , MI.SEQ            AS sort_order
        , MI.MENU_TYPE
        , MI.COMPANY_CODE
    FROM AUTHORITY_SUB_MENU ASM
    INNER JOIN MENU_INFO MI ON ASM.MENU_OBJID = MI.OBJID
    WHERE ASM.MASTER_OBJID = #{auth_objid}
    ORDER BY MI.SEQ ASC
  </select>

  <!-- 단건 조회 (upsert 확인용) -->
  <select id="getMenuPermissionByMenu" parameterType="map" resultType="map">
    SELECT
        OBJID
        , MASTER_OBJID
        , MENU_OBJID
        , CREATE_YN
        , READ_YN
        , UPDATE_YN
        , DELETE_YN
    FROM AUTHORITY_SUB_MENU
    WHERE MASTER_OBJID = #{auth_objid}
      AND MENU_OBJID = #{menu_objid}
  </select>

  <!-- 해당 권한 그룹의 메뉴 권한 전체 삭제 -->
  <delete id="deleteMenuPermissions" parameterType="map">
    DELETE FROM AUTHORITY_SUB_MENU
    WHERE MASTER_OBJID = #{auth_objid}
  </delete>

  <!-- 메뉴 권한 일괄 추가 (permissions: List&lt;Map&gt;) -->
  <insert id="insertMenuPermissions" parameterType="map">
    INSERT INTO AUTHORITY_SUB_MENU (
        OBJID
        , MASTER_OBJID
        , MENU_OBJID
        , CREATE_YN
        , READ_YN
        , UPDATE_YN
        , DELETE_YN
        , WRITER
        , CREATED_DATE
    ) VALUES
    <foreach collection="permissions" item="perm" separator=",">
      (
        #{auth_objid} || '_' || #{perm.menuObjid}
        , #{auth_objid}
        , #{perm.menuObjid}
        , COALESCE(#{perm.createYn}, 'N')
        , COALESCE(#{perm.readYn}, 'N')
        , COALESCE(#{perm.updateYn}, 'N')
        , COALESCE(#{perm.deleteYn}, 'N')
        , #{writer}
        , NOW()
      )
    </foreach>
  </insert>

  <!-- 개별 메뉴 권한 upsert (체크 즉시 반영용) -->
  <insert id="upsertMenuPermission" parameterType="map">
    INSERT INTO AUTHORITY_SUB_MENU (
        OBJID
        , MASTER_OBJID
        , MENU_OBJID
        , CREATE_YN
        , READ_YN
        , UPDATE_YN
        , DELETE_YN
        , WRITER
        , CREATED_DATE
    ) VALUES (
        #{objid}
        , #{auth_objid}
        , #{menu_objid}
        , COALESCE(#{create_yn}, 'N')
        , COALESCE(#{read_yn}, 'N')
        , COALESCE(#{update_yn}, 'N')
        , COALESCE(#{delete_yn}, 'N')
        , #{writer}
        , NOW()
    )
    ON CONFLICT (MASTER_OBJID, MENU_OBJID) DO UPDATE SET
        CREATE_YN    = COALESCE(EXCLUDED.CREATE_YN, AUTHORITY_SUB_MENU.CREATE_YN)
        , READ_YN    = COALESCE(EXCLUDED.READ_YN, AUTHORITY_SUB_MENU.READ_YN)
        , UPDATE_YN  = COALESCE(EXCLUDED.UPDATE_YN, AUTHORITY_SUB_MENU.UPDATE_YN)
        , DELETE_YN  = COALESCE(EXCLUDED.DELETE_YN, AUTHORITY_SUB_MENU.DELETE_YN)
        , WRITER     = EXCLUDED.WRITER
        , UPDATED_DATE = NOW()
  </insert>

  <!-- 개별 메뉴 권한 삭제 (모든 CRUD N 이 되어 사실상 해제 시 row 삭제도 가능) -->
  <delete id="deleteSingleMenuPermission" parameterType="map">
    DELETE FROM AUTHORITY_SUB_MENU
    WHERE MASTER_OBJID = #{auth_objid}
      AND MENU_OBJID = #{menu_objid}
  </delete>

  <!-- ──────────────────────────────────────────────────
       전체 메뉴 목록 (권한 설정용, 트리 구조 원천)
  ────────────────────────────────────────────────── -->
  <select id="getMenuList" parameterType="map" resultType="map">
    SELECT
        OBJID
        , MENU_NAME_KOR   AS menu_name
        , MENU_NAME_ENG
        , MENU_URL
        , PARENT_OBJ_ID   AS parent_objid
        , COMPANY_CODE
        , SEQ             AS sort_order
        , MENU_TYPE
        , STATUS
        , MENU_ICON
    FROM MENU_INFO
    WHERE 1=1
      AND (STATUS IS NULL OR STATUS = 'active')
    <if test="company_code != null and company_code != ''">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
    ORDER BY SEQ ASC
  </select>

  <!-- ──────────────────────────────────────────────────
       사용자가 속한 권한 그룹 목록
  ────────────────────────────────────────────────── -->
  <select id="getUserRoleGroupList" parameterType="map" resultType="map">
    SELECT
        AM.OBJID
        , AM.AUTH_NAME
        , AM.AUTH_CODE
        , AM.COMPANY_CODE
        , AM.STATUS
    FROM AUTHORITY_MASTER AM
    INNER JOIN AUTHORITY_SUB_USER ASU ON AM.OBJID = ASU.MASTER_OBJID
    WHERE ASU.USER_ID = #{user_id}
    <if test='company_code != null and company_code != "*"'>
      AND (AM.COMPANY_CODE = #{company_code} OR AM.COMPANY_CODE = '*')
    </if>
    ORDER BY AM.AUTH_NAME ASC
  </select>

</mapper>
