-- V119: MOD-4 익월 부활 재지급 (lapsed reinstatement repayment)
--       lapsed_reinstatement_recovery 테이블
--       메뉴 LAPSED_REINSTATEMENT (GRP_COMMISSION 하위)
--       공통코드 LAPSE_RECOVERY_STATUS

-- ============================================================
-- 1. lapsed_reinstatement_recovery (익월 부활 재지급 원장)
-- ============================================================

CREATE TABLE lapsed_reinstatement_recovery (
    recovery_id       BIGSERIAL      PRIMARY KEY,
    contract_id       BIGINT         NOT NULL REFERENCES contract(contract_id),
    agent_id          BIGINT         NOT NULL REFERENCES agent(agent_id),
    lapsed_month      CHAR(6)        NOT NULL,
    reinstated_month  CHAR(6)        NOT NULL,
    withheld_amount   NUMERIC(15,2)  NOT NULL,
    repaid_amount     NUMERIC(15,2)  NOT NULL DEFAULT 0,
    status            VARCHAR(20)    NOT NULL DEFAULT 'PENDING',
    created_at        TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by        BIGINT,
    updated_at        TIMESTAMP,
    CONSTRAINT chk_lrr_withheld_nn CHECK (withheld_amount >= 0),
    CONSTRAINT chk_lrr_status      CHECK (status IN ('PENDING','REPAID','CANCELLED'))
);

COMMENT ON TABLE  lapsed_reinstatement_recovery IS '익월 부활 재지급 원장';
COMMENT ON COLUMN lapsed_reinstatement_recovery.recovery_id      IS 'PK';
COMMENT ON COLUMN lapsed_reinstatement_recovery.contract_id      IS '계약 ID';
COMMENT ON COLUMN lapsed_reinstatement_recovery.agent_id         IS '설계사 ID';
COMMENT ON COLUMN lapsed_reinstatement_recovery.lapsed_month     IS '실효 발생월 YYYYMM';
COMMENT ON COLUMN lapsed_reinstatement_recovery.reinstated_month IS '부활 확정월 YYYYMM';
COMMENT ON COLUMN lapsed_reinstatement_recovery.withheld_amount  IS '보류된 수수료 금액';
COMMENT ON COLUMN lapsed_reinstatement_recovery.repaid_amount    IS '재지급 완료 금액';
COMMENT ON COLUMN lapsed_reinstatement_recovery.status           IS '상태: PENDING/REPAID/CANCELLED';

CREATE INDEX idx_lrr_agent_reinstated ON lapsed_reinstatement_recovery(agent_id, reinstated_month);
CREATE INDEX idx_lrr_contract         ON lapsed_reinstatement_recovery(contract_id);

-- ============================================================
-- 2. 메뉴 INSERT: LAPSED_REINSTATEMENT (GRP_COMMISSION 하위 PAGE)
-- ============================================================

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
    ('LAPSED_REINSTATEMENT', '익월부활재지급', '/commission/lapsed-reinstatement', 'commission/LapsedReinstatement', 170)
) 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);

-- ============================================================
-- 3. 메뉴 권한 항목 등록 (READ/CREATE/UPDATE/DELETE/EXPORT)
-- ============================================================

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),
    ('DELETE', '삭제', 40),
    ('EXPORT', '엑셀', 60)
) AS p(code, name, sort)
WHERE m.menu_code = 'LAPSED_REINSTATEMENT'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- ============================================================
-- 4. 역할별 권한 부여
-- ============================================================

-- 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 = 'LAPSED_REINSTATEMENT'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- MANAGER — READ/CREATE/UPDATE/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','CREATE','UPDATE','EXPORT')
  AND m.menu_code = 'LAPSED_REINSTATEMENT'
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 = 'LAPSED_REINSTATEMENT'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- ============================================================
-- 5. 공통코드: LAPSE_RECOVERY_STATUS
-- ============================================================

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('LAPSE_RECOVERY_STATUS', '부활재지급상태', 'PENDING=대기/REPAID=재지급완료/CANCELLED=취소', 'Y', 490)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, sort_order) VALUES
    ('LAPSE_RECOVERY_STATUS', 'PENDING',    '대기',       10),
    ('LAPSE_RECOVERY_STATUS', 'REPAID',     '재지급완료', 20),
    ('LAPSE_RECOVERY_STATUS', 'CANCELLED',  '취소',       30)
ON CONFLICT (group_code, code) DO NOTHING;
