<?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.VpdPolicyMapper">
  <sql id="policyColumns">
    ${alias}object_owner,
    ${alias}object_name,
    ${alias}policy_group,
    ${alias}policy_name,
    ${alias}pf_owner AS function_owner,
    ${alias}package AS package_name,
    ${alias}function AS function_name,
    RTRIM(
      CASE WHEN ${alias}sel = 'YES' THEN 'SELECT,' ELSE '' END ||
      CASE WHEN ${alias}ins = 'YES' THEN 'INSERT,' ELSE '' END ||
      CASE WHEN ${alias}upd = 'YES' THEN 'UPDATE,' ELSE '' END ||
      CASE WHEN ${alias}del = 'YES' THEN 'DELETE,' ELSE '' END ||
      CASE WHEN ${alias}idx = 'YES' THEN 'INDEX,' ELSE '' END,
      ','
    ) AS statement_types,
    ${alias}chk_option AS check_option,
    ${alias}enable AS enabled,
    ${alias}static_policy,
    ${alias}policy_type,
    ${alias}long_predicate
  </sql>

  <select id="findPolicies" resultType="com.cloudhandson.vpdbackoffice.domain.vpd.VpdPolicyView">
    SELECT <include refid="policyColumns"><property name="alias" value="p."/></include>
    FROM all_policies p
    WHERE EXISTS (
      SELECT 1
      FROM cb_protected_object o
      WHERE o.enabled_yn = 'Y'
        AND o.owner = p.object_owner
        AND o.object_name = p.object_name
    )
    ORDER BY p.object_owner, p.object_name, p.policy_name
  </select>

  <select id="findVpdTargets" resultType="com.cloudhandson.vpdbackoffice.domain.vpd.VpdTargetView">
    WITH managed_owners AS (
      SELECT USER AS owner FROM dual
      UNION
      SELECT owner FROM cb_protected_object
    ),
    managed_objects AS (
      SELECT owner, object_name, object_type
      FROM all_objects
      WHERE object_type IN ('TABLE', 'VIEW')
        AND (
          (#{owner,jdbcType=VARCHAR} IS NULL AND owner IN (SELECT owner FROM managed_owners))
          OR owner = UPPER(#{owner,jdbcType=VARCHAR})
        )
        AND owner NOT IN ('SYS', 'SYSTEM', 'ORDS_METADATA', 'ORDS_PUBLIC_USER')
        AND owner NOT LIKE 'APEX\_%' ESCAPE '\'
        AND owner NOT LIKE 'C##%'
        AND object_name NOT LIKE 'BIN$%'
    )
    SELECT o.owner,
           o.object_name,
           o.object_type,
           CASE WHEN po.object_id IS NULL THEN 'N' ELSE po.enabled_yn END AS protected_yn,
           po.ords_path,
           po.description,
           COUNT(p.policy_name) AS policy_count,
           LISTAGG(p.policy_name, ', ') WITHIN GROUP (ORDER BY p.policy_name) AS policy_names,
           LISTAGG(
             p.pf_owner ||
             CASE WHEN p.package IS NULL THEN '.' ELSE '.' || p.package || '.' END ||
             p.function,
             ', '
           ) WITHIN GROUP (ORDER BY p.policy_name) AS filter_names
    FROM managed_objects o
    LEFT JOIN cb_protected_object po
      ON po.owner = o.owner
     AND po.object_name = o.object_name
     AND po.enabled_yn = 'Y'
    LEFT JOIN all_policies p
      ON p.object_owner = o.owner
     AND p.object_name = o.object_name
    GROUP BY o.owner, o.object_name, o.object_type, po.object_id, po.enabled_yn, po.ords_path, po.description
    ORDER BY CASE WHEN COUNT(p.policy_name) > 0 THEN 0 ELSE 1 END,
             o.owner,
             o.object_type,
             o.object_name
    FETCH FIRST 500 ROWS ONLY
  </select>

  <select id="findPolicyNameOptions" resultType="string">
    SELECT DISTINCT policy_name
    FROM all_policies
    WHERE policy_name IS NOT NULL
      AND EXISTS (
        SELECT 1
        FROM cb_protected_object o
        WHERE o.enabled_yn = 'Y'
          AND o.owner = all_policies.object_owner
          AND o.object_name = all_policies.object_name
      )
    UNION
    SELECT object_name || '_POLICY'
    FROM cb_protected_object
    WHERE enabled_yn = 'Y'
    ORDER BY 1
  </select>

  <select id="findSchemaOwnerOptions" resultType="string">
    SELECT DISTINCT o.owner
    FROM all_objects o
    JOIN all_users u ON u.username = o.owner
    WHERE o.object_type IN ('TABLE', 'VIEW')
      AND NVL(u.oracle_maintained, 'N') = 'N'
      AND o.owner NOT IN ('SYS', 'SYSTEM', 'ORDS_METADATA', 'ORDS_PUBLIC_USER')
      AND o.owner NOT LIKE 'APEX\_%' ESCAPE '\'
      AND o.owner NOT LIKE 'C##%'
      AND o.object_name NOT LIKE 'BIN$%'
    ORDER BY o.owner
    FETCH FIRST 200 ROWS ONLY
  </select>

  <select id="findOwnerOptions" resultType="string">
    SELECT USER FROM dual
    UNION
    SELECT owner FROM cb_protected_object WHERE enabled_yn = 'Y'
    UNION
    SELECT pf_owner
    FROM all_policies p
    WHERE pf_owner IS NOT NULL
      AND EXISTS (
        SELECT 1
        FROM cb_protected_object o
        WHERE o.enabled_yn = 'Y'
          AND o.owner = p.object_owner
          AND o.object_name = p.object_name
      )
    ORDER BY 1
  </select>

  <select id="findFunctionOptions" resultType="com.cloudhandson.vpdbackoffice.domain.vpd.VpdFunctionOption">
    WITH allowed_owners AS (
      SELECT USER AS owner FROM dual
      UNION
      SELECT owner FROM cb_protected_object WHERE enabled_yn = 'Y'
      UNION
      SELECT pf_owner
      FROM all_policies p
      WHERE pf_owner IS NOT NULL
        AND EXISTS (
          SELECT 1
          FROM cb_protected_object o
          WHERE o.enabled_yn = 'Y'
            AND o.owner = p.object_owner
            AND o.object_name = p.object_name
        )
    ),
    policy_functions AS (
      SELECT pf_owner AS owner,
             package AS package_name,
             function AS function_name
      FROM all_policies p
      WHERE EXISTS (
        SELECT 1
        FROM cb_protected_object o
        WHERE o.enabled_yn = 'Y'
          AND o.owner = p.object_owner
          AND o.object_name = p.object_name
      )
    )
    SELECT owner,
           package_name,
           object_name AS function_name,
           CASE WHEN package_name IS NULL THEN 'FUNCTION' ELSE 'PACKAGE FUNCTION' END AS object_type
    FROM all_arguments
    WHERE owner IN (SELECT owner FROM allowed_owners)
      AND position = 0
      AND data_type = 'VARCHAR2'
      AND object_name IS NOT NULL
      AND (
        REGEXP_LIKE(COALESCE(package_name, '') || '.' || object_name, '(VPD|FILTER|POLICY|PREDICATE|RLS)', 'i')
        OR EXISTS (
          SELECT 1
          FROM policy_functions pf
          WHERE pf.owner = all_arguments.owner
            AND NVL(pf.package_name, '-') = NVL(all_arguments.package_name, '-')
            AND pf.function_name = all_arguments.object_name
        )
      )
    ORDER BY owner, package_name, function_name
    FETCH FIRST 300 ROWS ONLY
  </select>

  <select id="findPolicyTemplateOptions" resultType="com.cloudhandson.vpdbackoffice.domain.vpd.VpdPolicyTemplateOption">
    SELECT policy_name,
           function_owner,
           package_name,
           function_name,
           statement_types,
           enabled,
           check_option
    FROM (
      SELECT DISTINCT
             p.policy_name,
             p.pf_owner AS function_owner,
             p.package AS package_name,
             p.function AS function_name,
             RTRIM(
               CASE WHEN p.sel = 'YES' THEN 'SELECT,' ELSE '' END ||
               CASE WHEN p.ins = 'YES' THEN 'INSERT,' ELSE '' END ||
               CASE WHEN p.upd = 'YES' THEN 'UPDATE,' ELSE '' END ||
               CASE WHEN p.del = 'YES' THEN 'DELETE,' ELSE '' END ||
               CASE WHEN p.idx = 'YES' THEN 'INDEX,' ELSE '' END,
               ','
             ) AS statement_types,
             p.enable AS enabled,
             p.chk_option AS check_option
      FROM all_policies p
      WHERE p.policy_name IS NOT NULL
        AND p.pf_owner IS NOT NULL
        AND p.function IS NOT NULL
        AND EXISTS (
          SELECT 1
          FROM cb_protected_object o
          WHERE o.enabled_yn = 'Y'
            AND o.owner = p.object_owner
            AND o.object_name = p.object_name
        )
    )
    ORDER BY policy_name, function_owner, package_name, function_name
  </select>

  <select id="findSchemaObjects" resultType="com.cloudhandson.vpdbackoffice.domain.vpd.VpdSchemaObjectOption">
    SELECT owner, object_name, object_type
    FROM all_objects
    WHERE owner = UPPER(#{owner,jdbcType=VARCHAR})
      AND object_name NOT LIKE 'BIN$%'
      AND (
        (#{includeTablesYn,jdbcType=VARCHAR} = 'Y' AND object_type = 'TABLE')
        OR (#{includeViewsYn,jdbcType=VARCHAR} = 'Y' AND object_type = 'VIEW')
      )
    ORDER BY object_type, object_name
  </select>

  <select id="findPolicy" resultType="com.cloudhandson.vpdbackoffice.domain.vpd.VpdPolicyView">
    SELECT <include refid="policyColumns"><property name="alias" value="p."/></include>
    FROM all_policies p
    WHERE p.object_owner = UPPER(#{objectOwner,jdbcType=VARCHAR})
      AND p.object_name = UPPER(#{objectName,jdbcType=VARCHAR})
      AND p.policy_name = UPPER(#{policyName,jdbcType=VARCHAR})
      AND EXISTS (
        SELECT 1
        FROM cb_protected_object o
        WHERE o.enabled_yn = 'Y'
          AND o.owner = p.object_owner
          AND o.object_name = p.object_name
      )
  </select>

  <select id="findPoliciesForObject" resultType="com.cloudhandson.vpdbackoffice.domain.vpd.VpdPolicyView">
    SELECT <include refid="policyColumns"><property name="alias" value="p."/></include>
    FROM all_policies p
    WHERE p.object_owner = UPPER(#{objectOwner,jdbcType=VARCHAR})
      AND p.object_name = UPPER(#{objectName,jdbcType=VARCHAR})
    ORDER BY p.policy_name
  </select>

  <select id="findAnyPolicy" resultType="com.cloudhandson.vpdbackoffice.domain.vpd.VpdPolicyView">
    SELECT <include refid="policyColumns"><property name="alias" value="p."/></include>
    FROM all_policies p
    WHERE p.object_owner = UPPER(#{objectOwner,jdbcType=VARCHAR})
      AND p.object_name = UPPER(#{objectName,jdbcType=VARCHAR})
      AND p.policy_name = UPPER(#{policyName,jdbcType=VARCHAR})
  </select>

  <select id="findFunctionSource" resultType="string">
    SELECT LISTAGG(text, '') WITHIN GROUP (ORDER BY line)
    FROM all_source
    WHERE owner = UPPER(#{owner,jdbcType=VARCHAR})
      AND name = UPPER(#{objectName,jdbcType=VARCHAR})
      AND type = UPPER(#{objectType,jdbcType=VARCHAR})
  </select>

  <select id="findPolicyDescription" resultType="string">
    SELECT description
    FROM cb_vpd_policy_note
    WHERE object_owner = UPPER(#{objectOwner,jdbcType=VARCHAR})
      AND object_name = UPPER(#{objectName,jdbcType=VARCHAR})
      AND policy_name = UPPER(#{policyName,jdbcType=VARCHAR})
  </select>

  <select id="findFilterDescription" resultType="string">
    SELECT description
    FROM cb_vpd_filter_note
    WHERE function_owner = UPPER(#{functionOwner,jdbcType=VARCHAR})
      AND function_name = UPPER(#{functionName,jdbcType=VARCHAR})
  </select>

  <select id="findPolicyDescriptions" resultType="com.cloudhandson.vpdbackoffice.domain.vpd.VpdDescriptionNote">
    SELECT object_owner || '.' || object_name || '|' || policy_name AS note_key,
           description
    FROM cb_vpd_policy_note
  </select>

  <select id="findFilterDescriptions" resultType="com.cloudhandson.vpdbackoffice.domain.vpd.VpdDescriptionNote">
    SELECT function_owner || '|' || function_name AS note_key,
           description
    FROM cb_vpd_filter_note
  </select>

  <update id="upsertPolicyDescription">
    MERGE INTO cb_vpd_policy_note dst
    USING (
      SELECT UPPER(#{objectOwner,jdbcType=VARCHAR}) object_owner,
             UPPER(#{objectName,jdbcType=VARCHAR}) object_name,
             UPPER(#{policyName,jdbcType=VARCHAR}) policy_name,
             #{description,jdbcType=VARCHAR} description
      FROM dual
    ) src
    ON (dst.object_owner = src.object_owner
        AND dst.object_name = src.object_name
        AND dst.policy_name = src.policy_name)
    WHEN MATCHED THEN UPDATE SET dst.description = src.description, dst.updated_at = SYSTIMESTAMP
    WHEN NOT MATCHED THEN INSERT (object_owner, object_name, policy_name, description, updated_at)
      VALUES (src.object_owner, src.object_name, src.policy_name, src.description, SYSTIMESTAMP)
  </update>

  <update id="upsertFilterDescription">
    MERGE INTO cb_vpd_filter_note dst
    USING (
      SELECT UPPER(#{functionOwner,jdbcType=VARCHAR}) function_owner,
             UPPER(#{functionName,jdbcType=VARCHAR}) function_name,
             #{description,jdbcType=VARCHAR} description
      FROM dual
    ) src
    ON (dst.function_owner = src.function_owner AND dst.function_name = src.function_name)
    WHEN MATCHED THEN UPDATE SET dst.description = src.description, dst.updated_at = SYSTIMESTAMP
    WHEN NOT MATCHED THEN INSERT (function_owner, function_name, description, updated_at)
      VALUES (src.function_owner, src.function_name, src.description, SYSTIMESTAMP)
  </update>
</mapper>
