<?php
namespace ARCHM\Features;

if ( ! defined('ABSPATH') ) {
    // phpcs:ignore Universal.PHP.RequireExitDieParentheses.Missing
    exit;
}

use ARCHM\Features\Sqlite; // phpcs:ignore Universal.UseStatements.DisallowUseClass.FromSameNamespace

/**
 * My Account Archived Orders Handler
 *
 * Handles displaying archived orders in WooCommerce My Account page using React
 */
class MyAccountArchivedOrders {

    // Use DBHelper trait.
    use \ARCHM\Helpers\DBHelper;
    // Use utilities trait.
    use \ARCHM\Helpers\Utilities;

    /**
     * Constructor
     */
    public function __construct() {
        $this->set_remote_db_settings();
        $this->set_main_deb_settings();

        // Only initialize if the feature is enabled.
        if ( $this->is_feature_enabled() ) {
            add_action( 'init', [ $this, 'add_archived_orders_endpoint' ], 10 );
            add_filter( 'woocommerce_account_menu_items', [ $this, 'add_archived_orders_menu_item' ] );
            add_action( 'woocommerce_account_archived-orders_endpoint', [ $this, 'archived_orders_content' ] );
            add_action( 'wp_enqueue_scripts', [ $this, 'enqueue_scripts' ] );

            // Add notice after orders table if user has archived orders.
            add_action( 'woocommerce_account_orders_endpoint', [ $this, 'show_archived_orders_notice' ] );

            // Add fallback handler.
            add_action( 'template_redirect', [ $this, 'handle_archived_orders_fallback' ] );
        }

        // Add admin action to flush rewrite rules.
        add_action( 'admin_post_flush_archived_orders_rules', [ $this, 'flush_rewrite_rules_manually' ] );
    }

    /**
     * Check if the archived orders feature is enabled
     *
     * @return bool
     */
    private function is_feature_enabled() {
        $settings = get_option( 'archm_accoutn_page_archived_oder', [] );

        return isset( $settings['enable_feature'] ) && true === $settings['enable_feature'];
    }

    /**
     * Get the custom menu label for archived orders
     *
     * @return string
     */
    private function get_custom_menu_label() {
        $settings = get_option( 'archm_accoutn_page_archived_oder', [] );

        if ( isset( $settings['custom_menu_label'] ) && '' !== $settings['custom_menu_label'] ) {
            return sanitize_text_field( $settings['custom_menu_label'] );
        }

        return esc_html__( 'Archived Orders', 'archive-master' );
    }

    /**
     * Add archived orders endpoint
     */
    public function add_archived_orders_endpoint() {
        add_rewrite_endpoint( 'archived-orders', EP_ROOT | EP_PAGES );

        // Flush rewrite rules on plugin activation or if endpoint is not working.
        $flush_needed = false === get_option( 'archm_flush_rewrite_rules', false );

        // Check if endpoint is working by testing if it's in the rewrite rules.
        if ( ! $flush_needed ) {
            global $wp_rewrite;
            $rules           = $wp_rewrite->wp_rewrite_rules();
            $endpoint_exists = false;

            if ( is_array( $rules ) ) {
                foreach ( $rules as $pattern => $rewrite ) {
                    if ( false !== strpos( $pattern, 'archived-orders' ) ) {
                        $endpoint_exists = true;
                        break;
                    }
                }
            }

            if ( ! $endpoint_exists ) {
                $flush_needed = true;
            }
        }

        if ( $flush_needed ) {
            flush_rewrite_rules();
            update_option( 'archm_flush_rewrite_rules', true );
        }
    }

    /**
     * Add archived orders menu item to My Account
     *
     * @param array $items Menu items
     * @return array Modified menu items
     */
    public function add_archived_orders_menu_item( $items ) {
        // Insert archived orders after orders
        $new_items = [];

        foreach ( $items as $key => $item ) {
            $new_items[ $key ] = $item;
            if ( 'orders' === $key ) {
                $new_items['archived-orders'] = $this->get_custom_menu_label();
            }
        }

        return $new_items;
    }

