<?php
/*
 * This file is part of FEED ON FEEDS - http://feedonfeeds.com/
 *
 * fof-db.php - (nearly) all of the DB specific code
 *
 * Copyright (C) 2004-2007 Stephen Minutillo steve@minutillo.com http://minutillo.com/steve/
 *           (C) 2009-2014 Vitaliy Filippov vitalif@mail.ru http://yourcmc.ru/wiki/
 *
 * Distributed under the GPL - see LICENSE
 */

$FOF_FEED_TABLE = FOF_FEED_TABLE;
$FOF_ITEM_TABLE = FOF_ITEM_TABLE;
$FOF_ITEM_TAG_TABLE = FOF_ITEM_TAG_TABLE;
$FOF_SUBSCRIPTION_TABLE = FOF_SUBSCRIPTION_TABLE;
$FOF_TAG_TABLE = FOF_TAG_TABLE;
$FOF_USER_TABLE = FOF_USER_TABLE;

////////////////////////////////////////////////////////////////////////////////
// Utilities
////////////////////////////////////////////////////////////////////////////////

function fof_db_connect()
{
    global $fof_connection;
    $fof_connection = mysqli_connect('p:'.FOF_DB_HOST, FOF_DB_USER, FOF_DB_PASS, FOF_DB_DBNAME)
        or die("<br><br>Cannot connect to database.  Please update configuration in <b>fof-config.php</b>. MySQL says: <i>" . mysqli_connect_error() . "</i>");
    fof_db_query("SET NAMES utf8");
}

function fof_db_optimize()
{
    /* Может быть, проблемы из-за этого? */
    /*global $FOF_FEED_TABLE, $FOF_ITEM_TABLE, $FOF_ITEM_TAG_TABLE, $FOF_SUBSCRIPTION_TABLE, $FOF_TAG_TABLE, $FOF_USER_TABLE;*/
    /*fof_db_query("optimize table $FOF_FEED_TABLE, $FOF_ITEM_TABLE, $FOF_ITEM_TAG_TABLE, $FOF_SUBSCRIPTION_TABLE, $FOF_TAG_TABLE, $FOF_USER_TABLE");*/
}

function fof_safe_query(/* $query, [$args...]*/)
{
    global $fof_connection;
    $args = func_get_args();
    $query = array_shift($args);
    if (isset($args[0]) && is_array($args[0]))
        $args = $args[0];
    foreach ($args as &$a)
        $a = $fof_connection->real_escape_string($a);
    $query = vsprintf($query, $args);
    return fof_db_query($query);
}

function fof_db_query($sql, $live=0)
{
    global $fof_connection;

    list($usec, $sec) = explode(" ", microtime());
    $t1 = (float)$sec + (float)$usec;

    $result = $fof_connection->query($sql);

    $num = $affected = 0;
    if (is_object($result))
        $num = $result->num_rows;
    if ($result)
        $affected = $fof_connection->affected_rows;

    list($usec, $sec) = explode(" ", microtime());
    $t2 = (float)$sec + (float)$usec;
    $elapsed = $t2 - $t1;
    $logmessage = sprintf("%.3f: [%s] (%d / %d)", $elapsed, $sql, $num, $affected);
    fof_log($logmessage, "query");

    if ($live)
    {
        return $result;
    }
    else
    {
        if ($fof_connection->errno)
            fof_die_mysql_error("Cannot run query '$sql': ".$fof_connection->errno.": ".$fof_connection->error);
        return $result;
    }
}

function fof_num_rows($result)
{
    return $result ? $result->num_rows : 0;
}

function fof_insert_id()
{
    global $fof_connection;
    return $fof_connection->insert_id;
}

function fof_die_mysql_error($error)
{
    ob_start();
    print_r(debug_backtrace());
    $trace = ob_get_contents();
    ob_end_clean();
    $error .= "\n".$trace;
    fof_log($error, 'error');
    $error = "SQL error. Have you run <a href=\"install.php\"><code>install.php</code></a> to create or upgrade your installation?\n<pre>$error</pre>";
    die($error);
}

function fof_db_get_row($result)
{
    return $result->fetch_assoc();
}

function fof_db_get_value($sql)
{
    if (!($result = fof_db_query($sql)) ||
        !($row = fof_db_get_row($result)))
        return NULL;
    return reset($row);
}

////////////////////////////////////////////////////////////////////////////////
// Feed level stuff
////////////////////////////////////////////////////////////////////////////////

function fof_db_feed_mark_cached($feed_id)
{
    global $FOF_FEED_TABLE;

    $result = fof_safe_query("update $FOF_FEED_TABLE set feed_cache_date = %d where feed_id = %d", time(), $feed_id);
}

