#!/bin/bash
# The script creates the SQL script to create the files needed for PGXN

# Parameter handling

# Accept an argument whether the zip file for pgxn.org should be overridden
# Default is N -> no
PGXN='N'

# Accept an argument whether the database is kept or dropped
# Default is N -> no
DROPDATABASE='N'

# Accept an argument whether the tests should be done with or without additional
# packages
# Default is n -> nos
WITHPACKAGES='n'

# Counter
i=0

# Helptext to return usage
HELPTEXT='No parameters: defaults used; --pgxn(-px:): Create release for pgxn.org; --drop(-d:): Drop database at the end; --packages(-p): Include additonal packages, --help(-?, -h): This text'

# Loop through arguments
for arg in "$@"; do
	case "$arg" in
		--pgxn | -px)
			PGXN='y'
			;;
		--drop | -d)
			DROPDATABASE='y'
			;;
		--packages | -p)
			WITHPACKAGES='y'
			;;
		-h | -? | --help)
			# Show help text and leave the script
			echo "$HELPTEXT"
			exit 1
			;;
	  *)
			if [ "$arg" != --* ] || [ "$arg" != -* ] ; then
				case "$i" in
					1)
						PGXN=$1
						;;
					2)
						DROPDATABASE=$2
						;;
					3)
						WITHPACKAGES=$3
						;;
				esac
			else
				echo "Parameters not found: $HELPTEXT; exiting now!"
				exit 1
			fi
		;;
	esac

	((i=i+1))
done

