-- V18: 메뉴 트리 구조 (대메뉴 8개 신설 + 25개 PAGE 를 자식으로 매핑)

-- 1) 대메뉴 (DIRECTORY 타입) 추가 -- 기존 menu_code 충돌 회피
INSERT INTO menu (menu_code, menu_name, menu_type, menu_icon, menu_level, sort_order, is_visible, is_active, parent_menu_id, description)
VALUES
    ('GRP_ORG',     '조직 / 인사',   'DIRECTORY', 'users-group',  1, 20, 'Y', 'Y', NULL, '조직 트리 / 설계사 관리'),
    ('GRP_PRD',     '상품 / 계약',   'DIRECTORY', 'briefcase',    1, 30, 'Y', 'Y', NULL, '보험사 / 상품 / 계약 관리'),
    ('GRP_RULE',    '수수료 규정',   'DIRECTORY', 'receipt',      1, 40, 'Y', 'Y', NULL, '수수료율 / 지급율 / 오버라이드 / 환수'),
    ('GRP_RECEIVE', '데이터 수신',   'DIRECTORY', 'database',     1, 50, 'Y', 'Y', NULL, '보험사 데이터 수신 / 매핑'),
    ('GRP_LEDGER',  '원장 관리',     'DIRECTORY', 'file-invoice', 1, 60, 'Y', 'Y', NULL, '모집 / 유지 / 예외 원장'),
    ('GRP_SETTLE',  '정산 / 지급',   'DIRECTORY', 'calculator',   1, 70, 'Y', 'Y', NULL, '정산 결과 / 배치 / 지급'),
    ('GRP_REPORT',  '리포트',        'DIRECTORY', 'chart-bar',    1, 80, 'Y', 'Y', NULL, '실적 / 조직 / 환수 위험'),
    ('GRP_SYSTEM',  '시스템 관리',   'DIRECTORY', 'settings',     1, 99, 'Y', 'Y', NULL, '사용자 / 권한 / 코드 / 로그')
ON CONFLICT (menu_code) DO NOTHING;

-- 2) 자식 PAGE 메뉴들의 parent_menu_id + menu_level 갱신
UPDATE menu SET parent_menu_id = (SELECT menu_id FROM menu WHERE menu_code = 'GRP_ORG'),     menu_level = 2 WHERE menu_code IN ('ORG_TREE', 'ORG_AGENT');
UPDATE menu SET parent_menu_id = (SELECT menu_id FROM menu WHERE menu_code = 'GRP_PRD'),     menu_level = 2 WHERE menu_code IN ('COMPANY', 'PRODUCT', 'CONTRACT_LIST');
UPDATE menu SET parent_menu_id = (SELECT menu_id FROM menu WHERE menu_code = 'GRP_RULE'),    menu_level = 2 WHERE menu_code IN ('RULE_COMMISSION', 'RULE_PAYOUT', 'RULE_OVERRIDE', 'RULE_CHARGEBACK', 'RULE_EXCEPTION');
UPDATE menu SET parent_menu_id = (SELECT menu_id FROM menu WHERE menu_code = 'GRP_RECEIVE'), menu_level = 2 WHERE menu_code IN ('RECEIVE_MAPPING', 'RECEIVE_DATA');
UPDATE menu SET parent_menu_id = (SELECT menu_id FROM menu WHERE menu_code = 'GRP_LEDGER'),  menu_level = 2 WHERE menu_code IN ('LEDGER_RECRUIT', 'LEDGER_MAINTAIN', 'LEDGER_EXCEPTION');
UPDATE menu SET parent_menu_id = (SELECT menu_id FROM menu WHERE menu_code = 'GRP_SETTLE'),  menu_level = 2 WHERE menu_code IN ('SETTLE_LIST', 'BATCH_RUN', 'PAYMENT');
UPDATE menu SET parent_menu_id = (SELECT menu_id FROM menu WHERE menu_code = 'GRP_SYSTEM'),  menu_level = 2 WHERE menu_code IN ('SYSTEM_USER', 'SYSTEM_ROLE', 'SYSTEM_MENU', 'SYSTEM_CODE', 'SYSTEM_CONFIG', 'SYSTEM_LOG');

-- 3) 대시보드는 root, sort_order 10 으로 최상단
UPDATE menu SET sort_order = 10 WHERE menu_code = 'DASHBOARD';

-- 4) 자식 PAGE 의 그룹 내 sort_order 정렬
UPDATE menu SET sort_order = 1 WHERE menu_code = 'ORG_TREE';
UPDATE menu SET sort_order = 2 WHERE menu_code = 'ORG_AGENT';
UPDATE menu SET sort_order = 1 WHERE menu_code = 'COMPANY';
UPDATE menu SET sort_order = 2 WHERE menu_code = 'PRODUCT';
UPDATE menu SET sort_order = 3 WHERE menu_code = 'CONTRACT_LIST';
UPDATE menu SET sort_order = 1 WHERE menu_code = 'RULE_COMMISSION';
UPDATE menu SET sort_order = 2 WHERE menu_code = 'RULE_PAYOUT';
UPDATE menu SET sort_order = 3 WHERE menu_code = 'RULE_OVERRIDE';
UPDATE menu SET sort_order = 4 WHERE menu_code = 'RULE_CHARGEBACK';
UPDATE menu SET sort_order = 5 WHERE menu_code = 'RULE_EXCEPTION';
UPDATE menu SET sort_order = 1 WHERE menu_code = 'RECEIVE_DATA';
UPDATE menu SET sort_order = 2 WHERE menu_code = 'RECEIVE_MAPPING';
UPDATE menu SET sort_order = 1 WHERE menu_code = 'LEDGER_RECRUIT';
UPDATE menu SET sort_order = 2 WHERE menu_code = 'LEDGER_MAINTAIN';
UPDATE menu SET sort_order = 3 WHERE menu_code = 'LEDGER_EXCEPTION';
UPDATE menu SET sort_order = 1 WHERE menu_code = 'SETTLE_LIST';
UPDATE menu SET sort_order = 2 WHERE menu_code = 'BATCH_RUN';
UPDATE menu SET sort_order = 3 WHERE menu_code = 'PAYMENT';
UPDATE menu SET sort_order = 1 WHERE menu_code = 'SYSTEM_USER';
UPDATE menu SET sort_order = 2 WHERE menu_code = 'SYSTEM_ROLE';
UPDATE menu SET sort_order = 3 WHERE menu_code = 'SYSTEM_MENU';
UPDATE menu SET sort_order = 4 WHERE menu_code = 'SYSTEM_CODE';
UPDATE menu SET sort_order = 5 WHERE menu_code = 'SYSTEM_CONFIG';
UPDATE menu SET sort_order = 6 WHERE menu_code = 'SYSTEM_LOG';

-- 5) 대메뉴(DIRECTORY) 에 READ 권한 정의
INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT menu_id, 'READ', '조회', 1 FROM menu WHERE menu_code LIKE 'GRP_%'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- 6) 모든 역할에 대메뉴 READ 권한 부여 (자식 메뉴 권한이 있어야 자식이 보이지만, 부모 expand 가능)
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 IN ('SUPER_ADMIN', 'ADMIN', 'MANAGER', 'AGENT')
   AND m.menu_code LIKE 'GRP_%'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;
