<?php
/**
 * Order risk persistence with atomic current_token swap.
 *
 * @package Kayvandra\MarginBeacon
 */

declare(strict_types=1);

namespace Kayvandra\MarginBeacon\Infrastructure\Persistence;

use Kayvandra\MarginBeacon\Application\MaterialChange\OrderMaterialHasher;
use Kayvandra\MarginBeacon\Application\OrderRisk\OrderRefundLifecycle;
use Kayvandra\MarginBeacon\Application\OrderRisk\OrderRiskEligibility;
use Kayvandra\MarginBeacon\Domain\Risk\PresentationState;

/**
 * Repository for marginbeacon_order_risk tables.
 */
final class OrderRiskRepository {

	/**
	 * Ensure a queued pending row exists.
	 *
	 * @param int    $order_id Order ID.
	 * @param string $revision Revision hash.
	 * @param array  $signal   Signal payload.
	 * @return void
	 */
	public function ensure_queued( $order_id, $revision, array $signal ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_order_risk';

		$current = $this->get_current( $order_id );
		if ( $current && 'completed' === $current['processing_state'] ) {
			$wpdb->update(
				$table,
				array(
					'recalculation_state' => 'queued',
				),
				array( 'id' => (int) $current['id'] ),
				array( '%s' ),
				array( '%d' )
			);
			return;
		}

		if ( $current && in_array( $current['processing_state'], array( 'queued', 'running' ), true ) ) {
			return;
		}

		// Failed / unsupported / other terminal → re-queue in place.
		if ( $current ) {
			$this->mark_queued_for_retry( $order_id, $revision, $signal );
			return;
		}

		$labels = OrderRiskEligibility::evaluation_labels( $signal );

		$this->insert_row(
			array(
				'order_id'                    => $order_id,
				'order_revision'              => $revision,
				'processing_state'            => 'queued',
				'eligibility_state'           => $labels['eligibility_state'],
				'presentation_state'          => PresentationState::PENDING,
				'risk_badge'                  => null,
				'promotion_signal'            => $signal['signal'],
				'promotion_signal_confidence' => $signal['confidence'],
				'evaluation_scope'            => $labels['evaluation_scope'],
				'formula_version'             => AKSMB_FORMULA_VERSION,
				'input_hash'                  => 'pending_' . $revision,
				'current_token'               => 1,
			)
		);
	}

	/**
	 * Mark current snapshot as queued for manual retry / refresh / Pro recalculate.
	 *
	 * Authoritative completed results stay visible; only recalculation_state is set (PRD FR-055).
	 *
	 * @param int    $order_id Order ID.
	 * @param string $revision Revision.
	 * @param array  $signal   Signal.
	 * @return void
	 */
	public function mark_queued_for_retry( $order_id, $revision, array $signal ) {
		global $wpdb;
		$table   = $wpdb->prefix . 'marginbeacon_order_risk';
		$current = $this->get_current( $order_id );
		$labels  = OrderRiskEligibility::evaluation_labels( $signal );
		if ( ! $current ) {
			$this->insert_row(
				array(
					'order_id'                    => $order_id,
					'order_revision'              => $revision,
					'processing_state'            => 'queued',
					'eligibility_state'           => $labels['eligibility_state'],
					'presentation_state'          => PresentationState::PENDING,
					'risk_badge'                  => null,
					'promotion_signal'            => $signal['signal'],
					'promotion_signal_confidence' => $signal['confidence'],
					'evaluation_scope'            => $labels['evaluation_scope'],
					'formula_version'             => AKSMB_FORMULA_VERSION,
					'input_hash'                  => 'pending_' . $revision,
					'current_token'               => 1,
					'recalculation_state'         => 'queued',
				)
			);
			return;
		}

		// Keep completed / unsupported financial presentation; secondary recalc only.
		if ( in_array( (string) $current['processing_state'], array( 'completed', 'unsupported' ), true )
			&& PresentationState::PENDING !== (string) $current['presentation_state']
		) {
			$wpdb->update(
				$table,
				array(
					'order_revision'              => $revision,
					'eligibility_state'           => $labels['eligibility_state'],
					'evaluation_scope'            => $labels['evaluation_scope'],
					'promotion_signal'            => $signal['signal'],
					'promotion_signal_confidence' => $signal['confidence'],
					'recalculation_state'         => 'queued',
					'recalculation_error_code'    => null,
				),
				array( 'id' => (int) $current['id'] ),
				array( '%s', '%s', '%s', '%s', '%s', '%s', '%s' ),
				array( '%d' )
			);
			return;
		}

		$wpdb->update(
			$table,
			array(
				'order_revision'              => $revision,
				'processing_state'            => 'queued',
				'presentation_state'          => PresentationState::PENDING,
				'eligibility_state'           => $labels['eligibility_state'],
				'evaluation_scope'            => $labels['evaluation_scope'],
				'promotion_signal'            => $signal['signal'],
				'promotion_signal_confidence' => $signal['confidence'],
				'recalculation_state'         => 'queued',
				'recalculation_error_code'    => null,
				'short_reason_code'           => null,
			),
			array( 'id' => (int) $current['id'] )
		);
	}

