107 lines
4.7 KiB
SQL
107 lines
4.7 KiB
SQL
-- =====================================================
|
|
-- V_smart_meter_sms_alipay.sql
|
|
-- 智能表平台 + 短信平台 + 支付宝生活缴费 DDL
|
|
-- =====================================================
|
|
|
|
-- 1. 智能水表表
|
|
CREATE TABLE IF NOT EXISTS rev_smart_meter (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
meter_no VARCHAR(64) NOT NULL,
|
|
customer_no VARCHAR(64),
|
|
signal_strength INTEGER DEFAULT 100,
|
|
battery_level INTEGER DEFAULT 100,
|
|
valve_status VARCHAR(16) DEFAULT 'OPEN',
|
|
last_report_time TIMESTAMP,
|
|
online_status VARCHAR(16) DEFAULT 'ONLINE',
|
|
current_reading DOUBLE PRECISION DEFAULT 0,
|
|
install_address VARCHAR(256),
|
|
area_code VARCHAR(32),
|
|
remark VARCHAR(512),
|
|
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_smart_meter_meter_no ON rev_smart_meter(meter_no);
|
|
CREATE INDEX IF NOT EXISTS idx_smart_meter_customer_no ON rev_smart_meter(customer_no);
|
|
CREATE INDEX IF NOT EXISTS idx_smart_meter_online_status ON rev_smart_meter(online_status);
|
|
CREATE INDEX IF NOT EXISTS idx_smart_meter_area_code ON rev_smart_meter(area_code);
|
|
|
|
-- 2. 短信模板表
|
|
CREATE TABLE IF NOT EXISTS rev_sms_template (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
template_name VARCHAR(128) NOT NULL,
|
|
template_type VARCHAR(32) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
variables VARCHAR(512),
|
|
enabled INTEGER DEFAULT 1,
|
|
remark VARCHAR(512),
|
|
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sms_template_type ON rev_sms_template(template_type);
|
|
|
|
-- 3. 短信发送记录表
|
|
CREATE TABLE IF NOT EXISTS rev_sms_record (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
phone VARCHAR(32) NOT NULL,
|
|
content TEXT,
|
|
template_id BIGINT,
|
|
send_status VARCHAR(16) DEFAULT 'PENDING',
|
|
send_time TIMESTAMP,
|
|
error_msg VARCHAR(512),
|
|
customer_no VARCHAR(64),
|
|
biz_type VARCHAR(32),
|
|
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sms_record_phone ON rev_sms_record(phone);
|
|
CREATE INDEX IF NOT EXISTS idx_sms_record_send_status ON rev_sms_record(send_status);
|
|
CREATE INDEX IF NOT EXISTS idx_sms_record_send_time ON rev_sms_record(send_time);
|
|
CREATE INDEX IF NOT EXISTS idx_sms_record_customer_no ON rev_sms_record(customer_no);
|
|
|
|
-- 4. 支付宝生活缴费订单表
|
|
CREATE TABLE IF NOT EXISTS rev_alipay_order (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
out_trade_no VARCHAR(128) NOT NULL,
|
|
alipay_trade_no VARCHAR(128),
|
|
customer_no VARCHAR(64),
|
|
bill_id BIGINT,
|
|
amount DECIMAL(12, 2),
|
|
status VARCHAR(16) DEFAULT 'CREATED',
|
|
notify_data TEXT,
|
|
bill_period VARCHAR(32),
|
|
pay_time TIMESTAMP,
|
|
refund_amount DECIMAL(12, 2),
|
|
refund_time TIMESTAMP,
|
|
remark VARCHAR(512),
|
|
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_alipay_order_out_trade_no ON rev_alipay_order(out_trade_no);
|
|
CREATE INDEX IF NOT EXISTS idx_alipay_order_customer_no ON rev_alipay_order(customer_no);
|
|
CREATE INDEX IF NOT EXISTS idx_alipay_order_status ON rev_alipay_order(status);
|
|
CREATE INDEX IF NOT EXISTS idx_alipay_order_pay_time ON rev_alipay_order(pay_time);
|
|
|
|
-- =====================================================
|
|
-- 默认短信模板数据
|
|
-- =====================================================
|
|
|
|
INSERT INTO rev_sms_template (template_name, template_type, content, variables, enabled, remark) VALUES
|
|
('账单通知', 'BILL_NOTICE',
|
|
'【XX水务】尊敬的${customerName}，您${billPeriod}的水费账单已出，应缴金额${amount}元，请及时缴费。',
|
|
'["customerName","billPeriod","amount"]', 1, '月度账单通知'),
|
|
|
|
('欠费提醒', 'ARREARS_WARNING',
|
|
'【XX水务】尊敬的${customerName}，您有${overdueAmount}元水费已逾期${overdueDays}天，请尽快缴清，逾期将影响正常用水。',
|
|
'["customerName","overdueAmount","overdueDays"]', 1, '欠费催缴提醒'),
|
|
|
|
('阀门控制通知', 'VALVE_CONTROL',
|
|
'【XX水务】尊敬的${customerName}，您编号为${meterNo}的水表阀门已${action}，如有疑问请联系客服。',
|
|
'["customerName","meterNo","action"]', 1, '远程阀门操作通知'),
|
|
|
|
('停水通知', 'GENERAL',
|
|
'【XX水务】${areaName}将于${startTime}至${endTime}进行${reason}，届时将暂停供水，请提前做好储水准备。',
|
|
'["areaName","startTime","endTime","reason"]', 1, '通用停水通知');
|