water-management-system/db/postgresql/V3__monitor_list.sql

-- =============================================
-- 智慧水务管理系统 - 在线监测列表 DDL
-- 版本: V3
-- 功能: 在线监测设备 + 实时数据 + 多维筛选
-- =============================================

-- ==================== 在线监测设备 ====================

CREATE TABLE IF NOT EXISTS prod_monitor_device (
    id              BIGSERIAL PRIMARY KEY,
    device_code     VARCHAR(50)  NOT NULL UNIQUE,         -- 设备编号
    device_name     VARCHAR(200) NOT NULL,                 -- 设备名称
    device_type     VARCHAR(30)  NOT NULL,                 -- 设备类型: flow/pressure/level/quality
    area            VARCHAR(50)  NOT NULL,                 -- 所属区域
    location        VARCHAR(300),                          -- 安装位置描述
    lng             DECIMAL(12, 8),                        -- 经度
    lat             DECIMAL(12, 8),                        -- 纬度
    status          VARCHAR(20)  NOT NULL DEFAULT 'offline', -- 设备状态: online/offline/fault/abnormal
    last_report_time TIMESTAMP,                            -- 最后上报时间
    brand           VARCHAR(100),                          -- 品牌/型号
    install_time    TIMESTAMP,                             -- 安装时间
    remark          VARCHAR(500),                          -- 备注
    created_time    TIMESTAMP    DEFAULT NOW(),
    updated_time    TIMESTAMP    DEFAULT NOW()
);

COMMENT ON TABLE  prod_monitor_device IS '在线监测设备表';
COMMENT ON COLUMN prod_monitor_device.device_type IS '设备类型: flow(流量计)/pressure(压力计)/level(液位计)/quality(水质仪)';
COMMENT ON COLUMN prod_monitor_device.status IS '设备状态: online(在线)/offline(离线)/fault(故障)/abnormal(数据异常)';

CREATE INDEX IF NOT EXISTS idx_monitor_device_area       ON prod_monitor_device(area);
CREATE INDEX IF NOT EXISTS idx_monitor_device_type       ON prod_monitor_device(device_type);
CREATE INDEX IF NOT EXISTS idx_monitor_device_status     ON prod_monitor_device(status);
CREATE INDEX IF NOT EXISTS idx_monitor_device_report     ON prod_monitor_device(last_report_time DESC);
CREATE INDEX IF NOT EXISTS idx_monitor_device_code_name  ON prod_monitor_device(device_code, device_name);

-- ==================== 监测实时数据 ====================

CREATE TABLE IF NOT EXISTS prod_monitor_realtime_data (
    id               BIGSERIAL PRIMARY KEY,
    device_id        BIGINT         NOT NULL REFERENCES prod_monitor_device(id), -- 关联设备
    device_code      VARCHAR(50)    NOT NULL,                                     -- 设备编号（冗余）
    metric_key       VARCHAR(50)    NOT NULL,                                     -- 参数类型: flow/pressure/level/turbidity/ph/residual_chlorine/temperature
    metric_value     DECIMAL(14, 4) NOT NULL,                                     -- 实时值
    unit             VARCHAR(20),                                                 -- 单位
    threshold_high   DECIMAL(14, 4),                                              -- 阈值上限
    threshold_low    DECIMAL(14, 4),                                              -- 阈值下限
    is_abnormal      SMALLINT       DEFAULT 0,                                    -- 是否异常: 0正常 1异常
    collect_time     TIMESTAMP      NOT NULL,                                     -- 采集时间
    created_time     TIMESTAMP      DEFAULT NOW()
);

COMMENT ON TABLE  prod_monitor_realtime_data IS '监测实时数据表';
COMMENT ON COLUMN prod_monitor_realtime_data.metric_key IS '参数类型: flow(流量)/pressure(压力)/level(液位)/turbidity(浊度)/ph(pH值)/residual_chlorine(余氯)/temperature(温度)';

