-- Copyright (c) 2016-2018  Timescale, Inc. All Rights Reserved.
--
-- This file is licensed under the Apache License, see LICENSE-APACHE
-- at the top level directory of the TimescaleDB distribution.

-- This file is always prepended to all upgrade scripts.

-- Triggers should be disabled during upgrades to avoid having them
-- invoke functions that might load an old version of the shared
-- library before those functions have been updated.
DROP EVENT TRIGGER IF EXISTS timescaledb_ddl_command_end;
DROP EVENT TRIGGER IF EXISTS timescaledb_ddl_sql_drop;

-- These are legacy triggers. They need to be disabled here even
-- though they don't exist in newer versions, because they might still
-- exist when upgrading from older versions. Thus we need to DROP all
-- triggers here that have ever been created.
DROP TRIGGER IF EXISTS "0_cache_inval" ON _timescaledb_catalog.hypertable;
DROP TRIGGER IF EXISTS "0_cache_inval" ON _timescaledb_catalog.chunk;
DROP TRIGGER IF EXISTS "0_cache_inval" ON _timescaledb_catalog.chunk_constraint;
DROP TRIGGER IF EXISTS "0_cache_inval" ON _timescaledb_catalog.dimension_slice;
DROP TRIGGER IF EXISTS "0_cache_inval" ON _timescaledb_catalog.dimension;

CREATE OR REPLACE FUNCTION _timescaledb_internal.restart_background_workers()
RETURNS BOOL
AS '$libdir/timescaledb', 'ts_bgw_db_workers_restart'
LANGUAGE C VOLATILE;

SELECT _timescaledb_internal.restart_background_workers();
DROP FUNCTION IF EXISTS drop_chunks("any",name,name,boolean,"any",boolean,boolean);
DROP FUNCTION IF EXISTS add_drop_chunks_policy(REGCLASS,INTERVAL,BOOL,BOOL,BOOL);

ALTER TABLE _timescaledb_catalog.dimension
ADD COLUMN integer_now_func_schema     NAME     NULL;

ALTER TABLE _timescaledb_catalog.dimension
ADD COLUMN integer_now_func            NAME     NULL;

ALTER TABLE _timescaledb_catalog.dimension
ADD CONSTRAINT dimension_check2
CHECK (
        (integer_now_func_schema IS NULL AND integer_now_func IS NULL) OR
        (integer_now_func_schema IS NOT NULL AND integer_now_func IS NOT NULL)
);
-- ----------------------
CREATE TYPE _timescaledb_catalog.ts_interval AS (
    is_time_interval        BOOLEAN,
    time_interval		    INTERVAL,
    integer_interval        BIGINT
    );

-- q -- todo:: this is probably necessary if we keep the validation constraint in the table definition.
CREATE OR REPLACE FUNCTION _timescaledb_internal.valid_ts_interval(invl _timescaledb_catalog.ts_interval)
RETURNS BOOLEAN AS '$libdir/timescaledb-1.7.2', 'ts_valid_ts_interval' LANGUAGE C VOLATILE STRICT;

DROP VIEW IF EXISTS timescaledb_information.drop_chunks_policies;
DROP VIEW IF EXISTS timescaledb_information.policy_stats;

CREATE TABLE IF NOT EXISTS _timescaledb_config.bgw_policy_drop_chunks_tmp (
    job_id          		    INTEGER                 PRIMARY KEY REFERENCES _timescaledb_config.bgw_job(id) ON DELETE CASCADE,
    hypertable_id   		    INTEGER     UNIQUE      NOT NULL REFERENCES _timescaledb_catalog.hypertable(id) ON DELETE CASCADE,
    older_than	    _timescaledb_catalog.ts_interval    NOT NULL,
	cascade					    BOOLEAN                 NOT NULL,
    cascade_to_materializations BOOLEAN                 NOT NULL,
    CONSTRAINT valid_older_than CHECK(_timescaledb_internal.valid_ts_interval(older_than))
);

