<?php
/**
 * The local queue, which is also the event log.
 *
 * Queue first, send second. A conversion that only exists in a variable is
 * lost the moment the request ends, and the request this runs in is somebody's
 * checkout.
 *
 * @package Affiliatant
 */

defined( 'ABSPATH' ) || exit;

/**
 * One table: the retry queue and the record of everything that went out.
 */
class Affiliatant_Queue {
	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- this is a
	// queue in our own table. There is no WordPress API for it and no post
	// type to go through.
	// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- a queue is
	// read in order to be drained. A cached read hands the same row to two
	// workers, or hides a row that has just been added.
	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- the
	// only thing interpolated anywhere in this file is {$table}, which is
	// self::table(), which is $wpdb->prefix and a literal. Every VALUE goes
	// through $wpdb->prepare.
	// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same
	// reason, and this is the sniff the WordPress.org checker adds on top.
	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- enqueue() builds
	// its statement with $wpdb->prepare and passes the RESULT to
	// $wpdb->query, because $wpdb->insert() cannot express INSERT IGNORE.
	// The sniff cannot follow a prepared string across an assignment. All
	// seven values in it are bound.
	//
	// These are at the top of the class rather than on each call because
	// phpcs:ignore covers only the line after it, and most of these queries
	// are multi-line, so twelve ignores sat one line above a violation they
	// did not cover and Plugin Check reported all thirteen anyway.
	//
	// Switching the sniffs off here would hide a REAL interpolation added
	// later, so WooQueueSqlTest asserts the thing the sniff was trying to:
	// nothing but {$table} is ever interpolated into a query in this file.

	const SCHEMA_VERSION = '1';
	const SCHEMA_OPTION  = 'affiliatant_woo_schema';

	/** Sent rows older than this are pruned, in seconds. Thirty days. */
	const PRUNE_AFTER = 2592000;

	/**
	 * Table name with the site's prefix.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;

		return $wpdb->prefix . 'affiliatant_queue';
	}

	/**
	 * Create or update the table.
	 *
	 * dbDelta() rather than a raw CREATE, because it is the core function for
	 * this and it is idempotent. It is fussy about formatting: two spaces
	 * after PRIMARY KEY, lower case types, one field per line.
	 *
	 * @return void
	 */
	public static function install() {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$table   = self::table();
		$collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			event_id varchar(64) NOT NULL DEFAULT '',
			action varchar(16) NOT NULL DEFAULT 'conversion',
			order_id bigint(20) unsigned NOT NULL DEFAULT 0,
			payload longtext NULL,
			status varchar(12) NOT NULL DEFAULT 'queued',
			attempts smallint(5) unsigned NOT NULL DEFAULT 0,
			next_attempt bigint(20) unsigned NOT NULL DEFAULT 0,
			amount_minor bigint(20) NOT NULL DEFAULT 0,
			last_error varchar(255) NOT NULL DEFAULT '',
			remote_id varchar(64) NOT NULL DEFAULT '',
			created bigint(20) unsigned NOT NULL DEFAULT 0,
			updated bigint(20) unsigned NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY event_id (event_id),
			KEY due (status,next_attempt),
			KEY order_id (order_id)
		) {$collate};";

		dbDelta( $sql );

