<?php
/**
 * The site's activity log: every event the plugin records, kept on the site.
 *
 * It's what the Activity screen lists and what reports are built from, whether or not the
 * site is connected to Monthwrap. When it is connected, events not yet sent are sent from
 * here (sent_at stays empty until Monthwrap accepts them).
 *
 * Kept in the plugin's own table, so two requests recording at once can't overwrite each
 * other. Each event has a key; recording the same key twice keeps one, which is how a page
 * saved ten times in a day becomes one "updated" entry.
 *
 * @package Monthwrap
 */

defined( 'ABSPATH' ) || exit;

// The queries below run on the plugin's own table. Caching them would be wrong: the log
// changes on every event and every send.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

/**
 * The activity log table.
 */
class Monthwrap_Store {

	const DB_VERSION = '2';

	/** Events older than this many months are deleted. */
	const KEEP_MONTHS = 24;

	/** The oldest events are deleted beyond this many, so a busy site stays small. */
	const MAX_ROWS = 50000;

	/** An event that fails this many sends is no longer sent (it stays in the log). */
	const MAX_ATTEMPTS = 50;

	/**
	 * The table's name, with this site's prefix.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'monthwrap_activity';
	}

	/**
	 * Creates or updates the table. Safe to run again.
	 */
	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,
				event_key varchar(191) NOT NULL,
				type varchar(40) NOT NULL,
				occurred_at datetime NOT NULL,
				payload longtext NOT NULL,
				sent_at datetime DEFAULT NULL,
				attempts smallint(5) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				UNIQUE KEY event_key (event_key),
				KEY occurred_at (occurred_at),
				KEY type_occurred (type, occurred_at)
			) {$charset};"
		);
		update_option( 'monthwrap_db_version', self::DB_VERSION, false );
	}

	/**
	 * Creates the table after an update that changed it.
	 */
	public static function maybe_upgrade() {
		if ( get_option( 'monthwrap_db_version' ) !== self::DB_VERSION ) {
			self::install();
		}
	}

	/**
	 * Records an event. Its `id` is the key: the same id again is ignored.
	 *
	 * @param array $event An event, with `id`, `type` and `occurred_at` (ISO 8601).
	 */
	public static function record( array $event ) {
		global $wpdb;
		$inserted = $wpdb->query(
			$wpdb->prepare(
				'INSERT IGNORE INTO %i (event_key, type, occurred_at, payload) VALUES (%s, %s, %s, %s)',
				self::table(),
				substr( (string) $event['id'], 0, 191 ),
				substr( (string) $event['type'], 0, 40 ),
				gmdate( 'Y-m-d H:i:s', strtotime( (string) ( $event['occurred_at'] ?? 'now' ) ) ),
				wp_json_encode( $event )
			)
		);
		if ( $inserted ) {
			Monthwrap_Sender::send_soon();
		}
	}

	/**
	 * Events waiting to be sent: not sent yet, from $since on, oldest first.
	 *
	 * @param int    $limit How many.
	 * @param string $since UTC datetime; older events are never sent.
	 * @return array<int, array> Events by row id.
	 */
	public static function pending( $limit, $since ) {
		global $wpdb;
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT id, payload FROM %i WHERE sent_at IS NULL AND attempts < %d AND occurred_at >= %s ORDER BY id LIMIT %d',
				self::table(),
				self::MAX_ATTEMPTS,
				$since,
				$limit
			)
		);
		return self::decode( $rows );
	}

	/**
	 * How many events are waiting to be sent.
	 *
	 * @param string $since UTC datetime, as for pending().
	 * @return int
	 */
	public static function count_pending( $since ) {
		global $wpdb;
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE sent_at IS NULL AND attempts < %d AND occurred_at >= %s',
				self::table(),
				self::MAX_ATTEMPTS,
				$since
			)
		);
	}

	/**
	 * Events between two times, newest first, optionally of some types only.
	 *
	 * @param string   $from   UTC datetime, inclusive.
	 * @param string   $to     UTC datetime, exclusive.
	 * @param string[] $types  Event types, or empty for all.
	 * @param int      $limit  How many at most.
	 * @param int      $offset How many to skip.
	 * @return array[] Each with `event`, `sent_at` and `failed`.
	 */
	public static function between( $from, $to, array $types = array(), $limit = 1000, $offset = 0 ) {
		global $wpdb;
		$types = implode( ',', $types );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT payload, sent_at, attempts FROM %i WHERE occurred_at >= %s AND occurred_at < %s AND ( %s = '' OR FIND_IN_SET( type, %s ) ) ORDER BY occurred_at DESC, id DESC LIMIT %d OFFSET %d",
				self::table(),
				$from,
				$to,
				$types,
				$types,
				$limit,
				$offset
			)
		);
		$list  = array();
		foreach ( (array) $rows as $row ) {
			$event = json_decode( $row->payload, true );
			if ( is_array( $event ) ) {
				$list[] = array(
					'event'   => $event,
					'sent_at' => $row->sent_at,
					'failed'  => (int) $row->attempts >= self::MAX_ATTEMPTS,
				);
			}
		}
		return $list;
	}

	/**
	 * How many events between two times, optionally of some types only.
	 *
	 * @param string   $from  UTC datetime, inclusive.
	 * @param string   $to    UTC datetime, exclusive.
	 * @param string[] $types Event types, or empty for all.
	 * @return int
	 */
	public static function count_between( $from, $to, array $types = array() ) {
		global $wpdb;
		$types = implode( ',', $types );
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE occurred_at >= %s AND occurred_at < %s AND ( %s = '' OR FIND_IN_SET( type, %s ) )",
				self::table(),
				$from,
				$to,
				$types,
				$types
			)
		);
	}

	/**
	 * When the oldest event in the log happened.
	 *
	 * @return int Unix time, or 0 if the log is empty.
	 */
	public static function first_at() {
		global $wpdb;
		$first = $wpdb->get_var( $wpdb->prepare( 'SELECT MIN(occurred_at) FROM %i', self::table() ) );
		return $first ? (int) strtotime( $first . ' UTC' ) : 0;
	}

	/**
	 * Marks events as accepted by Monthwrap.
	 *
	 * @param int[] $ids Row ids.
	 */
	public static function mark_sent( array $ids ) {
		global $wpdb;
		if ( ! $ids ) {
			return;
		}
		$ids          = array_map( 'intval', $ids );
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE %i SET sent_at = UTC_TIMESTAMP() WHERE id IN ($placeholders)", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- only %d placeholders.
				array_merge( array( self::table() ), $ids )
			)
		);
	}

	/**
	 * Counts a failed send against these events.
	 *
	 * @param int[] $ids Row ids.
	 */
	public static function failed( array $ids ) {
		global $wpdb;
		if ( ! $ids ) {
			return;
		}
		$ids          = array_map( 'intval', $ids );
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE %i SET attempts = attempts + 1 WHERE id IN ($placeholders)", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- only %d placeholders.
				array_merge( array( self::table() ), $ids )
			)
		);
	}

	/**
	 * Stops sending events Monthwrap refused as malformed. They stay in the log.
	 *
	 * @param int[] $ids Row ids.
	 */
	public static function refused( array $ids ) {
		global $wpdb;
		foreach ( array_map( 'intval', $ids ) as $id ) { // Rare: only malformed events.
			$wpdb->update( self::table(), array( 'attempts' => self::MAX_ATTEMPTS ), array( 'id' => $id ), array( '%d' ), array( '%d' ) );
		}
	}

	/**
	 * Deletes events older than KEEP_MONTHS, and the oldest beyond MAX_ROWS.
	 */
	public static function prune() {
		global $wpdb;
		$table = self::table();
		$wpdb->query(
			$wpdb->prepare(
				'DELETE FROM %i WHERE occurred_at < %s',
				$table,
				gmdate( 'Y-m-d H:i:s', strtotime( '-' . self::KEEP_MONTHS . ' months' ) )
			)
		);
		$over = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) ) - self::MAX_ROWS;
		if ( $over > 0 ) {
			$wpdb->query( $wpdb->prepare( 'DELETE FROM %i ORDER BY id LIMIT %d', $table, $over ) );
		}
	}

	/**
	 * Removes the table. Used when the plugin is deleted.
	 */
	public static function drop() {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- removing the plugin's own table on uninstall.
		delete_option( 'monthwrap_db_version' );
	}

	/**
	 * Decodes rows into events by row id.
	 *
	 * @param array $rows Rows with id and payload.
	 * @return array<int, array>
	 */
	private static function decode( $rows ) {
		$events = array();
		foreach ( (array) $rows as $row ) {
			$event = json_decode( $row->payload, true );
			if ( is_array( $event ) ) {
				$events[ (int) $row->id ] = $event;
			}
		}
		return $events;
	}
}