-- V20: 데이터 도메인 사전 + 데이터 사전 시스템

-- ============================================================
-- 1. data_domain (도메인 마스터)
-- ============================================================
CREATE TABLE ga.data_domain (
    domain_code      VARCHAR(50)  NOT NULL,
    domain_name      VARCHAR(100) NOT NULL,
    domain_category  VARCHAR(30)  NOT NULL,   -- IDENTIFIER/PII/MONEY/RATE/DATE/CODE/TEXT/JSON/BOOLEAN
    data_type        VARCHAR(30)  NOT NULL,   -- VARCHAR/DECIMAL/INTEGER/DATE/TIMESTAMP/TEXT/JSONB/CHAR/BIGINT
    length           INT,
    precision        INT,
    scale            INT,
    format_pattern   VARCHAR(100),
    validation_regex VARCHAR(200),
    example_value    VARCHAR(200),
    masking_type     VARCHAR(20),             -- NAME/PHONE/RRN/ACCOUNT/EMAIL/NONE
    encrypted        CHAR(1)      NOT NULL DEFAULT 'N',
    description      TEXT,
    is_active        CHAR(1)      NOT NULL DEFAULT 'Y',
    created_at       TIMESTAMP    NOT NULL DEFAULT NOW(),
    created_by       BIGINT,
    updated_at       TIMESTAMP,
    updated_by       BIGINT,
    CONSTRAINT pk_data_domain PRIMARY KEY (domain_code)
);

CREATE INDEX idx_data_domain_category ON ga.data_domain (domain_category, is_active);

COMMENT ON TABLE  ga.data_domain                IS '데이터 도메인 마스터';
COMMENT ON COLUMN ga.data_domain.domain_code    IS '도메인 코드 (PK)';
COMMENT ON COLUMN ga.data_domain.domain_name    IS '도메인 한글명';
COMMENT ON COLUMN ga.data_domain.domain_category IS '도메인 분류 (IDENTIFIER/PII/MONEY/RATE/DATE/CODE/TEXT/JSON/BOOLEAN)';
COMMENT ON COLUMN ga.data_domain.data_type      IS '물리 데이터 타입 (VARCHAR/DECIMAL/INTEGER/DATE/TIMESTAMP/TEXT/JSONB/CHAR/BIGINT)';
COMMENT ON COLUMN ga.data_domain.length         IS 'VARCHAR 길이';
COMMENT ON COLUMN ga.data_domain.precision      IS 'DECIMAL 정밀도';
COMMENT ON COLUMN ga.data_domain.scale          IS 'DECIMAL 스케일';
COMMENT ON COLUMN ga.data_domain.format_pattern IS '표시 형식 패턴 (예: yyyy-MM-dd, #,##0.00)';
COMMENT ON COLUMN ga.data_domain.validation_regex IS '입력값 검증 정규식';
COMMENT ON COLUMN ga.data_domain.example_value  IS '예시 값';
COMMENT ON COLUMN ga.data_domain.masking_type   IS '마스킹 유형 (NAME/PHONE/RRN/ACCOUNT/EMAIL/NONE)';
COMMENT ON COLUMN ga.data_domain.encrypted      IS 'AES 암호화 여부 (Y/N)';
COMMENT ON COLUMN ga.data_domain.is_active      IS '사용여부 (Y/N)';

