pipe-ner-server/pipe-network-service/sql/migration_early_warning_columns.sql

-- ============================================================================
-- 预警表（app_user.early_warning）列补齐 / 类型归一
--
-- 背景
--   排水「设备运行实时监测」页发布预警时插入失败，报：
--     ERROR: 关系 "early_warning" 的 "warning_name" 字段不存在
--   实体 DrainageWarning.equipmentName 原先映射到 warning_name，
--   而早期建表脚本（sql/early_warning_system.sql）里设备名称列是 device_name。
--   实体已改为 device_name；本脚本负责把历史库的列形态补齐/统一。
--
-- 覆盖三种情况
--   1) 有 device_name        → 跳过
--   2) 只有 warning_name     → 新增 device_name，并把数据搬过去
--   3) 两者都没有（异常库）  → 新增 device_name
--   并把 warning_level 由字符型统一为 integer（实体、查询、前端均为整数）。
--
-- 注意：不给 early_warning 加坐标列。坐标只在查询时从设备档案补齐后返回前端，
--   属于展示派生字段，落库会让预警历史随设备改动而失真。
--
-- 幂等：可重复执行，已到位的环境只会输出 NOTICE，不做任何改动。
-- ============================================================================

-- 首句独立 ROLLBACK：清掉上次失败执行残留的 aborted 事务，
-- 否则重跑只会得到「当前事务被终止，事务块结束之前的查询被忽略」
ROLLBACK;

BEGIN;

SET search_path TO app_user, public;

-- ----------------------------------------------------------------------------
-- 1. device_name：实体 DrainageWarning.equipmentName 的映射列
-- ----------------------------------------------------------------------------
DO $$
DECLARE
    has_device_name  boolean;
    has_warning_name boolean;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'app_user' AND table_name = 'early_warning' AND column_name = 'device_name'
    ) INTO has_device_name;

    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'app_user' AND table_name = 'early_warning' AND column_name = 'warning_name'
    ) INTO has_warning_name;

    IF has_device_name THEN
        RAISE NOTICE 'early_warning.device_name 已存在，跳过';
    ELSE
        ALTER TABLE app_user.early_warning ADD COLUMN device_name varchar(256);
        IF has_warning_name THEN
            EXECUTE 'UPDATE app_user.early_warning SET device_name = warning_name WHERE device_name IS NULL';
            RAISE NOTICE '已新增 device_name，并把 warning_name 的数据搬迁过来';
        ELSE
            RAISE NOTICE '已新增 device_name（库中原本没有设备名称列）';
        END IF;
    END IF;
END $$;

COMMENT ON COLUMN app_user.early_warning.device_name IS '关联设备名称';

-- ----------------------------------------------------------------------------
-- 2. warning_level：字符型统一为 integer
--    实体字段是 Integer、查询条件也是整数；character varying 列在 KES 下
--    既可能插入报类型不符，也可能让 warning_level = 1 的等值查询走不到索引。
--    非数字历史值（如误填的中文）统一归一为 1，避免转换失败中断脚本。
-- ----------------------------------------------------------------------------
DO $$
DECLARE
    v_type text;
BEGIN
    SELECT data_type INTO v_type
    FROM information_schema.columns
    WHERE table_schema = 'app_user' AND table_name = 'early_warning' AND column_name = 'warning_level';

    IF v_type IS NULL THEN
        RAISE NOTICE 'early_warning.warning_level 不存在，跳过';
    ELSIF v_type IN ('integer', 'smallint', 'bigint', 'numeric', 'decimal') THEN
        RAISE NOTICE 'early_warning.warning_level 已是数值类型（%），跳过', v_type;
    ELSE
        ALTER TABLE app_user.early_warning ALTER COLUMN warning_level DROP DEFAULT;
        ALTER TABLE app_user.early_warning
            ALTER COLUMN warning_level TYPE integer
            USING (CASE WHEN trim(warning_level) ~ '^[0-9]+$' THEN trim(warning_level)::integer ELSE 1 END);
        RAISE NOTICE 'early_warning.warning_level 已由 % 转为 integer（非数字历史值已归一为 1）', v_type;
    END IF;
END $$;

COMMENT ON COLUMN app_user.early_warning.warning_level IS '预警级别（1严重 2重要 3一般 4常态）';

-- ----------------------------------------------------------------------------
-- 3. psgw_warning_scope_user：指定人员发布时的接收人列表
--    排水预警（DrainageWarningServiceImpl.publishWarning）在 publish_scope=3 时写入该表，
--    早期环境可能没有建表，导致「指定人员」发布直接失败。
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_user.psgw_warning_scope_user (
    id           varchar(64) NOT NULL,
    warning_id   varchar(64),
    user_id      bigint,
    user_name    varchar(128),
    approved     integer DEFAULT 0,
    approve_time timestamp,
    create_time  timestamp,
    PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS idx_psgw_warning_scope_user_warning ON app_user.psgw_warning_scope_user (warning_id);

COMMENT ON TABLE app_user.psgw_warning_scope_user IS '排水预警接收人员';

-- ----------------------------------------------------------------------------
-- 4. 旧版实体遗留列：current_value / threshold / unit / warning_time
--    这些列曾经被旧版 DrainageWarning 写入，现已被实体标为不落库。
--    若历史环境把它们建成了 NOT NULL 且无默认值，新代码不写就会触发非空约束，
--    这里统一放开非空限制（列本身保留，历史数据不动）。
-- ----------------------------------------------------------------------------
DO $$
DECLARE
    col_name text;
    exists_col boolean;
BEGIN
    FOREACH col_name IN ARRAY ARRAY['current_value', 'threshold', 'unit', 'warning_time'] LOOP
        SELECT EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_schema = 'app_user' AND table_name = 'early_warning' AND column_name = col_name
        ) INTO exists_col;

        IF exists_col THEN
            EXECUTE format('ALTER TABLE app_user.early_warning ALTER COLUMN %I DROP NOT NULL', col_name);
            RAISE NOTICE 'early_warning.% 已改为可空（该列不再由程序写入）', col_name;
        END IF;
    END LOOP;
END $$;

COMMIT;

-- ----------------------------------------------------------------------------
-- 核验（可选，取消注释后单独执行）
-- ----------------------------------------------------------------------------
-- SELECT column_name, data_type, is_nullable
-- FROM information_schema.columns
-- WHERE table_schema = 'app_user' AND table_name = 'early_warning'
--   AND column_name IN ('device_name','warning_name','warning_level')
-- ORDER BY column_name;