-- rc 모듈 (대사) 상세 테이블. Prefix 30 (00-base 다음, 35-st 이전에 로드).
-- ECPG 서버/배치가 참조하는 대사 로그/예외/차이/경보/요약 테이블 + 시퀀스.

-- ---------------------------------------------------------------------------
-- 대사 로그 (rc_match_log) - 서비스별 대사 결과 1행
-- ---------------------------------------------------------------------------
CREATE SEQUENCE IF NOT EXISTS rc_match_seq;
CREATE TABLE IF NOT EXISTS rc_match_log (
    match_id     BIGINT PRIMARY KEY,
    purchase_id  BIGINT NOT NULL,
    match_type   TEXT NOT NULL,                 -- APPR_ACQ/ACQ_DEP/3WAY/PARTIAL/MANUAL/RETURN/DELAY/TOLERANCE
    amount       BIGINT NOT NULL DEFAULT 0,
    status       TEXT NOT NULL DEFAULT 'MATCHED',-- MATCHED/HOLD/EXCEPTION/RELEASED/CONFIRMED/ROLLBACK
    biz_date     DATE NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- 대사 예외 (rc_exception)
-- ---------------------------------------------------------------------------
CREATE SEQUENCE IF NOT EXISTS rc_exc_seq;
CREATE TABLE IF NOT EXISTS rc_exception (
    exc_id       BIGINT PRIMARY KEY,
    purchase_id  BIGINT NOT NULL,
    reason       TEXT NOT NULL DEFAULT '',
    biz_date     DATE NOT NULL,
    resolved     BOOLEAN NOT NULL DEFAULT false,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- 대사 차이 (rc_diff) - 금액/건수 차이 리포트 라인
-- ---------------------------------------------------------------------------
CREATE SEQUENCE IF NOT EXISTS rc_diff_seq;
CREATE TABLE IF NOT EXISTS rc_diff (
    diff_id      BIGINT PRIMARY KEY,
    purchase_id  BIGINT NOT NULL DEFAULT 0,
    expected_amt BIGINT NOT NULL DEFAULT 0,
    actual_amt   BIGINT NOT NULL DEFAULT 0,
    diff_amt     BIGINT NOT NULL DEFAULT 0,
    diff_kind    TEXT NOT NULL DEFAULT 'AMT',   -- AMT / CNT
    biz_date     DATE NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- 대사 경보 (rc_alert)
-- ---------------------------------------------------------------------------
CREATE SEQUENCE IF NOT EXISTS rc_alert_seq;
CREATE TABLE IF NOT EXISTS rc_alert (
    alert_id     BIGINT PRIMARY KEY,
    purchase_id  BIGINT NOT NULL DEFAULT 0,
    lvl          TEXT NOT NULL DEFAULT 'INFO',
    message      TEXT NOT NULL DEFAULT '',
    biz_date     DATE NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- 대사 요약 (rc_summary) - 영업일 1행 (배치/RC_SUMMARY가 upsert)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rc_summary (
    biz_date       DATE PRIMARY KEY,
    matched_cnt    BIGINT NOT NULL DEFAULT 0,
    unmatched_cnt  BIGINT NOT NULL DEFAULT 0,
    gross_amount   BIGINT NOT NULL DEFAULT 0,
    updated_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);