V1__Create_notification_tables.sql
4.36 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
-- 创建 notification_templates 表 - 完全匹配实体类
CREATE TABLE notification_templates (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE,
type VARCHAR(50) NOT NULL,
channel VARCHAR(50) NOT NULL,
subject VARCHAR(500),
content TEXT NOT NULL,
variables TEXT,
is_active BOOLEAN DEFAULT true,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建 notification_preferences 表 - 完全匹配实体类
CREATE TABLE notification_preferences (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
channel VARCHAR(50) NOT NULL,
email_enabled BOOLEAN DEFAULT true,
sms_enabled BOOLEAN DEFAULT false,
push_enabled BOOLEAN DEFAULT true,
in_app_enabled BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE(user_id, channel)
);
-- 创建 notifications 表 - 完全匹配实体类
CREATE TABLE notifications (
id BIGSERIAL PRIMARY KEY,
notification_id VARCHAR(255) NOT NULL UNIQUE,
user_id BIGINT,
email VARCHAR(255),
phone VARCHAR(50),
device_token TEXT,
type VARCHAR(50) NOT NULL,
channel VARCHAR(50) NOT NULL,
template_name VARCHAR(255),
subject VARCHAR(500),
content TEXT,
status VARCHAR(20) DEFAULT 'PENDING',
priority VARCHAR(20) DEFAULT 'MEDIUM',
reference_type VARCHAR(50),
reference_id VARCHAR(255),
metadata TEXT,
failure_reason TEXT,
retry_count INTEGER DEFAULT 0,
max_retries INTEGER DEFAULT 3,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
sent_at TIMESTAMP,
delivered_at TIMESTAMP,
read_at TIMESTAMP
);
-- 创建索引
CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_status ON notifications(status);
CREATE INDEX idx_notifications_created_at ON notifications(created_at);
CREATE INDEX idx_notifications_type ON notifications(type);
CREATE INDEX idx_notifications_channel ON notifications(channel);
CREATE INDEX idx_notifications_reference ON notifications(reference_type, reference_id);
CREATE INDEX idx_notification_preferences_user_id ON notification_preferences(user_id);
CREATE INDEX idx_notification_preferences_channel ON notification_preferences(channel);
CREATE INDEX idx_notification_templates_type ON notification_templates(type);
CREATE INDEX idx_notification_templates_channel ON notification_templates(channel);
CREATE INDEX idx_notification_templates_active ON notification_templates(is_active);
-- 插入默认的通知模板数据
INSERT INTO notification_templates (name, type, channel, subject, content, variables, description, is_active) VALUES
('ORDER_CONFIRMATION_EMAIL', 'EMAIL', 'ORDER', '订单确认 - 订单号: {orderNumber}',
'<h1>尊敬的 {customerName}，</h1><p>您的订单已确认！</p><p><strong>订单号：</strong>{orderNumber}</p><p><strong>订单总额：</strong>{orderTotal}</p><p><strong>预计配送时间：</strong>{deliveryDate}</p>',
'["orderNumber", "customerName", "orderTotal", "deliveryDate"]', '订单确认邮件模板', true),
('ORDER_SHIPPED_EMAIL', 'EMAIL', 'SHIPPING', '订单已发货 - 订单号: {orderNumber}',
'<h1>尊敬的 {customerName}，</h1><p>您的订单已发货！</p><p><strong>订单号：</strong>{orderNumber}</p><p><strong>物流公司：</strong>{shippingCompany}</p><p><strong>物流单号：</strong>{trackingNumber}</p>',
'["orderNumber", "customerName", "shippingCompany", "trackingNumber"]', '订单发货通知邮件模板', true),
('PAYMENT_CONFIRMATION_SMS', 'SMS', 'PAYMENT', '',
'尊敬的{customerName}，您的订单{orderNumber}支付成功，金额{amount}元。感谢您的购买！',
'["customerName", "orderNumber", "amount"]', '支付成功短信模板', true),
('LOW_STOCK_ALERT', 'EMAIL', 'SYSTEM', '库存预警 - 产品: {productName}',
'<h1>库存预警</h1><p>产品 <strong>{productName}</strong> 库存不足。</p><p><strong>当前库存：</strong>{currentStock}</p><p><strong>最低库存阈值：</strong>{minStock}</p>',
'["productName", "currentStock", "minStock"]', '低库存预警模板', true),
('WELCOME_EMAIL', 'EMAIL', 'SYSTEM', '欢迎加入我们的平台！',
'<h1>欢迎 {customerName}！</h1><p>感谢您注册我们的平台。</p><p>开始您的购物之旅吧！</p>',
'["customerName"]', '新用户欢迎邮件模板', true);