#!/bin/bash
echo "Transform database collate UTF-8 to C in order to fix btree indexes for where-like filter"

export DATABASE=$1
export DATABASE_UTF8=${DATABASE}_utf8
export DATABASE_C=${DATABASE}_c
export DATABASE_OWNER=$2

if [[ $DATABASE == "" ]]; then
    echo "Database paremeter is required."
    echo "Uses db_utf8_to_c.sh DATABASE OWNER"
    exit -2
fi
if [[ $DATABASE_OWNER == "" ]]; then
    echo "Owner parameter is required."
    echo "Uses db_utf8_to_c.sh DATABASE OWNER"
    exit -2
fi

echo "Transform database UTF-8 to C:"${DATABASE}

echo "Creating backup"
pg_dump ${DATABASE} > ${DATABASE_UTF8}
result=$?
if [[ $result != 0 ]]; then
    echo "Failed to create backup"
    exit $result
fi

echo "Creating empty database"
createdb --owner=$DATABASE_OWNER --lc-collate='C' --encoding='unicode' -T template0 ${DATABASE_C}
result=$?
if [[ $result != 0 ]]; then
    echo "Failed to create new database"
    exit $result
fi

echo "Restoring database"
psql -d ${DATABASE_C} -qf ${DATABASE_UTF8} > ${DATABASE_C}_restore.out
result=$?
if [[ $result != 0 ]]; then
    echo "Failed to restore database"
    exit $result
fi

echo "Renamed of databases"
psql postgres -c "BEGIN;ALTER DATABASE ${DATABASE} RENAME TO ${DATABASE_UTF8};ALTER DATABASE ${DATABASE_C} RENAME TO ${DATABASE};COMMIT;"
result=$?
if [[ $result != 0 ]]; then
    echo "Failed to rename database"
    exit $result
fi
echo "*************Success***************"

echo "Verify slow results for ${DATABASE_UTF8}:"
psql ${DATABASE_UTF8} -c "SHOW LC_COLLATE"
# First time creates buffers
psql ${DATABASE_UTF8} -c "SELECT id, url FROM ir_attachment WHERE url::text LIKE '/web/content/%-d6fee47/web.assets_backend%%.css'" > /dev/null 2>&1 || true
psql ${DATABASE_UTF8} -c "EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT id, url FROM ir_attachment WHERE url::text LIKE '/web/content/%-d6fee47/web.assets_backend%%.css'" > ${DATABASE_UTF8}_query.out || true
cat ${DATABASE_UTF8}_query.out

echo "Verify faster results for ${DATABASE}:"
psql ${DATABASE} -c "SHOW LC_COLLATE"
# First time creates buffers
psql ${DATABASE} -c "SELECT id, url FROM ir_attachment WHERE url::text LIKE '/web/content/%-d6fee47/web.assets_backend%%.css'" > /dev/null 2>&1 || true
psql ${DATABASE} -c "EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT id, url FROM ir_attachment WHERE url::text LIKE '/web/content/%-d6fee47/web.assets_backend%%.css'" > ${DATABASE_C}_query.out || true
cat ${DATABASE_C}_query.out