<?php
/**
 * SeekQuarry/Yioop --
 * Open Source Pure PHP Search Engine, Crawler, and Indexer
 *
 * Copyright (C) 2009 - 2026  Chris Pollett chris@pollett.org
 *
 * LICENSE:
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 * END LICENSE
 *
 * @author Chris Pollett
 * @license https://www.gnu.org/licenses/ GPL3
 * @link https://www.seekquarry.com/
 * @copyright 2009 - 2026
 * @filesource
 */
namespace seekquarry\yioop\models;

use seekquarry\yioop\configs as C;
use seekquarry\yioop\library as L;
use seekquarry\yioop\library\data_structures\PackedTableTools;
use seekquarry\yioop\library\storage_formats\PartitionDocumentBundle;
use seekquarry\yioop\library\storage_formats as LSF;

/**
 * Model used to keep track for analytic and user experience activities that
 * users carry out on a Yioop web site. For analytics things that might
 * tracked are wiki page views, queries, query outcomes. For UX things that
 * the impression model allows is to keep track of recent group a user has
 * visited to provide better bread crumb drop downs, make the manage account
 * landing page list more relevant groups, determine start of whether a
 * media item has been watched, completely watched, etc.
 *
 * In terms of how things are implemented in the database. The tables
 * ITEM_IMPRESSION and ITEM_IMPRESSION_SUMMARY contain the raw statistics
 * of activities. If differential privacy is in use then
 * ITEM_IMPRESSION_STAT keeps track of fuzzified statistics.
 *
 * @author Chris Pollett
 */
