<?php
/**
 * Order Tracking and Reporting
 */

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

class CrewCommerce_Order_Tracking {
    
    private static $instance = null;
    
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    private function __construct() {
        // Admin-only hooks. admin_init also guarantees wc_get_page_screen_id() exists.
        add_action('admin_init', array($this, 'init'));
    }
    
    public function init() {
        // HPOS uses its own orders screen, where the classic list hooks don't fire.
        if ($this->hpos_enabled()) {
            $screen_id = wc_get_page_screen_id('shop_order');
            add_filter("manage_{$screen_id}_columns", array($this, 'add_order_columns'));
            add_action("manage_{$screen_id}_custom_column", array($this, 'populate_order_columns'), 10, 2);
            add_action('woocommerce_order_list_table_restrict_manage_orders', array($this, 'add_order_filters'));
            add_filter('woocommerce_order_list_table_prepare_items_query_args', array($this, 'filter_orders_by_company_hpos'));
        } else {
            add_filter('manage_edit-shop_order_columns', array($this, 'add_order_columns'));
            add_action('manage_shop_order_posts_custom_column', array($this, 'populate_order_columns'), 10, 2);
            add_action('restrict_manage_posts', array($this, 'add_order_filters'));
            add_filter('parse_query', array($this, 'filter_orders_by_company'));
        }

        // Add order meta boxes
        add_action('add_meta_boxes', array($this, 'add_order_meta_boxes'));
    }

    /**
     * Whether HPOS is the active order storage.
     */
    private function hpos_enabled() {
        return class_exists('\Automattic\WooCommerce\Utilities\OrderUtil')
            && \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
    }
    
    /**
     * The company filter on the WooCommerce order list (a read-only view
     * filter, so no nonce).
     */
    private function query_company() {
        return isset($_GET['crewcommerce_company']) ? absint(wp_unslash($_GET['crewcommerce_company'])) : 0; // phpcs:ignore WordPress.Security.NonceVerification.Recommended
    }

    private function query_approval_status() {
        $value = isset($_GET['crewcommerce_approval']) ? sanitize_key(wp_unslash($_GET['crewcommerce_approval'])) : ''; // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        return in_array($value, array('pending', 'approved', 'rejected', 'no_approval'), true) ? $value : '';
    }

    /**
     * Add custom columns to orders list
     */
    public function add_order_columns($columns) {
        $new_columns = array();
        
        foreach ($columns as $key => $column) {
            $new_columns[$key] = $column;
            
            if ($key === 'order_status') {
                $new_columns['company'] = __('Company', 'crewcommerce');
                $new_columns['requester'] = __('Requester', 'crewcommerce');
                $new_columns['approval_status'] = __('Approval Status', 'crewcommerce');
            }
        }
        
        return $new_columns;
    }
    
    /**
     * Populate custom order columns
     */
    public function populate_order_columns($column, $post_id) {
        $order = wc_get_order($post_id);
        if (!$order) {
            return;
        }
        
        switch ($column) {
            case 'company':
                $customer_id = $order->get_customer_id();
                $company     = null;
                if ($customer_id) {
                    $team_accounts = CrewCommerce_Team_Accounts::get_instance();
                    $company_id = $team_accounts->get_user_company($customer_id);
                    $company    = $company_id ? get_post($company_id) : null;
                }
                echo $company ? esc_html($company->post_title) : '&mdash;';
                break;
                
            case 'requester':
                $customer_id = $order->get_customer_id();
                if ($customer_id) {
                    $user = get_user_by('ID', $customer_id);
                    if ($user) {
                        echo esc_html($user->display_name);
                    } else {
                        echo '—';
                    }
                } else {
                    echo esc_html($order->get_billing_first_name() . ' ' . $order->get_billing_last_name());
                }
                break;
                
            case 'approval_status':
                $approvals = CrewCommerce_Approvals::get_instance();
                // Under HPOS this is a WC_Order, not a post ID.
                $workflow = $approvals->get_order_workflow($order->get_id());

                if ($workflow) {
                    $labels = CrewCommerce_Admin_Dashboard::workflow_status_labels();
                    printf(
                        '<span class="crewcommerce-status crewcommerce-status-%1$s">%2$s</span>',
                        esc_attr($workflow->status),
                        esc_html(isset($labels[$workflow->status]) ? $labels[$workflow->status] : ucfirst($workflow->status))
                    );
                } else {
                    echo '—';
                }
                break;
        }
    }
    
