SQL 쿼리 카탈로그 · queries.sql

-- =====================================================================
-- nestpay 기능별 표준 SQL 카탈로그 (queries.sql) - 전수 감사(2026-07-21) 반영판
-- 본 파일이 구현 기준: 모든 기능의 데이터 접근은 여기 정의된 SQL을 사용한다.
-- :param = 바인딩 파라미터. [TX] = 단일 DB 트랜잭션 필수.
-- =====================================================================
-- ############ 0. 공통 규약 (감사 확정) ############
-- 0.1 원장 기입 순서: 사용자·매장 지갑 FOR UPDATE(복수=wallet_id 오름차순)
--     → transactions INSERT → ledger_entries INSERT → 로트 처리 → 지갑 UPDATE
--     → outbox INSERT. 전부 한 커밋. 외부 API 호출은 반드시 커밋 후.
-- 0.2 시스템 지갑(FEE_REVENUE·SETTLEMENT_CLEARING·GIFT_ESCROW·UNMATCHED·EXPIRED·FORFEITED·EXCHANGE_{제휴사}[#49 구현보류]):
--     잠금 금지·balance 동기 갱신 금지·원장 balance_after=NULL (핫로우 제거).
--     balance는 일 마감 배치가 SUM으로 파생 갱신, SYSTEM_WALLET 대사가 검증.
-- 0.3 복식 분개 leg 표 (ΣDR=ΣCR, U=사용자지갑 M=매장 FEE CLR ESC UNM EXP FOR=시스템):
--     DEPOSIT        : DR CLR amt          | CR U (amt-fee), CR FEE fee
--     DEPOSIT(미매칭) : DR CLR amt          | CR UNM amt
--     수동매칭       : DR UNM amt          | CR U amt
--     미매칭반환     : DR UNM amt          | CR CLR amt
--     PAYMENT       : DR U amt            | CR M (amt-fee), CR FEE fee
--     CANCEL(결제)  : DR M (amt-fee), DR FEE fee | CR U amt        (수수료 반환 확정)
--     GIFT(직접)    : DR U(발신) amt       | CR U(수신) (amt-fee), CR FEE fee
--     GIFT(링크생성): DR U(발신) amt       | CR ESC (amt-fee), CR FEE fee
--     GIFT(수령)    : DR ESC x            | CR U(수신) x
--     GIFT(만료/회수): DR ESC x            | CR U(발신) x
--     WITHDRAW HOLD : DR U amt            | CR CLR (amt-fee), CR FEE fee
--     WITHDRAW 확정 : 분개 없음(상태 전이만 - CLR 잔액이 은행 실계좌 대사 대상)
--     WITHDRAW 실패 : DR CLR (amt-fee), DR FEE fee | CR U amt      (복원)
--     SETTLEMENT(매장): WITHDRAW와 동일(U→M)
--     REISSUE       : DR U(구) bal        | CR U(신) bal
--     EXPIRE        : DR U x              | CR EXP x
--     FORFEIT(포기) : DR U x              | CR FOR x
--     EXCHANGE(유입): DR EXC amt          | CR U (amt-fee), CR FEE fee   [포인트전환 외부→nestpay · #49 구현보류]
--     EXCHANGE(역환): DR U amt            | CR EXC (amt-fee), CR FEE fee  [nestpay→외부 · #49 구현보류]
-- 0.4 로트 규약: 로트 복원은 UPDATE 증가 금지(방어 트리거) - 반드시 "신규 로트 생성:
--     origin_lot_id=원 로트, lot_type·expires_at 승계(만료 경과분만 잔여기간 정책)".
--     적용: 취소(6.2)·출금 실패(8.5)·선물 만료/회수(7.5/7.6)·수동매칭 아님(신규 DEPOSIT 로트).
--     소진 순서: 결제 = REWARD 우선 → 만료 임박 순. 출금·선물 = DEPOSIT 로트만(0.5).
-- 0.5 포인트 사용 매트릭스: 충전성 DEPOSIT=결제·선물·출금 / 적립성 REWARD=결제만.
-- 0.6 진행중 상태 상수: IN ('PENDING','HOLD','UNKNOWN') - 한도·탈퇴·계좌변경 공통.
-- 0.7 커서 페이징 표준(OFFSET 금지): (정렬키, id) 복합 키셋
--     WHERE (sort < :c_sort OR (sort = :c_sort AND id < :c_id)) ORDER BY sort DESC, id DESC
-- 0.8 동적 조건 금지: (:p IS NULL OR col=:p) 패턴 금지 - 앱에서 파라미터 유무별 SQL 분기.
-- 0.9 배치 스캔 표준: FOR UPDATE SKIP LOCKED + 클레임 시각 기록 + 고아 스위퍼(서버 2대).
-- 0.10 재발행 이관(REISSUE)·내부 이동은 한도(3.3)·매출 집계에서 subtype으로 제외.

-- ############ 1. 가입·로그인 ############

-- 1.1 아이디 중복 확인
SELECT 1 FROM users WHERE login_id = LOWER(:login_id)
UNION ALL SELECT 1 FROM merchant_accounts WHERE login_id = LOWER(:login_id)
UNION ALL SELECT 1 FROM admin_accounts WHERE login_id = LOWER(:login_id) LIMIT 1;

-- 1.2 1인 1활성계정 + 재가입 대기 검증 (ix_users_ci)
SELECT u.id, u.status, u.withdrawn_at,
       (u.withdrawn_at IS NOT NULL AND u.withdrawn_at >
          NOW() - INTERVAL (SELECT CAST(svalue AS INT) FROM global_settings WHERE skey='REJOIN_WAIT_DAYS') DAY
       ) AS in_rejoin_wait
FROM users u WHERE u.ci_hash = :ci_hash ORDER BY u.id DESC LIMIT 1;

-- 1.3 [TX] 회원 생성
INSERT INTO users (login_id, password_hash, name, name_norm, ci_hash, active_ci, di_hash,
                   phone_enc, phone_hash, birth_date, marketing_agree, status, created_at)
VALUES (LOWER(:login_id), :pw_hash, :name, :name_norm, :ci_hash, :ci_hash, :di_hash,
        :phone_enc, :phone_hash, :birth, :mkt_agree, 'ACTIVE', NOW());

-- 1.4 계좌 등록 (실명 일치 → 1원인증 후)
INSERT INTO bank_accounts (owner_type, owner_id, bank_code, account_enc, account_hash,
                           holder_name, holder_name_norm, verified_at, status, created_at)
VALUES (:otype, :oid, :bank, :acct_enc, :acct_hash, :holder, :holder_norm, NOW(), 'ACTIVE', NOW());

-- 1.5 [TX] 계좌 변경 (감사 보완: 사용자 행 잠금 앵커로 check-then-act 레이스 제거 + 통지)
SELECT id FROM users WHERE id = :uid FOR UPDATE;                     -- 직렬화 앵커
SELECT COUNT(*) FROM transactions
 WHERE initiator_type=:otype AND initiator_id=:oid AND type IN ('WITHDRAW')
   AND status IN ('PENDING','HOLD','UNKNOWN');                       -- 0이어야 진행
UPDATE bank_accounts SET status='REMOVED' WHERE owner_type=:otype AND owner_id=:oid AND status='ACTIVE';
INSERT INTO bank_accounts (owner_type, owner_id, bank_code, account_enc, account_hash, holder_name,
                           holder_name_norm, verified_at, status, cooldown_until, created_at)
VALUES (:otype, :oid, :bank, :acct_enc, :acct_hash, :holder, :holder_norm, NOW(), 'ACTIVE',
        NOW() + INTERVAL (SELECT CAST(svalue AS INT) FROM global_settings WHERE skey='BANK_COOLDOWN_HOURS') HOUR, NOW());
INSERT INTO outbox_jobs (job_type, payload, run_after, status, created_at)
VALUES ('PUSH', :acct_change_notice, NOW(), 'PENDING', NOW());       -- 변경 통지(감사 보완)

-- 1.6 [TX] 기기 등록(동시 1기기) + 통지
UPDATE auth_devices SET status='REVOKED'
 WHERE principal_type=:ptype AND principal_id=:pid AND status='ACTIVE';
INSERT INTO auth_devices (principal_type, principal_id, device_uid, platform, model, status, last_login_at, created_at)
VALUES (:ptype, :pid, :duid, :platform, :model, 'ACTIVE', NOW(), NOW())
ON DUPLICATE KEY UPDATE status='ACTIVE', last_login_at=NOW();
INSERT INTO outbox_jobs (job_type, payload, run_after, status, created_at)
VALUES ('PUSH', :device_change_notice, NOW(), 'PENDING', NOW());

-- 1.7 PIN 검증/실패 (감사 교정: SET 좌→우 평가로 fail_count는 이미 신값 → >= 5)
SELECT pin_hash, fail_count, locked_until FROM auth_pins
 WHERE principal_type=:ptype AND principal_id=:pid;
UPDATE auth_pins SET fail_count = fail_count + 1,
       locked_until = IF(fail_count >= 5, NOW() + INTERVAL 30 MINUTE, locked_until)
 WHERE principal_type=:ptype AND principal_id=:pid;
UPDATE auth_pins SET fail_count = 0 WHERE principal_type=:ptype AND principal_id=:pid;  -- 성공 시

-- 1.8 로그인 이력 기록/조회
INSERT INTO login_histories (principal_type, principal_id, method, ip, device_uid, result, created_at)
VALUES (:ptype, :pid, :method, :ip, :duid, :result, NOW());
SELECT method, ip, result, created_at FROM login_histories
 WHERE principal_type=:ptype AND principal_id=:pid ORDER BY created_at DESC, id DESC LIMIT 20;

-- 1.9 계정 복구(CI)
SELECT id, login_id FROM users WHERE active_ci = :ci_hash;

-- 1.10 회원정보 변경(2차 항목 - 본인인증 재수행 후): 번호/개명/마케팅
UPDATE users SET phone_enc=:penc, phone_hash=:phash WHERE id=:uid;   -- SYSTEM VERSIONING이 이력 보존
UPDATE users SET name=:name, name_norm=:norm WHERE id=:uid;          -- 개명(계좌 실명 재확인 동반)
UPDATE users SET marketing_agree=:agree WHERE id=:uid;

-- ############ 2. 카드(지갑) 발급·관리 ############

-- 2.1 [TX] 발급 (감사 보완: 사용자 행 잠금 앵커로 수량 레이스 제거)
SELECT id FROM users WHERE id = :uid FOR UPDATE;
SELECT max_cards FROM card_quota_policies
 WHERE (scope='USER' AND target_id=:uid) OR scope='GLOBAL'
 ORDER BY (scope='USER') DESC LIMIT 1;
SELECT COUNT(*) FROM cards WHERE user_id=:uid AND status='ACTIVE';
INSERT INTO cards (user_id, number_hash, number_enc, cvc_enc, issued_at, expires_on,
                   design_code, color_code, is_primary, status, created_at)
VALUES (:uid, :num_hash, :num_enc, :cvc_enc, NOW(), DATE_ADD(CURDATE(), INTERVAL 5 YEAR),
        :design, :color, :is_first_card, 'ACTIVE', NOW());
INSERT INTO wallets (owner_type, card_id, balance, created_at) VALUES ('USER_CARD', :card_id, 0, NOW());

-- 2.3 [TX] 대표 카드 지정
UPDATE cards SET is_primary = FALSE WHERE user_id=:uid AND is_primary = TRUE;
UPDATE cards SET is_primary = TRUE  WHERE id=:card_id AND user_id=:uid AND status='ACTIVE';

-- 2.4 홈 캐러셀 (ix_cards_user)
SELECT c.id, c.number_enc, c.design_code, c.color_code, c.is_primary, c.expires_on, w.balance
FROM cards c JOIN wallets w ON w.card_id = c.id
WHERE c.user_id=:uid AND c.status='ACTIVE' ORDER BY c.is_primary DESC, c.id;

-- 2.5 [TX] 재발행 (로트 승계·상호참조=단방향+ix_txn_related 역조회 규약)
--  잔액 0이면 b~d 생략, e만 수행(감사 교정: amount>0 CHECK 충돌 방지)
SELECT id, balance FROM wallets WHERE id IN (:old_wid, :new_wid) ORDER BY id FOR UPDATE;   -- a
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id,        -- b1 구카드 출금
    card_id, amount, created_at, confirmed_at)
VALUES (:uid1, 'WITHDRAW', 'REISSUE', 'CONFIRMED', 'USER', :user_id, :old_card, :bal, NOW(), NOW());
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id,        -- b2 신카드 입금
    card_id, amount, related_txn_id, created_at, confirmed_at)
VALUES (:uid2, 'DEPOSIT', 'REISSUE', 'CONFIRMED', 'USER', :user_id, :new_card, :bal, :out_txn_id, NOW(), NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at)   -- c
VALUES (:out_txn, :old_wid, 'DR', :bal, 0, NOW()), (:in_txn, :new_wid, 'CR', :bal, :bal, NOW());
UPDATE wallets SET balance = 0    WHERE id = :old_wid;
UPDATE wallets SET balance = :bal WHERE id = :new_wid;
INSERT INTO point_lots (wallet_id, lot_type, source, amount_init, amount_remaining, expires_at, origin_lot_id, created_at)  -- d 로트 승계
SELECT :new_wid, lot_type, source, amount_remaining, amount_remaining, expires_at, id, NOW()
FROM point_lots WHERE wallet_id=:old_wid AND amount_remaining > 0;
UPDATE point_lots SET amount_remaining = 0 WHERE wallet_id=:old_wid AND amount_remaining > 0;
UPDATE cards SET status='REISSUED', reissued_to=:new_card WHERE id=:old_card;                  -- e

-- ############ 3. 정책 해석·한도 ############

-- 3.1 수수료(개별→전역, ux_fee_policy) - 결과는 거래에 스냅샷
SELECT rate, fixed_amount FROM fee_policies
 WHERE fee_type=:ftype AND ((scope IN ('USER','MERCHANT') AND target_id=:tid) OR scope='GLOBAL')
 ORDER BY (scope <> 'GLOBAL') DESC LIMIT 1;

-- 3.2 한도 정책
SELECT amount FROM limit_policies
 WHERE limit_type=:ltype AND window=:win AND ((scope='USER' AND target_id=:tid) OR scope='GLOBAL')
 ORDER BY (scope <> 'GLOBAL') DESC LIMIT 1;

-- 3.3 한도 사용량(전 카드 합산, ix_txn_initiator_date)
--     감사 교정: +PENDING(0.6 상수), REISSUE 등 내부 이동 제외
SELECT COALESCE(SUM(amount),0) FROM transactions
 WHERE initiator_type='USER' AND initiator_id=:uid AND type=:ttype
   AND status IN ('PENDING','HOLD','UNKNOWN','CONFIRMED')
   AND subtype NOT IN ('REISSUE')
   AND created_at >= :window_start;

-- 3.4 보유 한도(CAP)
SELECT COALESCE(SUM(w.balance),0) FROM wallets w
 JOIN cards c ON c.id = w.card_id WHERE c.user_id = :uid;

-- 3.5 출금 대기/취소기한/유효기간 정책
SELECT delay_days, execute_time FROM payout_policies
 WHERE (scope=:iscope AND target_id=:tid) OR scope=:gscope
 ORDER BY (scope=:iscope) DESC LIMIT 1;
SELECT days FROM cancel_policies
 WHERE (scope='MERCHANT' AND target_id=:mid) OR scope='GLOBAL'
 ORDER BY (scope='MERCHANT') DESC LIMIT 1;
SELECT months FROM point_expiry_policies WHERE lot_type=:ltype;

-- 3.6 출금·선물 가용액(감사 신설: DEPOSIT 로트만 - 0.5 매트릭스, ix_lots_fifo)
SELECT COALESCE(SUM(amount_remaining),0) FROM point_lots
 WHERE wallet_id=:wid AND lot_type='DEPOSIT' AND amount_remaining > 0 AND expires_at > NOW();

-- ############ 4. 입금(무통장) ############

-- 4.1 통지 수신 (감사 교정: INSERT IGNORE 금지 - 1062만 앱 멱등 처리. dedup_key 규약은 스키마 참조)
INSERT INTO deposit_notices (source, raw_text, dedup_key, bank_tran_ref, parsed_amount, parsed_name,
                             parsed_name_norm, identifier_value, status, received_at)
VALUES (:src, :raw, :dedup, :ref, :amt, :pname, :pname_norm, :ident, 'NEW', NOW());

-- 4.2 매칭: 식별자 → 사용자
SELECT di.user_id FROM deposit_identifiers di WHERE di.value = :ident AND di.status='ACTIVE';

-- 4.2b NEW 통지 워커 스캔 (감사 교정: 서버 2대 이중 처리 방지)
SELECT id, source, dedup_key, bank_tran_ref, parsed_amount, identifier_value FROM deposit_notices
 WHERE status='NEW' ORDER BY received_at LIMIT 100 FOR UPDATE SKIP LOCKED;

-- 4.3 [TX] 입금 확정 (감사 교정: 복식 CLR leg + 멱등키 + 통지 가드 + 카드 ACTIVE 재검증 + fee 분기)
--     ①통지 클레임(가드 - affected=0이면 롤백)
UPDATE deposit_notices SET status='MATCHED', matched_txn_id=NULL WHERE id=:notice_id AND status='NEW';
--     ②지갑 잠금 + 카드 상태 재검증(재발행 경합 - 감사 교정: REISSUED면 reissued_to로 리라우팅)
SELECT w.id, w.balance, c.status AS card_status, c.reissued_to
FROM wallets w JOIN cards c ON c.id = w.card_id WHERE w.card_id = :card_id FOR UPDATE;
--     ③거래 (멱등: 은행 거래 식별자 기반 - ux_txn_idem이 이중 적립 차단)
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id, idempotency_key,
    card_id, amount, fee_amount, fee_rate_snap, fee_fixed_snap, bank_tran_ref, created_at, confirmed_at)
VALUES (:tuid, 'DEPOSIT', 'VACCT', 'CONFIRMED', 'USER', :uid, CONCAT('DEP:', :src, ':', :dedup),
        :card_id, :amt, :fee, :rate, :fixed, :ref, NOW(), NOW());
--     ④원장 (0.3 leg 표: 시스템 leg는 balance_after NULL - 0.2)
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:txn, :clr_wid,  'DR', :amt,          NULL,         NOW()),
 (:txn, :user_wid, 'CR', :amt_net,      :u_bal_after, NOW());