	/**
	 * Update secondary recalculation_state on the current snapshot.
	 *
	 * @param int         $order_id Order ID.
	 * @param string      $state    queued|running|failed|null to clear.
	 * @param string|null $error    Optional stable error code.
	 * @return void
	 */
	public function set_recalculation_state( $order_id, $state, $error = null ) {
		global $wpdb;
		$table   = $wpdb->prefix . 'marginbeacon_order_risk';
		$current = $this->get_current( $order_id );
		if ( ! $current ) {
			return;
		}
		$data = array(
			'recalculation_state'      => ( '' === (string) $state || null === $state ) ? null : (string) $state,
			'recalculation_error_code' => $error,
		);
		$wpdb->update(
			$table,
			$data,
			array( 'id' => (int) $current['id'] )
		);
	}

	/**
	 * Persist no-promotion presentation for Free.
	 *
	 * @param int   $order_id Order ID.
	 * @param array $signal   Signal.
	 * @return void
	 */
	public function upsert_pending_no_promotion( $order_id, array $signal ) {
		$this->persist_result(
			$order_id,
			array(
				'processing_state'            => 'completed',
				'eligibility_state'           => 'no_promotion_free',
				'presentation_state'          => PresentationState::NO_PROMOTION,
				'risk_badge'                  => null,
				'short_reason_code'           => 'MB_NO_PROMOTION_DETECTED',
				'promotion_signal'            => 'none',
				'promotion_signal_confidence' => 'none',
				'evaluation_scope'            => 'free_promoted',
				'formula_version'             => AKSMB_FORMULA_VERSION,
				'input_hash'                  => 'no_promo_' . $order_id,
			)
		);
	}

