Jon's Radio PHP / MySQL

<?php

function gen_uuid($use_dashes = false)
{
    // Re-seed rand with updated time & unique-ish user info to delay introduction of duplicates in v4uuid
    // It might be a good idea to salt the client address, but this is not a high-security application
    mt_srand(crc32(serialize([microtime(true), getClientAddress(), 'ETC'])));

    return sprintf( $use_dashes ? '%04x%04x-%04x-%04x-%04x-%04x%04x%04x' : '%04x%04x%04x%04x%04x%04x%04x%04x',
/*time_low*/        mt_rand(0, 0xffff), mt_rand(0, 0xffff),
/*time_mid*/        mt_rand(0, 0xffff),
/*time_hi_ver*/     mt_rand(0, 0xffff) | 0x4000,
/*clk_seq_low_hi*/  mt_rand(0, 0xffff) | 0x8000,
/*node*/            mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
    );
}

function getClientAddress()
{
    $ip = null;
    if (!empty($_SERVER['HTTP_CLIENT_IP']))   //check ip from share internet
    {
      $ip = $_SERVER['HTTP_CLIENT_IP'];
    }
    elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))   //to check ip is pass from proxy
    {
      $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
    }
    else
    {
      $ip = $_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}

$db_address     = 'localhost';
$db_username    = 'bunbun';
$db_password    = 'tomato';
$db_conn        = null;
$db_cmd         = '';
$db_stmt        = null;

$current_user   = null;


