<?php

declare(strict_types=1);

namespace BadamSoft\WooProductExporter\Scheduler;

use DateTimeImmutable;
use Exception;
use wpdb;

class ScheduledExportManager {
    private wpdb $db;
    private bool $runsTableSchemaChecked = false;
    private bool $runsTableSupportsFileFormat = false;
    private bool $runsTableExistsChecked = false;
    private bool $runsTableExists = false;
    private bool $tasksTableSchemaChecked = false;
    private bool $tasksTableExists = false;

    /**
     * ScheduledExportManager constructor.
     *
     * @param wpdb $db WordPress database connection.
     */
    public function __construct( wpdb $db ) {
        $this->db = $db;
    }

    private function normalizeDate( string $date, string $boundary = 'start' ): string {
        $timezone = wp_timezone();

        try {
            $date_time = new DateTimeImmutable( $date, $timezone );
        } catch ( Exception $exception ) {
            return current_time( 'mysql' );
        }

        $date_time = 'end' === $boundary
            ? $date_time->setTime( 23, 59, 59 )
            : $date_time->setTime( 0, 0, 0 );

        return $date_time->format( 'Y-m-d H:i:s' );
    }

    /**
     * Get the database table name for scheduled export tasks.
     */
    public function get_tasks_table(): string {
        return $this->db->prefix . 'prodexfo_scheduled_exports';
    }

    /**
     * Get the database table name for export run history.
     */
    public function get_runs_table(): string {
        return $this->db->prefix . 'prodexfo_export_runs';
    }

    /**
     * Get the database table name for connection profiles.
     */
    public function get_profiles_table(): string {
        return $this->db->prefix . 'prodexfo_connection_profiles';
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function get_all_tasks(): array {
        $table = $this->get_tasks_table();

        if ( ! $this->tasksTableExists() ) {
            return [];
        }

        /** @var array<int, array<string, mixed>>|null $rows */
        $rows = $this->db->get_results( "SELECT * FROM {$table} ORDER BY id ASC", ARRAY_A );

        if ( ! is_array( $rows ) ) {
            return [];
        }

        return array_map( [ $this, 'format_task' ], $rows );
    }

    /**
     * @return array<string, mixed>|null
     */
    public function get_task( int $id ): ?array {
        if ( $id <= 0 ) {
            return null;
        }

        if ( ! $this->tasksTableExists() ) {
            return null;
        }

        $table = $this->get_tasks_table();

        /** @var array<string, mixed>|null $row */
        $row = $this->db->get_row(
            $this->db->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ),
            ARRAY_A
        );

        if ( ! $row ) {
            return null;
        }

        return $this->format_task( $row );
    }

    /**
     * Create a scheduled export task row.
     *
     * @param array<string, mixed> $data Raw task payload.
     * @return int                       Inserted task ID.
     */
    public function create_task( array $data ): int {
        if ( ! $this->tasksTableExists() ) {
            return 0;
        }

        $table = $this->get_tasks_table();

        $defaults = [
            'name'                       => '',
            'template_id'                => '',
            'enabled'                    => 1,
            'status'                     => 'idle',
            'schedule_type'              => 'cron',
            'schedule_cron'              => '',
            'schedule_interval'          => '',
            'schedule_timezone'          => 'UTC',
            'schedule_payload'           => null,
            'next_run_at'                => null,
            'incremental'                => 0,
            'incremental_mode'           => 'disabled',
            'incremental_field'          => 'post_modified',
            'incremental_anchor_value'   => null,
            'actions'                    => null,
            'last_action_log'            => null,
            'last_run_at'                => null,
            'created_at'                 => current_time( 'mysql' ),
            'updated_at'                 => current_time( 'mysql' ),
        ];

        $payload = array_merge( $defaults, $this->normalizeTaskPayload( $data ) );

        $this->db->insert( $table, $payload );

        return (int) $this->db->insert_id;
    }

