#!/bin/bash
# ============================================================
# 广元市站点数据初始化（逐条 -e 执行，规避达梦 disql stdin 多行问题）
# 用法：bash init-stations.sh
#       DBA_PWD=密码 bash init-stations.sh
# ============================================================
set -euo pipefail

DBA_PWD="${DBA_PWD:-5rUM7SuPYKfBth}"
DISQL="/opt/dmdbms/bin/disql"
SCHEMA="CONTINEW_ZHICHAO"
ok=0; fail=0

run() {
    local sql="$1"
    if docker exec dameng $DISQL SYSDBA/${DBA_PWD} -e "$sql" 2>&1 | grep -qE "affect rows|影响"; then
        echo "  OK: $(echo "$sql" | grep -oE "'[^']+'" | head -1)"
        ok=$((ok+1))
    else
        echo "  SKIP/FAIL: $(echo "$sql" | grep -oE "'[^']+'" | head -1)"
        fail=$((fail+1))
    fi
}

echo "=== 站点类型字典 ==="
run "INSERT INTO ${SCHEMA}.WIM_STATION_TYPE (ID,TYPE_NAME,TYPE_CODE,STATUS,CREATE_USER,CREATE_TIME) SELECT 31,'非现场检测点','31',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION_TYPE WHERE ID=31);"
run "INSERT INTO ${SCHEMA}.WIM_STATION_TYPE (ID,TYPE_NAME,TYPE_CODE,STATUS,CREATE_USER,CREATE_TIME) SELECT 21,'超限检测站','21',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION_TYPE WHERE ID=21);"
run "INSERT INTO ${SCHEMA}.WIM_STATION_TYPE (ID,TYPE_NAME,TYPE_CODE,STATUS,CREATE_USER,CREATE_TIME) SELECT 41,'高速入口称重站','41',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION_TYPE WHERE ID=41);"
run "INSERT INTO ${SCHEMA}.WIM_STATION_TYPE (ID,TYPE_NAME,TYPE_CODE,STATUS,CREATE_USER,CREATE_TIME) SELECT 42,'高速出口计重站','42',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION_TYPE WHERE ID=42);"
run "INSERT INTO ${SCHEMA}.WIM_STATION_TYPE (ID,TYPE_NAME,TYPE_CODE,STATUS,CREATE_USER,CREATE_TIME) SELECT 71,'重点货运源头站','71',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION_TYPE WHERE ID=71);"

echo ""
echo "=== 竹园镇更新 ==="
docker exec dameng $DISQL SYSDBA/${DBA_PWD} -e "UPDATE ${SCHEMA}.WIM_STATION SET STATION_NAME='竹园镇',LONGITUDE=106.2949,LATITUDE=32.2732,PILE_NUMBER='S208 K056+32',LANE_COUNT=2,STATION_TYPE_ID=31,ROAD_NAME='S208',UPDATE_TIME=SYSDATE WHERE STATION_CODE='5108221002';" 2>&1 | grep -qE "affect|影响" && echo "  OK: 竹园镇" || echo "  SKIP: 竹园镇"

echo ""
echo "=== 插入 16 个站点 ==="
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,PILE_NUMBER,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108121001,'沙河镇G108','5108121001',31,105.8505,32.5546,'K1856+700',2,'G108',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108121001');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,PILE_NUMBER,LANE_COUNT,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108021002,'大石镇','5108021002',31,105.954,32.3769,'K817+200',4,1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108021002');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,PILE_NUMBER,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108221001,'沙洲镇','5108221001',31,105.4694,32.7131,'G212 K705+270',2,'G212',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108221001');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,PILE_NUMBER,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108231002,'姚家镇','5108231002',31,105.3514,32.1628,'S208 K075+814',2,'S208',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108231002');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,PILE_NUMBER,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108231001,'涂山镇','5108231001',31,105.6486,31.653,'G347 K2373+127',2,'G347',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108231001');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,PILE_NUMBER,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108241001,'陵江镇','5108241001',31,105.9395,31.7908,'G212 K917+0',2,'G212',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108241001');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108211002,'三江镇','5108211002',31,106.604974,32.259485,2,'G542',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108211002');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108211001,'黄洋镇','5108211001',31,106.380816,32.242047,2,'G542',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108211001');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,LANE_COUNT,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108211004,'嘉川镇(旺苍方向)','5108211004',31,106.263102,32.248058,2,1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108211004');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,LANE_COUNT,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108211003,'嘉川镇(广元方向)','5108211003',31,106.309101,32.255587,2,1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108211003');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,LANE_COUNT,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108241002,'桥溪乡','5108241002',31,106.213931,32.101595,2,1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108241002');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,PILE_NUMBER,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108111004,'昭化镇','5108111004',31,105.688112,32.323037,'S205 K25+411',2,'S205',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108111004');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,PILE_NUMBER,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108111003,'红岩镇','5108111003',31,105.733197,32.21901,'S205 K45+711',2,'S205',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108111003');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,PILE_NUMBER,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108111002,'太公镇','5108111002',31,105.813799,32.07458,'G212 K870+518',2,'G212',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108111002');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,PILE_NUMBER,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108111001,'元坝镇','5108111001',31,105.996348,32.264135,'G542 K41+394',2,'G542',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108111001');"
run "INSERT INTO ${SCHEMA}.WIM_STATION (ID,STATION_NAME,STATION_CODE,STATION_TYPE_ID,LONGITUDE,LATITUDE,PILE_NUMBER,LANE_COUNT,ROAD_NAME,STATUS,CREATE_USER,CREATE_TIME) SELECT 5108021001,'宝轮镇','5108021001',31,105.56403,32.332594,'G108 K1910+200',4,'G108',1,1,SYSDATE FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE='5108021001');"

echo ""
echo "=== 验证 ==="
docker exec dameng $DISQL SYSDBA/${DBA_PWD} -e "SELECT STATION_CODE,STATION_NAME,LONGITUDE,LATITUDE FROM ${SCHEMA}.WIM_STATION WHERE STATION_CODE LIKE '5108%' ORDER BY STATION_CODE;" 2>&1 | grep -E "^[0-9]|STATION_CODE|---"
echo ""
echo "成功 $ok 条，跳过/失败 $fail 条"