INSERT INTO _timescaledb_config.bgw_policy_drop_chunks_tmp
(SELECT job_id, hypertable_id, ROW('t',older_than,NULL)::_timescaledb_catalog.ts_interval  as older_than, cascade, cascade_to_materializations
FROM _timescaledb_config.bgw_policy_drop_chunks);

ALTER EXTENSION timescaledb DROP TABLE _timescaledb_config.bgw_policy_drop_chunks;
DROP TABLE _timescaledb_config.bgw_policy_drop_chunks;

CREATE TABLE IF NOT EXISTS _timescaledb_config.bgw_policy_drop_chunks (
    job_id          		    INTEGER                 PRIMARY KEY REFERENCES _timescaledb_config.bgw_job(id) ON DELETE CASCADE,
    hypertable_id   		    INTEGER     UNIQUE      NOT NULL REFERENCES _timescaledb_catalog.hypertable(id) ON DELETE CASCADE,
    older_than	    _timescaledb_catalog.ts_interval    NOT NULL,
	cascade					    BOOLEAN                 NOT NULL,
    cascade_to_materializations BOOLEAN                 NOT NULL,
    CONSTRAINT valid_older_than CHECK(_timescaledb_internal.valid_ts_interval(older_than))
);

INSERT INTO _timescaledb_config.bgw_policy_drop_chunks
(SELECT * FROM _timescaledb_config.bgw_policy_drop_chunks_tmp);

SELECT pg_catalog.pg_extension_config_dump('_timescaledb_config.bgw_policy_drop_chunks', '');
DROP TABLE _timescaledb_config.bgw_policy_drop_chunks_tmp;
GRANT SELECT ON _timescaledb_config.bgw_policy_drop_chunks TO PUBLIC;

DROP FUNCTION IF EXISTS alter_job_schedule(INTEGER, INTERVAL, INTERVAL, INTEGER, INTERVAL, BOOL);


--ADDS last_successful_finish column
--Must remove from extension first
ALTER EXTENSION timescaledb DROP TABLE _timescaledb_internal.bgw_job_stat;
DROP VIEW IF EXISTS timescaledb_information.policy_stats;
DROP VIEW IF EXISTS timescaledb_information.continuous_aggregate_stats;

--create table and drop instead of rename so that all indexes dropped as well
CREATE TABLE _timescaledb_internal.bgw_job_stat_tmp AS SELECT * FROM _timescaledb_internal.bgw_job_stat;
DROP TABLE _timescaledb_internal.bgw_job_stat;

CREATE TABLE IF NOT EXISTS _timescaledb_internal.bgw_job_stat (
    job_id                  INTEGER         PRIMARY KEY REFERENCES _timescaledb_config.bgw_job(id) ON DELETE CASCADE,
    last_start              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_finish             TIMESTAMPTZ NOT NULL,
    next_start              TIMESTAMPTZ NOT NULL,
    last_successful_finish  TIMESTAMPTZ NOT NULL,
    last_run_success        BOOL        NOT NULL,
    total_runs              BIGINT      NOT NULL,
    total_duration          INTERVAL    NOT NULL,
    total_successes         BIGINT      NOT NULL,
    total_failures          BIGINT      NOT NULL,
    total_crashes           BIGINT      NOT NULL,
    consecutive_failures    INT         NOT NULL,
    consecutive_crashes     INT         NOT NULL
);
--The job_stat table is not dumped by pg_dump on purpose because (see tables.sql for details)

INSERT INTO _timescaledb_internal.bgw_job_stat
    SELECT job_id,
        last_start,
        last_finish,
        next_start,
        CASE WHEN last_run_success THEN last_finish ELSE '-infinity'::timestamptz END as last_successful_finish,
        last_run_success,
        total_runs,
        total_duration,
        total_successes,
        total_failures,
        total_crashes,
        consecutive_failures,
        consecutive_crashes
    FROM _timescaledb_internal.bgw_job_stat_tmp;

DROP TABLE _timescaledb_internal.bgw_job_stat_tmp;
GRANT SELECT ON _timescaledb_internal.bgw_job_stat TO PUBLIC;