-- fee > 0일 때만 추가 leg: (:txn, :fee_wid, 'CR', :fee, NULL, NOW())
--     ⑤로트 + 지갑 + 통지 확정 + outbox
INSERT INTO point_lots (wallet_id, lot_type, source, amount_init, amount_remaining, expires_at, created_at)
VALUES (:user_wid, 'DEPOSIT', 'DEPOSIT', :amt_net, :amt_net, :expires_kst, NOW());
UPDATE wallets SET balance = :u_bal_after WHERE id = :user_wid;
UPDATE deposit_notices SET matched_txn_id=:txn WHERE id=:notice_id;
INSERT INTO outbox_jobs (job_type, payload, run_after, status, created_at)
VALUES ('PUSH', :push_payload, NOW(), 'PENDING', NOW());

-- 4.4 [TX] 미매칭: 통지 가드(UNMATCHED 전이) + CLR→UNM 분개(0.3) + 대기건
UPDATE deposit_notices SET status='UNMATCHED' WHERE id=:notice_id AND status='NEW';
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id, idempotency_key,
    amount, created_at, confirmed_at)
VALUES (:tuid, 'DEPOSIT', 'VACCT', 'CONFIRMED', 'SYSTEM', 0, CONCAT('DEP:', :src, ':', :dedup), :amt, NOW(), NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:txn, :clr_wid, 'DR', :amt, NULL, NOW()), (:txn, :unm_wid, 'CR', :amt, NULL, NOW());
INSERT INTO unmatched_deposits (notice_id, amount, status, created_at) VALUES (:notice_id, :amt, 'PENDING', NOW());

