<?php
/**
 * Backup record — DAL for wp_backreload_backups.
 *
 * @package Backreload\Models
 */

namespace Backreload\Models;

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

class BackupRecord {

	public ?int    $id           = null;
	public string  $ext_id       = '';
	public string  $type         = 'full';
	/**
	 * Provenance of this backup:
	 *   'manual'    — user clicked Backup now or ran wp backreload create
	 *   'scheduled' — created by a schedule's cron tick
	 */
	public string  $kind         = 'manual';
	public string  $status       = 'pending';
	public int     $size         = 0;
	public int     $duration     = 0;
	public bool    $encrypted    = false;
	public array   $destinations = [];
	public array   $components   = [];
	public ?string $checksum     = null;
	public ?string $error        = null;
	public ?string $source_url   = null;
	public ?string $source_path  = null;
	/**
	 * Multi-zip parts. Each entry:
	 *   [
	 *     'name'        => 'bk_xxx-plugins-1.zip',
	 *     'category'    => 'plugins' | 'themes' | 'uploads' | 'mu' | 'others' | 'db',
	 *     'size'        => 123456,
	 *     'checksum'    => 'sha256-hex',
	 *     'encrypted'   => bool,
	 *     'uploaded_to' => [ 'd_xxx', 'd_yyy' ],   // list of destination ext_ids
	 *   ]
	 * Empty array for legacy single-zip backups.
	 *
	 * @var array<int,array<string,mixed>>
	 */
	public array $files          = [];
	public ?string $created_at   = null;
	public ?string $completed_at = null;

	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . 'backreload_backups';
	}

	public static function generate_id(): string {
		return 'bk_' . wp_generate_password( 10, false, false );
	}

	public static function create( array $data ): self {
		$rec               = new self();
		$rec->ext_id       = $data['ext_id'] ?? self::generate_id();
		$rec->type         = $data['type']   ?? 'full';
		$rec->kind         = $data['kind']   ?? 'manual';
		$rec->status       = $data['status'] ?? 'pending';
		$rec->components   = (array) ( $data['components']   ?? [] );
		$rec->destinations = (array) ( $data['destinations'] ?? [] );
		$rec->encrypted    = (bool)  ( $data['encrypted']    ?? false );
		$rec->source_url   = isset( $data['source_url'] )  ? (string) $data['source_url']  : null;
		$rec->source_path  = isset( $data['source_path'] ) ? (string) $data['source_path'] : null;
		$rec->created_at   = current_time( 'mysql', true );
		$rec->save();
		return $rec;
	}

	/**
	 * Throttled progress save. The engine calls this after non-status work
	 * (a part is built, a part is uploaded) to record incremental progress
	 * without thrashing the row. Writes are limited to one per N seconds
	 * per ext_id; in between, the in-memory state accumulates and lands
	 * with the next forced save.
	 *
	 * History: every progress event used to call save() directly, which
	 * (a) rewrote the LONGTEXT `files` column on every chunk, and (b) ran
	 * the cancel-race SELECT before every write. On a 20-part backup with
	 * 4 destinations and per-file granular progress, that was easily 100+
	 * SELECT/UPDATE pairs per run — measurably slowing the tick on shared
	 * MySQL. The UI polls every 2 seconds so saving more frequently than
	 * that wouldn't change what the user sees anyway.
	 *
	 * Terminal / cancellation status transitions bypass throttling and
	 * always land via the regular save() path; the engine catches them by
	 * calling save() directly (not via save_progress()) when flipping the
	 * status column.
	 *
	 * @param int $min_interval_seconds  Minimum seconds between actual writes.
	 *                                   Default 2 mirrors the BackupProgress
	 *                                   poll cadence in the UI.
	 */
	public function save_progress( int $min_interval_seconds = 2 ): void {
		// Static per-PHP-request map so each ext_id has its own debounce
		// timer. Cron ticks are separate requests, so the map naturally
		// resets at tick boundaries — that's intentional: each new tick
		// gets one immediate save followed by throttled ones.
		static $last_save_at = [];
		$key = (string) $this->ext_id;
		$now = microtime( true );
		if ( isset( $last_save_at[ $key ] ) && ( $now - $last_save_at[ $key ] ) < $min_interval_seconds ) {
			return; // Skip — within debounce window. State remains in memory.
		}
		$this->save();
		$last_save_at[ $key ] = $now;
	}

	public function save(): void {
		global $wpdb;

		// Guard against a cancel-race: if the row was flipped to 'cancelling' since
		// we loaded it (e.g. the user clicked Stop while we were mid-upload), adopt
		// that status into the in-memory record instead of clobbering it back to
		// 'running' / 'pending'. The next check_cancellation() will then throw and
		// the engine will exit cleanly through its CancelledException path.
		//
		// Skipped when we're already writing a terminal/cancel-aware status — those
		// are deliberate transitions that should land.
		if (
			$this->id
			&& ! in_array( $this->status, [ 'cancelling', 'cancelled', 'failed' ], true )
		) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from self::table() = $wpdb->prefix (never user input), cannot be bound via prepare(); reads the plugin's own table, result is transient and uncacheable.
			$db_status = $wpdb->get_var( $wpdb->prepare(
				'SELECT status FROM ' . self::table() . ' WHERE id = %d',
				$this->id
			) );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			if ( 'cancelling' === $db_status ) {
				$this->status = 'cancelling';
			}
		}

		$row = [
			'ext_id'       => $this->ext_id,
			'type'         => $this->type,
			'kind'         => $this->kind,
			'status'       => $this->status,
			'size'         => $this->size,
			'duration'     => $this->duration,
			'encrypted'    => $this->encrypted ? 1 : 0,
			'destinations' => wp_json_encode( $this->destinations ),
			'components'   => wp_json_encode( $this->components ),
			'files'        => wp_json_encode( $this->files ),
			'checksum'     => $this->checksum,
			'error'        => $this->error,
			'source_url'   => $this->source_url,
			'source_path'  => $this->source_path,
			'created_at'   => $this->created_at ?: current_time( 'mysql', true ),
			'completed_at' => $this->completed_at,
		];
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin reads/writes its own custom tables; results are transient backup state and must not be cached.
		if ( $this->id ) {
			$result = $wpdb->update( self::table(), $row, [ 'id' => $this->id ] );
			if ( false === $result ) {
				throw new \RuntimeException(
					 esc_html( 'Failed to update backup record: ' . ( $wpdb->last_error ?: 'unknown error' ) ) 
				);
			}
		} else {
			$result = $wpdb->insert( self::table(), $row );
			if ( false === $result ) {
				// Most likely cause: a schema migration didn't run and a new column doesn't exist.
				throw new \RuntimeException(
					 esc_html( 'Failed to insert backup record (probable schema mismatch): '
					. ( $wpdb->last_error ?: 'unknown error' )
					. '. Try deactivating + reactivating the plugin to re-run migrations.'
				 ) );
			}
			$this->id = (int) $wpdb->insert_id;
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Remove the DB row.
	 *
	 * NOTE: this does NOT delete archive files on destinations. Use
	 * `BackupRecord::delete_with_remote_files()` (preferred) when invoked from
	 * REST DELETE / retention sweeps. Kept on its own so internal callers that
	 * want a row-only removal (e.g. after manually cleaning up files) can opt
	 * out.
	 */
	public function delete(): void {
		global $wpdb;
		if ( $this->id ) {
			$wpdb->delete( self::table(), [ 'id' => $this->id ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin reads/writes its own custom tables; results are transient backup state and must not be cached.
		}
	}

	/**
	 * Delete the record AND every archive file it placed on destinations.
	 *
	 * Resolves remote filenames from `files[]` (modern multi-zip) with a
	 * fallback to the legacy `{ext_id}.zip{.enc}` filenames. Failures on any
	 * individual destination are logged but never thrown — we still want the
	 * DB row gone even if a destination is unreachable.
	 *
	 * @param array<string,\Backreload\Models\DestinationRecord>|null $dest_cache
	 *        Optional pre-loaded destinations map (ext_id → record), so retention
	 *        sweeps can pass it once instead of looking up per backup. Falls back
	 *        to per-call lookups when null.
	 */
	public function delete_with_remote_files( ?array $dest_cache = null ): void {
		$plugin  = function_exists( 'backreload' ) ? backreload() : null;
		$logger  = $plugin ? $plugin->get( 'logger' )       : null;
		$manager = $plugin ? $plugin->get( 'destinations' ) : null;

		// What filenames did this backup place on destinations? Three layers:
		//   1. Every archive part the engine recorded in $rec->files.
		//   2. The manifest.json sibling — NOT in files[] because it's
		//      metadata, not a restorable part, but it IS uploaded to the
		//      bk_<ext_id>/ folder by the engine. Without including it
		//      here, delete() would leave the manifest behind. Then sync's
		//      list_backups would see the folder, find the manifest,
		//      reimport the backup as a fresh record — zombie effect. This
		//      is the exact "delete doesn't work, scan brings them back"
		//      issue the user reported.
		//   3. Pre-multi-zip legacy fallback (single-zip layout).
		$names = ! empty( $this->files )
			? array_values( array_filter( array_column( $this->files, 'name' ) ) )
			: [ $this->ext_id . '.zip', $this->ext_id . '.zip.enc' ];
		// Add the manifest path. Multi-zip layout puts everything under
		// bk_<ext_id>/, so the manifest is bk_<ext_id>/manifest.json. Use
		// the same prefix derivation the engine uses on upload.
		$names[] = $this->ext_id . '/' . \Backreload\Core\Manifest::FILENAME;
		// Also try the (legacy) flat path in case a really old install
		// uploaded the manifest without a folder prefix.
		$names[] = \Backreload\Core\Manifest::FILENAME;

		foreach ( (array) $this->destinations as $did ) {
			$dest = $dest_cache[ $did ] ?? DestinationRecord::find( $did );
			if ( ! $dest || ! $manager ) continue;
			// Skip paused destinations — we're not interacting with them in
			// either direction while paused, so leave the remote files alone.
			// When the user un-pauses, retention will pick them up on the next
			// sweep if the backup is still expired.
			if ( ! empty( $dest->paused ) ) {
				if ( $logger ) {
					$logger->info( "Skipping retention delete on paused destination {$dest->name}", [], $this->ext_id );
				}
				continue;
			}
			// Skip destinations already known to be broken. The status flips
			// to 'error' on the first auth/connection failure (see below) so
			// every subsequent retention sweep can short-circuit instead of
			// re-attempting the same broken connection — which is what
			// triggered the user-reported "FTP banned my IP" cascade. The
			// user fixes the destination, Test flips status back to
			// 'connected', retention picks up on the next run.
			if ( 'error' === $dest->status ) {
				if ( $logger ) {
					$logger->info( "Skipping retention delete on errored destination {$dest->name}; re-test the destination to clear", [], $this->ext_id );
				}
				continue;
			}
			try {
				$handler = $manager->handler( $dest->type );
				$skip_rest_of_destination = false;
				$skipped_count            = 0;
				foreach ( $names as $name ) {
					if ( $skip_rest_of_destination ) {
						$skipped_count++;
						continue;
					}
					try { $handler->delete( $dest, $name ); }
					catch ( \Throwable $e ) {
						$msg = $e->getMessage();
						if ( self::looks_like_destination_unreachable( $msg ) ) {
							// Connection refused, DNS failure, auth rejection,
							// network unreachable — every subsequent file on
							// this destination is going to fail with the same
							// error. Spamming N identical warns per backup is
							// pure noise; emit one consolidated line and skip
							// the rest. The destination itself is the problem;
							// fix it in Destinations and re-run retention.
							if ( $logger ) {
								$logger->warn(
									"Skipping remaining file deletes on {$dest->name}; destination unreachable: {$msg}",
									[ 'files_remaining' => count( $names ) - $skipped_count ],
									$this->ext_id
								);
							}
							// Mark the destination as in error so the UI shows
							// a red badge and prompts the user to reconnect /
							// re-enter credentials. Throttled by a transient
							// so we don't churn the row on every backup delete.
							if ( ! get_transient( 'backreload_dest_error_marked_' . $dest->ext_id ) ) {
								set_transient( 'backreload_dest_error_marked_' . $dest->ext_id, 1, 5 * MINUTE_IN_SECONDS );
								$dest->status = 'error';
								try { $dest->save(); } catch ( \Throwable $ignored ) {}
							}
							$skip_rest_of_destination = true;
							$skipped_count++;
							continue;
						}
						// Per-file failure that isn't a destination outage —
						// log it and keep going (the file may have been
						// deleted out-of-band, permission set on one path,
						// etc.).
						if ( $logger ) {
							$logger->warn(
								"Couldn't delete {$name} from {$dest->name}: {$msg}",
								[],
								$this->ext_id
							);
						}
					}
				}
			} catch ( \Throwable $e ) {
				if ( $logger ) {
					$logger->warn( "Destination handler error during delete: " . $e->getMessage(), [], $this->ext_id );
				}
			}
		}

		$this->delete();
	}

	/**
	 * Does this error message look like the destination itself is unreachable
	 * (vs. a per-file permission / path issue)? Used to short-circuit retention
	 * deletes against a dead destination instead of churning one log line per
	 * file. Matches the common cURL / PHP / ssh2 / FTP wording.
	 */
	private static function looks_like_destination_unreachable( string $msg ): bool {
		$needles = [
			'cannot connect',
			'connection refused',
			'connection timed out',
			'no route to host',
			'network is unreachable',
			'name or service not known',
			'could not resolve host',
			'authentication failed',
			'auth failed',
			'login authentication failed',
			'access denied',         // S3 IAM
			'invalidaccesskeyid',    // S3 wrong key
			'signaturedoesnotmatch', // S3 wrong secret
			'forbidden',
			'bucket not reachable',
		];
		$low = strtolower( $msg );
		foreach ( $needles as $n ) {
			if ( false !== strpos( $low, $n ) ) return true;
		}
		return false;
	}

	/**
	 * Flip the DB status to 'cancelling' with a partial UPDATE that touches only the
	 * status column. This avoids the last-write-wins race where the engine's full
	 * save() (running on a parallel request via wp-cron) overwrites the cancel flag
	 * along with all the other in-memory fields it's mid-writing.
	 *
	 * Returns true if exactly one row was flipped (only meaningful when the row was
	 * in pending/running — the WHERE clause restricts to that to avoid stomping
	 * already-terminal states).
	 */
	public static function flag_cancel( string $ext_id ): bool {
		global $wpdb;
		$table = self::table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from self::table() = $wpdb->prefix (never user input), cannot be bound via prepare(); all data values use %s placeholders; updates the plugin's own table, transient and uncacheable.
		$rows  = $wpdb->query( $wpdb->prepare(
			"UPDATE {$table} SET status = 'cancelling'
			 WHERE ext_id = %s AND status IN ('pending','running')",
			$ext_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return is_int( $rows ) && $rows > 0;
	}

	/**
	 * Atomically claim this record for execution. Sets status='running' iff
	 * the current status is pending/running. Returns true if WE got the claim,
	 * false if another worker already has it (or the row is terminal).
	 *
	 * Prevents two cron workers (or one cron + one WP-CLI) from racing on the
	 * same backup record — the second caller will see false and back off.
	 */
	public static function claim_for_execution( string $ext_id ): bool {
		global $wpdb;
		$table = self::table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from self::table() = $wpdb->prefix (never user input), cannot be bound via prepare(); all data values use %s placeholders; updates the plugin's own table, transient and uncacheable.
		$rows = $wpdb->query( $wpdb->prepare(
			"UPDATE {$table} SET status = 'running'
			 WHERE ext_id = %s AND status IN ('pending','running')",
			$ext_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return is_int( $rows ) && $rows > 0;
	}

	public static function find( string $ext_id ): ?self {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin reads/writes its own custom tables; results are transient backup state and must not be cached. Interpolated identifier is a table name derived from $wpdb->prefix (never user input) and cannot be bound via prepare(); all data values use %s/%d placeholders.
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM ' . self::table() . ' WHERE ext_id = %s', $ext_id ), ARRAY_A );
		return $row ? self::from_row( $row ) : null;
	}

	/**
	 * Return successful backups of $type beyond the first $keep_recent, ordered
	 * newest-first. The result is the set retention should DELETE.
	 *
	 * Single targeted query — the previous `all(999) → filter in PHP → slice`
	 * approach was an N+1 walking pattern as soon as a site had >100 records.
	 *
	 * @return self[]
	 */
	public static function successful_older_than( string $type, int $keep_recent ): array {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin reads/writes its own custom tables; results are transient backup state and must not be cached.
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Interpolated identifier is a table name derived from $wpdb->prefix (never user input) and cannot be bound via prepare(); all data values use %s/%d placeholders.
		$rows = $wpdb->get_results( $wpdb->prepare(
			'SELECT * FROM ' . self::table() .
			' WHERE type = %s AND status = %s' .
			// Order by creation time, NOT insert id. They usually agree, but an
			// IMPORTED backup gets a fresh (high) auto-increment id while
			// carrying an OLD created_at — ordering by id would rank it as
			// "newest" and preferentially keep it over genuinely recent
			// backups, pruning the wrong ones. created_at is the true age;
			// id is only a stable tiebreaker for identical timestamps.
			' ORDER BY created_at DESC, id DESC' .
			// Use the LIMIT/OFFSET overflow trick: MySQL needs a finite LIMIT
			// after OFFSET; PHP_INT_MAX works portably as a "no limit" sentinel.
			' LIMIT ' . PHP_INT_MAX . ' OFFSET %d',
			$type,
			'success',
			$keep_recent
		), ARRAY_A );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return array_map( [ self::class, 'from_row' ], $rows ?: [] );
	}

	/**
	 * @param string|null $exclude_kind Hide records with this kind value. The
	 *   UI's main Backups list passes 'migrating' so the side-effect records
	 *   spawned by site-to-site migration don't pollute the regular list —
	 *   they're not useful as restorable backups (point at the source's URL,
	 *   not the destination's; visible from the Migrate page instead).
	 * @return self[]
	 */
	public static function all( int $limit = 100, int $offset = 0, ?string $type = null, ?string $exclude_kind = null ): array {
		global $wpdb;
		// The stale-record sweep used to fire on EVERY call here — meaning
		// every dashboard refresh + every page of the Backups list did a
		// throttled DB sweep just to discover "nothing to do yet". The sweep
		// now runs on a dedicated hourly cron (backreload_stale_sweep, set up
		// by the Scheduler) plus a manual invocation from the post-restore
		// path in RestoreEngine. Public callers of all() pay the listing
		// cost only.

		$where = '1=1';
		$args  = [];
		if ( $type && 'all' !== $type ) {
			$where .= ' AND type = %s';
			$args[] = $type;
		}
		if ( $exclude_kind ) {
			$where .= ' AND kind != %s';
			$args[] = $exclude_kind;
		}
		$args[] = $limit;
		$args[] = $offset;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin reads/writes its own custom tables; results are transient backup state and must not be cached.
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Interpolated identifier is a table name derived from $wpdb->prefix (never user input) and cannot be bound via prepare(); all data values use %s/%d placeholders.
		$rows = $wpdb->get_results( $wpdb->prepare(
			'SELECT * FROM ' . self::table() . " WHERE {$where} ORDER BY id DESC LIMIT %d OFFSET %d",
			$args
		), ARRAY_A );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return array_map( [ self::class, 'from_row' ], $rows ?: [] );
	}

	/**
	 * Sweep orphaned 'pending' / 'running' records. A record is orphaned when
	 * the engine that owned it died (PHP fatal, host kill, hard timeout) so
	 * the row sits in the table forever — the UI then shows a permanent
	 * "running" badge and the floating panel chases a backup nothing is
	 * actually working on.
	 *
	 * Two-tier check, in order of confidence:
	 *
	 *   1. If `backreload_tick_lock_<ext_id>` is missing or stale AND the row is
	 *      older than the age floor, finalize it. The tick mutex is bumped
	 *      each tick (shutdown handler clears it on fatal), so a missing or
	 *      stale lock means no engine is touching the record.
	 *   2. Hard backstop: anything older than 6 hours with no engine activity
	 *      gets cleared regardless — covers edge cases where the transient
	 *      check is unreliable (object cache flush, etc).
	 *
	 * Throttled to once a minute so list-page renders don't hammer the DB.
	 */
	/**
	 * Run one pass of stale-record detection. Public because Scheduler's
	 * hourly cron hook calls it, and RestoreEngine calls it once after a
	 * SQL replay (with the `backreload_post_restore_pass` transient lowering the
	 * age floor — see the in-method comment).
	 *
	 * The old REST-piggyback throttle (`backreload_stale_swept` transient) is
	 * gone: the cron handler is the rate limiter now, so we don't pay an
	 * extra get/set_transient round-trip per dashboard refresh just to
	 * discover the sweep already ran. Manual callers (the post-restore
	 * pass) get one direct, unthrottled call — they're not on a hot path.
	 */
	public static function sweep_stale(): void {
		global $wpdb;
		// Default: 6 minutes ≥ the 5-minute tick lock TTL. A backup that's
		// been running longer than 6 minutes WITHOUT an active tick lock
		// is unambiguously orphaned.
		//
		// Exception: when the post-restore sweep clears `backreload_stale_swept`
		// and calls this directly, the user has just done a restore and
		// the records are typically much younger than 6 minutes (snapshot
		// might have been taken seconds ago). In that case the tick locks
		// have ALSO been explicitly purged by the restore engine, so the
		// "no tick lock" signal is reliable from the moment the restore
		// completes. Detect that case via a sibling transient set by the
		// restore engine, and lower the age floor to zero so the just-
		// restored running rows get heal-or-failed immediately.
		$post_restore_pass = get_transient( 'backreload_post_restore_pass' );
		$age_floor_seconds = $post_restore_pass ? 0 : ( 6 * MINUTE_IN_SECONDS );
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin reads/writes its own custom tables; results are transient backup state and must not be cached.
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Interpolated identifier is a table name derived from $wpdb->prefix (never user input) and cannot be bound via prepare(); all data values use %s/%d placeholders.
		$candidates = $wpdb->get_results( $wpdb->prepare(
			'SELECT ext_id, files, destinations FROM ' . self::table() . "
			 WHERE status IN ( 'pending', 'running', 'cancelling' )
			 AND created_at < %s",
			gmdate( 'Y-m-d H:i:s', time() - $age_floor_seconds )
		), ARRAY_A );
		if ( empty( $candidates ) ) return;

		$now           = current_time( 'mysql', true );
		$hard_backstop = time() - 6 * HOUR_IN_SECONDS;
		foreach ( $candidates as $row ) {
			$ext_id = (string) $row['ext_id'];
			// Skip records whose engine is genuinely still ticking. The lock
			// is a non-transient option storing the acquire time(); we treat
			// anything younger than the 5-minute lock TTL as "live". A stale
			// lock (engine PHP-fatalled before its shutdown handler could
			// clear it) is treated as "no live tick" so the heal-or-fail
			// logic below kicks in. The 300s magic number mirrors
			// BackupEngine::TICK_LOCK_TTL_SECS — kept inline here to avoid a
			// model→engine dependency.
			$held_at = (int) get_option( 'backreload_tick_lock_' . $ext_id, 0 );
			if ( $held_at > 0 && ( time() - $held_at ) <= 300 ) {
				continue;
			}

			// Skip records a site-to-site migration is actively pushing. The
			// migration push engine (not the backup tick loop) owns these, and
			// its progress lives in a separate `backreload_migration_state_*`
			// option — the backup tick lock isn't refreshed during the push, so
			// without this a long/slow migration would look "orphaned" and get
			// falsely marked failed even though it's progressing fine.
			$mig = get_option( 'backreload_migration_state_' . $ext_id, null );
			if ( is_array( $mig ) && ! in_array( (string) ( $mig['phase'] ?? '' ), [ 'complete', 'failed', 'aborted', 'cancelled' ], true ) ) {
				continue;
			}

			// Heal-not-fail heuristic: if every file recorded on this backup
			// has been marked uploaded to at least one destination, the
			// engine actually completed its work — it just died before
			// flipping status to 'success'. Mark it success so the user
			// sees the correct end state instead of a confusing "running"
			// or "failed" row with files objectively present on disk.
			// (The recurring "after restore I see a running backup but the
			// files are on SFTP" report — engine completed uploads, snapshot
			// replay restored the row in its mid-flight state.)
			$files = json_decode( (string) ( $row['files'] ?? '[]' ), true ) ?: [];
			$looks_complete = false;
			if ( ! empty( $files ) ) {
				$all_uploaded = true;
				foreach ( $files as $f ) {
					if ( empty( $f['uploaded_to'] ) || ! is_array( $f['uploaded_to'] ) ) {
						$all_uploaded = false;
						break;
					}
				}
				$looks_complete = $all_uploaded;
			}
			if ( $looks_complete ) {
				$wpdb->update(
					self::table(),
					[
						'status'       => 'success',
						'error'        => null,
						'completed_at' => $now,
					],
					[ 'ext_id' => $ext_id ]
				);
				delete_transient( 'backreload_settings_snap_' . $ext_id );
				continue;
			}

			// Otherwise: no tick lock + 6+ minutes old + parts not all
			// uploaded → genuinely orphan, mark failed.
			$wpdb->update(
				self::table(),
				[
					'status'       => 'failed',
					'error'        => 'No engine activity for 15+ minutes, the previous tick likely died without finalizing this backup. Use Resume to retry, or delete it.',
					'completed_at' => $now,
				],
				[ 'ext_id' => $ext_id ]
			);
			// Clean up any settings snapshot transient that would otherwise
			// linger for the full 24h TTL.
			delete_transient( 'backreload_settings_snap_' . $ext_id );
		}
		// Hard-backstop pass — defensive only, the per-row check above should
		// already have caught these.
		$wpdb->query( $wpdb->prepare(
			'UPDATE ' . self::table() . " SET status = 'failed', error = %s, completed_at = %s
			 WHERE status IN ( 'pending', 'running', 'cancelling' )
			 AND created_at < %s",
			'Timed out, the process did not finish within the expected window.',
			$now,
			gmdate( 'Y-m-d H:i:s', $hard_backstop )
		) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	public static function from_row( array $row ): self {
		$r               = new self();
		$r->id           = (int) $row['id'];
		$r->ext_id       = (string) $row['ext_id'];
		$r->type         = (string) $row['type'];
		$r->kind         = (string) ( $row['kind'] ?? 'manual' );
		$r->status       = (string) $row['status'];
		$r->size         = (int) $row['size'];
		$r->duration     = (int) $row['duration'];
		$r->encrypted    = (bool) $row['encrypted'];
		$r->destinations = json_decode( $row['destinations'] ?? '[]', true ) ?: [];
		$r->components   = json_decode( $row['components']   ?? '[]', true ) ?: [];
		$r->files        = json_decode( $row['files']        ?? '[]', true ) ?: [];
		$r->checksum     = $row['checksum'];
		$r->error        = $row['error'];
		$r->source_url   = $row['source_url']  ?? null;
		$r->source_path  = $row['source_path'] ?? null;
		$r->created_at   = $row['created_at'];
		$r->completed_at = $row['completed_at'];
		return $r;
	}

	public function to_array(): array {
		return [
			'id'           => $this->ext_id,
			'type'         => $this->type,
			'kind'         => $this->kind,
			'status'       => $this->status,
			'size'         => $this->size,
			'duration'     => $this->duration,
			'encrypted'    => $this->encrypted,
			'destinations' => $this->destinations,
			'components'   => $this->components,
			'checksum'     => $this->checksum,
			'error'        => $this->error,
			'source_url'   => $this->source_url,
			'source_path'  => $this->source_path,
			'files'        => $this->files,
			'date'         => $this->created_at ? mysql2date( 'c', $this->created_at . ' UTC', false ) : null,
			'completed_at' => $this->completed_at,
		];
	}

	/* ─── Multi-zip helpers ────────────────────────────────────────── */

	/** Record a newly-created archive part. */
	public function add_file( string $name, string $category, int $size, string $checksum, bool $encrypted = false ): void {
		$this->files[] = [
			'name'        => $name,
			'category'    => $category,
			'size'        => $size,
			'checksum'    => $checksum,
			'encrypted'   => $encrypted,
			'uploaded_to' => [],
		];
		$this->size = array_sum( array_column( $this->files, 'size' ) );
	}

	/** Mark a file as successfully uploaded to a destination (idempotent). */
	public function mark_uploaded( string $file_name, string $destination_ext_id ): void {
		foreach ( $this->files as &$f ) {
			if ( $f['name'] === $file_name ) {
				$f['uploaded_to'] = array_values( array_unique( array_merge( $f['uploaded_to'] ?? [], [ $destination_ext_id ] ) ) );
				return;
			}
		}
	}

	/** Has this file been uploaded to the given destination already? Used for resumption. */
	public function is_uploaded( string $file_name, string $destination_ext_id ): bool {
		foreach ( $this->files as $f ) {
			if ( $f['name'] === $file_name ) {
				return in_array( $destination_ext_id, $f['uploaded_to'] ?? [], true );
			}
		}
		return false;
	}

	/** Files for a given category, in order. */
	public function files_in( string $category ): array {
		return array_values( array_filter( $this->files, fn( $f ) => ( $f['category'] ?? '' ) === $category ) );
	}
}
