-- V118: MOD-3 환수 분할상환 (installment clawback)
--       clawback_installment_plan 테이블
--       메뉴 CLAWBACK_INSTALLMENT (GRP_COMMISSION 하위)
--       공통코드 CB_INSTALLMENT_STATUS

-- ============================================================
-- 1. clawback_installment_plan (환수 분할상환 계획)
-- ============================================================

CREATE TABLE clawback_installment_plan (
    plan_id            BIGSERIAL      PRIMARY KEY,
    receivable_id      BIGINT         NOT NULL REFERENCES clawback_receivable(receivable_id),
    installment_no     INT            NOT NULL,
    installment_amount NUMERIC(15,2)  NOT NULL,
    due_month          CHAR(6)        NOT NULL,
    status             VARCHAR(20)    NOT NULL DEFAULT 'SCHEDULED',
    recovered_at       TIMESTAMP,
    created_at         TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by         BIGINT,
    updated_at         TIMESTAMP,
    CONSTRAINT chk_cip_amount_pos CHECK (installment_amount > 0),
    CONSTRAINT chk_cip_status     CHECK (status IN ('SCHEDULED','RECOVERED','CANCELLED')),
    CONSTRAINT uq_cip_receivable_no UNIQUE (receivable_id, installment_no)
);

COMMENT ON TABLE  clawback_installment_plan IS '환수 분할상환 계획';
COMMENT ON COLUMN clawback_installment_plan.plan_id            IS 'PK';
COMMENT ON COLUMN clawback_installment_plan.receivable_id      IS '환수채권 ID';
COMMENT ON COLUMN clawback_installment_plan.installment_no     IS '분할 회차 번호 (1부터)';
COMMENT ON COLUMN clawback_installment_plan.installment_amount IS '회차 납부 금액';
COMMENT ON COLUMN clawback_installment_plan.due_month          IS '납부 예정월 YYYYMM';
COMMENT ON COLUMN clawback_installment_plan.status             IS '상태: SCHEDULED/RECOVERED/CANCELLED';
COMMENT ON COLUMN clawback_installment_plan.recovered_at       IS '실제 회수 일시';

CREATE INDEX idx_cip_due_status    ON clawback_installment_plan(due_month, status);
CREATE INDEX idx_cip_receivable_id ON clawback_installment_plan(receivable_id);

-- ============================================================
-- 2. 메뉴 INSERT: CLAWBACK_INSTALLMENT (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
    ('CLAWBACK_INSTALLMENT', '환수분할상환', '/commission/clawback-installment', 'commission/ClawbackInstallment', 160)
) 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 = 'CLAWBACK_INSTALLMENT'
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 = 'CLAWBACK_INSTALLMENT'
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 = 'CLAWBACK_INSTALLMENT'
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 = 'CLAWBACK_INSTALLMENT'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

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

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('CB_INSTALLMENT_STATUS', '환수분할상환상태', 'SCHEDULED=예정/RECOVERED=회수/CANCELLED=취소', 'Y', 480)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, sort_order) VALUES
    ('CB_INSTALLMENT_STATUS', 'SCHEDULED',  '예정', 10),
    ('CB_INSTALLMENT_STATUS', 'RECOVERED',  '회수', 20),
    ('CB_INSTALLMENT_STATUS', 'CANCELLED',  '취소', 30)
ON CONFLICT (group_code, code) DO NOTHING;
