<?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.ProtectedObjectMapper">
  <select id="findEnabled" resultType="com.cloudhandson.vpdbackoffice.domain.protectedobject.ProtectedObject">
    SELECT object_id, owner, object_name, ords_path, enabled_yn, description
    FROM cb_protected_object
    WHERE enabled_yn = 'Y'
    ORDER BY owner, object_name
  </select>

  <select id="findEnabledWithPermissions" resultType="com.cloudhandson.vpdbackoffice.domain.protectedobject.ProtectedObject">
    SELECT DISTINCT po.object_id, po.owner, po.object_name, po.ords_path, po.enabled_yn, po.description
    FROM cb_protected_object po
    JOIN cb_permission p ON p.target_name = po.object_name
    WHERE po.enabled_yn = 'Y'
    ORDER BY po.owner, po.object_name
  </select>

  <select id="findById" resultType="com.cloudhandson.vpdbackoffice.domain.protectedobject.ProtectedObject">
    SELECT object_id, owner, object_name, ords_path, enabled_yn, description
    FROM cb_protected_object
    WHERE object_id = #{objectId}
  </select>

  <select id="findByOwnerAndName" resultType="com.cloudhandson.vpdbackoffice.domain.protectedobject.ProtectedObject">
    SELECT object_id, owner, object_name, ords_path, enabled_yn, description
    FROM cb_protected_object
    WHERE owner = UPPER(#{owner,jdbcType=VARCHAR})
      AND object_name = UPPER(#{objectName,jdbcType=VARCHAR})
  </select>

  <select id="findDatabaseObjects" resultType="com.cloudhandson.vpdbackoffice.domain.protectedobject.DatabaseObjectOption">
    SELECT owner, object_name, object_type
    FROM all_objects
    WHERE object_type IN ('TABLE', 'VIEW')
      AND owner NOT IN ('SYS', 'SYSTEM', 'ORDS_METADATA', 'ORDS_PUBLIC_USER')
      AND object_name NOT LIKE 'BIN$%'
      AND NOT EXISTS (
        SELECT 1
        FROM cb_protected_object po
        JOIN cb_permission p ON p.target_name = po.object_name
        WHERE po.owner = all_objects.owner
          AND po.object_name = all_objects.object_name
          AND po.enabled_yn = 'Y'
      )
    ORDER BY owner, object_type, object_name
    FETCH FIRST 500 ROWS ONLY
  </select>

  <select id="findDatabaseColumns" resultType="string">
    SELECT column_name
    FROM all_tab_columns
    WHERE owner = UPPER(#{owner,jdbcType=VARCHAR})
      AND table_name = UPPER(#{objectName,jdbcType=VARCHAR})
    ORDER BY column_id
  </select>

  <select id="findColumns" resultType="com.cloudhandson.vpdbackoffice.domain.protectedobject.ProtectedColumn">
    SELECT column_id,
           object_id,
           column_name,
           sensitive_yn,
           visible_role_id,
           NVL(sensitivity_level, CASE sensitive_yn WHEN 'Y' THEN 'CONFIDENTIAL' ELSE 'PUBLIC' END) AS sensitivity_level,
           NVL(redaction_method, CASE sensitive_yn WHEN 'Y' THEN 'NULLIFY' ELSE 'NONE' END) AS redaction_method
    FROM cb_protected_column
    WHERE object_id = #{objectId}
    ORDER BY column_id
  </select>

  <select id="findColumnsByObjectIds" resultType="com.cloudhandson.vpdbackoffice.domain.protectedobject.ProtectedColumn">
    SELECT column_id,
           object_id,
           column_name,
           sensitive_yn,
           visible_role_id,
           NVL(sensitivity_level, CASE sensitive_yn WHEN 'Y' THEN 'CONFIDENTIAL' ELSE 'PUBLIC' END) AS sensitivity_level,
           NVL(redaction_method, CASE sensitive_yn WHEN 'Y' THEN 'NULLIFY' ELSE 'NONE' END) AS redaction_method
    FROM cb_protected_column
    WHERE object_id IN
    <foreach collection="objectIds" item="objectId" open="(" separator="," close=")">
      #{objectId,jdbcType=NUMERIC}
    </foreach>
    ORDER BY object_id, column_id
  </select>

  <select id="findColumnById" resultType="com.cloudhandson.vpdbackoffice.domain.protectedobject.ProtectedColumn">
    SELECT column_id,
           object_id,
           column_name,
           sensitive_yn,
           visible_role_id,
           NVL(sensitivity_level, CASE sensitive_yn WHEN 'Y' THEN 'CONFIDENTIAL' ELSE 'PUBLIC' END) AS sensitivity_level,
           NVL(redaction_method, CASE sensitive_yn WHEN 'Y' THEN 'NULLIFY' ELSE 'NONE' END) AS redaction_method
    FROM cb_protected_column
    WHERE column_id = #{columnId}
  </select>

  <select id="findColumnByObjectAndName" resultType="com.cloudhandson.vpdbackoffice.domain.protectedobject.ProtectedColumn">
    SELECT column_id,
           object_id,
           column_name,
           sensitive_yn,
           visible_role_id,
           NVL(sensitivity_level, CASE sensitive_yn WHEN 'Y' THEN 'CONFIDENTIAL' ELSE 'PUBLIC' END) AS sensitivity_level,
           NVL(redaction_method, CASE sensitive_yn WHEN 'Y' THEN 'NULLIFY' ELSE 'NONE' END) AS redaction_method
    FROM cb_protected_column
    WHERE object_id = #{objectId}
      AND column_name = UPPER(#{columnName,jdbcType=VARCHAR})
  </select>

  <select id="nextObjectId" resultType="long">
    SELECT NVL(MAX(object_id), 0) + 1 FROM cb_protected_object
  </select>

  <select id="nextColumnId" resultType="long">
    SELECT NVL(MAX(column_id), 0) + 1 FROM cb_protected_column
  </select>

  <insert id="insertObject">
    INSERT INTO cb_protected_object (object_id, owner, object_name, ords_path, enabled_yn, description)
    VALUES (
      #{objectId},
      UPPER(#{command.owner}),
      UPPER(#{command.objectName}),
      #{command.ordsPath},
      'Y',
      #{command.description,jdbcType=VARCHAR}
    )
  </insert>

  <insert id="insertColumn">
    INSERT INTO cb_protected_column (
      column_id, object_id, column_name, sensitive_yn, sensitivity_level, redaction_method
    )
    VALUES (
      #{columnId}, #{objectId}, UPPER(#{columnName}), #{sensitiveYn}, #{sensitivityLevel}, #{redactionMethod}
    )
  </insert>

  <update id="updateColumnPolicy">
    UPDATE cb_protected_column
    SET sensitivity_level = #{sensitivityLevel,jdbcType=VARCHAR},
        redaction_method = #{redactionMethod,jdbcType=VARCHAR},
        sensitive_yn = CASE
          WHEN #{sensitivityLevel,jdbcType=VARCHAR} = 'PUBLIC'
           AND #{redactionMethod,jdbcType=VARCHAR} = 'NONE' THEN 'N'
          ELSE 'Y'
        END
    WHERE column_id = #{columnId,jdbcType=NUMERIC}
  </update>

  <update id="updateOrdsPath">
    UPDATE cb_protected_object
    SET ords_path = #{ordsPath,jdbcType=VARCHAR}
    WHERE object_id = #{objectId,jdbcType=NUMERIC}
  </update>

  <update id="updateDescription">
    UPDATE cb_protected_object
    SET description = #{description,jdbcType=VARCHAR}
    WHERE object_id = #{objectId,jdbcType=NUMERIC}
  </update>

  <update id="enableObject">
    UPDATE cb_protected_object
    SET enabled_yn = 'Y'
    WHERE object_id = #{objectId}
  </update>

  <update id="disableObject">
    UPDATE cb_protected_object
    SET enabled_yn = 'N'
    WHERE object_id = #{objectId}
  </update>
</mapper>
