-- V90: 정착지원금 (settling_support_rule + settling_support_ledger)
--      신입/경력 설계사 정착 지원금. 등급별 정액 월 지급 + 조기 해촉 시 환수.
--      공식: net_amount = support_amount - clawback_amount
--      환수: 조기해촉 시 clawback_amount = support_amount × (잔여약정개월 / 약정개월)

-- ============================================================
-- 1. settling_support_rule — 정착지원금 룰 마스터
-- ============================================================

CREATE TABLE settling_support_rule (
    rule_id         BIGSERIAL      PRIMARY KEY,
    agent_grade     VARCHAR(20)    NOT NULL,
    support_amount  NUMERIC(15,2)  NOT NULL,
    duration_months INT            NOT NULL,
    clawback_months INT            NOT NULL,
    effective_from  DATE           NOT NULL,
    effective_to    DATE,
    created_at      TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by      BIGINT,
    updated_at      TIMESTAMP,
    CHECK (support_amount >= 0),
    CHECK (duration_months > 0),
    CHECK (clawback_months >= 0)
);

COMMENT ON TABLE  settling_support_rule                   IS '정착지원금 룰 마스터 — 설계사 등급별 월 정착지원금 및 약정 기간 정의';
COMMENT ON COLUMN settling_support_rule.rule_id           IS '룰 PK';
COMMENT ON COLUMN settling_support_rule.agent_grade       IS '설계사 등급';
COMMENT ON COLUMN settling_support_rule.support_amount    IS '월 정액 지원금';
COMMENT ON COLUMN settling_support_rule.duration_months   IS '약정 지원 개월수';
COMMENT ON COLUMN settling_support_rule.clawback_months   IS '이 개월 내 해촉 시 환수 대상 (0=환수 없음)';
COMMENT ON COLUMN settling_support_rule.effective_from    IS '적용 시작일';
COMMENT ON COLUMN settling_support_rule.effective_to      IS '적용 종료일 (NULL=현재 유효)';

CREATE INDEX idx_ssr_grade_from ON settling_support_rule(agent_grade, effective_from);

-- ============================================================
-- 2. settling_support_ledger — 정착지원금 원장
-- ============================================================

CREATE TABLE settling_support_ledger (
    ledger_id        BIGSERIAL      PRIMARY KEY,
    agent_id         BIGINT         NOT NULL REFERENCES agent(agent_id),
    settle_month     CHAR(6)        NOT NULL,
    support_amount   NUMERIC(15,2)  NOT NULL,
    clawback_amount  NUMERIC(15,2)  NOT NULL DEFAULT 0,
    net_amount       NUMERIC(15,2)  NOT NULL,
    status           VARCHAR(20)    NOT NULL DEFAULT 'CALCULATED',
    created_at       TIMESTAMP      NOT NULL DEFAULT NOW(),
    updated_at       TIMESTAMP,
    CHECK (status IN ('CALCULATED','PAID','CLAWED_BACK','CANCELLED')),
    CHECK (support_amount >= 0),
    CHECK (clawback_amount >= 0)
);

COMMENT ON TABLE  settling_support_ledger                   IS '정착지원금 원장 — 설계사별 월 정착지원금 지급/환수 이력';
COMMENT ON COLUMN settling_support_ledger.ledger_id         IS '원장 PK';
COMMENT ON COLUMN settling_support_ledger.agent_id          IS '대상 설계사 FK';
COMMENT ON COLUMN settling_support_ledger.settle_month      IS '정산 귀속월 (YYYYMM)';
COMMENT ON COLUMN settling_support_ledger.support_amount    IS '지급액 (등급별 support_amount)';
COMMENT ON COLUMN settling_support_ledger.clawback_amount   IS '환수액 = support_amount × (잔여약정개월 / 약정개월) (조기해촉 시)';
COMMENT ON COLUMN settling_support_ledger.net_amount        IS '실지급액 = support_amount - clawback_amount';
COMMENT ON COLUMN settling_support_ledger.status            IS '상태 (CALCULATED/PAID/CLAWED_BACK/CANCELLED)';

CREATE INDEX idx_ssl_agent_month ON settling_support_ledger(agent_id, settle_month);
