-- V92: 시상 (award_rule + award_ledger)
--      신인도입/법인/유지율/실적 등 시상. 조건 충족 시 정률 또는 정액 지급.
--      정률(RATE): award_amount = base_amount × award_rate
--      정액(FIXED): award_amount = award_fixed

-- ============================================================
-- 1. award_rule — 시상 룰 마스터
-- ============================================================

CREATE TABLE award_rule (
    rule_id           BIGSERIAL      PRIMARY KEY,
    award_type        VARCHAR(20)    NOT NULL,
    calc_type         VARCHAR(10)    NOT NULL,
    threshold_amount  NUMERIC(15,2),
    award_rate        NUMERIC(7,4),
    award_fixed       NUMERIC(15,2),
    effective_from    DATE           NOT NULL,
    effective_to      DATE,
    created_at        TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by        BIGINT,
    updated_at        TIMESTAMP,
    CHECK (award_type IN ('NEW_RECRUIT','CORPORATE','RETENTION','PERFORMANCE')),
    CHECK (calc_type IN ('RATE','FIXED')),
    CHECK (award_rate IS NULL OR (award_rate >= 0 AND award_rate <= 1)),
    CHECK (award_fixed IS NULL OR award_fixed >= 0),
    CHECK (threshold_amount IS NULL OR threshold_amount >= 0)
);

COMMENT ON TABLE  award_rule                    IS '시상 룰 마스터 — 시상 유형별 조건 및 지급 기준';
COMMENT ON COLUMN award_rule.rule_id            IS '룰 PK';
COMMENT ON COLUMN award_rule.award_type         IS '시상 유형 (NEW_RECRUIT=신인도입/CORPORATE=법인/RETENTION=유지율/PERFORMANCE=실적)';
COMMENT ON COLUMN award_rule.calc_type          IS '계산방식 (RATE=정률 / FIXED=정액)';
COMMENT ON COLUMN award_rule.threshold_amount   IS '충족 기준 실적 (이상 시 시상 발생, NULL=조건 없음)';
COMMENT ON COLUMN award_rule.award_rate         IS '정률 시상율 (소수, calc_type=RATE 일 때 사용)';
COMMENT ON COLUMN award_rule.award_fixed        IS '정액 시상금 (calc_type=FIXED 일 때 사용)';
COMMENT ON COLUMN award_rule.effective_from     IS '적용 시작일';
COMMENT ON COLUMN award_rule.effective_to       IS '적용 종료일 (NULL=현재 유효)';

CREATE INDEX idx_awdr_type_from ON award_rule(award_type, effective_from);

-- ============================================================
-- 2. award_ledger — 시상 원장
-- ============================================================

CREATE TABLE award_ledger (
    ledger_id     BIGSERIAL      PRIMARY KEY,
    agent_id      BIGINT         NOT NULL REFERENCES agent(agent_id),
    settle_month  CHAR(6)        NOT NULL,
    award_type    VARCHAR(20)    NOT NULL,
    base_amount   NUMERIC(15,2)  NOT NULL,
    award_amount  NUMERIC(15,2)  NOT NULL,
    status        VARCHAR(20)    NOT NULL DEFAULT 'CALCULATED',
    created_at    TIMESTAMP      NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMP,
    CHECK (award_type IN ('NEW_RECRUIT','CORPORATE','RETENTION','PERFORMANCE')),
    CHECK (status IN ('CALCULATED','CONFIRMED','PAID','CANCELLED')),
    CHECK (base_amount >= 0),
    CHECK (award_amount >= 0)
);

COMMENT ON TABLE  award_ledger               IS '시상 원장 — 설계사별 시상 발생 이력';
COMMENT ON COLUMN award_ledger.ledger_id     IS '원장 PK';
COMMENT ON COLUMN award_ledger.agent_id      IS '설계사 FK';
COMMENT ON COLUMN award_ledger.settle_month  IS '정산 귀속월 (YYYYMM)';
COMMENT ON COLUMN award_ledger.award_type    IS '시상 유형 (NEW_RECRUIT/CORPORATE/RETENTION/PERFORMANCE)';
COMMENT ON COLUMN award_ledger.base_amount   IS '기준 실적 (인정실적 합산 등)';
COMMENT ON COLUMN award_ledger.award_amount  IS '시상금 = base×award_rate (RATE) 또는 award_fixed (FIXED)';
COMMENT ON COLUMN award_ledger.status        IS '상태 (CALCULATED/CONFIRMED/PAID/CANCELLED)';

CREATE INDEX idx_awdl_agent_month ON award_ledger(agent_id, settle_month);
CREATE INDEX idx_awdl_type_month  ON award_ledger(award_type, settle_month);