function fof_db_feed_mark_attempted_cache($feed_id)
{
    global $FOF_FEED_TABLE;

    $result = fof_safe_query("update $FOF_FEED_TABLE set feed_cache_attempt_date = %d where feed_id = %d", time(), $feed_id);
}

function fof_db_feed_update_metadata($feed_id, $url, $title, $link, $description, $image, $image_cache_date)
{
    global $FOF_FEED_TABLE;

    $sql = "update $FOF_FEED_TABLE set feed_url = '%s', feed_title = '%s', feed_link = '%s', feed_description = '%s'";
    $args = array($url, $title, $link, $description);

    if($image)
    {
        $sql .= ", feed_image = '%s' ";
        $args[] = $image;
    }
    else
    {
        $sql .= ", feed_image = NULL ";
    }

    $sql .= ", feed_image_cache_date = %d ";
    $args[] = $image_cache_date;

    $sql .= "where feed_id = %d";
    $args[] = $feed_id;

    $result = fof_safe_query($sql, $args);
}

function fof_db_get_latest_item_age($user_id)
{
    global $FOF_SUBSCRIPTION_TABLE, $FOF_ITEM_TABLE;
    $result = fof_safe_query(
        "select max(i.item_cached) max_date, i.feed_id id".
        " from $FOF_ITEM_TABLE i, $FOF_SUBSCRIPTION_TABLE s".
        " where s.user_id=%d and s.feed_id=i.feed_id group by i.feed_id", $user_id
    );
    return $result;
}

function fof_db_get_subscriptions($user_id)
{
    global $FOF_FEED_TABLE, $FOF_SUBSCRIPTION_TABLE;

    return fof_safe_query("select * from $FOF_FEED_TABLE, $FOF_SUBSCRIPTION_TABLE where $FOF_SUBSCRIPTION_TABLE.user_id = %d and $FOF_FEED_TABLE.feed_id = $FOF_SUBSCRIPTION_TABLE.feed_id order by feed_title", $user_id);
}

function fof_db_get_feeds()
{
    global $FOF_FEED_TABLE;

    return fof_db_query("select * from $FOF_FEED_TABLE order by feed_title");
}

function fof_db_get_item_by_id($item_id)
{
    global $FOF_ITEM_TABLE;
    if (($result = fof_db_query("select * from $FOF_ITEM_TABLE where item_id=".intval($item_id))) &&
        ($row = fof_db_get_row($result)))
        return $row;
    return NULL;
}

function fof_db_get_item_count($user_id)
{
    global $FOF_ITEM_TABLE, $FOF_SUBSCRIPTION_TABLE;
    return fof_safe_query(
        "select s.feed_id as id, (select count(*) from $FOF_ITEM_TABLE i where i.feed_id=s.feed_id)".
        " as count from $FOF_SUBSCRIPTION_TABLE s where s.user_id = %d", $user_id
    );
}

function fof_db_get_tagged_item_count($user_id, $tag_id)
{
    global $FOF_ITEM_TAG_TABLE;
    return fof_safe_query(
        "select count(*) as count, feed_id as id".
        " from $FOF_ITEM_TAG_TABLE where tag_id = %d and user_id = %d group by id",
        $tag_id, $user_id
    );
}

function fof_db_get_subscribed_users($feed_id)
{
    global $FOF_SUBSCRIPTION_TABLE;

    return fof_safe_query("select user_id from $FOF_SUBSCRIPTION_TABLE where $FOF_SUBSCRIPTION_TABLE.feed_id = %d", $feed_id);
}

function fof_db_is_subscribed($user_id, $feed_url)
{
    global $FOF_FEED_TABLE, $FOF_SUBSCRIPTION_TABLE;

    $result = fof_safe_query("select $FOF_SUBSCRIPTION_TABLE.feed_id from $FOF_FEED_TABLE, $FOF_SUBSCRIPTION_TABLE where feed_url='%s' and $FOF_SUBSCRIPTION_TABLE.feed_id = $FOF_FEED_TABLE.feed_id and $FOF_SUBSCRIPTION_TABLE.user_id = %d", $feed_url, $user_id);

    if (fof_num_rows($result) == 0)
        return false;

    return true;
}

function fof_db_get_feed_by_url($feed_url)
{
    global $FOF_FEED_TABLE;

    $result = fof_safe_query("select * from $FOF_FEED_TABLE where feed_url='%s'", $feed_url);

    return fof_db_get_row($result);
}

function fof_db_get_feed_by_id($feed_id, $user_id = false)
{
    global $FOF_FEED_TABLE, $FOF_SUBSCRIPTION_TABLE;

    $j = "";
    if ($user_id)
        $j = "JOIN $FOF_SUBSCRIPTION_TABLE s ON s.user_id=".intval($user_id)." AND s.feed_id=s.feed_id";
    $result = fof_safe_query("select * from $FOF_FEED_TABLE f $j where f.feed_id=%d", $feed_id);

    return fof_db_get_row($result);
}

