<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cloudhandson.vpdbackoffice.mapper.PermissionMapper">
  <select id="findRoles" resultType="com.cloudhandson.vpdbackoffice.domain.permission.AppRole">
    SELECT role_id,
           role_name,
           description,
           max_sensitivity_level
    FROM hmm_access_roles
    WHERE active_yn = 'Y'
    ORDER BY role_name
  </select>

  <select id="findRole" resultType="com.cloudhandson.vpdbackoffice.domain.permission.AppRole">
    SELECT role_id,
           role_name,
           description,
           max_sensitivity_level
    FROM hmm_access_roles
    WHERE role_id = #{roleId}
  </select>

  <select id="nextRoleId" resultType="long">
    SELECT NVL(MAX(role_id), 0) + 1 FROM hmm_access_roles
  </select>

  <insert id="insertRole">
    INSERT INTO hmm_access_roles (role_id, role_name, description, max_sensitivity_level, active_yn)
    VALUES (
      #{roleId,jdbcType=NUMERIC},
      UPPER(#{roleName,jdbcType=VARCHAR}),
      #{description,jdbcType=VARCHAR},
      #{maxSensitivityLevel,jdbcType=VARCHAR},
      'Y'
    )
  </insert>

  <update id="updateRoleMaxSensitivity">
    UPDATE hmm_access_roles
    SET max_sensitivity_level = #{maxSensitivityLevel,jdbcType=VARCHAR}
    WHERE role_id = #{roleId,jdbcType=NUMERIC}
  </update>

  <delete id="deleteRole">
    DELETE FROM hmm_access_roles
    WHERE role_id = #{roleId,jdbcType=NUMERIC}
  </delete>

  <select id="countUserRolesByRoleId" resultType="int">
    SELECT COUNT(*)
    FROM hmm_employee_access_roles
    WHERE role_id = #{roleId,jdbcType=NUMERIC}
  </select>

  <select id="countGroupRolesByRoleId" resultType="int">
    SELECT COUNT(*)
    FROM hmm_group_access_roles
    WHERE role_id = #{roleId,jdbcType=NUMERIC}
  </select>

  <select id="countPermissionsByRoleId" resultType="int">
    SELECT COUNT(*)
    FROM cb_permission
    WHERE role_id = #{roleId,jdbcType=NUMERIC}
  </select>

  <select id="findPermissionViews" resultType="com.cloudhandson.vpdbackoffice.domain.permission.PermissionView">
    SELECT p.perm_id AS permission_id,
           r.role_id,
           r.role_name,
           NVL(o.object_id, -1) AS object_id,
           p.target_name AS object_name,
           p.action_name AS action,
           NVL(p.permission_effect, 'ALLOW') AS permission_effect,
           LISTAGG(
             NVL2(pr.rule_column, pr.rule_column || ' ', '') ||
             pr.rule_type ||
             NVL2(pr.rule_value, ' ' || pr.rule_value, ''),
             ', '
           ) WITHIN GROUP (ORDER BY pr.rule_id) AS rules,
           (
             SELECT LISTAGG(pc.column_name, ', ')
                      WITHIN GROUP (ORDER BY pc.column_name)
             FROM cb_permission_column pc
             WHERE pc.permission_id = p.perm_id
           ) AS visible_columns,
           (
             SELECT LISTAGG(
                      CASE
                        WHEN pr2.rule_type = 'ALL' THEN '1 = 1'
                        WHEN pr2.rule_type = 'MANAGED_TEAM' THEN
                          NVL(pr2.rule_column, 'EMPLOYEE_ID')
                          || ' IN (current employee and direct reports)'
                        WHEN pr2.rule_type = 'SELF' THEN
                          NVL(pr2.rule_column, 'EMPLOYEE_ID')
                          || ' = SYS_CONTEXT(HMM_ACCESS_CTX.EMPLOYEE_ID)'
                        WHEN pr2.rule_type = 'STATIC_SQL' THEN pr2.rule_value
                        ELSE NVL2(pr2.rule_column, pr2.rule_column || ' ', '')
                          || pr2.rule_type
                          || NVL2(pr2.rule_value, ' ' || pr2.rule_value, '')
                      END,
                      CHR(10) || 'AND '
                    ) WITHIN GROUP (ORDER BY pr2.rule_id)
             FROM cb_permission_rule pr2
             WHERE pr2.perm_id = p.perm_id
           ) AS filter_preview,
           '컬럼 원문/마스킹은 컬럼 마스킹에서 관리' AS null_policy_preview
    FROM cb_permission p
    JOIN hmm_access_roles r
      ON r.role_id = p.role_id
    LEFT JOIN cb_protected_object o
      ON o.object_name = p.target_name
    LEFT JOIN cb_permission_rule pr
      ON pr.perm_id = p.perm_id
    GROUP BY
      p.perm_id,
      r.role_id,
      r.role_name,
      o.object_id,
      p.target_name,
      p.action_name,
      p.permission_effect
    ORDER BY r.role_name, p.target_name
  </select>

  <select id="findPermissionId" resultType="long">
    SELECT p.perm_id
    FROM cb_permission p
    JOIN cb_protected_object o ON o.object_name = p.target_name
    WHERE p.role_id = #{roleId}
      AND o.object_id = #{objectId}
  </select>

  <select id="findObjectIdByPermissionId" resultType="long">
    SELECT o.object_id
    FROM cb_permission p
    JOIN cb_protected_object o ON o.object_name = p.target_name
    WHERE p.perm_id = #{permissionId}
  </select>

  <select id="countPermissionsByObjectId" resultType="int">
    SELECT COUNT(*)
    FROM cb_permission p
    JOIN cb_protected_object o ON o.object_name = p.target_name
    WHERE o.object_id = #{objectId}
  </select>

  <select id="findPermissionSet" resultType="com.cloudhandson.vpdbackoffice.domain.permission.PermissionSet">
    SELECT p.perm_id AS permission_id,
           p.role_id,
           o.object_id,
           p.action_name AS action,
           NVL(p.permission_effect, 'ALLOW') AS permission_effect
    FROM cb_permission p
    JOIN cb_protected_object o ON o.object_name = p.target_name
    WHERE p.role_id = #{roleId}
      AND o.object_id = #{objectId}
  </select>

  <select id="nextPermissionId" resultType="long">
    SELECT cb_permission_seq.NEXTVAL FROM dual
  </select>

  <select id="nextRuleId" resultType="long">
    SELECT cb_permission_rule_seq.NEXTVAL FROM dual
  </select>

  <insert id="insertPermission">
    INSERT INTO cb_permission (perm_id, role_id, target_name, action_name, permission_effect)
    SELECT #{permissionId}, #{roleId}, object_name, #{action}, #{permissionEffect}
    FROM cb_protected_object
    WHERE object_id = #{objectId}
  </insert>

  <update id="updatePermissionAction">
    UPDATE cb_permission
    SET action_name = #{action}
    WHERE perm_id = #{permissionId}
  </update>

  <update id="updatePermissionEffect">
    UPDATE cb_permission
    SET permission_effect = #{permissionEffect}
    WHERE perm_id = #{permissionId}
  </update>

  <delete id="deleteRules">
    DELETE FROM cb_permission_rule
    WHERE perm_id = #{permissionId}
  </delete>

  <insert id="insertRule" parameterType="com.cloudhandson.vpdbackoffice.domain.permission.PermissionRule">
    INSERT INTO cb_permission_rule (rule_id, perm_id, rule_column, rule_type, rule_value)
    VALUES (
      #{ruleId,jdbcType=NUMERIC},
      #{permissionId,jdbcType=NUMERIC},
      #{ruleColumn,jdbcType=VARCHAR},
      #{ruleType,jdbcType=VARCHAR},
      #{ruleValue,jdbcType=VARCHAR}
    )
  </insert>

  <delete id="deleteVisibleColumns">
    DELETE FROM cb_permission_column
    WHERE permission_id = #{permissionId}
  </delete>

  <insert id="insertVisibleColumn">
    INSERT INTO cb_permission_column (permission_id, column_name)
    VALUES (#{permissionId}, #{columnName})
  </insert>

  <delete id="deletePermission">
    DELETE FROM cb_permission
    WHERE perm_id = #{permissionId}
  </delete>
</mapper>