ALTER TABLE _timescaledb_catalog.hypertable add column compressed boolean NOT NULL default false;
ALTER TABLE _timescaledb_catalog.hypertable add column compressed_hypertable_id          INTEGER   REFERENCES _timescaledb_catalog.hypertable(id);
ALTER TABLE _timescaledb_catalog.hypertable drop constraint hypertable_num_dimensions_check;
ALTER TABLE _timescaledb_catalog.hypertable add constraint hypertable_dim_compress_check check ( num_dimensions > 0  or compressed = true );
alter table _timescaledb_catalog.hypertable add constraint hypertable_compress_check check ( compressed = false or (compressed = true and compressed_hypertable_id is null ));

ALTER TABLE _timescaledb_catalog.chunk add column compressed_chunk_id integer references _timescaledb_catalog.chunk(id);
CREATE INDEX IF NOT EXISTS chunk_compressed_chunk_id_idx
ON _timescaledb_catalog.chunk(compressed_chunk_id);

CREATE TABLE _timescaledb_catalog.compression_algorithm(
	id SMALLINT PRIMARY KEY,
	version SMALLINT NOT NULL,
	name NAME NOT NULL,
	description TEXT
);

CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable_compression (
	hypertable_id INTEGER REFERENCES _timescaledb_catalog.hypertable(id) ON DELETE CASCADE,
	attname NAME NOT NULL,
	compression_algorithm_id SMALLINT REFERENCES _timescaledb_catalog.compression_algorithm(id),
    segmentby_column_index SMALLINT ,
    orderby_column_index SMALLINT,
    orderby_asc BOOLEAN,
    orderby_nullsfirst BOOLEAN,
	PRIMARY KEY (hypertable_id, attname),
    UNIQUE (hypertable_id, segmentby_column_index),
    UNIQUE (hypertable_id, orderby_column_index)
);

SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.hypertable_compression', '');

CREATE TABLE IF NOT EXISTS _timescaledb_catalog.compression_chunk_size (

    chunk_id            INTEGER REFERENCES _timescaledb_catalog.chunk(id) ON DELETE CASCADE,
    compressed_chunk_id   INTEGER REFERENCES _timescaledb_catalog.chunk(id) ON DELETE CASCADE,
    uncompressed_heap_size BIGINT NOT NULL,
    uncompressed_toast_size BIGINT NOT NULL,
    uncompressed_index_size BIGINT NOT NULL,
    compressed_heap_size BIGINT NOT NULL,
    compressed_toast_size BIGINT NOT NULL,
    compressed_index_size BIGINT NOT NULL,
    PRIMARY KEY( chunk_id, compressed_chunk_id)
);
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.compression_chunk_size', '');

CREATE TABLE IF NOT EXISTS _timescaledb_config.bgw_policy_compress_chunks(
    job_id                      INTEGER                 PRIMARY KEY REFERENCES _timescaledb_config.bgw_job(id) ON DELETE CASCADE,
    hypertable_id               INTEGER     UNIQUE      NOT NULL REFERENCES _timescaledb_catalog.hypertable(id) ON DELETE CASCADE,
    older_than      _timescaledb_catalog.ts_interval    NOT NULL,
    CONSTRAINT valid_older_than CHECK(_timescaledb_internal.valid_ts_interval(older_than))
);

SELECT pg_catalog.pg_extension_config_dump('_timescaledb_config.bgw_policy_compress_chunks', '');

GRANT SELECT ON _timescaledb_catalog.compression_algorithm TO PUBLIC;
GRANT SELECT ON _timescaledb_catalog.hypertable_compression TO PUBLIC;
GRANT SELECT ON _timescaledb_catalog.compression_chunk_size TO PUBLIC;
GRANT SELECT ON _timescaledb_config.bgw_policy_compress_chunks TO PUBLIC;

CREATE TYPE _timescaledb_internal.compressed_data;

