-- 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 _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
CREATE OR REPLACE FUNCTION _timescaledb_internal.insert_blocker() RETURNS trigger
AS '$libdir/timescaledb-1.7.2', 'ts_hypertable_insert_blocker' LANGUAGE C;

-- Drop all pre-0.11.1 insert_blockers from hypertables and add the new, visible trigger
CREATE FUNCTION _timescaledb_internal.insert_blocker_trigger_add(relid REGCLASS) RETURNS OID
AS '$libdir/timescaledb-1.7.2', 'ts_hypertable_insert_blocker_trigger_add' LANGUAGE C VOLATILE STRICT;

SELECT _timescaledb_internal.insert_blocker_trigger_add(h.relid)
FROM (SELECT format('%I.%I', schema_name, table_name)::regclass AS relid FROM _timescaledb_catalog.hypertable) AS h;

DROP FUNCTION _timescaledb_internal.insert_blocker_trigger_add(REGCLASS);

CREATE SCHEMA IF NOT EXISTS _timescaledb_config;
GRANT USAGE ON SCHEMA _timescaledb_config TO PUBLIC;

CREATE SEQUENCE IF NOT EXISTS _timescaledb_config.bgw_job_id_seq MINVALUE 1000;
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_config.bgw_job_id_seq', '');

CREATE TABLE IF NOT EXISTS _timescaledb_config.bgw_job (
    id                  INTEGER PRIMARY KEY DEFAULT nextval('_timescaledb_config.bgw_job_id_seq'),
    application_name    NAME        NOT NULL,
    job_type            NAME        NOT NULL,
    schedule_interval   INTERVAL    NOT NULL,
    max_runtime         INTERVAL    NOT NULL,
    max_retries         INT         NOT NULL,
    retry_period        INTERVAL    NOT NULL,
    CONSTRAINT  valid_job_type CHECK (job_type IN ('telemetry_and_version_check_if_enabled'))
);
ALTER SEQUENCE _timescaledb_config.bgw_job_id_seq OWNED BY _timescaledb_config.bgw_job.id;

SELECT pg_catalog.pg_extension_config_dump('_timescaledb_config.bgw_job', 'WHERE id >= 1000');

CREATE TABLE IF NOT EXISTS _timescaledb_internal.bgw_job_stat (
    job_id                  INT         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_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
--the statistics probably aren't very meaningful across instances.

GRANT SELECT ON _timescaledb_config.bgw_job TO PUBLIC;
GRANT SELECT ON _timescaledb_internal.bgw_job_stat TO PUBLIC;

DO language plpgsql $$
BEGIN
  RAISE WARNING '%',
 E'\nStarting in v0.12.0, TimescaleDB collects anonymous reports to better understand and assist our
users. For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.\n';
END;
$$;

CREATE TABLE IF NOT EXISTS _timescaledb_catalog.installation_metadata (
    key     NAME NOT NULL PRIMARY KEY,
    value   TEXT NOT NULL
);
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.installation_metadata', $$WHERE key='exported_uuid'$$);

INSERT INTO _timescaledb_catalog.installation_metadata SELECT 'install_timestamp', to_timestamp(0);
DROP INDEX IF EXISTS _timescaledb_catalog.dimension_hypertable_id_idx;

GRANT USAGE ON SCHEMA _timescaledb_cache TO PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA _timescaledb_catalog TO PUBLIC;
GRANT SELECT ON TABLE _timescaledb_internal.bgw_job_stat TO PUBLIC;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA _timescaledb_catalog TO PUBLIC;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA _timescaledb_config TO PUBLIC;

DROP FUNCTION IF EXISTS create_hypertable(regclass,name,name,integer,name,name,anyelement,boolean,boolean,regproc,boolean,text,regproc);
DROP FUNCTION IF EXISTS add_dimension(regclass,name,integer,anyelement,regproc,boolean);

DROP FUNCTION IF EXISTS _timescaledb_internal.get_version();
DROP FUNCTION IF EXISTS drop_chunks(INTERVAL, NAME, NAME, BOOLEAN);
DROP FUNCTION IF EXISTS drop_chunks(ANYELEMENT, NAME, NAME, BOOLEAN);
DROP FUNCTION IF EXISTS _timescaledb_internal.drop_chunks_impl(BIGINT, NAME, NAME, BOOLEAN, BOOLEAN);
DROP FUNCTION IF EXISTS _timescaledb_internal.drop_chunks_type_check(REGTYPE, NAME, NAME);
DROP FUNCTION IF EXISTS _timescaledb_internal.dimension_get_time(INTEGER);
DROP FUNCTION IF EXISTS create_hypertable(regclass, name, name, integer, name, name, anyelement, boolean, boolean, regproc, boolean, text, regproc);
DROP FUNCTION IF EXISTS _timescaledb_internal.to_microseconds(TIMESTAMPTZ);
DROP FUNCTION IF EXISTS _timescaledb_internal.to_timestamp_pg(BIGINT);
DROP FUNCTION IF EXISTS _timescaledb_internal.time_to_internal(anyelement);
--Now we define the argument tables for available BGW policies.
CREATE TABLE IF NOT EXISTS _timescaledb_config.bgw_policy_reorder (
    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,
	hypertable_index_name	NAME		NOT NULL
);
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_config.bgw_policy_reorder', '');

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				INTERVAL    NOT NULL,
	cascade					BOOLEAN
);
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_config.bgw_policy_drop_chunks', '');

----- End BGW policy table definitions

