-- 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(INTEGER, NAME, NAME, BOOLEAN);

DROP FUNCTION IF EXISTS _timescaledb_internal.create_chunk_constraint(integer,oid);
DROP FUNCTION IF EXISTS _timescaledb_internal.add_constraint(integer,oid);
DROP FUNCTION IF EXISTS _timescaledb_internal.add_constraint_by_name(integer,name);
DROP FUNCTION IF EXISTS _timescaledb_internal.need_chunk_constraint(oid);

INSERT INTO _timescaledb_catalog.chunk_index (chunk_id, index_name, hypertable_id, hypertable_index_name)
SELECT chunk_con.chunk_id, pg_chunk_index_class.relname, chunk.hypertable_id, pg_hypertable_index_class.relname
FROM _timescaledb_catalog.chunk_constraint chunk_con
INNER JOIN _timescaledb_catalog.chunk chunk ON (chunk_con.chunk_id = chunk.id)
INNER JOIN _timescaledb_catalog.hypertable hypertable ON (chunk.hypertable_id = hypertable.id)
INNER JOIN pg_constraint pg_chunk_con ON (
        pg_chunk_con.conrelid = format('%I.%I', chunk.schema_name, chunk.table_name)::regclass
        AND pg_chunk_con.conname = chunk_con.constraint_name
        AND pg_chunk_con.contype != 'f'
)
INNER JOIN pg_class pg_chunk_index_class ON (
    pg_chunk_con.conindid = pg_chunk_index_class.oid
)
INNER JOIN pg_constraint pg_hypertable_con ON (
        pg_hypertable_con.conrelid = format('%I.%I', hypertable.schema_name, hypertable.table_name)::regclass
        AND pg_hypertable_con.conname = chunk_con.hypertable_constraint_name
)
INNER JOIN pg_class pg_hypertable_index_class ON (
    pg_hypertable_con.conindid = pg_hypertable_index_class.oid
);

UPDATE _timescaledb_catalog.dimension_slice SET range_end = 9223372036854775807 WHERE range_end = 2147483647;
UPDATE _timescaledb_catalog.dimension_slice SET range_start = -9223372036854775808 WHERE range_start = 0;

DROP FUNCTION IF EXISTS _timescaledb_internal.range_value_to_pretty(BIGINT, regtype);

-- Upgrade support for setting partitioning function
DROP FUNCTION IF EXISTS create_hypertable(regclass,name,name,integer,name,name,anyelement,boolean,boolean);
DROP FUNCTION IF EXISTS add_dimension(regclass,name,integer,bigint);
DROP FUNCTION IF EXISTS _timescaledb_internal.create_hypertable_row(regclass,name,name,name,name,integer,name,name,bigint,name);
DROP FUNCTION IF EXISTS _timescaledb_internal.add_dimension(regclass,_timescaledb_catalog.hypertable,name,integer,bigint,boolean);

--- Post script

CREATE OR REPLACE FUNCTION _timescaledb_internal.set_time_columns_not_null()
    RETURNS VOID LANGUAGE PLPGSQL VOLATILE AS
$BODY$
DECLARE
        ht_time_column RECORD;
BEGIN

        FOR ht_time_column IN
        SELECT ht.schema_name, ht.table_name, d.column_name
        FROM _timescaledb_catalog.hypertable ht, _timescaledb_catalog.dimension d
        WHERE ht.id = d.hypertable_id AND d.partitioning_func IS NULL
        LOOP
                EXECUTE format(
                $$
                ALTER TABLE %I.%I ALTER %I SET NOT NULL
                $$, ht_time_column.schema_name, ht_time_column.table_name, ht_time_column.column_name);
        END LOOP;
END
$BODY$;

SELECT _timescaledb_internal.set_time_columns_not_null();

