<?php
/**
 * Costudio_Housekeeping — Automatic database maintenance.
 *
 * Cleans up old productions, logs, and usage records to keep the database slim.
 *
 * @package Contentosapp_Studio
 * @since   3.2.0
 */

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Cleanup queries on plugin-owned tables; all table names are $wpdb->prefix . constant, no user input.

class Costudio_Housekeeping {

	const CRON_HOOK = 'costudio_housekeeping_cleanup';
	const WATCHDOG_HOOK = 'costudio_stalled_pipeline_watchdog';
	const WATCHDOG_INTERVAL = 'costudio_every_fifteen_minutes';
	const DEFAULT_STALLED_AFTER_SECONDS = 900;
	const OPTIMIZE_OPTION = 'costudio_housekeeping_optimize_tables';

	/**
	 * Register cron hooks.
	 */
	public static function register_hooks(): void {
		if ( ! self::production_table_exists() ) {
			wp_clear_scheduled_hook( self::CRON_HOOK );
			wp_clear_scheduled_hook( self::WATCHDOG_HOOK );
			return;
		}

		add_filter( 'cron_schedules', [ __CLASS__, 'add_cron_schedules' ] );
		add_action( self::CRON_HOOK, [ __CLASS__, 'run_cleanup' ] );
		add_action( self::WATCHDOG_HOOK, [ __CLASS__, 'run_stalled_watchdog' ] );
		
		if ( ! wp_next_scheduled( self::CRON_HOOK ) ) {
			wp_schedule_event( time(), 'weekly', self::CRON_HOOK );
		}

		if ( ! wp_next_scheduled( self::WATCHDOG_HOOK ) ) {
			wp_schedule_event( time() + 5 * MINUTE_IN_SECONDS, self::WATCHDOG_INTERVAL, self::WATCHDOG_HOOK );
		}
	}

	/**
	 * Register the short interval used by the stalled-pipeline watchdog.
	 *
	 * @param array<string,array<string,mixed>> $schedules Cron schedules.
	 * @return array<string,array<string,mixed>>
	 */
	public static function add_cron_schedules( array $schedules ): array {
		$schedules[ self::WATCHDOG_INTERVAL ] = [
			'interval' => 15 * MINUTE_IN_SECONDS,
			'display'  => __( 'Every 15 minutes (Contentosapp Studio)', 'contentosapp-studio' ),
		];

		return $schedules;
	}

	/**
	 * Seconds after which a running production with no heartbeat is considered stalled.
	 */
	public static function stalled_after_seconds(): int {
		return max(
			5 * MINUTE_IN_SECONDS,
			(int) apply_filters( 'costudio_stalled_after_seconds', self::DEFAULT_STALLED_AFTER_SECONDS )
		);
	}

	/**
	 * Seconds after which the final publishing step is considered stalled.
	 *
	 * Final publishing is shorter than AI generation and now refreshes its
	 * heartbeat at each milestone. If that heartbeat stops, the worker was very
	 * likely killed by the host and the production should become retryable.
	 */
	public static function final_publish_stalled_after_seconds(): int {
		return max(
			5 * MINUTE_IN_SECONDS,
			(int) apply_filters( 'costudio_final_publish_stalled_after_seconds', 5 * MINUTE_IN_SECONDS )
		);
	}

	/**
	 * Get the proper stale threshold for a specific production.
	 *
	 * @param object $production Production row/model.
	 */
	public static function stalled_after_seconds_for_production( $production ): int {
		if ( self::is_final_publish_step( $production ) ) {
			return self::final_publish_stalled_after_seconds();
		}

		return self::stalled_after_seconds();
	}