function fof_db_add_feed($url, $title, $link, $description)
{
    global $FOF_FEED_TABLE, $FOF_ITEM_TABLE, $FOF_SUBSCRIPTION_TABLE, $fof_connection;

    fof_safe_query("insert into $FOF_FEED_TABLE (feed_url,feed_title,feed_link,feed_description) values ('%s', '%s', '%s', '%s')", $url, $title, $link, $description);

    return fof_insert_id();
}

function fof_db_add_subscription($user_id, $feed_id)
{
    global $FOF_SUBSCRIPTION_TABLE;

    fof_safe_query("insert into $FOF_SUBSCRIPTION_TABLE (feed_id, user_id) values (%d, %d)", $feed_id, $user_id);
}

function fof_db_delete_subscription($user_id, $feed_id)
{
    global $FOF_SUBSCRIPTION_TABLE, $FOF_ITEM_TAG_TABLE, $FOF_ITEM_TABLE;

    fof_safe_query(
        "delete from s, it using $FOF_ITEM_TAG_TABLE it, $FOF_SUBSCRIPTION_TABLE s, $FOF_ITEM_TABLE i".
        " where i.item_id=it.item_id and s.feed_id=%d and i.feed_id=s.feed_id and it.user_id=%d and s.user_id=it.user_id",
        $feed_id, $user_id
    );
}

function fof_db_delete_feed($feed_id)
{
    global $FOF_FEED_TABLE, $FOF_ITEM_TABLE;

    fof_safe_query("delete from $FOF_ITEM_TABLE where feed_id = %d", $feed_id);
    fof_safe_query("delete from $FOF_FEED_TABLE where feed_id = %d", $feed_id);
}


////////////////////////////////////////////////////////////////////////////////
// Item level stuff
////////////////////////////////////////////////////////////////////////////////

function fof_db_find_item($feed_id, $item_guid)
{
    global $FOF_FEED_TABLE, $FOF_ITEM_TABLE, $FOF_SUBSCRIPTION_TABLE, $fof_connection;

    $result = fof_safe_query("select item_id from $FOF_ITEM_TABLE where feed_id=%d and item_guid='%s'", $feed_id, $item_guid);
    $row = fof_db_get_row($result);

    return $row ? $row['item_id'] : NULL;
}

$fof_db_item_fields = array('item_guid', 'item_link', 'item_title', 'item_author', 'item_content', 'item_cached', 'item_published', 'item_updated');
function fof_db_add_item($feed_id, $item)
{
    global $FOF_FEED_TABLE, $FOF_ITEM_TABLE, $FOF_SUBSCRIPTION_TABLE, $fof_connection, $fof_db_item_fields;

    $args = array();
    $sql = "insert into $FOF_ITEM_TABLE (feed_id, ".implode(",", $fof_db_item_fields).") values (".intval($feed_id);
    foreach ($fof_db_item_fields as $k)
    {
        $sql .= ", '%s'";
        $args[] = $item[$k];
    }
    $sql .= ")";
    fof_safe_query($sql, $args);

    return fof_insert_id();
}

