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

-- 泵站信息表公用化迁移（KingbaseES / PostgreSQL 兼容）
-- 目标：
--   1. app_user.water_pump_station 改名为 app_user.pump_station（公用泵站表）
--   2. 新增 station_type 类型字段：water_supply-供水泵站，drainage-排水泵站
--   3. 存量数据全部回填为供水泵站，并强制非空与取值域约束
-- 可重复执行；不删除任何业务数据。
-- 对应回滚脚本：rollback_pump_station_shared.sql

-- 1. 表改名（重复执行时旧表已不存在，IF EXISTS 自动跳过；数据、主键、外键保持不变）
ALTER TABLE IF EXISTS app_user.water_pump_station RENAME TO pump_station;

-- 2. 新增泵站类型字段
ALTER TABLE app_user.pump_station ADD COLUMN IF NOT EXISTS station_type varchar(16);

-- 3. 存量数据回填为供水泵站（历史数据均来自供水子系统）
UPDATE app_user.pump_station SET station_type = 'water_supply' WHERE station_type IS NULL;

-- 4. 默认值与非空约束
ALTER TABLE app_user.pump_station ALTER COLUMN station_type SET DEFAULT 'water_supply';
ALTER TABLE app_user.pump_station ALTER COLUMN station_type SET NOT NULL;
COMMENT ON COLUMN app_user.pump_station.station_type IS '泵站类型：water_supply-供水泵站，drainage-排水泵站';

-- 5. 取值域 CHECK 约束（幂等）
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'ck_pump_station_type') THEN
        ALTER TABLE app_user.pump_station
            ADD CONSTRAINT ck_pump_station_type CHECK (station_type IN ('water_supply', 'drainage'));
    END IF;
END $$;

-- 6. 坐标索引随表改名，并新增类型过滤索引（均幂等）
ALTER INDEX IF EXISTS app_user.idx_water_pump_station_lon_lat RENAME TO idx_pump_station_lon_lat;
CREATE INDEX IF NOT EXISTS idx_pump_station_type ON app_user.pump_station (station_type);

-- 7. 主键序列改名（若沿用表名前缀默认命名则存在，幂等处理）
DO $$
BEGIN
    IF EXISTS (SELECT 1
                 FROM pg_class c
                 JOIN pg_namespace n ON n.oid = c.relnamespace
                WHERE n.nspname = 'app_user'
                  AND c.relname = 'water_pump_station_id_seq') THEN
        ALTER SEQUENCE app_user.water_pump_station_id_seq RENAME TO pump_station_id_seq;
    END IF;
END $$;

-- 8. 表注释更新为公用泵站表
COMMENT ON TABLE app_user.pump_station IS '公用泵站信息表，按 station_type 区分供水泵站（water_supply）与排水泵站（drainage）';

-- 执行后校验（手动执行确认）：
-- SELECT station_type, COUNT(*) FROM app_user.pump_station GROUP BY station_type;
-- SELECT column_name, data_type, is_nullable, column_default
--   FROM information_schema.columns WHERE table_schema='app_user' AND table_name='pump_station' AND column_name='station_type';