-- ============================================================
-- 2. data_dictionary (컬럼-도메인 매핑 + 한글명)
-- ============================================================
CREATE TABLE ga.data_dictionary (
    dict_id        BIGSERIAL    NOT NULL,
    table_name     VARCHAR(100) NOT NULL,
    column_name    VARCHAR(100) NOT NULL,
    domain_code    VARCHAR(50)  REFERENCES ga.data_domain (domain_code),
    column_label   VARCHAR(100),
    description    TEXT,
    is_pk          CHAR(1)      NOT NULL DEFAULT 'N',
    is_fk          CHAR(1)      NOT NULL DEFAULT 'N',
    fk_reference   VARCHAR(150),            -- 'agent.agent_id' 형태
    is_required    CHAR(1)      NOT NULL DEFAULT 'N',
    default_value  VARCHAR(200),
    business_rule  TEXT,
    sample_value   VARCHAR(200),
    sort_order     INT          NOT NULL DEFAULT 0,
    created_at     TIMESTAMP    NOT NULL DEFAULT NOW(),
    created_by     BIGINT,
    updated_at     TIMESTAMP,
    updated_by     BIGINT,
    CONSTRAINT pk_data_dictionary PRIMARY KEY (dict_id),
    CONSTRAINT uq_dict_table_column UNIQUE (table_name, column_name)
);

CREATE INDEX idx_dict_table  ON ga.data_dictionary (table_name);
CREATE INDEX idx_dict_domain ON ga.data_dictionary (domain_code);

COMMENT ON TABLE  ga.data_dictionary               IS '컬럼-도메인 매핑 + 한글명';
COMMENT ON COLUMN ga.data_dictionary.dict_id       IS '사전 ID (PK)';
COMMENT ON COLUMN ga.data_dictionary.table_name    IS '테이블명';
COMMENT ON COLUMN ga.data_dictionary.column_name   IS '컬럼명';
COMMENT ON COLUMN ga.data_dictionary.domain_code   IS '도메인 코드 (FK → data_domain)';
COMMENT ON COLUMN ga.data_dictionary.column_label  IS '컬럼 한글명';
COMMENT ON COLUMN ga.data_dictionary.is_pk         IS 'PK 여부 (Y/N)';
COMMENT ON COLUMN ga.data_dictionary.is_fk         IS 'FK 여부 (Y/N)';
COMMENT ON COLUMN ga.data_dictionary.fk_reference  IS 'FK 참조 (테이블.컬럼)';
COMMENT ON COLUMN ga.data_dictionary.business_rule IS '비즈니스 규칙 (검증/계산)';
COMMENT ON COLUMN ga.data_dictionary.sample_value  IS '샘플 값';

-- ============================================================
-- 3. View: v_data_dictionary_full
-- ============================================================
CREATE OR REPLACE VIEW ga.v_data_dictionary_full AS
SELECT
    c.table_name,
    c.column_name,
    c.ordinal_position,
    c.data_type,
    c.character_maximum_length                                                               AS max_length,
    c.numeric_precision                                                                      AS num_precision,
    c.numeric_scale                                                                          AS num_scale,
    c.is_nullable,
    c.column_default,
    obj_description(
        (c.table_schema || '.' || c.table_name)::regclass, 'pg_class'
    )                                                                                        AS table_comment,
    col_description(
        ((c.table_schema || '.' || c.table_name)::regclass)::oid,
        c.ordinal_position
    )                                                                                        AS column_comment,
    dd.column_label,
    dd.description   AS dict_description,
    dd.domain_code,
    d.domain_name,
    d.domain_category,
    d.format_pattern,
    d.masking_type,
    d.encrypted,
    dd.is_pk,
    dd.is_fk,
    dd.fk_reference,
    dd.business_rule,
    dd.sample_value
FROM information_schema.columns c
LEFT JOIN ga.data_dictionary dd
       ON dd.table_name = c.table_name
      AND dd.column_name = c.column_name
LEFT JOIN ga.data_domain d
       ON d.domain_code = dd.domain_code
WHERE c.table_schema = 'ga'
  AND c.table_name NOT LIKE 'flyway_%'
  AND c.table_name NOT LIKE 'BATCH_%'
ORDER BY c.table_name, c.ordinal_position;

-- ============================================================
-- 4. 초기 도메인 데이터 (~30개)
-- ============================================================

