#!/bin/sh

if [ $# -ne 4 ]; then
  echo "Usage: $0 check|run user database password"
  exit 1
fi

mode="$1"
dbuser="$2"
database="$3"
dbpass="$4"

if [ "$mode" != check ] && [ "$mode" != run ]; then
  echo "Choose check or run in first argument"
  exit 1
fi

MYSQL="/usr/bin/mysql -u $dbuser --password=$dbpass"

if ! $MYSQL "$database" -e "select null" > /dev/null; then
  echo "Cannot connect to the database"
  exit 1
fi

if [ "$mode" = check ]; then
  mysql -N -u"${dbuser}" --password="${dbpass}" << EOF
SELECT "show default_character_set_name, utf8 is correct:";
SELECT default_character_set_name db_charset FROM information_schema.schemata WHERE schema_name='$database';
SELECT "show number of wrong character_set_name/collation_name columns, 0 is correct:";
SELECT count(*) FROM information_schema.columns WHERE table_schema = '$database' AND (data_type IN ('text','varchar','longtext'))  AND ( UPPER(character_set_name)!='UTF8' OR collation_name!='utf8_bin');
EOF
  exit 0
fi

$MYSQL -e "alter database $database collate utf8_bin"

$MYSQL -N -e "SELECT TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE FROM information_schema.COLUMNS WHERE table_schema = '$database' AND data_type IN ('text', 'varchar', 'longtext') AND collation_name != 'utf8_bin'" | while read -r table_name column_name column_type is_nullable
do
  column_default=$($MYSQL -N -e "SELECT COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE table_schema = '$database' AND TABLE_NAME='$table_name' AND COLUMN_NAME='$column_name'")
  opt=""
  if [ "$is_nullable" = "NO" ]; then
    opt="NOT NULL"
  fi
  if [ "$column_default" = "NO" ]; then
    column_default="''"
  fi
  if [ "$column_default" != "NULL" ]; then
    opt="$opt default $column_default"
  fi

  echo "alter table $table_name modify column $column_name $column_type $opt collate 'utf8_bin';"
  $MYSQL "$database" -e "alter table $table_name modify column $column_name $column_type $opt collate 'utf8_bin'"
done

$MYSQL -N -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE table_schema = '$database' AND table_collation != 'utf8_bin'" | while read -r table_name
do
  echo "alter table $table_name collate 'utf8_bin';"
  $MYSQL "$database" -e "alter table $table_name collate 'utf8_bin';"
done