	/**
	 * Whether the production is in the internal final publishing step.
	 *
	 * @param object $production Production row/model.
	 */
	public static function is_final_publish_step( $production ): bool {
		if ( ! $production ) {
			return false;
		}

		$version = method_exists( $production, 'get_pipeline_version' )
			? (int) $production->get_pipeline_version()
			: 7;
		$max     = ( 7 === $version ) ? 7 : 6;
		$current = (int) ( $production->current_agent ?? 0 );

		if ( $current > $max ) {
			return true;
		}

		for ( $i = 1; $i <= $max; $i++ ) {
			$col = method_exists( $production, 'map_step_to_column' )
				? $production->map_step_to_column( $i )
				: sprintf( 'output_%02d', $i );
			if ( $col && empty( $production->$col ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Main cleanup routine.
	 */
	public static function run_cleanup(): void {
		global $wpdb;

		if ( ! self::production_table_exists() ) {
			return;
		}

		// 1. Purge old productions (older than 45 days).
		// Keeps the DB lean but allows users to see recent history.
		$table_prod = $wpdb->prefix . Costudio_Production::TABLE;
		$wpdb->query( "DELETE FROM {$table_prod} WHERE created_at < DATE_SUB(NOW(), INTERVAL 45 DAY)" );

		// 2. Purge old usage records (older than 60 days).
		$table_usage = $wpdb->prefix . 'costudio_usage';
		$wpdb->query( "DELETE FROM {$table_usage} WHERE created_at < DATE_SUB(NOW(), INTERVAL 60 DAY)" );

		// 3. Purge old logs after productions age out of the UI history.
		$table_logs = $wpdb->prefix . Costudio_Logger::TABLE;
		$wpdb->query( "DELETE FROM {$table_logs} WHERE created_at < DATE_SUB(NOW(), INTERVAL 45 DAY)" );

		// 4. Retain durable BYOK payloads for 7 days and audit metadata for 90.
		if ( class_exists( 'Costudio_BYOK_Jobs' ) ) {
			Costudio_BYOK_Jobs::cleanup();
		}

		// 5. The final-publication ledger follows the same retention principle:
		// discard the resumable payload after seven days and retain bounded audit
		// metadata for ninety days.
		if ( class_exists( 'Costudio_Publication_Jobs' ) ) {
			Costudio_Publication_Jobs::cleanup();
		}

		// 6. Optional heavy maintenance. OPTIMIZE TABLE can lock large tables,
		// so weekly cleanup never runs it unless explicitly enabled.
		if ( self::should_optimize_tables() ) {
			$tables = array( $table_prod, $table_usage, $table_logs );
			if ( class_exists( 'Costudio_BYOK_Jobs' ) ) {
				$tables[] = $wpdb->prefix . Costudio_BYOK_Jobs::TABLE;
			}
			self::optimize_tables( $tables );
		}
	}

	/**
	 * Whether weekly cleanup may run OPTIMIZE TABLE.
	 */
	public static function should_optimize_tables(): bool {
		$enabled = (bool) get_option( self::OPTIMIZE_OPTION, false );

		return (bool) apply_filters( 'costudio_housekeeping_should_optimize_tables', $enabled );
	}

	/**
	 * Run table optimization for a known list of Contentosapp Studio-owned tables.
	 *
	 * @param array<int,string>|null $tables Fully-qualified table names.
	 */
	public static function optimize_tables( ?array $tables = null ): bool {
		global $wpdb;

		if ( null === $tables ) {
			$tables = array(
				$wpdb->prefix . Costudio_Production::TABLE,
				$wpdb->prefix . 'costudio_usage',
				$wpdb->prefix . Costudio_Logger::TABLE,
			);
			if ( class_exists( 'Costudio_BYOK_Jobs' ) ) {
				$tables[] = $wpdb->prefix . Costudio_BYOK_Jobs::TABLE;
			}
		}

		$tables = array_values(
			array_filter(
				array_map(
					static function ( string $table ): string {
						$sanitized = preg_replace( '/[^A-Za-z0-9_]/', '', $table );
						return is_string( $sanitized ) ? $sanitized : '';
					},
					$tables
				)
			)
		);

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

		$result = $wpdb->query( 'OPTIMIZE TABLE ' . implode( ', ', $tables ) );

		do_action( 'costudio_housekeeping_optimized_tables', $tables, $result );

		return false !== $result;
	}

	/**
	 * Detect productions left in `running` after the background worker stopped.
	 *
	 * We deliberately mark them as retryable errors instead of auto-resuming:
	 * a silent PHP-FPM kill can happen while a provider request is still
	 * ambiguous, so invisible retries risk duplicate agent work or quota usage.
	 */
	public static function run_stalled_watchdog(): void {
		global $wpdb;

		if ( ! self::production_table_exists() ) {
			return;
		}

		// This sweep is independent of an open production screen. It lets ordinary
		// WordPress installs recover a queued/expired publication through WP-Cron
		// without any server-side worker configuration.
		if ( class_exists( 'Costudio_Publication_Jobs' ) ) {
			Costudio_Publication_Jobs::sweep( 20 );
		}
		if ( class_exists( 'Costudio_Runner' ) ) {
			Costudio_Runner::recover_stalled_handoffs( 20, 'watchdog' );
		}

		$table     = $wpdb->prefix . Costudio_Production::TABLE;
		$threshold = min( self::stalled_after_seconds(), self::final_publish_stalled_after_seconds() );
		$cutoff    = gmdate( 'Y-m-d H:i:s', time() - $threshold );
		$limit     = (int) apply_filters( 'costudio_stalled_watchdog_limit', 20 );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, current_agent, current_agent_started_at, heartbeat FROM {$table}
				 WHERE status = 'running'
				   AND heartbeat IS NOT NULL
				   AND heartbeat <> ''
				   AND heartbeat < %s
				 ORDER BY heartbeat ASC
				 LIMIT %d",
				$cutoff,
				$limit
			)
		);

		foreach ( $rows ?: [] as $row ) {
			$id = (int) $row->id;
			if ( empty( $row->current_agent_started_at ) ) {
				// Safe handoff gaps are retried by the dedicated recovery path above;
				// never turn them into an ambiguous provider failure.
				continue;
			}
			if ( class_exists( 'Costudio_Publication_Jobs' ) && Costudio_Publication_Jobs::has_live_job( $id ) ) {
				Costudio_Publication_Jobs::ensure_progress( $id, 'watchdog' );
				continue;
			}
			if ( class_exists( 'Costudio_BYOK_Jobs' ) && Costudio_BYOK_Jobs::has_pending_application( $id ) ) {
				self::resume_durable_byok_readback( $id, 'watchdog' );
				continue;
			}
			if ( class_exists( 'Costudio_Remote_Jobs' ) && Costudio_Remote_Jobs::has_live_job( $id ) ) {
				self::resume_durable_remote_polling( $id, 'watchdog' );
				continue;
			}
			$production = Costudio_Production::get_by_id( $id );
			if ( ! $production || empty( $production->heartbeat ) ) {
				continue;
			}

			$beat = \DateTimeImmutable::createFromFormat( 'Y-m-d H:i:s', $production->heartbeat, new \DateTimeZone( 'UTC' ) );
			if ( ! $beat ) {
				continue;
			}

			$row_threshold = self::stalled_after_seconds_for_production( $production );
			if ( ( time() - $beat->getTimestamp() ) <= $row_threshold ) {
				continue;
			}

			$lock_age = self::get_runner_lock_age( $id );

			// If a fresh lock exists, another worker is probably still alive.
			if ( $lock_age > 0 && $lock_age < $row_threshold ) {
				continue;
			}

			self::mark_stalled_production( $id, 'watchdog' );
		}
	}

	private static function production_table_exists(): bool {
		global $wpdb;

		$table = $wpdb->prefix . Costudio_Production::TABLE;
		return $table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
	}

	/**
	 * Mark a specific production as stalled/retryable.
	 */
	public static function mark_stalled_production( int $production_id, string $source = 'watchdog' ): bool {
		$production = Costudio_Production::get_by_id( $production_id );
		if ( ! $production || 'running' !== $production->status ) {
			return false;
		}
		if ( class_exists( 'Costudio_BYOK_Jobs' ) && Costudio_BYOK_Jobs::has_pending_application( $production_id ) ) {
			self::resume_durable_byok_readback( $production_id, $source );
			return false;
		}
		if ( class_exists( 'Costudio_Remote_Jobs' ) && Costudio_Remote_Jobs::has_live_job( $production_id ) ) {
			self::resume_durable_remote_polling( $production_id, $source );
			return false;
		}

		$agent = max( 1, (int) ( $production->current_agent ?? 1 ) );
		$is_final_publish = self::is_final_publish_step( $production );
		$msg   = $is_final_publish
			? __( 'A etapa final de publicação parece ter sido interrompida pelo servidor. Clique em "Retry publication" para tentar publicar novamente sem regenerar os agentes.', 'contentosapp-studio' )
			: __( 'The hosting environment appears to have stopped the background worker. Click "Retry Agent" to resume from the current agent.', 'contentosapp-studio' );

		delete_transient( "costudio_bg_{$production_id}" );
		delete_transient( "costudio_bg_src_{$production_id}" );
		delete_transient( "costudio_bg_at_{$production_id}" );
		delete_option( "costudio_handoff_recovery_{$production_id}" );
		delete_option( "costudio_runner_lock_{$production_id}" );
		self::clear_scheduled_runner_events( $production_id );

		Costudio_Production::set_error( $production_id, $msg, $agent );

		if ( class_exists( 'Costudio_Runner' ) ) {
			Costudio_Runner::pipeline_log_append(
				$production_id,
				sprintf(
					/* translators: 1: source label, 2: step label */
					__( 'Pipeline watchdog (%1$s) marked this production as stalled on %2$s. Use retry to continue safely.', 'contentosapp-studio' ),
					$source,
					$is_final_publish ? __( 'final publishing', 'contentosapp-studio' ) : sprintf( 'Agent %02d', $agent )
				),
				'warn',
				$is_final_publish ? null : $agent
			);
		}

		costudio_log( 'pipeline.stalled_marked_retryable', [
			'production_id' => $production_id,
			'agent'         => $agent,
			'source'        => $source,
		], 'warning' );

		return true;
	}

	/** Re-dispatching a poll-only v2 attempt is safe and never calls the provider twice. */
	private static function resume_durable_remote_polling( int $production_id, string $source ): void {
		$key = 'costudio_remote_poll_nudge_' . $production_id;
		if ( get_transient( $key ) ) {
			return;
		}
		set_transient( $key, 1, MINUTE_IN_SECONDS );
		if ( ! class_exists( 'Costudio_Runner' ) ) {
			return;
		}
		$token = wp_generate_password( 32, false );
		Costudio_Runner::prime_background_token( $production_id, $token, 'durable_remote_' . sanitize_key( $source ) );
		Costudio_Runner::kick_off_background( $production_id, $token );
		Costudio_Runner::pipeline_log_append(
			$production_id,
			__( 'Remote AI job is still active. Resuming its status check without starting a new provider request.', 'contentosapp-studio' ),
			'info'
		);
	}

	/** Nudge only the local result reader; provider execution remains CLI-only. */
	private static function resume_durable_byok_readback( int $production_id, string $source ): void {
		$key = 'costudio_byok_readback_nudge_' . $production_id;
		if ( get_transient( $key ) ) {
			return;
		}
		set_transient( $key, 1, MINUTE_IN_SECONDS );
		if ( ! class_exists( 'Costudio_Runner' ) ) {
			return;
		}
		$token = wp_generate_password( 32, false );
		Costudio_Runner::prime_background_token( $production_id, $token, 'durable_byok_' . sanitize_key( $source ) );
		Costudio_Runner::kick_off_background( $production_id, $token );
		Costudio_Runner::pipeline_log_append(
			$production_id,
			__( 'The durable BYOK attempt is still authoritative. Resuming its local status read without starting another provider request.', 'contentosapp-studio' ),
			'info'
		);
	}

	private static function get_runner_lock_age( int $production_id ): int {
		$value = get_option( "costudio_runner_lock_{$production_id}", '' );
		if ( ! is_string( $value ) || strpos( $value, '|' ) === false ) {
			return 0;
		}

		$parts = explode( '|', $value, 2 );
		$ts    = (int) ( $parts[1] ?? 0 );
		return $ts > 0 ? max( 0, time() - $ts ) : 0;
	}

	private static function clear_scheduled_runner_events( int $production_id ): void {
		$crons = _get_cron_array();
		if ( empty( $crons ) ) {
			return;
		}

		foreach ( $crons as $timestamp => $cronhooks ) {
			if ( empty( $cronhooks[ Costudio_Runner::CRON_HOOK ] ) ) {
				continue;
			}

			foreach ( $cronhooks[ Costudio_Runner::CRON_HOOK ] as $event ) {
				$args = $event['args'] ?? [];
				if ( isset( $args[0] ) && (int) $args[0] === $production_id ) {
					wp_unschedule_event( (int) $timestamp, Costudio_Runner::CRON_HOOK, $args );
				}
			}
		}
	}
}