-- IDENTIFIER (식별자)
INSERT INTO ga.data_domain (domain_code, domain_name, domain_category, data_type, length, precision, scale, format_pattern, validation_regex, example_value, masking_type, encrypted, description) VALUES
('DOM_ID_BIGINT',    'ID 식별자',    'IDENTIFIER', 'BIGINT',  NULL, NULL, NULL, NULL,     NULL,            '12345',         'NONE', 'N', '자동증가 대리키 (PK용)'),
('DOM_LICENSE_NO',   '사번/면허번호','IDENTIFIER', 'VARCHAR', 50,   NULL, NULL, NULL,     NULL,            'GA20230001',    'NONE', 'N', '설계사 사번 또는 보험업 면허번호'),
('DOM_POLICY_NO',    '증권번호',     'IDENTIFIER', 'VARCHAR', 50,   NULL, NULL, NULL,     NULL,            '20230100012345','NONE', 'N', '보험사 발급 증권번호'),
('DOM_COMPANY_CODE', '보험사 코드',  'IDENTIFIER', 'VARCHAR', 20,   NULL, NULL, NULL,     NULL,            'KDB',           'NONE', 'N', '보험사 식별 코드'),
('DOM_PRODUCT_CODE', '상품 코드',    'IDENTIFIER', 'VARCHAR', 50,   NULL, NULL, NULL,     NULL,             'PRD-2023-001', 'NONE', 'N', '보험 상품 코드'),
('DOM_SETTLE_MONTH', '정산월',       'IDENTIFIER', 'CHAR',    6,    NULL, NULL, 'YYYYMM', '^\d{6}$',       '202306',        'NONE', 'N', '정산 기준 연월 (YYYYMM)'),
('DOM_BATCH_ID',     '배치 ID',      'IDENTIFIER', 'VARCHAR', 50,   NULL, NULL, NULL,     NULL,            'BATCH-20230601','NONE', 'N', '배치 작업 식별자');

-- PII (개인정보)
INSERT INTO ga.data_domain (domain_code, domain_name, domain_category, data_type, length, precision, scale, format_pattern, validation_regex, example_value, masking_type, encrypted, description) VALUES
('DOM_RRN',        '주민번호',  'PII', 'VARCHAR', 200, NULL, NULL, 'YYYYMMDD-NNNNNNN', '^\d{6}-\d{7}$',            '800101-1234567', 'RRN',     'Y', 'AES 암호화 + RRN 마스킹'),
('DOM_NAME',       '성명',      'PII', 'VARCHAR', 50,  NULL, NULL, NULL,              NULL,                         '홍길동',          'NAME',    'N', '고객/설계사 실명'),
('DOM_PHONE',      '전화번호',  'PII', 'VARCHAR', 20,  NULL, NULL, NULL,              '^\d{2,3}-\d{3,4}-\d{4}$',   '010-1234-5678',  'PHONE',   'N', '연락처'),
('DOM_EMAIL',      '이메일',    'PII', 'VARCHAR', 100, NULL, NULL, NULL,              NULL,                         'user@example.com','EMAIL',  'N', '이메일 주소'),
('DOM_ACCOUNT_NO', '계좌번호',  'PII', 'VARCHAR', 200, NULL, NULL, NULL,              NULL,                         '123-456-789012', 'ACCOUNT', 'Y', 'AES 암호화 + 계좌 마스킹'),
('DOM_BANK_CODE',  '은행코드',  'PII', 'VARCHAR', 10,  NULL, NULL, NULL,              NULL,                         '004',            'NONE',    'N', '금융기관 코드 (은행연합회 기준)');

