<?php
/**
 * Sign-in monitoring figures.
 *
 * @package Rahkar\WP
 */

declare( strict_types = 1 );

namespace Rahkar\WP\Modules\OtpAuth;

use Rahkar\WP\Database\Schema;

defined( 'ABSPATH' ) || exit;

/**
 * Read-only numbers over the sign-in log, for the monitoring screen and the
 * alert rules.
 *
 * Everything is derived from {@see OtpLog}, so there is no second store to keep
 * in step and the figures always match what the log screen lists. Test-mode
 * rows are never counted: a store owner trying the flow on their own number must
 * look neither like traffic nor like a healthy gateway.
 */
final class MonitorStats {

	/**
	 * Send attempts and failed attempts over a recent window.
	 *
	 * @param int    $seconds Window length.
	 * @param string $gateway Only this gateway; empty for all.
	 *
	 * @return array{total: int, failed: int}
	 */
	public static function window( int $seconds, string $gateway = '' ): array {
		global $wpdb;

		[ $where, $args ] = self::sendWhere( time() - $seconds, $gateway );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where is built in sendWhere() from literal fragments; every value is a bound placeholder.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT COUNT(*) AS total, SUM(result IN (%s, %s, %s)) AS failed FROM %i WHERE ' . $where,
				array_merge( OtpLog::FAILED_SENDS, [ Schema::table( 'otp_log' ) ], $args )
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return [
			'total'  => (int) ( $row['total'] ?? 0 ),
			'failed' => (int) ( $row['failed'] ?? 0 ),
		];
	}

	/**
	 * Codes that actually went out over a recent window — what the SMS bill is
	 * made of.
	 *
	 * @param int $seconds Window length.
	 */
	public static function delivered( int $seconds ): int {
		global $wpdb;

		[ $where, $args ] = self::sendWhere( time() - $seconds );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where is built in sendWhere() from literal fragments; every value is a bound placeholder.
		$count = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE ' . $where . ' AND result = %s',
				array_merge( [ Schema::table( 'otp_log' ) ], $args, [ OtpLog::RESULT_OK ] )
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return (int) $count;
	}

	/**
	 * Sign-ins and new accounts over a recent window.
	 *
	 * @param int $seconds Window length.
	 *
	 * @return array{logins: int, signups: int}
	 */
	public static function signIns( int $seconds ): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT event, COUNT(*) AS n FROM %i WHERE event IN (%s, %s, %s) AND result = %s AND created_at >= %s GROUP BY event',
				Schema::table( 'otp_log' ),
				OtpLog::EVENT_LOGIN,
				OtpLog::EVENT_SIGNUP,
				OtpLog::EVENT_PASSWORD,
				OtpLog::RESULT_OK,
				gmdate( 'Y-m-d H:i:s', time() - $seconds )
			),
			ARRAY_A
		);

		$out = [
			'logins'  => 0,
			'signups' => 0,
		];

		foreach ( (array) $rows as $row ) {
			$key          = OtpLog::EVENT_SIGNUP === $row['event'] ? 'signups' : 'logins';
			$out[ $key ] += (int) $row['n'];
		}

		return $out;
	}

	/**
	 * Send attempts in equal slices ending now, oldest first.
	 *
	 * Slices are aligned to their length, so one covers the same clock period on
	 * every page load instead of shifting by a few seconds each time.
	 *
	 * @param int $slices       Number of slices.
	 * @param int $sliceSeconds Length of each.
	 *
	 * @return list<array{from: int, total: int, failed: int}>
	 */
	public static function timeline( int $slices, int $sliceSeconds ): array {
		global $wpdb;

		$end   = (int) ( ceil( time() / $sliceSeconds ) * $sliceSeconds );
		$start = $end - $slices * $sliceSeconds;
		$since = gmdate( 'Y-m-d H:i:s', $start );

		[ $where, $args ] = self::sendWhere( $start );

		// The slot is worked out from the stored UTC text against a UTC start, not
		// through UNIX_TIMESTAMP(), which reads a DATETIME in the connection's time
		// zone and would shift every bar on a server not set to UTC.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where is built in sendWhere() from literal fragments; every value is a bound placeholder.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT FLOOR(TIMESTAMPDIFF(SECOND, %s, created_at) / %d) AS slot, COUNT(*) AS total, SUM(result IN (%s, %s, %s)) AS failed
				FROM %i WHERE ' . $where . ' GROUP BY slot',
				array_merge( [ $since, $sliceSeconds ], OtpLog::FAILED_SENDS, [ Schema::table( 'otp_log' ) ], $args )
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$bySlot = [];

		foreach ( (array) $rows as $row ) {
			$bySlot[ (int) $row['slot'] ] = $row;
		}

		$out = [];

		for ( $i = 0; $i < $slices; $i++ ) {
			$out[] = [
				'from'   => $start + $i * $sliceSeconds,
				'total'  => (int) ( $bySlot[ $i ]['total'] ?? 0 ),
				'failed' => (int) ( $bySlot[ $i ]['failed'] ?? 0 ),
			];
		}

		return $out;
	}

	/**
	 * The latest success and the latest failure on one gateway.
	 *
	 * @param string $gateway Gateway id.
	 *
	 * @return array{ok_at: int, error_at: int, error: string}
	 */
	public static function lastOutcomes( string $gateway ): array {
		global $wpdb;

		$table = Schema::table( 'otp_log' );

		// Alerts count too: a gateway that just delivered an alert is working,
		// and one that refused it is not, whichever kind of message it was.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery
		$ok = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT MAX(created_at) FROM %i WHERE gateway = %s AND event IN (%s, %s) AND result = %s',
				$table,
				$gateway,
				OtpLog::EVENT_SEND,
				OtpLog::EVENT_ALERT,
				OtpLog::RESULT_OK
			)
		);

		$error = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT created_at, message FROM %i WHERE gateway = %s AND event IN (%s, %s) AND result IN (%s, %s) ORDER BY id DESC LIMIT 1',
				$table,
				$gateway,
				OtpLog::EVENT_SEND,
				OtpLog::EVENT_ALERT,
				OtpLog::RESULT_TEMPORARY,
				OtpLog::RESULT_PERMANENT
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return [
			'ok_at'    => is_string( $ok ) ? (int) strtotime( $ok . ' UTC' ) : 0,
			'error_at' => is_array( $error ) ? (int) strtotime( (string) $error['created_at'] . ' UTC' ) : 0,
			'error'    => is_array( $error ) ? (string) $error['message'] : '',
		];
	}

	/**
	 * The most recent alerts sent to administrators.
	 *
	 * @param int $limit Rows.
	 *
	 * @return list<array<string, mixed>>
	 */
	public static function recentAlerts( int $limit ): array {
		return OtpLog::search( [ 'event' => OtpLog::EVENT_ALERT ], $limit );
	}

	/**
	 * WHERE clause selecting real sends since a moment.
	 *
	 * @param int    $since   Unix time.
	 * @param string $gateway Only this gateway; empty for all.
	 *
	 * @return array{0: string, 1: list<string>}
	 */
	private static function sendWhere( int $since, string $gateway = '' ): array {
		$where = 'event = %s AND result <> %s AND created_at >= %s';
		$args  = [ OtpLog::EVENT_SEND, OtpLog::RESULT_TEST, gmdate( 'Y-m-d H:i:s', $since ) ];

		if ( '' !== $gateway ) {
			$where .= ' AND gateway = %s';
			$args[] = $gateway;
		}

		return [ $where, $args ];
	}
}