1130 lines
47 KiB
PL/PgSQL
1130 lines
47 KiB
PL/PgSQL
-- =============================================================================
|
||
-- Genex Platform - Comprehensive Seed Data
|
||
-- =============================================================================
|
||
-- Purpose: Populate dev/demo database with realistic data covering the full
|
||
-- user lifecycle: registration -> KYC -> wallet -> coupon browsing
|
||
-- -> purchase -> trading -> settlement.
|
||
--
|
||
-- Usage: psql -U genex -d genex_dev -f migrations/seed_data.sql
|
||
-- (Run AFTER all 000-025 migrations have been applied)
|
||
--
|
||
-- Password: All users share the password 'Test123456!'
|
||
-- The hash below is a bcrypt placeholder. In a real environment,
|
||
-- generate via: await bcrypt.hash('Test123456!', 12)
|
||
-- Placeholder hash: $2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM
|
||
--
|
||
-- Idempotency: All INSERTs use ON CONFLICT DO NOTHING so this script can be
|
||
-- re-run safely without duplicating data.
|
||
-- =============================================================================
|
||
|
||
BEGIN;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 0. Password hash constant (used for ALL seed users)
|
||
-- ---------------------------------------------------------------------------
|
||
-- bcrypt hash for 'Test123456!' with cost factor 12
|
||
-- In production, each user would have a unique salt; this is for dev/demo only.
|
||
DO $$
|
||
DECLARE
|
||
pw_hash CONSTANT VARCHAR := '$2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM';
|
||
BEGIN
|
||
RAISE NOTICE 'Seed data: using shared password hash for all users (password: Test123456!)';
|
||
END $$;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 1. USERS (10 users)
|
||
-- ---------------------------------------------------------------------------
|
||
-- Roles: 1 admin, 2 issuers, 1 market_maker, 6 regular users
|
||
-- KYC levels: 0 (none), 1 (basic), 2 (enhanced), 3 (institutional)
|
||
INSERT INTO users (id, phone, email, password_hash, nickname, avatar_url, kyc_level, wallet_mode, role, status, residence_state, nationality, last_login_at, created_at) VALUES
|
||
|
||
-- [U01] Super Admin
|
||
('a0000000-0001-4000-8000-000000000001',
|
||
'13900000001', 'admin@gogenex.com',
|
||
'$2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM',
|
||
'系统管理员', NULL, 3, 'pro', 'admin', 'active', 'CA', 'US',
|
||
NOW() - INTERVAL '1 hour', NOW() - INTERVAL '180 days'),
|
||
|
||
-- [U02] Issuer Account #1 - Coffee Chain
|
||
('a0000000-0002-4000-8000-000000000002',
|
||
'13800100002', 'issuer.coffee@gogenex.com',
|
||
'$2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM',
|
||
'星享咖啡官方', 'https://cdn.gogenex.com/avatars/issuer_coffee.png', 2, 'standard', 'issuer', 'active', 'SH', 'CN',
|
||
NOW() - INTERVAL '3 hours', NOW() - INTERVAL '150 days'),
|
||
|
||
-- [U03] Issuer Account #2 - Cinema Group
|
||
('a0000000-0003-4000-8000-000000000003',
|
||
'13800200003', 'issuer.cinema@gogenex.com',
|
||
'$2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM',
|
||
'云顶影业官方', 'https://cdn.gogenex.com/avatars/issuer_cinema.png', 2, 'standard', 'issuer', 'active', 'BJ', 'CN',
|
||
NOW() - INTERVAL '6 hours', NOW() - INTERVAL '120 days'),
|
||
|
||
-- [U04] Market Maker
|
||
('a0000000-0004-4000-8000-000000000004',
|
||
'13800300004', 'mm@gogenex.com',
|
||
'$2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM',
|
||
'做市商Alpha', NULL, 2, 'pro', 'market_maker', 'active', 'HK', 'HK',
|
||
NOW() - INTERVAL '30 minutes', NOW() - INTERVAL '100 days'),
|
||
|
||
-- [U05] Regular user - KYC Level 3 (full verification)
|
||
('a0000000-0005-4000-8000-000000000005',
|
||
'13600500005', 'zhangwei@example.com',
|
||
'$2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM',
|
||
'张伟', 'https://cdn.gogenex.com/avatars/user_zhangwei.png', 3, 'standard', 'user', 'active', 'GD', 'CN',
|
||
NOW() - INTERVAL '2 hours', NOW() - INTERVAL '90 days'),
|
||
|
||
-- [U06] Regular user - KYC Level 2
|
||
('a0000000-0006-4000-8000-000000000006',
|
||
'15800600006', 'lina@example.com',
|
||
'$2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM',
|
||
'李娜', NULL, 2, 'standard', 'user', 'active', 'JS', 'CN',
|
||
NOW() - INTERVAL '1 day', NOW() - INTERVAL '75 days'),
|
||
|
||
-- [U07] Regular user - KYC Level 1
|
||
('a0000000-0007-4000-8000-000000000007',
|
||
'18600700007', 'wangfang@example.com',
|
||
'$2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM',
|
||
'王芳', NULL, 1, 'standard', 'user', 'active', 'ZJ', 'CN',
|
||
NOW() - INTERVAL '3 days', NOW() - INTERVAL '60 days'),
|
||
|
||
-- [U08] Regular user - KYC Level 0 (new user, no KYC)
|
||
('a0000000-0008-4000-8000-000000000008',
|
||
'17700800008', 'liuyang@example.com',
|
||
'$2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM',
|
||
'刘洋', NULL, 0, 'standard', 'user', 'active', 'SC', 'CN',
|
||
NOW() - INTERVAL '5 days', NOW() - INTERVAL '30 days'),
|
||
|
||
-- [U09] Regular user - KYC Level 1, frozen account
|
||
('a0000000-0009-4000-8000-000000000009',
|
||
'15900900009', 'chenhao@example.com',
|
||
'$2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM',
|
||
'陈浩', NULL, 1, 'standard', 'user', 'frozen', 'HN', 'CN',
|
||
NOW() - INTERVAL '15 days', NOW() - INTERVAL '45 days'),
|
||
|
||
-- [U10] Regular user - KYC Level 2, active trader
|
||
('a0000000-0010-4000-8000-000000000010',
|
||
'13101000010', 'zhaoxue@example.com',
|
||
'$2a$12$LJ3m4ys3Mz5rQW3b2p3Uke5K6xMz5K6xMz5K6xMz5K6xMz5K6xM',
|
||
'赵雪', 'https://cdn.gogenex.com/avatars/user_zhaoxue.png', 2, 'standard', 'user', 'active', 'LN', 'CN',
|
||
NOW() - INTERVAL '4 hours', NOW() - INTERVAL '55 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2. KYC SUBMISSIONS (5 submissions)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO kyc_submissions (id, user_id, target_level, full_name, id_type, id_number, date_of_birth, id_front_url, id_back_url, selfie_url, address, annual_income, net_worth, status, reject_reason, reviewed_by, reviewed_at, created_at) VALUES
|
||
|
||
-- KYC-1: U05 张伟 -> Level 3 approved
|
||
('b0000000-0001-4000-8000-000000000001',
|
||
'a0000000-0005-4000-8000-000000000005', 3,
|
||
'张伟', 'id_card', '440106199001011234', '1990-01-01',
|
||
'https://cdn.gogenex.com/kyc/u05_front.jpg', 'https://cdn.gogenex.com/kyc/u05_back.jpg',
|
||
'https://cdn.gogenex.com/kyc/u05_selfie.jpg',
|
||
'广东省广州市天河区珠江新城花城大道100号', 350000.00, 1200000.00,
|
||
'approved', NULL,
|
||
'a0000000-0001-4000-8000-000000000001', NOW() - INTERVAL '85 days',
|
||
NOW() - INTERVAL '88 days'),
|
||
|
||
-- KYC-2: U06 李娜 -> Level 2 approved
|
||
('b0000000-0002-4000-8000-000000000002',
|
||
'a0000000-0006-4000-8000-000000000006', 2,
|
||
'李娜', 'id_card', '320105199205152345', '1992-05-15',
|
||
'https://cdn.gogenex.com/kyc/u06_front.jpg', 'https://cdn.gogenex.com/kyc/u06_back.jpg',
|
||
'https://cdn.gogenex.com/kyc/u06_selfie.jpg',
|
||
'江苏省南京市鼓楼区中山路200号', 180000.00, 500000.00,
|
||
'approved', NULL,
|
||
'a0000000-0001-4000-8000-000000000001', NOW() - INTERVAL '70 days',
|
||
NOW() - INTERVAL '73 days'),
|
||
|
||
-- KYC-3: U07 王芳 -> Level 2 rejected (blurry photo)
|
||
('b0000000-0003-4000-8000-000000000003',
|
||
'a0000000-0007-4000-8000-000000000007', 2,
|
||
'王芳', 'id_card', '330102199308203456', '1993-08-20',
|
||
'https://cdn.gogenex.com/kyc/u07_front.jpg', 'https://cdn.gogenex.com/kyc/u07_back.jpg',
|
||
'https://cdn.gogenex.com/kyc/u07_selfie.jpg',
|
||
'浙江省杭州市西湖区文三路300号', 120000.00, 300000.00,
|
||
'rejected', '证件照片模糊，请重新上传清晰照片',
|
||
'a0000000-0001-4000-8000-000000000001', NOW() - INTERVAL '55 days',
|
||
NOW() - INTERVAL '58 days'),
|
||
|
||
-- KYC-4: U08 刘洋 -> Level 1 pending
|
||
('b0000000-0004-4000-8000-000000000004',
|
||
'a0000000-0008-4000-8000-000000000008', 1,
|
||
'刘洋', 'id_card', '510105199506104567', '1995-06-10',
|
||
'https://cdn.gogenex.com/kyc/u08_front.jpg', NULL, NULL,
|
||
'四川省成都市武侯区科华北路400号', NULL, NULL,
|
||
'pending', NULL, NULL, NULL,
|
||
NOW() - INTERVAL '28 days'),
|
||
|
||
-- KYC-5: U10 赵雪 -> Level 2 approved
|
||
('b0000000-0005-4000-8000-000000000005',
|
||
'a0000000-0010-4000-8000-000000000010', 2,
|
||
'赵雪', 'passport', 'E12345678', '1994-12-03',
|
||
'https://cdn.gogenex.com/kyc/u10_front.jpg', NULL,
|
||
'https://cdn.gogenex.com/kyc/u10_selfie.jpg',
|
||
'辽宁省大连市中山区人民路500号', 220000.00, 800000.00,
|
||
'approved', NULL,
|
||
'a0000000-0001-4000-8000-000000000001', NOW() - INTERVAL '50 days',
|
||
NOW() - INTERVAL '53 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 3. WALLETS (10 wallets, one per user)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO wallets (id, user_id, balance, frozen, currency, created_at) VALUES
|
||
|
||
('c0000000-0001-4000-8000-000000000001',
|
||
'a0000000-0001-4000-8000-000000000001', 500000.00, 0.00, 'USD',
|
||
NOW() - INTERVAL '180 days'),
|
||
|
||
('c0000000-0002-4000-8000-000000000002',
|
||
'a0000000-0002-4000-8000-000000000002', 250000.00, 10000.00, 'USD',
|
||
NOW() - INTERVAL '150 days'),
|
||
|
||
('c0000000-0003-4000-8000-000000000003',
|
||
'a0000000-0003-4000-8000-000000000003', 180000.00, 5000.00, 'USD',
|
||
NOW() - INTERVAL '120 days'),
|
||
|
||
('c0000000-0004-4000-8000-000000000004',
|
||
'a0000000-0004-4000-8000-000000000004', 1000000.00, 50000.00, 'USD',
|
||
NOW() - INTERVAL '100 days'),
|
||
|
||
('c0000000-0005-4000-8000-000000000005',
|
||
'a0000000-0005-4000-8000-000000000005', 45000.00, 2000.00, 'USD',
|
||
NOW() - INTERVAL '90 days'),
|
||
|
||
('c0000000-0006-4000-8000-000000000006',
|
||
'a0000000-0006-4000-8000-000000000006', 28000.00, 500.00, 'USD',
|
||
NOW() - INTERVAL '75 days'),
|
||
|
||
('c0000000-0007-4000-8000-000000000007',
|
||
'a0000000-0007-4000-8000-000000000007', 12000.00, 0.00, 'USD',
|
||
NOW() - INTERVAL '60 days'),
|
||
|
||
('c0000000-0008-4000-8000-000000000008',
|
||
'a0000000-0008-4000-8000-000000000008', 1000.00, 0.00, 'USD',
|
||
NOW() - INTERVAL '30 days'),
|
||
|
||
('c0000000-0009-4000-8000-000000000009',
|
||
'a0000000-0009-4000-8000-000000000009', 5500.00, 5500.00, 'USD',
|
||
NOW() - INTERVAL '45 days'),
|
||
|
||
('c0000000-0010-4000-8000-000000000010',
|
||
'a0000000-0010-4000-8000-000000000010', 38000.00, 3000.00, 'USD',
|
||
NOW() - INTERVAL '55 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 4. ISSUERS (2 issuers linked to user accounts)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO issuers (id, user_id, company_name, business_license, contact_name, contact_phone, contact_email, credit_rating, credit_score, issuance_quota, used_quota, tier, status, is_first_month, approved_at, created_at) VALUES
|
||
|
||
-- Issuer #1: 星享咖啡 (linked to U02)
|
||
('d0000000-0001-4000-8000-000000000001',
|
||
'a0000000-0002-4000-8000-000000000002',
|
||
'星享咖啡连锁有限公司', 'BL-2024-SH-00158',
|
||
'陈明辉', '13800100002', 'issuer.coffee@gogenex.com',
|
||
'A', 78.50, 500000.00, 125000.00,
|
||
'gold', 'active', false,
|
||
NOW() - INTERVAL '145 days', NOW() - INTERVAL '148 days'),
|
||
|
||
-- Issuer #2: 云顶影业 (linked to U03)
|
||
('d0000000-0002-4000-8000-000000000002',
|
||
'a0000000-0003-4000-8000-000000000003',
|
||
'云顶影业集团股份有限公司', 'BL-2024-BJ-00672',
|
||
'林小燕', '13800200003', 'issuer.cinema@gogenex.com',
|
||
'AA', 86.20, 1000000.00, 280000.00,
|
||
'platinum', 'active', false,
|
||
NOW() - INTERVAL '115 days', NOW() - INTERVAL '118 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 5. STORES (4 stores across 2 issuers)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO stores (id, issuer_id, name, address, phone, latitude, longitude, status, created_at) VALUES
|
||
|
||
-- 2 stores for 星享咖啡
|
||
('e0000000-0001-4000-8000-000000000001',
|
||
'd0000000-0001-4000-8000-000000000001',
|
||
'星享咖啡 陆家嘴旗舰店',
|
||
'上海市浦东新区陆家嘴环路1088号',
|
||
'021-50501234', 31.2397000, 121.4995000, 'active',
|
||
NOW() - INTERVAL '140 days'),
|
||
|
||
('e0000000-0002-4000-8000-000000000002',
|
||
'd0000000-0001-4000-8000-000000000001',
|
||
'星享咖啡 南京西路店',
|
||
'上海市静安区南京西路1266号恒隆广场B1',
|
||
'021-62881234', 31.2283000, 121.4486000, 'active',
|
||
NOW() - INTERVAL '130 days'),
|
||
|
||
-- 2 stores for 云顶影业
|
||
('e0000000-0003-4000-8000-000000000003',
|
||
'd0000000-0002-4000-8000-000000000002',
|
||
'云顶影城 国贸IMAX店',
|
||
'北京市朝阳区建国门外大街1号国贸商城5层',
|
||
'010-65051234', 39.9085000, 116.4583000, 'active',
|
||
NOW() - INTERVAL '110 days'),
|
||
|
||
('e0000000-0004-4000-8000-000000000004',
|
||
'd0000000-0002-4000-8000-000000000002',
|
||
'云顶影城 南山科技园店',
|
||
'深圳市南山区科技园南区科苑路15号科兴科学园A座3层',
|
||
'0755-86801234', 22.5431000, 113.9495000, 'active',
|
||
NOW() - INTERVAL '100 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 6. COUPONS (8 coupons across categories)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO coupons (id, chain_token_id, issuer_id, name, description, image_url, face_value, current_price, issue_price, total_supply, remaining_supply, expiry_date, coupon_type, category, status, owner_user_id, resale_count, max_resale_count, is_transferable, created_at) VALUES
|
||
|
||
-- C01: 咖啡畅饮券 - Active, dining
|
||
('f0000000-0001-4000-8000-000000000001', 1001,
|
||
'd0000000-0001-4000-8000-000000000001',
|
||
'咖啡畅饮券', '星享咖啡任意门店任意饮品一杯，含经典系列与季节限定',
|
||
'https://cdn.gogenex.com/coupons/coffee_free.png',
|
||
50.00, 42.50, 45.00, 1000, 780,
|
||
CURRENT_DATE + INTERVAL '180 days', 'utility', '餐饮', 'listed',
|
||
NULL, 0, 3, true,
|
||
NOW() - INTERVAL '130 days'),
|
||
|
||
-- C02: 精品手冲体验券 - Active, dining
|
||
('f0000000-0002-4000-8000-000000000002', 1002,
|
||
'd0000000-0001-4000-8000-000000000001',
|
||
'精品手冲体验券', '指定旗舰门店精品单品手冲咖啡体验，含品鉴讲解',
|
||
'https://cdn.gogenex.com/coupons/pourover.png',
|
||
128.00, 108.80, 118.00, 500, 340,
|
||
CURRENT_DATE + INTERVAL '90 days', 'utility', '餐饮', 'listed',
|
||
NULL, 0, 2, true,
|
||
NOW() - INTERVAL '100 days'),
|
||
|
||
-- C03: 200元咖啡储值券 - Active, dining (higher value)
|
||
('f0000000-0003-4000-8000-000000000003', 1003,
|
||
'd0000000-0001-4000-8000-000000000001',
|
||
'200元咖啡储值券', '星享咖啡全线门店通用储值券，可拆分使用',
|
||
'https://cdn.gogenex.com/coupons/coffee_200.png',
|
||
200.00, 172.00, 180.00, 300, 210,
|
||
CURRENT_DATE + INTERVAL '365 days', 'utility', '餐饮', 'listed',
|
||
NULL, 0, 5, true,
|
||
NOW() - INTERVAL '80 days'),
|
||
|
||
-- C04: IMAX电影票 - Active, entertainment
|
||
('f0000000-0004-4000-8000-000000000004', 2001,
|
||
'd0000000-0002-4000-8000-000000000002',
|
||
'IMAX 3D电影通票', '云顶影城任意场次IMAX 3D电影一张，含3D眼镜',
|
||
'https://cdn.gogenex.com/coupons/imax_ticket.png',
|
||
120.00, 96.00, 100.00, 800, 580,
|
||
CURRENT_DATE + INTERVAL '120 days', 'utility', '娱乐', 'listed',
|
||
NULL, 0, 3, true,
|
||
NOW() - INTERVAL '95 days'),
|
||
|
||
-- C05: 年度影院会员卡 - Active, entertainment (premium)
|
||
('f0000000-0005-4000-8000-000000000005', 2002,
|
||
'd0000000-0002-4000-8000-000000000002',
|
||
'年度影院会员卡', '云顶影城全年无限次2D观影，IMAX/4D另加20元差价',
|
||
'https://cdn.gogenex.com/coupons/annual_pass.png',
|
||
999.00, 849.15, 899.00, 200, 142,
|
||
CURRENT_DATE + INTERVAL '365 days', 'utility', '娱乐', 'listed',
|
||
NULL, 0, 1, false,
|
||
NOW() - INTERVAL '90 days'),
|
||
|
||
-- C06: 双人电影套餐券 - Active, entertainment
|
||
('f0000000-0006-4000-8000-000000000006', 2003,
|
||
'd0000000-0002-4000-8000-000000000002',
|
||
'双人电影套餐券', '两张电影票+大桶爆米花+两杯饮料，周末通用',
|
||
'https://cdn.gogenex.com/coupons/couple_combo.png',
|
||
200.00, 165.00, 175.00, 600, 420,
|
||
CURRENT_DATE + INTERVAL '150 days', 'utility', '娱乐', 'listed',
|
||
NULL, 0, 3, true,
|
||
NOW() - INTERVAL '85 days'),
|
||
|
||
-- C07: 旅行出行券 - Paused (seasonal)
|
||
('f0000000-0007-4000-8000-000000000007', 3001,
|
||
'd0000000-0001-4000-8000-000000000001',
|
||
'春节出行礼券500元', '合作酒店及民宿通用代金券，满1000可用',
|
||
'https://cdn.gogenex.com/coupons/travel_500.png',
|
||
500.00, 425.00, 450.00, 100, 100,
|
||
CURRENT_DATE + INTERVAL '60 days', 'utility', '旅行', 'minted',
|
||
NULL, 0, 2, true,
|
||
NOW() - INTERVAL '20 days'),
|
||
|
||
-- C08: 已过期券 - Expired, retail
|
||
('f0000000-0008-4000-8000-000000000008', 4001,
|
||
'd0000000-0001-4000-8000-000000000001',
|
||
'双十一特惠100元券', '全场通用，限2024年双十一活动期间使用',
|
||
'https://cdn.gogenex.com/coupons/1111_special.png',
|
||
100.00, NULL, 88.00, 2000, 0,
|
||
CURRENT_DATE - INTERVAL '30 days', 'utility', '购物', 'expired',
|
||
NULL, 0, 3, true,
|
||
NOW() - INTERVAL '200 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 7. COUPON RULES (4 rules)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO coupon_rules (id, coupon_id, rule_type, rule_value, created_at) VALUES
|
||
|
||
-- C01 rules: per-user limit and transferable
|
||
('70000000-0001-4000-8000-000000000001',
|
||
'f0000000-0001-4000-8000-000000000001', 'per_user_limit',
|
||
'{"max_quantity": 5}',
|
||
NOW() - INTERVAL '130 days'),
|
||
|
||
-- C03 rules: min purchase
|
||
('70000000-0002-4000-8000-000000000002',
|
||
'f0000000-0003-4000-8000-000000000003', 'min_purchase',
|
||
'{"min_amount": 200, "description": "满200元可用"}',
|
||
NOW() - INTERVAL '80 days'),
|
||
|
||
-- C04 rules: store restriction + resale limit
|
||
('70000000-0003-4000-8000-000000000003',
|
||
'f0000000-0004-4000-8000-000000000004', 'store_restriction',
|
||
'{"store_ids": ["e0000000-0003-4000-8000-000000000003", "e0000000-0004-4000-8000-000000000004"], "description": "仅限云顶影城线下门店"}',
|
||
NOW() - INTERVAL '95 days'),
|
||
|
||
-- C05 rules: not transferable (annual pass is personal)
|
||
('70000000-0004-4000-8000-000000000004',
|
||
'f0000000-0005-4000-8000-000000000005', 'transferable',
|
||
'{"enabled": false, "reason": "年度会员卡仅限本人使用"}',
|
||
NOW() - INTERVAL '90 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 8. ORDERS (6 orders - buy and sell at different prices)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO orders (id, user_id, coupon_id, side, order_type, price, quantity, filled_quantity, status, is_maker, cancelled_at, created_at) VALUES
|
||
|
||
-- O01: U05 张伟 buys 咖啡畅饮券 (filled)
|
||
('80000000-0001-4000-8000-000000000001',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'f0000000-0001-4000-8000-000000000001',
|
||
'buy', 'limit', 43.00, 2, 2, 'filled', false, NULL,
|
||
NOW() - INTERVAL '25 days'),
|
||
|
||
-- O02: U04 Market Maker sells 咖啡畅饮券 (filled - matched with O01)
|
||
('80000000-0002-4000-8000-000000000002',
|
||
'a0000000-0004-4000-8000-000000000004',
|
||
'f0000000-0001-4000-8000-000000000001',
|
||
'sell', 'limit', 42.50, 5, 2, 'partial', true, NULL,
|
||
NOW() - INTERVAL '26 days'),
|
||
|
||
-- O03: U06 李娜 buys IMAX电影票 (filled)
|
||
('80000000-0003-4000-8000-000000000003',
|
||
'a0000000-0006-4000-8000-000000000006',
|
||
'f0000000-0004-4000-8000-000000000004',
|
||
'buy', 'limit', 98.00, 1, 1, 'filled', false, NULL,
|
||
NOW() - INTERVAL '20 days'),
|
||
|
||
-- O04: U10 赵雪 sells IMAX电影票 (filled - matched with O03)
|
||
('80000000-0004-4000-8000-000000000004',
|
||
'a0000000-0010-4000-8000-000000000010',
|
||
'f0000000-0004-4000-8000-000000000004',
|
||
'sell', 'limit', 96.00, 3, 1, 'partial', true, NULL,
|
||
NOW() - INTERVAL '21 days'),
|
||
|
||
-- O05: U05 张伟 buy order still open (双人电影套餐券)
|
||
('80000000-0005-4000-8000-000000000005',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'f0000000-0006-4000-8000-000000000006',
|
||
'buy', 'limit', 160.00, 1, 0, 'open', false, NULL,
|
||
NOW() - INTERVAL '5 days'),
|
||
|
||
-- O06: U10 赵雪 sell order cancelled
|
||
('80000000-0006-4000-8000-000000000006',
|
||
'a0000000-0010-4000-8000-000000000010',
|
||
'f0000000-0002-4000-8000-000000000002',
|
||
'sell', 'limit', 115.00, 2, 0, 'cancelled', false,
|
||
NOW() - INTERVAL '8 days',
|
||
NOW() - INTERVAL '12 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 9. TRADES (3 matched trades)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO trades (id, buy_order_id, sell_order_id, coupon_id, buyer_id, seller_id, price, quantity, buyer_fee, seller_fee, status, tx_hash, settled_at, created_at) VALUES
|
||
|
||
-- T01: 咖啡畅饮券 trade (O01 buy matched with O02 sell)
|
||
('90000000-0001-4000-8000-000000000001',
|
||
'80000000-0001-4000-8000-000000000001',
|
||
'80000000-0002-4000-8000-000000000002',
|
||
'f0000000-0001-4000-8000-000000000001',
|
||
'a0000000-0005-4000-8000-000000000005', -- buyer: 张伟
|
||
'a0000000-0004-4000-8000-000000000004', -- seller: Market Maker
|
||
42.50, 2,
|
||
1.2750, 0.8500, -- buyer 1.5%, seller 1.0%
|
||
'settled',
|
||
'0xabc123def456789012345678901234567890abcdef1234567890abcdef123456',
|
||
NOW() - INTERVAL '24 days',
|
||
NOW() - INTERVAL '25 days'),
|
||
|
||
-- T02: IMAX电影票 trade (O03 buy matched with O04 sell)
|
||
('90000000-0002-4000-8000-000000000002',
|
||
'80000000-0003-4000-8000-000000000003',
|
||
'80000000-0004-4000-8000-000000000004',
|
||
'f0000000-0004-4000-8000-000000000004',
|
||
'a0000000-0006-4000-8000-000000000006', -- buyer: 李娜
|
||
'a0000000-0010-4000-8000-000000000010', -- seller: 赵雪
|
||
96.00, 1,
|
||
1.4400, 0.9600, -- buyer 1.5%, seller 1.0%
|
||
'settled',
|
||
'0xdef789abc012345678901234567890abcdef1234567890abcdef1234567890ab',
|
||
NOW() - INTERVAL '19 days',
|
||
NOW() - INTERVAL '20 days'),
|
||
|
||
-- T03: Another 咖啡畅饮券 trade - pending settlement
|
||
('90000000-0003-4000-8000-000000000003',
|
||
'80000000-0001-4000-8000-000000000001',
|
||
'80000000-0002-4000-8000-000000000002',
|
||
'f0000000-0001-4000-8000-000000000001',
|
||
'a0000000-0006-4000-8000-000000000006', -- buyer: 李娜
|
||
'a0000000-0004-4000-8000-000000000004', -- seller: Market Maker
|
||
43.00, 1,
|
||
0.6450, 0.4300,
|
||
'pending', NULL, NULL,
|
||
NOW() - INTERVAL '2 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 10. SETTLEMENTS (4 settlements)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO settlements (id, trade_id, buyer_id, seller_id, amount, buyer_fee, seller_fee, status, tx_hash, completed_at, created_at) VALUES
|
||
|
||
-- S01: Settlement for T01 (completed)
|
||
('91000000-0001-4000-8000-000000000001',
|
||
'90000000-0001-4000-8000-000000000001',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'a0000000-0004-4000-8000-000000000004',
|
||
85.00, 1.2750, 0.8500,
|
||
'completed',
|
||
'0xabc123def456789012345678901234567890abcdef1234567890abcdef123456',
|
||
NOW() - INTERVAL '24 days',
|
||
NOW() - INTERVAL '25 days'),
|
||
|
||
-- S02: Settlement for T02 (completed)
|
||
('91000000-0002-4000-8000-000000000002',
|
||
'90000000-0002-4000-8000-000000000002',
|
||
'a0000000-0006-4000-8000-000000000006',
|
||
'a0000000-0010-4000-8000-000000000010',
|
||
96.00, 1.4400, 0.9600,
|
||
'completed',
|
||
'0xdef789abc012345678901234567890abcdef1234567890abcdef1234567890ab',
|
||
NOW() - INTERVAL '19 days',
|
||
NOW() - INTERVAL '20 days'),
|
||
|
||
-- S03: Settlement for T03 (pending)
|
||
('91000000-0003-4000-8000-000000000003',
|
||
'90000000-0003-4000-8000-000000000003',
|
||
'a0000000-0006-4000-8000-000000000006',
|
||
'a0000000-0004-4000-8000-000000000004',
|
||
43.00, 0.6450, 0.4300,
|
||
'pending', NULL, NULL,
|
||
NOW() - INTERVAL '2 days'),
|
||
|
||
-- S04: A failed settlement (edge case for testing)
|
||
('91000000-0004-4000-8000-000000000004',
|
||
'90000000-0001-4000-8000-000000000001', -- re-uses trade T01 for demo; in production this would be a separate trade
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'a0000000-0004-4000-8000-000000000004',
|
||
42.50, 0.6375, 0.4250,
|
||
'failed', NULL, NULL,
|
||
NOW() - INTERVAL '23 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 11. TRANSACTIONS (15 wallet transactions)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO transactions (id, wallet_id, user_id, type, amount, balance_after, reference_id, reference_type, description, status, created_at) VALUES
|
||
|
||
-- U05 张伟 deposits
|
||
('a1000000-0001-4000-8000-000000000001',
|
||
'c0000000-0005-4000-8000-000000000005',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'deposit', 50000.00, 50000.00,
|
||
NULL, NULL, '银行转账充值', 'completed',
|
||
NOW() - INTERVAL '85 days'),
|
||
|
||
-- U05 张伟 purchases coupon (buy order O01)
|
||
('a1000000-0002-4000-8000-000000000002',
|
||
'c0000000-0005-4000-8000-000000000005',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'purchase', -85.00, 49915.00,
|
||
'80000000-0001-4000-8000-000000000001', 'order',
|
||
'购买咖啡畅饮券x2', 'completed',
|
||
NOW() - INTERVAL '25 days'),
|
||
|
||
-- U05 张伟 fee for trade
|
||
('a1000000-0003-4000-8000-000000000003',
|
||
'c0000000-0005-4000-8000-000000000005',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'fee', -1.28, 49913.72,
|
||
'90000000-0001-4000-8000-000000000001', 'trade',
|
||
'交易手续费(买方)', 'completed',
|
||
NOW() - INTERVAL '25 days'),
|
||
|
||
-- U04 Market Maker deposit
|
||
('a1000000-0004-4000-8000-000000000004',
|
||
'c0000000-0004-4000-8000-000000000004',
|
||
'a0000000-0004-4000-8000-000000000004',
|
||
'deposit', 1000000.00, 1000000.00,
|
||
NULL, NULL, '机构资金注入', 'completed',
|
||
NOW() - INTERVAL '98 days'),
|
||
|
||
-- U04 Market Maker sale proceeds (T01)
|
||
('a1000000-0005-4000-8000-000000000005',
|
||
'c0000000-0004-4000-8000-000000000004',
|
||
'a0000000-0004-4000-8000-000000000004',
|
||
'sale', 85.00, 1000085.00,
|
||
'90000000-0001-4000-8000-000000000001', 'trade',
|
||
'出售咖啡畅饮券x2收入', 'completed',
|
||
NOW() - INTERVAL '24 days'),
|
||
|
||
-- U04 Market Maker fee
|
||
('a1000000-0006-4000-8000-000000000006',
|
||
'c0000000-0004-4000-8000-000000000004',
|
||
'a0000000-0004-4000-8000-000000000004',
|
||
'fee', -0.85, 1000084.15,
|
||
'90000000-0001-4000-8000-000000000001', 'trade',
|
||
'交易手续费(卖方)', 'completed',
|
||
NOW() - INTERVAL '24 days'),
|
||
|
||
-- U06 李娜 deposit
|
||
('a1000000-0007-4000-8000-000000000007',
|
||
'c0000000-0006-4000-8000-000000000006',
|
||
'a0000000-0006-4000-8000-000000000006',
|
||
'deposit', 30000.00, 30000.00,
|
||
NULL, NULL, '银行转账充值', 'completed',
|
||
NOW() - INTERVAL '70 days'),
|
||
|
||
-- U06 李娜 purchase IMAX ticket (O03)
|
||
('a1000000-0008-4000-8000-000000000008',
|
||
'c0000000-0006-4000-8000-000000000006',
|
||
'a0000000-0006-4000-8000-000000000006',
|
||
'purchase', -96.00, 29904.00,
|
||
'80000000-0003-4000-8000-000000000003', 'order',
|
||
'购买IMAX 3D电影通票x1', 'completed',
|
||
NOW() - INTERVAL '20 days'),
|
||
|
||
-- U06 李娜 fee
|
||
('a1000000-0009-4000-8000-000000000009',
|
||
'c0000000-0006-4000-8000-000000000006',
|
||
'a0000000-0006-4000-8000-000000000006',
|
||
'fee', -1.44, 29902.56,
|
||
'90000000-0002-4000-8000-000000000002', 'trade',
|
||
'交易手续费(买方)', 'completed',
|
||
NOW() - INTERVAL '20 days'),
|
||
|
||
-- U10 赵雪 deposit
|
||
('a1000000-0010-4000-8000-000000000010',
|
||
'c0000000-0010-4000-8000-000000000010',
|
||
'a0000000-0010-4000-8000-000000000010',
|
||
'deposit', 40000.00, 40000.00,
|
||
NULL, NULL, '银行转账充值', 'completed',
|
||
NOW() - INTERVAL '50 days'),
|
||
|
||
-- U10 赵雪 sale proceeds (T02)
|
||
('a1000000-0011-4000-8000-000000000011',
|
||
'c0000000-0010-4000-8000-000000000010',
|
||
'a0000000-0010-4000-8000-000000000010',
|
||
'sale', 96.00, 40096.00,
|
||
'90000000-0002-4000-8000-000000000002', 'trade',
|
||
'出售IMAX 3D电影通票x1收入', 'completed',
|
||
NOW() - INTERVAL '19 days'),
|
||
|
||
-- U10 赵雪 fee
|
||
('a1000000-0012-4000-8000-000000000012',
|
||
'c0000000-0010-4000-8000-000000000010',
|
||
'a0000000-0010-4000-8000-000000000010',
|
||
'fee', -0.96, 40095.04,
|
||
'90000000-0002-4000-8000-000000000002', 'trade',
|
||
'交易手续费(卖方)', 'completed',
|
||
NOW() - INTERVAL '19 days'),
|
||
|
||
-- U07 王芳 deposit
|
||
('a1000000-0013-4000-8000-000000000013',
|
||
'c0000000-0007-4000-8000-000000000007',
|
||
'a0000000-0007-4000-8000-000000000007',
|
||
'deposit', 15000.00, 15000.00,
|
||
NULL, NULL, '银行转账充值', 'completed',
|
||
NOW() - INTERVAL '55 days'),
|
||
|
||
-- U07 王芳 withdrawal
|
||
('a1000000-0014-4000-8000-000000000014',
|
||
'c0000000-0007-4000-8000-000000000007',
|
||
'a0000000-0007-4000-8000-000000000007',
|
||
'withdraw', -3000.00, 12000.00,
|
||
NULL, NULL, '提现到银行卡', 'completed',
|
||
NOW() - INTERVAL '40 days'),
|
||
|
||
-- U05 张伟 transfer out to U06 李娜
|
||
('a1000000-0015-4000-8000-000000000015',
|
||
'c0000000-0005-4000-8000-000000000005',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'transfer_out', -500.00, 45000.00,
|
||
'a0000000-0006-4000-8000-000000000006', 'user',
|
||
'转账给用户李娜', 'completed',
|
||
NOW() - INTERVAL '15 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 12. MESSAGES (8 messages)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO messages (id, user_id, title, content, type, is_read, reference_type, reference_id, created_at) VALUES
|
||
|
||
-- M01: Welcome message for U05
|
||
('a2000000-0001-4000-8000-000000000001',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'欢迎加入Genex', '您好张伟，欢迎加入Genex券金融平台！现在完成KYC认证即可开始交易。',
|
||
'system', true, NULL, NULL,
|
||
NOW() - INTERVAL '90 days'),
|
||
|
||
-- M02: KYC approved for U05
|
||
('a2000000-0002-4000-8000-000000000002',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'KYC认证已通过', '恭喜！您的KYC L3认证已审核通过，现在可以享受全部交易功能。',
|
||
'kyc', true, 'kyc_submission', 'b0000000-0001-4000-8000-000000000001',
|
||
NOW() - INTERVAL '85 days'),
|
||
|
||
-- M03: Trade completed notification for U05
|
||
('a2000000-0003-4000-8000-000000000003',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'交易成交通知', '您的买单已成交：咖啡畅饮券 x2，成交价 ¥42.50/张，总计 ¥85.00。',
|
||
'trade', true, 'trade', '90000000-0001-4000-8000-000000000001',
|
||
NOW() - INTERVAL '25 days'),
|
||
|
||
-- M04: Deposit notification for U06
|
||
('a2000000-0004-4000-8000-000000000004',
|
||
'a0000000-0006-4000-8000-000000000006',
|
||
'充值成功', '您已成功充值 ¥30,000.00 到钱包，当前余额 ¥30,000.00。',
|
||
'wallet', true, 'transaction', 'a1000000-0007-4000-8000-000000000007',
|
||
NOW() - INTERVAL '70 days'),
|
||
|
||
-- M05: Trade notification for U06
|
||
('a2000000-0005-4000-8000-000000000005',
|
||
'a0000000-0006-4000-8000-000000000006',
|
||
'交易成交通知', '您的买单已成交：IMAX 3D电影通票 x1，成交价 ¥96.00。',
|
||
'trade', false, 'trade', '90000000-0002-4000-8000-000000000002',
|
||
NOW() - INTERVAL '20 days'),
|
||
|
||
-- M06: Account frozen notification for U09
|
||
('a2000000-0006-4000-8000-000000000006',
|
||
'a0000000-0009-4000-8000-000000000009',
|
||
'账户安全通知', '您的账户因安全原因已被临时冻结，请联系客服了解详情。',
|
||
'compliance', false, NULL, NULL,
|
||
NOW() - INTERVAL '15 days'),
|
||
|
||
-- M07: Promotion message for U07
|
||
('a2000000-0007-4000-8000-000000000007',
|
||
'a0000000-0007-4000-8000-000000000007',
|
||
'限时活动：新券上线', '春节出行礼券500元即将上线，限量100张，敬请关注！',
|
||
'promotion', false, 'coupon', 'f0000000-0007-4000-8000-000000000007',
|
||
NOW() - INTERVAL '18 days'),
|
||
|
||
-- M08: System maintenance notice for U08
|
||
('a2000000-0008-4000-8000-000000000008',
|
||
'a0000000-0008-4000-8000-000000000008',
|
||
'系统维护通知', '平台将于本周六凌晨2:00-4:00进行系统升级维护，届时交易功能暂停。',
|
||
'system', false, NULL, NULL,
|
||
NOW() - INTERVAL '10 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 13. AML ALERTS (3 alerts)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO aml_alerts (id, user_id, alert_type, severity, details, status, resolved_by, resolved_at, created_at) VALUES
|
||
|
||
-- AML-1: Structuring alert for U09 (reason for frozen account)
|
||
('a3000000-0001-4000-8000-000000000001',
|
||
'a0000000-0009-4000-8000-000000000009',
|
||
'structuring', 'high',
|
||
'{"pattern": "多笔小额充值紧接大额提现", "total_in": 9800, "total_out": 9500, "window_hours": 24, "tx_count": 12}',
|
||
'investigating', NULL, NULL,
|
||
NOW() - INTERVAL '16 days'),
|
||
|
||
-- AML-2: Buy-transfer-withdraw pattern for U08 (low severity, dismissed)
|
||
('a3000000-0002-4000-8000-000000000002',
|
||
'a0000000-0008-4000-8000-000000000008',
|
||
'buy_transfer_withdraw', 'low',
|
||
'{"pattern": "充值后购买券并快速转让", "amount": 500, "time_span_minutes": 45}',
|
||
'dismissed',
|
||
'a0000000-0001-4000-8000-000000000001',
|
||
NOW() - INTERVAL '22 days',
|
||
NOW() - INTERVAL '25 days'),
|
||
|
||
-- AML-3: Fan-out pattern for U10 (medium, resolved)
|
||
('a3000000-0003-4000-8000-000000000003',
|
||
'a0000000-0010-4000-8000-000000000010',
|
||
'fan_out', 'medium',
|
||
'{"pattern": "同一券多次小额转让给不同用户", "coupon_id": "f0000000-0004-4000-8000-000000000004", "recipient_count": 5, "total_value": 480}',
|
||
'resolved',
|
||
'a0000000-0001-4000-8000-000000000001',
|
||
NOW() - INTERVAL '8 days',
|
||
NOW() - INTERVAL '12 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 14. DISPUTES (2 disputes)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO disputes (id, type, status, plaintiff_id, defendant_id, order_id, description, amount, resolution, resolved_at, created_at, updated_at) VALUES
|
||
|
||
-- D01: Pending buyer complaint - coupon not usable
|
||
('a4000000-0001-4000-8000-000000000001',
|
||
'buyer_claim', 'processing',
|
||
'a0000000-0006-4000-8000-000000000006', -- plaintiff: 李娜
|
||
'a0000000-0010-4000-8000-000000000010', -- defendant: 赵雪
|
||
'80000000-0003-4000-8000-000000000003',
|
||
'购买的IMAX电影通票在到店使用时被拒，商户称券已被核销。请求退款。',
|
||
96.00, NULL, NULL,
|
||
NOW() - INTERVAL '5 days', NOW() - INTERVAL '5 days'),
|
||
|
||
-- D02: Resolved refund request
|
||
('a4000000-0002-4000-8000-000000000002',
|
||
'refund_request', 'resolved',
|
||
'a0000000-0005-4000-8000-000000000005', -- plaintiff: 张伟
|
||
'a0000000-0004-4000-8000-000000000004', -- defendant: Market Maker
|
||
'80000000-0001-4000-8000-000000000001',
|
||
'咖啡畅饮券在门店使用时发现已过活动期，实际可用期限与描述不符。',
|
||
42.50,
|
||
'经核实，该券活动描述确实存在歧义，已为买方全额退款并补偿10元代金券。',
|
||
NOW() - INTERVAL '12 days',
|
||
NOW() - INTERVAL '18 days', NOW() - INTERVAL '12 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 15. AUDIT LOGS (6 entries)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO audit_logs (id, admin_id, admin_name, action, resource, resource_id, details, ip_address, result, created_at, updated_at) VALUES
|
||
|
||
-- Audit: Admin approved KYC for U05
|
||
('a5000000-0001-4000-8000-000000000001',
|
||
'a0000000-0001-4000-8000-000000000001', '系统管理员',
|
||
'kyc.approve', 'kyc_submission', 'b0000000-0001-4000-8000-000000000001',
|
||
'{"user_id": "a0000000-0005-4000-8000-000000000005", "target_level": 3, "full_name": "张伟"}',
|
||
'10.0.1.100', 'success',
|
||
NOW() - INTERVAL '85 days', NOW() - INTERVAL '85 days'),
|
||
|
||
-- Audit: Admin froze U09 account
|
||
('a5000000-0002-4000-8000-000000000002',
|
||
'a0000000-0001-4000-8000-000000000001', '系统管理员',
|
||
'user.freeze', 'user', 'a0000000-0009-4000-8000-000000000009',
|
||
'{"reason": "AML alert - structuring pattern detected", "alert_id": "a3000000-0001-4000-8000-000000000001"}',
|
||
'10.0.1.100', 'success',
|
||
NOW() - INTERVAL '15 days', NOW() - INTERVAL '15 days'),
|
||
|
||
-- Audit: Issuer created coupon C01
|
||
('a5000000-0003-4000-8000-000000000003',
|
||
'a0000000-0002-4000-8000-000000000002', '星享咖啡官方',
|
||
'coupon.create', 'coupon', 'f0000000-0001-4000-8000-000000000001',
|
||
'{"name": "咖啡畅饮券", "face_value": 50.00, "total_supply": 1000}',
|
||
'192.168.1.50', 'success',
|
||
NOW() - INTERVAL '130 days', NOW() - INTERVAL '130 days'),
|
||
|
||
-- Audit: Admin resolved dispute D02
|
||
('a5000000-0004-4000-8000-000000000004',
|
||
'a0000000-0001-4000-8000-000000000001', '系统管理员',
|
||
'dispute.resolve', 'dispute', 'a4000000-0002-4000-8000-000000000002',
|
||
'{"resolution": "refund_approved", "refund_amount": 42.50, "compensation": 10.00}',
|
||
'10.0.1.100', 'success',
|
||
NOW() - INTERVAL '12 days', NOW() - INTERVAL '12 days'),
|
||
|
||
-- Audit: Admin dismissed AML alert
|
||
('a5000000-0005-4000-8000-000000000005',
|
||
'a0000000-0001-4000-8000-000000000001', '系统管理员',
|
||
'aml_alert.dismiss', 'aml_alert', 'a3000000-0002-4000-8000-000000000002',
|
||
'{"reason": "False positive - user is a small business owner with normal transaction pattern"}',
|
||
'10.0.1.100', 'success',
|
||
NOW() - INTERVAL '22 days', NOW() - INTERVAL '22 days'),
|
||
|
||
-- Audit: Admin rejected KYC for U07
|
||
('a5000000-0006-4000-8000-000000000006',
|
||
'a0000000-0001-4000-8000-000000000001', '系统管理员',
|
||
'kyc.reject', 'kyc_submission', 'b0000000-0003-4000-8000-000000000003',
|
||
'{"user_id": "a0000000-0007-4000-8000-000000000007", "target_level": 2, "reason": "证件照片模糊"}',
|
||
'10.0.1.100', 'success',
|
||
NOW() - INTERVAL '55 days', NOW() - INTERVAL '55 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 16. ADDRESS MAPPINGS (for users with KYC >= 1)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO address_mappings (user_id, internal_address, chain_address, chain_type, created_at) VALUES
|
||
|
||
('a0000000-0002-4000-8000-000000000002',
|
||
'genex:issuer_coffee', '0x1a2b3c4d5e6f7a8b9c0d1e2f3a4b5c6d7e8f9a0b', 'evm',
|
||
NOW() - INTERVAL '148 days'),
|
||
|
||
('a0000000-0003-4000-8000-000000000003',
|
||
'genex:issuer_cinema', '0x2b3c4d5e6f7a8b9c0d1e2f3a4b5c6d7e8f9a0b1c', 'evm',
|
||
NOW() - INTERVAL '118 days'),
|
||
|
||
('a0000000-0004-4000-8000-000000000004',
|
||
'genex:mm_alpha', '0x3c4d5e6f7a8b9c0d1e2f3a4b5c6d7e8f9a0b1c2d', 'evm',
|
||
NOW() - INTERVAL '98 days'),
|
||
|
||
('a0000000-0005-4000-8000-000000000005',
|
||
'genex:zhangwei', '0x4d5e6f7a8b9c0d1e2f3a4b5c6d7e8f9a0b1c2d3e', 'evm',
|
||
NOW() - INTERVAL '85 days'),
|
||
|
||
('a0000000-0006-4000-8000-000000000006',
|
||
'genex:lina', '0x5e6f7a8b9c0d1e2f3a4b5c6d7e8f9a0b1c2d3e4f', 'evm',
|
||
NOW() - INTERVAL '70 days'),
|
||
|
||
('a0000000-0007-4000-8000-000000000007',
|
||
'genex:wangfang', '0x6f7a8b9c0d1e2f3a4b5c6d7e8f9a0b1c2d3e4f5a', 'evm',
|
||
NOW() - INTERVAL '58 days'),
|
||
|
||
('a0000000-0010-4000-8000-000000000010',
|
||
'genex:zhaoxue', '0x7a8b9c0d1e2f3a4b5c6d7e8f9a0b1c2d3e4f5a6b', 'evm',
|
||
NOW() - INTERVAL '50 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 17. CREDIT METRICS (snapshots for both issuers)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO credit_metrics (id, issuer_id, redemption_rate, breakage_ratio, market_tenure_months, user_satisfaction, computed_score, computed_rating, snapshot_date, created_at) VALUES
|
||
|
||
-- 星享咖啡 - current metrics
|
||
('a6000000-0001-4000-8000-000000000001',
|
||
'd0000000-0001-4000-8000-000000000001',
|
||
0.7200, 0.0800, 5, 0.8500, 78.50, 'A',
|
||
CURRENT_DATE, NOW()),
|
||
|
||
-- 星享咖啡 - last month
|
||
('a6000000-0002-4000-8000-000000000002',
|
||
'd0000000-0001-4000-8000-000000000001',
|
||
0.6800, 0.0900, 4, 0.8300, 75.20, 'A',
|
||
CURRENT_DATE - INTERVAL '30 days', NOW() - INTERVAL '30 days'),
|
||
|
||
-- 云顶影业 - current metrics
|
||
('a6000000-0003-4000-8000-000000000003',
|
||
'd0000000-0002-4000-8000-000000000002',
|
||
0.8500, 0.0500, 4, 0.9100, 86.20, 'AA',
|
||
CURRENT_DATE, NOW()),
|
||
|
||
-- 云顶影业 - last month
|
||
('a6000000-0004-4000-8000-000000000004',
|
||
'd0000000-0002-4000-8000-000000000002',
|
||
0.8200, 0.0600, 3, 0.8900, 84.50, 'AA',
|
||
CURRENT_DATE - INTERVAL '30 days', NOW() - INTERVAL '30 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 18. OFAC SCREENINGS (3 registration screenings)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO ofac_screenings (id, user_id, screen_type, name_screened, address_screened, is_match, match_score, match_details, action_taken, created_at) VALUES
|
||
|
||
('a7000000-0001-4000-8000-000000000001',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'registration', '张伟', NULL, false, 0.00, NULL, 'none',
|
||
NOW() - INTERVAL '90 days'),
|
||
|
||
('a7000000-0002-4000-8000-000000000002',
|
||
'a0000000-0006-4000-8000-000000000006',
|
||
'registration', '李娜', NULL, false, 0.00, NULL, 'none',
|
||
NOW() - INTERVAL '75 days'),
|
||
|
||
('a7000000-0003-4000-8000-000000000003',
|
||
'a0000000-0009-4000-8000-000000000009',
|
||
'transaction', '陈浩',
|
||
'0x9999999999999999999999999999999999999999', false, 12.50,
|
||
'{"closest_match": "Chen Hao (SDN List)", "list": "SDN", "score": 12.50, "threshold": 85.00}',
|
||
'none',
|
||
NOW() - INTERVAL '40 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 19. JOURNAL ENTRIES (4 accounting entries)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO journal_entries (id, entry_date, debit_account, debit_amount, credit_account, credit_amount, memo, reference_type, reference_id, tx_hash, created_at) VALUES
|
||
|
||
-- Trading fee revenue from T01
|
||
('a8000000-0001-4000-8000-000000000001',
|
||
CURRENT_DATE - INTERVAL '24 days',
|
||
'1001', 2.13, '4001', 2.13,
|
||
'交易手续费收入 - 咖啡畅饮券 T01 (buyer_fee=1.28 + seller_fee=0.85)',
|
||
'trade', '90000000-0001-4000-8000-000000000001',
|
||
'0xabc123def456789012345678901234567890abcdef1234567890abcdef123456',
|
||
NOW() - INTERVAL '24 days'),
|
||
|
||
-- Trading fee revenue from T02
|
||
('a8000000-0002-4000-8000-000000000002',
|
||
CURRENT_DATE - INTERVAL '19 days',
|
||
'1001', 2.40, '4001', 2.40,
|
||
'交易手续费收入 - IMAX电影通票 T02 (buyer_fee=1.44 + seller_fee=0.96)',
|
||
'trade', '90000000-0002-4000-8000-000000000002',
|
||
'0xdef789abc012345678901234567890abcdef1234567890abcdef1234567890ab',
|
||
NOW() - INTERVAL '19 days'),
|
||
|
||
-- User deposit recognized as custodial liability
|
||
('a8000000-0003-4000-8000-000000000003',
|
||
CURRENT_DATE - INTERVAL '85 days',
|
||
'1001', 50000.00, '2002', 50000.00,
|
||
'用户充值 - 张伟 U05, 资金托管负债',
|
||
'transaction', 'a1000000-0001-4000-8000-000000000001', NULL,
|
||
NOW() - INTERVAL '85 days'),
|
||
|
||
-- Deferred revenue for coupon issuance
|
||
('a8000000-0004-4000-8000-000000000004',
|
||
CURRENT_DATE - INTERVAL '130 days',
|
||
'1101', 45000.00, '2001', 45000.00,
|
||
'券发行递延收入 - 咖啡畅饮券 C01 (1000张 x ¥45发行价)',
|
||
'coupon', 'f0000000-0001-4000-8000-000000000001', NULL,
|
||
NOW() - INTERVAL '130 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 20. SAR REPORTS (1 report linked to AML alert)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO sar_reports (id, alert_id, user_id, filing_type, subject_info, suspicious_activity, total_amount, date_range_start, date_range_end, narrative, fincen_filing_id, status, filed_at, created_at) VALUES
|
||
|
||
('a9000000-0001-4000-8000-000000000001',
|
||
'a3000000-0001-4000-8000-000000000001',
|
||
'a0000000-0009-4000-8000-000000000009',
|
||
'initial',
|
||
'{"name": "陈浩", "id_type": "id_card", "id_number": "430***********9876", "phone": "159****0009", "account_opened": "2025-01-01"}',
|
||
'{"type": "structuring", "pattern": "多笔小额充值紧接大额提现", "transactions": [{"amount": 800, "type": "deposit"}, {"amount": 900, "type": "deposit"}, {"amount": 750, "type": "deposit"}, {"amount": 9500, "type": "withdraw"}]}',
|
||
9800.00,
|
||
CURRENT_DATE - INTERVAL '20 days',
|
||
CURRENT_DATE - INTERVAL '16 days',
|
||
'用户陈浩在24小时内进行了12笔小额充值（总计9,800元），随后立即发起一笔9,500元的大额提现。该行为符合资金分拆(structuring)模式，涉嫌规避大额交易报告门槛。账户已冻结待调查。',
|
||
NULL, -- Not yet filed with FinCEN
|
||
'pending_review',
|
||
NULL,
|
||
NOW() - INTERVAL '14 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 21. BREAKAGE RECORDS (1 record for expired coupon C08)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO breakage_records (id, coupon_id, issuer_id, face_value, total_amount, platform_share, issuer_share, platform_share_rate, expired_at, processed_at, created_at) VALUES
|
||
|
||
('aa000000-0001-4000-8000-000000000001',
|
||
'f0000000-0008-4000-8000-000000000008',
|
||
'd0000000-0001-4000-8000-000000000001',
|
||
100.00, 176000.00, 17600.00, 158400.00, 0.1000,
|
||
CURRENT_DATE - INTERVAL '30 days',
|
||
NOW() - INTERVAL '29 days',
|
||
NOW() - INTERVAL '29 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 22. REFUNDS (1 completed refund linked to dispute D02)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO refunds (id, user_id, coupon_id, order_id, refund_type, amount, fee_refunded, reason, status, requires_arbitration, processed_at, created_at) VALUES
|
||
|
||
('ab000000-0001-4000-8000-000000000001',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
'f0000000-0001-4000-8000-000000000001',
|
||
'80000000-0001-4000-8000-000000000001',
|
||
'secondary', 42.50, true,
|
||
'券活动描述与实际可用期限不符，经仲裁判定全额退款',
|
||
'completed', true,
|
||
NOW() - INTERVAL '12 days',
|
||
NOW() - INTERVAL '18 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 23. TRAVEL RULE RECORDS (1 record for large transfer)
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO travel_rule_records (id, sender_id, receiver_id, amount, sender_address, receiver_address, sender_identity_hash, receiver_identity_hash, is_external, trisa_message_id, tx_hash, status, created_at) VALUES
|
||
|
||
('ac000000-0001-4000-8000-000000000001',
|
||
'a0000000-0004-4000-8000-000000000004',
|
||
'a0000000-0005-4000-8000-000000000005',
|
||
5000.00,
|
||
'0x3c4d5e6f7a8b9c0d1e2f3a4b5c6d7e8f9a0b1c2d',
|
||
'0x4d5e6f7a8b9c0d1e2f3a4b5c6d7e8f9a0b1c2d3e',
|
||
'0x9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08',
|
||
'0xa3bf4f1b2b0b822cd15d6c15b0f00a089f86d081884c7d659a2feaa0c55ad015',
|
||
false, NULL,
|
||
'0x1234abcdef567890abcdef1234567890abcdef1234567890abcdef1234567890',
|
||
'completed',
|
||
NOW() - INTERVAL '30 days')
|
||
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- DONE
|
||
-- ---------------------------------------------------------------------------
|
||
COMMIT;
|
||
|
||
-- Post-commit verification (optional, useful for debugging)
|
||
DO $$
|
||
DECLARE
|
||
r RECORD;
|
||
BEGIN
|
||
FOR r IN
|
||
SELECT 'users' AS tbl, COUNT(*) AS cnt FROM users
|
||
UNION ALL SELECT 'kyc_submissions', COUNT(*) FROM kyc_submissions
|
||
UNION ALL SELECT 'wallets', COUNT(*) FROM wallets
|
||
UNION ALL SELECT 'issuers', COUNT(*) FROM issuers
|
||
UNION ALL SELECT 'stores', COUNT(*) FROM stores
|
||
UNION ALL SELECT 'coupons', COUNT(*) FROM coupons
|
||
UNION ALL SELECT 'coupon_rules', COUNT(*) FROM coupon_rules
|
||
UNION ALL SELECT 'orders', COUNT(*) FROM orders
|
||
UNION ALL SELECT 'trades', COUNT(*) FROM trades
|
||
UNION ALL SELECT 'settlements', COUNT(*) FROM settlements
|
||
UNION ALL SELECT 'transactions', COUNT(*) FROM transactions
|
||
UNION ALL SELECT 'messages', COUNT(*) FROM messages
|
||
UNION ALL SELECT 'aml_alerts', COUNT(*) FROM aml_alerts
|
||
UNION ALL SELECT 'disputes', COUNT(*) FROM disputes
|
||
UNION ALL SELECT 'audit_logs', COUNT(*) FROM audit_logs
|
||
UNION ALL SELECT 'address_mappings', COUNT(*) FROM address_mappings
|
||
UNION ALL SELECT 'credit_metrics', COUNT(*) FROM credit_metrics
|
||
UNION ALL SELECT 'ofac_screenings', COUNT(*) FROM ofac_screenings
|
||
UNION ALL SELECT 'journal_entries', COUNT(*) FROM journal_entries
|
||
UNION ALL SELECT 'sar_reports', COUNT(*) FROM sar_reports
|
||
UNION ALL SELECT 'breakage_records', COUNT(*) FROM breakage_records
|
||
UNION ALL SELECT 'refunds', COUNT(*) FROM refunds
|
||
UNION ALL SELECT 'travel_rule_records', COUNT(*) FROM travel_rule_records
|
||
LOOP
|
||
RAISE NOTICE 'Table %-25s: % rows', r.tbl, r.cnt;
|
||
END LOOP;
|
||
RAISE NOTICE '--- Seed data verification complete ---';
|
||
END $$;
|