#!/usr/bin/env bash
# Copyright (c) 2000-2014 Synology Inc. All rights reserved.

if [ -z "$UPGRADE_LIB_PATH" ]; then return; fi

if [ ! -z "$SYNO_LIBSYNOINDEXUTILS_DB_MIGRATION_LIB_DB_SH" ]; then return; fi
SYNO_LIBSYNOINDEXUTILS_DB_MIGRATION_LIB_DB_SH=yes

# shellcheck source=base.sh
. "${UPGRADE_LIB_PATH}/base.sh"

# Binary
CREATEDB="/usr/bin/createdb"
CREATEUSER="/usr/bin/createuser"
SYNOPSQL="/usr/syno/bin/synopsql"

#######################################
# Create empty database
# Arguments:
#   $1: database
#   $2: encoding. Only support default (ASCII) and UTF8
# Returns:
#   100: (FATAL_ERROR)failed to create database
#######################################
CreateEmptyDb()
{
	local db_name=$1
	local encoding=$2
	local option=()

	if [ "$encoding" = "UTF8" ]; then
		option=(-E utf8 -l en_US.utf8 -T template0)
	fi

	echo "Create database: $db_name"
	if ! $CREATEDB -U $DB_ADMIN "${option[@]}" "$db_name"; then
		echo "Failed to create database" "$db_name"
		return 100
	fi
	return 0
}

#######################################
# Check, create, and initialize the database
# Arguments:
#   $1: database
#   $2: SQL command to check the existence of the database
#   $3: SQL command to initialize the database
# Returns:
#   0: database exists and had been initialized
#   1: database is newly created
#   100: (FATAL_ERROR)failed to create the database or failed to initialize
#######################################
CreateDb()
{
	local db_name=$1
	local test_sql=$2
	local init_sql=$3

	$PSQL -U $DB_ADMIN "$db_name" -c "$test_sql" > /dev/null 2>&1
	local ret=$?
	if [ $ret = 0 ]; then
		return 0
	elif [ $ret = 2 ]; then # Cannot find database
		local encoding=""
		if [ "$db_name" = $MEDIASERVER_DATABASE ]; then
			encoding="UTF8"
		fi
		if ! CreateEmptyDb "$db_name" $encoding; then
			echo "Failed to create database $db_name"
			return 100
		fi
	fi

	# database exists but has not been initialized
	if ! ImportSqlScript "$db_name" "$init_sql"; then
		echo "Failed to initial media database $db_name"
		return 100
	fi
	return 1
}

#######################################
# Check and create the DB's role
# Arguments:
#   $1: role's name
# Returns:
#   None
#######################################
CreateDbUser()
{
	(
	flock $synoindex_create_db_user_lock
	local role=$1

	local user_exists
	user_exists=$(ExecSqlCommandAndCaptureOutput "SELECT count(1) FROM pg_roles WHERE rolname='$role'")

	if [ "$user_exists" = "0" ]; then
		echo "Create pgsql user $role"
		$CREATEUSER -U "$DB_ADMIN" "$role"
	fi
	) {synoindex_create_db_user_lock}>/var/run/synoindex_create_db_user.lock
}

#######################################
# Change the DB's owner
# Arguments:
#   $1: database
#   $2: role's name
# Returns:
#   None
#######################################
ChangeDbOwnerImp()
{
	local db_name=$1
	local role=$2

	echo "Change $db_name owner to $role"
	$SYNOPSQL --change-own "$db_name" "$role"
}

#######################################
# Check and change the DB's owner
# Arguments:
#   $1: database
#   $2: role's name
# Returns:
#   None
#######################################
ChangeDbOwner()
{
	local db_name=$1
	local role=$2

	local current_owner
	current_owner=$(ExecSqlCommandAndCaptureOutput "SELECT pg_catalog.pg_get_userbyid(d.datdba) FROM pg_catalog.pg_database d WHERE d.datname = '$db_name'")
	if [ "$current_owner" != "$role" ]; then
		ChangeDbOwnerImp "$db_name" "$role"
		return 0
	fi

	local wrong_owner_count
	wrong_owner_count=$(ExecSqlInDBAndCaptureOutput "$db_name" "SELECT count(1) FROM pg_tables WHERE schemaname='public' AND tableowner != '$role'")

	if [ "$wrong_owner_count" != "0" ]; then
		ChangeDbOwnerImp "$db_name" "$role"
	fi
}