<?php
/**
 * Order Manager — Module 5 core business logic.
 *
 * Handles all post-placement order operations:
 *   - Listing orders (admin) with filters, search, pagination
 *   - Getting a single order with items + status history
 *   - Updating order status (with history logging + optional email)
 *   - Adding internal / customer-facing notes
 *   - Fetching customer order history (storefront)
 *   - Seeding default order statuses on activation
 *
 * STATUS SYSTEM NOTE
 * ------------------
 * The schema stores `orders.status_id` (FK → order_statuses.id). However,
 * the existing CheckoutManager / OrderRepository (Module 4) inserts
 * `status = 'pending'` instead of `status_id`. This is a known inconsistency.
 *
 * To resolve it while remaining backward-compatible, this manager:
 *  1. Always queries via a JOIN so callers receive `status_handle` + `status_name`.
 *  2. `update_status()` writes the correct `status_id`.
 *  3. `seed_default_statuses()` is called on plugin activation to ensure the
 *     statuses table is populated before any order is placed.
 *
 * ACTION ITEM for Sean: In OrderRepository::create_order() replace:
 *     'status' => 'pending'
 * with:
 *     'status_id' => (new OrderManager())->get_status_id_by_handle('pending')
 *
 * @package TitanCart\Modules\Orders
 */

namespace TitanCart\Modules\Orders;

use TitanCart\DB\Database;

defined( 'ABSPATH' ) || exit;

class OrderManager {

    // ── Built-in status handles ────────────────────────────────────────────────

    const STATUS_PENDING    = 'pending';
    const STATUS_PROCESSING = 'processing';
    const STATUS_ON_HOLD    = 'on_hold';
    const STATUS_COMPLETED  = 'completed';
    const STATUS_CANCELLED  = 'cancelled';
    const STATUS_REFUNDED   = 'refunded';
    const STATUS_FAILED     = 'failed';

    /**
     * Default statuses seeded on activation.
     * Keys   = handle, Values = [ label, color, notify_customer, stock_action, position ]
     */
    const DEFAULT_STATUSES = [
        'pending'    => [ 'Pending',    '#D9A441', 0, 'none',    10 ],
        'processing' => [ 'Processing', '#1F6AE1', 1, 'none',    20 ],
        'on_hold'    => [ 'On Hold',    '#71797E', 0, 'none',    30 ],
        'completed'  => [ 'Completed',  '#1F8A70', 1, 'none',    40 ],
        'cancelled'  => [ 'Cancelled',  '#C84646', 1, 'restore', 50 ],
        'refunded'   => [ 'Refunded',   '#9B59B6', 1, 'restore', 60 ],
        'failed'     => [ 'Failed',     '#C84646', 0, 'none',    70 ],
    ];

    private Database $db;

    public function __construct() {
        $this->db = titancart_db();
    }

