-- V103: 수수료 시뮬레이터 (commission_simulation)
--       보험료/요율/지급률 입력 → 수수료·세금·실지급·예상환수 산출. 시나리오 저장.
--       공식:
--         company_payment     = premium_amount × company_rate
--         agent_commission    = company_payment × payout_rate
--         withholding_tax     = agent_commission × 0.033  (3.3%, HALF_UP)
--         net_amount          = agent_commission - withholding_tax

CREATE TABLE commission_simulation (
    sim_id               BIGSERIAL      PRIMARY KEY,
    sim_name             VARCHAR(100)   NOT NULL,
    premium_amount       NUMERIC(15,2)  NOT NULL,
    company_rate         NUMERIC(7,4)   NOT NULL,
    payout_rate          NUMERIC(7,4)   NOT NULL,
    commission_year      INT            NOT NULL,
    company_payment      NUMERIC(15,2)  NOT NULL,
    agent_commission     NUMERIC(15,2)  NOT NULL,
    withholding_tax      NUMERIC(15,2)  NOT NULL,
    net_amount           NUMERIC(15,2)  NOT NULL,
    expected_chargeback  NUMERIC(15,2),
    created_at           TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by           BIGINT
);

COMMENT ON TABLE  commission_simulation                    IS '수수료 시뮬레이터 — 시나리오별 수수료 산출 결과 저장';
COMMENT ON COLUMN commission_simulation.sim_name           IS '시나리오명';
COMMENT ON COLUMN commission_simulation.premium_amount     IS '입력 보험료';
COMMENT ON COLUMN commission_simulation.company_rate       IS '보험사 요율 (소수)';
COMMENT ON COLUMN commission_simulation.payout_rate        IS '지급률 (소수)';
COMMENT ON COLUMN commission_simulation.commission_year    IS '회차';
COMMENT ON COLUMN commission_simulation.company_payment    IS '보험사 지급액 = 보험료 × company_rate';
COMMENT ON COLUMN commission_simulation.agent_commission   IS '설계사 수수료 = 보험사지급액 × payout_rate';
COMMENT ON COLUMN commission_simulation.withholding_tax    IS '원천세 = 설계사수수료 × 3.3%';
COMMENT ON COLUMN commission_simulation.net_amount         IS '실지급 = 설계사수수료 - 원천세';
COMMENT ON COLUMN commission_simulation.expected_chargeback IS '예상 환수액 (13개월 50% 등 시나리오)';
COMMENT ON COLUMN commission_simulation.created_by         IS '시뮬레이션 요청자 FK(users)';

CREATE INDEX idx_simu_created_by ON commission_simulation(created_by, created_at);
CREATE INDEX idx_simu_created_at ON commission_simulation(created_at DESC);