function fof_db_get_items($user_id = 1, $feed = NULL, $what = "unread",
    $when = NULL, $start = NULL, $limit = NULL, $order = "desc", $search = NULL)
{
    global $FOF_SUBSCRIPTION_TABLE, $FOF_FEED_TABLE, $FOF_ITEM_TABLE, $FOF_ITEM_TAG_TABLE, $FOF_TAG_TABLE;

    $user_id = intval($user_id);
    $prefs = fof_prefs();
    $offset = $prefs['tzoffset'];
    if (!empty($prefs['dst']))
        $offset += date('I');

    if (!is_null($when) && $when != "")
    {
        if($when == "today")
            $whendate = fof_todays_date();
        else
            $whendate = $when;

        $whendate = explode("/", $whendate);
        $begin = gmmktime(0, 0, 0, $whendate[1], $whendate[2], $whendate[0]) - ($offset * 60 * 60);
        $end = $begin + (24 * 60 * 60);
    }

    $limit_clause = '';
    if (is_numeric($start))
    {
        if (!is_numeric($limit))
            $limit = $prefs["howmany"];
        $limit_clause = " limit $start, $limit ";
    }

    $args = array();
    $select = "SELECT STRAIGHT_JOIN i.* , f.*, s.subscription_prefs ";
    $from = "$FOF_ITEM_TABLE i, $FOF_FEED_TABLE f, $FOF_SUBSCRIPTION_TABLE s ";
    $where = "WHERE s.user_id=$user_id AND s.feed_id=f.feed_id AND f.feed_id=i.feed_id";

    if (!is_null($feed) && $feed != "")
        $where .= sprintf(" AND f.feed_id = %d ", $feed);

    $pubdate = 'i.item_published';

    if ($what != "all")
    {
        $alltags = array();
        foreach (fof_db_get_tags($user_id) as $t) // returned from cache
        {
            $alltags[mb_strtolower($t['tag_name'])] = $t;
        }
        $min_tag = false;
        $min_i = false;
        $tags = preg_split("/[\s,]*,[\s,]*/", $what);
        foreach ($tags as $i => $tag)
        {
            $tag = mb_strtolower(trim($tag));
            if (isset($alltags[$tag]))
            {
                $from = "$FOF_ITEM_TAG_TABLE it$i, $from";
                // feed_id condition is redundant, used to speedup match by feed+tag(s)
                $where .= " AND it$i.feed_id=i.feed_id AND it$i.user_id=$user_id".
                    " AND it$i.item_id=i.item_id AND it$i.tag_id=%d";
                $args[] = $alltags[$tag]['tag_id'];
                if (!$min_tag || $min_tag['count'] > $alltags[$tag]['count'])
                {
                    // For several tags it's better to sort using the most rare one
                    $min_tag = $alltags[$tag];
                    $min_i = $i;
                }
            }
        }
        if ($min_i !== false)
        {
            $pubdate = "it$min_i.item_published";
        }
    }

    if (!is_null($when) && $when != "")
        $where .= sprintf(" AND $pubdate > %d AND $pubdate < %d", $begin, $end);

    if (!is_null($search) && $search != "")
    {
        $where .= " AND (i.item_title like '%%%s%%' OR i.item_content like '%%%s%%')";
        $args[] = $search;
        $args[] = $search;
    }

    $order_by = "order by $pubdate desc $limit_clause ";

    $query = "$select FROM $from $where $order_by";
    $result = fof_safe_query($query, $args);
    if (fof_num_rows($result) == 0)
        return array();

    while ($row = fof_db_get_row($result))
    {
        $row['prefs'] = unserialize($row['subscription_prefs']);
        $array[] = $row;
    }

    $array = fof_multi_sort($array, 'item_published', $order != "asc");

    foreach ($array as $i => $item)
    {
        $ids[] = $item['item_id'];
        $lookup[$item['item_id']] = $i;
        $array[$i]['tags'] = array();
    }

    $items = join($ids, ", ");

    $result = fof_safe_query(
        "select t.tag_name, it.item_id from $FOF_TAG_TABLE t, $FOF_ITEM_TAG_TABLE it".
        " where t.tag_id = it.tag_id and it.item_id in (%s) and it.user_id = %d", $items, $user_id
    );
    while ($row = fof_db_get_row($result))
    {
        $item_id = $row['item_id'];
        $tag = $row['tag_name'];
        $array[$lookup[$item_id]]['tags'][] = $tag;
    }

    return $array;
}

function fof_db_get_item($user_id, $item_id)
{
    global $FOF_FEED_TABLE, $FOF_ITEM_TABLE, $FOF_ITEM_TAG_TABLE, $FOF_TAG_TABLE;

    $query = "select f.feed_image, f.feed_title, f.feed_link, f.feed_description, i.*".
        " from $FOF_FEED_TABLE f, $FOF_ITEM_TABLE i where i.feed_id=f.feed_id and i.item_id = %d";

    $result = fof_safe_query($query, $item_id);

    $item = fof_db_get_row($result);
    $item['tags'] = array();
    if ($user_id)
    {
        $result = fof_safe_query(
            "select t.tag_name from $FOF_TAG_TABLE t, $FOF_ITEM_TAG_TABLE it".
            " where t.tag_id = it.tag_id and it.item_id = %d and it.user_id = %d", $item_id, $user_id
        );
        while ($row = fof_db_get_row($result))
        {
            $item['tags'][] = $row['tag_name'];
        }
    }

    return $item;
}

////////////////////////////////////////////////////////////////////////////////
// Tag stuff
////////////////////////////////////////////////////////////////////////////////

function fof_db_get_subscription_to_tags()
{
    $r = array('filter' => array());
    global $FOF_SUBSCRIPTION_TABLE;
    $result = fof_safe_query("select * from $FOF_SUBSCRIPTION_TABLE");
    while($row = fof_db_get_row($result))
    {
        $prefs = unserialize($row['subscription_prefs']);
        $tags = $prefs['tags'];
        if (!isset($r[$row['feed_id']]))
            $r[$row['feed_id']] = array();
        $r[$row['feed_id']][$row['user_id']] = $tags;
        if (!empty($prefs['filter']))
        {
            if (!isset($r['filter'][$row['feed_id']]))
                $r['filter'][$row['feed_id']] = array();
            $r['filter'][$row['feed_id']][$row['user_id']] = $prefs['filter'];
        }
    }

    return $r;
}