-- 4.5 [TX] admin 수동 매칭 (감사 보완: 완전 SQL - UNM→사용자 이체 + 로트 + 감사)
UPDATE unmatched_deposits SET status='MATCHED', resolved_by=:admin_id, resolved_at=NOW(),
       resolution='MANUAL_MATCH', resolution_note=:note WHERE id=:umd_id AND status='PENDING';
SELECT w.id, w.balance FROM wallets w JOIN cards c ON c.id=w.card_id
 WHERE w.card_id=:target_card AND c.status='ACTIVE' FOR UPDATE;
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id,
    card_id, amount, related_txn_id, created_at, confirmed_at)
VALUES (:tuid, 'DEPOSIT', 'VACCT', 'CONFIRMED', 'ADMIN', :admin_id, :target_card, :amt, :orig_txn, NOW(), NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:txn, :unm_wid, 'DR', :amt, NULL, NOW()), (:txn, :user_wid, 'CR', :amt, :u_bal_after, NOW());
INSERT INTO point_lots (wallet_id, lot_type, source, amount_init, amount_remaining, expires_at, created_at)
VALUES (:user_wid, 'DEPOSIT', 'DEPOSIT', :amt, :amt, :expires_kst, NOW());
UPDATE wallets SET balance=:u_bal_after WHERE id=:user_wid;
INSERT INTO audit_logs (actor_type, actor_id, action, target_type, target_id, detail, reason, ip, created_at)
VALUES ('ADMIN', :admin_id, 'UNMATCHED_RESOLVE', 'UNMATCHED_DEPOSIT', :umd_id, :detail_masked, :note, :ip, NOW());

-- 4.6 [TX] admin 미매칭 반환: UNM DR / CLR CR(0.3) + 대기건 RETURNED + 감사 (반환 이체는 커밋 후 외부 호출)
UPDATE unmatched_deposits SET status='RETURNED', resolved_by=:admin_id, resolved_at=NOW(),
       resolution='RETURN', resolution_note=:note, return_method=:method,
       return_bank_code=:rbank, return_account_enc=:racct_enc WHERE id=:umd_id AND status='PENDING';
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id, amount, related_txn_id, created_at)
VALUES (:tuid, 'WITHDRAW', 'UNMATCHED_RETURN', 'HOLD', 'ADMIN', :admin_id, :amt, :orig_txn, NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:txn, :unm_wid, 'DR', :amt, NULL, NOW()), (:txn, :clr_wid, 'CR', :amt, NULL, NOW());
INSERT INTO audit_logs (actor_type, actor_id, action, target_type, target_id, reason, ip, created_at)
VALUES ('ADMIN', :admin_id, 'UNMATCHED_RETURN', 'UNMATCHED_DEPOSIT', :umd_id, :note, :ip, NOW());

-- ############ 5. 결제 ############

-- 5.1 QR 검증(서버 해독 후)
SELECT q.id, q.merchant_id, q.qr_type, q.amount, q.status, q.expires_at, m.biz_name, m.status AS m_status,
       m.vat_mode, m.vat_rate
FROM merchant_qrs q JOIN merchants m ON m.id = q.merchant_id WHERE q.id = :qr_id;

-- 5.2 [TX] 결제 기입 (감사 교정: ①ORDER형 소각을 TX 안 선두로 ②시스템 지갑 무잠금 0.2)
UPDATE merchant_qrs SET status='USED' WHERE id=:qr_id AND qr_type='ORDER' AND status='ACTIVE';  -- affected=1 필수, 실패=롤백
SELECT id, balance FROM wallets WHERE id IN (:user_wid, :merchant_wid) ORDER BY id FOR UPDATE;  -- FEE 지갑 잠금 제외
SELECT id, lot_type, amount_remaining FROM point_lots                                            -- 결제: REWARD 우선(0.4)
 WHERE wallet_id=:user_wid AND amount_remaining > 0 AND expires_at > NOW()
 ORDER BY (lot_type='REWARD') DESC, expires_at ASC, id ASC FOR UPDATE;
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id, idempotency_key,
    card_id, merchant_id, amount, fee_amount, fee_rate_snap, fee_fixed_snap, vat_amount, cancelable_until,
    pg_order_id, created_at, confirmed_at)
VALUES (:tuid, 'PAYMENT', :subtype, 'CONFIRMED', 'USER', :uid, :idem,
        :card_id, :mid, :amt, :fee, :rate, :fixed, :vat, :cancel_until, :pg_oid, NOW(), NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:txn, :user_wid,     'DR', :amt,          :u_bal_after, NOW()),
 (:txn, :merchant_wid, 'CR', :amt_minus_fee,:m_bal_after, NOW()),
 (:txn, :fee_wid,      'CR', :fee,          NULL,         NOW());   -- fee=0이면 이 leg 생략
INSERT INTO lot_allocations (ledger_entry_id, lot_id, amount) VALUES (:dr_entry, :lot_id, :alloc_amt); -- 로트별 반복
UPDATE point_lots SET amount_remaining = amount_remaining - :alloc_amt WHERE id = :lot_id;             -- 로트별 반복
UPDATE wallets SET balance=:u_bal_after WHERE id=:user_wid;
UPDATE wallets SET balance=:m_bal_after WHERE id=:merchant_wid;      -- FEE 지갑 갱신 없음(일 배치 파생)
INSERT INTO outbox_jobs (job_type, payload, run_after, status, created_at)
VALUES ('PUSH', :both_sides_payload, NOW(), 'PENDING', NOW());

-- 5.3 PG 주문 (감사 교정: SELECT 선행 + 1062 멱등 - ODKU의 auto_increment 낭비 회피)
SELECT id, status, txn_id FROM pg_orders WHERE merchant_id=:mid AND order_no=:ono;
INSERT INTO pg_orders (merchant_id, order_no, amount, supply_amount, vat_amount, item_name,
                       qr_id, status, is_sandbox, created_at)
VALUES (:mid, :ono, :amt, :supply, :vat, :item, :qr_id, 'CREATED', :sandbox, NOW());
UPDATE pg_orders SET status='PAID', txn_id=:txn WHERE id=:pg_oid AND status='CREATED';   -- 상태 가드
UPDATE pg_orders SET status='CANCELED' WHERE id=:pg_oid AND status='PAID';

-- 5.4 웹훅 3단 (감사 보완: 클레임→커밋→발송, 외부 대기 중 락 금지)
INSERT INTO webhook_deliveries (merchant_id, event_type, pg_order_id, payload, target_url, attempts, next_retry_at, status, created_at)
VALUES (:mid, :ev, :pg_oid, :payload, :url, 0, NOW(), 'PENDING', NOW());
SELECT id, payload, target_url, attempts FROM webhook_deliveries
 WHERE status='PENDING' AND next_retry_at <= NOW() ORDER BY next_retry_at LIMIT 50 FOR UPDATE SKIP LOCKED;
UPDATE webhook_deliveries SET status='SENDING', claimed_at=NOW() WHERE id IN (:ids);       -- 클레임 후 커밋
UPDATE webhook_deliveries SET status='DELIVERED', last_http_code=:code WHERE id=:id AND status='SENDING';
UPDATE webhook_deliveries SET attempts=attempts+1, last_http_code=:code,
       next_retry_at = NOW() + INTERVAL LEAST(POW(2, attempts-1)*60, 86400) SECOND,        -- attempts는 신값(1.7 규약)
       status = IF(attempts >= 10, 'EXHAUSTED', 'PENDING'), claimed_at=NULL
 WHERE id=:id AND status='SENDING';
UPDATE webhook_deliveries SET status='PENDING', claimed_at=NULL                            -- SENDING 고아 스위퍼
 WHERE status='SENDING' AND claimed_at < NOW() - INTERVAL 10 MINUTE;

-- ############ 6. 취소 (전체 취소만) ############

-- 6.1 [TX] 원거래 잠금·검증
SELECT id, status, amount, fee_amount, cancelable_until, card_id, merchant_id
FROM transactions WHERE id=:orig_txn AND type='PAYMENT' FOR UPDATE;
-- 검증: status='CONFIRMED'(CANCELED면 멱등 응답 / 기한 경과=CANCEL_WINDOW_EXPIRED)
SELECT id, balance FROM wallets WHERE id IN (:user_wid, :merchant_wid) ORDER BY id FOR UPDATE;
-- 매장 잔액 < amount-fee → fail: reject_logs 기록 + MERCHANT_INSUFFICIENT_BALANCE 응답(원장 무변경)

-- 6.2 취소 기입 (감사 재설계: 로트 복원 = 신규 로트 생성 - 트리거 충돌 제거)
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id, card_id,
                          merchant_id, amount, fee_amount, related_txn_id, created_at, confirmed_at)