    /**
         * Display archived orders content - Traditional WooCommerce-style table
     */
    public function archived_orders_content() {
        // Check if feature is enabled.
        if ( ! $this->is_feature_enabled() ) {
            echo '<div class="archive-master-notice"><p>' . esc_html__( 'Archived Orders feature is not enabled.', 'archive-master' ) . '</p></div>';
            return;
        }

        $current_user_id = get_current_user_id();

        if ( ! $current_user_id ) {
            echo '<div class="archive-master-notice"><p>' . esc_html__( 'Please log in to view your archived orders.', 'archive-master' ) . '</p></div>';
            return;
        }

        // Pagination - check multiple sources for page number.
        // WordPress handles pagination differently for plain vs pretty permalinks.
        $paged_get = filter_input( INPUT_GET, 'paged', FILTER_VALIDATE_INT );
        $paged_var = get_query_var( 'paged' );

        // For pretty permalinks, check if we're on a paginated endpoint URL.
        // WordPress parses /page/2/ as paged=2 in the query vars.
        $current_page = 1;

        // Prioritize GET parameter (works for plain permalinks).
        if ( null !== $paged_get && false !== $paged_get ) {
            $current_page = max( 1, (int) $paged_get );
        } elseif ( $paged_var ) {
            // Query var (works for pretty permalinks when WordPress parses /page/X/).
            $current_page = max( 1, (int) $paged_var );
        } else {
            // Fallback: check the current URL structure for /page/X/ pattern.
            $request_uri = isset( $_SERVER['REQUEST_URI'] ) ? esc_url_raw( wp_unslash( $_SERVER['REQUEST_URI'] ) ) : '';
            if ( preg_match( '/\/page\/(\d+)\/?$/', $request_uri, $matches ) ) {
                $current_page = max( 1, (int) $matches[1] );
            }
        }

        $items_per_page = 5;
        $offset         = ( $current_page - 1 ) * $items_per_page;

        // Get archived orders (no search/filter).
        $result = $this->get_user_archived_orders(
            $current_user_id,
            '',
            [],
            '',
            '',
            $items_per_page,
            $offset
        );

        $orders       = $result['orders'];
        $total_orders = $result['total'];
        $total_pages  = (int) ceil( $total_orders / $items_per_page );

        // Display the content.
        $this->render_archived_orders_table( $orders, $current_page, $total_pages, $total_orders );
    }

    /**
     * Render the archived orders table in WooCommerce style
     *
     * @param array  $orders        Array of archived orders.
     * @param int    $current_page   Current page number.
     * @param int    $total_pages    Total number of pages.
     * @param int    $total_orders   Total number of orders.
     */
	private function render_archived_orders_table( $orders, $current_page, $total_pages, $total_orders ) {
        // Prepare variables for template.
        $menu_label = $this->get_custom_menu_label();
        $instance   = $this;

        // Load template file.
        $template_path = ARCHM_PLUGIN_PATH . '/templates/my-account-archived-orders.php';

        if ( file_exists( $template_path ) ) {
            include $template_path;
        } else {
            // Fallback if template file doesn't exist.
            echo '<div class="archive-master-error"><p>' . esc_html__( 'Template file not found.', 'archive-master' ) . '</p></div>';
        }
    }

    /**
     * Fallback method to handle archived orders via query parameter
     */
    public function handle_archived_orders_fallback() {
        // Check if feature is enabled
        if ( ! $this->is_feature_enabled() ) {
            return;
        }

        // Check if we're on the my-account page and the archived-orders parameter is set.
        $archived_orders_flag = filter_input( INPUT_GET, 'archived-orders', FILTER_DEFAULT );

        if ( is_account_page() && null !== $archived_orders_flag ) {
            // Don't exit here, let the normal flow continue.
            // The archived_orders_content method will be called by the endpoint handler.
            return;
        }
    }

