#!/bin/bash

# Database migration script for WordPress
# Currently this script migrates a local db to a remote db
# Requirements:
#  -  SSH acccess using private key (no password)
#  -  .env file must be used with the below variables populated
#  -  .env file must also exist on the remote server with the db information populated
#  -  ./boot-env.php must exist in the root of the repo
#
# Author: Jasand Pereza


LOCAL_DB_NAME=$(php ./boot-env.php DB_NAME);
LOCAL_DB_USER=$(php ./boot-env.php DB_USER);
LOCAL_DB_PASS=$(php ./boot-env.php DB_PASS);
LOCAL_DOMAIN_NAME=$(php ./boot-env.php LOCAL_DOMAIN_NAME);
DB_TABLE_PREFIX=$(php ./boot-env.php DB_TABLE_PREFIX);
DB_TABLE_PREFIX="${DB_TABLE_PREFIX/wp_/wp}";
PATH_REMOTE_BOOT_ENV=$(php ./boot-env.php PATH_REMOTE_BOOT_ENV);
SSH_REMOTE_HOST=$(php ./boot-env.php SSH_REMOTE_HOST);
SSH_REMOTE_USER=$(php ./boot-env.php SSH_REMOTE_USER);
DOMAIN_NAME_REMOTE=$(php ./boot-env.php DOMAIN_NAME_REMOTE);

echo 'exporting local database...';

mysqldump --user="$LOCAL_DB_USER" --password="$LOCAL_DB_PASS" $LOCAL_DB_NAME --result-file=tempdb.sql &>/dev/null

IMPORT_STRING="
SET @from='://$LOCAL_DOMAIN_NAME';
SET @to='://$DOMAIN_NAME_REMOTE';
SET @prefix='$DB_TABLE_PREFIX';
SET @s1 = CONCAT('UPDATE ', @prefix, '_posts SET post_content = replace(post_content, \\\"', @from,'\\\", \\\"', @to,'\\\");');
SET @s2 = CONCAT('UPDATE ', @prefix, '_options SET option_value = replace(option_value, \\\"', @from,'\\\", \\\"', @to,'\\\") WHERE option_name=\\\"home\\\" OR option_name = \\\"siteurl\\\"');
SET @s3 = CONCAT('UPDATE ', @prefix, '_postmeta SET meta_value = replace(meta_value, \\\"', @from,'\\\", \\\"', @to,'\\\");');
SET @s4 = CONCAT('UPDATE ', @prefix, '_posts SET guid = replace(guid, \\\"', @from,'\\\", \\\"', @to,'\\\");');
PREPARE s1 FROM @s1;
EXECUTE s1;
DEALLOCATE PREPARE s1;
PREPARE s2 FROM @s2;
EXECUTE s2;
DEALLOCATE PREPARE s2;
PREPARE s3 FROM @s3;
EXECUTE s3;
DEALLOCATE PREPARE s3;
PREPARE s4 FROM @s4;
EXECUTE s4;
DEALLOCATE PREPARE s4;";


echo 'importing local database on remote server...';
echo $IMPORT_STRING > ./import.sql;
ssh $SSH_REMOTE_USER@$SSH_REMOTE_HOST  -t 'eval $(php '$PATH_REMOTE_BOOT_ENV');' < ./tempdb.sql &>/dev/null

echo 'running migrate script on remote sever...';
ssh $SSH_REMOTE_USER@$SSH_REMOTE_HOST -t 'eval $(php '$PATH_REMOTE_BOOT_ENV');' < ./import.sql &>/dev/null

rm ./tempdb.sql
rm ./import.sql

echo 'done.';
echo 'NOTE: You may want check to insure the database was indeed migrated. Verbosity in this script is turned off by default';