VALUES (:tuid, 'CANCEL', :orig_subtype, 'CONFIRMED', :itype, :iid, :card_id, :mid, :amt, :fee, :orig_txn, NOW(), NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:txn, :merchant_wid, 'DR', :amt_minus_fee, :m_bal_after, NOW()),
 (:txn, :fee_wid,      'DR', :fee,           NULL,         NOW()),   -- 수수료 반환(확정) / fee=0 생략
 (:txn, :user_wid,     'CR', :amt,           :u_bal_after, NOW());
--  로트 복원(0.4): 원거래 DR 엔트리의 배분(ix_alloc_entry) 역추적 → 원 로트 속성 승계 신규 로트
INSERT INTO point_lots (wallet_id, lot_type, source, amount_init, amount_remaining, expires_at, origin_lot_id, created_at)
SELECT :user_wid, pl.lot_type, 'CANCEL_RESTORE', la.amount, la.amount,
       GREATEST(pl.expires_at, NOW() + INTERVAL 1 DAY),               -- 만료 경과분 최소 유예(잔여기간 정책)
       pl.id, NOW()
FROM lot_allocations la JOIN point_lots pl ON pl.id = la.lot_id
WHERE la.ledger_entry_id = :orig_dr_entry;
UPDATE wallets SET balance=:m_bal_after WHERE id=:merchant_wid;
UPDATE wallets SET balance=:u_bal_after WHERE id=:user_wid;
UPDATE transactions SET status='CANCELED' WHERE id=:orig_txn AND status='CONFIRMED';
INSERT INTO outbox_jobs (job_type, payload, run_after, status, created_at) VALUES ('PUSH', :p, NOW(), 'PENDING', NOW());

-- ############ 7. 선물 ############

-- 7.1 번호 검색(ix_users_phone_hash / 미존재="존재하지 않는 회원" + reject_logs)
SELECT u.id, u.name FROM users u WHERE u.phone_hash=:phash AND u.status='ACTIVE';

-- 7.2 [TX] 직접 이체(번호/QR): 발신 DEPOSIT 로트만(0.5) + counterparty 기록 + 수취 로트(유형 승계·기간 리셋)
SELECT id, balance FROM wallets WHERE id IN (:s_wid, :r_wid) ORDER BY id FOR UPDATE;
SELECT id, lot_type, amount_remaining FROM point_lots                    -- 선물: DEPOSIT만
 WHERE wallet_id=:s_wid AND lot_type='DEPOSIT' AND amount_remaining > 0 AND expires_at > NOW()
 ORDER BY expires_at ASC, id ASC FOR UPDATE;
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id, idempotency_key,
    card_id, counterparty_card_id, amount, fee_amount, fee_rate_snap, fee_fixed_snap, memo, created_at, confirmed_at)
VALUES (:tuid, 'GIFT', :sub, 'CONFIRMED', 'USER', :uid, :idem, :s_card, :r_card, :amt, :fee, :rate, :fixed, :msg, NOW(), NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:txn, :s_wid, 'DR', :amt, :s_bal_after, NOW()),
 (:txn, :r_wid, 'CR', :amt_net, :r_bal_after, NOW()),
 (:txn, :fee_wid, 'CR', :fee, NULL, NOW());                              -- fee=0 생략(발신인 부담)
INSERT INTO lot_allocations (ledger_entry_id, lot_id, amount) VALUES (:dr_entry, :lot_id, :alloc); -- 반복
UPDATE point_lots SET amount_remaining = amount_remaining - :alloc WHERE id=:lot_id;               -- 반복
INSERT INTO point_lots (wallet_id, lot_type, source, amount_init, amount_remaining, expires_at, created_at)
VALUES (:r_wid, 'DEPOSIT', 'GIFT', :amt_net, :amt_net, :recv_expires_kst, NOW());                   -- 수취 로트(기간 리셋)
UPDATE wallets SET balance=:s_bal_after WHERE id=:s_wid;
UPDATE wallets SET balance=:r_bal_after WHERE id=:r_wid;
INSERT INTO outbox_jobs (job_type, payload, run_after, status, created_at) VALUES ('PUSH', :p, NOW(), 'PENDING', NOW());

-- 7.3 [TX] URL 선물 생성: 발신 DR / ESC CR(무잠금·NULL) / FEE CR + gift_links (로트 소진은 7.2 패턴)
INSERT INTO gift_links (sender_card_id, hold_txn_id, token_hash, amount, message, expires_at, status)
VALUES (:card_id, :txn, :thash, :amt_net, :msg, NOW() + INTERVAL 24 HOUR, 'CREATED');

-- 7.4 [TX] 수령: 링크 잠금 → ESC DR / 수취 CR + 수취 로트 생성(감사 보완)
SELECT id, amount, status, expires_at FROM gift_links WHERE token_hash=:thash FOR UPDATE;
UPDATE gift_links SET status='CLAIMED', claimed_card_id=:rcard, claimed_at=NOW()
 WHERE id=:gid AND status='CREATED' AND expires_at > NOW();
SELECT w.id, w.balance FROM wallets w JOIN cards c ON c.id=w.card_id
 WHERE w.card_id=:rcard AND c.status='ACTIVE' FOR UPDATE;
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id,
    card_id, counterparty_card_id, amount, related_txn_id, created_at, confirmed_at)
VALUES (:tuid, 'GIFT', 'GIFT_LINK', 'CONFIRMED', 'USER', :recv_uid, :sender_card, :rcard, :amt, :hold_txn, NOW(), NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:txn, :esc_wid, 'DR', :amt, NULL, NOW()), (:txn, :r_wid, 'CR', :amt, :r_bal_after, NOW());
INSERT INTO point_lots (wallet_id, lot_type, source, amount_init, amount_remaining, expires_at, created_at)
VALUES (:r_wid, 'DEPOSIT', 'GIFT', :amt, :amt, :recv_expires_kst, NOW());
UPDATE wallets SET balance=:r_bal_after WHERE id=:r_wid;

-- 7.5 만료 반환 배치: ESC DR / 발신 CR + 원 로트 승계 신규 로트(0.4, 감사 보완)
SELECT id, sender_card_id, hold_txn_id, amount FROM gift_links
 WHERE status='CREATED' AND expires_at <= NOW() LIMIT 100 FOR UPDATE SKIP LOCKED;
--  (이후 [TX]: 발신 지갑 잠금 → GIFT 반환 거래 + ESC DR/발신 CR + 신규 로트(원 hold의 소진 로트 승계) + status='EXPIRED')

-- 7.6 [TX] 송금인 회수(감사 신설): 7.5와 동일 분개, gift_links.status='CANCELED'
UPDATE gift_links SET status='CANCELED' WHERE id=:gid AND status='CREATED';

-- ############ 8. 출금·정산 ############

-- 8.1 [TX] 신청=선차감 HOLD (0.3 leg: U DR amt / CLR CR amt-fee / FEE CR fee, DEPOSIT 로트만 소진)
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id, idempotency_key,
    card_id, merchant_id, amount, fee_amount, fee_rate_snap, fee_fixed_snap, scheduled_at, bank_account_id, created_at)
VALUES (:tuid, 'WITHDRAW', :sub, 'HOLD', :itype, :iid, :idem, :card_id, :mid, :amt, :fee, :rate, :fixed,
        :scheduled_kst, :bank_acct_id, NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:txn, :src_wid, 'DR', :amt, :bal_after, NOW()),
 (:txn, :clr_wid, 'CR', :amt_minus_fee, NULL, NOW()),
 (:txn, :fee_wid, 'CR', :fee, NULL, NOW());                              -- fee=0 생략
-- 사용자 출금: DEPOSIT 로트 선점(7.2 패턴) + lot_allocations. 매장 정산: 로트 없음(매장 지갑은 무로트).

-- 8.2 실행 배치 (감사 교정: 2단계 - 과잠금 제거, ix_txn_withdraw_sched)
SELECT id FROM transactions
 WHERE type='WITHDRAW' AND status='HOLD' AND scheduled_at <= NOW()
 ORDER BY scheduled_at LIMIT 100;
SELECT t.id, t.amount, t.fee_amount, t.bank_account_id FROM transactions t
 WHERE t.id IN (:ids) AND t.status='HOLD' FOR UPDATE SKIP LOCKED;
SELECT b.id, b.bank_code, b.account_enc, b.cooldown_until FROM bank_accounts b WHERE b.id IN (:acct_ids);  -- 무잠금
UPDATE transactions SET status='PENDING' WHERE id IN (:claimed_ids) AND status='HOLD';   -- 클레임 후 커밋 → 외부 이체 호출

-- 8.3 이체 결과 반영 (상태 가드)
UPDATE transactions SET status='CONFIRMED', confirmed_at=NOW(), bank_tran_ref=:ref WHERE id=:txn AND status='PENDING';
UPDATE transactions SET status='UNKNOWN', bank_tran_ref=:ref WHERE id=:txn AND status='PENDING';
-- 명시 실패 → 8.5 복원 [TX]

-- 8.4 리컨실러 (감사 교정: SKIP LOCKED + UNKNOWN 전이 SQL)
SELECT id, bank_tran_ref, created_at FROM transactions
 WHERE type='WITHDRAW' AND status='UNKNOWN' AND created_at <= NOW() - INTERVAL 10 MINUTE
 ORDER BY created_at LIMIT 50 FOR UPDATE SKIP LOCKED;
UPDATE transactions SET status='CONFIRMED', confirmed_at=NOW() WHERE id=:txn AND status='UNKNOWN';

-- 8.5 [TX] 실패 복원(감사 신설): 역분개 + DEPOSIT 로트 승계 복원(0.4)
UPDATE transactions SET status='FAILED', fail_reason=:reason WHERE id=:txn AND status IN ('PENDING','UNKNOWN');
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id,
    card_id, merchant_id, amount, fee_amount, related_txn_id, created_at, confirmed_at)