    /**
     * Update fields of an existing scheduled export task.
     *
     * @param int                  $id   Task ID.
     * @param array<string, mixed> $data Fields to update.
     */
    public function update_task( int $id, array $data ): void {
        if ( $id <= 0 ) {
            return;
        }

        if ( ! $this->tasksTableExists() ) {
            return;
        }

        $table = $this->get_tasks_table();

        $payload = array_merge(
            [ 'updated_at' => current_time( 'mysql' ) ],
            $data
        );

        $this->db->update(
            $table,
            $this->normalizeTaskPayload( $payload ),
            [ 'id' => $id ]
        );
    }

    /**
     * Delete a scheduled export task by ID.
     */
    public function delete_task( int $id ): void {
        if ( $id <= 0 ) {
            return;
        }

        if ( ! $this->tasksTableExists() ) {
            return;
        }

        $this->db->delete( $this->get_tasks_table(), [ 'id' => $id ] );
    }

    /**
     * Delete a single export run entry.
     */
    public function delete_run( int $id ): bool {
        if ( $id <= 0 ) {
            return false;
        }

        $table = $this->get_runs_table();
        $deleted = $this->db->delete( $table, [ 'id' => $id ], [ '%d' ] );

        return false !== $deleted && $deleted > 0;
    }

    /**
     * Insert a new export run row into the runs table.
     *
     * @param array<string, mixed> $data Run metadata payload.
     * @return int                       Inserted run ID.
     */
    public function record_run( array $data ): int {
        $table = $this->get_runs_table();

        $this->ensureRunsTableSchema();

        if ( ! $this->runsTableSupportsFileFormat && isset( $data['file_format'] ) ) {
            unset( $data['file_format'] );
        }

        $defaults = [
            'task_id'           => 0,
            'template_id'       => '',
            'run_type'          => 'scheduled',
            'started_at'        => current_time( 'mysql' ),
            'finished_at'       => current_time( 'mysql' ),
            'status'            => 'success',
            'rows_exported'     => 0,
            'file_path'         => null,
            'file_size'         => 0,
            'images_zip_path'   => null,
            'images_zip_size'   => 0,
            'actions'           => null,
            'action_results'    => null,
            'incremental_from'  => null,
            'incremental_to'    => null,
            'log'               => null,
            'created_at'        => current_time( 'mysql' ),
        ];

        $payload = array_merge( $defaults, $this->normalizeRunPayload( $data ) );

        $this->db->insert( $table, $payload );

        return (int) $this->db->insert_id;
    }

    /**
     * Update actions and action_results columns for a run.
     *
     * @param int                   $run_id  Run identifier.
     * @param array<string, mixed>  $actions Actions payload.
     * @param array<string, mixed>  $results Action result payload.
     */
    public function update_run_actions( int $run_id, array $actions, array $results ): void {
        $this->db->update(
            $this->get_runs_table(),
            [
                'actions'        => wp_json_encode( $actions ),
                'action_results' => wp_json_encode( $results ),
            ],
            [ 'id' => $run_id ]
        );
    }

    /**
     * Update file-related metadata (path, size, format) for a run.
     *
     * @param int                  $run_id Run identifier.
     * @param array<string, mixed> $meta   Meta values to merge.
     * @return bool                        True on success.
     */
    public function update_run_file_meta( int $run_id, array $meta ): bool {
        if ( $run_id <= 0 ) {
            return false;
        }

        $defaults = [
            'file_path'       => null,
            'file_size'       => 0,
            'images_zip_path' => null,
            'images_zip_size' => 0,
            'file_format'     => '',
            'rows_exported'   => 0,
            'status'          => 'success',
            'finished_at'     => current_time( 'mysql' ),
        ];

        $payload = array_merge( $defaults, $meta );

        $updated = $this->db->update(
            $this->get_runs_table(),
            $payload,
            [ 'id' => $run_id ]
        );

        return false !== $updated;
    }

    public function update_run_zip_meta( int $run_id, string $zip_path, int $zip_size ): bool {
        if ( $run_id <= 0 ) {
            return false;
        }

        $zip_path = trim( $zip_path );
        $zip_size = max( 0, $zip_size );

        $updated = $this->db->update(
            $this->get_runs_table(),
            [
                'images_zip_path' => '' === $zip_path ? null : $zip_path,
                'images_zip_size' => $zip_size,
            ],
            [ 'id' => $run_id ]
        );

        return false !== $updated;
    }

