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

-- =============================================
-- 智慧水务管理系统 - 视频监控集成 + AI人员闯入检测 DDL
-- 版本: V3
-- =============================================

-- ==================== 视频监控摄像头 ====================

CREATE TABLE IF NOT EXISTS prod_video_camera (
    id              BIGSERIAL PRIMARY KEY,
    camera_id       VARCHAR(50) NOT NULL UNIQUE,
    name            VARCHAR(100) NOT NULL,
    area            VARCHAR(50),
    stream_url_rtsp VARCHAR(500),
    stream_url_hls  VARCHAR(500),
    stream_url_flv  VARCHAR(500),
    status          INTEGER DEFAULT 0,
    manufacturer    VARCHAR(50),
    model           VARCHAR(50),
    lng             DOUBLE PRECISION,
    lat             DOUBLE PRECISION,
    install_location VARCHAR(200),
    install_date    DATE,
    last_online_time TIMESTAMP,
    ai_enabled      INTEGER DEFAULT 0,
    remark          VARCHAR(500),
    created_time    TIMESTAMP DEFAULT NOW(),
    updated_time    TIMESTAMP DEFAULT NOW(),
    deleted         INTEGER DEFAULT 0
);

COMMENT ON TABLE prod_video_camera IS '视频监控摄像头表';
COMMENT ON COLUMN prod_video_camera.camera_id IS '摄像头唯一编号';
COMMENT ON COLUMN prod_video_camera.status IS '状态: 0=离线, 1=在线, 2=故障';
COMMENT ON COLUMN prod_video_camera.ai_enabled IS '是否启用AI检测: 0=未启用, 1=已启用';
COMMENT ON COLUMN prod_video_camera.stream_url_rtsp IS 'RTSP视频流地址';
COMMENT ON COLUMN prod_video_camera.stream_url_hls IS 'HLS视频流地址';
COMMENT ON COLUMN prod_video_camera.stream_url_flv IS 'FLV视频流地址';

CREATE INDEX IF NOT EXISTS idx_video_camera_area ON prod_video_camera(area);
CREATE INDEX IF NOT EXISTS idx_video_camera_status ON prod_video_camera(status);

-- ==================== AI闯入检测事件 ====================

CREATE TABLE IF NOT EXISTS prod_intrusion_event (
    id              BIGSERIAL PRIMARY KEY,
    camera_id       BIGINT NOT NULL,
    camera_name     VARCHAR(100),
    area            VARCHAR(50),
    event_type      VARCHAR(30) NOT NULL,
    confidence      NUMERIC(6, 4),
    snapshot_url    VARCHAR(500),
    video_clip_url  VARCHAR(500),
    alert_level     VARCHAR(20),
    alert_status    INTEGER DEFAULT 0,
    detected_at     TIMESTAMP NOT NULL,
    handle_result   TEXT,
    handled_by      BIGINT,
    handler_name    VARCHAR(50),
    handled_time    TIMESTAMP,
    alert_record_id BIGINT,
    remark          VARCHAR(500),
    created_time    TIMESTAMP DEFAULT NOW(),
    updated_time    TIMESTAMP DEFAULT NOW(),
    deleted         INTEGER DEFAULT 0
);

COMMENT ON TABLE prod_intrusion_event IS 'AI人员闯入检测事件表';
COMMENT ON COLUMN prod_intrusion_event.event_type IS '事件类型: person_intrusion=人员闯入, person_loitering=人员徘徊, zone_breach=区域越界';
COMMENT ON COLUMN prod_intrusion_event.confidence IS 'AI识别置信度(0~1)';
COMMENT ON COLUMN prod_intrusion_event.alert_level IS '报警等级: info, warning, critical';
COMMENT ON COLUMN prod_intrusion_event.alert_status IS '报警状态: 0=待处理, 1=已确认, 2=已处理, 3=已忽略';

CREATE INDEX IF NOT EXISTS idx_intrusion_camera ON prod_intrusion_event(camera_id);
CREATE INDEX IF NOT EXISTS idx_intrusion_area ON prod_intrusion_event(area);
CREATE INDEX IF NOT EXISTS idx_intrusion_detected_at ON prod_intrusion_event(detected_at DESC);
CREATE INDEX IF NOT EXISTS idx_intrusion_alert_status ON prod_intrusion_event(alert_status);

