/**
 * Encode a location into a latitude and longitude, caching the result.
 *
 * Works by triggering the 'geocode' 'location' plugin
 * hook, and requires a geocoding plugin to be installed.
 *
 * @param string $location The location, e.g. "London", or "24 Foobar Street, Gotham City"
 * @return string|false
 */
function elgg_geocode_location($location)
{
    global $CONFIG;
    if (is_array($location)) {
        return false;
    }
    $location = sanitise_string($location);
    // Look for cached version
    $query = "SELECT * from {$CONFIG->dbprefix}geocode_cache WHERE location='{$location}'";
    $cached_location = get_data_row($query);
    if ($cached_location) {
        return array('lat' => $cached_location->lat, 'long' => $cached_location->long);
    }
    // Trigger geocode event if not cached
    $return = false;
    $return = elgg_trigger_plugin_hook('geocode', 'location', array('location' => $location), $return);
    // If returned, cache and return value
    if ($return && is_array($return)) {
        $lat = (double) $return['lat'];
        $long = (double) $return['long'];
        // Put into cache at the end of the page since we don't really care that much
        $query = "INSERT DELAYED INTO {$CONFIG->dbprefix}geocode_cache " . " (location, lat, `long`) VALUES ('{$location}', '{$lat}', '{$long}')" . " ON DUPLICATE KEY UPDATE lat='{$lat}', `long`='{$long}'";
        execute_delayed_write_query($query);
    }
    return $return;
}

/**
 * Find an API User's details based on the provided public api key.
 * These users are not users in the traditional sense.
 *
 * @param string $api_key   The API Key
 *
 * @return mixed stdClass representing the database row or false.
 */
function get_api_user($api_key)
{
    $dbprefix = elgg_get_config('dbprefix');
    $api_key = sanitise_string($api_key);
    $query = "SELECT * from {$dbprefix}api_users" . " where api_key='{$api_key}' and active=1";
    return get_data_row($query);
}

 /**
  * Get access collection by its name from database
  * 
  * @param string $name Collection name
  * @return stdClass
  */
 public function getCollectionIdByName($name)
 {
     $name = sanitize_string($name);
     $query = "SELECT * FROM {$this->dbprefix}access_collections\n\t\t\t\t\tWHERE name = '{$name}'";
     $collection = get_data_row($query);
     return $collection ? $collection->id : 0;
 }

/**
 * Create or update the extras table for a given object.
 * Call create_entity first.
 *
 * @param int    $guid        The guid of the entity you're creating (as obtained by create_entity)
 * @param string $title       The title of the object
 * @param string $description The object's description
 *
 * @return bool
 */
function create_object_entity($guid, $title, $description)
{
    global $CONFIG;
    $guid = (int) $guid;
    $title = sanitise_string($title);
    $description = sanitise_string($description);
    $row = get_entity_as_row($guid);
    if ($row) {
        // Core entities row exists and we have access to it
        $query = "SELECT guid from {$CONFIG->dbprefix}objects_entity where guid = {$guid}";
        if ($exists = get_data_row($query)) {
            $query = "UPDATE {$CONFIG->dbprefix}objects_entity\n\t\t\t\tset title='{$title}', description='{$description}' where guid={$guid}";
            $result = update_data($query);
            if ($result != false) {
                // Update succeeded, continue
                $entity = get_entity($guid);
                elgg_trigger_event('update', $entity->type, $entity);
                return $guid;
            }
        } else {
            // Update failed, attempt an insert.
            $query = "INSERT into {$CONFIG->dbprefix}objects_entity\n\t\t\t\t(guid, title, description) values ({$guid}, '{$title}','{$description}')";
            $result = insert_data($query);
            if ($result !== false) {
                $entity = get_entity($guid);
                if (elgg_trigger_event('create', $entity->type, $entity)) {
                    return $guid;
                } else {
                    $entity->delete();
                }
            }
        }
    }
    return false;
}

/**
 * Find an API User's details based on the provided public api key.
 * These users are not users in the traditional sense.
 *
 * @param int    $site_guid The GUID of the site.
 * @param string $api_key   The API Key
 *
 * @return mixed stdClass representing the database row or false.
 */
