/* Before executing this script, replace [TableName] to the table name in SQL database (default is values) */

DO $$
BEGIN

BEGIN
	CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
EXCEPTION WHEN others THEN
	raise notice E'Got exception at attempt to create timescaledb extension.
		SQLSTATE: % 
		SQLERRM: %', SQLSTATE, SQLERRM;
END;

-- create table if not exists:
IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = '[TableName]')
	THEN 
	BEGIN 
	CREATE TABLE public."[TableName]"
	(
    	"time" timestamp(3) without time zone,
    	sourceid integer,
    	status integer,
			data_type SMALLINT,
    	value real,
			double_value double precision,
			int_value BIGINT,
			string_value TEXT,
			"client_time" timestamp(3) without time zone
	) WITH (OIDS = FALSE);
	END;
	CREATE UNIQUE INDEX IF NOT EXISTS idx_time_sourceid ON [TableName] (sourceid, time);
ELSE
BEGIN
	ALTER TABLE public."[TableName]" ADD COLUMN IF NOT EXISTS "data_type" SMALLINT;
	ALTER TABLE public."[TableName]" ADD COLUMN IF NOT EXISTS "double_value" double precision;
	ALTER TABLE public."[TableName]" ADD COLUMN IF NOT EXISTS "int_value" BIGINT;
	ALTER TABLE public."[TableName]" ADD COLUMN IF NOT EXISTS "string_value" TEXT;
	ALTER TABLE public."[TableName]" ADD COLUMN IF NOT EXISTS "client_time" timestamp(3);
END;
END IF;
-- If table is empty, convert it to timescaledb hypertable:
IF NOT EXISTS (SELECT * FROM public."[TableName]" LIMIT 1) 
	THEN 
	BEGIN
		PERFORM create_hypertable('[TableName]', 'time', if_not_exists => true);
	EXCEPTION WHEN undefined_function THEN
		raise notice E'Got exception at attempt to convert [TableName] table into TimescaleDB hypertable:
        	SQLSTATE: % 
        	SQLERRM: %', SQLSTATE, SQLERRM;
	END;
END IF;

END
$$;