    /**
     * Company and approval filters on the Orders list. Hooked for both the
     * classic and the HPOS list, which pass the same two arguments.
     */
    public function add_order_filters($post_type = 'shop_order', $which = '') {
        if ($post_type !== 'shop_order') {
            return;
        }

        // Company filter
        $companies = get_posts(array(
            'post_type' => 'crewcommerce_company',
            'posts_per_page' => -1,
            'post_status' => 'publish'
        ));
        
        if (!empty($companies)) {
            $selected_company = $this->query_company();

            echo '<select name="crewcommerce_company">';
            echo '<option value="">' . esc_html__('All Companies', 'crewcommerce') . '</option>';

            foreach ($companies as $company) {
                printf(
                    '<option value="%1$d" %2$s>%3$s</option>',
                    (int) $company->ID,
                    selected($selected_company, $company->ID, false),
                    esc_html($company->post_title)
                );
            }

            echo '</select>';
        }
        
        // Approval status filter
        $selected_approval = $this->query_approval_status();
        $options           = array(
            ''            => __('All Approval Statuses', 'crewcommerce'),
            'pending'     => __('Pending Approval', 'crewcommerce'),
            'approved'    => __('Approved', 'crewcommerce'),
            'rejected'    => __('Rejected', 'crewcommerce'),
            'no_approval' => __('No Approval Required', 'crewcommerce'),
        );

        echo '<select name="crewcommerce_approval">';
        foreach ($options as $value => $label) {
            printf('<option value="%1$s" %2$s>%3$s</option>', esc_attr($value), selected($selected_approval, $value, false), esc_html($label));
        }
        echo '</select>';
    }
    
    /**
     * Filter orders by company and approval status
     */
    public function filter_orders_by_company($query) {
        global $pagenow, $typenow;
        
        if ($pagenow !== 'edit.php' || $typenow !== 'shop_order') {
            return;
        }
        
        $company_id      = $this->query_company();
        $approval_status = $this->query_approval_status();

        if ($company_id || $approval_status) {
            $meta_query = array();
            
            if ($company_id) {
                // Get users from this company
                $team_accounts = CrewCommerce_Team_Accounts::get_instance();
                $team_members = $team_accounts->get_team_members($company_id);
                $user_ids = wp_list_pluck($team_members, 'user_id');
                
                if (!empty($user_ids)) {
                    $meta_query[] = array(
                        'key' => '_customer_user',
                        'value' => $user_ids,
                        'compare' => 'IN'
                    );
                }
            }
            
            if ($approval_status && $approval_status !== 'no_approval') {
                global $wpdb;
                
                // Get order IDs with specific approval status
                $order_ids = $wpdb->get_col($wpdb->prepare(
                    "SELECT order_id FROM {$wpdb->prefix}crewcommerce_approval_workflows WHERE status = %s",
                    $approval_status
                ));
                
                if (!empty($order_ids)) {
                    $query->set('post__in', $order_ids);
                } else {
                    // No orders found with this status
                    $query->set('post__in', array(0));
                }
            } elseif ($approval_status === 'no_approval') {
                global $wpdb;
                
                // Get order IDs without approval workflows
                $order_ids_with_approval = $wpdb->get_col(
                    "SELECT DISTINCT order_id FROM {$wpdb->prefix}crewcommerce_approval_workflows"
                );
                
                if (!empty($order_ids_with_approval)) {
                    $query->set('post__not_in', $order_ids_with_approval);
                }
            }
            
            if (!empty($meta_query)) {
                $query->set('meta_query', $meta_query);
            }
        }
    }

