-- V34: 해촉 정산 (termination_settlement) — 설계사 해촉 시 미지급분·환수 처리

-- ============================================================
-- 1. termination_settlement — 해촉 정산 마스터
-- ============================================================

CREATE TABLE termination_settlement (
    term_settle_id          BIGSERIAL       PRIMARY KEY,
    agent_id                BIGINT          NOT NULL REFERENCES agent(agent_id),
    termination_date        DATE            NOT NULL,               -- 해촉 일자
    org_history_id          BIGINT          REFERENCES agent_org_history(history_id), -- LEAVE 이력 참조
    calc_month              CHAR(6)         NOT NULL,               -- 해촉 정산 처리 월 (YYYYMM)

    -- 미지급 수수료 (해촉 시점 기준)
    unpaid_recruit_amount   NUMERIC(15,2)   NOT NULL DEFAULT 0,     -- 미지급 신계약 수수료
    unpaid_installment_amount NUMERIC(15,2) NOT NULL DEFAULT 0,     -- 미지급 분급 잔액
    unpaid_incentive_amount NUMERIC(15,2)   NOT NULL DEFAULT 0,     -- 미지급 시책

    -- 환수 예정 금액
    chargeback_expected_amount NUMERIC(15,2) NOT NULL DEFAULT 0,    -- 환수 예정 총액
    chargeback_waived_amount   NUMERIC(15,2) NOT NULL DEFAULT 0,    -- 면제 환수액

    -- 공제 항목
    deduction_amount        NUMERIC(15,2)   NOT NULL DEFAULT 0,     -- 공제 차감액

    -- 최종 지급
    final_pay_amount        NUMERIC(15,2)   NOT NULL DEFAULT 0,     -- 최종 지급액 (미지급 - 환수 - 공제)

    status                  VARCHAR(20)     NOT NULL DEFAULT 'CALCULATING', -- CALCULATING/CONFIRMED/PAID/CANCELLED
    note                    VARCHAR(1000),
    confirmed_by            BIGINT,
    confirmed_at            TIMESTAMP,
    paid_at                 TIMESTAMP,
    payment_id              BIGINT          REFERENCES payment(payment_id), -- 최종 지급 참조
    created_at              TIMESTAMP       NOT NULL DEFAULT NOW(),
    created_by              BIGINT,
    updated_at              TIMESTAMP,
    updated_by              BIGINT,
    UNIQUE (agent_id, termination_date)                             -- 해촉 1건당 정산 1건
);

COMMENT ON TABLE  termination_settlement                         IS '해촉 정산 — 설계사 해촉 시 미지급분/환수/최종지급 통합 처리';
COMMENT ON COLUMN termination_settlement.termination_date        IS '해촉 일자';
COMMENT ON COLUMN termination_settlement.org_history_id          IS 'agent_org_history의 LEAVE 이력 참조';
COMMENT ON COLUMN termination_settlement.unpaid_recruit_amount   IS '해촉 시점 미지급 신계약 수수료';
COMMENT ON COLUMN termination_settlement.unpaid_installment_amount IS '해촉 시점 미지급 분급 잔액 (installment_plan.SCHEDULED 합계)';
COMMENT ON COLUMN termination_settlement.unpaid_incentive_amount IS '해촉 시점 미지급 시책 금액';
COMMENT ON COLUMN termination_settlement.chargeback_expected_amount IS '해촉 시점 발생 예정 환수 총액';
COMMENT ON COLUMN termination_settlement.chargeback_waived_amount IS '합의·면제된 환수액';
COMMENT ON COLUMN termination_settlement.final_pay_amount        IS '최종 지급액 = (미지급합계) - (환수예정-면제) - 공제';
COMMENT ON COLUMN termination_settlement.status                  IS 'CALCULATING/CONFIRMED/PAID/CANCELLED';

CREATE INDEX idx_ts_agent   ON termination_settlement(agent_id, termination_date DESC);
CREATE INDEX idx_ts_month   ON termination_settlement(calc_month, status);

-- ============================================================
-- 2. 공통코드 TERM_SETTLE_STATUS
-- ============================================================

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('TERM_SETTLE_STATUS', '해촉정산상태', 'CALCULATING/CONFIRMED/PAID/CANCELLED', 'Y', 360)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, code_desc, sort_order) VALUES
    ('TERM_SETTLE_STATUS', 'CALCULATING', '산출중',   '해촉 정산 금액 산출 중',   10),
    ('TERM_SETTLE_STATUS', 'CONFIRMED',   '확정',     '해촉 정산 금액 확정',       20),
    ('TERM_SETTLE_STATUS', 'PAID',        '지급완료', '최종 지급 완료',             30),
    ('TERM_SETTLE_STATUS', 'CANCELLED',   '취소',     '해촉 정산 취소',             40)
ON CONFLICT (group_code, code) DO NOTHING;
