<?php

namespace WTeam_Executor;

use WP_Error;

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

// Database diagnostics use short-lived object caching for bounded reads.
class WTeam_Executor_Data_Reader {

    /** @var WTeam_Executor_Data_Policy */
    private $policy;

    /** @param WTeam_Executor_Data_Policy|null $policy */
    public function __construct( ?WTeam_Executor_Data_Policy $policy = null ) {
        $this->policy = $policy ? $policy : new WTeam_Executor_Data_Policy();
    }

    /**
     * Executes one structured, bounded database diagnostic request.
     *
     * The caller never supplies SQL. Tables and columns are checked against the
     * active WordPress database. Values are passed through $wpdb->prepare();
     * identifiers use WordPress 6.2+ native %i placeholders or the equivalent
     * strict validated legacy quoting path on WordPress 5.3-6.1.
     *
     * @param array<string,mixed> $request Request data.
     * @return array<string,mixed>
     */
    public function read( array $request ) {
        $type           = isset( $request['type'] ) ? sanitize_key( (string) $request['type'] ) : 'database';
        $operation      = isset( $request['operation'] ) ? sanitize_key( (string) $request['operation'] ) : '';
        $table          = isset( $request['table'] ) ? trim( (string) $request['table'] ) : '';
        $requested_rows = isset( $request['limit'] ) ? absint( $request['limit'] ) : ( isset( $request['max_rows'] ) ? absint( $request['max_rows'] ) : $this->policy->max_read_rows() );
        $limit          = max( 1, min( $this->policy->max_read_rows(), $requested_rows ) );

        $result = [
            'status'    => 'blocked',
            'type'      => $type,
            'operation' => $operation,
            'table'     => $table,
            'message'   => '',
            'data'      => [],
        ];

        if ( ! $this->policy->reads_enabled() ) {
            $result['message'] = __( 'Database diagnostics are disabled in Executor > Settings.', 'wteam-executor' );
            return $result;
        }

        if ( 'database' !== $type ) {
            $result['message'] = __( 'Only structured database diagnostic requests are supported.', 'wteam-executor' );
            return $result;
        }

        if ( 'table_list' === $operation ) {
            return $this->read_table_list( $result, $limit );
        }

        if ( 'table_schema' === $operation ) {
            return $this->read_table_schema( $result, $table );
        }

        if ( 'select_rows' === $operation ) {
            return $this->read_select_rows( $result, $request, $limit );
        }

        if ( 'count_rows' === $operation ) {
            return $this->read_count_rows( $result, $request );
        }

        $result['message'] = __( 'The database request operation must be table_list, table_schema, select_rows, or count_rows.', 'wteam-executor' );
        return $result;
    }

    /**
     * @param array<string,mixed> $result Result envelope.
     * @param int                 $limit Maximum tables returned.
     * @return array<string,mixed>
     */
    private function read_table_list( array $result, $limit ) {
        $tables = $this->allowed_table_names();
        if ( is_wp_error( $tables ) ) {
            $result['status']  = 'error';
            $result['message'] = $tables->get_error_message();
            return $result;
        }

        $total = count( $tables );
        $shown = array_slice( $tables, 0, $limit );
        $rows  = [];
        foreach ( $shown as $table ) {
            $rows[] = [ 'table' => $table ];
        }

        $result['status']  = 'ok';
        /* translators: %1$d: number of returned tables, %2$d: total allowed tables. */
        $result['message'] = sprintf( __( 'Listed %1$d of %2$d allowed table(s) from the active WordPress site.', 'wteam-executor' ), count( $rows ), $total );
        $result['data']    = [
            'row_count'   => count( $rows ),
            'total_rows'  => $total,
            'truncated'   => $total > count( $rows ),
            'rows'        => $rows,
            'fingerprint' => hash( 'sha256', wp_json_encode( $tables, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES ) ),
        ];

        return $result;
    }

