-- V122: MOD-7 환수 감면/면제 (chargeback waiver/reduction)
--       chargeback_waiver_request 테이블
--       메뉴 CHARGEBACK_WAIVER (GRP_COMMISSION 하위)
--       공통코드 WAIVER_STATUS

-- ============================================================
-- 1. chargeback_waiver_request (환수 감면 신청)
-- ============================================================

CREATE TABLE chargeback_waiver_request (
    request_id          BIGSERIAL      PRIMARY KEY,
    contract_id         BIGINT         NOT NULL REFERENCES contract(contract_id),
    agent_id            BIGINT         NOT NULL REFERENCES agent(agent_id),
    settle_month        CHAR(6)        NOT NULL,
    original_chargeback NUMERIC(15,2)  NOT NULL,
    waiver_amount       NUMERIC(15,2)  NOT NULL,
    reason              VARCHAR(200),
    approval_status     VARCHAR(20)    NOT NULL DEFAULT 'REQUESTED',
    approver_id         BIGINT,
    approved_at         TIMESTAMP,
    created_at          TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by          BIGINT,
    updated_at          TIMESTAMP,
    CONSTRAINT chk_cwr_original_nn    CHECK (original_chargeback >= 0),
    CONSTRAINT chk_cwr_waiver_nn      CHECK (waiver_amount >= 0),
    CONSTRAINT chk_cwr_waiver_lte_orig CHECK (waiver_amount <= original_chargeback),
    CONSTRAINT chk_cwr_status         CHECK (approval_status IN ('REQUESTED','APPROVED','REJECTED'))
);

COMMENT ON TABLE  chargeback_waiver_request IS '환수 감면 신청';
COMMENT ON COLUMN chargeback_waiver_request.request_id          IS 'PK';
COMMENT ON COLUMN chargeback_waiver_request.contract_id         IS '계약 ID';
COMMENT ON COLUMN chargeback_waiver_request.agent_id            IS '설계사 ID';
COMMENT ON COLUMN chargeback_waiver_request.settle_month        IS '정산월 YYYYMM';
COMMENT ON COLUMN chargeback_waiver_request.original_chargeback IS '원래 환수액';
COMMENT ON COLUMN chargeback_waiver_request.waiver_amount       IS '감면 신청 금액 (<=original_chargeback)';
COMMENT ON COLUMN chargeback_waiver_request.reason              IS '감면 사유';
COMMENT ON COLUMN chargeback_waiver_request.approval_status     IS '승인 상태: REQUESTED/APPROVED/REJECTED';
COMMENT ON COLUMN chargeback_waiver_request.approver_id         IS '승인자 사용자 ID';
COMMENT ON COLUMN chargeback_waiver_request.approved_at         IS '승인/반려 일시';

CREATE INDEX idx_cwr_agent_month  ON chargeback_waiver_request(agent_id, settle_month);
CREATE INDEX idx_cwr_status       ON chargeback_waiver_request(approval_status);

-- ============================================================
-- 2. 메뉴 INSERT: CHARGEBACK_WAIVER (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
    ('CHARGEBACK_WAIVER', '환수감면신청', '/commission/chargeback-waiver', 'commission/ChargebackWaiver', 190)
) 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 = 'CHARGEBACK_WAIVER'
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 = 'CHARGEBACK_WAIVER'
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 = 'CHARGEBACK_WAIVER'
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 = 'CHARGEBACK_WAIVER'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

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

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('WAIVER_STATUS', '환수감면상태', 'REQUESTED=신청/APPROVED=승인/REJECTED=반려', 'Y', 510)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, sort_order) VALUES
    ('WAIVER_STATUS', 'REQUESTED', '신청', 10),
    ('WAIVER_STATUS', 'APPROVED',  '승인', 20),
    ('WAIVER_STATUS', 'REJECTED',  '반려', 30)
ON CONFLICT (group_code, code) DO NOTHING;
