<?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

// Visitor log, Action log, session timelines, Spy and the filter picker.
class Logs {

	// Turns whitelisted filter params into SQL conditions on sessions (s). $except leaves one filter
	// out, so the filter picker can list every value of the field being picked.
	public static function filters( \WP_REST_Request $request, $except = '' ) {
		global $wpdb;

		$given = function ( $param ) use ( $request, $except ) {
			return $param !== $except && null !== $request[ $param ] && '' !== $request[ $param ];
		};

		$columns = array(
			'country'    => 's.country',
			'device'     => 's.device',
			'browser'    => 's.browser',
			'os'         => 's.os',
			'landing'    => 's.entry_path',
			'utm_source' => 's.utm_source',
		);

		$where = array();
		foreach ( $columns as $param => $column ) {
			if ( $given( $param ) ) {
				$where[] = $wpdb->prepare( "$column = %s", $request[ $param ] );
			}
		}
		if ( $given( 'referrer' ) ) {
			$where[] = $wpdb->prepare( 's.referrer_host = %s', '(direct)' === $request['referrer'] ? '' : $request['referrer'] );
		}
		if ( 'type' !== $except && in_array( $request['type'], array( 'new', 'returning' ), true ) ) {
			$where[] = 'new' === $request['type'] ? 's.is_new = 1' : 's.is_new = 0';
		}
		if ( 'visitor' !== $except && absint( $request['visitor'] ) ) {
			$where[] = $wpdb->prepare( 's.visitor_id = %d', $request['visitor'] );
		}
		// Visits that viewed this page anywhere, not only as the landing page.
		if ( $given( 'page' ) ) {
			$where[] = $wpdb->prepare(
				'EXISTS (SELECT 1 FROM ' . Plugin::table( 'pageviews' ) . ' fp WHERE fp.session_id = s.id AND fp.path = %s)',
				$request['page']
			);
		}
		if ( $given( 'event' ) ) {
			$where[] = $wpdb->prepare(
				'EXISTS (SELECT 1 FROM ' . Plugin::table( 'events' ) . ' fe WHERE fe.session_id = s.id AND fe.name = %s)',
				$request['event']
			);
		}

		return $where ? ' AND ' . implode( ' AND ', $where ) : '';
	}

	// Values the filter picker offers for one field: what exists in the date range under the other
	// active filters, with the number of visits for each, most visits first.
	public static function filter_values( \WP_REST_Request $request ) {
		global $wpdb;

		$field = (string) $request['field'];
		$sql   = array(
			// field => array( value expression, extra join, count expression )
			'page'       => array( 'pv.path', 'JOIN ' . Plugin::table( 'pageviews' ) . ' pv ON pv.session_id = s.id', 'COUNT(DISTINCT s.id)' ),
			'landing'    => array( 's.entry_path', '', 'COUNT(*)' ),
			'country'    => array( 's.country', '', 'COUNT(*)' ),
			'device'     => array( 's.device', '', 'COUNT(*)' ),
			'browser'    => array( 's.browser', '', 'COUNT(*)' ),
			'os'         => array( 's.os', '', 'COUNT(*)' ),
			'utm_source' => array( 's.utm_source', '', 'COUNT(*)' ),
			'referrer'   => array( "IF(s.referrer_host = '', '(direct)', s.referrer_host)", '', 'COUNT(*)' ),
			'event'      => array( 'ev.name', 'JOIN ' . Plugin::table( 'events' ) . ' ev ON ev.session_id = s.id', 'COUNT(DISTINCT s.id)' ),
			'type'       => array( "IF(s.is_new = 1, 'new', 'returning')", '', 'COUNT(*)' ),
		);
		if ( ! isset( $sql[ $field ] ) ) {
			return new \WP_Error( 'footloom_bad_field', 'Unknown filter.', array( 'status' => 400 ) );
		}

		list( $value, $join, $count ) = $sql[ $field ];
		list( , , $start, $end )      = Rest::date_range( $request );

		$where = $wpdb->prepare( 's.started_at >= %s AND s.started_at < %s', $start, $end )
			. self::filters( $request, $field )
			. " AND $value IS NOT NULL AND $value <> ''";

		$search = trim( (string) $request['q'] );
		if ( '' !== $search ) {
			$where .= $wpdb->prepare( " AND $value LIKE %s", '%' . $wpdb->esc_like( $search ) . '%' );
		}

		// Grouped by the expression, not the alias: events has its own "value" column.
		return $wpdb->get_results(
			"SELECT $value AS value, $count AS count
			FROM " . Plugin::table( 'sessions' ) . " s $join
			WHERE $where
			GROUP BY $value
			ORDER BY count DESC, $value
			LIMIT 50",
			ARRAY_A
		);
	}

