<?php
/**
 * Repository for the delivery log table.
 *
 * @package TextTango
 */

declare( strict_types=1 );

namespace TextTango\Messages;

use TextTango\Install\Schema;
use WP_Error;

defined( 'ABSPATH' ) || exit;

/**
 * Reads and writes rows in `texttango_messages`.
 *
 * Every outbound message passes through {@see MessageLog::claim()} first. The
 * insert is the idempotency check: `dedupe_key` is UNIQUE, so a duplicate insert
 * fails at the database and the caller skips the send. Because the TextTango API
 * exposes no idempotency key (spec §2), this is the only guard against a retried
 * hook double-sending — and double-charging.
 *
 * All timestamps are stored in UTC.
 */
final class MessageLog {

	public const STATUS_QUEUED    = 'queued';
	public const STATUS_SENDING   = 'sending';
	public const STATUS_SENT      = 'sent';
	public const STATUS_DELIVERED = 'delivered';
	public const STATUS_FAILED    = 'failed';
	public const STATUS_SKIPPED   = 'skipped';
	public const STATUS_SANDBOX   = 'sandbox';

	/**
	 * A send whose HTTP request timed out. The platform may or may not have
	 * accepted it, and we hold no UUID, so it is never retried automatically —
	 * it is surfaced for a human instead.
	 */
	public const STATUS_TIMEOUT = 'send_timeout';

	/**
	 * Statuses that will never change again on their own.
	 *
	 * @var string[]
	 */
	public const TERMINAL_STATUSES = array(
		self::STATUS_DELIVERED,
		self::STATUS_FAILED,
		self::STATUS_SKIPPED,
		self::STATUS_SANDBOX,
	);

	/**
	 * Longest dedupe key the column accepts.
	 */
	private const DEDUPE_KEY_MAX = 191;

	/**
	 * Build a dedupe key from its parts, hashing if it would overflow the column.
	 *
	 * @param string ...$parts Key components, e.g. 'order_status', '123', 'processing'.
	 */
	public static function build_dedupe_key( string ...$parts ): string {
		$key = implode( ':', array_filter( $parts, static fn( $part ) => '' !== $part ) );

		if ( strlen( $key ) > self::DEDUPE_KEY_MAX ) {
			$key = 'h:' . hash( 'sha256', $key );
		}

		return $key;
	}

	/**
	 * A dedupe key for sends that have no natural identity (test sends, manual
	 * resends) and must therefore never collide with anything.
	 *
	 * @param string $event Event name.
	 */
	public static function unique_dedupe_key( string $event ): string {
		return self::build_dedupe_key( $event, (string) time(), wp_generate_password( 12, false ) );
	}

	/**
	 * Reserve a row for an outbound message.
	 *
	 * @param array<string, mixed> $data Row values; `dedupe_key` is required.
	 * @return int|WP_Error Row id, or a `texttango_duplicate` error when this message
	 *                      was already claimed, or `texttango_db_error` on failure.
	 */
	public static function claim( array $data ) {
		global $wpdb;

		if ( empty( $data['dedupe_key'] ) ) {
			return new WP_Error(
				'texttango_missing_dedupe_key',
				__( 'A message cannot be logged without a dedupe key.', 'texttango' )
			);
		}

		$now = self::now();

		list( $row, $formats ) = self::prepare_write(
			array_merge(
				array(
					'status'     => self::STATUS_QUEUED,
					'created_at' => $now,
					'updated_at' => $now,
				),
				$data
			)
		);

		$suppressed = $wpdb->suppress_errors( true );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; the unique-index collision is the dedupe check itself.
		$inserted = $wpdb->insert( Schema::table_name(), $row, $formats );
		$error    = $wpdb->last_error;
		$wpdb->suppress_errors( $suppressed );

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

		if ( false !== stripos( $error, 'duplicate entry' ) ) {
			return new WP_Error(
				'texttango_duplicate',
				__( 'This message was already sent or queued.', 'texttango' ),
				array( 'dedupe_key' => $data['dedupe_key'] )
			);
		}

		return new WP_Error( 'texttango_db_error', $error );
	}