    // ══════════════════════════════════════════════════════════════════════════
    // LIST ORDERS (Admin)
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Fetch a paginated, filtered list of orders for the admin panel.
     *
     * Supported $args:
     *   page         int     Page number (1-indexed). Default 1.
     *   per_page     int     Rows per page. Default 20, max 100.
     *   status       string  Filter by status handle (e.g. 'processing').
     *   search       string  Matches order_number, billing_email, billing_first/last_name.
     *   date_from    string  YYYY-MM-DD lower bound (created_at).
     *   date_to      string  YYYY-MM-DD upper bound (created_at).
     *   orderby      string  Column to sort by. Default 'created_at'.
     *   order        string  'ASC' or 'DESC'. Default 'DESC'.
     *   ids          int[]   Restrict to specific order IDs (post-filter, pre-pagination).
     *                        Empty array short-circuits to zero results (defense against
     *                        WHERE id IN () MySQL parse error). Added S187 — mirrors
     *                        ProductRepository::get_list's ids filter so extensions can
     *                        pre-resolve a scoped id set and delegate to the canonical
     *                        list shape rather than duplicating column logic.
     *
     * Extension hook:
     *   `titancart_orders_list_where` (added S187) lets extensions append WHERE
     *   clauses + matching params to the query. State-array filter; see filter
     *   docblock below for contract. Marketplace uses this for vendor-scoped reads.
     *
     * @param  array $args
     * @return array{
     *   orders: array,
     *   total: int,
     *   pages: int,
     *   page: int,
     *   per_page: int
     * }
     */
    public function list_orders( array $args = [] ): array {
        $page     = max( 1, (int) ( $args['page']     ?? 1 ) );
        $per_page = min( 100, max( 1, (int) ( $args['per_page'] ?? 20 ) ) );
        $offset   = ( $page - 1 ) * $per_page;

        $allowed_order_by = [ 'id', 'created_at', 'total', 'status_id', 'billing_email' ];
        $orderby = in_array( $args['orderby'] ?? '', $allowed_order_by, true )
            ? $args['orderby']
            : 'created_at';
        $order   = strtoupper( $args['order'] ?? 'DESC' ) === 'ASC' ? 'ASC' : 'DESC';

        $where  = [ 'o.deleted_at IS NULL' ];
        $params = [];

        // Generic ids filter (S187 — mirrors ProductRepository::get_list).
        // When callers pass an explicit ids[] arg, restrict the result set. An
        // empty ids[] is treated as a deliberate "no matches" signal and we
        // short-circuit before SQL — both to avoid the WHERE id IN () MySQL
        // parse error and to honour the caller's filter intent.
        if ( isset( $args['ids'] ) && is_array( $args['ids'] ) ) {
            $ids_filter = [];
            foreach ( $args['ids'] as $id ) {
                $id = (int) $id;
                if ( $id > 0 ) {
                    $ids_filter[] = $id;
                }
            }
            if ( empty( $ids_filter ) ) {
                return [
                    'orders'   => [],
                    'total'    => 0,
                    'pages'    => 1,
                    'page'     => $page,
                    'per_page' => $per_page,
                ];
            }
            // Inline integer list is safe (every value was int-cast above).
            $where[] = 'o.id IN (' . implode( ',', $ids_filter ) . ')';
        }

        // Status filter.
        if ( ! empty( $args['status'] ) ) {
            $where[]  = 'os.handle = ?s';
            $params[] = sanitize_key( $args['status'] );
        }

        // Full-text search.
        if ( ! empty( $args['search'] ) ) {
            $s        = '%' . $this->db->escape_like( $args['search'] ) . '%';
            $where[]  = '(o.order_number LIKE ?s OR o.billing_email LIKE ?s OR CONCAT(o.billing_first_name, " ", o.billing_last_name) LIKE ?s)';
            $params[] = $s;
            $params[] = $s;
            $params[] = $s;
        }

        // Date range.
        if ( ! empty( $args['date_from'] ) ) {
            $where[]  = 'DATE(o.created_at) >= ?s';
            $params[] = sanitize_text_field( $args['date_from'] );
        }
        if ( ! empty( $args['date_to'] ) ) {
            $where[]  = 'DATE(o.created_at) <= ?s';
            $params[] = sanitize_text_field( $args['date_to'] );
        }

        /**
         * Filter the WHERE clauses + bound params for the orders list query.
         *
         * Lets extensions append WHERE conditions (with corresponding bound
         * params) to scope the order list without forking the read path. The
         * marketplace plugin subscribes from VendorOrderScopeFilter to inject
         * an EXISTS subquery against tc_vendor_products when $args['vendor_id']
         * is set — enabling vendor-scoped reads via delegation to this method
         * instead of a parallel VendorOrderRepository::list_for_vendor() with
         * duplicated column logic. Pattern #158 second-instance (S185 was the
         * response-layer twin at titancart_products_list_response; this is the
         * query-layer half).
         *
         * State-array shape:
         *   - where:  string[]   Active WHERE clauses (joined with AND).
         *   - params: array      Positional bound params for ?s / ?i placeholders.
         *   - args:   array      Resolved query args (read-only context — listener
         *                        keys off this to decide whether to inject).
         *
         * Listener contract:
         *   - Return the state array with same shape; both where + params must
         *     be returned together so they stay in lockstep.
         *   - May only ADD to where + params (never remove existing entries).
         *   - Newly appended params MUST be in the same positional order as
         *     newly appended where placeholders.
         *   - args is read-only context — listeners must not mutate it.
         *
         * @since  S187
         * @param  array{where:array,params:array,args:array} $state
         * @return array{where:array,params:array,args:array}
         */
        $state  = apply_filters( 'titancart_orders_list_where', [
            'where'  => $where,
            'params' => $params,
            'args'   => $args,
        ] );
        $where  = isset( $state['where']  ) && is_array( $state['where']  ) ? $state['where']  : $where;
        $params = isset( $state['params'] ) && is_array( $state['params'] ) ? $state['params'] : $params;

        $where_sql = 'WHERE ' . implode( ' AND ', $where );
        $joins     = $this->status_join_sql();

        // Total count.
        $count_sql = "SELECT COUNT(*) FROM {$this->db->table('orders')} o {$joins} {$where_sql}";
        $total     = (int) ( empty( $params )
            ? $this->db->getField( $count_sql )
            : $this->db->getField( $count_sql, ...$params )
        );

        // Rows.
        $select_sql = "
            SELECT {$this->order_select_columns()}
            FROM {$this->db->table('orders')} o
            {$joins}
            {$where_sql}
            ORDER BY o.{$orderby} {$order}
            LIMIT ?i OFFSET ?i
        ";
        $row_params   = array_merge( $params, [ $per_page, $offset ] );
        $orders       = $this->db->getArray( $select_sql, ...$row_params );
        // KI-97 (S425): re-label from the checkout snapshot when the join blanked.
        $orders       = $this->apply_method_snapshot_fallback_all( $orders ?: [] );

        return [
            'orders'   => $orders ?: [],
            'total'    => $total,
            'pages'    => $total > 0 ? (int) ceil( $total / $per_page ) : 1,
            'page'     => $page,
            'per_page' => $per_page,
        ];
    }

