-- V65: P5-W1 테스트 데이터 (agent_annual_income, contract_accounting_entry, 메뉴)

-- ============================================================
-- 1. agent_annual_income: 설계사 10명 / 2025년
-- ============================================================
INSERT INTO agent_annual_income (agent_id, settle_year, total_commission, total_tax_withheld, total_local_tax, business_income)
SELECT
    a.agent_id,
    2025,
    v.total_commission,
    v.total_commission * 0.033,
    v.total_commission * 0.033 * 0.1,
    v.total_commission - (v.total_commission * 0.033) - (v.total_commission * 0.033 * 0.1)
FROM (
    SELECT agent_id, ROW_NUMBER() OVER (ORDER BY agent_id) AS rn
    FROM agent
    LIMIT 10
) a
JOIN (VALUES
    (1,  42000000.00),
    (2,  38500000.00),
    (3,  55000000.00),
    (4,  29800000.00),
    (5,  61200000.00),
    (6,  47300000.00),
    (7,  33600000.00),
    (8,  52100000.00),
    (9,  28400000.00),
    (10, 44700000.00)
) AS v(rn, total_commission) ON a.rn = v.rn
ON CONFLICT (agent_id, settle_year) DO NOTHING;

-- ============================================================
-- 2. contract_accounting_entry: payment 10건 기반
--    journal_no NULL 5건 (미전기) + 채번 5건 (전기완료)
-- ============================================================
INSERT INTO contract_accounting_entry (contract_id, payment_id, entry_date, debit_account, credit_account, amount, description, journal_no)
SELECT
    c.contract_id,
    p.payment_id,
    COALESCE(p.pay_date, CURRENT_DATE),
    '8350',
    '2530',
    p.pay_amount,
    '지급수수료 자동분개 #' || p.payment_id,
    CASE WHEN p.payment_id % 2 = 0 THEN 'JNL-2025-' || LPAD(p.payment_id::text, 5, '0') ELSE NULL END
FROM (
    SELECT py.payment_id, py.pay_amount, py.pay_date, sm.agent_id
    FROM payment py
    JOIN settle_master sm ON sm.settle_id = py.settle_id
    ORDER BY py.payment_id
    LIMIT 10
) p
JOIN LATERAL (
    SELECT c.contract_id
    FROM contract c
    WHERE c.agent_id = p.agent_id
    ORDER BY c.contract_id
    LIMIT 1
) c ON true;

-- ============================================================
-- 3. 메뉴: ANNUAL_INCOME + ACCOUNTING_JOURNAL (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
    ('ANNUAL_INCOME',       '연말 사업소득 명세서', '/commission/annual-income',      'commission/AnnualIncome',      80),
    ('ACCOUNTING_JOURNAL',  '회계 분개장',          '/commission/accounting-journal', 'commission/AccountingJournal', 90)
) 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 ('ANNUAL_INCOME', 'ACCOUNTING_JOURNAL')
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 ('ANNUAL_INCOME', 'ACCOUNTING_JOURNAL')
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 ('ANNUAL_INCOME', 'ACCOUNTING_JOURNAL')
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- AGENT: READ (본인 연말명세서)
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 = 'AGENT'
  AND mp.perm_code = 'READ'
  AND m.menu_code = 'ANNUAL_INCOME'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;
