<?php
/**
 * Queries the database for popular posts.
 *
 * To use this class, you must pass it an array of parameters (mostly the same ones used with
 * the wpp_get_mostpopular() template tag).
 *
 * eg.:
 * $popular_posts = new \WordPressPopularPosts\Query([
 *  'range' => 'last7days',
 *  'order_by' => 'views',
 *  'limit' => 5
 * ]);
 * var_dump( $popular_posts->get_posts() );
 *
 * @since             4.0.0
 * @package           WordPressPopularPosts
 */

namespace WordPressPopularPosts;

class Query {
    /**
     * Database query string.
     *
     * @since    4.0.0
     * @access   private
     * @var      string      $query
     */
    private $query;

    /**
     * List of posts.
     *
     * @since    4.0.0
     * @access   private
     * @var      array      $posts
     */
    private $posts = [];

    /**
     * Plugin options.
     *
     * @since    4.0.0
     * @access   private
     * @var      array      $options
     */
    private $options;

    /**
     * Constructor.
     *
     * @since   4.0.0
     * @param   array   $options
     */
    public function __construct(array $options = [])
    {
        $this->set_options($options)
            ->execute();
    }

    /**
     * Sets class options.
     *
     * @since   5.0.0
     * @param   array   $options
     * @return  WordPressPopularPosts\Query
     */
    public function set_options(array $options = [])
    {
        $this->options = $options;
        // Reset posts array
        $this->posts = [];

        return $this;
    }