function fof_db_get_subscription_prefs($user_id, $feed_id)
{
    global $FOF_SUBSCRIPTION_TABLE;
    $result = fof_safe_query("select subscription_prefs from $FOF_SUBSCRIPTION_TABLE where feed_id = %d and user_id = %d", $feed_id, $user_id);
    $row = fof_db_get_row($result);
    $prefs = unserialize($row['subscription_prefs']);
    return $prefs;
}

function fof_db_set_subscription_prefs($user_id, $feed_id, $prefs)
{
    global $FOF_SUBSCRIPTION_TABLE;
    return fof_safe_query("update $FOF_SUBSCRIPTION_TABLE set subscription_prefs = '%s' where feed_id = %d and user_id = %d", serialize($prefs), $feed_id, $user_id);
}

// tag feed and all its items
function fof_db_tag_feed($user_id, $feed_id, $tag_id)
{
    $prefs = fof_db_get_subscription_prefs($user_id, $feed_id);
    if(!is_array($prefs['tags']) || !in_array($tag_id, $prefs['tags']))
        $prefs['tags'][] = $tag_id;
    fof_db_set_subscription_prefs($user_id, $feed_id, $prefs);
    fof_db_tag_feed_items($user_id, $feed_id, $tag_id);
}

function fof_db_tag_feed_items($user_id, $feed_id, $tag_id, $since = 0)
{
    global $FOF_ITEM_TAG_TABLE, $FOF_ITEM_TABLE;
    fof_safe_query(
        "insert into $FOF_ITEM_TAG_TABLE (tag_id, user_id, item_id, item_published, feed_id)".
        " select %d, %d, item_id, item_published, feed_id from $FOF_ITEM_TABLE where feed_id=%d".
        ($since ? " and item_published >= ".$since : '').
        " on duplicate key update item_published=values(item_published)",
        $tag_id, $user_id, $feed_id
    );
}

function fof_db_set_feedprop($user_id, $feed_id, $prop, $value)
{
    $chg = false;
    $prefs = fof_db_get_subscription_prefs($user_id, $feed_id);
    if (empty($prefs[$prop]) ? $value : ($prefs[$prop] != $value))
    {
        $prefs[$prop] = $value;
        $chg = true;
    }
    fof_db_set_subscription_prefs($user_id, $feed_id, $prefs);
    return $chg;
}

// untag feed and all its items
function fof_db_untag_feed($user_id, $feed_id, $tag_id)
{
    global $FOF_ITEM_TAG_TABLE, $FOF_ITEM_TABLE;
    $prefs = fof_db_get_subscription_prefs($user_id, $feed_id);
    if(is_array($prefs['tags']))
        $prefs['tags'] = array_diff($prefs['tags'], array($tag_id));
    fof_db_set_subscription_prefs($user_id, $feed_id, $prefs);
    fof_safe_query(
        "delete from it using $FOF_ITEM_TAG_TABLE it, $FOF_ITEM_TABLE i".
        " where it.item_id=i.item_id and it.user_id=%d and it.tag_id=%d and i.feed_id=%d",
        $user_id, $tag_id, $feed_id
    );
}

function fof_db_get_item_tags($user_id, $item_id)
{
    global $FOF_TAG_TABLE, $FOF_ITEM_TAG_TABLE;

    $result = fof_safe_query("select $FOF_TAG_TABLE.tag_name from $FOF_TAG_TABLE, $FOF_ITEM_TAG_TABLE where $FOF_TAG_TABLE.tag_id = $FOF_ITEM_TAG_TABLE.tag_id and $FOF_ITEM_TAG_TABLE.item_id = %d and $FOF_ITEM_TAG_TABLE.user_id = %d", $item_id, $user_id);

    return $result;
}

function fof_db_item_has_tags($item_id)
{
    global $FOF_ITEM_TAG_TABLE;

    $result = fof_safe_query("select count(*) as \"count\" from $FOF_ITEM_TAG_TABLE where item_id=%d and tag_id > 2", $item_id);
    $row = fof_db_get_row($result);

    return $row["count"];
}

function fof_db_get_unread_count($user_id)
{
    global $FOF_ITEM_TAG_TABLE;

    $result = fof_safe_query("select count(*) as \"count\" from $FOF_ITEM_TAG_TABLE where tag_id = 1 and user_id = %d", $user_id);
    $row = fof_db_get_row($result);

    return $row["count"];
}

function fof_db_get_tag_unread($user_id)
{
    global $FOF_TAG_TABLE, $FOF_ITEM_TAG_TABLE;

    $result = fof_safe_query(
        "select straight_join count(*) as count, it2.tag_id from $FOF_ITEM_TAG_TABLE it, $FOF_ITEM_TAG_TABLE it2 use index (item_id_user_id_tag_id)".
        " where it.item_id = it2.item_id and it.tag_id = 1 and it.user_id = %d and it2.user_id = %d group by it2.tag_id", $user_id, $user_id
    );

    $counts = array();
    while ($row = fof_db_get_row($result))
        $counts[$row['tag_id']] = $row['count'];

    return $counts;
}

