<?php

$project = "test";
$con=mysqli_connect("localhost","user","password");

function createUserAndDb($con, $proj, $type, $host = "localhost"){
  $password = bin2hex(openssl_random_pseudo_bytes(10));

  $user = "{$proj}-{$type}";
  $userHost = "`{$user}`@`{$host}`";

  $sql="CREATE USER {$userHost} identified by '{$password}'";
  if (mysqli_query($con,$sql)){
    echo "Created $user with $password".PHP_EOL;
  }else{
    echo mysqli_error($con).PHP_EOL;
  }

  $sql="CREATE DATABASE `{$user}`";
  if (mysqli_query($con,$sql)){
    echo "Created database $user".PHP_EOL;
  }else{
    echo 'database create error '.mysqli_error($con).' '.$sql.PHP_EOL;
  }

  mysqli_query($con,"use `{$user}`");

  $sql= "GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `{$user}`.* TO {$userHost}" ;
  if (mysqli_query($con,$sql)){
    echo "Grant for db {$user}.* to  {$userHost}".PHP_EOL;
    mysqli_query($con,"FLUSH PRIVILEGES");
  }else{
    echo 'error grant premissions '.mysqli_error($con).PHP_EOL.$sql.PHP_EOL;
  }
};

createUserAndDb($con, $project, 'dev');
createUserAndDb($con, $project, 'prod');