CREATE OR REPLACE FUNCTION _timescaledb_internal.to_unix_microseconds(ts TIMESTAMPTZ) RETURNS BIGINT
    AS '$libdir/timescaledb-1.7.2', 'ts_pg_timestamp_to_unix_microseconds' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION _timescaledb_internal.to_timestamp(unixtime_us BIGINT) RETURNS TIMESTAMPTZ
    AS '$libdir/timescaledb-1.7.2', 'ts_pg_unix_microseconds_to_timestamp' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION _timescaledb_internal.dimension_is_finite(
    val      BIGINT
)
    RETURNS BOOLEAN LANGUAGE SQL IMMUTABLE PARALLEL SAFE AS
$BODY$
    --end values of bigint reserved for infinite
    SELECT val > (-9223372036854775808)::bigint AND val < 9223372036854775807::bigint
$BODY$;

CREATE OR REPLACE FUNCTION _timescaledb_internal.dimension_slice_get_constraint_sql(
    dimension_slice_id  INTEGER
)
    RETURNS TEXT LANGUAGE PLPGSQL VOLATILE AS
$BODY$
DECLARE
    dimension_slice_row _timescaledb_catalog.dimension_slice;
    dimension_row _timescaledb_catalog.dimension;
    parts TEXT[];
BEGIN
    SELECT * INTO STRICT dimension_slice_row
    FROM _timescaledb_catalog.dimension_slice
    WHERE id = dimension_slice_id;

    SELECT * INTO STRICT dimension_row
    FROM _timescaledb_catalog.dimension
    WHERE id = dimension_slice_row.dimension_id;

    IF dimension_row.partitioning_func IS NOT NULL THEN

        IF  _timescaledb_internal.dimension_is_finite(dimension_slice_row.range_start) THEN
            parts = parts || format(
            $$
                %1$I.%2$I(%3$I) >= %4$L
            $$,
            dimension_row.partitioning_func_schema,
            dimension_row.partitioning_func,
            dimension_row.column_name,
            dimension_slice_row.range_start);
        END IF;

        IF _timescaledb_internal.dimension_is_finite(dimension_slice_row.range_end) THEN
            parts = parts || format(
            $$
                %1$I.%2$I(%3$I) < %4$L
            $$,
            dimension_row.partitioning_func_schema,
            dimension_row.partitioning_func,
            dimension_row.column_name,
            dimension_slice_row.range_end);
        END IF;

        return array_to_string(parts, 'AND');
    ELSE
        --TODO: only works with time for now
        IF _timescaledb_internal.time_literal_sql(dimension_slice_row.range_start, dimension_row.column_type) =
           _timescaledb_internal.time_literal_sql(dimension_slice_row.range_end, dimension_row.column_type) THEN
            RAISE 'Time based constraints have the same start and end values for column "%": %',
                    dimension_row.column_name,
                    _timescaledb_internal.time_literal_sql(dimension_slice_row.range_end, dimension_row.column_type);
        END IF;

        parts = ARRAY[]::text[];

        IF _timescaledb_internal.dimension_is_finite(dimension_slice_row.range_start) THEN
            parts = parts || format(
            $$
                 %1$I >= %2$s
            $$,
            dimension_row.column_name,
            _timescaledb_internal.time_literal_sql(dimension_slice_row.range_start, dimension_row.column_type));
        END IF;

        IF _timescaledb_internal.dimension_is_finite(dimension_slice_row.range_end) THEN
            parts = parts || format(
            $$
                 %1$I < %2$s
            $$,
            dimension_row.column_name,
            _timescaledb_internal.time_literal_sql(dimension_slice_row.range_end, dimension_row.column_type));
        END IF;

        return array_to_string(parts, 'AND');
    END IF;
END
$BODY$;

--has to be done since old range_end for the CHECK constraint was 2147483647 on closed partitions.
DO $$
DECLARE
    chunk_constraint_row  _timescaledb_catalog.chunk_constraint;
    chunk_row _timescaledb_catalog.chunk;
