<?php

namespace Fotogenic_AI;

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Logger uses plugin-owned custom tables with bounded admin/debug queries; dynamic fragments and identifiers are internally controlled and prepared before execution.

final class Logger {
    private static ?Logger $instance = null;
    private bool $enabled_cache = false;
    private bool $enabled_set = false;
    private bool $retention_checked = false;
    private bool $db_ready = false;
    private const RETENTION_DAYS = 7;
    private const EXPORT_MAX_ROWS_DEFAULT = 5000;
    private const MAX_DB_ROWS = 30000;
    private const MAX_RUN_ROWS = 10000;
    private const TABLE_NAME = 'fotogenic_ai_logs';
    private const RUNS_TABLE_NAME = 'fotogenic_ai_runs';
	public const SCHEMA_VERSION = 10;
	private const DIAGNOSTIC_SCRUB_SCHEMA_VERSION = 7;
    private const SCHEMA_VERSION_OPTION = 'fotogenic_ai_logger_schema_version';
    private const PURGE_LOCK_KEY = 'fotogenic_ai_log_purge_lock';
    private const WORKFLOWS = [ 'product_images', 'campaign_set', 'increase_resolution' ];
    private const DIAGNOSTIC_ENDPOINTS = [
        'campaign_set_generator',
        'configuration',
        'configuration_generator',
        'credits_balance',
        'enhance_images',
        'images_generator',
        'job',
        'jobs_batch',
        'price',
    ];
    private const DIAGNOSTIC_STATES = [
        'accepted',
        'active',
        'claimed',
        'complete',
        'completed',
        'error',
        'failed',
        'idle',
        'low',
        'normal',
        'not_found',
        'not_ready',
        'partial',
        'paused',
        'pending',
        'processing',
        'queued',
        'ready',
        'recovering',
        'requires_snapshot',
        'retrying',
        'running',
        'saved',
        'scheduled',
        'skipped',
        'stale',
        'started',
        'starting',
        'stopped',
        'stopping',
        'success',
        'timeout',
		'uncertain',
		'unresolved',
        'zero',
    ];
    private const DIAGNOSTIC_ACTIONS = [
        'add_all_to_gallery',
        'apply_both',
        'apply_featured',
        'apply_gallery',
        'both',
        'featured',
        'gallery',
        'pause',
        'resume',
        'stop',
    ];
    private const DIAGNOSTIC_INTEGER_KEYS = [
        'attempt',
        'batch_size',
        'count',
        'duration_ms',
        'elapsed',
        'elapsed_ms',
        'expected_count',
        'failed_count',
        'http_status',
        'input_count',
        'max_attempts',
        'memory_bytes',
        'output_count',
        'page',
        'pages',
        'reschedule_in',
        'retry_after',
        'returned_count',
        'saved_count',
        'total',
        'warning_count',
    ];
    private const DIAGNOSTIC_BOOLEAN_KEYS = [ 'cache_hit', 'cached', 'fresh', 'retryable', 'scheduled' ];
    private const DIAGNOSTIC_SAFE_KEYS = [
        'action',
        'attempt',
        'batch_size',
        'cache_hit',
        'cached',
        'count',
        'duration_ms',
        'elapsed',
        'elapsed_ms',
        'endpoint',
        'error_code',
        'expected_count',
        'failed_count',
        'final_status',
        'fresh',
        'http_status',
        'input_count',
        'max_attempts',
        'memory_bytes',
        'method',
        'mode',
        'output_count',
        'page',
        'pages',
        'quality',
        'request_type',
        'reschedule_in',
        'retry_after',
        'retryable',
        'returned_count',
        'run_target',
        'saved_count',
        'scheduled',
        'source_type',
        'state',
        'status',
        'step',
        'support_id',
        'target',
        'total',
        'warning_count',
        'workflow',
    ];

    /**
     * Security & performance notes:
     * - Logging is a no-op when disabled and avoids heavy context work.
     * - Sensitive values are redacted on write and export (defense in depth).
     * - DB queries are indexed, paginated, and bounded by retention limits.
     * - Purge is rate-limited and runs on cron + small admin chance.
     */

    private function __construct() {}

    public static function instance(): Logger {
        if ( null === self::$instance ) {
            self::$instance = new self();
        }

        return self::$instance;
    }

    public function enabled(): bool {
        if ( $this->enabled_set ) {
            return $this->enabled_cache;
        }
        $options = Settings::instance()->get_options();
        $this->enabled_cache = ! empty( $options['debug_logging'] );
        $this->enabled_set = true;

        return $this->enabled_cache;
    }

    public function info( string $event, array $context = [] ): void {
        $this->write( 'info', $event, $context );
    }

    public function warn( string $event, array $context = [] ): void {
        $this->write( 'warn', $event, $context );
    }

    public function error( string $event, array $context = [] ): void {
        $this->write( 'error', $event, $context );
    }

    /**
     * Persist a minimal support correlation record without request content or private identifiers.
     */
    public function support_error( string $support_id, string $error_code, int $http_status, string $step = '' ): void {
        $this->write(
            'error',
            'client_error',
            [
                'support_id' => strtolower( trim( $support_id ) ),
                'error_code' => sanitize_key( $error_code ),
                'http_status'=> max( 0, min( 599, $http_status ) ),
                'step'       => sanitize_key( $step ),
            ],
            true
        );
    }

    /**
     * Log finalize worker trace events (visible in run trace when debug_logging or filter enabled).
     * Events: finalize_worker_started, finalize_worker_job_check, finalize_worker_rescheduled, finalize_worker_exit.
     * Context should include run_id, user_id, and optionally attempt, elapsed, reason.
     *
     * @param string $event   Event name.
     * @param array  $context run_id, user_id, attempt?, elapsed?, reason?, reschedule_in?.
     * @return void
     */
    public function log_finalize_worker( string $event, array $context = [] ): void {
        if ( ! $this->enabled() && ! apply_filters( 'fotogenic_ai_log_finalize_worker', false ) ) {
            return;
        }
        $this->write( 'info', $event, $context, true );
    }

    public function api( array $meta ): void {
        $this->write( 'api', 'api_request', $meta );
    }

    public function list_entries( string $level, string $search, int $page, int $per_page, string $date_range = '' ): array {
        if ( ! $this->enabled() ) {
            return [
                'entries'        => [],
                'total'          => 0,
                'available_total'=> 0,
                'page'           => $page,
                'per_page'       => $per_page,
            ];
        }
        $this->maybe_opportunistic_purge();
        $this->ensure_tables();
        $level = strtolower( $level );
        $search = trim( $search );
        $date_range = strtolower( trim( $date_range ) );

        global $wpdb;
        $table = $this->get_table_name();
        $where = [ '1=1' ];
        $params = [];

        $stored_levels = $this->get_stored_entry_levels( $level );
        if ( 1 === count( $stored_levels ) ) {
            $where[] = 'level = %s';
            $params[] = $stored_levels[0];
        } elseif ( 1 < count( $stored_levels ) ) {
            $placeholders = implode( ', ', array_fill( 0, count( $stored_levels ), '%s' ) );
            $where[] = "level IN ({$placeholders})";
            $params = array_merge( $params, $stored_levels );
        }
        if ( '' !== $search ) {
            $like = '%' . $wpdb->esc_like( $search ) . '%';
            $where[] = '(event LIKE %s OR context LIKE %s)';
            $params[] = $like;
            $params[] = $like;
        }
        $date_bounds = $this->resolve_entry_date_bounds( $date_range );
        if ( 2 === count( $date_bounds ) ) {
            $where[] = 'event_time BETWEEN %s AND %s';
            $params[] = $date_bounds[0];
            $params[] = $date_bounds[1];
        }

        $where_sql = implode( ' AND ', $where );
        $count_sql = "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}";
        $prepared_count_sql = empty( $params ) ? $count_sql : $wpdb->prepare( $count_sql, $params );
        $total = (int) $wpdb->get_var( $prepared_count_sql );
        $available_total = '1=1' === $where_sql
            ? $total
            : (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );

        $offset = max( 0, ( $page - 1 ) * $per_page );
        $query = "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY event_time DESC LIMIT %d OFFSET %d";
        $query_params = array_merge( $params, [ $per_page, $offset ] );
        $prepared_query = $wpdb->prepare( $query, $query_params );
        $rows = $wpdb->get_results(
            $prepared_query,
            ARRAY_A
        );

        $entries = [];
        foreach ( $rows as $row ) {
            $entries[] = $this->project_diagnostic_event( $row );
        }

        return [
            'entries'        => $entries,
            'total'          => $total,
            'available_total'=> $available_total,
            'page'           => $page,
            'per_page'       => $per_page,
        ];
    }

