-- V99: 등급평가 (grade_eval_rule + agent_grade_evaluation)
--      설계사 실적 점수로 등급 산정.
--      공식: evaluated_grade = score 이상인 최대 min_score 룰의 grade (Service 결정)

-- ============================================================
-- 1. grade_eval_rule — 점수구간별 등급 룰 마스터
-- ============================================================

CREATE TABLE grade_eval_rule (
    rule_id        BIGSERIAL   PRIMARY KEY,
    grade_id       INT         NOT NULL REFERENCES grade(grade_id),
    min_score      NUMERIC(7,2) NOT NULL,
    effective_from DATE        NOT NULL,
    effective_to   DATE,
    created_at     TIMESTAMP   NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE  grade_eval_rule               IS '등급평가 룰 — 실적점수 구간별 등급 매핑';
COMMENT ON COLUMN grade_eval_rule.grade_id       IS '부여 등급 FK(grade)';
COMMENT ON COLUMN grade_eval_rule.min_score       IS '최소 실적점수 (이 점수 이상이면 해당 등급)';
COMMENT ON COLUMN grade_eval_rule.effective_from  IS '룰 적용 시작일';
COMMENT ON COLUMN grade_eval_rule.effective_to    IS '룰 적용 종료일 (NULL=현재 유효)';

CREATE INDEX idx_ger_grade_from ON grade_eval_rule(grade_id, effective_from);
CREATE INDEX idx_ger_score      ON grade_eval_rule(min_score);

-- ============================================================
-- 2. agent_grade_evaluation — 설계사 등급평가 원장
-- ============================================================

CREATE TABLE agent_grade_evaluation (
    eval_id             BIGSERIAL    PRIMARY KEY,
    agent_id            INT          NOT NULL REFERENCES agent(agent_id),
    eval_period         CHAR(6)      NOT NULL,
    performance_score   NUMERIC(7,2) NOT NULL,
    prev_grade_id       INT          REFERENCES grade(grade_id),
    evaluated_grade_id  INT          REFERENCES grade(grade_id),
    status              VARCHAR(20)  NOT NULL DEFAULT 'EVALUATED',
    created_at          TIMESTAMP    NOT NULL DEFAULT NOW(),
    UNIQUE (agent_id, eval_period),
    CHECK (status IN ('EVALUATED','APPLIED'))
);

COMMENT ON TABLE  agent_grade_evaluation                  IS '설계사 등급평가 원장 — 월별 실적점수 기반 등급 산정 결과';
COMMENT ON COLUMN agent_grade_evaluation.agent_id          IS '대상 설계사 FK';
COMMENT ON COLUMN agent_grade_evaluation.eval_period        IS '평가 기준월 (YYYYMM)';
COMMENT ON COLUMN agent_grade_evaluation.performance_score  IS '실적 점수';
COMMENT ON COLUMN agent_grade_evaluation.prev_grade_id      IS '기존(직전) 등급 FK';
COMMENT ON COLUMN agent_grade_evaluation.evaluated_grade_id IS '산정 등급 FK';
COMMENT ON COLUMN agent_grade_evaluation.status             IS '상태 (EVALUATED=평가완료/APPLIED=반영완료)';

CREATE INDEX idx_age_agent_period ON agent_grade_evaluation(agent_id, eval_period);
CREATE INDEX idx_age_status       ON agent_grade_evaluation(status, eval_period);