	/**
	 * Atomically persist a completed (or terminal) result as current.
	 *
	 * Latest material revision wins: a job bound to revision R may become current
	 * only when R still matches the live material hash at promotion time (when provided).
	 * Older workers finishing later must not demote a newer current revision.
	 *
	 * @param int         $order_id       Order ID.
	 * @param array       $header         Header fields.
	 * @param array       $lines          Line rows.
	 * @param string|null $live_revision  Live material hash at persist call (null = ungated legacy path).
	 * @return string OrderRiskPersistOutcome::*
	 */
	public function persist_result( $order_id, array $header, array $lines = array(), $live_revision = null ) {
		global $wpdb;
		$table       = $wpdb->prefix . 'marginbeacon_order_risk';
		$lines_table = $wpdb->prefix . 'marginbeacon_order_risk_lines';

		$header['order_id']         = $order_id;
		$header['current_token']    = null;
		$header['evaluated_at_gmt'] = gmdate( 'Y-m-d H:i:s' );
		if ( empty( $header['order_revision'] ) ) {
			unset( $header['order_revision'] );
		}

		if ( empty( $header['input_hash'] ) ) {
			throw new \RuntimeException( 'MB_ORDER_PERSIST_MISSING_HASH' );
		}

		$claimed = isset( $header['order_revision'] ) ? (string) $header['order_revision'] : '';
		// Re-read live material hash under persistence so mid-flight edits lose safely.
		$live_revision = $this->resolve_live_revision( $order_id, $live_revision );
		if ( $this->is_stale_revision_claim( $claimed, $live_revision ) ) {
			return OrderRiskPersistOutcome::STALE_REVISION;
		}

		// Idempotent: same input_hash — re-promote if returning to prior state (A→B→A) and still live.
		$existing = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT id, current_token, order_revision FROM {$table} WHERE order_id = %d AND input_hash = %s LIMIT 1",
				$order_id,
				$header['input_hash']
			),
			ARRAY_A
		);
		if ( $existing ) {
			if ( (int) $existing['current_token'] === 1 ) {
				$wpdb->update(
					$table,
					array(
						'recalculation_state'      => null,
						'recalculation_error_code' => null,
					),
					array( 'id' => (int) $existing['id'] )
				);
				return OrderRiskPersistOutcome::ALREADY_CURRENT;
			}
			$promo_rev = '' !== $claimed ? $claimed : (string) ( $existing['order_revision'] ?? '' );
			return $this->promote_existing_snapshot( $order_id, (int) $existing['id'], $promo_rev, $live_revision );
		}

		$wpdb->query( 'START TRANSACTION' );
		try {
			if ( ! $this->can_promote_revision( $order_id, $claimed, $live_revision ) ) {
				$wpdb->query( 'ROLLBACK' );
				return OrderRiskPersistOutcome::STALE_REVISION;
			}

			$new_id = $this->insert_row( $header );
			if ( ! $new_id ) {
				throw new \RuntimeException( 'MB_ORDER_PERSIST_FAILED' );
			}

			foreach ( $lines as $line ) {
				$line['order_risk_id'] = $new_id;
				$ok                    = $wpdb->insert( $lines_table, $line );
				if ( false === $ok ) {
					throw new \RuntimeException( 'MB_ORDER_LINE_PERSIST_FAILED' );
				}
			}

			$superseded = $wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
					"UPDATE {$table} SET current_token = NULL, superseded_at_gmt = %s WHERE order_id = %d AND current_token = 1 AND id <> %d",
					gmdate( 'Y-m-d H:i:s' ),
					$order_id,
					$new_id
				)
			);
			if ( false === $superseded ) {
				throw new \RuntimeException( 'MB_ORDER_SUPERSEDE_FAILED' );
			}

			// Fresh live check after locking/supersede intent (order may have changed).
			$live_revision = $this->resolve_live_revision( $order_id, $live_revision );
			if ( $this->is_stale_revision_claim( $claimed, $live_revision )
				|| ! $this->can_promote_revision( $order_id, $claimed, $live_revision ) ) {
				$wpdb->query( 'ROLLBACK' );
				return OrderRiskPersistOutcome::STALE_REVISION;
			}

			$promoted = $wpdb->update(
				$table,
				array( 'current_token' => 1 ),
				array( 'id' => $new_id ),
				array( '%d' ),
				array( '%d' )
			);
			if ( false === $promoted ) {
				throw new \RuntimeException( 'MB_ORDER_PROMOTE_FAILED' );
			}

			$wpdb->query( 'COMMIT' );
			return OrderRiskPersistOutcome::PERSISTED;
		} catch ( \Exception $e ) {
			$wpdb->query( 'ROLLBACK' );
			throw $e;
		}
	}

	/**
	 * Persist terminal evaluation failure (Action Scheduler may retry).
	 *
	 * Recalculation with a prior completed snapshot keeps that row current and
	 * only marks secondary recalculation_state=failed (PRD FR-055). First-ever
	 * evaluation failure may promote a Failed presentation as current.
	 *
	 * @param int         $order_id       Order ID.
	 * @param string      $origin         Origin label.
	 * @param string      $code           Stable error code.
	 * @param string      $revision       Material revision hash when known.
	 * @param string|null $live_revision  Live material hash gate.
	 * @return string OrderRiskPersistOutcome::*
	 */
	public function persist_evaluation_failed( $order_id, $origin, $code, $revision = '', $live_revision = null ) {
		$current = $this->get_current( $order_id );
		if ( is_array( $current ) && 'completed' === (string) ( $current['processing_state'] ?? '' ) ) {
			$this->set_recalculation_state( $order_id, 'failed', $code );
			return OrderRiskPersistOutcome::RECALCULATION_FAILED;
		}

		return $this->persist_result(
			$order_id,
			array(
				'processing_state'            => 'failed',
				'eligibility_state'           => 'evaluation_failed',
				'presentation_state'          => PresentationState::FAILED,
				'risk_badge'                  => null,
				'short_reason_code'           => $code,
				'promotion_signal'            => 'none',
				'promotion_signal_confidence' => 'none',
				'evaluation_scope'            => 'free_promoted',
				'evaluation_origin'           => $origin,
				'formula_version'             => AKSMB_FORMULA_VERSION,
				'order_revision'              => $revision,
				'input_hash'                  => hash( 'sha256', 'failed_' . (int) $order_id . '_' . $code . '_' . gmdate( 'Y-m-d H:i:s' ) ),
			),
			array(),
			$live_revision
		);
	}

	/**
	 * Re-promote a historical snapshot as current (idempotent hash hit).
	 *
	 * @param int         $order_id       Order ID.
	 * @param int         $row_id         Row to promote.
	 * @param string      $claimed        Claimed material revision.
	 * @param string|null $live_revision  Live material hash gate.
	 * @return string OrderRiskPersistOutcome::*
	 */
	private function promote_existing_snapshot( $order_id, $row_id, $claimed = '', $live_revision = null ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_order_risk';

		$live_revision = $this->resolve_live_revision( $order_id, $live_revision );

		$wpdb->query( 'START TRANSACTION' );
		try {
			if ( ! $this->can_promote_revision( $order_id, $claimed, $live_revision ) ) {
				$wpdb->query( 'ROLLBACK' );
				return OrderRiskPersistOutcome::STALE_REVISION;
			}

			$superseded = $wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
					"UPDATE {$table} SET current_token = NULL, superseded_at_gmt = %s WHERE order_id = %d AND current_token = 1 AND id <> %d",
					gmdate( 'Y-m-d H:i:s' ),
					$order_id,
					$row_id
				)
			);
			if ( false === $superseded ) {
				throw new \RuntimeException( 'MB_ORDER_SUPERSEDE_FAILED' );
			}

			$live_revision = $this->resolve_live_revision( $order_id, $live_revision );
			if ( $this->is_stale_revision_claim( $claimed, $live_revision )
				|| ! $this->can_promote_revision( $order_id, $claimed, $live_revision ) ) {
				$wpdb->query( 'ROLLBACK' );
				return OrderRiskPersistOutcome::STALE_REVISION;
			}

			$promoted = $wpdb->update(
				$table,
				array(
					'current_token'            => 1,
					'superseded_at_gmt'        => null,
					'recalculation_state'      => null,
					'recalculation_error_code' => null,
				),
				array( 'id' => $row_id ),
				array( '%d', '%s', '%s', '%s' ),
				array( '%d' )
			);
			if ( false === $promoted ) {
				throw new \RuntimeException( 'MB_ORDER_PROMOTE_FAILED' );
			}
			$wpdb->query( 'COMMIT' );
			return OrderRiskPersistOutcome::PERSISTED;
		} catch ( \Exception $e ) {
			$wpdb->query( 'ROLLBACK' );
			throw $e;
		}
	}

	/**
	 * Resolve the freshest material revision for an order.
	 *
	 * @param int         $order_id       Order ID.
	 * @param string|null $live_revision  Caller-provided hint.
	 * @return string|null
	 */
	private function resolve_live_revision( $order_id, $live_revision ) {
		if ( function_exists( 'wc_get_order' ) ) {
			$order = wc_get_order( (int) $order_id );
			if ( $order ) {
				return OrderMaterialHasher::hash_order( $order );
			}
		}
		if ( null === $live_revision || '' === (string) $live_revision ) {
			return null;
		}
		return (string) $live_revision;
	}

	/**
	 * Claimed revision must match live hash when both are known.
	 *
	 * @param string      $claimed       Claimed revision.
	 * @param string|null $live_revision Live revision.
	 * @return bool
	 */
	private function is_stale_revision_claim( $claimed, $live_revision ) {
		$claimed = (string) $claimed;
		if ( '' === $claimed || null === $live_revision || '' === (string) $live_revision ) {
			return false;
		}
		return ! hash_equals( $claimed, (string) $live_revision );
	}

	/**
	 * Whether this claim may become current given the locked/current row.
	 *
	 * @param int         $order_id      Order ID.
	 * @param string      $claimed       Claimed material revision.
	 * @param string|null $live_revision Live material hash.
	 * @return bool
	 */
	private function can_promote_revision( $order_id, $claimed, $live_revision ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_order_risk';

		if ( $this->is_stale_revision_claim( $claimed, $live_revision ) ) {
			return false;
		}

		$current = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT id, order_revision FROM {$table} WHERE order_id = %d AND current_token = 1 LIMIT 1",
				$order_id
			),
			ARRAY_A
		);
		if ( ! $current ) {
			return true;
		}

		$current_rev = isset( $current['order_revision'] ) ? (string) $current['order_revision'] : '';
		$claimed     = (string) $claimed;

		// Same revision refresh / identical claim.
		if ( '' === $claimed || '' === $current_rev || hash_equals( $current_rev, $claimed ) ) {
			return true;
		}

		// Current is a different revision. Only the live latest may supersede it.
		if ( null === $live_revision || '' === (string) $live_revision ) {
			return false;
		}
		return hash_equals( $claimed, (string) $live_revision );
	}

	/**
	 * Mark current (or create) as ineligible cancelled/failed — not evaluated.
	 *
	 * @param int $order_id Order ID.
	 * @return void
	 */
	public function mark_ineligible_status( $order_id ) {
		$current = $this->get_current( $order_id );
		if ( $current && 'completed' === $current['processing_state'] && 'financial_result' === $current['presentation_state'] ) {
			// Keep existing financial snapshot; do not wipe badge on later cancel.
			return;
		}

		$this->persist_result(
			$order_id,
			array(
				'processing_state'            => 'completed',
				'eligibility_state'           => 'ineligible_status',
				'presentation_state'          => PresentationState::NOT_EVALUATED,
				'risk_badge'                  => null,
				'short_reason_code'           => 'MB_STATUS_INELIGIBLE',
				'promotion_signal'            => $current ? $current['promotion_signal'] : 'none',
				'promotion_signal_confidence' => $current ? $current['promotion_signal_confidence'] : 'none',
				'evaluation_scope'            => 'free_promoted',
				'formula_version'             => AKSMB_FORMULA_VERSION,
				'input_hash'                  => 'ineligible_' . $order_id,
			)
		);
	}

	/**
	 * Mark current snapshot as refunded without changing financial badge.
	 *
	 * @param int $order_id Order ID.
	 * @return bool True when a current row was updated.
	 */
	public function mark_refunded( $order_id ) {
		global $wpdb;
		$current = $this->get_current( $order_id );
		if ( ! $current ) {
			return false;
		}

		$coverage = OrderRefundLifecycle::coverage_from_row( $current );
		if ( ! empty( $coverage[ OrderRefundLifecycle::COVERAGE_FLAG ] ) ) {
			return true;
		}

		$coverage = OrderRefundLifecycle::with_refund_flag( $coverage );
		$ok       = $wpdb->update(
			$wpdb->prefix . 'marginbeacon_order_risk',
			array( 'coverage_json' => wp_json_encode( $coverage ) ),
			array( 'id' => (int) $current['id'] ),
			array( '%s' ),
			array( '%d' )
		);

		return false !== $ok;
	}

	/**
	 * Get current snapshot for order.
	 *
	 * @param int $order_id Order ID.
	 * @return array|null
	 */
	public function get_current( $order_id ) {
		$map = $this->get_current_for_orders( array( (int) $order_id ) );
		return isset( $map[ (int) $order_id ] ) ? $map[ (int) $order_id ] : null;
	}

	/**
	 * Bulk-fetch current snapshots for many orders (Orders list — avoid N+1).
	 *
	 * @param int[] $order_ids Order IDs.
	 * @return array<int,array|null> Map order_id => row or null when missing.
	 */
	public function get_current_for_orders( array $order_ids ) {
		$order_ids = array_values( array_unique( array_filter( array_map( 'intval', $order_ids ) ) ) );
		$out       = array();
		foreach ( $order_ids as $id ) {
			$out[ $id ] = null;
		}
		if ( empty( $order_ids ) ) {
			return $out;
		}

		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_order_risk';
		$in    = implode( ',', $order_ids );
		$rows  = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name; IDs sanitized to ints.
			"SELECT * FROM {$table} WHERE current_token = 1 AND order_id IN ({$in})",
			ARRAY_A
		);
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				$oid         = (int) $row['order_id'];
				$out[ $oid ] = $row;
			}
		}
		return $out;
	}

	/**
	 * List current snapshots (newest first).
	 *
	 * @param int $limit Max rows.
	 * @return array
	 */
	public function list_current( $limit = 50 ) {
		return $this->list_current_filtered(
			array(
				'limit' => $limit,
			)
		);
	}

	/**
	 * List current snapshots with optional Pro filters.
	 *
	 * @param array $args {
	 *     @type int         $limit              Max rows (1–200).
	 *     @type string|null $risk_badge         critical|high|warning|safe|unknown.
	 *     @type string|null $promotion_signal   coupon|sale_price|both|none.
	 *     @type string|null $signal_confidence  verified|suspected|none.
	 *     @type string|null $evaluation_scope   free_promoted|pro_baseline.
	 *     @type string|null $evaluation_origin  realtime|recent_preview.
	 *     @type string|null $presentation_state financial_result|no_promotion|pending|…
	 *     @type string|null $acknowledged       yes|no.
	 * }
	 * @return array<int,array<string,mixed>>
	 */
	public function list_current_filtered( array $args = array() ) {
		global $wpdb;
		$table     = $wpdb->prefix . 'marginbeacon_order_risk';
		$ack_table = $wpdb->prefix . 'marginbeacon_acknowledgements';
		$limit     = isset( $args['limit'] ) ? max( 1, min( 200, (int) $args['limit'] ) ) : 50;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table names from $wpdb->prefix.
		$sql    = "SELECT r.*,
			CASE WHEN a.id IS NOT NULL AND a.status = 'active' THEN 1 ELSE 0 END AS acknowledgement_active
			FROM {$table} r
			LEFT JOIN {$ack_table} a ON a.id = r.acknowledgement_ref
			WHERE r.current_token = 1";
		$params = array();

		if ( ! empty( $args['risk_badge'] ) ) {
			$badge = sanitize_key( (string) $args['risk_badge'] );
			if ( in_array( $badge, array( 'critical', 'high', 'warning', 'safe', 'unknown' ), true ) ) {
				$sql     .= ' AND r.risk_badge = %s';
				$params[] = $badge;
			}
		}
		if ( ! empty( $args['promotion_signal'] ) ) {
			$sig = sanitize_key( (string) $args['promotion_signal'] );
			if ( in_array( $sig, array( 'coupon', 'sale_price', 'both', 'none' ), true ) ) {
				$sql     .= ' AND r.promotion_signal = %s';
				$params[] = $sig;
			}
		}
		if ( ! empty( $args['signal_confidence'] ) ) {
			$conf = sanitize_key( (string) $args['signal_confidence'] );
			if ( in_array( $conf, array( 'verified', 'suspected', 'none' ), true ) ) {
				$sql     .= ' AND r.promotion_signal_confidence = %s';
				$params[] = $conf;
			}
		}
		if ( ! empty( $args['evaluation_scope'] ) ) {
			$scope = sanitize_key( (string) $args['evaluation_scope'] );
			if ( in_array( $scope, array( 'free_promoted', 'pro_baseline' ), true ) ) {
				$sql     .= ' AND r.evaluation_scope = %s';
				$params[] = $scope;
			}
		}
		if ( ! empty( $args['evaluation_origin'] ) ) {
			$origin = sanitize_key( (string) $args['evaluation_origin'] );
			if ( in_array( $origin, array( 'realtime', 'recent_preview' ), true ) ) {
				$sql     .= ' AND r.evaluation_origin = %s';
				$params[] = $origin;
			}
		}
		if ( ! empty( $args['presentation_state'] ) ) {
			$pres = sanitize_key( (string) $args['presentation_state'] );
			if ( in_array( $pres, array( 'financial_result', 'no_promotion', 'pending', 'unsupported', 'failed', 'not_evaluated' ), true ) ) {
				$sql     .= ' AND r.presentation_state = %s';
				$params[] = $pres;
			}
		}
		if ( ! empty( $args['acknowledged'] ) ) {
			$ack = sanitize_key( (string) $args['acknowledged'] );
			if ( 'yes' === $ack ) {
				$sql .= " AND r.acknowledgement_ref IS NOT NULL AND r.acknowledgement_ref > 0
					AND a.status = 'active'";
			} elseif ( 'no' === $ack ) {
				$sql .= " AND (
					r.acknowledgement_ref IS NULL OR r.acknowledgement_ref = 0
					OR a.id IS NULL OR a.status <> 'active'
				)";
			}
		}

		$sql     .= ' ORDER BY r.evaluated_at_gmt DESC, r.id DESC LIMIT %d';
		$params[] = $limit;

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Counts of current confirmed risk badges (excludes preliminary unpaid results).
	 *
	 * @return array<string,int>
	 */
	public function severity_counts() {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_order_risk';
		$rows  = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
			"SELECT risk_badge, COUNT(*) AS c FROM {$table}
			WHERE current_token = 1
				AND risk_badge IS NOT NULL
				AND (payment_result_state IS NULL OR payment_result_state = '' OR payment_result_state = 'confirmed')
			GROUP BY risk_badge",
			ARRAY_A
		);
		$out = array(
			'critical' => 0,
			'high'     => 0,
			'warning'  => 0,
			'safe'     => 0,
			'unknown'  => 0,
		);
		if ( is_array( $rows ) ) {
			foreach ( $rows as $r ) {
				$key = (string) $r['risk_badge'];
				if ( isset( $out[ $key ] ) ) {
					$out[ $key ] = (int) $r['c'];
				}
			}
		}
		return $out;
	}

	/**
	 * Counts of current preliminary (payment-pending) risk badges.
	 *
	 * @return array<string,int>
	 */
	public function preliminary_severity_counts() {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_order_risk';
		$rows  = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
			"SELECT risk_badge, COUNT(*) AS c FROM {$table}
			WHERE current_token = 1
				AND risk_badge IS NOT NULL
				AND payment_result_state = 'preliminary'
			GROUP BY risk_badge",
			ARRAY_A
		);
		$out = array(
			'critical' => 0,
			'high'     => 0,
			'warning'  => 0,
			'safe'     => 0,
			'unknown'  => 0,
		);
		if ( is_array( $rows ) ) {
			foreach ( $rows as $r ) {
				$key = (string) $r['risk_badge'];
				if ( isset( $out[ $key ] ) ) {
					$out[ $key ] = (int) $r['c'];
				}
			}
		}
		return $out;
	}

	/**
	 * Patch acknowledgement_ref on a current snapshot (FR-041 sync without recalculate).
	 *
	 * @param int                 $order_risk_id Order risk row ID.
	 * @param int|null            $ack_ref       Active acknowledgement ID or null.
	 * @param int                 $ack_count     Acknowledged risky lines count.
	 * @param array<string,mixed> $current_row  Current row (for coverage_json merge).
	 * @return bool
	 */
	public function update_acknowledgement_ref( $order_risk_id, $ack_ref, $ack_count, array $current_row ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_order_risk';

		$data    = array();
		$formats = array();

		if ( $ack_ref > 0 ) {
			$data['acknowledgement_ref'] = (int) $ack_ref;
			$formats[]                   = '%d';
		} else {
			$data['acknowledgement_ref'] = null;
			$formats[]                   = '%s';
		}

		if ( ! empty( $current_row['coverage_json'] ) ) {
			$coverage = json_decode( (string) $current_row['coverage_json'], true );
			if ( is_array( $coverage ) ) {
				$coverage['acknowledged_lines'] = max( 0, (int) $ack_count );
				$data['coverage_json']          = wp_json_encode( $coverage );
				$formats[]                      = '%s';
			}
		}

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

		$ok = $wpdb->update(
			$table,
			$data,
			array( 'id' => (int) $order_risk_id ),
			$formats,
			array( '%d' )
		);

		return false !== $ok;
	}

	/**
	 * Clear acknowledgement_ref on current snapshots pointing at a revoked acknowledgement.
	 *
	 * @param int $ack_id Acknowledgement ID.
	 * @return int Rows updated.
	 */
	public function clear_acknowledgement_refs_for_ack( $ack_id ) {
		global $wpdb;
		$ack_id = (int) $ack_id;
		if ( $ack_id <= 0 ) {
			return 0;
		}
		$table = $wpdb->prefix . 'marginbeacon_order_risk';
		$n     = $wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from prefix.
				"UPDATE {$table} SET acknowledgement_ref = NULL WHERE current_token = 1 AND acknowledgement_ref = %d",
				$ack_id
			)
		);
		return false === $n ? 0 : (int) $n;
	}

	/**
	 * Order IDs whose current snapshot includes a catalog entity on a line, or applied coupon.
	 *
	 * @param string $scope_type product|variation|coupon.
	 * @param int    $entity_id  Product, variation, or coupon ID.
	 * @return int[]
	 */
	public function find_order_ids_for_catalog_entity( $scope_type, $entity_id ) {
		global $wpdb;

		$scope_type = sanitize_key( (string) $scope_type );
		$entity_id  = (int) $entity_id;
		if ( $entity_id <= 0 ) {
			return array();
		}

		if ( 'coupon' === $scope_type ) {
			return $this->find_order_ids_for_coupon( $entity_id );
		}

		$risk_table  = $wpdb->prefix . 'marginbeacon_order_risk';
		$lines_table = $wpdb->prefix . 'marginbeacon_order_risk_lines';

		if ( 'variation' === $scope_type ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names from prefix.
			$sql = "SELECT DISTINCT r.order_id FROM {$risk_table} r
				INNER JOIN {$lines_table} l ON l.order_risk_id = r.id
				WHERE r.current_token = 1 AND r.processing_state = 'completed' AND l.variation_id = %d";
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names from prefix.
			$sql = "SELECT DISTINCT r.order_id FROM {$risk_table} r
				INNER JOIN {$lines_table} l ON l.order_risk_id = r.id
				WHERE r.current_token = 1 AND r.processing_state = 'completed' AND l.product_id = %d";
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$ids = $wpdb->get_col( $wpdb->prepare( $sql, $entity_id ) );
		if ( ! is_array( $ids ) ) {
			return array();
		}

		return array_values( array_filter( array_map( 'intval', $ids ) ) );
	}

	/**
	 * Completed current snapshots that used a coupon (keyset cursor on risk.id DESC).
	 *
	 * Examines an entire candidate page without early-exit on match count, so no
	 * matched orders inside the window are skipped. Next page uses id < next_before_id.
	 *
	 * @param int      $coupon_id       Coupon post ID.
	 * @param int      $page_size       Candidate page size.
	 * @param int|null $before_risk_id  Exclusive upper bound on risk.id (null = first page).
	 * @return array{order_ids:int[],next_before_id:int,has_more:bool,examined:int}
	 */
	public function find_order_ids_for_coupon_page( $coupon_id, $page_size = 50, $before_risk_id = null ) {
		global $wpdb;

		$coupon_id = (int) $coupon_id;
		$page_size = max( 1, min( 100, (int) $page_size ) );
		$empty     = array(
			'order_ids'      => array(),
			'next_before_id' => 0,
			'has_more'       => false,
			'examined'       => 0,
		);
		if ( $coupon_id <= 0 || ! class_exists( '\WC_Coupon' ) || ! function_exists( 'wc_get_order' ) ) {
			return $empty;
		}

		$coupon = new \WC_Coupon( $coupon_id );
		if ( ! $coupon->get_id() ) {
			return $empty;
		}
		$code = (string) $coupon->get_code();
		if ( '' === $code ) {
			return $empty;
		}

		$before     = null === $before_risk_id ? PHP_INT_MAX : max( 0, (int) $before_risk_id );
		$risk_table = $wpdb->prefix . 'marginbeacon_order_risk';
		$rows       = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from prefix.
				"SELECT id, order_id FROM {$risk_table} WHERE current_token = 1 AND processing_state = 'completed' AND promotion_signal IN ('coupon','both') AND id < %d ORDER BY id DESC LIMIT %d",
				$before,
				$page_size
			),
			ARRAY_A
		);
		if ( ! is_array( $rows ) || empty( $rows ) ) {
			return $empty;
		}

		$matched      = array();
		$examined_ids = array();
		foreach ( $rows as $row ) {
			$examined_ids[] = (int) $row['id'];
			$order_id       = (int) $row['order_id'];
			$order          = wc_get_order( $order_id );
			if ( ! $order ) {
				continue;
			}
			foreach ( $order->get_coupon_codes() as $applied ) {
				if ( 0 === strcasecmp( (string) $applied, $code ) ) {
					$matched[] = $order_id;
					break;
				}
			}
		}

		return self::coupon_keyset_page_result( $matched, $examined_ids, $page_size );
	}

	/**
	 * Build coupon sync page cursor metadata (production helper for tests).
	 *
	 * @param int[] $matched_order_ids Matched order IDs from the examined page.
	 * @param int[] $examined_risk_ids Risk row ids in DESC examination order.
	 * @param int   $page_size         Requested page size.
	 * @return array{order_ids:int[],next_before_id:int,has_more:bool,examined:int}
	 */
	public static function coupon_keyset_page_result( array $matched_order_ids, array $examined_risk_ids, $page_size ) {
		$page_size = max( 1, (int) $page_size );
		$last_id   = empty( $examined_risk_ids ) ? 0 : (int) end( $examined_risk_ids );
		return array(
			'order_ids'      => array_values( array_unique( array_map( 'intval', $matched_order_ids ) ) ),
			'next_before_id' => $last_id,
			'has_more'       => count( $examined_risk_ids ) >= $page_size,
			'examined'       => count( $examined_risk_ids ),
		);
	}

	/**
	 * Completed current snapshots that used a coupon (first keyset page).
	 *
	 * @param int $coupon_id Coupon post ID.
	 * @param int $limit     Max candidates examined on first page.
	 * @return int[]
	 */
	public function find_order_ids_for_coupon( $coupon_id, $limit = 50 ) {
		$page = $this->find_order_ids_for_coupon_page( $coupon_id, max( 1, min( 100, (int) $limit ) ), null );
		return $page['order_ids'];
	}

	/**
	 * Line evidence rows for the current order snapshot (Pro FR-070).
	 *
	 * @param int $order_id Order ID.
	 * @return array<int,array<string,mixed>>
	 */
	public function get_lines_for_order( $order_id ) {
		global $wpdb;
		$order_id = (int) $order_id;
		if ( $order_id <= 0 ) {
			return array();
		}
		$current = $this->get_current( $order_id );
		if ( ! $current || empty( $current['id'] ) ) {
			return array();
		}
		$lines_table = $wpdb->prefix . 'marginbeacon_order_risk_lines';
		$rows        = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT * FROM {$lines_table} WHERE order_risk_id = %d ORDER BY id ASC",
				(int) $current['id']
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * JOIN / WHERE fragments to constrain Woo order lists by Promo Risk (Pro FR-009).
	 *
	 * @param string $filter           Sanitized filter from OrdersListRiskFilter::sanitize().
	 * @param string $orders_id_column Qualified orders.id column (e.g. wp_wc_orders.id or {$wpdb->posts}.ID).
	 * @return array{join:string,where:string}|null
	 */
	public function orders_list_filter_sql( $filter, $orders_id_column ) {
		global $wpdb;

		$filter = \Kayvandra\MarginBeacon\Application\OrderRisk\OrdersListRiskFilter::sanitize( $filter );
		if ( '' === $filter ) {
			return null;
		}

		$table = $wpdb->prefix . 'marginbeacon_order_risk';
		$col   = $orders_id_column;
		$alias = 'aksmb_or';

		if ( \Kayvandra\MarginBeacon\Application\OrderRisk\OrdersListRiskFilter::is_missing_row( $filter ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table names from $wpdb->prefix.
			return array(
				'join'  => " LEFT JOIN {$table} AS {$alias} ON {$alias}.order_id = {$col} AND {$alias}.current_token = 1 ",
				'where' => " AND {$alias}.id IS NULL ",
			);
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table names from $wpdb->prefix.
		$join = " INNER JOIN {$table} AS {$alias} ON {$alias}.order_id = {$col} AND {$alias}.current_token = 1 ";

		$severities = \Kayvandra\MarginBeacon\Application\OrderRisk\OrdersListRiskFilter::severity_values();
		if ( in_array( $filter, $severities, true ) ) {
			return array(
				'join'  => $join,
				'where' => $wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- fixed SQL alias.
					" AND {$alias}.risk_badge = %s ",
					$filter
				),
			);
		}

		if ( 'no_result' === $filter ) {
			return array(
				'join'  => $join,
				'where' => " AND {$alias}.risk_badge IS NULL AND {$alias}.presentation_state <> 'pending' ",
			);
		}

		return array(
			'join'  => $join,
			'where' => $wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- fixed SQL alias.
				" AND {$alias}.presentation_state = %s ",
				$filter
			),
		);
	}

	/**
	 * Insert row helper.
	 *
	 * @param array $data Data.
	 * @return int
	 */
	private function insert_row( array $data ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_order_risk';
		$ok    = $wpdb->insert( $table, $data );
		if ( false === $ok ) {
			return 0;
		}
		return (int) $wpdb->insert_id;
	}
}