-- use collector database
USE collector;

-- create new tables

-- netflow v5
CREATE TABLE new_nf_v5 AS netflow_v5
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(date)
ORDER BY date
TTL date + toIntervalDay(1) DELETE;

-- netflow v9
CREATE TABLE new_nf_v9 AS netflow_v9
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(date)
ORDER BY date
TTL date + toIntervalDay(1) DELETE;

-- ipfix
CREATE TABLE new_ipfix AS ipfix
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(date)
ORDER BY date
TTL date + toIntervalDay(1) DELETE;

-- sflow v5
CREATE TABLE collector.new_sflow AS sflow
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(date)
ORDER BY date
TTL date + toIntervalDay(1) DELETE;

-- fill in new tables with data from the old ones
INSERT INTO new_nf_v5 SELECT * FROM netflow_v5;
INSERT INTO new_nf_v9 SELECT * FROM netflow_v9;
INSERT INTO new_ipfix SELECT * FROM ipfix;
INSERT INTO new_sflow SELECT * FROM sflow;

-- remove old tables
DROP TABLE netflow_v5;
DROP TABLE netflow_v9;
DROP TABLE ipfix;
DROP TABLE sflow;

-- rename tables
RENAME TABLE new_nf_v5 TO netflow_v5;
RENAME TABLE new_nf_v9 TO netflow_v9;
RENAME TABLE new_ipfix TO ipfix;
RENAME TABLE new_sflow TO sflow;

-- check table row count
SELECT count() FROM netflow_v5;
SELECT count() FROM netflow_v9;
SELECT count() FROM ipfix;
SELECT count() FROM sflow;