<?php
/**
 * Retention cleanup for operational events and expired rows (PRD §17).
 *
 * @package Kayvandra\MarginBeacon
 */

declare(strict_types=1);

namespace Kayvandra\MarginBeacon\Application\Retention;

/**
 * Purges rows past retain_until_gmt / closed revision TTL / superseded order risk.
 */
final class RetentionCleanupService {

	/** Closed price revisions retention (PRD: 90 days). */
	const PRICE_REVISION_DAYS = 90;

	/** Superseded order-risk rows retention. */
	const SUPERSEDED_ORDER_RISK_DAYS = 90;

	/** Findings retention (PRD §17.6: 90 days). */
	const FINDINGS_DAYS = 90;

	/**
	 * Run bounded cleanup batches.
	 *
	 * @param int $limit Max rows per table per run.
	 * @return array{events:int,revisions:int,findings:int,order_risk:int,order_risk_lines:int}
	 */
	public function run( $limit = 500 ) {
		global $wpdb;
		$limit = max( 1, min( 5000, (int) $limit ) );
		$now   = gmdate( 'Y-m-d H:i:s' );

		$events_table   = $wpdb->prefix . 'marginbeacon_operational_events';
		$rev_table      = $wpdb->prefix . 'marginbeacon_price_revisions';
		$findings_table = $wpdb->prefix . 'marginbeacon_findings';
		$risk_table     = $wpdb->prefix . 'marginbeacon_order_risk';
		$lines_table    = $wpdb->prefix . 'marginbeacon_order_risk_lines';

		/*
		 * Table names come from $wpdb->prefix (trusted). Dynamic IN (%d,...) lists
		 * cannot be static placeholders. Values are still passed through prepare().
		 */

		$events = (int) $wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"DELETE FROM {$events_table} WHERE retain_until_gmt IS NOT NULL AND retain_until_gmt < %s LIMIT %d",
				$now,
				$limit
			)
		);

		$revs = (int) $wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"DELETE FROM {$rev_table} WHERE valid_to_gmt IS NOT NULL AND valid_to_gmt < DATE_SUB(%s, INTERVAL %d DAY) LIMIT %d",
				$now,
				self::PRICE_REVISION_DAYS,
				$limit
			)
		);

		$findings = (int) $wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"DELETE FROM {$findings_table} WHERE created_at_gmt < DATE_SUB(%s, INTERVAL %d DAY) LIMIT %d",
				$now,
				self::FINDINGS_DAYS,
				$limit
			)
		);

		// Collect superseded parent IDs first so child lines can be purged (no FK cascade).
		$stale_ids = $wpdb->get_col(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT id FROM {$risk_table} WHERE current_token IS NULL AND superseded_at_gmt IS NOT NULL AND superseded_at_gmt < DATE_SUB(%s, INTERVAL %d DAY) ORDER BY id ASC LIMIT %d",
				$now,
				self::SUPERSEDED_ORDER_RISK_DAYS,
				$limit
			)
		);
		$stale_ids     = array_map( 'intval', (array) $stale_ids );
		$lines_deleted = 0;
		$order_risk    = 0;
		if ( ! empty( $stale_ids ) ) {
			$placeholders  = implode( ',', array_fill( 0, count( $stale_ids ), '%d' ) );
			$lines_deleted = (int) $wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- trusted table name; IN list of ints.
					"DELETE FROM {$lines_table} WHERE order_risk_id IN ({$placeholders})",
					$stale_ids
				)
			);
			$order_risk = (int) $wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- trusted table name; IN list of ints.
					"DELETE FROM {$risk_table} WHERE id IN ({$placeholders})",
					$stale_ids
				)
			);
		}

		return array(
			'events'           => $events,
			'revisions'        => $revs,
			'findings'         => $findings,
			'order_risk'       => $order_risk,
			'order_risk_lines' => $lines_deleted,
		);
	}
}