    // ══════════════════════════════════════════════════════════════════════════
    // GET SINGLE ORDER
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get one order by primary key, with items, option snapshots, notes, history.
     *
     * @param  int    $order_id
     * @param  string $lang  Language code for status label.
     * @return array|null
     */
    public function get_order( int $order_id, string $lang = 'en' ): ?array {
        $order = $this->db->getRow(
            "SELECT {$this->order_select_columns()}
             FROM {$this->db->table('orders')} o
             {$this->status_join_sql()}
             WHERE o.id = ?i AND o.deleted_at IS NULL
             LIMIT 1",
            $order_id
        );

        if ( ! $order ) {
            return null;
        }

        $order            = $this->apply_method_snapshot_fallback( $order );
        $order['items']   = $this->get_items( $order_id );
        $order['notes']   = $this->get_notes( $order_id );
        $order['history'] = $this->get_history( $order_id );

        return $order;
    }

    /**
     * Get one order by its human-readable order number.
     *
     * @param  string $order_number  e.g. 'TC-20260311-0042'
     * @param  string $lang
     * @return array|null
     */
    public function get_by_number( string $order_number, string $lang = 'en' ): ?array {
        $order = $this->db->getRow(
            "SELECT {$this->order_select_columns()}
             FROM {$this->db->table('orders')} o
             {$this->status_join_sql()}
             WHERE o.order_number = ?s AND o.deleted_at IS NULL
             LIMIT 1",
            strtoupper( $order_number )
        );

        if ( ! $order ) {
            return null;
        }

        $order            = $this->apply_method_snapshot_fallback( $order );
        $order['items']   = $this->get_items( (int) $order['id'] );
        $order['notes']   = $this->get_notes( (int) $order['id'] );
        $order['history'] = $this->get_history( (int) $order['id'] );

        return $order;
    }