function fof_db_get_tags($user_id)
{
    global $FOF_TAG_TABLE, $FOF_ITEM_TABLE, $FOF_ITEM_TAG_TABLE, $fof_connection;

    static $cache = array();
    if (isset($cache[$user_id]))
        return $cache[$user_id];

    $sql = "SELECT $FOF_TAG_TABLE.tag_id, $FOF_TAG_TABLE.tag_name, count($FOF_ITEM_TAG_TABLE.item_id) as count
        FROM $FOF_TAG_TABLE
        LEFT JOIN $FOF_ITEM_TAG_TABLE ON $FOF_TAG_TABLE.tag_id = $FOF_ITEM_TAG_TABLE.tag_id
        WHERE $FOF_ITEM_TAG_TABLE.user_id = %d
        GROUP BY $FOF_TAG_TABLE.tag_id order by $FOF_TAG_TABLE.tag_name";

    $result = fof_safe_query($sql, $user_id);
    $tags = array();
    while ($row = fof_db_get_row($result))
        $tags[$row['tag_id']] = $row;

    $cache[$user_id] = $tags;
    return $tags;
}

function fof_db_get_tag_id_map()
{
    global $FOF_TAG_TABLE;

    $result = fof_safe_query("select * from $FOF_TAG_TABLE");

    $tags = array();
    while ($row = fof_db_get_row($result))
        $tags[$row['tag_id']] = $row['tag_name'];

    return $tags;
}

function fof_db_create_tag($user_id, $tag)
{
    global $FOF_TAG_TABLE, $fof_connection;

    fof_safe_query("insert into $FOF_TAG_TABLE (tag_name) values ('%s')", $tag);

    return fof_insert_id();
}

function fof_db_get_tag_by_name($user_id, $tag)
{
    global $FOF_TAG_TABLE;

    $result = fof_safe_query("select $FOF_TAG_TABLE.tag_id from $FOF_TAG_TABLE where $FOF_TAG_TABLE.tag_name = '%s'", $tag);

    $row = fof_db_get_row($result);

    return $row ? $row['tag_id'] : NULL;
}

function fof_db_mark_unread($user_id, $items)
{
    fof_db_tag_items($user_id, 1, $items);
}

function fof_db_mark_read($user_id, $items)
{
    fof_db_untag_items($user_id, 1, $items);
}

function fof_db_delete_items($user_id, $items, $check_private)
{
    global $FOF_ITEM_TABLE, $FOF_SUBSCRIPTION_TABLE;
    if (!$items)
        return;
    if ($check_private)
    {
        // Check if items correspond to $user_id's private feed
        $result = fof_db_query(
            "SELECT i.item_id FROM $FOF_ITEM_TABLE i".
            " LEFT JOIN $FOF_SUBSCRIPTION_TABLE s ON s.feed_id=i.feed_id AND s.user_id != $user_id".
            " WHERE s.feed_id IS NULL AND i.item_id IN (".implode(',', array_map('intval', $items)).")"
        );
        $items = array();
        foreach ($result as $r)
            $items[] = $r['item_id'];
    }
    if (!$items)
        return;
    $items = implode(',', array_map('intval', $items));
    fof_db_query("DELETE FROM $FOF_ITEM_TABLE WHERE item_id IN ($items)");
}

function fof_db_mark_feed_read($user_id, $feed_id)
{
    fof_db_untag_feed($user_id, $feed_id, 1);
}

function fof_db_mark_feed_unread($user_id, $feed_id, $what)
{
    fof_db_tag_feed_items($user_id, $feed_id, 1, $what == 'today' ? mktime(0, 0, 0, date('n'), date('j'), date('Y')) : 0);
}

function fof_db_mark_item_unread($id, $except_users = array())
{
    global $FOF_ITEM_TABLE, $FOF_SUBSCRIPTION_TABLE, $FOF_ITEM_TAG_TABLE;
    fof_safe_query(
        "insert into $FOF_ITEM_TAG_TABLE (user_id, tag_id, item_id, feed_id, item_published)".
        " select s.user_id, 1, i.item_id, i.feed_id, i.item_published from $FOF_ITEM_TABLE i, $FOF_SUBSCRIPTION_TABLE s".
        " where i.item_id=%d and i.feed_id=s.feed_id".
        ($except_users ? " and s.user_id not in (".implode(',', array_map('intval', $except_users)).")" : '').
        " on duplicate key update feed_id=values(feed_id), item_published=values(item_published)", $id
    );
}

