<?php

declare(strict_types=1);

namespace SiteFort\Modules\Scanner\Repository;

// If this file is called directly, abort.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Cloud Job Repository - Database Access Layer for cloud_jobs table
 *
 * Provides CRUD operations for tracking Intel Engine scan jobs.
 *
 */
class CloudJobRepository {

	/**
	 * Guard to avoid repeated schema checks in the same request.
	 *
	 * @var bool
	 */
	private static $index_checked = false;

	/**
	 * WordPress database object.
	 *
	 * @var \wpdb
	 */
	private $wpdb;

	/**
	 * Table name.
	 *
	 * @var string
	 */
	private $table_name;

	/**
	 * Constructor.
	 */
	public function __construct() {
		global $wpdb;
		$this->wpdb       = $wpdb;
		$this->table_name = $wpdb->prefix . 'sitefort_cloud_jobs';
		$this->ensure_scan_status_index();
	}

	/**
	 * Create a new cloud job record.
	 *
	 * @param array $data Job data.
	 * @return int|false Inserted ID or false on failure.
	 */
	public function create( array $data ) {
		$defaults = [
			'status'     => 'queued',
			'created_at' => time(),
		];

		$data = array_merge( $defaults, $data );

		$result = $this->wpdb->insert(
			$this->table_name,
			$data,
			$this->get_format_array( $data )
		);

		return $result ? $this->wpdb->insert_id : false;
	}

	/**
	 * Get a cloud job by ID.
	 *
	 * @param int $id Job ID.
	 * @return array|null Job record or null.
	 */
	public function get_by_id( int $id ): ?array {
		$result = $this->get_row_prepared(
			"SELECT * FROM {$this->table_identifier()} WHERE id = %d",
			$id
		);

		return $result ?: null;
	}

	/**
	 * Get a cloud job by job_id (Intel Engine job ID).
	 *
	 * @param string $job_id Intel Engine job ID.
	 * @return array|null Job record or null.
	 */
	public function get_by_job_id( string $job_id ): ?array {
		$result = $this->get_row_prepared(
			"SELECT * FROM {$this->table_identifier()} WHERE job_id = %s",
			$job_id
		);

		return $result ?: null;
	}

	/**
	 * Update a cloud job record.
	 *
	 * @param string $job_id Intel Engine job ID.
	 * @param array  $data   Data to update.
	 * @return bool Success.
	 */
	public function update( string $job_id, array $data ): bool {
		$result = $this->wpdb->update(
			$this->table_name,
			$data,
			[ 'job_id' => $job_id ],
			$this->get_format_array( $data ),
			[ '%s' ]
		);

		return false !== $result;
	}

	/**
	 * Get jobs by scan ID.
	 *
	 * @param string $scan_id Scan UUID.
	 * @return array Job records.
	 */
	public function get_by_scan_id( string $scan_id ): array {
		$results = $this->get_results_prepared(
			"SELECT * FROM {$this->table_identifier()}
			WHERE scan_id = %s
			ORDER BY created_at DESC",
			$scan_id
		);

		if ( $results === null ) {
			sitefort_log( '[SiteFort] DB error in get_by_scan_id: ' . $this->wpdb->last_error );
			return [];
		}

		return $results;
	}

	/**
	 * Count jobs for a scan across one or more statuses.
	 *
	 * @param string $scan_id  Scan UUID.
	 * @param array  $statuses Status list.
	 * @return int
	 */
	public function count_by_scan_and_statuses( string $scan_id, array $statuses ): int {
		$statuses = array_values(
			array_filter(
				array_map(
					static fn( $status ): string => sanitize_text_field( (string) $status ),
					$statuses
				),
				static fn( string $status ): bool => '' !== $status
			)
		);

		if ( empty( $statuses ) ) {
			return 0;
		}

		$placeholders = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );
		$params       = array_merge(
			[ $scan_id ],
			$statuses
		);

		$count = $this->get_var_prepared(
			"SELECT COUNT(*)
			FROM {$this->table_identifier()}
			WHERE scan_id = %s
			AND status IN ({$placeholders})",
			...$params
		);

