dbregionremoval

#!/bin/bash
echo
echo Removing georegions, their associated regions and all links/entities that belong to those.

function runsql {

  if [ "$DRYRUN" = true ]; then
    SQLTORUN="select count(*) $1"
  else
    SQLTORUN="delete $1"
  fi

  echo "Executing: $SQLTORUN";
  mysql --user="$USER" --password="$PASSWORD" --database="$DATABASE" --port "$PORT" -h "$HOST" --execute="$SQLTORUN" 2>&1 | grep -v "can be insecure"
}

function help {
    echo "Usage: removeGeoRegions.sh --user databaseusername --password databasepassword --database databasename --host hostname --removeGeoRegions comma_delimited_geo_region_ids --dryrun"
    exit;
}

while true; do
  case "$1" in
    --help )
    help;
    ;;
    --host ) HOST="$2"; shift 2 ;;
    --port ) PORT="$2"; shift 2 ;;
    --user ) USER="$2"; shift 2 ;;
    --password ) PASSWORD="$2"; shift 2 ;;
    --database ) DATABASE="$2"; shift 2 ;;
    --dryrun ) DRYRUN=true; shift ;;
    --removeGeoRegions ) GEOREGIONSTOREMOVE="$2"; shift 2 ;;
    * ) break ;;
  esac
done

if [ -z ${HOST+x} ]; then
  echo "You must pass a --host option";
  help;
  exit;
fi

if [ -z ${PORT+x} ]; then
  echo "You must pass a --port option";
  help;
  exit;
fi

if [ -z ${USER+x} ]; then
  echo "You must pass a --user option";
  help;
  exit;
fi

if [ -z ${PASSWORD+x} ]; then
  echo "You must pass a --password option";
  help;
  exit;
fi

if [ -z ${DATABASE+x} ]; then
  echo "You must pass a --database option";
  help;
  exit;
fi

if [ -z ${GEOREGIONSTOREMOVE+x} ]; then
  echo "You must pass a --removeGeoRegions option";
  help;
  exit;
fi


if [ "$DRYRUN" = true ]; then
  echo "NOTE: Running as a DRY RUN, this will only SELECT counts from the DB and will not alter anything"
else
  echo "WARNING: This is NOT a dry run, data will be deleted from the DB!";
fi
read -p "This will remove georegions: $GEOREGIONSTOREMOVE from database $DATABASE as well as the associated regions and all data linked to them.
Continue? <y/N> " prompt
if [[ $prompt =~ [yY](es)* ]]; then
  echo "Removing data..."

  # Remove appointments and appointment_service
  runsql "from appointments where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE));"

  # Remove service_accreditation
  runsql "from service_accreditation where service_id in (select id from services where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE)));"

  # Remove services
  runsql "from services where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE));"

  # Remove servicecats
  runsql "from service_categories where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE));"

  # Remove calendar_accreditation
  runsql "from calendar_accreditation where calendar_id in (select id from calendars where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE)));"

  # Remove calendar_service
  runsql "from calendar_service where calendar_id in (select id from calendars where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE)));"

  # Remove counter_user
  runsql "from counter_user where counter_id in (select id from counters where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE)));"

  # Remove favourite_availability_periods
  runsql "from favourite_availability_periods where counter_id in (select id from counters where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE)));"


  # Remove calendars
  runsql "from calendars where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE));"

  # Remove counters
  runsql "from counters where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE));"
  # Remove references for users
  # Special case since we are updating not deleting!

  if [ "$DRYRUN" = true ]; then
    SQLTORUN="select count(*) from users where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE));"
  else
    SQLTORUN="update users set region_id = null where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE));"
  fi
  echo "Executing: $SQLTORUN";
  mysql --user="$USER" --password="$PASSWORD" --database="$DATABASE" --port "$PORT" -h "$HOST" --execute="$SQLTORUN" 2>&1 | grep -v "can be insecure"

  # Remove help section entries
  runsql "from help_section_region_mapping_brands where help_section_region_mappings_id in (select id from help_section_region_mapping where region_id in (select id from regions where geographic_region_id in (1)));"

  runsql "from help_section_region_mapping where region_id in (select id from regions where geographic_region_id in (1));"

  # Remove oauth clients:
  runsql "from oauth_client_scopes where client_id in (select id from oauth_clients where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE)));"

  runsql "from oauth_clients where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE));"
  # Remove accreditations
  runsql "from accreditations where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE));"

  # Remove region_user
  runsql "from region_user where region_id in (select id from regions where geographic_region_id in ($GEOREGIONSTOREMOVE));"

  # Remove regions
  runsql "from regions where geographic_region_id in ($GEOREGIONSTOREMOVE);"


  # Remove geographic_region_user
  runsql "from geographic_region_user where geographic_region_id in ($GEOREGIONSTOREMOVE);"

  # Remove geographic_regions
  runsql "from geographic_regions where id in ($GEOREGIONSTOREMOVE);"

  # Set passwords to null for users that no longer have a georegion or region (disable users):
  # Special case since we are updating not deleting!
  if [ "$DRYRUN" = true ]; then
    SQLTORUN="select count(*) from users where has_regions_scope = 0 and id not in (select user_id from region_user) and id not in (select user_id from geographic_region_user) and deleted_at is null;"
  else
    SQLTORUN="update users set password = null where has_regions_scope = 0 and id not in (select user_id from region_user) and id not in (select user_id from geographic_region_user) and deleted_at is null;"
  fi
  echo "Executing: $SQLTORUN";
  mysql --user="$USER" --password="$PASSWORD" --database="$DATABASE" --port "$PORT" -h "$HOST" --execute="$SQLTORUN" 2>&1 | grep -v "can be insecure"



  exit;
else
  echo "Operation cancelled."
  exit;
fi