<?php

class PeepSoAdminEngagementDashboard extends PeepSoAjaxCallback
{/*
	 * Builds the required flot data set based on the request
	 * @param PeepSoAjaxResponse $resp The response object
	 */
	public function get_graph_data(PeepSoAjaxResponse $resp)
	{
// http://ps.davidjesch.com/peepsoajax/adminEngagementDashboard.get_graph_data?
//	date_range=this_month&
//	stats[]=peepso-comment&
//	stats[]=peepso-post&
//	stats[]=likes
		if (!PeepSo::is_admin()) {
			$resp->success(FALSE);
			$resp->error(__('Insufficient permissions.', 'peepso-core'));
			return;
		}
		
		$stats = $this->_input->value('stats', 'peepso-comment,peepso-post,likes',FALSE); // SQL safe, admin only
		$stats = explode(',', $stats);

		$series = array();
		foreach ($stats as $stat) {
			$data = $this->get_stats($stat, $resp);
			$total = 0;
			foreach ($data as $point)
				$total += $point[1];

			$series[] = array(
				'type' => $stat,
				'data' => $data,
				'total' => $total
			);
		}

		$resp->set('series', $series);

		// Piggyback breakdown + heatmap + panel HTML on the same request (one round-trip)
		$range = $this->get_date_range_where($this->_input->value('date_range', '', FALSE));
		if (is_array($range)) {
			$resp->set('breakdown',         $this->get_breakdown_for_range($range));
			$resp->set('heatmap',           $this->get_heatmap_for_range($range));
			$resp->set('top_content_html',  PeepSoAdminDashboardModern::render_top_content_for_ajax($range));
			$resp->set('contributors_html', PeepSoAdminDashboardModern::render_contributors_for_ajax($range));
		}

		$resp->success(TRUE);
	}