	/**
	 * Update a row, always refreshing `updated_at`.
	 *
	 * @param int                  $id   Row id.
	 * @param array<string, mixed> $data Columns to change.
	 */
	public static function update( int $id, array $data ): bool {
		global $wpdb;

		list( $row, $formats ) = self::prepare_write( array_merge( $data, array( 'updated_at' => self::now() ) ) );

		if ( empty( $row ) ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		$updated = $wpdb->update( Schema::table_name(), $row, array( 'id' => $id ), $formats, array( '%d' ) );

		return false !== $updated;
	}

	/**
	 * Fetch one row by id.
	 *
	 * @param int $id Row id.
	 * @return array<string, mixed>|null
	 */
	public static function find( int $id ): ?array {
		global $wpdb;

		$table = Schema::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table; the name comes from $wpdb->prefix.
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ), ARRAY_A );

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

	/**
	 * Fetch one row by the TextTango message UUID — the lookup a delivery webhook does.
	 *
	 * @param string $uuid TextTango message UUID.
	 * @return array<string, mixed>|null
	 */
	public static function find_by_uuid( string $uuid ): ?array {
		global $wpdb;

		$table = Schema::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table; the name comes from $wpdb->prefix.
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE texttango_message_uuid = %s", $uuid ), ARRAY_A );

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

	/**
	 * Fetch one row by dedupe key.
	 *
	 * @param string $key Dedupe key.
	 * @return array<string, mixed>|null
	 */
	public static function find_by_dedupe_key( string $key ): ?array {
		global $wpdb;

		$table = Schema::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table; the name comes from $wpdb->prefix.
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE dedupe_key = %s", $key ), ARRAY_A );

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

	/**
	 * Rows that were handed to the API but never reached a terminal status —
	 * the input to the missed-webhook self-heal poller (spec §19).
	 *
	 * @param int $older_than_minutes Only rows untouched for this long.
	 * @param int $limit              Maximum rows to return.
	 * @return array<int, array<string, mixed>>
	 */
	public static function pending_reconciliation( int $older_than_minutes = 15, int $limit = 50 ): array {
		global $wpdb;

		$table  = Schema::table_name();
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $older_than_minutes * MINUTE_IN_SECONDS ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names cannot be parameterised; built from $wpdb->prefix.
				"SELECT * FROM {$table}
				WHERE texttango_message_uuid IS NOT NULL
				AND texttango_message_uuid != ''
				AND status IN ( %s, %s )
				AND updated_at < %s
				ORDER BY updated_at ASC
				LIMIT %d",
				self::STATUS_SENT,
				self::STATUS_SENDING,
				$cutoff,
				$limit
			),
			ARRAY_A
		);

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

	/**
	 * Most recent rows, for the Logs screen.
	 *
	 * @param int $limit Maximum rows.
	 * @return array<int, array<string, mixed>>
	 */
	public static function recent( int $limit = 20 ): array {
		global $wpdb;

		$table = Schema::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table; the name comes from $wpdb->prefix.
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} ORDER BY id DESC LIMIT %d", $limit ), ARRAY_A );

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

	/**
	 * One page of the log, filtered.
	 *
	 * Ordered by `created_at` rather than `id`. Both composite indexes are
	 * `(column, created_at)`, so ordering by the second column is what lets a
	 * filtered query read the rows straight off the index instead of sorting
	 * them; `id` follows as a tiebreaker, because two rows written in the same
	 * second would otherwise have no defined order between them and paging
	 * could show one twice while never showing the other.
	 *
	 * @param LogFilter $filter What the merchant asked for.
	 * @return array<int, array<string, mixed>>
	 */
	public static function search( LogFilter $filter ): array {
		global $wpdb;

		$table = Schema::table_name();

		list( $where, $params ) = $filter->where();

		$params[] = LogFilter::PER_PAGE;
		$params[] = $filter->offset();

		/*
		 * The clause is assembled before `prepare()` sees it because the number
		 * of conditions varies. Every value in it is a `%s` bound from $params
		 * — LogFilter never puts one in the string — but no static analyser can
		 * see that from here, so both the WordPress sniffs and Plugin Check's
		 * own are silenced by name rather than with a blanket ignore.
		 */
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix; $where holds literals only.
		$sql = "SELECT * FROM {$table} {$where} ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; prepared on the line above.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A );

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

	/**
	 * How many rows match a filter.
	 *
	 * @param LogFilter $filter What the merchant asked for.
	 */
	public static function count_matching( LogFilter $filter ): int {
		global $wpdb;

		$table = Schema::table_name();

		list( $where, $params ) = $filter->where();

		if ( empty( $params ) ) {
			// An unfiltered count is the whole table, and prepare() with no
			// values to bind is an error rather than a no-op.
			return self::count();
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix; $where holds literals only.
		$sql = "SELECT COUNT(*) FROM {$table} {$where}";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; prepared on the line above.
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Every event name that appears in the log, for the filter's list.
	 *
	 * Read from the log rather than from `Events::all()`, which holds only the
	 * WooCommerce notifications — sign-in codes, campaigns, password resets and
	 * admin alerts all land here too, and a filter that cannot name half of what
	 * it is filtering is worse than none. Cheap since `event_created` covers it.
	 *
	 * @return string[]
	 */
	public static function events_seen(): array {
		global $wpdb;

		$table = Schema::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table; the name comes from $wpdb->prefix.
		$events = $wpdb->get_col( "SELECT DISTINCT event FROM {$table} WHERE event != '' ORDER BY event ASC" );

		return is_array( $events ) ? array_map( 'strval', $events ) : array();
	}

	/**
	 * How many rows the log holds.
	 *
	 * @return int
	 */
	public static function count(): int {
		global $wpdb;

		$table = Schema::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table; the name comes from $wpdb->prefix.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
	}

	/**
	 * Current UTC timestamp in MySQL format.
	 */
	private static function now(): string {
		return gmdate( 'Y-m-d H:i:s' );
	}

	/**
	 * Pair a row with its `$wpdb` format specifiers.
	 *
	 * Both halves are derived from the same `array_keys()` call, and that is the
	 * point. `$wpdb->insert()` matches formats to values **positionally**, so a
	 * row ordered by the caller and a format list ordered by the column map will
	 * silently apply the wrong specifier to every column past the first
	 * divergence — writing `dedupe_key` as `%d`, for instance, which stores every
	 * key as `0` and turns the idempotency guard into a block on all sends after
	 * the first. Building them together makes that impossible.
	 *
	 * Unknown keys are dropped, so a caller passing extra context cannot break
	 * the query.
	 *
	 * @param array<string, mixed> $data Candidate values.
	 * @return array{0: array<string, mixed>, 1: string[]} Row and matching formats.
	 */
	public static function prepare_write( array $data ): array {
		$columns = self::column_formats();
		$row     = array_intersect_key( $data, $columns );

		$formats = array_map(
			static fn( string $column ): string => $columns[ $column ],
			array_keys( $row )
		);

		return array( $row, $formats );
	}

	/**
	 * Column name to `$wpdb` format specifier.
	 *
	 * @return array<string, string>
	 */
	private static function column_formats(): array {
		return array(
			'dedupe_key'             => '%s',
			'event'                  => '%s',
			'wp_user_id'             => '%d',
			'order_id'               => '%d',
			'recipient'              => '%s',
			'sender_id'              => '%s',
			'body'                   => '%s',
			'texttango_message_uuid' => '%s',
			'status'                 => '%s',
			'error_code'             => '%s',
			'error_reason'           => '%s',
			'cost'                   => '%f',
			'attempts'               => '%d',
			'sandbox'                => '%d',
			'created_at'             => '%s',
			'updated_at'             => '%s',
		);
	}
}
