-- V36: 보험사 회신 인터페이스 (carrier_reconciliation_out) — 정산 결과 외부 전송 관리

-- ============================================================
-- 1. carrier_reconciliation_out — 보험사 회신 (아웃바운드) 이력
-- ============================================================

CREATE TABLE carrier_reconciliation_out (
    recon_out_id        BIGSERIAL       PRIMARY KEY,
    carrier_id          BIGINT          NOT NULL REFERENCES insurance_company(company_id),
    settle_month        CHAR(6)         NOT NULL,               -- 대상 정산월 (YYYYMM)
    file_path           VARCHAR(1000),                          -- 전송 파일 경로 (SFTP/FTP 등)
    file_name           VARCHAR(300),                           -- 파일명
    file_size           BIGINT,                                 -- 파일 크기 (bytes)
    record_count        INT,                                    -- 파일 내 레코드 수
    status              VARCHAR(20)     NOT NULL DEFAULT 'READY', -- READY/SENT/ACK/FAIL
    sent_at             TIMESTAMP,                              -- 전송 일시
    ack_at              TIMESTAMP,                              -- 보험사 수신 확인 일시
    ack_message         VARCHAR(1000),                          -- 보험사 회신 메시지
    fail_reason         VARCHAR(1000),                          -- 실패 사유
    retry_count         INT             NOT NULL DEFAULT 0,     -- 재시도 횟수
    last_retry_at       TIMESTAMP,                              -- 마지막 재시도 일시
    created_at          TIMESTAMP       NOT NULL DEFAULT NOW(),
    created_by          BIGINT,
    updated_at          TIMESTAMP,
    UNIQUE (carrier_id, settle_month)                           -- 보험사 + 정산월 단위 단건
);

COMMENT ON TABLE  carrier_reconciliation_out                IS '보험사 정산 회신 인터페이스 — 아웃바운드 전송 이력 관리';
COMMENT ON COLUMN carrier_reconciliation_out.carrier_id     IS '대상 보험사 (insurance_company.company_id)';
COMMENT ON COLUMN carrier_reconciliation_out.settle_month   IS '전송 대상 정산월 (YYYYMM)';
COMMENT ON COLUMN carrier_reconciliation_out.file_path      IS '전송 파일 저장 경로 (절대경로 또는 S3 URI)';
COMMENT ON COLUMN carrier_reconciliation_out.status         IS 'READY=전송 준비 / SENT=전송 완료 / ACK=수신 확인 / FAIL=전송 실패';
COMMENT ON COLUMN carrier_reconciliation_out.sent_at        IS '파일 전송 완료 일시';
COMMENT ON COLUMN carrier_reconciliation_out.ack_at         IS '보험사 수신 확인 일시';
COMMENT ON COLUMN carrier_reconciliation_out.retry_count    IS 'FAIL 후 재시도 횟수 (최대 재시도 횟수는 system_config 참조)';

CREATE INDEX idx_cro_carrier    ON carrier_reconciliation_out(carrier_id, settle_month);
CREATE INDEX idx_cro_status     ON carrier_reconciliation_out(status, sent_at DESC);
CREATE INDEX idx_cro_month      ON carrier_reconciliation_out(settle_month, status);

-- ============================================================
-- 2. 공통코드 RECON_OUT_STATUS
-- ============================================================

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('RECON_OUT_STATUS', '회신전송상태', 'READY/SENT/ACK/FAIL', 'Y', 380)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, code_desc, sort_order) VALUES
    ('RECON_OUT_STATUS', 'READY', '전송준비', '파일 생성 완료, 전송 대기 중',          10),
    ('RECON_OUT_STATUS', 'SENT',  '전송완료', '보험사로 파일 전송 완료 (확인 전)',      20),
    ('RECON_OUT_STATUS', 'ACK',   '수신확인', '보험사 수신 확인 완료',                  30),
    ('RECON_OUT_STATUS', 'FAIL',  '전송실패', '파일 전송 실패 — 재시도 필요',           40)
ON CONFLICT (group_code, code) DO NOTHING;
