1. 创建数据库（如果需要）：

-- 如果数据库不存在，创建数据库
CREATE DATABASE IF NOT EXISTS telemetry;

2. 创建扩展 (TimescaleDB):

-- 创建 TimescaleDB 扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;

3. 创建表：

-- SOE事件表
CREATE TABLE soe_events (
    time TIMESTAMPTZ NOT NULL,
    region_code VARCHAR(10),
    ocs_time TIMESTAMPTZ,
    soe_time TIMESTAMPTZ,
    st_name VARCHAR(100),
    feeder_gid VARCHAR(50),
    dev_name VARCHAR(100),
    dev_oid VARCHAR(50) NOT NULL,  -- 使用 dev_oid 作为键
    dev_gid VARCHAR(50),
    event_name VARCHAR(100),
    status INTEGER,
    PRIMARY KEY (time, dev_oid)
);

-- 遥测数据表
CREATE TABLE telemetry_data (
    time TIMESTAMPTZ NOT NULL,
    dev_oid VARCHAR(50) NOT NULL,  -- 使用 dev_oid 作为键
    i0 DECIMAL(10, 3),    -- 零序电流
    ia DECIMAL(10, 3),    -- A相电流
    ib DECIMAL(10, 3),    -- B相电流
    ic DECIMAL(10, 3),    -- C相电流
    ua DECIMAL(10, 3),    -- A相电压
    ub DECIMAL(10, 3),    -- B相电压
    uc DECIMAL(10, 3),    -- C相电压
    uab DECIMAL(10, 3),   -- AB线电压
    ubc DECIMAL(10, 3),   -- BC线电压
    uca DECIMAL(10, 3),   -- CA线电压
    p DECIMAL(10, 3),     -- 有功功率
    q DECIMAL(10, 3),     -- 无功功率
    cos_phi DECIMAL(5, 3), -- 功率因数
    PRIMARY KEY (time, dev_oid)
);

-- 遥信数据表（宽表形式）
CREATE TABLE telecontrol_data (
    time TIMESTAMPTZ NOT NULL,
    dev_oid VARCHAR(50) NOT NULL,
    -- 遥信字段 (Translated to English)
    switch_BR INTEGER NULL,              -- 开关BR: 0-分闸；1-合闸
    switch_fault_total INTEGER NULL,     -- 开关事故总: 0-正常；1-故障
    overcurrent_stage1_action INTEGER NULL, -- 过流I段动作: 0-未动作；1-动作（分闸）
    overcurrent_stage2_action INTEGER NULL, -- 过流II段动作: 0-未动作；1-动作（分闸）
    overcurrent_stage3_action INTEGER NULL, -- 过流III段动作: 0-未动作；1-动作（分闸）
    zero_sequence_action INTEGER NULL,     -- 零序动作: 0-未动作；1-动作（分闸）
    reclosing_action INTEGER NULL,         -- 重合闸动作: 0-未动作；1-动作（合闸）
    overload_alarm INTEGER NULL,           -- 过负荷告警: 0-正常；1-告警
    switch_not_energized INTEGER NULL,     -- 开关未储能: 0-正常；1-未储能
    lockout_total INTEGER NULL,            -- 闭锁总: 0-正常；1-闭锁
    local_remote INTEGER NULL,             -- 就地远方: 0-就地；1-远方
    overcurrent_mode INTEGER NULL,         -- 三段过流保护工作模式: 0-未开启；1-开启
    smart_distributed INTEGER NULL,       -- 智能分布式: 0-未开启；1-开启
    sectionalized_mode INTEGER NULL,       -- 分段工作模式: 0-未开启；1-开启
    tie_mode INTEGER NULL,                 -- 联络工作模式: 0-未开启；1-开启
    terminal_fault_alarm INTEGER NULL,     -- 终端故障告警: 0-正常；1-告警
    terminal_power_loss INTEGER NULL,      -- 终端电源交流失压: 0-正常；1-失压
    energized_close INTEGER NULL,          -- 得电合闸: 0-分闸；1-合闸
    deenergized_open INTEGER NULL,         -- 失电分闸: 0-分闸；1-合闸
    single_side_energized_close INTEGER NULL, -- 单侧失电合闸: 0-分闸；1-合闸
    y_time_zero_voltage_lockout INTEGER NULL, -- Y时限零压闭锁: 0-正常；1-闭锁
    y_time_undervoltage_lockout INTEGER NULL, -- Y时限失压闭锁: 0-正常；1-闭锁
    y_time_overcurrent_lockout INTEGER NULL, -- Y时限过流闭锁: 0-正常；1-闭锁
    y_time_zero_current_lockout INTEGER NULL, -- Y时限零流闭锁: 0-正常；1-闭锁
    x_time_lockout INTEGER NULL,           -- X时限闭锁: 0-正常；1-闭锁
    switch_power_side_voltage INTEGER NULL, -- 开关电源侧有压: 0-无压；1-有压
    switch_load_side_voltage INTEGER NULL,  -- 开关负荷侧有压: 0-无压；1-有压
    switch_door_detection INTEGER NULL,     -- 开关门禁检测: 0-柜门关闭；1-柜门未关闭
    PRIMARY KEY (time, dev_oid)
);