VALUES (:tuid, 'DEPOSIT', :orig_sub, 'CONFIRMED', 'SYSTEM', 0, :card_id, :mid, :amt, :fee, :txn, NOW(), NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:rtxn, :clr_wid, 'DR', :amt_minus_fee, NULL, NOW()),
 (:rtxn, :fee_wid, 'DR', :fee, NULL, NOW()),
 (:rtxn, :src_wid, 'CR', :amt, :bal_after, NOW());
INSERT INTO point_lots (wallet_id, lot_type, source, amount_init, amount_remaining, expires_at, origin_lot_id, created_at)
SELECT :src_wid, pl.lot_type, 'WITHDRAW_RESTORE', la.amount, la.amount,
       GREATEST(pl.expires_at, NOW() + INTERVAL 1 DAY), pl.id, NOW()
FROM lot_allocations la JOIN point_lots pl ON pl.id = la.lot_id WHERE la.ledger_entry_id = :orig_dr_entry;
UPDATE wallets SET balance=:bal_after WHERE id=:src_wid;
INSERT INTO outbox_jobs (job_type, payload, run_after, status, created_at) VALUES ('PUSH', :p, NOW(), 'PENDING', NOW());

-- ############ 9. 포인트 만료 ############

-- 9.1 후보 수집 (감사 교정: 무잠금 + low-watermark 하한)
SELECT l.id, l.wallet_id, l.amount_remaining FROM point_lots l
 WHERE l.expires_at > :watermark AND l.expires_at <= NOW() AND l.amount_remaining > 0
 ORDER BY l.expires_at LIMIT 200;

-- 9.2 [TX] 만료 기입 (감사 교정: 지갑 우선 잠금 → 로트 재잠금·재검증 - 데드락 순서 통일)
SELECT id, balance FROM wallets WHERE id = :wid FOR UPDATE;
SELECT id, amount_remaining FROM point_lots
 WHERE id IN (:lot_ids) AND amount_remaining > 0 AND expires_at <= NOW() FOR UPDATE;
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id, card_id, amount, created_at, confirmed_at)
VALUES (:tuid, 'EXPIRE', :lot_type, 'CONFIRMED', 'SYSTEM', 0, :card_id, :amt, NOW(), NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:txn, :wid, 'DR', :amt, :bal_after, NOW()), (:txn, :exp_wid, 'CR', :amt, NULL, NOW());
UPDATE point_lots SET amount_remaining = 0 WHERE id IN (:lot_ids);
UPDATE wallets SET balance=:bal_after WHERE id=:wid;

-- 9.3 만료 예정 고지(D-30/D-7, ix_lots_expiry)
SELECT c.user_id, l.wallet_id, SUM(l.amount_remaining) AS expiring
FROM point_lots l JOIN wallets w ON w.id=l.wallet_id JOIN cards c ON c.id=w.card_id
WHERE l.amount_remaining > 0 AND l.expires_at BETWEEN :d_from AND :d_to
GROUP BY c.user_id, l.wallet_id;

-- ############ 10. 내역·명세 ############

-- 10.1 카드 이용내역 (감사 교정: 복합 키셋 커서 0.7 / ix_txn_card)
--      type 필터는 앱에서 SQL 분기(0.8): 무필터/유필터 2종
SELECT id, txn_uid, type, subtype, status, amount, fee_amount, memo, created_at
FROM transactions
WHERE card_id = :card_id
  AND (created_at < :c_ts OR (created_at = :c_ts AND id < :c_id))
ORDER BY created_at DESC, id DESC LIMIT 30;
SELECT id, txn_uid, type, subtype, status, amount, fee_amount, memo, created_at
FROM transactions
WHERE card_id = :card_id AND type = :ttype
  AND (created_at < :c_ts OR (created_at = :c_ts AND id < :c_id))
ORDER BY created_at DESC, id DESC LIMIT 30;

-- 10.1b 받은 선물 내역(감사 신설: ix_txn_counterparty)
SELECT id, txn_uid, amount, memo, card_id AS sender_card, created_at
FROM transactions
WHERE counterparty_card_id = :card_id AND type='GIFT'
  AND (created_at < :c_ts OR (created_at = :c_ts AND id < :c_id))
ORDER BY created_at DESC, id DESC LIMIT 30;

-- 10.2 사용자 통합 내역 (감사 교정: ix_txn_initiator_created + 커서)
SELECT id, type, subtype, status, amount, card_id, created_at FROM transactions
WHERE initiator_type='USER' AND initiator_id=:uid
  AND (created_at < :c_ts OR (created_at = :c_ts AND id < :c_id))
ORDER BY created_at DESC, id DESC LIMIT 30;

-- 10.3 월 명세: 당월 실시간(ix_txn_card 범위) / 마감월 스냅샷
SELECT type, subtype, status, SUM(amount) AS amt_sum, SUM(fee_amount) AS fee_sum, COUNT(*) AS cnt
FROM transactions
WHERE card_id=:card_id AND created_at >= :month_start AND created_at < :month_end
GROUP BY type, subtype, status;
SELECT * FROM monthly_statements WHERE card_id=:card_id AND yyyymm=:ym;

-- 10.3b 받은선물 월합계(gift_recv_sum 원천, 감사 신설)
SELECT COALESCE(SUM(amount),0) FROM transactions
WHERE counterparty_card_id=:card_id AND type='GIFT' AND status='CONFIRMED'
  AND created_at >= :month_start AND created_at < :month_end;

-- 10.4 매장 매출: 당일·단기 실시간(ix_txn_merchant). 장기 추이는 18장 집계 사용(감사 확정)
SELECT DATE(created_at) AS d, COUNT(*) AS cnt,
       SUM(IF(type='PAYMENT', amount, 0)) AS sales,
       SUM(IF(type='CANCEL',  amount, 0)) AS cancels,
       SUM(IF(type='PAYMENT', COALESCE(vat_amount,0), 0)) AS vat_sum,
       SUM(IF(type='PAYMENT', fee_amount, 0)) AS fee_sum
FROM transactions
WHERE merchant_id=:mid AND type IN ('PAYMENT','CANCEL') AND status IN ('CONFIRMED','CANCELED')
  AND created_at >= :from_ts AND created_at < :to_ts
GROUP BY DATE(created_at) ORDER BY d;

-- ############ 11. 대사·모니터링 ############

-- 11.1 복식 균형(일 배치, 파티션 범위) - 0.3 leg 표가 기준
SELECT le.txn_id,
       SUM(IF(le.direction='DR', le.amount, 0)) AS dr_sum,
       SUM(IF(le.direction='CR', le.amount, 0)) AS cr_sum
FROM ledger_entries le
WHERE le.created_at >= :day_start AND le.created_at < :day_end
GROUP BY le.txn_id HAVING dr_sum <> cr_sum;

-- 11.2 잔액 체인(당일 윈도우, 시스템 leg 제외)
SELECT * FROM (
  SELECT le.wallet_id, le.id, le.direction, le.amount, le.balance_after,
         LAG(le.balance_after) OVER (PARTITION BY le.wallet_id ORDER BY le.id) AS prev_bal
  FROM ledger_entries le
  WHERE le.created_at >= :day_start AND le.created_at < :day_end AND le.balance_after IS NOT NULL
) x
WHERE prev_bal IS NOT NULL
  AND balance_after <> prev_bal + IF(direction='CR', amount, -amount);

-- 11.2b 일 경계 체인(감사 신설: 전일 스냅샷 ↔ 당일 첫 행)
SELECT f.wallet_id, f.id, f.balance_after, s.balance AS snap_bal
FROM (
  SELECT le.wallet_id, le.id, le.direction, le.amount, le.balance_after,
         ROW_NUMBER() OVER (PARTITION BY le.wallet_id ORDER BY le.id) AS rn
  FROM ledger_entries le
  WHERE le.created_at >= :day_start AND le.created_at < :day_end AND le.balance_after IS NOT NULL
) f
JOIN daily_wallet_snapshots s ON s.wallet_id = f.wallet_id
 AND s.snap_date = (SELECT MAX(s2.snap_date) FROM daily_wallet_snapshots s2
                    WHERE s2.wallet_id = f.wallet_id AND s2.snap_date < :day_start)
WHERE f.rn = 1
  AND f.balance_after <> s.balance + IF(f.direction='CR', f.amount, -f.amount);

-- 11.3 지갑 캐시 대사(최신 스냅샷 + 증분, 희소 스냅샷 규약)
SELECT w.id, w.balance,
       COALESCE(s.balance,0) + COALESCE(d.delta,0) AS derived
FROM wallets w
LEFT JOIN daily_wallet_snapshots s
  ON s.wallet_id = w.id
 AND s.snap_date = (SELECT MAX(s2.snap_date) FROM daily_wallet_snapshots s2
                    WHERE s2.wallet_id = w.id AND s2.snap_date < :today)
LEFT JOIN (SELECT wallet_id, SUM(IF(direction='CR', amount, -amount)) AS delta
           FROM ledger_entries WHERE created_at >= :day_start AND balance_after IS NOT NULL
           GROUP BY wallet_id) d ON d.wallet_id = w.id
WHERE w.owner_type <> 'SYSTEM'
HAVING w.balance <> derived;

-- 11.3b 시스템 지갑 검증·파생 갱신(감사 신설: SYSTEM_WALLET - 0.2 규약의 검증측)
SELECT w.id, w.system_code, w.balance,
       (SELECT COALESCE(SUM(IF(le.direction='CR', le.amount, -le.amount)),0)
        FROM ledger_entries le WHERE le.wallet_id = w.id) AS ledger_sum
FROM wallets w WHERE w.owner_type='SYSTEM' HAVING w.balance <> ledger_sum;
UPDATE wallets w SET w.balance =
  (SELECT COALESCE(SUM(IF(le.direction='CR', le.amount, -le.amount)),0)
   FROM ledger_entries le WHERE le.wallet_id = w.id)