CREATE INDEX IF NOT EXISTS idx_realtime_device_id     ON prod_monitor_realtime_data(device_id);
CREATE INDEX IF NOT EXISTS idx_realtime_device_code   ON prod_monitor_realtime_data(device_code);
CREATE INDEX IF NOT EXISTS idx_realtime_metric_key    ON prod_monitor_realtime_data(metric_key);
CREATE INDEX IF NOT EXISTS idx_realtime_collect_time  ON prod_monitor_realtime_data(collect_time DESC);
CREATE INDEX IF NOT EXISTS idx_realtime_device_metric ON prod_monitor_realtime_data(device_id, metric_key, collect_time DESC);

-- ==================== 初始化数据（示例） ====================

INSERT INTO prod_monitor_device (device_code, device_name, device_type, area, location, lng, lat, status, last_report_time, brand)
VALUES
    ('MON-FLOW-001', '一号泵站出口流量计', 'flow',     '一体化水厂', '一号泵站出口',    82.07123456, 44.84567890, 'online',  NOW(), 'E+H Promag 50'),
    ('MON-FLOW-002', '二号泵站出口流量计', 'flow',     '一体化水厂', '二号泵站出口',    82.07234567, 44.84678901, 'online',  NOW(), 'E+H Promag 50'),
    ('MON-PRES-001', '管网压力监测点A',    'pressure', '管网一区',   '人民路DN300',    82.06890123, 44.84234567, 'online',  NOW(), 'WIKA S-20'),
    ('MON-PRES-002', '管网压力监测点B',    'pressure', '管网一区',   '建设路DN200',    82.06901234, 44.84345678, 'offline', NOW() - INTERVAL '2 hours', 'WIKA S-20'),
    ('MON-LEV-001',  '清水池液位计',       'level',    '一体化水厂', '清水池',         82.07156789, 44.84501234, 'online',  NOW(), 'VEGA VEGAPULS 64'),
    ('MON-LEV-002',  '沉淀池液位计',       'level',    '一体化水厂', '沉淀池',         82.07167890, 44.84512345, 'fault',   NOW() - INTERVAL '30 minutes', 'VEGA VEGAPULS 64'),
    ('MON-QUAL-001', '出厂水质监测仪',     'quality',  '一体化水厂', '出厂水口',       82.07178901, 44.84523456, 'online',  NOW(), 'HACH sc200'),
    ('MON-QUAL-002', '管网末梢水质仪',     'quality',  '管网二区',   '末梢检测点',     82.06543210, 44.83987654, 'abnormal',NOW(), 'HACH sc200'),
    ('MON-FLOW-003', '三号泵站流量计',     'flow',     '管网二区',   '三号泵站',       82.06654321, 44.84098765, 'online',  NOW(), 'E+H Promag 10'),
    ('MON-PRES-003', '高位水池压力计',     'pressure', '管网三区',   '高位水池出口',   82.07345678, 44.84789012, 'online',  NOW(), 'WIKA S-20')
ON CONFLICT (device_code) DO NOTHING;

INSERT INTO prod_monitor_realtime_data (device_id, device_code, metric_key, metric_value, unit, threshold_high, threshold_low, is_abnormal, collect_time)
SELECT d.id, d.device_code, m.metric_key, m.metric_value, m.unit, m.threshold_high, m.threshold_low, m.is_abnormal, NOW()
FROM prod_monitor_device d
CROSS JOIN (VALUES
    ('flow',              125.50,  'm³/h', 200.0,   10.0,   0),
    ('pressure',          0.35,    'MPa',  0.6,     0.15,   0),
    ('level',             3.80,    'm',    5.0,     0.5,    0),
    ('turbidity',         0.45,    'NTU',  1.0,     NULL,   0),
    ('ph',                7.20,    '',     8.5,     6.5,    0),
    ('residual_chlorine', 0.35,    'mg/L', 0.8,     0.05,   0)
) AS m(metric_key, metric_value, unit, threshold_high, threshold_low, is_abnormal)
WHERE d.status = 'online'
ON CONFLICT DO NOTHING;