-- V123: MOD-8 채널별 수수료 차등 (channel-specific commission)
--       contract.sales_channel 컬럼 추가
--       commission_rate.sales_channel 컬럼 추가 (V3 확인: product_id 기반 룰 테이블)
--       payout_rule.sales_channel 컬럼 추가 (V3 확인: grade_id 기반 룰 테이블)
--       공통코드 SALES_CHANNEL
--       하위호환: sales_channel NULL = DIRECT 취급, NULL 룰이 폴백

-- ============================================================
-- 1. contract 테이블에 판매채널 컬럼 추가
-- ============================================================

ALTER TABLE contract ADD COLUMN IF NOT EXISTS sales_channel VARCHAR(20);

COMMENT ON COLUMN contract.sales_channel IS '판매채널 (NULL=DIRECT 취급): DIRECT/BANCASSURANCE/HOME_SHOPPING/PARTNERSHIP';

-- ============================================================
-- 2. commission_rate 테이블에 sales_channel 컬럼 추가
--    NULL = 모든 채널 적용 (폴백 룰)
-- ============================================================

ALTER TABLE commission_rate ADD COLUMN IF NOT EXISTS sales_channel VARCHAR(20);

COMMENT ON COLUMN commission_rate.sales_channel IS '판매채널 조건 (NULL=모든 채널 폴백)';

CREATE INDEX idx_cr_product_channel ON commission_rate(product_id, sales_channel, effective_from);

-- ============================================================
-- 3. payout_rule 테이블에 sales_channel 컬럼 추가
--    NULL = 모든 채널 적용 (폴백 룰)
-- ============================================================

ALTER TABLE payout_rule ADD COLUMN IF NOT EXISTS sales_channel VARCHAR(20);

COMMENT ON COLUMN payout_rule.sales_channel IS '판매채널 조건 (NULL=모든 채널 폴백)';

CREATE INDEX idx_pr_grade_channel ON payout_rule(grade_id, insurance_type, sales_channel, effective_from);

-- ============================================================
-- 4. 공통코드: SALES_CHANNEL
-- ============================================================

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('SALES_CHANNEL', '판매채널', 'DIRECT=직판/BANCASSURANCE=방카/HOME_SHOPPING=홈쇼핑/PARTNERSHIP=제휴', 'Y', 520)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, sort_order) VALUES
    ('SALES_CHANNEL', 'DIRECT',         '직판',   10),
    ('SALES_CHANNEL', 'BANCASSURANCE',  '방카',   20),
    ('SALES_CHANNEL', 'HOME_SHOPPING',  '홈쇼핑', 30),
    ('SALES_CHANNEL', 'PARTNERSHIP',    '제휴',   40)
ON CONFLICT (group_code, code) DO NOTHING;