    /**
     * Get archived orders for a specific user
     *
     * @param int $user_id User ID
     * @return array Archived orders
     */
    private function get_user_archived_orders( $user_id, $search = '', $filter_status = [], $filter_date_from = '', $filter_date_to = '', $limit = 10, $offset = 0 ) {
        try {
            $table_name        = $this->wpdb->prefix . 'archived_orders';
            $selected_database = get_option( 'archm_selected_database' );
            $orders            = [];
            $total             = 0;

            if ( 'sqlite' === $selected_database || 'google_drive' === $selected_database ) {
                $sqlite = Sqlite::get_instance();

                // Check if SQLite database connection exists.
                if ( ! $sqlite || ! $sqlite->sqdb ) {
                    return [
						'orders' => [],
						'total' => 0,
					];
                }

                // phpcs:disable WordPress.DB.RestrictedClasses.mysql__PDO
                // Build WHERE and params - only filter by customer_id.
                $where  = "WHERE json_extract(json(order_data), '$.customer_id') = ?";
                $params = [ [ $user_id, \PDO::PARAM_INT ] ];

                // Count total.
                $count_sql  = "SELECT COUNT(*) as total FROM {$table_name} {$where}";
                $count_stmt = $sqlite->sqdb->prepare( $count_sql );
                $i          = 1;
                foreach ( $params as $param ) {
                    $count_stmt->bindValue( $i++, $param[0], $param[1] );
                }
                $count_stmt->execute();
                $total = (int) $count_stmt->fetchColumn();

                // Data query with pagination.
                $data_sql  = "SELECT * FROM {$table_name} {$where} ORDER BY id DESC LIMIT ? OFFSET ?";
                $data_stmt = $sqlite->sqdb->prepare( $data_sql );
                $i         = 1;
                foreach ( $params as $param ) {
                    $data_stmt->bindValue( $i++, $param[0], $param[1] );
                }
                $data_stmt->bindValue( $i++, $limit, \PDO::PARAM_INT );
                $data_stmt->bindValue( $i++, $offset, \PDO::PARAM_INT );
                $data_stmt->execute();
                $orders = $data_stmt->fetchAll( \PDO::FETCH_ASSOC );
                // phpcs:enable WordPress.DB.RestrictedClasses.mysql__PDO
            }

            if ( 'rds' === $selected_database || 'custom_mysql' === $selected_database ) {
                // Check if RDS database is connected.
                if ( ! $this->is_bd_connected() ) {
                    return [
						'orders' => [],
						'total' => 0,
					];
                }

                // Set remote db instance.
                $this->set_db_instance();

                // Verify remote_db instance was set properly.
                if ( ! $this->remote_db ) {
                    return [
						'orders' => [],
						'total' => 0,
					];
                }

                // Check if JSON functions are supported.
                $json_supported = $this->check_mysql_json_support();

                if ( $json_supported ) {
                    // Use JSON_EXTRACT for databases that support it.
                    // Build WHERE clause - only filter by customer_id.
                    $where = $this->remote_db->prepare( "JSON_EXTRACT(order_data, '$.customer_id') = %d", $user_id );

                    // Count total matching rows.
                    $count_query = "SELECT COUNT(*) FROM {$table_name} WHERE {$where}";
                    $total       = (int) $this->remote_db->get_var( $count_query );

                    // Data query with pagination.
                    $data_query = $this->remote_db->prepare( "SELECT * FROM {$table_name} WHERE {$where} ORDER BY id DESC LIMIT %d OFFSET %d", $limit, $offset );
                    $orders     = $this->remote_db->get_results( $data_query, ARRAY_A );
                } else {
                    // Fallback: Fetch orders with LIMIT/OFFSET and filter in PHP for older MySQL versions.
                    // Fetch a larger batch to account for filtering (fetch enough to cover offset + limit after filtering).
                    // Use a multiplier to ensure we get enough results after filtering.
                    $fetch_multiplier = 10; // Fetch 10x the limit to account for filtering
                    $fetch_limit      = max( $limit * $fetch_multiplier, $offset + $limit * 2 );
                    $fetch_offset     = 0;

                    // Fetch orders in batches until we have enough filtered results.
                    $filtered_orders = [];
                    $batch_size      = 1000; // Process in batches of 1000
                    $processed_count = 0;
                    $filtered_count  = 0;

                    while ( $filtered_count < ( $offset + $limit ) && $processed_count < $fetch_limit ) {
                        $current_batch_size = min( $batch_size, $fetch_limit - $processed_count );
                        $batch_orders       = $this->remote_db->get_results(
                            $this->remote_db->prepare(
                                "SELECT * FROM {$table_name} ORDER BY id DESC LIMIT %d OFFSET %d",
                                $current_batch_size,
                                $fetch_offset
                            ),
                            ARRAY_A
                        );

                        // If no more orders, break.
                        if ( empty( $batch_orders ) ) {
                            break;
                        }

                        // Filter orders in PHP - only filter by customer_id.
                        foreach ( $batch_orders as $order ) {
                            $order_data = json_decode( $order['order_data'], true );

                            if ( ! is_array( $order_data ) ) {
                                continue;
                            }

                            // Filter by customer_id only.
                            $order_customer_id = isset( $order_data['customer_id'] ) ? (int) $order_data['customer_id'] : 0;
                            if ( $order_customer_id !== $user_id ) {
                                continue;
                            }

                            $filtered_orders[] = $order;
                            $filtered_count++;
                        }

                        $fetch_offset    += $current_batch_size;
                        $processed_count += $current_batch_size;

                        // If we have enough results for pagination, we can stop early.
                        if ( count( $filtered_orders ) >= ( $offset + $limit ) ) {
                            break;
                        }
                    }

                    // Calculate total count.
                    // Since we can't use JSON_EXTRACT in COUNT, we estimate based on the ratio of matches.
                    $total = count( $filtered_orders );

                    // If we have enough results for the current page, estimate total based on match ratio.
                    if ( count( $filtered_orders ) >= ( $offset + $limit ) && $processed_count > 0 ) {
                        // Calculate match ratio from what we've processed.
                        $match_ratio = count( $filtered_orders ) / $processed_count;

                        // Get total orders in database (simple COUNT without JSON).
                        $db_total = (int) $this->remote_db->get_var( "SELECT COUNT(*) FROM {$table_name}" );

                        // Estimate total matching orders based on ratio.
                        if ( $db_total > 0 && $match_ratio > 0 ) {
                            $total = (int) ( $db_total * $match_ratio );
                            // Ensure total is at least what we have.
                            $total = max( $total, count( $filtered_orders ) );
                        }
                    }

                    // Apply pagination to filtered results.
                    $orders = array_slice( $filtered_orders, $offset, $limit );
                }
            }

            // Process orders to extract relevant data based on table structure.
            $processed_orders = [];
            foreach ( $orders as $order ) {
                $order_data_raw    = isset( $order['order_data'] ) ? json_decode( $order['order_data'], true ) : [];
                $ordered_products  = isset( $order['ordered_products'] ) ? json_decode( $order['ordered_products'], true ) : [];
                $order_data        = is_array( $order_data_raw ) ? $order_data_raw : [];
                $billing_data      = isset( $order_data['billing'] ) && is_array( $order_data['billing'] ) ? $order_data['billing'] : [];
                $date_created_data = isset( $order_data['date_created'] ) && is_array( $order_data['date_created'] ) ? $order_data['date_created'] : [];
                $payment_method    = '';

                if ( isset( $order_data['payment_method_title'] ) && '' !== $order_data['payment_method_title'] ) {
                    $payment_method = $order_data['payment_method_title'];
                } elseif ( isset( $order_data['payment_method'] ) ) {
                    $payment_method = $order_data['payment_method'];
                }

                $customer_first_name = isset( $billing_data['first_name'] ) ? $billing_data['first_name'] : '';
                $customer_last_name  = isset( $billing_data['last_name'] ) ? $billing_data['last_name'] : '';

                // Enrich products with name and SKU.
                $enriched_products = $this->enrich_products_with_details( $ordered_products );

                $processed_orders[] = [
                    'id'                => isset( $order['id'] ) ? (int) $order['id'] : 0,
                    'archived_order_id' => isset( $order['archived_order_id'] ) ? $order['archived_order_id'] : '',
                    'order_id'          => isset( $order_data['id'] ) ? $order_data['id'] : '',
                    'order_date'        => isset( $date_created_data['date'] ) ? $date_created_data['date'] : '',
                    'order_status'      => isset( $order_data['status'] ) ? $order_data['status'] : '',
                    'order_total'       => isset( $order_data['total'] ) ? $order_data['total'] : '',
                    'order_currency'    => isset( $order_data['currency'] ) ? $order_data['currency'] : '',
                    'payment_method'    => $payment_method,
                    'customer_name'     => trim( $customer_first_name . ' ' . $customer_last_name ),
                    'customer_email'    => isset( $billing_data['email'] ) ? $billing_data['email'] : '',
                    'billing_address'   => [
                        'first_name' => $customer_first_name,
                        'last_name'  => $customer_last_name,
                        'address_1'  => isset( $billing_data['address_1'] ) ? $billing_data['address_1'] : '',
                        'address_2'  => isset( $billing_data['address_2'] ) ? $billing_data['address_2'] : '',
                        'city'       => isset( $billing_data['city'] ) ? $billing_data['city'] : '',
                        'state'      => isset( $billing_data['state'] ) ? $billing_data['state'] : '',
                        'postcode'   => isset( $billing_data['postcode'] ) ? $billing_data['postcode'] : '',
                        'phone'      => isset( $billing_data['phone'] ) ? $billing_data['phone'] : '',
                        'email'      => isset( $billing_data['email'] ) ? $billing_data['email'] : '',
                    ],
                    'products'          => $enriched_products,
                    'products_count'    => count( $enriched_products ),
                    'created_at'        => isset( $order['created_at'] ) ? $order['created_at'] : '',
                ];
            }

            // Debug logging for processed orders.
			if ( ! empty( $processed_orders ) ) {
                $statuses = array_column( $processed_orders, 'order_status' );
                // error_log( 'Archive Master - Processed orders statuses: ' . wp_json_encode( array_unique( $statuses ) ) );
            }

            return [
                'orders' => $processed_orders,
                'total'  => $total,
            ];

        } catch ( \Exception $e ) {
            // error_log( 'Archive Master - Error fetching archived orders: ' . $e->getMessage() );
            return [
                'orders' => [],
                'total'  => 0,
            ];
        }
    }


