<?php

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

final class NPATD_Job_Store {
	const TABLE_SUFFIX = 'npatd_runs';

	private static $table_ready = null;

	private static function table_name(): string {
		global $wpdb;

		return $wpdb->prefix . self::TABLE_SUFFIX;
	}

	public static function install(): void {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		global $wpdb;

		$table_name      = self::table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			run_id varchar(64) NOT NULL,
			external_run_id varchar(191) NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			response_json longtext NULL,
			last_error_code varchar(100) NULL,
			last_error_message text NULL,
			started_at datetime NULL,
			completed_at datetime NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY run_id (run_id),
			UNIQUE KEY external_run_id (external_run_id),
			KEY status (status)
		) {$charset_collate};";

		dbDelta( $sql );

		self::$table_ready = self::table_exists();
	}

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

		$table_name = self::table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- required for runtime table existence check.
		$found      = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );

		return is_string( $found ) && $table_name === $found;
	}

	private static function ensure_table(): bool {
		if ( true === self::$table_ready ) {
			return true;
		}

		if ( false === self::$table_ready ) {
			return false;
		}

		if ( self::table_exists() ) {
			self::$table_ready = true;

			return true;
		}

		self::install();

		return true === self::$table_ready;
	}

	private static function now_mysql_utc(): string {
		return current_time( 'mysql', true );
	}

	private static function map_key( string $external_run_id ): string {
		return 'npatd_run_map_' . md5( $external_run_id );
	}

	private static function status_key( string $run_id ): string {
		return 'npatd_run_status_' . $run_id;
	}

	private static function response_key( string $run_id ): string {
		return 'npatd_run_response_' . $run_id;
	}

	private static function cache_run( string $external_run_id, string $run_id, string $status, array $response ): void {
		set_transient( self::map_key( $external_run_id ), $run_id, HOUR_IN_SECONDS );
		set_transient( self::status_key( $run_id ), $status, HOUR_IN_SECONDS );
		set_transient( self::response_key( $run_id ), $response, HOUR_IN_SECONDS );
	}

	private static function update_cache_without_external( string $run_id, string $status, array $response ): void {
		set_transient( self::status_key( $run_id ), $status, HOUR_IN_SECONDS );
		set_transient( self::response_key( $run_id ), $response, HOUR_IN_SECONDS );
	}

	private static function find_or_create_run_transient( string $external_run_id ): string {
		$existing = get_transient( self::map_key( $external_run_id ) );
		if ( is_string( $existing ) && '' !== $existing ) {
			return $existing;
		}

		$run_id   = 'run_' . wp_generate_password( 12, false, false );
		$response = NPATD_Ability_Contract::base_response( $run_id, 'pending' );

		self::cache_run( $external_run_id, $run_id, 'pending', $response );

		return $run_id;
	}

	public static function find_or_create_run( string $external_run_id ): string {
		if ( ! self::ensure_table() ) {
			return self::find_or_create_run_transient( $external_run_id );
		}

		global $wpdb;

		$table_name = self::table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin-owned runtime table read.
		$existing   = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT run_id FROM %i WHERE external_run_id = %s LIMIT 1',
				$table_name,
				$external_run_id
			)
		);

		if ( is_string( $existing ) && '' !== $existing ) {
			$status   = self::get_status( $existing );
			$response = self::get_stored_response( $existing, $status );
			self::cache_run( $external_run_id, $existing, $status, $response );

			return $existing;
		}

		$run_id = 'run_' . wp_generate_password( 12, false, false );
		$now    = self::now_mysql_utc();
		$base   = NPATD_Ability_Contract::base_response( $run_id, 'pending' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- plugin-owned runtime table insert.
		$inserted = $wpdb->insert(
			$table_name,
			array(
				'run_id'          => $run_id,
				'external_run_id' => $external_run_id,
				'status'          => 'pending',
				'response_json'   => wp_json_encode( $base ),
				'started_at'      => $now,
				'completed_at'    => null,
				'created_at'      => $now,
				'updated_at'      => $now,
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false === $inserted ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin-owned runtime table read.
			$existing_after_race = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT run_id FROM %i WHERE external_run_id = %s LIMIT 1',
					$table_name,
					$external_run_id
				)
			);

			if ( is_string( $existing_after_race ) && '' !== $existing_after_race ) {
				$status   = self::get_status( $existing_after_race );
				$response = self::get_stored_response( $existing_after_race, $status );
				self::cache_run( $external_run_id, $existing_after_race, $status, $response );

				return $existing_after_race;
			}

			return self::find_or_create_run_transient( $external_run_id );
		}

		self::cache_run( $external_run_id, $run_id, 'pending', $base );

		return $run_id;
	}

	public static function get_status( string $run_id ): string {
		if ( self::ensure_table() ) {
			global $wpdb;

			$table_name = self::table_name();
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin-owned runtime table read.
			$status     = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT status FROM %i WHERE run_id = %s LIMIT 1',
					$table_name,
					$run_id
				)
			);

			if ( is_string( $status ) && '' !== $status ) {
				return $status;
			}
		}

		$status = get_transient( self::status_key( $run_id ) );
		if ( ! is_string( $status ) || '' === $status ) {
			return 'pending';
		}

		return $status;
	}

	public static function mark_processing( string $run_id ): void {
		$response = NPATD_Ability_Contract::base_response( $run_id, 'processing' );
		if ( self::ensure_table() ) {
			global $wpdb;

			$table_name = self::table_name();
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin-owned runtime table update.
			$wpdb->update(
				$table_name,
				array(
					'status'        => 'processing',
					'response_json' => wp_json_encode( $response ),
					'updated_at'    => self::now_mysql_utc(),
				),
				array( 'run_id' => $run_id ),
				array( '%s', '%s', '%s' ),
				array( '%s' )
			);
		}

		self::update_cache_without_external( $run_id, 'processing', $response );
	}

	public static function mark_completed( string $run_id, int $post_id, string $post_url, array $quality_flags = array(), array $result_payload = array() ): array {
		$response                 = NPATD_Ability_Contract::base_response( $run_id, 'completed' );
		$response['post_id']      = $post_id;
		$response['post_url']     = $post_url;
		$response['quality_flags'] = $quality_flags;

		if ( isset( $result_payload['title'] ) && is_string( $result_payload['title'] ) ) {
			$response['title'] = $result_payload['title'];
		}

		if ( isset( $result_payload['blocks'] ) && is_array( $result_payload['blocks'] ) ) {
			$response['blocks'] = $result_payload['blocks'];
		}

		if ( isset( $result_payload['ai_usage'] ) && is_array( $result_payload['ai_usage'] ) ) {
			$response['ai_usage'] = self::sanitize_ai_usage( $result_payload['ai_usage'] );
		}

		$response['updated_existing_post'] = ! empty( $result_payload['updated_existing_post'] );

		if ( self::ensure_table() ) {
			global $wpdb;

			$table_name = self::table_name();
			$now        = self::now_mysql_utc();

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin-owned runtime table update.
			$wpdb->update(
				$table_name,
				array(
					'status'          => 'completed',
					'response_json'   => wp_json_encode( $response ),
					'last_error_code' => null,
					'last_error_message' => null,
					'completed_at'    => $now,
					'updated_at'      => $now,
				),
				array( 'run_id' => $run_id ),
				array( '%s', '%s', '%s', '%s', '%s', '%s' ),
				array( '%s' )
			);
		}

		self::update_cache_without_external( $run_id, 'completed', $response );

		return $response;
	}

	private static function sanitize_ai_usage( array $usage ): array {
		$prompt_tokens     = isset( $usage['prompt_tokens'] ) ? max( 0, (int) $usage['prompt_tokens'] ) : 0;
		$completion_tokens = isset( $usage['completion_tokens'] ) ? max( 0, (int) $usage['completion_tokens'] ) : 0;
		$total_tokens      = isset( $usage['total_tokens'] ) ? max( 0, (int) $usage['total_tokens'] ) : ( $prompt_tokens + $completion_tokens );
		$model_calls       = isset( $usage['model_calls'] ) ? max( 0, (int) $usage['model_calls'] ) : 0;
		$source            = isset( $usage['source'] ) && is_string( $usage['source'] ) ? sanitize_key( $usage['source'] ) : 'unknown';
		$connector         = isset( $usage['connector'] ) && is_string( $usage['connector'] ) ? sanitize_text_field( $usage['connector'] ) : 'unknown';

		if ( '' === trim( $connector ) ) {
			$connector = 'unknown';
		}

		return array(
			'prompt_tokens'     => $prompt_tokens,
			'completion_tokens' => $completion_tokens,
			'total_tokens'      => $total_tokens,
			'model_calls'       => $model_calls,
			'source'            => $source,
			'connector'         => $connector,
		);
	}

	private static function usage_window_defaults(): array {
		return array(
			'calls'             => 0,
			'prompt_tokens'     => 0,
			'completion_tokens' => 0,
			'total_tokens'      => 0,
			'actual_runs'       => 0,
			'estimated_runs'    => 0,
			'unknown_runs'      => 0,
			'model_calls'       => 0,
		);
	}

	private static function usage_from_response( array $response ): array {
		if ( ! isset( $response['ai_usage'] ) || ! is_array( $response['ai_usage'] ) ) {
			return array(
				'prompt_tokens'     => 0,
				'completion_tokens' => 0,
				'total_tokens'      => 0,
				'model_calls'       => 0,
				'source'            => 'unknown',
				'connector'         => 'unknown',
			);
		}

		return self::sanitize_ai_usage( $response['ai_usage'] );
	}

	private static function window_tokens_source( array $window ): string {
		$actual    = isset( $window['actual_runs'] ) ? (int) $window['actual_runs'] : 0;
		$estimated = isset( $window['estimated_runs'] ) ? (int) $window['estimated_runs'] : 0;
		$unknown   = isset( $window['unknown_runs'] ) ? (int) $window['unknown_runs'] : 0;

		if ( $estimated > 0 ) {
			return 'estimated';
		}

		if ( $unknown > 0 && 0 === $actual ) {
			return 'unknown';
		}

		if ( $actual > 0 ) {
			return 'actual';
		}

		return 'unknown';
	}

	private static function aggregate_usage_window( string $from_mysql_utc, string $to_mysql_utc ): array {
		$window = self::usage_window_defaults();

		if ( ! self::ensure_table() ) {
			return $window;
		}

		global $wpdb;

		$table_name = self::table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin-owned runtime table read for analytics.
		$rows = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT response_json FROM %i WHERE status = %s AND completed_at >= %s AND completed_at < %s',
				$table_name,
				'completed',
				$from_mysql_utc,
				$to_mysql_utc
			)
		);

		if ( ! is_array( $rows ) || empty( $rows ) ) {
			return $window;
		}

		$window['calls'] = count( $rows );

		foreach ( $rows as $response_json ) {
			if ( ! is_string( $response_json ) || '' === $response_json ) {
				$window['unknown_runs']++;
				continue;
			}

			$decoded = json_decode( $response_json, true );
			if ( ! is_array( $decoded ) ) {
				$window['unknown_runs']++;
				continue;
			}

			$usage = self::usage_from_response( $decoded );
			$window['prompt_tokens']     += (int) $usage['prompt_tokens'];
			$window['completion_tokens'] += (int) $usage['completion_tokens'];
			$window['total_tokens']      += (int) $usage['total_tokens'];
			$window['model_calls']       += (int) $usage['model_calls'];
			if ( 'actual' === $usage['source'] ) {
				$window['actual_runs']++;
			} elseif ( 'estimated' === $usage['source'] ) {
				$window['estimated_runs']++;
			} else {
				$window['unknown_runs']++;
			}
		}

		return $window;
	}

	public static function get_usage_analytics(): array {
		$today_start = gmdate( 'Y-m-d 00:00:00' );
		$today_end   = gmdate( 'Y-m-d 00:00:00', time() + DAY_IN_SECONDS );
		$month_start_timestamp = (int) strtotime( gmdate( 'Y-m-01 00:00:00' ) . ' UTC' );
		$month_end_timestamp   = (int) strtotime( '+1 month', $month_start_timestamp );

		$month_start = gmdate( 'Y-m-d H:i:s', $month_start_timestamp );
		$month_end   = gmdate( 'Y-m-d H:i:s', $month_end_timestamp );

		$today = self::aggregate_usage_window( $today_start, $today_end );
		$month = self::aggregate_usage_window( $month_start, $month_end );
		$today_tokens_source = self::window_tokens_source( $today );
		$month_tokens_source = self::window_tokens_source( $month );

		$tokens_source = 'estimated' === $month_tokens_source || 'estimated' === $today_tokens_source
			? 'estimated'
			: ( 'actual' === $month_tokens_source || 'actual' === $today_tokens_source ? 'actual' : 'unknown' );

		return array(
			'today'         => $today,
			'month'         => $month,
			'today_tokens_source' => $today_tokens_source,
			'month_tokens_source' => $month_tokens_source,
			'tokens_source' => $tokens_source,
			'timezone'      => 'UTC',
			'generated_at'  => gmdate( 'c' ),
		);
	}

	public static function mark_failed( string $run_id, string $code, string $message ): array {
		$base     = NPATD_Ability_Contract::base_response( $run_id, 'failed' );
		$response = NPATD_Ability_Contract::with_error( $base, $code, $message );
		$now      = self::now_mysql_utc();

		if ( self::ensure_table() ) {
			global $wpdb;

			$table_name = self::table_name();
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin-owned runtime table update.
			$wpdb->update(
				$table_name,
				array(
					'status'             => 'failed',
					'response_json'      => wp_json_encode( $response ),
					'last_error_code'    => $code,
					'last_error_message' => $message,
					'completed_at'       => $now,
					'updated_at'         => $now,
				),
				array( 'run_id' => $run_id ),
				array( '%s', '%s', '%s', '%s', '%s', '%s' ),
				array( '%s' )
			);
		}

		self::update_cache_without_external( $run_id, 'failed', $response );

		return $response;
	}

	private static function get_stored_response( string $run_id, string $fallback_status ): array {
		if ( self::ensure_table() ) {
			global $wpdb;

			$table_name     = self::table_name();
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin-owned runtime table read.
			$response_json  = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT response_json FROM %i WHERE run_id = %s LIMIT 1',
					$table_name,
					$run_id
				)
			);

			if ( is_string( $response_json ) && '' !== $response_json ) {
				$decoded = json_decode( $response_json, true );
				if ( is_array( $decoded ) ) {
					return $decoded;
				}
			}
		}

		$response = get_transient( self::response_key( $run_id ) );
		if ( ! is_array( $response ) ) {
			return NPATD_Ability_Contract::base_response( $run_id, $fallback_status );
		}

		return $response;
	}

	public static function get_processing_response( string $run_id ): array {
		return self::get_stored_response( $run_id, 'processing' );
	}

	public static function get_completed_response( string $run_id ): array {
		return self::get_stored_response( $run_id, 'completed' );
	}

	public static function get_failed_response( string $run_id ): array {
		return self::get_stored_response( $run_id, 'failed' );
	}
}
