<?php

/**
 * Class DbUserModel
 *
 * Model for managing MySQL database users
 */
class DbUserModel
{
    /**
     * Get all database users
     * @return array
     */
    public static function getAllUsers()
    {
        $database = DatabaseFactory::getFactory()->getConnection();

        try {
            $sql = "SELECT User, Host FROM mysql.user ORDER BY User, Host";
            $query = $database->prepare($sql);
            $query->execute();

            return $query->fetchAll(PDO::FETCH_OBJ);
        } catch (PDOException $e) {
            return array();
        }
    }

    /**
     * Get user details
     * @param string $username
     * @param string $host
     * @return object|null
     */
    public static function getUserDetails($username, $host)
    {
        $database = DatabaseFactory::getFactory()->getConnection();

        try {
            $sql = "SELECT * FROM mysql.user WHERE User = :username AND Host = :host";
            $query = $database->prepare($sql);
            $query->execute(array(':username' => $username, ':host' => $host));

            return $query->fetch(PDO::FETCH_OBJ);
        } catch (PDOException $e) {
            return null;
        }
    }

    /**
     * Get user privileges
     * @param string $username
     * @param string $host
     * @return array
     */
    public static function getUserPrivileges($username, $host)
    {
        $database = DatabaseFactory::getFactory()->getConnection();

        try {
            // Escape username and host for SHOW GRANTS
            $sql = "SHOW GRANTS FOR " . $database->quote($username) . "@" . $database->quote($host);
            $query = $database->prepare($sql);
            $query->execute();

            $grants = array();
            while ($row = $query->fetch(PDO::FETCH_NUM)) {
                $grants[] = $row[0];
            }

            return $grants;
        } catch (PDOException $e) {
            return array();
        }
    }

    /**
     * Create a new database user
     * @param string $username
     * @param string $password
     * @param string $host
     * @return bool
     */
    public static function createUser($username, $password, $host)
    {
        if (!self::validateUsername($username) || empty($password) || empty($host)) {
            return false;
        }

        $database = DatabaseFactory::getFactory()->getConnection();

        try {
            $sql = "CREATE USER " . $database->quote($username) . "@" . $database->quote($host) .
                   " IDENTIFIED BY " . $database->quote($password);
            $database->exec($sql);

            return true;
        } catch (PDOException $e) {
            return false;
        }
    }

    /**
     * Update user password
     * @param string $username
     * @param string $host
     * @param string $password
     * @return bool
     */
    public static function updateUserPassword($username, $host, $password)
    {
        if (empty($password)) {
            return false;
        }

        $database = DatabaseFactory::getFactory()->getConnection();

        try {
            $sql = "ALTER USER " . $database->quote($username) . "@" . $database->quote($host) .
                   " IDENTIFIED BY " . $database->quote($password);
            $database->exec($sql);

            return true;
        } catch (PDOException $e) {
            return false;
        }
    }

    /**
     * Update user privileges
     * @param string $username
     * @param string $host
     * @param array $privileges
     * @return bool
     */
    public static function updateUserPrivileges($username, $host, $privileges)
    {
        $database = DatabaseFactory::getFactory()->getConnection();

        try {
            $sql = "REVOKE ALL PRIVILEGES, GRANT OPTION FROM " .
                   $database->quote($username) . "@" . $database->quote($host);
            $database->exec($sql);

            if (!empty($privileges) && is_array($privileges)) {
                if (in_array('ALL PRIVILEGES', $privileges)) {
                    $sql = "GRANT ALL PRIVILEGES ON *.* TO " .
                           $database->quote($username) . "@" . $database->quote($host);
                    $database->exec($sql);
                } else {
                    $valid_privs = array('SELECT', 'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'DROP', 'ALTER', 'INDEX',
                                         'REFERENCES', 'CREATE TEMPORARY TABLES', 'LOCK TABLES', 'EXECUTE',
                                         'CREATE VIEW', 'SHOW VIEW', 'CREATE ROUTINE', 'ALTER ROUTINE', 'EVENT', 'TRIGGER');
                    $privileges = array_intersect($privileges, $valid_privs);

                    if (!empty($privileges)) {
                        $priv_string = implode(', ', $privileges);
                        $sql = "GRANT " . $priv_string . " ON *.* TO " .
                               $database->quote($username) . "@" . $database->quote($host);
                        $database->exec($sql);
                    }
                }
            }

            $database->exec("FLUSH PRIVILEGES");

            return true;
        } catch (PDOException $e) {
            return false;
        }
    }

    /**
     * Delete a database user
     * @param string $username
     * @param string $host
     * @return bool
     */
    public static function deleteUser($username, $host)
    {
        $database = DatabaseFactory::getFactory()->getConnection();

        try {
            $sql = "DROP USER " . $database->quote($username) . "@" . $database->quote($host);
            $database->exec($sql);

            return true;
        } catch (PDOException $e) {
            return false;
        }
    }

    /**
     * Validate username format
     * @param string $username
     * @return bool
     */
    private static function validateUsername($username)
    {
        return !empty($username) && preg_match('/^[a-zA-Z0-9_]+$/', $username);
    }
}