-- ############################################################################
-- SCRIPT DE CONFIGURAÇÃO TIMESCALEDB PARA ZABBIX 7.0 LTS
-- Execute como usuário postgres: psql -U postgres -d zabbix -f timescaledb_setup.sql
-- ############################################################################

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

-- History
SELECT create_hypertable('history', 'clock', chunk_time_interval => 86400, if_not_exists => TRUE, migrate_data => TRUE);
SELECT create_hypertable('history_uint', 'clock', chunk_time_interval => 86400, if_not_exists => TRUE, migrate_data => TRUE);
SELECT create_hypertable('history_str', 'clock', chunk_time_interval => 86400, if_not_exists => TRUE, migrate_data => TRUE);
SELECT create_hypertable('history_log', 'clock', chunk_time_interval => 86400, if_not_exists => TRUE, migrate_data => TRUE);
SELECT create_hypertable('history_text', 'clock', chunk_time_interval => 86400, if_not_exists => TRUE, migrate_data => TRUE);

-- Trends
SELECT create_hypertable('trends', 'clock', chunk_time_interval => 2592000, if_not_exists => TRUE, migrate_data => TRUE);
SELECT create_hypertable('trends_uint', 'clock', chunk_time_interval => 2592000, if_not_exists => TRUE, migrate_data => TRUE);

-- Compressão (History: 1 dia, Trends: 7 dias)
ALTER TABLE history SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
SELECT add_compression_policy('history', INTERVAL '1 day');

ALTER TABLE history_uint SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
SELECT add_compression_policy('history_uint', INTERVAL '1 day');

ALTER TABLE history_str SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
SELECT add_compression_policy('history_str', INTERVAL '1 day');

ALTER TABLE history_log SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
SELECT add_compression_policy('history_log', INTERVAL '1 day');

ALTER TABLE history_text SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
SELECT add_compression_policy('history_text', INTERVAL '1 day');

ALTER TABLE trends SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
SELECT add_compression_policy('trends', INTERVAL '7 days');

ALTER TABLE trends_uint SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
SELECT add_compression_policy('trends_uint', INTERVAL '7 days');