-- ============================================================
-- 62_kb_aso_masking_backoffice_metadata.sql
--
-- Creates the metadata used by the masking-rule backoffice pages.
-- Run as ADMIN with SQLcl. This script does not create a DBMS_REDACT
-- policy; run 63_kb_aso_masking_rule_runtime.sql after rules are
-- connected to protected columns.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE OFF
SET FEEDBACK ON

PROMPT === Creating masking-rule metadata tables ===
DECLARE
  PROCEDURE ensure_table(p_name IN VARCHAR2, p_ddl IN CLOB) IS
  BEGIN
    EXECUTE IMMEDIATE p_ddl;
    DBMS_OUTPUT.PUT_LINE('created ' || p_name);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -955 THEN
        DBMS_OUTPUT.PUT_LINE('exists ' || p_name);
      ELSE
        RAISE;
      END IF;
  END;
BEGIN
  ensure_table('CB_MASKING_RULE', q'[
    CREATE TABLE cb_masking_rule (
      rule_id       NUMBER PRIMARY KEY,
      rule_code     VARCHAR2(64) NOT NULL UNIQUE,
      rule_name     VARCHAR2(100) NOT NULL,
      template_code VARCHAR2(30) NOT NULL,
      description   VARCHAR2(400),
      enabled_yn    CHAR(1) DEFAULT 'Y' CHECK (enabled_yn IN ('Y','N')) NOT NULL
    )]');

  ensure_table('CB_COLUMN_MASKING_RULE', q'[
    CREATE TABLE cb_column_masking_rule (
      column_id  NUMBER PRIMARY KEY,
      rule_id    NUMBER NOT NULL,
      updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
    )]');

  ensure_table('CB_USER_MASKING_RULE', q'[
    CREATE TABLE cb_user_masking_rule (
      user_id    NUMBER NOT NULL,
      column_id  NUMBER NOT NULL,
      decision   VARCHAR2(10) NOT NULL CHECK (decision IN ('MASK','UNMASK')),
      active_yn  CHAR(1) DEFAULT 'Y' CHECK (active_yn IN ('Y','N')) NOT NULL,
      updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
      CONSTRAINT cb_user_masking_rule_pk PRIMARY KEY (user_id, column_id)
    )]');
END;
/

PROMPT === Seeding curated masking templates ===
DECLARE
  v_next_rule_id NUMBER;

  PROCEDURE ensure_rule(
      p_code        IN VARCHAR2,
      p_name        IN VARCHAR2,
      p_template    IN VARCHAR2,
      p_description IN VARCHAR2
  ) IS
  BEGIN
    MERGE INTO cb_masking_rule dst
    USING (
      SELECT p_code rule_code,
             p_name rule_name,
             p_template template_code,
             p_description description
      FROM dual
    ) src
    ON (dst.rule_code = src.rule_code)
    WHEN NOT MATCHED THEN
      INSERT (rule_id, rule_code, rule_name, template_code, description, enabled_yn)
      VALUES (v_next_rule_id, src.rule_code, src.rule_name, src.template_code, src.description, 'Y');
    v_next_rule_id := v_next_rule_id + 1;
  END;
BEGIN
  SELECT NVL(MAX(rule_id), 0) + 1 INTO v_next_rule_id FROM cb_masking_rule;
  ensure_rule('MASK_NULLIFY', '값 숨김 (NULL)', 'NULLIFY',
    '값을 NULL로 반환하는 기본 마스킹 방식');
  ensure_rule('MASK_FULL', '전체 마스킹', 'FULL',
    '문자형은 공백, 숫자형은 0으로 반환하는 전체 마스킹 방식');
  ensure_rule('MASK_TEXT_PARTIAL', '문자열 일부 마스킹', 'TEXT_PARTIAL',
    '첫 글자만 보이고 나머지는 가리는 문자열 마스킹 방식');
  ensure_rule('MASK_RRN_PARTIAL', '주민등록번호 부분 마스킹', 'RRN_PARTIAL',
    '앞 6자리만 보이고 나머지는 가리는 식별번호 마스킹 방식');
END;
/
COMMIT;

PROMPT === Metadata ready ===
SELECT rule_id, rule_code, rule_name, template_code, enabled_yn
FROM cb_masking_rule
ORDER BY rule_id;
EXIT