-- V78: P5-W3 본론(결산 / 이상치·예측 / 연말명세서) 메뉴·권한·공통코드 일괄 등록
-- 신규 메뉴 4개:
--   ACCOUNTING_CLOSE   (GRP_COMMISSION 하위) — 회계 결산
--   YEAR_END_STMT      (GRP_COMMISSION 하위) — 연말 지급명세서
--   RETENTION_ANOMALY  (GRP_KPI 하위)        — 유지율 이상치
--   FORECAST_KPI       (GRP_KPI 하위)        — 차월 KPI 예측

-- ============================================================
-- 1. 메뉴 INSERT
-- ============================================================
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
    ('ACCOUNTING_CLOSE', '회계 결산',       '/commission/accounting-close', 'accounting/AccountingCloseList', 130),
    ('YEAR_END_STMT',    '연말 지급명세서', '/commission/year-end-statement', 'income/YearEndStatementList', 140)
) 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);

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
    ('RETENTION_ANOMALY', '유지율 이상치',  '/kpi/retention-anomaly', 'kpi/RetentionAnomalyList',  50),
    ('FORECAST_KPI',      '차월 KPI 예측', '/kpi/forecast',          'kpi/ForecastKpiList',       60)
) AS x(code, name, path, cmpt, sort) ON p.menu_code = 'GRP_KPI'
WHERE NOT EXISTS (SELECT 1 FROM menu WHERE menu_code = x.code);

-- ============================================================
-- 2. 권한 항목 등록 (perm)
-- ============================================================
-- ACCOUNTING_CLOSE: READ / CREATE / EXECUTE / EXPORT / APPROVE
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),
    ('EXECUTE', '실행', 30),
    ('EXPORT',  '엑셀', 40),
    ('APPROVE', '승인', 50)
) AS p(code, name, sort)
WHERE m.menu_code = 'ACCOUNTING_CLOSE'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- YEAR_END_STMT: READ / EXECUTE / EXPORT / UPDATE
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),
    ('UPDATE',  '수정', 30),
    ('EXECUTE', '발급', 40),
    ('EXPORT',  '엑셀', 60)
) AS p(code, name, sort)
WHERE m.menu_code = 'YEAR_END_STMT'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- RETENTION_ANOMALY: READ / UPDATE
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),
    ('UPDATE',  '검토', 30)
) AS p(code, name, sort)
WHERE m.menu_code = 'RETENTION_ANOMALY'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- FORECAST_KPI: READ / EXECUTE
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),
    ('EXECUTE', '재산출',   30)
) AS p(code, name, sort)
WHERE m.menu_code = 'FORECAST_KPI'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- ============================================================
-- 3. 역할별 권한 부여
-- ============================================================
-- 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 ('ACCOUNTING_CLOSE', 'YEAR_END_STMT', 'RETENTION_ANOMALY', 'FORECAST_KPI')
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 ('ACCOUNTING_CLOSE', 'YEAR_END_STMT', 'RETENTION_ANOMALY', 'FORECAST_KPI')
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- ============================================================
-- 4. 공통코드 그룹 + 상세 등록
-- ============================================================
INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('ACC_CLOSE_TYPE',   '회계결산구분', 'MONTHLY/QUARTERLY/YEARLY',           'Y', 400),
    ('ACC_CLOSE_STATUS', '회계결산상태', 'DRAFT/CLOSED/REOPENED',              'Y', 410),
    ('YES_STATUS',       '연말명세서상태', 'DRAFT/GENERATED/SENT',             'Y', 420),
    ('YES_FORMAT',       '연말명세서포맷', 'XLSX/PDF',                         'Y', 430),
    ('ANOMALY_SEVERITY', '이상치심각도', 'LOW/MED/HIGH',                       'Y', 440),
    ('ANOMALY_STATUS',   '이상치상태',   'NEW/REVIEWED/RESOLVED',              'Y', 450),
    ('FORECAST_METHOD',  '예측기법',     'SMA3/SMA6/NAIVE',                    'Y', 460)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, sort_order, is_active)
VALUES
    ('ACC_CLOSE_TYPE',   'MONTHLY',   '월결산',    10, 'Y'),
    ('ACC_CLOSE_TYPE',   'QUARTERLY', '분기결산',  20, 'Y'),
    ('ACC_CLOSE_TYPE',   'YEARLY',    '연결산',    30, 'Y'),
    ('ACC_CLOSE_STATUS', 'DRAFT',     '초안',      10, 'Y'),
    ('ACC_CLOSE_STATUS', 'CLOSED',    '마감완료',  20, 'Y'),
    ('ACC_CLOSE_STATUS', 'REOPENED',  '재오픈',    30, 'Y'),
    ('YES_STATUS',       'DRAFT',     '대기',      10, 'Y'),
    ('YES_STATUS',       'GENERATED', '파일생성',  20, 'Y'),
    ('YES_STATUS',       'SENT',      '발송완료',  30, 'Y'),
    ('YES_FORMAT',       'XLSX',      'Excel',     10, 'Y'),
    ('YES_FORMAT',       'PDF',       'PDF',       20, 'Y'),
    ('ANOMALY_SEVERITY', 'LOW',       '낮음',      10, 'Y'),
    ('ANOMALY_SEVERITY', 'MED',       '보통',      20, 'Y'),
    ('ANOMALY_SEVERITY', 'HIGH',      '높음',      30, 'Y'),
    ('ANOMALY_STATUS',   'NEW',       '신규',      10, 'Y'),
    ('ANOMALY_STATUS',   'REVIEWED',  '검토중',    20, 'Y'),
    ('ANOMALY_STATUS',   'RESOLVED',  '완료',      30, 'Y'),
    ('FORECAST_METHOD',  'SMA3',      '3개월 이동평균', 10, 'Y'),
    ('FORECAST_METHOD',  'SMA6',      '6개월 이동평균', 20, 'Y'),
    ('FORECAST_METHOD',  'NAIVE',     '단순 직전월',    30, 'Y')
ON CONFLICT (group_code, code) DO NOTHING;

-- ============================================================
-- 5. 테스트 데이터 (소량) — accounting_close 1건 / year_end_statement 1건
-- ============================================================
INSERT INTO accounting_close (close_period, close_type, close_status, journal_prefix, journal_count, total_debit, total_credit, created_at)
VALUES ('2025-03', 'MONTHLY', 'DRAFT', '2025-03', 0, 0, 0, now())
ON CONFLICT (close_period, close_type) DO NOTHING;

INSERT INTO year_end_statement (agent_id, statement_year, statement_type, total_income, total_withheld, total_local_tax, file_status)
SELECT a.agent_id, 2025, 'INCOME_DEDUCTION',
       COALESCE(ai.total_commission,   0),
       COALESCE(ai.total_tax_withheld, 0),
       COALESCE(ai.total_local_tax,    0),
       'DRAFT'
FROM agent a
LEFT JOIN agent_annual_income ai ON ai.agent_id = a.agent_id AND ai.settle_year = 2025
WHERE NOT EXISTS (
    SELECT 1 FROM year_end_statement yes
    WHERE yes.agent_id = a.agent_id AND yes.statement_year = 2025 AND yes.statement_type = 'INCOME_DEDUCTION'
);