    // ══════════════════════════════════════════════════════════════════════════
    // ORDER ITEMS
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get all line items for an order, with their option snapshots.
     *
     * @param  int $order_id
     * @return array
     */
    public function get_items( int $order_id ): array {
        $items = $this->db->getArray(
            "SELECT oi.*
             FROM {$this->db->table('order_items')} oi
             WHERE oi.order_id = ?i
             ORDER BY oi.id ASC",
            $order_id
        );

        if ( empty( $items ) ) {
            return [];
        }

        // Attach option snapshots.
        $item_ids = array_column( $items, 'id' );
        $options  = $this->db->getArray(
            "SELECT * FROM {$this->db->table('order_item_options')}
             WHERE order_item_id IN (?a)
             ORDER BY id ASC",
            $item_ids
        );

        // Index options by item id.
        $options_map = [];
        foreach ( $options as $opt ) {
            $options_map[ (int) $opt['order_item_id'] ][] = $opt;
        }

        // Batch-load product main images.
        //
        // Produces the S93 canonical `{url, thumb, alt}` array shape under
        // $item['image'] (mirrors cart.php / ProductRepository::get_listing
        // / CartManager). The legacy $item['thumbnail'] string URL is kept
        // alongside for backward compatibility with consumers that haven't
        // migrated; new consumers should read $item['image'] and handle the
        // array-or-null case.
        $product_ids = array_unique( array_filter( array_column( $items, 'product_id' ) ) );
        $image_map   = [];
        if ( ! empty( $product_ids ) ) {
            $images = $this->db->getArray(
                "SELECT pi.product_id, pi.wp_attachment_id, pi.alt_text
                 FROM {$this->db->table('product_images')} pi
                 WHERE pi.product_id IN (?a) AND pi.is_main = 1
                 ORDER BY pi.position ASC",
                $product_ids
            );
            $thumb_size = \TitanCart\Modules\Products\ImageService::cart_thumb_size();
            foreach ( $images as $img ) {
                $pid = (int) $img['product_id'];
                if ( ! isset( $image_map[ $pid ] ) && ! empty( $img['wp_attachment_id'] ) ) {
                    $att_id = (int) $img['wp_attachment_id'];
                    $thumb  = wp_get_attachment_image_url( $att_id, $thumb_size );
                    if ( $thumb ) {
                        $url = wp_get_attachment_image_url( $att_id, 'medium_large' )
                            ?: wp_get_attachment_image_url( $att_id, 'medium' )
                            ?: $thumb;
                        $image_map[ $pid ] = [
                            'url'   => $url,
                            'thumb' => $thumb,
                            'alt'   => (string) ( $img['alt_text'] ?? '' ),
                        ];
                    }
                }
            }
        }

        foreach ( $items as &$item ) {
            $item['options']   = $options_map[ (int) $item['id'] ] ?? [];
            $img               = $image_map[ (int) $item['product_id'] ] ?? null;
            // S93 canonical shape — array {url, thumb, alt} or null.
            // When alt_text is empty on the row, fall back to the captured
            // line-item name (which is the customer-facing name at purchase
            // time, surviving product renames).
            if ( $img !== null && $img['alt'] === '' ) {
                $img['alt'] = (string) ( $item['name'] ?? '' );
            }
            $item['image']     = $img;
            // Legacy string-URL field (kept for backward compat).
            $item['thumbnail'] = $img['thumb'] ?? null;
        }
        unset( $item );

        return $items;
    }