    /**
     * Enrich products array with product name and SKU from WooCommerce.
     *
     * @param array $products Array of products with product_id, quantity, and total.
     * @return array Enriched products array with name and sku added.
     */
    private function enrich_products_with_details( $products ) {
        if ( ! is_array( $products ) || empty( $products ) ) {
            return [];
        }

        $enriched_products = [];

        foreach ( $products as $product ) {
            $product_id = isset( $product['product_id'] ) ? (int) $product['product_id'] : 0;

            if ( 0 === $product_id ) {
                // If no product ID, keep original data.
                $enriched_products[] = array_merge(
                    $product,
                    [
                        'name' => esc_html__( 'N/A', 'archive-master' ),
                        'sku'  => '',
                        'url'  => '',
                    ]
                );
                continue;
            }

            // Get product from WooCommerce.
            $wc_product = wc_get_product( $product_id );

            if ( ! $wc_product ) {
                // Product not found (might be deleted).
                $enriched_products[] = array_merge(
                    $product,
                    [
                        'name' => esc_html__( 'Product not found', 'archive-master' ),
                        'sku'  => '',
                        'url'  => '',
                    ]
                );
                continue;
            }

            // Get product name, SKU, and URL.
            $product_name = $wc_product->get_name();
            $product_sku  = $wc_product->get_sku();
            $product_url  = $wc_product->get_permalink();

            // Add name, SKU, and URL to product data.
            $enriched_products[] = array_merge(
                $product,
                [
                    'name' => $product_name ? $product_name : esc_html__( 'N/A', 'archive-master' ),
                    'sku'  => $product_sku ? $product_sku : '',
                    'url'  => $product_url ? $product_url : '',
                ]
            );
        }

        return $enriched_products;
    }

