SecMPS/doc/db_init.sql

-- ============================================
-- SecMPS v2.0 数据库建表脚本
-- 数据库: gljs_main
-- ============================================

USE gljs_main;

-- 1. 统一设备主表
CREATE TABLE IF NOT EXISTS Base_Device (
    DeviceId        CHAR(36) NOT NULL PRIMARY KEY,
    DeviceName      NVARCHAR(100) NOT NULL,
    AdapterCode     NVARCHAR(50) NOT NULL,
    SourceId        NVARCHAR(100) NOT NULL,
    DeviceCategory  INT NOT NULL DEFAULT 1,
    DeviceType      NVARCHAR(50),
    RegionId        INT NULL,
    IsParent        TINYINT NOT NULL DEFAULT 0,
    ParentDeviceId  CHAR(36) NULL,
    IsOnline        TINYINT NOT NULL DEFAULT 0,
    IpAddress       NVARCHAR(50),
    Port            INT,
    Location        NVARCHAR(200),
    Lat             DOUBLE,
    Lng             DOUBLE,
    MapModelId      NVARCHAR(100),
    MapModelScale   FLOAT DEFAULT 1.0,
    MapModelRotation NVARCHAR(100),
    ExtraData       TEXT,
    LocalOverrides  TEXT,
    SyncVersion     BIGINT DEFAULT 0,
    LastSyncTime    DATETIME,
    Enable          TINYINT DEFAULT 1,
    Remark          NVARCHAR(500),
    CreateID        INT,
    Creator         NVARCHAR(50),
    CreateDate      DATETIME DEFAULT CURRENT_TIMESTAMP,
    ModifyID        INT,
    Modifier        NVARCHAR(50),
    ModifyDate      DATETIME,
    UNIQUE INDEX IX_Base_Device_Adapter_Source (AdapterCode, SourceId),
    INDEX IX_Base_Device_RegionId (RegionId),
    INDEX IX_Base_Device_ParentId (ParentDeviceId)
);

-- 2. 视频设备扩展表
CREATE TABLE IF NOT EXISTS Device_Video_Ext (
    ExtId           CHAR(36) NOT NULL PRIMARY KEY,
    DeviceId        CHAR(36) NOT NULL,
    OwlDeviceId     NVARCHAR(64) NOT NULL,
    Protocol        INT DEFAULT 1,
    Manufacturer    NVARCHAR(100),
    Model           NVARCHAR(100),
    ChannelCount    INT DEFAULT 0,
    OwlStatus       NVARCHAR(500),
    CreateDate      DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX IX_VideoExt_Owl (OwlDeviceId),
    INDEX IX_VideoExt_Device (DeviceId)
);

-- 3. 视频通道表
CREATE TABLE IF NOT EXISTS Video_Channel (
    ChannelId       CHAR(36) NOT NULL PRIMARY KEY,
    OwlChannelId    NVARCHAR(64) NOT NULL,
    DeviceId        CHAR(36) NOT NULL,
    ChannelName     NVARCHAR(100) NOT NULL,
    ChannelNo       INT DEFAULT 0,
    OwlStreamApp    NVARCHAR(50),
    OwlStreamName   NVARCHAR(100),
    HasPtz          TINYINT DEFAULT 0,
    HasRecording    TINYINT DEFAULT 0,
    RecordMode      INT DEFAULT 0,
    IsOnline        TINYINT DEFAULT 0,
    SnapshotUrl     NVARCHAR(500),
    Location        NVARCHAR(200),
    Lat             DOUBLE,
    Lng             DOUBLE,
    Enable          TINYINT DEFAULT 1,
    CreateDate      DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX IX_Channel_Owl (OwlChannelId),
    INDEX IX_Channel_Device (DeviceId)
);

-- 4. 录像记录表
CREATE TABLE IF NOT EXISTS Video_Record (
    RecordId        CHAR(36) NOT NULL PRIMARY KEY,
    ChannelId       CHAR(36) NOT NULL,
    OwlRecordId     INT NOT NULL,
    App             NVARCHAR(50),
    Stream          NVARCHAR(100),
    StartedAt       DATETIME NOT NULL,
    EndedAt         DATETIME,
    Duration        DOUBLE DEFAULT 0,
    FilePath        NVARCHAR(500),
    FileSize        BIGINT DEFAULT 0,
    CreateDate      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX IX_Record_Channel (ChannelId),
    INDEX IX_Record_Time (StartedAt)
);

-- 5. IoT设备扩展表
CREATE TABLE IF NOT EXISTS Device_IoT_Ext (
    ExtId           CHAR(36) NOT NULL PRIMARY KEY,
    DeviceId        CHAR(36) NOT NULL,
    Mc4DeviceId     INT NOT NULL,
    ObjectType      INT,
    Tag             NVARCHAR(100),
    ParentId        INT,
    Mc4Option       NVARCHAR(500),
    CreateDate      DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX IX_IoTExt_Mc4 (Mc4DeviceId),
    INDEX IX_IoTExt_Device (DeviceId)
);

-- 6. 设备点位表
CREATE TABLE IF NOT EXISTS IoT_DevicePoint (
    PointId         CHAR(36) NOT NULL PRIMARY KEY,
    DeviceId        CHAR(36) NOT NULL,
    Mc4DeviceId     INT NOT NULL,
    PointIndex      INT NOT NULL,
    PointType       INT,
    PointTag        NVARCHAR(100),
    PointName       NVARCHAR(100) NOT NULL,
    PointDesc       NVARCHAR(200),
    Unit            NVARCHAR(50),
    IsControlPoint  TINYINT DEFAULT 0,
    Mc4Option       NVARCHAR(500),
    Enable          TINYINT DEFAULT 1,
    CreateDate      DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX IX_Point_Mc4 (Mc4DeviceId, PointIndex),
    INDEX IX_Point_Device (DeviceId)
);

-- 7. 设备数据归档表（仅存快照，实时不入库）
CREATE TABLE IF NOT EXISTS IoT_DeviceData (
    DataId          CHAR(36) NOT NULL PRIMARY KEY,
    DeviceId        CHAR(36) NOT NULL,
    PointId         CHAR(36) NOT NULL,
    PointValue      DOUBLE,
    UpdateTime      DATETIME NOT NULL,
    `Interval`      INT DEFAULT 0,
    ArchiveType     INT DEFAULT 1,
    CreateDate      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX IX_Data_Device (DeviceId),
    INDEX IX_Data_Time (CreateDate)
);

-- 8. 告警记录表
CREATE TABLE IF NOT EXISTS IoT_Alarm (
    AlarmId         CHAR(36) NOT NULL PRIMARY KEY,
    Mc4AlarmId      NVARCHAR(64) NOT NULL,
    DeviceId        CHAR(36),
    PointId         CHAR(36),
    AlarmType       INT DEFAULT 0,
    AlarmLevel      INT DEFAULT 1,
    AlarmDesc       NVARCHAR(500),
    AlarmValue      DOUBLE,
    StartTime       DATETIME NOT NULL,
    EndTime         DATETIME,
    ConfirmTime     DATETIME,
    ConfirmUser     NVARCHAR(50),
    State           INT DEFAULT 1,
    AdapterCode     NVARCHAR(50),
    CreateDate      DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX IX_Alarm_Mc4 (Mc4AlarmId),
    INDEX IX_Alarm_Device (DeviceId),
    INDEX IX_Alarm_Time (StartTime)
);