function fof_db_tag_items($user_id, $tag_id, $items)
{
    global $FOF_ITEM_TAG_TABLE, $FOF_ITEM_TABLE;
    if (!$items)
        return;

    $items = implode(',', (array)$items);
    $sql = "insert into $FOF_ITEM_TAG_TABLE (user_id, tag_id, item_id, item_published, feed_id)".
        " select ".intval($user_id).", ".intval($tag_id).", item_id, item_published, feed_id".
        " from $FOF_ITEM_TABLE where item_id in ($items)".
        " on duplicate key update feed_id=values(feed_id), item_published=values(item_published)";

    fof_db_query($sql, 1);
}

function fof_db_untag_items($user_id, $tag_id, $items)
{
    global $FOF_ITEM_TAG_TABLE;

    if(!$items)
        return;
    if(!is_array($items))
        $items = array($items);

    $sql = "item_id IN (".implode(",", array_map('intval', $items)).")";
    $sql = "delete from $FOF_ITEM_TAG_TABLE where user_id = %d and tag_id = %d and $sql";

    fof_safe_query($sql, array($user_id, $tag_id));
}

////////////////////////////////////////////////////////////////////////////////
// User stuff
////////////////////////////////////////////////////////////////////////////////

function fof_db_get_users()
{
    global $FOF_USER_TABLE;

    $result = fof_safe_query("select user_name, user_id, user_prefs from $FOF_USER_TABLE");

    $users = array();
    while ($row = fof_db_get_row($result))
    {
        $users[$row['user_id']]['user_name'] = $row['user_name'];
        $users[$row['user_id']]['user_prefs'] = unserialize($row['user_prefs']);
    }
}

function fof_db_add_user($username, $password)
{
    global $FOF_USER_TABLE;

    $password_hash = md5($password . $username);

    fof_safe_query("insert into $FOF_USER_TABLE (user_name, user_password_hash) values ('%s', '%s')", $username, $password_hash);
}

function fof_db_change_password($username, $password)
{
    global $FOF_USER_TABLE;

    $password_hash = md5($password . $username);

    fof_safe_query("update $FOF_USER_TABLE set user_password_hash = '%s' where user_name = '%s'", $password_hash, $username);
}

function fof_db_get_user($username, $userid = NULL)
{
    global $FOF_USER_TABLE;
    if ($username === NULL)
        $result = fof_safe_query("select * from $FOF_USER_TABLE where user_id = %d", $userid);
    else
        $result = fof_safe_query("select * from $FOF_USER_TABLE where user_name = '%s'", $username);
    $row = fof_db_get_row($result);
    return $row;
}

function fof_update_user($user)
{
    global $FOF_USER_TABLE;
    if (!$user || !$user['user_id'])
        return;
    $values = array();
    $query = "";
    foreach (array('user_name', 'user_password_hash', 'user_level', 'user_prefs') as $k)
    {
        $query .= ($query ? ", " : "") . "`$k`='%s'";
        $values[] = $user[$k];
    }
    $query = "UPDATE $FOF_USER_TABLE SET $query WHERE user_id='%s'";
    $values[] = $user['user_id'];
    array_unshift($values, $query);
    call_user_func_array('fof_safe_query', $values);
}

function fof_db_delete_user($username)
{
    global $FOF_USER_TABLE, $FOF_ITEM_TAG_TABLE, $FOF_SUBSCRIPTION_TABLE;
    $user = fof_db_get_user($username);
    $user_id = $user['user_id'];

    fof_safe_query("delete from $FOF_SUBSCRIPTION_TABLE where user_id = %d", $user_id);
    fof_safe_query("delete from $FOF_ITEM_TAG_TABLE where user_id = %d", $user_id);
    fof_safe_query("delete from $FOF_USER_TABLE where user_id = %d", $user_id);
}

function fof_db_save_prefs($user_id, $prefs)
{
    global $FOF_USER_TABLE;

    $prefs = serialize($prefs);

    fof_safe_query("update $FOF_USER_TABLE set user_prefs = '%s' where user_id = %d", $prefs, $user_id);
}

function fof_set_current_user($row)
{
    global $fof_user_id, $fof_user_name, $fof_user_level;
    $fof_user_name = $row['user_name'];
    $fof_user_id = $row['user_id'];
    $fof_user_level = $row['user_level'];
    return true;
}

function fof_db_authenticate($user_name, $user_password_hash)
{
    global $FOF_USER_TABLE;

    $result = fof_safe_query("select * from $FOF_USER_TABLE where user_name = '%s' and user_password_hash = '%s'", $user_name, $user_password_hash);

    if (fof_num_rows($result) == 0)
        return false;

    $row = fof_db_get_row($result);

    fof_set_current_user($row);

    return true;
}

