-- V102: 적립금 (agent_reserve_fund)
--       환수 대비 수수료 일부 적립 + 잔액 관리.
--       공식: balance = (직전월 balance) + reserve_in - reserve_out
--       Service가 직전월 balance를 조회하여 누적 계산.

CREATE TABLE agent_reserve_fund (
    reserve_id   BIGSERIAL      PRIMARY KEY,
    agent_id     INT            NOT NULL REFERENCES agent(agent_id),
    settle_month CHAR(6)        NOT NULL,
    reserve_in   NUMERIC(15,2)  NOT NULL DEFAULT 0,
    reserve_out  NUMERIC(15,2)  NOT NULL DEFAULT 0,
    balance      NUMERIC(15,2)  NOT NULL,
    created_at   TIMESTAMP      NOT NULL DEFAULT NOW(),
    UNIQUE (agent_id, settle_month)
);

COMMENT ON TABLE  agent_reserve_fund              IS '적립금 — 환수 대비 설계사별 월별 적립금 잔액';
COMMENT ON COLUMN agent_reserve_fund.agent_id      IS '설계사 FK';
COMMENT ON COLUMN agent_reserve_fund.settle_month  IS '정산 귀속월 (YYYYMM)';
COMMENT ON COLUMN agent_reserve_fund.reserve_in    IS '당월 적립액';
COMMENT ON COLUMN agent_reserve_fund.reserve_out   IS '당월 인출/사용액';
COMMENT ON COLUMN agent_reserve_fund.balance       IS '잔액 = 전월잔액 + reserve_in - reserve_out';

CREATE INDEX idx_arf_agent_month ON agent_reserve_fund(agent_id, settle_month);
