<?php

/**
 * Matomo - free/libre analytics platform
 *
 * @link    https://matomo.org
 * @license https://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
 */
namespace Piwik\Plugins\SitesManager;

use Piwik\Access;
use Piwik\Db;
use Piwik\Common;
use Exception;
class Model
{
    private static $rawPrefix = 'site';
    private $table;
    public function __construct()
    {
        $this->table = Common::prefixTable(self::$rawPrefix);
    }
    public function createSite($site)
    {
        $db = $this->getDb();
        $db->insert($this->table, $site);
        $idSite = $db->lastInsertId();
        return $idSite;
    }
    /**
     * Returns all websites belonging to the specified group
     * @param string $group Group name
     * @return array of sites
     */
    public function getSitesFromGroup($group)
    {
        $db = $this->getDb();
        $sites = $db->fetchAll("SELECT * FROM " . $this->table . "\n                                WHERE `group` = ?", $group);
        return $sites;
    }
    /**
     * Returns the list of website groups, including the empty group
     * if no group were specified for some websites
     *
     * @return array of group names strings
     */
    public function getSitesGroups()
    {
        $db = $this->getDb();
        $groups = $db->fetchAll("SELECT DISTINCT `group` FROM " . $this->table);
        $cleanedGroups = array();
        foreach ($groups as $group) {
            $cleanedGroups[] = $group['group'];
        }
        return $cleanedGroups;
    }
    /**
     * Returns all websites
     *
     * @return array The list of websites, indexed by idsite
     */
    public function getAllSites()
    {
        $db = $this->getDb();
        $sites = $db->fetchAll("SELECT * FROM " . $this->table . " ORDER BY idsite ASC");
        return $sites;
    }
    /**
     * Returns the list of the website IDs that received some visits since the specified timestamp.
     *
     * @param string $time
     * @param string $now
     * @return array The list of website IDs
     */
    public function getSitesWithVisits($time, $now)
    {
        $sites = Db::fetchAll("\n            SELECT idsite FROM " . $this->table . " s\n            WHERE EXISTS (\n                SELECT 1\n                FROM " . Common::prefixTable('log_visit') . " v\n                WHERE v.idsite = s.idsite\n                AND visit_last_action_time > ?\n                AND visit_last_action_time <= ?\n                LIMIT 1)\n        ", array($time, $now));
        return $sites;
    }
    /**
     * Returns the list of websites ID associated with a URL.
     *
     * @param array $urls
     * @return array list of websites ID
     */
    public function getAllSitesIdFromSiteUrl(array $urls)
    {
        $siteUrlTable = Common::prefixTable('site_url');
        $db = $this->getDb();
        $ids = $db->fetchAll(
            'SELECT idsite FROM `' . $this->table . '`
                    WHERE main_url IN ( ' . Common::getSqlStringFieldsArray($urls) . ') ' . 'UNION
                SELECT idsite FROM `' . $siteUrlTable . '`
                    WHERE url IN ( ' . Common::getSqlStringFieldsArray($urls) . ') ',
            // Bind
            array_merge($urls, $urls)
        );
        return $ids;
    }
    /**
     * Returns the list of websites ID associated with a URL.
     *
     * @param string $login
     * @param array $urls
     * @return array list of websites ID
     */
    public function getSitesIdFromSiteUrlHavingAccess($login, $urls)
    {
        $siteUrlTable = Common::prefixTable('site_url');
        $sqlAccessSite = Access::getSqlAccessSite('idsite');
        $db = $this->getDb();
        $ids = $db->fetchAll(
            'SELECT idsite
                FROM `' . $this->table . '`
                    WHERE main_url IN ( ' . Common::getSqlStringFieldsArray($urls) . ')' . 'AND idsite IN (' . $sqlAccessSite . ') ' . 'UNION
                SELECT idsite
                FROM `' . $siteUrlTable . '`
                    WHERE url IN ( ' . Common::getSqlStringFieldsArray($urls) . ')' . 'AND idsite IN (' . $sqlAccessSite . ')',
            // Bind
            array_merge($urls, array($login), $urls, array($login))
        );
        return $ids;
    }
    /**
     * Returns all websites with a timezone matching one the specified timezones
     *
     * @param array $timezones
     * @return array
     * @ignore
     */
    public function getSitesFromTimezones($timezones)
    {
        $query = 'SELECT idsite FROM `' . $this->table . '`
                  WHERE timezone IN (' . Common::getSqlStringFieldsArray($timezones) . ')
                  ORDER BY idsite ASC';
        $db = $this->getDb();
        $sites = $db->fetchAll($query, $timezones);
        return $sites;
    }
    public function deleteSite($idSite)
    {
        $db = $this->getDb();
        $db->query("DELETE FROM `" . $this->table . "` WHERE idsite = ?", $idSite);
        $db->query("DELETE FROM `" . Common::prefixTable("site_url") . "` WHERE idsite = ?", $idSite);
        $db->query("DELETE FROM `" . Common::prefixTable("access") . "` WHERE idsite = ?", $idSite);
    }
    /**
     * Returns the list of websites from the ID array in parameters.
     *
     * @param array $idSites list of website ID
     * @param int|null|false $limit
     * @param string[] $siteTypesToExclude Optional list of site types to exclude. E.g. intranet or rollup.
     * @return array
     */
    public function getSitesFromIds($idSites, $limit = \false, $siteTypesToExclude = [])
    {
        if (count($idSites) === 0) {
            return array();
        }
        if ($limit) {
            $limit = "LIMIT " . (int) $limit;
        } else {
            $limit = '';
        }
        $idSites = array_map('intval', $idSites);
        $bind = [];
        $typeWhere = '';
        if (!empty($siteTypesToExclude)) {
            $bind = $siteTypesToExclude;
            $typeWhere = ' AND type NOT IN (' . Common::getSqlStringFieldsArray($siteTypesToExclude) . ') ';
        }
        $db = $this->getDb();
        $sites = $db->fetchAll("SELECT * FROM " . $this->table . "\n                                WHERE idsite IN (" . implode(", ", $idSites) . ") {$typeWhere}\n                                ORDER BY idsite ASC {$limit}", $bind);
        return $sites;
    }
    /**
     * Returns the website information : name, main_url
     *
     * @throws Exception if the site ID doesn't exist or the user doesn't have access to it
     * @param int $idSite
     * @return array
     */
    public function getSiteFromId($idSite)
    {
        $db = $this->getDb();
        $site = $db->fetchRow("SELECT * FROM " . $this->table . "\n                               WHERE idsite = ?", $idSite);
        return $site;
    }
    /**
     * Returns the list of all the website IDs registered.
     * Caller must check access.
     *
     * @return int[]|string[] The list of website IDs
     */
    public function getSitesId()
    {
        $result = Db::fetchAll("SELECT idsite FROM " . Common::prefixTable('site'));
        $idSites = array();
        foreach ($result as $idSite) {
            $idSites[] = $idSite['idsite'];
        }
        return $idSites;
    }
    /**
     * Returns the list of all URLs registered for the given idSite (main_url + alias URLs).
     *
     * @throws Exception if the website ID doesn't exist or the user doesn't have access to it
     * @param int $idSite
     * @return array list of URLs
     */
    public function getSiteUrlsFromId($idSite)
    {
        $urls = $this->getAliasSiteUrlsFromId($idSite);
        $site = $this->getSiteFromId($idSite);
        if (empty($site)) {
            return $urls;
        }
        return array_merge(array($site['main_url']), $urls);
    }
    /**
     * Returns the list of alias URLs registered for the given idSite.
     * The website ID must be valid when calling this method!
     *
     * @param int $idSite
     * @return array list of alias URLs
     */
    public function getAliasSiteUrlsFromId($idSite)
    {
        $db = $this->getDb();
        $result = $db->fetchAll("SELECT url FROM " . Common::prefixTable("site_url") . "\n                                 WHERE idsite = ?", $idSite);
        $urls = array();
        foreach ($result as $url) {
            $urls[] = $url['url'];
        }
        return $urls;
    }
    /**
     * Returns all known URLs (main and alias) for all sites, each row containing idsite and url.
     *
     * @return array list of URLs
     */
    public function getAllKnownUrlsForAllSites()
    {
        $db = $this->getDb();
        $mainUrls = $db->fetchAll("SELECT idsite, main_url as url FROM " . Common::prefixTable("site"));
        $aliasUrls = $db->fetchAll("SELECT idsite, url FROM " . Common::prefixTable("site_url"));
        return array_merge($mainUrls, $aliasUrls);
    }
    public function updateSite($site, $idSite)
    {
        $idSite = (int) $idSite;
        $db = $this->getDb();
        $db->update($this->table, $site, "idsite = {$idSite}");
    }
    /**
     * Returns the list of unique timezones from all configured sites.
     *
     * @return array ( string )
     */
    public function getUniqueSiteTimezones()
    {
        $results = Db::fetchAll("SELECT distinct timezone FROM " . $this->table);
        $timezones = array();
        foreach ($results as $result) {
            $timezones[] = $result['timezone'];
        }
        return $timezones;
    }
    /**
     * Updates the field ts_created for the specified websites.
     *
     * @param int[] $idSites Site IDs whose ts_created may be updated.
     * @param string $minDateSql The minimum creation date; sites with a later ts_created are lowered to this value.
     *
     * @ignore
     */
    public function updateSiteCreatedTime($idSites, $minDateSql)
    {
        $idSites = array_map('intval', $idSites);
        $query = "UPDATE " . $this->table . " SET ts_created = ?" . " WHERE idsite IN ( " . implode(",", $idSites) . " ) AND ts_created > ?";
        $bind = array($minDateSql, $minDateSql);
        Db::query($query, $bind);
    }
    /**
     * Returns all used type ids (unique)
     * @return array of used type ids
     */
    public function getUsedTypeIds()
    {
        $types = array();
        $db = $this->getDb();
        $rows = $db->fetchAll("SELECT DISTINCT `type` as typeid FROM " . $this->table);
        foreach ($rows as $row) {
            $types[] = $row['typeid'];
        }
        return $types;
    }
    /**
     * Insert the list of alias URLs for the website.
     * The URLs must not exist already for this website!
     */
    public function insertSiteUrl($idSite, $url)
    {
        $db = $this->getDb();
        $db->insert(Common::prefixTable("site_url"), array('idsite' => (int) $idSite, 'url' => $url));
    }
    /**
     * Filter a list of site IDs based on the provided search pattern and return all columns for the remaining sites.
     *
     * @param int[] $ids
     * @param string $pattern
     * @param int|null $limit
     * @param string[] $siteTypesToExclude Optional list of site types to exclude. E.g. intranet or rollup.
     * @return array
     */
    public function getPatternMatchSites($ids, $pattern, $limit, $siteTypesToExclude = [])
    {
        $ids_str = '';
        foreach ($ids as $id_val) {
            $ids_str .= (int) $id_val . ' , ';
        }
        $ids_str .= (int) $id_val;
        $bind = self::getPatternMatchSqlBind($pattern);
        // Also match the idsite
        $where = '';
        if (is_numeric($pattern)) {
            $bind[] = $pattern;
            $where = 'OR s.idsite = ?';
        }
        $typeWhere = '';
        if (!empty($siteTypesToExclude)) {
            $bind = array_merge($bind, $siteTypesToExclude);
            $typeWhere = 'AND type NOT IN (' . Common::getSqlStringFieldsArray($siteTypesToExclude) . ')';
        }
        $query = "SELECT *\n                  FROM " . $this->table . " s\n                  WHERE ( " . self::getPatternMatchSqlQuery('s') . "\n                          {$where} ) {$typeWhere}\n                     AND idsite in ({$ids_str})";
        if ($limit && intval($limit) > 0) {
            $query .= " LIMIT " . (int) $limit;
        }
        $db = $this->getDb();
        $sites = $db->fetchAll($query, $bind);
        return $sites;
    }
    public static function getPatternMatchSqlQuery($table)
    {
        return "({$table}.name like ? OR {$table}.main_url like ? OR {$table}.group like ?)";
    }
    public static function getPatternMatchSqlBind($pattern)
    {
        return array('%' . $pattern . '%', 'http%' . $pattern . '%', '%' . $pattern . '%');
    }
    /**
     * Delete all the alias URLs for the given idSite.
     */
    public function deleteSiteAliasUrls($idsite)
    {
        $db = $this->getDb();
        $db->query("DELETE FROM " . Common::prefixTable("site_url") . " WHERE idsite = ?", $idsite);
    }
    private function getDb()
    {
        return Db::get();
    }
}
