-- V93: 생보운영지원수수료 (life_support_rule + life_support_ledger)
--      생보 계약 운영지원 수수료. 설계사 등급별 지급률 × 생보 실적 + 환수.
--      공식: support_amount = base_amount × support_rate
--            net_amount = support_amount - clawback_amount

-- ============================================================
-- 1. life_support_rule — 생보운영지원수수료 룰 마스터
-- ============================================================

CREATE TABLE life_support_rule (
    rule_id        BIGSERIAL      PRIMARY KEY,
    agent_grade    VARCHAR(20)    NOT NULL,
    support_rate   NUMERIC(7,4)   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_rate >= 0 AND support_rate <= 1)
);

COMMENT ON TABLE  life_support_rule                IS '생보운영지원수수료 룰 마스터 — 등급별 생보 운영지원 수수료율';
COMMENT ON COLUMN life_support_rule.rule_id        IS '룰 PK';
COMMENT ON COLUMN life_support_rule.agent_grade    IS '설계사 등급';
COMMENT ON COLUMN life_support_rule.support_rate   IS '지급률 (소수, 예: 0.03 = 3%)';
COMMENT ON COLUMN life_support_rule.effective_from IS '적용 시작일';
COMMENT ON COLUMN life_support_rule.effective_to   IS '적용 종료일 (NULL=현재 유효)';

CREATE INDEX idx_lsr_grade_from ON life_support_rule(agent_grade, effective_from);

-- ============================================================
-- 2. life_support_ledger — 생보운영지원수수료 원장
-- ============================================================

CREATE TABLE life_support_ledger (
    ledger_id        BIGSERIAL      PRIMARY KEY,
    agent_id         BIGINT         NOT NULL REFERENCES agent(agent_id),
    settle_month     CHAR(6)        NOT NULL,
    base_amount      NUMERIC(15,2)  NOT NULL,
    support_rate     NUMERIC(7,4)   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','CONFIRMED','PAID','CLAWED_BACK','CANCELLED')),
    CHECK (base_amount >= 0),
    CHECK (support_rate >= 0 AND support_rate <= 1),
    CHECK (support_amount >= 0),
    CHECK (clawback_amount >= 0)
);

COMMENT ON TABLE  life_support_ledger                   IS '생보운영지원수수료 원장 — 설계사별 생보 운영지원 수수료 발생/환수 이력';
COMMENT ON COLUMN life_support_ledger.ledger_id         IS '원장 PK';
COMMENT ON COLUMN life_support_ledger.agent_id          IS '설계사 FK';
COMMENT ON COLUMN life_support_ledger.settle_month      IS '정산 귀속월 (YYYYMM)';
COMMENT ON COLUMN life_support_ledger.base_amount       IS '생보 실적 기준금액';
COMMENT ON COLUMN life_support_ledger.support_rate      IS '적용 지급률 (life_support_rule 스냅샷)';
COMMENT ON COLUMN life_support_ledger.support_amount    IS '지원수수료 = base_amount × support_rate';
COMMENT ON COLUMN life_support_ledger.clawback_amount   IS '환수액';
COMMENT ON COLUMN life_support_ledger.net_amount        IS '실지급액 = support_amount - clawback_amount';
COMMENT ON COLUMN life_support_ledger.status            IS '상태 (CALCULATED/CONFIRMED/PAID/CLAWED_BACK/CANCELLED)';

CREATE INDEX idx_lsl_agent_month ON life_support_ledger(agent_id, settle_month);