BEGIN
    -- Need to do this update in two loops: first remove constraints, then add back.
    -- This is because we can only remove the old partitioning function when
    -- there are no constraints on the tables referencing the old function
    FOR chunk_constraint_row IN
        SELECT cc.*
        FROM _timescaledb_catalog.chunk_constraint cc
        INNER JOIN  _timescaledb_catalog.dimension_slice ds ON (cc.dimension_slice_id = ds.id)
        INNER JOIN  _timescaledb_catalog.dimension d ON (ds.dimension_id = d.id)
        WHERE d.partitioning_func IS NOT NULL
    LOOP
        SELECT * INTO STRICT chunk_row FROM _timescaledb_catalog.chunk c WHERE c.id = chunk_constraint_row.chunk_id;

        EXECUTE format('ALTER TABLE %I.%I DROP CONSTRAINT %I', chunk_row.schema_name, chunk_row.table_name, chunk_constraint_row.constraint_name);
    END LOOP;

    RAISE NOTICE 'Updating constraints';

    DROP FUNCTION IF EXISTS _timescaledb_internal.get_partition_for_key(text);
    CREATE OR REPLACE FUNCTION _timescaledb_internal.get_partition_for_key(val anyelement)
    RETURNS int
    AS '$libdir/timescaledb-1.7.2', 'ts_get_partition_for_key' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

    FOR chunk_constraint_row IN
        SELECT cc.*
        FROM _timescaledb_catalog.chunk_constraint cc
        INNER JOIN  _timescaledb_catalog.dimension_slice ds ON (cc.dimension_slice_id = ds.id)
        INNER JOIN  _timescaledb_catalog.dimension d ON (ds.dimension_id = d.id)
        WHERE d.partitioning_func IS NOT NULL
    LOOP
        SELECT * INTO STRICT chunk_row FROM _timescaledb_catalog.chunk c WHERE c.id = chunk_constraint_row.chunk_id;
        PERFORM _timescaledb_internal.chunk_constraint_add_table_constraint(chunk_constraint_row);
    END LOOP;

END$$;

--for timestamp (non-tz) columns we used to have internal_time -> constraint_time via local_time.
--So the internal time was interpreted as UTC but the constraint was printed in terms of the local time.
--Now we interpret the internal_time as UTC and the constraints is generated as UTC as well.
--These constraints should not be re-written since they are correct for the data. But we should adjust the internal time
--to be consistent.

-- So _timescaledb_internal.to_timestamp(internal_time)::timestamp gives you the old constraint
-- We then convert it to timestamptz as though it was at UTC
-- finally, we convert it to the internal represtentation back.

UPDATE _timescaledb_catalog.dimension_slice ds
SET
range_end = _timescaledb_internal.to_unix_microseconds(timezone('UTC',_timescaledb_internal.to_timestamp(range_end)::timestamp)),
range_start = _timescaledb_internal.to_unix_microseconds(timezone('UTC',_timescaledb_internal.to_timestamp(range_start)::timestamp))
FROM _timescaledb_catalog.dimension d
WHERE ds.dimension_id = d.id AND d.column_type = 'timestamp'::regtype;
DROP FUNCTION IF EXISTS _timescaledb_internal.create_hypertable_row(REGCLASS, NAME, NAME, NAME, NAME, INTEGER, NAME, NAME, BIGINT, NAME, REGPROC);
DROP FUNCTION IF EXISTS _timescaledb_internal.rename_hypertable(NAME, NAME, NAME, NAME);

DROP FUNCTION IF EXISTS drop_chunks(bigint,name,name,boolean);
DROP FUNCTION IF EXISTS drop_chunks(timestamptz,name,name,boolean);
DROP FUNCTION IF EXISTS _timescaledb_cache.invalidate_relcache(oid);