    // ══════════════════════════════════════════════════════════════════════════
    // STATUS MANAGEMENT
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Update an order's status, log the change, and optionally notify the customer.
     *
     * @param  int    $order_id
     * @param  string $new_handle  Status handle (e.g. 'processing').
     * @param  string $comment     Optional staff comment for the history entry.
     * @param  bool   $notify      Send status-update email to customer?
     * @return bool   False if the status handle does not exist or order not found.
     */
    public function update_status(
        int    $order_id,
        string $new_handle,
        string $comment  = '',
        bool   $notify   = false
    ): bool {
        $order = $this->db->getRow(
            "SELECT o.id, o.status_id, os.handle AS current_handle,
                    o.billing_email, o.order_number
             FROM {$this->db->table('orders')} o
             LEFT JOIN {$this->db->table('order_statuses')} os ON os.id = o.status_id
             WHERE o.id = ?i AND o.deleted_at IS NULL LIMIT 1",
            $order_id
        );

        if ( ! $order ) {
            return false;
        }

        $new_status_id = $this->get_status_id_by_handle( $new_handle );
        if ( ! $new_status_id ) {
            return false;
        }

        $old_handle = $order['current_handle'] ?? '';

        // No-op if status is unchanged.
        if ( $old_handle === $new_handle ) {
            return true;
        }

        // Update the order row.
        $this->db->update(
            'orders',
            [ 'status_id' => $new_status_id ],
            [ 'id'        => $order_id ]
        );

        // Log the transition.
        $this->log_history( $order_id, $old_handle, $new_handle, get_current_user_id() ?: null, $comment );

        // Add a system note.
        $note = sprintf(
            /* translators: 1: old status, 2: new status */
            __( 'Status changed from "%1$s" to "%2$s".', 'titancart-lite' ),
            $old_handle ?: __( 'none', 'titancart-lite' ),
            $new_handle
        );
        if ( $comment ) {
            $note .= ' ' . $comment;
        }
        $this->add_note( $order_id, $note, 'system' );

        /**
         * Fires after an order's status has been updated.
         *
         * @param int    $order_id
         * @param string $new_handle
         * @param string $old_handle
         * @param bool   $notify
         */
        do_action( 'titancart_order_status_changed', $order_id, $new_handle, $old_handle, $notify );

        return true;
    }

    /**
     * Get the status ID for a given handle. Returns 0 if not found.
     *
     * @param  string $handle
     * @return int
     */
    public function get_status_id_by_handle( string $handle ): int {
        $id = $this->db->getField(
            "SELECT id FROM {$this->db->table('order_statuses')} WHERE handle = ?s LIMIT 1",
            $handle
        );
        return (int) $id;
    }

