acquire-core-x/db/schema.d/72-security.sql

-- ===========================================================================
-- 72-security.sql : 패리티 고도화 (Wave J) — 카드보안·자금연동·수수료·분쟁·리포팅
--   실제 매입사 대비 갭이던 PCI/3DS/AVS·CVV, 은행 rails 펀딩, DCC, 채그백수수료,
--   retrieval, 규제보고를 데이터 모델로 추가. 기존 구조 불변(추가만).
-- ===========================================================================

-- ---------------------------------------------------------------------------
-- J-1. 카드보안
-- ---------------------------------------------------------------------------
-- 토큰볼트: PAN 을 토큰으로 치환, 원문은 저장하지 않음(마스킹만 보관). PCI 스코프 축소.
CREATE SEQUENCE IF NOT EXISTS card_token_seq START 4000000;
CREATE TABLE IF NOT EXISTS card_vault (
    token        TEXT PRIMARY KEY,            -- 표면 토큰(비민감). 예: TKN-xxxxxxxx
    pan_masked   TEXT NOT NULL,               -- 4111-****-****-1111 (마스킹)
    pan_hash     TEXT NOT NULL,               -- SHA-256(PAN) — 중복탐지/조회용(원문 불가역)
    bin          TEXT NOT NULL,               -- 앞 6자리(라우팅)
    last4        TEXT NOT NULL,
    card_type    TEXT NOT NULL DEFAULT 'CREDIT',
    issuer       TEXT NOT NULL DEFAULT 'BC',
    exp_ym       TEXT,                         -- YYYYMM (만료)
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_vault_hash ON card_vault(pan_hash);
CREATE INDEX IF NOT EXISTS ix_vault_bin  ON card_vault(bin);

-- 3DS/SCA 인증 결과 (비대면 거래 인증·책임전환)
CREATE SEQUENCE IF NOT EXISTS au_3ds_seq START 500000;
CREATE TABLE IF NOT EXISTS au_3ds (
    tds_id       BIGINT PRIMARY KEY,
    purchase_id  BIGINT,
    token        TEXT,
    version      TEXT NOT NULL DEFAULT '2.2.0',  -- 3DS 버전
    eci          TEXT NOT NULL,                  -- 05=완전인증 06=시도 07=미인증
    cavv         TEXT,                           -- 인증검증값(Base64)
    ds_trans_id  TEXT,                           -- Directory Server txn id
    auth_status  TEXT NOT NULL,                  -- Y=인증 A=시도 N=실패 U=불가 R=거부
    liability    TEXT NOT NULL DEFAULT 'ISSUER', -- 책임주체: ISSUER(전환됨)/MERCHANT
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_3ds_pid ON au_3ds(purchase_id);

-- AVS/CVV 검증 응답
CREATE SEQUENCE IF NOT EXISTS au_verify_seq START 550000;
CREATE TABLE IF NOT EXISTS au_verify (
    vrf_id       BIGINT PRIMARY KEY,
    purchase_id  BIGINT,
    token        TEXT,
    avs_code     TEXT NOT NULL DEFAULT 'U',   -- Y/A/Z/N/U (주소·우편일치)
    cvv_code     TEXT NOT NULL DEFAULT 'U',   -- M=일치 N=불일치 P=미처리 U=불가
    risk_score   INTEGER NOT NULL DEFAULT 0,  -- 0~999
    decision     TEXT NOT NULL DEFAULT 'APPROVE', -- APPROVE/DECLINE/REVIEW
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_verify_pid ON au_verify(purchase_id);

-- ---------------------------------------------------------------------------
-- J-2. 자금이동 실연동
-- ---------------------------------------------------------------------------
-- 펀딩 지시/반환 (펌뱅킹/ACH 등가). 순액정산 결과를 은행 rails 로 지급 지시.
CREATE SEQUENCE IF NOT EXISTS st_fundinstr_seq START 850000;
CREATE TABLE IF NOT EXISTS st_funding_instr (
    instr_id     BIGINT PRIMARY KEY,
    merchant_id  TEXT NOT NULL,
    biz_date     DATE NOT NULL,
    bank_code    TEXT NOT NULL DEFAULT 'KB',   -- 입금은행
    account_no   TEXT NOT NULL,
    amount       BIGINT NOT NULL,              -- 지급액(펀딩 deposit)
    rail         TEXT NOT NULL DEFAULT 'FIRMBANK', -- FIRMBANK/ACH/RTGS
    status       TEXT NOT NULL DEFAULT 'SENT', -- SENT/CONFIRMED/RETURNED
    return_code  TEXT,                         -- 반환사유(계좌오류 등)
    sent_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    confirmed_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS ix_fundinstr_merch ON st_funding_instr(merchant_id, biz_date);

-- DCC (해외카드 원화가맹점 결제시 통화선택) — 승인시점 환율고정.
CREATE SEQUENCE IF NOT EXISTS ac_dcc_seq START 450000;
CREATE TABLE IF NOT EXISTS ac_dcc (
    dcc_id        BIGINT PRIMARY KEY,
    purchase_id   BIGINT NOT NULL,
    base_ccy      TEXT NOT NULL DEFAULT 'KRW', -- 가맹점 통화
    txn_ccy       TEXT NOT NULL,               -- 카드소지자 통화(USD/JPY/EUR...)
    base_amount   BIGINT NOT NULL,             -- 원화 금액
    txn_amount    NUMERIC(18,2) NOT NULL,      -- 외화 금액
    fx_rate       NUMERIC(14,6) NOT NULL,      -- 고정환율
    markup_bps    INTEGER NOT NULL DEFAULT 300,-- DCC 마크업(3%)
    opted_in      BOOLEAN NOT NULL DEFAULT true,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_dcc_pid ON ac_dcc(purchase_id);

-- ---------------------------------------------------------------------------
-- J-3. 수수료·분쟁 운영
-- ---------------------------------------------------------------------------
-- 채그백 수수료 (분쟁 발생시 가맹점 부과 건당 수수료)
CREATE SEQUENCE IF NOT EXISTS st_cbfee_seq START 950000;
CREATE TABLE IF NOT EXISTS st_cb_fee (
    cbfee_id     BIGINT PRIMARY KEY,
    dispute_id   BIGINT NOT NULL,
    merchant_id  TEXT NOT NULL,
    fee_amount   BIGINT NOT NULL DEFAULT 15000, -- 건당 채그백 수수료(원)
    stage        TEXT NOT NULL,                 -- 어느 단계에서 부과
    billed       BOOLEAN NOT NULL DEFAULT false,
    biz_date     DATE NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- retrieval request (매출전표 조회요청 — 채그백 전 단계)
CREATE SEQUENCE IF NOT EXISTS ac_retrieval_seq START 620000;
CREATE TABLE IF NOT EXISTS ac_retrieval (
    retr_id      BIGINT PRIMARY KEY,
    purchase_id  BIGINT NOT NULL,
    merchant_id  TEXT NOT NULL,
    reason       TEXT NOT NULL,                -- 조회사유
    status       TEXT NOT NULL DEFAULT 'OPEN', -- OPEN/FULFILLED/EXPIRED
    sla_due      DATE NOT NULL,                -- SLA 마감일
    fulfilled_at TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 분쟁 SLA 타이머 컬럼 추가 (기존 ac_dispute 확장)
ALTER TABLE ac_dispute ADD COLUMN IF NOT EXISTS sla_due DATE;
ALTER TABLE ac_dispute ADD COLUMN IF NOT EXISTS fraud_flag BOOLEAN NOT NULL DEFAULT false;

-- ---------------------------------------------------------------------------
-- J-4. 리포팅·규제
-- ---------------------------------------------------------------------------
-- 가맹점 월 명세서
CREATE SEQUENCE IF NOT EXISTS rp_stmt_seq START 700000;
CREATE TABLE IF NOT EXISTS rp_merchant_stmt (
    stmt_id      BIGINT PRIMARY KEY,
    merchant_id  TEXT NOT NULL,
    stmt_month   TEXT NOT NULL,               -- YYYYMM
    gross_sales  BIGINT NOT NULL DEFAULT 0,
    txn_count    BIGINT NOT NULL DEFAULT 0,
    total_fees   BIGINT NOT NULL DEFAULT 0,
    interchange  BIGINT NOT NULL DEFAULT 0,
    scheme_fee   BIGINT NOT NULL DEFAULT 0,
    markup       BIGINT NOT NULL DEFAULT 0,
    cb_fees      BIGINT NOT NULL DEFAULT 0,
    net_deposit  BIGINT NOT NULL DEFAULT 0,
    issued_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (merchant_id, stmt_month)
);

-- 규제보고 (여신전문금융업법 정산주기 준수 등)
CREATE SEQUENCE IF NOT EXISTS rp_reg_seq START 710000;
CREATE TABLE IF NOT EXISTS rp_regulatory (
    reg_id       BIGINT PRIMARY KEY,
    report_type  TEXT NOT NULL,               -- SETTLE_CYCLE/FEE_DISCLOSURE/TXN_VOLUME
    period       TEXT NOT NULL,               -- YYYYMM
    metric       TEXT NOT NULL,
    value        NUMERIC(18,2) NOT NULL,
    compliant    BOOLEAN NOT NULL DEFAULT true,
    note         TEXT,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);