-- V63: 설계사 연말 사업소득 집계 테이블

CREATE TABLE agent_annual_income (
    agent_id          BIGINT         NOT NULL REFERENCES agent(agent_id),
    settle_year       INT            NOT NULL,
    total_commission  NUMERIC(18,2)  NOT NULL DEFAULT 0,
    total_tax_withheld NUMERIC(18,2) NOT NULL DEFAULT 0,
    total_local_tax   NUMERIC(18,2)  NOT NULL DEFAULT 0,
    business_income   NUMERIC(18,2)  NOT NULL DEFAULT 0,
    generated_at      TIMESTAMP      NOT NULL DEFAULT now(),
    created_at        TIMESTAMP      NOT NULL DEFAULT now(),
    updated_at        TIMESTAMP,
    PRIMARY KEY (agent_id, settle_year)
);

COMMENT ON TABLE agent_annual_income IS '설계사 연말 사업소득 집계';
COMMENT ON COLUMN agent_annual_income.business_income IS 'total_commission - total_tax_withheld - total_local_tax';

CREATE INDEX idx_aai_year ON agent_annual_income(settle_year);

-- EXPLAIN ANALYZE: settle_year 단독 집계 — idx_aai_year Index Scan 확인
-- EXPLAIN ANALYZE
--   SELECT settle_year, SUM(business_income)
--   FROM agent_annual_income
--   GROUP BY settle_year;
