<?php

use Illuminate\Support\Facades\DB;
use Mtc\Core\PaginationTemplate;
/**
 * LISTING CLASS
 *
 * @author Rihards Silins
 * @copyright MTC Media 2014
 * @version 5 04/11/2016
 * @access public
 *
 * MTC CMS2 Listing class.
 *
 * Class for filtering, ordering & listing out "pages" in lists.
 * This class has a powerfull page-pagedata joining engine so that
 * you can filter pages by both native attributes and pagedata you've
 * customly added.
 *
 * Help: http://wiki.mtcmedia.co.uk/index.php?title=CMS2#Retrieve_subpages_.2F_List_a_listing_.2F_News_page
 *
 * TODO: PDO this.
 *
 */
class Listing
{
    public $list                        = array();              // Where list is auto stored after Listing::getList()
    public $pagination                  = array();              // Pagination. autstored after Listing::getPagination()
    public $archive                     = array();              // Archive. autstored after Listing::getArchive()

    public $language                    = "";                   // "" is English

    protected $container_id             = 0;                    // ID of page that has sub pages that are articles/events or other listing items
    public $container_pages          = array();              // Array of pages set in $container_id

    protected $current_page             = 1;                    // Current page of the list pagination. change using Listing::setCurrentPage()
    protected $limit                    = PHP_INT_MAX;          // Number of list items do displat per list pagination page. Changes using Listing::setLimit()
    protected $only_published           = true;                 // Display only published pages true/false. Change using Listing::onlyPublished()
    protected $only_default_pages       = true;                 // Display only type default pages true/false. Change using Listing::onlyDefaultPages()
    protected $debug                    = 0;                    // 0 - no debug / 1 - debug
    protected $cache                    = 0;                    // 0 - no cache / 1 - cache queries

    protected $offset                   = 0;                    // LIMIT offset. Set according to $this->limit and $this->current_page
    protected $total_listing_pages      = 1;                    // Total listing pages. Set by Listing::getPagination()
    protected $total_articles;                                  // Total number of listing items. Set by Listing::getPagination()

    public $get_params                  = array(                // Params for $_GET. Change using Listing::setGetParams()
        'pagination'    => "page",                              // Get param for paginating pages.
        'from'          => "filter_from",                       // Get param for filtering pages 'from' "www.domain.com?filter_from=1"
        'to'            => "filter_to",                         // Get param for filtering pages 'to' "www.domain.com?filter_to=10"
        'filter'        => "filter",                            // Get param for filtering for example by A "www.domain.com?filter=A"
        'order'         => "order",                             // Get param for ordering pages
        'direction'     => "direction",                         // Get param for setting ordering direction
    );

    protected $order                    = array("`order` ");    // Order stack. See method for correct param format. Change using Listing::setOrder()
    protected $_order                   = "ORDER BY `order` ";  // Compiled order

    protected $filter                   = array();              // Filter stack. See method for correct param format. Change using Listing::setFilter()
    protected $_filter                  = "WHERE 1 ";           // Filter stack. See method for correct param format. Change using Listing::setFilter()

    protected $select                   = array("`id` ");       // Select. There needs to be a `id` for fetching pages.  Change using Listing::setSelect()
    protected $_select                  = "SELECT `id` ";       //      (One can do a `as` to get the id from somewhere else)

    /**
     * Query parameters
     * @var mixed[]
     */
    protected $params                   = array();

    protected $from                     = "FROM `pages` ";      // From. Change using Listing::setFrom()

    /**
     * Listing()
     *
     * @param int/int[] container_id
     * @return $this / false
     *
     * Construct function.
     * Generates object and sets the id of the page that contains the blog
     */
    public function __construct($container_id = 0)
    {
        if (defined('CMS_MULTI_LANG') && CMS_MULTI_LANG === true && CMS_MULTI_LANG_OTHER_DEFAULT_TO_P_LANGUAGE === true) {
            global $p;
            if (!empty($p->language)) {
                $this->setLanguage($p->language);
            }
        }

        if (isset($container_id) && (is_numeric($container_id) || is_array($container_id))) {
            $this->container_id = $container_id;
            $this->fetchContainerPages();
            return $this;
        }
        return $this;
    }

    /**
     * Fetch the page object of the container_id(s)
     * @return bool success
     */
    private function fetchContainerPages()
    {
        $this->container_pages = array();
        if (is_array($this->container_id)) {
            $i=0;
            foreach ($this->container_id as $container_id) {
                $this->container_pages[$i] = new Page();
                if (defined('CMS_MULTI_LANG') && CMS_MULTI_LANG === true && CMS_MULTI_LANG_OTHER_DEFAULT_TO_P_LANGUAGE === true) {
                    $this->container_pages[$i]->setLanguage($this->language);
                }
                $this->container_pages[$i]->Get($container_id);
                $this->cache = 1; // cache enabled due to multiple containers
                $i++;
            }
        } else {
            $this->container_pages[0] = new Page();
            if (defined('CMS_MULTI_LANG') && CMS_MULTI_LANG === true && CMS_MULTI_LANG_OTHER_DEFAULT_TO_P_LANGUAGE === true) {
                $this->container_pages[0]->setLanguage($this->language);
            }
            $this->container_pages[0]->Get($this->container_id);
            if ($this->container_pages[0]->listing_container == 1) {
                $this->cache = 1;
            }
        }
        return true;
    }

    public static function newInstance($container_id = 0)
    {
        return new Listing($container_id);
    }

    /**
     * Listing::setGetParams();
     *
     * Params for $_GET.
     *     'pagination'     => "page",            // Get param for paginating pages.
     *     'from'             => "filter_from",    // Get param for filtering pages 'from' "www.domain.com?filter_from=1"
     *     'to'             => "filter_to",        // Get param for filtering pages 'to' "www.domain.com?filter_to=10"
     *     'filter'         => "filter",        // Get param for filtering for example by A "www.domain.com?filter=A"
     *     'order'         => "order",            // Get param for ordering pages
     *     'direction'     => "direction",        // Get param for setting ordering direction
     *
     * @param string[] new_params
     * @return $this
     */
    public function setGetParams($new_params)
    {
        foreach ($this->get_params as $key => $value) {
            if (isset($new_params[$key]) && !empty($new_params[$key])) {
                $this->get_params[$key] = $new_params[$key];
            }
        }
        return $this;
    }

