water-management-system/sql/V_smart_meter_sms_alipay.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, '通用停水通知');