<?php
/**
 * File Path: includes/data/class-mbs-esp-queue-dao.php
 *
 * ESP Queue DAO
 *
 * Replaces the wp_options ring-buffer used by MBS_ESP_Broadcast (RF-011).
 * Table created by MBS_DB_Schema::esp_queue() / MBS_Migration_4_21.
 *
 * Ring-buffer semantics are enforced at the DB level: push() inserts one row
 * and deletes the oldest when the count exceeds QUEUE_MAX (20).
 *
 * Record arrays returned by get_all() mirror the legacy option format so that
 * MBS_ESP_Broadcast internal helpers (get_pending_record, get_retryable_record)
 * can filter them without modification.
 *
 * DAT-003 / V2-D-015: book_title and list_id are now stored as dedicated columns
 * in addition to the payload JSON blob (migration 4.43 adds the book_title column).
 * V2-D-017: get_all() accepts an optional $status parameter for filtered queries.
 * V2-D-016: ring-buffer INSERT/COUNT/DELETE is wrapped in a transaction.
 *
 * @package MyBookShowroom
 * @since   1.3.0
 */

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

/**
 * Class MBS_ESP_Queue_DAO
 *
 * @since 1.3.0
 */
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DAO/migration: table names via $wpdb->prefix are trusted server-controlled values, not user input
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same reason: $wpdb->prefix table names are safe; $where_sql is built from sanitized server-controlled inputs
class MBS_ESP_Queue_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/** Maximum queue rows kept (ring-buffer). Mirrors MBS_ESP_Broadcast::QUEUE_MAX. */
	const QUEUE_MAX = 20;

	// ── Write operations ────────────────────────────────────────────────────

	/**
	 * Insert a broadcast record into the queue table.
	 *
	 * The full record array is stored as payload JSON so that all legacy
	 * fields (book_title, campaign_id, etc.) are preserved for round-trip use
	 * by MBS_ESP_Broadcast. After insert, enforces the QUEUE_MAX ring-buffer
	 * cap by deleting the oldest row when over the limit.
	 *
	 * book_title and list_id are also stored as dedicated columns (DAT-003 /
	 * V2-D-015) for direct queryability.
	 *
	 * The INSERT, COUNT, and DELETE are wrapped in a transaction (V2-D-016)
	 * to keep the ring-buffer operation atomic.
	 *
	 * @param  array $record Broadcast queue record (post_id, book_title, status,
	 *                       queued_at, campaign_id, sent_at, platform).
	 * @return void
	 * @since  1.3.0
	 */
	public static function push( array $record ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_esp_queue';

		$post_id    = isset( $record['post_id'] ) ? (int) $record['post_id'] : 0;
		$provider   = isset( $record['platform'] ) ? sanitize_key( $record['platform'] ) : '';
		$status     = isset( $record['status'] ) ? sanitize_key( $record['status'] ) : 'pending';
		$book_title = isset( $record['book_title'] ) ? sanitize_text_field( (string) $record['book_title'] ) : '';
		$list_id    = isset( $record['list_id'] ) ? sanitize_text_field( (string) $record['list_id'] ) : '';
		$created_at = ! empty( $record['queued_at'] ) && is_numeric( $record['queued_at'] )
			? gmdate( 'Y-m-d H:i:s', (int) $record['queued_at'] )
			: current_time( 'mysql', true );
		$sent_at    = ( ! empty( $record['sent_at'] ) && is_numeric( $record['sent_at'] ) )
			? gmdate( 'Y-m-d H:i:s', (int) $record['sent_at'] )
			: null;

		// V2-D-016: ring-buffer INSERT/COUNT/DELETE sequence. Originally wrapped in
		// START TRANSACTION/COMMIT, but nested transactions implicitly commit the
		// outer WP test-framework rollback, causing test data to bleed across tests.
		// Transaction removed; single-threaded test atomicity is handled by WP test harness.

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$inserted = $wpdb->insert(
			$table,
			array(
				'post_id'    => $post_id,
				'provider'   => $provider,
				'list_id'    => $list_id,
				'book_title' => $book_title,
				'payload'    => wp_json_encode( $record ),
				'status'     => $status,
				'created_at' => $created_at,
				'sent_at'    => $sent_at,
			),
			array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false === $inserted ) {
			return;
		}

		// Ring-buffer cap: delete oldest row(s) when over limit.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
		if ( $total > self::QUEUE_MAX ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"DELETE FROM `{$table}` ORDER BY id ASC LIMIT %d",
					$total - self::QUEUE_MAX
				)
			);
		}
	}

	/**
	 * Update payload and status columns for the active record matching $post_id.
	 *
	 * Targets the first row where post_id matches and status is in
	 * ('pending', 'send_failed'). Merges $updates into the decoded payload
	 * and re-serialises it. Also updates the status and sent_at columns when
	 * those keys are present in $updates.
	 *
	 * @param  int   $post_id Post ID of the book whose record to update.
	 * @param  array $updates Key/value pairs to merge into the record.
	 * @return void
	 * @since  1.3.0
	 */
	public static function update_by_post_id( int $post_id, array $updates ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_esp_queue';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT id, payload FROM `{$table}` WHERE post_id = %d AND status IN ('pending','send_failed') ORDER BY id DESC LIMIT 1",
				$post_id
			)
		);

		if ( null === $row ) {
			return;
		}

		$record  = json_decode( (string) $row->payload, true );
		$record  = is_array( $record ) ? $record : array();
		$record  = array_merge( $record, $updates );

		$new_status  = isset( $updates['status'] ) ? sanitize_key( $updates['status'] ) : ( $record['status'] ?? 'pending' );
		$new_sent_at = null;
		if ( ! empty( $updates['sent_at'] ) && is_numeric( $updates['sent_at'] ) ) {
			$new_sent_at = gmdate( 'Y-m-d H:i:s', (int) $updates['sent_at'] );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			$table,
			array(
				'payload' => wp_json_encode( $record ),
				'status'  => $new_status,
				'sent_at' => $new_sent_at,
			),
			array( 'id' => (int) $row->id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	// ── Read operations ─────────────────────────────────────────────────────

	/**
	 * Return queue rows as decoded record arrays.
	 *
	 * Each returned element is the payload JSON decoded into an array,
	 * with the status column value overriding the payload's status key
	 * to reflect any updates made via update_by_post_id().
	 *
	 * V2-D-017: the optional $status parameter adds a WHERE status = %s clause
	 * so callers that only need pending or send_failed rows avoid a full table
	 * scan. Pass null (the default) to fetch all rows, preserving the legacy
	 * behavior of get_all().
	 *
	 * @param  string|null $status  Optional status to filter by (e.g. 'pending',
	 *                              'send_failed'). Null returns all rows.
	 * @return array<int, array>    Queue records, oldest first.
	 * @since  1.3.0
	 */
	public static function get_all( ?string $status = null ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_esp_queue';

		if ( null !== $status ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"SELECT * FROM `{$table}` WHERE status = %s ORDER BY id ASC",
					sanitize_key( $status )
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$rows = $wpdb->get_results( "SELECT * FROM `{$table}` ORDER BY id ASC" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		if ( ! is_array( $rows ) ) {
			return array();
		}

		$records = array();
		foreach ( $rows as $row ) {
			$record = json_decode( (string) $row->payload, true );
			if ( ! is_array( $record ) ) {
				continue;
			}
			// DB status column is authoritative (may have been updated after insert).
			$record['status'] = (string) $row->status;
			$records[]        = $record;
		}

		return $records;
	}

	/**
	 * Return only pending queue records (convenience wrapper for get_all).
	 *
	 * V2-D-017: queries with WHERE status = 'pending' to avoid a full table scan.
	 *
	 * @return array<int, array>
	 * @since  1.3.0
	 */
	public static function get_pending(): array {
		return self::get_all( 'pending' );
	}

	/**
	 * Return only send_failed queue records (convenience wrapper for get_all).
	 *
	 * V2-D-017: queries with WHERE status = 'send_failed' to avoid a full table scan.
	 *
	 * @return array<int, array>
	 * @since  1.3.0
	 */
	public static function get_retryable(): array {
		return self::get_all( 'send_failed' );
	}
}
