-- V121: MOD-6 환수 시효/소멸 (statute of limitations)
--       statute_config 테이블 + 시드 1행
--       메뉴 STATUTE_CONFIG (GRP_COMMISSION 하위)
--       clawback_receivable.status CHECK: WRITTEN_OFF 이미 존재 (V111 확인) → 제약 변경 불필요

-- ============================================================
-- 1. statute_config (환수 시효 설정)
-- ============================================================

CREATE TABLE statute_config (
    config_id         BIGSERIAL     PRIMARY KEY,
    entity_type       VARCHAR(30)   NOT NULL,
    expiration_months INT           NOT NULL,
    is_active         CHAR(1)       NOT NULL DEFAULT 'Y',
    created_at        TIMESTAMP     NOT NULL DEFAULT NOW(),
    created_by        BIGINT,
    updated_at        TIMESTAMP,
    CONSTRAINT chk_sc_entity_type        CHECK (entity_type IN ('CLAWBACK_RECEIVABLE')),
    CONSTRAINT chk_sc_expiration_pos     CHECK (expiration_months > 0),
    CONSTRAINT chk_sc_is_active          CHECK (is_active IN ('Y','N')),
    CONSTRAINT uq_sc_entity_type         UNIQUE (entity_type)
);

COMMENT ON TABLE  statute_config IS '환수 시효 설정';
COMMENT ON COLUMN statute_config.config_id         IS 'PK';
COMMENT ON COLUMN statute_config.entity_type       IS '대상 엔티티 타입 (CLAWBACK_RECEIVABLE)';
COMMENT ON COLUMN statute_config.expiration_months IS '시효 만료 개월수';
COMMENT ON COLUMN statute_config.is_active         IS '활성 여부 (Y/N)';

-- ============================================================
-- 2. 시드 데이터: CLAWBACK_RECEIVABLE 36개월
-- ============================================================

INSERT INTO statute_config (entity_type, expiration_months, is_active)
VALUES ('CLAWBACK_RECEIVABLE', 36, 'Y')
ON CONFLICT (entity_type) DO NOTHING;

-- ============================================================
-- 3. 메뉴 INSERT: STATUTE_CONFIG (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
    ('STATUTE_CONFIG', '환수시효관리', '/commission/statute', 'commission/StatuteConfig', 180)
) 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. 메뉴 권한 항목 등록 (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 = 'STATUTE_CONFIG'
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 = 'STATUTE_CONFIG'
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 = 'STATUTE_CONFIG'
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 = 'STATUTE_CONFIG'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;
