-- V38: 리포트 메뉴 하위 항목 추가
-- GRP_REPORT / REPORT 그룹 모두 처리 (V12 = REPORT, V18 = GRP_REPORT)

-- 1. REPORT 그룹에 하위 PAGE 삽입 (menu_code=REPORT 기준)
INSERT INTO menu (parent_menu_id, menu_code, menu_name, menu_type, menu_path, component_path, menu_level, sort_order)
SELECT p.menu_id, x.code, x.name, 'PAGE', x.path, x.cmpt, 2, x.sort
FROM menu p
JOIN (VALUES
    ('REPORT_PERFORMANCE',   '실적 리포트',      '/report/performance',     'report/PerformanceReport',    10),
    ('REPORT_ORG',           '조직별 리포트',    '/report/org',             'report/OrgReport',            20),
    ('REPORT_CHARGEBACK',    '환수위험 리포트',  '/report/chargeback-risk', 'report/ChargebackRiskReport', 30)
) AS x(code, name, path, cmpt, sort)
ON p.menu_code = 'REPORT'
WHERE NOT EXISTS (SELECT 1 FROM menu WHERE menu_code = x.code);

-- 2. GRP_REPORT 그룹에도 동일 PAGE 삽입 (V18 기준 경로; menu_code=GRP_REPORT)
INSERT INTO menu (parent_menu_id, menu_code, menu_name, menu_type, menu_path, component_path, menu_level, sort_order)
SELECT p.menu_id, x.code || '_GRP', x.name, 'PAGE', x.path, x.cmpt, 2, x.sort
FROM menu p
JOIN (VALUES
    ('REPORT_PERFORMANCE',   '실적 리포트',      '/report/performance',     'report/PerformanceReport',    10),
    ('REPORT_ORG',           '조직별 리포트',    '/report/org',             'report/OrgReport',            20),
    ('REPORT_CHARGEBACK',    '환수위험 리포트',  '/report/chargeback-risk', 'report/ChargebackRiskReport', 30)
) AS x(code, name, path, cmpt, sort)
ON p.menu_code = 'GRP_REPORT'
WHERE NOT EXISTS (SELECT 1 FROM menu WHERE menu_code = x.code || '_GRP');

-- 3. 새 PAGE 메뉴에 권한 항목 추가
INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT m.menu_id, p.code, p.name, p.sort
FROM menu m
CROSS JOIN (VALUES
    ('READ',   '조회', 10),
    ('EXPORT', '엑셀', 60)
) AS p(code, name, sort)
WHERE m.menu_code IN (
    'REPORT_PERFORMANCE', 'REPORT_ORG', 'REPORT_CHARGEBACK',
    'REPORT_PERFORMANCE_GRP', 'REPORT_ORG_GRP', 'REPORT_CHARGEBACK_GRP'
)
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- 4. SUPER_ADMIN / ADMIN / MANAGER 에게 리포트 READ/EXPORT 권한 부여
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', 'MANAGER')
  AND m.menu_code IN (
      'REPORT_PERFORMANCE', 'REPORT_ORG', 'REPORT_CHARGEBACK',
      'REPORT_PERFORMANCE_GRP', 'REPORT_ORG_GRP', 'REPORT_CHARGEBACK_GRP'
  )
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;