-- MONEY (금액) — DECIMAL(15,2)
INSERT INTO ga.data_domain (domain_code, domain_name, domain_category, data_type, length, precision, scale, format_pattern, validation_regex, example_value, masking_type, encrypted, description) VALUES
('DOM_AMOUNT',     '금액',      'MONEY', 'DECIMAL', NULL, 15, 2, '#,##0',    NULL, '1,000,000', 'NONE', 'N', '일반 금액'),
('DOM_PREMIUM',    '보험료',    'MONEY', 'DECIMAL', NULL, 15, 2, '#,##0',    NULL, '150,000',   'NONE', 'N', '월 납입 보험료'),
('DOM_COMMISSION', '수수료',    'MONEY', 'DECIMAL', NULL, 15, 2, '#,##0',    NULL, '45,000',    'NONE', 'N', '설계사 수수료 금액'),
('DOM_TAX',        '세액',      'MONEY', 'DECIMAL', NULL, 15, 2, '#,##0',    NULL, '1,485',     'NONE', 'N', '원천징수 세액 (소득세 + 지방소득세)'),
('DOM_NET_AMOUNT', '순지급액',  'MONEY', 'DECIMAL', NULL, 15, 2, '#,##0',    NULL, '43,515',    'NONE', 'N', '세액 공제 후 실지급액');

-- RATE (율) — DECIMAL(7,4)
INSERT INTO ga.data_domain (domain_code, domain_name, domain_category, data_type, length, precision, scale, format_pattern, validation_regex, example_value, masking_type, encrypted, description) VALUES
('DOM_RATE_PCT',   '수수료율(%)', 'RATE', 'DECIMAL', NULL, 7, 4, '#,##0.0000', NULL, '30.0000', 'NONE', 'N', '보험사 지급 수수료율'),
('DOM_PAYOUT_PCT', '지급율(%)',   'RATE', 'DECIMAL', NULL, 7, 4, '#,##0.0000', NULL, '80.0000', 'NONE', 'N', 'GA 내부 설계사 지급율'),
('DOM_TAX_RATE',   '세율(%)',     'RATE', 'DECIMAL', NULL, 7, 4, '#,##0.0000', NULL, '3.3000',  'NONE', 'N', '원천세율 (소득세 3% + 지방소득세 0.3%)');

-- DATE (날짜)
INSERT INTO ga.data_domain (domain_code, domain_name, domain_category, data_type, length, precision, scale, format_pattern, validation_regex, example_value, masking_type, encrypted, description) VALUES
('DOM_DATE',           '날짜',  'DATE', 'DATE',      NULL, NULL, NULL, 'yyyy-MM-dd',          NULL, '2023-06-01',         'NONE', 'N', '일반 날짜'),
('DOM_DATETIME',       '일시',  'DATE', 'TIMESTAMP', NULL, NULL, NULL, 'yyyy-MM-dd HH:mm:ss', NULL, '2023-06-01 09:00:00','NONE', 'N', '일반 일시'),
('DOM_CONTRACT_DATE',  '계약일','DATE', 'DATE',      NULL, NULL, NULL, 'yyyy-MM-dd',          NULL, '2023-06-01',         'NONE', 'N', '보험 계약 체결일'),
('DOM_EFFECTIVE_DATE', '효력일','DATE', 'DATE',      NULL, NULL, NULL, 'yyyy-MM-dd',          NULL, '2023-07-01',         'NONE', 'N', '보험 효력 개시일');

-- CODE (코드)
INSERT INTO ga.data_domain (domain_code, domain_name, domain_category, data_type, length, precision, scale, format_pattern, validation_regex, example_value, masking_type, encrypted, description) VALUES
('DOM_STATUS',         '상태',     'CODE', 'VARCHAR', 20, NULL, NULL, NULL, NULL,       'ACTIVE',  'NONE', 'N', '레코드 상태 코드'),
('DOM_YN_FLAG',        '여부 플래그','CODE', 'CHAR',  1,  NULL, NULL, NULL, '^[YN]$',   'Y',       'NONE', 'N', 'Y/N 이진 플래그'),
('DOM_INSURANCE_TYPE', '보험종류', 'CODE', 'VARCHAR', 30, NULL, NULL, NULL, NULL,       'WHOLE_LIFE','NONE','N', '보험 종류 코드'),
('DOM_PAY_CYCLE',      '납입주기', 'CODE', 'VARCHAR', 20, NULL, NULL, NULL, NULL,       'MONTHLY', 'NONE', 'N', '보험료 납입 주기 코드');

