-- V3: 수수료 규정 (보험사율, 지급율, 오버라이드, 환수, 예외코드)

CREATE TABLE commission_rate (
    rate_id          SERIAL PRIMARY KEY,
    product_id       BIGINT         NOT NULL REFERENCES product(product_id),
    commission_year  INT            NOT NULL,
    rate_pct         DECIMAL(7,4)   NOT NULL,
    pay_method_cond  VARCHAR(50),
    min_premium      DECIMAL(15,2),
    effective_from   DATE           NOT NULL,
    effective_to     DATE,
    version          INT            NOT NULL DEFAULT 1,
    created_at       TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by       BIGINT
);
CREATE INDEX idx_cr_product ON commission_rate(product_id, effective_from);

CREATE TABLE payout_rule (
    rule_id           SERIAL PRIMARY KEY,
    grade_id          BIGINT        NOT NULL REFERENCES grade(grade_id),
    insurance_type    VARCHAR(30)   NOT NULL,
    commission_year   INT           NOT NULL,
    payout_pct        DECIMAL(7,4)  NOT NULL,
    pay_method_cond   VARCHAR(50),
    performance_grade VARCHAR(20),
    effective_from    DATE          NOT NULL,
    effective_to      DATE,
    version           INT           NOT NULL DEFAULT 1,
    created_at        TIMESTAMP     NOT NULL DEFAULT NOW(),
    created_by        BIGINT
);
CREATE INDEX idx_pr_grade ON payout_rule(grade_id, insurance_type, effective_from);

CREATE TABLE override_rule (
    override_id          SERIAL PRIMARY KEY,
    from_grade           BIGINT        NOT NULL,
    to_grade             BIGINT        NOT NULL,
    override_pct         DECIMAL(7,4)  NOT NULL,
    calc_type            VARCHAR(20)   NOT NULL,    -- DIRECT / TREE
    insurance_type_cond  VARCHAR(30),
    cap_amount           DECIMAL(15,2),
    effective_from       DATE          NOT NULL,
    effective_to         DATE
);
CREATE INDEX idx_or_grade ON override_rule(from_grade, to_grade, effective_from);

CREATE TABLE chargeback_rule (
    cb_rule_id          SERIAL PRIMARY KEY,
    company_code        VARCHAR(20),
    insurance_type      VARCHAR(30),
    lapse_month_from    INT          NOT NULL,
    lapse_month_to      INT          NOT NULL,
    cb_rate             DECIMAL(7,4) NOT NULL,
    include_override    CHAR(1)      NOT NULL DEFAULT 'N',
    installment_allowed CHAR(1)      NOT NULL DEFAULT 'N',
    max_installments    INT,
    effective_from      DATE         NOT NULL,
    effective_to        DATE
);
CREATE INDEX idx_cb_lookup ON chargeback_rule(company_code, insurance_type, effective_from);

CREATE TABLE exception_type_code (
    exception_code     VARCHAR(20)  PRIMARY KEY,
    exception_name     VARCHAR(100) NOT NULL,
    category           VARCHAR(30),
    direction          VARCHAR(10)  NOT NULL,        -- PLUS / MINUS
    auto_calc_yn       CHAR(1)      NOT NULL DEFAULT 'N',
    approval_required  CHAR(1)      NOT NULL DEFAULT 'N',
    tax_included       CHAR(1)      NOT NULL DEFAULT 'N',
    description        VARCHAR(200),
    is_active          CHAR(1)      NOT NULL DEFAULT 'Y'
);
