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

  <!-- 부서 목록 조회 (회사별, 부서원 수 포함). soft-delete: 기본 active 만, include_deleted=true 시 deleted 포함 -->
  <select id="selectDepartments" parameterType="map" resultType="map">
    SELECT
      D.DEPT_CODE,
      D.DEPT_NAME,
      D.COMPANY_CODE,
      D.PARENT_DEPT_CODE,
      D.SHORT_NAME,
      D.DEPT_TYPE,
      D.ORG_SYSTEM,
      D.APPROVAL_MANAGER,
      D.DEPT_MANAGER,
      D.ZIPCODE,
      D.ADDRESS1,
      D.ADDRESS2,
      D.START_DATE,
      D.END_DATE,
      D.SORT_ORDER,
      D.STATUS,
      D.DELETED_AT,
      COUNT(DISTINCT UD.USER_ID) AS MEMBER_COUNT,
      (SELECT COALESCE(json_agg(json_build_object('user_id', m.USER_ID) ORDER BY m.SORT_ORDER, m.USER_ID), '[]'::json)
         FROM DEPT_MANAGERS m WHERE m.DEPT_CODE = D.DEPT_CODE AND m.ROLE = 'approval')::TEXT AS APPROVAL_MANAGERS,
      (SELECT COALESCE(json_agg(json_build_object('user_id', m.USER_ID) ORDER BY m.SORT_ORDER, m.USER_ID), '[]'::json)
         FROM DEPT_MANAGERS m WHERE m.DEPT_CODE = D.DEPT_CODE AND m.ROLE = 'dept')::TEXT AS DEPT_MANAGERS,
      (SELECT COALESCE(json_agg(json_build_object('user_id', m.USER_ID) ORDER BY m.SORT_ORDER, m.USER_ID), '[]'::json)
         FROM DEPT_MANAGERS m WHERE m.DEPT_CODE = D.DEPT_CODE AND m.ROLE = 'org_leader')::TEXT AS ORG_LEADERS
    FROM DEPT_INFO D
    LEFT JOIN USER_DEPT UD ON D.DEPT_CODE = UD.DEPT_CODE
    WHERE (D.COMPANY_CODE = #{company_code} OR D.COMPANY_CODE = '*')
    <if test="include_deleted == null or include_deleted == false">
      AND D.DELETED_AT IS NULL
    </if>
    <if test="base_date != null and base_date != ''">
      AND (D.START_DATE IS NULL OR D.START_DATE &lt;= #{base_date}::date)
      AND (D.END_DATE IS NULL OR D.END_DATE &gt;= #{base_date}::date)
    </if>
    GROUP BY
      D.DEPT_CODE, D.DEPT_NAME, D.COMPANY_CODE, D.PARENT_DEPT_CODE,
      D.SHORT_NAME, D.DEPT_TYPE, D.ORG_SYSTEM, D.APPROVAL_MANAGER, D.DEPT_MANAGER,
      D.ZIPCODE, D.ADDRESS1, D.ADDRESS2, D.START_DATE, D.END_DATE,
      D.SORT_ORDER, D.STATUS, D.DELETED_AT
    ORDER BY COALESCE(D.SORT_ORDER, 9999), D.DEPT_NAME
  </select>

  <!-- 부서 단건 조회 (active 만) -->
  <select id="selectDepartmentByCode" parameterType="map" resultType="map">
    SELECT
      DEPT_CODE,
      DEPT_NAME,
      COMPANY_CODE,
      PARENT_DEPT_CODE,
      SHORT_NAME,
      DEPT_TYPE,
      ORG_SYSTEM,
      APPROVAL_MANAGER,
      DEPT_MANAGER,
      ZIPCODE,
      ADDRESS1,
      ADDRESS2,
      START_DATE,
      END_DATE,
      SORT_ORDER,
      STATUS,
      DELETED_AT,
      (SELECT COALESCE(json_agg(json_build_object('user_id', m.USER_ID) ORDER BY m.SORT_ORDER, m.USER_ID), '[]'::json)
         FROM DEPT_MANAGERS m WHERE m.DEPT_CODE = DEPT_INFO.DEPT_CODE AND m.ROLE = 'approval')::TEXT AS APPROVAL_MANAGERS,
      (SELECT COALESCE(json_agg(json_build_object('user_id', m.USER_ID) ORDER BY m.SORT_ORDER, m.USER_ID), '[]'::json)
         FROM DEPT_MANAGERS m WHERE m.DEPT_CODE = DEPT_INFO.DEPT_CODE AND m.ROLE = 'dept')::TEXT AS DEPT_MANAGERS,
      (SELECT COALESCE(json_agg(json_build_object('user_id', m.USER_ID) ORDER BY m.SORT_ORDER, m.USER_ID), '[]'::json)
         FROM DEPT_MANAGERS m WHERE m.DEPT_CODE = DEPT_INFO.DEPT_CODE AND m.ROLE = 'org_leader')::TEXT AS ORG_LEADERS
    FROM DEPT_INFO
    WHERE DEPT_CODE = #{dept_code}
      AND DELETED_AT IS NULL
  </select>

  <!-- 부서 단건 조회 (deleted 포함) — 복구 검증·복구 처리용 -->
  <select id="selectDepartmentByCodeIncludingDeleted" parameterType="map" resultType="map">
    SELECT
      DEPT_CODE,
      DEPT_NAME,
      COMPANY_CODE,
      PARENT_DEPT_CODE,
      SHORT_NAME,
      DEPT_TYPE,
      ORG_SYSTEM,
      APPROVAL_MANAGER,
      DEPT_MANAGER,
      ZIPCODE,
      ADDRESS1,
      ADDRESS2,
      START_DATE,
      END_DATE,
      SORT_ORDER,
      STATUS,
      DELETED_AT,
      (SELECT COALESCE(json_agg(json_build_object('user_id', m.USER_ID) ORDER BY m.SORT_ORDER, m.USER_ID), '[]'::json)
         FROM DEPT_MANAGERS m WHERE m.DEPT_CODE = DEPT_INFO.DEPT_CODE AND m.ROLE = 'approval')::TEXT AS APPROVAL_MANAGERS,
      (SELECT COALESCE(json_agg(json_build_object('user_id', m.USER_ID) ORDER BY m.SORT_ORDER, m.USER_ID), '[]'::json)
         FROM DEPT_MANAGERS m WHERE m.DEPT_CODE = DEPT_INFO.DEPT_CODE AND m.ROLE = 'dept')::TEXT AS DEPT_MANAGERS,
      (SELECT COALESCE(json_agg(json_build_object('user_id', m.USER_ID) ORDER BY m.SORT_ORDER, m.USER_ID), '[]'::json)
         FROM DEPT_MANAGERS m WHERE m.DEPT_CODE = DEPT_INFO.DEPT_CODE AND m.ROLE = 'org_leader')::TEXT AS ORG_LEADERS
    FROM DEPT_INFO
    WHERE DEPT_CODE = #{dept_code}
  </select>

  <!-- 중복 부서명 확인 (per-tenant, 활성 부서만, 공백/대소문자 무관) -->
  <select id="selectDuplicateDeptName" parameterType="map" resultType="map">
    SELECT DEPT_CODE, DEPT_NAME
    FROM DEPT_INFO
    WHERE COMPANY_CODE = #{company_code}
      AND DELETED_AT IS NULL
      AND TRIM(LOWER(DEPT_NAME)) = TRIM(LOWER(#{dept_name}))
  </select>

  <!-- 회사명 조회 (정확 매칭, '*' 글로벌 fallback 제거 — selectOne 에서 다중 row 충돌 방지) -->
  <select id="selectCompanyName" parameterType="map" resultType="map">
    SELECT COMPANY_NAME
    FROM COMPANY_MNG
    WHERE COMPANY_CODE = #{company_code}
    LIMIT 1
  </select>

  <!-- 다음 부서 코드 번호 조회 (전역 카운트) -->
  <select id="selectNextDeptNumber" resultType="map">
    SELECT COALESCE(MAX(CAST(SUBSTRING(DEPT_CODE FROM 6) AS INTEGER)), 0) + 1 AS NEXT_NUMBER
    FROM DEPT_INFO
    WHERE DEPT_CODE ~ '^DEPT_[0-9]+$'
  </select>

  <!-- 부서 생성 (dept_info 스키마 1:1) -->
  <insert id="insertDepartment" parameterType="map">
    INSERT INTO DEPT_INFO (
      DEPT_CODE,
      DEPT_NAME,
      COMPANY_CODE,
      COMPANY_NAME,
      PARENT_DEPT_CODE,
      SHORT_NAME,
      DEPT_TYPE,
      ORG_SYSTEM,
      APPROVAL_MANAGER,
      DEPT_MANAGER,
      ZIPCODE,
      ADDRESS1,
      ADDRESS2,
      START_DATE,
      END_DATE,
      SORT_ORDER,
      STATUS,
      LOCATION,
      CREATED_DATE
    ) VALUES (
      #{dept_code},
      #{dept_name},
      #{company_code},
      #{company_name},
      #{parent_dept_code},
      #{short_name},
      COALESCE(#{dept_type}, 'dept'),
      #{org_system},
      #{approval_manager},
      #{dept_manager},
      #{zipcode},
      #{address1},
      #{address2},
      #{start_date}::date,
      #{end_date}::date,
      COALESCE(#{sort_order}, 10),
      COALESCE(#{status}, 'active'),
      #{location},
      NOW()
    )
  </insert>

  <!-- 부서 수정 (dept_info 스키마 1:1) -->
  <update id="updateDepartment" parameterType="map">
    UPDATE DEPT_INFO
    SET
      DEPT_NAME        = #{dept_name},
      PARENT_DEPT_CODE = #{parent_dept_code},
      SHORT_NAME       = #{short_name},
      DEPT_TYPE        = #{dept_type},
      ORG_SYSTEM       = #{org_system},
      APPROVAL_MANAGER = #{approval_manager},
      DEPT_MANAGER     = #{dept_manager},
      ZIPCODE          = #{zipcode},
      ADDRESS1         = #{address1},
      ADDRESS2         = #{address2},
      START_DATE       = #{start_date}::date,
      END_DATE         = #{end_date}::date,
      SORT_ORDER       = #{sort_order},
      STATUS           = #{status},
      LOCATION         = #{location}
    WHERE DEPT_CODE = #{dept_code}
      AND DELETED_AT IS NULL
  </update>

  <!-- 하위 부서 수 조회 (기본 active 자식만, include_deleted=true 시 deleted 자식도 카운트) -->
  <select id="selectChildDeptCount" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM DEPT_INFO
    WHERE PARENT_DEPT_CODE = #{dept_code}
    <if test="include_deleted == null or include_deleted == false">
      AND DELETED_AT IS NULL
    </if>
  </select>

  <!-- 부서 삭제 (soft-delete: DELETED_AT = NOW()). USER_DEPT 보존 — 복구 시 멤버 그대로 살아남 -->
  <update id="deleteDepartment" parameterType="map">
    UPDATE DEPT_INFO
    SET DELETED_AT = NOW()
    WHERE DEPT_CODE = #{dept_code}
      AND DELETED_AT IS NULL
  </update>

  <!-- 부서 복구 (DELETED_AT = NULL). 호출 전에 부모 deleted 여부 service 에서 검증 -->
  <update id="restoreDepartment" parameterType="map">
    UPDATE DEPT_INFO
    SET DELETED_AT = NULL
    WHERE DEPT_CODE = #{dept_code}
      AND DELETED_AT IS NOT NULL
  </update>

  <!-- 부서원 목록 조회 -->
  <select id="selectDeptMembers" parameterType="map" resultType="map">
    SELECT
      U.USER_ID,
      U.USER_NAME,
      U.EMAIL,
      U.TEL AS PHONE,
      U.CELL_PHONE,
      U.POSITION_NAME,
      UD.DEPT_CODE,
      D.DEPT_NAME,
      UD.IS_PRIMARY
    FROM USER_DEPT UD
    LEFT JOIN USER_INFO U ON UD.USER_ID = U.USER_ID
    JOIN DEPT_INFO D ON UD.DEPT_CODE = D.DEPT_CODE
    WHERE UD.DEPT_CODE = #{dept_code}
    ORDER BY UD.IS_PRIMARY DESC, U.USER_NAME
  </select>

  <!-- 사용자 검색 (부서원 추가용) -->
  <select id="searchUsers" parameterType="map" resultType="map">
    SELECT
      USER_ID,
      USER_NAME,
      EMAIL,
      POSITION_NAME,
      COMPANY_CODE
    FROM USER_INFO
    WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
      AND (
        USER_ID ILIKE #{search} ESCAPE '\'
        OR USER_NAME ILIKE #{search} ESCAPE '\'
      )
    ORDER BY USER_NAME
    LIMIT 20
  </select>

  <!-- 사용자 존재 확인 -->
  <select id="selectUserById" parameterType="map" resultType="map">
    SELECT USER_ID, USER_NAME
    FROM USER_INFO
    WHERE USER_ID = #{user_id}
  </select>

  <!-- 기존 부서원 확인 (IS_PRIMARY 포함 — 제거 시 자동 승격 판단용) -->
  <select id="selectExistingMember" parameterType="map" resultType="map">
    SELECT USER_ID, DEPT_CODE, IS_PRIMARY
    FROM USER_DEPT
    WHERE USER_ID = #{user_id}
      AND DEPT_CODE = #{dept_code}
  </select>

  <!-- 사용자의 USER_DEPT row 중 첫 번째 (primary 자동 승격용) -->
  <select id="selectFirstUserDept" parameterType="map" resultType="map">
    SELECT USER_ID, DEPT_CODE
    FROM USER_DEPT
    WHERE USER_ID = #{user_id}
    ORDER BY CREATED_DATE ASC
    LIMIT 1
  </select>

  <!-- 사용자의 주 부서 확인 -->
  <select id="selectUserPrimaryDept" parameterType="map" resultType="map">
    SELECT USER_ID, DEPT_CODE
    FROM USER_DEPT
    WHERE USER_ID = #{user_id}
      AND IS_PRIMARY = TRUE
  </select>

  <!-- 부서원 추가 -->
  <insert id="insertDeptMember" parameterType="map">
    INSERT INTO USER_DEPT (USER_ID, DEPT_CODE, IS_PRIMARY, CREATED_DATE)
    VALUES (#{user_id}, #{dept_code}, #{is_primary}, NOW())
  </insert>

  <!-- 부서원 제거 -->
  <delete id="deleteDeptMember" parameterType="map">
    DELETE FROM USER_DEPT
    WHERE USER_ID = #{user_id}
      AND DEPT_CODE = #{dept_code}
  </delete>

  <!-- 사용자 주 부서 전체 해제 -->
  <update id="clearUserPrimaryDept" parameterType="map">
    UPDATE USER_DEPT
    SET IS_PRIMARY = FALSE
    WHERE USER_ID = #{user_id}
  </update>

  <!-- 특정 부서를 주 부서로 설정 -->
  <update id="setUserPrimaryDept" parameterType="map">
    UPDATE USER_DEPT
    SET IS_PRIMARY = TRUE
    WHERE USER_ID = #{user_id}
      AND DEPT_CODE = #{dept_code}
  </update>

  <!-- 부서별 관리자 매핑 (role 단위 sync 용) — 전체 삭제 -->
  <delete id="deleteDeptManagersByDeptAndRole" parameterType="map">
    DELETE FROM DEPT_MANAGERS
     WHERE DEPT_CODE = #{dept_code}
       AND ROLE = #{role}
  </delete>

  <!-- 부서별 관리자 매핑 — bulk insert. parameterType=map, list 와 role 전달. -->
  <insert id="insertDeptManagers" parameterType="map">
    INSERT INTO DEPT_MANAGERS (DEPT_CODE, USER_ID, ROLE, SORT_ORDER) VALUES
    <foreach collection="user_ids" item="uid" index="idx" separator=",">
      (#{dept_code}, #{uid}, #{role}, #{idx} + 1)
    </foreach>
  </insert>

  <!-- 사용자 ID 들이 같은 회사(또는 글로벌 *) 에 실존하는지 검증 — cross-tenant injection 방지 -->
  <select id="selectValidUserIds" parameterType="map" resultType="string">
    SELECT USER_ID FROM USER_INFO
     WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
       AND USER_ID IN
       <foreach collection="user_ids" item="u" open="(" separator="," close=")">#{u}</foreach>
  </select>

</mapper>
