-- V116: MOD-1 슈퍼바이저/도입수수료 (recruiter/development commission)
--       agent.recruiter_agent_id 컬럼 추가
--       recruiter_commission_rule / recruiter_commission_ledger 테이블
--       메뉴 RECRUITER_COMM (GRP_COMMISSION 하위)
--       공통코드 RECRUITER_TYPE

-- ============================================================
-- 1. agent 테이블에 도입자 컬럼 추가
-- ============================================================

ALTER TABLE agent ADD COLUMN IF NOT EXISTS recruiter_agent_id BIGINT REFERENCES agent(agent_id);

COMMENT ON COLUMN agent.recruiter_agent_id IS '도입자 설계사 ID (NULL=도입자 없음)';

-- ============================================================
-- 2. recruiter_commission_rule (도입수수료 룰 마스터)
-- ============================================================

CREATE TABLE recruiter_commission_rule (
    rule_id          BIGSERIAL      PRIMARY KEY,
    recruiter_type   VARCHAR(20)    NOT NULL,
    calc_type        VARCHAR(10)    NOT NULL,
    commission_rate  NUMERIC(7,4),
    fixed_amount     NUMERIC(15,2),
    effective_from   DATE           NOT NULL,
    effective_to     DATE,
    created_at       TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by       BIGINT,
    updated_at       TIMESTAMP,
    CONSTRAINT chk_rcr_recruiter_type CHECK (recruiter_type IN ('NEW_RECRUIT','DEVELOPMENT','TRAINING')),
    CONSTRAINT chk_rcr_calc_type      CHECK (calc_type IN ('RATE','FIXED')),
    CONSTRAINT chk_rcr_rate_range     CHECK (commission_rate IS NULL OR (commission_rate >= 0 AND commission_rate <= 1))
);

COMMENT ON TABLE  recruiter_commission_rule IS '도입수수료 룰 마스터';
COMMENT ON COLUMN recruiter_commission_rule.rule_id         IS 'PK';
COMMENT ON COLUMN recruiter_commission_rule.recruiter_type  IS '도입유형: NEW_RECRUIT/DEVELOPMENT/TRAINING';
COMMENT ON COLUMN recruiter_commission_rule.calc_type       IS '계산방식: RATE(정률)/FIXED(정액)';
COMMENT ON COLUMN recruiter_commission_rule.commission_rate IS '정률 요율 (0~1)';
COMMENT ON COLUMN recruiter_commission_rule.fixed_amount    IS '정액 금액';
COMMENT ON COLUMN recruiter_commission_rule.effective_from  IS '적용 시작일';
COMMENT ON COLUMN recruiter_commission_rule.effective_to    IS '적용 종료일 (NULL=무기한)';

CREATE INDEX idx_rcr_type_from ON recruiter_commission_rule(recruiter_type, effective_from);

-- ============================================================
-- 3. recruiter_commission_ledger (도입수수료 원장)
-- ============================================================

CREATE TABLE recruiter_commission_ledger (
    ledger_id         BIGSERIAL      PRIMARY KEY,
    recruiter_agent_id BIGINT        NOT NULL REFERENCES agent(agent_id),
    recruit_agent_id   BIGINT        NOT NULL REFERENCES agent(agent_id),
    settle_month       CHAR(6)       NOT NULL,
    recruiter_type     VARCHAR(20)   NOT NULL,
    base_amount        NUMERIC(15,2) NOT NULL,
    commission_amount  NUMERIC(15,2) NOT NULL,
    status             VARCHAR(20)   NOT NULL DEFAULT 'CALCULATED',
    created_at         TIMESTAMP     NOT NULL DEFAULT NOW(),
    created_by         BIGINT,
    updated_at         TIMESTAMP,
    CONSTRAINT chk_rcl_status CHECK (status IN ('CALCULATED','CONFIRMED','PAID','CANCELLED'))
);

COMMENT ON TABLE  recruiter_commission_ledger IS '도입수수료 원장';
COMMENT ON COLUMN recruiter_commission_ledger.ledger_id          IS 'PK';
COMMENT ON COLUMN recruiter_commission_ledger.recruiter_agent_id IS '도입자 설계사 ID';
COMMENT ON COLUMN recruiter_commission_ledger.recruit_agent_id   IS '피도입(신규) 설계사 ID';
COMMENT ON COLUMN recruiter_commission_ledger.settle_month       IS '정산월 YYYYMM';
COMMENT ON COLUMN recruiter_commission_ledger.recruiter_type     IS '도입유형';
COMMENT ON COLUMN recruiter_commission_ledger.base_amount        IS '기준금액 (피도입 당월 모집수수료 합)';
COMMENT ON COLUMN recruiter_commission_ledger.commission_amount  IS '도입수수료 금액';
COMMENT ON COLUMN recruiter_commission_ledger.status             IS '상태: CALCULATED/CONFIRMED/PAID/CANCELLED';

CREATE INDEX idx_rcl_recruiter_month ON recruiter_commission_ledger(recruiter_agent_id, settle_month);
CREATE INDEX idx_rcl_recruit_month   ON recruiter_commission_ledger(recruit_agent_id, settle_month);

-- ============================================================
-- 4. 메뉴 INSERT: RECRUITER_COMM (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
    ('RECRUITER_COMM', '도입수수료', '/commission/recruiter', 'commission/RecruiterCommission', 150)
) 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);

-- ============================================================
-- 5. 메뉴 권한 항목 등록 (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 = 'RECRUITER_COMM'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

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

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

-- ============================================================
-- 7. 공통코드: RECRUITER_TYPE
-- ============================================================

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('RECRUITER_TYPE', '도입수수료유형', 'NEW_RECRUIT=신인도입/DEVELOPMENT=육성/TRAINING=교육', 'Y', 460)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, sort_order) VALUES
    ('RECRUITER_TYPE', 'NEW_RECRUIT',  '신인도입', 10),
    ('RECRUITER_TYPE', 'DEVELOPMENT',  '육성',     20),
    ('RECRUITER_TYPE', 'TRAINING',     '교육',     30)
ON CONFLICT (group_code, code) DO NOTHING;