-- TEXT (텍스트)
INSERT INTO ga.data_domain (domain_code, domain_name, domain_category, data_type, length, precision, scale, format_pattern, validation_regex, example_value, masking_type, encrypted, description) VALUES
('DOM_TEXT_SHORT',  '짧은 텍스트', 'TEXT', 'VARCHAR', 200,  NULL, NULL, NULL, NULL, '간단한 메모', 'NONE', 'N', '200자 이내 단문'),
('DOM_TEXT_LONG',   '긴 텍스트',   'TEXT', 'TEXT',    NULL, NULL, NULL, NULL, NULL, '상세 내용...',  'NONE', 'N', '제한 없는 장문'),
('DOM_DESCRIPTION', '설명',        'TEXT', 'VARCHAR', 500,  NULL, NULL, NULL, NULL, '비고 내용',  'NONE', 'N', '500자 이내 설명/비고');

-- JSON / 기타
INSERT INTO ga.data_domain (domain_code, domain_name, domain_category, data_type, length, precision, scale, format_pattern, validation_regex, example_value, masking_type, encrypted, description) VALUES
('DOM_JSONB',       'JSON 데이터',    'JSON',       'JSONB',  NULL, NULL, NULL, NULL, NULL, '{"key":"value"}', 'NONE', 'N', 'PostgreSQL JSONB 타입'),
('DOM_AUDIT_USER',  '감사 사용자 ID', 'IDENTIFIER', 'BIGINT', NULL, NULL, NULL, NULL, NULL, '1',              'NONE', 'N', '생성자/수정자 user_id');

-- ============================================================
-- 5. 초기 데이터 사전 매핑 (~50개)
-- ============================================================
INSERT INTO ga.data_dictionary
    (table_name, column_name, domain_code, column_label, is_pk, is_fk, fk_reference, is_required)
VALUES
-- agent
('agent', 'agent_id',    'DOM_ID_BIGINT',  '설계사ID', 'Y', 'N', NULL,                   'Y'),
('agent', 'org_id',      'DOM_ID_BIGINT',  '조직ID',   'N', 'Y', 'organization.org_id',  'Y'),
('agent', 'agent_name',  'DOM_NAME',       '설계사명', 'N', 'N', NULL,                   'Y'),
('agent', 'resident_no', 'DOM_RRN',        '주민번호', 'N', 'N', NULL,                   'N'),
('agent', 'license_no',  'DOM_LICENSE_NO', '사번',     'N', 'N', NULL,                   'N'),
('agent', 'phone',       'DOM_PHONE',      '전화번호', 'N', 'N', NULL,                   'N'),
('agent', 'email',       'DOM_EMAIL',      '이메일',   'N', 'N', NULL,                   'N'),
('agent', 'account_no',  'DOM_ACCOUNT_NO', '계좌번호', 'N', 'N', NULL,                   'N'),
('agent', 'status',      'DOM_STATUS',     '상태',     'N', 'N', NULL,                   'Y'),
('agent', 'join_date',   'DOM_DATE',       '입사일',   'N', 'N', NULL,                   'N'),

-- contract
('contract', 'contract_id',   'DOM_ID_BIGINT',     '계약ID',  'Y', 'N', NULL,             'Y'),
('contract', 'agent_id',      'DOM_ID_BIGINT',     '설계사ID','N', 'Y', 'agent.agent_id', 'Y'),
('contract', 'product_id',    'DOM_ID_BIGINT',     '상품ID',  'N', 'Y', 'product.product_id','Y'),
('contract', 'policy_no',     'DOM_POLICY_NO',     '증권번호','N', 'N', NULL,             'Y'),
('contract', 'premium',       'DOM_PREMIUM',       '보험료',  'N', 'N', NULL,             'N'),
('contract', 'pay_cycle',     'DOM_PAY_CYCLE',     '납입주기','N', 'N', NULL,             'N'),
('contract', 'contract_date', 'DOM_CONTRACT_DATE', '계약일',  'N', 'N', NULL,             'N'),