class ImpressionModel extends Model
{
    /**
     * Maximum bytes of impression records buffered in BUFFER_FILE
     * before they're flushed to the persistent impression tables
     */
    const MAX_TMP_BUFFER_SIZE = 40000;
    /**
     * Sub-folder under CACHE_DIR holding per-process impression
     * buffer files and AnalyticsJob status pages
     */
    const IMPRESSION_FOLDER = "AnalyticLogs";
    /**
     * Filename within IMPRESSION_FOLDER for the rolling buffer of
     * raw impression rows awaiting summary flush
     */
    const BUFFER_FILE = "buffer.log";
    /**
     * Column layout for the in-memory impression-buffer rows
     * (PackedTableTools schema: user_id is the primary key, plus
     * item_id, type_id, and timestamp ints)
     */
    const IMPRESSION_FORMAT = ["PRIMARY KEY" => "user_id", "item_id" => "INT",
        "type_id" => "INT", "timestamp" => "INT" ];
    /**
     * Used to create a new counter related to a particular user for a
     * particular activity in the impression analytics. This entails adding both
     * a log-like record of when the activity happened and creating  a
     * new global count for this acitvity.
     *
     * @param int $user_id id of user we are adding analytic information for
     * @param int $item_id id of particular item we are adding analytic
     *      information of
     * @param int $type_id type of particular item we are adding analytic
     *      information of (group, wiki, thread, etc)
     */
    public function init($user_id, $item_id, $type_id)
    {
        $this->initWithDb($user_id, $item_id, $type_id, $this->db);
    }
    /**
     * Used to add a count record related to a particular user for a particular
     * activity to the impression analytics. This entails adding both
     * a log-like record of when the activity happened and incrementing a
     * global count of this activity.
     *
     * @param int $user_id id of user we are adding analytic information for
     * @param int $item_id id of particular item we are adding analytic
     *      information of
     * @param int $type_id type of particular item we are adding analytic
     *      information of (group, wiki, thread, etc)
     * @param int $time optional UNIX timestamp to use for impression
     *      tracking; if set to -1 or omitted, the current time will be used
     */
    public function add($user_id, $item_id, $type_id, $time = -1)
    {
        $time = ($time > 0) ? $time : time();
        $this->addWithDb($user_id, $item_id, $type_id, $this->db, $time);
    }
    /**
     * Used to add a count record related to a web search to the impression
     * analytics. This entails adding a record to QUERY_ITEM if it doesn't
     * exist together with an add() call.
     *
     * @param string $query search query we are adding an impression for
     */
    public function addQueryImpression($query)
    {
        if (!C\p('SEARCH_ANALYTICS_MODE') ||
            C\p('SEARCH_ANALYTICS_MODE') == "0") {
            return;
        }
        $db = $this->db;
        $dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
            "DB_USER" => C\p('DB_USER'), "DB_PASSWORD" => C\p('DB_PASSWORD'),
            "DB_NAME" => C\p('DB_NAME')];
        $query_hash = L\crawlHash($query);
        $sql = "SELECT ID FROM QUERY_ITEM WHERE QUERY_HASH = ?";
        $result = $db->execute($sql, [$query_hash]);
        $row = $db->fetchArray($result);
        $impression_type = (strncmp($query, "cache:", 6) === 0) ?
            C\CACHE_IMPRESSION : C\QUERY_IMPRESSION;
        if (empty($row['ID'])) {
            $sql = "INSERT INTO QUERY_ITEM (QUERY_HASH, QUERY, CREATION)
                VALUES (?, ?, ?)";
            $sql = $db->insertIgnore($sql, $dbinfo);
            $result = $db->execute($sql, [$query_hash, $query, time()]);
            $this->initWithDb(C\PUBLIC_USER_ID, $db->insertID("QUERY_ITEM"),
                $impression_type, $db);
        } else {
            $this->addWithDb(C\PUBLIC_USER_ID, $row['ID'],
                $impression_type, $db);
        }
    }
    /**
     * Used to add an impression record related to a user clicking
     * a link on the query result page for web search. This entails adding a
     * record to QUERY_ITEM the particular link click from the query doesn't
     * exist together with an add() impression call call.
     *
     * @param string $query search query the user was clicking a link on
     * @param string $link url that the user clicked on from the results
     */
    public function addQueryExitImpression($query, $link)
    {
        if (!C\p('SEARCH_ANALYTICS_MODE') ||
            C\p('SEARCH_ANALYTICS_MODE') == "0") {
            return;
        }
        $db = $this->db;
        $dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
            "DB_USER" => C\p('DB_USER'), "DB_PASSWORD" => C\p('DB_PASSWORD'),
            "DB_NAME" => C\p('DB_NAME')];
        $query_link = trim($query) . "=>" . trim($link);
        $query_hash = L\crawlHash($query);
        $ql_hash = L\crawlHash($query_link);
        $sql = "SELECT ID FROM QUERY_ITEM WHERE QUERY_HASH = ?";
        $result = $db->execute($sql, [$ql_hash]);
        $row = $db->fetchArray($result);
        if (empty($row['ID'])) {
            $sql = "INSERT INTO QUERY_ITEM (QUERY_HASH, QUERY, CREATION,
                AUX_HASH) VALUES (?, ?, ?, ?)";
            $sql = $db->insertIgnore($sql, $dbinfo);
            $result = $db->execute($sql, [$ql_hash, $query_link, time(),
                $query_hash]);
            $this->initWithDb(C\PUBLIC_USER_ID, $db->insertID("QUERY_ITEM"),
                C\QUERY_IMPRESSION_EXIT, $db);
        } else {
            $this->addWithDb(C\PUBLIC_USER_ID, $row['ID'],
                C\QUERY_IMPRESSION_EXIT, $db);
        }
    }
    /**
     * Returns num many most recent impression items of the given type for
     * a user
     *
     * @param int $user_id id of user we are looking for information about
     * @param int $type_id type of particular item we want information on
     *      (group, wiki, thread, etc)
     * @param int $num how many most recent entries we want to get
     * @return array of $num many most recent item id's of $type_id of
     *      for the given $user_id
     */
    public function recent($user_id, $type_id, $num)
    {
        $db = $this->db;
        // mysql doesn't like LIMIT to be parameterized
        $num = intval($num);
        $sql = "SELECT ITEM_ID, MAX(UPDATE_TIMESTAMP) AS MOST_RECENT
            FROM ITEM_IMPRESSION_SUMMARY
            WHERE USER_ID = ? AND ITEM_TYPE = ? AND
            UPDATE_PERIOD = " . C\MOST_RECENT_VIEW . " GROUP BY ITEM_ID
            ORDER BY MOST_RECENT DESC LIMIT $num";
        $result = $db->execute($sql, [$user_id, $type_id]);
        $rows = [];
        while ($row = $db->fetchArray($result)) {
            $rows[] = $row['ITEM_ID'];
        }
        $rows = array_values(array_filter($rows));
        return $rows;
    }
    /**
     * Returns the most recent timestamp of any view impression of a group a
     * user has.
     * @param int $user_id want most recent impression for
     * @param int $thread_id thread (GROUP_ITEM) the user's view
     *  impression should be looked up against
     * @return int timstamp of most recent impression
     */
    public function mostRecentThreadView($user_id, $thread_id)
    {
        $db = $this->db;
        $sql = "SELECT COALESCE(MAX(UPDATE_TIMESTAMP), 0) AS MOST_RECENT FROM ".
            "ITEM_IMPRESSION_SUMMARY WHERE USER_ID = ? AND ITEM_TYPE = " .
            C\THREAD_IMPRESSION . " AND ITEM_ID = ? " .
            "AND UPDATE_PERIOD = ". C\MOST_RECENT_VIEW;
        $result = $db->execute($sql, [$user_id, $thread_id]);
        if (!$result || !($row = $db->fetchArray($result))) {
            return 0;
        }
        return $row["MOST_RECENT"] ?? 0;
    }
    /**
     * Returns the most recent timestamp of any view impression of a group a
     * user has.
     * @param int $user_id want most recent impression for
     * @return int timstamp of most recent impression
     */
    public function mostRecentGroupView($user_id)
    {
        $db = $this->db;
        $sql = "SELECT COALESCE(MAX(UPDATE_TIMESTAMP), 0) AS MOST_RECENT FROM ".
            "ITEM_IMPRESSION_SUMMARY WHERE USER_ID = $user_id " .
                "AND ITEM_TYPE = " .
            C\GROUP_IMPRESSION . " AND ITEM_ID IN (SELECT GROUP_ID " .
            "FROM USER_GROUP WHERE USER_ID = $user_id AND STATUS = ".
            C\ACTIVE_STATUS . ")  AND UPDATE_PERIOD = ".C\MOST_RECENT_VIEW;
        $result = $db->execute($sql);
        if (!$result || !($row = $db->fetchArray($result))) {
            return 0;
        }
        return $row["MOST_RECENT"];
    }
    /**
     * For a list of group_ids that $user_id may belong to, returns an
     * array of pairs group_id => timestamp of most recent view
     * @param int $user_id user to look up most recent views for
     * @param array $group_ids groups to check in
     * @return array pairs group_id => timestamp
     */
    public function mostRecentGroupViews($user_id, $group_ids)
    {
        $db = $this->db;
        $in_clause = " (";
        $comma = "";
        foreach ($group_ids as $group_id) {
            $in_clause .= $comma . intval($group_id);
            $comma = ",";
        }
        $in_clause .= ") ";
        $sql = "SELECT ITEM_ID AS GROUP_ID, UPDATE_TIMESTAMP AS MOST_RECENT " .
            "FROM ITEM_IMPRESSION_SUMMARY " .
            "WHERE USER_ID = ? AND ITEM_TYPE = " . C\GROUP_IMPRESSION .
            " AND ITEM_ID IN $in_clause AND UPDATE_PERIOD = ".
            C\MOST_RECENT_VIEW . " AND NUM_VIEWS = 0";
        $result = $db->execute($sql, [$user_id]);
        if (!$result) {
            return [];
        }
        $group_views = [];
        while ($row = $db->fetchArray($result)) {
            if (!empty($row["GROUP_ID"]) && $row["MOST_RECENT"]) {
                $group_views[$row["GROUP_ID"]] = $row["MOST_RECENT"];
            }
        }
        return $group_views;
    }
    /**
     * lastSeenAtMessagesScreen gives, for each person named, the last time
     * they were at their own messages screen.
     *
     * MessageComponent calls this to draw the mark beside a contact that
     * says whether that person is there now. The messages screen asks the
     * server every few seconds whether a call is ringing, and each ask
     * records a view of the asker's own messages group, so the newest such
     * view is when that person was last at the screen. Being signed in
     * says only that a sign-in has not run out, which stays true for hours
     * after somebody has closed the page. Every person is asked about in
     * one query, since the contact list is read on every draw of the
     * screen.
     *
     * @param array $messages_group_for_user the messages group to look up
     *     for each person, as an array whose keys are user ids and whose
     *     values are that person's own messages group id
     * @return array a Unix timestamp for each person who has been at their
     *     messages screen, as an array whose keys are user ids and whose
     *     values are the seconds since 1970 of the last time they were
     *     there; a person with no such visit on record is left out
     */
    public function lastSeenAtMessagesScreen($messages_group_for_user)
    {
        $seen = [];
        if (empty($messages_group_for_user)) {
            return $seen;
        }
        $pairs = [];
        $values = [];
        foreach ($messages_group_for_user as $user_id => $group_id) {
            $pairs[] = "(USER_ID = ? AND ITEM_ID = ?)";
            $values[] = $user_id;
            $values[] = $group_id;
        }
        $db = $this->db;
        $sql = "SELECT USER_ID, MAX(UPDATE_TIMESTAMP) AS MOST_RECENT " .
            "FROM ITEM_IMPRESSION_SUMMARY WHERE ITEM_TYPE = " .
            C\GROUP_IMPRESSION . " AND UPDATE_PERIOD = " .
            C\MOST_RECENT_VIEW . " AND (" . implode(" OR ", $pairs) .
            ") GROUP BY USER_ID";
        $result = $db->execute($sql, $values);
        if (empty($result)) {
            return $seen;
        }
        while ($row = $db->fetchArray($result)) {
            $seen[$row["USER_ID"]] = $row["MOST_RECENT"];
        }
        return $seen;
    }
    /**
     * Used by Analytics job to aggregate impression raw data to make
     * hourly, daily, monthly, and yearly impression statistics.
     */
    public function computeStatistics()
    {
        $db = $this->db;
        $dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
            "DB_USER" => C\p('DB_USER'), "DB_PASSWORD" => C\p('DB_PASSWORD'),
            "DB_NAME" => C\p('DB_NAME')];
        $timestamps = [
            C\ONE_HOUR => (int) floor(time() / C\ONE_HOUR) * C\ONE_HOUR,
            C\ONE_DAY => (int) floor(time() / C\ONE_DAY) * C\ONE_DAY,
            C\ONE_MONTH => (int) floor(time() / C\ONE_MONTH) * C\ONE_MONTH,
            C\ONE_YEAR => (int) floor(time() / C\ONE_YEAR) * C\ONE_YEAR,
        ];
        $one_week_stamp = C\ONE_WEEK * floor(time()/C\ONE_WEEK);
        $pdb = new LSF\PartitionDocumentBundle(C\LOG_DIR . "/" .
            self::IMPRESSION_FOLDER, self::IMPRESSION_FORMAT);
        $max_part  = $pdb->parameters['SAVE_PARTITION'] ?? 0;
        $prev_period = null;
        foreach ($timestamps as $period => $aligned_timestamp) {
            $db->beginTransaction();
            $db->execute(
                'DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE UPDATE_PERIOD = ? ' .
                'AND UPDATE_TIMESTAMP = ?', [$period, $aligned_timestamp] );
            if ($period === C\ONE_HOUR) {
                $cutoff_part = null;
                for ($part = $max_part; $part >= 0; $part--) {
                    $index = $pdb->loadPartitionIndex($part, false,
                        PackedTableTools::APPEND_MODE );
                    if (empty($index)) {
                        continue;
                    }
                    $max_timestamp = 0;
                    foreach ($index as $packed) {
                        $rows = $pdb->table_tools->unpack($packed);
                        foreach ($rows as $r) {
                            if ($r['timestamp'] > $max_timestamp) {
                                $max_timestamp = $r['timestamp'];
                            }
                        }
                    }
                    if ($max_timestamp >= $aligned_timestamp) {
                        $cutoff_part = $part;
                        break;
                    }
                }
                if ($cutoff_part === null) {
                    L\crawlLog("No relevant partition found");
                } else {
                    L\crawlLog("Cutoff partition: $cutoff_part");
                    $summary = [];
                    for ($part = $cutoff_part; $part <= $max_part; $part++) {
                        $index = $pdb->loadPartitionIndex($part, false,
                            PackedTableTools::APPEND_MODE);
                        foreach ($index as $user_id => $packed_row) {
                            $rows = $pdb->table_tools->unpack($packed_row);
                            foreach ($rows as $row) {
                                if ($row['timestamp'] >= $aligned_timestamp) {
                                    $iid = $row['item_id'];
                                    $tid = $row['type_id'];
                                    $key = $user_id . '-' . $iid . '-' . $tid;
                                    if (isset($summary[$key])) {
                                        $summary[$key]['num_views']++;
                                    } else {
                                       $summary[$key] = [ 'user_id' => $user_id,
                                           'item_id' => $row['item_id'],
                                           'item_type' => $row['type_id'],
                                           'num_views' => 1];
                                    }
                                }
                            }
                        }
                    }
                    $chunks = array_chunk($summary, 500);
                    L\crawlLog('Inserting ' . count($chunks) . ' chunks');
                    foreach ($chunks as $chunk) {
                        $rows_sql = [];
                        foreach ($chunk as $s) {
                            $uid = (int)$s['user_id'];
                            $iid = (int)$s['item_id'];
                            $itype = (int)$s['item_type'];
                            $num_views  = (int)$s['num_views'];
                            $rows_sql[] = "($uid,$iid,$itype,$period," .
                                "$aligned_timestamp,$num_views)";
                        }
                        if ($rows_sql) {
                            $values = implode(',', $rows_sql);
                            $sql = 'INSERT INTO ITEM_IMPRESSION_SUMMARY ' .
                                '(USER_ID, ITEM_ID, ITEM_TYPE, ' .
                                'UPDATE_PERIOD, UPDATE_TIMESTAMP, ' .
                                'NUM_VIEWS) VALUES ' . $values;
                            $db->execute($db->insertIgnore($sql, $dbinfo));
                        }
                    }
                }
            } else {
                $sql = 'INSERT INTO ITEM_IMPRESSION_SUMMARY (USER_ID, ITEM_ID,
                    ITEM_TYPE,  UPDATE_PERIOD, UPDATE_TIMESTAMP, NUM_VIEWS)
                    SELECT USER_ID, ITEM_ID, ITEM_TYPE, ? AS UPDATE_PERIOD,
                        ? AS UPDATE_TIMESTAMP, SUM(NUM_VIEWS)
                        FROM ITEM_IMPRESSION_SUMMARY WHERE UPDATE_PERIOD = ?
                        AND UPDATE_TIMESTAMP >= ?
                        GROUP BY USER_ID, ITEM_ID, ITEM_TYPE';
                $db->execute($db->insertIgnore($sql, $dbinfo),
                    [$period, $aligned_timestamp, $prev_period,
                    $aligned_timestamp]);
                L\crawlLog( "Computing statistics for $period " .
                "second update period");
            }
            $prev_period = $period;
            $db->commit();
        }
    }
    /**
     * Used to return an array of impression statistics for a particular update
     * period for a particular type of impression.
     *
     * @param int $type type of impression to return statistic
     * @param int $period an update period to get statistics for in second, for
     *      example, 3600 would give statistics for an hour. Only
     *      hour, day, month, and year second quantities supported
     * @param int $filter a string to filter the items names of the
     *      statistics returns (for example, filter could be used to
     *      filter statistics about popular thread names with respect to
     *      the number of views statistics )
     * @param int $group_id group identifier of group want stats for
     * @param int $user_id user identifier of user want stats for
     * @param int $limit first row we want from the result set
     * @param int $num number of rows we want starting from the first row
     *     in the result set
     * @return array per-item statistics rows; for GROUP_IMPRESSION
     *     this is a flat list, otherwise it's a map item_name => rows
     *     (each row has GROUP_NAME, ID, PERIOD, TIMESTAMP, NUM_VIEWS,
     *     and the relevant ITEM_NAME)
     */
    public function getStatistics($type, $period, $filter = "",
        $group_id = C\PUBLIC_GROUP_ID, $user_id = C\PUBLIC_USER_ID,
        $limit = 0, $num = 100)
    {
        $db = $this->db;
        $select = "";
        $from = "";
        $where = "";
        list ($timestamp, $actual_period) = $this->getLastTimestamp($period);
        $parameters = [$type, $actual_period, $group_id, $user_id];
        $filter_where = "";
        switch ($type)
        {
            case C\GROUP_IMPRESSION:
                $select = "";
                $from = "";
                $where = " AND IIS.ITEM_ID = G.GROUP_ID";
                break;
            case C\QUERY_IMPRESSION:
                $select = ", MIN(QI.QUERY) AS ITEM_NAME";
                $from = ", QUERY_ITEM QI";
                $where = " AND IIS.ITEM_ID = QI.ID ";
                if (!empty($filter)) {
                    $filter_where = " AND LOWER(QI.QUERY) ".
                        "LIKE LOWER('%$filter%') ";
                }
                break;
            case C\THREAD_IMPRESSION:
                $select = ", MIN(GI.TITLE) AS ITEM_NAME";
                $from = ", GROUP_ITEM GI";
                $where = " AND IIS.ITEM_ID = GI.ID ".
                    "AND G.GROUP_ID = GI.GROUP_ID";
                if (!empty($filter)) {
                    $filter_where = " AND LOWER(GI.TITLE) ".
                        "LIKE LOWER('%$filter%') ";
                }
                break;
            case C\WIKI_IMPRESSION:
                $select = ", MIN(GP.TITLE) AS ITEM_NAME";
                $from = ", GROUP_PAGE GP";
                $where = " AND IIS.ITEM_ID = GP.ID ".
                    "AND G.GROUP_ID = GP.GROUP_ID";
                if (!empty($filter)) {
                    $filter_where = " AND LOWER(GP.TITLE) ".
                        "LIKE LOWER('%$filter%') ";
                }
                break;
        }
        $timestamp_where = "";
        if ($period != C\FOREVER) {
            $timestamp_where = " AND IIS.UPDATE_TIMESTAMP >= ? ";
            $parameters[] = $timestamp;
        }
        $sql = "SELECT MIN(G.GROUP_NAME) AS GROUP_NAME, IIS.ITEM_ID AS ID,
            MIN(IIS.UPDATE_PERIOD) AS PERIOD,
            MIN(IIS.UPDATE_TIMESTAMP) AS TIMESTAMP,
            SUM(IIS.NUM_VIEWS) AS NUM_VIEWS $select
            FROM ITEM_IMPRESSION_SUMMARY IIS, SOCIAL_GROUPS G $from
            WHERE  IIS.ITEM_TYPE = ? AND IIS.UPDATE_PERIOD = ?
            AND G.GROUP_ID = ? AND IIS.USER_ID = ? $where $filter_where
            $timestamp_where GROUP BY IIS.ITEM_ID ORDER BY PERIOD ASC,
            NUM_VIEWS DESC " .
            $db->limitOffset($limit, $num);
        $result = $db->execute($sql, $parameters);
        $statistics = [];
        while ($row = $db->fetchArray($result)) {
            if ($type == C\GROUP_IMPRESSION) {
                $statistics[] = $row;
            } else {
                $item_name = $row['ITEM_NAME'];
                unset($row['ITEM_NAME']);
                $statistics[$item_name][] = $row;
            }
        }
        return $statistics;
    }
    /**
     * Calculates total number of views of given item id for
     * given time period
     *
     * @param int $type the impression type to get data for
     * @param int $period time period for which to show stats
     * @param int $item_id item identifier of item for which to show stats
     * @return array per-timestamp impression rows for the item,
     *     ordered by UPDATE_TIMESTAMP ascending (each row has ID,
     *     VIEWS, TYPE, UPDATE_TIMESTAMP, PERIOD)
     */
    public function getPeriodHistogramData($type, $period, $item_id)
    {
        $db = $this->db;
        list ($timestamp, $actual_period) = $this->getLastTimestamp($period);
        $parameters = [$type, $actual_period, $item_id, $timestamp];
        $sql = "SELECT IIS.ITEM_ID AS ID, IIS.NUM_VIEWS AS VIEWS,
            IIS.ITEM_TYPE AS TYPE, IIS.UPDATE_TIMESTAMP,
            IIS.UPDATE_PERIOD AS PERIOD
            FROM ITEM_IMPRESSION_SUMMARY IIS
            WHERE IIS.ITEM_TYPE = ? AND IIS.UPDATE_PERIOD = ?
            AND IIS.ITEM_ID = ? AND IIS.USER_ID = 2
            AND IIS.UPDATE_TIMESTAMP >= ? ORDER BY UPDATE_TIMESTAMP ASC";
        $result = $db->execute($sql, $parameters);
        $statistics = [];
        while ($row = $db->fetchArray($result)) {
            $statistics[] = $row;
        }
        return $statistics;
    }
    /**
     * Subtracts the timestamp to get actual time period window.
     * To get statistics for one time period, lower time period's data needs
     * to be extracted. For example, to get last day's stat, result in every
     * one hour needs to be extracted in the last 24 hours
     *
     * @param int $period time period
     * @return array two-element list [$timestamp, $actual_period]:
     *     the cutoff timestamp from which statistics rows should be
     *     read, and the smaller bucket size to aggregate at (e.g. for
     *     ONE_DAY the bucket is ONE_HOUR)
     */
    public function getLastTimestamp($period)
    {
        $time_periods = [C\ONE_HOUR => 1, C\ONE_DAY => 24, C\ONE_MONTH => 30,
            C\ONE_YEAR => 12, C\FOREVER => 0];
        $previous_period = [C\ONE_HOUR => C\ONE_HOUR,
            C\ONE_DAY => C\ONE_HOUR, C\ONE_MONTH => C\ONE_DAY,
            C\ONE_YEAR => C\ONE_MONTH, C\FOREVER => C\FOREVER];
        $interval = (!empty($time_periods[$period])) ?
            $time_periods[$period] : C\FOREVER;
        $period = (!empty($previous_period[$period])) ?
            $previous_period[$period] : 0;
        $current_timestamp = floor(time()/$period) * $period;
        $last_timestamp = ($period == C\FOREVER) ? 0:
            ($current_timestamp - ($period * ($interval - 1)));
        return [$last_timestamp, $period];
    }
    /**
     * Used to update the fuzzified view counts of a thread item.
     *
     * @param int $item_id id of the thread item to update fuzzified counts for
     * @param int $num_views current number of views for item. This value
     *  is stored in the TMP_NUM_VIEWS column to remember the last time the
     *  FUZZY_NUM_VIEWS column was updated. Only when the TMP_NUM_VIEWS column
     *  differs from the NUM_VIEWS COLUMN will this method need to be called.
     * @param int $fuzzy_num_views number of views after epsilon privacy
     *  fuzzification applied.
     */
    public function updatePrivacyViews($item_id, $num_views, $fuzzy_num_views)
    {
        $sql = "UPDATE ITEM_IMPRESSION_SUMMARY SET FUZZY_NUM_VIEWS=?,
            TMP_NUM_VIEWS=? WHERE ITEM_ID=? AND ITEM_TYPE=" .
            C\THREAD_IMPRESSION;
        $this->db->execute($sql, [$fuzzy_num_views, $num_views, $item_id]);
    }
    /**
     * Used to update the fuzzy statistics of impression items.
     *
     * @param int $item_id id of the item to update the statistics
     * @param int $item_type type of the item
     * @param int $period time period of the item
     * @param int $num_views number of views of the item for
     *  specified time period
     * @param int $fuzzy_num_views fuzzified views of the item
     *  for specified time period
     */
    public function updateImpressionStat($item_id, $item_type, $period,
        $num_views, $fuzzy_num_views)
    {
            $sql = "UPDATE ITEM_IMPRESSION_STAT SET FUZZY_NUM_VIEWS=?,
                NUM_VIEWS=? WHERE ITEM_ID=? AND ITEM_TYPE=?
                    AND UPDATE_PERIOD=?";
            $parameters = [$fuzzy_num_views, $num_views, $item_id,
                $item_type, $period];
            $result = $this->db->execute($sql, $parameters);
    }
    /**
     * Returns the fuzzy statistics of the specified impression item.
     * If no statistics exists it creates default dummy statistics
     * It is assumed this function is always called at least once before
     * @see updateImpressionStat
     *
     * @param int $item_id id of the item to return the statistics
     * @param int $item_type type of the item
     * @param int $period time period of the item
     * @return array values of $sum and $fuzzy_num_views
     */
    public function getImpressionStat($item_id, $item_type, $period)
    {
        $sql = "SELECT NUM_VIEWS, FUZZY_NUM_VIEWS
            FROM ITEM_IMPRESSION_STAT WHERE
            ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD=?";
        $parameters = [$item_id, $item_type, $period];
        $result = $this->db->execute($sql, $parameters);
        $row = $this->db->fetchArray($result);
        if (empty($row)) {
            $periods = [C\ONE_HOUR, C\ONE_DAY, C\ONE_MONTH, C\ONE_YEAR,
                C\FOREVER];
            foreach ($periods as $period) {
                $sql = "INSERT INTO ITEM_IMPRESSION_STAT VALUES
                    (?, ?, ?, -1, -1)";
                $this->db->execute($sql,
                    [$item_id, $item_type, $period]);
            }
            return [-1, -1];
        }
        return [$row['NUM_VIEWS'], $row['FUZZY_NUM_VIEWS']];
    }
    /**
     * Used to create a new counter related to a particular user for a
     * particular activity in the impression analytics. This entails adding both
     * a log-like record of when the activity happened and creating  a
     * new global count for this activity. This static method version requires
     * having an initialized data source manager and may be appropriate to call
     * in the context of another model.
     *
     * @param int $user_id id of user we are adding analytic information for
     * @param int $item_id id of particular item we are adding analytic
     *      information of
     * @param int $type_id type of particular item we are adding analytic
     *      information of (group, wiki, thread, etc)
     * @param object $db a DatasourceManager used to query the Yioop
     *      Yioop database
     * @param int $time optional UNIX timestamp to use for impression
     *     tracking; if set to -1 or omitted, the current time will be used
     */
    public static function initWithDb($user_id, $item_id, $type_id, $db,
        $time = -1)
    {
        $dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
            "DB_USER" => C\p('DB_USER'), "DB_PASSWORD" => C\p('DB_PASSWORD'),
            "DB_NAME" => C\p('DB_NAME')];
        /*
           The next insert makes sure if the key ($user_id, $item_id, $type_id)
           is not yet present it gets initilized appropriately to 0
         */
        $sql = "INSERT INTO ITEM_IMPRESSION_SUMMARY VALUES
            (?, ?, ?, " . C\FOREVER .", 0, 0, -1, -1)";
        $sql = $db->insertIgnore($sql, $dbinfo);
        $db->execute($sql, [C\PUBLIC_USER_ID, $item_id, $type_id]);
        $time = ($time > 0) ? $time : time();
        self::addWithDb($user_id, $item_id, $type_id, $db, $time);
    }
    /**
     * Used to add a count record related to a particular user for a particular
     * activity to the impression analytics. This entails adding both
     * a log-like record of when the activity happened and incrementing a
     * global count of this activity. This static method version requires
     * having an initialized data source manager and may be appropriate to call
     * in the context of another model.
     *
     * @param int $user_id id of user we are adding analytic information for
     * @param int $item_id id of particular item we are adding analytic
     *      information of
     * @param int $type_id type of particular item we are adding analytic
     *      information of (group, wiki, thread, etc)
     * @param object $db a DatasourceManager used to query the Yioop
     *      Yioop database
     * @param int $input_timestamp optional UNIX timestamp to use for impression
     *    tracking; if set to -1 or omitted, the current time will be used
     */
    public static function addWithDb($user_id, $item_id, $type_id, $db,
        $input_timestamp = -1)
    {
        if (!C\p('GROUP_ANALYTICS_MODE') ||
            C\p('GROUP_ANALYTICS_MODE') == "0" ||
            !is_numeric($user_id) || !is_numeric($item_id)) {
            return;
        }
        $input_timestamp = ($input_timestamp > 0) ? $input_timestamp : time();
        $dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
            "DB_USER" => C\p('DB_USER'), "DB_PASSWORD" => C\p('DB_PASSWORD'),
            "DB_NAME" => C\p('DB_NAME')];
        $log_folder  = C\LOG_DIR . "/" . self::IMPRESSION_FOLDER;
        $pdb = new LSF\PartitionDocumentBundle($log_folder,
            self::IMPRESSION_FORMAT);
        $buffer_file = $log_folder . '/' . self::BUFFER_FILE;
        $line = implode(',', [(int)$user_id, (int)$item_id, (int)$type_id,
            (int)$input_timestamp]) . "\n";
        file_put_contents($buffer_file, $line, FILE_APPEND);
        clearstatcache(true, $buffer_file);
        if (filesize($buffer_file) >= self::MAX_TMP_BUFFER_SIZE) {
            $lines = file($buffer_file, FILE_IGNORE_NEW_LINES |
                FILE_SKIP_EMPTY_LINES) ?: [];
            $rows = [];
            foreach ($lines as $l) {
                list($u, $i, $t, $ts) = explode(',', $l);
                $rows[] = ['user_id' => (int)$u, 'item_id' => (int)$i,
                    'type_id' => (int)$t, 'timestamp' => (int)$ts];
            }
            $pdb->put($rows);
            file_put_contents($buffer_file, '');
        }
        $sql = "DELETE FROM ITEM_IMPRESSION_SUMMARY
            WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = ".
            C\MOST_RECENT_VIEW;
        $db->execute($sql, [$user_id, $item_id, $type_id]);
        $sql = "INSERT INTO ITEM_IMPRESSION_SUMMARY VALUES
            (?, ?, ?, " . C\MOST_RECENT_VIEW .", ?, 0, -1, -1)";
        $sql = $db->insertIgnore($sql, $dbinfo);
        $db->execute($sql, [$user_id, $item_id, $type_id, $input_timestamp]);
        // Add one to the forever count of ($user_id, $item_id, $type_id)
        $sql = "UPDATE ITEM_IMPRESSION_SUMMARY
            SET NUM_VIEWS = NUM_VIEWS + 1 WHERE USER_ID=? AND
            ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = ". C\FOREVER . "
            AND UPDATE_TIMESTAMP = 0";
        $db->execute($sql, [C\PUBLIC_USER_ID, $item_id, $type_id]);
    }
    /**
     * Used to delete information related to a particular user from the
     * impression analytics. This static method version requires
     * having an initialized data source manager and may be appropriate to call
     * in the context of another model.
     *
     * @param int $user_id id of user we are deleting analytic information for
     * @param int $item_id id of particular item we are deleting analytic
     *      information of
     * @param int $type_id type of particular item we are deleting analytic
     *      information of (group, wiki, thread, etc)
     * @param object $db a DatasourceManager used to query the Yioop
     *      Yioop database
     */
    public static function deleteWithDb($user_id, $item_id, $type_id, $db)
    {
        $sql = "DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE USER_ID=? AND
            ITEM_ID=? AND ITEM_TYPE=?";
        $db->execute($sql, [$user_id, $item_id, $type_id]);
        $db->execute($sql, [C\PUBLIC_USER_ID, $item_id, $type_id]);
        // Also delete records from ITEM_IMPRESSION_STAT
        $sql = "DELETE FROM ITEM_IMPRESSION_STAT WHERE ITEM_ID=? AND
            ITEM_TYPE=?";
        $db->execute($sql, [$item_id, $type_id]);
    }
    /**
     * Deletes statistics related to web queries.
     */
    public function deleteQueryStatistics()
    {
        $db = $this->db;
        $db->execute($sql);
        $sql = "DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE ITEM_TYPE= " .
            C\QUERY_IMPRESSION . " OR ITEM_TYPE=" . C\CACHE_IMPRESSION;
        $db->execute($sql);
        $sql = "DELETE FROM QUERY_ITEM";
        $db->execute($sql);
    }
    /**
     * Deletes statistics related to web queries.
     */
    public function deleteOldRawImpressions()
    {
        $impression_dir = C\LOG_DIR . "/" . self::IMPRESSION_FOLDER;
        $this->db->unlinkRecursive($impression_dir);
    }
}