    /**
     * Format price with currency symbol.
     *
     * @param mixed  $amount   Amount value.
     * @param string $currency Currency code.
     * @return string
     */
	private function format_price( $amount, $currency = '' ) {
		$amount = '' !== $amount ? $amount : 0;

		if ( '' === $currency ) {
			return wc_price( $amount );
		}

		return wc_price(
			$amount,
			[
				'currency' => $currency,
			]
		);
	}

    /**
     * Enqueue scripts for traditional table styling.
     */
    public function enqueue_scripts() {
        // Check if we're on account page or on the archived-orders endpoint.
        $is_archived_orders_page = is_account_page()
            || is_wc_endpoint_url( 'archived-orders' )
            || null !== filter_input( INPUT_GET, 'archived-orders', FILTER_DEFAULT );

        if ( $is_archived_orders_page && $this->is_feature_enabled() ) {
            // Enqueue WooCommerce styles if not already loaded.
            wp_enqueue_style( 'woocommerce-general' );
            wp_enqueue_style( 'woocommerce-layout' );
            wp_enqueue_style( 'woocommerce-smallscreen' );

            // Add minimal custom CSS for archived orders table.
            wp_add_inline_style(
                'woocommerce-general',
                '.woocommerce-orders-table { margin-top: 0 !important; }'
            );
        }
	}

