Eye/scripts/updates/3-0-1/migration.msql

-- Переименование таблиц в нижний регистр
RENAME TABLE Customers TO customers;
RENAME TABLE OU TO ou;
RENAME TABLE User_auth TO user_auth;
RENAME TABLE User_list TO user_list;
RENAME TABLE User_stats TO user_stats;
RENAME TABLE User_stats_full TO user_stats_full;
RENAME TABLE User_auth_alias TO user_auth_alias;
RENAME TABLE Wan_stats TO wan_stats;
RENAME TABLE Traffic_detail TO traffic_detail;
RENAME TABLE Unknown_mac TO unknown_mac;
RENAME TABLE Group_filters TO group_filters;
RENAME TABLE Group_list TO group_list;
RENAME TABLE Filter_list TO filter_list;
RENAME TABLE Queue_list TO queue_list;

-- Изменение типов и имён столбцов
ALTER TABLE wan_stats CHANGE COLUMN `in` bytes_in BIGINT NOT NULL DEFAULT 0;
ALTER TABLE wan_stats CHANGE COLUMN `out` bytes_out BIGINT NOT NULL DEFAULT 0;
ALTER TABLE group_filters CHANGE COLUMN `order` rule_order INTEGER NOT NULL DEFAULT 0;
ALTER TABLE user_auth CHANGE COLUMN `client-id` client_id VARCHAR(250);
ALTER TABLE customers CHANGE COLUMN `Login` login VARCHAR(20) DEFAULT NULL;

-- Переименовываем eof → end_life в user_auth
ALTER TABLE user_auth CHANGE COLUMN `eof` end_life TIMESTAMP NULL DEFAULT NULL;

ALTER TABLE dhcp_log CHANGE COLUMN `client-id` client_id VARCHAR(250);
ALTER TABLE dhcp_log CHANGE COLUMN `circuit-id` circuit_id VARCHAR(255);
ALTER TABLE dhcp_log CHANGE COLUMN `remote-id` remote_id VARCHAR(255);

-- Точки в именах
ALTER TABLE acl CHANGE COLUMN `description.english` description_english VARCHAR(250) NOT NULL;
ALTER TABLE acl CHANGE COLUMN `description.russian` description_russian VARCHAR(250) NOT NULL;

ALTER TABLE config_options CHANGE COLUMN `description.english` description_english TEXT;
ALTER TABLE config_options CHANGE COLUMN `description.russian` description_russian TEXT;

ALTER TABLE device_types CHANGE COLUMN `name.russian` name_russian VARCHAR(50);
ALTER TABLE device_types CHANGE COLUMN `name.english` name_english VARCHAR(50);

-- timestamp → ts
ALTER TABLE dhcp_log        CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE dhcp_queue      CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE dns_cache       CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE mac_history     CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE user_auth       CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE user_stats      CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE user_stats_full CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE worklog         CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE user_list       CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE traffic_detail  CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE unknown_mac     CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE user_auth_alias CHANGE COLUMN `timestamp` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- time/date → ts
DELETE FROM wan_stats WHERE `time` <= '1970-01-01 03:00:00' OR `time` = '0000-00-00 00:00:00';
ALTER TABLE wan_stats     CHANGE COLUMN `time` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE remote_syslog CHANGE COLUMN `date` ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- Добавляем столбец `comment` во все нужные таблицы (включая customers!)
ALTER TABLE acl              ADD COLUMN comment VARCHAR(250);
ALTER TABLE building         ADD COLUMN comment VARCHAR(250);
ALTER TABLE customers        ADD COLUMN comment VARCHAR(255);
ALTER TABLE devices          ADD COLUMN comment VARCHAR(255);
ALTER TABLE filter_instances ADD COLUMN comment VARCHAR(200);
ALTER TABLE filter_list      ADD COLUMN comment VARCHAR(250);
ALTER TABLE group_list       ADD COLUMN comment VARCHAR(250);
ALTER TABLE ou               ADD COLUMN comment VARCHAR(250);
ALTER TABLE subnets          ADD COLUMN comment VARCHAR(250);
ALTER TABLE vendors          ADD COLUMN comment VARCHAR(255);

