-- V42: 갱신수수료 + 부활수수료 (rule + ledger 각 2쌍)
--      갱신: 자동차/실비/단기보험 갱신 계약 시 정률 또는 정액 수수료
--      부활: 실효 후 부활 시 수수료 + 기존 환수액 일부 환원

-- ============================================================
-- 1. renewal_commission_rule — 갱신수수료율 마스터
-- ============================================================

CREATE TABLE renewal_commission_rule (
    rule_id          BIGSERIAL     PRIMARY KEY,
    product_id       BIGINT        NOT NULL REFERENCES product(product_id),
    pay_type         VARCHAR(20)   NOT NULL DEFAULT 'RATE',
    rate_value       NUMERIC(7,4),           -- RATE 방식: 갱신 보험료 대비 비율
    fixed_amount     NUMERIC(15,2),          -- FIXED 방식: 건당 고정 금액
    effective_from   DATE          NOT NULL,
    effective_to     DATE,
    created_at       TIMESTAMP     NOT NULL DEFAULT NOW(),
    created_by       BIGINT,
    updated_at       TIMESTAMP,
    UNIQUE (product_id, effective_from),
    CHECK (pay_type IN ('RATE','FIXED')),
    CHECK (
        (pay_type = 'RATE'  AND rate_value IS NOT NULL) OR
        (pay_type = 'FIXED' AND fixed_amount IS NOT NULL)
    )
);

COMMENT ON TABLE  renewal_commission_rule             IS '갱신수수료율 마스터 — 자동차/실비/단기보험 갱신 대상 상품별 룰';
COMMENT ON COLUMN renewal_commission_rule.pay_type    IS 'RATE=갱신보험료 대비 비율 / FIXED=건당 고정금액';
COMMENT ON COLUMN renewal_commission_rule.rate_value  IS 'RATE 방식 비율 (소수, 예: 0.02 = 2%)';
COMMENT ON COLUMN renewal_commission_rule.fixed_amount IS 'FIXED 방식 건당 지급액';

CREATE INDEX idx_rcr_product ON renewal_commission_rule(product_id, effective_from);

-- ============================================================
-- 2. renewal_commission_ledger — 갱신수수료 원장
-- ============================================================

CREATE TABLE renewal_commission_ledger (
    ledger_id          BIGSERIAL      PRIMARY KEY,
    contract_id        BIGINT         NOT NULL REFERENCES contract(contract_id),
    agent_id           BIGINT         NOT NULL REFERENCES agent(agent_id),
    rule_id            BIGINT         REFERENCES renewal_commission_rule(rule_id),
    settle_month       CHAR(6)        NOT NULL,
    renewal_date       DATE           NOT NULL,
    renewal_premium    NUMERIC(15,2)  NOT NULL,
    commission_amount  NUMERIC(15,2)  NOT NULL,
    pay_type           VARCHAR(20)    NOT NULL DEFAULT 'RATE',
    status             VARCHAR(20)    NOT NULL DEFAULT 'CALCULATED',
    created_at         TIMESTAMP      NOT NULL DEFAULT NOW(),
    updated_at         TIMESTAMP,
    CHECK (pay_type IN ('RATE','FIXED')),
    CHECK (status IN ('CALCULATED','CONFIRMED','PAID','CANCELLED'))
);

COMMENT ON TABLE  renewal_commission_ledger               IS '갱신수수료 원장 — 계약 갱신 1회당 발생';
COMMENT ON COLUMN renewal_commission_ledger.renewal_date  IS '계약 갱신일';
COMMENT ON COLUMN renewal_commission_ledger.renewal_premium IS '갱신 보험료';
COMMENT ON COLUMN renewal_commission_ledger.commission_amount IS '갱신수수료 (rate*premium 또는 fixed_amount)';

CREATE INDEX idx_rcl_agent_month ON renewal_commission_ledger(agent_id, settle_month);
CREATE INDEX idx_rcl_contract    ON renewal_commission_ledger(contract_id);