function get_api_user($site_guid, $api_key)
{
    global $CONFIG;
    $api_key = sanitise_string($api_key);
    $site_guid = (int) $site_guid;
    $query = "SELECT * from {$CONFIG->dbprefix}api_users" . " where api_key='{$api_key}' and site_guid={$site_guid} and active=1";
    return get_data_row($query);
}

 /**
  * Find an API User's details based on the provided public api key.
  * These users are not users in the traditional sense.
  *
  * @param string $api_key Pulic API key
  * @return \hypeJunction\Graph\ApiUser|false
  */
 public function get($api_key)
 {
     $api_key = sanitise_string($api_key);
     $row = get_data_row("SELECT * FROM {$this->dbprefix}api_users\n\t\t\t\t\t\t\t\tWHERE api_key='{$api_key}' AND site_guid={$this->site_guid} AND active=1");
     if (!$row) {
         return false;
     }
     return new ApiUser($row);
 }

/**
 * Return the site via a url.
 *
 * @param string $url The URL of a site
 *
 * @return mixed
 */
function get_site_by_url($url)
{
    global $CONFIG;
    $url = sanitise_string($url);
    $row = get_data_row("SELECT * from {$CONFIG->dbprefix}sites_entity where url='{$url}'");
    if ($row) {
        return get_entity($row->guid);
    }
    return false;
}

 public function testCanGetDataRow()
 {
     $row1 = get_data_row("\n\t\t\tSELECT *\n\t\t\tFROM {$this->prefix}users_entity\n\t\t\tWHERE username = '" . sanitize_string($this->user->username) . "'\n\t\t");
     $row2 = get_data_row("\n\t\t\tSELECT *\n\t\t\tFROM {$this->prefix}users_entity\n\t\t\tWHERE username = ?\n\t\t", null, [$this->user->username]);
     $row3 = get_data_row("\n\t\t\tSELECT *\n\t\t\tFROM {$this->prefix}users_entity\n\t\t\tWHERE username = :username\n\t\t", null, [':username' => $this->user->username]);
     $this->assertIsA($row1, 'stdClass');
     $this->assertEqual($row1->username, $this->user->username);
     $this->assertEqual($row1, $row2);
     $this->assertEqual($row1, $row3);
 }

 /**
  * Load a key
  *
  * @param string $key    Name
  * @param int    $offset Offset
  * @param int    $limit  Limit
  *
  * @return string
  */
 public function load($key, $offset = 0, $limit = null)
 {
     $dbprefix = elgg_get_config('dbprefix');
     $key = sanitise_string($key);
     $row = get_data_row("SELECT * from {$dbprefix}hmac_cache where hmac='{$key}'");
     if ($row) {
         return $row->hmac;
     }
     return false;
 }

 /**
  * Load a key
  *
  * @param string $key    Name
  * @param int    $offset Offset
  * @param int    $limit  Limit
  *
  * @return string
  */
 public function load($key, $offset = 0, $limit = null)
 {
     global $CONFIG;
     $key = sanitise_string($key);
     $row = get_data_row("SELECT * from {$CONFIG->dbprefix}hmac_cache where hmac='{$key}'");
     if ($row) {
         return $row->hmac;
     }
     return false;
 }

 /**
  * Loads a token from the DB
  * 
  * @param string $token Token
  * @return UserToken|false
  */
 public static function load($token)
 {
     $dbprefix = elgg_get_config('dbprefix');
     $token = sanitize_string($token);
     $row = get_data_row("SELECT * FROM {$dbprefix}users_apisessions WHERE token='{$token}'");
     if (!$row) {
         return false;
     }
     return new UserToken($row);
 }

/**
 * Validate a token against a given site.
 *
 * A token registered with one site can not be used from a
 * different apikey(site), so be aware of this during development.
 *
 * @param string $token The Token.
 *
 * @return mixed The user id attached to the token if not expired or false.
 */
function validate_user_token($token)
{
    $dbprefix = elgg_get_config('dbprefix');
    $token = sanitise_string($token);
    $time = time();
    $user = get_data_row("SELECT * from {$dbprefix}users_apisessions\n\t\twhere token='{$token}' and {$time} < expires");
    if ($user) {
        return $user->user_guid;
    }
    return false;
}

/**
 * Return the number of users registered in the system.
 *
 * @param bool $show_deactivated
 * @return int
 */