    /**
     * Listing::setCurrentPage()
     *
     * Sets pagination page. Just pass $_GET['page']. Even if not defined.
     * This method is going to take care of the rest.
     *
     * @param int current_page
     * @return $this
     */
    public function setCurrentPage($current_page)
    {
        if (isset($current_page) && !empty($current_page) && preg_match('/^[0-9]+$/', $current_page)) {
            $this->current_page = $current_page;
        }
        return $this;
    }

    /**
     * Listing::setLanguage()
     *
     * Set langauge of the listing. Thus setting what language should be queried for and displayed
     *
     * @param string $language
     * @return $this
     */
    public function setLanguage($language)
    {
        if ($language === "" || defined("CMS_MULTI_LANG_CODE_".$language)) {
            $this->language = $language;
            $this->fetchContainerPages();
        }
        return $this;
    }

    /**
     * Listing::onlyDefaultPages()
     *
     * Changes if to include only default type pages or not
     *
     * @param bool only_published
     * @return $this
     */
    public function onlyDefaultPages($only_default_pages)
    {
        if (isset($only_default_pages) && is_bool($only_default_pages)) {
            $this->only_default_pages = $only_default_pages;
        }
        return $this;
    }

    /**
     * Listing::onlyPublished()
     *
     * Changes if to include only published pages or not
     *
     * @param bool only_published
     * @return $this
     */
    public function onlyPublished($only_published)
    {
        if (isset($only_published) && is_bool($only_published)) {
            $this->only_published = $only_published;
        }
        return $this;
    }

    /**
     * Listing::setFilter()
     *
     * Sets filter/where.
     *
     * EXAMPLE:
     * [
     *        "AND",
     *        "OR",
     *        "AND (",
     *        "OR (",
     *        ")",
     *        SIMPLE_SQL_WHERE_ITEM,
     *        SIMPLE_SQL_WHERE_STRUCTURE(AND/OR/(/)/),
     *        array(PAGEDATA_DATA_NAME, VALUE),
     *        array(PAGEDATA_DATA_NAME, VALUE, OPERATOR),
     *        array(PAGEDATA_DATA_NAME, VALUE, OPERATOR, TYPE),
     *        array(array(PAGEDATA_LIST_NAME, PAGEDATA_INDEX_OF_LIST_ITEM, PAGEDATA_DATA_NAME), VALUE),
     *        array(array(PAGEDATA_LIST_NAME, PAGEDATA_INDEX_OF_LIST_ITEM, PAGEDATA_DATA_NAME), VALUE, OPERATOR),
     *        array(array(PAGEDATA_LIST_NAME, PAGEDATA_INDEX_OF_LIST_ITEM, PAGEDATA_DATA_NAME), VALUE, OPERATOR, TYPE),
     *
     *        // simple string filter items
     *        "`pages`.`serchable` = 1 "
     *        "`serchable` = 1 "
     *
     *         // Sold = '1' or Always Sold = '1'
     *        "(",
     *        array("Sold", "1"),
     *        "OR",
     *        array("Aways Sold", "1"),
     *        ")",
     *
     *        // Sold = '1' or Sold = NULL (not set)
     *        "(",
     *        array("Sold", "1"),
     *        "OR",
     *        array("Sold", "NULL"),
     *        ")",
     *
     *        // Archive > '1'
     *        array("Archive", "1", ">"),
     *
     *        // Archive LIKE '1'
     *        array("Archive", "1", "LIKE"),
     *
     *        // Search Text LIKE '%foobar%'
     *        array("Search Text", "%foobar%", "LIKE"),
     *
     *        // by default the glue is "AND",
     *        // Sold = '1' AND Field > '1'
     *        array("Sold", "1"),
     *        array("Field", "1", ">"),
     *
     *        // In list Meta (first item) data Archive = 1
     *        array(array("Meta", 0, "Archive"), "1"),
     *
     *        //// DATE
     *
     *        // Date > NOW()
     *        array("Date", NOW(), ">","datetime"),
     *
     *        // Date = '2014-03-05'
     *        array("Date", "2014-03-05", "=", "date"),
     *
     *        // Date > '2014-03-05' AND Date < '2014-04-05'
     *        array("Date", "2014-03-05", ">", "date"),
     *        array("Date", "2014-04-05", "<", "date"),
     *]
     *
     * @param array filter
     * @return @this
     */
    public function setFilter($filter)
    {
        if (isset($filter) && is_array($filter)) {
            $this->filter = $filter;
        }
        return $this;
    }

    /**
     * Listing::setSelect()
     *
     * Set the select for the listing queries
     *
     * EXAMPLE:
     * [
     *      SIMPLE_SQL_SELECT_ITEM,
     *      array(SELECTED_PAGEDATA_ALIAS, PAGEDATA_DATA_NAME),
     *      array(SELECTED_PAGEDATA_ALIAS, PAGEDATA_DATA_NAME, TYPE),
     *      array(SELECTED_PAGEDATA_ALIAS, array(PAGEDATA_LIST_NAME, PAGEDATA_INDEX_OF_LIST_ITEM, PAGEDATA_DATA_NAME)),
     *      array(SELECTED_PAGEDATA_ALIAS, array(PAGEDATA_LIST_NAME, PAGEDATA_INDEX_OF_LIST_ITEM, PAGEDATA_DATA_NAME), TYPE),
     *
     *      "`id` ",
     *      " (SELECT `page_id` FROM `translate_table` WHERE `translate_table`.`source_page_id` = `pages`.`id` ) as `id` ",
     *       array("pd_date", array("date", 0, "date"), "datetime"),
     *       array("pd_date", array("date", 0, "date"), "datetime"),
     *       array("pd_name", array("name", 0, "name")))
     *       array("hits", array("hits", 0, "hits"), "int"),
     *       array("pd_hits", "hits", "int"),
     *       array("show", array("show", 0, "show"), "tinyint"),
     * ]
     *
     * @param array mixed $select_stack
     * @return $this
     */
    public function setSelect($select_stack)
    {
        if (isset($select_stack)) {
            $this->select = $select_stack;
        }
        return $this;
    }