	/**
	 * Returns post/comment/reaction totals for a date range (used by the donut chart).
	 */
	private function get_breakdown_for_range(array $range)
	{
		global $wpdb;

		$likes_table = $wpdb->prefix . 'peepso_likes';
		$reactions_table = $wpdb->prefix . 'peepso_reactions';
		$min = $range['min'];
		$max = $range['max'];

		return array(
			'posts'     => intval($wpdb->get_var($wpdb->prepare(
				"SELECT COUNT(*) FROM `{$wpdb->posts}`
				 WHERE post_type='peepso-post' AND post_status='publish'
				   AND (CAST(post_date AS DATE) BETWEEN %s AND %s)",
				$min, $max
			))),
			'comments'  => intval($wpdb->get_var($wpdb->prepare(
				"SELECT COUNT(*) FROM `{$wpdb->posts}`
				 WHERE post_type='peepso-comment' AND post_status='publish'
				   AND (CAST(post_date AS DATE) BETWEEN %s AND %s)",
				$min, $max
			))),
			'reactions' => intval($wpdb->get_var($wpdb->prepare(
				"SELECT SUM(cnt) as total FROM (
				SELECT COUNT(*) cnt FROM `{$likes_table}`
					WHERE (CAST(like_timestamp AS DATE) BETWEEN %s AND %s)
				UNION 
				SELECT COUNT(*) cnt FROM `{$reactions_table}`
					WHERE (CAST(reaction_timestamp AS DATE) BETWEEN %s AND %s)
				) as X",
				$min, $max, $min, $max
			))),
		);
	}

	/**
	 * Returns a 7×24 activity matrix for the heatmap.
	 * Uses post_date (WordPress local timezone) so hours match the WP timezone setting.
	 */
	private function get_heatmap_for_range(array $range)
	{
		global $wpdb;

		$min = $range['min'];
		$max = $range['max'];

		$rows = $wpdb->get_results($wpdb->prepare(
			"SELECT DAYOFWEEK(post_date) AS dow, HOUR(post_date) AS hour, COUNT(*) AS cnt
			 FROM `{$wpdb->posts}`
			 WHERE post_type IN ('peepso-post','peepso-comment')
			   AND post_status = 'publish'
			   AND (CAST(post_date AS DATE) BETWEEN %s AND %s)
			 GROUP BY dow, hour",
			$min, $max
		));

		// MySQL DAYOFWEEK: 1=Sun … 7=Sat
		// Reorder to Mon=0, Tue=1, …, Sat=5, Sun=6
		$matrix = array();
		for ($d = 0; $d < 7; $d++) {
			$matrix[$d] = array_fill(0, 24, 0);
		}
		foreach ($rows as $row) {
			$dow = intval($row->dow);
			$d   = ($dow === 1) ? 6 : $dow - 2; // Sun→6, Mon→0 … Sat→5
			$h   = intval($row->hour);
			$matrix[$d][$h] = intval($row->cnt);
		}

		return $matrix;
	}

	/**
	 * Default function
	 * Returns series data from the peepso_activities table, custom data should create other functions
	 * @param $type string The post type
	 * @return array Must contain [min, max, data] for proper rendering of graph
	 */
	public function get_stats($type, &$resp)
	{
		global $wpdb;
		
		add_filter('peepso_activity_date_range_where', array(&$this, 'get_date_range_where'));
		$range = apply_filters('peepso_activity_date_range_where', $this->_input->value('date_range','',FALSE)); // SQL safe, admin only

		$resp->set('min', strtotime($range['min']) * 1000);
		$resp->set('max', strtotime($range['max']) * 1000);

		add_filter('peepso_admin_engagement_graph_stats', array(&$this, '_get_stats'), 10, 3);

		$query = apply_filters('peepso_admin_engagement_graph_stats', $type, $range);

		$data = array();

		if (count($query) > 0) {
			foreach ($query as $_data) {
                if(!is_object($_data)) {
                    continue;
                }
				$data[] = array(strtotime($_data->_post_date) * 1000, $_data->post_count);
			}
		}

		return ($data);
	}

	/**
	 * Called from 'peepso_admin_engagement_graph_stats'
	 * @param  string $type      The post type to get stats of
	 * @param  array $range      Contains min, max and a where clause for date range filtering
	 * @return object            The WP_Query object
	 */
	public function _get_stats($type, $range)
	{
		global $wpdb;

		switch ($type) {
			case PeepSoActivityStream::CPT_POST:
			case PeepSoActivityStream::CPT_COMMENT:
				// TODO: use TABLE_NAME constant from PeepSoActivity for table reference
				$base_query = '
					SELECT
						COUNT(`ID`) AS `post_count`, CAST(`post_date` AS DATE) AS `_post_date`
						FROM `'. $wpdb->posts .'`
						LEFT JOIN `'. $wpdb->prefix . 'peepso_activities` `pa`
							ON `pa`.`act_external_id` = `'. $wpdb->posts . '`.`ID`
						WHERE ';
				$where = array('`post_type` = %s AND `post_status` = %s');
				$where[] = $range['where'];
				$base_query .= implode(' AND ', $where) . ' GROUP BY `_post_date`';
				$base_query = $wpdb->prepare($base_query, $type, 'publish');
//				return ($wpdb->get_results($base_query));
//echo 'query=' . $base_query, PHP_EOL;
				$res = $wpdb->get_results($base_query);
//echo 'res=' . var_export($res, TRUE), PHP_EOL;
				return ($res);
			case 'likes':
				$likes = PeepSoLike::get_instance();
				return ($likes->get_likes_graph_data(
					array('from' => $range['min'], 'to' => $range['max'])
				));
			default:
				// Try to get a custom filter.
				return (apply_filters('peepso_admin_engagement_graph_stats-' . $type, $range));
		}
	}

	/*
	 * Determines the minimum and maximum dates to query for data and precreates an
	 * SQL WHERE condition
	 * @string $date_range The date range indentifier
	 * @return array [min, max, where]
	 */
	public function get_date_range_where($date_range)
	{
		// Note: strtotime() returns different data for certain strings such as 'first day this month' depending on the version of PHP.
		// Because of this, I added checking for a return value of FALSE, indicating an error. In these cases, I calculate the date
		// "the hard way" by using the day of week or the day of month. This ensure correct behavior across different PHP versions.

		// TODO: should be using the configured starting day of week instead of assuming Sun...Sat
		switch ($date_range)
		{
		case 'this_week':
			$min = strtotime('sunday last week'); // this week');	// $date_time_from->modify('this week')->format('Y-m-d');
			// Add one more day so that saturday this week is included
			$max = strtotime('sunday this week'); // last week');	// $date_time_from->modify('next week')->format('Y-m-d');
//echo 'min=', var_export($min), date(' Y-m-d', $min), ' max=', var_export($max), date(' Y-m-d', $max), ' : ', __LINE__, PHP_EOL;
//			if (FALSE === $min || FALSE === $max) {
				$dow = intval(date('w'));						// 0=Sun, 1=Mon, etc.
				$today = strtotime('now');
				$min = strtotime(sprintf('%d days ago', $dow));	// use day of week to calculate beginning of week
				$max = $today;									// use today as end of week
//echo 'min=', var_export($min), date(' Y-m-d', $min), ' max=', var_export($max), date(' Y-m-d', $max), ' : ', __LINE__, PHP_EOL;
//			}
			break;
		case 'last_week':
			$min = strtotime('sunday 1 week ago'); // monday last week');	// $date_time_from->modify('monday last week')->format('Y-m-d');
			$max = strtotime('sunday last week');	// this week');			// $date_time_to->modify('this week')->format('Y-m-d');
//echo 'min=', var_export($min), date(' Y-m-d', $min), ' max=', var_export($max), date(' Y-m-d', $max), ' : ', __LINE__, PHP_EOL;
//			if (FALSE === $min || FALSE === $max) {
				$dow = intval(date('w'));						// 0=Sun, 1=Mon, etc.
				$today = strtotime('now');
				$max = strtotime(sprintf('%d days ago', $dow + 1));	// use day of week to calculate end of last week
				$min = strtotime('6 days ago', $max);
//echo 'min=', var_export($min), date(' Y-m-d', $min), ' max=', var_export($max), date(' Y-m-d', $max), ' : ', __LINE__, PHP_EOL;
//			}
			break;
		case 'this_month':
			$min = strtotime('first day this month');	// $date_time_from->modify('first day of this month')->format('Y-m-d');
			$max = strtotime('last day this month');		// $date_time_to->modify('last day of this month')->format('Y-m-d');
//echo 'min=', var_export($min), date(' Y-m-d', $min), ' max=', var_export($max), date(' Y-m-d', $max), ' : ', __LINE__, PHP_EOL;
			if (FALSE === $min || FALSE === $max || 1 !== intval(date('d', $min))) {
				$today = strtotime('now');					// start with current date

				$day = intval(date('d', $today));			// get day of month
				$max = $today;								// it's the current month so use today

				$min = strtotime(sprintf('%d days ago', $day - 1), $max);	// first day of month = last - (days in month - 1)
//echo 'min=', var_export($min), date(' Y-m-d', $min), ' max=', var_export($max), date(' Y-m-d', $max), ' : ', __LINE__, PHP_EOL;
			}
			break;
		case 'last_month':
			$min = strtotime('first day of last month');	// $date_time_from->modify('first day of last month')->format('Y-m-d');
			$max = strtotime('last day of last month');		// $date_time_to->modify('last day of last month')->format('Y-m-d');
//echo 'min=', var_export($min), date(' Y-m-d', $min), ' max=', var_export($max), date(' Y-m-d', $max), ' : ', __LINE__, PHP_EOL;
			if (FALSE === $min || FALSE === $max || 1 !== intval(date('d', $min))) {
				$today = strtotime('now');					// start with current date

				$day = intval(date('d', $today));			// get day of month
				$max = strtotime(sprintf('%d days ago', $day), $today);	// last day of previous month = today - day_of_month

				$days_in_month = intval(date('d', $max));		// get days in last month
				$min = strtotime(sprintf('%d days ago', $days_in_month - 1), $max);	// first day of month = last - (days in month - 1)
//echo 'min=', var_export($min), date(' Y-m-d', $min), ' max=', var_export($max), date(' Y-m-d', $max), ' : ', __LINE__, PHP_EOL;
			}
			break;
		default:
			if (preg_match('/^custom:(\d{4}-\d{2}-\d{2}):(\d{4}-\d{2}-\d{2})$/', $date_range, $m)) {
				$mindate = $m[1];
				$maxdate = $m[2];
				if ($mindate > $maxdate) { list($mindate, $maxdate) = array($maxdate, $mindate); }
				return array(
					'min'   => $mindate,
					'mints' => strtotime($mindate),
					'max'   => $maxdate,
					'maxts' => strtotime($maxdate),
					'where' => " (CAST(`post_date` AS DATE) BETWEEN '{$mindate}' AND '{$maxdate}') "
				);
			}
			return '1';
		}
		$mindate = date('Y-m-d', $min);
		$maxdate = date('Y-m-d', $max);

		return (array(
			'min' => $mindate,
			'mints' => $min,
			'max' => $maxdate,
			'maxts' => $max,
			'where' => " (CAST(`post_date` AS DATE) BETWEEN '{$mindate}' AND '{$maxdate}') "
		));
	}
}

// EOF