    /**
     * HPOS version of filter_orders_by_company(), as query args.
     * Under HPOS 'post__in' maps to the order ID and 'customer' replaces
     * the _customer_user meta.
     */
    public function filter_orders_by_company_hpos($query_args) {
        $company_id      = $this->query_company();
        $approval_status = $this->query_approval_status();

        if ($company_id) {
            $team_accounts = CrewCommerce_Team_Accounts::get_instance();
            $team_members  = $team_accounts->get_team_members($company_id);
            $user_ids      = wp_list_pluck($team_members, 'user_id');
            $query_args['customer'] = !empty($user_ids) ? $user_ids : array(0);
        }

        if ($approval_status && $approval_status !== 'no_approval') {
            global $wpdb;
            $order_ids = $wpdb->get_col($wpdb->prepare(
                "SELECT order_id FROM {$wpdb->prefix}crewcommerce_approval_workflows WHERE status = %s",
                $approval_status
            ));
            $query_args['post__in'] = !empty($order_ids) ? $order_ids : array(0);
        } elseif ($approval_status === 'no_approval') {
            global $wpdb;
            $order_ids_with_approval = $wpdb->get_col(
                "SELECT DISTINCT order_id FROM {$wpdb->prefix}crewcommerce_approval_workflows"
            );
            if (!empty($order_ids_with_approval)) {
                $query_args['post__not_in'] = $order_ids_with_approval;
            }
        }

        return $query_args;
    }

    /**
     * Add order meta boxes
     */
    public function add_order_meta_boxes() {
        // Under HPOS the order screen isn't 'shop_order'.
        $screen_id = $this->hpos_enabled() ? wc_get_page_screen_id('shop_order') : 'shop_order';

        add_meta_box(
            'crewcommerce_order_info',
            __('CrewCommerce Order Info', 'crewcommerce'),
            array($this, 'render_order_info_meta_box'),
            $screen_id,
            'side',
            'high'
        );
    }
    
    /**
     * Render order info meta box
     */
    public function render_order_info_meta_box($post_or_order) {
        // A WP_Post, or a WC_Order under HPOS. wc_get_order() takes either.
        $order = wc_get_order($post_or_order);
        if (!$order) {
            return;
        }
        
        $customer_id = $order->get_customer_id();
        $team_accounts = CrewCommerce_Team_Accounts::get_instance();
        $approvals = CrewCommerce_Approvals::get_instance();
        
        // Get company info
        $company_id = $customer_id ? $team_accounts->get_user_company($customer_id) : 0;
        $company = $company_id ? get_post($company_id) : null;
        
        // Get user role
        $user_role = $customer_id ? $team_accounts->get_user_role($customer_id) : '';
        
        $workflow = $approvals->get_order_workflow($order->get_id());
        $labels   = CrewCommerce_Admin_Dashboard::workflow_status_labels();
        $roles    = array(
            'admin'     => __('Admin', 'crewcommerce'),
            'manager'   => __('Manager', 'crewcommerce'),
            'requester' => __('Requester', 'crewcommerce'),
        );
        ?>
        <div class="crewcommerce-order-info">
            <p><strong><?php esc_html_e('Company:', 'crewcommerce'); ?></strong><br>
            <?php echo esc_html($company ? $company->post_title : __('No company', 'crewcommerce')); ?></p>

            <p><strong><?php esc_html_e('Role:', 'crewcommerce'); ?></strong><br>
            <?php echo esc_html(isset($roles[$user_role]) ? $roles[$user_role] : __('N/A', 'crewcommerce')); ?></p>

            <?php if ($workflow) : ?>
                <p><strong><?php esc_html_e('Approval status:', 'crewcommerce'); ?></strong><br>
                <span class="crewcommerce-status crewcommerce-status-<?php echo esc_attr($workflow->status); ?>"><?php echo esc_html(isset($labels[$workflow->status]) ? $labels[$workflow->status] : ucfirst($workflow->status)); ?></span></p>

                <?php
                $approver = $workflow->approver_id ? get_user_by('ID', $workflow->approver_id) : null;
                if ($approver) :
                    ?>
                    <p><strong><?php esc_html_e('Approver:', 'crewcommerce'); ?></strong><br><?php echo esc_html($approver->display_name); ?></p>
                <?php endif; ?>

                <?php if ($workflow->notes) : ?>
                    <p><strong><?php esc_html_e('Notes:', 'crewcommerce'); ?></strong><br><?php echo esc_html($workflow->notes); ?></p>
                <?php endif; ?>

                <p><strong><?php esc_html_e('Submitted:', 'crewcommerce'); ?></strong><br>
                <?php echo esc_html(date_i18n(get_option('date_format') . ' ' . get_option('time_format'), strtotime($workflow->created_at))); ?></p>
            <?php else : ?>
                <p><em><?php esc_html_e('This order did not need approval.', 'crewcommerce'); ?></em></p>
            <?php endif; ?>
        </div>
        <?php
    }