-- Now we define a special stats table for each job/chunk pair. This will be used by the scheduler
-- to determine whether to run a specific job on a specific chunk.
CREATE TABLE IF NOT EXISTS _timescaledb_internal.bgw_policy_chunk_stats (
	job_id					INTEGER 	NOT NULL REFERENCES _timescaledb_config.bgw_job(id) ON DELETE CASCADE,
	chunk_id				INTEGER		NOT NULL REFERENCES _timescaledb_catalog.chunk(id) ON DELETE CASCADE,
	num_times_job_run		INTEGER,
	last_time_job_run		TIMESTAMPTZ,
	UNIQUE(job_id,chunk_id)
);

GRANT SELECT ON _timescaledb_config.bgw_policy_reorder TO PUBLIC;
GRANT SELECT ON _timescaledb_config.bgw_policy_drop_chunks TO PUBLIC;
GRANT SELECT ON _timescaledb_internal.bgw_policy_chunk_stats TO PUBLIC;

DROP FUNCTION IF EXISTS _timescaledb_internal.drop_chunks_impl(REGCLASS, "any", "any", BOOLEAN);
DROP FUNCTION IF EXISTS drop_chunks("any", NAME, NAME, BOOLEAN, "any");

DROP FUNCTION IF EXISTS _timescaledb_internal.get_os_info();

-- we add an addition optional argument to locf
DROP FUNCTION IF EXISTS locf(ANYELEMENT,ANYELEMENT);

CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_agg (
    mat_hypertable_id INTEGER PRIMARY KEY REFERENCES _timescaledb_catalog.hypertable(id) ON DELETE CASCADE,
    raw_hypertable_id INTEGER NOT NULL REFERENCES  _timescaledb_catalog.hypertable(id) ON DELETE CASCADE,
    user_view_schema NAME NOT NULL,
    user_view_name NAME NOT NULL,
    partial_view_schema NAME NOT NULL,
    partial_view_name NAME NOT NULL,
    bucket_width  BIGINT NOT NULL,
    job_id INTEGER UNIQUE NOT NULL REFERENCES _timescaledb_config.bgw_job(id) ON DELETE RESTRICT,
    refresh_lag BIGINT NOT NULL,
    direct_view_schema NAME NOT NULL,
    direct_view_name NAME NOT NULL,
    max_interval_per_job BIGINT NOT NULL,
    UNIQUE(user_view_schema, user_view_name),
    UNIQUE(partial_view_schema, partial_view_name)
);
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.continuous_agg', '');

GRANT SELECT ON _timescaledb_catalog.continuous_agg TO PUBLIC;

CREATE OR REPLACE FUNCTION _timescaledb_internal.finalize_agg_sfunc(
tstate internal, aggfn TEXT, inner_agg_collation_schema NAME, inner_agg_collation_name NAME, inner_agg_input_types NAME[][], inner_agg_serialized_state BYTEA, return_type_dummy_val ANYELEMENT)
RETURNS internal
AS '$libdir/timescaledb-1.7.2', 'ts_finalize_agg_sfunc'
LANGUAGE C IMMUTABLE ;

CREATE OR REPLACE FUNCTION _timescaledb_internal.finalize_agg_ffunc(
tstate internal, aggfn TEXT, inner_agg_collation_schema NAME, inner_agg_collation_name NAME, inner_agg_input_types NAME[][], inner_agg_serialized_state BYTEA, return_type_dummy_val ANYELEMENT)
RETURNS anyelement
AS '$libdir/timescaledb-1.7.2', 'ts_finalize_agg_ffunc'
LANGUAGE C IMMUTABLE ;

CREATE AGGREGATE _timescaledb_internal.finalize_agg(agg_name TEXT,  inner_agg_collation_schema NAME,  inner_agg_collation_name NAME, inner_agg_input_types NAME[][], inner_agg_serialized_state BYTEA, return_type_dummy_val anyelement) (
    SFUNC = _timescaledb_internal.finalize_agg_sfunc,
    STYPE = internal,
    FINALFUNC = _timescaledb_internal.finalize_agg_ffunc,
    FINALFUNC_EXTRA
);

ALTER TABLE _timescaledb_catalog.installation_metadata RENAME TO telemetry_metadata;
ALTER INDEX _timescaledb_catalog.installation_metadata_pkey RENAME TO telemetry_metadata_pkey;

CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_invalidation_threshold(
    hypertable_id INTEGER PRIMARY KEY REFERENCES _timescaledb_catalog.hypertable(id) ON DELETE CASCADE,
    watermark BIGINT NOT NULL
);
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.continuous_aggs_invalidation_threshold', '');

GRANT SELECT ON _timescaledb_catalog.continuous_aggs_invalidation_threshold TO PUBLIC;

CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_completed_threshold(
    materialization_id INTEGER PRIMARY KEY
        REFERENCES _timescaledb_catalog.continuous_agg(mat_hypertable_id)
        ON DELETE CASCADE,
    watermark BIGINT NOT NULL
);
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.continuous_aggs_completed_threshold', '');

GRANT SELECT ON _timescaledb_catalog.continuous_aggs_completed_threshold TO PUBLIC;

-- this does not have an FK on the materialization table since INSERTs to this
-- table are performance critical
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log(
    hypertable_id INTEGER NOT NULL,
    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', '');

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;

DROP FUNCTION IF EXISTS drop_chunks(
    older_than "any",
    table_name  NAME,
    schema_name NAME,
    cascade  BOOLEAN,
    newer_than "any",
    verbose BOOLEAN
);

CREATE OR REPLACE FUNCTION drop_chunks(
    older_than "any" = NULL,
    table_name  NAME = NULL,
    schema_name NAME = NULL,
    cascade  BOOLEAN = FALSE,
    newer_than "any" = NULL,