-- V94: 지점장수당 (branch_mgr_allowance_rule + branch_mgr_allowance_ledger)
--      지점장이 소속 조직 실적에 대해 받는 수당. cap 제한 + 환수.
--      공식: allowance_amount = LEAST(base_amount × allowance_rate, cap_amount)
--            (cap_amount NULL 이면 상한 없음)

-- ============================================================
-- 1. branch_mgr_allowance_rule — 지점장수당 룰 마스터
-- ============================================================

CREATE TABLE branch_mgr_allowance_rule (
    rule_id         BIGSERIAL      PRIMARY KEY,
    position_code   VARCHAR(20)    NOT NULL,
    allowance_rate  NUMERIC(7,4)   NOT NULL,
    cap_amount      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 (allowance_rate >= 0 AND allowance_rate <= 1),
    CHECK (cap_amount IS NULL OR cap_amount >= 0)
);

COMMENT ON TABLE  branch_mgr_allowance_rule                   IS '지점장수당 룰 마스터 — 직책별 수당율 및 상한 정의';
COMMENT ON COLUMN branch_mgr_allowance_rule.rule_id           IS '룰 PK';
COMMENT ON COLUMN branch_mgr_allowance_rule.position_code     IS '직책 코드 (지점장 등)';
COMMENT ON COLUMN branch_mgr_allowance_rule.allowance_rate    IS '수당율 (소수, 예: 0.02 = 2%)';
COMMENT ON COLUMN branch_mgr_allowance_rule.cap_amount        IS '수당 상한금액 (NULL=무제한)';
COMMENT ON COLUMN branch_mgr_allowance_rule.effective_from    IS '적용 시작일';
COMMENT ON COLUMN branch_mgr_allowance_rule.effective_to      IS '적용 종료일 (NULL=현재 유효)';

CREATE INDEX idx_bmar_position_from ON branch_mgr_allowance_rule(position_code, effective_from);

-- ============================================================
-- 2. branch_mgr_allowance_ledger — 지점장수당 원장
-- ============================================================

CREATE TABLE branch_mgr_allowance_ledger (
    ledger_id         BIGSERIAL      PRIMARY KEY,
    manager_agent_id  BIGINT         NOT NULL REFERENCES agent(agent_id),
    org_id            BIGINT         NOT NULL REFERENCES organization(org_id),
    settle_month      CHAR(6)        NOT NULL,
    base_amount       NUMERIC(15,2)  NOT NULL,
    allowance_rate    NUMERIC(7,4)   NOT NULL,
    allowance_amount  NUMERIC(15,2)  NOT NULL,
    clawback_amount   NUMERIC(15,2)  NOT NULL DEFAULT 0,
    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 (allowance_rate >= 0 AND allowance_rate <= 1),
    CHECK (allowance_amount >= 0),
    CHECK (clawback_amount >= 0)
);

COMMENT ON TABLE  branch_mgr_allowance_ledger                     IS '지점장수당 원장 — 지점장이 소속 조직 실적 기준으로 받는 수당 이력';
COMMENT ON COLUMN branch_mgr_allowance_ledger.ledger_id           IS '원장 PK';
COMMENT ON COLUMN branch_mgr_allowance_ledger.manager_agent_id    IS '지점장 설계사 FK';
COMMENT ON COLUMN branch_mgr_allowance_ledger.org_id              IS '대상 조직 FK';
COMMENT ON COLUMN branch_mgr_allowance_ledger.settle_month        IS '정산 귀속월 (YYYYMM)';
COMMENT ON COLUMN branch_mgr_allowance_ledger.base_amount         IS '조직 실적 기준금액';
COMMENT ON COLUMN branch_mgr_allowance_ledger.allowance_rate      IS '적용 수당율 (branch_mgr_allowance_rule 스냅샷)';
COMMENT ON COLUMN branch_mgr_allowance_ledger.allowance_amount    IS '수당액 = LEAST(base_amount × allowance_rate, cap_amount)';
COMMENT ON COLUMN branch_mgr_allowance_ledger.clawback_amount     IS '환수액';
COMMENT ON COLUMN branch_mgr_allowance_ledger.status              IS '상태 (CALCULATED/CONFIRMED/PAID/CLAWED_BACK/CANCELLED)';

CREATE INDEX idx_bmal_manager_month ON branch_mgr_allowance_ledger(manager_agent_id, settle_month);
CREATE INDEX idx_bmal_org_month     ON branch_mgr_allowance_ledger(org_id, settle_month);