    /**
     * @param array<string,mixed> $result Result envelope.
     * @param string              $table Requested table.
     * @return array<string,mixed>
     */
    private function read_table_schema( array $result, $table ) {
        global $wpdb;

        $normalized = $this->allowed_table( $table );
        if ( is_wp_error( $normalized ) ) {
            $result['message'] = $normalized->get_error_message();
            return $result;
        }

        $previous = $wpdb->suppress_errors( true );

        /*
         * Plugin Check cannot follow SQL preparation through prepare_database_query().
         * That helper validates identifiers and prepares all value placeholders before
         * the query reaches wpdb, so the DirectDB parameter findings are false positives.
         */
        // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
        $columns = $wpdb->get_results( $this->prepare_database_query( 'DESCRIBE %i', $normalized ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared by prepare_database_query(); live bounded diagnostic read; stale cached schema would be misleading.
        $indexes = $wpdb->get_results( $this->prepare_database_query( 'SHOW INDEX FROM %i', $normalized ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared by prepare_database_query(); live bounded diagnostic read; stale cached indexes would be misleading.
        $count   = $wpdb->get_var( $this->prepare_database_query( 'SELECT COUNT(*) FROM %i', $normalized ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared by prepare_database_query(); live bounded diagnostic count.
        // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter

        $error = (string) $wpdb->last_error;
        $wpdb->suppress_errors( $previous );

        if ( '' !== $error ) {
            $result['status']  = 'error';
            $result['message'] = __( 'Database schema inspection failed: ', 'wteam-executor' ) . $this->clean_text( $error, 1000 );
            return $result;
        }

        $columns = is_array( $columns ) ? $columns : [];
        $indexes = is_array( $indexes ) ? $indexes : [];
        $raw     = [
            'columns'        => $columns,
            'indexes'        => $indexes,
            'estimated_rows' => is_numeric( $count ) ? (int) $count : null,
        ];

        $result['status']  = 'ok';
        $result['table']   = $normalized;
        $result['message'] = __( 'Collected the allowed table schema, indexes, and row count. Row values were not sampled automatically.', 'wteam-executor' );
        $result['data']    = [
            'columns'        => $this->safe_rows( $columns ),
            'indexes'        => $this->safe_rows( $indexes ),
            'estimated_rows' => $raw['estimated_rows'],
            'primary_key'    => $this->primary_key_columns_from_indexes( $indexes ),
            'fingerprint'    => hash( 'sha256', wp_json_encode( $raw, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES ) ),
        ];

        return $result;
    }

    /**
     * @param array<string,mixed> $result Result envelope.
     * @param array<string,mixed> $request Structured request.
     * @param int                 $limit Maximum returned rows.
     * @return array<string,mixed>
     */
    private function read_select_rows( array $result, array $request, $limit ) {
        global $wpdb;

        $table = $this->allowed_table( isset( $request['table'] ) ? (string) $request['table'] : '' );
        if ( is_wp_error( $table ) ) {
            $result['message'] = $table->get_error_message();
            return $result;
        }

        $allowed_columns = $this->table_columns( $table );
        if ( is_wp_error( $allowed_columns ) ) {
            $result['status']  = 'error';
            $result['message'] = $allowed_columns->get_error_message();
            return $result;
        }

        $columns = isset( $request['columns'] ) && is_array( $request['columns'] ) ? $request['columns'] : [];
        $columns = $this->allowed_columns( $columns, $allowed_columns );
        if ( is_wp_error( $columns ) ) {
            $result['message'] = $columns->get_error_message();
            return $result;
        }

        if ( empty( $columns ) ) {
            $result['message'] = __( 'Select at least one explicit database column. Use table_schema first when the column names are unknown.', 'wteam-executor' );
            return $result;
        }

        $where = isset( $request['where'] ) && is_array( $request['where'] ) ? $request['where'] : [];
        if ( count( $where ) > 1 ) {
            $result['message'] = __( 'A structured database read may use at most one filter. Run another bounded diagnostic request when additional filtering is required.', 'wteam-executor' );
            return $result;
        }

        $filter = $this->normalize_where_filter( $where, $allowed_columns );
        if ( is_wp_error( $filter ) ) {
            $result['message'] = $filter->get_error_message();
            return $result;
        }

        $order_by = isset( $request['order_by'] ) ? trim( (string) $request['order_by'] ) : '';
        $direction = 'NONE';
        if ( '' !== $order_by ) {
            $order_by = $this->allowed_column( $order_by, $allowed_columns );
            if ( is_wp_error( $order_by ) ) {
                $result['message'] = $order_by->get_error_message();
                return $result;
            }

            $direction = isset( $request['order'] ) ? strtoupper( trim( (string) $request['order'] ) ) : 'ASC';
            if ( ! in_array( $direction, [ 'ASC', 'DESC' ], true ) ) {
                $result['message'] = __( 'Database sort direction must be ASC or DESC.', 'wteam-executor' );
                return $result;
            }
        }

        $fetch_limit  = min( $this->policy->max_read_rows() + 1, $limit + 1 );
        $prepare_args = $this->structured_select_arguments( $columns, $table, $filter, $order_by, $direction, $fetch_limit );
        $cache_key    = 'select_' . hash(
            'sha256',
            wp_json_encode(
                [ $table, $columns, $filter, $order_by, $direction, $fetch_limit ],
                JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES
            )
        );
        $cache_group  = 'executor_database_reads';
        $rows         = wp_cache_get( $cache_key, $cache_group );
        $error        = '';

        if ( false === $rows ) {
            $previous = $wpdb->suppress_errors( true );

            // Direct access is required for structured diagnostics across allowlisted WordPress tables; the result is cached below.
            /*
             * Plugin Check/WPCS cannot trace the prepared SQL returned by our compatibility
             * wrapper. The wrapper is required because WordPress 5.3-6.1 has no native %i
             * placeholder support; identifiers are allowlisted and normalized, while all
             * value placeholders still pass through $wpdb->prepare().
             */
            // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- No higher-level WordPress API supports this bounded diagnostic query.
            $rows = $wpdb->get_results(
                $this->prepare_database_query(
                    "SELECT
                        %i AS __executor_col_01, %i AS __executor_col_02, %i AS __executor_col_03, %i AS __executor_col_04, %i AS __executor_col_05,
                        %i AS __executor_col_06, %i AS __executor_col_07, %i AS __executor_col_08, %i AS __executor_col_09, %i AS __executor_col_10,
                        %i AS __executor_col_11, %i AS __executor_col_12, %i AS __executor_col_13, %i AS __executor_col_14, %i AS __executor_col_15,
                        %i AS __executor_col_16, %i AS __executor_col_17, %i AS __executor_col_18, %i AS __executor_col_19, %i AS __executor_col_20
                    FROM %i
                    WHERE (
                        %d = 0
                        OR CASE %d
                            WHEN 1 THEN %i = %s
                            WHEN 2 THEN %i <> %s
                            WHEN 3 THEN %i > %s
                            WHEN 4 THEN %i >= %s
                            WHEN 5 THEN %i < %s
                            WHEN 6 THEN %i <= %s
                            WHEN 7 THEN %i LIKE %s
                            WHEN 8 THEN %i NOT LIKE %s
                            WHEN 9 THEN %i IS NULL
                            WHEN 10 THEN %i IS NOT NULL
                            ELSE 0
                        END
                    )
                    ORDER BY
                        CASE WHEN %s = 'ASC' THEN %i END ASC,
                        CASE WHEN %s = 'DESC' THEN %i END DESC
                    LIMIT %d",
                    $prepare_args[0],
                    $prepare_args[1],
                    $prepare_args[2],
                    $prepare_args[3],
                    $prepare_args[4],
                    $prepare_args[5],
                    $prepare_args[6],
                    $prepare_args[7],
                    $prepare_args[8],
                    $prepare_args[9],
                    $prepare_args[10],
                    $prepare_args[11],
                    $prepare_args[12],
                    $prepare_args[13],
                    $prepare_args[14],
                    $prepare_args[15],
                    $prepare_args[16],
                    $prepare_args[17],
                    $prepare_args[18],
                    $prepare_args[19],
                    $prepare_args[20],
                    $prepare_args[21],
                    $prepare_args[22],
                    $prepare_args[23],
                    $prepare_args[24],
                    $prepare_args[25],
                    $prepare_args[26],
                    $prepare_args[27],
                    $prepare_args[28],
                    $prepare_args[29],
                    $prepare_args[30],
                    $prepare_args[31],
                    $prepare_args[32],
                    $prepare_args[33],
                    $prepare_args[34],
                    $prepare_args[35],
                    $prepare_args[36],
                    $prepare_args[37],
                    $prepare_args[38],
                    $prepare_args[39],
                    $prepare_args[40],
                    $prepare_args[41],
                    $prepare_args[42],
                    $prepare_args[43],
                    $prepare_args[44],
                    $prepare_args[45]
                ),
                ARRAY_A
            );
            // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter

            $error = (string) $wpdb->last_error;
            $wpdb->suppress_errors( $previous );

            if ( '' === $error && is_array( $rows ) ) {
                wp_cache_set( $cache_key, $rows, $cache_group, 15 );
            }
        }

        if ( '' !== $error ) {
            $result['status']  = 'error';
            $result['message'] = __( 'Database query failed: ', 'wteam-executor' ) . $this->clean_text( $error, 1000 );
            return $result;
        }

        $rows      = $this->project_structured_rows( is_array( $rows ) ? $rows : [], $columns );
        $truncated = count( $rows ) > $limit;
        if ( $truncated ) {
            $rows = array_slice( $rows, 0, $limit );
        }

        $safe_rows         = $this->safe_rows( $rows );
        $result['status']  = 'ok';
        $result['table']   = $table;
        /* translators: %d: number of database rows returned. */
        $result['message'] = sprintf( __( 'Returned %d row(s) from one structured read-only database request.', 'wteam-executor' ), count( $safe_rows ) );
        $result['data']    = [
            'row_count'   => count( $safe_rows ),
            'truncated'   => $truncated,
            'columns'     => $columns,
            'rows'        => $safe_rows,
            'fingerprint' => hash( 'sha256', wp_json_encode( [ $table, $columns, $filter, $order_by, $direction, $rows ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES ) ),
        ];

        return $result;
    }

    /**
     * @param array<string,mixed> $result Result envelope.
     * @param array<string,mixed> $request Structured request.
     * @return array<string,mixed>
     */
    private function read_count_rows( array $result, array $request ) {
        global $wpdb;

        $table = $this->allowed_table( isset( $request['table'] ) ? (string) $request['table'] : '' );
        if ( is_wp_error( $table ) ) {
            $result['message'] = $table->get_error_message();
            return $result;
        }

        $allowed_columns = $this->table_columns( $table );
        if ( is_wp_error( $allowed_columns ) ) {
            $result['status']  = 'error';
            $result['message'] = $allowed_columns->get_error_message();
            return $result;
        }

        if ( empty( $allowed_columns ) ) {
            $result['status']  = 'error';
            $result['message'] = __( 'The selected database table has no readable columns.', 'wteam-executor' );
            return $result;
        }

        $where = isset( $request['where'] ) && is_array( $request['where'] ) ? $request['where'] : [];
        if ( count( $where ) > 1 ) {
            $result['message'] = __( 'A structured database read may use at most one filter. Run another bounded diagnostic request when additional filtering is required.', 'wteam-executor' );
            return $result;
        }

        $filter = $this->normalize_where_filter( $where, $allowed_columns );
        if ( is_wp_error( $filter ) ) {
            $result['message'] = $filter->get_error_message();
            return $result;
        }

        $fallback       = (string) $allowed_columns[0];
        $filter_active  = empty( $filter ) ? 0 : 1;
        $filter_column  = $filter_active ? (string) $filter['column'] : $fallback;
        $filter_value   = $filter_active && array_key_exists( 'value', $filter ) ? (string) $filter['value'] : '';
        $operator_codes = [
            '='           => 1,
            '!='          => 2,
            '>'           => 3,
            '>='          => 4,
            '<'           => 5,
            '<='          => 6,
            'LIKE'        => 7,
            'NOT LIKE'    => 8,
            'IS NULL'     => 9,
            'IS NOT NULL' => 10,
        ];
        $filter_operator = $filter_active && isset( $operator_codes[ (string) $filter['operator'] ] )
            ? (int) $operator_codes[ (string) $filter['operator'] ]
            : 0;
        $like_value = '%' . $wpdb->esc_like( $filter_value ) . '%';

        $cache_key   = 'count_' . hash( 'sha256', wp_json_encode( [ $table, $filter ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES ) );
        $cache_group = 'executor_database_reads';
        $count       = wp_cache_get( $cache_key, $cache_group );
        $error       = '';

        if ( false === $count ) {
            $previous = $wpdb->suppress_errors( true );

            /*
             * Plugin Check/WPCS cannot trace the prepared SQL returned by our compatibility
             * wrapper. Table/column identifiers are allowlisted and normalized, and all
             * runtime values remain parameterized through $wpdb->prepare().
             */
            // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
            $count = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Exact structured count is cached below.
                $this->prepare_database_query(
                    "SELECT COUNT(*)
                    FROM %i
                    WHERE (
                        %d = 0
                        OR CASE %d
                            WHEN 1 THEN %i = %s
                            WHEN 2 THEN %i <> %s
                            WHEN 3 THEN %i > %s
                            WHEN 4 THEN %i >= %s
                            WHEN 5 THEN %i < %s
                            WHEN 6 THEN %i <= %s
                            WHEN 7 THEN %i LIKE %s
                            WHEN 8 THEN %i NOT LIKE %s
                            WHEN 9 THEN %i IS NULL
                            WHEN 10 THEN %i IS NOT NULL
                            ELSE 0
                        END
                    )",
                    $table,
                    $filter_active,
                    $filter_operator,
                    $filter_column,
                    $filter_value,
                    $filter_column,
                    $filter_value,
                    $filter_column,
                    $filter_value,
                    $filter_column,
                    $filter_value,
                    $filter_column,
                    $filter_value,
                    $filter_column,
                    $filter_value,
                    $filter_column,
                    $like_value,
                    $filter_column,
                    $like_value,
                    $filter_column,
                    $filter_column
                )
            );
            // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter

            $error = (string) $wpdb->last_error;
            $wpdb->suppress_errors( $previous );

            if ( '' === $error && is_numeric( $count ) ) {
                $count = (int) $count;
                wp_cache_set( $cache_key, $count, $cache_group, 15 );
            }
        }

        if ( '' !== $error ) {
            $result['status']  = 'error';
            $result['message'] = __( 'Database count query failed: ', 'wteam-executor' ) . $this->clean_text( $error, 1000 );
            return $result;
        }

        if ( ! is_numeric( $count ) ) {
            $result['status']  = 'error';
            $result['message'] = __( 'Database count query returned an invalid result.', 'wteam-executor' );
            return $result;
        }

        $count             = (int) $count;
        $result['status']  = 'ok';
        $result['table']   = $table;
        /* translators: %d: exact number of matching database rows. */
        $result['message'] = sprintf( __( 'Counted %d matching row(s) with one structured read-only database request.', 'wteam-executor' ), $count );
        $result['data']    = [
            'count'       => $count,
            'fingerprint' => hash( 'sha256', wp_json_encode( [ $table, $filter, $count ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES ) ),
        ];

        return $result;
    }

    /**
     * Build the fixed placeholder argument list for a structured SELECT.
     *
     * The SQL template itself is static. Unused output-column placeholders are
     * filled with the first validated requested column and are discarded after
     * the query, so arbitrary SQL or identifier fragments are never concatenated.
     *
     * @param array<int,string>   $columns Validated requested columns.
     * @param string              $table Validated table.
     * @param array<string,mixed> $filter Normalized optional filter.
     * @param string              $order_by Validated order column or empty string.
     * @param string              $direction ASC, DESC, or NONE.
     * @param int                 $limit Query row limit.
     * @return array<int,mixed>
     */
    private function structured_select_arguments( array $columns, $table, array $filter, $order_by, $direction, $limit ) {
        $fallback = (string) $columns[0];
        $args     = [];

        for ( $index = 0; $index < 20; $index++ ) {
            $args[] = isset( $columns[ $index ] ) ? (string) $columns[ $index ] : $fallback;
        }

        global $wpdb;

        $filter_active = empty( $filter ) ? 0 : 1;
        $filter_column = $filter_active ? (string) $filter['column'] : $fallback;
        $filter_value  = $filter_active && array_key_exists( 'value', $filter ) ? (string) $filter['value'] : '';
        $order_column  = '' !== $order_by ? (string) $order_by : $fallback;

        $operator_codes = [
            '='           => 1,
            '!='          => 2,
            '>'           => 3,
            '>='          => 4,
            '<'           => 5,
            '<='          => 6,
            'LIKE'        => 7,
            'NOT LIKE'    => 8,
            'IS NULL'     => 9,
            'IS NOT NULL' => 10,
        ];
        $filter_operator = $filter_active && isset( $operator_codes[ (string) $filter['operator'] ] )
            ? (int) $operator_codes[ (string) $filter['operator'] ]
            : 0;
        $like_value = '%' . $wpdb->esc_like( $filter_value ) . '%';

        $args[] = (string) $table;
        $args[] = $filter_active;
        $args[] = $filter_operator;

        foreach ( [ '=', '!=', '>', '>=', '<', '<=' ] as $operator ) {
            $args[] = $filter_column;
            $args[] = $filter_value;
        }
        $args[] = $filter_column; // LIKE.
        $args[] = $like_value;
        $args[] = $filter_column; // NOT LIKE.
        $args[] = $like_value;

        $args[] = $filter_column; // IS NULL.
        $args[] = $filter_column; // IS NOT NULL.
        $args[] = (string) $direction;
        $args[] = $order_column;
        $args[] = (string) $direction;
        $args[] = $order_column;
        $args[] = (int) $limit;

        return $args;
    }

    /**
     * Prepare a database query containing WordPress identifier placeholders.
     *
     * WordPress 6.2+ prepares %i natively. WordPress 5.3-6.1 does not know
     * that placeholder, so this compatibility path replaces only %i tokens
     * with strictly validated, backtick-quoted identifiers, then delegates all
     * value placeholders (%s, %d, %f) to wpdb::prepare(). Query templates are
     * internal/static and callers pass only table/column names that have already
     * passed the database allowlist.
     *
     * @param string $query SQL template.
     * @param mixed  ...$args Placeholder arguments.
     * @return string Prepared SQL.
     */
    private function prepare_database_query( $query, ...$args ) {
        global $wpdb;

        if ( $wpdb->has_cap( 'identifier_placeholders' ) ) {
            return $wpdb->prepare( $query, $args ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is an internal static template; identifiers and values are supplied separately.
        }

        $arg_index  = 0;
        $value_args = [];
        $invalid    = false;

        $legacy_query = preg_replace_callback(
            '/%(?:%|i|d|s|f)/',
            function ( $match ) use ( &$arg_index, $args, &$value_args, &$invalid ) {
                if ( '%%' === $match[0] ) {
                    return '%%';
                }

                if ( ! array_key_exists( $arg_index, $args ) ) {
                    $invalid = true;
                    return $match[0];
                }

                $value = $args[ $arg_index ];
                $arg_index++;

                if ( '%i' === $match[0] ) {
                    $identifier = $this->policy->normalize_identifier( (string) $value );
                    if ( is_wp_error( $identifier ) ) {
                        $invalid = true;
                        return '`__wteam_executor_invalid_identifier__`';
                    }

                    // normalize_identifier() rejects backticks; doubling remains defense in depth.
                    return '`' . str_replace( '`', '``', $identifier ) . '`';
                }

                $value_args[] = $value;
                return $match[0];
            },
            (string) $query
        );

        if ( null === $legacy_query || $invalid || $arg_index !== count( $args ) ) {
            // Fail closed if an internal template and its argument list ever diverge.
            return 'SELECT * FROM';
        }

        if ( empty( $value_args ) ) {
            return $legacy_query;
        }

        return $wpdb->prepare( $legacy_query, $value_args ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Legacy query contains only validated quoted identifiers plus standard value placeholders.
    }

    /**
     * Normalize the single optional structured filter.
     *
     * @param array<int,mixed>  $conditions Requested filters.
     * @param array<int,string> $allowed_columns Allowed columns.
     * @return array<string,mixed>|WP_Error
     */
    private function normalize_where_filter( array $conditions, array $allowed_columns ) {
        if ( empty( $conditions ) ) {
            return [];
        }

        $condition = reset( $conditions );
        if ( ! is_array( $condition ) ) {
            return new WP_Error( 'wteam_executor_invalid_where', __( 'The database filter must be a structured object.', 'wteam-executor' ) );
        }

        $column = isset( $condition['column'] ) ? $this->allowed_column( (string) $condition['column'], $allowed_columns ) : new WP_Error( 'wteam_executor_missing_where_column', __( 'The database filter requires a column.', 'wteam-executor' ) );
        if ( is_wp_error( $column ) ) {
            return $column;
        }

        $operator = isset( $condition['operator'] ) ? strtoupper( trim( (string) $condition['operator'] ) ) : '=';
        if ( ! in_array( $operator, [ '=', '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IS NULL', 'IS NOT NULL' ], true ) ) {
            return new WP_Error( 'wteam_executor_invalid_where_operator', __( 'The requested database filter operator is not allowed.', 'wteam-executor' ) );
        }

        $value = array_key_exists( 'value', $condition ) ? $condition['value'] : '';
        if ( null !== $value && ! is_scalar( $value ) ) {
            return new WP_Error( 'wteam_executor_invalid_where_value', __( 'The database filter value must be a scalar value or null.', 'wteam-executor' ) );
        }

        return [
            'column'   => $column,
            'operator' => $operator,
            'value'    => null === $value ? '' : (string) $value,
        ];
    }

    /**
     * Convert fixed SQL aliases back to the explicitly requested columns.
     *
     * @param array<int,array<string,mixed>> $rows Query rows.
     * @param array<int,string>              $columns Requested columns.
     * @return array<int,array<string,mixed>>
     */
    private function project_structured_rows( array $rows, array $columns ) {
        $projected = [];
        foreach ( $rows as $row ) {
            $item = [];
            foreach ( $columns as $index => $column ) {
                $alias = sprintf( '__executor_col_%02d', $index + 1 );
                if ( array_key_exists( $alias, $row ) ) {
                    $item[ $column ] = $row[ $alias ];
                }
            }
            $projected[] = $item;
        }
        return $projected;
    }

    /**
     * Returns the exact table name only when it belongs to the current site allowlist.
     *
     * @param string $table Table name.
     * @return string|WP_Error
     */
    private function allowed_table( $table ) {
        $normalized = $this->policy->normalize_table_name( $table );
        if ( is_wp_error( $normalized ) ) {
            return $normalized;
        }

        $allowed = $this->allowed_table_names();
        if ( is_wp_error( $allowed ) ) {
            return $allowed;
        }

        if ( ! in_array( $normalized, $allowed, true ) ) {
            return new WP_Error( 'wteam_executor_table_not_allowed', __( 'The requested table is not in the current WordPress site database allowlist.', 'wteam-executor' ) );
        }

        return $normalized;
    }

    /**
     * @return array<int,string>|WP_Error
     */
    private function allowed_table_names() {
        global $wpdb;

        if ( ! isset( $wpdb ) ) {
            return new WP_Error( 'wteam_executor_database_unavailable', __( 'The WordPress database connection is unavailable.', 'wteam-executor' ) );
        }

        $prefix_pattern = $wpdb->esc_like( (string) $wpdb->prefix ) . '%';
        $previous       = $wpdb->suppress_errors( true );
        $tables         = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $prefix_pattern ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Live table allowlist discovery for diagnostics.
        $error          = (string) $wpdb->last_error;
        $wpdb->suppress_errors( $previous );

        if ( '' !== $error ) {
            return new WP_Error( 'wteam_executor_database_table_list_failed', __( 'Database table listing failed: ', 'wteam-executor' ) . $this->clean_text( $error, 1000 ) );
        }

        $tables = is_array( $tables ) ? array_map( 'strval', $tables ) : [];

        // On multisite, selected WordPress global tables do not use the current blog prefix.
        foreach ( [ 'users', 'usermeta', 'blogs', 'blogmeta', 'site', 'sitemeta', 'signups', 'registration_log' ] as $property ) {
            if ( isset( $wpdb->{$property} ) && is_string( $wpdb->{$property} ) && '' !== $wpdb->{$property} && $this->table_exists( $wpdb->{$property} ) ) {
                $tables[] = $wpdb->{$property};
            }
        }

        $tables = array_values( array_unique( $tables ) );
        sort( $tables, SORT_STRING );
        return $tables;
    }

    /**
     * @param string $table Table name.
     * @return array<int,string>|WP_Error
     */
    private function table_columns( $table ) {
        global $wpdb;

        $previous = $wpdb->suppress_errors( true );

        // Plugin Check cannot trace the fully prepared SQL returned by prepare_database_query().
        // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows = $wpdb->get_results( $this->prepare_database_query( 'DESCRIBE %i', $table ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared by prepare_database_query(); live schema allowlist discovery for the requested table.
        // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter

        $error = (string) $wpdb->last_error;
        $wpdb->suppress_errors( $previous );

        if ( '' !== $error ) {
            return new WP_Error( 'wteam_executor_database_schema_failed', __( 'Database schema inspection failed: ', 'wteam-executor' ) . $this->clean_text( $error, 1000 ) );
        }

        $columns = [];
        foreach ( is_array( $rows ) ? $rows : [] as $row ) {
            if ( isset( $row['Field'] ) && is_string( $row['Field'] ) ) {
                $columns[] = $row['Field'];
            }
        }
        return array_values( array_unique( $columns ) );
    }

    /**
     * @param array<int,mixed>  $columns Requested columns.
     * @param array<int,string> $allowed_columns Allowed columns.
     * @return array<int,string>|WP_Error
     */
    private function allowed_columns( array $columns, array $allowed_columns ) {
        $result = [];
        foreach ( array_slice( $columns, 0, 20 ) as $column ) {
            if ( ! is_scalar( $column ) ) {
                return new WP_Error( 'wteam_executor_invalid_column', __( 'Database column names must be strings.', 'wteam-executor' ) );
            }
            $validated = $this->allowed_column( (string) $column, $allowed_columns );
            if ( is_wp_error( $validated ) ) {
                return $validated;
            }
            $result[] = $validated;
        }
        return array_values( array_unique( $result ) );
    }

    /**
     * @param string            $column Requested column.
     * @param array<int,string> $allowed_columns Allowed columns.
     * @return string|WP_Error
     */
    private function allowed_column( $column, array $allowed_columns ) {
        $normalized = $this->policy->normalize_identifier( $column );
        if ( is_wp_error( $normalized ) ) {
            return $normalized;
        }
        if ( ! in_array( $normalized, $allowed_columns, true ) ) {
            return new WP_Error( 'wteam_executor_column_not_allowed', __( 'The requested database column is not present in the selected table.', 'wteam-executor' ) );
        }
        return $normalized;
    }

    /**
     * @param string $table Table name.
     * @return bool
     */
    public function table_exists( $table ) {
        global $wpdb;

        if ( ! isset( $wpdb ) ) {
            return false;
        }

        $pattern = $wpdb->esc_like( (string) $table );
        $found   = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $pattern ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Exact live existence check for a diagnostic table.
        return (string) $found === (string) $table;
    }

    /**
     * @param array<int,array<string,mixed>> $indexes Table index rows.
     * @return array<int,string>
     */
    private function primary_key_columns_from_indexes( array $indexes ) {
        $primary = [];
        foreach ( $indexes as $index ) {
            if ( ! is_array( $index ) || 'PRIMARY' !== (string) ( $index['Key_name'] ?? '' ) || ! isset( $index['Column_name'] ) ) {
                continue;
            }

            $sequence = isset( $index['Seq_in_index'] ) ? max( 1, (int) $index['Seq_in_index'] ) : count( $primary ) + 1;
            $primary[ $sequence ] = (string) $index['Column_name'];
        }

        ksort( $primary, SORT_NUMERIC );
        return array_values( array_unique( $primary ) );
    }

            /**
     * @param array<int,array<string,mixed>> $rows Database rows.
     * @return array<int,array<string,mixed>>
     */
    private function safe_rows( array $rows ) {
        $safe = [];
        foreach ( $rows as $row ) {
            $safe[] = $this->safe_row( is_array( $row ) ? $row : [] );
        }
        return $safe;
    }

    /**
     * @param array<string,mixed> $row Database row.
     * @return array<string,mixed>
     */
    private function safe_row( array $row ) {
        if ( ! $this->policy->redact_sensitive_data() ) {
            foreach ( $row as $key => $value ) {
                $row[ $key ] = $this->safe_value( $value, false );
            }
            return $row;
        }

        $semantic_key = '';
        foreach ( [ 'option_name', 'meta_key', 'setting', 'name', 'key' ] as $candidate ) {
            if ( isset( $row[ $candidate ] ) && is_scalar( $row[ $candidate ] ) ) {
                $semantic_key = strtolower( (string) $row[ $candidate ] );
                break;
            }
        }

        foreach ( $row as $key => $value ) {
            $key_lc = strtolower( (string) $key );
            $hint   = $key_lc;
            if ( in_array( $key_lc, [ 'option_value', 'meta_value', 'value', 'data', 'payload' ], true ) && '' !== $semantic_key ) {
                $hint .= ' ' . $semantic_key;
            }

            if ( $this->is_sensitive_hint( $hint ) ) {
                $row[ $key ] = '[redacted]';
            } else {
                $row[ $key ] = $this->safe_value( $value, true );
            }
        }

        return $row;
    }

    /**
     * @param mixed $value Value.
     * @param bool  $redact_content Whether to redact embedded secrets/PII.
     * @return mixed
     */
    private function safe_value( $value, $redact_content ) {
        if ( null === $value || is_bool( $value ) || is_int( $value ) || is_float( $value ) ) {
            return $value;
        }

        if ( ! is_scalar( $value ) ) {
            $value = wp_json_encode( $value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES );
        }

        $text = $this->clean_text( (string) $value, 4000 );
        if ( ! $redact_content ) {
            return $text;
        }

        $text = preg_replace( '/[A-Z0-9._%+\-]+@[A-Z0-9.\-]+\.[A-Z]{2,}/i', '[redacted-email]', $text );
        $text = preg_replace( '/(?<!\d)(?:\+?\d[\d\s().-]{7,}\d)(?!\d)/', '[redacted-phone-or-number]', $text );
        $text = preg_replace( '/(?<!\d)(?:\d[ -]*?){13,19}(?!\d)/', '[redacted-card-number]', $text );
        $text = preg_replace( '/(bearer\s+)[A-Za-z0-9._~+\/=-]+/i', '$1[redacted-token]', $text );
        $text = preg_replace( '/((?:api[_-]?key|secret|password|passwd|token|authorization)["\'\s:=]+)[^,;\s"\']+/i', '$1[redacted]', $text );

        return is_string( $text ) ? $text : '';
    }

    /**
     * @param string $hint Field-name context.
     * @return bool
     */
    private function is_sensitive_hint( $hint ) {
        foreach ( [
            'password', 'passwd', 'user_pass', 'secret', 'token', 'nonce', 'api_key', 'apikey', 'authorization',
            'cookie', 'session', 'card', 'cvv', 'iban', 'swift', 'private_key', 'client_secret', 'access_key',
            'activation_key', 'auth_key', 'secure_auth', 'logged_in_key', 'nonce_key', 'smtp_pass', 'license_key',
            'email', 'phone', 'address', 'user_login', 'display_name', 'first_name', 'last_name',
        ] as $needle ) {
            if ( false !== strpos( (string) $hint, $needle ) ) {
                return true;
            }
        }
        return false;
    }

        /**
     * @param string $text Text.
     * @param int    $length Maximum length.
     * @return string
     */
    private function clean_text( $text, $length ) {
        $text = preg_replace( '/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]/', '', (string) $text );
        $text = is_string( $text ) ? trim( $text ) : '';
        if ( function_exists( 'mb_substr' ) ) {
            return mb_substr( $text, 0, $length );
        }
        return substr( $text, 0, $length );
    }
}