function get_number_users($show_deactivated = false)
{
    global $CONFIG;
    $access = "";
    if (!$show_deactivated) {
        $access = "and " . get_access_sql_suffix();
    }
    $result = get_data_row("SELECT count(*) as count from {$CONFIG->dbprefix}entities where type='user' {$access}");
    if ($result) {
        return $result->count;
    }
    return false;
}

 /**
  * Get scraped data
  * 
  * @param string $url URL
  * @return array|void
  * @throws \InvalidArgumentException
  */
 public function get($url)
 {
     if (!filter_var($url, FILTER_VALIDATE_URL)) {
         throw new \InvalidArgumentException(__METHOD__ . ' expects a valid URL');
     }
     $data = $this->cache->get(sha1($url));
     if ($data) {
         return $data;
     }
     $dbprefix = elgg_get_config('dbprefix');
     $row = get_data_row("\n\t\t\tSELECT * FROM {$dbprefix}scraper_data\n\t\t\tWHERE url = :url\n\t\t", null, [':url' => $url]);
     return $row ? unserialize($row->data) : null;
 }

 /**
  * Check that token exists and is valid
  *
  * @param string $token
  * @return boolean
  */
 public function validateToken($token)
 {
     $token = $this->db->sanitizeString($token);
     $time = time();
     $dbprefix = $this->db->getTablePrefix();
     $site_guid = $this->config->site_guid;
     $user = get_data_row("SELECT * from {$dbprefix}users_apisessions\n\t\t\twhere token='{$token}' and site_guid={$site_guid} and {$time} < expires");
     if ($user) {
         return true;
     } else {
         return false;
     }
 }

 /**
  * returns the ACL of the site
  *
  * Needs a custom query because of deadloop problems with get_private_setting
  *
  * @return int
  */
 public function getACL()
 {
     if (!isset($this->subsite_acl_cache)) {
         $this->subsite_acl_cache = false;
         $query = "SELECT value";
         $query .= " FROM " . get_config("dbprefix") . "private_settings";
         $query .= " WHERE name = 'subsite_acl'";
         $query .= " AND entity_guid = " . $this->getGUID();
         if ($setting = get_data_row($query)) {
             $this->subsite_acl_cache = $setting->value;
         }
     }
     return $this->subsite_acl_cache;
 }

/**
 * Return the number of users registered in the system.
 *
 * @param bool $show_deactivated Count not enabled users?
 *
 * @return int
 */
function get_number_users($show_deactivated = false)
{
    global $CONFIG;
    $access = "";
    if (!$show_deactivated) {
        $access = "and " . _elgg_get_access_where_sql(array('table_alias' => ''));
    }
    $query = "SELECT count(*) as count\n\t\tfrom {$CONFIG->dbprefix}entities where type='user' {$access}";
    $result = get_data_row($query);
    if ($result) {
        return $result->count;
    }
    return false;
}

 public function testCanGetData()
 {
     $data = [['id' => 1, 'foo' => 'bar1'], ['id' => 2, 'foo' => 'bar2'], ['id' => 3, 'foo' => 'bar1']];
     _elgg_services()->db->addQuerySpec(['sql' => 'SELECT FROM A WHERE foo = :foo', 'params' => [':foo' => 'bar1'], 'results' => function () use($data) {
         $results = [];
         foreach ($data as $elem) {
             if ($elem['foo'] == 'bar1') {
                 $results[] = (object) $elem;
             }
         }
         return $results;
     }]);
     $this->assertEquals([$data[0], $data[2]], get_data('SELECT FROM A WHERE foo = :foo', [$this, 'rowToArray'], [':foo' => 'bar1']));
     $this->assertEquals($data[0], get_data_row('SELECT FROM A WHERE foo = :foo', [$this, 'rowToArray'], [':foo' => 'bar1']));
 }

/**
 * Validate a token against a given site.
 *
 * A token registered with one site can not be used from a
 * different apikey(site), so be aware of this during development.
 *
 * @param string $token     The Token.
 * @param int    $site_guid The ID of the site (default is current site)
 *
 * @return mixed The user id attached to the token if not expired or false.
 */
function validate_user_token($token, $site_guid)
{
    global $CONFIG;
    if (!isset($site_guid)) {
        $site_guid = $CONFIG->site_id;
    }
    $site_guid = (int) $site_guid;
    $token = sanitise_string($token);
    $time = time();
    $user = get_data_row("SELECT * from {$CONFIG->dbprefix}users_apisessions\n\t\twhere token='{$token}' and site_guid={$site_guid} and {$time} < expires");
    if ($user) {
        return $user->user_guid;
    }
    return false;
}

/**
 * Create or update the entities table for a given group.
 * Call create_entity first.
 *
 * @param int    $guid        GUID
 * @param string $name        Name
 * @param string $description Description
 *
 * @return bool
 */
function create_group_entity($guid, $name, $description)
{
    global $CONFIG;
    $guid = (int) $guid;
    $name = sanitise_string($name);
    $description = sanitise_string($description);
    $row = get_entity_as_row($guid);
    if ($row) {
        // Exists and you have access to it
        $exists = get_data_row("SELECT guid from {$CONFIG->dbprefix}groups_entity WHERE guid = {$guid}");
        if ($exists) {
        } else {
        }
    }
    return false;
}