-- Теперь безопасно переименовываем `comment` → `description`
ALTER TABLE acl              CHANGE COLUMN `comment` description VARCHAR(250);
ALTER TABLE building         CHANGE COLUMN `comment` description VARCHAR(250);
ALTER TABLE customers        CHANGE COLUMN `comment` description VARCHAR(255);
ALTER TABLE devices          CHANGE COLUMN `comment` description VARCHAR(255);
ALTER TABLE filter_instances CHANGE COLUMN `comment` description VARCHAR(200);
ALTER TABLE filter_list      CHANGE COLUMN `comment` description VARCHAR(250);
ALTER TABLE group_list       CHANGE COLUMN `comment` description VARCHAR(250);
ALTER TABLE ou               CHANGE COLUMN `comment` description VARCHAR(250);
ALTER TABLE subnets          CHANGE COLUMN `comment` description VARCHAR(250);
ALTER TABLE user_list        CHANGE COLUMN `fio` description VARCHAR(255);
ALTER TABLE vendors          CHANGE COLUMN `comment` description VARCHAR(255);

-- user_auth.comments → description
ALTER TABLE user_auth CHANGE COLUMN `comments` description VARCHAR(250);

-- Добавляем поле mac_found в user_auth
ALTER TABLE user_auth ADD COLUMN mac_found TIMESTAMP NULL DEFAULT NULL;
UPDATE user_auth SET mac_found = last_found WHERE last_found IS NOT NULL;

-- type → уточнённые имена
ALTER TABLE config_options           CHANGE COLUMN `type` option_type VARCHAR(100) NOT NULL;
ALTER TABLE dns_queue                CHANGE COLUMN `type` operation_type VARCHAR(10) NOT NULL DEFAULT 'add';
ALTER TABLE filter_list              CHANGE COLUMN `type` filter_type SMALLINT NOT NULL DEFAULT 0;
ALTER TABLE auth_rules               CHANGE COLUMN `type` rule_type INT(11) NOT NULL;
ALTER TABLE device_l3_interfaces     CHANGE COLUMN `type` interface_type INT(11) NOT NULL DEFAULT 0;

-- user_stats - add pkt counters
ALTER TABLE `user_stats` ADD COLUMN `pkt_in` int(11) NOT NULL DEFAULT 0;
ALTER TABLE `user_stats` ADD COLUMN `pkt_out` int(11) NOT NULL DEFAULT 0;
ALTER TABLE `user_stats` ADD COLUMN `step` int(11) NOT NULL DEFAULT 3600;

ALTER TABLE `user_stats_full` MODIFY COLUMN `pkt_in` int(11) NOT NULL DEFAULT 0;
ALTER TABLE `user_stats_full` MODIFY COLUMN `pkt_out` int(11) NOT NULL DEFAULT 0;

-- Set default value for ip to NULL
ALTER TABLE `dhcp_log`       MODIFY COLUMN `ip` varchar(15) NULL DEFAULT NULL;
ALTER TABLE `dhcp_queue`     MODIFY COLUMN `ip` varchar(15) NULL DEFAULT NULL;
ALTER TABLE `mac_history`    MODIFY COLUMN `ip` varchar(16) NULL DEFAULT NULL;
ALTER TABLE `remote_syslog`  MODIFY COLUMN `ip` varchar(15) NULL DEFAULT NULL;
ALTER TABLE `user_auth`      MODIFY COLUMN `ip` varchar(18) NULL DEFAULT NULL;

-- Add ACL for disable user
INSERT INTO acl (id, name, description_english, description_russian) VALUES (0, 'Disabled', 'User disabled', 'Пользователь отключен');

-- queue_list
ALTER TABLE queue_list CHANGE COLUMN `Download` `download` INT(11) NOT NULL DEFAULT 0;
ALTER TABLE queue_list CHANGE COLUMN `Upload` `upload` INT(11) NOT NULL DEFAULT 0;