#!/bin/bash

# 定义IP地址变量
SERVER_IP="192.168.1.10"

# MySQL 服务的登录信息
USERNAME="boshiyuan"
PASSWORD="123456"
DATABASE="viewwatch"

# 连接到 MySQL/MariaDB 数据库并执行操作
mysql -u"$USERNAME" -p"$PASSWORD" "$DATABASE" <<EOF

-- 检查并添加system_properties表中的ng_video_save_day列
SET @dbname = DATABASE();
SET @tablename = "system_properties";
SET @columnname = "ng_video_save_day";
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = @dbname
    AND table_name = @tablename
    AND column_name = @columnname) > 0,
    "SELECT 'Column Exists'",
    "ALTER TABLE system_properties ADD ng_video_save_day INT"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 检查并添加device表中的is_save_ng_video列
SET @tablename = "device";
SET @columnname = "is_save_ng_video";
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = @dbname
    AND table_name = @tablename
    AND column_name = @columnname) > 0,
    "SELECT 'Column Exists'",
    "ALTER TABLE device ADD is_save_ng_video TINYINT(1) DEFAULT 0"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 分析ng_statistics表
ANALYZE TABLE ng_statistics;

-- 优化ng_statistics表
OPTIMIZE TABLE ng_statistics;

-- 显示ng_statistics表的索引
SHOW INDEX FROM ng_statistics;

-- 检查并添加device表中的display_order列
SET @tablename = "device";
SET @columnname = "display_order";
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = @dbname
    AND table_name = @tablename
    AND column_name = @columnname) > 0,
    "SELECT 'Column Exists'",
    "ALTER TABLE device ADD display_order INT"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 如果列刚刚被添加，更新现有记录
UPDATE device SET display_order = id WHERE display_order IS NULL;

-- 创建触发器以自动设置新插入的device的display_order
DELIMITER //
DROP TRIGGER IF EXISTS before_insert_device //
CREATE TRIGGER before_insert_device
    BEFORE INSERT ON device
    FOR EACH ROW
BEGIN
    SET NEW.display_order = (SELECT IFNULL(MAX(display_order), 0) + 1 FROM device);
END;
//
DELIMITER ;

-- 创建 working_time_record 表
CREATE TABLE IF NOT EXISTS \`working_time_record\` (
    \`id\` int NOT NULL AUTO_INCREMENT,
    \`device_id\` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '设备名称',
    \`record_timepart\` datetime DEFAULT NULL COMMENT '记录的时间点，例如 9:00:00 标识 9-10点的时间段',
    \`working_time\` int DEFAULT NULL COMMENT '正常工作时长，分钟',
    \`stop_time\` int DEFAULT NULL COMMENT '停机时长',
    PRIMARY KEY (\`id\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='监视器工作时长';

-- 修改 ng_statistics 表，添加 period_time 列（如果不存在）
SET @tablename = "ng_statistics";
SET @columnname = "period_time";
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = @dbname
    AND table_name = @tablename
    AND column_name = @columnname) > 0,
    "SELECT 'Column Exists'",
    "ALTER TABLE ng_statistics ADD period_time INT DEFAULT NULL COMMENT '检测周期 单位：秒'"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 移除 ng_statistics 表中的 camera 列（如果存在）
SET @columnname = "camera";
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = @dbname
    AND table_name = @tablename
    AND column_name = @columnname) > 0,
    "ALTER TABLE ng_statistics DROP COLUMN camera",
    "SELECT 'Column does not exist'"
));
PREPARE alterIfExists FROM @preparedStatement;
EXECUTE alterIfExists;
DEALLOCATE PREPARE alterIfExists;

-- 新添加的服务器 IP 更新操作
UPDATE server SET wan_ip = '$SERVER_IP' WHERE id = 'ccs_001';
UPDATE server SET lan_ip = '$SERVER_IP' WHERE id = 'cdn001';
UPDATE server SET lan_ip = '$SERVER_IP' WHERE id = 'ccs_001';
UPDATE server SET wan_ip = '$SERVER_IP' WHERE id = 'cdn001';

-- 8.18 新增修改，添加 custom_shift_start 和 custom_shift_end 列
SET @tablename = "system_properties";
SET @columnname = "custom_shift_start";
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = @dbname
    AND table_name = @tablename
    AND column_name = @columnname) > 0,
    "SELECT 'Column Exists'",
    "ALTER TABLE system_properties ADD custom_shift_start TIME DEFAULT NULL"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = "custom_shift_end";
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = @dbname
    AND table_name = @tablename
    AND column_name = @columnname) > 0,
    "SELECT 'Column Exists'",
    "ALTER TABLE system_properties ADD custom_shift_end TIME DEFAULT NULL"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 检查并删除现有索引 idx_ng_statistics_date_state
SET @indexname = 'idx_ng_statistics_date_state';
SET @preparedStatement = (SELECT IF(
    EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = @dbname AND table_name = 'ng_statistics' AND index_name = @indexname),
    CONCAT('DROP INDEX ', @indexname, ' ON ng_statistics'),
    'SELECT "Index does not exist"'
));
PREPARE dropIfExists FROM @preparedStatement;
EXECUTE dropIfExists;
DEALLOCATE PREPARE dropIfExists;

-- 创建索引 idx_ng_statistics_date_state
CREATE INDEX idx_ng_statistics_date_state ON ng_statistics(ng_date, state);

-- 检查并删除现有索引 idx_ng_statistics_device_date_state
SET @indexname = 'idx_ng_statistics_device_date_state';
SET @preparedStatement = (SELECT IF(
    EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = @dbname AND table_name = 'ng_statistics' AND index_name = @indexname),
    CONCAT('DROP INDEX ', @indexname, ' ON ng_statistics'),
    'SELECT "Index does not exist"'
));
PREPARE dropIfExists FROM @preparedStatement;
EXECUTE dropIfExists;
DEALLOCATE PREPARE dropIfExists;

-- 创建索引 idx_ng_statistics_device_date_state
CREATE INDEX idx_ng_statistics_device_date_state ON ng_statistics(device_id, ng_date, state);

-- 新增内容：为 ng_statistics 表添加 uuid 列
SET @tablename = "ng_statistics";
SET @columnname = "uuid";
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = @dbname
    AND table_name = @tablename
    AND column_name = @columnname) > 0,
    "SELECT 'Column Exists'",
    "ALTER TABLE ng_statistics ADD uuid VARCHAR(64)"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 新增内容：为 photo 表添加 uuid 列
SET @tablename = "photo";
SET @columnname = "uuid";
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = @dbname
    AND table_name = @tablename
    AND column_name = @columnname) > 0,
    "SELECT 'Column Exists'",
    "ALTER TABLE photo ADD uuid VARCHAR(64)"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 修改 period_time 列的类型为 DOUBLE
ALTER TABLE ng_statistics MODIFY COLUMN period_time DOUBLE;

-- 创建 custom_shifts 表
CREATE TABLE IF NOT EXISTS custom_shifts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    shift_start TIME NOT NULL,
    shift_end TIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

EOF

echo "Database operations completed."

# 验证数据库是否正常工作
echo "Verifying database..."
mysql -u"$USERNAME" -p"$PASSWORD" -e "
USE $DATABASE;
SHOW DATABASES;
SHOW TABLES;
"

echo "Script execution completed."