    /**
     * Query export runs with optional filters and pagination.
     *
     * @param array<string, mixed> $args
     * @return array<int, array<string, mixed>>
     */
    public function get_runs( array $args = [] ): array {
        $defaults = [
            'status'        => '',
            'task_id'       => 0,
            'template_id'   => '',
            'template_ids'  => [],
            'run_type'      => '',
            'date_from'     => '',
            'date_to'       => '',
            'task_name'     => '',
            'limit'         => 100,
            'offset'        => 0,
        ];

        $args = wp_parse_args( $args, $defaults );

        $runs_table  = $this->get_runs_table();
        $tasks_table = $this->get_tasks_table();

        $join_tasks = $this->tasksTableExists();

        $sql    = $join_tasks
            ? "SELECT runs.*, tasks.name AS task_name FROM {$runs_table} runs LEFT JOIN {$tasks_table} tasks ON runs.task_id = tasks.id"
            : "SELECT runs.*, '' AS task_name FROM {$runs_table} runs";
        $values = [];

        $clauses = [];

        if ( $args['status'] ) {
            $clauses[] = 'runs.status = %s';
            $values[]  = sanitize_key( $args['status'] );
        }

        if ( $args['task_id'] ) {
            $clauses[] = 'runs.task_id = %d';
            $values[]  = (int) $args['task_id'];
        }

        if ( $args['template_id'] ) {
            $clauses[] = 'runs.template_id = %s';
            $values[]  = sanitize_text_field( $args['template_id'] );
        }

        $template_ids = [];
        if ( isset( $args['template_ids'] ) && is_array( $args['template_ids'] ) ) {
            foreach ( $args['template_ids'] as $template_id_value ) {
                $template_id_value = sanitize_text_field( (string) $template_id_value );

                if ( '' !== $template_id_value ) {
                    $template_ids[] = $template_id_value;
                }
            }
        }

        if ( ! empty( $template_ids ) ) {
            $placeholders = implode( ', ', array_fill( 0, count( $template_ids ), '%s' ) );
            $clauses[]    = "runs.template_id IN ( {$placeholders} )";
            $values       = array_merge( $values, $template_ids );
        }

        if ( $args['run_type'] ) {
            $clauses[] = 'runs.run_type = %s';
            $values[]  = sanitize_key( $args['run_type'] );
        }

        if ( $args['date_from'] ) {
            $clauses[] = 'runs.started_at >= %s';
            $values[]  = $this->normalizeDate( $args['date_from'], 'start' );
        }

        if ( $args['date_to'] ) {
            $clauses[] = 'runs.started_at <= %s';
            $values[]  = $this->normalizeDate( $args['date_to'], 'end' );
        }

        if ( $args['task_name'] && $join_tasks ) {
            $clauses[] = 'tasks.name LIKE %s';
            $values[]  = '%' . $this->db->esc_like( $args['task_name'] ) . '%';
        }

        if ( ! empty( $clauses ) ) {
            $sql .= ' WHERE ' . implode( ' AND ', $clauses );
        }

        $sql .= ' ORDER BY runs.started_at DESC LIMIT %d OFFSET %d';
        $limit   = max( 1, (int) $args['limit'] );
        $offset  = max( 0, (int) $args['offset'] );
        $values[] = $limit;
        $values[] = $offset;

        $prepared_sql = $this->db->prepare( $sql, $values );

        /** @var array<int, array<string, mixed>>|null $rows */
        $rows = $this->db->get_results( $prepared_sql, ARRAY_A );

        if ( ! is_array( $rows ) ) {
            return [];
        }

        return array_map( [ $this, 'format_run' ], $rows );
    }

