-- V74: 메뉴/권한 전수보강 (AUDIT_P5W3.md §4 갭 리포트 기반)
-- 작성: 2026-05-17
--
-- 작업 범위 (총 20건):
--   (b) menu_permission INSERT 3건: NOTICE/DELETE, SYSTEM_DATA_DICT/DELETE, TERM_SETTLE/APPROVE
--   (c) role_menu_permission INSERT 6건: 위 3 perm × {SUPER_ADMIN, ADMIN}
--   (route) menu UPDATE menu_path + component_path 11건: §4-4 표 기준 App.tsx 라우트 목표값
--
-- ※ 의도적 제외: ADMIN/SYSTEM_ROLE(READ/CREATE/UPDATE/DELETE 4건) +
--               ADMIN/SYSTEM_USER(READ/CREATE/UPDATE 3건) = 7건은 V74에서 보강하지 않는다.
--   사유: V16에서 ADMIN 역할은 GRP_SYSTEM 계열 역할/사용자 관리 메뉴로부터 의도적으로 배제됨.
--   (역할·사용자 관리는 SUPER_ADMIN 전용 보안 경계)
--   이를 "정합 보강"으로 ADMIN에 부여하면 권한상승 회귀에 해당한다.
--   SUPER_ADMIN은 §4-3 기준 이미 해당 grant를 보유하여 기능상 문제 없음.

-- ============================================================
-- 1. menu_permission INSERT — 누락된 perm 3건
--    패턴: V72/V73 과 동일한 NOT EXISTS 서브쿼리
-- ============================================================

-- NOTICE / DELETE
INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT m.menu_id, 'DELETE', '삭제', 40
FROM menu m
WHERE m.menu_code = 'NOTICE'
  AND NOT EXISTS (
      SELECT 1 FROM menu_permission mp
      WHERE mp.menu_id = m.menu_id AND mp.perm_code = 'DELETE'
  );

-- SYSTEM_DATA_DICT / DELETE
INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT m.menu_id, 'DELETE', '삭제', 40
FROM menu m
WHERE m.menu_code = 'SYSTEM_DATA_DICT'
  AND NOT EXISTS (
      SELECT 1 FROM menu_permission mp
      WHERE mp.menu_id = m.menu_id AND mp.perm_code = 'DELETE'
  );

-- TERM_SETTLE / APPROVE
INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT m.menu_id, 'APPROVE', '승인', 50
FROM menu m
WHERE m.menu_code = 'TERM_SETTLE'
  AND NOT EXISTS (
      SELECT 1 FROM menu_permission mp
      WHERE mp.menu_id = m.menu_id AND mp.perm_code = 'APPROVE'
  );

-- ============================================================
-- 2. role_menu_permission INSERT — 위 3 perm × {SUPER_ADMIN, ADMIN} = 6건
--    패턴: ON CONFLICT DO NOTHING (role_id, menu_id, perm_code UNIQUE)
-- ============================================================

-- NOTICE / DELETE — SUPER_ADMIN + ADMIN
INSERT INTO role_menu_permission (role_id, menu_id, perm_code, is_granted)
SELECT r.role_id, mp.menu_id, mp.perm_code, 'Y'
FROM role r
CROSS JOIN menu_permission mp
JOIN menu m ON m.menu_id = mp.menu_id
WHERE r.role_code IN ('SUPER_ADMIN', 'ADMIN')
  AND m.menu_code = 'NOTICE'
  AND mp.perm_code = 'DELETE'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- SYSTEM_DATA_DICT / DELETE — SUPER_ADMIN + ADMIN
INSERT INTO role_menu_permission (role_id, menu_id, perm_code, is_granted)
SELECT r.role_id, mp.menu_id, mp.perm_code, 'Y'
FROM role r
CROSS JOIN menu_permission mp
JOIN menu m ON m.menu_id = mp.menu_id
WHERE r.role_code IN ('SUPER_ADMIN', 'ADMIN')
  AND m.menu_code = 'SYSTEM_DATA_DICT'
  AND mp.perm_code = 'DELETE'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- TERM_SETTLE / APPROVE — SUPER_ADMIN + ADMIN
INSERT INTO role_menu_permission (role_id, menu_id, perm_code, is_granted)
SELECT r.role_id, mp.menu_id, mp.perm_code, 'Y'
FROM role r
CROSS JOIN menu_permission mp
JOIN menu m ON m.menu_id = mp.menu_id
WHERE r.role_code IN ('SUPER_ADMIN', 'ADMIN')
  AND m.menu_code = 'TERM_SETTLE'
  AND mp.perm_code = 'APPROVE'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- ============================================================
-- 3. menu UPDATE — menu_path / component_path 불일치 11건
--    App.tsx 실제 라우트를 정본으로 UPDATE
-- ============================================================

-- commission/reinstatement (REINSTATE_COMM)
UPDATE menu
SET menu_path      = '/commission/reinstatement',
    component_path = 'commission/ReinstatementCommission'
WHERE menu_code = 'REINSTATE_COMM'
  AND menu_path != '/commission/reinstatement';

-- commission/persistency (PERSIST_BONUS)
UPDATE menu
SET menu_path      = '/commission/persistency',
    component_path = 'commission/PersistencyBonus'
WHERE menu_code = 'PERSIST_BONUS'
  AND menu_path != '/commission/persistency';

-- commission/override-ledger (OVERRIDE_LEDGER)
UPDATE menu
SET menu_path      = '/commission/override-ledger',
    component_path = 'commission/OverrideLedger'
WHERE menu_code = 'OVERRIDE_LEDGER'
  AND menu_path != '/commission/override-ledger';

-- agent/contracts (AGENT_CONTRACT)
UPDATE menu
SET menu_path      = '/agent/contracts',
    component_path = 'agent/AgentContracts'
WHERE menu_code = 'AGENT_CONTRACT'
  AND menu_path != '/agent/contracts';

-- agent/licenses (AGENT_LICENSE)
UPDATE menu
SET menu_path      = '/agent/licenses',
    component_path = 'agent/AgentLicenses'
WHERE menu_code = 'AGENT_LICENSE'
  AND menu_path != '/agent/licenses';

-- agent/trainings (AGENT_TRAINING)
UPDATE menu
SET menu_path      = '/agent/trainings',
    component_path = 'agent/AgentTrainings'
WHERE menu_code = 'AGENT_TRAINING'
  AND menu_path != '/agent/trainings';

-- contracts/endorsements (ENDORSE)
UPDATE menu
SET menu_path      = '/contracts/endorsements',
    component_path = 'contracts/ContractEndorsements'
WHERE menu_code = 'ENDORSE'
  AND menu_path != '/contracts/endorsements';

-- contracts/complaints (COMPLAINT)
UPDATE menu
SET menu_path      = '/contracts/complaints',
    component_path = 'contracts/Complaints'
WHERE menu_code = 'COMPLAINT'
  AND menu_path != '/contracts/complaints';

-- system/approvals (APPROVAL)
UPDATE menu
SET menu_path      = '/system/approvals',
    component_path = 'system/Approvals'
WHERE menu_code = 'APPROVAL'
  AND menu_path != '/system/approvals';

-- system/notices (NOTICE)
UPDATE menu
SET menu_path      = '/system/notices',
    component_path = 'system/Notices'
WHERE menu_code = 'NOTICE'
  AND menu_path != '/system/notices';

-- payments/withdraw (WITHDRAW)
UPDATE menu
SET menu_path      = '/payments/withdraw',
    component_path = 'payments/WithdrawRequest'
WHERE menu_code = 'WITHDRAW'
  AND menu_path != '/payments/withdraw';
