<?php

class APVC_Query {

	const CAMPAIGN_URLS       = 'campaign_urls';
	const CAMPAIGNS           = 'campaigns';
	const REFERRER_TYPES      = 'referrer_types';
	const REFERRERS           = 'referrers';
	const VIEWS_DATA          = 'views_data';
	const VIEWS               = 'page_views';
	const VISITORS            = 'users';
	const SESSIONS            = 'user_sessions';
	const WC_ORDERS           = 'ecommerce_orders';
	const SHORTCODES          = 'shortcodes';
	const EXPORTED_DATA       = 'exported_data';
	const SMART_NOTIFICATIONS = 'smart_notifications';

	public function __construct() {}

	public static function get_table_name( string $name ) {
		global $wpdb;
		$prefix     = $wpdb->prefix;
		$reflection = new \ReflectionClass( static::class );
		$constants  = $reflection->getConstants();

		if ( in_array( $name, $constants ) ) {
			return $prefix . 'apvc_' . $name;
		} else {
			return null;
		}
	}

	public static function create_visitor( $user_data = array() ) {
		if ( is_null( $user_data ) && ! is_array( $user_data ) ) {
			return;
		}

		global $wpdb;
		$visitors_table = self::get_table_name( self::VISITORS );
		$existing_data  =
			$wpdb->prepare(
				"select COUNT(*) FROM $visitors_table WHERE visitor_id = %s AND country_code = %s AND city = %s AND subdivision = %s AND country = %s AND continent = %s AND site_id = %d",
				$user_data['visitor_id'],
				$user_data['country_code'],
				$user_data['city'],
				$user_data['subdivision'],
				$user_data['country'],
				$user_data['continent'],
				$user_data['site_id']
			);
		$col            = $wpdb->get_var( $existing_data );

		if ( $col > 0 ) {

		} else {
			$visitor_sql = $wpdb->prepare(
				'INSERT INTO ' . $visitors_table . ' (visitor_id, country_code, city, subdivision, country, continent, site_id ) VALUES ( %s, %s, %s, %s, %s, %s, %d )',
				$user_data['visitor_id'],
				$user_data['country_code'],
				$user_data['city'],
				$user_data['subdivision'],
				$user_data['country'],
				$user_data['continent'],
				$user_data['site_id']
			);
			$wpdb->query( $visitor_sql );
		}

		return $wpdb->insert_id;
	}

	public static function get_current_user_session( $visitor_id ) {
		if ( is_null( $visitor_id ) ) {
			return;
		}

		global $wpdb;
		$sessions_table = self::get_table_name( self::SESSIONS );
		$existing_data  = $wpdb->prepare(
			"SELECT user_session_id FROM $sessions_table WHERE visitor_id = %s AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 MINUTE ) ORDER BY created_at DESC LIMIT 1",
			$visitor_id
		);

		return $wpdb->get_var( $existing_data );
	}

	public static function create_user_session( $args ) {
		global $wpdb;
		$sessions_table = self::get_table_name( self::SESSIONS );
		$session_sql    = $wpdb->prepare(
			'INSERT INTO ' . $sessions_table . ' (visitor_id, referrer_id, campaign_id, created_at, device_type, device_os, device_browser, device_browser_ver ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s )',
			$args['visitor_id'],
			$args['referrer_id'],
			$args['campaign_id'],
			$args['created_at'],
			$args['device_type'],
			$args['device_os'],
			$args['device_browser'],
			$args['device_browser_ver']
		);

		$wpdb->query( $session_sql );
		return $wpdb->insert_id;
	}

