Administrator / spring-boot-microservices-onlineshooping-fullstack · Files

-- V1__Complete_notification_schema.sql

-- =============================================
-- 通知偏好设置表
-- =============================================
CREATE TABLE IF NOT EXISTS notification_preferences (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    channel VARCHAR(50) NOT NULL DEFAULT 'ORDER',  -- ORDER, PAYMENT, SHIPPING, PROMOTION, SYSTEM
    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)  -- 每个用户每个渠道只能有一条记录
);

-- =============================================
-- 通知记录表
-- =============================================
CREATE TABLE IF NOT EXISTS notifications (
    id BIGSERIAL PRIMARY KEY,
    notification_id VARCHAR(100) NOT NULL UNIQUE,
    user_id BIGINT,
    email VARCHAR(255),
    phone VARCHAR(50),
    device_token TEXT,
    type VARCHAR(20) NOT NULL,  -- EMAIL, SMS, PUSH, IN_APP
    channel VARCHAR(20) NOT NULL,  -- ORDER, PAYMENT, SHIPPING, PROMOTION, SYSTEM
    template_name VARCHAR(100),
    subject VARCHAR(255),
    content TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',  -- PENDING, SENT, FAILED, DELIVERED, READ
    priority VARCHAR(20) DEFAULT 'MEDIUM',  -- LOW, MEDIUM, HIGH, URGENT
    reference_type VARCHAR(50),  -- ORDER, PAYMENT, USER
    reference_id VARCHAR(100),
    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 TABLE IF NOT EXISTS notification_templates (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    type VARCHAR(50) NOT NULL,
    channel VARCHAR(20) NOT NULL,
    subject VARCHAR(255),
    content TEXT NOT NULL,
    variables TEXT,  -- JSON array of variable names
    is_active BOOLEAN DEFAULT true,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- 通知系统设置表
-- =============================================
CREATE TABLE IF NOT EXISTS notification_settings (
    id BIGSERIAL PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    description TEXT,
    is_enabled BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 0
);

-- =============================================
-- 创建索引
-- =============================================

-- notifications 表索引
CREATE INDEX IF NOT EXISTS idx_notifications_user_id ON notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_notifications_status ON notifications(status);
CREATE INDEX IF NOT EXISTS idx_notifications_created_at ON notifications(created_at);
CREATE INDEX IF NOT EXISTS idx_notifications_type ON notifications(type);
CREATE INDEX IF NOT EXISTS idx_notifications_channel ON notifications(channel);
CREATE INDEX IF NOT EXISTS idx_notifications_notification_id ON notifications(notification_id);
CREATE INDEX IF NOT EXISTS idx_notifications_reference ON notifications(reference_type, reference_id);

-- notification_preferences 表索引
CREATE INDEX IF NOT EXISTS idx_notification_preferences_user_id ON notification_preferences(user_id);
CREATE INDEX IF NOT EXISTS idx_notification_preferences_channel ON notification_preferences(channel);
CREATE INDEX IF NOT EXISTS idx_notification_preferences_user_channel ON notification_preferences(user_id, channel);

-- notification_templates 表索引
CREATE INDEX IF NOT EXISTS idx_notification_templates_type ON notification_templates(type);
CREATE INDEX IF NOT EXISTS idx_notification_templates_channel ON notification_templates(channel);
CREATE INDEX IF NOT EXISTS idx_notification_templates_active ON notification_templates(is_active);

-- =============================================
-- 插入默认通知模板数据
-- =============================================
INSERT INTO notification_templates (name, type, channel, subject, content, variables, is_active, description) VALUES
-- 订单相关邮件模板
('ORDER_CONFIRMATION_EMAIL', 'EMAIL', 'ORDER', '订单确认 - 订单号: {orderNumber}', 
 '<html><body><h1>订单确认</h1><p>尊敬的 {customerName}，</p><p>您的订单 #{orderNumber} 已确认。</p><p>订单总金额: {orderAmount}</p><p>预计发货时间: {estimatedDelivery}</p><p>感谢您的购买！</p></body></html>',
 '["orderNumber", "customerName", "orderAmount", "estimatedDelivery"]', true, '订单确认邮件模板'),

('ORDER_SHIPPED_EMAIL', 'EMAIL', 'ORDER', '订单已发货 - 订单号: {orderNumber}',
 '<html><body><h1>订单已发货</h1><p>尊敬的 {customerName}，</p><p>您的订单 #{orderNumber} 已发货。</p><p>物流公司: {shippingCarrier}</p><p>运单号: {trackingNumber}</p><p>预计到达: {estimatedArrival}</p></body></html>',
 '["orderNumber", "customerName", "shippingCarrier", "trackingNumber", "estimatedArrival"]', true, '订单发货通知邮件模板'),

('ORDER_DELIVERED_EMAIL', 'EMAIL', 'ORDER', '订单已送达 - 订单号: {orderNumber}',
 '<html><body><h1>订单已送达</h1><p>尊敬的 {customerName}，</p><p>您的订单 #{orderNumber} 已成功送达。</p><p>希望您对商品满意，期待再次为您服务！</p></body></html>',
 '["orderNumber", "customerName"]', true, '订单送达通知邮件模板'),

-- 用户相关邮件模板
('WELCOME_EMAIL', 'EMAIL', 'SYSTEM', '欢迎加入我们的平台！',
 '<html><body><h1>欢迎加入！</h1><p>尊敬的 {customerName}，</p><p>感谢您注册我们的平台。</p><p>我们很高兴为您提供服务。</p></body></html>',
 '["customerName"]', true, '新用户欢迎邮件模板'),

('PASSWORD_RESET_EMAIL', 'EMAIL', 'SYSTEM', '密码重置请求',
 '<html><body><h1>密码重置</h1><p>我们收到了您的密码重置请求。</p><p>重置链接: {resetLink}</p><p>如果您没有请求重置密码，请忽略此邮件。</p></body></html>',
 '["resetLink"]', true, '密码重置邮件模板'),

-- SMS 模板
('ORDER_CONFIRMATION_SMS', 'SMS', 'ORDER', NULL,
 '尊敬的{customerName}，您的订单#{orderNumber}已确认，金额{orderAmount}。感谢您的购买！',
 '["customerName", "orderNumber", "orderAmount"]', true, '订单确认短信模板'),

('ORDER_SHIPPED_SMS', 'SMS', 'ORDER', NULL,
 '您的订单#{orderNumber}已发货，运单号{trackingNumber}，{shippingCarrier}承运。',
 '["orderNumber", "trackingNumber", "shippingCarrier"]', true, '订单发货短信模板'),

-- 推送通知模板
('PROMOTION_PUSH', 'PUSH', 'PROMOTION', '特别优惠',
 '限时特惠！{discountPercentage}折扣等您来拿，仅限今天！',
 '["discountPercentage"]', true, '促销活动推送模板'),

('ABANDONED_CART_PUSH', 'PUSH', 'PROMOTION', '购物车提醒',
 '您的购物车还有商品未完成购买，立即完成订单享受优惠！',
 '[]', true, '购物车提醒推送模板')
ON CONFLICT (name) DO NOTHING;

-- =============================================
-- 插入默认通知偏好设置
-- =============================================
INSERT INTO notification_preferences (user_id, channel, email_enabled, sms_enabled, push_enabled, in_app_enabled) VALUES
(1, 'ORDER', true, false, true, true),
(1, 'PAYMENT', true, true, true, true),
(1, 'SHIPPING', true, true, true, true),
(1, 'PROMOTION', false, false, true, true),
(1, 'SYSTEM', true, false, true, true)
ON CONFLICT (user_id, channel) DO NOTHING;

-- =============================================
-- 插入系统设置数据
-- =============================================
INSERT INTO notification_settings (setting_key, setting_value, description, is_enabled) VALUES
('EMAIL_PROVIDER', 'SENDGRID', '邮件服务提供商', true),
('SMS_PROVIDER', 'TWILIO', '短信服务提供商', true),
('PUSH_PROVIDER', 'FIREBASE', '推送通知服务提供商', true),
('MAX_RETRY_ATTEMPTS', '3', '最大重试次数', true),
('RETRY_DELAY_SECONDS', '60', '重试延迟时间（秒）', true),
('BATCH_PROCESSING_SIZE', '100', '批量处理大小', true),
('RATE_LIMIT_PER_MINUTE', '60', '每分钟速率限制', true),
('DEFAULT_TIMEZONE', 'UTC', '默认时区', true)
ON CONFLICT (setting_key) DO NOTHING;

-- =============================================
-- 添加表注释
-- =============================================
COMMENT ON TABLE notification_preferences IS '用户通知偏好设置';
COMMENT ON TABLE notifications IS '通知记录';
COMMENT ON TABLE notification_templates IS '通知模板';
COMMENT ON TABLE notification_settings IS '通知系统设置';

COMMENT ON COLUMN notification_preferences.channel IS '通知渠道类型：ORDER, PAYMENT, SHIPPING, PROMOTION, SYSTEM';
COMMENT ON COLUMN notification_preferences.email_enabled IS '邮件通知是否启用';
COMMENT ON COLUMN notification_preferences.sms_enabled IS '短信通知是否启用';
COMMENT ON COLUMN notification_preferences.push_enabled IS '推送通知是否启用';
COMMENT ON COLUMN notification_preferences.in_app_enabled IS '应用内通知是否启用';

COMMENT ON COLUMN notifications.notification_id IS '通知唯一标识';
COMMENT ON COLUMN notifications.type IS '通知类型：EMAIL, SMS, PUSH, IN_APP';
COMMENT ON COLUMN notifications.channel IS '通知渠道：ORDER, PAYMENT, SHIPPING, PROMOTION, SYSTEM';
COMMENT ON COLUMN notifications.status IS '状态：PENDING, SENT, FAILED, DELIVERED, READ';
COMMENT ON COLUMN notifications.priority IS '优先级：LOW, MEDIUM, HIGH, URGENT';
COMMENT ON COLUMN notifications.reference_type IS '关联类型：ORDER, PAYMENT, USER';
COMMENT ON COLUMN notifications.content IS '通知内容';

COMMENT ON COLUMN notification_templates.type IS '通知类型：EMAIL, SMS, PUSH';
COMMENT ON COLUMN notification_templates.channel IS '通知渠道：ORDER, PAYMENT, SHIPPING, PROMOTION, SYSTEM';
COMMENT ON COLUMN notification_templates.variables IS '模板变量列表（JSON数组）';
COMMENT ON COLUMN notification_templates.description IS '模板描述';