    /**
     * Filtered orders via wc_get_orders(), so it works with HPOS.
     */
    public function get_filtered_orders($filters = array()) {
        $args = array(
            'status'  => array_keys(wc_get_order_statuses()),
            'limit'   => -1,
            'orderby' => 'date',
            'order'   => 'DESC',
            'return'  => 'objects',
        );

        // Customer filter by company
        if (!empty($filters['company_id'])) {
            $team_accounts = CrewCommerce_Team_Accounts::get_instance();
            $team_members  = $team_accounts->get_team_members($filters['company_id']);
            $user_ids      = wp_list_pluck($team_members, 'user_id');

            if (empty($user_ids)) {
                return array();
            }
            $args['customer_id'] = count($user_ids) === 1 ? $user_ids[0] : $user_ids;
        }

        // Customer filter by specific user
        if (!empty($filters['user_id'])) {
            $args['customer_id'] = intval($filters['user_id']);
        }

        // Date range
        if (!empty($filters['date_from'])) {
            $args['date_created'] = '>=' . strtotime($filters['date_from']);
        }
        if (!empty($filters['date_to'])) {
            $args['date_created'] = '<=' . strtotime($filters['date_to'] . ' 23:59:59');
        }
        if (!empty($filters['date_from']) && !empty($filters['date_to'])) {
            $args['date_created'] = strtotime($filters['date_from']) . '...' . strtotime($filters['date_to'] . ' 23:59:59');
        }

        // Order status
        if (!empty($filters['order_status'])) {
            $status = $filters['order_status'];
            $args['status'] = str_replace('wc-', '', $status);
        }

        $orders = wc_get_orders($args);

        // Filter by approval status in PHP (no direct WC query arg for this)
        if (!empty($filters['approval_status'])) {
            $approvals       = CrewCommerce_Approvals::get_instance();
            $filtered_orders = array();

            foreach ($orders as $order) {
                $workflow = $approvals->get_order_workflow($order->get_id());

                if ($filters['approval_status'] === 'no_approval' && !$workflow) {
                    $filtered_orders[] = $order;
                } elseif ($workflow && $workflow->status === $filters['approval_status']) {
                    $filtered_orders[] = $order;
                }
            }

            $orders = $filtered_orders;
        }

        return $orders;
    }
    