    /**
     * Count export runs matching the given filters.
     *
     * @param array<string, mixed> $args
     */
    public function count_runs( array $args = [] ): int {
        $defaults = [
            'status'        => '',
            'task_id'       => 0,
            'template_id'   => '',
            'template_ids'  => [],
            'run_type'      => '',
            'date_from'     => '',
            'date_to'       => '',
            'task_name'     => '',
        ];

        $args = wp_parse_args( $args, $defaults );

        $runs_table  = $this->get_runs_table();
        $tasks_table = $this->get_tasks_table();

        $sql    = "SELECT COUNT(*) FROM {$runs_table} runs";
        $values = [];

        $join_tasks = $args['task_name'] ? $this->tasksTableExists() : false;

        if ( $join_tasks ) {
            $sql .= " LEFT JOIN {$tasks_table} tasks ON runs.task_id = tasks.id";
        }

        $clauses = [];

        if ( $args['status'] ) {
            $clauses[] = 'runs.status = %s';
            $values[]  = sanitize_key( $args['status'] );
        }

        if ( $args['task_id'] ) {
            $clauses[] = 'runs.task_id = %d';
            $values[]  = (int) $args['task_id'];
        }

        if ( $args['template_id'] ) {
            $clauses[] = 'runs.template_id = %s';
            $values[]  = sanitize_text_field( $args['template_id'] );
        }

        $template_ids = [];
        if ( isset( $args['template_ids'] ) && is_array( $args['template_ids'] ) ) {
            foreach ( $args['template_ids'] as $template_id_value ) {
                $template_id_value = sanitize_text_field( (string) $template_id_value );

                if ( '' !== $template_id_value ) {
                    $template_ids[] = $template_id_value;
                }
            }
        }

        if ( ! empty( $template_ids ) ) {
            $placeholders = implode( ', ', array_fill( 0, count( $template_ids ), '%s' ) );
            $clauses[]    = "runs.template_id IN ( {$placeholders} )";
            $values       = array_merge( $values, $template_ids );
        }

        if ( $args['run_type'] ) {
            $clauses[] = 'runs.run_type = %s';
            $values[]  = sanitize_key( $args['run_type'] );
        }

        if ( $args['date_from'] ) {
            $clauses[] = 'runs.started_at >= %s';
            $values[]  = $this->normalizeDate( $args['date_from'], 'start' );
        }

        if ( $args['date_to'] ) {
            $clauses[] = 'runs.started_at <= %s';
            $values[]  = $this->normalizeDate( $args['date_to'], 'end' );
        }

        if ( $args['task_name'] && $join_tasks ) {
            $clauses[] = 'tasks.name LIKE %s';
            $values[]  = '%' . $this->db->esc_like( $args['task_name'] ) . '%';
        }

        if ( ! empty( $clauses ) ) {
            $sql .= ' WHERE ' . implode( ' AND ', $clauses );
        }

        $prepared_sql = ! empty( $values ) ? $this->db->prepare( $sql, $values ) : $sql;
        $count        = $this->db->get_var( $prepared_sql );

        return (int) $count;
    }

    private function tasksTableExists(): bool {
        if ( $this->tasksTableSchemaChecked ) {
            return $this->tasksTableExists;
        }

        $table = $this->get_tasks_table();

        $this->ensureTasksTableExists( $table );

        $this->tasksTableSchemaChecked = true;

        return $this->tasksTableExists;
    }

    private function ensureTasksTableExists( string $table ): void {
        $exists = $this->db->get_var(
            $this->db->prepare(
                'SHOW TABLES LIKE %s',
                $table
            )
        );

        if ( null !== $exists ) {
            $this->tasksTableExists = true;
            return;
        }

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset_collate = $this->db->get_charset_collate();

        $sql_tasks = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(191) NOT NULL,
            template_id VARCHAR(64) NOT NULL,
            enabled TINYINT(1) NOT NULL DEFAULT 1,
            status VARCHAR(20) NOT NULL DEFAULT 'idle',
            schedule_type VARCHAR(20) NOT NULL DEFAULT 'cron',
            schedule_cron VARCHAR(100) NOT NULL DEFAULT '',
            schedule_interval VARCHAR(50) NOT NULL DEFAULT '',
            schedule_timezone VARCHAR(64) NOT NULL DEFAULT 'UTC',
            schedule_payload LONGTEXT NULL,
            next_run_at DATETIME NULL DEFAULT NULL,
            incremental TINYINT(1) NOT NULL DEFAULT 0,
            incremental_mode VARCHAR(20) NOT NULL DEFAULT 'disabled',
            incremental_field VARCHAR(64) NOT NULL DEFAULT 'post_modified',
            incremental_anchor_value DATETIME NULL DEFAULT NULL,
            actions LONGTEXT NULL,
            last_action_log LONGTEXT NULL,
            last_run_at DATETIME NULL DEFAULT NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY  (id),
            KEY template_id (template_id),
            KEY enabled (enabled),
            KEY next_run_at (next_run_at)
        ) {$charset_collate};";

