-- V24: 공제/차감 도메인 (agent_deduction, payment_deduction_detail, payment 확장, 공통코드)

-- ============================================================
-- 1. agent_deduction — 월별 차감 마스터
-- ============================================================

CREATE TABLE agent_deduction (
    deduction_id    BIGSERIAL       PRIMARY KEY,
    agent_id        BIGINT          NOT NULL REFERENCES agent(agent_id),
    settle_month    CHAR(6)         NOT NULL,
    deduction_type  VARCHAR(20)     NOT NULL,
    amount          NUMERIC(15,2)   NOT NULL DEFAULT 0,
    description     VARCHAR(255),
    status          VARCHAR(20)     NOT NULL DEFAULT 'PENDING',
    created_at      TIMESTAMP       NOT NULL DEFAULT NOW(),
    created_by      BIGINT,
    updated_at      TIMESTAMP,
    updated_by      BIGINT,
    UNIQUE (agent_id, settle_month, deduction_type)
);

CREATE INDEX idx_ad_month_agent ON agent_deduction(settle_month, agent_id);

-- ============================================================
-- 2. payment_deduction_detail — 이체 시 적용된 차감 명세
-- ============================================================

CREATE TABLE payment_deduction_detail (
    detail_id       BIGSERIAL       PRIMARY KEY,
    payment_id      BIGINT          NOT NULL REFERENCES payment(payment_id),
    deduction_type  VARCHAR(20)     NOT NULL,
    amount          NUMERIC(15,2)   NOT NULL,
    description     VARCHAR(255),
    applied_at      TIMESTAMP       NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_pdd_payment ON payment_deduction_detail(payment_id);

-- ============================================================
-- 3. payment 테이블 차감 합계 컬럼 추가
-- ============================================================

ALTER TABLE payment
    ADD COLUMN deduction_amount NUMERIC(15,2) NOT NULL DEFAULT 0;

-- ============================================================
-- 4. 공통코드 그룹 DEDUCTION_TYPE 추가
-- ============================================================

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('DEDUCTION_TYPE',   '차감유형',   'LOAN/ADVANCE/GUARANTEE/DUES/AD/CHARGEBACK_PENDING/OTHER', 'Y', 210),
    ('DEDUCTION_STATUS', '차감상태',   'PENDING/APPLIED/CANCELLED',                                'Y', 220)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, code_desc, sort_order) VALUES
    ('DEDUCTION_TYPE', 'LOAN',               '대출잔액',       '설계사 대출 미상환 잔액',           10),
    ('DEDUCTION_TYPE', 'ADVANCE',            '가불금',         '선지급 후 정산 차감',               20),
    ('DEDUCTION_TYPE', 'GUARANTEE',          '신원보증보험료', '신원보증보험 납부액',               30),
    ('DEDUCTION_TYPE', 'DUES',               '협회비',         '보험협회 등 협회비',               40),
    ('DEDUCTION_TYPE', 'AD',                 '광고비/판촉비',  '광고 및 판촉 활동 비용',           50),
    ('DEDUCTION_TYPE', 'CHARGEBACK_PENDING', '미회수환수금',   '환수 분할 미처리 잔액',            60),
    ('DEDUCTION_TYPE', 'OTHER',              '기타',           '기타 차감 항목',                   70),
    ('DEDUCTION_STATUS', 'PENDING',   '대기',   '차감 등록 후 미적용 상태',  10),
    ('DEDUCTION_STATUS', 'APPLIED',   '적용됨', '이체 시 차감 완료',        20),
    ('DEDUCTION_STATUS', 'CANCELLED', '취소',   '차감 취소 처리됨',         30)
ON CONFLICT (group_code, code) DO NOTHING;