DROP FUNCTION IF EXISTS set_chunk_time_interval(REGCLASS, BIGINT);
DROP FUNCTION IF EXISTS add_dimension(REGCLASS, NAME, INTEGER, BIGINT, REGPROC);
DROP FUNCTION IF EXISTS _timescaledb_internal.add_dimension(REGCLASS, _timescaledb_catalog.hypertable, NAME, INTEGER, BIGINT, REGPROC, BOOLEAN);
DROP FUNCTION IF EXISTS _timescaledb_internal.time_interval_specification_to_internal(REGTYPE, anyelement, INTERVAL, TEXT);

-- Tablespace changes
DROP FUNCTION IF EXISTS _timescaledb_internal.attach_tablespace(integer, name);
DROP FUNCTION IF EXISTS attach_tablespace(regclass, name);
-- Cache invalidation functions and triggers
DROP FUNCTION IF EXISTS _timescaledb_cache.invalidate_relcache_trigger();
DROP FUNCTION IF EXISTS _timescaledb_cache.invalidate_relcache(regclass);

-- Tablespace changes
DROP FUNCTION IF EXISTS _timescaledb_internal.select_tablespace(integer, integer[]);
DROP FUNCTION IF EXISTS _timescaledb_internal.select_tablespace(integer, integer);
DROP FUNCTION IF EXISTS _timescaledb_internal.select_tablespace(integer);

-- Chunk functions
DROP FUNCTION IF EXISTS _timescaledb_internal.chunk_create(integer, integer, name, name);
DROP FUNCTION IF EXISTS _timescaledb_internal.drop_chunk_metadata(int);

-- Chunk constraint functions
DROP FUNCTION IF EXISTS _timescaledb_internal.create_chunk_constraint(integer, oid);
DROP FUNCTION IF EXISTS _timescaledb_internal.drop_constraint(integer, name);
DROP FUNCTION IF EXISTS _timescaledb_internal.drop_chunk_constraint(integer, name, boolean);
DROP FUNCTION IF EXISTS _timescaledb_internal.chunk_constraint_drop_table_constraint(_timescaledb_catalog.chunk_constraint);

-- Dimension and time functions
DROP FUNCTION IF EXISTS _timescaledb_internal.change_column_type(int, name, regtype);
DROP FUNCTION IF EXISTS _timescaledb_internal.rename_column(int, name, name);
DROP FUNCTION IF EXISTS _timescaledb_internal.set_time_column_constraint(regclass, name);
DROP FUNCTION IF EXISTS _timescaledb_internal.add_dimension(regclass, _timescaledb_catalog.hypertable, name, integer, anyelement, regproc, boolean, boolean);
DROP FUNCTION IF EXISTS add_dimension(regclass, name, integer, anyelement, regproc);
DROP FUNCTION IF EXISTS _timescaledb_internal.time_interval_specification_to_internal(regtype, anyelement, interval, text, boolean);
DROP FUNCTION IF EXISTS _timescaledb_internal.time_interval_specification_to_internal_with_default_time(regtype, anyelement, text, boolean);
DROP FUNCTION IF EXISTS _timescaledb_internal.create_hypertable(regclass, name, name, name, name, integer, name, name, bigint, name, boolean, regproc);
DROP FUNCTION IF EXISTS create_hypertable(regclass,name,name,integer,name,name,anyelement,boolean,boolean,regproc);
DROP FUNCTION IF EXISTS set_chunk_time_interval(regclass, anyelement);

