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

-- Переименование таблиц в нижний регистр (snake_case)

ALTER TABLE "Customers" RENAME TO customers;
ALTER TABLE "OU" RENAME TO ou;
ALTER TABLE "User_auth" RENAME TO user_auth;
ALTER TABLE "User_list" RENAME TO user_list;
ALTER TABLE "User_stats" RENAME TO user_stats;
ALTER TABLE "User_stats_full" RENAME TO user_stats_full;
ALTER TABLE "User_auth_alias" RENAME TO user_auth_alias;
ALTER TABLE "Wan_stats" RENAME TO wan_stats;
ALTER TABLE "Traffic_detail" RENAME TO traffic_detail;
ALTER TABLE "Unknown_mac" RENAME TO unknown_mac;
ALTER TABLE "Group_filters" RENAME TO group_filters;
ALTER TABLE "Group_list" RENAME TO group_list;
ALTER TABLE "Filter_list" RENAME TO filter_list;
ALTER TABLE "Queue_list" RENAME TO queue_list;

-- wan_stats: in/out → bytes_in/bytes_out
ALTER TABLE wan_stats RENAME COLUMN "in" TO bytes_in;
ALTER TABLE wan_stats RENAME COLUMN "out" TO bytes_out;

-- group_filters: order → rule_order
ALTER TABLE group_filters RENAME COLUMN "order" TO rule_order;

-- user_auth: client-id → client_id
ALTER TABLE user_auth RENAME COLUMN "client-id" TO client_id;

-- customers: Login to lower case
ALTER TABLE customers RENAME COLUMN Login TO login;

-- Переименовываем eof → end_life в user_auth
ALTER TABLE user_auth RENAME COLUMN eof TO end_life;

-- dhcp_log: дефисы → подчёркивания
ALTER TABLE dhcp_log RENAME COLUMN "client-id" TO client_id;
ALTER TABLE dhcp_log RENAME COLUMN "circuit-id" TO circuit_id;
ALTER TABLE dhcp_log RENAME COLUMN "remote-id" TO remote_id;

-- Точки в именах → подчёркивания
ALTER TABLE acl RENAME COLUMN "description.english" TO description_english;
ALTER TABLE acl RENAME COLUMN "description.russian" TO description_russian;
ALTER TABLE config_options RENAME COLUMN "description.english" TO description_english;
ALTER TABLE config_options RENAME COLUMN "description.russian" TO description_russian;
ALTER TABLE device_types RENAME COLUMN "name.russian" TO name_russian;
ALTER TABLE device_types RENAME COLUMN "name.english" TO name_english;

-- timestamp → ts
ALTER TABLE dhcp_log RENAME COLUMN timestamp TO ts;
ALTER TABLE dhcp_queue RENAME COLUMN timestamp TO ts;
ALTER TABLE dns_cache RENAME COLUMN timestamp TO ts;
ALTER TABLE mac_history RENAME COLUMN timestamp TO ts;
ALTER TABLE user_auth RENAME COLUMN timestamp TO ts;
ALTER TABLE user_stats RENAME COLUMN timestamp TO ts;
ALTER TABLE user_stats_full RENAME COLUMN timestamp TO ts;
ALTER TABLE worklog RENAME COLUMN timestamp TO ts;
ALTER TABLE user_list RENAME COLUMN timestamp TO ts;
ALTER TABLE traffic_detail RENAME COLUMN timestamp TO ts;
ALTER TABLE unknown_mac RENAME COLUMN timestamp TO ts;
ALTER TABLE user_auth_alias RENAME COLUMN timestamp TO ts;

-- time/date → ts
ALTER TABLE wan_stats RENAME COLUMN time TO ts;
ALTER TABLE remote_syslog RENAME COLUMN date TO ts;

-- comment → description (во всех таблицах)
ALTER TABLE acl RENAME COLUMN comment TO description;
ALTER TABLE building RENAME COLUMN comment TO description;
ALTER TABLE devices RENAME COLUMN comment TO description;
ALTER TABLE filter_instances RENAME COLUMN comment TO description;
ALTER TABLE filter_list RENAME COLUMN comment TO description;
ALTER TABLE group_list RENAME COLUMN comment TO description;
ALTER TABLE ou RENAME COLUMN comment TO description;
ALTER TABLE subnets RENAME COLUMN comment TO description;
ALTER TABLE user_auth RENAME COLUMN comments TO description;
ALTER TABLE user_list RENAME COLUMN comment TO description;
ALTER TABLE vendors RENAME COLUMN comment TO description;

-- Добавляем поле mac_found в user_auth (аналог arp_found)
ALTER TABLE user_auth ADD COLUMN mac_found TIMESTAMP;

-- type → уточнённые имена
ALTER TABLE config_options RENAME COLUMN type TO option_type;
ALTER TABLE dns_queue RENAME COLUMN type TO operation_type;
ALTER TABLE filter_list RENAME COLUMN type TO filter_type;

UPDATE user_auth SET mac_found = last_found WHERE last_found IS NOT NULL;

-- user_stats - set default value 0

ALTER TABLE user_stats ADD COLUMN pkt_in integer NOT NULL DEFAULT 0;
ALTER TABLE user_stats ADD COLUMN pkt_out integer NOT NULL DEFAULT 0;
ALTER TABLE user_stats ADD COLUMN step integer NOT NULL DEFAULT 3600;

-- Сначала обновите NULL-значения на 0 (если они есть)
UPDATE user_stats_full SET pkt_in = 0 WHERE pkt_in IS NULL;
UPDATE user_stats_full SET pkt_out = 0 WHERE pkt_out IS NULL;

-- Затем измените столбцы
ALTER TABLE user_stats_full ALTER COLUMN pkt_in SET NOT NULL;
ALTER TABLE user_stats_full ALTER COLUMN pkt_in SET DEFAULT 0;
ALTER TABLE user_stats_full ALTER COLUMN pkt_out SET NOT NULL;
ALTER TABLE user_stats_full ALTER COLUMN pkt_out SET DEFAULT 0;