WHERE w.owner_type='SYSTEM';                                          -- 일 마감 파생 갱신

-- 11.4 별도관리 대사(감사 재정의: 사용자 DEPOSIT 로트 합 + 매장 지갑 합, ix_lots_type_remaining)
SELECT
  (SELECT COALESCE(SUM(l.amount_remaining),0) FROM point_lots l
    WHERE l.lot_type='DEPOSIT' AND l.amount_remaining > 0) AS user_charge_sum,
  (SELECT COALESCE(SUM(w.balance),0) FROM wallets w WHERE w.owner_type='MERCHANT') AS merchant_sum;

-- 11.5 장기 체류 감시
SELECT id, type, status, created_at FROM transactions
 WHERE status IN ('PENDING','HOLD','UNKNOWN') AND created_at <= NOW() - INTERVAL 1 HOUR
 ORDER BY created_at LIMIT 100;

-- 11.6 일 스냅샷 (감사 교정: 변동 지갑만 + 당일 파티션 범위 산출 + ODKU 멱등)
INSERT INTO daily_wallet_snapshots (snap_date, wallet_id, balance, last_ledger_id)
SELECT :snap_date, w.id, w.balance, d.max_id
FROM wallets w
JOIN (SELECT wallet_id, MAX(id) AS max_id FROM ledger_entries
      WHERE created_at >= :day_start AND created_at < :day_end GROUP BY wallet_id) d
  ON d.wallet_id = w.id
ON DUPLICATE KEY UPDATE balance = VALUES(balance), last_ledger_id = VALUES(last_ledger_id);
-- 월 1회 전량 베이스라인: 위 SQL에서 JOIN 대신 전 지갑 대상(동일 ODKU)

-- 11.7 고아 검사(감사 신설: ORPHAN_LEDGER - 파티션 FK 제거 대체, 일 범위)
SELECT le.id FROM ledger_entries le LEFT JOIN wallets w ON w.id = le.wallet_id
 WHERE le.created_at >= :day_start AND le.created_at < :day_end AND w.id IS NULL LIMIT 100;
SELECT le.id FROM ledger_entries le LEFT JOIN transactions t ON t.id = le.txn_id
 WHERE le.created_at >= :day_start AND le.created_at < :day_end AND t.id IS NULL LIMIT 100;

-- 11.8 검증 이력 기록
INSERT INTO integrity_check_runs (check_type, scope, status, checked_count, anomaly_count, started_at, finished_at)
VALUES (:ctype, :scope, :status, :cnt, :anom, :start, NOW());
INSERT INTO integrity_findings (run_id, target_type, target_id, detail, status) VALUES (:run, :tt, :tid, :detail, 'OPEN');

-- ############ 12. 거래 추적 (ix_txn_related) ############
WITH RECURSIVE chain AS (
  SELECT id, type, subtype, status, amount, card_id, counterparty_card_id, merchant_id, related_txn_id, created_at, 0 AS depth
  FROM transactions WHERE id = :txn_id
  UNION ALL
  SELECT t.id, t.type, t.subtype, t.status, t.amount, t.card_id, t.counterparty_card_id, t.merchant_id, t.related_txn_id, t.created_at, c.depth+1
  FROM transactions t JOIN chain c ON t.related_txn_id = c.id
  WHERE c.depth < 10
)
SELECT * FROM chain;

-- ############ 13. outbox 워커 (클레임·스위퍼 - 감사 보완) ############
SELECT id, job_type, payload, attempts FROM outbox_jobs
 WHERE status='PENDING' AND run_after <= NOW()
 ORDER BY run_after LIMIT 50 FOR UPDATE SKIP LOCKED;
UPDATE outbox_jobs SET status='RUNNING', claimed_at=NOW() WHERE id IN (:ids);
UPDATE outbox_jobs SET status='DONE' WHERE id=:id AND status='RUNNING';
UPDATE outbox_jobs SET status=IF(attempts >= max_attempts,'EXHAUSTED','PENDING'),           -- attempts=신값(1.7 규약)
       attempts=attempts+1, last_error=:err, claimed_at=NULL,
       run_after=NOW() + INTERVAL LEAST(POW(2, attempts-1), 3600) SECOND
 WHERE id=:id AND status='RUNNING';
UPDATE outbox_jobs SET status='PENDING', claimed_at=NULL                                     -- RUNNING 고아 스위퍼
 WHERE status='RUNNING' AND claimed_at < NOW() - INTERVAL 10 MINUTE;

-- ############ 14. FDS·거절 로그 ############
INSERT INTO fds_alerts (rule_id, principal_type, principal_id, txn_id, detail, status, created_at)
VALUES (:rule, :ptype, :pid, :txn, :detail_masked, 'OPEN', NOW());
SELECT a.id, r.code, r.name, a.principal_type, a.principal_id, a.created_at
FROM fds_alerts a JOIN fds_rules r ON r.id=a.rule_id WHERE a.status='OPEN' ORDER BY a.created_at LIMIT 50;
INSERT INTO reject_logs (principal_type, principal_id, ip, action, reject_code, context, created_at)
VALUES (:ptype, :pid, :ip, :action, :code, :ctx_masked, NOW());

-- 14.1 패스스루(감사 교정: t2 기준 1회 합산 + status 필터, ix_txn_initiator_date)
SELECT t2.initiator_id, SUM(t2.amount) AS moved
FROM transactions t2
WHERE t2.initiator_type='USER' AND t2.type IN ('GIFT','WITHDRAW')
  AND t2.status IN ('CONFIRMED','HOLD') AND t2.created_at >= :from_ts
  AND EXISTS (SELECT 1 FROM transactions t1
              WHERE t1.initiator_type='USER' AND t1.initiator_id = t2.initiator_id
                AND t1.type='DEPOSIT' AND t1.status='CONFIRMED'
                AND t1.created_at BETWEEN t2.created_at - INTERVAL 30 MINUTE AND t2.created_at)
GROUP BY t2.initiator_id HAVING moved >= :threshold;

-- 14.2 선물 집중(수신자 기준, ix_txn_counterparty - 감사 신설)
SELECT c.user_id AS receiver_uid, COUNT(DISTINCT t.initiator_id) AS senders, SUM(t.amount) AS recv_sum
FROM transactions t JOIN cards c ON c.id = t.counterparty_card_id
WHERE t.type='GIFT' AND t.status='CONFIRMED' AND t.created_at >= :from_ts
GROUP BY c.user_id HAVING senders >= :n OR recv_sum >= :threshold;

-- 14.3 다계정 기기(ix_device_uid - 감사 신설)
SELECT device_uid, COUNT(DISTINCT CONCAT(principal_type,':',principal_id)) AS principals
FROM auth_devices WHERE device_uid = :duid GROUP BY device_uid;
SELECT d.device_uid, COUNT(DISTINCT d.principal_id) AS cnt FROM auth_devices d
WHERE d.created_at >= :from_ts GROUP BY d.device_uid HAVING cnt >= :n;

-- 14.4 IP 열거(ix_reject_ip - 감사 신설)
SELECT ip, COUNT(*) AS attempts FROM reject_logs
WHERE created_at >= NOW() - INTERVAL 10 MINUTE AND reject_code IN ('USER_NOT_FOUND','QR_INVALID','PIN_FAIL')
GROUP BY ip HAVING attempts >= :n;

-- ############ 15. 콘텐츠·약관·문의·앱상태 ############
SELECT id, kind, title, starts_at FROM notices
 WHERE status='PUBLISHED' AND (starts_at IS NULL OR starts_at<=NOW()) AND (ends_at IS NULL OR ends_at>=NOW())
 ORDER BY id DESC LIMIT 20;
SELECT id, image_path, link_type, link_target FROM banners
 WHERE status='PUBLISHED' AND starts_at<=NOW() AND ends_at>=NOW() ORDER BY sort_order;
SELECT id, ntype, title, body, deeplink, read_at, created_at FROM notification_inbox
 WHERE principal_type=:pt AND principal_id=:pid AND created_at >= NOW() - INTERVAL 30 DAY
 ORDER BY created_at DESC, id DESC LIMIT 30;
UPDATE notification_inbox SET read_at=NOW() WHERE id=:nid AND principal_type=:pt AND principal_id=:pid;

-- 15.1 약관(감사 보완: requires_reconsent 포함 + 재동의 판정)
SELECT id, version, body, effective_at, requires_reconsent FROM policy_documents
 WHERE kind=:kind AND status='ACTIVE' AND effective_at<=NOW() ORDER BY effective_at DESC LIMIT 1;
SELECT d.id, d.kind, d.version FROM policy_documents d
 WHERE d.status='ACTIVE' AND d.requires_reconsent = TRUE AND d.effective_at <= NOW()
   AND NOT EXISTS (SELECT 1 FROM policy_agreements a
                   WHERE a.policy_id = d.id AND a.principal_type=:pt AND a.principal_id=:pid);
INSERT INTO policy_agreements (principal_type, principal_id, policy_id, agreed_at) VALUES (:pt,:pid,:doc,NOW());

-- 15.2 문의
INSERT INTO inquiries (principal_type, principal_id, title, body, status, created_at)
VALUES (:pt,:pid,:title,:body,'OPEN',NOW());
INSERT INTO inquiry_attachments (inquiry_id, file_path, created_at) VALUES (:iq, :path, NOW());

-- 15.3 앱 상태(/app-status·CPM 임계 - 감사 보완)
SELECT skey, svalue FROM global_settings
 WHERE skey IN ('MAINTENANCE_ON','MAINTENANCE_MSG','MAINTENANCE_UNTIL','MIN_APP_VERSION_IOS','MIN_APP_VERSION_ANDROID');
