<?php
namespace Footloom;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// Queries in this file read and write the plugin's own custom tables, so they use $wpdb directly and
// are not cached. Table names come from Plugin::table() and every value from the request goes through
// $wpdb->prepare(). The sniffs below cannot see that through the interpolated table names.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter

// Summary numbers, chart, heatmap and ranked reports for a date range.
class Reports {

	public static function stats( \WP_REST_Request $request ) {
		global $wpdb;

		$offset = Plugin::tz_offset();
		list( $from, $to, $start, $end ) = Rest::date_range( $request );

		$s = Plugin::table( 'sessions' );
		$p = Plugin::table( 'pageviews' );
		$e = Plugin::table( 'events' );

		// All queries below only interpolate table names and the integer offset. Dates go through prepare().
		$in_sessions  = $wpdb->prepare( 's.started_at >= %s AND s.started_at < %s', $start, $end );
		$in_pageviews = $wpdb->prepare( 'p.created_at >= %s AND p.created_at < %s', $start, $end );
		$in_events    = $wpdb->prepare( 'e.created_at >= %s AND e.created_at < %s', $start, $end );

		// A bounce is a visit that saw one page and left within 10 seconds.
		$summary = $wpdb->get_row(
			"SELECT COUNT(DISTINCT s.visitor_id) AS visitors,
				COUNT(DISTINCT CASE WHEN s.is_new = 1 THEN s.visitor_id END) AS new_visitors,
				COUNT(*) AS sessions,
				SUM(s.pageview_count = 1 AND TIMESTAMPDIFF(SECOND, s.started_at, s.last_seen_at) < 10) AS bounces,
				AVG(TIMESTAMPDIFF(SECOND, s.started_at, s.last_seen_at)) AS avg_duration
			FROM $s s WHERE $in_sessions",
			ARRAY_A
		);
		$pageview_total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM $p p WHERE $in_pageviews" );
		$visitor_total  = (int) $summary['visitors'];
		$session_total  = (int) $summary['sessions'];

		$summary = array(
			'visitors'           => $visitor_total,
			'new_visitors'       => (int) $summary['new_visitors'],
			'returning_visitors' => $visitor_total - (int) $summary['new_visitors'],
			'sessions'           => $session_total,
			'pageviews'          => $pageview_total,
			'events'             => (int) $wpdb->get_var( "SELECT COUNT(*) FROM $e e WHERE $in_events" ),
			'pages_per_session'  => $session_total ? round( $pageview_total / $session_total, 2 ) : 0,
			'bounce_rate'        => $session_total ? round( 100 * $summary['bounces'] / $session_total, 1 ) : 0,
			'avg_duration'       => (int) $summary['avg_duration'],
		);

		// Daily chart, with empty days filled in.
		$days = array();
		for ( $day = strtotime( "$from UTC" ); $day <= strtotime( "$to UTC" ); $day += DAY_IN_SECONDS ) {
			$days[ gmdate( 'Y-m-d', $day ) ] = array( 'visitors' => 0, 'pageviews' => 0 );
		}
		$rows = $wpdb->get_results(
			"SELECT DATE(DATE_ADD(s.started_at, INTERVAL $offset SECOND)) AS day, COUNT(DISTINCT s.visitor_id) AS n
			FROM $s s WHERE $in_sessions GROUP BY day"
		);
		foreach ( $rows as $row ) {
			if ( isset( $days[ $row->day ] ) ) {
				$days[ $row->day ]['visitors'] = (int) $row->n;
			}
		}
		$rows = $wpdb->get_results(
			"SELECT DATE(DATE_ADD(p.created_at, INTERVAL $offset SECOND)) AS day, COUNT(*) AS n
			FROM $p p WHERE $in_pageviews GROUP BY day"
		);
		foreach ( $rows as $row ) {
			if ( isset( $days[ $row->day ] ) ) {
				$days[ $row->day ]['pageviews'] = (int) $row->n;
			}
		}

		// Heatmap: sessions by weekday (0 = Sunday) and hour.
		$heatmap = array_fill( 0, 7, array_fill( 0, 24, 0 ) );
		$rows    = $wpdb->get_results(
			"SELECT DAYOFWEEK(DATE_ADD(s.started_at, INTERVAL $offset SECOND)) - 1 AS wd,
				HOUR(DATE_ADD(s.started_at, INTERVAL $offset SECOND)) AS hr, COUNT(*) AS n
			FROM $s s WHERE $in_sessions GROUP BY wd, hr"
		);
		foreach ( $rows as $row ) {
			$heatmap[ (int) $row->wd ][ (int) $row->hr ] = (int) $row->n;
		}

		$top = function ( $sql ) use ( $wpdb ) {
			return array_map( function ( $row ) {
				return array( 'label' => $row['label'], 'count' => (int) $row['count'] );
			}, (array) $wpdb->get_results( $sql . ' ORDER BY count DESC LIMIT 20', ARRAY_A ) );
		};

		$tables = array(
			'pages'     => $wpdb->get_results(
				"SELECT p.path AS label, COUNT(*) AS count, COUNT(DISTINCT s.visitor_id) AS visitors,
					ROUND(AVG(p.duration)) AS avg_time
				FROM $p p JOIN $s s ON s.id = p.session_id
				WHERE $in_pageviews GROUP BY p.path ORDER BY count DESC LIMIT 20",
				ARRAY_A
			),
			'entry'     => $top( "SELECT s.entry_path AS label, COUNT(*) AS count FROM $s s WHERE $in_sessions GROUP BY label" ),
			'exit'      => $top( "SELECT s.exit_path AS label, COUNT(*) AS count FROM $s s WHERE $in_sessions GROUP BY label" ),
			'referrers' => $top( "SELECT IF(s.referrer_host = '', '(direct)', s.referrer_host) AS label, COUNT(*) AS count FROM $s s WHERE $in_sessions GROUP BY label" ),
			'utm'       => $top(
				"SELECT CONCAT_WS(' / ', s.utm_source, s.utm_medium, s.utm_campaign) AS label, COUNT(*) AS count
				FROM $s s WHERE $in_sessions AND (s.utm_source IS NOT NULL OR s.utm_campaign IS NOT NULL) GROUP BY label"
			),
			'countries' => $top( "SELECT IFNULL(s.country, 'Unknown') AS label, COUNT(*) AS count FROM $s s WHERE $in_sessions GROUP BY label" ),
			'devices'   => $top( "SELECT s.device AS label, COUNT(*) AS count FROM $s s WHERE $in_sessions GROUP BY label" ),
			'browsers'  => $top( "SELECT s.browser AS label, COUNT(*) AS count FROM $s s WHERE $in_sessions GROUP BY label" ),
			'os'        => $top( "SELECT s.os AS label, COUNT(*) AS count FROM $s s WHERE $in_sessions GROUP BY label" ),
			'screens'   => $top(
				"SELECT CASE WHEN s.screen_width IS NULL THEN 'Unknown'
					WHEN s.screen_width < 768 THEN 'Small (under 768px)'
					WHEN s.screen_width < 1280 THEN 'Medium (768 to 1279px)'
					ELSE 'Large (1280px and up)' END AS label, COUNT(*) AS count
				FROM $s s WHERE $in_sessions GROUP BY label"
			),
			'languages' => $top( "SELECT IF(s.language = '', 'Unknown', s.language) AS label, COUNT(*) AS count FROM $s s WHERE $in_sessions GROUP BY label" ),
			'search'    => $wpdb->get_results(
				"SELECT LOWER(p.search_term) AS label, COUNT(*) AS count, MIN(p.search_results) AS results
				FROM $p p WHERE $in_pageviews AND p.search_term IS NOT NULL GROUP BY label ORDER BY count DESC LIMIT 20",
				ARRAY_A
			),
			'search_empty' => $top( "SELECT LOWER(p.search_term) AS label, COUNT(*) AS count FROM $p p WHERE $in_pageviews AND p.search_results = 0 GROUP BY label" ),
		);

		// 404s with the referrer that sends the most traffic to each missing path.
		$not_found = array();
		$rows      = $wpdb->get_results(
			"SELECT p.path, s.referrer_host, COUNT(*) AS n
			FROM $p p JOIN $s s ON s.id = p.session_id
			WHERE $in_pageviews AND p.is_404 = 1 GROUP BY p.path, s.referrer_host"
		);
		foreach ( $rows as $row ) {
			$path = $row->path;
			if ( ! isset( $not_found[ $path ] ) ) {
				$not_found[ $path ] = array( 'label' => $path, 'count' => 0, 'referrer' => '', 'best' => 0 );
			}
			$not_found[ $path ]['count'] += (int) $row->n;
			if ( (int) $row->n > $not_found[ $path ]['best'] ) {
				$not_found[ $path ]['best']     = (int) $row->n;
				$not_found[ $path ]['referrer'] = '' === $row->referrer_host ? '(direct)' : $row->referrer_host;
			}
		}
		usort( $not_found, function ( $a, $b ) {
			return $b['count'] - $a['count'];
		} );
		$tables['not_found'] = array_slice( $not_found, 0, 20 );

		$tables['events'] = $wpdb->get_results(
			"SELECT e.name AS label, COUNT(*) AS count, COUNT(DISTINCT s.visitor_id) AS visitors, SUM(e.value) AS total
			FROM $e e JOIN $s s ON s.id = e.session_id
			WHERE $in_events GROUP BY e.name ORDER BY count DESC LIMIT 50",
			ARRAY_A
		);
		$tables['event_labels'] = $wpdb->get_results(
			"SELECT e.name, IFNULL(e.label, '(none)') AS label, COUNT(*) AS count
			FROM $e e WHERE $in_events GROUP BY e.name, label ORDER BY count DESC LIMIT 500",
			ARRAY_A
		);

		$goals = array();
		foreach ( (array) get_option( 'footloom_goals', array() ) as $goal ) {
			if ( 'event' === $goal['type'] ) {
				$row = $wpdb->get_row( $wpdb->prepare(
					"SELECT COUNT(*) AS completions, COUNT(DISTINCT s.visitor_id) AS visitors
					FROM $e e JOIN $s s ON s.id = e.session_id WHERE $in_events AND e.name = %s",
					$goal['match']
				) );
			} else {
				// Page goals match the exact path, or every path starting with it when it ends in *.
				$match = rtrim( $goal['match'], '*' );
				$where = substr( $goal['match'], -1 ) === '*'
					? $wpdb->prepare( 'p.path LIKE %s', $wpdb->esc_like( $match ) . '%' )
					: $wpdb->prepare( 'p.path = %s', $match );
				$row   = $wpdb->get_row(
					"SELECT COUNT(*) AS completions, COUNT(DISTINCT s.visitor_id) AS visitors
					FROM $p p JOIN $s s ON s.id = p.session_id WHERE $in_pageviews AND $where"
				);
			}
			$goals[] = array(
				'name'        => $goal['name'],
				'completions' => (int) $row->completions,
				'visitors'    => (int) $row->visitors,
				'rate'        => $visitor_total ? round( 100 * $row->visitors / $visitor_total, 1 ) : 0,
			);
		}

		return array(
			'from'    => $from,
			'to'      => $to,
			'summary' => $summary,
			'days'    => $days,
			'heatmap' => $heatmap,
			'tables'  => $tables,
			'goals'   => $goals,
		);
	}

	// The most common query strings for one page in the date range, for the Pages breakdown.
	public static function page_queries( \WP_REST_Request $request ) {
		global $wpdb;

		list( , , $start, $end ) = Rest::date_range( $request );

		return $wpdb->get_results( $wpdb->prepare(
			"SELECT IF(query = '', '(no query)', query) AS label, COUNT(*) AS count
			FROM " . Plugin::table( 'pageviews' ) . '
			WHERE path = %s AND created_at >= %s AND created_at < %s
			GROUP BY label
			ORDER BY count DESC
			LIMIT 20',
			(string) $request['path'], $start, $end
		), ARRAY_A );
	}
}