-- V69: P5-W2 테스트 데이터 (withholding_tax_report, vat_report, 메뉴)

-- ============================================================
-- 1. withholding_tax_report: agent 5명 × 2025 Q1~Q4 = 20건
-- ============================================================
INSERT INTO withholding_tax_report (settle_quarter, agent_id, total_income, total_withheld, total_local_tax, generated_at, file_status)
SELECT
    q.quarter,
    a.agent_id,
    q.base_income,
    q.base_income * 0.033,
    q.base_income * 0.033 * 0.1,
    now(),
    CASE WHEN q.quarter IN ('2025-Q1','2025-Q2') THEN 'FILED' ELSE 'DRAFT' END
FROM (
    SELECT agent_id, ROW_NUMBER() OVER (ORDER BY agent_id) AS rn
    FROM agent LIMIT 5
) a
CROSS JOIN (VALUES
    ('2025-Q1', 10500000.00),
    ('2025-Q2', 11200000.00),
    ('2025-Q3', 10800000.00),
    ('2025-Q4', 12300000.00)
) AS q(quarter, base_income)
ON CONFLICT (agent_id, settle_quarter) DO NOTHING;

-- ============================================================
-- 2. vat_report: 2025 Q1~Q4 = 4건
-- ============================================================
INSERT INTO vat_report (settle_quarter, total_sales, total_purchase, total_vat_payable, tax_invoice_count, generated_at, file_status)
VALUES
    ('2025-Q1', 850000000.00, 120000000.00, 73000000.00, 142, now(), 'FILED'),
    ('2025-Q2', 920000000.00, 135000000.00, 78500000.00, 158, now(), 'FILED'),
    ('2025-Q3', 880000000.00, 128000000.00, 75200000.00, 151, now(), 'DRAFT'),
    ('2025-Q4', 960000000.00, 140000000.00, 82000000.00, 167, now(), 'DRAFT')
ON CONFLICT (settle_quarter) DO NOTHING;

-- ============================================================
-- 3. 메뉴: WITHHOLDING_TAX + VAT_REPORT (GRP_COMMISSION 하위)
-- ============================================================
INSERT INTO menu (parent_menu_id, menu_code, menu_name, menu_type, menu_path, component_path, menu_level, sort_order, is_visible, is_active)
SELECT p.menu_id, x.code, x.name, 'PAGE', x.path, x.cmpt, 2, x.sort, 'Y', 'Y'
FROM menu p
JOIN (VALUES
    ('WITHHOLDING_TAX', '원천세 신고',   '/commission/withholding-tax', 'commission/WithholdingTax', 100),
    ('VAT_REPORT',      '부가세 신고',   '/commission/vat-report',      'commission/VatReport',      110)
) AS x(code, name, path, cmpt, sort) ON p.menu_code = 'GRP_COMMISSION'
WHERE NOT EXISTS (SELECT 1 FROM menu WHERE menu_code = x.code);

-- ============================================================
-- 4. 권한 항목 등록
-- ============================================================
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),
    ('CREATE', '등록', 20),
    ('UPDATE', '수정', 30),
    ('EXPORT', '엑셀', 60)
) AS p(code, name, sort)
WHERE m.menu_code IN ('WITHHOLDING_TAX', 'VAT_REPORT')
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- ============================================================
-- 5. 역할별 권한 부여
-- ============================================================
-- 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 IN ('WITHHOLDING_TAX', 'VAT_REPORT')
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- 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 = 'MANAGER'
  AND mp.perm_code IN ('READ', 'EXPORT')
  AND m.menu_code IN ('WITHHOLDING_TAX', 'VAT_REPORT')
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;