-- ==================== 视频录像记录 ====================

CREATE TABLE IF NOT EXISTS prod_video_recording (
    id              BIGSERIAL PRIMARY KEY,
    camera_id       BIGINT NOT NULL,
    camera_name     VARCHAR(100),
    area            VARCHAR(50),
    start_time      TIMESTAMP NOT NULL,
    end_time        TIMESTAMP,
    duration_sec    INTEGER,
    file_size_mb    NUMERIC(10, 2),
    storage_path    VARCHAR(500),
    playback_url    VARCHAR(500),
    record_type     VARCHAR(20) NOT NULL,
    event_id        BIGINT,
    remark          VARCHAR(500),
    created_time    TIMESTAMP DEFAULT NOW(),
    updated_time    TIMESTAMP DEFAULT NOW(),
    deleted         INTEGER DEFAULT 0
);

COMMENT ON TABLE prod_video_recording IS '视频录像记录表';
COMMENT ON COLUMN prod_video_recording.record_type IS '录像类型: scheduled=计划录像, event_triggered=事件触发, manual=手动录像';
COMMENT ON COLUMN prod_video_recording.event_id IS '关联闯入事件ID(事件触发时有值)';

CREATE INDEX IF NOT EXISTS idx_recording_camera ON prod_video_recording(camera_id);
CREATE INDEX IF NOT EXISTS idx_recording_start_time ON prod_video_recording(start_time DESC);
CREATE INDEX IF NOT EXISTS idx_recording_record_type ON prod_video_recording(record_type);
CREATE INDEX IF NOT EXISTS idx_recording_event ON prod_video_recording(event_id);

-- ==================== 初始化测试数据 ====================

INSERT INTO prod_video_camera (camera_id, name, area, stream_url_rtsp, stream_url_hls, stream_url_flv,
    status, manufacturer, model, lng, lat, install_location, install_date, ai_enabled, last_online_time)
VALUES
    ('CAM-001', '一体化水厂-沉淀池', '一体化水厂', 'rtsp://192.168.1.100/stream1', 'http://192.168.1.100/hls/stream1.m3u8', 'http://192.168.1.100/flv/stream1.flv',
     1, '海康威视', 'DS-2CD2T26FWDA3-IS', 87.5712, 43.7928, '一体化水厂沉淀池北侧', '2024-03-15', 1, NOW()),
    ('CAM-002', '一体化水厂-清水池', '一体化水厂', 'rtsp://192.168.1.101/stream1', 'http://192.168.1.101/hls/stream1.m3u8', 'http://192.168.1.101/flv/stream1.flv',
     1, '海康威视', 'DS-2CD2T26FWDA3-IS', 87.5715, 43.7930, '一体化水厂清水池入口', '2024-03-15', 1, NOW()),
    ('CAM-003', '查村调压站-入口', '八家户片区', 'rtsp://192.168.1.102/stream1', 'http://192.168.1.102/hls/stream1.m3u8', 'http://192.168.1.102/flv/stream1.flv',
     1, '大华', 'DH-IPC-HFW5442T-ASE', 87.5680, 43.7890, '查村调压站大门', '2024-04-10', 1, NOW()),
    ('CAM-004', '精芒片区-管网节点1', '精芒片区', 'rtsp://192.168.1.103/stream1', 'http://192.168.1.103/hls/stream1.m3u8', 'http://192.168.1.103/flv/stream1.flv',
     0, '大华', 'DH-IPC-HFW5442T-ASE', 87.5650, 43.7860, '精芒片区管网节点井', '2024-05-20', 0, '2025-06-10 08:30:00'),
    ('CAM-005', '八家户泵站-机房', '八家户片区', 'rtsp://192.168.1.104/stream1', 'http://192.168.1.104/hls/stream1.m3u8', 'http://192.168.1.104/flv/stream1.flv',
     2, '宇视', 'IPC3612SB-ADZK-I0', 87.5670, 43.7880, '八家户泵站机房入口', '2024-06-01', 1, '2025-06-01 12:00:00');