-- V25: 수수료 명세서 (commission_statement, 공통코드)

-- ============================================================
-- 1. commission_statement — 명세서 발급 마스터
--
-- 재발급 정책: UNIQUE(agent_id, settle_month, statement_type) 유지.
-- 재발급은 기존 row를 PUT으로 갱신(issued_at, file_path 등 업데이트)
-- — 재발급 빈도가 낮고 명세서 이력 추적보다 최신본이 중요하기 때문.
-- ============================================================

CREATE TABLE commission_statement (
    statement_id        BIGSERIAL       PRIMARY KEY,
    agent_id            BIGINT          NOT NULL REFERENCES agent(agent_id),
    settle_month        CHAR(6)         NOT NULL,
    statement_type      VARCHAR(20)     NOT NULL,

    -- 발급 시점 집계 스냅샷 (동결값)
    gross_amount        NUMERIC(15,2)   NOT NULL,
    income_tax_amount   NUMERIC(15,2)   NOT NULL,
    local_tax_amount    NUMERIC(15,2)   NOT NULL,
    vat_amount          NUMERIC(15,2)   NOT NULL DEFAULT 0,
    deduction_amount    NUMERIC(15,2)   NOT NULL DEFAULT 0,
    net_amount          NUMERIC(15,2)   NOT NULL,

    -- 발급 정보
    issued_at           TIMESTAMP       NOT NULL,
    issued_by           VARCHAR(50),
    file_path           VARCHAR(500),
    file_format         VARCHAR(10),
    issue_status        VARCHAR(20)     NOT NULL DEFAULT 'GENERATED',

    -- 감사 4종
    created_at          TIMESTAMP       NOT NULL DEFAULT NOW(),
    created_by          BIGINT,
    updated_at          TIMESTAMP,
    updated_by          BIGINT,

    UNIQUE (agent_id, settle_month, statement_type)
);

CREATE INDEX idx_cs_agent_month ON commission_statement(agent_id, settle_month, statement_type DESC);
CREATE INDEX idx_cs_issued_at   ON commission_statement(issued_at DESC);

-- ============================================================
-- 2. 공통코드 그룹 STATEMENT_TYPE / STATEMENT_STATUS 추가
-- ============================================================

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('STATEMENT_TYPE',   '명세서유형', 'MONTHLY/ANNUAL/PROOF',                  'Y', 230),
    ('STATEMENT_STATUS', '명세서상태', 'GENERATED/SENT/DOWNLOADED/CANCELLED',   'Y', 240)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, code_desc, sort_order) VALUES
    ('STATEMENT_TYPE', 'MONTHLY', '월별',     '월별 수수료 명세서',        10),
    ('STATEMENT_TYPE', 'ANNUAL',  '연간누계', '연간 수수료 누계 명세서',   20),
    ('STATEMENT_TYPE', 'PROOF',   '소득증빙', '소득증빙용 발급 명세서',    30),
    ('STATEMENT_STATUS', 'GENERATED',   '생성됨',       '명세서 파일 생성 완료',     10),
    ('STATEMENT_STATUS', 'SENT',        '발송',         '이메일 등으로 발송 완료',   20),
    ('STATEMENT_STATUS', 'DOWNLOADED',  '다운로드됨',   '설계사 다운로드 확인',      30),
    ('STATEMENT_STATUS', 'CANCELLED',   '취소',         '명세서 취소 처리',          40)
ON CONFLICT (group_code, code) DO NOTHING;
