-- 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 public.time_bucket(INTERVAL, TIMESTAMP);
DROP FUNCTION IF EXISTS public.time_bucket(INTERVAL, TIMESTAMPTZ);
DROP FUNCTION IF EXISTS public.time_bucket(INTERVAL, DATE);
DROP FUNCTION IF EXISTS public.time_bucket(INTERVAL, TIMESTAMP, INTERVAL);
DROP FUNCTION IF EXISTS public.time_bucket(INTERVAL, TIMESTAMPTZ, INTERVAL);
DROP FUNCTION IF EXISTS public.time_bucket(INTERVAL, DATE, INTERVAL);
DROP FUNCTION IF EXISTS public.time_bucket(BIGINT, BIGINT);
DROP FUNCTION IF EXISTS public.time_bucket(INT, INT);
DROP FUNCTION IF EXISTS public.time_bucket(SMALLINT, SMALLINT);
DROP FUNCTION IF EXISTS public.time_bucket(BIGINT, BIGINT, BIGINT);
DROP FUNCTION IF EXISTS public.time_bucket(INT, INT, INT);
DROP FUNCTION IF EXISTS public.time_bucket(SMALLINT, SMALLINT, SMALLINT);

-- Indexing updates
DROP EVENT TRIGGER IF EXISTS ddl_create_index;
DROP EVENT TRIGGER IF EXISTS ddl_alter_index;
DROP EVENT TRIGGER IF EXISTS ddl_drop_index;
DROP TRIGGER IF EXISTS trigger_main_on_change_chunk_index ON _timescaledb_catalog.chunk_index;
DROP TRIGGER IF EXISTS trigger_main_on_change_hypertable_index ON _timescaledb_catalog.hypertable_index;

DROP FUNCTION IF EXISTS _timescaledb_internal.get_index_definition_for_table(NAME, NAME, NAME, TEXT);
DROP FUNCTION IF EXISTS _timescaledb_internal.create_chunk_index_row(NAME, NAME, NAME, NAME, TEXT);
DROP FUNCTION IF EXISTS _timescaledb_internal.on_change_chunk_index();
DROP FUNCTION IF EXISTS _timescaledb_internal.add_index(INTEGER, NAME, NAME, TEXT);
DROP FUNCTION IF EXISTS _timescaledb_internal.drop_index(NAME, NAME);
DROP FUNCTION IF EXISTS _timescaledb_internal.get_general_index_definition(REGCLASS, REGCLASS, _timescaledb_catalog.hypertable);
DROP FUNCTION IF EXISTS _timescaledb_internal.ddl_process_create_index();
DROP FUNCTION IF EXISTS _timescaledb_internal.ddl_process_alter_index();
DROP FUNCTION IF EXISTS _timescaledb_internal.ddl_process_drop_index();
DROP FUNCTION IF EXISTS _timescaledb_internal.create_index_on_all_chunks(INTEGER, NAME, NAME, TEXT);
DROP FUNCTION IF EXISTS _timescaledb_internal.drop_index_on_all_chunks(NAME, NAME);
DROP FUNCTION IF EXISTS _timescaledb_internal.on_change_hypertable_index();
DROP FUNCTION IF EXISTS _timescaledb_internal.need_chunk_index(INTEGER, OID);
DROP FUNCTION IF EXISTS _timescaledb_internal.check_index(REGCLASS, _timescaledb_catalog.hypertable);

DROP FUNCTION IF EXISTS indexes_relation_size_pretty(REGCLASS);

ALTER TABLE IF EXISTS _timescaledb_catalog.chunk_index RENAME TO chunk_index_old;
ALTER SEQUENCE IF EXISTS _timescaledb_catalog.chunk_index_id_seq RENAME TO chunk_index_old_id_seq;

-- Create new table
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_index (
    chunk_id              INTEGER NOT NULL REFERENCES _timescaledb_catalog.chunk(id) ON DELETE CASCADE,
    index_name            NAME NOT NULL,
    hypertable_id         INTEGER NOT NULL REFERENCES _timescaledb_catalog.hypertable(id) ON DELETE CASCADE,
    hypertable_index_name NAME NOT NULL,
    UNIQUE(chunk_id, index_name)
);
CREATE INDEX IF NOT EXISTS chunk_index_hypertable_id_hypertable_index_name_idx
ON _timescaledb_catalog.chunk_index(hypertable_id, hypertable_index_name);
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.chunk_index', '');

-- Remove metadata table triggers
DROP TRIGGER trigger_block_truncate ON _timescaledb_catalog.hypertable;
DROP TRIGGER trigger_block_truncate ON _timescaledb_catalog.dimension;
DROP TRIGGER trigger_block_truncate ON _timescaledb_catalog.dimension_slice;
DROP TRIGGER trigger_block_truncate ON _timescaledb_catalog.chunk_constraint;
DROP TRIGGER trigger_block_truncate ON _timescaledb_catalog.hypertable_index;
DROP TRIGGER trigger_1_main_on_change_hypertable ON _timescaledb_catalog.hypertable;
DROP FUNCTION IF EXISTS _timescaledb_internal.on_truncate_block();
DROP FUNCTION IF EXISTS _timescaledb_internal.on_trigger_error(TEXT, NAME, NAME);
DROP FUNCTION IF EXISTS _timescaledb_internal.on_change_hypertable();
DROP FUNCTION IF EXISTS _timescaledb_internal.setup_main(BOOLEAN);
DROP FUNCTION IF EXISTS restore_timescaledb();

DROP FUNCTION IF EXISTS _timescaledb_internal.drop_chunk(INTEGER, BOOLEAN, BOOLEAN);
DROP FUNCTION IF EXISTS drop_chunks(TIMESTAMPTZ, NAME, NAME);
DROP FUNCTION IF EXISTS drop_chunks(INTERVAL, NAME, NAME);
DROP FUNCTION IF EXISTS _timescaledb_internal.drop_chunks_older_than(BIGINT, NAME, NAME);
DROP FUNCTION IF EXISTS _timescaledb_internal.truncate_hypertable(NAME, NAME);

--- Post script
-- Indexing updates

-- Convert old chunk_index table data to new format
INSERT INTO _timescaledb_catalog.chunk_index
SELECT ch.id, ci.index_name, h.id, ci.main_index_name
FROM _timescaledb_catalog.chunk_index_old ci,
     _timescaledb_catalog.hypertable h,
     _timescaledb_catalog.chunk ch,
     pg_index i,
     pg_class c
WHERE ci.schema_name = ch.schema_name
AND   ci.table_name = ch.table_name
AND   i.indexrelid = format('%I.%I', ci.main_schema_name, ci.main_index_name)::REGCLASS
AND   i.indrelid = c.oid
AND   ci.main_schema_name = h.schema_name
AND   c.relname = h.table_name;

ALTER EXTENSION timescaledb
DROP TABLE _timescaledb_catalog.chunk_index_old;
ALTER EXTENSION timescaledb
DROP TABLE _timescaledb_catalog.hypertable_index;
ALTER EXTENSION timescaledb
DROP SEQUENCE _timescaledb_catalog.chunk_index_old_id_seq;

DROP TABLE IF EXISTS _timescaledb_catalog.chunk_index_old;
DROP TABLE IF EXISTS _timescaledb_catalog.hypertable_index;
-- No need to drop _timescaledb_catalog.chunk_index_old_id_seq,
-- removed with table.
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);