	// One row per session, newest first, 50 at a time.
	public static function visitors( \WP_REST_Request $request ) {
		global $wpdb;

		list( , , $start, $end ) = Rest::date_range( $request );
		$s       = Plugin::table( 'sessions' );
		$p       = Plugin::table( 'pageviews' );
		$e       = Plugin::table( 'events' );
		$v       = Plugin::table( 'visitors' );
		$offset  = absint( $request['offset'] );
		$filters = self::filters( $request );
		// A visitor is active if one of their visits was seen within the live window and has not left.
		$live_since = gmdate( 'Y-m-d H:i:s', time() - Plugin::LIVE_WINDOW );

		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT s.id, s.visitor_id, LEFT(v.uid, 6) AS code, s.is_new, s.started_at,
				EXISTS (SELECT 1 FROM $s ls WHERE ls.visitor_id = s.visitor_id AND ls.last_seen_at > %s AND ls.left_at IS NULL) AS is_live,
				TIMESTAMPDIFF(SECOND, s.started_at, s.last_seen_at) AS duration,
				s.pageview_count, (SELECT COUNT(*) FROM $e e WHERE e.session_id = s.id) AS event_count,
				s.entry_path, p.query, p.title, s.referrer_host, s.referrer_url,
				s.country, s.device, s.browser, s.os
			FROM $s s
			JOIN $v v ON v.id = s.visitor_id
			LEFT JOIN $p p ON p.id = (SELECT MIN(id) FROM $p WHERE session_id = s.id)
			WHERE s.started_at >= %s AND s.started_at < %s $filters
			ORDER BY s.started_at DESC, s.id DESC
			LIMIT 51 OFFSET %d",
			$live_since, $start, $end, $offset
		), ARRAY_A );

		$result = array(
			'rows'     => array_slice( $rows, 0, 50 ),
			'has_more' => count( $rows ) > 50,
		);

		// When looking at one visitor, include a short profile for the header.
		if ( absint( $request['visitor'] ) ) {
			$result['visitor'] = $wpdb->get_row( $wpdb->prepare(
				"SELECT id, LEFT(uid, 6) AS code, first_seen_at, session_count,
					(SELECT MAX(last_seen_at) FROM $s WHERE visitor_id = v.id) AS last_seen_at
				FROM $v v WHERE id = %d",
				$request['visitor']
			), ARRAY_A );
		}

		return $result;
	}

	// Pageviews and events mixed together, newest first. Shared by the Action log and Spy.
	// $page_where and $event_where limit the pageviews (p) and events (e) read, $outer_where filters
	// on the visit (s). All three must already be prepared.
	public static function action_rows( $page_where, $event_where, $outer_where, $limit, $offset ) {
		global $wpdb;

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

		return $wpdb->get_results( $wpdb->prepare(
			"SELECT a.*, s.visitor_id, LEFT(v.uid, 6) AS code, s.is_new, s.country, s.device,
				EXISTS (SELECT 1 FROM $s ls WHERE ls.visitor_id = s.visitor_id AND ls.last_seen_at > %s AND ls.left_at IS NULL) AS is_live,
				IF(a.type = 'page' AND a.id = (SELECT MIN(id) FROM $p WHERE session_id = a.session_id), s.referrer_host, '') AS referrer_host,
				IF(a.type = 'page' AND a.id = (SELECT MIN(id) FROM $p WHERE session_id = a.session_id), s.referrer_url, '') AS referrer_url
			FROM (
				SELECT 'page' AS type, p.id, p.created_at, p.session_id, p.path, p.query, p.title, p.is_404,
					NULL AS name, NULL AS label, NULL AS value
				FROM $p p WHERE $page_where
				UNION ALL
				SELECT 'event', e.id, e.created_at, e.session_id, p.path, p.query, p.title, 0,
					e.name, e.label, e.value
				FROM $e e JOIN $p p ON p.id = e.pageview_id WHERE $event_where
			) a
			JOIN $s s ON s.id = a.session_id
			JOIN $v v ON v.id = s.visitor_id
			WHERE 1 = 1 $outer_where
			ORDER BY a.created_at DESC, a.type DESC, a.id DESC
			LIMIT %d OFFSET %d",
			// A visitor is active if one of their visits was seen within the live window and has not left.
			gmdate( 'Y-m-d H:i:s', time() - Plugin::LIVE_WINDOW ), $limit, $offset
		), ARRAY_A );
	}

	// Action log: 50 rows at a time for the date range and filters.
	public static function actions( \WP_REST_Request $request ) {
		global $wpdb;

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

		$rows = self::action_rows(
			$wpdb->prepare( 'p.created_at >= %s AND p.created_at < %s', $start, $end ),
			$wpdb->prepare( 'e.created_at >= %s AND e.created_at < %s', $start, $end ),
			self::filters( $request ),
			51,
			absint( $request['offset'] )
		);

		return array(
			'rows'     => array_slice( $rows, 0, 50 ),
			'has_more' => count( $rows ) > 50,
		);
	}

	// Everything that happened in one session, in order, with seconds since the session started.
	public static function session( \WP_REST_Request $request ) {
		global $wpdb;

		$p  = Plugin::table( 'pageviews' );
		$e  = Plugin::table( 'events' );
		$id = absint( $request['id'] );

		return $wpdb->get_results( $wpdb->prepare(
			"SELECT a.*, TIMESTAMPDIFF(SECOND, (SELECT started_at FROM " . Plugin::table( 'sessions' ) . " WHERE id = %d), a.created_at) AS at
			FROM (
				SELECT 'page' AS type, id, created_at, path, query, title, duration, is_404, NULL AS name, NULL AS label, NULL AS value
				FROM $p WHERE session_id = %d
				UNION ALL
				SELECT 'event', e.id, e.created_at, p.path, p.query, p.title, 0, 0, e.name, e.label, e.value
				FROM $e e JOIN $p p ON p.id = e.pageview_id WHERE e.session_id = %d
			) a
			ORDER BY a.created_at, a.type DESC, a.id",
			$id, $id, $id
		), ARRAY_A );
	}

	// Online count for the top bar, plus every action from the current visit of each visitor who is
	// online, for the Spy tab. Polled every 10 seconds while the dashboard is open.
	public static function live() {
		global $wpdb;

		$ids = array_map( 'intval', $wpdb->get_col( $wpdb->prepare(
			'SELECT id FROM ' . Plugin::table( 'sessions' ) . ' WHERE last_seen_at > %s AND left_at IS NULL',
			gmdate( 'Y-m-d H:i:s', time() - Plugin::LIVE_WINDOW )
		) ) );

		$actions = array();
		if ( $ids ) {
			$in      = implode( ',', $ids );
			$actions = self::action_rows( "p.session_id IN ($in)", "e.session_id IN ($in)", '', 100, 0 );
		}

		return array(
			'count'   => count( $ids ),
			'actions' => $actions,
		);
	}
}