    /**
     * Listing::setLimit
     *
     * Set how many listing items/articles per pagination page
     *
     * @param int limit
     * @return $this
     */
    public function setLimit($limit)
    {
        if (isset($limit) && !empty($limit) && preg_match('/^[0-9]+$/', $limit)) {
            $this->limit = $limit;
        }
        return $this;
    }

    /**
     * Listing::setDebug
     *
     * Set debug. Right now - eaches queries before running them;
     *
     * @param int debug
     * @return $this
     */
    public function setDebug($debug)
    {
        if (isset($debug) && !empty($debug) && preg_match('/^[0-9]+$/', $debug)) {
            $this->debug = $debug;
        }
        return $this;
    }

    /**
     * Listing::setOrder()
     *
     * Sets the ordering
     *
     * EXAMPLE:
     * [
     *        SIMPLE_SQL_ORDER_ITEM,
     *        array(PAGEDATA_DATA_NAME),
     *        array(PAGEDATA_DATA_NAME, ORDER_DIRECTION),
     *        array(PAGEDATA_DATA_NAME, ORDER_DIRECTION, TYPE),
     *        array(array(PAGEDATA_LIST_NAME, PAGEDATA_INDEX_OF_LIST_ITEM, PAGEDATA_DATA_NAME)),
     *        array(array(PAGEDATA_LIST_NAME, PAGEDATA_INDEX_OF_LIST_ITEM, PAGEDATA_DATA_NAME),ORDER_DIRECTION),
     *        array(array(PAGEDATA_LIST_NAME, PAGEDATA_INDEX_OF_LIST_ITEM, PAGEDATA_DATA_NAME),ORDER_DIRECTION,TYPE),
     *
     *        array("name"),
     *        array(array("details", 0, "name")),
     *        array(array("details", 0, "name"),"DESC"),
     *        array("date", "ASC","datetime"),
     *        "`order` ",
     *        "`order` DESC",
     *        "`order` ASC",
     *        "`id` ASC",
     * ]
     *
     * @param array mixed order_stack
     * @return $this
     */
    public function setOrder($order)
    {
        $this->order = $order;
        return $this;
    }

    /**
     * Listing::_compileFilter()
     *
     * Compiles filter stack to pure sql
     *
     * @param array mixed filter
     * @return string sql_where_statement
     */
    protected function _compileFilter($raw_filter)
    {

        $sql_result  = "WHERE 1 ";

        if ($this->only_default_pages) {
            $sql_result .= "AND `type` = 'default' ";
        }

        if (!empty($this->container_id)) {
            if (is_array($this->container_id)) {
                $sql_result .= "AND (";
                for ($i=0; $i < count($this->container_id); $i++) {
                    if ($i > 0) {
                        $sql_result .= "OR ";
                    }
                    $sql_result .= "`sub_id` = ".CmsPdo::cleanDB($this->container_id[$i])." ";
                }
                $sql_result .= ") ";
            } else {
                $sql_result .= "AND `sub_id` = ".CmsPdo::cleanDB($this->container_id)." ";
            }
        }
        if ($this->only_published) {
            $sql_result .= "AND `published` = 1 ";
        }

        // bool for telling the compiler if a glue " AND / OR / AND (/ OR ("
        //     has already been inserted thus not having the needing to add the
        //   automatic " AND "
        $last_stack_element_was_glue = false;

        for ($j=0; $j<count($raw_filter); $j++) {
            // check for glue
            if ($last_stack_element_was_glue == true) {
                // there was glue before reset
                $last_stack_element_was_glue = false;

                if (!is_array($raw_filter[$j])) {
                    $sql_result .= " ".$raw_filter[$j]." ";
                    continue;
                }

            } else {
                if (is_array($raw_filter[$j])) {
                    $sql_result .= " AND ";
                } else if (in_array($raw_filter[$j], array("OR","AND","AND (", "OR ("))) {
                    $sql_result .= " ".$raw_filter[$j]." ";
                    $last_stack_element_was_glue = true;
                    continue;
                } else if (in_array($raw_filter[$j], array(")"))) {
                    $sql_result .= " ".$raw_filter[$j]." ";
                    continue;
                } else {
                    $sql_result .= " AND ".$raw_filter[$j]." ";
                    continue;
                }
            }

            $operator = "= ";
            $operator_after_value = "";
            $value_seperator = ", ";
            if (isset($raw_filter[$j][2]) &&
                in_array(
                    $raw_filter[$j][2],
                    array(
                        ">","<","=","LIKE",">=","<=","IS","IS NOT","IN","NOT IN","REGEXP", "NOT REGEXP",
                        "NOT LIKE", "!=", "<>", "<=>", "&&", "||", "RLIKE", "BETWEEN"
                    )
                )
            ) {
                $operator = $raw_filter[$j][2];
                // special cases
                if ($operator == "IN") {
                    $operator = "IN (";
                    $operator_after_value = ") ";
                } else if ($operator == "NOT IN") {
                    $operator = "NOT IN (";
                    $operator_after_value = ") ";
                } else if ($operator == "BETWEEN") {
                    $value_seperator = " AND ";
                }
            }

            $type = "`value`";
            if (isset($raw_filter[$j][3])) {
                if ($raw_filter[$j][3] == "date" || $raw_filter[$j][3] == "datetime" || $raw_filter[$j][3] == "time") {
                    $type = "`datetime_value`";
                } else if ($raw_filter[$j][3] == "int") {
                    $type = "`int_value`";
                } else if ($raw_filter[$j][3] == "float") {
                    $type = "`float_value`";
                } else if ($raw_filter[$j][3] == "varchar") {
                    $type = "`varchar_value`";
                } else if ($raw_filter[$j][3] == "tinyint") {
                    $type = "`tinyint_value`";
                }
            }


            $value = "NULL ";

            if (isset($raw_filter[$j][1])) {
                if (is_array($raw_filter[$j][1]) && !empty($raw_filter[$j][1])) {
                    $value = "";
                    for ($i=0; $i < count($raw_filter[$j][1]); $i++) {
                        if ($i>0) {
                            $value .= $value_seperator;
                        }
                        $placeholder = ":l_p_".count($this->params);
                        $this->params[$placeholder] = $raw_filter[$j][1][$i];
                        $value .= $placeholder;
                    }
                } else {
                    $placeholder = ":l_p_".count($this->params);
                    $this->params[$placeholder] = $raw_filter[$j][1];
                    $value = $placeholder;
                }
            }

            $offset = 0;
            $list_searching = false;
            if (is_array($raw_filter[$j][0])) {
                $list_searching = true;
                $list_name = $raw_filter[$j][0][0];
                $offset = $raw_filter[$j][0][1];
                $data_name = $raw_filter[$j][0][2];
            } else {
                $list_name = $raw_filter[$j][0];
                $data_name = $raw_filter[$j][0];
            }

            $group_check = "";
            if (!empty($raw_filter[$j][4]) && $raw_filter[$j][4] == "group_check") {
                $group_check = " AND `page_list_item_data`.`value` ".$operator." ".$value." ".$operator_after_value." ";
            }

            $sql_result .= " (
                SELECT ".$type."
                FROM `page_list_item_data`
                WHERE
                    1
                    AND `page_list_item_data`.`name` = '".CmsPdo::cleanDB($data_name)."'
                    AND `page_list_item_data`.`page_id` = `pages`.`id`";

            if (!empty($group_check)) {
                $sql_result .= $group_check;
            }

            if (defined('CMS_MULTI_LANG') && CMS_MULTI_LANG === true) {
                $sql_result .= "
            AND `page_list_item_data`.`language` = '".$this->language."'
                ";

            }

            if ($list_searching) {
                $sql_result .= "
            AND `page_list_item_data`.`page_list_item_id` = (
                SELECT `id`
                FROM `page_list_item`
                WHERE
                    1
                    AND `page_list_item`.`page_id` = `pages`.`id`
                    AND `page_list_item`.`page_list_id` = (
                        SELECT `id`
                        FROM  `page_list`
                        WHERE
                            1
                            AND `page_list`.`name` = '".CmsPdo::cleanDB($list_name)."'
                            AND `page_list`.`page_id` = `pages`.`id`
                        LIMIT 1
                    )
                ORDER BY `order`
                LIMIT ".$offset.", 1
            )
                ";
            }
            $sql_result .= "
                LIMIT 1
            ) ".$operator." ".$value." ".$operator_after_value." ";

        }