    /**
     * Builds the database query.
     *
     * @since    4.0.0
     * @access   private
     */
    private function build_query()
    {
        /**
         * @var wpdb $wpdb
         */
        global $wpdb;

        if ( isset($wpdb) ) {

            $this->options = Helper::merge_array_r(
                Settings::get('widget_options'),
                (array) $this->options
            );

            $now = new \DateTime(Helper::now(), wp_timezone());
            $args = [];
            $fields = 'p.ID AS id, p.post_title AS title, p.post_author AS uid';
            $table = '';
            $join = '';
            $where = 'WHERE 1 = 1';
            $groupby = '';
            $orderby = '';
            $limit = 'LIMIT ' . (filter_var($this->options['limit'], FILTER_VALIDATE_INT) && $this->options['limit'] > 0 ? $this->options['limit'] : 10) . (isset($this->options['offset']) && filter_var($this->options['offset'], FILTER_VALIDATE_INT) !== false && $this->options['offset'] >= 0 ? " OFFSET {$this->options['offset']}" : '');

            // Get post date
            if ( isset($this->options['stats_tag']['date']['active']) && $this->options['stats_tag']['date']['active'] ) {
                $fields .= ', p.post_date AS date';
            }

            // Get post excerpt $instance
            if ( isset($this->options['post-excerpt']['active']) && $this->options['post-excerpt']['active'] ) {
                $fields .= ', p.post_excerpt AS post_excerpt, p.post_content AS post_content';
            }

            // Get entries from these post types
            $post_types = ['post', 'page'];

            if ( isset($this->options['post_type']) && ! empty($this->options['post_type']) ) {

                $post_types = explode(',', $this->options['post_type']);
                $pt = '';
                $where .= ' AND p.post_type IN(';

                foreach( $post_types as $post_type ) {
                    $pt .= '%s, ';
                    array_push($args, trim($post_type));
                }

                $where .= rtrim($pt, ', ') . ')';

            }
            else {
                $where .= " AND p.post_type IN('post', 'page')";
            }

            // Get entries from these authors
            if ( isset($this->options['author']) && ! empty($this->options['author']) ) {

                $author_IDs = explode(',', $this->options['author']);
                $uid = '';
                $where .= ' AND p.post_author IN(';

                foreach( $author_IDs as $author_ID ) {
                    $uid .= '%d, ';
                    array_push($args, trim($author_ID));
                }

                $where .= rtrim($uid, ', ') . ')';

            }

            // Get / exclude entries from these taxonomies
            if (
                ( isset($this->options['taxonomy']) && ! empty($this->options['taxonomy']) ) &&
                ( ( isset($this->options['cat']) && ! empty($this->options['cat']) )
                || ( isset($this->options['term_id']) && ! empty($this->options['term_id']) ) )
            ) {

                if ( isset($this->options['cat']) && ! empty($this->options['cat']) ) {
                    $this->options['term_id'] = $this->options['cat'];
                }

                // Let's do some cleanup before attempting the filtering
                $this->options['taxonomy'] = trim($this->options['taxonomy'], ' ;');
                $this->options['term_id'] = trim($this->options['term_id'], ' ;');

                if (
                    $this->options['taxonomy'] 
                    && $this->options['term_id']
                ) {

                    $taxonomies = array_map('trim', explode(';', $this->options['taxonomy']));
                    $term_IDs_for_taxonomies = array_map('trim', explode(';', $this->options['term_id']));

                    // Apparently we have at least one taxonomy and matching term ID(s)
                    if ( count($taxonomies) && count($term_IDs_for_taxonomies) ) {

                        // Parameters mismatch: we either have too little taxonomies
                        // or too little term ID groups, let's trim the excess
                        if ( count($taxonomies) != count($term_IDs_for_taxonomies) ) {
                            // We have more taxonomies than term ID groups,
                            // let's remove some taxonomies
                            if ( count($taxonomies) > count($term_IDs_for_taxonomies) ) {
                                $taxonomies = array_slice($taxonomies, 0, count($term_IDs_for_taxonomies));
                            }
                            // We have more term ID groups than taxonomies,
                            // let's remove some term ID groups
                            else {
                                $term_IDs_for_taxonomies = array_slice($term_IDs_for_taxonomies, 0, count($taxonomies));
                            }
                        }

                        $registered_taxonomies = get_taxonomies(['public' => true]);

                        foreach ( $taxonomies as $index => $taxonomy ) {
                            // Invalid taxonomy, discard the taxonomy
                            if ( ! isset($registered_taxonomies[$taxonomy]) ) {
                                unset($taxonomies[$index]);
                                unset($term_IDs_for_taxonomies[$index]);
                            }
                        }

                        // If we still have something we can use 
                        // for filtering, let's use it
                        if (
                            ! empty($taxonomies)
                            && ! empty($term_IDs_for_taxonomies)
                        ) {

                            $tax_terms = [];

                            foreach( $taxonomies as $taxIndex => $taxonomy ) {
                                $tax_terms[$taxonomy] = [
                                    'term_id_include' => [],
                                    'term_id_exclude' => []
                                ];

                                $term_IDs = array_map('trim', explode(',', $term_IDs_for_taxonomies[$taxIndex]));

                                foreach ( $term_IDs as $term_ID ) {
                                    if ( $term_ID >= 0 ) {
                                        $tax_terms[$taxonomy]['term_id_include'][] = (int) $term_ID;
                                    } else {
                                        $tax_terms[$taxonomy]['term_id_exclude'][] = abs((int) $term_ID);
                                    }
                                }
                            }

                            $term_rel_table = $wpdb->prepare("%i", "{$wpdb->term_relationships}");
                            $term_tax_table = $wpdb->prepare("%i", "{$wpdb->term_taxonomy}");

                            foreach( $tax_terms as $taxonomy => $terms ) {

                                if ( ! empty($terms['term_id_include']) ) {
                                    $where .= " AND p.ID IN (
                                        SELECT object_id
                                        FROM {$term_rel_table} AS r
                                            JOIN {$term_tax_table} AS x ON x.term_taxonomy_id = r.term_taxonomy_id
                                        WHERE x.taxonomy = %s";

                                    array_push($args, $taxonomy);

                                    $inTID = '';

                                    foreach ($terms['term_id_include'] as $term_ID) {
                                        $inTID .= '%d, ';
                                        array_push($args, $term_ID);
                                    }

                                    $where .= ' AND x.term_id IN(' . rtrim($inTID, ', ') . ') )';
                                }

                                if ( ! empty($terms['term_id_exclude']) ) {
                                    //phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_tax_query
                                    $post_ids = get_posts(
                                        [
                                            'post_type' => $post_types,
                                            'posts_per_page' => -1,
                                            'tax_query' => [
                                                [
                                                    'taxonomy' => $taxonomy,
                                                    'field' => 'id',
                                                    'terms' => $terms['term_id_exclude'],
                                                ],
                                            ],
                                            'fields' => 'ids'
                                        ]
                                    );
                                    //phpcs:enable

                                    if ( is_array($post_ids) && ! empty($post_ids) ) {
                                        if ( isset($this->options['exclude']) && ! empty($this->options['exclude']) ) {
                                            $pid_arr = explode(',', $this->options['exclude']);
                                            $pid_arr = array_merge($pid_arr, $post_ids);

                                            // Remove duplicates
                                            $pid_arr_no_dupes = [];
                                            foreach( $pid_arr as $key => $post_id ) {
                                                $pid_arr_no_dupes[$post_id] = true;
                                            }
                                            $pid_arr_no_dupes = array_keys($pid_arr_no_dupes);

                                            $this->options['exclude'] = implode(',', $pid_arr_no_dupes);
                                        } else {
                                            $this->options['exclude'] = implode(',', $post_ids);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }

            // Exclude these entries from the listing
            if ( isset($this->options['exclude']) && ! empty($this->options['exclude']) ) {
                $excluded_post_IDs = explode(',', $this->options['exclude']);
                $xpid = '';
                $where .= ' AND p.ID NOT IN(';

                foreach( $excluded_post_IDs as $excluded_post_ID ) {
                    $xpid .= '%d, ';
                    array_push($args, trim($excluded_post_ID));
                }

                $where .= rtrim($xpid, ', ') . ')';
            }

            $table = $wpdb->prepare("%i", "{$wpdb->posts}") . " p";
            $comments_table = $wpdb->prepare("%i", "{$wpdb->comments}");
            $data_table = $wpdb->prepare("%i", "{$wpdb->prefix}popularpostsdata");
            $summary_table = $wpdb->prepare("%i", "{$wpdb->prefix}popularpostssummary");

            // All-time range
            if ( 'all' == $this->options['range'] ) {

                // Order by views count
                if ( 'comments' != $this->options['order_by'] ) {

                    $join = "INNER JOIN {$data_table} v ON p.ID = v.postid";

                    // Order by views
                    if ( 'views' == $this->options['order_by'] ) {
                        if ( ! isset($this->options['stats_tag']['views']) || $this->options['stats_tag']['views'] ) {
                            $fields .= ', v.pageviews';
                        }
                        $orderby = 'ORDER BY v.pageviews DESC';
                    }
                    // Order by average views
                    else {
                        $fields .= ", ( v.pageviews/(IF ( DATEDIFF('{$now->format('Y-m-d')}', MIN(v.day)) > 0, DATEDIFF('{$now->format('Y-m-d')}', MIN(v.day)), 1) ) ) AS avg_views";
                        $groupby = 'GROUP BY v.postid';
                        $orderby = 'ORDER BY avg_views DESC';
                    }

                    // Display comments count, too
                    if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
                        $fields .= ', p.comment_count';
                    }
                }
                // Order by comments count
                else {
                    $where .= ' AND p.comment_count > 0';
                    $orderby = 'ORDER BY p.comment_count DESC';

                    // Display comment count
                    if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
                        $fields .= ', p.comment_count';
                    }

                    // Display views count, too
                    if ( isset($this->options['stats_tag']['views']) && $this->options['stats_tag']['views'] ) {
                        $fields .= ', IFNULL(v.pageviews, 0) AS pageviews';
                        $join = "INNER JOIN {$data_table} v ON p.ID = v.postid";
                    }
                }
            }
            // Custom time range
            else {
                list($dates, $datetimes, $include_timestamps) = Helper::get_dates(
                    $this->options['range'],
                    $this->options['time_unit'],
                    $this->options['time_quantity']
                );

                list($start_date, $end_date) = $dates;
                list($start_datetime, $end_datetime) = $datetimes;

                if ( $include_timestamps ) {
                    $views_time_range = "(view_datetime BETWEEN '{$start_datetime}' AND '{$end_datetime}')";
                    $comments_time_range = "(comment_date BETWEEN '{$start_datetime}' AND '{$end_datetime}')";
                } else {
                    $views_time_range = "(view_date BETWEEN '{$start_date}' AND '{$end_date}')";
                    $comments_time_range = "(comment_date BETWEEN '{$start_date} 00:00:00' AND '{$end_date} 23:59:59')";
                }

                // Get entries published within the specified time range
                if ( isset($this->options['freshness']) && $this->options['freshness'] ) {
                    $where .= " AND p.post_date >= '" . ( $include_timestamps ? $start_datetime : $start_date ) . "'";
                }

                // Order by views count
                if ( 'comments' != $this->options['order_by'] ) {
                    // Order by views
                    if ( 'views' == $this->options['order_by'] ) {
                        $fields .= ', v.pageviews';
                        $join = "INNER JOIN (SELECT SUM(pageviews) AS pageviews, postid FROM {$summary_table} WHERE {$views_time_range} GROUP BY postid) v ON p.ID = v.postid";
                        $orderby = 'ORDER BY pageviews DESC';
                    }
                    // Order by average views
                    else {
                        $fields .= ', v.avg_views';
                        $join = "INNER JOIN (SELECT SUM(pageviews)/(IF ( DATEDIFF('{$now->format('Y-m-d H:i:s')}', '{$start_datetime}') > 0, DATEDIFF('{$now->format('Y-m-d H:i:s')}', '{$start_datetime}'), 1) ) AS avg_views, postid FROM {$summary_table} WHERE {$views_time_range} GROUP BY postid) v ON p.ID = v.postid";
                        $orderby = 'ORDER BY avg_views DESC';
                    }

                    // Display comments count, too
                    if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
                        $fields .= ', IFNULL(c.comment_count, 0) AS comment_count';
                        $join .= " LEFT JOIN (SELECT comment_post_ID, COUNT(comment_post_ID) AS comment_count FROM {$comments_table} WHERE {$comments_time_range} AND comment_approved = '1' GROUP BY comment_post_ID) c ON p.ID = c.comment_post_ID";
                    }
                }
                // Order by comments count
                else {
                    $fields .= ', c.comment_count';
                    $join = "INNER JOIN (SELECT COUNT(comment_post_ID) AS comment_count, comment_post_ID FROM {$comments_table} WHERE {$comments_time_range} AND comment_approved = '1' GROUP BY comment_post_ID) c ON p.ID = c.comment_post_ID";
                    $orderby = 'ORDER BY comment_count DESC';

                    // Display views count, too
                    if ( isset($this->options['stats_tag']['views']) && $this->options['stats_tag']['views'] ) {
                        $fields .= ', v.pageviews';
                        $join .= " INNER JOIN (SELECT SUM(pageviews) AS pageviews, postid FROM {$summary_table} WHERE {$views_time_range} GROUP BY postid) v ON p.ID = v.postid";
                    }
                }
            }

            // List only published, non password-protected posts
            $where .= " AND p.post_password = '' AND p.post_status = 'publish'";

            if ( ! empty($args) ) {
                $where = $wpdb->prepare($where, $args); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $where and $args are built and prepared dynamically, see above
            }

            $fields = apply_filters('wpp_query_fields', $fields, $this->options);
            $table = apply_filters('wpp_query_table', $table, $this->options);
            $join = apply_filters('wpp_query_join', $join, $this->options);
            $where = apply_filters('wpp_query_where', $where, $this->options);
            $groupby = apply_filters('wpp_query_group_by', $groupby, $this->options);
            $orderby = apply_filters('wpp_query_order_by', $orderby, $this->options);
            $limit = apply_filters('wpp_query_limit', $limit, $this->options);

            // Finally, build the query
            $query = "SELECT {$fields} FROM {$table} {$join} {$where} {$groupby} {$orderby} {$limit};";
            $this->query = $query;
        }
    }

    /**
     * Queries the database.
     *
     * @since    4.0.0
     * @access   private
     */
    private function run_query()
    {
        /**
         * @var wpdb $wpdb
         */
        global $wpdb;

        if ( isset($wpdb) && ! empty($this->query) && ! is_wp_error($this->query) ) {
            $this->posts = $wpdb->get_results($this->query); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- at this point $this->query has been prepared already
        }
    }

    /**
     * Executes the query.
     *
     * @since   6.0.0
     * @return  WordPressPopularPosts\Query
     */
    public function execute()
    {
        $this->build_query();
        $this->run_query();

        return $this;
    }

    /**
     * Returns the query string.
     *
     * @since    4.0.0
     * @return   WP_Error|string   Query string on success, WP_Error on failure
     */
    public function get_query()
    {
        return $this->query;
    }

    /**
     * Returns the list of posts.
     *
     * @since    4.0.0
     * @return   array
     */
    public function get_posts()
    {
        return $this->posts;
    }
}