<?php

/* Replace with your connection parameters & database name */

$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'test';

$pdo = new PDO("mysql:host=$hostname", $username, $password);

$converter = new Converter($pdo, $database);
echo $converter->getSQL();

class Converter
{
    /**
     * @var PDO
     */
    private $pdo;

    /**
     * @var string
     */
    private $schemaName;

    /**
     * @param PDO    $pdo
     * @param string $schemaName
     */
    public function __construct(PDO $pdo, $schemaName)
    {
        $this->pdo        = $pdo;
        $this->schemaName = $schemaName;
    }

    /**
     * @return string
     */
    public function getSQL()
    {
        $tables  = $this->loadSchemaInfo('TABLES');
        $columns = $this->loadSchemaInfo('COLUMNS');

        $changeset = [];

        foreach ($tables as $row) {
            $table = $row['TABLE_NAME'];
            $collation = $row['TABLE_COLLATION'];
            $collation = $this->convertCollation($collation);

            if ($collation === null) {
                continue;
            }

            $changeset[$table][] = "COLLATE $collation";
        }

        foreach ($columns as $row) {
            $table = $row['TABLE_NAME'];
            $column = $row['COLUMN_NAME'];
            $collation = $row['COLLATION_NAME'];
            $collation  = $this->convertCollation($collation);

            if ($collation === null) {
                continue;
            }

            $definition = $this->getColumnDefinition($row);

            $changeset[$table][] = "MODIFY COLUMN $column $definition COLLATE $collation";
        }

        $sql = '';

        foreach ($changeset as $table => $changes) {
            $lastKey = count($changes) - 1;

            $sql .= "ALTER TABLE $table" . PHP_EOL;

            foreach ($changes as $key => $change) {
                $sql .= "  $change";
                $sql .= ($key === $lastKey) ? ';' : ',';
                $sql .= PHP_EOL;
            }

            $sql .= PHP_EOL;
        }

        return $sql;
    }

    /**
     * @param string $name
     *
     * @return array
     */
    private function loadSchemaInfo($name)
    {
        $statement = $this->pdo->prepare("SELECT * FROM INFORMATION_SCHEMA.$name where table_schema = ?");
        $statement->execute([$this->schemaName]);

        return $statement->fetchAll();
    }

    /**
     * @param string|null $collation
     *
     * @return string|null
     */
    private function convertCollation($collation)
    {
        if ($collation === null) {
            return null;
        }

        $collation = preg_replace('/^utf8_/', 'utf8mb4_', $collation, -1, $count);

        if ($count !== 1) {
            return null;
        }

        return $collation;
    }

    /**
     * @param array $column
     *
     * @return string
     */
    private function getColumnDefinition(array $column)
    {
        $definition = $column['COLUMN_TYPE'];

        if ($column['IS_NULLABLE'] === 'YES') {
            $definition .= ' NULL';
        } else {
            $definition .= ' NOT NULL';
        }

        if ($column['COLUMN_DEFAULT'] !== null) {
            $definition .= ' DEFAULT ' . $column['COLUMN_DEFAULT'];
        }

        return $definition;
    }
}