    /**
     * Resolve presentation-level filters to the logger's bounded storage vocabulary.
     * API diagnostics are informational in the approved Settings presentation.
     *
     * @return string[]
     */
    private function get_stored_entry_levels( string $level ): array {
        switch ( strtolower( trim( $level ) ) ) {
            case 'info':
                return [ 'info', 'api' ];
            case 'warning':
                return [ 'warn' ];
            case 'error':
                return [ 'error' ];
            default:
                return [];
        }
    }

    /**
     * Convert the Settings date filter into inclusive UTC database bounds.
     * Logger timestamps are stored in UTC while Today follows the WordPress timezone.
     *
     * @param string                  $date_range Approved Settings range key.
     * @param \DateTimeImmutable|null $now        Testable current time in the WordPress timezone.
     * @return string[] Empty or exactly two UTC datetime strings.
     */
    private function resolve_entry_date_bounds( string $date_range, ?\DateTimeImmutable $now = null ): array {
        $date_range = strtolower( trim( $date_range ) );

        if ( '' === $date_range ) {
            return [];
        }

        if ( ! in_array( $date_range, [ 'today', '7d', '30d' ], true ) ) {
            return [];
        }

        $now = $now ?? current_datetime();
        $end_timestamp = $now->getTimestamp();
        if ( 'today' === $date_range ) {
            $start_timestamp = $now->setTime( 0, 0, 0 )->getTimestamp();
        } else {
            $days = '7d' === $date_range ? 7 : 30;
            $start_timestamp = $end_timestamp - ( $days * DAY_IN_SECONDS );
        }

        return [
            gmdate( 'Y-m-d H:i:s', $start_timestamp ),
            gmdate( 'Y-m-d H:i:s', $end_timestamp ),
        ];
    }

    public function delete_log_entries(): int {
        return $this->delete_log_db_entries();
    }