        \dbDelta( $sql_tasks );

        $exists_after = $this->db->get_var(
            $this->db->prepare(
                'SHOW TABLES LIKE %s',
                $table
            )
        );

        $this->tasksTableExists = null !== $exists_after;
    }

    /**
     * Convenience wrapper to get the most recent runs.
     *
     * @param int $limit Maximum number of runs.
     * @return array<int, array<string, mixed>>
     */
    public function get_recent_runs( int $limit = 100 ): array {
        return $this->get_runs( [ 'limit' => $limit ] );
    }

    /**
     * Fetch a single run by ID and normalize its structure.
     *
     * @return array<string, mixed>|null
     */
    public function get_run( int $id ): ?array {
        $row = $this->db->get_row(
            $this->db->prepare( 'SELECT * FROM ' . $this->get_runs_table() . ' WHERE id = %d', $id ),
            ARRAY_A
        );

        if ( ! $row ) {
            return null;
        }

        return $this->format_run( $row );
    }

    /**
     * @param array<string, mixed> $data
     * @return array<string, mixed>
     */
    private function normalizeTaskPayload( array $data ): array {
        if ( isset( $data['schedule_payload'] ) && is_array( $data['schedule_payload'] ) ) {
            $data['schedule_payload'] = wp_json_encode( $data['schedule_payload'] );
        }

        if ( isset( $data['actions'] ) && is_array( $data['actions'] ) ) {
            $data['actions'] = wp_json_encode( $data['actions'] );
        }

        if ( isset( $data['last_action_log'] ) && is_array( $data['last_action_log'] ) ) {
            $data['last_action_log'] = wp_json_encode( $data['last_action_log'] );
        }

        return $data;
    }

    /**
     * @param array<string, mixed> $data
     * @return array<string, mixed>
     */
    private function normalizeRunPayload( array $data ): array {
        foreach ( [ 'actions', 'action_results' ] as $key ) {
            if ( isset( $data[ $key ] ) && is_array( $data[ $key ] ) ) {
                $data[ $key ] = wp_json_encode( $data[ $key ] );
            }
        }

        if ( isset( $data['log'] ) && is_array( $data['log'] ) ) {
            $data['log'] = wp_json_encode( $data['log'] );
        }

        return $data;
    }

    /**
     * Update last_run_at column for a task.
     */
    public function mark_last_run( int $id, string $timestamp ): void {
        $this->update_task(
            $id,
            [
                'last_run_at' => $timestamp,
            ]
        );
    }

    /**
     * @param array<string, mixed> $task
     * @return array<string, mixed>
     */
    public function format_task( array $task ): array {
        foreach ( [ 'schedule_payload', 'actions', 'last_action_log' ] as $key ) {
            if ( isset( $task[ $key ] ) && is_string( $task[ $key ] ) && '' !== $task[ $key ] ) {
                $decoded = json_decode( (string) $task[ $key ], true );
                $task[ $key ] = is_array( $decoded ) ? $decoded : [];
            } elseif ( ! isset( $task[ $key ] ) ) {
                $task[ $key ] = [];
            }
        }

        $task['enabled'] = (int) ( $task['enabled'] ?? 0 );

        return $task;
    }

    /**
     * @param array<string, mixed> $run
     * @return array<string, mixed>
     */
    public function format_run( array $run ): array {
        foreach ( [ 'actions', 'action_results' ] as $key ) {
            if ( isset( $run[ $key ] ) && is_string( $run[ $key ] ) && '' !== $run[ $key ] ) {
                $decoded = json_decode( (string) $run[ $key ], true );
                $run[ $key ] = is_array( $decoded ) ? $decoded : [];
            } elseif ( ! isset( $run[ $key ] ) ) {
                $run[ $key ] = [];
            }
        }

        $run['file_size']       = isset( $run['file_size'] ) ? (int) $run['file_size'] : 0;
        $run['task_id']         = isset( $run['task_id'] ) ? (int) $run['task_id'] : 0;
        $run['file_path_local'] = isset( $run['file_path'] ) ? (string) $run['file_path'] : '';
        $run['file_url']        = $this->maybeConvertFilePathToUrl( $run['file_path_local'] );

        if ( $run['file_url'] ) {
            $run['file_path'] = $run['file_url'];
        }

        $run['images_zip_size']        = isset( $run['images_zip_size'] ) ? (int) $run['images_zip_size'] : 0;
        $run['images_zip_path_local']  = isset( $run['images_zip_path'] ) ? (string) $run['images_zip_path'] : '';
        $run['images_zip_url']         = $this->maybeConvertFilePathToUrl( $run['images_zip_path_local'] );

        if ( $run['images_zip_url'] ) {
            $run['images_zip_path'] = $run['images_zip_url'];
        }

        $stored_format = isset( $run['file_format'] ) ? strtoupper( (string) $run['file_format'] ) : '';

        $file_source = $run['file_path_local'] ?? '';

        if ( ! $file_source && ! empty( $run['file_path'] ) ) {
            $file_source = (string) $run['file_path'];
        }

        $detected_format = $this->detectFileFormat( $file_source );

        $run['file_format'] = $stored_format ?: $detected_format;

        $run['fields']   = $this->decodeJsonColumn( $run['fields_json'] ?? null, [] );
        $run['filters']  = $this->decodeJsonColumn( $run['filters_json'] ?? null, [] );
        $run['settings'] = $this->decodeJsonColumn( $run['settings_json'] ?? null, [] );

        $run['started_at_iso']  = $this->format_run_datetime( $run['started_at'] ?? '' );
        $run['finished_at_iso'] = $this->format_run_datetime( $run['finished_at'] ?? '' );
        $run['created_at_iso']  = $this->format_run_datetime( $run['created_at'] ?? '' );

        unset( $run['fields_json'], $run['filters_json'], $run['settings_json'] );

        return $run;
    }

    private function format_run_datetime( $value ): string {
        $value = is_string( $value ) ? trim( $value ) : '';

        if ( '' === $value ) {
            return '';
        }

        try {
            $date = new DateTimeImmutable( $value, new \DateTimeZone( 'UTC' ) );
        } catch ( Exception $exception ) {
            return '';
        }

        return $date->setTimezone( wp_timezone() )->format( DATE_ATOM );
    }

    private function maybeConvertFilePathToUrl( string $path ): string {
        if ( '' === $path ) {
            return '';
        }

        if ( preg_match( '#^https?://#i', $path ) ) {
            return $path;
        }

        $uploads = wp_upload_dir();

        if ( empty( $uploads['basedir'] ) || empty( $uploads['baseurl'] ) ) {
            return '';
        }

        if ( 0 !== strpos( $path, $uploads['basedir'] ) ) {
            return '';
        }

        $relative = ltrim( str_replace( $uploads['basedir'], '', $path ), '/\\' );

        return trailingslashit( $uploads['baseurl'] ) . str_replace( DIRECTORY_SEPARATOR, '/', $relative );
    }

    private function detectFileFormat( string $path ): string {
        if ( '' === $path ) {
            return '';
        }

        $clean     = preg_split( '/[?#]/', $path )[0] ?? '';
        $extension = strtolower( pathinfo( $clean, PATHINFO_EXTENSION ) );

        return $extension ? strtoupper( $extension ) : '';
    }

    private function ensureRunsTableSchema(): void {
        if ( $this->runsTableSchemaChecked ) {
            return;
        }

        $table = $this->get_runs_table();

        $this->ensureRunsTableExists( $table );

        if ( ! $this->runsTableExists ) {
            $this->runsTableSupportsFileFormat = false;
            $this->runsTableSchemaChecked      = true;
            return;
        }

        $this->ensureRunsColumn( $table, 'file_format', "ALTER TABLE {$table} ADD COLUMN file_format VARCHAR(16) NOT NULL DEFAULT '' AFTER file_path" );
        $this->ensureRunsColumn( $table, 'fields_json', "ALTER TABLE {$table} ADD COLUMN fields_json LONGTEXT NULL AFTER file_path" );
        $this->ensureRunsColumn( $table, 'filters_json', "ALTER TABLE {$table} ADD COLUMN filters_json LONGTEXT NULL AFTER fields_json" );
        $this->ensureRunsColumn( $table, 'settings_json', "ALTER TABLE {$table} ADD COLUMN settings_json LONGTEXT NULL AFTER filters_json" );

        $this->runsTableSupportsFileFormat = $this->columnExists( $table, 'file_format' );
        $this->runsTableSchemaChecked      = true;
    }

    private function ensureRunsColumn( string $table, string $column, string $alter_sql ): void {
        if ( $this->columnExists( $table, $column ) ) {
            return;
        }

        $this->db->query( $alter_sql );
    }

    private function columnExists( string $table, string $column ): bool {
        $this->ensureRunsTableExists( $table );

        if ( ! $this->runsTableExists ) {
            return false;
        }

        $existing = $this->db->get_var( $this->db->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", $column ) );

        return null !== $existing;
    }

    private function ensureRunsTableExists( string $table ): void {
        if ( $this->runsTableExistsChecked ) {
            return;
        }

        $exists = $this->db->get_var(
            $this->db->prepare(
                'SHOW TABLES LIKE %s',
                $table
            )
        );

        $this->runsTableExists = null !== $exists;

        if ( ! $this->runsTableExists ) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';

            $charset_collate = $this->db->get_charset_collate();

            $sql_runs = "CREATE TABLE {$table} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                task_id BIGINT UNSIGNED NOT NULL,
                template_id VARCHAR(64) NOT NULL,
                run_type VARCHAR(20) NOT NULL DEFAULT 'scheduled',
                started_at DATETIME NOT NULL,
                finished_at DATETIME NULL DEFAULT NULL,
                status VARCHAR(20) NOT NULL,
                rows_exported BIGINT UNSIGNED NOT NULL DEFAULT 0,
                file_path TEXT NULL,
                fields_json LONGTEXT NULL,
                filters_json LONGTEXT NULL,
                settings_json LONGTEXT NULL,
                file_format VARCHAR(16) NOT NULL DEFAULT '',
                file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
                images_zip_path TEXT NULL,
                images_zip_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
                actions LONGTEXT NULL,
                action_results LONGTEXT NULL,
                incremental_from DATETIME NULL DEFAULT NULL,
                incremental_to DATETIME NULL DEFAULT NULL,
                log LONGTEXT NULL,
                created_at DATETIME NULL DEFAULT NULL,
                PRIMARY KEY  (id),
                KEY task_id (task_id),
                KEY started_at (started_at)
            ) {$charset_collate};";

            \dbDelta( $sql_runs );

            $exists = $this->db->get_var(
                $this->db->prepare(
                    'SHOW TABLES LIKE %s',
                    $table
                )
            );
            $this->runsTableExists = null !== $exists;
        }

        $this->runsTableExistsChecked = true;
    }

    /**
     * @param mixed $value
     * @param array<mixed> $default
     * @return array<mixed>
     */
    private function decodeJsonColumn( $value, array $default ): array {
        if ( ! is_string( $value ) || '' === $value ) {
            return $default;
        }

        $decoded = json_decode( $value, true );

        return is_array( $decoded ) ? $decoded : $default;
    }
}