SELECT svalue FROM global_settings WHERE skey='CPM_CONFIRM_THRESHOLD';

-- ############ 16. 탈퇴 ############

-- 16.1 탈퇴 가능 검증(잔액·진행중(0.6)·미수령 선물·취소기간 미경과 결제)
SELECT COALESCE(SUM(w.balance),0) AS total_balance FROM wallets w JOIN cards c ON c.id=w.card_id WHERE c.user_id=:uid;
SELECT COUNT(*) FROM transactions WHERE initiator_type='USER' AND initiator_id=:uid AND status IN ('PENDING','HOLD','UNKNOWN');
SELECT COUNT(*) FROM gift_links g JOIN cards c ON c.id=g.sender_card_id WHERE c.user_id=:uid AND g.status='CREATED';
SELECT COUNT(*) FROM transactions WHERE initiator_type='USER' AND initiator_id=:uid
  AND type='PAYMENT' AND status='CONFIRMED' AND cancelable_until > NOW();

-- 16.2 [TX] 소액 잔액 포기(감사 신설: FORFEIT_MAX_AMOUNT 이하 + 동의 - 0.3 FORFEIT leg)
SELECT CAST(svalue AS INT) FROM global_settings WHERE skey='FORFEIT_MAX_AMOUNT';
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id, card_id, amount, created_at, confirmed_at)
VALUES (:tuid, 'ADJUST', 'FORFEIT', 'CONFIRMED', 'USER', :uid, :card_id, :bal, NOW(), NOW());
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at) VALUES
 (:txn, :wid, 'DR', :bal, 0, NOW()), (:txn, :for_wid, 'CR', :bal, NULL, NOW());
UPDATE point_lots SET amount_remaining = 0 WHERE wallet_id=:wid AND amount_remaining > 0;
UPDATE wallets SET balance = 0 WHERE id=:wid;
INSERT INTO audit_logs (actor_type, actor_id, action, target_type, target_id, detail, reason, created_at)
VALUES ('SYSTEM', NULL, 'FORFEIT_CONSENT', 'USER', :uid, :consent_record, '탈퇴 소액 포기 동의', NOW());

-- 16.3 [TX] 탈퇴 확정 + 파기 예정일(감사 보완)
UPDATE users SET status='WITHDRAWN', active_ci=NULL, withdrawn_at=NOW(),
       destroy_due_at = DATE_ADD(CURDATE(), INTERVAL 5 YEAR)
 WHERE id=:uid AND status='ACTIVE';
UPDATE cards SET status='EXPIRED' WHERE user_id=:uid AND status='ACTIVE';

-- 16.4 개인정보 파기 배치(감사 신설: destroy_due_at 도래분 익명화)
SELECT id FROM users WHERE status='WITHDRAWN' AND anonymized_at IS NULL AND destroy_due_at <= CURDATE() LIMIT 100;
UPDATE users SET phone_enc=X'00', phone_hash=SHA2(CONCAT('DESTROYED:', id), 256),
       name='(파기)', name_norm='', birth_date=NULL, anonymized_at=NOW() WHERE id=:uid;
-- 버전 이력 파기(마이그레이션 계정 월 배치): DELETE HISTORY FROM users BEFORE SYSTEM_TIME NOW() - INTERVAL 5 YEAR;

-- ############ 17. admin 검색·운영 ############
-- (개인정보 열람 규칙: *_enc 복호화 표시 시 반드시 17.9b 접근기록 INSERT 동반 - 감사 확정)

-- 17.1 회원 검색
SELECT id, login_id, name, status, created_at FROM users WHERE login_id = LOWER(:q) LIMIT 20;
SELECT id, login_id, name, status, created_at FROM users WHERE phone_hash = :phash LIMIT 20;
SELECT id, login_id, name, status, created_at FROM users
 WHERE status = :status AND (created_at < :c_ts OR (created_at = :c_ts AND id < :c_id))
 ORDER BY created_at DESC, id DESC LIMIT 20;
SELECT id, login_id, name, status FROM users WHERE name LIKE CONCAT(:name, '%') LIMIT 20;  -- 저빈도 허용

-- 17.2 매장 검색(ix_merchants_status)
SELECT id, biz_no, biz_name, status, created_at FROM merchants WHERE biz_no = :bizno;
SELECT id, biz_no, biz_name, status FROM merchants
 WHERE status = :status AND id < :c_id ORDER BY id DESC LIMIT 20;
SELECT id, biz_no, biz_name, status FROM merchants WHERE biz_name LIKE CONCAT(:name, '%') LIMIT 20;

-- 17.3 거래 검색(감사 교정: 0.8 - 조건 조합별 정적 SQL 4종)
SELECT * FROM transactions WHERE txn_uid = :tuid;
SELECT id, type, status, amount, created_at FROM transactions
 WHERE created_at >= :from_ts AND created_at < :to_ts
   AND (created_at < :c_ts OR (created_at = :c_ts AND id < :c_id))
 ORDER BY created_at DESC, id DESC LIMIT 50;                                   -- ix_txn_created
SELECT id, type, status, amount, created_at FROM transactions
 WHERE status = :tstatus AND created_at >= :from_ts AND created_at < :to_ts
   AND (created_at < :c_ts OR (created_at = :c_ts AND id < :c_id))
 ORDER BY created_at DESC, id DESC LIMIT 50;                                   -- ix_txn_status
SELECT id, type, status, amount, created_at FROM transactions
 WHERE bank_tran_ref = :ref;                                                   -- ix_txn_bank_ref

-- 17.4 API 신청·인증(감사 보완: merchant_api_credentials SQL 신설)
SELECT mac.id, mac.merchant_id, m.biz_name, mac.requested_at FROM merchant_api_credentials mac
 JOIN merchants m ON m.id = mac.merchant_id
 WHERE mac.status='REQUESTED' ORDER BY mac.requested_at LIMIT 50;               -- ix_apicred_queue
UPDATE merchant_api_credentials SET status='APPROVED', approved_by=:admin_id, client_id=:cid, api_key_hash=:kh
 WHERE id=:id AND status='REQUESTED';
SELECT id, merchant_id, api_key_hash, prev_key_hash, prev_key_expires_at, webhook_url, webhook_secret_enc, mode
 FROM merchant_api_credentials WHERE client_id = :client_id;                    -- PG 요청 인증(고빈도, unique)
UPDATE merchant_api_credentials SET prev_key_hash=api_key_hash,
       prev_key_expires_at=NOW() + INTERVAL 24 HOUR, api_key_hash=:new_hash WHERE merchant_id=:mid;  -- 키 롤
UPDATE merchant_api_credentials SET mode='LIVE'
 WHERE merchant_id=:mid AND status='APPROVED'
   AND test_create_ok_at IS NOT NULL AND test_webhook_ok_at IS NOT NULL AND test_query_ok_at IS NOT NULL;

-- 17.5 미매칭 큐 / 통지 스캔(4.2b 재게)
SELECT ud.id, ud.amount, dn.parsed_name, dn.received_at
FROM unmatched_deposits ud JOIN deposit_notices dn ON dn.id = ud.notice_id
WHERE ud.status = 'PENDING' ORDER BY ud.id LIMIT 50;

-- 17.6 보낸 선물(ix_gift_sender)
SELECT id, amount, status, expires_at FROM gift_links
 WHERE sender_card_id = :card_id AND status = :gstatus ORDER BY id DESC LIMIT 30;

-- 17.7 문의 큐(ix_inquiry_queue) / FDS 큐(14장)
SELECT id, principal_type, principal_id, title, created_at FROM inquiries
 WHERE status = 'OPEN' ORDER BY created_at LIMIT 50;

-- 17.8 PG 미결제 스윕(ix_pg_status)
SELECT id, merchant_id, order_no FROM pg_orders
 WHERE status = 'CREATED' AND created_at <= NOW() - INTERVAL 1 HOUR LIMIT 100;
UPDATE pg_orders SET status='EXPIRED' WHERE id IN (:ids) AND status='CREATED';

-- 17.9 감사로그 / 17.9b 접근기록(감사 보완: 기록·조회 표준)
SELECT id, action, actor_id, reason, created_at FROM audit_logs
 WHERE target_type = :tt AND target_id = :tid ORDER BY id DESC LIMIT 50;
SELECT id, action, target_type, target_id, created_at FROM audit_logs
 WHERE actor_type = 'ADMIN' AND actor_id = :aid AND created_at >= :from_ts
 ORDER BY created_at DESC, id DESC LIMIT 50;
INSERT INTO pii_access_logs (admin_id, target_type, target_id, fields, purpose, created_at)
VALUES (:admin_id, :tt, :tid, :fields, :purpose, NOW());
SELECT admin_id, fields, purpose, created_at FROM pii_access_logs
 WHERE target_type=:tt AND target_id=:tid ORDER BY created_at DESC LIMIT 50;    -- ix_pii_target
SELECT target_type, target_id, fields, created_at FROM pii_access_logs
 WHERE admin_id=:aid AND created_at >= :from_ts ORDER BY created_at DESC LIMIT 50;  -- ix_pii_admin

-- 17.10 일 마감 집계 소스(ix_txn_created) - 18.1이 소비
SELECT type, status, COUNT(*) AS cnt, SUM(amount) AS amt, SUM(fee_amount) AS fee
FROM transactions
WHERE created_at >= :day_start AND created_at < :day_end AND subtype NOT IN ('REISSUE')
GROUP BY type, status;

-- 17.11 대시보드 추이(ux_summary_day_metric)
SELECT summary_date, metric, value FROM daily_summaries
 WHERE summary_date >= :from_d AND summary_date <= :to_d ORDER BY summary_date;

