-- use collector database
USE collector;

-- create new tables

CREATE TABLE new_flows AS flows
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(toDateTime(timestamp_sec))
ORDER BY toDateTime(timestamp_sec)
TTL toDateTime(timestamp_sec) + toIntervalMonth(1) DELETE;

CREATE TABLE new_rate_metrics AS rate_metrics
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(date)
ORDER BY (date, policy, ip_src, ip_dst, port_src, port_dst, l3_proto, l4_proto, agent_address, ingress_ifindex, egress_ifindex, is_fragmented)
TTL date + toIntervalMonth(1) DELETE;

-- fill in new tables with data from the old ones
INSERT INTO new_flows SELECT * FROM flows;
INSERT INTO new_rate_metrics SELECT * FROM rate_metrics;

-- remove old tables
DROP TABLE flows;
DROP TABLE rate_metrics;

-- rename tables
RENAME TABLE new_flows TO flows;
RENAME TABLE new_rate_metrics TO rate_metrics;

-- check table row count
SELECT count() FROM flows;
SELECT count() FROM rate_metrics;