마이그레이션 · V2

-- =====================================================================
-- V2: MariaDB 고유 기능 적용 (V1 = schema.dbml 생성 DDL)
-- 전수 감사(2026-07-21) 반영판 - MariaDB 11.4 실DB 재검증 대상
-- =====================================================================

-- ---------------------------------------------------------------------
-- 1. CHECK 제약 (돈 관련 불변식)
--    시스템 지갑은 음수 허용(SETTLEMENT_CLEARING=은행 대사 계정, 감사 확정 규약)
-- ---------------------------------------------------------------------
ALTER TABLE wallets
  ADD CONSTRAINT chk_wallet_balance CHECK (owner_type = 'SYSTEM' OR balance >= 0),
  ADD CONSTRAINT chk_wallet_owner CHECK (
    (owner_type = 'USER_CARD' AND card_id IS NOT NULL AND merchant_id IS NULL AND system_code IS NULL) OR
    (owner_type = 'MERCHANT'  AND merchant_id IS NOT NULL AND card_id IS NULL AND system_code IS NULL) OR
    (owner_type = 'SYSTEM'    AND system_code IS NOT NULL AND card_id IS NULL AND merchant_id IS NULL)
  );

ALTER TABLE point_lots
  ADD CONSTRAINT chk_lot_amounts CHECK (amount_remaining >= 0 AND amount_remaining <= amount_init AND amount_init > 0);

ALTER TABLE ledger_entries
  ADD CONSTRAINT chk_ledger_amount CHECK (amount > 0),
  ADD CONSTRAINT chk_ledger_direction CHECK (direction IN ('DR','CR'));
-- balance_after NULL 허용은 SYSTEM 지갑 leg 전용(교차 테이블 CHECK 불가 → SYSTEM_WALLET 대사가 검증)

ALTER TABLE transactions
  ADD CONSTRAINT chk_txn_amount CHECK (amount > 0),
  ADD CONSTRAINT chk_txn_fee CHECK (fee_amount >= 0);

ALTER TABLE lot_allocations
  ADD CONSTRAINT chk_alloc_amount CHECK (amount > 0);

-- ---------------------------------------------------------------------
-- 2. 월별 RANGE 파티셔닝 (7종 - 감사 반영: login_histories·pii_access_logs 편입)
--    * 파티션 테이블 FK 불가 → 해당 FK 제거(논리 참조 + 고아 검사 대사)
--    * transactions·deposit_notices는 UNIQUE 멱등 우선으로 비파티션(아카이브 런북)
-- ---------------------------------------------------------------------
ALTER TABLE ledger_entries DROP FOREIGN KEY ledger_entries_ibfk_1;
ALTER TABLE ledger_entries DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE ledger_entries PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
  PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
  PARTITION p_max   VALUES LESS THAN MAXVALUE
);

ALTER TABLE notification_inbox DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE notification_inbox PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
  PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
  PARTITION p_max   VALUES LESS THAN MAXVALUE
);

ALTER TABLE external_api_logs DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE external_api_logs PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
  PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
  PARTITION p_max   VALUES LESS THAN MAXVALUE
);

ALTER TABLE reject_logs DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE reject_logs PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
  PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
  PARTITION p_max   VALUES LESS THAN MAXVALUE
);

ALTER TABLE app_error_logs DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE app_error_logs PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
  PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
  PARTITION p_max   VALUES LESS THAN MAXVALUE
);

ALTER TABLE login_histories DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE login_histories PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
  PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
  PARTITION p_max   VALUES LESS THAN MAXVALUE
);

ALTER TABLE pii_access_logs DROP FOREIGN KEY pii_access_logs_ibfk_1;
ALTER TABLE pii_access_logs DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE pii_access_logs PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
  PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
  PARTITION p_max   VALUES LESS THAN MAXVALUE
);

-- ---------------------------------------------------------------------
-- 3. SYSTEM VERSIONING (17종 - 감사 반영: 보안 가변 마스터 4종 추가)
-- ---------------------------------------------------------------------
ALTER TABLE users               ADD SYSTEM VERSIONING;
ALTER TABLE merchants           ADD SYSTEM VERSIONING;
ALTER TABLE cards               ADD SYSTEM VERSIONING;
ALTER TABLE admin_accounts      ADD SYSTEM VERSIONING;
ALTER TABLE bank_accounts       ADD SYSTEM VERSIONING;
ALTER TABLE fee_policies        ADD SYSTEM VERSIONING;
ALTER TABLE limit_policies      ADD SYSTEM VERSIONING;
ALTER TABLE payout_policies     ADD SYSTEM VERSIONING;
ALTER TABLE cancel_policies     ADD SYSTEM VERSIONING;
ALTER TABLE card_quota_policies ADD SYSTEM VERSIONING;
ALTER TABLE point_expiry_policies ADD SYSTEM VERSIONING;
ALTER TABLE global_settings     ADD SYSTEM VERSIONING;
ALTER TABLE fds_rules           ADD SYSTEM VERSIONING;
ALTER TABLE merchant_api_credentials ADD SYSTEM VERSIONING;  -- 웹훅 URL·모드·키 롤 이력(감사 보완)
ALTER TABLE merchant_accounts   ADD SYSTEM VERSIONING;
ALTER TABLE admin_allowed_ips   ADD SYSTEM VERSIONING;
ALTER TABLE deposit_identifiers ADD SYSTEM VERSIONING;