-- 17.12 만료 예정 리포트 / 시스템 지갑 현황(ix_wallets_owner_type)
SELECT DATE(expires_at) AS d, SUM(amount_remaining) FROM point_lots
 WHERE amount_remaining > 0 AND expires_at BETWEEN :f AND :t GROUP BY DATE(expires_at);
SELECT system_code, balance FROM wallets WHERE owner_type = 'SYSTEM';

-- 17.13 캠페인 대상 적재(감사 교정: 키셋 청크 - 장기 TX·잠금 경합 제거)
INSERT INTO push_campaign_targets (campaign_id, principal_type, principal_id)
SELECT :cid, 'USER', id FROM users
 WHERE id > :last_id AND status='ACTIVE' AND marketing_agree = TRUE
 ORDER BY id LIMIT 10000;                                                       -- 배치당 커밋, :last_id 전진

-- 17.14 매장 월정산 마감 소스(감사 교정: 유형 분기 + REISSUE 제외 + 취소 수수료 차감)
SELECT merchant_id,
       SUM(type='PAYMENT') AS sales_count,
       SUM(IF(type='PAYMENT', amount, 0)) AS sales_sum,
       SUM(type='CANCEL') AS cancel_count,
       SUM(IF(type='CANCEL', amount, 0)) AS cancel_sum,
       SUM(IF(type='PAYMENT', amount - COALESCE(vat_amount,0), 0)) AS supply_sum,
       SUM(IF(type='PAYMENT', COALESCE(vat_amount,0), 0)) AS vat_sum,
       SUM(IF(type='PAYMENT', fee_amount, 0)) - SUM(IF(type='CANCEL', fee_amount, 0)) AS fee_sum
FROM transactions
WHERE type IN ('PAYMENT','CANCEL') AND status IN ('CONFIRMED','CANCELED')
  AND created_at >= :m_start AND created_at < :m_end AND merchant_id IS NOT NULL
GROUP BY merchant_id;

-- 17.15 강제 조치(감사 보완: 사유 필수 + 감사)
UPDATE users SET status='SUSPENDED' WHERE id=:uid AND status='ACTIVE';
UPDATE cards SET status='SUSPENDED' WHERE id=:card_id AND status='ACTIVE';
UPDATE merchants SET status='SUSPENDED' WHERE id=:mid AND status='ACTIVE';
INSERT INTO audit_logs (actor_type, actor_id, action, target_type, target_id, reason, ip, created_at)
VALUES ('ADMIN', :admin_id, :force_action, :tt, :tid, :reason, :ip, NOW());

-- ############ 18. 차트·집계 (감사 신설 - 100만건+ 즉시 응답 계층) ############
-- 재집계 윈도우 W = MAX(취소기한) + MAX(출금대기일) + 1 (정책 상한으로 고정) -
-- 마감 배치는 매일 최근 W일을 멱등(ODKU) 재계산해 지연 상태 전이를 흡수한다.

-- 18.1 daily_summaries 적재(17.10 소스 → ODKU)
INSERT INTO daily_summaries (summary_date, metric, value)
SELECT :d, CONCAT(:metric_prefix, '_', :bucket), :value FROM DUAL
ON DUPLICATE KEY UPDATE value = VALUES(value);

-- 18.2 merchant_daily_summaries 적재(멱등 - 17.14 일 단위판)
INSERT INTO merchant_daily_summaries (merchant_id, summary_date, sales_count, sales_sum,
    cancel_count, cancel_sum, supply_sum, vat_sum, fee_sum)
SELECT merchant_id, DATE(created_at),
       SUM(type='PAYMENT'), SUM(IF(type='PAYMENT', amount, 0)),
       SUM(type='CANCEL'),  SUM(IF(type='CANCEL', amount, 0)),
       SUM(IF(type='PAYMENT', amount - COALESCE(vat_amount,0), 0)),
       SUM(IF(type='PAYMENT', COALESCE(vat_amount,0), 0)),
       SUM(IF(type='PAYMENT', fee_amount, 0)) - SUM(IF(type='CANCEL', fee_amount, 0))
FROM transactions
WHERE type IN ('PAYMENT','CANCEL') AND status IN ('CONFIRMED','CANCELED')
  AND created_at >= :recalc_start AND created_at < :day_end AND merchant_id IS NOT NULL
GROUP BY merchant_id, DATE(created_at)
ON DUPLICATE KEY UPDATE sales_count=VALUES(sales_count), sales_sum=VALUES(sales_sum),
  cancel_count=VALUES(cancel_count), cancel_sum=VALUES(cancel_sum),
  supply_sum=VALUES(supply_sum), vat_sum=VALUES(vat_sum), fee_sum=VALUES(fee_sum);

-- 18.3 매장 차트 조회(과거=집계 range 365행 + 당일=실시간 UNION / 결측일 0채움=Java)
SELECT summary_date AS d, sales_count, sales_sum, cancel_sum, vat_sum, fee_sum
FROM merchant_daily_summaries
WHERE merchant_id=:mid AND summary_date >= :from_d AND summary_date < CURDATE()
UNION ALL
SELECT CURDATE(), SUM(type='PAYMENT'), SUM(IF(type='PAYMENT',amount,0)),
       SUM(IF(type='CANCEL',amount,0)), SUM(IF(type='PAYMENT',COALESCE(vat_amount,0),0)),
       SUM(IF(type='PAYMENT',fee_amount,0))
FROM transactions
WHERE merchant_id=:mid AND type IN ('PAYMENT','CANCEL') AND status IN ('CONFIRMED','CANCELED')
  AND created_at >= CURDATE();

-- 18.4 hourly_summaries(admin 전역 당일 시간대 - 시간 배치)
INSERT INTO hourly_summaries (stat_hour, metric, value)
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00'), CONCAT(type,'_SUM'), SUM(amount)
FROM transactions
WHERE created_at >= :hour_start AND created_at < :hour_end AND subtype NOT IN ('REISSUE')
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00'), type
ON DUPLICATE KEY UPDATE value = VALUES(value);
SELECT stat_hour, metric, value FROM hourly_summaries
 WHERE stat_hour >= :from_h AND stat_hour < :to_h ORDER BY stat_hour;

-- 18.5 월명세 생성(마감 배치 - 전이 윈도우 W일 경과 후 확정)
INSERT INTO monthly_statements (card_id, yyyymm, deposit_sum, withdraw_sum, payment_sum,
    gift_sent_sum, gift_recv_sum, expire_sum, fee_sum, closing_balance, created_at)
SELECT c.id, :ym,
  COALESCE(SUM(IF(t.type='DEPOSIT'  AND t.subtype NOT IN ('REISSUE'), t.amount, 0)),0),
  COALESCE(SUM(IF(t.type='WITHDRAW' AND t.subtype NOT IN ('REISSUE') AND t.status IN ('CONFIRMED','PENDING','HOLD','UNKNOWN'), t.amount, 0)),0),
  COALESCE(SUM(IF(t.type='PAYMENT'  AND t.status='CONFIRMED', t.amount, 0)),0),
  COALESCE(SUM(IF(t.type='GIFT' AND t.card_id=c.id, t.amount, 0)),0),
  COALESCE((SELECT SUM(g.amount) FROM transactions g
            WHERE g.counterparty_card_id=c.id AND g.type='GIFT' AND g.status='CONFIRMED'
              AND g.created_at >= :m_start AND g.created_at < :m_end),0),
  COALESCE(SUM(IF(t.type='EXPIRE', t.amount, 0)),0),
  COALESCE(SUM(t.fee_amount),0),
  (SELECT s.balance FROM daily_wallet_snapshots s JOIN wallets w2 ON w2.id=s.wallet_id
    WHERE w2.card_id=c.id AND s.snap_date < :next_month_first ORDER BY s.snap_date DESC LIMIT 1),
  NOW()
FROM cards c LEFT JOIN transactions t
  ON t.card_id=c.id AND t.created_at >= :m_start AND t.created_at < :m_end
GROUP BY c.id
ON DUPLICATE KEY UPDATE deposit_sum=VALUES(deposit_sum), withdraw_sum=VALUES(withdraw_sum),
  payment_sum=VALUES(payment_sum), gift_sent_sum=VALUES(gift_sent_sum), gift_recv_sum=VALUES(gift_recv_sum),
  expire_sum=VALUES(expire_sum), fee_sum=VALUES(fee_sum), closing_balance=VALUES(closing_balance);

-- 18.6 집계-원장 대사(SUMMARY_RECON - 감사 신설): 전일 집계를 원본 재유도와 대조
SELECT s.metric, s.value AS stored,
       (SELECT COALESCE(SUM(amount),0) FROM transactions
         WHERE type = SUBSTRING_INDEX(s.metric,'_',1) AND status='CONFIRMED'
           AND subtype NOT IN ('REISSUE')
           AND created_at >= :yday_start AND created_at < :day_start) AS derived
FROM daily_summaries s WHERE s.summary_date = :yesterday AND s.metric LIKE '%_CONFIRMED'
HAVING stored <> derived;

-- ############ 19. 보존·정리 (보존 매트릭스 집행 - 감사 신설) ############
DELETE FROM outbox_jobs WHERE status IN ('DONE') AND created_at < NOW() - INTERVAL 90 DAY LIMIT 10000;      -- 반복 실행
DELETE FROM webhook_deliveries WHERE status='DELIVERED' AND created_at < NOW() - INTERVAL 180 DAY LIMIT 10000;
DELETE FROM hourly_summaries WHERE stat_hour < NOW() - INTERVAL 90 DAY LIMIT 10000;
-- EXHAUSTED outbox·webhook은 검토(OUTBOX_HEALTH 대사) 후 수동 정리. 파티션 테이블은 routines.sql 이벤트가 DROP.
-- transactions·deposit_notices 아카이브는 routines.sql 6절 런북(DBA 절차).