		return (int) $count;
	}

	/**
	 * Get jobs by status.
	 *
	 * @param string $status Job status.
	 * @param int    $limit  Limit.
	 * @return array Job records.
	 */
	public function get_by_status( string $status, int $limit = 50 ): array {
		$results = $this->get_results_prepared(
			"SELECT * FROM {$this->table_identifier()}
			WHERE status = %s
			ORDER BY created_at ASC
			LIMIT %d",
			$status,
			$limit
		);

		if ( $results === null ) {
			sitefort_log( '[SiteFort] DB error in get_by_status: ' . $this->wpdb->last_error );
			return [];
		}

		return $results;
	}

	/**
	 * Get timed-out jobs (in scanning state > timeout_at).
	 *
	 * @return array Job records.
	 */
	public function get_timed_out_jobs(): array {
		$results = $this->get_results_prepared(
			"SELECT * FROM {$this->table_identifier()}
			WHERE status IN ('submitted', 'processing')
			AND timeout_at IS NOT NULL
			AND timeout_at < %d
			ORDER BY timeout_at ASC
			LIMIT 100",
			time()
		);

		if ( $results === null ) {
			sitefort_log( '[SiteFort] DB error in get_timed_out_jobs: ' . $this->wpdb->last_error );
			return [];
		}

		return $results;
	}

	/**
	 * Get jobs needing polling (not retrieved yet).
	 *
	 * @param string $scan_id Scan UUID - REQUIRED for scoping (defense-in-depth).
	 * @return array Job records.
	 */
	public function get_jobs_needing_poll( string $scan_id ): array {
		if ( empty( $scan_id ) ) {
			return []; // Never poll without explicit scan scope
		}

		$results = $this->get_results_prepared(
			"SELECT * FROM {$this->table_identifier()}
			WHERE scan_id = %s
			AND status IN ('submitted', 'processing')
			AND (last_poll_at IS NULL OR last_poll_at < %d)
			ORDER BY submitted_at ASC
			LIMIT 50",
			$scan_id,
			time() - 30
		);

		if ( $results === null ) {
			sitefort_log( '[SiteFort] DB error in get_jobs_needing_poll: ' . $this->wpdb->last_error );
			return [];
		}

		return $results;
	}

	public function get_active_jobs_for_scan( string $scan_id, int $limit = 50 ): array {
		if ( empty( $scan_id ) ) {
			return [];
		}

		$limit = max( 1, min( 50, $limit ) );
		$results = $this->get_results_prepared(
			"SELECT * FROM {$this->table_identifier()}
			WHERE scan_id = %s
			AND status IN ('submitted', 'processing')
			ORDER BY submitted_at ASC
			LIMIT %d",
			$scan_id,
			$limit
		);

		if ( $results === null ) {
			sitefort_log( '[SiteFort] DB error in get_active_jobs_for_scan: ' . $this->wpdb->last_error );
			return [];
		}

		return $results;
	}

	/**
	 * Delete jobs by scan ID.
	 *
	 * @param string $scan_id Scan UUID.
	 * @return bool Success.
	 */
	public function delete_by_scan_id( string $scan_id ): bool {
		$result = $this->wpdb->delete(
			$this->table_name,
			[ 'scan_id' => $scan_id ],
			[ '%s' ]
		);

		return false !== $result;
	}

	/**
	 * Get format array for wpdb operations based on data types.
	 *
	 * @param array $data Data array.
	 * @return array Format array.
	 */
	private function get_format_array( array $data ): array {
		$formats = [];

		foreach ( $data as $value ) {
			if ( is_int( $value ) ) {
				$formats[] = '%d';
			} elseif ( is_float( $value ) ) {
				$formats[] = '%f';
			} else {
				$formats[] = '%s';
			}
		}

		return $formats;
	}

	/**
	 * Ensure composite index for scan status aggregation exists.
	 *
	 * Keeps worker aggregation queries fast under large scan volumes.
	 *
	 * @return void
	 */
	private function ensure_scan_status_index(): void {
		if ( self::$index_checked ) {
			return;
		}

		self::$index_checked = true;

		$index = $this->get_var_prepared(
			"SELECT INDEX_NAME
			FROM information_schema.statistics
			WHERE table_schema = DATABASE()
			AND table_name = %s
			AND index_name = %s
			LIMIT 1",
			$this->table_name,
			'idx_scan_status'
		);

		if ( $index ) {
			return;
		}

		// Add composite index to reduce full scans during status aggregation.
		$this->query_prepared(
			"ALTER TABLE {$this->table_identifier()} ADD INDEX idx_scan_status (scan_id, status)"
		);
	}

	/**
	 * Clear all actively running/polling jobs.
	 * Used when starting a fresh scan.
	 *
	 * @return bool Success.
	 */
	public function clear_all_active(): bool {
		$result = $this->query_prepared(
			"DELETE FROM {$this->table_identifier()}
			WHERE status IN ('submitted', 'processing', 'queued')"
		);

		return false !== $result;
	}

	private function table_identifier(): string {
		if ( $this->table_name === '' || strlen( $this->table_name ) > 64 || ! preg_match( '/^[A-Za-z0-9_]+$/', $this->table_name ) ) {
			throw new \InvalidArgumentException( 'Invalid table identifier.' );
		}

		return '`' . str_replace( '`', '``', $this->table_name ) . '`';
	}

	private function get_results_prepared( string $sql, ...$params ): ?array {
		$prepared = $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return null;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			$results = $this->wpdb->get_results( $prepared, ARRAY_A );
		return is_array( $results ) ? $results : null;
	}

	private function get_row_prepared( string $sql, ...$params ): ?array {
		$prepared = $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return null;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			$result = $this->wpdb->get_row( $prepared, ARRAY_A );
		return is_array( $result ) ? $result : null;
	}

	private function get_var_prepared( string $sql, ...$params ) {
		$prepared = $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return null;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			return $this->wpdb->get_var( $prepared );
	}

	private function query_prepared( string $sql, ...$params ) {
		$prepared = empty( $params ) ? $sql : $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return false;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			return $this->wpdb->query( $prepared );
	}

	private function prepare_sql( string $sql, ...$params ): string {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Bound with wpdb::prepare().
			$prepared = $this->wpdb->prepare( $sql, ...$params );
		return is_string( $prepared ) ? $prepared : '';
	}
}