    /**
     * Export DB log entries as newline-delimited JSON lines.
     */
    public function export_entries_as_text(): string {
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_table_name();
        $max_rows = $this->get_export_max_rows();
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT event_time, level, event, context FROM {$table} ORDER BY event_time DESC, id DESC LIMIT %d",
                $max_rows
            ),
            ARRAY_A
        );
        if ( empty( $rows ) ) {
            return '';
        }
        $rows = array_reverse( $rows );
        $lines = [];
        foreach ( $rows as $row ) {
            $lines[] = wp_json_encode( $this->project_diagnostic_event( $row ), JSON_UNESCAPED_SLASHES );
        }
        return implode( PHP_EOL, $lines ) . PHP_EOL;
    }

    private function write( string $level, string $event, array $context = [], bool $force = false ): void {
        if ( ! $force && ! $this->enabled() ) {
            return;
        }
        $this->ensure_retention();
        $entry = [
            'timestamp'  => gmdate( 'c' ),
            'event_time' => gmdate( 'Y-m-d H:i:s' ),
            'level'      => $level,
            'event'      => $event,
            'context'    => $this->sanitize_diagnostic_context( $context ),
            'request_id' => '',
            'run_id'     => '',
            'job_id'     => '',
            'user_id'    => 0,
        ];

        $this->write_db_entry( $entry );
    }

    private function ensure_retention(): void {
        if ( $this->retention_checked ) {
            return;
        }
        $this->retention_checked = true;
        $this->run_purge();
    }

    /**
     * Retain only bounded, non-identifying diagnostic fields at logger ingress.
     */
    private function sanitize_diagnostic_context( array $context ): array {
        $safe = [];
        foreach ( $context as $key => $value ) {
            $key = strtolower( (string) $key );
            if ( ! in_array( $key, self::DIAGNOSTIC_SAFE_KEYS, true ) || $this->is_sensitive_key( $key ) ) {
                continue;
            }
            if ( is_array( $value ) || is_object( $value ) ) {
                continue;
            }
            $sanitized = $this->sanitize_diagnostic_value( $key, $value );
            if ( null === $sanitized ) {
                continue;
            }
            $safe[ $key ] = $sanitized;
        }
        return $safe;
    }

    /**
     * Apply a closed, per-key contract. A key allowlist alone is insufficient because an
     * attacker could otherwise place a token, prompt, URL, or identifier in a safe key.
     *
     * @param mixed $value Candidate value.
     * @return bool|int|string|null Safe scalar, or null when the value violates its contract.
     */
    private function sanitize_diagnostic_value( string $key, $value ) {
        if ( in_array( $key, self::DIAGNOSTIC_INTEGER_KEYS, true ) ) {
            if ( ! is_int( $value ) && ! ( is_string( $value ) && preg_match( '/^\d{1,12}$/', $value ) ) ) {
                return null;
            }
            $number = (int) $value;
            if ( $number < 0 ) {
                return null;
            }
            if ( 'http_status' === $key && $number > 599 ) {
                return null;
            }
            return $number;
        }
        if ( in_array( $key, self::DIAGNOSTIC_BOOLEAN_KEYS, true ) ) {
            return is_bool( $value ) ? $value : null;
        }
        if ( ! is_string( $value ) && ! is_int( $value ) ) {
            return null;
        }

        if ( 'status' === $key && is_int( $value ) ) {
            return $value >= 0 && $value <= 599 ? $value : null;
        }

        $string = trim( (string) $value );
        if ( '' === $string || strlen( $string ) > 64 ) {
            return null;
        }

        switch ( $key ) {
            case 'endpoint':
                return in_array( $string, self::DIAGNOSTIC_ENDPOINTS, true ) ? $string : null;
            case 'method':
                $method = strtoupper( $string );
                return in_array( $method, [ 'DELETE', 'GET', 'HEAD', 'PATCH', 'POST', 'PUT' ], true ) ? $method : null;
            case 'workflow':
                return in_array( $string, self::WORKFLOWS, true ) ? $string : null;
            case 'run_target':
            case 'source_type':
            case 'target':
                return in_array( $string, [ 'media', 'page', 'post', 'product' ], true ) ? $string : null;
            case 'quality':
                $quality = strtoupper( $string );
				return in_array( $quality, [ '1K', '2K', '4K' ], true ) ? $quality : null;
            case 'action':
                return in_array( $string, self::DIAGNOSTIC_ACTIONS, true ) ? $string : null;
            case 'mode':
                return in_array( $string, [ 'auto', 'bulk', 'ids', 'manual', 'query', 'single' ], true ) ? $string : null;
            case 'request_type':
                return in_array( $string, [ 'api', 'auth' ], true ) ? $string : null;
            case 'final_status':
            case 'state':
                return in_array( $string, self::DIAGNOSTIC_STATES, true ) ? $string : null;
            case 'status':
                if ( preg_match( '/^\d{1,3}$/', $string ) ) {
                    $status = (int) $string;
                    return $status <= 599 ? $status : null;
                }
                return in_array( $string, self::DIAGNOSTIC_STATES, true ) ? $string : null;
            case 'error_code':
            case 'step':
                return preg_match( '/^[a-z][a-z0-9_]{0,63}$/', $string ) ? $string : null;
            case 'support_id':
                return preg_match( '/^[a-f0-9]{8}-(?:[a-f0-9]{4}-){3}[a-f0-9]{12}$/', $string ) ? $string : null;
        }

        return null;
    }

    /**
     * Produce the only diagnostic event shape permitted outside the logger table.
     */
    private function project_diagnostic_event( array $row ): array {
        $context = json_decode( (string) ( $row['context'] ?? '' ), true );
        if ( ! is_array( $context ) ) {
            $context = [];
        }
        $context = $this->sanitize_diagnostic_context( Api_Client::instance()->redact_for_export( $context ) );
        $event_time = $this->sanitize_diagnostic_timestamp( (string) ( $row['event_time'] ?? '' ) );
        $level = strtolower( trim( (string) ( $row['level'] ?? '' ) ) );
        $event = strtolower( trim( (string) ( $row['event'] ?? '' ) ) );

        return [
            'timestamp'  => '' !== $event_time ? gmdate( 'c', (int) strtotime( $event_time ) ) : '',
            'event_time' => $event_time,
            'level'      => in_array( $level, [ 'api', 'error', 'info', 'warn' ], true ) ? $level : '',
            'event'      => preg_match( '/^[a-z][a-z0-9_]{0,63}$/', $event ) ? $event : '',
            'context'    => $context,
        ];
    }

    private function sanitize_diagnostic_timestamp( string $timestamp ): string {
        $timestamp = trim( $timestamp );
        if ( ! preg_match( '/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $timestamp ) ) {
            return '';
        }
        return false !== strtotime( $timestamp ) ? $timestamp : '';
    }

    private function is_sensitive_key( string $key ): bool {
        $keys = [
            'x-api-key',
            'x-wp-nonce',
            'authorization',
            'accesstoken',
            'refreshtoken',
            'code',
            'cookie',
            'api_key',
            'api-key',
            'nonce',
            'user_agent',
            'page_url',
            'url',
            'path_params',
            'query_params',
            'body_preview',
            'referer',
            'referrer',
            'custominstructions',
            'custom_instructions',
            'instructions',
            'multipart',
            'account_id',
            'attachment_id',
            'bulk_item_id',
            'bulk_run_uuid',
            'debug_id',
            'job_id',
            'jobid',
            'request_id',
            'run_id',
            'source_attachment_id',
            'subject_id',
            'user_id',
            'user_agent_hash',
        ];
        return in_array( $key, $keys, true );
    }

    public function generate_run_id(): string {
        return wp_generate_uuid4();
    }

    /**
     * List one bounded, ownership-scoped row per durable run. Bulk item rows are
     * aggregated by their immutable Bulk run UUID so pagination never implies
     * that one queue item is a separate user run.
     *
     * @return array{runs: array<int, array<string, mixed>>, total: int, page: int, per_page: int, status_counts: array<string, int>, ready: bool}
     */
    public function list_owned_run_groups( int $user_id, int $page, int $per_page, string $query = '', string $status = 'all' ): array {
		$page      = max( 1, $page );
		$per_page = max( 1, min( 48, $per_page ) );
		$query     = substr( sanitize_text_field( $query ), 0, 200 );
			$statuses  = [ 'queued', 'starting', 'processing', 'paused', 'stopping', 'uncertain', 'unresolved', 'partial', 'completed', 'failed', 'stopped', 'removed' ];
		$status    = sanitize_key( $status );
		$status    = in_array( $status, $statuses, true ) ? $status : 'all';
        if ( $user_id <= 0 ) {
            return [
				'runs'          => [],
				'total'         => 0,
				'page'          => $page,
				'per_page'      => $per_page,
				'status_counts' => [],
				'ready'         => true,
            ];
        }

        if ( ! $this->ensure_schema() ) {
            return $this->unavailable_run_groups_result( $page, $per_page );
        }
        $this->mark_stale_runs();

        global $wpdb;
        $table      = $this->get_runs_table_name();
        $offset     = max( 0, ( $page - 1 ) * $per_page );
        $group_expr = "CASE WHEN bulk_run_uuid IS NOT NULL AND bulk_run_uuid <> '' THEN bulk_run_uuid ELSE run_id END";
        $bulk_repo_ready = ! class_exists( Bulk_Repo::class ) || Bulk_Repo::instance()->ensure_tables();
        if ( ! $bulk_repo_ready ) {
            return $this->unavailable_run_groups_result( $page, $per_page );
        }
        $logger_status_sql = "
                CASE
					WHEN SUM(CASE WHEN final_status = 'uncertain' THEN 1 ELSE 0 END) > 0 THEN 'uncertain'
					WHEN SUM(CASE WHEN final_status = 'unresolved' THEN 1 ELSE 0 END) > 0 THEN 'unresolved'
                    WHEN SUM(CASE WHEN final_status IN ('starting', 'started') THEN 1 ELSE 0 END) > 0 THEN 'starting'
                    WHEN SUM(CASE WHEN final_status = 'processing' THEN 1 ELSE 0 END) > 0 THEN 'processing'
					WHEN SUM(CASE WHEN final_status = 'paused' THEN 1 ELSE 0 END) > 0 THEN 'paused'
					WHEN SUM(CASE WHEN final_status = 'stopping' THEN 1 ELSE 0 END) > 0 THEN 'stopping'
					WHEN SUM(CASE WHEN final_status = 'queued' THEN 1 ELSE 0 END) > 0 THEN 'queued'
                    WHEN SUM(CASE WHEN final_status = 'partial' THEN 1 ELSE 0 END) > 0 THEN 'partial'
                    WHEN SUM(CASE WHEN final_status IN ('failed', 'timeout', 'stale') THEN 1 ELSE 0 END) > 0 THEN 'failed'
                    WHEN SUM(CASE WHEN final_status = 'stopped' THEN 1 ELSE 0 END) > 0 THEN 'stopped'
                    WHEN SUM(CASE WHEN final_status = 'completed' THEN 1 ELSE 0 END) > 0 THEN 'completed'
                    ELSE 'uncertain'
                END
        ";
        $logger_groups_sql = "
            SELECT
                {$group_expr} AS group_key,
                MAX(COALESCE(bulk_run_uuid, '')) AS bulk_run_uuid,
                MIN(run_id) AS sample_run_id,
                MIN(started_at) AS started_at,
                MAX(COALESCE(ended_at, started_at)) AS ended_at,
                MAX(workflow) AS workflow,
                MAX(run_target) AS run_target,
                MAX(subject_id) AS subject_id,
                MAX(COALESCE(request_id, '')) AS request_id,
                COUNT(*) AS item_count,
                SUM(COALESCE(expected_output_count, 0)) AS expected_output_count,
                MIN(COALESCE(expected_output_count, 0)) AS min_outputs_per_item,
                MAX(COALESCE(expected_output_count, 0)) AS outputs_per_item,
                SUM(COALESCE(images_count, 0)) AS images_count,
				{$logger_status_sql} AS status,
				CONCAT_WS(
					' ',
					{$group_expr},
					MAX(COALESCE(bulk_run_uuid, '')),
					MAX(COALESCE(request_id, '')),
					MAX(workflow),
					MAX(run_target),
					MAX(subject_id),
					GROUP_CONCAT(DISTINCT COALESCE(product_title, '') SEPARATOR ' '),
					GROUP_CONCAT(DISTINCT COALESCE(target_snapshot, '') SEPARATOR ' ')
				) AS search_text,
                MAX(started_at) AS sort_at
            FROM {$table}
            WHERE user_id = %d AND run_id <> ''
            GROUP BY {$group_expr}
        ";
        $history_index_sql = $logger_groups_sql;
        $history_params    = [ $user_id ];

        if ( class_exists( Bulk_Repo::class ) && $bulk_repo_ready ) {
            $bulk_runs_table  = $wpdb->prefix . 'fotogenic_ai_bulk_runs';
            $bulk_items_table = $wpdb->prefix . 'fotogenic_ai_bulk_run_items';
			$bulk_status_sql = "
				CASE
					WHEN bulk_run.status = 'completed' AND bulk_run.status_reason = 'partial' THEN 'partial'
					WHEN bulk_run.status = 'admitting' THEN 'starting'
					WHEN bulk_run.status = 'running' THEN 'processing'
					WHEN bulk_run.status IN ('paused', 'stopping', 'stopped', 'completed', 'failed') THEN bulk_run.status
					ELSE 'uncertain'
				END
			";
			$logger_groups_sql = "
				SELECT
					logger_group.group_key,
					logger_group.bulk_run_uuid,
					logger_group.sample_run_id,
					logger_group.started_at,
					logger_group.ended_at,
					logger_group.workflow,
					logger_group.run_target,
					logger_group.subject_id,
					logger_group.request_id,
					logger_group.item_count,
					logger_group.expected_output_count,
					logger_group.min_outputs_per_item,
					logger_group.outputs_per_item,
					logger_group.images_count,
					CASE
						WHEN bulk_run.id IS NOT NULL THEN {$bulk_status_sql}
						ELSE logger_group.status
					END AS status,
					logger_group.search_text,
					logger_group.sort_at
				FROM ({$logger_groups_sql}) logger_group
				LEFT JOIN {$bulk_runs_table} bulk_run
					ON bulk_run.user_id = %d
					AND bulk_run.run_uuid = logger_group.bulk_run_uuid
			";
			$history_params[] = $user_id;
            $bulk_only_sql    = "
                SELECT
                    bulk_run.run_uuid AS group_key,
                    bulk_run.run_uuid AS bulk_run_uuid,
                    '' AS sample_run_id,
                    COALESCE(bulk_run.started_at, bulk_run.created_at) AS started_at,
                    COALESCE(bulk_run.finished_at, bulk_run.updated_at, bulk_run.created_at) AS ended_at,
                    bulk_run.workflow AS workflow,
                    COALESCE(
                        (
                            SELECT bulk_item.subject_type
                            FROM {$bulk_items_table} bulk_item
                            WHERE bulk_item.run_id = bulk_run.id
                            ORDER BY bulk_item.item_ordinal ASC, bulk_item.id ASC
                            LIMIT 1
                        ),
                        ''
                    ) AS run_target,
                    0 AS subject_id,
                    '' AS request_id,
                    bulk_run.selected_count AS item_count,
                    0 AS expected_output_count,
                    0 AS min_outputs_per_item,
                    0 AS outputs_per_item,
                    0 AS images_count,
					{$bulk_status_sql} AS status,
					CONCAT_WS(
						' ',
						bulk_run.run_uuid,
						bulk_run.workflow,
						bulk_run.status,
						COALESCE(
							(
								SELECT GROUP_CONCAT(
									DISTINCT CONCAT_WS(' ', bulk_item.subject_type, bulk_item.subject_id, bulk_item.target_type, COALESCE(bulk_item.target_snapshot, ''))
									SEPARATOR ' '
								)
								FROM {$bulk_items_table} bulk_item
								WHERE bulk_item.run_id = bulk_run.id
							),
							''
						)
					) AS search_text,
                    COALESCE(bulk_run.started_at, bulk_run.created_at) AS sort_at
                FROM {$bulk_runs_table} bulk_run
                WHERE bulk_run.user_id = %d
                    AND bulk_run.run_uuid <> ''
                    AND NOT EXISTS (
                        SELECT 1
                        FROM {$table} generation_run
                        WHERE generation_run.user_id = bulk_run.user_id
                            AND generation_run.bulk_run_uuid = bulk_run.run_uuid
                            AND generation_run.run_id <> ''
                    )
            ";
            $history_index_sql  = "{$logger_groups_sql} UNION ALL {$bulk_only_sql}";
            $history_params[]   = $user_id;
        }

        $query_filtered_sql    = "SELECT * FROM ({$history_index_sql}) history_index";
		$query_filtered_params = $history_params;
		if ( '' !== $query ) {
			$query_filtered_sql     .= ' WHERE history_index.search_text LIKE %s';
			$query_filtered_params[] = '%' . $wpdb->esc_like( $query ) . '%';
		}

		$facets_sql = "
			SELECT status, COUNT(*) AS total
			FROM ({$query_filtered_sql}) query_filtered_runs
			GROUP BY status
		";
		$facets = $wpdb->get_results(
			$wpdb->prepare( $facets_sql, ...$query_filtered_params ),
			ARRAY_A
		);
		if ( ! is_array( $facets ) || '' !== trim( (string) $wpdb->last_error ) ) {
            return $this->unavailable_run_groups_result( $page, $per_page );
        }
		$status_counts = [];
		foreach ( $facets as $facet ) {
			$facet_status = sanitize_key( (string) ( $facet['status'] ?? '' ) );
			if ( in_array( $facet_status, $statuses, true ) ) {
				$status_counts[ $facet_status ] = max( 0, (int) ( $facet['total'] ?? 0 ) );
			}
		}
		$total = 'all' === $status ? array_sum( $status_counts ) : (int) ( $status_counts[ $status ] ?? 0 );

		$filtered_sql    = $query_filtered_sql;
		$filtered_params = $query_filtered_params;
		if ( 'all' !== $status ) {
			$filtered_sql     = "SELECT * FROM ({$query_filtered_sql}) status_filtered_runs WHERE status = %s";
			$filtered_params[] = $status;
		}
        $rows_sql  = "
            SELECT *
            FROM ({$filtered_sql}) owned_runs
            ORDER BY sort_at DESC, group_key DESC
            LIMIT %d OFFSET %d
        ";
		$rows_params = array_merge( $filtered_params, [ $per_page, $offset ] );
        $rows       = $wpdb->get_results(
            $wpdb->prepare( $rows_sql, ...$rows_params ),
            ARRAY_A
        );
        if ( ! is_array( $rows ) || '' !== trim( (string) $wpdb->last_error ) ) {
            return $this->unavailable_run_groups_result( $page, $per_page );
        }

        return [
			'runs'          => $rows,
			'total'         => $total,
			'page'          => $page,
			'per_page'      => $per_page,
			'status_counts' => $status_counts,
			'ready'         => true,
        ];
    }

    /**
     * @return array{runs: array<int, array<string, mixed>>, total: int, page: int, per_page: int, status_counts: array<string, int>, ready: bool}
     */
    private function unavailable_run_groups_result( int $page, int $per_page ): array {
        return [
			'runs'          => [],
			'total'         => 0,
			'page'          => $page,
			'per_page'      => $per_page,
			'status_counts' => [],
			'ready'         => false,
        ];
    }

    public function create_run( string $run_id, array $summary ): bool {
        if ( '' === $run_id ) {
            return false;
        }
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_runs_table_name();
        $run_target = isset( $summary['run_target'] ) && in_array( $summary['run_target'], [ 'product', 'media', 'post', 'page' ], true )
            ? (string) $summary['run_target']
            : 'unknown';
        $workflow = isset( $summary['workflow'] ) && in_array( $summary['workflow'], self::WORKFLOWS, true )
            ? (string) $summary['workflow']
            : 'product_images';
        $subject_id = absint( $summary['subject_id'] ?? 0 );
        $source_attachment_ids = isset( $summary['source_attachment_ids'] ) && is_array( $summary['source_attachment_ids'] )
            ? array_values( array_unique( array_filter( array_map( 'absint', $summary['source_attachment_ids'] ) ) ) )
            : [];
        $data = [
            'run_id'       => $run_id,
            'job_id'       => $summary['job_id'] ?? '',
            'run_target'   => $run_target,
            'subject_id'   => $subject_id,
            'workflow'     => $workflow,
            'request_id'   => substr( sanitize_text_field( (string) ( $summary['request_id'] ?? '' ) ), 0, 64 ),
            'source_attachment_id' => (int) ( $summary['source_attachment_id'] ?? 0 ),
            'source_attachment_ids' => wp_json_encode( $source_attachment_ids ),
            'expected_output_count' => max( 0, (int) ( $summary['expected_output_count'] ?? 0 ) ),
            'estimated_cost' => max( 0, (float) ( $summary['estimated_cost'] ?? 0 ) ),
			'product_title'=> (string) ( $summary['product_title'] ?? '' ),
            'user_id'      => (int) ( $summary['user_id'] ?? 0 ),
            'started_at'   => $summary['started_at'] ?? gmdate( 'Y-m-d H:i:s' ),
            'images_count' => (int) ( $summary['images_count'] ?? 0 ),
            'quality'      => (string) ( $summary['quality'] ?? '' ),
            'final_status' => (string) ( $summary['final_status'] ?? 'started' ),
        ];
        $formats = [ '%s', '%s', '%s', '%d', '%s', '%s', '%d', '%s', '%d', '%f', '%s', '%d', '%s', '%d', '%s', '%s' ];
        $bulk_run_uuid = $this->sanitize_bulk_run_uuid( (string) ( $summary['bulk_run_uuid'] ?? '' ) );
        if ( '' !== $bulk_run_uuid ) {
            $data['bulk_run_uuid'] = $bulk_run_uuid;
            $formats[] = '%s';
            $bulk_item_id = absint( $summary['bulk_item_id'] ?? 0 );
            if ( $bulk_item_id > 0 ) {
                $data['bulk_item_id'] = $bulk_item_id;
                $formats[] = '%d';
            }
        }
        $run_item_uuid = strtolower( trim( (string) ( $summary['run_item_uuid'] ?? '' ) ) );
        if ( wp_is_uuid( $run_item_uuid ) ) {
            $data['run_item_uuid'] = $run_item_uuid;
            $formats[] = '%s';
        }
        $campaign_group_uuid = strtolower( trim( (string) ( $summary['campaign_group_uuid'] ?? '' ) ) );
        if ( wp_is_uuid( $campaign_group_uuid ) ) {
            $data['campaign_group_uuid'] = $campaign_group_uuid;
            $formats[] = '%s';
        }
        $item_ordinal = (int) ( $summary['item_ordinal'] ?? 0 );
        if ( $item_ordinal > 0 ) {
            $data['item_ordinal'] = $item_ordinal;
            $formats[] = '%d';
        }
        $target_snapshot = Workflow_Item_Identity::normalize_target_snapshot( $summary['target_snapshot'] ?? null );
        if ( ! is_wp_error( $target_snapshot ) && in_array( (string) ( $target_snapshot['targetType'] ?? '' ), [ 'product', 'variation', 'media', 'post', 'page' ], true ) ) {
            $data['target_type'] = (string) $target_snapshot['targetType'];
            $data['parent_product_id'] = (int) $target_snapshot['parentProductId'];
            $data['variation_id'] = (int) $target_snapshot['variationId'];
            $data['target_snapshot'] = wp_json_encode( $target_snapshot, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE );
            array_push( $formats, '%s', '%d', '%d', '%s' );
        }
        foreach ( [ 'source_roles_snapshot', 'destination_snapshot' ] as $snapshot_field ) {
            $encoded = $this->sanitize_identity_snapshot( $summary[ $snapshot_field ] ?? null );
            if ( '' !== $encoded ) {
                $data[ $snapshot_field ] = $encoded;
                $formats[] = '%s';
            }
        }
		$credit_settlement_state = Workflow_Credit_Settlement::encode_state( $summary['credit_settlement_state'] ?? null );
		if ( '' !== $credit_settlement_state ) {
			$data['credit_settlement_state'] = $credit_settlement_state;
			$formats[] = '%s';
		}
        $inserted = $wpdb->insert(
            $table,
            $data,
            $formats
        );
        if ( 1 !== $inserted || '' !== trim( (string) $wpdb->last_error ) ) {
            return false;
        }

        return $this->run_row_matches_fields( $this->get_run_row( $run_id ), $data );
    }

    public function update_run( string $run_id, array $fields ): bool {
        if ( '' === $run_id || empty( $fields ) ) {
            return false;
        }
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_runs_table_name();
        if ( isset( $fields['bulk_run_uuid'] ) ) {
            $fields['bulk_run_uuid'] = $this->sanitize_bulk_run_uuid( (string) $fields['bulk_run_uuid'] );
            if ( '' === $fields['bulk_run_uuid'] ) {
                unset( $fields['bulk_run_uuid'] );
            }
        }
        if ( isset( $fields['bulk_item_id'] ) ) {
            $fields['bulk_item_id'] = absint( $fields['bulk_item_id'] );
            if ( $fields['bulk_item_id'] <= 0 ) {
                unset( $fields['bulk_item_id'] );
            }
        }
        foreach ( [ 'run_item_uuid', 'campaign_group_uuid' ] as $uuid_field ) {
            if ( ! isset( $fields[ $uuid_field ] ) ) {
                continue;
            }
            $fields[ $uuid_field ] = strtolower( trim( (string) $fields[ $uuid_field ] ) );
            if ( ! wp_is_uuid( $fields[ $uuid_field ] ) ) {
                unset( $fields[ $uuid_field ] );
            }
        }
        if ( isset( $fields['item_ordinal'] ) ) {
            $fields['item_ordinal'] = max( 0, (int) $fields['item_ordinal'] );
        }
        if ( array_key_exists( 'target_snapshot', $fields ) ) {
            $target_snapshot = Workflow_Item_Identity::normalize_target_snapshot( $fields['target_snapshot'] );
            if ( is_wp_error( $target_snapshot ) || ! in_array( (string) ( $target_snapshot['targetType'] ?? '' ), [ 'product', 'variation', 'media', 'post', 'page' ], true ) ) {
                unset( $fields['target_snapshot'], $fields['target_type'], $fields['parent_product_id'], $fields['variation_id'] );
            } else {
                $fields['target_type'] = (string) $target_snapshot['targetType'];
                $fields['parent_product_id'] = (int) $target_snapshot['parentProductId'];
                $fields['variation_id'] = (int) $target_snapshot['variationId'];
                $fields['target_snapshot'] = wp_json_encode( $target_snapshot, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE );
            }
        }
        foreach ( [ 'source_roles_snapshot', 'destination_snapshot' ] as $snapshot_field ) {
            if ( ! array_key_exists( $snapshot_field, $fields ) ) {
                continue;
            }
            $encoded = $this->sanitize_identity_snapshot( $fields[ $snapshot_field ] );
            if ( '' === $encoded ) {
                unset( $fields[ $snapshot_field ] );
            } else {
                $fields[ $snapshot_field ] = $encoded;
            }
        }
		if ( array_key_exists( 'credit_settlement_state', $fields ) ) {
			$encoded = Workflow_Credit_Settlement::encode_state( $fields['credit_settlement_state'] );
			if ( '' === $encoded ) {
				unset( $fields['credit_settlement_state'] );
			} else {
				$fields['credit_settlement_state'] = $encoded;
			}
		}
        if ( isset( $fields['workflow'] ) && ! in_array( $fields['workflow'], self::WORKFLOWS, true ) ) {
            unset( $fields['workflow'] );
        }
        if ( isset( $fields['request_id'] ) ) {
            $fields['request_id'] = substr( sanitize_text_field( (string) $fields['request_id'] ), 0, 64 );
        }
        if ( isset( $fields['source_attachment_id'] ) ) {
            $fields['source_attachment_id'] = max( 0, (int) $fields['source_attachment_id'] );
        }
        if ( isset( $fields['source_attachment_ids'] ) && is_array( $fields['source_attachment_ids'] ) ) {
            $fields['source_attachment_ids'] = wp_json_encode(
                array_values( array_unique( array_filter( array_map( 'absint', $fields['source_attachment_ids'] ) ) ) )
            );
        }
        if ( isset( $fields['expected_output_count'] ) ) {
            $fields['expected_output_count'] = max( 0, (int) $fields['expected_output_count'] );
        }
        if ( isset( $fields['estimated_cost'] ) ) {
            $fields['estimated_cost'] = max( 0, (float) $fields['estimated_cost'] );
        }
        if ( empty( $fields ) ) {
            return false;
        }
        $formats = [];
        foreach ( $fields as $value ) {
            if ( is_int( $value ) ) {
                $formats[] = '%d';
            } elseif ( is_float( $value ) ) {
                $formats[] = '%f';
            } else {
                $formats[] = '%s';
            }
        }
        $updated = $wpdb->update( $table, $fields, [ 'run_id' => $run_id ], $formats, [ '%s' ] );
        if ( false === $updated || '' !== trim( (string) $wpdb->last_error ) ) {
            return false;
        }

        return $this->run_row_matches_fields( $this->get_run_row( $run_id ), $fields );
    }

    public function finalize_run( string $run_id, string $final_status, array $fields = [] ): bool {
        if ( '' === $run_id ) {
            return false;
        }
        $this->ensure_tables();
        if ( 'failed' === $final_status ) {
            $row = $this->get_run_row( $run_id );
            $attachments_map = [];
            if ( 'campaign_set' === (string) ( $row['workflow'] ?? '' ) && ! empty( $row['attachments_map'] ) ) {
                $decoded = json_decode( (string) $row['attachments_map'], true );
                $attachments_map = is_array( $decoded ) ? array_filter( array_map( 'absint', $decoded ) ) : [];
            }
            if ( ! empty( $attachments_map ) ) {
                $final_status = 'partial';
                $fields['error_code'] = (string) ( $fields['error_code'] ?? 'partial' );
                if ( '' === $fields['error_code'] ) {
                    $fields['error_code'] = 'partial';
                }
            }
        }
        $ended_at = gmdate( 'Y-m-d H:i:s' );
        if ( isset( $fields['error_message'] ) ) {
            $fields['error_message'] = sanitize_text_field( (string) $fields['error_message'] );
        }
        if ( isset( $fields['error_code'] ) ) {
            $fields['error_code'] = sanitize_text_field( (string) $fields['error_code'] );
        }
        $fields = array_merge(
            [
                'final_status' => $final_status,
                'ended_at'     => $ended_at,
            ],
            $fields
        );
        if ( ! $this->update_run( $run_id, $fields ) ) {
            return false;
        }
        do_action( 'fotogenic_ai_run_finalized', $run_id, $final_status );
        return true;
    }

    /**
     * Verify the exact durable projection after an insert or update. WordPress
     * database drivers return scalar columns as strings, so comparisons follow
     * the normalized type that was written rather than PHP's fetched type.
     */
    private function run_row_matches_fields( array $row, array $fields ): bool {
        if ( empty( $row ) ) {
            return false;
        }
        foreach ( $fields as $key => $expected ) {
            if ( ! array_key_exists( $key, $row ) ) {
                return false;
            }
            $actual = $row[ $key ];
            if ( is_int( $expected ) ) {
                if ( (int) $actual !== $expected ) {
                    return false;
                }
                continue;
            }
            if ( is_float( $expected ) ) {
                if ( abs( (float) $actual - $expected ) > 0.00001 ) {
                    return false;
                }
                continue;
            }
            if ( ! hash_equals( (string) $expected, (string) $actual ) ) {
                return false;
            }
        }
        return true;
    }

    public function get_latest_workflow_run_for_subject( int $user_id, string $workflow, string $run_target, int $subject_id ): array {
        if ( $user_id <= 0 || $subject_id <= 0 || ! in_array( $workflow, self::WORKFLOWS, true ) ) {
            return [];
        }
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_runs_table_name();
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE user_id = %d AND workflow = %s AND run_target = %s AND subject_id = %d ORDER BY started_at DESC LIMIT 1",
                $user_id,
                $workflow,
                $run_target,
                $subject_id
            ),
            ARRAY_A
        );
        return is_array( $row ) ? $row : [];
    }

    public function get_latest_workflow_run_for_item( int $user_id, string $workflow, string $run_item_uuid ): array {
        $run_item_uuid = strtolower( trim( $run_item_uuid ) );
        if ( $user_id <= 0 || ! in_array( $workflow, self::WORKFLOWS, true ) || ! wp_is_uuid( $run_item_uuid ) ) {
            return [];
        }
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_runs_table_name();
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE user_id = %d AND workflow = %s AND run_item_uuid = %s ORDER BY started_at DESC LIMIT 1",
                $user_id,
                $workflow,
                $run_item_uuid
            ),
            ARRAY_A
        );
        return is_array( $row ) ? $row : [];
    }

    public function has_active_workflow_run( int $user_id, string $workflow, string $run_target, int $subject_id ): bool {
        if ( $user_id <= 0 || $subject_id <= 0 || ! in_array( $workflow, self::WORKFLOWS, true ) ) {
            return false;
        }
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_runs_table_name();
        $found = $wpdb->get_var(
            $wpdb->prepare(
				"SELECT 1 FROM {$table} WHERE user_id = %d AND workflow = %s AND run_target = %s AND subject_id = %d AND final_status IN ('starting','processing','unresolved') ORDER BY started_at DESC LIMIT 1",
                $user_id,
                $workflow,
                $run_target,
                $subject_id
            )
        );
        return (bool) $found;
    }

    public function has_active_workflow_run_for_item( int $user_id, string $workflow, string $run_item_uuid ): bool {
        $run_item_uuid = strtolower( trim( $run_item_uuid ) );
        if ( $user_id <= 0 || ! in_array( $workflow, self::WORKFLOWS, true ) || ! wp_is_uuid( $run_item_uuid ) ) {
            return false;
        }
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_runs_table_name();
        return (bool) $wpdb->get_var(
            $wpdb->prepare(
				"SELECT 1 FROM {$table} WHERE user_id = %d AND workflow = %s AND run_item_uuid = %s AND final_status IN ('starting','processing','unresolved') ORDER BY started_at DESC LIMIT 1",
                $user_id,
                $workflow,
                $run_item_uuid
            )
        );
    }

    public function touch_run_api( string $run_id ): void {
        if ( '' === $run_id ) {
            return;
        }
        $this->update_run( $run_id, [ 'last_api_request_at' => gmdate( 'Y-m-d H:i:s' ) ] );
    }

    private function write_db_entry( array $entry ): void {
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_table_name();
        $wpdb->insert(
            $table,
            [
                'run_id'     => $entry['run_id'] ?? '',
                'job_id'     => $entry['job_id'] ?? '',
                'user_id'    => (int) ( $entry['user_id'] ?? 0 ),
                'event_time' => $entry['event_time'] ?? gmdate( 'Y-m-d H:i:s' ),
                'level'      => $entry['level'] ?? '',
                'event'      => $entry['event'] ?? '',
                'context'    => wp_json_encode( $entry['context'] ?? [] ),
                'request_id' => $entry['request_id'] ?? '',
            ],
            [ '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s' ]
        );
    }

    /**
     * Install or upgrade logger tables when the stored schema version is stale.
     * Pass true from activation to repair/install idempotently even when the version option exists.
     */
    public function maybe_upgrade_schema( bool $force = false ): bool {
        if ( $this->db_ready && ! $force ) {
            return true;
        }
        $stored_version = (int) get_option( self::SCHEMA_VERSION_OPTION, 0 );
        if ( $stored_version > self::SCHEMA_VERSION ) {
            $this->db_ready = false;
            return false;
        }
        if ( ! $force && self::SCHEMA_VERSION === $stored_version && $this->schema_tables_are_current() ) {
            $this->db_ready = true;
            return true;
        }

        global $wpdb;
        $table = $this->get_table_name();
        $runs_table = $this->get_runs_table_name();
        $charset = $wpdb->get_charset_collate();
		$job_id_length = Upstream_Identifier::MAX_LENGTH;
        $sql = "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            run_id varchar(64) NULL,
            job_id varchar({$job_id_length}) NULL,
            user_id bigint(20) unsigned NULL,
            event_time datetime NOT NULL,
            level varchar(12) NOT NULL,
            event varchar(64) NOT NULL,
            context longtext NULL,
            request_id varchar(64) NULL,
            PRIMARY KEY  (id),
            KEY run_id_event_time (run_id, event_time),
            KEY job_id (job_id(191)),
            KEY level_event_time (level, event_time),
            KEY event_time (event_time)
        ) {$charset};";
        $runs_sql = "CREATE TABLE {$runs_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            run_id varchar(64) NOT NULL,
            bulk_run_uuid char(36) NULL,
            bulk_item_id bigint(20) unsigned NULL,
            run_item_uuid char(36) NULL,
            campaign_group_uuid char(36) NULL,
            item_ordinal int unsigned NULL,
            job_id varchar({$job_id_length}) NULL,
            run_target varchar(20) NOT NULL,
            subject_id bigint(20) unsigned NOT NULL,
            target_type varchar(20) NOT NULL DEFAULT 'unknown',
            parent_product_id bigint(20) unsigned NOT NULL DEFAULT 0,
            variation_id bigint(20) unsigned NOT NULL DEFAULT 0,
            target_snapshot longtext NULL,
            source_roles_snapshot longtext NULL,
            destination_snapshot longtext NULL,
            workflow varchar(32) NOT NULL DEFAULT 'product_images',
            request_id varchar(64) NULL,
            source_attachment_id bigint(20) unsigned NULL,
            source_attachment_ids longtext NULL,
            expected_output_count int unsigned NULL,
            estimated_cost decimal(12,4) unsigned NULL,
            product_title varchar(255) NULL,
            user_id bigint(20) unsigned NULL,
            started_at datetime NOT NULL,
            ended_at datetime NULL,
            final_status varchar(32) NULL,
            error_code varchar(64) NULL,
            error_message text NULL,
            attachment_id bigint(20) unsigned NULL,
            job_ids longtext NULL,
            attachments_map longtext NULL,
            images_count int unsigned NULL,
            quality varchar(16) NULL,
            last_api_request_at datetime NULL,
			finalize_attempts int unsigned NOT NULL DEFAULT 0,
			finalize_meta longtext NULL,
			delivery_state longtext NULL,
			credit_settlement_state longtext NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY run_id (run_id),
            KEY job_id (job_id(191)),
            KEY user_run_subject_started (user_id, run_target, subject_id, started_at),
            KEY user_workflow_subject_started (user_id, workflow, run_target, subject_id, started_at),
            KEY request_id (request_id),
            KEY bulk_run_uuid_started (bulk_run_uuid, started_at),
            KEY bulk_run_item (bulk_run_uuid, bulk_item_id),
            KEY run_item_uuid (run_item_uuid),
            KEY campaign_group_uuid (campaign_group_uuid),
            KEY user_workflow_item (user_id, workflow, run_item_uuid),
            KEY started_at (started_at),
            KEY last_api_request_at (last_api_request_at),
            KEY status_api_time (final_status, last_api_request_at)
        ) {$charset};";
        if ( ! function_exists( 'dbDelta' ) ) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }
        dbDelta( $sql );
        dbDelta( $runs_sql );
        if ( $this->schema_tables_are_current() ) {
			if ( $stored_version < self::DIAGNOSTIC_SCRUB_SCHEMA_VERSION ) {
                if ( ! $this->scrub_persisted_diagnostic_identifiers() ) {
                    $this->db_ready = false;
                    return false;
                }
            }
            $updated = update_option( self::SCHEMA_VERSION_OPTION, self::SCHEMA_VERSION, false );
            if ( ! $updated && self::SCHEMA_VERSION !== (int) get_option( self::SCHEMA_VERSION_OPTION, 0 ) ) {
                $this->db_ready = false;
                return false;
            }
        }
        $this->db_ready = self::SCHEMA_VERSION === (int) get_option( self::SCHEMA_VERSION_OPTION, 0 )
            && $this->schema_tables_are_current();

        return $this->db_ready;
    }

    public function ensure_schema(): bool {
        return $this->maybe_upgrade_schema();
    }

    private function ensure_tables(): void {
        if ( $this->db_ready ) {
            return;
        }
        $this->maybe_upgrade_schema();
    }

    /**
     * Remove retired identifiers and free-form values from diagnostic rows.
     */
    private function scrub_persisted_diagnostic_identifiers(): bool {
        global $wpdb;
        $table = $this->get_table_name();
        $updated = $wpdb->query(
            "UPDATE {$table}
            SET run_id = '', job_id = '', user_id = 0, request_id = '', context = '{}'"
        );
        if ( false === $updated ) {
            return false;
        }
        $remaining = $wpdb->get_var(
            "SELECT COUNT(*) FROM {$table}
            WHERE COALESCE(run_id, '') <> ''
                OR COALESCE(job_id, '') <> ''
                OR COALESCE(request_id, '') <> ''
                OR COALESCE(user_id, 0) <> 0
                OR COALESCE(context, '') NOT IN ('', '{}')"
        );
        return null !== $remaining && 0 === (int) $remaining;
    }

    private function schema_tables_are_current(): bool {
        global $wpdb;
        $logs_table = $this->get_table_name();
        $runs_table = $this->get_runs_table_name();
        $logs_exists = (string) $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $logs_table ) )
        );
        $runs_exists = (string) $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $runs_table ) )
        );
        if ( $logs_table !== $logs_exists || $runs_table !== $runs_exists ) {
            return false;
        }

        $log_columns = array_map( 'strval', (array) $wpdb->get_col( "SHOW COLUMNS FROM {$logs_table}", 0 ) );
        $current_log_columns = [ 'id', 'run_id', 'job_id', 'user_id', 'event_time', 'level', 'event', 'context', 'request_id' ];
        sort( $log_columns );
        sort( $current_log_columns );
        if ( $current_log_columns !== $log_columns ) {
            return false;
        }
		$log_job_column = $wpdb->get_row(
			$wpdb->prepare( "SHOW COLUMNS FROM {$logs_table} LIKE %s", 'job_id' ),
			ARRAY_A
		);
		if ( ! is_array( $log_job_column ) || 'varchar(' . Upstream_Identifier::MAX_LENGTH . ')' !== strtolower( (string) ( $log_job_column['Type'] ?? '' ) ) ) {
			return false;
		}
        $log_indexes = array_values( array_unique( array_map( 'strval', (array) $wpdb->get_col( "SHOW INDEX FROM {$logs_table}", 2 ) ) ) );
        $current_log_indexes = [ 'PRIMARY', 'run_id_event_time', 'job_id', 'level_event_time', 'event_time' ];
        sort( $log_indexes );
        sort( $current_log_indexes );
        if ( $current_log_indexes !== $log_indexes ) {
            return false;
        }

		foreach ( [ 'workflow', 'request_id', 'source_attachment_id', 'source_attachment_ids', 'expected_output_count', 'estimated_cost', 'bulk_run_uuid', 'bulk_item_id', 'run_item_uuid', 'campaign_group_uuid', 'item_ordinal', 'target_type', 'parent_product_id', 'variation_id', 'target_snapshot', 'source_roles_snapshot', 'destination_snapshot', 'delivery_state', 'credit_settlement_state' ] as $column ) {
            $found = $wpdb->get_var(
                $wpdb->prepare( "SHOW COLUMNS FROM {$runs_table} LIKE %s", $column )
            );
            if ( $column !== $found ) {
                return false;
            }
        }
        $run_columns = array_map( 'strval', (array) $wpdb->get_col( "SHOW COLUMNS FROM {$runs_table}", 0 ) );
		$current_columns = [ 'id', 'run_id', 'bulk_run_uuid', 'bulk_item_id', 'run_item_uuid', 'campaign_group_uuid', 'item_ordinal', 'job_id', 'run_target', 'subject_id', 'target_type', 'parent_product_id', 'variation_id', 'target_snapshot', 'source_roles_snapshot', 'destination_snapshot', 'workflow', 'request_id', 'source_attachment_id', 'source_attachment_ids', 'expected_output_count', 'estimated_cost', 'product_title', 'user_id', 'started_at', 'ended_at', 'final_status', 'error_code', 'error_message', 'attachment_id', 'job_ids', 'attachments_map', 'images_count', 'quality', 'last_api_request_at', 'finalize_attempts', 'finalize_meta', 'delivery_state', 'credit_settlement_state' ];
        sort( $run_columns );
        sort( $current_columns );
        if ( $current_columns !== $run_columns ) {
            return false;
        }
		$run_job_column = $wpdb->get_row(
			$wpdb->prepare( "SHOW COLUMNS FROM {$runs_table} LIKE %s", 'job_id' ),
			ARRAY_A
		);
		if ( ! is_array( $run_job_column ) || 'varchar(' . Upstream_Identifier::MAX_LENGTH . ')' !== strtolower( (string) ( $run_job_column['Type'] ?? '' ) ) ) {
			return false;
		}
        $target_column = $wpdb->get_row(
            $wpdb->prepare( "SHOW COLUMNS FROM {$runs_table} LIKE %s", 'target_type' ),
            ARRAY_A
        );
        if ( ! is_array( $target_column ) || 'unknown' !== (string) ( $target_column['Default'] ?? '' ) ) {
            return false;
        }
        $run_indexes = array_values( array_unique( array_map( 'strval', (array) $wpdb->get_col( "SHOW INDEX FROM {$runs_table}", 2 ) ) ) );
        $current_indexes = [ 'PRIMARY', 'run_id', 'job_id', 'user_run_subject_started', 'user_workflow_subject_started', 'request_id', 'bulk_run_uuid_started', 'bulk_run_item', 'run_item_uuid', 'campaign_group_uuid', 'user_workflow_item', 'started_at', 'last_api_request_at', 'status_api_time' ];
        sort( $run_indexes );
        sort( $current_indexes );
        return $current_indexes === $run_indexes;
    }

    /**
     * Get a privacy-sanitized run row by run_id for background workers.
     *
     * @param string $run_id Run ID.
     * @return array<string, mixed>
     */
    public function get_run_row( string $run_id ): array {
        if ( '' === trim( $run_id ) ) {
            return [];
        }
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_runs_table_name();
        $row = $wpdb->get_row(
            $wpdb->prepare( "SELECT * FROM {$table} WHERE run_id = %s LIMIT 1", $run_id ),
            ARRAY_A
        );
        return is_array( $row ) ? $row : [];
    }

    /** @param mixed $snapshot Stored snapshot. */
    private function sanitize_identity_snapshot( $snapshot ): string {
        if ( $snapshot instanceof \stdClass ) {
            $snapshot = get_object_vars( $snapshot );
        } elseif ( is_string( $snapshot ) ) {
            $trimmed = trim( $snapshot );
            $decoded = json_decode( $trimmed, true );
            if ( ! is_array( $decoded ) || ! fotogenic_ai_str_starts_with( $trimmed, '{' ) ) {
                return '';
            }
            $snapshot = $decoded;
        }
        if ( ! is_array( $snapshot ) || ( ! empty( $snapshot ) && fotogenic_ai_array_is_list( $snapshot ) ) ) {
            return '';
        }
        $encoded = wp_json_encode( (object) $snapshot, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE );
        return is_string( $encoded ) ? $encoded : '';
    }

    private function get_table_name(): string {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_NAME;
    }

    private function get_runs_table_name(): string {
        global $wpdb;
        return $wpdb->prefix . self::RUNS_TABLE_NAME;
    }

    private function delete_log_db_entries(): int {
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_table_name();
        $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE id >= %d", 0 ) );
        return is_int( $deleted ) ? $deleted : 0;
    }

    private function purge_db_retention( int $cutoff_timestamp ): void {
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_table_name();
        $cutoff = gmdate( 'Y-m-d H:i:s', $cutoff_timestamp );
        $wpdb->query(
            $wpdb->prepare( "DELETE FROM {$table} WHERE event_time < %s", $cutoff )
        );
        $count = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE id >= %d",
                0
            )
        );
        $max_rows = $this->get_max_db_rows();
        if ( $count > $max_rows ) {
            $excess = $count - $max_rows;
            $wpdb->query(
                $wpdb->prepare( "DELETE FROM {$table} ORDER BY event_time ASC LIMIT %d", $excess )
            );
        }

        $runs_table = $this->get_runs_table_name();
        $wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$runs_table}
				WHERE started_at < %s
					AND COALESCE(credit_settlement_state, '') NOT LIKE %s
					AND COALESCE(credit_settlement_state, '') NOT LIKE %s",
				$cutoff,
				'%"status":"pending"%',
				'%"status":"settling"%'
			)
        );
        $run_count = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$runs_table} WHERE id >= %d",
                0
            )
        );
        $max_run_rows = $this->get_max_run_rows();
        if ( $run_count > $max_run_rows ) {
            $excess = $run_count - $max_run_rows;
            $wpdb->query(
				$wpdb->prepare(
					"DELETE FROM {$runs_table}
					WHERE COALESCE(credit_settlement_state, '') NOT LIKE %s
						AND COALESCE(credit_settlement_state, '') NOT LIKE %s
					ORDER BY started_at ASC
					LIMIT %d",
					'%"status":"pending"%',
					'%"status":"settling"%',
					$excess
				)
            );
        }
    }

    private function sanitize_bulk_run_uuid( string $bulk_run_uuid ): string {
        $bulk_run_uuid = strtolower( trim( $bulk_run_uuid ) );
        if ( '' === $bulk_run_uuid ) {
            return '';
        }
        if ( ! preg_match( '/^[a-f0-9-]{36}$/', $bulk_run_uuid ) ) {
            return '';
        }
        return $bulk_run_uuid;
    }

    private function maybe_opportunistic_purge(): void {
		if ( ! Background_Runtime_State::is_enabled() || ! is_admin() ) {
            return;
        }
        if ( random_int( 1, 100 ) !== 1 ) {
            return;
        }
        if ( get_transient( self::PURGE_LOCK_KEY ) ) {
            return;
        }
        set_transient( self::PURGE_LOCK_KEY, 1, 10 * MINUTE_IN_SECONDS );
        $this->run_purge();
    }

    public function run_purge(): void {
		if ( ! Background_Runtime_State::is_enabled() ) {
			wp_unschedule_hook( 'fotogenic_ai_log_purge' );
			return;
		}
        $cutoff = time() - ( $this->get_retention_days() * DAY_IN_SECONDS );
        $this->purge_db_retention( $cutoff );
    }

    private function mark_stale_runs(): void {
        $this->ensure_tables();
        global $wpdb;
        $table = $this->get_runs_table_name();
        $rows = $wpdb->get_results(
            "SELECT run_id FROM {$table} WHERE final_status IS NULL OR final_status IN ('started','starting','processing')", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- The table name is plugin-owned and the query has no user input.
            ARRAY_A
        );
        if ( empty( $rows ) ) {
            return;
        }
        foreach ( $rows as $row ) {
            $run_id = (string) ( $row['run_id'] ?? '' );
            if ( '' === $run_id ) {
                continue;
            }
            Workflow_Start_Coordinator::terminalize_stale_run( $run_id );
        }
    }

    private function get_retention_days(): int {
        $days = (int) apply_filters( 'fotogenic_ai_log_retention_days', self::RETENTION_DAYS );
        return max( 1, $days );
    }

    private function get_export_max_rows(): int {
        $rows = (int) apply_filters( 'fotogenic_ai_log_export_max_rows', self::EXPORT_MAX_ROWS_DEFAULT );
        return max( 100, min( 20000, $rows ) );
    }

    private function get_max_db_rows(): int {
        $rows = (int) apply_filters( 'fotogenic_ai_log_max_rows', self::MAX_DB_ROWS );
        return max( 1000, $rows );
    }

    private function get_max_run_rows(): int {
        $rows = (int) apply_filters( 'fotogenic_ai_log_max_runs', self::MAX_RUN_ROWS );
        return max( 100, $rows );
    }

    /**
     * Max elapsed runtime (seconds) before marking run as timeout. Used by poll and background finalizer.
     *
     * @param string $workflow Workflow identifier.
     * @return int
     */
    public function get_generation_timeout_seconds( string $workflow = '' ): int {
        $workflow = sanitize_key( $workflow );
		$defaults = [
			'campaign_set'        => 900,
			'increase_resolution' => 600,
		];
		$default = $defaults[ $workflow ] ?? 120;
        $seconds = (int) apply_filters( 'fotogenic_ai_generation_timeout_seconds', $default, $workflow );
        return max( 60, $seconds );
    }
}