function fof_db_get_is_duplicate_item($item_id, $item_guid, $content_md5)
{
    global $FOF_ITEM_TABLE, $FOF_USER_TABLE, $FOF_FEED_TABLE, $FOF_SUBSCRIPTION_TABLE;
    $result = fof_safe_query("select * from $FOF_ITEM_TABLE where item_guid='%s' AND item_id!='%d'", $item_guid, $item_id);
    $dups = array();
    while ($row = fof_db_get_row($result))
        if (md5($row['item_content']) == $content_md5)
            $dups[] = intval($row['item_id']);
    if (!count($dups))
        return array();
    $result = fof_db_query(
        "SELECT DISTINCT $FOF_SUBSCRIPTION_TABLE.user_id FROM $FOF_ITEM_TABLE, $FOF_SUBSCRIPTION_TABLE".
        " WHERE item_id IN (".join(",",$dups).") AND $FOF_SUBSCRIPTION_TABLE.feed_id=$FOF_ITEM_TABLE.feed_id"
    );
    $users = array();
    while ($row = fof_db_get_row($result))
        $users[] = reset($row);
    return $users;
}

function fof_db_set_feed_custom_title($user_id, $feed_id, $feed_title, $custom_title)
{
    global $FOF_FEED_TABLE, $FOF_SUBSCRIPTION_TABLE;
    $prefs = fof_db_get_subscription_prefs($user_id, $feed_id);
    if (!$prefs)
        return false;
    if (!$custom_title || $feed_title == $custom_title)
        $r = $prefs['feed_title'] = '';
    else
        $r = $prefs['feed_title'] = $custom_title;
    fof_db_set_subscription_prefs($user_id, $feed_id, $prefs);
    return $r;
}

function fof_db_get_top_readers($days, $count = NULL)
{
    global $FOF_ITEM_TABLE, $FOF_USER_TABLE, $FOF_ITEM_TAG_TABLE, $FOF_SUBSCRIPTION_TABLE, $FOF_TAG_TABLE;
    $result = fof_db_query(
"select u.*, count(i.item_id) posts from $FOF_USER_TABLE u
 join $FOF_SUBSCRIPTION_TABLE s on s.user_id=u.user_id
 join $FOF_ITEM_TABLE i on i.feed_id=s.feed_id
 join $FOF_TAG_TABLE t on t.tag_name='unread'
 left join $FOF_ITEM_TAG_TABLE ti on ti.tag_id=t.tag_id and ti.item_id=i.item_id and ti.user_id=u.user_id
 where i.item_published > ".(time()-intval($days*86400))." and ti.tag_id is null
 group by u.user_id order by posts desc
 ".(!is_null($count) ? "limit $count" : ""));
    $readers = array();
    while ($row = fof_db_get_row($result))
        $readers[] = $row;
    return $readers;
}

function fof_db_get_most_popular_feeds($count = NULL)
{
    global $FOF_SUBSCRIPTION_TABLE, $FOF_FEED_TABLE;
    $result = fof_db_query(
"select *, count(u.user_id) readers from $FOF_FEED_TABLE f join $FOF_SUBSCRIPTION_TABLE u on u.feed_id=f.feed_id
 left join $FOF_SUBSCRIPTION_TABLE s on s.feed_id=f.feed_id and s.user_id=".fof_current_user()."
 where s.feed_id is null and f.feed_url not regexp '^[a-z]+://[^/]+:[^/]*@'
 group by f.feed_id having readers>1 order by readers desc
 ".(!is_null($count) ? "limit $count" : ""));
    $feeds = array();
    while ($row = fof_db_get_row($result))
        $feeds[] = $row;
    return $feeds;
}

function fof_db_get_feed_single_user($id)
{
    global $FOF_SUBSCRIPTION_TABLE;
    $result = fof_safe_query("SELECT user_id, COUNT(user_id) n FROM $FOF_SUBSCRIPTION_TABLE WHERE feed_id=%d GROUP BY feed_id", $id);
    $row = fof_db_get_row($result);
    if (!$row || $row['n'] > 1)
        return NULL;
    return $row['user_id'];
}

function fof_cache_fn($key)
{
    if (preg_match('/[^a-z0-9_\-]/', $key))
        $key = 'md5_'.md5($key);
    return dirname(__FILE__).'/cache/'.$key;
}

function fof_cache_get($key)
{
    $file = fof_cache_fn($key);

    $fp = @fopen($file, "rb");
    if (!$fp)
        return NULL;

    $expire = intval(fgets($fp));
    $value = fread($fp, 1048576);
    fclose($fp);

    if (time() > $expire)
    {
        fof_cache_unset($key);
        return NULL;
    }

    return $value;
}

function fof_cache_set($key, $value, $ttl = 86400)
{
    $file = fof_cache_fn($key);
    if ($fp = fopen($file, "wb"))
    {
        fwrite($fp, (time()+$ttl)."\n".$value);
        fclose($fp);
        return true;
    }
    return false;
}

function fof_cache_unset($key)
{
    $file = fof_cache_fn($key);
    @unlink($file);
}