-- ===========================================================================
-- 99-bulk-derive.sql : 대량 히스토리 매입(98-bulk)의 파생 테이블 적재.
--   승인/원장/정산/가맹점집계. ID 는 오프셋 대역(비충돌). idempotent 가드.
-- ===========================================================================

-- 승인 (매입 1:1, 99%)
INSERT INTO approval (approval_id, purchase_id, approved, approved_at)
SELECT purchase_id, purchase_id, (random() < 0.99),
       created_at
FROM purchase p WHERE p.purchase_id >= 10000000
  AND NOT EXISTS (SELECT 1 FROM approval a WHERE a.approval_id = p.purchase_id);

-- 원장: 정산완료(S) 건 RECON + SETTLE 이중기표
INSERT INTO ledger (ledger_id, purchase_id, entry_type, amount, biz_date, created_at)
SELECT 100000000 + row_number() OVER (ORDER BY purchase_id), purchase_id, 'RECON', net, biz_date,
       biz_date::timestamptz + interval '20 hours'
FROM purchase WHERE purchase_id >= 10000000 AND status = 'S'
  AND NOT EXISTS (SELECT 1 FROM ledger l WHERE l.ledger_id >= 100000000 LIMIT 1);
INSERT INTO ledger (ledger_id, purchase_id, entry_type, amount, biz_date, created_at)
SELECT 200000000 + row_number() OVER (ORDER BY purchase_id), purchase_id, 'SETTLE', net, biz_date,
       biz_date::timestamptz + interval '21 hours'
FROM purchase WHERE purchase_id >= 10000000 AND status = 'S'
  AND NOT EXISTS (SELECT 1 FROM ledger l WHERE l.ledger_id >= 200000000 LIMIT 1);

-- 정산 (S건, T+1 지급)
INSERT INTO settlement (settlement_id, purchase_id, merchant_id, net, biz_date, status, settled_at)
SELECT 30000000 + row_number() OVER (ORDER BY purchase_id), purchase_id, merchant_id, net, biz_date,
       'SETTLED', biz_date::timestamptz + interval '1 day 10 hours'
FROM purchase WHERE purchase_id >= 10000000 AND status = 'S'
  AND NOT EXISTS (SELECT 1 FROM settlement s WHERE s.settlement_id >= 30000000 LIMIT 1);

-- 가맹점 일집계
INSERT INTO merchant_summary (merchant_id, biz_date, txn_count, gross_amount, fee_amount, net_amount)
SELECT merchant_id, biz_date, count(*), sum(amount), sum(fee), sum(net)
FROM purchase WHERE purchase_id >= 10000000
GROUP BY merchant_id, biz_date
ON CONFLICT (merchant_id, biz_date) DO NOTHING;

-- 시퀀스 정렬 (라이브 채번이 오프셋 대역 뒤로 가도록)
SELECT setval('purchase_seq',   (SELECT max(purchase_id)   FROM purchase));
SELECT setval('approval_seq',   (SELECT max(approval_id)   FROM approval));
SELECT setval('ledger_seq',     (SELECT max(ledger_id)     FROM ledger));
SELECT setval('settlement_seq', (SELECT max(settlement_id) FROM settlement));