-- recruit_ledger
('recruit_ledger', 'ledger_id',      'DOM_ID_BIGINT',   '원장ID',     'Y', 'N', NULL,             'Y'),
('recruit_ledger', 'agent_id',       'DOM_ID_BIGINT',   '설계사ID',   'N', 'Y', 'agent.agent_id', 'Y'),
('recruit_ledger', 'policy_no',      'DOM_POLICY_NO',   '증권번호',   'N', 'N', NULL,             'Y'),
('recruit_ledger', 'premium',        'DOM_PREMIUM',     '보험료',     'N', 'N', NULL,             'N'),
('recruit_ledger', 'company_rate',   'DOM_RATE_PCT',    '회사율',     'N', 'N', NULL,             'N'),
('recruit_ledger', 'company_amount', 'DOM_COMMISSION',  '회사수수료', 'N', 'N', NULL,             'N'),
('recruit_ledger', 'payout_rate',    'DOM_PAYOUT_PCT',  '지급율',     'N', 'N', NULL,             'N'),
('recruit_ledger', 'agent_amount',   'DOM_COMMISSION',  '설계사수수료','N','N', NULL,             'N'),
('recruit_ledger', 'tax_amount',     'DOM_TAX',         '세액',       'N', 'N', NULL,             'N'),
('recruit_ledger', 'settle_month',   'DOM_SETTLE_MONTH','정산월',     'N', 'N', NULL,             'Y'),
('recruit_ledger', 'status',         'DOM_STATUS',      '상태',       'N', 'N', NULL,             'Y'),

-- settle_master
('settle_master', 'settle_id',      'DOM_ID_BIGINT',   '정산ID',   'Y', 'N', NULL,             'Y'),
('settle_master', 'agent_id',       'DOM_ID_BIGINT',   '설계사ID', 'N', 'Y', 'agent.agent_id', 'Y'),
('settle_master', 'settle_month',   'DOM_SETTLE_MONTH','정산월',   'N', 'N', NULL,             'Y'),
('settle_master', 'recruit_total',  'DOM_COMMISSION',  '모집합계', 'N', 'N', NULL,             'N'),
('settle_master', 'maintain_total', 'DOM_COMMISSION',  '유지합계', 'N', 'N', NULL,             'N'),
('settle_master', 'gross_amount',   'DOM_AMOUNT',      '총액',     'N', 'N', NULL,             'N'),
('settle_master', 'tax_amount',     'DOM_TAX',         '세액',     'N', 'N', NULL,             'N'),
('settle_master', 'net_amount',     'DOM_NET_AMOUNT',  '순지급액', 'N', 'N', NULL,             'N'),
('settle_master', 'status',         'DOM_STATUS',      '상태',     'N', 'N', NULL,             'Y'),

-- payment
('payment', 'payment_id', 'DOM_ID_BIGINT',  '지급ID',  'Y', 'N', NULL,                        'Y'),
('payment', 'settle_id',  'DOM_ID_BIGINT',  '정산ID',  'N', 'Y', 'settle_master.settle_id',   'Y'),
('payment', 'agent_id',   'DOM_ID_BIGINT',  '설계사ID','N', 'Y', 'agent.agent_id',             'Y'),
('payment', 'bank_code',  'DOM_BANK_CODE',  '은행코드','N', 'N', NULL,                        'N'),
('payment', 'account_no', 'DOM_ACCOUNT_NO', '계좌번호','N', 'N', NULL,                        'N'),
('payment', 'pay_amount', 'DOM_AMOUNT',     '지급액',  'N', 'N', NULL,                        'N')

ON CONFLICT (table_name, column_name) DO NOTHING;

-- ============================================================
-- 6. 메뉴 등록 (GRP_SYSTEM 하위)
-- ============================================================
INSERT INTO menu (menu_code, menu_name, menu_type, menu_path, component_path, menu_icon,
                  menu_level, sort_order, is_visible, is_active, parent_menu_id, description)