-- Hypertable and related functions
DROP FUNCTION IF EXISTS _timescaledb_internal.set_time_columns_not_null();
DROP FUNCTION IF EXISTS _timescaledb_internal.create_schema(name);
DROP FUNCTION IF EXISTS _timescaledb_internal.check_role(regclass);
DROP FUNCTION IF EXISTS _timescaledb_internal.attach_tablespace(name,regclass);
DROP FUNCTION IF EXISTS _timescaledb_internal.create_default_indexes(_timescaledb_catalog.hypertable,regclass,name);
DROP FUNCTION IF EXISTS _timescaledb_internal.create_hypertable_schema(name);
DROP FUNCTION IF EXISTS _timescaledb_internal.detach_tablespace(name,regclass);
DROP FUNCTION IF EXISTS _timescaledb_internal.detach_tablespaces(regclass);
DROP FUNCTION IF EXISTS _timescaledb_internal.dimension_type(regclass,name,boolean);
DROP FUNCTION IF EXISTS _timescaledb_internal.show_tablespaces(regclass);
DROP FUNCTION IF EXISTS _timescaledb_internal.verify_hypertable_indexes(regclass);
DROP FUNCTION IF EXISTS _timescaledb_internal.validate_triggers(regclass);
DROP FUNCTION IF EXISTS _timescaledb_internal.chunk_create_table(int, name);
DROP FUNCTION IF EXISTS _timescaledb_internal.ddl_change_owner(oid, name);
DROP FUNCTION IF EXISTS _timescaledb_internal.truncate_hypertable(name,name,boolean);
DROP FUNCTION IF EXISTS attach_tablespace(name,regclass);
DROP FUNCTION IF EXISTS detach_tablespace(name,regclass);

-- Remove redundant index
DROP INDEX IF EXISTS _timescaledb_catalog.dimension_slice_dimension_id_range_start_range_end_idx;

DROP FUNCTION IF EXISTS _timescaledb_internal.drop_hypertable(int,boolean);

DELETE FROM _timescaledb_catalog.dimension_slice WHERE id IN
(SELECT ds.id FROM _timescaledb_catalog.chunk_constraint cc
 RIGHT JOIN _timescaledb_catalog.dimension_slice ds
 ON (ds.id = cc.dimension_slice_id)
 WHERE dimension_slice_id IS NULL);

-- Post script
DROP FUNCTION IF EXISTS _timescaledb_internal.ddl_command_end();
--Fix any potential catalog issues that may have been introduced if a
--trigger was dropped on a hypertable before the current bugfix
--Only deletes orphaned rows from pg_depend.
DELETE FROM pg_depend d 
WHERE d.classid = 'pg_trigger'::regclass 
AND NOT EXISTS (SELECT 1 FROM pg_trigger WHERE oid = d.objid);
-- Adaptive chunking
CREATE OR REPLACE FUNCTION _timescaledb_internal.calculate_chunk_interval(
        dimension_id INTEGER,
        dimension_coord BIGINT,
        chunk_target_size BIGINT
) RETURNS BIGINT AS '$libdir/timescaledb-1.7.2', 'ts_calculate_chunk_interval' LANGUAGE C;

ALTER TABLE _timescaledb_catalog.hypertable ADD COLUMN chunk_sizing_func_schema NAME;
ALTER TABLE _timescaledb_catalog.hypertable ADD COLUMN chunk_sizing_func_name NAME;
ALTER TABLE _timescaledb_catalog.hypertable ADD COLUMN chunk_target_size BIGINT CHECK (chunk_target_size >= 0);
UPDATE _timescaledb_catalog.hypertable SET chunk_target_size = 0;
UPDATE _timescaledb_catalog.hypertable SET chunk_sizing_func_schema = '_timescaledb_internal';
UPDATE _timescaledb_catalog.hypertable SET chunk_sizing_func_name = 'calculate_chunk_interval';
ALTER TABLE _timescaledb_catalog.hypertable ALTER COLUMN chunk_target_size SET NOT NULL;
ALTER TABLE _timescaledb_catalog.hypertable ALTER COLUMN chunk_sizing_func_schema SET NOT NULL;
ALTER TABLE _timescaledb_catalog.hypertable ALTER COLUMN chunk_sizing_func_name SET NOT NULL;

DROP FUNCTION IF EXISTS create_hypertable(regclass,name,name,integer,name,name,anyelement,boolean,boolean,regproc,boolean);
DROP FUNCTION IF EXISTS _timescaledb_internal.time_to_internal(anyelement,regtype);
-- Trigger that blocks INSERTs on the hypertable's root table