    /**
     * Get order statistics
     */
    public function get_order_statistics($filters = array()) {
        // Count straight from the database. Loading every order runs out of memory
        // on stores with a long order history.
        if (empty($filters)) {
            return $this->get_store_order_totals();
        }

        $orders = $this->get_filtered_orders($filters);
        
        $stats = array(
            'total_orders' => count($orders),
            'total_value' => 0,
            'pending_approval' => 0,
            'approved' => 0,
            'rejected' => 0,
            'no_approval_required' => 0,
            'by_status' => array(),
            'by_company' => array()
        );
        
        $team_accounts = CrewCommerce_Team_Accounts::get_instance();
        $approvals = CrewCommerce_Approvals::get_instance();
        
        foreach ($orders as $order) {
            if (!$order) {
                continue;
            }
            
            $stats['total_value'] += $order->get_total();
            
            // Count by order status
            $status = $order->get_status();
            if (!isset($stats['by_status'][$status])) {
                $stats['by_status'][$status] = 0;
            }
            $stats['by_status'][$status]++;
            
            // Count by company
            $customer_id = $order->get_customer_id();
            if ($customer_id) {
                $company_id = $team_accounts->get_user_company($customer_id);
                if ($company_id) {
                    $company = get_post($company_id);
                    $company_name = $company ? $company->post_title : 'Unknown';
                    
                    if (!isset($stats['by_company'][$company_name])) {
                        $stats['by_company'][$company_name] = 0;
                    }
                    $stats['by_company'][$company_name]++;
                }
            }
            
            // Count by approval status
            $workflow = $approvals->get_order_workflow($order->get_id());
            if ($workflow) {
                switch ($workflow->status) {
                    // Still waiting on a decision (pending, unassigned, or waiting for a second step).
                    case 'pending':
                    case 'unassigned':
                    case 'waiting':
                        $stats['pending_approval']++;
                        break;
                    case 'approved':
                        $stats['approved']++;
                        break;
                    case 'rejected':
                        $stats['rejected']++;
                        break;
                }
            } else {
                $stats['no_approval_required']++;
            }
        }
        
        return $stats;
    }

    /**
     * Dashboard totals straight from SQL, so no order objects are loaded.
     * Same numbers as get_order_statistics().
     */
    private function get_store_order_totals() {
        global $wpdb;

        $statuses = array_keys(wc_get_order_statuses());
        $in       = implode(',', array_fill(0, count($statuses), '%s'));

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- table names come from $wpdb and the status list is passed through prepare().
        if ($this->hpos_enabled()) {
            $orders_table = $wpdb->prefix . 'wc_orders';
            $orders_sql   = $wpdb->prepare(
                "SELECT id, total_amount AS total FROM {$orders_table} WHERE type = 'shop_order' AND status IN ({$in})",
                $statuses
            );
        } else {
            $orders_sql = $wpdb->prepare(
                "SELECT p.ID AS id, pm.meta_value AS total FROM {$wpdb->posts} p
                 LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id = p.ID AND pm.meta_key = '_order_total'
                 WHERE p.post_type = 'shop_order' AND p.post_status IN ({$in})",
                $statuses
            );
        }

        $totals = $wpdb->get_row("SELECT COUNT(*) AS n, COALESCE(SUM(o.total), 0) AS value FROM ({$orders_sql}) o");

        $workflows_table = $wpdb->prefix . 'crewcommerce_approval_workflows';
        $by_status = $wpdb->get_results(
            "SELECT w.status, COUNT(*) AS n
             FROM {$workflows_table} w
             INNER JOIN (SELECT order_id, MAX(id) AS latest_id FROM {$workflows_table} GROUP BY order_id) l ON l.latest_id = w.id
             INNER JOIN ({$orders_sql}) o ON o.id = w.order_id
             GROUP BY w.status",
            OBJECT_K
        );
        // phpcs:enable

        $count = function ($status) use ($by_status) {
            return isset($by_status[$status]) ? (int) $by_status[$status]->n : 0;
        };

        $with_workflow = array_sum(array_map(function ($row) { return (int) $row->n; }, $by_status));

        return array(
            'total_orders'         => (int) $totals->n,
            'total_value'          => (float) $totals->value,
            // Still waiting on a decision, assigned or not, first level or second.
            'pending_approval'     => $count('pending') + $count('unassigned') + $count('waiting'),
            'approved'             => $count('approved'),
            'rejected'             => $count('rejected'),
            'no_approval_required' => (int) $totals->n - $with_workflow,
            'by_status'            => array(),
            'by_company'           => array(),
        );
    }
}