    /**
     * Show archived orders notice at bottom of orders table.
     */
	public function show_archived_orders_notice() {
		$current_user_id = get_current_user_id();

		if ( ! $current_user_id ) {
			return;
		}

		// Check if user has any archived orders.
		$result = $this->get_user_archived_orders( $current_user_id, '', [], '', '', 1, 0 );

		if ( ! empty( $result['orders'] ) && $result['total'] > 0 ) {
			$archived_orders_url = wc_get_account_endpoint_url( 'archived-orders' );
			?>
            <div style="margin-top: 20px; padding: 15px; background: #f8f9fa; border: 1px solid #dee2e6; border-radius: 4px;">
                <p style="margin: 0; color: #6c757d;">
                    <?php esc_html_e( 'Some of your orders have been archived.', 'archive-master' ); ?>
                    <a href="<?php echo esc_url( $archived_orders_url ); ?>" style="color: #007cba; text-decoration: none;">
                        <?php esc_html_e( 'Click here to view them', 'archive-master' ); ?>
                    </a>
                </p>
            </div>
            <?php
		}
	}

    /**
     * Manually flush rewrite rules.
     */
    public function flush_rewrite_rules_manually() {
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_die( esc_html__( 'Unauthorized', 'archive-master' ) );
        }

        flush_rewrite_rules();
        delete_option( 'archm_flush_rewrite_rules' );

        wp_safe_redirect( admin_url( 'admin.php?page=master-archives&archived_orders_flushed=1' ) );
        // phpcs:ignore Universal.PHP.RequireExitDieParentheses.Missing
        exit;
    }
}