    /**
     * Return all statuses as an indexed array, with translated name.
     *
     * @param  string $lang
     * @return array
     */
    public function get_all_statuses( string $lang = 'en', string $type = '' ): array {
        $where = $type ? " WHERE os.type = ?s" : '';
        $params = $type ? [ $lang, $type ] : [ $lang ];
        return $this->db->getArray(
            "SELECT os.*, COALESCE(ost.name, os.handle) AS label
             FROM {$this->db->table('order_statuses')} os
             LEFT JOIN {$this->db->table('order_status_translations')} ost
                 ON ost.status_id = os.id AND ost.language_code = ?s
             {$where}
             ORDER BY os.position ASC",
            ...$params
        );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // NOTES
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Add a note to an order.
     *
     * @param  int    $order_id
     * @param  string $note     Note text.
     * @param  string $type     'internal' | 'customer' | 'system'
     * @param  int|null $author_id  WP user ID of the author (null = system).
     * @return int  Inserted note ID, 0 on failure.
     */
    public function add_note(
        int     $order_id,
        string  $note,
        string  $type      = 'internal',
        ?int    $author_id = null
    ): int {
        $allowed = [ 'internal', 'customer', 'system' ];
        $type    = in_array( $type, $allowed, true ) ? $type : 'internal';

        $id = $this->db->insert( 'order_notes', [
            'order_id'  => $order_id,
            'author_id' => $author_id,
            'note'      => wp_kses_post( $note ),
            'type'      => $type,
        ] );

        return (int) $id;
    }

    /**
     * Get all notes for an order.
     *
     * @param  int $order_id
     * @return array
     */
    public function get_notes( int $order_id ): array {
        return $this->db->getArray(
            "SELECT n.*, u.display_name AS author_name
             FROM {$this->db->table('order_notes')} n
             LEFT JOIN {$this->db->wpdb_prefix()}users u ON u.ID = n.author_id
             WHERE n.order_id = ?i
             ORDER BY n.created_at ASC",
            $order_id
        );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // HISTORY
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get status-change history for an order.
     *
     * @param  int $order_id
     * @return array
     */
    public function get_history( int $order_id ): array {
        return $this->db->getArray(
            "SELECT h.*, u.display_name AS changed_by_name
             FROM {$this->db->table('order_history')} h
             LEFT JOIN {$this->db->wpdb_prefix()}users u ON u.ID = h.changed_by
             WHERE h.order_id = ?i
             ORDER BY h.created_at ASC",
            $order_id
        );
    }

    /**
     * Log a single status transition to order_history.
     *
     * @param int      $order_id
     * @param string   $from_handle
     * @param string   $to_handle
     * @param int|null $changed_by
     * @param string   $comment
     */
    public function log_history(
        int     $order_id,
        string  $from_handle,
        string  $to_handle,
        ?int    $changed_by = null,
        string  $comment    = ''
    ): void {
        $this->db->insert( 'order_history', [
            'order_id'   => $order_id,
            'from_status'=> $from_handle ?: null,
            'to_status'  => $to_handle,
            'changed_by' => $changed_by,
            'comment'    => $comment ?: null,
        ] );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // CUSTOMER ORDERS (Storefront)
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get paginated orders for a specific customer (storefront my-orders page).
     *
     * @param  int    $customer_id
     * @param  int    $page
     * @param  int    $per_page
     * @return array{orders: array, total: int, pages: int}
     */
    public function get_customer_orders( int $customer_id, int $page = 1, int $per_page = 10 ): array {
        $offset = ( max( 1, $page ) - 1 ) * $per_page;
        $joins  = $this->status_join_sql();

        $total = (int) $this->db->getField(
            "SELECT COUNT(*) FROM {$this->db->table('orders')} o {$joins}
             WHERE o.customer_id = ?i AND o.deleted_at IS NULL",
            $customer_id
        );

        $orders = $this->db->getArray(
            "SELECT {$this->order_select_columns()}
             FROM {$this->db->table('orders')} o
             {$joins}
             WHERE o.customer_id = ?i AND o.deleted_at IS NULL
             ORDER BY o.created_at DESC
             LIMIT ?i OFFSET ?i",
            $customer_id,
            $per_page,
            $offset
        );

        // KI-97 (S425): re-label from the checkout snapshot when the join blanked.
        $orders = $this->apply_method_snapshot_fallback_all( $orders ?: [] );

        return [
            'orders'   => $orders ?: [],
            'total'    => $total,
            'pages'    => $total > 0 ? (int) ceil( $total / $per_page ) : 1,
        ];
    }

    /**
     * Get a single order visible to a specific customer (ownership check included).
     *
     * @param  int $order_id
     * @param  int $customer_id
     * @return array|null
     */
    public function get_customer_order( int $order_id, int $customer_id ): ?array {
        $order = $this->db->getRow(
            "SELECT {$this->order_select_columns()}
             FROM {$this->db->table('orders')} o
             {$this->status_join_sql()}
             WHERE o.id = ?i AND o.customer_id = ?i AND o.deleted_at IS NULL
             LIMIT 1",
            $order_id,
            $customer_id
        );

        if ( ! $order ) {
            return null;
        }

        $order            = $this->apply_method_snapshot_fallback( $order );
        $order['items']   = $this->get_items( (int) $order['id'] );
        // Only include customer-facing notes.
        $order['notes']   = $this->get_customer_visible_notes( (int) $order['id'] );

        return $order;
    }

    /**
     * Get only customer-visible notes (type = 'customer').
     *
     * @param int $order_id
     * @return array
     */
    public function get_customer_visible_notes( int $order_id ): array {
        return $this->db->getArray(
            "SELECT id, note, created_at
             FROM {$this->db->table('order_notes')}
             WHERE order_id = ?i AND type = 'customer'
             ORDER BY created_at ASC",
            $order_id
        );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // ACTIVATION — SEED DEFAULT STATUSES
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Insert the built-in order statuses if they haven't been seeded yet.
     * Called from the Installer or on first boot.
     */
    public function seed_default_statuses(): void {
        foreach ( self::DEFAULT_STATUSES as $handle => $meta ) {
            [ $label, $color, $notify, $stock_action, $position ] = $meta;

            // Upsert — skip if already exists.
            $existing_id = $this->db->getField(
                "SELECT id FROM {$this->db->table('order_statuses')} WHERE handle = ?s LIMIT 1",
                $handle
            );

            if ( $existing_id ) {
                continue;
            }

            $status_id = $this->db->insert( 'order_statuses', [
                'handle'          => $handle,
                'color'           => $color,
                'position'        => $position,
                'is_default'      => $handle === 'pending' ? 1 : 0,
                'notify_customer' => $notify,
                'stock_action'    => $stock_action,
            ] );

            if ( $status_id ) {
                $this->db->insert( 'order_status_translations', [
                    'status_id'     => $status_id,
                    'language_code' => 'en',
                    'name'          => $label,
                ] );
            }
        }
    }

    // ══════════════════════════════════════════════════════════════════════════
    // HELPERS
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * The SELECT column list used across all order queries.
     *
     * Derives three sets of human-readable fields from ID-only FK columns so
     * the admin UI (and any REST consumer) doesn't have to do lookups:
     *   - status_handle / status_color / status_label   (from order_statuses + _translations)
     *   - payment_method / payment_gateway              (from payment_methods + _translations)
     *   - shipping_method / shipping_carrier            (from shipping_methods + _translations)
     *
     * All fall back through: translation name → base table slug → empty string.
     * Join SQL lives in status_join_sql() — must always be paired with this.
     */
    private function order_select_columns(): string {
        return "
            o.*,
            COALESCE(os.handle, '') AS status_handle,
            COALESCE(os.color,  '#71797E') AS status_color,
            COALESCE(ost.name,  os.handle, '') AS status_label,
            /* KI-97 (S424) — ⚠️ REVERTED IN THE SAME SESSION. These four columns
             * briefly carried a JSON_EXTRACT fallback to o.payment_info /
             * o.shipping_info so a deleted method would not blank the label on a
             * historical order. It broke the ORDERS PAGE ON BOTH STORES: this
             * method feeds EVERY order read path, so one unsupported or failing
             * SQL function empties the entire list rather than degrading one
             * column. The snapshot fallback belongs in PHP, after the rows are
             * fetched, where a failure cannot take the query with it. Do not put
             * JSON functions back into this shared SELECT. */
            COALESCE(pmt.name,  pm.gateway, '') AS payment_method,
            COALESCE(pm.gateway, '') AS payment_gateway,
            COALESCE(smt.name,  sm.carrier, '') AS shipping_method,
            COALESCE(sm.carrier, '') AS shipping_carrier
        ";
    }

    /**
     * Fill blank method labels from the order's own checkout snapshots.
     *
     * KI-97 (S425). order_select_columns() resolves the payment and shipping
     * labels by LEFT JOIN, so deleting a gateway or a shipping method leaves
     * the matching column as '' on every historical order that used it, and
     * the admin order screen then renders a bare em dash where the name
     * should be.
     *
     * tc_orders.payment_info / shipping_info hold the name captured AT
     * CHECKOUT (CheckoutManager::payment_snapshot() and the KI-32 shipping
     * snapshot), so the name outlives the row it came from.
     *
     * ⚠️ This runs in PHP AFTER the rows are fetched, deliberately. S424 put
     * the equivalent JSON_EXTRACT into order_select_columns(), which feeds
     * EVERY order read path, and one failing expression returned ZERO orders
     * on both stores instead of blanking one column. Here the worst case is
     * that a missing or malformed snapshot leaves the label as the '' it
     * already was — it cannot take the query with it.
     *
     * The live join still WINS whenever it resolved, so renaming a method
     * that still exists behaves exactly as before. Orders placed before the
     * snapshot existed carry none and legitimately keep ''.
     *
     * @param  array $order One fetched order row.
     * @return array
     */
    private function apply_method_snapshot_fallback( array $order ): array {
        if ( empty( $order['payment_method'] ) ) {
            $snap = $this->decode_snapshot( $order['payment_info'] ?? null );
            if ( $snap !== null ) {
                if ( ! empty( $snap['name'] ) ) {
                    $order['payment_method'] = (string) $snap['name'];
                }
                if ( empty( $order['payment_gateway'] ) && ! empty( $snap['gateway'] ) ) {
                    $order['payment_gateway'] = (string) $snap['gateway'];
                }
            }
        }

        if ( empty( $order['shipping_method'] ) ) {
            $snap = $this->decode_snapshot( $order['shipping_info'] ?? null );
            if ( $snap !== null ) {
                if ( ! empty( $snap['name'] ) ) {
                    $order['shipping_method'] = (string) $snap['name'];
                }
                if ( empty( $order['shipping_carrier'] ) && ! empty( $snap['carrier'] ) ) {
                    $order['shipping_carrier'] = (string) $snap['carrier'];
                }
            }
        }

        return $order;
    }

    /**
     * Apply the snapshot fallback across a list of fetched order rows.
     *
     * @param  array $orders
     * @return array
     */
    private function apply_method_snapshot_fallback_all( array $orders ): array {
        foreach ( $orders as $i => $row ) {
            if ( is_array( $row ) ) {
                $orders[ $i ] = $this->apply_method_snapshot_fallback( $row );
            }
        }

        return $orders;
    }

    /**
     * Decode one of the JSON snapshot columns to an array, tolerantly.
     *
     * Returns null for NULL, '', malformed JSON or a non-array payload, so
     * every caller can treat "no usable snapshot" as a single case. An
     * already-decoded array is passed straight through.
     *
     * @param  mixed $raw
     * @return array|null
     */
    private function decode_snapshot( $raw ): ?array {
        if ( is_array( $raw ) ) {
            return $raw;
        }
        if ( ! is_string( $raw ) || $raw === '' ) {
            return null;
        }

        $decoded = json_decode( $raw, true );

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

    /**
     * LEFT JOINs for status + payment method + shipping method, each with their
     * 'en' translation row when present. Kept as one block so all read paths
     * produce the same column shape. If WPML or storefront i18n needs a
     * different language, callers can add a filter on this method later.
     */
    private function status_join_sql(): string {
        return "
            LEFT JOIN {$this->db->table('order_statuses')} os
                ON os.id = o.status_id
            LEFT JOIN {$this->db->table('order_status_translations')} ost
                ON ost.status_id = os.id AND ost.language_code = 'en'
            LEFT JOIN {$this->db->table('payment_methods')} pm
                ON pm.id = o.payment_method_id
            LEFT JOIN {$this->db->table('payment_method_translations')} pmt
                ON pmt.method_id = pm.id AND pmt.language_code = 'en'
            LEFT JOIN {$this->db->table('shipping_methods')} sm
                ON sm.id = o.shipping_method_id
            LEFT JOIN {$this->db->table('shipping_method_translations')} smt
                ON smt.method_id = sm.id AND smt.language_code = 'en'
        ";
    }
}
