<?php
namespace TeknoTok\AiTranslator\Database;

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

/**
 * Repository to interface with the background translation jobs table.
 */
class JobRepository {
	/**
	 * Flag to track if a translation job was added in the current request session.
	 *
	 * @var bool
	 */
	public static $job_added_this_request = false;
	/**
	 * Get table name.
	 *
	 * @return string
	 */
	private static function get_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'teknotok_jobs';
	}

	/**
	 * Add a translation job to the queue.
	 *
	 * @param string $object_type Object type ('post', 'taxonomy', 'string', etc.)
	 * @param int    $object_id   Database ID of the object.
	 * @param string $target_lang  Language code.
	 * @param string $source_hash  Optional source content hash.
	 * @return int|bool Job ID on success, false on failure.
	 */
	public static function add_job( $object_type, $object_id, $target_lang, $source_hash = '' ) {
		global $wpdb;

		$source_hash = sanitize_text_field( (string) $source_hash );
		if ( '' === $source_hash && 'post' === $object_type ) {
			$source_hash = self::get_post_source_hash( $object_id );
		}

		if ( self::has_job( $object_type, $object_id, $target_lang, $source_hash ) ) {
			return true;
		}

		$table = self::get_table_name();

		$data = array(
			'object_type' => $object_type,
			'object_id'   => $object_id,
			'source_hash' => $source_hash ?: null,
			'target_lang' => $target_lang,
			'status'      => 'pending',
			'created_at'  => current_time( 'mysql', true ),
			'updated_at'  => current_time( 'mysql', true ),
		);

		$format = array( '%s', '%d', '%s', '%s', '%s', '%s', '%s' );

		$result = $wpdb->insert( $table, $data, $format );

		if ( false !== $result ) {
			$job_id = $wpdb->insert_id;
			self::$job_added_this_request = true;
			if ( self::should_trigger_async_queue() ) {
				\TeknoTok\AiTranslator\Queue\QueueManager::trigger_async_queue();
			}
			return $job_id;
		}

		return false;
	}

	/**
	 * Build a stable hash of post fields that can affect translations.
	 *
	 * @param int $post_id Post ID.
	 * @return string Source hash, or empty string when unavailable.
	 */
	public static function get_post_source_hash( $post_id ) {
		$post = get_post( $post_id );
		if ( ! $post ) {
			return '';
		}

		$seo_keys = array(
			'_yoast_wpseo_title',
			'_yoast_wpseo_metadesc',
			'_metaseo_description',
			'rank_math_title',
			'rank_math_description',
			'rank_math_focus_keyword',
		);

		$source = array(
			'title'   => (string) $post->post_title,
			'excerpt' => (string) $post->post_excerpt,
			'content' => (string) $post->post_content,
			'meta'    => array(),
		);

		foreach ( $seo_keys as $meta_key ) {
			$value = get_post_meta( $post_id, $meta_key, true );
			if ( is_scalar( $value ) && '' !== trim( (string) $value ) ) {
				$source['meta'][ $meta_key ] = (string) $value;
			}
		}

		return md5( wp_json_encode( $source ) );
	}

	/**
	 * Add a frontend/theme string translation job without forcing synchronous shutdown processing.
	 *
	 * @param string $source_text Original string.
	 * @param string $target_lang Target language code.
	 * @return int|bool Job ID on success, true if already queued/cached, false on failure.
	 */
	public static function add_string_job( $source_text, $target_lang ) {
		global $wpdb;

		$source_text = trim( (string) $source_text );
		$target_lang = sanitize_key( $target_lang );

		if ( empty( $source_text ) || empty( $target_lang ) ) {
			return false;
		}

		if ( class_exists( '\TeknoTok\AiTranslator\Utils\HtmlParser' ) && ! \TeknoTok\AiTranslator\Utils\HtmlParser::should_translate( $source_text, $target_lang ) ) {
			return true;
		}

		if ( false !== \TeknoTok\AiTranslator\Database\MemoryRepository::get_translation( $source_text, $target_lang ) ) {
			return true;
		}

		$hash = md5( $source_text );
		if ( self::has_string_job( $hash, $target_lang ) ) {
			return true;
		}

		$table = self::get_table_name();
		$result = $wpdb->insert(
			$table,
			array(
				'object_type' => 'string',
				'object_id'   => 0,
				'source_hash' => $hash,
				'source_text' => $source_text,
				'target_lang' => $target_lang,
				'status'      => 'pending',
				'created_at'  => current_time( 'mysql', true ),
				'updated_at'  => current_time( 'mysql', true ),
			),
			array( '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false !== $result ) {
			if ( self::should_trigger_async_queue() ) {
				\TeknoTok\AiTranslator\Queue\QueueManager::trigger_async_queue();
			}
			return $wpdb->insert_id;
		}

		return false;
	}

	/**
	 * Add a frontend URL warm-up job to discover theme/header/footer strings.
	 *
	 * @param string $source_url   Source-language URL to warm.
	 * @param string $target_lang  Target language code.
	 * @return int|bool Job ID on success, true if already queued/completed, false on failure.
	 */
	public static function add_frontend_warmup_job( $source_url, $target_lang ) {
		global $wpdb;

		$source_url  = esc_url_raw( (string) $source_url );
		$target_lang = sanitize_key( $target_lang );

		if ( empty( $source_url ) || empty( $target_lang ) ) {
			return false;
		}

		$source_hash = md5( 'frontend_warmup|' . $source_url );
		if ( self::has_job( 'frontend_warmup', 0, $target_lang, $source_hash ) ) {
			return true;
		}

		$table = self::get_table_name();
		$result = $wpdb->insert(
			$table,
			array(
				'object_type' => 'frontend_warmup',
				'object_id'   => 0,
				'source_hash' => $source_hash,
				'source_text' => $source_url,
				'target_lang' => $target_lang,
				'status'      => 'pending',
				'created_at'  => current_time( 'mysql', true ),
				'updated_at'  => current_time( 'mysql', true ),
			),
			array( '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false !== $result ) {
			if ( self::should_trigger_async_queue() ) {
				\TeknoTok\AiTranslator\Queue\QueueManager::trigger_async_queue();
			}
			return $wpdb->insert_id;
		}

		return false;
	}

	/**
	 * Check whether adding a job should immediately spawn the async queue worker.
	 *
	 * @return bool True when the async worker should be triggered.
	 */
	private static function should_trigger_async_queue() {
		if ( ! empty( $_GET['teknotok_ait_warmup'] ) ) {
			return false;
		}

		return empty( $_SERVER['HTTP_X_TEKNOTOK_WARMUP'] );
	}

	/**
	 * Check if a string translation job is already queued.
	 *
	 * @param string $source_hash Source text hash.
	 * @param string $target_lang Target language code.
	 * @return bool True if queued.
	 */
	public static function has_string_job( $source_hash, $target_lang ) {
		global $wpdb;
		$table = self::get_table_name();

		$query = $wpdb->prepare(
			"SELECT COUNT(*) FROM {$table} WHERE object_type = 'string' AND source_hash = %s AND target_lang = %s AND status IN ('pending', 'processing')",
			$source_hash,
			$target_lang
		);

		return (int) $wpdb->get_var( $query ) > 0;
	}

	/**
	 * Check if a job is already queued.
	 *
	 * @param string $object_type Object type.
	 * @param int    $object_id   Object ID.
	 * @param string $target_lang  Language.
	 * @param string $source_hash  Optional source content hash.
	 * @return bool True if queued (pending or processing), false otherwise.
	 */
	public static function has_job( $object_type, $object_id, $target_lang, $source_hash = '' ) {
		global $wpdb;
		$table = self::get_table_name();

		$source_hash = sanitize_text_field( (string) $source_hash );
		if ( '' !== $source_hash ) {
			$query = $wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE object_type = %s AND object_id = %d AND target_lang = %s AND source_hash = %s AND status IN ('pending', 'processing', 'completed')",
				$object_type,
				$object_id,
				$target_lang,
				$source_hash
			);
		} else {
			$query = $wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE object_type = %s AND object_id = %d AND target_lang = %s AND status IN ('pending', 'processing')",
				$object_type,
				$object_id,
				$target_lang
			);
		}

		return (int) $wpdb->get_var( $query ) > 0;
	}

	/**
	 * Get pending jobs.
	 *
	 * @param int $limit Max number of jobs to fetch.
	 * @return array List of pending job objects.
	 */
	public static function get_pending_jobs( $limit = 10 ) {
		global $wpdb;
		$table = self::get_table_name();

		$now = time();
		$cutoff_1 = gmdate( 'Y-m-d H:i:s', $now - 900 ); // 15 minutes backoff
		$cutoff_2 = gmdate( 'Y-m-d H:i:s', $now - 7200 ); // 2 hours backoff

		$query = $wpdb->prepare(
			"SELECT * FROM {$table} 
			WHERE status = 'pending' 
			   OR (
			      status = 'failed' 
			      AND retry_count < 3 
			      AND (
			         error_message IS NULL
			         OR (
			            error_message NOT LIKE %s
			            AND error_message NOT LIKE %s
			            AND error_message NOT LIKE %s
			            AND error_message NOT LIKE %s
			         )
			      )
			      AND (
			         (retry_count = 1 AND updated_at < %s) 
			         OR (retry_count = 2 AND updated_at < %s)
			      )
			   )
			ORDER BY CASE status WHEN 'pending' THEN 0 ELSE 1 END ASC, id ASC LIMIT %d",
			'%quota%exceeded%',
			'%limit%exceeded%',
			'%word%limit%exceeded%',
			'%license%',
			$cutoff_1,
			$cutoff_2,
			$limit
		);

		return $wpdb->get_results( $query );
	}

	/**
	 * Reset jobs that have been stuck in 'processing' status for too long.
	 *
	 * @param int $timeout_seconds Timeout limit in seconds. Default is 300 (5 minutes).
	 * @return int Number of reset jobs.
	 */
	public static function reset_stuck_jobs( $timeout_seconds = 300 ) {
		global $wpdb;
		$table = self::get_table_name();

		$cutoff = time() - $timeout_seconds;
		$cutoff_mysql = gmdate( 'Y-m-d H:i:s', $cutoff );

		$query = $wpdb->prepare(
			"UPDATE {$table} SET status = 'failed', error_message = %s, retry_count = retry_count + 1, updated_at = %s WHERE status = 'processing' AND updated_at < %s",
			__( 'Job timed out or execution terminated unexpectedly.', 'teknotok-ai-translator-pro' ),
			current_time( 'mysql', true ),
			$cutoff_mysql
		);

		return $wpdb->query( $query );
	}

	/**
	 * Reset jobs that failed because the gateway quota/license limit was reached.
	 *
	 * @return int Number of reset jobs.
	 */
	public static function reset_quota_failed_jobs() {
		global $wpdb;
		$table = self::get_table_name();

		$now = current_time( 'mysql', true );

		return $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET status = 'pending', retry_count = 0, error_message = NULL, updated_at = %s
				WHERE status = 'failed'
				AND (
					error_message LIKE %s
					OR error_message LIKE %s
					OR error_message LIKE %s
					OR error_message LIKE %s
				)",
				$now,
				'%quota%exceeded%',
				'%limit%exceeded%',
				'%word%limit%exceeded%',
				'%license%'
			)
		);
	}

	/**
	 * Reset non-quota failed jobs after server or timeout issues have been fixed.
	 *
	 * @return int Number of reset jobs.
	 */
	public static function reset_retryable_failed_jobs() {
		global $wpdb;
		$table = self::get_table_name();

		$now = current_time( 'mysql', true );

		return $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET status = 'pending', retry_count = 0, error_message = NULL, updated_at = %s
				WHERE status = 'failed'
				AND (
					error_message IS NULL
					OR (
						error_message NOT LIKE %s
						AND error_message NOT LIKE %s
						AND error_message NOT LIKE %s
						AND error_message NOT LIKE %s
					)
				)",
				$now,
				'%quota%exceeded%',
				'%limit%exceeded%',
				'%word%limit%exceeded%',
				'%license%'
			)
		);
	}

	/**
	 * Count failed jobs that are safe to retry manually.
	 *
	 * @return int Number of non-quota failed jobs.
	 */
	public static function get_retryable_failed_count() {
		global $wpdb;
		$table = self::get_table_name();

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table}
				WHERE status = 'failed'
				AND (
					error_message IS NULL
					OR (
						error_message NOT LIKE %s
						AND error_message NOT LIKE %s
						AND error_message NOT LIKE %s
						AND error_message NOT LIKE %s
					)
				)",
				'%quota%exceeded%',
				'%limit%exceeded%',
				'%word%limit%exceeded%',
				'%license%'
			)
		);
	}

	/**
	 * Update job status.
	 *
	 * @param int    $id            Job ID.
	 * @param string $status        New status ('pending', 'processing', 'completed', 'failed').
	 * @param string $error_message Optional error message for failures.
	 * @return bool True on success, false on failure.
	 */
	public static function update_status( $id, $status, $error_message = null ) {
		global $wpdb;
		$table = self::get_table_name();

		$data = array(
			'status'     => $status,
			'updated_at' => current_time( 'mysql', true ),
		);
		$format = array( '%s', '%s' );

		if ( 'failed' === $status ) {
			$data['error_message'] = $error_message;
			$format[] = '%s';

			// Increment retry count
			$wpdb->query( $wpdb->prepare( "UPDATE {$table} SET retry_count = retry_count + 1 WHERE id = %d", $id ) );
		}

		$result = $wpdb->update(
			$table,
			$data,
			array( 'id' => $id ),
			$format,
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Get total counts by status.
	 *
	 * @return array
	 */
	public static function get_counts() {
		global $wpdb;
		$table = self::get_table_name();

		$results = $wpdb->get_results( "SELECT status, COUNT(*) as count FROM {$table} GROUP BY status", OBJECT_K );

		return array(
			'pending'    => isset( $results['pending'] ) ? (int) $results['pending']->count : 0,
			'processing' => isset( $results['processing'] ) ? (int) $results['processing']->count : 0,
			'completed'  => isset( $results['completed'] ) ? (int) $results['completed']->count : 0,
			'failed'     => isset( $results['failed'] ) ? (int) $results['failed']->count : 0,
		);
	}

	/**
	 * Clear all background jobs.
	 *
	 * @return void
	 */
	public static function clear_queue() {
		global $wpdb;
		$table = self::get_table_name();
		$wpdb->query( "TRUNCATE TABLE {$table}" );
	}
}