        return $sql_result;
    }

    /**
     * Listing::_compileOrder()
     *
     * Compiles order stack to pure order by sql statement
     *
     * @param array order_stack
     * @return string sql_result
     */
    protected function _compileOrder($order_stack)
    {

        $sql_result = "";

        for ($j=0; $j<count($order_stack); $j++) {
            if (empty($sql_result)) {
                $sql_result .= " ORDER BY ";
            } else {
                $sql_result .= ", ";
            }

            // if its not an array. just append it and continue
            if (!is_array($order_stack[$j])) {
                $sql_result .= " ".$order_stack[$j]." ";
                continue;
            }

            $offset = 0;
            $list_searching = false;
            if (is_array($order_stack[$j][0])) {
                $list_searching = true;
                $list_name = $order_stack[$j][0][0];
                $offset = $order_stack[$j][0][1];
                $data_name = $order_stack[$j][0][2];
            } else {
                $list_name = $order_stack[$j][0];
                $data_name = $order_stack[$j][0];
            }

            $type = "`value`";
            if ($order_stack[$j][2] == "date" || $order_stack[$j][2] == "datetime" || $order_stack[$j][2] == "time") {
                $type = "`datetime_value`";
            } else if ($order_stack[$j][2] == "int") {
                $type = "`int_value`";
            }

            $sql_result .= " (
                SELECT ".$type."
                FROM `page_list_item_data`
                WHERE
                    1
                    AND `page_list_item_data`.`name` = '".CmsPdo::cleanDB($data_name)."'
                    AND `page_list_item_data`.`page_id` = `pages`.`id`";

            if (defined('CMS_MULTI_LANG') && CMS_MULTI_LANG === true) {
                $sql_result .= "
            AND `page_list_item_data`.`language` = '".$this->language."'
                ";

            }

            if ($list_searching) {
                $sql_result .= "
                AND `page_list_item_data`.`page_list_item_id` = (
                    SELECT `id`
                    FROM `page_list_item`
                    WHERE
                        1
                        AND `page_list_item`.`page_id` = `pages`.`id`
                        AND `page_list_item`.`page_list_id` = (
                            SELECT `id`
                            FROM  `page_list`
                            WHERE
                                1
                                AND `page_list`.`name` = '".CmsPdo::cleanDB($list_name)."'
                                AND `page_list`.`page_id` = `pages`.`id`
                            LIMIT 1
                        )
                    ORDER BY `order`
                    LIMIT ".$offset.", 1
                )

                ";
            }

            $sql_result .= "
                LIMIT 1
            ) ";

            // if DESC / ASC is set
            if (isset($order_stack[$j][1]) && !empty($order_stack[$j][1])) {
                $sql_result .= " ".$order_stack[$j][1];
            }
        }
        return $sql_result;
    }

    /**
     * Listing::_compileSelect()
     *
     * Compiles select stack to pure select sql statement
     *
     * @param array mixed select_stack
     * @return string select_sql
     */
    protected function _compileSelect($select_stack)
    {
        $sql_result = "";

        for ($j=0; $j<count($select_stack); $j++) {
            if (empty($sql_result)) {
                $sql_result .= "SELECT ";
            } else {
                $sql_result .= ", ";
            }

            // if its not an array. just append it and continue
            if (!is_array($select_stack[$j])) {
                $sql_result .= " ".$select_stack[$j]." ";
                continue;
            }

            $offset = 0;
            $list_searching = false;
            if (is_array($select_stack[$j][1])) {
                $list_searching = true;
                $list_name = $select_stack[$j][1][0];
                $offset    = $select_stack[$j][1][1];
                $data_name = $select_stack[$j][1][2];
            } else {
                $list_name = $select_stack[$j][1];
                $data_name = $select_stack[$j][1];
            }

            $type = "`value`";
            if ($select_stack[$j][2] == "date" || $select_stack[$j][2] == "datetime" || $select_stack[$j][2] == "time") {
                $type = "`datetime_value`";
            } else if ($select_stack[$j][2] == "int") {
                $type = "`int_value`";
            }

            $sql_result .= " (
                SELECT ".$type."
                FROM `page_list_item_data`
                WHERE
                    1
                    AND `page_list_item_data`.`name` = '".CmsPdo::cleanDB($data_name)."'
                    AND `page_list_item_data`.`page_id` = `pages`.`id`";

            if (defined('CMS_MULTI_LANG') && CMS_MULTI_LANG === true) {
                $sql_result .= "
            AND `page_list_item_data`.`language` = '".$this->language."'
                ";

            }

            if ($list_searching) {
                $sql_result .= "
                AND `page_list_item_data`.`page_list_item_id` = (
                    SELECT `id`
                    FROM `page_list_item`
                    WHERE
                        1
                        AND `page_list_item`.`page_id` = `pages`.`id`
                        AND `page_list_item`.`page_list_id` = (
                            SELECT `id`
                            FROM  `page_list`
                            WHERE
                                1
                                AND `page_list`.`name` = '".CmsPdo::cleanDB($list_name)."'
                                AND `page_list`.`page_id` = `pages`.`id`
                            LIMIT 1
                        )
                    ORDER BY `order`
                    LIMIT ".$offset.", 1
                )
                ";
            }

            $sql_result .= "
                LIMIT 1
            ) as `".$select_stack[$j][0]."` ";
        }
        return $sql_result;
    }


    /**
     * Listing::run()
     *
     * Sets the listing up for querying. This needs to be executed before doing getTotalNum or getListing or getArchive
     *
     * @param none
     * @return Listing $this
     */
    public function run()
    {

        $this->offset = ($this->current_page - 1 ) * $this->limit;

        $this->_filter = $this->_compileFilter($this->filter);
        $this->_order = $this->_compileOrder($this->order);
        $this->_select = $this->_compileSelect($this->select);

        return $this;
    }

    /**
     * Listing::getTotalNum()
     *
     * Runs the listing and counts how many total results.  You need to call Listing::run before this
     *
     * @param none
     * @return int total_number_of_listing_items;
     */
    public function getTotalNum()
    {
        $this->total_articles = 0;
        $this->_count_select = $this->_compileSelect(array("COUNT(`id`)"));
        $query = $this->_count_select.$this->from.$this->_filter;
        $result = $this->runQuery($query);
        if (empty($result)) {
            return $this->total_articles;
        }
        $this->total_article = $result[0];
        return $this->total_article;
    }

    /**
     * Listing::getArchive()
     * Generate archive tree
     */
    public function getArchive($options = array())
    {
        if (!isset($options['by'])) {
            $options['by'] = 'years and months';
        }
        if (!isset($options['index'])) {
            $options['index'] = array('date','datetime');
        }
        if (!isset($options['display_count'])) {
            $options['display_count'] = true;
        }
        if (!isset($options['eloquent_model_name_column'])) {
            $options['eloquent_model_name_column'] = "name";
        }
        if (!isset($options['month_format'])) {
            $options['month_format'] = "F";
        }
        if (!isset($options['enable_year_links'])) {
            $options['enable_year_links'] = false;
        }
        if (!isset($options['num_of_articles_string_format'])) {
            $options['num_of_articles_string_format'] = " (%d)";
        }
        if (!isset($options['display_empty_months'])) {
            $options['display_empty_months'] = false;
        }
        if (!isset($options['display_empty_years'])) {
            $options['display_empty_years'] = false;
        }

        if (!isset($options['base_url'])) {
            $options['base_url'] = $this->geNondestructiveBaseUrl(true, true);
        } else {
            $options['base_url'] = $options['base_url']."?";
        }

        $result = array();

        if ($options['by'] === 'years and months') {
            if (is_array($options['index'][0])) {
                // Totally not advised for performance performances
                // Need to test this more.
                $select = $this->_compileSelect(array(array("pd_date", array($options['index'][0][0], $options['index'][0][1], $options['index'][0][2]), $options['index'][1])));
            } else {
                $select = $this->_compileSelect(array(array("pd_date", $options['index'][0], $options['index'][1])));
            }

            $archive_date_filters = $this->filter;
            $archive_date_filters[] = array($options['index'][0], "1990-01-01",">=", "datetime");
            $archive_date_filter = $this->_compileFilter($archive_date_filters);
            $order = $this->_compileOrder(array(" pd_date ASC "));
            $query = $select.$this->from.$archive_date_filter.$order;
            $query_result = $this->runQuery($query);
            if (empty($query_result)) {
                return array();
            }

            $date = date_create_from_format("Y-m-d H:i:s", $query_result[0]);
            $from['year'] = $date->format('Y');

            $date = date_create_from_format("Y-m-d H:i:s", $query_result[count($query_result)-1]);
            $to['year'] = $date->format('Y');

            $years = array();
            $y = $to['year'];
            do {
                $years[$y] = array();
                $y--;
            } while ($y >= $from['year']);

            $year = "";
            $month = "";

            for ($i=0; $i < count($query_result); $i++) {
                $date = date_create_from_format("Y-m-d H:i:s", $query_result[$i]);
                if ($year != $date->format("Y")) {
                    $year = $date->format("Y");
                }
                if ($month != $date->format("m")) {
                    $month = $date->format("m");
                }
                if (isset($years[$year][$month])) {
                    $years[$year][$month]++;
                } else {
                    $years[$year][$month] = 1;
                }
            }

            if ($options['display_empty_months'] == true) {
                foreach ($years as $year => $months) {
                    for ($i=1; $i <= 12; $i++) {
                        $month = str_pad($i, 2, '0', STR_PAD_LEFT);
                        if (!isset($years[$year][$month])) {
                            $years[$year][$month] = 0;
                        }
                    }
                    ksort($years[$year]);
                }
            }

            $i=0;
            foreach ($years as $year => $months) {
                $link = "#";
                if ($options['enable_year_links'] == true) {
                    $link = $options['base_url'].$this->get_params['from']."=".$year."-01-01&".$this->get_params['to']."=".$year."-12-31";
                }

                if (count($months) == 0 && !$options['display_empty_years']) {
                    $i++;
                    continue;
                }

                $result[$i] = array(
                    'id' => $year,
                    'title' => $year,
                    'link' => $link,
                    'sub' => array(),
                );

                foreach ($months as $month => $number_of_listing_items) {
                    $date = new DateTime($year."-".$month."-01");

                    $class = "";
                    $data_archive_empty_month = "false";
                    $link = $options['base_url'].$this->get_params['from']."=".$date->format("Y-m-d")."&".$this->get_params['to']."=".$date->modify('last day of this month')->format("Y-m-d");
                    if (empty($number_of_listing_items)) {
                        $class = " cmsArchiveEmptyMonth";
                        $data_archive_empty_month = "true";
                        $link = "#";
                    }

                    $title_num_of_articles_part = sprintf($options['num_of_articles_string_format'], $number_of_listing_items);

                    if ($options['display_count'] == true) {
                        $title = "<span class='cmsArchiveMonthText".$class."'>".$date->format($options['month_format'])."</span><span class='cmsArchiveNumOfArticles'>".$title_num_of_articles_part."</span>";
                    } else {
                        $title = "<span class='cmsArchiveMonthText".$class."'>".$date->format($options['month_format'])."</span>";
                    }

                    $result[$i]['sub'][] = array(
                        'id' => $year."-".$month."-01",
                        'title' => $title,
                        'link' => $link,
                        'sub' => array(),
                        'anchor_attributes' => array(
                            'data-archive-empty-month' => $data_archive_empty_month
                        )
                    );

                }
                $i++;
            }

        } elseif ($options['by'] === 'eloquent_model_id' && !empty($options['eloquent_model_classname'])) {
            $param_name = "";
            if (is_array($options['index'][0])) {
                $param_name = Util::slugify($options['index'][0][0]);
                // Not advised for performance performances
                $select = $this->_compileSelect(array(array("archive_id", array($options['index'][0][0], $options['index'][0][1], $options['index'][0][2]), $options['index'][1])));
            } else {
                $param_name = Util::slugify($options['index'][0]);
                $select = $this->_compileSelect(array(array("archive_id", $options['index'][0], $options['index'][1])));
            }

            $filter = $this->_compileFilter($this->filter);
            $order = $this->_compileOrder(array(" archive_id ASC "));
            $query = $select.$this->from.$filter.$order;
            $query_result = $this->runQuery($query);

            if (empty($query_result)) {
                return array();
            }

            for ($i=0; $i < count($query_result); $i++) {
                if (isset($categories[$query_result[$i]])) {
                    $categories[$query_result[$i]]++;
                } else {
                    $categories[$query_result[$i]] = 1;
                }
            }

            foreach ($categories as $category_id => $number_of_items_in_this_category) {
                $object = $options['eloquent_model_classname']::find($category_id);
                if (empty($object)) {
                    continue;
                }

                $link = $options['base_url'].$param_name."=".$category_id;

                $number_of_items_in_this_category = sprintf($options['num_of_articles_string_format'], $number_of_items_in_this_category);

                if ($options['display_count'] == true) {
                    $title = "<span class='cmsArchiveText'>".$object->{$options['eloquent_model_name_column']}."</span><span class='cmsArchiveNumOfArticles'>".$number_of_items_in_this_category."</span>";
                } else {
                    $title = "<span class='cmsArchiveText'>".$object->{$options['eloquent_model_name_column']}."</span>";
                }

                $result[] = array(
                    'id' => $object->id,
                    'title' => $title,
                    'link' => $link,
                    'sub' => array(),
                );
            }
        }
        $this->archive = $result;
        return $result;
    }

    /**
     * Listing::getPagination()
     *
     * Generates pagination info
     *
     * @param Twig\Environment $twig
     * @param array $options
     * @return string html template
     * @author Martins Fridenbergs <martins.fridenbergs@mtcmedia.co.uk>
     */
    public function getPagination(Twig\Environment $twig, $options = array())
    {
        $this->total_articles = $this->getTotalNum();
        $this->total_listing_pages = ceil($this->total_articles / $this->limit);

        $pagination_settings = [
            'item_count' =>  $this->total_articles,
            'show_view_all' => false,
            'per_page' => $this->limit,
            'active_page' => $this->current_page,
            'page_url' => trim(trim($url = $this->geNondestructiveBaseUrl(), '?'), '&')
        ];

        // Load all extra settings
        foreach ($options as $key => $option) {
            $pagination_settings[$key] = $option;
        }

        $pagination = new PaginationTemplate($pagination_settings);
        $this->pagination = $pagination->render($twig);
        return $this->pagination;
    }

    /**
     * Listing::getListing()
     *
     * Runs the listing and returns listing data. You need to call Listing::run before this
     *
     * @param none
     * @return array listing
     */
    public function getListing($options = array())
    {
        if (!isset($options['get_page_objects'])) {
            $options['get_page_objects'] = true;
        }

        $result = array(
            'list' => array(),     // an array that depending on $options['get_page_objects'] hold either an array of pages or page ids
        );

        $query = $this->_select.$this->from.$this->_filter.$this->_order;

        $query .= " LIMIT ".$this->offset.", ".$this->limit;

        $this->list = array();

        $result = $this->runQuery($query);

        if (empty($result)) {
            $result['list'] = $this->list;
            return $result;
        }

        for ($i=0; $i < count($result); $i++) {
            if ($options['get_page_objects']) {
                $list_item = new Page($result[$i]);
                if (!empty($list_item->id)) {
                    $this->list[] = $list_item;
                }
            } else {
                $this->list[] = $result[$i];
            }
        }
    }

    public function geNondestructiveBaseUrl($remove_pagination = true, $remove_filters = false)
    {
        $url_params = parse_url($_SERVER['REQUEST_URI'], PHP_URL_QUERY);

        parse_str($url_params, $url_params_arr_unfiltered);

        $url_params = "";

        foreach ($url_params_arr_unfiltered as $key => $value) {
            if ($remove_pagination && $key == $this->get_params['pagination']) {
                continue;
            }

            if ($remove_filters &&
                in_array($key, array($this->get_params['from'], $this->get_params['to'], $this->get_params['filter']))) {
                continue;
            }

            if ($url_params != "") {
                $url_params .= "&";
            }

            if (is_array($value)) {
                foreach ($value as $k => $val) {
                    if ($url_params != "") {
                        $url_params .= "&";
                    }
                    $url_params .= $key . "%5B" . $k . "%5D" . "=" . clean_page($val);
                }
            } else {
                $url_params .= $key . "=" . clean_page($value);
            }

        }

        $url = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);

        if (empty($url_params)) {
            $url = $url."?";
        } else {
            $url = $url."?".$url_params."&";
        }
        return $url;
    }

    public function generatePaginationTree()
    {
        $url = $this->geNondestructiveBaseUrl();

        $result = array();

        for ($i=1; $i<=$this->total_listing_pages; $i++) {
            $result[] = array(
                'id' => $i,
                'title' => $i,
                'link' => $url.$this->get_params['pagination'].'='.$i,
                'sub' => array()
            );
        }

        return $result;
    }

    /**
     * Listing class run query class with inbuilt caching system
     * @param string $query
     * @return string[] $result
     */
    private function runQuery($query)
    {
        $pdo = new CmsPdo();

        // filter params for any that aren't used in this $query
        $query_params = [];
        foreach ($this->params as $param_placeholder => $param_value) {
            if (strpos($query, $param_placeholder) !== false) {
                $query_params[$param_placeholder] = $param_value;
            }
        }

        if ($this->debug) {
            echo "<!--".$query."-->";
            echo "<!--".json_encode($this->params)."-->";
        }

        if (defined('LISTING_CACHE') && LISTING_CACHE === true && $this->cache) {
            $serialized_params = serialize($query_params);
            $setup_hash = md5($query." ".$serialized_params);
            $pdo->run(
                "
                SELECT `result`, `id`, `last_fetched`
                FROM `listing_cache`
                WHERE `setup_hash` = :setup_hash
                LIMIT 1
                ",
                array(
                    ':setup_hash' => $setup_hash
                )
            );
            $data = $pdo->fetch_array();
            if (!empty($data)) {
                if (empty($data['result'])) {
                    $result = null;
                } else {
                    $data['result'] = substr($data['result'], 2, -2);
                    $result = explode(";;", $data['result']);
                }
                // Feedback the fetch for statistics if last fetch wasn't today
                // I only need to know if this cache row is utilized atleast once a day
                if (!str_contains($data['last_fetched'], date("Y-m-d"))) {
                    $pdo->run(
                        "
                        UPDATE `listing_cache`
                        SET
                            `last_fetched` = NOW(),
                            `num_of_fetches` = 1 + `num_of_fetches`,
                            `num_of_total_fetches` = 1 + `num_of_total_fetches`
                        WHERE `id` = :id
                        LIMIT 1
                        ",
                        array(
                            ':id' => $data['id']
                        )
                    );
                }
                return $result;

            }
        }
        $start_time = microtime(true);
        $pdo->run($query, $query_params);
        $result = array();
        while ($data = $pdo->fetch_array()) {
            $result[] = $data[key($data)];
        }
        $refresh_time_in_seconds = microtime(true) - $start_time;
        if (defined('LISTING_CACHE') && LISTING_CACHE === true && $this->cache) {
            $serialized_params = serialize($query_params);
            $setup_hash = md5($query." ".$serialized_params);
            if (empty($result)) {
                $result_string = "";
            } else {
                $result_string = implode(";;", $result);
                $result_string = ";;".$result_string.";;";
            }
            $pdo->run(
                "
                INSERT INTO `listing_cache`
                    (
                        `setup_hash`,
                        `result`,
                        `query`,
                        `params`,
                        `updated`,
                        `last_fetched`,
                        `num_of_fetches`,
                        `num_of_total_fetches`,
                        `refresh_time_in_seconds`
                    )
                VALUES
                    (
                        :setup_hash,
                        :result,
                        :query,
                        :params,
                        NOW(),
                        NOW(),
                        1,
                        1,
                        :refresh_time_in_seconds
                    )
                ",
                array(
                    ':setup_hash'               => $setup_hash,
                    ':result'                   => $result_string,
                    ':query'                    => $query,
                    ':params'                   => $serialized_params,
                    ':refresh_time_in_seconds'  => $refresh_time_in_seconds
                )
            );
        }
        return $result;
    }

    /**
     * Set params added in sql bits
     * @param array $params
     * @return Listing Object $this
     */
    public function setParams(array $params) {
        $this->params = array_merge($this->params, $params);
        return $this;
    }

    public static function updateListingCache($page_id = "", $page_id_2 = "")
    {
        $pdo = new CmsPdo();
        $pdo2 = new CmsPdo();

        $listing_cache_row_max_age = "1 WEEK";
        if (defined("CMS_LISTING_CACHE_ROW_EXPIRY")) {
            $listing_cache_row_max_age = CMS_LISTING_CACHE_ROW_EXPIRY;
        }

        $listing_cache_row_max_age_for_rows_used_only_once = "3 DAY";
        if (defined("CMS_LISTING_CACHE_ROW_EXPIRY_FOR_ROWS_USED_ONLY_ONCE")) {
            $listing_cache_row_max_age_for_rows_used_only_once = CMS_LISTING_CACHE_ROW_EXPIRY_FOR_ROWS_USED_ONLY_ONCE;
        }

        // delete the ones we want to delete
        $query = "
            DELETE
            FROM `listing_cache`
            WHERE (
                (`last_fetched` < DATE_SUB(NOW(), INTERVAL ".$listing_cache_row_max_age."))
                OR
                (
                    (`last_fetched` < DATE_SUB(NOW(), INTERVAL ".$listing_cache_row_max_age_for_rows_used_only_once."))
                    AND `num_of_total_fetches` = 1
                )
            )
        ";

        $pdo->run($query);

        // select ones we want to update

        $query = "
            SELECT `id`, `query`, `params`
            FROM `listing_cache`
            WHERE
                `result` LIKE :like_page_id_in_result
                OR
                `query` LIKE :like_page_id_in_query
        ";

        $params = array(
            ':like_page_id_in_result'=>'%;;'.$page_id.';;%',
            ':like_page_id_in_query'=>'% '.$page_id.' %'
        );

        if (!empty($page_id_2)) {
            $query .= "
                OR
                `result` LIKE :like_page_id_2_in_result
                OR
                `query` LIKE :like_page_id_2_in_query
            ";
            $params['like_page_id_2_in_result'] = '%;;'.$page_id_2.';;%';
            $params['like_page_id_2_in_query'] = '% '.$page_id_2.' %';
        }

        $pdo->run(
            $query,
            $params
        );

        while ($data = $pdo->fetch_array()) {
            $start_time = microtime(true);
            $pdo2->run(
                $data['query'],
                unserialize($data['params'])
            );
            $result = array();
            while ($data2 = $pdo2->fetch_array()) {
                $result[] = $data2[key($data2)];
            }
            if (empty($result)) {
                $result_string = "";
            } else {
                $result_string = implode(";;", $result);
                $result_string = ";;".$result_string.";;";
            }
            $refresh_time_in_seconds = microtime(true) - $start_time;
            $query = "
            UPDATE `listing_cache`
            SET
                `result` = :result_string,
                `num_of_fetches` = 1,
                `updated` = NOW(),
                `refresh_time_in_seconds` = :refresh_time_in_seconds
            WHERE `id` = :id
            ";
            $pdo2->run(
                $query,
                array(
                    ':result_string' => $result_string,
                    ':refresh_time_in_seconds' => $refresh_time_in_seconds,
                    ':id' => $data['id']
                )
            );
        }

        return true;
    }

    /**
     * Generate tag tree from options passed for listing
     * @param mixed[] $options
     * @param mixed[][] $result
     */
    public function getTags($options = array())
    {
        if (!isset($options['index']) || !isset($options['index'][0]) || !preg_match("/[0-9a-zA-Z ]+/", $options['index'][0])) {
            $options['index'] = ['Tag', 'varchar'];
        }
        if (
            !isset($options['order_by']) ||
            !in_array($options['order_by'], ['count desc', 'count asc', 'alphabet asc', 'alphabet desc'])
        ) {
            $options['order_by'] = 'count desc';
        }
        if (!isset($options['display_count'])) {
            $options['display_count'] = true;
        }
        if (!isset($options['num_of_articles_string_format'])) {
            $options['num_of_articles_string_format'] = " (%d)";
        }
        if (!isset($options['number_of_tags_limit'])) {
            $options['number_of_tags_limit'] = 5;
        }

        if (!isset($options['base_url'])) {
            $options['base_url'] = $this->geNondestructiveBaseUrl(true, true);
        } else {
            $options['base_url'] = $options['base_url']."?";
        }

        $param_name = Util::slugify($options['index'][0]);
        $result = array();

        $pdo = new CmsPdo();
        $query = "
        SELECT `page_list_item_data`.`varchar_value`, COUNT(`page_list_item_data`.`id`) AS `count`
        FROM `pages`
            JOIN `page_list` ON `page_list`.`page_id` = `pages`.`id`
            JOIN `page_list_item_data` ON `page_list_item_data`.`page_list_id` = `page_list`.`id`
                AND `page_list_item_data`.`varchar_value` != ''
                AND `page_list_item_data`.`name` = :name
        WHERE 1
            AND `pages`.`sub_id` = :id
            AND `pages`.`type` = 'default'
            AND `pages`.`published` = 1

        GROUP BY `page_list_item_data`.`varchar_value`

        ";

        if ($options['order_by'] === "count asc") {
            $query .= "ORDER BY `count` ASC";
        } elseif ($options['order_by'] === "count desc") {
            $query .= "ORDER BY `count` DESC";
        } elseif ($options['order_by'] === "alphabet asc") {
            $query .= "ORDER BY `varchar_value` ASC";
        } elseif ($options['order_by'] === "alphabet desc") {
            $query .= "ORDER BY `varchar_value` DESC";
        }

        if ($options['number_of_tags_limit'] !== false && is_numeric($options['number_of_tags_limit'])) {
            $query .= " LIMIT :limit ";
        }

        $result = $pdo->run(
            $query,
            [
                ':id'   => $this->container_id,
                ':limit'=> $options['number_of_tags_limit'],
                ':name' => $options['index'][0]
            ]
        );

        $query_results =  $result->fetchAll();

        $result = [];
        foreach ($query_results as $query_result) {
            $link = $options['base_url'] . $param_name . "=" . urlencode($query_result['varchar_value']);

            $number_of_items_with_this_tag = sprintf($options['num_of_articles_string_format'], $query_result['count']);

            if ($options['display_count'] == true) {
                $title = clean_page($query_result['varchar_value']) . " " . $number_of_items_with_this_tag;
            } else {
                $title = clean_page($query_result['varchar_value']);
            }

            $result[] = [
                'id' => 'tag_' . clean_page($query_result['varchar_value']),
                'title' => $title,
                'link' => $link,
                'sub' => array(),
                'active' => ($options['display_active'] && $options['display_active'] == $title),
            ];
        }

        $this->tags = $result;
        return $result;
    }
}