	public static function get_view_count( $post_id ) {
		if ( is_null( $post_id ) ) {
			return 0;
		}

		global $wpdb;
		$views_table = self::get_table_name( self::VIEWS_DATA );
		$viewCount   = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT views FROM $views_table WHERE singular_id = %d",
				$post_id
			)
		);
		return $viewCount ? $viewCount : 0;
	}

	public static function create_referrer( $domain_name ) {
		if ( is_null( $domain_name ) ) {
			return null;
		}

		global $wpdb;
		$referrers_table = self::get_table_name( self::REFERRERS );
		$referrers_sql   = $wpdb->prepare(
			'INSERT INTO ' . $referrers_table . ' (domain_name) VALUES ( %s )',
			$domain_name
		);
		$wpdb->query( $referrers_sql );
		return $wpdb->insert_id;
	}

	public static function get_referrer( $domain_name ) {
		if ( is_null( $domain_name ) ) {
			return;
		}

		global $wpdb;
		$referrers_table = self::get_table_name( self::REFERRERS );
		$existing_data   = $wpdb->prepare(
			"SELECT id FROM $referrers_table WHERE domain_name = %s LIMIT 1",
			$domain_name
		);
		return $wpdb->get_row( $existing_data );
	}

	public static function create_view( $args ) {
		if ( is_null( $args ) ) {
			return;
		}

		global $wpdb;
		$views_table      = self::get_table_name( self::VIEWS );
		$views_data_table = self::get_table_name( self::VIEWS_DATA );

		$views_sql = $wpdb->prepare(
			'INSERT INTO ' . $views_table . ' (page_id, viewed_at, page, user_session_id) VALUES ( %s, %s, %s, %s )',
			$args['page_id'],
			$args['viewed_at'],
			$args['page'],
			$args['user_session_id']
		);
		$wpdb->query( $views_sql );

		$views_sql      = $wpdb->prepare(
			'SELECT COUNT(*) FROM `' . $views_table . '` WHERE `page_id` = %d',
			$args['page_id']
		);
		$existing_count = $wpdb->get_var( $views_sql );
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE $views_data_table SET views = %d WHERE singular_id = %d",
				absint( $existing_count ),
				$args['page_id']
			)
		);

		return $wpdb->insert_id;
	}

	public static function get_dashboard_data( $args ) {
		if ( is_null( $args ) ) {
			return;
		}
	}

	public static function get_end_date( $end_date ) {
		return empty( $end_date ) ? new \DateTime() : new \DateTime( $end_date );
	}

	public static function get_yearly_stats_query( $start_date = '', $end_date = '' ) {

		global $wpdb;

		$start_date = empty( $start_date ) ? new \DateTime( '1990-01-01' ) : $start_date;
		$end_date   = self::get_end_date( $end_date );

		$start_date = APVC_Timezone_Helpers::start_of_locale_day( $start_date );
		$end_date   = APVC_Timezone_Helpers::end_of_locale_day( $end_date );

		$views_data    = self::get_table_name( self::VIEWS );
		$existing_data = $wpdb->prepare(
			"SELECT COUNT(*) as views FROM $views_data WHERE viewed_at BETWEEN %s AND %s",
			$start_date->format( APVC_DATE_FORMAT ),
			$end_date->format( APVC_DATE_FORMAT )
		);

		return $wpdb->get_var( $existing_data );
	}

	public static function get_monthly_stats_query( $start_date = '', $end_date = '' ) {
		global $wpdb;
		$start_date = empty( $start_date ) ? new \DateTime( 'first day of this month 00:00:00' ) : $start_date;
		$end_date   = self::get_end_date( $end_date );

		$start_date = APVC_Timezone_Helpers::start_of_locale_day( $start_date );
		$end_date   = APVC_Timezone_Helpers::end_of_locale_day( $end_date );

		$views_data    = self::get_table_name( self::VIEWS );
		$existing_data = $wpdb->prepare(
			"SELECT COUNT(*) as views FROM $views_data WHERE viewed_at BETWEEN %s AND %s",
			$start_date->format( APVC_DATE_FORMAT ),
			$end_date->format( APVC_DATE_FORMAT )
		);

		return $wpdb->get_var( $existing_data );
	}

	public static function get_weekly_stats_query( $start_date = '', $end_date = '' ) {
		global $wpdb;
		$first_day_of_the_week = APVC_Timezone_Helpers::get_wp_day_name( get_option( 'start_of_week', true ) );
		$start_date            = empty( $start_date ) ? new \DateTime( $first_day_of_the_week . ' last week 00:00:00' ) : $start_date;
		$end_date              = self::get_end_date( $end_date );

		$start_date = APVC_Timezone_Helpers::start_of_locale_day( $start_date );
		$end_date   = APVC_Timezone_Helpers::end_of_locale_day( $end_date );

		$views_data    = self::get_table_name( self::VIEWS );
		$existing_data = $wpdb->prepare(
			"SELECT COUNT(*) as views FROM $views_data WHERE viewed_at BETWEEN %s AND %s",
			$start_date->format( APVC_DATE_FORMAT ),
			$end_date->format( APVC_DATE_FORMAT )
		);

		return $wpdb->get_var( $existing_data );
	}

	public static function get_daily_stats_query( $start_date = '', $end_date = '' ) {
		global $wpdb;

		$start_date_bs = empty( $start_date ) ? new \DateTime( 'today 00:00:00' ) : $start_date;
		$end_date_bs   = self::get_end_date( $end_date );

		$start_date = APVC_Timezone_Helpers::start_of_locale_day( $start_date_bs );
		$end_date   = APVC_Timezone_Helpers::end_of_locale_day( $end_date_bs );

		$views_data    = self::get_table_name( self::VIEWS );
		$existing_data = $wpdb->prepare(
			"SELECT COUNT(*) as views FROM $views_data WHERE viewed_at BETWEEN %s AND %s",
			$start_date->format( APVC_DATE_FORMAT ),
			$end_date->format( APVC_DATE_FORMAT )
		);

		return $wpdb->get_var( $existing_data );
	}


	public static function get_all_states_with_range( $start_date = '', $end_date = '', $type ) {
		global $wpdb;

		$start_date_bs = empty( $start_date ) ? new \DateTime( '-30 days 00:00:00' ) : new \DateTime( $start_date );
		$end_date_bs   = self::get_end_date( $end_date );

		$start_date = APVC_Timezone_Helpers::start_of_locale_day( $start_date_bs );
		$end_date   = APVC_Timezone_Helpers::end_of_locale_day( $end_date_bs );

		$views_data = self::get_table_name( self::VIEWS );
		$sessions   = self::get_table_name( self::SESSIONS );
		$users      = self::get_table_name( self::VISITORS );

		if ( 'refer' === $type ) {
			$existing_data = $wpdb->prepare(
				"SELECT DATE_FORMAT(CONVERT_TZ(views.viewed_at, '+00:00', '+05:30'), '%e/%m') as date, COUNT(*) as views, COUNT(DISTINCT sessions.visitor_id) as visitors, COUNT(DISTINCT sessions.visitor_id) as unq_visitors, COUNT(DISTINCT sessions.user_session_id) as sessions FROM $views_data AS views LEFT JOIN $sessions AS sessions ON views.user_session_id = sessions.user_session_id LEFT JOIN $users AS visitors ON sessions.visitor_id = visitors.visitor_id WHERE views.viewed_at BETWEEN %s AND %s  AND (1 = 1 OR views.page_id IN ('0') ) AND visitors.country_code IS NOT NULL AND sessions.referrer_id != 0 GROUP BY DATE(CONVERT_TZ(views.viewed_at, '+00:00', '+05:30'))",
				$start_date->format( APVC_DATE_FORMAT ),
				$end_date->format( APVC_DATE_FORMAT )
			);
			return $wpdb->get_results( $existing_data, ARRAY_A );
		}

		$existing_data = $wpdb->prepare(
			"SELECT DATE_FORMAT(CONVERT_TZ(views.viewed_at, '+00:00', '+05:30'), '%e/%m') as date, COUNT(*) as views, COUNT(DISTINCT sessions.visitor_id) as visitors, COUNT(DISTINCT sessions.visitor_id) as unq_visitors, COUNT(DISTINCT sessions.user_session_id) as sessions FROM $views_data AS views LEFT JOIN $sessions AS sessions ON views.user_session_id = sessions.user_session_id LEFT JOIN $users AS visitors ON sessions.visitor_id = visitors.visitor_id WHERE views.viewed_at BETWEEN %s AND %s  AND (1 = 1 OR views.page_id IN ('0') ) AND visitors.country_code IS NOT NULL GROUP BY DATE(CONVERT_TZ(views.viewed_at, '+00:00', '+05:30'))",
			$start_date->format( APVC_DATE_FORMAT ),
			$end_date->format( APVC_DATE_FORMAT )
		);

		return $wpdb->get_results( $existing_data, ARRAY_A );
	}

	public static function get_dashboard_visitors_with_range_by_field( $field = '', $start_data = '', $end_date = '', $type = 'dashboard' ) {
		return array_sum( array_column( self::get_all_states_with_range( $start_data, $end_date, $type ), $field ) );
	}

	public static function get_dashboard_list_posts( $limit, $args = array() ) {
		global $wpdb;
		$views_data_table = self::get_table_name( self::VIEWS_DATA );
		$sessions_table   = self::get_table_name( self::SESSIONS );
		if ( isset( $args['post_type'] ) ) {
			$conditions = " WHERE stored_type = '" . $args['post_type'] . "'";
		} else {
			$conditions = '';
		}
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM ' . $views_data_table . ' ' . $conditions . ' ORDER BY views DESC LIMIT %d',
				$limit
			)
		);
	}

	public static function get_dashboard_list_posts_with_pagination( $limit, $page, $args = array() ) {
		global $wpdb;
		$views_data_table = self::get_table_name( self::VIEWS_DATA );
		if ( isset( $args['post_type'] ) ) {
			$conditions = " WHERE stored_type = '" . $args['post_type'] . "'";
		} else {
			$conditions = '';
		}
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM ' . $views_data_table . ' ' . $conditions . ' ORDER BY views DESC LIMIT %d OFFSET %d',
				$page,
				$limit
			)
		);
	}

	public static function get_post_total_sessions( $page_id ) {
		global $wpdb;
		$views_table = self::get_table_name( self::VIEWS );
		return $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(DISTINCT(user_session_id)) FROM ' . $views_table . ' WHERE `page_id` = %d ',
				$page_id
			)
		);
	}

	public static function get_detailed_reports( $args, $limit ) {
		global $wpdb;
		$views            = self::get_table_name( self::VIEWS );
		$views_data_table = self::get_table_name( self::VIEWS_DATA );
		$user_sessions    = self::get_table_name( self::SESSIONS );
		$users            = self::get_table_name( self::VISITORS );

		$condition      = '';
		$spread         = array();
		$spread['p_id'] = $args['p_id'];

		if ( isset( $args['startDate'] ) && isset( $args['endDate'] ) && ! empty( $args['startDate'] ) ) {
			$condition           .= ' AND ( ' . $views . '.viewed_at BETWEEN %s AND %s ) ';
			$spread['startDate'] = $args['startDate'] . ' 00:00:00';
			$spread['endDate']   = $args['endDate'] . ' 23:59:59';
		}
		if ( isset( $args['device_type'] ) && ! empty( $args['device_type'] ) ) {
			$condition             .= ' AND ' . $user_sessions . '.device_type = %s ';
			$spread['device_type'] = $args['device_type'];
		}
		if ( isset( $args['device_os'] ) && ! empty( $args['device_os'] ) ) {
			$condition           .= ' AND ' . $user_sessions . '.device_os = %s ';
			$spread['device_os'] = $args['device_os'];
		}
		if ( isset( $args['device_br'] ) && ! empty( $args['device_br'] ) ) {
			$condition           .= ' AND ' . $user_sessions . '.device_browser = %s ';
			$spread['device_br'] = $args['device_br'];
		}
		$spread['limit'] = $limit;

		$query = $wpdb->prepare(
			'SELECT * FROM ' . $views . ' INNER JOIN ' . $views_data_table . ' ON ' . $views . '.page_id = ' . $views_data_table . '.singular_id INNER JOIN ' . $user_sessions . ' ON ' . $views . '.user_session_id = ' . $user_sessions . '.user_session_id LEFT JOIN ' . $users . ' ON ' . $user_sessions . '.visitor_id = ' . $users . '.visitor_id WHERE ' . $views . '.page_id = %d ' . $condition . ' ORDER BY ' . $views . '.viewed_at DESC LIMIT %d',
			...$spread
		);

		return $wpdb->get_results( $query );
	}

	public static function get_detailed_reports_with_pagi( $p_id, $limit, $page = 20, $args = array() ) {
		global $wpdb;

		$views            = self::get_table_name( self::VIEWS );
		$views_data_table = self::get_table_name( self::VIEWS_DATA );
		$user_sessions    = self::get_table_name( self::SESSIONS );
		$users            = self::get_table_name( self::VISITORS );

		$condition      = '';
		$spread         = array();
		$spread['p_id'] = $p_id;

		if ( isset( $args['startDate'] ) && isset( $args['endDate'] ) && ! empty( $args['startDate'] ) ) {
			$condition           .= ' AND ( ' . $views . '.viewed_at BETWEEN %s AND %s ) ';
			$spread['startDate'] = $args['startDate'] . ' 00:00:00';
			$spread['endDate']   = $args['endDate'] . ' 23:59:59';
		}
		if ( isset( $args['device_type'] ) && ! empty( $args['device_type'] ) ) {
			$condition             .= ' AND ' . $user_sessions . '.device_type = %s ';
			$spread['device_type'] = $args['device_type'];
		}
		if ( isset( $args['device_os'] ) && ! empty( $args['device_os'] ) ) {
			$condition           .= ' AND ' . $user_sessions . '.device_os = %s ';
			$spread['device_os'] = $args['device_os'];
		}
		if ( isset( $args['device_br'] ) && ! empty( $args['device_br'] ) ) {
			$condition           .= ' AND ' . $user_sessions . '.device_browser = %s ';
			$spread['device_br'] = $args['device_br'];
		}
		$spread['offset'] = $page;
		$spread['limit']  = $limit;

		$query = $wpdb->prepare(
			'SELECT * FROM ' . $views . ' INNER JOIN ' . $views_data_table . ' ON ' . $views . '.page_id = ' . $views_data_table . '.singular_id INNER JOIN ' . $user_sessions . ' ON ' . $views . '.user_session_id = ' . $user_sessions . '.user_session_id LEFT JOIN ' . $users . ' ON ' . $user_sessions . '.visitor_id = ' . $users . '.visitor_id WHERE ' . $views . '.page_id = %d ' . $condition . ' ORDER BY ' . $views . '.viewed_at DESC LIMIT %d OFFSET %d',
			...$spread
		);

		return $wpdb->get_results( $query );
	}

	public static function get_device_list() {
		global $wpdb;
		$user_sessions = self::get_table_name( self::SESSIONS );
		return $wpdb->get_results(
			'SELECT device_type FROM ' . $user_sessions . ' GROUP BY device_type ASC'
		);
	}

	public static function get_device_os_list() {
		global $wpdb;
		$user_sessions = self::get_table_name( self::SESSIONS );
		return $wpdb->get_results( 'SELECT device_os FROM ' . $user_sessions . ' GROUP BY device_os ASC' );
	}

	public static function get_device_br_list() {
		global $wpdb;
		$user_sessions = self::get_table_name( self::SESSIONS );
		return $wpdb->get_results( 'SELECT device_browser FROM ' . $user_sessions . ' WHERE device_browser IS NOT NULL AND device_browser != "" GROUP BY device_browser ASC' );
	}

	public static function get_trending_referrer_name( $startDate = '', $endDate = '' ) {
		global $wpdb;
		$user_sessions        = self::get_table_name( self::SESSIONS );
		$referrers_table      = self::get_table_name( self::REFERRERS );
		$referrer_types_table = self::get_table_name( self::REFERRER_TYPES );
		$condition            = '';
		$spread               = array();

		if ( ! empty( $startDate ) && ! empty( $endDate ) ) {
			$condition           .= ' WHERE ( ' . $user_sessions . '.created_at BETWEEN %s AND %s ) ';
			$spread['startDate'] = $startDate . ' 00:00:00';
			$spread['endDate']   = $endDate . ' 23:59:59';
		}

		if ( ! empty( $spread ) ) {
			$ref = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT `referrer_id`, COUNT(`referrer_id`) FROM ' . $user_sessions . ' ' . $condition . ' GROUP BY referrer_id HAVING COUNT(`referrer_id`) > 1',
					$spread['startDate'],
					$spread['endDate']
				)
			);
		} else {
			$ref = $wpdb->get_var( 'SELECT `referrer_id`, COUNT(`referrer_id`) FROM ' . $user_sessions . ' GROUP BY referrer_id HAVING COUNT(`referrer_id`) > 1' );
		}

		if ( 0 === (int) $ref ) {
			return __( 'Direct', 'apvc' );
		}

		return $wpdb->get_var(
			$wpdb->prepare(
				'SELECT CONCAT (' . $referrer_types_table . '.name, "/", ' . $referrer_types_table . '.type ) FROM ' . $referrers_table . ' INNER JOIN ' . $referrer_types_table . ' ON ' . $referrers_table . '.domain_name = ' . $referrer_types_table . '.domain_to_match WHERE `id` = %d',
				$ref
			)
		);
	}


	public static function get_referrers_list() {
		global $wpdb;
		$user_sessions        = self::get_table_name( self::SESSIONS );
		$referrers_table      = self::get_table_name( self::REFERRERS );
		$referrer_types_table = self::get_table_name( self::REFERRER_TYPES );

		$ref = $wpdb->get_results( 'SELECT * FROM ' . $user_sessions . ' GROUP BY referrer_id  ' );

		$referrers_list = array();
		if ( ! empty( $ref ) && count( $ref ) > 0 ) {
			$index = 0;
			foreach ( $ref as $refer ) {

				if ( 0 === (int) $refer->referrer_id ) {
					$referrers_list[ $index ]['referrer'] = __( 'Direct', 'apvc' );
					$referrers_list[ $index ]['type']     = __( 'Direct', 'apvc' );
					$referrers_list[ $index ]['visitors'] = APVC_Helper_Functions::getTotalVisitorsForRef( (int) $refer->referrer_id );
					$referrers_list[ $index ]['views']    = APVC_Helper_Functions::getTotalViewsForRef( (int) $refer->referrer_id );
				} else {

					$refer_name = $wpdb->get_var(
						$wpdb->prepare(
							'SELECT `domain_name` FROM ' . $referrers_table . ' WHERE `id` = %d',
							(int) $refer->referrer_id
						)
					);

					$ref_data = $wpdb->get_row(
						$wpdb->prepare(
							'SELECT * FROM ' . $referrer_types_table . ' WHERE `domain_to_match` = %s',
							$refer_name
						)
					);

					if ( ! empty( $ref_data->name ) ) {
						$referrers_list[ $index ]['referrer'] = $ref_data->name;
						$referrers_list[ $index ]['type']     = $ref_data->type;
						$referrers_list[ $index ]['visitors'] = APVC_Helper_Functions::getTotalVisitorsForRef( (int) $refer->referrer_id );
						$referrers_list[ $index ]['views']       = APVC_Helper_Functions::getTotalViewsForRef( (int) $refer->referrer_id );
					}
				}
				$index++;
			}
		}

		return $referrers_list;
	}

	public static function save_shortcode( $title, $shortcode ) {
		global $wpdb;
		$shortcodes_table = self::get_table_name( self::SHORTCODES );
		$shortcodes_sql   = $wpdb->prepare(
			'INSERT INTO ' . $shortcodes_table . ' (title, shortcode, date ) VALUES ( %s, %s, %s )',
			$title,
			$shortcode,
			current_time( 'mysql' )
		);

		$wpdb->query( $shortcodes_sql );
		return $wpdb->insert_id;
	}

	public static function delete_shortcode( $id ) {
		global $wpdb;
		$shortcodes_table = self::get_table_name( self::SHORTCODES );
		if ( 0 < $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . $shortcodes_table . ' WHERE `id` = %d', $id ) ) ) {
			return $wpdb->query(
				$wpdb->prepare(
					'DELETE FROM ' . $shortcodes_table . ' WHERE `id` = %d',
					$id
				)
			);
		}
	}

	public static function get_custom_shortcodes() {
		global $wpdb;
		$shortcodes_table = self::get_table_name( self::SHORTCODES );
		$shortcodes_sql   = 'SELECT * FROM ' . $shortcodes_table;
		return $wpdb->get_results( 'SELECT * FROM ' . $shortcodes_table );
	}

	public static function get_current_page_counts( $post_id ) {
		global $wpdb;
		$views_data  = self::get_table_name( self::VIEWS_DATA );
		$count_query = $wpdb->prepare(
			'SELECT `views` FROM ' . $views_data . ' WHERE `singular_id` = %d',
			$post_id
		);
		$count       = $wpdb->get_var( $count_query );
		return APVC_Admin_Helpers::filter_count_format( apply_filters( 'post_starting_count', $count, $post_id ) );
	}

	public static function get_current_page_today_counts( $post_id ) {
		global $wpdb;
		$start_date_bs = new \DateTime( 'today 00:00:00' );
		$end_date_bs   = new \DateTime( 'today 23:59:00' );

		$start_date = APVC_Timezone_Helpers::start_of_locale_day( $start_date_bs );
		$end_date   = APVC_Timezone_Helpers::end_of_locale_day( $end_date_bs );

		$views_data    = self::get_table_name( self::VIEWS );
		$existing_data = $wpdb->prepare(
			"SELECT COUNT(*) as views FROM $views_data WHERE `page_id` = %d AND `viewed_at` BETWEEN %s AND %s",
			$post_id,
			$start_date->format( APVC_DATE_FORMAT ),
			$end_date->format( APVC_DATE_FORMAT )
		);

		return APVC_Admin_Helpers::filter_count_format( absint($wpdb->get_var( $existing_data )) );
	}

	public static function get_total_counts() {
		global $wpdb;
		$views_data  = self::get_table_name( self::VIEWS_DATA );
		$count_query = 'SELECT SUM(`views`) FROM ' . $views_data;
		return APVC_Admin_Helpers::filter_count_format( $wpdb->get_var( $count_query ) );
	}

	public static function get_starting_count( $id ) {
		return APVC_Admin_Helpers::filter_count_format( ( get_post_meta( $id, 'apvc_starting_count', true ) ) ? get_post_meta( $id, 'apvc_starting_count', true ) : 0 );
	}

	public static function set_starting_count( $id, $count = 0 ) {
		return update_post_meta( $id, 'apvc_starting_count', (int) $count );
	}

	public static function reset_everything() {
		global $wpdb;

		$views      = self::get_table_name( self::VIEWS );
		$views_data = self::get_table_name( self::VIEWS_DATA );
		$referrers  = self::get_table_name( self::REFERRERS );
		$visitors   = self::get_table_name( self::VISITORS );
		$sessions   = self::get_table_name( self::SESSIONS );

		$wpdb->query( 'TRUNCATE TABLE `' . $views . '`' );
		$wpdb->query( 'TRUNCATE TABLE `' . $views_data . '`' );
		$wpdb->query( 'TRUNCATE TABLE `' . $referrers . '`' );
		$wpdb->query( 'TRUNCATE TABLE `' . $visitors . '`' );
		$wpdb->query( 'TRUNCATE TABLE `' . $sessions . '`' );

		return wp_send_json_success( 'Success' );
	}

	public static function get_top_articles( $type ) {
		global $wpdb;
		$views_data = self::get_table_name( self::VIEWS_DATA );
		$pages      = $wpdb->get_results( $wpdb->prepare( 'SELECT `singular_id` as page_id, `stored_title` as page_title, `stored_url` as page_url, `views` FROM ' . $views_data . ' WHERE `stored_type` = %s ORDER BY `views` DESC LIMIT 10', $type ) );
		$pages_list = array();
		if ( ! empty( $pages ) && count( $pages ) > 0 ) {
			$index = 0;
			foreach ( $pages as $page ) {
				$pages_list[ $index ]['page_id']       = $page->page_id;
				$pages_list[ $index ]['page_title']    = ( 45 < strlen( $page->page_title ) ) ? substr( $page->page_title, 0, 45 ) . '...' : $page->page_title;
				$pages_list[ $index ]['page_url']      = $page->page_url;
				$pages_list[ $index ]['views']         = $page->views;
				$pages_list[ $index ]['visitors']      = self::get_visitors_for_trending( absint( $page->page_id ) );
				$pages_list[ $index ]['user_sessions'] = self::get_user_sessions_for_trending( absint( $page->page_id ) );
				$index++;
			}
		}

		return $pages_list;
	}

	public static function get_user_sessions_for_trending( $page_id ) {
		global $wpdb;
		$views_tbl = self::get_table_name( self::VIEWS );
		return count( $wpdb->get_results( $wpdb->prepare( 'SELECT DISTINCT(user_session_id) as sessions FROM ' . $views_tbl . ' WHERE `page_id` = %d GROUP BY `user_session_id` DESC', $page_id ) ) );
	}

	public static function get_visitors_for_trending( $page_id ) {
		global $wpdb;
		$views_tbl     = self::get_table_name( self::VIEWS );
		$user_sessions = self::get_table_name( self::SESSIONS );
		return count( $wpdb->get_results( $wpdb->prepare( 'SELECT DISTINCT(visitor_id) as visitors FROM ' . $user_sessions . ' LEFT JOIN ' . $views_tbl . ' ON ' . $user_sessions . '.user_session_id = ' . $views_tbl . '.user_session_id WHERE `page_id` = %d GROUP BY `visitor_id` DESC', $page_id ) ) );
	}

	public static function get_top_country() {
		global $wpdb;
		$visitors_tbl  = self::get_table_name( self::VISITORS );
		$views_tbl     = self::get_table_name( self::VIEWS );
		$user_sessions = self::get_table_name( self::SESSIONS );

		$pages    = $wpdb->get_results( 'SELECT COUNT(*) as views, country FROM ' . $visitors_tbl . ' LEFT JOIN ' . $user_sessions . ' ON ' . $visitors_tbl . '.visitor_id = ' . $user_sessions . '.visitor_id LEFT JOIN ' . $views_tbl . ' ON ' . $views_tbl . '.user_session_id = ' . $user_sessions . '.user_session_id WHERE country != "" GROUP BY country ORDER BY views DESC LIMIT 10' );
		$cnt_list = array();
		if ( ! empty( $pages ) && count( $pages ) > 0 ) {
			$index = 0;
			foreach ( $pages as $page ) {
				$cnt_list[ $index ]['country']       = $page->country;
				$cnt_list[ $index ]['views']         = $page->views;
				$cnt_list[ $index ]['visitors']      = self::get_visitors_for_country( $page->country );
				$cnt_list[ $index ]['user_sessions'] = self::get_user_sessions_for_country( $page->country );
				$index++;
			}
		}

		return $cnt_list;
	}

	public static function get_visitors_for_country( $country ) {
		global $wpdb;
		$visitors_tbl = self::get_table_name( self::VISITORS );
		return $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(country) as country FROM ' . $visitors_tbl . ' WHERE `country` = %s', $country ) );
	}

	public static function get_user_sessions_for_country( $country ) {
		global $wpdb;
		$visitors_tbl      = self::get_table_name( self::VISITORS );
		$user_sessions_tbl = self::get_table_name( self::SESSIONS );
		return $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(user_session_id) as users FROM ' . $user_sessions_tbl . ' LEFT JOIN ' . $visitors_tbl . ' ON ' . $user_sessions_tbl . '.visitor_id = ' . $visitors_tbl . '.visitor_id WHERE `country` = %s', $country ) );
	}

	public static function get_top_states() {
		global $wpdb;
		$visitors_tbl  = self::get_table_name( self::VISITORS );
		$views_tbl     = self::get_table_name( self::VIEWS );
		$user_sessions = self::get_table_name( self::SESSIONS );

		$pages    = $wpdb->get_results( 'SELECT COUNT(*) as views, subdivision FROM ' . $visitors_tbl . ' LEFT JOIN ' . $user_sessions . ' ON ' . $visitors_tbl . '.visitor_id = ' . $user_sessions . '.visitor_id LEFT JOIN ' . $views_tbl . ' ON ' . $views_tbl . '.user_session_id = ' . $user_sessions . '.user_session_id WHERE subdivision != "" GROUP BY subdivision ORDER BY views DESC LIMIT 10' );
		$cnt_list = array();
		if ( ! empty( $pages ) && count( $pages ) > 0 ) {
			$index = 0;
			foreach ( $pages as $page ) {
				$cnt_list[ $index ]['state']         = $page->subdivision;
				$cnt_list[ $index ]['views']         = $page->views;
				$cnt_list[ $index ]['visitors']      = self::get_visitors_for_states( $page->subdivision );
				$cnt_list[ $index ]['user_sessions'] = self::get_user_sessions_for_states( $page->subdivision );
				$index++;
			}
		}

		return $cnt_list;
	}

	public static function get_visitors_for_states( $subdivision ) {
		global $wpdb;
		$visitors_tbl = self::get_table_name( self::VISITORS );
		return $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(subdivision) as subdivision FROM ' . $visitors_tbl . ' WHERE `subdivision` = %s', $subdivision ) );
	}

	public static function get_user_sessions_for_states( $subdivision ) {
		global $wpdb;
		$visitors_tbl      = self::get_table_name( self::VISITORS );
		$user_sessions_tbl = self::get_table_name( self::SESSIONS );
		return $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(user_session_id) as users FROM ' . $user_sessions_tbl . ' LEFT JOIN ' . $visitors_tbl . ' ON ' . $user_sessions_tbl . '.visitor_id = ' . $visitors_tbl . '.visitor_id WHERE `subdivision` = %s', $subdivision ) );
	}

	public static function get_top_cities() {
		global $wpdb;
		$visitors_tbl  = self::get_table_name( self::VISITORS );
		$views_tbl     = self::get_table_name( self::VIEWS );
		$user_sessions = self::get_table_name( self::SESSIONS );

		$pages    = $wpdb->get_results( 'SELECT COUNT(*) as views, city FROM ' . $visitors_tbl . ' LEFT JOIN ' . $user_sessions . ' ON ' . $visitors_tbl . '.visitor_id = ' . $user_sessions . '.visitor_id LEFT JOIN ' . $views_tbl . ' ON ' . $views_tbl . '.user_session_id = ' . $user_sessions . '.user_session_id WHERE city != "" GROUP BY city ORDER BY views DESC LIMIT 10' );
		$cnt_list = array();
		if ( ! empty( $pages ) && count( $pages ) > 0 ) {
			$index = 0;
			foreach ( $pages as $page ) {
				$cnt_list[ $index ]['city']          = $page->city;
				$cnt_list[ $index ]['views']         = $page->views;
				$cnt_list[ $index ]['visitors']      = self::get_visitors_for_cities( $page->city );
				$cnt_list[ $index ]['user_sessions'] = self::get_user_sessions_for_cities( $page->city );
				$index++;
			}
		}

		return $cnt_list;
	}

	public static function get_visitors_for_cities( $city ) {
		global $wpdb;
		$visitors_tbl = self::get_table_name( self::VISITORS );
		return $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(city) as city FROM ' . $visitors_tbl . ' WHERE `city` = %s', $city ) );
	}

	public static function get_user_sessions_for_cities( $city ) {
		global $wpdb;
		$visitors_tbl      = self::get_table_name( self::VISITORS );
		$user_sessions_tbl = self::get_table_name( self::SESSIONS );
		return $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(user_session_id) as users FROM ' . $user_sessions_tbl . ' LEFT JOIN ' . $visitors_tbl . ' ON ' . $user_sessions_tbl . '.visitor_id = ' . $visitors_tbl . '.visitor_id WHERE `city` = %s', $city ) );
	}

	public static function get_top_devices() {
		global $wpdb;
		$views_tbl     = self::get_table_name( self::VIEWS );
		$user_sessions = self::get_table_name( self::SESSIONS );
		$pages         = $wpdb->get_results( 'SELECT COUNT(*) as views, device_type FROM ' . $user_sessions . ' LEFT JOIN ' . $views_tbl . ' ON ' . $user_sessions . '.user_session_id = ' . $views_tbl . '.user_session_id WHERE device_type != "" GROUP BY device_type ORDER BY views DESC LIMIT 10' );
		$dvc_list      = array();
		if ( ! empty( $pages ) && count( $pages ) > 0 ) {
			$index = 0;
			foreach ( $pages as $page ) {
				$dvc_list[ $index ]['device']        = $page->device_type;
				$dvc_list[ $index ]['views']         = $page->views;
				$dvc_list[ $index ]['visitors']      = self::get_visitors_for_devices( $page->device_type );
				$dvc_list[ $index ]['user_sessions'] = self::get_user_sessions_for_devices( $page->device_type );
				$index++;
			}
		}

		return $dvc_list;
	}

	public static function get_visitors_for_devices( $device_type ) {
		global $wpdb;
		$sessions_tbl = self::get_table_name( self::SESSIONS );
		return count( $wpdb->get_results( $wpdb->prepare( 'SELECT DISTINCT(visitor_id) as visitor_id FROM ' . $sessions_tbl . ' WHERE `device_type` = %s GROUP BY `visitor_id`', $device_type ) ) );
	}

	public static function get_user_sessions_for_devices( $device_type ) {
		global $wpdb;
		$user_sessions_tbl = self::get_table_name( self::SESSIONS );
		return $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(user_session_id) as users FROM ' . $user_sessions_tbl . ' WHERE `device_type` = %s', $device_type ) );
	}

	public static function get_top_devices_os() {
		global $wpdb;
		$views_tbl     = self::get_table_name( self::VIEWS );
		$user_sessions = self::get_table_name( self::SESSIONS );
		$pages         = $wpdb->get_results( 'SELECT COUNT(*) as views, device_os FROM ' . $user_sessions . ' LEFT JOIN ' . $views_tbl . ' ON ' . $user_sessions . '.user_session_id = ' . $views_tbl . '.user_session_id WHERE device_os != "" GROUP BY device_os ORDER BY views DESC LIMIT 10' );

		$dvc_list = array();
		if ( ! empty( $pages ) && count( $pages ) > 0 ) {
			$index = 0;
			foreach ( $pages as $page ) {
				$dvc_list[ $index ]['device_os']     = $page->device_os;
				$dvc_list[ $index ]['views']         = $page->views;
				$dvc_list[ $index ]['visitors']      = self::get_visitors_for_device_os( $page->device_os );
				$dvc_list[ $index ]['user_sessions'] = self::get_user_sessions_for_device_os( $page->device_os );
				$index++;
			}
		}

		return $dvc_list;
	}

	public static function get_visitors_for_device_os( $device_os ) {
		global $wpdb;
		$sessions_tbl = self::get_table_name( self::SESSIONS );
		return count( $wpdb->get_results( $wpdb->prepare( 'SELECT DISTINCT(visitor_id) as visitor_id FROM ' . $sessions_tbl . ' WHERE `device_os` = %s GROUP BY `visitor_id`', $device_os ) ) );
	}

	public static function get_user_sessions_for_device_os( $device_os ) {
		global $wpdb;
		$user_sessions_tbl = self::get_table_name( self::SESSIONS );
		return $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(user_session_id) as users FROM ' . $user_sessions_tbl . ' WHERE `device_os` = %s', $device_os ) );
	}

	public static function get_top_browsers() {
		global $wpdb;
		$views_tbl     = self::get_table_name( self::VIEWS );
		$user_sessions = self::get_table_name( self::SESSIONS );
		$pages         = $wpdb->get_results( 'SELECT COUNT(*) as views, device_browser FROM ' . $user_sessions . ' LEFT JOIN ' . $views_tbl . ' ON ' . $user_sessions . '.user_session_id = ' . $views_tbl . '.user_session_id WHERE device_browser != "" GROUP BY device_browser ORDER BY views DESC LIMIT 10' );

		$dvc_list = array();
		if ( ! empty( $pages ) && count( $pages ) > 0 ) {
			$index = 0;
			foreach ( $pages as $page ) {
				$dvc_list[ $index ]['device_browser'] = $page->device_browser;
				$dvc_list[ $index ]['views']          = $page->views;
				$dvc_list[ $index ]['visitors']       = self::get_visitors_for_device_browser( $page->device_browser );
				$dvc_list[ $index ]['user_sessions']  = self::get_user_sessions_for_device_browser( $page->device_browser );
				$index++;
			}
		}

		return $dvc_list;
	}

	public static function get_visitors_for_device_browser( $device_browser ) {
		global $wpdb;
		$sessions_tbl = self::get_table_name( self::SESSIONS );
		return count( $wpdb->get_results( $wpdb->prepare( 'SELECT DISTINCT(visitor_id) as visitor_id FROM ' . $sessions_tbl . ' WHERE `device_browser` = %s GROUP BY `visitor_id`', $device_browser ) ) );
	}

	public static function get_user_sessions_for_device_browser( $device_browser ) {
		global $wpdb;
		$user_sessions_tbl = self::get_table_name( self::SESSIONS );
		return $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(user_session_id) as users FROM ' . $user_sessions_tbl . ' WHERE `device_browser` = %s', $device_browser ) );
	}

}