VALUES
(
    'SYSTEM_DATA_DOMAIN',
    '데이터 도메인 사전',
    'PAGE',
    '/system/data-domains',
    'system/DataDomainList',
    'database',
    2,
    8,
    'Y',
    'Y',
    (SELECT menu_id FROM menu WHERE menu_code = 'GRP_SYSTEM'),
    '비즈니스 도메인 정의 (주민번호/금액/율 등)'
),
(
    'SYSTEM_DATA_DICT',
    '데이터 사전',
    'PAGE',
    '/system/data-dictionary',
    'system/DataDictionary',
    'book',
    2,
    9,
    'Y',
    'Y',
    (SELECT menu_id FROM menu WHERE menu_code = 'GRP_SYSTEM'),
    '테이블/컬럼별 한글명 + 도메인 매핑'
)
ON CONFLICT (menu_code) DO NOTHING;

-- ============================================================
-- 7. 메뉴 권한 정의
-- ============================================================

-- SYSTEM_DATA_DOMAIN : READ / CREATE / UPDATE / DELETE
INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT menu_id, 'READ',   '조회', 1 FROM menu WHERE menu_code = 'SYSTEM_DATA_DOMAIN'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT menu_id, 'CREATE', '등록', 2 FROM menu WHERE menu_code = 'SYSTEM_DATA_DOMAIN'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT menu_id, 'UPDATE', '수정', 3 FROM menu WHERE menu_code = 'SYSTEM_DATA_DOMAIN'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT menu_id, 'DELETE', '삭제', 4 FROM menu WHERE menu_code = 'SYSTEM_DATA_DOMAIN'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- SYSTEM_DATA_DICT : READ / CREATE / UPDATE (사전은 DELETE 없음)
INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT menu_id, 'READ',   '조회', 1 FROM menu WHERE menu_code = 'SYSTEM_DATA_DICT'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT menu_id, 'CREATE', '등록', 2 FROM menu WHERE menu_code = 'SYSTEM_DATA_DICT'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT menu_id, 'UPDATE', '수정', 3 FROM menu WHERE menu_code = 'SYSTEM_DATA_DICT'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- ============================================================
-- 8. 역할-메뉴 권한 부여
-- ============================================================

-- SUPER_ADMIN / ADMIN : SYSTEM_DATA_DOMAIN 모든 권한 (READ/CREATE/UPDATE/DELETE)
INSERT INTO role_menu_permission (role_id, menu_id, perm_code, is_granted)
SELECT r.role_id, m.menu_id, p.perm_code, 'Y'
  FROM role r
  CROSS JOIN menu m
  CROSS JOIN (VALUES ('READ'), ('CREATE'), ('UPDATE'), ('DELETE')) AS p(perm_code)
 WHERE r.role_code IN ('SUPER_ADMIN', 'ADMIN')
   AND m.menu_code = 'SYSTEM_DATA_DOMAIN'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- SUPER_ADMIN / ADMIN : SYSTEM_DATA_DICT 모든 권한 (READ/CREATE/UPDATE)
INSERT INTO role_menu_permission (role_id, menu_id, perm_code, is_granted)
SELECT r.role_id, m.menu_id, p.perm_code, 'Y'
  FROM role r
  CROSS JOIN menu m
  CROSS JOIN (VALUES ('READ'), ('CREATE'), ('UPDATE')) AS p(perm_code)
 WHERE r.role_code IN ('SUPER_ADMIN', 'ADMIN')
   AND m.menu_code = 'SYSTEM_DATA_DICT'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- MANAGER : 두 메뉴 모두 READ 만
INSERT INTO role_menu_permission (role_id, menu_id, perm_code, is_granted)
SELECT r.role_id, m.menu_id, 'READ', 'Y'
  FROM role r
  CROSS JOIN menu m
 WHERE r.role_code = 'MANAGER'
   AND m.menu_code IN ('SYSTEM_DATA_DOMAIN', 'SYSTEM_DATA_DICT')
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;
