<?php
/**
 * Agent visit events: one small table, written once per agent request, pruned after 90 days.
 *
 * Only agents and automation are written. A person's visit writes nothing.
 *
 * @package AgentStamp
 */

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

class AgentStamp_Events {

	const DB_VERSION = '1';
	const KEEP_DAYS  = 90;
	const DAILY_CAP  = 20000;
	const PRUNE_HOOK = 'agent_stamp_prune_events';

	/**
	 * @return string
	 */
	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'agent_stamp_events';
	}

	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$table   = self::table();
		$charset = $wpdb->get_charset_collate();
		dbDelta(
			"CREATE TABLE {$table} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				created_at int(10) unsigned NOT NULL,
				session char(16) NOT NULL,
				agent varchar(64) NOT NULL DEFAULT '',
				kind varchar(16) NOT NULL DEFAULT '',
				stage varchar(16) NOT NULL DEFAULT '',
				reason varchar(32) NOT NULL DEFAULT '',
				message varchar(191) NOT NULL DEFAULT '',
				status smallint(5) unsigned NOT NULL DEFAULT 0,
				value decimal(14,2) NOT NULL DEFAULT 0,
				ref bigint(20) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				KEY created_at (created_at),
				KEY session (session)
			) {$charset};"
		);
		update_option( 'agent_stamp_db_version', self::DB_VERSION, true );
		// Read on every request: exist and autoload, so a store without saved rules does not query for them.
		add_option( AgentStamp_Policy::OPTION, AgentStamp_Policy::defaults(), '', true );
		if ( ! wp_next_scheduled( self::PRUNE_HOOK ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', self::PRUNE_HOOK );
		}
	}

	public static function maybe_install() {
		if ( self::DB_VERSION !== get_option( 'agent_stamp_db_version' ) ) {
			self::install();
		}
	}

	public static function init() {
		add_action( self::PRUNE_HOOK, array( __CLASS__, 'prune' ) );
	}

	/**
	 * @param array<int,array<string,mixed>> $rows
	 */
	public static function insert( array $rows ) {
		global $wpdb;
		if ( empty( $rows ) || self::DB_VERSION !== get_option( 'agent_stamp_db_version' ) ) {
			return;
		}
		$day   = 'agent_stamp_events_' . gmdate( 'Ymd' );
		$count = (int) get_transient( $day );
		if ( $count >= self::DAILY_CAP ) {
			return;
		}
		set_transient( $day, $count + count( $rows ), DAY_IN_SECONDS );

		foreach ( $rows as $row ) {
			$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom agent visit table.
				self::table(),
				array(
					'created_at' => (int) $row['created_at'],
					'session'    => substr( (string) $row['session'], 0, 16 ),
					'agent'      => substr( (string) $row['agent'], 0, 64 ),
					'kind'       => substr( (string) $row['kind'], 0, 16 ),
					'stage'      => substr( (string) $row['stage'], 0, 16 ),
					'reason'     => substr( (string) $row['reason'], 0, 32 ),
					'message'    => function_exists( 'mb_substr' ) ? mb_substr( (string) $row['message'], 0, 191 ) : substr( (string) $row['message'], 0, 191 ),
					'status'     => max( 0, min( 65535, (int) $row['status'] ) ),
					'value'      => (float) $row['value'],
					'ref'        => max( 0, (int) $row['ref'] ),
				),
				array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%f', '%d' )
			);
		}
	}

	/**
	 * Sessions since a time, folded in SQL so a busy store does not load every event into PHP.
	 * The newest sessions are kept when there are more than the cap.
	 *
	 * @param int $since
	 * @param int $cap
	 * @return array<string,array<string,mixed>> Records for AgentStamp_Funnel::summarize_sessions().
	 */
	public static function sessions( $since, $cap = 20000 ) {
		global $wpdb;
		if ( self::DB_VERSION !== get_option( 'agent_stamp_db_version' ) ) {
			return array();
		}
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				"SELECT session,
					MAX(agent) AS agent,
					MAX(CASE WHEN kind = 'signed' THEN agent ELSE '' END) AS signed_agent,
					MAX(kind) AS kind,
					MAX(CASE stage WHEN 'product' THEN 1 WHEN 'cart' THEN 2 WHEN 'checkout' THEN 3 WHEN 'submit' THEN 3 WHEN 'thanks' THEN 4 WHEN 'ordered' THEN 4 ELSE 0 END) AS rank_reached,
					MAX(CASE WHEN stage IN ('cart', 'checkout', 'submit') THEN value ELSE 0 END) AS cart_value,
					MAX(CASE WHEN stage = 'ordered' THEN 1 ELSE 0 END) AS ordered,
					MAX(id) AS last_id
				FROM %i WHERE created_at >= %d
				GROUP BY session ORDER BY last_id DESC LIMIT %d",
				self::table(),
				(int) $since,
				(int) $cap
			),
			ARRAY_A
		);
		if ( ! is_array( $rows ) || empty( $rows ) ) {
			return array();
		}
		$sessions = array();
		$oldest   = PHP_INT_MAX;
		foreach ( $rows as $row ) {
			$signed  = '' !== (string) $row['signed_agent'];
			$session = AgentStamp_Funnel::session_record( $signed ? (string) $row['signed_agent'] : (string) $row['agent'], $signed ? 'signed' : (string) $row['kind'] );
			$session['rank']    = (int) $row['rank_reached'];
			$session['value']   = (float) $row['cart_value'];
			$session['ordered'] = 1 === (int) $row['ordered'];
			$sessions[ (string) $row['session'] ] = $session;
			$oldest = min( $oldest, (int) $row['last_id'] );
		}

		$paid = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				"SELECT session, SUM(order_value) AS revenue, COUNT(*) AS orders FROM (
					SELECT session, ref, MAX(value) AS order_value FROM %i
					WHERE created_at >= %d AND stage = 'ordered' GROUP BY session, ref
				) paid GROUP BY session",
				self::table(),
				(int) $since
			),
			ARRAY_A
		);
		foreach ( (array) $paid as $row ) {
			if ( isset( $sessions[ $row['session'] ] ) ) {
				$sessions[ $row['session'] ]['revenue'] = (float) $row['revenue'];
				$sessions[ $row['session'] ]['orders']  = (int) $row['orders'];
			}
		}

		$reasons = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				"SELECT session, stage, reason, MAX(message) AS message, MAX(id) AS last_id FROM %i
				WHERE created_at >= %d AND reason <> ''
				GROUP BY session, stage, reason ORDER BY last_id DESC LIMIT %d",
				self::table(),
				(int) $since,
				(int) $cap * 4
			),
			ARRAY_A
		);
		// Newest are fetched first so the cap drops old ones; they are applied oldest first so the latest at a step wins.
		foreach ( array_reverse( (array) $reasons ) as $row ) {
			if ( isset( $sessions[ $row['session'] ] ) ) {
				AgentStamp_Funnel::note_reason( $sessions[ $row['session'] ], (string) $row['reason'], (string) $row['message'], AgentStamp_Funnel::rank( (string) $row['stage'] ) );
			}
		}
		return $sessions;
	}

	/**
	 * The latest failures, newest first.
	 *
	 * @param int $since
	 * @param int $limit
	 * @return array<int,array<string,mixed>>
	 */
	public static function failures( $since, $limit = 50 ) {
		global $wpdb;
		if ( self::DB_VERSION !== get_option( 'agent_stamp_db_version' ) ) {
			return array();
		}
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				"SELECT created_at, agent, kind, stage, reason, message, status, value FROM %i WHERE created_at >= %d AND reason <> '' ORDER BY created_at DESC, id DESC LIMIT %d",
				self::table(),
				(int) $since,
				(int) $limit
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Days of agent visits kept. Older visits are deleted.
	 *
	 * @return int
	 */
	public static function keep_days() {
		return self::KEEP_DAYS;
	}

	public static function prune() {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %d', self::table(), time() - self::keep_days() * DAY_IN_SECONDS ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
	}

}