if (( $# == 0 )); then
	echo "Using default settings: No pgxn.org release, database kept, additional not packages installed."
fi

# Copy the build.cfg.example to build.cfg and edit the configuration to match your nees
# Include the local configuration
source ./build.cfg

# Starts with the script to create all objects
# Define output file
EXTENSION=$(grep -m 1 '"name":' META.json | \
  sed -e 's/[[:space:]]*"name":[[:space:]]*"\([^"]*\)",/\1/')

EXTVERSION=$(grep -m 1 '"version":' META.json | \
  sed -e 's/[[:space:]]*"version":[[:space:]]*"\([^"]*\)",/\1/')

# Folders
UNINSTDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/sql/out/uninstall"
VERSIONDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/sql/out/versions"
TESTDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/test/sql/out"
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/sql"
RELEASEDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/releases"

# Uninstall file with drop statements
FILENAME="$UNINSTDIR/$EXTENSION""_uninstall--$EXTVERSION.sql"

# Always start with an empty file
truncate -s 0 $FILENAME

echo '/*** uninstall file to drop all objects created by the extension pgsql_tweaks ***/' >> $FILENAME
echo '' >> $FILENAME
echo 'DO $$' >> $FILENAME
echo 'DECLARE' >> $FILENAME
echo '	t_schemaname TEXT;' >> $FILENAME
echo '	i_count INTEGER;' >> $FILENAME
echo 'BEGIN' >> $FILENAME
echo '' >> $FILENAME

echo "SELECT DISTINCT routine_schema" >> $FILENAME
echo "INTO t_schemaname" >> $FILENAME
echo "FROM information_schema.routines" >> $FILENAME
echo "WHERE specific_catalog = current_database()" >> $FILENAME
echo "	AND routine_name IN" >> $FILENAME
echo "		(" >> $FILENAME
echo "			'gap_fill'," >> $FILENAME
echo "			'replace_latin1'" >> $FILENAME
echo "		)" >> $FILENAME
echo ";" >> $FILENAME
echo '' >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_empty(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_empty_b(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(BIGINT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(INTEGER[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(SMALLINT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(REAL[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(DOUBLE PRECISION[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(NUMERIC[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(BIGINT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(INTEGER[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(SMALLINT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(REAL[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(DOUBLE PRECISION[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(NUMERIC[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(TEXT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(BIGINT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(INTEGER[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(SMALLINT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(REAL[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(DOUBLE PRECISION[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(NUMERIC[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(TEXT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(BIGINT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(INTEGER[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(NUMERIC[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(REAL[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(DOUBLE PRECISION[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(SMALLINT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(TEXT[], BOOLEAN)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(SMALLINT[], BOOLEAN)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(INTEGER[], BOOLEAN)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(BIGINT[], BOOLEAN)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(NUMERIC[], BOOLEAN)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(REAL[], BOOLEAN)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(DOUBLE PRECISION[], BOOLEAN)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(DATE[], BOOLEAN)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(TIMESTAMP[], BOOLEAN)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(TIMESTAMP WITH TIME ZONE[], BOOLEAN)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.to_unix_timestamp(TIMESTAMP WITH TIME ZONE)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.to_unix_timestamp(TIMESTAMP)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.datetime_de(TIMESTAMP WITH TIME ZONE,BOOLEAN)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.date_de(DATE)';" >> $FILENAME
echo "EXECUTE 'DROP AGGREGATE IF EXISTS ' || t_schemaname || '.gap_fill(ANYELEMENT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.gap_fill_internal(ANYELEMENT, ANYELEMENT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_latin1(TEXT, TEXT[], TEXT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_latin1(TEXT, TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_latin1(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_encoding(TEXT, TEXT[], TEXT[])';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_encoding(TEXT, TEXT, TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_encoding(TEXT, TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.return_not_part_of_encoding(TEXT, TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.return_not_part_of_latin1(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_latin1(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_encoding(TEXT, TEXT, TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_encoding(TEXT, TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.pg_schema_size(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_bigint(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_integer(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_smallint(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_numeric(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_real(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_double_precision(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_boolean(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_json(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_timestamp(TEXT, TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_timestamp(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_time(TEXT, TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_time(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_date(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_date(TEXT, TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_json(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_jsonb(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_hex(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_uuid(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.hex2bigint(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_bigint_array(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_integer_array(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_smallint_array(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_text_array(TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.get_markdown_doku_by_schema(TEXT, TEXT)';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_functions';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_foreign_keys';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_db_views';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_table_matview_infos';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_object_ownership';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_bloat_info';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_unused_indexes';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_partitioned_tables_infos';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_table_bloat';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_active_locks';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_missing_indexes';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_role_permissions';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_role_infos';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_query_times';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_query_average_time_in_seconds';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_time_consuming_queries';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_memory_usage_queries';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_called_queries';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_rows_returned_queries';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_shared_block_hits_queries';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_block_writes_queries';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_wal_records_generated_queries';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_query_activity';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_wal';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_wal_archiving';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_active_locks';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_replication';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_database_conflicts';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_blocked_and_blocking_activity';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_follower_wal_status';" >> $FILENAME
echo "EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_vacuum';" >> $FILENAME
echo "EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.role_inheritance(TEXT)';" >> $FILENAME
echo '' >> $FILENAME
echo 'WITH schema_objects AS'  >> $FILENAME
echo '	('  >> $FILENAME
echo '		SELECT c.oid'  >> $FILENAME
echo '		, nspname AS schema_name'  >> $FILENAME
echo '		FROM pg_namespace AS n'  >> $FILENAME
echo '		INNER JOIN pg_authid AS a'  >> $FILENAME
echo '			ON n.nspowner = a.oid'  >> $FILENAME
echo '		INNER JOIN pg_class AS c'  >> $FILENAME
echo '			ON n.oid = c.relnamespace'  >> $FILENAME
echo '		WHERE nspname = t_schemaname'  >> $FILENAME
echo '		UNION ALL'  >> $FILENAME
echo '		SELECT pt.oid'  >> $FILENAME
echo '			, nspname AS schema_name'  >> $FILENAME
echo '		FROM pg_namespace AS n'  >> $FILENAME
echo '		INNER JOIN pg_authid AS a'  >> $FILENAME
echo '			ON n.nspowner = a.oid'  >> $FILENAME
echo '		INNER JOIN pg_type AS pt'  >> $FILENAME
echo '			ON n.oid = pt.typnamespace'  >> $FILENAME
echo '		WHERE nspname = t_schemaname'  >> $FILENAME
echo '		UNION ALL'  >> $FILENAME
echo '		SELECT pc.oid'  >> $FILENAME
echo '			, nspname AS schema_name'  >> $FILENAME
echo '		FROM pg_namespace AS n'  >> $FILENAME
echo '		INNER JOIN pg_authid AS a'  >> $FILENAME
echo '			ON n.nspowner = a.oid'  >> $FILENAME
echo '		INNER JOIN pg_proc AS pc'  >> $FILENAME
echo '			ON n.oid = pc.pronamespace'  >> $FILENAME
echo '		WHERE nspname = t_schemaname'  >> $FILENAME
echo '	)'  >> $FILENAME
echo 'SELECT count(*)'  >> $FILENAME
echo 'INTO i_count'  >> $FILENAME
echo 'FROM schema_objects'  >> $FILENAME
echo ';' >> $FILENAME
echo '' >> $FILENAME
echo 'IF i_count = 0 THEN' >> $FILENAME
echo "	EXECUTE 'DROP SCHEMA ' || t_schemaname;" >> $FILENAME
echo 'END IF;' >> $FILENAME
echo '' >> $FILENAME
echo 'END;' >> $FILENAME
echo '$$;' >> $FILENAME

DROPFILE=$FILENAME


# Installation file
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/sql"
FILENAME="$VERSIONDIR/$EXTENSION--$EXTVERSION.sql"

# Array with all file names
declare -a SQLFILES=(
  "function_is_date"
  "function_is_time"
  "function_is_timestamp"
  "function_is_numeric"
  "function_is_bigint"
  "function_is_integer"
  "function_is_smallint"
  "function_is_real"
  "function_is_double_precision"
  "function_is_boolean"
  "function_is_json"
  "function_is_jsonb"
  "function_is_uuid"
  "function_pg_schema_size"
  "function_is_encoding"
  "function_is_latin1"
  "function_return_not_part_of_latin1"
  "function_replace_encoding"
  "function_replace_latin1"
  "function_return_not_part_of_encoding"
  "aggregate_function_gap_fill"
  "function_date_de"
  "function_datetime_de"
  "function_to_unix_timestamp"
  "function_is_empty"
  "function_array_max"
  "function_array_min"
  "function_array_avg"
  "function_array_sum"
  "function_array_trim"
  "function_hex2bigint"
  "function_is_hex"
  "function_is_bigint_array"
  "function_is_integer_array"
  "function_is_smallint_array"
  "function_is_text_array"
	"function_get_markdown_doku_by_schema"
	"function_role_inheritance"
	"view_pg_db_views"
	"view_pg_foreign_keys"
	"view_pg_functions"
	"view_pg_table_matview_infos"
	"view_pg_partitioned_tables_infos"
	"view_pg_active_locks"
	"view_pg_object_ownership"
	"view_pg_bloat_info"
	"view_pg_unused_indexes"
	"view_pg_table_bloat"
	"view_pg_missing_indexes"
	"view_pg_role_permissions"
	"view_pg_role_infos"
	"view_statistics_top_ten_query_times"
	"view_statistics_top_ten_query_average_time_in_seconds"
	"view_statistics_top_ten_time_consuming_queries"
	"view_statistics_top_ten_memory_usage_queries"
	"view_statistics_top_ten_called_queries"
	"view_statistics_top_ten_rows_returned_queries"
	"view_statistics_top_ten_shared_block_hits_queries"
	"view_statistics_top_ten_block_writes_queries"
	"view_statistics_top_ten_wal_records_generated_queries"
	"view_statistics_query_activity"
	"view_monitoring_wal"
	"view_monitoring_wal_archiving"
	"view_monitoring_active_locks"
	"view_monitoring_replication"
	"view_monitoring_database_conflicts"
	"view_monitoring_blocked_and_blocking_activity"
	"view_monitoring_follower_wal_status"
	"view_monitoring_vacuum"
  )
arraylength=${#SQLFILES[@]}

# Always start with an empty file
truncate -s 0 $FILENAME

# Add licencse information
echo '/**' >> $FILENAME
echo ' * PostgreSQL pgsql_tweaks extension' >> $FILENAME
echo ' * Licence:    PostgreSQL Licence, see https://raw.githubusercontent.com/sjstoelting/pgsql-tweaks/master/LICENSE.md' >> $FILENAME
echo ' * Author:     Stefanie Janine Stölting <mail@stefanie-stoelting.de>' >> $FILENAME
echo ' * Repository: http://github.com/sjstoelting/pgsql_tweaks/' >> $FILENAME
echo ' * Version:    '$EXTVERSION >> $FILENAME
echo ' */' >> $FILENAME

# Add initial statements
echo '' >> $FILENAME
echo '/*** Initial statements ***/' >> $FILENAME
echo 'SET client_min_messages TO warning;' >> $FILENAME
echo 'SET log_min_messages    TO warning;' >> $FILENAME
echo '' >> $FILENAME

# Schema creation
echo '/*** Create a schema for the extension ***/' >> $FILENAME
echo 'CREATE SCHEMA IF NOT EXISTS pgsql_tweaks;' >> $FILENAME
echo '' >> $FILENAME
echo '/*** Grant rights to role public for all objects ***/' >> $FILENAME
echo 'GRANT USAGE ON SCHEMA pgsql_tweaks TO public;' >> $FILENAME
echo '' >> $FILENAME
echo 'ALTER DEFAULT PRIVILEGES IN SCHEMA pgsql_tweaks GRANT SELECT ON TABLES TO public;' >> $FILENAME
echo '' >> $FILENAME
echo 'ALTER DEFAULT PRIVILEGES IN SCHEMA pgsql_tweaks GRANT EXECUTE ON FUNCTIONS TO public;' >> $FILENAME
echo '' >> $FILENAME
echo '/*** Set search path to have all objects in the schema pgsql_tweaks ***/' >> $FILENAME
echo 'SET search_path TO pgsql_tweaks, public;' >> $FILENAME
echo '' >> $FILENAME

# It is not allowed to drop functions in the script to create the extension
# cat $DROPFILE >> $FILENAME

echo '' >> $FILENAME
echo '/*** files with creation statements ***/' >> $FILENAME
echo '' >> $FILENAME

for (( i=1; i<${arraylength}+1; i++ ));
do
  cat $DIR/${SQLFILES[$i-1]}".sql" >> $FILENAME
  echo '' >> $FILENAME
done # (( i=1; i<${arraylength}+1; i++ ))

# Now the test script has to be generated
# Define output file
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/test/sql"
FILENAME="$TESTDIR/$EXTENSION""_test--$EXTVERSION.sql"

# Always start with an empty file
truncate -s 0 $FILENAME

# Add initial statements
# Timing is only on when not creating versions
if [ "$PGXN" != "y" ]; then
  echo '\timing' >> $FILENAME
fi # [ "$PGXN" != "y" ]

echo 'SET client_min_messages TO warning;' >> $FILENAME
echo 'SET log_min_messages    TO warning;' >> $FILENAME
echo '' >> $FILENAME

echo '/*** files with test statements ***/' >> $FILENAME
echo '' >> $FILENAME
echo 'SET search_path TO pgsql_tweaks;' >> $FILENAME
echo '' >> $FILENAME

for (( i=1; i<${arraylength}+1; i++ ));
do
  echo "SELECT 'Test starting: ${SQLFILES[$i-1]}' AS next_test;"  >> $FILENAME
  cat $DIR/${SQLFILES[$i-1]}".sql" >> $FILENAME
  echo '' >> $FILENAME
done # (( i=1; i<${arraylength}+1; i++ ))


# Create control file
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
FILENAME="$DIR/pgsql_tweaks.control"

# Always start with an empty file
truncate -s 0 $FILENAME

# Control data
echo "# pgsql_tweaks extension" >> $FILENAME
echo "comment = 'Some functions and views for daily usage'" >> $FILENAME
echo "default_version = '$EXTVERSION'" >> $FILENAME
echo "module_pathname = '\$libdir/pgsql_tweaks'" >> $FILENAME
echo "relocatable = true" >> $FILENAME

# Create the test data
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

psql -h $DBHOST -p $DBPORT -X -q -b postgres -c "CREATE DATABASE $DBNAME;"

psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -c "SELECT version ();"

# Create the necessary additional extensions for the tests
if [ "$WITHPACKAGES" = "y" ]; then
    psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
    psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -c "CREATE EXTENSION IF NOT EXISTS pgstattuple;"
		echo "Extensions pg_stat_statements and pgstattuple created"
else
		echo "No extensions created, some functions and views are not created"
fi # [ "$WITHPACKAGES" = "y" ]

psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -f "$DIR/sql/out/versions/pgsql_tweaks--$EXTVERSION.sql"

if [ "$PGXN" = "y" ]; then
  # The result messages and captions are exported in English UTF8 en_EN
    LC_MESSAGES=en_EN psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -f "$DIR/test/sql/out/pgsql_tweaks_test--$EXTVERSION.sql" > "$DIR/test/sql/out/pgsql_tweaks_test--$EXTVERSION.out"
else
  # During development the messages are kept in the local installed language
    psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -f "$DIR/test/sql/out/pgsql_tweaks_test--$EXTVERSION.sql" > "$DIR/test/sql/out/pgsql_tweaks_test--$EXTVERSION.out"
fi # [ "$PGXN" = "y" ]

# Check the statements used in the README file
psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -f "$DIR/test/sql/examples.sql" > "/dev/null"

# Option to keep or drop the database for tests
if [ "$DROPDATABASE" = "y" ]; then
    psql -h $DBHOST -p $DBPORT -X -q -b postgres -c "DROP DATABASE $DBNAME;"
		echo "Database $DBNAME dropped"
else
		echo "Database $DBNAME kept"
fi # [ "$DROPDATABASE" = "y" ]

# Create a documentation  for PGXN, the link differ from GitHun to PGXN
./create_pgxn_doc.sh

# Create a documentation in HTML
./create_html_doc.sh

# Create the PGXN package, output path is users tmp
if [ "$PGXN" = "y" ]; then

	echo "Creating release files in $RELEASEDIR"

	# Create the release directory and the gitignore
	mkdir -p "$RELEASEDIR"
	if [ ! -d "$RELEASEDIR/.gitignore" ]; then
		echo "gitignore in $RELEASEDIR does not exist. Creating it."
		echo "*" > "$RELEASEDIR/.gitignore"
		echo "" >> "$RELEASEDIR/.gitignore"
  fi # [ ! -d "$RELEASEDIR/.gitignore" ]

	rm -f "$RELEASEDIR/pgsql-tweaks-$EXTVERSION.*"
	git archive --format zip --prefix=pgsql-tweaks-$EXTVERSION/ --output "$RELEASEDIR/pgsql-tweaks-v$EXTVERSION.zip" main
	git archive --format tar.gz --prefix=pgsql-tweaks-$EXTVERSION/ --output "$RELEASEDIR/pgsql-tweaks-v$EXTVERSION.tar.gz" main
else
  echo "No pgxn zip file has been created"
fi # [ "$PGXN" = "y" ]

# Unset variables
unset DIR
unset FILENAME
unset i
unset arraylength