-- 故障分析结果表
CREATE TABLE fault_analysis (
    id SERIAL PRIMARY KEY,
    time TIMESTAMPTZ NOT NULL,
    dev_oid VARCHAR(50) NOT NULL,  -- 使用 dev_oid 作为键
    fault_type VARCHAR(50),       -- 故障类型
    fault_severity INTEGER,       -- 故障严重程度 1-轻微 2-中等 3-严重
    fault_cause VARCHAR(200),      -- 故障原因
    analysis_data JSONB,          -- 分析数据详情
    created_at TIMESTAMPTZ DEFAULT NOW()
);

4. 创建超表 (TimescaleDB):

-- 创建超表
SELECT create_hypertable('soe_events', 'time');
SELECT create_hypertable('telemetry_data', 'time');
SELECT create_hypertable('telecontrol_data', 'time');

5. 创建索引：

-- 创建索引
CREATE INDEX idx_soe_dev_oid ON soe_events (dev_oid, time DESC);
CREATE INDEX idx_telemetry_dev_oid ON telemetry_data (dev_oid, time DESC);
CREATE INDEX idx_telecontrol_dev_oid ON telecontrol_data (dev_oid, time DESC);
CREATE INDEX idx_fault_analysis_dev_oid ON fault_analysis (dev_oid, time DESC);

6. 创建视图：

-- 创建新的 fault_snapshot 视图
CREATE VIEW fault_snapshot AS
SELECT
    s.time AS soe_time,
    s.dev_oid,
    s.event_name,
    s.status AS soe_status,
    t.time AS telemetry_time,
    t.i0,
    t.ia,
    t.ib,
    t.ic,
    t.ua,
    t.ub,
    t.uc,
    t.uab,
    t.ubc,
    t.uca,
    t.p,
    t.q,
    t.cos_phi,
    tc.time AS telecontrol_time,
    tc.switch_BR,
    tc.switch_fault_total,
    tc.overcurrent_stage1_action,
    tc.overcurrent_stage2_action,
    tc.overcurrent_stage3_action,
    tc.zero_sequence_action,
    tc.reclosing_action,
    tc.overload_alarm,
    tc.switch_not_energized,
    tc.lockout_total,
    tc.local_remote,
    tc.overcurrent_mode,
    tc.smart_distributed,
    tc.sectionalized_mode,
    tc.tie_mode,
    tc.terminal_fault_alarm,
    tc.terminal_power_loss,
    tc.energized_close,
    tc.deenergized_open,
    tc.single_side_energized_close,
    tc.y_time_zero_voltage_lockout,
    tc.y_time_undervoltage_lockout,
    tc.y_time_overcurrent_lockout,
    tc.y_time_zero_current_lockout,
    tc.x_time_lockout,
    tc.switch_power_side_voltage,
    tc.switch_load_side_voltage,
    tc.switch_door_detection
FROM
    soe_events s
LEFT JOIN LATERAL (
    SELECT *
    FROM telemetry_data
    WHERE dev_oid = s.dev_oid
      AND time <= s.soe_time
    ORDER BY time DESC
    LIMIT 1
) t ON TRUE
LEFT JOIN LATERAL (
    SELECT *
    FROM telecontrol_data
    WHERE dev_oid = s.dev_oid
      AND time <= s.soe_time
    ORDER BY time DESC
    LIMIT 1
) tc ON TRUE;