--the textual input/output is simply base64 encoding of the binary representation
CREATE FUNCTION _timescaledb_internal.compressed_data_in(CSTRING)
   RETURNS _timescaledb_internal.compressed_data
   AS '$libdir/timescaledb-1.7.2', 'ts_compressed_data_in'
   LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION _timescaledb_internal.compressed_data_out(_timescaledb_internal.compressed_data)
   RETURNS CSTRING
   AS '$libdir/timescaledb-1.7.2', 'ts_compressed_data_out'
   LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION _timescaledb_internal.compressed_data_send(_timescaledb_internal.compressed_data)
   RETURNS BYTEA
   AS '$libdir/timescaledb-1.7.2', 'ts_compressed_data_send'
   LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION _timescaledb_internal.compressed_data_recv(internal)
   RETURNS _timescaledb_internal.compressed_data
   AS '$libdir/timescaledb-1.7.2', 'ts_compressed_data_recv'
   LANGUAGE C IMMUTABLE STRICT;

CREATE TYPE _timescaledb_internal.compressed_data (
    INTERNALLENGTH = VARIABLE,
    STORAGE = EXTERNAL,
    ALIGNMENT = DOUBLE, --needed for alignment in ARRAY type compression
    INPUT = _timescaledb_internal.compressed_data_in,
    OUTPUT = _timescaledb_internal.compressed_data_out,
    RECEIVE = _timescaledb_internal.compressed_data_recv,
    SEND = _timescaledb_internal.compressed_data_send
);

--insert data for compression_algorithm --
insert into _timescaledb_catalog.compression_algorithm values
( 0, 1, 'COMPRESSION_ALGORITHM_NONE', 'no compression'),
( 1, 1, 'COMPRESSION_ALGORITHM_ARRAY', 'array'),
( 2, 1, 'COMPRESSION_ALGORITHM_DICTIONARY', 'dictionary'),
( 3, 1, 'COMPRESSION_ALGORITHM_GORILLA', 'gorilla'),
( 4, 1, 'COMPRESSION_ALGORITHM_DELTADELTA', 'deltadelta')
on conflict(id) do update set (version, name, description)
= (excluded.version, excluded.name, excluded.description);

--NOTE: below added after initial tagging and release; not
--present in all released versions -- this is also re-executed
--in the next update.
CLUSTER  _timescaledb_catalog.hypertable USING hypertable_pkey;
ALTER TABLE _timescaledb_catalog.hypertable SET WITHOUT CLUSTER;
--rewrite hypertable catalog table because previous updates messed up
--and the table is now using the missingval optimization which doesnt work
--with catalog scans; Note this is equivalent to a VACUUM FULL
--but that command cannot be used inside an update script.
CLUSTER  _timescaledb_catalog.hypertable USING hypertable_pkey;
ALTER TABLE _timescaledb_catalog.hypertable SET WITHOUT CLUSTER;

--The metadata table also has the missingval optimization;
CLUSTER  _timescaledb_catalog.metadata USING metadata_pkey;
ALTER TABLE _timescaledb_catalog.metadata SET WITHOUT CLUSTER;
DO
$BODY$
DECLARE
    hypertable_name TEXT;
BEGIN
    SELECT first_dim.schema_name || '.' || first_dim.table_name
    FROM _timescaledb_catalog.continuous_agg ca
    INNER JOIN LATERAL (
        SELECT dim.*, h.*
        FROM _timescaledb_catalog.hypertable h
        INNER JOIN _timescaledb_catalog.dimension dim ON (dim.hypertable_id = h.id)
        WHERE ca.raw_hypertable_id = h.id
        ORDER by dim.id ASC
        LIMIT 1
    ) first_dim ON true
    WHERE first_dim.column_type IN (REGTYPE 'int2', REGTYPE 'int4', REGTYPE 'int8')
    AND (first_dim.integer_now_func_schema IS NULL OR first_dim.integer_now_func IS NULL)
    INTO hypertable_name;

    IF hypertable_name is not null AND (current_setting('timescaledb.ignore_update_errors', true) is null OR current_setting('timescaledb.ignore_update_errors', true) != 'on') THEN
        RAISE 'The continuous aggregate on hypertable "%" will break unless an integer_now func is set using set_integer_now_func().', hypertable_name;
    END IF;