-- ============================================================
-- 3. reinstatement_commission_rule — 부활수수료율 마스터
-- ============================================================

CREATE TABLE reinstatement_commission_rule (
    rule_id             BIGSERIAL     PRIMARY KEY,
    product_id          BIGINT        NOT NULL REFERENCES product(product_id),
    pay_type            VARCHAR(20)   NOT NULL DEFAULT 'RATE',
    rate_value          NUMERIC(7,4),           -- RATE: 부활 보험료 대비 비율
    fixed_amount        NUMERIC(15,2),           -- FIXED: 건당 고정 금액
    chargeback_reversal_rate NUMERIC(7,4) NOT NULL DEFAULT 0,
    -- 부활 시 기존 환수액 중 환원 비율 (예: 0.5 = 50% 환원)
    effective_from      DATE          NOT NULL,
    effective_to        DATE,
    created_at          TIMESTAMP     NOT NULL DEFAULT NOW(),
    created_by          BIGINT,
    updated_at          TIMESTAMP,
    UNIQUE (product_id, effective_from),
    CHECK (pay_type IN ('RATE','FIXED')),
    CHECK (chargeback_reversal_rate >= 0 AND chargeback_reversal_rate <= 1)
);

COMMENT ON TABLE  reinstatement_commission_rule                       IS '부활수수료율 마스터 — 실효 후 부활 시 적용 룰';
COMMENT ON COLUMN reinstatement_commission_rule.chargeback_reversal_rate IS '기존 환수액 중 부활 시 환원 비율 (0~1)';

CREATE INDEX idx_rsr_product ON reinstatement_commission_rule(product_id, effective_from);

-- ============================================================
-- 4. reinstatement_commission_ledger — 부활수수료 원장
-- ============================================================

CREATE TABLE reinstatement_commission_ledger (
    ledger_id              BIGSERIAL      PRIMARY KEY,
    contract_id            BIGINT         NOT NULL REFERENCES contract(contract_id),
    agent_id               BIGINT         NOT NULL REFERENCES agent(agent_id),
    rule_id                BIGINT         REFERENCES reinstatement_commission_rule(rule_id),
    settle_month           CHAR(6)        NOT NULL,
    reinstatement_date     DATE           NOT NULL,
    reinstatement_premium  NUMERIC(15,2)  NOT NULL,
    commission_amount      NUMERIC(15,2)  NOT NULL,
    chargeback_reversal    NUMERIC(15,2)  NOT NULL DEFAULT 0,
    -- 기존 환수액 중 환원금 (부활 인센티브)
    net_commission         NUMERIC(15,2)  NOT NULL,
    -- 최종 수수료 = commission_amount + chargeback_reversal
    pay_type               VARCHAR(20)    NOT NULL DEFAULT 'RATE',
    status                 VARCHAR(20)    NOT NULL DEFAULT 'CALCULATED',
    created_at             TIMESTAMP      NOT NULL DEFAULT NOW(),
    updated_at             TIMESTAMP,
    CHECK (pay_type IN ('RATE','FIXED')),
    CHECK (status IN ('CALCULATED','CONFIRMED','PAID','CANCELLED'))
);

COMMENT ON TABLE  reinstatement_commission_ledger                   IS '부활수수료 원장 — 실효 계약 부활 1건당 발생';
COMMENT ON COLUMN reinstatement_commission_ledger.reinstatement_date IS '계약 부활일';
COMMENT ON COLUMN reinstatement_commission_ledger.chargeback_reversal IS '부활로 인한 환수액 환원금';
COMMENT ON COLUMN reinstatement_commission_ledger.net_commission     IS '최종 수수료 = 부활수수료 + 환수환원금';

CREATE INDEX idx_rsl_agent_month ON reinstatement_commission_ledger(agent_id, settle_month);
CREATE INDEX idx_rsl_contract    ON reinstatement_commission_ledger(contract_id);