// Establish MySQL DB
try
{
    $db_conn = new PDO('mysql:host=$db_address;charset=utf8mb4', $db_username, $db_password);

    $db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $db_cmd = 'CREATE DATABASE IF NOT EXISTS radio';
    $conn->exec($db_cmd);
    echo 'Radio database created.<br>';

    $db_cmd = 'USE radio';
    $conn->exec($db_cmd);
    echo 'Radio database defaulted.<br>';

    $db_cmd = 'CREATE TABLE IF NOT EXISTS users ('
        .' user_id CHAR(32) PRIMARY KEY,'
        .' username VARCHAR(32) NOT NULL,'
        .' email VARCHAR(1024) NOT NULL,'
        .' active_ip VARCHAR(45)'
        .' pass_hash VARCHAR(255),'
        .' UNIQUE KEY (uuid, username, email)';
    $conn->exec($db_cmd);
    echo 'Users table established.<br>';

    $db_cmd = 'CREATE TABLE IF NOT EXISTS user_mgmt ('
        .' user_id CHAR(32) PRIMARY KEY,'
        .' token CHAR(32) NOT NULL,';
    $conn->exec($db_cmd);
    echo 'User Management table established.<br>';

    $db_cmd = 'CREATE TABLE IF NOT EXISTS rooms ('
        .' room_id CHAR(32) PRIMARY KEY,'
        .' name VARCHAR(32) NOT NULL,'
        .' owner_id CHAR(32) NOT NULL)';
    $conn->exec($db_cmd);
    echo 'Rooms table established.<br>';

    $db_cmd = 'CREATE TABLE IF NOT EXISTS queue ('
        .' queue_id INT AUTO_INCREMENT,'
        .' room_id CHAR(32),'
        .' song_name VARCHAR(64) NOT NULL,'
        .' song_url VARCHAR(64) NOT NULL,'
        .' song_start TIMESTAMP,'
        .' song_added TIMESTAMP.'
        .' requester_id CHAR(32) NOT NULL,'
        .' PRIMARY KEY (id, room_id))';
    $conn->exec($db_cmd);
    echo 'Queue table established.<br>';

    $db_cmd = 'CREATE TABLE IF NOT EXISTS permissions ('
        .' room_id CHAR(32),'
        .' user_id CHAR(32),'
        .' role TINYINT NOT NULL,'
        .' PRIMARY KEY (room_id, user_id))';
    $conn->exec($db_cmd);
    echo 'Permissions table established.<br>';

    $db_cmd = 'SET GLOBAL event_scheduler = ON';
    $conn->exec($db_cmd);
    echo 'Scheduled Events enabled.<br>';

    $db_stmt = $db_conn->prepare('SELECT uuid, username FROM users WHERE active_ip = :active_ip LIMIT 1');
    $db_stmt->bindParam(':active_ip', getClientAddress());

    $db_stmt->execute();

    $user_id = $db_stmt->fetch();

    $current_user = ($user_id ? $user_id : null;
}
catch(PDOException $e)
{
    echo '<br>Database Init Error:<br>Query:  ' . $db_cmd . '<br>    ' . $e->getMessage() . '<br>';
}

function user_register($username, $email, $password, &$user_id)
{
    try
    {
        $user_id = null;

        $db_stmt = $db_conn->prepare('SELECT 1 FROM users WHERE username = :username OR email = :email LIMIT 1');
        $db_stmt->bindParam(':username', $username);
        $db_stmt->bindParam(':email', $email);

        $db_stmt->execute();

        if ($db_stmt->fetch())
            return 'Username or Email is already registered.';

        do
        {
            $user_id = gen_uuid();
            $db_stmt = $db_conn->prepare('SELECT 1 FROM users WHERE user_id = :user_id LIMIT 1');
            $db_stmt->bindParam(':user_id', $user_id);
            $db_stmt->execute();
        }
        while ($db_stmt->fetch());

        $db_stmt = $db_conn->prepare('INSERT INTO users (user_id, username, email, pass_hash) VALUES (:user_id, :username, :email, :pass_hash)');
        $db_stmt->bindParam(':user_id', $user_id);
        $db_stmt->bindParam(':username', $username);
        $db_stmt->bindParam(':email', $email);
        $db_stmt->bindValue(':pass_hash', null, PDO::PARAM_STR); //password_hash($password, PASSWORD_DEFAULT));

        $db_stmt->execute();

        $token = gen_uuid();
        $db_stmt = $db_conn->prepare('INSERT INTO user_mgmt (user_id, token) VALUES (:user_id, :token)');
        $db_stmt->bindParam(':user_id', $user_id);
        $db_stmt->bindParam(':token', $token);

        $db_stmt->execute();

        $headers = 'MIME-Version 1.0' . '\r\n' . 'Content-type:text/html;charset=UTF-8' . '\r\n' . 'From: registration@radiobun.com';
        mail($email, 'Radio Website Registration Email', sprintf('Username: %s\nToken: %s', $username, $token), $headers);

        return null;
    }
    catch(PDOException $e)
    {
        return 'DB Error:<br> Query:  ' . $db_cmd . '<br> Error:  ' . $e->getMessage() . '<br>';
    }
}

function user_setup_password($username, $token, $password)
{
    try
    {
        $db_stmt = $db_conn->prepare('SELECT user_id FROM users WHERE username = :username LIMIT 1');
        $db_stmt->bindParam(':username', $username);

        $db_stmt->execute();

        $user_id = $db_stmt->fetch();
        if (!$user_id)
            return 'Unknown Username';
        else
            $user_id = $user_id['user_id'];

        $db_stmt = $db_conn->prepare('SELECT 1 FROM user_mgmt WHERE user_id = :user_id AND token = :token LIMIT 1');
        $db_stmt->bindParam(':user_id', $user_id);
        $db_stmt->bindParam(':token', $token);

        $db_stmt->execute();

        if (!$db_stmt->fetch())
            return 'Bad Token';

        $db_stmt = $db_conn->prepare('DELETE FROM user_mgmt WHERE user_id = :user_id');
        $db_stmt->bindParam(':user_id', $user_id);

        $db_stmt->execute();

        $db_stmt = $db_conn->prepare('UPDATE users SET password = :password WHERE user_id = :user_id');
        $db_stmt->bindParam(':user_id', $user_id);
        $db_stmt->bindParam(':password', $password);

        $db_stmt->execute();

        user_login($username, $password);

        return null;
    }
    catch(PDOException $e)
    {
        return 'DB Error:<br> Query:  ' . $db_cmd . '<br> Error:  ' . $e->getMessage() . '<br>';
    }
}

function user_login($username, $password)
{
    try
    {
        if (!isset($current_user))
        {
            $db_stmt = $db_conn->prepare('SELECT user_id FROM users WHERE username = :username LIMIT 1');
            $db_stmt->bindParam(':username', $username);

            $db_stmt->execute();

            $user_id = $db_stmt->fetch();
            if (!$user_id)
                return 'Unknown Username';
            else
                $user_id = $user_id['user_id'];

            $db_stmt = $db_conn->prepare('SELECT 1 FROM users WHERE username = :username AND pass_hash = :pass_hash LIMIT 1');
            $db_stmt->bindParam(':username', $username);
            $db_stmt->bindParam(':email', $email);

            $db_stmt->execute();

            if ($db_stmt->fetch())
            {
                $current_user = array('user_id'=>$user_id,'username'=>$username);

                $db_stmt = $db_conn->prepare('UPDATE users SET active_ip = :active_ip WHERE username = :username');
                $db_stmt->bindParam(':username', $username);
                $db_stmt->bindParam(':active_ip', getClientAddress());

                $db_stmt->execute();
            }
            else
            {
                $current_user = null;
            }

            return null;
        }

        return 'User is already logged in';
    }
    catch(PDOException $e)
    {
        return 'DB Error:<br> Query:  ' . $db_cmd . '<br> Error:  ' . $e->getMessage() . '<br>';
    }
}

function user_logout()
{
    try
    {
        if (isset($current_user))
        {
            $db_stmt = $db_conn->prepare('UPDATE users SET active_ip = :active_ip WHERE user_id = :user_id LIMIT 1');
            $db_stmt->bindParam(':user_id', $current_user['user_id']);
            $db_stmt->bindValue(':active_ip', null, PDO::PARAM_STR);

            $db_stmt->execute();

            $current_user = null;

            return null;
        }

        return 'User is not logged in';
    }
    catch(PDOException $e)
    {
        return 'DB Error:<br> Query:  ' . $db_cmd . '<br> Error:  ' . $e->getMessage() . '<br>';
    }
}

function user_get_owned_rooms(&$rooms)
{
    $rooms = null;

    try
    {
        if (!isset($current_user))
            return 'User is not logged in';

        $db_stmt = $db_conn->prepare('SELECT room_id, name FROM rooms WHERE owner_id = :owner_id');
        $db_stmt->bindParam(':owner_id', $current_user['user_id']);

        $db_stmt->execute();

        $rooms = $db_stmt->fetchAll(PDO::FETCH_ASSOC);

        return null;
    }
    catch(PDOException $e)
    {
        return 'DB Error:<br> Query:  ' . $db_cmd . '<br> Error:  ' . $e->getMessage() . '<br>';
    }
}

function room_destroy($room_id)
{
    try
    {
        if (!isset($current_user))
            return 'User is not logged in';

        $db_stmt = $db_conn->prepare('SELECT 1 FROM rooms WHERE room_id = :room_id AND owner_id = :owner_id LIMIT 1');
        $db_stmt->bindParam(':room_id', $room_id);
        $db_stmt->bindParam(':owner_id', $current_user['user_id']);

        $db_stmt->execute();

        if (!$db_stmt->fetch())
            return 'Room not found for user';

        $db_stmt = $db_conn->prepare('DELETE FROM rooms WHERE room_id = :room_id');
        $db_stmt->bindParam(':room_id', $room_id);

        $db_stmt->execute();

        return null;
    }
    catch(PDOException $e)
    {
        return 'DB Error:<br> Query:  ' . $db_cmd . '<br> Error:  ' . $e->getMessage() . '<br>';
    }
}

function room_create($name, &$room_id)
{
    $room_id = null;

    try
    {
        if (!isset($current_user))
            return 'User is not logged in';

        do
        {
            $room_id = gen_uuid();
            $db_stmt = $db_conn->prepare('SELECT 1 FROM rooms WHERE room_id = :room_id AND owner_id = :owner_id LIMIT 1');
            $db_stmt->bindParam(':room_id', $room_id);
            $db_stmt->bindParam(':owner_id', $current_user['user_id']);
            $db_stmt->execute();
        }
        while ($db_stmt->fetch());

        $db_stmt = $db_conn->prepare('INSERT INTO rooms (room_id, name, owner_id) VALUES (:room_id, :name, :owner_id)');
        $db_stmt->bindParam(':room_id', $room_id);
        $db_stmt->bindParam(':name', $name);
        $db_stmt->bindParam(':owner_id', $current_user['user_id']);

        $db_stmt->execute();

        $db_stmt = $db_conn->prepare('INSERT INTO permissions (room_id, user_id, role) VALUES (:room_id, :user_id, :role)');
        $db_stmt->bindParam(':room_id', $room_id);
        $db_stmt->bindParam(':user_id', $current_user['user_id']);
        $db_stmt->bindValue(':role', 1, PDO::PARAM_INT);

        $db_stmt->execute();

        // make room

        return null;
    }
    catch(PDOException $e)
    {
        return 'DB Error:<br> Query:  ' . $db_cmd . '<br> Error:  ' . $e->getMessage() . '<br>';
    }
}

function room_get_songs($room_id, &$songs)
{
    $songs = null;
    try
    {
        if (!isset($current_user))
            return 'User is not logged in';

        $db_stmt = $db_conn->prepare('SELECT q.queue_id AS queue_id, q.song_name AS name, q.song_url AS url, q.song_start AS started, q.song_added AS added, u.username AS requester FROM queue AS q, users AS u WHERE q.room_id = :room_id AND u.user_id = q.requester_id ORDER BY q.song_added DESC');
        $db_stmt->bindParam(':room_id', $room_id);

        $db_stmt->execute();

        $playlist = $db_stmt->fetchAll(PDO::FETCH_ASSOC);

        $songs = ($playlist ? $playlist : null);

        return null;
    }
    catch(PDOException $e)
    {
        return 'DB Error:<br> Query:  ' . $db_cmd . '<br> Error:  ' . $e->getMessage() . '<br>';
    }
}

function room_add_song($room_id, $song_name, $song_url)
{
    try
    {
        if (!isset($current_user))
            return 'User is not logged in';

        $db_stmt = $db_conn->prepare('SELECT role FROM permissions WHERE room_id = :room_id AND user_id = :user_id LIMIT 1');
        $db_stmt->bindParam(':room_id', $room_id);
        $db_stmt->bindParam(':user_id', $current_user['user_id']);

        $db_stmt->execute();

        $role = $db_stmt->fetch();
        $role = ($role ? $role['role'] : 0);

        if ($role < 0)
            return 'User does not have permission for this room';

        .' queue_id INT AUTO_INCREMENT,'
        .' room_id CHAR(32),'
        .' song_name VARCHAR(64) NOT NULL,'
        .' song_url VARCHAR(64) NOT NULL,'
        .' song_start TIMESTAMP,'
        .' song_added TIMESTAMP.'
        .' requester_id CHAR(32) NOT NULL,'

        $db_stmt = $db_conn->prepare('INSERT INTO queue (queue_id, room_id, song_name, song_url, song_start, song_added, requester_id) VALUES (:queue_id, :room_id, :song_name, :song_url, :song_start, UNIX_TIMESTAMP(), :requester_id)');
        $db_stmt->bindValue(':queue_id', null, PDO::PARAM_INT);
        $db_stmt->bindParam(':room_id', $room_id);
        $db_stmt->bindParam(':song_name', $song_name);
        $db_stmt->bindParam(':song_url', $song_url);
        $db_stmt->bindValue(':song_start', null, PDO::PARAM_INT);
        $db_stmt->bindParam(':requester_id', $current_user['user_id']);

        $db_stmt->execute();

        return null;
    }
    catch(PDOException $e)
    {
        return 'DB Error:<br> Query:  ' . $db_cmd . '<br> Error:  ' . $e->getMessage() . '<br>';
    }
}

function room_del_song($room_id, $queue_id)
{
    try
    {
        if (!isset($current_user))
            return 'User is not logged in';

        $db_stmt = $db_conn->prepare('SELECT role FROM permissions WHERE room_id = :room_id AND user_id = :user_id LIMIT 1');
        $db_stmt->bindParam(':room_id', $room_id);
        $db_stmt->bindParam(':user_id', $current_user['user_id']);

        $db_stmt->execute();

        $role = $db_stmt->fetch();
        $role = ($role ? $role['role'] : 0);

        if ($role < 1)
            return 'User does not have permission for this room';

        $db_stmt = $db_conn->prepare('SELECT 1 FROM queue WHERE queue_id = :queue_id AND room_id = :room_id LIMIT 1');
        $db_stmt->bindParam(':queue_id', $queue_id);
        $db_stmt->bindParam(':room_id', $room_id);

        $db_stmt->execute();

        if (!$db_stmt->fetch())
            return 'Song not found in playlist';

        $db_stmt = $db_conn->prepare('DELETE FROM queue WHERE queue_id = :queue_id AND room_id = :room_id');
        $db_stmt->bindParam(':queue_id', $queue_id);
        $db_stmt->bindParam(':room_id', $room_id);

        $db_stmt->execute();

        return null;
    }
    catch(PDOException $e)
    {
        return 'DB Error:<br> Query:  ' . $db_cmd . '<br> Error:  ' . $e->getMessage() . '<br>';
    }
}

// Shutdown DB Connection
$db_stmt    = null;
$db_cmd     = null;
$db_conn    = null;
?>