END
$BODY$;


ALTER TABLE  _timescaledb_catalog.continuous_agg
    ADD COLUMN  ignore_invalidation_older_than BIGINT NOT NULL DEFAULT BIGINT '9223372036854775807';
UPDATE _timescaledb_catalog.continuous_agg SET ignore_invalidation_older_than = BIGINT '9223372036854775807';

CLUSTER  _timescaledb_catalog.continuous_agg USING continuous_agg_pkey;
ALTER TABLE _timescaledb_catalog.continuous_agg SET WITHOUT CLUSTER;

CREATE INDEX IF NOT EXISTS continuous_agg_raw_hypertable_id_idx
      ON _timescaledb_catalog.continuous_agg(raw_hypertable_id);


--Add modification_time column
CREATE TABLE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log_tmp AS SELECT * FROM _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
ALTER EXTENSION timescaledb DROP TABLE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
DROP TABLE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log
(
    hypertable_id              INTEGER NOT NULL,
    modification_time BIGINT  NOT NULL, --time at which the raw table was modified
    lowest_modified_value      BIGINT  NOT NULL,
    greatest_modified_value    BIGINT  NOT NULL
);
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.continuous_aggs_hypertable_invalidation_log', '');
--modification_time == INT_MIN to cause these invalidations to be processed
INSERT INTO _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log
    SELECT hypertable_id, BIGINT '-9223372036854775808', lowest_modified_value, greatest_modified_value
    FROM _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log_tmp;
DROP TABLE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log_tmp;
CREATE INDEX continuous_aggs_hypertable_invalidation_log_idx
    ON _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log (hypertable_id, lowest_modified_value ASC);
GRANT SELECT ON  _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log TO PUBLIC;

--Add modification_time column
CREATE TABLE _timescaledb_catalog.continuous_aggs_materialization_invalidation_log_tmp AS SELECT * FROM _timescaledb_catalog.continuous_aggs_materialization_invalidation_log;
ALTER EXTENSION timescaledb DROP TABLE _timescaledb_catalog.continuous_aggs_materialization_invalidation_log;
DROP TABLE _timescaledb_catalog.continuous_aggs_materialization_invalidation_log;
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_materialization_invalidation_log
(
    materialization_id         INTEGER
        REFERENCES _timescaledb_catalog.continuous_agg (mat_hypertable_id)
            ON DELETE CASCADE,
    modification_time BIGINT NOT NULL, --time at which the raw table was modified
    lowest_modified_value      BIGINT NOT NULL,
    greatest_modified_value    BIGINT NOT NULL
);
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.continuous_aggs_materialization_invalidation_log', '');
--modification_time == INT_MIN to cause these invalidations to be processed
INSERT INTO _timescaledb_catalog.continuous_aggs_materialization_invalidation_log
    SELECT materialization_id, BIGINT '-9223372036854775808', lowest_modified_value, greatest_modified_value
    FROM _timescaledb_catalog.continuous_aggs_materialization_invalidation_log_tmp;
DROP TABLE _timescaledb_catalog.continuous_aggs_materialization_invalidation_log_tmp;
CREATE INDEX continuous_aggs_materialization_invalidation_log_idx
    ON _timescaledb_catalog.continuous_aggs_materialization_invalidation_log (materialization_id, lowest_modified_value ASC);
GRANT SELECT ON  _timescaledb_catalog.continuous_aggs_materialization_invalidation_log TO PUBLIC;

ALTER TABLE _timescaledb_config.bgw_policy_drop_chunks ALTER COLUMN cascade_to_materializations DROP NOT NULL;

UPDATE _timescaledb_config.bgw_policy_drop_chunks SET cascade_to_materializations = NULL WHERE cascade_to_materializations = false;