		update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION, true );
	}

	/**
	 * Make sure the table is there before a storefront hook writes to it.
	 *
	 * A merchant who copied a new version of the plugin over the old one
	 * never ran the activation hook, so this cannot assume it ran.
	 *
	 * @return void
	 */
	public static function ensure() {
		if ( (string) get_option( self::SCHEMA_OPTION ) === self::SCHEMA_VERSION ) {
			return;
		}
		self::install();
	}

	/**
	 * Record an event, exactly once.
	 *
	 * The unique key on event_id is what makes the whole plugin safe to hook
	 * from four places at once: the status transition, the dynamic status
	 * hook, payment_complete and the subscription renewal hook all compute
	 * the same event id for the same thing, and only the first one through
	 * writes a row.
	 *
	 * @param string $action   conversion or reverse.
	 * @param string $event_id Deterministic event id.
	 * @param int    $order_id WooCommerce order id.
	 * @param array  $payload  What to send.
	 * @return int|false Row id, or false when it is already recorded.
	 */
	public static function enqueue( $action, $event_id, $order_id, array $payload ) {
		global $wpdb;

		self::ensure();

		$now   = time();
		$table = self::table();

		// INSERT IGNORE on the unique event_id, so two hooks racing inside the
		// same request cannot both write. $wpdb->insert() cannot express
		// IGNORE, so this is a prepared query, with every value bound.
		$sql = $wpdb->prepare(
			"INSERT IGNORE INTO {$table}
				(event_id, action, order_id, payload, status, attempts, next_attempt, created, updated)
			VALUES (%s, %s, %d, %s, 'queued', 0, %d, %d, %d)",
			(string) $event_id,
			(string) $action,
			(int) $order_id,
			wp_json_encode( $payload ),
			$now,
			$now,
			$now
		);

		$wpdb->query( $sql );

		if ( ! $wpdb->insert_id ) {
			return false;
		}

		return (int) $wpdb->insert_id;
	}

	/**
	 * One row by id.
	 *
	 * @param int $id Row id.
	 * @return array|null
	 */
	public static function row( $id ) {
		global $wpdb;

		$table = self::table();

		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", (int) $id ), ARRAY_A );

		return is_array( $row ) ? $row : null;
	}

	/**
	 * Rows that are due.
	 *
	 * @param int $limit How many.
	 * @return array<int,array>
	 */
	public static function due( $limit = 20 ) {
		global $wpdb;

		self::ensure();
		$table = self::table();

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE status = 'queued' AND next_attempt <= %d ORDER BY next_attempt ASC, id ASC LIMIT %d",
				time(),
				(int) $limit
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Mark a row sent.
	 *
	 * @param int    $id           Row id.
	 * @param string $remote_id    Affiliatant's own id for the thing created.
	 * @param int    $amount_minor Commission minor units reversed, 0 otherwise.
	 * @return void
	 */
	public static function mark_sent( $id, $remote_id, $amount_minor = 0 ) {
		global $wpdb;

		$row = self::row( $id );

		$wpdb->update(
			self::table(),
			array(
				'status'       => 'sent',
				'attempts'     => $row ? (int) $row['attempts'] + 1 : 1,
				'last_error'   => '',
				'remote_id'    => substr( (string) $remote_id, 0, 64 ),
				'amount_minor' => (int) $amount_minor,
				'updated'      => time(),
			),
			array( 'id' => (int) $id ),
			array( '%s', '%d', '%s', '%s', '%d', '%d' ),
			array( '%d' )
		);
	}

	/**
	 * Apply the retry ladder to a row that failed.
	 *
	 * @param array  $row       The row.
	 * @param string $message   Error text.
	 * @param bool   $retryable Worth trying again.
	 * @return void
	 */
	public static function mark_failure( array $row, $message, $retryable ) {
		global $wpdb;

		$next = Affiliatant_Retry::after_failure( (int) $row['attempts'], (bool) $retryable, time() );

		$wpdb->update(
			self::table(),
			array(
				'status'       => $next['status'],
				'attempts'     => $next['attempts'],
				'next_attempt' => $next['next_attempt'],
				'last_error'   => substr( (string) $message, 0, 255 ),
				'updated'      => time(),
			),
			array( 'id' => (int) $row['id'] ),
			array( '%s', '%d', '%d', '%s', '%d' ),
			array( '%d' )
		);
	}

	/**
	 * Commission already reversed for this order, in minor units.
	 *
	 * Reads only rows this plugin actually SENT, which is what makes a second
	 * partial refund claw back the difference rather than the whole
	 * proportion over again.
	 *
	 * @param int $order_id Order id.
	 * @return int
	 */
	public static function reversed_so_far( $order_id ) {
		global $wpdb;

		$table = self::table();

		$sum = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT SUM(amount_minor) FROM {$table} WHERE order_id = %d AND action = 'reverse' AND status = 'sent'",
				(int) $order_id
			)
		);

		return null === $sum ? 0 : (int) $sum;
	}

	/**
	 * Has a conversion for this order ever been recorded?
	 *
	 * A queued row counts as well as a sent one: the conversion is on its way
	 * and the reversal behind it will find it when the queue drains in order.
	 *
	 * @param int $order_id Order id.
	 * @return bool
	 */
	public static function has_conversion( $order_id ) {
		global $wpdb;

		self::ensure();
		$table = self::table();

		$found = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$table} WHERE order_id = %d AND action = 'conversion' AND status <> 'failed' LIMIT 1",
				(int) $order_id
			)
		);

		return null !== $found;
	}

	/**
	 * Counts by status, for the admin panel.
	 *
	 * @return array<string,int>
	 */
	public static function counts() {
		global $wpdb;

		self::ensure();
		$table  = self::table();
		$counts = array(
			'queued' => 0,
			'sent'   => 0,
			'failed' => 0,
		);

		$rows = $wpdb->get_results( "SELECT status, COUNT(*) AS n FROM {$table} GROUP BY status", ARRAY_A );
		foreach ( (array) $rows as $row ) {
			$counts[ (string) $row['status'] ] = (int) $row['n'];
		}

		return $counts;
	}

	/**
	 * The most recent events, for the status panel.
	 *
	 * @param int $limit How many.
	 * @return array<int,array>
	 */
	public static function recent( $limit = 20 ) {
		global $wpdb;

		self::ensure();
		$table = self::table();

		$rows = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$table} ORDER BY id DESC LIMIT %d", (int) $limit ),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Delete every failed row. The admin screen's "clear failed" button.
	 *
	 * @return int Rows deleted.
	 */
	public static function clear_failed() {
		global $wpdb;

		return (int) $wpdb->delete( self::table(), array( 'status' => 'failed' ), array( '%s' ) );
	}

	/**
	 * Drop sent rows past the retention window.
	 *
	 * Only rows with status 'sent'. A queued or failed row is never pruned,
	 * however old: it is work still outstanding.
	 *
	 * @return void
	 */
	public static function prune() {
		global $wpdb;

		$table = self::table();

		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$table} WHERE status = 'sent' AND updated < %d LIMIT 500",
				time() - self::PRUNE_AFTER
			)
		);
	}
}